Extracting the same column from multiple data frames and cbind them into a new data frame in R

Question

I'm looking for a line of code that will allow me to extract a column of the same name from multiple data frames and cbind them into a single dataframe. I'm also looking to name each column in the new dataframe after the dataframe it came from.

Below is the code I've been using with reproducible data. I've been trying do.call however I can't get it to work:

Asset   <- structure(c(63.281303433027, 63.3979720475464, 63.6714334032718, 
            62.9559893597375, 63.0078420773017, 62.8893215800121, 31.6989860237732, 
            31.8357167016359, 31.4779946798687, 31.5039210386508, 31.4446607900061, 
            31.0492838185792, 63.3979720475464, 63.6714334032718, 62.9559893597375, 
            63.0078420773017, 62.8893215800121, 62.0985676371584), 
            class = c("xts","zoo"), .indexCLASS = "Date", .indexTZ = "UTC", tclass = "Date", tzone = "UTC", 
            index = structure(c(1550534400, 1550620800, 1550707200, 1550793600, 1551052800, 1551139200),tzone = "UTC", tclass = "Date"), .Dim = c(6L, 3L), 
            .Dimnames = list(NULL, c("Beginning.Value", "Unit.Price", "Ending.Value")))

Register<- structure(c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 212.156319855224, 
            213.718845942538, 211.63547782612, 211.809091835821, 211.63547782612, 
            207.989583622389),
            class = c("xts", "zoo"), .indexCLASS = "Date", .indexTZ = "UTC", tclass = "Date", tzone = "UTC", 
            index = structure(c(1550534400,1550620800, 1550707200, 1550793600, 1551052800, 1551139200), tzone = "UTC", tclass = "Date"), .Dim = c(6L, 3L), 
            .Dimnames = list(NULL, c("Amount", "Taxes", "Ending.Value")))

Ledger<- structure(c(0.994402284972246, 1.00685740995534, 0.991497559782253, 
            1.00156143848816, 1.00071020618011, 0.995451606923588, 161.592601088027, 
            160.688051756542, 161.789955602362, 160.414346177021, 160.664823311196, 
            160.778928461638, 160.688051756542, 161.789955602362, 160.414346177021, 
            160.664823311196, 160.778928461638, 160.04764269659), class = c("xts", "zoo"), .indexCLASS = "Date", .indexTZ = "UTC", tclass = "Date", tzone = "UTC", 
            index = structure(c(1550534400, 1550620800, 1550707200, 1550793600, 1551052800, 1551139200), tzone = "UTC", tclass = "Date"), .Dim = c(6L, 3L), 
            .Dimnames = list(NULL, c("Discount_Proxy", "Beginning.Value","Ending.Value")))

dfs <- data.frame(c("Ledger","Registry","Ledger"))
names(dfs) <- "Data Frame"

Values <- do.call('cbind', list(dfs[,1]$Ending.Value))

score 3 · Answer 1 · answered Oct 11 '21 at 01:39

A tidyverse-based idea that maybe didn't exist when this question was asked, but should scale well. The only place you need to specify dataset names is in bundling the data frames into a list; in a situation that returns a list of data frames (such as reading files from a directory) you wouldn't even need to do it there. tibble::lst is a wrapper around the base list that names values by their original object names. {{ ds }} := Ending.Value is tidyeval notation to rename the Ending.Value column dynamically based on the value in the variable ds, which in this case is the name of the dataset.

library(dplyr)
library(xts)

tibble::lst(Asset, Register, Ledger) %>%
  purrr::map(as.data.frame) %>%
  purrr::imap(function(dat, ds) select(dat, {{ ds }} := Ending.Value)) %>%
  bind_cols()
#>               Asset Register   Ledger
#> 2019-02-19 63.39797 212.1563 160.6881
#> 2019-02-20 63.67143 213.7188 161.7900
#> 2019-02-21 62.95599 211.6355 160.4143
#> 2019-02-22 63.00784 211.8091 160.6648
#> 2019-02-25 62.88932 211.6355 160.7789
#> 2019-02-26 62.09857 207.9896 160.0476

score 2 · Accepted Answer · answered Feb 28 '19 at 22:21

2

If you don't mind naming the data.frames in your list:

list_ls <- list("Asset" = Asset, "Register" = Register, "Ledger" = Ledger)

foo <- do.call(cbind, lapply(list_ls, function(x) x$Ending.Value))

test <- cbind(Asset$Ending.Value, Register$Ending.Value, Ledger$Ending.Value)
colnames(test) <- c("Asset", "Register", "Ledger")

length(which(foo != test))

answered Feb 28 '19 at 22:21

Qwfqwf

483
3
9

Works brilliantly, thank you. I've tried a few ways of automating the list_ls, is there a way of coding something so that I don't have to manually create the list. The work I'm doing will have an ever increasing amount of dataframes. Thank you again – Tom Feb 28 '19 at 23:43
Maybe [this](https://stackoverflow.com/questions/14577412/how-to-convert-variable-object-name-into-string) post can help? Depending on exactly where your data are coming from you could use that method to make a name each time and then add it to the list. Not sure if that makes sense! – Qwfqwf Feb 28 '19 at 23:49

Extracting the same column from multiple data frames and cbind them into a new data frame in R

2 Answers2