Extracting columns from dataframes in a list to produce a final, combined dataframe

Question

Apologies, returning to lists and dataframes in R after a while, so have forgotten my way about. Suppose that I have several dataframes in a list:

d2<- data.frame(week=c("12th","13th","14th"),value=c(1,20,100))
d1<- data.frame(week=c("12th","13th","14th"),value=c(1,10,15))
d3<- data.frame(week=c("12th","13th","14th"),value=c(1,220,30))
dfList<- list(d1,d2,d3)

dfList
[[1]]
  week value
1 12th     1
2 13th    10
3 14th    15

[[2]]
  week value
1 12th     1
2 13th    20
3 14th   100

[[3]]
  week value
1 12th     1
2 13th   220
3 14th    30

And I would like to have a final dataframe with combined data, the shape of which is as follows

finalDf<- data.frame(week=c("12th","13th","14th"),value1=c(1,20,100),value2=c(1,10,15),value3=c(1,220,30))

  week value1 value2 value3
1 12th      1      1      1
2 13th     20     10    220
3 14th    100     15     30

How could I achieve the above form of data? Also, what if my initial dataframes also have NAs, which I would like to remove prior to achieving the final form of data?

Many, many thanks.

IRTFM · Accepted Answer · 2014-08-04T17:43:05.603

I see the cbind strategies, but they may fail if there are missing values, so I thought a merge approach should be illustrated:

 Reduce( function(x,y) merge(x, y, by="week"), dfList)
  week value.x value.y value
1 12th       1       1     1
2 13th      10      20   220
3 14th      15     100    30

If you wanted to keep all the potential NA values then perhaps adding an , all.x=TRUE argument would be needed.

score 3 · Answer 2 · answered Aug 03 '14 at 20:19

> cbind(dfList[[1]], lapply(dfList[2:3], `[`, "value"))
  week value value value
1 12th     1     1     1
2 13th    10    20   220
3 14th    15   100    30

It seems the numbering of your dataframes is not the same in your data setup and your intended result, but any version of this code (changing the relevant extraction indices in [ and [[) will get you to your intended structure.

akrun · Answer 3 · 2014-08-04T06:45:16.550

2

You could try:

 library(plyr)
 join_all(dfList, by="week")
 #  week value value value
 #1 12th     1     1     1
 #2 13th    10    20   220
 #3 14th    15   100    30

Also, works with NA Using @Frank's data

  res <- join_all(dfList, by="week")
  res
  #  week value value value
  #1 12th     1    NA     1
  #2 13th    10    NA   220
  #3 14th    15    NA    NA

  str(res)
 #'data.frame': 3 obs. of  4 variables:
 # $ week : Factor w/ 3 levels "12th","13th",..: 1 2 3
 #$ value: num  1 10 15
 #$ value: logi  NA NA NA
 #$ value: num  1 220 NA  ##numeric columns

edited Aug 04 '14 at 06:45

answered Aug 04 '14 at 06:33

akrun

874,273
37
540
662

so simple! I am overjoyed for learning about so many different approaches! Thanks! – info_seekeR Aug 08 '14 at 12:20

score 2 · Answer 4 · answered Aug 05 '14 at 05:49

There are some very nice answers already, but here's another:

Step 1: Combine your data.frames into a long list:

dfDF <- do.call(rbind, dfList)

Step 2: Add a "time" variable that indicates the list from which the data came. There are a few ways to do that....

with(dfDF, ave(as.character(week), week, FUN = seq_along))
# [1] "1" "1" "1" "2" "2" "2" "3" "3" "3"

rep(sequence(length(dfList)), vapply(dfList, nrow, 1L))
# [1] 1 1 1 2 2 2 3 3 3

dfDF$time <- with(dfDF, ave(as.character(week), week, FUN = seq_along))

Step 3: Use dcast to go from "long" to "wide".

library(reshape2)
dcast(dfDF, week ~ time, value.var = "value")
#   week  1   2   3
# 1 12th  1   1   1
# 2 13th 10  20 220
# 3 14th 15 100  30

Jota · Answer 5 · 2014-08-03T23:23:31.007

df <- data.frame(t(unique(t(do.call(cbind, dfList)))), stringsAsFactors = FALSE)
df
#  week value value.1 value.2
#1 12th     1       1       1
#2 13th    10      20     220
#3 14th    15     100      30

If you want the value columns to be numeric instead of characters:

df[2:4] <- sapply(df[2:4], as.numeric)
df
#  week value value.1 value.2
#1 12th     1       1       1
#2 13th    10      20     220
#3 14th    15     100      30

Also, works with NAs:

d2<- data.frame(week=c("12th","13th","14th"),value=c(NA,NA,NA))
d1<- data.frame(week=c("12th","13th","14th"),value=c(1,10,15))
d3<- data.frame(week=c("12th","13th","14th"),value=c(1,220,NA))
dfList<- list(d1,d2,d3)

df <- data.frame(t(unique(t(do.call(cbind, dfList)))), stringsAsFactors = FALSE)
df
#  week value value.1 value.2
#1 12th     1    <NA>       1
#2 13th    10    <NA>     220
#3 14th    15    <NA>    <NA>

Extracting columns from dataframes in a list to produce a final, combined dataframe

5 Answers5