Make a new column for every variable and tally

Question

I have the following dataframe:

sample                  name
1      a cobra, tiger, reptile
2      b          tiger, spynx
3      c        reptile, cobra
4      d         sphynx, tiger
5      e       cat, dog, tiger
6      f            dog, spynx

and what I want to make from that is.

  sample cobra tiger spynx reptile cat dog
1      a     1     1     0       1   0   0
2      b     0     1     1       0   0   0
3      c     1     0     0       1   0   0
4      d     0     1     1       0   0   0
5      e     0     1     0       0   1   1
6      f     0     0     1       0   1   1

so basically make a new column out of all the variables that are in the column: name. and put a 1 if a value is present in the df$name and 0 if it is not present.

all <- unique(unlist(strsplit(as.character(df$name), ", ")))
all <- all[!is.na(all)]
for(i in df){
df[i]<- 0 }

this gives me all the variables as 0's, and now I want to match it to the name column, and if it is present make a 1 out of the 0

How would you approach this?

score 0 · Accepted Answer · answered Oct 19 '22 at 07:35

With tidyr and dplyr...


library(tidyr)
library(dplyr, warn = FALSE)

df1 |> 
  separate_rows(name) |>
  group_by(sample, name) |> 
  summarise(count = n(), .groups = "drop") |> 
  pivot_wider(names_from = "name", values_from = "count", values_fill = 0)
#> # A tibble: 6 × 8
#>   sample cobra reptile tiger spynx sphynx   cat   dog
#>   <chr>  <int>   <int> <int> <int>  <int> <int> <int>
#> 1 a          1       1     1     0      0     0     0
#> 2 b          0       0     1     1      0     0     0
#> 3 c          1       1     0     0      0     0     0
#> 4 d          0       0     1     0      1     0     0
#> 5 e          0       0     1     0      0     1     1
#> 6 f          0       0     0     1      0     0     1

^{Created on 2022-10-19 with reprex v2.0.2}

data

df1 <- data.frame(sample = letters[1:6],
                  name = c("cobra, tiger, reptile", 
                           "tiger, spynx",
                           "reptile, cobra",
                           "sphynx, tiger",
                           "cat, dog, tiger",
                           "dog, spynx"))

Make a new column for every variable and tally

1 Answers1