Transpose rows, columns, and groups

user_11294806 0
Viewed 72

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?

dplyr r
1 Answers

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])
user_37322710
Related