There might be an R-only way of doing this, but one efficient way would be to do this with command-line (not R) tools.
Setup for the sake of this answer:
mt1 <- mtcars[1:3,c(1,2,3)]
mt2 <- mtcars[3:4,c(1,2,4)]
mt3 <- mtcars[5:10,c(1,3,4)]
combined <- rbindlist(list(mt1, mt2, mt3), use.names = TRUE, fill = TRUE)
combined
# mpg cyl disp hp
# 1: 21.0 6 160.0 NA
# 2: 21.0 6 160.0 NA
# 3: 22.8 4 108.0 NA
# 4: 22.8 4 NA 93
# 5: 21.4 6 NA 110
# 6: 18.7 NA 360.0 175
# 7: 18.1 NA 225.0 105
# 8: 14.3 NA 360.0 245
# 9: 24.4 NA 146.7 62
# 10: 22.8 NA 140.8 95
# 11: 19.2 NA 167.6 123
write.table(mt1, "mt1.tsv", row.names = FALSE)
write.table(mt2, "mt2.tsv", row.names = FALSE)
write.table(mt3, "mt3.tsv", row.names = FALSE)
Now that we know what the data looks like, let's get the filenames programmatically:
filenames <- list.files(".", pattern = "^mt.*\\.tsv", full.names = TRUE)
filenames
# [1] "./mt1.tsv" "./mt2.tsv" "./mt3.tsv"
From here, let's grab the first 1 row from each file (quickly/efficiently, since it's only 1 row each) and rbindlist them so that we know what the resulting table should look like. Granted, we don't need to keep any of the actual values, just the columns.
row1s <- rbindlist(lapply(filenames, function(fn) fread(fn, nrows = 1)),
use.names = TRUE, fill = TRUE)[0,]
row1s
# Empty data.table (0 rows and 4 cols): mpg,cyl,disp,hp
For demonstration here, note that merging this 0-row table with one of the originals renders a consistent schema. (No need to do this with real data unless you want to verify one or two.)
row1s[mt1, on = intersect(names(row1s), names(mt1))]
# mpg cyl disp hp
# 1: 21.0 6 160 NA
# 2: 21.0 6 160 NA
# 3: 22.8 4 108 NA
row1s[mt2, on = intersect(names(row1s), names(mt2))]
# mpg cyl disp hp
# 1: 22.8 4 NA 93
# 2: 21.4 6 NA 110
The goal will be to do this for all files programmatically:
# iterate through each file: read, left-join, write
for (fn in filenames) {
dat <- fread(fn)
dat <- row1s[dat, on = intersect(names(row1s), names(dat))]
fwrite(dat, file.path(dirname(fn), paste0("augm_", basename(fn))), sep = "\t")
}
newfilenames <- list.files(".", pattern = "^augm_mt.*\\.tsv$", full.names = TRUE)
newfilenames
# [1] "./augm_mt1.tsv" "./augm_mt2.tsv" "./augm_mt3.tsv"
To verify the new files look consistent, look for the double-\t (which indicate empty data, i.e. NA on import):
# double-\t indicates an empty field
lapply(newfilenames, readLines, n = 2)
# [[1]]
# [1] "mpg\tcyl\tdisp\thp" "21\t6\t160\t"
# [[2]]
# [1] "mpg\tcyl\tdisp\thp" "22.8\t4\t\t93"
# [[3]]
# [1] "mpg\tcyl\tdisp\thp" "18.7\t\t360\t175"
Now that we have this, let's go to a command-prompt (on windows, git-bash or just windows' bash if you need it). We need bash and one of tail or grep. The goal is that the want the column headers from one of these augm_mt files but none of the others.
If we naively concatenate the files, we'll see the header row repeated in the middle of the data ... and with R, this means that every column will be character, likely not what you want:
$ cat augm_mt1.tsv augm_mt2.tsv
mpg cyl disp hp
21 6 160
21 6 160
22.8 4 108
mpg cyl disp hp
22.8 4 93
21.4 6 110
Three options to side-step this, depending on what tools you have and how much you trust the contents of your data. (I suggest number 1, tail, if you have it, as it is the least-ambiguous.)
If you have tail, then we can "start at line 2" (skipping line 1) for each file:
$ cat augm_mt2.tsv
mpg cyl disp hp
22.8 4 93
21.4 6 110
$ tail -n +2 augm_mt2.tsv
22.8 4 93
21.4 6 110
If you run this on multiple files, it tends to prepend the filename with each set of tailed lines (try it), we'll suppress that in favor of contiguous lines by adding the -q option.
If you know that one or more of the column names are never seen in the real contents, then you can do one of:
$ grep -v mpg augm_mt2.tsv
22.8 4 93
21.4 6 110
$ grep -v 'mpg.*cyl.*disp' augm_mt3.tsv
18.7 360 175
18.1 225 105
14.3 360 245
24.4 146.7 62
22.8 140.8 95
19.2 167.6 123
More complicated but should be safer than "hand-written regex" as in number 2:
$ HDR=$(head -n 1 augm_mt2.tsv)
$ grep -F "$HDR" -v augm_mt2.tsv
22.8 4 93
21.4 6 110
(The -F means "fixed string", so no regex matching is attempted. This is safest, since something like a period in a column name could pose a potential risk. Remote, but non-zero.)
Whichever way you choose to go, this is how you combine those three files into one mega-file for reading back into R:
$ { head -n 1 augm_mt1.tsv ; tail -q -n +2 augm_*.tsv ; } > alldata_mt.tsv
The head -n 1 merely outputs the header row only, no data, making it much easier to do augm_*.tsv in the next command. (Otherwise we would need to find a way to do everything do all-but-the-first.)
Now we can read this back into R in one command:
fread("alldata_mt.tsv")
# mpg cyl disp hp
# 1: 21.0 6 160.0 NA
# 2: 21.0 6 160.0 NA
# 3: 22.8 4 108.0 NA
# 4: 22.8 4 NA 93
# 5: 21.4 6 NA 110
# 6: 18.7 NA 360.0 175
# 7: 18.1 NA 225.0 105
# 8: 14.3 NA 360.0 245
# 9: 24.4 NA 146.7 62
# 10: 22.8 NA 140.8 95
# 11: 19.2 NA 167.6 123
And for validation with this micro-data:
all.equal(fread("alldata_mt.tsv"), combined)
# [1] TRUE
Alternative that leaves intermediate files with no column headers, so we don't have to dance around things:
for (fn in filenames) {
dat <- fread(fn)
dat <- row1s[dat, on = intersect(names(row1s), names(dat))]
fwrite(dat, file.path(dirname(fn), paste0("augm_", basename(fn))), sep = "\t", col.names = FALSE)
}
Then in bash:
$ cat augm_*tsv > alldata2_mt.tsv
Then in R again,
fread("alldata2_mt.tsv", header = FALSE)
# V1 V2 V3 V4
# 1: 21.0 6 160.0 NA
# 2: 21.0 6 160.0 NA
# 3: 22.8 4 108.0 NA
# 4: 22.8 4 NA 93
# 5: 21.4 6 NA 110
# 6: 18.7 NA 360.0 175
# 7: 18.1 NA 225.0 105
# 8: 14.3 NA 360.0 245
# 9: 24.4 NA 146.7 62
# 10: 22.8 NA 140.8 95
# 11: 19.2 NA 167.6 123
... and you'll have to know the names to reassign them. This method seems like a little less work (it is), but it does leave the remote possibility that the order of column names is inadvertently changed. The first method above that preserves column names in all of the files preempts that potential mis-step.
