How to reshape a dataframe by similar columns

Question

I have a data frame which has five columns like below:

 id    p1    p2   time                      group
___   ___   ___  ____                      _______
 1     1.2  1.9  2016-10-09 01:00:00         1
 1     1.8  1.3  2016-10-09 03:00:00         1
 1     1.2  1.9  2016-10-09 03:00:00         2
 1     1.8  1.3  2016-10-09 06:00:00         2
 3     1.2  1.9  2016-10-09 09:00:00         1
 3     1.8  1.3  2016-10-09 12:00:00         1

From this I need to reshape long to wide for each id and each group which is like below:

 id    group      p1_start    p2_start    time_start           p1_complete p2_complete    time_complete                      
 ___   ______    __________   ________    ___________          ________  ______    __________   ________    
  1          1        1.2        1.9      2016-10-09 01:00:00   1.2        1.9      2016-10-09 03:00:00   
  1          2        1.2        1.9      2016-10-09 06:00:00   1.2        1.9      2016-10-09 03:00:00        
  3          1        1.2        1.9      2016-10-09 09:00:00   1.2        1.9      2016-10-09 12:00:00

So I tried with

reshape(DT, idvar = c("id","group"), timevar = "group", direction = "wide")

But this resulted in what not expected output.

Any help is appreciated.

[How to make a great R reproducible example?](http://stackoverflow.com/questions/5963269) — Sotos, Nov 07 '17 at 09:09
What happens with those rows with `p1` are `1.8`, or rows where `p2` are `1.3`? — MrGumble, Nov 07 '17 at 09:26

score 1 · Accepted Answer · answered Nov 07 '17 at 09:21

1

try this, df is your original data. library(data.table) setDT(df) df <- df[, c(.SD[1,], .SD[2,]), by = c('id', 'group')] names(df) <- c('id', 'group', 'p1_start', 'p2_start', 'time_start', 'p1_complete', 'p2_complete', 'time_complete')

answered Nov 07 '17 at 09:21

myincas

1,500
10
15

here's a not so cute way of setting the names more generally : `names(df2)[-(1:2)] <- paste0(names(df2)[-(1:2)],c("_start","_complete")[duplicated(names(df2)[-(1:2)])+1])` – moodymudskipper Nov 07 '17 at 10:26

score 0 · Answer 2 · answered Nov 07 '17 at 10:09

In case you don't insist on a data.table solution:

library(dplyr) # for pipes `%>%`
library(tidyr) # for `spread`
df %>%
  cbind(spread_grp = c("start","complete")) %>% # adds column which alternates "start" and "complete"
  nest(p1,p2,time)                          %>% # nest the columns we want to spread
  spread(spread_grp,data)                   %>% # spreads our nested column
  unnest(.sep="_")                              # unnest, concatenating the original colum names with the spread_grp values

#   id group complete_p1 complete_p2       complete_time start_p1 start_p2          start_time
# 1  1     1         1.8         1.3 2016-10-09 03:00:00      1.2      1.9 2016-10-09 01:00:00
# 2  1     2         1.8         1.3 2016-10-09 06:00:00      1.2      1.9 2016-10-09 03:00:00
# 3  3     1         1.8         1.3 2016-10-09 12:00:00      1.2      1.9 2016-10-09 09:00:00

The names are not exactly those from your expected output, hopefully that's not an issue.

data

df <- read.table(text="id    p1    p2   time                      group
1     1.2  1.9  '2016-10-09 01:00:00'         1
                 1     1.8  1.3  '2016-10-09 03:00:00'         1
                 1     1.2  1.9  '2016-10-09 03:00:00'         2
                 1     1.8  1.3  '2016-10-09 06:00:00'         2
                 3     1.2  1.9  '2016-10-09 09:00:00'         1
                 3     1.8  1.3  '2016-10-09 12:00:00'         1",stringsAsFactor = FALSE,header=TRUE)

How to reshape a dataframe by similar columns

2 Answers2