Transpose rows, columns, and groups

Question

I have a DF that I want to transpose rows and columns and group (distinct count) the number of ID's. My example:

ID<- c(111,222,111,222,333,111,222,333)
AGE_GROUP<- c("30_40","30_40","30_40","30_40","50_60","30_40","30_40","50_60")
DATE<-c("JAN","JAN","FEB","FEB","FEB","MAR","MAR","MAR")
DF<-data.frame(ID,AGE_GROUP,DATE)

The result I wish to have.My new DF:

AGE_GROUP<- c("30_40","50_60")
JAN<- c(2,0)
FEB<- c(2,1)
MAR<- c(2,1)
DF2<-data.frame(AGE_GROUP,JAN,FEB,MAR)

How would I do this grouping and transposition?

Also see https://stackoverflow.com/q/62439791/5325862 – camille Feb 09 '21 at 20:50 — camille, Feb 09 '21 at 20:50

score 3 · Accepted Answer · answered Feb 09 '21 at 20:14

3

We use pivot_wider with values_fn after removing the 'ID' column

library(dplyr)
library(tidyr)
DF %>% 
  select(-ID) %>% 
  pivot_wider(names_from = DATE, values_from = DATE, 
     values_fn = length, values_fill = 0)

-output

# A tibble: 2 x 4
#  AGE_GROUP   JAN   FEB   MAR
#  <chr>     <int> <int> <int>
#1 30_40         2     2     2
#2 50_60         0     1     1

In base R, table would be easier

table(DF[-1])

answered Feb 09 '21 at 20:14

akrun

874,273
37
540
662

Thanks, worked here! Did you simply consider the occurrence count? What if it were the distinct occurrence count? – Bruno Avila Feb 09 '21 at 21:02
1

@BrunoAvila it is simply the count. If you need unique then use `table(unique(DF[-1]))` – akrun Feb 09 '21 at 21:05

Transpose rows, columns, and groups

1 Answers1