reshape only 2 columns keeping multiple columns same

Question

My sample data looks like:

time         state           district      count   category
2018-01-01    Telangana      Nalgonda        17      Water
2018-01-01    Telangana      Nalgonda        8       Irrigation
2018-01-01    Telangana      Nalgonda        52      Seeds
2018-01-01    Telangana      Nalgonda        28      Electricity
2018-01-01    Telangana      Nalgonda        27      Storage
2018-01-01    Telangana      Nalgonda        12      Pesticides

I've around 2 years of monthly data of different states and districts. I would like to melt the data to wide format

Tried :

one <- reshape(dataset,idvar =  c("time","state","district"),v.names = names(dataset$category),
                 timevar = "count"
                 , direction = "wide")

Expected Output :

 time         state      district   Water Irrigation  Seeds Electricity Storage Pesticides 
2018-01-01  Telangana   Nalgonda      17       8        52      28          27     12

I'm not sure how exactly reshape package works. I've seen many examples but couldn't figure it out right explanations. Can some one let me know what's wrong I'm doing.

The `timevar=` specifies the new column groupings - `reshape(dat, idvar=c("time","state","district"), direction="wide", timevar="category")` — thelatemail, Aug 07 '19 at 01:00

score 1 · Answer 1 · answered Aug 07 '19 at 00:30

We could use gather and spread

library(dplyr)
library(tidyr)

df %>%
  gather(key, value, count) %>%
  spread(category, value) %>%
  select(-key)

#        time     state district Electricity Irrigation Pesticides Seeds Storage Water
#1 2018-01-01 Telangana Nalgonda          28          8         12    52      27    17

akrun · Answer 2 · 2019-08-07T02:57:50.397

We can use data.table

library(data.table)
dcast(setDT(df1), time + state + district + rowid(count) ~ 
      category, value.var = 'count')
#         time     state district count Electricity Irrigation Pesticides Seeds Storage Water
#1: 2018-01-01 Telangana Nalgonda     1          28          8         12    52      27    17

data

df1 <- structure(list(time = c("2018-01-01", "2018-01-01", "2018-01-01", 
"2018-01-01", "2018-01-01", "2018-01-01"), state = c("Telangana", 
"Telangana", "Telangana", "Telangana", "Telangana", "Telangana"
), district = c("Nalgonda", "Nalgonda", "Nalgonda", "Nalgonda", 
"Nalgonda", "Nalgonda"), count = c(17L, 8L, 52L, 28L, 27L, 12L
), category = c("Water", "Irrigation", "Seeds", "Electricity", 
"Storage", "Pesticides")), class = "data.frame", row.names = c(NA, 
-6L))

reshape only 2 columns keeping multiple columns same

2 Answers2

data