In R, what is a method to join dataframes by Date with identical column names and dimensions and run a function on the matching columns

user_16416706 0
Viewed 53

I'd like to join multiple data frames by Date that have identical column names, and the same dimensions, but the values may be different. The joined data frame should be the mean between each value for each matching column.

My problem is similar to these questions, and the answers given provide similar solutions to what I need, however, they do not fully answer my question. The Python code (2.) is the closest to what I need, but I can't code in Python well enough:

  1. Combining Multiple Identically-Named Columns in R
  2. inner merge dataFrame with identical column names and apply aggregate function

I would prefer a solution using dplyr and/or purrr. But I am always willing to learn more about base R.

The input data looks like this:

df1 <- data.frame(Date=c("2019-01-01","2019-01-02","2019-01-03"),A=c(3,2,1),B=c(2,2,2),C=c(1,3,3))
df2 <- data.frame(Date=c("2019-01-01","2019-01-02","2019-01-03"),A=c(1,2,3),B=c(4,4,4),C=c(3,3,1))
df3 <- data.frame(Date=c("2019-01-01","2019-01-02","2019-01-03"),A=c(5,2,3),B=c(4,5,4),C=c(3,3,1))

The output that I would want is:

df4 <- data.frame(Date=c("2019-01-01","2019-01-02","2019-01-03"),A=c(3,2,2.3),B=c(3.3,3.6,3.3),C=c(2.3,2.3,1.6))

Thanks for the help.

dplyr dataframe r
2 Answers

We can bind the datasets together, then do a group by 'Date' and summarise across all the columns with mean

library(dplyr)
bind_rows(df1, df2, df3) %>%
     group_by(Date) %>% 
     summarise(across(everything(), mean), .groups = 'drop')

-ouput

# A tibble: 3 x 4
  Date           A     B     C
  <chr>      <dbl> <dbl> <dbl>
1 2019-01-01  3     3.33  2.33
2 2019-01-02  2     3.67  3   
3 2019-01-03  2.33  3.33  1.67
user_37322710

In base R, you can use aggregate after combining the three datasets into one.

aggregate(.~Date, rbind(df1, df2, df3), mean, na.rm = TRUE)

#        Date        A        B        C
#1 2019-01-01 3.000000 3.333333 2.333333
#2 2019-01-02 2.000000 3.666667 3.000000
#3 2019-01-03 2.333333 3.333333 1.666667
user_39629140
Related