List of data frames to nested list based on names - R

Question

I have a list of data frames like this:

dflist <- list(
  X2013.11.14.Date = data.frame(replicate(2,sample(0:1,5,rep=TRUE))), X2013.11.14.Treatment = data.frame(replicate(2,sample(0:1,5,rep=TRUE))),
  X2013.11.14.Value = data.frame(replicate(2,sample(0:1,5,rep=TRUE))), X2014.08.12.Date = data.frame(replicate(2,sample(0:1,5,rep=TRUE))),
  X2014.08.12.Treatment = data.frame(replicate(2,sample(0:1,5,rep=TRUE))), X2014.08.12.Value = data.frame(replicate(2,sample(0:1,5,rep=TRUE))))

Within the list, each data frame is named with a date combined with a description like this:

names(dflist)
#[1] "X2013.11.14.Location"      "X2013.11.14.Treatment" "X2013.11.14.Value"    "X2014.08.12.Location"     
#[5] "X2014.08.12.Treatment" "X2014.08.12.Value"

I would like to combine the individual data frames into new, larger data frames using the date portion of their names (e.g. X2013.11.14), keeping them within the list and if possible having the date as the list element name and the description (e.g. Location) as the column names. I'm not even sure what to try. Thanks.

MKR · Accepted Answer · 2018-04-12 06:50:02Z

3

An option using dplyr::bind_rows and tidyr::separate arrange data in a dataframe with date/time.

This solution is in similar line of that provided by @Frank.

library(tidyverse)
library(lubridate)

bind_rows(dflist, .id = "ID") %>%
  separate(ID, c("Year", "Month", "Day", "Type"), sep = "\\.") %>%
  mutate(Year = gsub("X","",Year)) %>%
  unite( "Date", Year, Month, Day, sep = "-") %>%
  mutate(Date = ymd(Date)) 

#          Date      Type X1 X2
# 1  2013-11-14      Date  0  0
# 2  2013-11-14      Date  0  0
# 3  2013-11-14      Date  0  1
# 4  2013-11-14      Date  0  1
# 5  2013-11-14      Date  1  0
# 6  2013-11-14 Treatment  0  0
# 7  2013-11-14 Treatment  1  0
# 8  2013-11-14 Treatment  0  0
# 9  2013-11-14 Treatment  0  0
# 10 2013-11-14 Treatment  1  0
# ...so on

edited Apr 12, 2018 at 6:50

answered Apr 11, 2018 at 23:10

MKR

20.2k4 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MKR Over a year ago

Thanks @Frank. Yes. I should modify Date column to represent date format. I'll update it now.

Frank · Accepted Answer · 2018-04-11 22:46:14Z

I'd do...

patt = "^X(.{10}).(.*)$"

library(data.table)
DT = rbindlist(dflist, id="name")

DT[, `:=`(
  date = as.IDate(sub(patt, "\\1", name), format = "%Y.%m.%d"),
  var = sub(patt, "\\2", name),
  name = NULL
)]

which gives...

    X1 X2       date       var
 1:  0  0 2013-11-14      Date
 2:  0  1 2013-11-14      Date
 3:  1  1 2013-11-14      Date
 4:  1  0 2013-11-14      Date
 5:  1  1 2013-11-14      Date
 6:  1  0 2013-11-14 Treatment
 7:  0  0 2013-11-14 Treatment
 8:  1  1 2013-11-14 Treatment
 9:  0  1 2013-11-14 Treatment
10:  1  0 2013-11-14 Treatment
11:  1  0 2013-11-14    Values
12:  1  1 2013-11-14    Values
13:  0  0 2013-11-14    Values
14:  1  0 2013-11-14    Values
15:  1  1 2013-11-14    Values
16:  0  1 2014-08-12      Date
17:  1  1 2014-08-12      Date
18:  1  0 2014-08-12      Date
19:  1  1 2014-08-12      Date
20:  1  1 2014-08-12      Date
21:  0  0 2014-08-12 Treatment
22:  0  0 2014-08-12 Treatment
23:  0  0 2014-08-12 Treatment
24:  0  1 2014-08-12 Treatment
25:  1  1 2014-08-12 Treatment
26:  1  0 2014-08-12     Value
27:  1  0 2014-08-12     Value
28:  0  0 2014-08-12     Value
29:  0  0 2014-08-12     Value
30:  1  0 2014-08-12     Value
    X1 X2       date       var

By the way, we see here that the names are not consistent (Value vs Values).

Why one table instead of a list with dates as names? When dates are stored as names, they are not dates -- they are just strings -- and you cannot use standard functions like month() or difftime() to work on them. If you really want a list, there's still...

listDT = DT[, list(tab = list(.SD)), by=date]

         date          tab
1: 2013-11-14 <data.table>
2: 2014-08-12 <data.table>

This way, dates are still stored correctly and you have a list of tables (in the second column). To access each table, syntax like listDT[date == "2013-11-14", tab[[1]]] works.

Collectives™ on Stack Overflow

List of data frames to nested list based on names - R

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related