英文:
How to merge several rows with event data from multiple columns when based on unique character column?
问题
假设我有一个名为d的数据框，其中包含以下内容：
> dpnr age.hl age.hl.time kon.hl kon.hl.time sen.hl sen.hl.time mix.hl mix.hl.time1 A 0 2018-12-31 1 2011-02-01 0 2018-12-31 0 2018-12-312 A 1 2013-10-31 0 2018-12-31 0 2018-12-31 1 2013-10-313 A 0 2018-12-31 0 2018-12-31 1 2016-09-30 0 2018-12-314 B 0 2018-12-31 0 2018-12-31 1 2004-04-30 0 2018-12-315 C 0 2018-12-31 0 2018-12-31 0 2018-12-31 1 2006-01-176 C 1 2008-07-31 0 2018-12-31 1 2009-01-31 0 2018-12-31
其中，d$pnr是唯一的患者标识符，age.hl、kon.hl、sen.hl和mix.hl表示不同的疾病及其对应的时间变量。如果在任何一列中出现1，则不能再在同一列中再次出现1。
我需要合并行，使得d$pnr只出现一次。也就是说，每列的事件数据/信息应合并到同一行中。
我在dplyr中寻找解决方案。
期望的输出：
pnr age.hl age.hl.time kon.hl kon.hl.time sen.hl sen.hl.time mix.hl mix.hl.time1 A 1 2013-10-31 1 2011-02-01 1 2016-09-30 1 2013-10-312 B 0 2018-12-31 0 2018-12-31 1 2004-04-30 0 2018-12-313 C 1 2008-07-31 0 2018-12-31 1 2009-01-31 1 2006-01-17
数据：
d <- data.frame(pnr = c("A", "A", "A", "B", "C", "C"),age.hl = c(0, 1, 0, 0, 0, 1),age.hl.time = c(as.Date("2018-12-31"), as.Date("2013-10-31"), as.Date("2018-12-31"),as.Date("2018-12-31"), as.Date("2018-12-31"), as.Date("2008-07-31")),kon.hl = c(1, 0, 0, 0, 0, 0),kon.hl.time = c(as.Date("2011-02-01"), as.Date("2018-12-31"), as.Date("2018-12-31"),as.Date("2018-12-31"), as.Date("2018-12-31"), as.Date("2018-12-31")),sen.hl = c(0, 0, 1, 1, 0, 1),sen.hl.time = c(as.Date("2018-12-31"), as.Date("2018-12-31"), as.Date("2016-09-30"),as.Date("2004-04-30"), as.Date("2018-12-31"), as.Date("2009-01-31")),mix.hl = c(0, 1, 0, 0, 1, 0),mix.hl.time = c(as.Date("2018-12-31"), as.Date("2013-10-31"), as.Date("2018-12-31"),as.Date("2018-12-31"), as.Date("2006-01-17"), as.Date("2018-12-31")))
英文:
Let's say I have d:
> dpnr age.hl age.hl.time kon.hl kon.hl.time sen.hl sen.hl.time mix.hl mix.hl.time1 A 0 2018-12-31 1 2011-02-01 0 2018-12-31 0 2018-12-312 A 1 2013-10-31 0 2018-12-31 0 2018-12-31 1 2013-10-313 A 0 2018-12-31 0 2018-12-31 1 2016-09-30 0 2018-12-314 B 0 2018-12-31 0 2018-12-31 1 2004-04-30 0 2018-12-315 C 0 2018-12-31 0 2018-12-31 0 2018-12-31 1 2006-01-176 C 1 2008-07-31 0 2018-12-31 1 2009-01-31 0 2018-12-31
d$pnr is a unique patient identifier. age.hl, kon.hl, sen.hl and mix.hl denote different diseases and their corresponding time variable. All patients are censored at 2018-12-31 if 0 event occured.
If 1 occur in any column, it cannot occur again in the same column.
I need to merge rows, so d$pnr only occur one time. I.e, event data/information from each column shall be merged to the same row.
I look for a solution in dplyr.
Expected output:
pnr age.hl age.hl.time kon.hl kon.hl.time sen.hl sen.hl.time mix.hl mix.hl.time1 A 1 2013-10-31 1 2011-02-01 1 2016-09-30 1 2013-10-312 B 0 2018-12-31 0 2018-12-31 1 2004-04-30 0 2018-12-313 C 1 2008-07-31 0 2018-12-31 1 2009-01-31 1 2006-01-17
Data
d <- data.frame(pnr = c("A", "A", "A", "B", "C", "C"),age.hl = c(0, 1, 0, 0, 0, 1),age.hl.time = c(as.Date("2018-12-31"), as.Date("2013-10-31"), as.Date("2018-12-31"),as.Date("2018-12-31"), as.Date("2018-12-31"), as.Date("2008-07-31")),kon.hl = c(1, 0, 0, 0, 0, 0),kon.hl.time = c(as.Date("2011-02-01"), as.Date("2018-12-31"), as.Date("2018-12-31"),as.Date("2018-12-31"), as.Date("2018-12-31"), as.Date("2018-12-31")),sen.hl = c(0, 0, 1, 1, 0, 1),sen.hl.time = c(as.Date("2018-12-31"), as.Date("2018-12-31"), as.Date("2016-09-30"),as.Date("2004-04-30"), as.Date("2018-12-31"), as.Date("2009-01-31")),mix.hl = c(0, 1, 0, 0, 1, 0),mix.hl.time = c(as.Date("2018-12-31"), as.Date("2013-10-31"), as.Date("2018-12-31"),as.Date("2018-12-31"), as.Date("2006-01-17"), as.Date("2018-12-31")))
答案1
得分: 2
你可以使用which.max()函数：
library(dplyr)d %>%summarise(across(ends_with("time"), ~ .x[which.max(get(sub(".time", "", cur_column())))]),across(ends_with("hl"), max),.by = pnr) %>%select(names(d))# pnr age.hl age.hl.time kon.hl kon.hl.time sen.hl sen.hl.time mix.hl mix.hl.time# 1 A 1 2013-10-31 1 2011-02-01 1 2016-09-30 1 2013-10-31# 2 B 0 2018-12-31 0 2018-12-31 1 2004-04-30 0 2018-12-31# 3 C 1 2008-07-31 0 2018-12-31 1 2009-01-31 1 2006-01-17
注意：第一个across()函数中的值依赖于第二个across()函数中的值，所以这两个across()函数不能互换位置。
英文:
You can use which.max():
library(dplyr)d %>%summarise(across(ends_with("time"), ~ .x[which.max(get(sub(".time", "", cur_column())))]),across(ends_with("hl"), max),.by = pnr) %>%select(names(d))# pnr age.hl age.hl.time kon.hl kon.hl.time sen.hl sen.hl.time mix.hl mix.hl.time# 1 A 1 2013-10-31 1 2011-02-01 1 2016-09-30 1 2013-10-31# 2 B 0 2018-12-31 0 2018-12-31 1 2004-04-30 0 2018-12-31# 3 C 1 2008-07-31 0 2018-12-31 1 2009-01-31 1 2006-01-17
Note: The function in the first across() involves the values in the second across(), so the two across() are not exchangeable.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论