Reshaping database using reshape package-part 2

Question

Following the interaction in the previous post Reshaping database using reshape package I create this one to ask other question. Briefly: I have a database with some rows that it replicate for Id column, I would like to transpose it. The following cose show a little example of my database.

test<-data.frame(Id=c(1,1,2,3),    
                 St=c(20,80,80,20),
                 gap=seq(0.02,0.08,by=0.02),
                 gip=c(0.23,0.60,0.86,2.09),
                 gat=c(0.0107,0.989,0.337,0.663))

I would like a final database like this figure that I attached:

With one row for each Id value and the different columns attached.

Can you give me any suggestions?

score 3 · Accepted Answer · answered Jun 01 '19 at 10:15

You can use dcast from data.table. This function allows to spread multiple value variables.

library(data.table)
setDT(test) # convert test to a data.table
test1 <- dcast(test, Id ~ rowid(Id),
               value.var = c('St', 'gap', 'gip', 'gat'), fill = 0)
test1
#   Id St_1 St_2 gap_1 gap_2 gip_1 gip_2  gat_1 gat_2
#1:  1   20   80  0.02  0.04  0.23   0.6 0.0107 0.989
#2:  2   80    0  0.06  0.00  0.86   0.0 0.3370 0.000
#3:  3   20    0  0.08  0.00  2.09   0.0 0.6630 0.000

If you want to continue with a data.frame call setDF(test1) at the end.

score 0 · Answer 2 · answered Jun 01 '19 at 10:57

A dplyr/tidyr alternative is to first gather to long format, group_by Id and key and create a sequential row identifier for each group (new_key) and finally spread it back to wide form.

library(dplyr)
library(tidyr)

test %>%
  gather(key, value, -Id) %>%
  group_by(Id, key) %>%
  mutate(new_key = paste0(key, row_number())) %>%
  ungroup() %>%
  select(-key) %>%
  spread(new_key, value, fill = 0)

#     Id  gap1  gap2   gat1  gat2  gip1  gip2   St1   St2
#  <dbl> <dbl> <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1     1  0.02  0.04 0.0107 0.989  0.23   0.6    20    80
#2     2  0.06  0    0.337  0      0.86   0      80     0
#3     3  0.08  0    0.663  0      2.09   0      20     0

Reshaping database using reshape package-part 2

2 Answers2