Compare two data frames to populate the Days range in r-CodePudding

I have Two data frames DF1 and DF2, I need to compare the Days from DF1 with respect to LOW Range and HI Range columns from DF2 and get the Days Range column in the resulting data frame.

Items=c("Vegetables","Fruits","Grocery","Dairy Product")
Days=c(16,5,41,25)
DF1=data.frame(Items,Days)

Low_Range=c(0,8,15,22,31,61)
Hi_Range=c(7,14,21,30,60,90)
Days_Range=c("within 7 days","8 to 14 days","15 to 21 days","22 to 30 days","31 to 60 days","61 to 90 days")
DF2=data.frame(Low_Range,Hi_Range,Days_Range)

Days_Slot=c("15 to 21 days","within 7 days","31 to 60 days","22 to 30 days")
DF_Result=data.frame(Items,Days,Days_Slot)

DF_Result will be my Resulting data frame with Days_Slot as new column added to DF1. Can anyone Help to solve this

CodePudding user response：

This can be solved by updating in a non-equi join:

library(data.table)
setDT(DF1)[setDT(DF2), on = .(Days >= Low_Range, Days <= Hi_Range), 
           Days_Slot := Days_Range][]

           Items Days     Days_Slot
1:    Vegetables   16 15 to 21 days
2:        Fruits    5 within 7 days
3:       Grocery   41 31 to 60 days
4: Dairy Product   25 22 to 30 days

Note that DF1 is updated by reference, i.e., the new column Days_Slot is appended to DF1 without copying the object.

As the intervals are contiguous, the matching Days_Range can be determined also by a rolling join:

library(data.table)
setDT(DF1)
setDT(DF2)
DF1[, Days_Slot := DF2[DF1, on = .(Low_Range = Days), roll = TRUE]$Days_Range][]

           Items Days     Days_Slot
1:    Vegetables   16 15 to 21 days
2:        Fruits    5 within 7 days
3:       Grocery   41 31 to 60 days
4: Dairy Product   25 22 to 30 days

Again, a new column Days_Slot is appended to DF1 by reference.

By the way, a backward rolling join will give the same result:

DF1[, Days_Slot := DF2[DF1, on = .(Hi_Range = Days), roll = -Inf]$Days_Range][]

CodePudding user response：

You can use fuzzyjoin.

fuzzyjoin::fuzzy_left_join(DF1, DF2,
                           by = c('Days' = 'Low_Range', 'Days' = 'Hi_Range'), 
                           match_fun = c(`>=`, `<=`))

#          Items Days Low_Range Hi_Range    Days_Range
#1    Vegetables   16        15       21 15 to 21 days
#2        Fruits    5         0        7 within 7 days
#3       Grocery   41        31       60 31 to 60 days
#4 Dairy Product   25        22       30 22 to 30 days

If your dataset is huge you may also try data.table.

library(data.table)
setDT(DF1)
setDT(DF2)

DF2[DF1, on = .(Low_Range <= Days, Hi_Range >= Days)]