Appending csvs with different column quantities and spellings

user_5021595 0
Viewed 37

Nothing too complicated, it would just be useful to use rbindlist on a large number of csvs where the column names change a little over time (minor spelling changes), the column orders remain the same, and at some point, two additional columns are added to the csvs (which I don't really need).


library(data.table)

csv1 <- data.table("apple" = 1:3, "orange" = 2:4, "dragonfruit" = 13:15)

csv2 <- data.table("appole" = 7:9, "orangina" = 6:8, "dragonificfruit" = 2:4, "pear" = 1:3)

l <- list(csv1, csv2)

When I run

csv_append <- rbindlist(l, fill=TRUE) #which also forces use.names=TRUE

it gives me a data.table with 7 columns

     apple orange dragonfruit appole orangina dragonificfruit pear
1:     1      2          13     NA       NA              NA   NA
2:     2      3          14     NA       NA              NA   NA
3:     3      4          15     NA       NA              NA   NA
4:    NA     NA          NA      7        6               2    1
5:    NA     NA          NA      8        7               3    2
6:    NA     NA          NA      9        8               4    3

as opposed to what I want, which is:

       V1     V2         V3     V4
1:     1      2          13     NA
2:     2      3          14     NA
3:     3      4          15     NA
4:     7      6           2     1
5:     8      7           3     2
6:     9      8           4     3

which I can use, even though I have to go through the extra step later of renaming the columns back to standard variable names.

If I instead try the default fill=FALSE and use.names=FALSE, it throws an error:

Error in rbindlist(l) : 
  Item 2 has 4 columns, inconsistent with item 1 which has 3 columns. To fill missing columns use fill=TRUE.

Is there a simple way to manage this, either by forcing fill=TRUE and use.names=FALSE somehow or by omitting the additional columns in the csvs that have them by specifying a vector of columns to append?

data.table append csv r
2 Answers

If we only need first 3 columns, then drop the rest and bind as usual:

rbindlist(lapply(l, function(i) i[, 1:3]))
#    apple orange dragonfruit
# 1:     1      2          13
# 2:     2      3          14
# 3:     3      4          15
# 4:     7      6           2
# 5:     8      7           3
# 6:     9      8           4

Another option, from the comments: we could directly read the files, and set to keep only first 3 columns using fread, then bind:

rbindlist(lapply(filenames, fread, select = c(1:3)))
user_6800680

Here is an option with name matching using phonetic from stringdist. Extract the column names from the list of data.table ('nmlist'), unlist, group using phonetic, get the first element, relist it to the same list structure as 'nmlist', use Map to change the column names of the list of data.table, and then apply rbindlist

library(stringdist)
library(data.table)
nmlist <- lapply(l, names)
nm1 <- unlist(nmlist)
rbindlist(Map(setnames, l, relist(ave(nm1, phonetic(nm1), 
      FUN = function(x) x[1]), skeleton = nmlist)), fill = TRUE)

-output

#    apple orange dragonfruit pear
#1:     1      2          13   NA
#2:     2      3          14   NA
#3:     3      4          15   NA
#4:     7      6           2    1
#5:     8      7           3    2
#6:     9      8           4    3
user_37322710
Related