Dataframes in a list; adding a new variable with name of dataframe
dataframe, lapply, list, names, r
Solution
Personally, I think it's easier to add the names after collapse:
df1 <- data.frame(x=c(1:5),y=c(11:15))
df2 <- data.frame(x=c(1:5),y=c(11:15))
mylist <- list(df1 = df1, df2 = df2)
all <- do.call("rbind", mylist)
all$id <- rep(names(mylist), sapply(mylist, nrow))
Problem
I have a list of dataframes which I eventually want to merge while maintaining a record of their original dataframe name or list index. This will allow me to subset etc across all the rows. To accomplish this I would like to add a new variable 'id' to every dataframe, which contains the name/index of the dataframe it belongs to. Edit: "In my real code the dataframe variables are created from reading multiple files using the following code, so I don't have actual names only those in the 'files.to.read' list which I'm unsure if they will align with the dataframe order: ``` mylist <- llply(files.to.read, read.csv) ``` A few methods have been highlighted in several posts: Working-with-dataframes-in-a-list-drop-variables-add-new-ones and Using-lapply-with-changing-arguments I have tried two similar methods, the first using the index list: ``` df1 <- data.frame(x=c(1:5),y=c(11:15)) df2 <- data.frame(x=c(1:5),y=c(11:15)) mylist <- list(df1,df2) # Adds a new coloumn 'id' with a value of 5 to every row in every dataframe. # I WANT to change the value based on the list index. mylist1 <- lapply(mylist, function(x){ x$id <- 5 return (x) } ) #Example of what I WANT, instead of '5'. #> mylist1 #[[1]] #x y id #1 1 11 1 #2 2 12 1 #3 3 13 1 #4 4 14 1 #5 5 15 1 # #[[2]] #x y id #1 1 11 2 #2 2 12 2 #3 3 13 2 #4 4 14 2 #5 5 15 2 ``` The second attempts to pass the names() of the list. ``` # I WANT it to add a new coloumn 'id' with the name of the respective dataframe # to every row in every dataframe. mylist2 <- lapply(names(mylist), function(x){ portfolio.results[[x]]$id <- "dataframe name here" return (portfolio.results[[x]]) } ) #Example of what I WANT, instead of 'dataframe name here'. # mylist2 #[[1]] #x y id #1 1 11 df1 #2 2 12 df1 #3 3 13 df1 #4 4 14 df1 #5 5 15 df1 # #[[2]] #x y id #1 1 11 df2 #2 2 12 df2 #3 3 13 df2 #4 4 14 df2 #5 5 15 df2 ``` But the names() function doesn't work on a list of dataframes; it returns NULL. Could I use seq_along(mylist) in the first example. Any ideas or better way to handle the whole "merge with source id" Edit - Added Solution below: I've implemented a solution using Hadleys suggestion and Tommy’s nudge which looks something like this. ``` files.to.read <- list.files(datafolder, pattern="\\_D.csv$", full.names=FALSE) mylist <- llply(files.to.read, read.csv) all <- do.call("rbind", mylist) all$id <- rep(files.to.read, sapply(mylist, nrow)) ``` I used the files.to.read vector as the id for each dataframe I also changed from using merge_recurse() as it was very slow for some reason. ``` all <- merge_recurse(mylist) ``` Thanks everyone.