Create a data frame with the common columns of a data frame list - R

user_9643128 0
Viewed 39

I need to get the common columns of a data frame list separated in different data frames. Please look at the following example:

df1 <- data.frame(Dates = c('01-01-2020','02-01-2020','03-01-2020'), col1 = c(1,2,3), col2 = c(3,2,1))
df2 <- data.frame(Dates = c('01-01-2020','02-01-2020','03-01-2020'), col1 = c(4,5,6), col2 = c(6,5,4))
df3 <- data.frame(Dates = c('01-01-2020','02-01-2020'), col1 = c(7,8), col2 = c(8,7))
ldf <- list(df1, df2, df3)

The desired output would be the following two data frames:

df_col1:
Date       df1 df2 df3
01-01-2020   1   4   7
02-01-2020   2   5   8
03-01-2020   3   6  NA

df_col2:
Date       df1 df2 df3
01-01-2020   3   6   8
02-01-2020   2   5   7
03-01-2020   1   4  NA

Of course, ldf is actually way longer, but the number of columns is fixed to 5, so the number of outputs is also fixed (4). This means I wouldn't mind if I use a block of code for each output.

I've tried several things but none seems to work. I'm using base R and hope to find a solution wihtout additional packages.

Thanks a lot for your time!

dataframe r merge list
3 Answers

We bind the list elements with bind_rows from dplyr, then loop over the 'col' columns, along with the common 'Dates', reshape to 'wide' format with pivot_wider and rename if needed

library(dplyr)
library(purrr)
library(tidyr)
library(stringr)
newdf <- bind_rows(ldf) 
out <- map(names(newdf)[-1], ~ 
            newdf %>%
                select(Dates, .x) %>%
                mutate(rn = rowid(Dates)) %>% 
                pivot_wider(names_from =rn, values_from = !! rlang::sym(.x))  %>%
        rename_at(-1, ~ str_c('df', seq_along(.))))

-output

out
#[[1]]
# A tibble: 3 x 4
#  Dates        df1   df2   df3
#  <chr>      <dbl> <dbl> <dbl>
#1 01-01-2020     1     4     7
#2 02-01-2020     2     5     8
#3 03-01-2020     3     6    NA

#[[2]]
# A tibble: 3 x 4
#  Dates        df1   df2   df3
#  <chr>      <dbl> <dbl> <dbl>
#1 01-01-2020     3     6     8
#2 02-01-2020     2     5     7
#3 03-01-2020     1     4    NA

Or using base R

newdf <- do.call(rbind, ldf)
f1 <- function(dat, colName) {
      lst1 <- split(dat[[colName]], dat$Dates)
       do.call(rbind, lapply(lst1, `length<-`, max(lengths(lst1))))
 }
     
f1(newdf, 'col1')
f1(newdf, 'col2')
user_37322710

Another Base R option is to do:

m <- Reduce(function(x,y)merge(x, y, by='Dates', all=TRUE), ldf)
lapply(split.default(m[-1], sub("\\..*", "", names(m[-1]))), cbind, m[1])
user_83802720

Another wordy approach using base R:

#Code
names(ldf) <- paste0('df',1:length(ldf))
#Function
myfun <- function(x) {
  y <- reshape(x,direction = 'long',
               v.names='col',
               idvar = 'Dates',varying = list(2:3))
  return(y)
}
z <- do.call(rbind,lapply(ldf,myfun))
z$Data <- gsub("\\..*","",rownames(z))
rownames(z) <- NULL
#Reshape
z2 <- reshape(z,idvar = c('Dates','time'),timevar = 'Data')
#List
List <- split(z2,z2$time)
List

Output:

List
$`1`
       Dates time col.df1 col.df2 col.df3
1 01-01-2020    1       1       4       7
2 02-01-2020    1       2       5       8
3 03-01-2020    1       3       6      NA

$`2`
       Dates time col.df1 col.df2 col.df3
4 01-01-2020    2       3       6       8
5 02-01-2020    2       2       5       7
6 03-01-2020    2       1       4      NA
user_20808480
Related