0

I have the following dataset 

structure(list(Year = c("Oranges", "Cherrys", "Apples", "Bananas"
), `42461` = c(0, NA, 12, NA), `42491` = c(1, 12, NA, NA), `42522` = c(1, 
12, 7, NA), `42552` = c(NA, 12, 6, NA), `42583` = c(2, NA, 8, 
NA), `42614` = c(NA, 12, 5, NA), `42644` = c(NA, NA, 4, NA), 
    `42675` = c(NA, 12, NA, NA), `42705` = c(NA, 3, NA, NA), 
    `42736` = c(NA, NA, 12, NA), `42767` = c(NA, NA, 12, NA), 
    `42795` = c(NA, 12, NA, NA), Total = c(0, 0, 0, 0)), .Names = c("Year", 
"42461", "42491", "42522", "42552", "42583", "42614", "42644", 
"42675", "42705", "42736", "42767", "42795", "Total"), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -4L))

I would like to pivot it to look like: Category-Values-Year

I tried the following:

datdat %>% gather(Cat,Var)

but the problem is that the year is the name of each column.

Psidom
  • 209,562
  • 33
  • 339
  • 356

1 Answers1

1

I removed the "Totals" column, I'm not sure if this is what you're asking for:

library (data.table)
dat = data.table (structure(list(Year = c("Oranges", "Cherrys", "Apples", 
"Bananas"
), `42461` = c(0, NA, 12, NA), `42491` = c(1, 12, NA, NA), `42522` = c(1, 
12, 7, NA), `42552` = c(NA, 12, 6, NA), `42583` = c(2, NA, 8, 
NA), `42614` = c(NA, 12, 5, NA), `42644` = c(NA, NA, 4, NA), 
`42675` = c(NA, 12, NA, NA), `42705` = c(NA, 3, NA, NA), 
`42736` = c(NA, NA, 12, NA), `42767` = c(NA, NA, 12, NA), 
`42795` = c(NA, 12, NA, NA), Total = c(0, 0, 0, 0)), .Names = c("Year", 
"42461", "42491", "42522", "42552", "42583", "42614", "42644", 
"42675", "42705", "42736", "42767", "42795", "Total"), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -4L)))

names (dat)[1] = "Category"
dat [, "Total" := NULL]
melt.dat = melt (dat, id.vars = c("Category"), variable.name = "Year")

melt.dat gives you:

> head (melt.dat)
   Category  Year value
1:  Oranges 42461     0
2:  Cherrys 42461    NA
3:   Apples 42461    12
4:  Bananas 42461    NA
5:  Oranges 42491     1
6:  Cherrys 42491    12

Also note, the table is a data.table, not a data.frame :) Forgot to mention, run install.packages ("data.table") if you don't have it yet

JVP
  • 309
  • 1
  • 11