John0101 John0101 - 9 months ago 33
R Question

How to read and name different CSV files in R

I would like to work on several csv files to make some comparisons, so I wrote this code to read the different csv files I have:

path <- "C:\\data\\"
files <- list.files(path=path, pattern="*.csv")
for(file in files)
perpos <- which(strsplit(file, "")[[1]]==".")
gsub(" ","",substr(file, 1, perpos-1)),

My csv files are something like this:

Start Time,End Time,Total,Diffuse,Direct,Reflected
04/09/14 00:01:00,04/09/14 00:01:00,2.221220E-003,5.797364E-004,0.000000E+000,1.641484E-003,
04/09/14 00:02:00,04/09/14 00:02:00,2.221220E-003,5.797364E-004,0.000000E+000,1.641484E-003,
04/09/14 00:03:00,04/09/14 00:03:00,2.221220E-003,5.797364E-004,0.000000E+000,1.641484E-003,

Using my code, R separate correctly all files, but for each of them it creates a table adding a more extra space at the beginning:

|Start Time |End Time |Total |Diffuse |Direct |Reflected
04/09/14 00:01:00|04/09/14 00:01:00|2.221220E-003|5.797364E-004|0.000000E+000|1.641484E-003|NA

How can I fix it?

Moreover, considering that the original name of each file is really long, is it possible to name each data.frame using the last letters of the file? Or just a cardinal number?

Answer Source

You're having a problem because your csv files have a blank column at the end... which makes your data end in a comma:

04/09/14 00:01:00,04/09/14 00:01:00,2.221220E-003,5.797364E-004,0.000000E+000,1.641484E-003,

This leads R to think your data consists of 7 columns rather than 6. The correct solution is to resave all your csv files correctly. Otherwise, R will see 7 columns but only 6 columnnames, and will logically think that the first column is rownames. Here you can apply the patch we came up with @konradrudolph:

library(tibble) df %>% rownames_to_column() %>% setNames(c(colnames(.)[-1], 'DROP')) %>% select(-DROP)

where df is the data from the csv. But patches like this can lead to unexpected results... better save the csv files correctly.