使用R重塑数据，包括“登录”和“注销”时间。

Question

我是R的新手，正在为自己的目的而开展一个项目。 我有这些数据（问题结尾处有可重现的dput）：

     X            datetime  user  state
1    1 2016-02-19 19:13:26 User1 joined
2    2 2016-02-19 19:21:18 User2 joined
3    3 2016-02-19 19:21:33 User1 joined
4    4 2016-02-19 19:35:38 User1 joined
5    5 2016-02-19 19:44:15 User1 joined
6    6 2016-02-19 19:48:55 User1 joined
7    7 2016-02-19 19:52:40 User1 joined
8    8 2016-02-19 19:53:15 User3 joined
9    9 2016-02-19 20:02:34 User3 joined
10  10 2016-02-19 20:13:48 User3 joined
19 637 2016-02-19 19:13:32 User1   left
20 638 2016-02-19 19:25:26 User1   left
21 639 2016-02-19 19:30:30 User2   left
22 640 2016-02-19 19:42:16 User1   left
23 641 2016-02-19 19:47:59 User1   left
24 642 2016-02-19 19:51:06 User1   left
25 643 2016-02-19 20:02:26 User3   left 

我想让它看起来像这样：

    user  joined                left
1   User1 2016-02-19 19:13:26   2016-02-19 19:13:32
2   User2 2016-02-19 19:21:18   2016-02-19 19:30:30
3   User3 2016-02-19 19:53:15   2016-02-19 20:02:26 
4   User1 2016-02-19 19:21:33   2016-02-19 19:25:26
.
.
.

我正在研究tidyr，因为显然涉及到一些重塑，但我无法理解需要做什么。这是否可能（没有循环/大量的过程代码）？我无法理解如何避免的问题是，没有办法知道特定的“左”记录应该连接到特定的“连接”记录。我能找到的示例都涉及静态月份或日期，其他值在其上收集。我应该补充说，并不一定保证所有记录都有“左”值（用户仍然可能“加入”）。

以下是数据样本：

samp <- data.frame(
  X = c(
    1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L,
    15L, 16L, 17L, 18L, 637L, 638L, 639L, 640L, 641L, 642L, 643L,
    644L, 645L, 646L, 647L, 648L, 649L, 650L, 651L
  ),
  datetime = factor(c(
    "2016-02-19 19:13:26", "2016-02-19 19:21:18", "2016-02-19 19:21:33",
    "2016-02-19 19:35:38", "2016-02-19 19:44:15", "2016-02-19 19:48:55",
    "2016-02-19 19:52:40", "2016-02-19 19:53:15", "2016-02-19 20:02:34",
    "2016-02-19 20:13:48", "2016-02-19 20:49:31", "2016-02-19 20:59:58",
    "2016-02-19 21:06:20", "2016-02-19 21:11:15", "2016-02-19 21:11:22",
    "2016-02-19 22:05:18", "2016-02-19 22:05:47", "2016-02-19 22:30:30",
    "2016-02-19 19:13:32", "2016-02-19 19:25:26", "2016-02-19 19:30:30",
    "2016-02-19 19:42:16", "2016-02-19 19:47:59", "2016-02-19 19:51:06",
    "2016-02-19 20:02:26", "2016-02-19 20:13:38", "2016-02-19 20:42:27",
    "2016-02-19 20:48:22", "2016-02-19 21:10:43", "2016-02-19 21:11:13",
    "2016-02-19 21:17:33", "2016-02-19 22:02:45", "2016-02-19 22:05:37"
  )),
  user = factor(rep(
    c(
      "User1", "User2", "User1", "User3", "User4", "User1", "User4", "User3",
      "User1", "User2", "User1", "User3", "User1", "User4", "User1", "User4"
    ),
    c(
      1L, 1L, 5L, 4L, 1L, 2L, 3L, 1L, 2L, 1L, 3L, 3L, 1L, 1L, 2L,
      2L
    )
  )),
  state = factor(rep(c("joined", "left"), c(18L, 15L)))
)

- Tim Coker

---

更多信息：可以安全地假设除了最后一个“joined”记录之外，所有记录都有相应的“left”值（在示例数据集中不是这样。我将真实数据缩小到这里发布）。似乎可以通过用户/状态切割数据集，然后将每个用户离开的日期进行列绑定来解决此问题。ts<-spread(test, state, datetime)可以为数据集准备好很多工作。 - Tim Coker

这里的X列有意义吗，例如作为序列号？ - A. Webb

不，它可以被忽略。 - Tim Coker

这是一个基本的reshape，如果您首先按州使用户ID唯一，则为reshape（samp，drop ='X'，dir ='wide'，idvar ='user'，timevar ='state'，v.names ='datetime'）。 - rawr

Answer 1

使用data.table包中的rowid()函数和dcast()函数：

require(data.table)
dcast(dt, user + rowid(user, state) ~ state, value.var="datetime")

#      user user_1              joined                left
#  1: User1      1 2016-02-19 19:13:26 2016-02-19 19:13:32
#  2: User1      2 2016-02-19 19:21:33 2016-02-19 19:25:26
#  3: User1      3 2016-02-19 19:35:38 2016-02-19 19:42:16
#  4: User1      4 2016-02-19 19:44:15 2016-02-19 19:47:59
#  5: User1      5 2016-02-19 19:48:55 2016-02-19 19:51:06
#  6: User1      6 2016-02-19 19:52:40                <NA>
#  7: User2      1 2016-02-19 19:21:18 2016-02-19 19:30:30
#  8: User3      1 2016-02-19 19:53:15 2016-02-19 20:02:26
#  9: User3      2 2016-02-19 20:02:34                <NA>
# 10: User3      3 2016-02-19 20:13:48                <NA>

- Arun

Answer 2

我们可以利用“left”和“joined”的顺序，为每个用户匹配它们之间的关系。

为此，我将使用 library(data.table)。

library(data.table)
setDT(df)

## order the data by user and datetime
df <- df[order(user, datetime)]
## add an 'order' column, which is a sequence from 1 to lenght()  
## for each user
df[, order := seq(1:.N), by=user]

## split the left and joins
dt_left <- df[state == "left"]
dt_joined <- df[state == "joined"]

## assuming 'left' is after 'joined', shift the 'order' back for left
dt_left[, order := order - 1]

## join user an dorder (and subsetting relevant columns) 
## keeping when there's a 'joined' but not a 'left'
dt <- dt_left[, .(user, order, datetime)][dt_joined[, .(user, order, datetime)], on=c("user", "order"), nomatch=NA]

## rename columns
setnames(dt, c("datetime", "i.datetime"), c("left", "joined"))

     user order                left              joined
 1: User1     1 2016-02-19 19:13:32 2016-02-19 19:13:26
 2: User1     3 2016-02-19 19:25:26 2016-02-19 19:21:33
 3: User1     5 2016-02-19 19:42:16 2016-02-19 19:35:38
 4: User1     7 2016-02-19 19:47:59 2016-02-19 19:44:15
 5: User1     9 2016-02-19 19:51:06 2016-02-19 19:48:55
 6: User1    11 2016-02-19 20:48:22 2016-02-19 19:52:40
 7: User1    13 2016-02-19 21:11:13 2016-02-19 21:06:20
 8: User1    15 2016-02-19 21:17:33 2016-02-19 21:11:15
 9: User2     1 2016-02-19 19:30:30 2016-02-19 19:21:18
10: User3     1 2016-02-19 20:02:26 2016-02-19 19:53:15
11: User3     3 2016-02-19 20:13:38 2016-02-19 20:02:34
12: User3     5 2016-02-19 20:42:27 2016-02-19 20:13:48
13: User3     7                  NA 2016-02-19 20:49:31
14: User3     8                  NA 2016-02-19 22:30:30
15: User4     1 2016-02-19 21:10:43 2016-02-19 20:59:58
16: User4     3 2016-02-19 22:02:45 2016-02-19 21:11:22
17: User4     5 2016-02-19 22:05:37 2016-02-19 22:05:18
18: User4     7                  NA 2016-02-19 22:05:47

- SymbolixAU

Answer 3

基础版本：

samp$count <- with(samp, ave(as.character(user),list(state,user),FUN=seq_along) )

out <- merge(
  samp[samp$state=="joined",c("user","datetime","count")],
  samp[samp$state=="left",c("user","datetime","count")],
  by=c("user","count"), all.x=TRUE
)

out[order(out$count),]

- thelatemail

Answer 4

另一种方法：

library(tidyr)
df <- df %>% spread(state, datetime)

df_joined <- df[!is.na(df$joined), 2:3]
df_joined <- df_joined[with(df_joined, order(user, joined)), ]

df_left <- df[!is.na(df$left), c(2, 4)]
df_left <- df_left[with(df_left, order(user, left)), ]

merge(df_joined, df_left, all = TRUE, by = 'user')

- ytk

Answer 5

我们需要一个序列号来确定每个用户+状态组内日期时间的顺序。这里使用的序列号是重塑后数据框中已连接-[左侧]记录的有意义连续计数。

使用tidyr中的spread。

spread(within(samp[,-1],seq<-ave(as.numeric(datetime),user,state,FUN=order)),
  state,datetime)



    user seq              joined                left
1  User1   1 2016-02-19 19:13:26 2016-02-19 19:13:32
2  User1   2 2016-02-19 19:21:33 2016-02-19 19:25:26
3  User1   3 2016-02-19 19:35:38 2016-02-19 19:42:16
4  User1   4 2016-02-19 19:44:15 2016-02-19 19:47:59
5  User1   5 2016-02-19 19:48:55 2016-02-19 19:51:06
6  User1   6 2016-02-19 19:52:40 2016-02-19 20:48:22
7  User1   7 2016-02-19 21:06:20 2016-02-19 21:11:13
8  User1   8 2016-02-19 21:11:15 2016-02-19 21:17:33
9  User2   1 2016-02-19 19:21:18 2016-02-19 19:30:30
10 User3   1 2016-02-19 19:53:15 2016-02-19 20:02:26
11 User3   2 2016-02-19 20:02:34 2016-02-19 20:13:38
12 User3   3 2016-02-19 20:13:48 2016-02-19 20:42:27
13 User3   4 2016-02-19 20:49:31                <NA>
14 User3   5 2016-02-19 22:30:30                <NA>
15 User4   1 2016-02-19 20:59:58 2016-02-19 21:10:43
16 User4   2 2016-02-19 21:11:22 2016-02-19 22:02:45
17 User4   3 2016-02-19 22:05:18 2016-02-19 22:05:37
18 User4   4 2016-02-19 22:05:47                <NA>

这也可以用来自reshape2的dcast编写。

dcast(within(samp,seq<-ave(as.numeric(datetime),user,state,FUN=order)),
  user+seq~state, value.var="datetime")

- A. Webb

Answer 6

自从 tidyr 1.0.0 版本以后，以下操作是可行的：

suppressPackageStartupMessages(library(tidyverse))
pivot_wider(samp[-1], names_from = "state", values_from = "datetime", 
            values_fn = list(datetime = list)) %>%
  mutate(left = map2(left, lengths(joined),`length<-`)) %>%
  unchop(everything())

#> # A tibble: 18 x 3
#>   user  joined              left               
#>   <fct> <fct>               <fct>              
#>  1 User1 2016-02-19 19:13:26 2016-02-19 19:13:32
#>  2 User1 2016-02-19 19:21:33 2016-02-19 19:25:26
#>  3 User1 2016-02-19 19:35:38 2016-02-19 19:42:16
#>  4 User1 2016-02-19 19:44:15 2016-02-19 19:47:59
#>  5 User1 2016-02-19 19:48:55 2016-02-19 19:51:06
#>  6 User1 2016-02-19 19:52:40 2016-02-19 20:48:22
#>  7 User1 2016-02-19 21:06:20 2016-02-19 21:11:13
#>  8 User1 2016-02-19 21:11:15 2016-02-19 21:17:33
#>  9 User2 2016-02-19 19:21:18 2016-02-19 19:30:30
#> 10 User3 2016-02-19 19:53:15 2016-02-19 20:02:26
#> 11 User3 2016-02-19 20:02:34 2016-02-19 20:13:38
#> 12 User3 2016-02-19 20:13:48 2016-02-19 20:42:27
#> 13 User3 2016-02-19 20:49:31 NA                 
#> 14 User3 2016-02-19 22:30:30 NA                 
#> 15 User4 2016-02-19 20:59:58 2016-02-19 21:10:43
#> 16 User4 2016-02-19 21:11:22 2016-02-19 22:02:45
#> 17 User4 2016-02-19 22:05:18 2016-02-19 22:05:37
#> 18 User4 2016-02-19 22:05:47 NA 

• values_fn被设置为在列表中存储给定用户的多个值
• 由于它们的长度不同，我们使用mutate和length<-完成短的部分，并用NAs补全
• 然后我们使用unchop进行垂直展开

- moodymudskipper