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)
}

Rich Scriven · Answer 1 · 2015-09-21T22:57:46.513

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"))

score 2 · Answer 2 · answered Sep 21 '15 at 19:23

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

So many answers so fast! I tried this first, it worked on my larger DF. Thanks — panstotts, Sep 21 '15 at 19:51
You are welcome :). Just to be fair if you have a large data.frame (100K+ rows) and you are interested in speed @jeremycg 's answer will probably be the fastest. For smaller data sets this is ok as it relies on base-R and is easy to understand. — LyzandeR, Sep 21 '15 at 19:54

score 2 · Answer 3 · answered Sep 21 '15 at 19:24

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

Ananta · Answer 4 · 2015-09-21T20:27:34.093

Please give us dput.

using reshape2

Thanks to Richard Scriven, I am working off his 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

score 1 · Answer 5 · answered Sep 21 '15 at 19:35

Lots of great answers already. This one's a bit cryptic, but in base 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

score 1 · Answer 6 · answered Sep 22 '15 at 06:09

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

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

score 0 · Answer 7 · answered Sep 21 '15 at 21:00

0

I code golf'ed this one:

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 make a dummy matrix, then do.call(pmax) them all together.

answered Sep 21 '15 at 21:00

Neal Fultz

9,282
1
39
60

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

7 Answers7