CODEWITHSUNDEEP
rdataframejoinmergetime-series
moreirasd
moreirasd

Reputation: 127

Combine two dataframes of different frequncies and unequal lenght in R

I need to join/merge two dataframes, df1 and df2, that have different frequencies and lengths. In these dataframes the date correspond to the dates of the change in the values (value1 and value2).

The dataframes are:

State <- c(A,A,A,B,B,B)
Date1 <- c(01/01/2015, 02/04/2015, 12/01/2016, 03/02/2015, 02/05/2015, 25/01/2016)
Date2 <- C(01/02/2013, 01/04/2015, 19/07/2013, 23/01/2016)
value1 <- c(2.5, 3.2, 2.7, 3.1, 3.6, 2.8)
value2 <- c(0.12, 0.25, 0.2, 0.3)

df1 <- data.frame(State, Date1, value1)
df2 <- data.frame(State, Date2, value2)

Then we have:

State   Date1       Value1
A       01/01/2015  2.5 
A       02/04/2015  3.2
A       12/01/2016  2.7
B       03/02/2015  3.1
B       02/05/2015  3.6
B       25/01/2016  2.8

State   Date2       Value2
A       01/02/2013  0.12    
A       01/04/2015  0.25
B       19/07/2013  0.20
B       23/01/2016  0.30

I want to join/merge these two dataframes and adjust them to the same time-frequency (daily or weekly). The ideal outcome would be, for daily frequency:

State   Date        Value1  Value2
A       01/02/2013  NA      0,12
A       02/02/2013  NA      0,12
(...)
A       01/01/2015  2,5     0,12
(...)
A       01/04/2015  2,5     0,25
A       02/04/2015  3,2     0,25
(...)
A       12/01/2016  2,7     0,25
(...)
B       19/07/2013  NA      0,20
(...)
B       03/02/2015  3,1     0,20
(...)
B       02/05/2015  3,6     0,20
(...)
B       23/01/2016  3,6     0,30
B       24/01/2016  3,6     0,30
B       25/01/2016  2,8     0,30

Any help is appreciated!

Upvotes: 0

Views: 35

Answers (1)

KaptajnKasper
KaptajnKasper

Reputation: 170

I slightly modified the sample you provide i show how to achieve what i think you are looking for using dplyr's full_join

library(dplyr)

State1 <- c("A","A","A","B","B","B")
State2 <- c("A","A","B","B")
Date1 <- c("01/01/2015", "01/04/2015", "12/01/2016", "03/02/2015", "02/05/2015", "25/01/2016")
Date2 <- c("01/02/2013", "01/04/2015", "19/07/2013", "23/01/2016")
value1 <- c(2.5, 3.2, 2.7, 3.1, 3.6, 2.8)
value2 <- c(0.12, 0.25, 0.2, 0.3)

df1 <- data.frame(state = State1, date = Date1, value = value1)
df2 <- data.frame(state = State2, date = Date2, value = value2)

full_join(df1, df2, by=c("state", "date"), suffix = c("1","2"))

Which would return the following

  state       date value1 value2
1     A 01/01/2015    2.5     NA
2     A 01/04/2015    3.2   0.25
3     A 12/01/2016    2.7     NA
4     B 03/02/2015    3.1     NA
5     B 02/05/2015    3.6     NA
6     B 25/01/2016    2.8     NA
7     A 01/02/2013     NA   0.12
8     B 19/07/2013     NA   0.20
9     B 23/01/2016     NA   0.30

I hope this is helpful to you.

Upvotes: 1

Related Questions