Reading, merging & sorting .csv files

Question

I'm very new to R but do program. I'm probably just getting fed up with my own progress at this stage, so here's my issue;

Lots of .csv files, large (6MB) with spectrum data that I need to do analysis afterwards. I'm trying to read in the data - two columns of Frequency and Voltage (V as dB values), 500,000 data points per file. I would like to "merge" the data from the 2nd column in a new data set for every 10 files.

Eg: 10 files, ten Frequency (all the same for each so can be ignored for the moment) and ten Voltage. Take the data from the Voltage in the 2nd column and merge it into a data set. If I have 10 files = I end up with one data set, 100 files = 10 data sets. Hopefully in the end each data set will have 11 columns | Frequency | V1 | V2 | ... | V10 |. It would be nice to do an Index-Match on each file but I'm not sure my PC will be able for it until I upgrade resources.

This might seem quiet convoluted, all suggestions welcome, memory seems to be an issue when trying to sort through 1200 .csv files or even just reading 100 of them. Thanks for your time!

eipi10 eipi10 · Accepted Answer · 2016-09-06T23:10:21

I haven't tested this since I obviously don't have your data, but something like the code below should work. Basically, you create a vector of all the file names and then read, combine, and write 10 of them at a time.

library(reshape2)
library(dplyr)

# Get the names of all the csv files
files = list.files(pattern="csv$")

# Read, combine, and save ten files at a time in each iteration of the loop
for (i in (unique(1:length(files)) - 1) %/% 10)) {

  # Read ten files at a time into a list
  dat = lapply(files[(1:length(files) - 1) %/% 10 == i], function(f) {
    d=read.csv(f, header=TRUE, stringsAsFactors=FALSE)
    # Add file name as a column
    d$file = gsub("(.*)\\.csv$", "\\1", f)
    return(d)
  })

  # Combine the ten files into a single data frame
  dat = bind_rows(dat)

  # Reshape from long to wide format
  dat = dcast(Frequency ~ file, value.var="Voltage")

  # Write to csv
  write.csv(dat, paste("Files_", i,".csv"), row.names=FALSE)
}

On the other hand, if you want to just combine them all into a single file in long format, which will make analysis easier (if you have enough memory of course):

  # Read all files into a list
  dat = lapply(files, function(f) {
    d = read.csv(f, header=TRUE, stringsAsFactors=FALSE)
    # Add file name as a column
    d$file = gsub("(.*)\\.csv$", "\\1", f)
    return(d)
  })

  # Combine into a single data frame
  dat = bind_rows(dat)

  # Save to csv
  write.csv(dat, "All_files_combined.csv", row.names=FALSE)

Reading, merging & sorting .csv files

1 Answers