在R中通过创建子集重塑工艺路线数据

Question

我需要转换这个表，根据日期、方向和路线创建Cab.ID子集。

Date          Direction Cab.ID  Route
Sep 24, 2018    Logout  x-1      R1
Sep 24, 2018    Logout  x-2      R1
Sep 24, 2018    Logout  x-1      R2
Sep 24, 2018    Login   x-3      R1
Sep 25, 2018    Login   y-1      R3
Sep 25, 2018    Logout  z-1      R4
Sep 25, 2018    Logout  z-1      R4
Sep 25, 2018    Logout  x-4      R5
Sep 25, 2018    Login   x-4      R5
Sep 26, 2018    Login   x-3      R6
Sep 26, 2018    Login   x-5      R6

所需的表

Date         Route    Login-Cabid   Logout-Cabid
Sep 24, 2018    R1      x-3           x-1,x-2
Sep 24, 2018    R2                    x-1
Sep 25, 2018    R3      y-1 
Sep 25, 2018    R4                    z-1
Sep 25, 2018    R5      x-4           x-4
Sep 26, 2018    R6      x-3,x-5 

谢谢

Answer 1

在base R中，我们可以使用aggregate和reshape

df2 <- aggregate(Cab.ID ~ Date + Direction + Route, unique(df1), toString)

reshape(df2, idvar = c("Date", "Route"), timevar = "Direction", direction = "wide")
#          Date Route Cab.ID.Login Cab.ID.Logout
#1 Sep 24, 2018    R1          x-3      x-1, x-2
#3 Sep 24, 2018    R2         <NA>           x-1
#4 Sep 25, 2018    R3          y-1          <NA>
#5 Sep 25, 2018    R4         <NA>           z-1
#6 Sep 25, 2018    R5          x-4           x-4
#8 Sep 26, 2018    R6     x-3, x-5          <NA>

如果您想要使用tidyverse或data.table，可以使用下面的方法

library(dplyr)
library(tidyr)
df1 %>% 
  unique() %>% 
  group_by(Date, Route, Direction) %>% 
  summarise(Cab.ID = toString(Cab.ID)) %>% 
  spread(Direction, Cab.ID)

或

library(data.table)
setDT(unique(df1))[, .(Cab.ID = toString(Cab.ID)), by = .(Date, Route, Direction)
           ][, dcast(.SD, Date + Route ~ Direction, value.var = 'Cab.ID')]

data

df1 <- structure(list(Date = c("Sep 24, 2018", "Sep 24, 2018", "Sep 24, 2018", 
"Sep 24, 2018", "Sep 25, 2018", "Sep 25, 2018", "Sep 25, 2018", 
"Sep 25, 2018", "Sep 25, 2018", "Sep 26, 2018", "Sep 26, 2018"
), Direction = c("Logout", "Logout", "Logout", "Login", "Login", 
"Logout", "Logout", "Logout", "Login", "Login", "Login"), Cab.ID = c("x-1", 
"x-2", "x-1", "x-3", "y-1", "z-1", "z-1", "x-4", "x-4", "x-3", 
"x-5"), Route = c("R1", "R1", "R2", "R1", "R3", "R4", "R4", "R5", 
"R5", "R6", "R6")), .Names = c("Date", "Direction", "Cab.ID", 
"Route"), class = "data.frame", row.names = c(NA, -11L))

Answer 2

同意markus的观点，您可以在df2 <- aggregate(Cab.ID ~ Date + Direction + Route, df1, toString)之后使用spread {tidyr}

spread(df2, key = Direction, value = Cab.ID)