Mean of a variable from multiple data.frame in R?

user_10226369 0
Viewed 36

I have the following data.frame and I would like to create another data.frame (say M) that would store mean of A1 from M1,M2,M3, mean of B1, from M1,M2,M3 and likewise for C1.

library(tidyverse)


set.seed(123)

M1 <- data.frame(Date = seq(as.Date("2001-01-01"), to = as.Date("2005-12-31"), by = "month"),
           A1 = runif(60,1,5),
           B1 = runif(60,1,5),
           C1 = runif(60,1,5))

M2 <- data.frame(Date = seq(as.Date("2001-01-01"), to = as.Date("2005-12-31"), by = "month"),
                 A1 = runif(60,1,5),
                 B1 = runif(60,1,5),
                 C1 = runif(60,1,5))

M3 <- data.frame(Date = seq(as.Date("2001-01-01"), to = as.Date("2005-12-31"), by = "month"),
                 A1 = runif(60,1,5),
                 B1 = runif(60,1,5),
                 C1 = runif(60,1,5))

Desired output

The output would be a data.frame (M) with variables of A (mean of A1 from M1,M2,M3), B (mean of B1 from M1,M2,M3), and C (mean of C1 from M1,M2,M3).

tidyverse mean lubridate dataframe r
1 Answers

We get the datasets in a list (mget), subset the columns of interest, get the elementwise sum (+) and divide by 3 (as there are only 3 datasets)

M <- Reduce(`+`, lapply(mget(ls(pattern = "^M\\d+")), `[`, 
     -1))/3
names(M) <- sub("\\d+", "", names(M))

-output

> head(M)
         A        B        C
1 2.694883 3.345868 3.196847
2 2.724759 2.868531 2.524246
3 3.685341 2.535909 2.859400
4 2.941540 2.639169 3.182535
5 3.530815 3.690165 3.576402
6 2.747724 3.399104 3.107880

Or another option is to bind the datasets together and then do a group by mean

library(dplyr)
library(stringr)
bind_rows(M1, M2, M3) %>%
   group_by(Date) %>%
   summarise(across(everything(), mean, na.rm = TRUE, 
      .names = "{str_remove(.col, '[0-9]+')}"))
# A tibble: 60 × 4
   Date           A     B     C
   <date>     <dbl> <dbl> <dbl>
 1 2001-01-01  2.69  3.35  3.20
 2 2001-02-01  2.72  2.87  2.52
 3 2001-03-01  3.69  2.54  2.86
 4 2001-04-01  2.94  2.64  3.18
 5 2001-05-01  3.53  3.69  3.58
 6 2001-06-01  2.75  3.40  3.11
 7 2001-07-01  2.32  3.02  1.58
 8 2001-08-01  2.97  3.89  2.04
 9 2001-09-01  3.49  3.56  2.36
10 2001-10-01  3.46  3.71  3.69
# … with 50 more rows
user_37322710
Related