How to check for existence of a set of columns in large dataframes?

I am constructing repeated cross-sectional (a panel) from multiple years of survey data. The survey data for each year is published as a separate dataframe with inconsistencies in variable names (column names) and sometimes variables are missing entirely. There are 450 columns so manual previewing is out of the question. As such, I want to search each dataframe to check for the existence of a list of variables of interest.

I first tried dplyr::select() but this is insufficient because it walks through the column names in order and only produces a warning for the first column name that can't be found in the dataframe when there may be in fact mutliple:

df <- data.frame(c1 = 1:5,
                  c2 = letters[1:5],
                  c3 = letters[1:5],
                  c4 = letters[1:5],
                  c5 = letters[1:5],
                  c6 = letters[1:5])
> 
> select(df, c(c1, c2, c7, c10, c11))
Error in `select()`:
! Can't subset columns that don't exist.
✖ Column `c7` doesn't exist.
Run `rlang::last_trace()` to see where the error occurred.

The answer to this similar question (https://stackoverflow.com/questions/50647011/grep-with-multiple-column-names-in-data-frame?newreg=63d1d79b4c504a65976efcec952f1005) I also find unsatisfactory.

which(!is.na(match(colnames(df), c("c1", "c2", "c7", "c10", "c11"))))
[1] 1 2

The position of the matching column names is returned (not the names) but it is the non-matching column names which are the most important for us to know, the real problem has 10 or more variables of interest so a human friendly output is important.

Does anyone know of a better way? Perhaps a function that would produce a table of matches (TRUE/FALSE) for each supplied column name?

You can use %in% with ! to get a logical vector of columns that are not present in the data set:

cols <- c("c1", "c2", "c7", "c10", "c11")
!cols %in% colnames(df)
#[1] FALSE FALSE  TRUE  TRUE  TRUE

Or with setdiff to get the names:

setdiff(cols, colnames(df))
#[1] "c7"  "c10" "c11"

And to select directly the columns in the data.frame without error messages, use any_of:

library(dplyr)
select(df, any_of(cols))
#   c1 c2
# 1  1  a
# 2  2  b
# 3  3  c
# 4  4  d
# 5  5  e

You could use an outer approach. Use both a names vector of variables of interest as well as a named list of your data frames.

v <- c('X1', 'X3', 'X7', 'X10', 'X11') |> setNames(nm=_)

f <- \(x, y) x %in% names(y)
t(outer(v, mget(ls(pattern='^df\\d$')), Vectorize(f)))
#       X1    X3    X7  X10   X11
# df1 TRUE  TRUE  TRUE TRUE FALSE
# df2 TRUE FALSE  TRUE TRUE FALSE
# df3 TRUE  TRUE FALSE TRUE FALSE

Instead of mget(ls(.)) you can also hard-code the list, using

list(df1=df1, df2=df2, df3=df3)

Explanation: We want to know if the statement v %in% names(df) is true, and that for each of multiple data frames. What's needed is kind of a double for-loop approach, with the outer looping over the data frames and the inner over v. However we can use outer, which can be used for such problems, of two vectors X and Y it iterates each element of Y over each element of X and throws a matrix. We just have to Vectorize the statement.

Edit

You might want to apply a consistent logic to variable naming in your set of data frames. For instance, three characters to abbreviate the meaning (abbreviate('ethnicity', 3) will help), but more importantly followed by two digits (or any number, as long as globally consistent) that carry congruent meaning. This would allow you to use endsWith.

ptt <- sprintf('%02d', c(1, 3, 7, 10, 11)) |> setNames(nm=_)

Note: The sprintf('%02d', .) conveniently fills up to two digits with a leading zero, %02d, and the _ is the base placeholder what was piped into setNames, i.e. output names are identical to content of each element (we want it for the matrix to be well displayed):

ptt
#   01   03   07   10   11 
# "01" "03" "07" "10" "11" 

f <- \(x, y) any(endsWith(names(x), y))

Adapted outer solution:

outer(mget(ls(pattern='^df[4-6]$')), ptt, Vectorize(f))
#       01    03    07   10    11
# df4 TRUE  TRUE  TRUE TRUE FALSE
# df5 TRUE FALSE  TRUE TRUE FALSE
# df6 TRUE  TRUE FALSE TRUE FALSE

Again, the search pattern '^df[4-6]$' looks for df4, df5, and df6; but instead of the mget(.) you can use a named list list(df4=df4, df5=df5, df6=df6), but mget is neat.

Data:

df1 <- df2 <- df3 <- data.frame(matrix(,2, 10))

df2$X3  <- NULL
df3$X2  <- df3$X7  <- NULL


df4 <- df5 <- df6 <- data.frame(matrix(,2, 10))

names(df4) <- paste0('eth', sprintf('%02d', seq_along(df4)))
names(df5) <- paste0('rgn', sprintf('%02d', seq_along(df5)))
names(df6) <- paste0('inc', sprintf('%02d', seq_along(df6)))


df5[3] <- NULL
df6[2] <- df6[7]  <- NULL