Left join value of exact date and if missing find previous value

user_14033222 0
Viewed 615

I have two datasets (df1 and df2) that both have the common columns "date" and "country" that are used to left_join the datasets (especially the column "price" from df2). If there is no exact match (date and country), the missing values (NAs) are filled with the value from the previous row (by country). This works quite well so far.

However, I have problems if there is no exact match for the first observation (by country), because then it is not possible to fill it with the previous row. In this case the NA (e.g. row 1; 2015-07-18) should be filled with the previous value from df2, which contains an observation on 2015-07-15. It is important, that I match on the exact date or previous date (latest known value) and not the nearest date, which would be 2017-07-20.

In the final table, the price on 2015-07-18 (UK) should be 2.5, which is the last known value.

Below you will find the current table and exemplary data.

   date         country   price
1  2015-07-18   UK        NA  
2  2015-07-20   UK        3.0  
3  2015-07-21   UK        2.7
4  2015-07-22   UK        4.5
5  2015-07-25   UK        4.6
6  2015-07-19   US        1.3
7  2015-07-20   US        2.7
8  2015-07-21   US        3.9
9  2015-07-22   US        2.8
10 2015-07-24   US        2.5

library(dplyr)

date <- c("2015-07-18", "2015-07-20", "2015-07-21", "2015-07-22", "2015-07-25", "2015-07-19", "2015-07-20",
          "2015-07-21", "2015-07-22", "2015-07-24")
country <- c("UK", "UK", "UK", "UK", "UK", "US", "US", "US", "US", "US")

df1 <- cbind.data.frame(date, country)

date <- c("2015-07-15", "2015-07-20", "2015-07-21", "2015-07-22", "2015-07-24", "2015-07-19", "2015-07-20",
          "2015-07-21", "2015-07-22", "2015-07-24")
country <- c("UK", "UK", "UK", "UK", "UK", "US", "US", "US", "US", "US")
price <- c(2.5, 3.0, 2.7, 4.5, 4.6, 1.3, 2.7, 3.9, 2.8, 2.5)

df2 <- cbind.data.frame(date, country, price)

df <- df1 %>%
  left_join(df2, by = c("date", "country"))

df %>%
  group_by(country) %>%
  arrange(date) %>%
  tidyr::fill(price,.direction ="down") %>%
  arrange(country, date) %>%
  ungroup()
dplyr left-join r
3 Answers

You can try full_join()

df_b <- df1 %>% 
  full_join(df2, by = c("date", "country")) %>% 
  arrange(country, date) %>% 
  group_by(country) %>% 
  mutate(price = ifelse(is.na(price), lag(price), price)) %>% 
  ungroup() %>% 
  filter(date %in% df$date) %>% 
  inner_join(df, by = c("date", "country")) %>% 
  select(-price.y)
# A tibble: 10 x 3
# date       country price.x
# <chr>      <chr>     <dbl>
#   1 2015-07-18 UK          2.5
# 2 2015-07-20 UK          3  
# 3 2015-07-21 UK          2.7
# 4 2015-07-22 UK          4.5
# 5 2015-07-25 UK          4.6
# 6 2015-07-19 US          1.3
# 7 2015-07-20 US          2.7
# 8 2015-07-21 US          3.9
# 9 2015-07-22 US          2.8
# 10 2015-07-24 US          2.5
user_123363050

Here is an option using rolling join in data.table (which is shorter and should be much faster):

library(data.table)
setDT(df1)
setDT(df2)
df1[, price := df2[.SD, on=.(country, date), roll=Inf, price]]

output:

          date country price
 1: 2015-07-18      UK   2.5
 2: 2015-07-20      UK   3.0
 3: 2015-07-21      UK   2.7
 4: 2015-07-22      UK   4.5
 5: 2015-07-25      UK   4.6
 6: 2015-07-19      US   1.3
 7: 2015-07-20      US   2.7
 8: 2015-07-21      US   3.9
 9: 2015-07-22      US   2.8
10: 2015-07-24      US   2.5

data:

date <- as.Date(c("2015-07-18", "2015-07-20", "2015-07-21", "2015-07-22", "2015-07-25", "2015-07-19", "2015-07-20",
    "2015-07-21", "2015-07-22", "2015-07-24"))
country <- c("UK", "UK", "UK", "UK", "UK", "US", "US", "US", "US", "US")
df1 <- data.frame(date, country)

date <- as.Date(c("2015-07-15", "2015-07-20", "2015-07-21", "2015-07-22", "2015-07-24", "2015-07-19", "2015-07-20",
    "2015-07-21", "2015-07-22", "2015-07-24"))
country <- c("UK", "UK", "UK", "UK", "UK", "US", "US", "US", "US", "US")
price <- c(2.5, 3.0, 2.7, 4.5, 4.6, 1.3, 2.7, 3.9, 2.8, 2.5)
df2 <- data.frame(date, country, price)

edit: look up for multiple columns:

cols <- c("price", "cost", "revenue") 
df1[, (cols) := df2[.SD, on=.(country, date), roll=Inf, mget(cols)]]
user_19894800

Tho Vu beat me to it, but here is my solution which is very similar:

full_join(df1,df2) %>% 
    arrange(country, date) %>% 
    mutate(price=ifelse(is.na(price), lag(price), price))
user_82673530
Related