I have a set of 67 Excel files that I am trying to merge into a panel dataset in R. The file names are of the form: qjMMMYYe.xls, where MMM is a three-letter abbreviation for the month, running from jan to nov in two-month increments, and YY is the year, running from 09 to 20. The first is qjjan09e.xls and the last is qjjan20e.xls.
I am new to R, and I want to:
a) Read each file into R and name it in a way that can be ordered chronologically, e.g. qjjan09e.xls is assigned to data0901 and qjjan20e.xls is assigned to data2001
b) Create three new columns in each dataframe: year and month store the respective date components, and wave stores the chronological number of the file (e.g. the first file qjjan09e.xls is assigned 1 and the last file qjjan20e.xls is assigned 67)
c) Stacks the dataframes on top of each other to create a panel dataset
For a), I get the list of filenames through list.files(pattern="*.xls") and read them by looping through read_excel, but I cannot figure out how to rename the dataframes using regex. I think the month.abb function will help me if I can find a way to extract the three-letter abbreviations from the file names. I assume that this part would help me create the year and month columns needed in b), but I am also not sure how to get the wave number from my renamed files. I assume that c) involves rbind().
My solution involves the tidyverse (for some readable data-wrangling), and data.table for it's speedy processing of large amounts of data
It's probably not the most elegant way of things, but it will get the job done ;-)
I included comments and in-bewteen-results in the code below
library( tidyverse )
library( readxl )
library( data.table )
#get files to read
files.v <- list.files( path = "./temp", pattern = ".*\\.xls$", full.names = TRUE )
# [1] "./temp/qjjan09e.xls" "./temp/qjjan20e.xls"
#build df for lookup operation later on
DF <- data.frame( filename = files.v ) %>%
dplyr::mutate(
#use rownumbers to get file identifier
id = row_number(),
#extract year and month string from filename, and parse to date
date_id = paste0( gsub( "^.*([a-z]{3})([0-9]+.*)", "\\1", filename ),
gsub( "[^0-9]", "", filename ) ) %>%
#parse extracted strings to 'real' date using the corerect locale
readr::parse_date( format = "%b%y", locale = locale( date_names = "en" ) ) %>%
#format the date to the desired format
format( "%y%m" )
)
# filename id date_id
# 1 ./temp/qjjan09e.xls 1 0901
# 2 ./temp/qjjan20e.xls 2 2001
#read excel-files to list
L <- lapply( files.v, readxl::read_excel )
#name list
names(L) <- files.v
# $`./temp/qjjan09e.xls`
# # A tibble: 5 x 2
# col1 col2
# <dbl> <dbl>
# 1 1 8
# 2 2 9
# 3 3 10
# 4 4 11
# 5 5 12
#
# $`./temp/qjjan20e.xls`
# # A tibble: 5 x 2
# col1 col2
# <dbl> <dbl>
# 1 11 18
# 2 12 19
# 3 13 20
# 4 14 21
# 5 15 22
#now bind the List together, using it's names as an ID
DT <- data.table::rbindlist( L, use.names = TRUE, fill = TRUE, idcol = "filename" )
# filename col1 col2
# 1: ./temp/qjjan09e.xls 1 8
# 2: ./temp/qjjan09e.xls 2 9
# 3: ./temp/qjjan09e.xls 3 10
# 4: ./temp/qjjan09e.xls 4 11
# 5: ./temp/qjjan09e.xls 5 12
# 6: ./temp/qjjan20e.xls 11 18
# 7: ./temp/qjjan20e.xls 12 19
# 8: ./temp/qjjan20e.xls 13 20
# 9: ./temp/qjjan20e.xls 14 21
#10: ./temp/qjjan20e.xls 15 22
#now join the relevant info into the coluns needed, using a (fast!!) update join
# setDT is used on DF to make it a data.table
DT[ data.table::setDT(DF),
`:=`( id_col = i.id, date_col = i.date_id ),
on = .( filename )]
# filename col1 col2 id_col date_col
# 1: ./temp/qjjan09e.xls 1 8 1 0901
# 2: ./temp/qjjan09e.xls 2 9 1 0901
# 3: ./temp/qjjan09e.xls 3 10 1 0901
# 4: ./temp/qjjan09e.xls 4 11 1 0901
# 5: ./temp/qjjan09e.xls 5 12 1 0901
# 6: ./temp/qjjan20e.xls 11 18 2 2001
# 7: ./temp/qjjan20e.xls 12 19 2 2001
# 8: ./temp/qjjan20e.xls 13 20 2 2001
# 9: ./temp/qjjan20e.xls 14 21 2 2001
#10: ./temp/qjjan20e.xls 15 22 2 2001