-1

I am looping through multiple files where each file creates a table. I would like to save each table to a file so that I have all of the tables together. The idea is that after each file runs, the final output will append to the previous final output in a table. My current code loops through each file but only takes the data from the last file in the folder and repeatedly appends to the same output. Here is my code:

filenames <- Sys.glob("*.xlsx")
print(filenames)

final.df<-data.table()
for(i in 1:length(filenames)) {
   #... final table named "Stats"

   Stats$file <- i   #To view which file is listed
   df <- data.table(Stats)
   final.df <- rbind(final.df, df)
   drop(df)
}

When I run the code, the file names are printed 26 times in this format:

[1] "file1.xlsx"          "file2.xlsx"
[1] "file1.xlsx"          "file2.xlsx"
[1] "file1.xlsx"          "file2.xlsx"
...

My current output prints in this format:

X        Y        Z       File
1        10       2       file2
2        6        2       file2
1        9        2       file2
1        10       2       file2
2        6        2       file2
1        9        2       file2

My desired output would look like the following:

X        Y        Z       File
0        4        1       file1
1        7        1       file1
0        1        1       file1
1        10       2       file2
2        6        2       file2
1        9        2       file2

SOLUTION

files <- list.files(pattern = "\\.xlsx$")
print(files)

final.df<-list() 

for(title in c(paste(files, sep="."))) {
   #... (Some data named Stats)
   Stats<-data.table(Stats)
   final.df[[title]] <- Stats
   print(final.df)
 }
 Final <- rbindlist(final.df)
SoccerAnalytics26
  • 171
  • 3
  • 12
  • This is probably answered and is duplicated. Check this out https://stackoverflow.com/questions/24819433/reading-multiple-csv-files-from-a-folder-into-a-single-dataframe-in-r – user5249203 Nov 13 '17 at 20:27
  • Some more inspiration: https://stackoverflow.com/q/32888757/2204410 – Jaap Nov 13 '17 at 20:34
  • I know how to read each individual file, I am looking to append the output I get after reading the file. I believe these solutions append the original files together, which is not exactly what I am looking for. – SoccerAnalytics26 Nov 13 '17 at 20:42

1 Answers1

0

Without your data, I think this should work:

listOut <- list()
for(i in 1:length(filenames)) {
  df <- data.table(filenames[i])
  listOut[[i]] <- df
}

dfFinal <- rbindlist(listOut)

The idea is to make a dataframe and append it to a list for each of your files. Then you can rbind all of the list elements together. This is much more efficient than trying to append to an existing dataframe.

IanK
  • 376
  • 1
  • 8
  • This answer makes sense, but my output is still taking the last file in my folder and appending a duplicate result. – SoccerAnalytics26 Nov 13 '17 at 20:49
  • Yeah, I'm having a hard time imagining this without seeing the data. I made an edit above. If that doesn't work, can you share the files somewhere? – IanK Nov 13 '17 at 21:14
  • I had to do something a little different with the loop line, but otherwise this was a great solution. – SoccerAnalytics26 Nov 14 '17 at 15:52