CODEWITHSUNDEEP
rdatabasedataframedata-wrangling
Abraham Arbelaez
Abraham Arbelaez

Reputation: 9

How can I join a variable from a different dataset with two if_else statements?

let's say I have df1:

Country Date
Country A 2010-08-10
Country B 2010-10-10
Country C 2010-11-10
Country A 2011-09-10
Country A 2015-08-10

and I have df2

Ranking Country Date
5 Country A 2010-07-10
8 Country B 2010-07-10
27 Country C 2010-07-10
6 Country A 2010-09-10
10 Country B 2010-09-10
20 Country C 2010-09-10
16 Country A 2010-10-10
3 Country B 2010-10-10
27 Country C 2010-10-10
10 Country A 2011-08-10
1 Country A 2015-07-10

How can I make a table that would like this?

Country Date Ranking
Country A 2010-08-10 5
Country B 2010-10-10 8
Country C 2010-11-10 27
Country A 2011-09-10 10
Country A 2015-08-10 1

So that the country has the ranking from a month before. I tried but I cannot figure it out. This is what I have

df1 <- df1 %>% 
mutate(ranking = if_else(Date > df2$Ranking & Country == df2$Country, df2$Country, df2$Country))

Upvotes: 0

Views: 29

Answers (1)

Jon Spring
Jon Spring

Reputation: 66775

We could do this by joining the tables, where one table's Date is offset by a month.

library(dplyr); library(lubridate)
df1 %>% 
  left_join(df2 %>% mutate(Date = Date %m+% months(1)))

One bit of warning "from a month before" is ambiguous and sometimes tricky. What day is a month before March 30?

Edit: if we want "prior rank in the data", then:

df1 %>%
  left_join(df2 %>% 
    group_by(Country) %>%
    arrange(Date) %>%
    mutate(Rank = lead(Rank),
           Date = lead(Date))

This would take all the df2 Rank + Date values and use the next one in the data.

Upvotes: 1

Related Questions