Find a row in a data.table that is same as the header

user_4125751 0
Viewed 55

I am reading in a huge csv file with fread. The data is somehow mis-formatted and the header is repeated now and then. I want now to remove the headers in the file, thus, I have to search for rows where the content is equal to the header.

I could think of 2 solutions, neither is optimal:

  1. Option 1 assumes that all non-header repeating rows are otherwise mutually different in at least one position
  2. Option 2 is very verbose and would require a lot of writing

Basically I would need a sort of a loop with loops through all columns and compare them with the header.

Thus, the whole thing boils down to the question:

How to find a specific row in a data table without hard coding the filter?

Code

library(data.table)
foo <- data.frame(a = c(1:2, "a", 1:2, "a"), b = c(letters[1:2], "b", letters[2:1], "b"),
                  stringsAsFactors = FALSE)
setDT(foo)

## option 1: use duplicates, assuming that each row is otherwise unique
foo[-(which(duplicated(rbind(as.list(names(foo)), foo))) - 1)]

## option 2: compare directly, but becomes very cumbersome with growing number of columns
foo[!(a == names(foo)[1] & b == names(foo)[2])]
data.table fread r
2 Answers

Anti-join:

setkeyv(foo, names(foo)) # Reordes data though
foo[!list(names(foo))]

   a b
1: 1 a
2: 1 b
3: 2 a
4: 2 b

Without setting keys:

nfoo <- names(foo)
foo[!setNames(as.list(nfoo), nfoo), on = nfoo]
user_45522950

As misplaced headers are repeated same as the actual headers, then we only need to compare the first column, i.e. your option 2, but check only 1st (or any) column:

foo[ !(a == names(foo)[1]), ]

Or remove headers outside R using grep, something like:

fread("grep -v myCol1 myfile.txt")

Or paste every row, compare with header:

foo[ do.call(paste, c(foo, list(sep = "_"))) != paste(colnames(foo), collapse = "_"), ]

I'd prefer to go with 2nd option, then we don't have issue of column classes that we have when using other "after-fread" solutions.

user_6800680
Related