Fill in columns with 1 or 0 if this colname matches the variable found in other named columns inside the same data block

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 

He took all the colors of the cars and combined them into one list with unique names.

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

Add Column Colors to the End of the Data Frame

 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 

I would like to populate NA with 1 or 0 if 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 that this link here is close to what I'm trying to do, but I can’t figure out how to create it in my existing framework. stack overflow

 #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 few options.

Option 1: We could use .table data merging after re-casting the molten 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: Below is a less attractive, but reasonable approach to base R.

 ## 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 to do this with 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 

Answer 3

Using dplyr and tidyr :

 library(dplyr) library(tidyr) fastcars %>% gather(car, col, -day) %>% spread(col, car) %>% mutate_each(funs(+!is.na(.)), -day) %>% left_join(fastcars, ., by = "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 

Answer 4

Please give us a dput .

using reshape2

Thanks to Richard Scriven, I am working on my data structure.

  dd2<-melt(df,id.vars="day") dd3<-dcast(data=dd2, day ~ value, value.var="value", length) merge(df, dd3, by="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 

Answer 5

Already a lot of great answers. This is a little mysterious, but in the base of R.

 fastcars <- data.frame(day=paste0("day", 1:5), car1 = c("red", "blue", "blue", "green", "black"), car2 = c("silver", "red", "white", "black", "red"), car3 = c("blue", "green", "green", "red", "silver"), stringsAsFactors=FALSE) # for aggregate function msum <- function(x) min(sum(x), 1) cars <- data.frame(day=rep(paste0("day", 1:nrow(fastcars)), 3), stack(fastcars, select=-day)) cars <- cbind(cars, model.matrix(ind ~ values - 1, data=cars)) res <- aggregate(cars[, -c(1:3)], list(cars$day), msum) merge(fastcars, res, by.x="day", by.y="Group.1") day car1 car2 car3 valuesblack valuesblue valuesgreen valuesred valuessilver valueswhite 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 

Answer 6

We could also do this using table . We unlist "car" columns, cbind with the first column, get table , convert it to data.frame and cbind with the original data set.

 cbind(fastcars,as.data.frame.matrix(table(cbind(fastcars[1], cars=unlist(fastcars[2:4]))))) # 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 

Answer 7

I golf'ed this code:

 fastcars <- cbind(fastcars, do.call(pmax, lapply(fastcars[-1], outer, setNames(nm=unique(unlist(fastcars[-1]))), `==`)) ) 

For each of car1, car2, car3 use outer to create a dummy matrix, then do.call(pmax) all together.