我导入了一个表格(表1(，格式如下：
ID Name Age Start Status1 Status2 Status3 Status4 Status5 Status6
01 John 45 2011 25 13 13 12 34 35
02 Mike 23 2017 12 15 18 45 32 25
03 Peter 34 2019 34 12 15 19 24 27
04 Sarah 21 2020 45 46 47 48 49 50
我生成了另一个表(Table2(，如下所示：
ID Name Age Start Year Value
02 Mike 23 2017 3 24
03 Peter 34 2019 1 11
03 Peter 34 2019 2 13
03 Peter 34 2019 3 13
04 Sarah 21 2020 1 42
04 Sarah 21 2020 2 43
04 Sarah 21 2020 3 44
表 2 包含开始(包括开始(后前 3 年的预测值，但前提是尚未过去三年(截至当前年份，2019 年(。 所以在上面的例子中，ID 01 已经超过3年了他们的起始值，所以没有预测;ID 02 经过其起始值两年，因此有一个预测;ID 03 和 04 是零年，超过其起始值，因此有三个预测。
我需要将 Table2 中的值转置到 Table1 中，替换状态值并将它们向右移动。 最终表应如下所示：
ID Name Age Start Status1 Status2 Status3 Status4 Status5 Status6
01 John 45 2011 25 13 13 12 34 35
02 Mike 23 2017 24 12 15 18 45 32
03 Peter 34 2019 11 13 13 34 12 15
04 Sarah 21 2020 42 43 44 45 46 47
如您所愿，ID 01 的行保持不变。 对于 ID 02，旧的状态值已向右移动一个，并且状态 1 已被替换。 对于 ID 03 和 04，旧的"状态"值已向右移动三个位置，并将"状态 1"替换为"状态 3"。 如您所见，状态 4 到 状态 6 中的旧值已被删除。
我对此很满意。 到目前为止，我试图围绕替换制定一些规则，如下所示：
1(如果年份与当前年份(2019(相比超过3年，则保留"状态"列名称。例如表1的第1行。
2( 如果"开始"为 2019 或更高版本，请通过添加 3 来重命名"状态"列。例如，在表 1 的第 3 行和第 4 行中，状态 1 应重命名为状态 4，依此类推。 最后 3 个状态列(状态 4 到状态 6(将被删除。
3(如果Start在3年的2019年(即2017年至2019年(内，则通过添加2016和Start之间的差异来重命名"状态"列。例如，在表 1 的第 2 行中，状态 1 将重命名为状态 2，依此类推。最后一列(状态 6(将被删除。
我可以使用 if/else 对这些进行编码，但我不确定如何使用 select(( 和 left_join(( 将它们放入数据整理管道中以成功执行这些更改。 我正在使用R，但如果它更容易，也可以使用SQL。任何帮助将不胜感激。
附言我很欣赏生成的表格格式奇怪，但不幸的是，这就是我需要制作的。
这是一个data.table的解决方案。大多数答案都归结为merge_row函数，该函数采用预测值和状态值的串联向量，删除NAs，然后在 6 个元素处截断向量。它返回一个列表，因为这是data.table在使用 RHS 时对 RHS 的期望:=。
library(data.table)
set.seed(1234)
## Building data
table1 <- data.table(ID = 1:10,
Name = LETTERS[1:10],
Age = rpois(10, 40),
Start = sample(2015:2020, 10, rep = T),
Status1 = rpois(10, 10),
Status2 = rpois(10, 10),
Status3 = rpois(10, 10),
Status4 = rpois(10, 10),
Status5 = rpois(10, 10),
Status6 = rpois(10, 10))
res = list()
for(i in 1:nrow(table1)) {
if(table1[i,Start] <= 2016) {
res[[i]] <- NULL
} else {
y <- table1[i, Start]
n_y <- min(3, 3 - (2019-y))
res[[i]] <- table1[i,
cbind(.SD[,.(ID, Name, Age, Start)],
Year = seq_len(n_y),
Value = rpois(n_y, 20))]
}
}
table2 <- do.call(rbind, res)
merge_row <- function(x) {
x = x[!is.na(x)][1:6]
return(lapply(1:6, FUN = function(i) x[i]))
}
## Doing the merge
merged_table <- merge(dcast(table2, ID + Name + Age + Start ~ Year, value.var = 'Value'),
table1,
all = TRUE)[,
paste0('Status', 1:6) :=
merge_row(c(`1`, `2`, `3`, Status1,
Status2, Status3, Status4,
Status5, Status6)),
ID][,c('1', '2', '3') := NULL][]
merged_table
#> ID Name Age Start Status1 Status2 Status3 Status4 Status5 Status6
#> 1: 1 A 32 2019 23 22 18 8 15 8
#> 2: 2 B 41 2018 29 17 8 5 11 10
#> 3: 3 C 42 2017 20 11 14 15 13 10
#> 4: 4 D 35 2018 21 19 7 6 12 16
#> 5: 5 E 43 2019 19 17 19 8 14 18
#> 6: 6 F 36 2016 10 8 9 11 12 6
#> 7: 7 G 46 2019 18 19 20 10 13 7
#> 8: 8 H 36 2016 12 14 8 10 11 11
#> 9: 9 I 36 2020 23 16 19 13 6 11
#> 10: 10 J 28 2017 19 20 8 9 7 9
创建于 2019-09-24 由 reprex 软件包 (v0.3.0(
另一种选择是使用 Table1 的长格式 rbind Table2，获取前 6 个值，然后再次透视到宽格式：
idcols <- c("ID","Name","Age","Start")
dcast(
rbindlist(list(Table2,
melt(Table1, id.vars=idcols, variable.name="Year", value.name="Value")))[,
.(Status=paste0("Status", 1:6), Value=head(Value, 6)), idcols],
as.formula(paste(paste(idcols, collapse=" + "), "~ Status")), value.var="Value")
输出：
ID Name Age Start Status1 Status2 Status3 Status4 Status5 Status6
1: 1 John 45 2011 25 13 13 12 34 35
2: 2 Mike 23 2017 24 12 15 18 45 32
3: 3 Peter 34 2019 11 13 13 34 12 15
4: 4 Sarah 21 2020 42 43 44 45 46 47
数据：
library(data.table)
Table1 <- fread("ID Name Age Start Status1 Status2 Status3 Status4 Status5 Status6
01 John 45 2011 25 13 13 12 34 35
02 Mike 23 2017 12 15 18 45 32 25
03 Peter 34 2019 34 12 15 19 24 27
04 Sarah 21 2020 45 46 47 48 49 50")
Table2 <- fread("ID Name Age Start Year Value
02 Mike 23 2017 3 24
03 Peter 34 2019 1 11
03 Peter 34 2019 2 13
03 Peter 34 2019 3 13
04 Sarah 21 2020 1 42
04 Sarah 21 2020 2 43
04 Sarah 21 2020 3 44")