How to join multiple txt by adding parts of the file name and then save csv?

Question

I need to open multiple txt (all have the same number of variables and variables names, see picture[1]); to each, add multiple columns with specific parts of their file name; then save a CSV resulting from merging all txts, with the added columns corresponding to each txt unique file name.

The file name looks like:

NVR_ch2_main_20220505140000_20220505150000 

and I need three columns:

Month (05, the first of the two after 2022),  
Day (05) and  
hour (14). 

Example of one of the txt sources of datawould

dput(head('NVR_ch2_main_20220505132105_20220505140000.txt'))
# A tibble: 41 x 8 
Selection View Channel Begin Time (s) End Time (s) Low Freq (Hz) High Freq (Hz)
type <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <lgl> 
1 1 Waveform 1 1 790. 792. 0 4000 NA 
# ... with 37 more rows

I managed the process with the whole file name for one file:

read_tsv('NVR_ch2_main_20220505132105_20220505140000.txt') %>%
mutate(filename = 'NVR_ch2_main_20220505132105_20220505140000.txt') %>% 
select(filename, everything ()) %>% 
write_csv('C:/Users/marta/Documents/R/Crete/NVR_ch2_main_20220505132105_20220505140000.csv')

Answer 1

library(tidyverse)

list_of_files <- list.files("C:/Users/marta/Documents/R/Crete", pattern="tsv", full.names=T)

fun1 <- function(x) {
    df1 <- read_tsv(x, comment = "#") 
    newname <- gsub("tsv", "csv", x)
    df1$month <- rep(str_sub(x, 18, 19), nrow(df1))
    df1$day <- rep(str_sub(x, 20, 21), nrow(df1))
    df1$hour = rep(str_sub(x, 22, 23), nrow(df1))
    write.csv(df1, newname)
    }
    
lapply(list_of_files, fun1)

You may have to adjust the numbers that are in the str_sub() calls so that you get the right position from the file names.

Note the the columns will be probably be characters. If you need numbers for month, day, and hour, you should change str_sub() to as.numeric(str_sub()).

Answer 2

Try this. I can't promise it will work since I don't have your data, but this should find all files with the common prefix, extract the month day and hour from the file name, and then read all the data in as one combined file:

library(tidyverse)

tibble(file = list.files(pattern = "NVR.*\\.txt")) |>
  extract(file, 
          into = c("month", "day", "hour"), 
          regex = ".*\\w_\\d{4}(\\d{2})(\\d{2})(\\d{2})\\d{4}_", 
          remove = FALSE, 
          convert = TRUE) |>
  mutate(data = map(file, read_tsv)) |>
  unnest(data)