Spit column into many columns

Asked by At
data = data.frame("id"=1:40,
                  "group"=1:5,
                  "score"=sample(1:4,10,r=T))
table(data[which(data$group==1),]$score)
WANT=data.frame("group"=1:5,
                  "score1"=c(0,4,0,4,0),
                  "score2"=c(4,0,0,4,0),
                  "score3"=c(0,4,0,4,0),
                  "score4"=c(0,0,4,4,0))

In data I have "score" but I want to make separate columns for each "score" and then sum up as shown here.

I also want to have my complete data frame 'WANT' and put 0s if there aren't any people but otherwise keep the same structure in terms of rows.

1 Answers

0
akrun On

An option would be spread after getting the count or frequency by 'group' and 'score'

library(tidyverse)
data %>% 
    count(group, score) %>%
    mutate(score = str_c("score", score)) %>%
    spread(score, n, fill = 0)

If we wanted to have with the all the combinations after the count, use complete

data %>% 
  count(group, score) %>% 
  complete(group, score, fill = list(n = 0))

I also want to have my complete data frame 'WANT' and put 0s if there aren't any people but otherwise keep the same structure in terms of rows.