Combining dfs with different lenghts according to date

I am trying to combine several df with different lenghts. All data are related to a specific date.
I need to combine them to a single df but all data should be in the row of the date it was taken.
Unfortunately I was unsuccessfull searching for an answer on the internet - but mybe I just did not use the right words.

Example:
3 df:

          t1 Q1
1 1990-01-01  1
2 1990-01-02  2
3 1990-01-03  3
4 1990-01-04  4
5 1990-01-05  5
 > print(df2)
          t2 Q2
1 1990-01-03  6
2 1990-01-04  7
3 1990-01-05  8
> print(df3)
          t3 Q3
1 1990-01-01  9
2 1990-01-02 10
3 1990-01-03 11

should look like

> print(df)
          t1 Q1 Q2 Q3
1 1990-01-01  1 Na  9
2 1990-01-02  2 NA 10
3 1990-01-03  3  6 11
4 1990-01-04  4  7 NA
5 1990-01-05  5  8 NA

Here is one solution.

library(dplyr)
library(tidyr)
df1 <- data.frame(t1=c("1990-01-01","1990-01-02","1990-01-03",
                       "1990-01-04","1990-01-05"),
                  Q1=1:5)
df2 <- data.frame(t2=c("1990-01-03","1990-01-04","1990-01-05"),
                  Q2=6:8)
df3<- data.frame(t3=c("1990-01-01","1990-01-02","1990-01-03"),
                 Q3=9:11)

df1 <- rename(df1,T=t1, Value=Q1)
df1$Q <- "Q1"

df2 <- rename(df2,T=t2, Value=Q2)
df2$Q <- "Q2"

df3 <- rename(df3,T=t3, Value=Q3)
df3$Q <- "Q3"

AllDF <- rbind(df1,df2,df3)
AllDF
#>             T Value  Q
#> 1  1990-01-01     1 Q1
#> 2  1990-01-02     2 Q1
#> 3  1990-01-03     3 Q1
#> 4  1990-01-04     4 Q1
#> 5  1990-01-05     5 Q1
#> 6  1990-01-03     6 Q2
#> 7  1990-01-04     7 Q2
#> 8  1990-01-05     8 Q2
#> 9  1990-01-01     9 Q3
#> 10 1990-01-02    10 Q3
#> 11 1990-01-03    11 Q3
WideDF <- pivot_wider(AllDF,names_from = "Q",values_from = "Value")
WideDF
#> # A tibble: 5 x 4
#>   T             Q1    Q2    Q3
#>   <chr>      <int> <int> <int>
#> 1 1990-01-01     1    NA     9
#> 2 1990-01-02     2    NA    10
#> 3 1990-01-03     3     6    11
#> 4 1990-01-04     4     7    NA
#> 5 1990-01-05     5     8    NA

Created on 2022-08-15 by the reprex package (v2.0.1)

1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.