Fill in columns with 1 or 0, if that colname matches a variable found in other named columns within the same dataframe

Question

view(fastcars)

   day    car1   car2   car3
1 day1   red  silver   blue
2 day2  blue    red   green
3 day3  blue  white   green
4 day4 green  black     red
5 day5 black    red  silver

Took all colors of cars and combined into one list with unique names.

cars <- stack(fastcars[, c(2:4)])
cars <- t(unique(cars[,1]))

Add colors as colnames to end of dataframe

fastcars[c(cars)] <- NA

   day  car1   car2   car3  red blue green black silver white
1 day1   red  silver  blue  NA   NA    NA    NA     NA    NA
2 day2  blue    red  green  NA   NA    NA    NA     NA    NA
3 day3  blue  white  green  NA   NA    NA    NA     NA    NA
4 day4 green  black    red  NA   NA    NA    NA     NA    NA
5 day5 black    red silver  NA   NA    NA    NA     NA    NA

Would like to fill in NAs with 1 or 0 if the colnames match the variable in columns car1, car2, and/or car3.

day car1     car2    car3   red  blue green black silver white
day1     red   silver    blue      1    1     0     0      1     0
day2    blue      red   green      1    1     1     0      0     0
day3    blue    white   green      0    1     1     0      0     1
day4   green    black     red      1    0     1     1      0     0
day5   black      red   silver     1    0     0     1      1     0`

I believe this link here is close to what I am trying to do but can't figure out how to create this within my existing dataframe. https://stackoverflow.com/a/30274596/3837899

#Generate example dataframe with character column

example <- as.data.frame(c("A", "A", "B", "F", "C", "G", "C", "D", "E", "F"))
names(example) <- "strcol"

#For every unique value in the string column, create a new 1/0 column
#This is what Factors do "under-the-hood" automatically when passed to    function requiring numeric data

for(level in unique(example$strcol)){
  example[paste("dummy", level, sep = "_")] <- ifelse(example$strcol == level, 1, 0)
}

Answer 1

Here are a couple of options.

Option 1: We could use a data.table merge after re-casting the melted data.

library(data.table) # v1.9.6
## make 'df' a data.table
setDT(df)
## melt, cast, and merge on 'day'
df[dcast(melt(df, "day"), day ~ value, fun.aggregate = length), on = "day"]
#     day  car1   car2   car3 black blue green red silver white
# 1: day1   red silver   blue     0    1     0   1      1     0
# 2: day2  blue    red  green     0    1     1   1      0     0
# 3: day3  blue  white  green     0    1     1   0      0     1
# 4: day4 green  black    red     1    0     1   1      0     0
# 5: day5 black    red silver     1    0     0   1      1     0

Option 2: Here is a less-appealing but sound base R approach.

## make sure car columns are character (may not be necessary)
df[-1] <- lapply(df[-1], as.character)
## get unique values of car columns
u <- unique(unlist(df[-1]))
## match 'u' with each row in 'df'
l <- lapply(seq_len(nrow(df)), function(i) as.numeric(u %in% df[i, -1]))
## bring the data together
cbind(df, setNames(do.call(rbind.data.frame, l), u))
#    day  car1   car2   car3 red blue green black silver white
# 1 day1   red silver   blue   1    1     0     0      1     0
# 2 day2  blue    red  green   1    1     1     0      0     0
# 3 day3  blue  white  green   0    1     1     0      0     1
# 4 day4 green  black    red   1    0     1     1      0     0
# 5 day5 black    red silver   1    0     0     1      1     0

Data:

df <-structure(list(day = structure(1:5, .Label = c("day1", "day2", 
"day3", "day4", "day5"), class = "factor"), car1 = structure(c(4L, 
2L, 2L, 3L, 1L), .Label = c("black", "blue", "green", "red"), class = "factor"), 
    car2 = structure(c(3L, 2L, 4L, 1L, 2L), .Label = c("black", 
    "red", "silver", "white"), class = "factor"), car3 = structure(c(1L, 
    2L, 2L, 3L, 4L), .Label = c("blue", "green", "red", "silver"
    ), class = "factor")), .Names = c("day", "car1", "car2", 
"car3"), class = "data.frame", row.names = c("1", "2", "3", "4", 
"5"))

Answer 2

Starting from this data.frame:

> fastcars
   day  car1   car2   car3 red blue green black silver white
1 day1   red silver   blue  NA   NA    NA    NA     NA    NA
2 day2  blue    red  green  NA   NA    NA    NA     NA    NA
3 day3  blue  white  green  NA   NA    NA    NA     NA    NA
4 day4 green  black    red  NA   NA    NA    NA     NA    NA
5 day5 black    red silver  NA   NA    NA    NA     NA    NA

This looks like one way of doing it using base-R:

#for every colour fill in each column
for (i in c('red','blue','green','black','silver','white')){
  #a simple apply per row is returning 1 if any row has the corresponding colour
  #or a 0 otherwise
  fastcars[, i] <- apply(fastcars[2:4], 1, function(x) ifelse(any(x==i),1,0) )
}

Output:

> fastcars
   day  car1   car2   car3 red blue green black silver white
1 day1   red silver   blue   1    1     0     0      1     0
2 day2  blue    red  green   1    1     1     0      0     0
3 day3  blue  white  green   0    1     1     0      0     1
4 day4 green  black    red   1    0     1     1      0     0
5 day5 black    red silver   1    0     0     1      1     0