data.table create table from rows

Seth W. Bigelow 0
Viewed 20

I would like to analyze a table that reports job codes used by people over the course of several pay periods: I want to know how many times each person has used each job code. The table lists people in the first column, and pay periods in subsequent columns -- I cannot transpose without creating new problems with names. The table looks like this:

people pp1 pp2 pp3 pp4
Bob A A A C
Ted B B B B
Alice B A C C

My desired output looks like this:

people A B C
Bob 3 0 1
Ted 0 4 0
Alice 1 1 2

My code is as follows:

myDT <- data.table(
    people = c('Bob','Ted','Alice'),
    pp1 = c('A','B','B'),  
    pp2 = c('A','B','A'),  
    pp3 = c('A','B','C'),  
    pp4 = c('C','B','C')  
)

id.col=paste('pp',1:3)
myDT[ , table(as.matrix(.SD)), .SDcols = id.col, by = 1:nrow(myDT)]

but it's nowhere close to working

data.table
1 Answers
melt(myDT, "people") |>
  dcast(people ~ value, fun.aggregate = length)
#    people     A     B     C
#    <char> <int> <int> <int>
# 1:  Alice     1     1     2
# 2:    Bob     3     0     1
# 3:    Ted     0     4     0
r2evans0
Related