CODEWITHSUNDEEP
rpurrr
cephalopod
cephalopod

Reputation: 1906

how to add together dataframes within a list but only for matching dates

I have a list of dataframes that I want to consolidate these dataframes into one data frame. I am looking to solve two problems:

  1. How to add together the columns
  2. How to only include common dates across all the dfs withing the list

This is what I have:

library(tidyverse)
library(lubridate)

df1 <- data.frame(
               date = ymd(c("2019-02-01", "2019-02-02", "2019-02-03", "2019-02-04",
                        "2019-02-05")),
                  x = c(1, 2, 3, 4, 5),
                  y = c(2, 3, 4, 5, 6),
                  z = c(3, 4, 5, 6, 7)
       ) 


df2 <- data.frame(
               date = ymd(c("2019-02-01", "2019-02-02", "2019-02-04", "2019-02-05")),
                  x = c(1, 2, 3, 4),
                  y = c(2, 3, 4, 5),
                  z = c(3, 4, 5, 6)
       )

df3 <- data.frame(
               date = ymd(c("2019-02-01", "2019-02-02", "2019-02-03", "2019-02-04")),
                  x = c(1, 2, 3, 4),
                  y = c(2, 3, 4, 5),
                  z = c(3, 4, 5, 6)
       )


dfl <- list(df1, df2, df3)

This is the output I am looking for:

data.frame(
        date = ymd(c("2019-02-01", "2019-02-02", "2019-02-04")),
           x = c(3, 6, 11),
           y = c(6, 9, 14),
           z = c(9, 12, 17)
)

I have tried inner_join and tried looping through the list but it got too complicated and I still didn't manage to land on the answer. Is there a more cleaner way to get to the final answer

Upvotes: 1

Views: 29

Answers (1)

Maurits Evers
Maurits Evers

Reputation: 50668

How about this?

bind_rows(dfl) %>%
    group_by(date) %>%
    mutate(n = 1) %>%
    summarise_all(sum) %>%
    filter(n == length(dfl)) %>%
    select(-n)
## A tibble: 3 x 4
#  date           x     y     z
#  <date>     <dbl> <dbl> <dbl>
#1 2019-02-01     3     6     9
#2 2019-02-02     6     9    12
#3 2019-02-04    11    14    17

This assumes that there are no duplicate dates in a single data.frame of dfl.

Upvotes: 2

Related Questions