2
\$\begingroup\$

So I want to import and edit a huge amount of data. I have a file with multiple data sets from time series that are chronologically ordered. Now I want to open them and edit to have a final dataset where the files are edited and ordered.

The code looks like this but it is taking some time to load the data:

setwd <-("C:/Users/D60378/Desktop/DATA")
path_data <- 'test'
files_data <- list.files(path_data)
length(files_data)
for (i in 1:length(files_data)){
 # use intermediary path if nested dir
tempPath <- file.path(getwd(), path_data, files_data[i])
  # name of the dataser
   name <- gsub('-', '_', substr(files_data[i], start = 0, stop =     (nchar(files_data[i])-4)))
   print(name)
     df <- read.csv(tempPath, skip=5, header=FALSE, sep = ';', dec = ',') #sheet 1 is the
   # column names 
   df_names <- read.csv(tempPath, skip=4, nrows=1, header=FALSE, sep = ';', dec=',')
   dfnames_test_MS7 <- c()
   for (i in 1:length(df_names)){
     print(as.character(df_names[[i]]))
    dfnames_test_MS7[i] <- as.character(df_names[[i]])
   }
   dfnames_test_MS7[1] <- "DateTime"
   for (i in 1:length(dfnames_test_MS7)){
     dfnames_test_MS7[i] <- gsub(' ', '_', dfnames_test_MS7[i])
   }
  dfnames_test_MS7

   names(df) <- dfnames_test_MS7
   assign(name, df)
}
\$\endgroup\$
  • 1
    \$\begingroup\$ how many csv file you have? how large they are? with how many time series and how long? \$\endgroup\$ – minem Jul 31 '17 at 8:10
1
\$\begingroup\$

1) Firstly you could use fread from data.table to speed up reading of .csv files.

2) It looks like that you do not need the two inner loops, you could do that with vectorization.

If you could provide some example data of df and df_names(using dput) then I could write the necessary code, and test the timings...


Update:

It looks like for smaller files (200 cols and 2000 rows) read_csv2 from readr is faster than data.table's fread:

Unit: milliseconds
                                                               expr      min       lq     mean   median       uq      max neval cld
 read.csv(tempPath, skip = 5, header = FALSE, sep = ";", dec = ",") 475.7547 480.2411 488.7772 484.3644 487.7255 515.8005     5   c
                                      read_csv2(tempPath, skip = 4) 179.2461 181.9832 182.2904 182.3569 182.4955 185.3702     5 a  
                               fread(tempPath, skip = 4, dec = ",") 463.4811 468.1232 470.4556 468.3920 469.5664 482.7155     5  b 

You should test it yourself and see if there is significant difference.

The end code could look something like this:

files_data <- list.files(path_data, full.names = T)
files_data

files_data <- grep(".csv", files_data, value = T, fixed = T)
length(files_data)

files_data # paths to files


myAproach <- function(i) {
  require(readr)
  tempPath <- files_data[i]
  name <- basename(tempPath)
  name <- gsub('-', '_', substr(name, start = 0,
                                stop = (nchar(name) - 4)),
               fixed = T)
  print(name)

  df <- read_csv2(tempPath, skip = 4)

  dfnames_test_MS7 <- colnames(df)
  dfnames_test_MS7[1] <- "DateTime"
  dfnames_test_MS7 <- gsub('V', 'x', dfnames_test_MS7, fixed = T)
  #fixed for speed
  colnames(df) <- dfnames_test_MS7
  df
}
resDflist <- lapply(1:length(files_data), myAproach)

resDflist is list of data.frames. In my opinion it is easier to work with lists than assign the data.frames to global environment.

\$\endgroup\$
  • 2
    \$\begingroup\$ +1 but a couple comments (in the spirit of improving coding standards). You should make myAproach take a filename as input, so it does not rely on an object defined outside its scope (files_data). Prefer TRUE to T; the latter is frowned upon as it can be overwritten (T <- FALSE). \$\endgroup\$ – flodel Aug 1 '17 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.