How to join the corrects values according multiple conditions?

Ramiro Guzmán 0
Viewed 21

Hello¡ I hope be clear and consisstant because I think the proccess is a little complicated to understand. I will show an example what I need after an explanation. I have a dataset (base 2) that contains two data I want to join with another base (base 1) but there are many facts. The first is that I need two values that are in the same variable but these values are designed according another variable. The second is that I need to join the correct value according the time period.

I show an example from one case.

base 1:

STORE CODE PERIOD
60M4 1105

base 2:

VALUE CODE WEEK FROM WEEK TO STORE CODE CHANEL
AREA I BR AREA 945 1189 60M4 NA
AREA I BR AREA 1190 NA 60M4 NA
BIG STORE TYPE 1198 NA 60M4 5

Joined base:

STORE CODE PERIOD BR AREA STORE TYPE CHANEL
60M4 1105 AREA I BIG 5

In base2, the variable 'CODE' has the two variables (BR AREA & STORE TYPE) I need in the joining but as values in rows and the values I need are in 'VALUE' (AREA I & BIG). Then, the joining part to getting the AREA are connected to a period of time, this means that the store from the periods 945 to 1189 was in AREA I and then from 1190-NA (up to day) is in AREA I (the same), so, I need to join the correct period of time, and as I show, my period in this case is 1105, that means that I need to join the AREA in the period 945-1189 in addition to join the store type an chanel.

First I tried to filter the information, but it hasn't worked for me. I have thousands of rows and I unkwnow if could be possible with a cicle or just the correct filter.

Thank you so much

join r
1 Answers
library(dplyr)
library(tidyr)
library(tibble)

df1 <- tribble(~STORE_CODE,     ~PERIOD,
        "60M4",     1105)

df2 <- tribble(~VALUE,  ~CODE,  ~WEEK_FROM,     ~WEEK_TO,   ~STORE_CODE,    ~CHANEL,
        "AREA I",   "BR AREA",  945,    1189,   "60M4",     NA,
        "AREA I",   "BR AREA",  1190,   NA,     "60M4",     NA,
        "BIG",  "STORE TYPE",   1198,   NA,     "60M4",     5)

df2 |> 
  pivot_wider(values_from = VALUE, names_from = CODE) |> 
  group_by(STORE_CODE) |> 
  fill(CHANEL, `BR AREA`, `STORE TYPE`, .direction = "downup") |> 
  ungroup() |> 
  right_join(df1, by = "STORE_CODE") |> 
  filter(PERIOD >= WEEK_FROM, PERIOD <= WEEK_TO)  |> 
  select(STORE_CODE, PERIOD, `BR AREA`, `STORE TYPE`, CHANEL)

# A tibble: 1 × 5
  STORE_CODE PERIOD `BR AREA` `STORE TYPE` CHANEL
  <chr>       <dbl> <chr>     <chr>         <dbl>
1 60M4         1105 AREA I    BIG               5

This is assuming that variables like BR AREA, STORE TYPE, and CHANEL are the same each for each STORE_CODE.

Phil0
Related