Reading multiple .txt files with different header in R and saving the file name as a column for each file

Question

I have more than 100 text files that i need to import into R.Some of them have different headers.I know that I can assign the same headers to all before I rbind them using do.call for merging into one data frame.But before that I need to save the file name of each file as a new column.How do I go about that? I have too many files to manually create the new columns.Thanks

Answer 1

I made up a reproducible example. Skip to WHAT YOU WANT if you want to skip reproducible example. Some data to write to csv files. temp0 has 2 columns and temp1 has 3 columns

MAKING UP DATA

library(tidyverse)

temp0 <- mtcars %>% head(.,5) %>% select(mpg,cyl)

                   mpg cyl
Mazda RX4         21.0   6
Mazda RX4 Wag     21.0   6
Datsun 710        22.8   4
Hornet 4 Drive    21.4   6
Hornet Sportabout 18.7   8

temp1 <- mtcars %>% head(.,5) %>% select(cyl,gear,carb)

                  cyl gear carb
Mazda RX4           6    4    4
Mazda RX4 Wag       6    4    4
Datsun 710          4    4    1
Hornet 4 Drive      6    3    1
Hornet Sportabout   8    3    2

To write them out to file, I make a random vector of the names of the data

set.seed(1)
num.files <- 20
rand.num <- sample(c(0,1),num.files,replace=T)
files <- paste0(rep("temp",20),rand.num)

head(files)
[1] "temp0" "temp0" "temp1" "temp1" "temp0"

Finally write to csv files

library(readr)
for (i in 1:length(files)) {
    write_csv(get(files[i]), paste0("C:/temp/temp", i, ".csv"), col_names=F)
}

-----WHAT YOU WANT-----

To read files and add an id, you can use map

temp <- map(1:20,~read_csv(paste0("C:/temp/temp", .x, ".csv"),col_names=F) %>% mutate(id=.x))

Depending on your column names, the following might help you to join them all in a tidy way using Reduce and full_join (it works this toy example).

final <- Reduce(full_join, temp)

OUTPUT

head(final)
      X1    X2    id    X3
 1  21.0     6     1    NA
 2  21.0     6     1    NA
 3  22.8     4     1    NA
 4  21.4     6     1    NA
 5  18.7     8     1    NA