row bind multiple dataframes and add name of originating dataframe into column

Question

I have numerous dataframes in my environment:

x1 <- structure(list(time = structure(c(1327241343, 1327327803, 1327414263
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), x1 = c(22.5, 
12, 0)), .Names = c("time", "x1"), class = c("tbl_df", "tbl", 
"data.frame"), row.names = c(NA, -3L))

x2 <- structure(list(time = structure(c(1326636543, 1326636603, 1326636663
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), x2 = c(8, 
6, 1)), .Names = c("time", "x2"), class = c("tbl_df", "tbl", 
"data.frame"), row.names = c(NA, -3L))

x3 <- structure(list(time = structure(numeric(0), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), x3 = numeric(0)), .Names = c("time", 
"x1"), class = c("tbl_df", "tbl", "data.frame"), row.names = integer(0))

##-----------------------------------------------------------------------------
## PREVIEW
##-----------------------------------------------------------------------------
> knitr::kable(x1)

|time                |   x1|
|:-------------------|----:|
|2012-01-22 14:09:03 | 22.5|
|2012-01-23 14:10:03 | 12.0|
|2012-01-24 14:11:03 |  0.0|

> knitr::kable(x2)

|time                | x2|
|:-------------------|--:|
|2012-01-15 14:09:03 |  8|
|2012-01-15 14:10:03 |  6|
|2012-01-15 14:11:03 |  1|

> knitr::kable(x3)

|time | x1|
|:----|--:|

Notice that x3 is an empty data frame, since this reflects my scenario. I'm trying to obtain the following row binded single dataframe:

x.all <- structure(list(time = structure(c(1327241343, 1327327803, 1327414263, 
1326636543, 1326636603, 1326636663), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), x = c(22.5, 12, 0, 8, 6, 1), which = c("x1", 
"x1", "x1", "x2", "x2", "x2")), .Names = c("time", "x", "which"
), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-6L))

##-----------------------------------------------------------------------------
## PREVIEW
##-----------------------------------------------------------------------------
> knitr::kable(x.all)

|time                |    x|which |
|:-------------------|----:|:-----|
|2012-01-22 14:09:03 | 22.5|x1    |
|2012-01-23 14:10:03 | 12.0|x1    |
|2012-01-24 14:11:03 |  0.0|x1    |
|2012-01-15 14:09:03 |  8.0|x2    |
|2012-01-15 14:10:03 |  6.0|x2    |
|2012-01-15 14:11:03 |  1.0|x2    |

I know how to do this one by one. However, with more than 100 dataframes and I'm looking for a way to do this efficiently (each dataframe contains 2 columns and >500,000 rows).

Thanks.

CPak · Accepted Answer · 2017-10-21T12:21:43.657

This should work for you, using tidyverse. Use get to grab the data by name.

listofdf <- paste0("x", 1:3)
# "x1" "x2" "x3"

library(tidyverse)
map_df(listofdf, ~get(.x) %>% setNames(c("time","x")), .id="which") %>%
  mutate(which = paste0("x", which)) %>%
  select(time, x, which)

# # A tibble: 6 x 3
                 # time     x which
               # <dttm> <dbl> <chr>
# 1 2012-01-22 14:09:03  22.5    x1
# 2 2012-01-23 14:10:03  12.0    x1
# 3 2012-01-24 14:11:03   0.0    x1
# 4 2012-01-15 14:09:03   8.0    x2
# 5 2012-01-15 14:10:03   6.0    x2
# 6 2012-01-15 14:11:03   1.0    x2

Edit Work with data that does not always start with same pattern

You need to make 2 modifications

Get data in your R environment with ls()

listofdf <- ls()
# "x1" "x2" "x3"

Get ids with mutate(which = listofdf[as.integer(which)]) rather than mutate(which = paste0("x", which))

map_df(listofdf, ~get(.x) %>% setNames(c("time","x")), .id="which") %>%
  mutate(which = listofdf[as.integer(which)]) %>%   # 2nd change
  select(time, x, which)

Thanks. This nearly works. Can you edit for the case when the x variable cannot be constructed from a pattern (i.e. `paste0("x", 1:3)` ) ? Happy to accept it as an answer. — Thomas Speidel, Oct 20 '17 at 21:23

row bind multiple dataframes and add name of originating dataframe into column

1 Answers1