Transform data frame

Question

I have a questionnaire with an open-ended question like "Please name up to ten animals", which gives me the following data frame (where each letter stands for an animal):

nrow <- 1000
list <- vector("list", nrow)

for(i in 1:nrow){
  na <- rep(NA, sample(1:10, 1))
  list[[i]] <- sample(c(letters, na), 10, replace=FALSE)
}

df <- data.frame()
df <- rbind(df, do.call(rbind, list))

head(df)
# V1   V2 V3 V4   V5 V6   V7 V8 V9  V10
# 1  r <NA>  a  j    w  e    i  h  u    z
# 2  t    o  e  x    d  v <NA>  z  n    c
# 3  f    y  e  s    n  c    z  i  u    k
# 4  y <NA>  v  j    h  z    p  i  c    q
# 5  w    s  v  f <NA>  c    g  b  x    e
# 6  p <NA>  a  h    v  x    k  z  o <NA>

How can I transform this data frame to look like the following data frame? Remember that I don't actually know the column names.

 r <- 1000
 c <- length(letters)
 t1 <- matrix(rbinom(r*c,1,0.5),r,c)
 colnames(t1) <- letters
 head(t1)
 #      a b c d e f g h i j k l m n o p q r s t u v w x y z
 # [1,] 0 1 0 1 0 0 0 1 0 0 1 1 1 1 0 0 0 1 0 1 0 1 1 0 1 0
 # [2,] 1 1 1 1 0 1 0 1 1 1 1 0 1 0 0 0 1 1 1 0 0 1 0 1 0 1
 # [3,] 0 1 0 0 0 1 1 1 0 1 0 0 0 0 0 0 0 0 0 1 0 1 0 0 0 0
 # [4,] 1 0 1 0 0 0 0 0 1 1 0 0 0 0 0 0 1 1 1 0 1 0 1 1 0 0
 # [5,] 1 0 1 1 1 1 1 1 1 0 1 1 0 0 0 0 1 1 0 1 1 0 0 1 0 0
 # [6,] 1 1 0 1 1 0 0 1 0 0 1 0 0 0 0 0 1 1 1 0 0 0 1 1 0 1

Answer 1

You can try mtabulate from the "qdapTools" package:

library(qdapTools)
head(mtabulate(as.data.frame(t(df))))
#   c d i l m o r v x y a f s t k p u b h j n q e g w z
# 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
# 2 0 1 0 0 1 0 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0
# 3 0 0 1 0 0 0 1 0 1 1 1 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0
# 4 1 0 1 1 0 0 0 0 0 1 0 0 0 0 0 0 1 1 1 1 1 0 0 0 0 0
# 5 0 1 0 0 0 0 1 0 0 0 0 0 1 0 1 1 0 1 1 0 1 1 0 0 0 0
# 6 0 0 0 0 1 0 0 0 0 0 0 0 1 1 1 0 1 1 0 1 0 1 0 0 0 0

---

There are, of course, many other options.

For example, cSplit_e from my "splitstackshape" package (with the downside that inefficiently, you need to paste the values together first before you can split them):

library(splitstackshape)
library(dplyr)

As ones and zeroes:

df %>%
  mutate(combined = apply(., 1, function(x) paste(na.omit(x), collapse = ","))) %>%
  cSplit_e("combined", ",", mode = "binary", type = "character", fill = 0) %>%
  select(starts_with("combined_")) %>%
  head
#   combined_a combined_b combined_c combined_d combined_e combined_f combined_g combined_h combined_i
# 1          0          0          1          1          0          0          0          0          1
# 2          1          0          0          1          0          1          0          0          0
# 3          1          0          0          0          0          0          0          0          1
# 4          0          1          1          0          0          0          0          1          1
# 5          0          1          0          1          0          0          0          1          0
# 6          0          1          0          0          0          0          0          0          0
#   combined_j combined_k combined_l combined_m combined_n combined_o combined_p combined_q combined_r
# 1          0          0          1          1          0          1          0          0          1
# 2          0          0          0          1          0          0          0          0          0
# 3          0          1          0          0          0          0          1          0          1
# 4          1          0          1          0          1          0          0          0          0
# 5          0          1          0          0          1          0          1          1          1
# 6          1          1          0          1          0          0          0          1          0
#   combined_s combined_t combined_u combined_v combined_w combined_x combined_y combined_z
# 1          0          0          0          1          0          1          1          0
# 2          1          1          0          0          0          0          0          0
# 3          0          1          1          0          0          1          1          0
# 4          0          0          1          0          0          0          1          0
# 5          1          0          0          0          0          0          0          0
# 6          1          1          1          0          0          0          0          0

As the original values:

df %>%
  mutate(combined = apply(., 1, function(x) paste(na.omit(x), collapse = ","))) %>%
  cSplit_e("combined", ",", mode = "value", type = "character", fill = "") %>%
  select(starts_with("combined_")) %>%
  head
#   combined_a combined_b combined_c combined_d combined_e combined_f combined_g combined_h combined_i
# 1                                c          d                                                      i
# 2          a                                d                     f                                 
# 3          a                                                                                       i
# 4                     b          c                                                      h          i
# 5                     b                     d                                           h           
# 6                     b                                                                             
#   combined_j combined_k combined_l combined_m combined_n combined_o combined_p combined_q combined_r
# 1                                l          m                     o                                r
# 2                                           m                                                       
# 3                     k                                                      p                     r
# 4          j                     l                     n                                            
# 5                     k                                n                     p          q          r
# 6          j          k                     m                                           q           
#   combined_s combined_t combined_u combined_v combined_w combined_x combined_y combined_z
# 1                                           v                     x          y           
# 2          s          t                                                                  
# 3                     t          u                                x          y           
# 4                                u                                           y           
# 5          s                                                                             
# 6          s          t          u   

Alternatively, you can use "reshape2":

library(reshape2)

## The values
dcast(melt(as.matrix(df), na.rm = TRUE), 
      Var1 ~ value, value.var = "value")

## ones and zeroes
dcast(melt(as.matrix(df), na.rm = TRUE), 
      Var1 ~ value, value.var = "value", fun.aggregate = length)

Answer 2

You can do the following using tidyr which could be much faster than other approaches, though I like the approach by @germcd very much. You may need to tinker with the select, removing NAs as well as a blank space, which may be an artifact of the simulated data you provided:

require(tidyr)

##  Add an ID for each record:
df$id <- 1:nrow(df)

out <- (df %>% 
  gather(column, animal, -id) %>% 
  filter(animal != " ") %>% 
  spread(animal, column)
)

head(out)

This code gathers the unnamed columns into a long format, removes any empty columns or missing data, and then spreads by the unique values of the animal column. This also has the potentially desirable property of preserving the column order in which the animals were named. If it's not desirable then you could easily convert the resulting animal columns to numeric:

out_num <- out
out_num[,-1] <- as.numeric((!is.na(out[,-1])))
head(out_num)

Answer 3

td <-  data.frame(t(apply(df, 1, function(x) as.numeric( unique(unlist(df)) %in% x))))
colnames (td) <- unique(unlist(df))

letters could be replaced with a vector of animal names colnames(t1).