{"id":195303,"date":"2023-08-25T09:37:57","date_gmt":"2023-08-25T13:37:57","guid":{"rendered":"https:\/\/ibkrcampus.com\/?p=195303"},"modified":"2023-08-25T13:42:58","modified_gmt":"2023-08-25T17:42:58","slug":"basic-r-read-so-many-csv-files","status":"publish","type":"post","link":"https:\/\/www.interactivebrokers.com\/campus\/ibkr-quant-news\/basic-r-read-so-many-csv-files\/","title":{"rendered":"Basic R: Read So Many CSV Files"},"content":{"rendered":"\n
This post presents basic R code snippets to read files with given file extensions such as csv or txt. This is simple but very useful when it comes to the case where there are too many files to read manually.<\/p>\n\n\n\n
If we have too many (i.e. 1000 files) csv files or its variants, it is impossible to read these files one by one manually.<\/p>\n\n\n\n
For example, let’s assume that there are 6 files (USD.CSV, EUR.csv, CNY.TXT, AUD.txt, CNY2.ttxt, USD.CCSV) in a target directory. The first four files (csv, CSV, txt, TXT) are the files that we want to read. The contents of these files are straightforward because the output will show these contents in the later.<\/p>\n\n\n\n
In this case, we can use list.files()<\/strong> R function to get names of these files with some certain file extensions.<\/p>\n\n\n\n
list.files() function<\/h3>\n\n\n\n
list.files()<\/strong> is a built-in R function which returns a list of names of files with a given pattern.<\/p>\n\n\n\n
In the above R command, “\\\\.(csv|txt)$” pattern specifies that 1) it is applied at the end of file name($), 2) multiple file extensions such as csv or txt file ((csv|txt)) are allowed but not for similar extensions such as ccsv or ttxt(\\\\.). csv and CSV or txt and TXT are allowed because case sensitivity is ignored (ignore.case = TRUE).<\/p>\n\n\n\n
R code<\/h3>\n\n\n\n
The following R code is easy and self-contained: 1) reads each csv (CSV) or txt (TXT) files and make each data.frame separately and 2) reads and collects them into one data.frame.<\/p>\n\n\n\n
#========================================================#\n# Quantitative ALM, Financial Econometrics & Derivatives \n# ML\/DL using R, Python, Tensorflow by Sang-Heon Lee \n#\n# https:\/\/shleeai.blogspot.com\n#--------------------------------------------------------#\n# Basic R : read all csv files when these are so many\n#========================================================#\n \n graphics.off() # clear all graphs\n rm(list = ls()) # remove all files from your workspace\n \n # working directory\n setwd(\"D:\/SHLEE\/blog\/R\/many_csv\")\n \n # directory where csv files are located\n path<- file.path(getwd())\n \n#-------------------------------------------------------\n# make a list of all file names with csv or txt ext.\n#-------------------------------------------------------\n# $ : end of file name\n# (csv|txt) : multiple file extentions\n# \\\\. : avoid unwanted cases such as .ccsv\n#-------------------------------------------------------\n v.filename <- list.files(\n path, pattern=\"\\\\.(csv|txt)$\", \n ignore.case = TRUE, \n full.names = FALSE)\n \n#-------------------------------------------------------\n# Test 1) read and make each data.frame\n#-------------------------------------------------------\n for(fn in v.filename) {\n \n df.each <- read.csv(fn)\n \n # to do with df.each \n \n # print\n print(fn); print(df.each)\n \n # use of assign()\n #\n # save each file as each data.frame\n # which has each file name\n assign(fn, read.csv(fn))\n }\n \n#-------------------------------------------------------\n# Test 2) read and collect into one data.frame\n#-------------------------------------------------------\n df.all <- do.call(\n rbind, lapply(v.filename, \n function(x) read.csv(x)))\n print(df.all)\n \n # use of vroom package\n library(vroom)\n df.all.vroom <- vroom(v.filename)\n print(df.all.vroom)<\/pre>\n\n\n\n
Output<\/h3>\n\n\n\n
We can find that only 4 files with correct file extensions are read while 2 unwanted files (.CCSV and .ttxt) are ignored.<\/p>\n\n\n\n<\/figure>\n\n\n\n
Josep Pueyo-Ros kindly advised me to use assign() function to save as a different data.frame with the name of the file. I think it is very useful so I add this function to R code and get the following 4 data.frames which can be seen at Environment\/Data explorer<\/strong> in R studio.<\/p>\n\n\n\n
\n
USD.CSV<\/li>\n\n\n\n
EUR.csv<\/li>\n\n\n\n
CNY.TXT<\/li>\n\n\n\n
AUD.txt<\/li>\n<\/ul>\n\n\n\n
Additional Output – vroom package<\/h3>\n\n\n\n
HP (kind blog visitor) suggests the use of vroom<\/strong> which is a recently developed R package designed specifically for speed. I think this has lots of merits such as fast speed and suitability for complicated task. Result from vroom() function is as follows.<\/p>\n\n\n\n
> df.all.vroom <- vroom(v.filename)\nRows: 14 Columns: 3 \n-- Column specification ------------------- \nDelimiter: \",\"\nchr (2): currency, maturity\ndbl (1): ws\n \ni<U+00A0>Use spec()\n(\n)\n to retrieve the full column specification for this data.\ni<U+00A0>Specify the column types or set `show_col_types = FALSE` to quiet this message.\n \n> print(df.all.vroom)\n# A tibble: 14 x 3\n currency maturity ws\n <chr> <chr> <dbl>\n 1 AUD 6m 213000000\n 2 AUD 2y 106000000\n 3 AUD 2y 214000000\n 4 CNY 6m 84000000\n 5 CNY 6m 42000000\n 6 CNY 6m 144000000\n 7 EUR 3m 1785000000\n 8 EUR 6m 200000000\n 9 EUR 1y 250000000\n10 EUR 1y 1855000000\n11 USD 3m 285000000\n12 USD 3m 456000000\n13 USD 1y 112000000\n14 USD 2y 56000000\n> <\/pre>\n\n\n\n
This R code is efficient and useful especially when there are too many files to read.<\/p>\n\n\n\n
![\"\"](\"\/campus\/wp-content\/uploads\/sites\/2\/2023\/08\/output-SHLeeAIFinancialModel.png\")
<\/figure>\n\n\n\n