r - 如何将两个变量转置/转换为一行？

Question

我要转这个

id  |  amount |  day
---------------------
 A  |   10    |  0 
 A  |   54    |  8
 A  |   23    |  18
 A  |   43    |  28
 A  |   87    |  51
 B  |   34    |  0
 B  |   76    |  1 
 B  |   12    |  7

进入这个

id | a1 | a2 | a3 | a4 | a5 | d1 | d2 | d3 | d4 | d5 
--------------------------------------------------------
 A | 10 | 54 | 23 | 43 | 87 | 0  |  8 | 18 | 28 | 51
 B | 34 | 76 | 12 | 0  |  0 | 0  |  1 |  7 | 0  | 0

即。通过 id 将 df 的行转置/转换为未知数量的列，将零放在由于长度不等而存在空值的位置。

我已经尝试过

df <- data.frame(id=c('A','A','A','A','A','B','B','B'),    amount=c(10,54,23,43,87,34,76,12), day=c(0,8,18,28,51,0,1,7))
library(reshape2)
x <- dcast(df, id ~ day, mean, value = 'amount')

但这并不完全正确。我该怎么做？

Answer 1

在引入“时间”变量后使用 base R reshape():

df$time <- ave(as.numeric(as.character(df$id)), df$id, FUN = seq_along)
df
#   id amount day time
# 1  A     10   0    1
# 2  A     54   8    2
# 3  A     23  18    3
# 4  A     43  28    4
# 5  A     87  51    5
# 6  B     34   0    1
# 7  B     76   1    2
# 8  B     12   7    3
reshape(df, direction = "wide", idvar="id", timevar="time")
#   id amount.1 day.1 amount.2 day.2 amount.3 day.3 amount.4 day.4 amount.5 day.5
# 1  A       10     0       54     8       23    18       43    28       87    51
# 6  B       34     0       76     1       12     7       NA    NA       NA    NA

可选步骤:

1. 重组列顺序:

   df2 <- df2[c("id", 
                grep("amount", names(df2), value=TRUE), 
                grep("day", names(df2), value = TRUE))]
   

2. 将NA替换为0:

   df2[is.na(df2)] <- 0
   df2
   #   id amount.1 amount.2 amount.3 amount.4 amount.5 day.1 day.2 day.3 day.4 day.5
   # 1  A       10       54       23       43       87     0     8    18    28    51
   # 6  B       34       76       12        0        0     0     1     7     0     0