Apologies in advance if this question has been adequately answered previously. I've poked around various lapply questions here but haven't found one quite like this situation.
I have data on four companies with one .xlsx file on eight different dates. For clarity, a total of 32 .xlsx files (1 for each company-date combination). The eight dates are the upper level file structure in the path where the first folder is "01-01-19," the second is "02-01-19," etc. Within each of these folders, the four companies have different file names where I can use grepl to accurately identify which company it is.
My goal is to read in certain ranges of the .xlsx files, based on the file names, and then to rbind or rbindlist all of the data.tables of specific companies together.
My current approach has been to loop through the file names (the dates e.g., "01-01-19") using lapply. Then once, I am in the lower-level folder structure with specific company .xlsx files, I can again loop through using a nested lapply to read each table as a data.table. I use an if-else statement to check the company name as each company has a different number of observations which is consistent across all time periods (i.e. co1 will consistently have 100 observations in each period but co2 may have 300 observations each period, etc.)
My current approach has been the following:
# get the files which are indexed by date - e.g. "01-01-2019"
files <- list.files(path=file.path(input_fld,"co_data"))
# nested lapplys to 1. loop through the outer files (dates) and 2. loop through the inner four files (companies) conditionally
co_dt_list <- lapply(files, function(x) {
# get the files inside the index_data
in_files <- list.files(path=file.path(input_fld,"co_data",x))
# the second lapply to loop through each of the company names individually
lapply(in_files, function(y) {
if (grepl("co1", y) == TRUE) {
# if the file name has "co1" in the name, read this as a data.table
data.table(read_excel(file.path(input_fld, "co_data", x, y), range = "A15:Q115"))
} else if (grepl("co2", y) == TRUE) {
# if the file name has "co2" in the name, read this as a data.table
data.table(read_excel(file.path(input_fld, "co_data", x, y), range = "A15:Q315"))
} else if (grepl("co3", y) == TRUE) {
# if the file name has "co3" in the name, read this as a data.table
data.table(read_excel(file.path(input_fld, "co_data", x, y), range = "A15:Q515"))
} else {
# else read in this as a data.table
data.table(read_excel(file.path(input_fld, "co_data", x, y), range = "A15:Q715"))
}
})
})
This loop functionally works as written but gets me to an undesired output. It indexes each date as the first list index and the company as the second (inner) list index. In other words, the first list of data.tables will be co1, co2, co3, and co4 on 01-01-19 and the second will be the same for 02-01-19, etc.
I've created some synthetic data for what the output looks like below. Forgive the manual effort on combining lists in advance.
# generate a date list
date_list <- seq.Date(as.Date("2019-01-01"), as.Date("2019-08-01"), by = "month")
# generate fake datasets by date
co1 <- lapply(date_list, function(x) { data.table(co = 1, date = x, data = rnorm(100)) })
co2 <- lapply(date_list, function(x) { data.table(co = 2, date = x, data = rnorm(300)) })
co3 <- lapply(date_list, function(x) { data.table(co = 3, date = x, data = rnorm(500)) })
co4 <- lapply(date_list, function(x) { data.table(co = 4, date = x, data = rnorm(700)) })
# put them together in current list format
list_1 <- list(co1[[1]], co2[[1]], co3[[1]], co4[[1]])
list_2 <- list(co1[[2]], co2[[2]], co3[[2]], co4[[2]])
list_3 <- list(co1[[3]], co2[[3]], co3[[3]], co4[[3]])
list_4 <- list(co1[[4]], co2[[4]], co3[[4]], co4[[4]])
list_5 <- list(co1[[5]], co2[[5]], co3[[5]], co4[[5]])
list_6 <- list(co1[[6]], co2[[6]], co3[[6]], co4[[6]])
list_7 <- list(co1[[7]], co2[[7]], co3[[7]], co4[[7]])
list_8 <- list(co1[[8]], co2[[8]], co3[[8]], co4[[8]])
# this is what the output looks like
list_output <- list(list_1, list_2, list_3, list_4,
list_5, list_6, list_7, list_8)
My question is: is there an easy way to either a) take the list_output I have in its current form and rbind or rbindlist the data.tables of each company to one another? Or, b) is there a better way to structure initial nested lapply loops to get an output which is indexed by company as opposed to date?
Thanks!