-2

I have 20 .CSV files and all of them have equal number of row/col (1 row and 42 columns). I want to make a dataframe out of all them and have each CSV file as one row of my dataframe and have the name of the CSV file as my row name. Is it even possible to do this?

To illustrate this with an example:

A.csv
10 21 32 45

B.csv
33 45 93 90

C.csv
12 93 Na 21

Resulting dataframe I am looking for would be:

A 10 21 32 45
B 33 45 93 90
C 12 93 Na 21
hunajujiw
  • 5
  • 2

2 Answers2

3

Both data.table (rbindlist) and dplyr (bind_rows) have functions to do this. My preferred solution would be to use readr::read_csv together with dplyr::bind_rows to do this: 

library(readr)
library(dplyr)

bind_rows(
  lapply(
    list.files(
      "path/to/csv_files", 
      pattern = ".csv", 
      full.names = TRUE
    ), 
    read_csv, 
    header = FALSE, 
    na_strings = c("Na")
  )
)
tchakravarty
  • 10,736
  • 12
  • 72
  • 116
2

You can try to use the list.files() function combined with lapply which loop through all the files and read them as data.frame. Finally, a do.call(rbind ... combine separate dataframes together:

do.call(rbind, lapply(list.files(PathToCsvFile, pattern = ".csv", full.names = T), read.csv))

Upadate:

filePaths <- list.files(PathToCsvFile, pattern = ".csv", full.names = T)
cbind(gsub(".csv", "", basename(filePaths)), do.call(rbind, lapply(filePaths, read.csv)))
Psidom
  • 209,562
  • 33
  • 339
  • 356
  • Thank you for your answer. But for some reason this is not working and shows an error: `Error in file(file, "rt") : cannot open the connection In addition: Warning message: In file(file, "rt") : cannot open file 'D38hz6lVkkxE.csv': No such file or directory` I checked the file and it is working perfectly fine and is not corrupted. – hunajujiw May 28 '16 at 19:33
  • The PathToCsvFile should be the directory where your csv files are located. – Psidom May 28 '16 at 19:38
  • You might need `full.names = TRUE` in `list.files()` – Rich Scriven May 28 '16 at 19:39
  • That's exactly what I did, and it looks like it was able to access the directory and find the name of the first file in that folder, I wrote the directory as `"C:/Documents/CSVs"` – hunajujiw May 28 '16 at 19:40
  • Taking @RichardScriven's suggestion, specify the `full.names = T` in the `list.files()` function. – Psidom May 28 '16 at 19:43
  • Both the answers ignored the requirement of having the source csv filename as in a column. – Frash May 28 '16 at 20:10
  • Adding `full.names=T` and 'header=FALSE` gave me good results, but as Frash mentioned I didn't get the CSV names to be the row names. – hunajujiw May 28 '16 at 20:31
  • You can extract the file names and cbind with the data frame you just get. See the updated answer. – Psidom May 28 '16 at 20:37
  • Just fyi, you don't have to explicitly specify the `header = F` since that is the default. – Psidom May 28 '16 at 20:40
  • Not using `header=FALSE` gives me only one row! – hunajujiw May 28 '16 at 23:11