Search code examples
rdata.tablereshape2

Using the first column for variable names and vice versa with reshape

I have a DF as follows:

library(data.table)
DF <- structure(list(fullname = c(" Returns Count", " Dependent Exemptions Count", 
" Joint Returns Count", " Single Returns Count", " Head of Household Count"
), year = c(1998, 1998, 1998, 1998, 1998), State = c("Alabama", 
"Alabama", "Alabama", "Alabama", "Alabama"), Total_Returns = c(1879778, 
1354776, 774623, 690246, 382183), Breakeven_Loss = c(11649, 4254, 
5365, 5075, 591)), row.names = c(NA, -5L), class = c("data.table", 
"data.frame"))

I want switch the first column with the row names, keeping the state and year in place. I tried multiple things, among others:

valvar <- names(DF)[4:16]
DF<- dcast(DF, year + State ~ fullname, value.var= valvar)

or

valvar <- names(DF)[4:16]
DF<- dcast(DF, year + State + valvar ~ fullname)

But neither work. What am I missing here?

Desired output:

enter image description here

Solution

  • It would be easier to melt first:

    library(data.table)
DF[, melt(.SD, id.vars = c("year", "State", "fullname"))
   ][, dcast(.SD, variable + year + State ~ fullname)]

         variable year   State  Dependent Exemptions Count  Head of Household Count  Joint Returns Count  Returns Count  Single Returns Count
1:  Total_Returns 1998 Alabama                     1354776                   382183               774623        1879778                690246
2: Breakeven_Loss 1998 Alabama                        4254                      591                 5365          11649                  5075