Search code examples
rdataframecbind

Add columns to specific rows in a list of data frames from a data frame in R

I want to add two columns next to the headers of the list mentioned below (except the 1st data frame).

Structure of the list of data frames multidf where I want to add the columns

structure(list(`0` = structure(list(X01.04.2020 = 0:1, Asset = c("PORTFOLIO", "CASH"), Position = c("--", "--"), Price = c("--", "--"), Mark.to.Market = c(1000000L, 1000000L)), class = "data.frame", row.names = c(NA, -2L)), `1` = structure(list(X02.04.2020 = c(0L, 1L, 2L, 3L, NA), Asset = c("PORTFOLIO", "CASH", "FUTURES", "VXc1", "Total"), Position = c("--", "--", "500", "-5931", "Buys:"), Price = c("--", "--", "2516", "47", "1"), Mark.to.Market = c("999231", "509866", "1258250", "-279795", "Sells:"), Position.prior = c(NA, NA, 0L, 0L, 1L), Transaction = c("", "", "500", "-5931", "TC spent:"), TC.spent = c(NA, NA, 629L, 140L, 769L)), class = "data.frame", row.names = c(NA, -5L)), `2` = structure(list(X03.04.2020 = c(0L, 1L, 2L, 3L, NA), Asset = c("PORTFOLIO", "CASH", "FUTURES", "VXc1", "Total"), Position = c("--", "--", "505", "-6206", "Buys:"), Price = c("--", "--", "2483", "45", "1"), Mark.to.Market = c("995708", "508078", "1253789", "-278805", "Sells:"), Position.prior = c(NA, NA, 500L, -5931L, 1L), Transaction = c("", "", "5", "-275", "TC spent:"), TC.spent = c(NA, NA, 6L, 6L, 12L)), class = "data.frame", row.names = c(NA, -5L)), `3` = structure(list(X06.04.2020 = c(0L, 1L, 2L, 3L, NA), Asset = c("PORTFOLIO", "CASH", "FUTURES", "VXc1", "Total"), Position = c("--", "--", "522", "-7352", "Buys:"), Price = c("--", "--", "2644", "42", "1"), Mark.to.Market = c("1096915", "559989", "1380429", "-307130", "Sells:"), Position.prior = c(NA, NA, 505L, -6206L, 1L), Transaction = c("", "", "17", "-1146", "TC spent:"), TC.spent = c(NA, NA, 22L, 24L, 46L)), class = "data.frame", row.names = c(NA, -5L)), `4` = structure(list(X07.04.2020 = c(0L, 1L, 2L, 3L, NA), Asset = c("PORTFOLIO", "CASH", "FUTURES", "VXc1", "Total"), Position = c("--", "--", "514", "-6863", "Buys:"), Price = c("--", "--", "2642", "44", "1"), Mark.to.Market = c("1079069", "550870", "1357988", "-302144", "Sells:"), Position.prior = c(NA, NA, 522L, -7352L, 1L), Transaction = c("", "", "-8", "489", "TC spent:"), TC.spent = c(NA, NA, 11L, 11L, 21L)), class = "data.frame", row.names = c(NA, -5L)), `5` = structure(list(X08.04.2020 = c(0L, 1L, 2L, 3L, NA), Asset = c("PORTFOLIO", "CASH", "FUTURES", "VXc1", "Total"), Position = c("--", "--", "524", "-7521", "Buys:"), Price = c("--", "--", "2735", "42", "1"), Mark.to.Market = c("1138189", "580800", "1433140", "-318702", "Sells:"), Position.prior = c(NA, NA, 514L, -6863L, 1L), Transaction = c("", "", "10", "-658", "TC spent:"), TC.spent = c(NA, NA, 14L, 14L, 28L)), class = "data.frame", row.names = c(NA, -5L))), .Dim = 6L, .Dimnames = list(`cumsum(!grepl("\\S", txt))` = c("0", "1", "2", "3", "4", "5")))

This is the expected result Reproducible Example

Structure of data frame df3 (the last two columns from reproducible example) that needs to be added to the list of data frames as shown above

structure(c("DOWN", "DOWN", "DOWN", "DOWN", "DOWN", "FLAT", "UP", "UP", "UP", "UP", "UP", "UP"), .Dim = c(6L, 2L))

I have used the following code to get the list of data frames multidf and data frame df3

#Load the two input files in a data frame
df1 <- read.csv("SP500Preds_20210202.csv")
df2 <- read.csv("VIXPredPerfectForesight_20210213.csv")
#Extract and combine the required items to be loaded in final output
df3 <- cbind(df1[, c(3)], df2[, c(3)])
#Load the file that needs to be updated
txt <- readLines("ProcessedTrades.csv")
multidf <- by(txt, cumsum(!grepl("\\S", txt)), 
  FUN = function(x) read.csv(text = x, strip.white = TRUE))

In order to get the expected result, I have used the following code

multidf[-1] <- Map(cbind, multidf[-1], asplit(df3, 1))

I am getting the following error

multidf[-1] <- Map(cbind, multidf[-1], asplit(df3, 1)) Error in data.frame(..., check.names = FALSE) : arguments imply differing number of rows: 5, 2

Can anyone help me with this? Thanks in advance.

adding reproducible examples of CSV files below

SP500Preds_20210202.csv or df1

structure(list(date = c("04/06/19", "05/06/19", "06/06/19", "07/06/19", "10/06/19", "11/06/19"), prediction_for_date = c("11/6/2019", "12/6/2019", "13/06/19", "14/06/19", "17/06/19", "18/06/19"), Predictions = c("DOWN", "DOWN", "DOWN", "DOWN", "DOWN", "FLAT")), row.names = c(NA, 6L), class = "data.frame")

VIXPredPerfectForesight_20210213.csv or df2

structure(list(date = c(20190604L, 20190605L, 20190606L, 20190607L, 20190610L, 20190611L), X1.Day.Pred = c("DOWN", "DOWN", "UP", "DOWN", "UP", "DOWN"), X5.Day.Pred = c("UP", "UP", "UP", "UP", "UP", "UP")), row.names = c(NA, 6L), class = "data.frame")
Solution

  • There are 6 elements (data.frame) in the list multidf and there are 6 rows in the matrix 'df3'. So, if the intention is to create two new columns in each of the list elements

    multidf2 <- Map(cbind, multidf, lapply(asplit(df3, 1), as.list))

    Or with apply

    multidf2 <- Map(cbind, multidf, apply(df3, 1, as.list))

    If we need to create only two blank columns with column names as in 'df3' rows

    multidf2 <-  Map(function(x, y) {x[y] <- ''; x}, multidf, asplit(df3, 1))