Assigning value to multiple columns based on if column name is in a string vector

user_12406581 0
Viewed 39

I'm trying to flag if a column's name appears in string vector in the same data frame.

For example, I have a dataframe that looks like the following:

df1 <-  data.frame(ID = c('123', '234', '345', '456', '567')
                   , Types = c('A|B|C|D', 'A|B', 'D|B', 'B|D|C', 'D')
                   , A = NA
                   , B = NA
                   , C = NA
                   , D = NA)
df1
  ID   Types  A  B  C  D
1 123 A|B|C|D NA NA NA NA
2 234     A|B NA NA NA NA
3 345     D|B NA NA NA NA
4 456   B|D|C NA NA NA NA
5 567       D NA NA NA NA

I'm trying to put a 1 in each column where its name is in the string 'Types' so that the output dataframe looks like

df2 <- data.frame(ID = c('123', '234', '345', '456', '567')
                  , Types = c('A|B|C|D', 'A|B', 'D|B', 'B|D|C', 'D')
                  , A = c(1,1,0,0,0)
                  , B = c(1,1,1,1,0)
                  , C = c(1,0,0,1,0)
                  , D = c(1,0,1,1,1))

df2
   ID   Types A B C D
1 123 A|B|C|D 1 1 1 1
2 234     A|B 1 1 0 0
3 345     D|B 0 1 0 1
4 456   B|D|C 0 1 1 1
5 567       D 0 0 0 1

I was able to do this using this loop

for(j in 3:6)
{
  for(i in 1:5)
  {
    df1[i,j] <- case_when(colnames(df1)[j] %like% df1[i,2] ~ 1, T ~ 0)
  }
}

But the actual dataframe I'm using is significantly larger so this loop is very slow. I'm looking for help coming up with a more efficient way of doing this!

Thank you!

dataframe r string
2 Answers

We can split the column and use mtabulate

library(qdapTools)
df1[-(1:2)] <- mtabulate(strsplit(df1$Types, "|", fixed = TRUE))
df1
#   ID   Types A B C D
#1 123 A|B|C|D 1 1 1 1
#2 234     A|B 1 1 0 0
#3 345     D|B 0 1 0 1
#4 456   B|D|C 0 1 1 1
#5 567       D 0 0 0 1

Or using cSplit_e

library(splitstackshape)
cSplit_e(df1[1:2], "Types", "|", type = 'character', fill = 0)
user_37322710

Here is a base R option using strsplit + table + factor

df1[-(1:2)] <- t(sapply(
  strsplit(df1$Types, "\\|"),
  function(x) table(factor(x, levels = names(df1)[-(1:2)]))
))

which gives

   ID   Types A B C D
1 123 A|B|C|D 1 1 1 1
2 234     A|B 1 1 0 0
3 345     D|B 0 1 0 1
4 456   B|D|C 0 1 1 1
5 567       D 0 0 0 1
user_121587570
Related