how to grab specific columns from multiple data.frame and save it as a new data.frame in R?

user_10226369 0
Viewed 76

I have the following example data.frame which contains multiple variables (i.e., A,B,C).

set.seed(123)

D1 <- data.frame(Date = seq(as.Date("2001-01-01"), to= as.Date("2001-01-10"), by="day"),
                 A = runif(10,1,5),
                 B = runif(10,3,6),
                 C = runif(10,2,5))
D2 <- data.frame(Date = seq(as.Date("2001-01-01"), to= as.Date("2001-01-10"), by="day"),
                 A = runif(10,1,5),
                 B = runif(10,3,6),
                 C = runif(10,2,5))
D3 <- data.frame(Date = seq(as.Date("2001-01-01"), to= as.Date("2001-01-10"), by="day"),
                 A = runif(10,1,5), 
                 B = runif(10,3,6), 
                 C = runif(10,2,5))

Target I want to grab each variables from all the data.frame and save it as a new data.frame with the name set as Variable and column names set as data.frame+variable like below

A <- data.frame(Date, D1A,D2A,D3A)
B <- data.frame(Date,D1B,D2B,D3B)
C <- data.frame(Date,D1C,D2C,D3C)

I would appreciate any help here.

tidyverse reshape dataframe r data-binding
2 Answers

We get the datasets in a list (without the first column 'Date'), transpose, then loop over the list with map, bind the 'Date' column in each of those datasets (it is better to keep it as a list, but if needed use list2env to create the objects in the global env)

library(dplyr)
library(purrr)
list(D1 = D1[-1], D2 = D2[-1], D3 = D3[-1]) %>%
     transpose %>%
     map(~ bind_cols(D1['Date'], .)) %>%
    list2env(.GlobalEnv)

-check the objects A, B, C created

head(A, 2)
#        Date       D1       D2       D3
#1 2001-01-01 2.150310 4.852097 3.660461
#2 2001-01-02 4.153221 4.609196 1.379363
head(B, 2)
#        Date       D1       D2       D3
#1 2001-01-01 5.870500 3.428400 5.263425
#2 2001-01-02 4.360002 4.243639 4.887663
head(C, 2)
#        Date       D1       D2       D3
#1 2001-01-01 4.668618 2.137494 2.730858
#2 2001-01-02 4.078410 3.326600 4.004167
user_37322710

Here is a base R option

lst <- mget(ls(pattern = "^D\\d+"))
list2env(
  sapply(
    names(lst[[1]])[-1],
    function(x) {
      cbind(
        lst[[1]]["Date"],
        list2DF(lapply(lst, `[[`, x))
      )
    },
    USE.NAMES = TRUE,
    simplify = FALSE
  ),
  envir = .GlobalEnv
)
user_121587570
Related