Reshape Data partially from Wide to Long in R

Question

I have a data frame df that looks like the following:

 id pickuptime    pickupx   pickupy   dropofftime    dropx    dropy
  1 2/1/2013 12:23  73        40    2/1/2013 12:34    73       41
  1 1/1/2013 12:45  73.6      41    1/1/2013 12:57    73.5     41
  2 1/2/2013 13:00  73.45     42    1/2/2013 14:00    73       42
  2 1/2/2013 14:50  73.11     41    1/2/2013 15:30    73       44
  2 1/2/2013 16:00  73.1      41    1/2/2013 18:00    74       42

I want to reorganize it so that the output looks like the following:

  id    time              x     y        pickup_dropoff
   1    2/1/2013 12:23    73    40       pickup
   1    2/1/2013 12:34    73    41       dropoff
   1    1/1/2013 12:45   73.6   41       pickup
   1    1/1/2013 12:57   73.5   41       dropoff
   2    1/2/2013 13:00   73.45  42       pickup
   2    1/2/2013 14:00   73     42       dropoff
   2    1/2/2013 14:50   73.11  41       pickup
   2    1/2/2013 15:30   73     44       dropoff
   2    1/2/2013 16:00   73.1   41       pickup
   2    1/2/2013 18:00    74    42       dropoff

So I've been playing around with the reshape2 package in hopes that this is what I need following this post: http://www.cookbook-r.com/Manipulating_data/Converting_data_between_wide_and_long_format/

since the data seems to be displayed in some iteration of wide format. Perhaps I'm mistaken, as I am still learning R but here is my attempt:

          df2 = melt(df,
          id.vars=c("id", "pickuptime", "pickupx", "pickupy"),
          measure.vars=c("dropofftime", "dropoffy", "dropoffx" ),
                     variable.name="x",
                     value.name="y")

Not sure where to go from here or if I'm even on the right track as I haven't found other examples on the web. Appreciate the help!

score 2 · Accepted Answer · answered Jun 24 '16 at 20:18

I don't know if this is possible using melt, but you could certainly split the two and then stack them. Something like this:

library(reshape2)


df_pickup <- df[,c("id",names(df)[grepl("pick",names(df))])]
names(df_pickup)<- c("id","time","x","y")
df_dropoff <- df[,c("id",names(df)[grepl("drop",names(df))])]
names(df_dropoff)<- c("id","time","x","y")

df2 <- rbind(cbind(df_pickup,pickup_dropoff="pickup"),cbind(df_dropoff,pickup_dropoff="dropoff"))
df2

   id               time     x  y pickup_dropoff
1   1     2/1/2013 12:23 73.00 40         pickup
2   1     1/1/2013 12:45 73.60 41         pickup
3   2     1/2/2013 13:00 73.45 42         pickup
4   2     1/2/2013 14:50 73.11 41         pickup
5   2     1/2/2013 16:00 73.10 41         pickup
6   1     2/1/2013 12:34 73.00 41        dropoff
7   1     1/1/2013 12:57 73.50 41        dropoff
8   2     1/2/2013 14:00 73.00 42        dropoff
9   2     1/2/2013 15:30 73.00 44        dropoff
10  2     1/2/2013 18:00 74.00 42        dropoff

Data:

df<-structure(list(id = c(1L, 1L, 2L, 2L, 2L), pickuptime = structure(c(5L, 
1L, 2L, 3L, 4L), .Label = c(" 1/1/2013 12:45", " 1/2/2013 13:00", 
" 1/2/2013 14:50", " 1/2/2013 16:00", " 2/1/2013 12:23"), class = "factor"), 
    pickupx = c(73, 73.6, 73.45, 73.11, 73.1), pickupy = c(40L, 
    41L, 42L, 41L, 41L), dropofftime = structure(c(5L, 1L, 2L, 
    3L, 4L), .Label = c("    1/1/2013 12:57", "    1/2/2013 14:00", 
    "    1/2/2013 15:30", "    1/2/2013 18:00", "   2/1/2013 12:34"
    ), class = "factor"), dropx = c(73, 73.5, 73, 73, 74), dropy = c(41L, 
    41L, 42L, 44L, 42L), X = c(NA, NA, NA, NA, NA)), .Names = c("id", 
"pickuptime", "pickupx", "pickupy", "dropofftime", "dropx", "dropy", 
"X"), class = "data.frame", row.names = c(NA, -5L))

Reshape Data partially from Wide to Long in R

1 Answers1

Linked