In R time series dataframe, how to separate and categorize based on regex

user_19110395 0
Viewed 24

Hello I have a time series dataframe comprised of a list of products and their different tax rates that I need to segregate into two categories: percentages numbers(AV) and text(everything else without percentage numbers (SPEC), that are separated by the first plus sign in the character vector:

#note there are many more years
product <- c("01","02")
yr1<-c("0%","11.5% + 190 GBP/100kg")
yr2<-c("0%","15% + 190 GBP/100kg + MAX 8.5%/100kg")
yearnum =2

sched <- data.frame(product,yr1,yr2)

#where yearnum is the number of years
schedule<-c(paste0("yr",1:yearnum))
#categorize av and specific DUTY rates
for(j in 1:yearnum){
  for(i in schedule){
  sched <- sched %>% separate(i, c(paste0("av.yr",j), paste0("spec.yr",j)), " \\+ ", remove=F, extra = "merge")}}

I'm trying to separate them into the result below, but there is something wrong with my for loop formulation. Could anyone please help?

#and the output should be
product <- c("01","02")
yr1<-c("0%","11.5% + 190 GBP/100kg")
yr2<-c("0%","15% + 190 GBP/100kg + MAX 8.5%/100kg") 
av.yr1<- c("0%","11.5%")
av.yr2 <-c("0%","15%")
spec.yr1 <-c("","190 GBP/100kg")
spec.yr2 <-c("","190 GBP/100kg + MAX 8.5%/100kg")

sched<-data.frame(product,yr1,yr2,av.yr1,av.yr2,spec.yr1,spec.yr2)
r for-loop split
2 Answers

If you have lots of years, I think the best thing to do is to pivot your data into long format, use separate or mutate with regular expressions, and pivot_back to wide.

pivot_longer(sched, -product) %>%
  separate(value,into=c("av","spec"),sep = " [+] ",extra = "merge") %>% 
  pivot_wider(names_from=name,values_from=av:spec,names_sep = ".")

Output:

  product av.yr1 av.yr2 spec.yr1      spec.yr2                      
  <chr>   <chr>  <chr>  <chr>         <chr>                         
1 01      0%     0%     NA            NA                            
2 02      11.5%  15%    190 GBP/100kg 190 GBP/100kg + MAX 8.5%/100kg

Here is an option using mutate, which retains the original columns as well:

pivot_longer(sched, -product, values_to = "yr", names_prefix = "yr") %>%
mutate(av.yr = str_extract(yr,"^\\d*[.]?\\d*%"),
       spec.yr = str_remove(yr, "^\\d*[.]?\\d*%( [+] )?")) %>% 
pivot_wider(names_from=name, values_from=yr:spec.yr, names_sep = "")

Output

  product yr1                   yr2                                  av.yr1 av.yr2 spec.yr1        spec.yr2                        
  <chr>   <chr>                 <chr>                                <chr>  <chr>  <chr>           <chr>                           
1 01      0%                    0%                                   0%     0%     ""              ""                              
2 02      11.5% + 190 GBP/100kg 15% + 190 GBP/100kg + MAX 8.5%/100kg 11.5%  15%    "190 GBP/100kg" "190 GBP/100kg + MAX 8.5%/100kg"
user_44475400

You only need to iterate over one index:

library(tidyr)
#note there are many more years
product <- c("01","02")
yr1<-c("0%","11.5% + 190 GBP/100kg")
yr2<-c("0%","15% + 190 GBP/100kg + MAX 8.5%/100kg")
yearnum =2

sched <- data.frame(product,yr1,yr2)

#where yearnum is the number of years
schedule<-c(paste0("yr",1:yearnum))
#categorize av and specific DUTY rates
for(j in 1:yearnum){
  i <- schedule[j]
  sched <- sched %>% separate(i, c(paste0("av.yr",j), paste0("spec.yr",j)), 
                              " \\+ ", remove=F, extra = "merge", fill = "right")
}
sched
#>   product                   yr1 av.yr1      spec.yr1
#> 1      01                    0%     0%          <NA>
#> 2      02 11.5% + 190 GBP/100kg  11.5% 190 GBP/100kg
#>                                    yr2 av.yr2                       spec.yr2
#> 1                                   0%     0%                           <NA>
#> 2 15% + 190 GBP/100kg + MAX 8.5%/100kg    15% 190 GBP/100kg + MAX 8.5%/100kg

Created on 2022-05-25 by the reprex package (v2.0.1)

user_191453680
Related