CODEWITHSUNDEEP
r
Zheng
Zheng

Reputation: 79

Reshape but expand the data in R

I have the below dataset:

my.data <- read.table(text = '
                  ID  tmc_code  wDay    time_category   TTTR
                  1  121-04711  weekday Afternoon   1.1
                  2  121-04711  weekend Evening     1.3
                  3  121-04711  weekday Morning 1.1
                  4  121-04712  weekend Afternoon   1.101626016
                  5  121-04712  weekday Evening 1.281124498
                  6  121-04712  weekday Morning 1.080645161
                  ', header = TRUE, stringsAsFactors = FALSE, na.strings = 'NA')
my.data

and I would like to have a wide format result like this: 

#result
  #          tmc_code    wDay    TTTR_afternnon TTTR_Evening  TTTR_Morning
  #          121-04711  weekday         1.1         1.3           NA
  #          121-04711  weekend         NA          NA            1.1
  #          121-04712  weekday         NA       1.281124498    1.080645161
  #          121-04712  weekend    1.101626016      NA            NA

We can see hat not only reshape function should be used, but actually this process will turn 6 data into 9 data.

The reshape function below does not work for this situation:

w.my.data <- reshape(my.data, idvar = "tmc_code", timevar = "time_category", direction = "wide")

I wonder anyone has better ideas? Much appreciated!

Upvotes: 7

Views: 119

Answers (2)

zack
zack

Reputation: 5405

Similar to @Frank's answer, but using tidyr::spread:

library(tidyverse)

my.data %>% 
  select(-ID) %>% # Be sure no important info is lost/misrepresented in dropping ID
  mutate(time_category = paste0("TTTR", "_", time_category)) %>%
  spread(time_category, TTTR)

   tmc_code    wDay TTTR_Afternoon TTTR_Evening TTTR_Morning
1 121-04711 weekday       1.100000           NA     1.100000
2 121-04711 weekend             NA     1.300000           NA
3 121-04712 weekday             NA     1.281124     1.080645
4 121-04712 weekend       1.101626           NA           NA

Upvotes: 5

Frank
Frank

Reputation: 66819

You can use the reshape2 package:

> reshape2::dcast(my.data, tmc_code + wDay ~ paste("TTTR", time_category, sep="_"))

Using TTTR as value column: use value.var to override.
   tmc_code    wDay TTTR_Afternoon TTTR_Evening TTTR_Morning
1 121-04711 weekday       1.100000           NA     1.100000
2 121-04711 weekend             NA     1.300000           NA
3 121-04712 weekday             NA     1.281124     1.080645
4 121-04712 weekend       1.101626           NA           NA

Oh, and apparently it works with reshape as well, which also gives a helpful warning about variation in the ID that is being ignored here:

> reshape(my.data, idvar = c("tmc_code", "wDay"), timevar = "time_category", v.names = "TTTR", direction = "wide")

   ID  tmc_code    wDay TTTR.Afternoon TTTR.Evening TTTR.Morning
1:  1 121-04711 weekday       1.100000           NA     1.100000
2:  2 121-04711 weekend             NA     1.300000           NA
3:  4 121-04712 weekend       1.101626           NA           NA
4:  5 121-04712 weekday             NA     1.281124     1.080645
Warning message:
In reshapeWide(data, idvar = idvar, timevar = timevar, varying = varying,  :
  some constant variables (ID) are really varying

Upvotes: 5

Related Questions