CODEWITHSUNDEEP
rdataframedatereplace
B-Z
B-Z

Reputation: 83

How to replace some values of a column in a data frame based on another data frame through a lagged date and ID?

I am trying to replace some values in a column of a data frame with the help of a date and ID in another data frame but I cannot manage to find any solution. It will be more clear with an example.

I have two data frames constructed as followed:

date.1 <- c("01.02.2011","02.02.2011","03.02.2011","04.02.2011","05.02.2011","01.02.2011","02.02.2011","03.02.2011","04.02.2011","05.02.2011")
date.1 <- as.Date(date.1, format="%d.%m.%Y")
values.1 <- c("1","3","5","1","2","6","7","8","9","10")
ID.1 <- c("10","10","10","10","10","11","11","11","11","11")

df.1 <- data.frame(date.1, values.1, ID.1)
names(df.1) <- c("date","values","ID")

date.2 <- c("04.02.2011","04.02.2011")
date.2 <- as.Date(date.2, format="%d.%m.%Y")
values.2 <- c("1", "9")
ID.2 <- c("10","11")

df.2 <- data.frame(date.2, values.2, ID.2)
names(df.2) <- c("date","values","ID")

which looked like:

> df.1
         date values ID
1  2011-02-01      1 10
2  2011-02-02      3 10
3  2011-02-03      5 10
4  2011-02-04      1 10
5  2011-02-05      2 10
6  2011-02-01      6 11
7  2011-02-02      7 11
8  2011-02-03      8 11
9  2011-02-04      9 11
10 2011-02-05     10 11


> df.2
        date values ID
1 2011-02-04      1 10
2 2011-02-04      9 11

I would like to replace the "values" in df.2 for each ID with the "values" of df.1 on the next date, i.e. with the values on 2011-02-05 but I don't manage to replace them. Thus, I would like to obtain:

> df.2
            date values ID
    1 2011-02-04      2 10
    2 2011-02-04     10 11

Your help would be really appreciated. If any editing of the question is needed, do not hesitate to let me know.

Upvotes: 0

Views: 38

Answers (2)

sachin2014
sachin2014

Reputation: 462

Is this what you are looking for?

library(lubridate)
library(dplyr)
df.2$values <- df.1 %>% filter (ID == df.2$ID & date == (df.2$date +1)) %>% select(values)

Upvotes: 1

stefan
stefan

Reputation: 124183

If next date means date + 1 day, then try this: 

library(dplyr)

df.2 %>% 
  mutate(date1 = date + 1) %>% 
  select(-values) %>% 
  left_join(df.1, by = c(date1 = "date", ID = "ID")) %>% 
  select(-date1)
#>         date ID values
#> 1 2011-02-04 10      2
#> 2 2011-02-04 11     10

Created on 2020-03-28 by the reprex package (v0.3.0)

Upvotes: 2

Related Questions