python - reshape 表以创建按前缀聚合的时间序列

Question

我有一个类似于以下的表格:

name,time_in,time_out
jim,1/1/2000 08:24,1/1/2000 15:24
jim,1/2/2000 08:13,1/2/2000 16:24
jim,1/3/2000 08:14,1/3/2000 15:25
jim,1/4/2000 08:33,1/4/2000 16:23
linda,1/1/2000 08:24,1/1/2000 15:24
mark,1/2/2000 08:13,1/2/2000 16:24
mark,1/3/2000 08:14,1/3/2000 15:25
mark,1/4/2000 08:33,1/4/2000 16:23

忽略实际时间，重点是日子不一致。我所看到的大多数 reshape 都非常漂亮和方形，而我的数据最终可能会被稀疏 reshape 。有没有一种简单的方法(寻找 R 解决方案，但打开 Excel 或任何快速的方法)来创建类似于以下内容的表聚合:

jim,1/1/2000 08:24,1/1/2000 15:24,1/2/2000 08:13,1/2/2000 16:24,1/3/2000 08:14,1/3/2000 15:25,1/4/2000 08:33,1/4/2000 16:23
linda,1/1/2000 08:24,1/1/2000 15:24
mark,1/2/2000 08:13,1/2/2000 16:24,1/3/2000 08:14,1/3/2000 15:25,1/4/2000 08:33,1/4/2000 16:23

每个人都聚合到一行(此时也不知道如何处理标题，请打开以寻求建议。)

Answer 1

考虑这个基本的 R 解决方案，它使用不同名称的运行计数，并按计数将长整形为宽整形:

# RECREATING DATA FRAME
df <- data.frame(name = c("Jim", "Jim", "Jim", "Jim", "linda", "mark", "mark", "mark"),
       time_in = c("1/1/2000 8:24", "1/2/2000 08:13", "1/3/2000 08:14", "1/4/2000 08:33",
                   "1/1/2000 08:24", "1/2/2000 08:13", "1/3/2000 08:14", "1/4/2000 08:33"),
       time_out = c("1/1/2000 15:24", "1/2/2000 16:24", "1/3/2000 15:25", "1/4/2000 16:23",
                    "1/1/2000 15:24", "1/2/2000 16:24", "1/4/2000 15:25", "1/4/2000 16:23"))

# COUNTING BY GROUPED NAMES
df$numcount <- sapply(1:nrow(df),
                      function(i) sum(df[1:i, c("name")] == df$name[i]))

# RESHAPING LONG TO WIDE
reshapedf <- reshape(df, v.names = c("time_in", "time_out"), 
                           timevar=c("numcount"), idvar = c("name"), 
                           direction = "wide")

row.names(reshapedf) <- NULL

reshapedf

输出

   name      time_in.1     time_out.1      time_in.2     time_out.2      time_in.3     time_out.3      time_in.4     time_out.4
1   Jim  1/1/2000 8:24 1/1/2000 15:24 1/2/2000 08:13 1/2/2000 16:24 1/3/2000 08:14 1/3/2000 15:25 1/4/2000 08:33 1/4/2000 16:23
2 linda 1/1/2000 08:24 1/1/2000 15:24           <NA>           <NA>           <NA>           <NA>           <NA>           <NA>
3  mark 1/2/2000 08:13 1/2/2000 16:24 1/3/2000 08:14 1/4/2000 15:25 1/4/2000 08:33 1/4/2000 16:23           <NA>           <NA>