CODEWITHSUNDEEP
rreshapereshape2tidyverse
Kathiravan Meeran
Kathiravan Meeran

Reputation: 450

reshape data to different columns based on value from single column

Hi I have a dataframe as below (example)..

time <-c( 8/11/2017 10:21,  8/10/2017 22:34,    8/16/2017 2:28, 8/14/2017 6:17, 8/11/2017 6:33, 8/15/2017 23:46,    8/10/2017 20:10,    8/14/2017 3:35, 8/11/2017 4:09, 8/15/2017 21:05,    8/11/2017 2:16, 8/10/2017 18:17,    8/13/2017 10:02,    8/13/2017 9:08, 8/13/2017 8:32, 8/13/2017 8:20, 8/13/2017 7:56)

code  <- c( 1,  3,  2,  2,  1,  2,  2,  1,  1,  2,  2,  2,  2,  2,  1,  1,  2)    

var1 <-  c( 5,  11, 16, 22, 27, 33, 38, 44, 49, 55, 60, 66, 71, 77, 66, 71, 77)    

var2 <- c(  115,    66, 71, 33, 38, 110,    115,    121,    126,    132,    104,    66, 71, 77, 115,    121,    66)    

var3 <- c(  38, 44, 49, 55, 60, 66, 71, 77, 66, 71, 77, 132,    104,    66, 71, 77, 115)    

time    code    var1    var2    var3    
8/11/2017 10:21 1   5   115 38     
8/10/2017 22:34 3   11  66  44    
8/16/2017 2:28  2   16  71  49    
8/14/2017 6:17  2   22  33  55    
8/11/2017 6:33  1   27  38  60    
8/15/2017 23:46 2   33  110 66    
8/10/2017 20:10 2   38  115 71    
8/14/2017 3:35  1   44  121 77    
8/11/2017 4:09  1   49  126 66    
8/15/2017 21:05 2   55  132 71    
8/11/2017 2:16  2   60  104 77    
8/10/2017 18:17 2   66  66  132    
8/13/2017 10:02 2   71  71  104    
8/13/2017 9:08  2   77  77  66    
8/13/2017 8:32  1   66  115 71  
8/13/2017 8:20  1   71  121 77  
8/13/2017 7:56  2   77  66  115

I want to recast this dataframe using the column "code". The output I'm expecting should be as below. 

time    code1_var1  code1_var2  code1_var3  code2_var1  code2_var2  code2_var3  code3_var1  code3_var2  code3_var3    
8/11/2017 10:21                                 
8/10/2017 22:34                                 
8/16/2017 2:28                                  
8/14/2017 6:17                                  
8/11/2017 6:33                                  
8/15/2017 23:46                                 
8/10/2017 20:10                                 
8/14/2017 3:35                                  
8/11/2017 4:09                                  
8/15/2017 21:05                                 
8/11/2017 2:16                                  
8/10/2017 18:17                                 
8/13/2017 10:02                                 
8/13/2017 9:08                                  
8/13/2017 8:32                                  
8/13/2017 8:20                                  
8/13/2017 7:56

But when I tried dcast funtion in R It is giving me an error for time variable.

Please help me with this reshaping objective

note: The result should have many NA because of reshaping and missing data.

Upvotes: 1

Views: 584

Answers (1)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193517

The easiest way to do this would be with dcast from "data.table" or even reshape from base R.

Assuming your vectors are collected in a data.frame named "d", try the following:

library(data.table)
setDT(d)
x <- dcast(d, time ~ code, value.var = paste0("var", 1:3))
head(x)
#               time var1_1 var1_2 var1_3 var2_1 var2_2 var2_3 var3_1 var3_2 var3_3
# 1: 8/10/2017 18:17     NA     66     NA     NA     66     NA     NA    132     NA
# 2: 8/10/2017 20:10     NA     38     NA     NA    115     NA     NA     71     NA
# 3: 8/10/2017 22:34     NA     NA     11     NA     NA     66     NA     NA     44
# 4: 8/11/2017 10:21      5     NA     NA    115     NA     NA     38     NA     NA
# 5:  8/11/2017 2:16     NA     60     NA     NA    104     NA     NA     77     NA
# 6:  8/11/2017 4:09     49     NA     NA    126     NA     NA     66     NA     NA

OR

reshape(d, direction = "wide", idvar = "time", timevar = "code")

If you wanted to use the tidyverse, you would need to first gather, then create a new "times" variable, and then reshape to the wide format:

library(tidyverse)
d %>%
  gather(variable, value, starts_with("var")) %>%
  unite(key, code, variable) %>%
  spread(key, value)

Upvotes: 1

Related Questions