How to load multiple files from a folder and use part of filename as column in dataset

user_12467164 0
Viewed 132

I am slowly fumbling my way around R and learning lots thanks to forums like this and blogs. I have found a handy piece of code (below) to solve part of a new problem but now I am stuck.

library(readr)
library(dplyr)

myFiles <- list.files(path = "C:/Desktop/M2P/", pattern = "*.txt", full.names = FALSE)
myTable <- sapply(myFiles, read_csv, simplify=FALSE) %>% 
  bind_rows(.id = "id")

All of the filenames in the path are like this: 'YYYYMMDD_SUMMARY.txt'

The file contains a number of columns separated by ","

The code above adds a new column ("id") to the table with the exact filename that was loaded, along with all of my data in columns and this is great, however ...

I would like to adjust this so that I get a column added which is just the date part of the filename, that is, YYYY-MM-DD. I want to use this date later to drive some functionality and to group the data.

is this possible?

dplyr readr r
3 Answers

Add a mutate statement to get the date from the file names. 

library(dplyr)
library(readr)

sapply(myFiles, read_csv, simplify=FALSE) %>% 
   bind_rows(.id = "id") %>%
   mutate(id = sub('(\\d+).*', '\\1', id))
   #If you need as date object
   #mutate(id = lubridate::ymd(sub('(\\d+).*', '\\1', id)))
user_39629140

Another way is to use separate from tidyr to split the id into the date and the remainder:

library(dplyr)
library(tidyr)

df <- tibble(id = c("20110101_SUMMARY", "20110201_SUMMARY", "20110301_SUMMARY"), x = runif(3), y = runif(3))
df %>% 
  separate(id, into = c("date", "desc")) %>% 
  mutate(date = as.Date(date, "%Y%m%d"))
user_129938610

Here is a data.table approach of things.. Since you are just starting with R, it might seem a little intimidating, but I highly recommend taking a look at this package...

Explanation and in-between output is commented in the code below..

--

Let's say I've got two files in the subfolder "temp", like this:

enter image description here

with contents like this:

enter image description here

#get a list of all *full* filenames in the folder ./temp, ending on the pattern ".txt" 
files.to.read <- list.files( path = "./temp", pattern = ".*.\\.txt$", full.names = TRUE )
#[1] "./temp/20200302_SUMMARY.txt" "./temp/20200303_SUMMARY.txt"

#load data.table library
library( data.table )
#create a list, reading the files from the above created list
#fread() from the data.table package is a fast and robust reader, with many, many options.
l <- lapply( files.to.read, fread )
# [[1]]
# col1 col2
# 1:    6    9
# 
# [[2]]
# col1 col2
# 1:    1    4

#now, add names to the list, based on the read filenames 
#do this by removing all non digit-charactes (regex = "[^0-9]" ) from the filename (=basename)
names(l) <- gsub( "[^0-9]", "", basename(files.to.read) )

#as you can see, the dates are now the namse of the list l

# $`20200302`
# col1 col2
# 1:    6    9
# 
# $`20200303`
# col1 col2
# 1:    1    4

#now use rbindlist from the data.table package to rowbind the list together
#use the names from the list as id in a new column, named "date"
rbindlist( l, idcol = "date" )

final output

#        date col1 col2
# 1: 20200302    6    9
# 2: 20200303    1    4
user_63562780
Related