CODEWITHSUNDEEP
rdataframegroup-bydplyr
user9191983
user9191983

Reputation: 595

How to make new columns by counting up an existing column

I'd like to make some other columns by doing group_by in R.

When the original table is like below

 userID   cat1    cat2
    a        f       3
    a        f       3
    a        u       1
    a        m       1
    b        u       2
    b        m       1
    b        m       2

I group them by userID and want it come like

userID   cat1_f  cat1_m  cat1_u  cat2_1  cat2_2  cat2_3
a        2       1       1       2       0       1
b        0       2       1       1       2       0

Upvotes: 2

Views: 87

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388907

We could gather all the values then count them, create a new column by pasteing cat and value values and then spread it back to wide format with fill=0.

library(tidyverse)

df %>%
  gather(cat, value, -userID) %>%
  count(userID, cat, value) %>%
  unite(cat, c(cat, value)) %>%
  spread(cat, n, fill = 0)

#  userID cat1_f cat1_m cat1_u cat2_1 cat2_2 cat2_3
#  <fct>   <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
#1  a          2      1      1      2      0      2
#2  b          0      2      1      1      2      0

Upvotes: 2

akrun
akrun

Reputation: 887048

We can just use table from base R

table(df)
#       cat1
#userID f m u
#     a 2 1 1
#     b 0 2 1

Or with dcast from data.table

library(data.table)
dcast(setDT(df), userID ~ paste0('cat1_', cat1))

data

df <- structure(list(userID = c("a", "a", "a", "a", "b", "b", "b"), 
cat1 = c("f", "f", "u", "m", "u", "m", "m")), class = "data.frame", 
 row.names = c(NA, -7L))

Upvotes: 1

Related Questions