如何在r中创建汇总缺失成像数据的标题

Question

我正在处理类似于以下格式的成像数据:

name  side  modality
<chr> <chr> <chr>   
1 alex  right xray    
2 alex  left xray    
3 brad  right xray    
4 brad  left  xray    
5 alex  right ct      
6 alex  left  ct      
7 brad  right ct      
8 alex  right mri     
9 brad  right mri     
10 brad  left  mri

假设每个人都应该有所有模式的左和右图像，它表明Alex缺少左侧MRI, Brad缺少左侧CT, Charlie(根本没有出现在data中)缺少所有图像。我试图创建一个汇总表，显示哪些元素是"存在"或"缺席"，给定的列表names(包括Charlie)。它看起来像这样:

name    left_xray right_xray left_ct right_ct left_mri right_mri n_absent
<chr>   <chr>     <chr>      <chr>   <chr>    <chr>    <chr>        <dbl>
1 alex    present   present    present present  absent   present          1
2 brad    present   present    absent  present  present  present          1
3 charlie absent    absent     absent  absent   absent   absent           6

我已经使用了各种dplyr动词来获取每种模式缺少数据的患者列表，但是我真的不确定从哪里开始创建汇总表。

假数据:

data <- tibble(name = c('alex', 'alex', 'brad', 'brad', 'alex', 'alex', 'brad', 'alex', 'brad', 'brad'),
side = c('right', 'left', 'right', 'left', 'right', 'left', 'right', 'right','right','left'),
modality = c('xray','xray','xray','xray','ct','ct','ct','mri','mri','mri'))
names <- tibble(name = c('alex', 'brad', 'charlie'))

谢谢!

Answer 1

代码

library(dplyr)
library(tidyr)
expand_grid(
name = c('alex', 'brad', 'charlie'),
modality = c("xray","ct","mri"),
side = c("right",'left')
) %>% 
left_join(
data %>% 
mutate(aux = "present")
)  %>% 
mutate(aux = replace_na(aux,"absent")) %>% 
unite(modality_side,side,modality) %>% 
pivot_wider(names_from = modality_side,values_from = aux) %>%
rowwise() %>% 
mutate(n_absent = sum(c_across(-name) == "absent"))

输出

# A tibble: 3 x 8
# Rowwise: 
name    right_xray left_xray right_ct left_ct right_mri left_mri n_absent
<chr>   <chr>      <chr>     <chr>    <chr>   <chr>     <chr>       <int>
1 alex    present    present   present  present present   absent          1
2 brad    present    present   present  absent  present   present         1
3 charlie absent     absent    absent   absent  absent    absent          6

Answer 2

使用name、side和情态的当前和所有可能组合的full_join方法。

library(dplyr)
library(tidyr)
full_join(df %>% mutate(grp = 1), 
setNames(crossing(
unique(unlist(c(df$name, Names))), unique(df$side), unique(df$modality)),
colnames(df)) %>% mutate(grp = 2), c("name", "side", "modality")) %>%
select(name:grp.x) %>% 
mutate(grp.x = if_else(is.na(grp.x), "absent", "present")) %>% 
pivot_wider(names_from=c("side", "modality"), values_from=grp.x) %>% 
rowwise() %>% 
mutate(n_absent = sum(across(contains("_"), ~ .x == "absent"))) %>% 
ungroup()

结果

# A tibble: 3 × 8
name    right_xray left_xray right_ct left_ct right_mri left_mri n_absent
<chr>   <chr>      <chr>     <chr>    <chr>   <chr>     <chr>       <int>
1 alex    present    present   present  present present   absent          1
2 brad    present    present   present  absent  present   present         1
3 charlie absent     absent    absent   absent  absent    absent          6

df <- structure(list(name = c("alex", "alex", "brad", "brad", "alex",
"alex", "brad", "alex", "brad", "brad"), side = c("right", "left",
"right", "left", "right", "left", "right", "right", "right",
"left"), modality = c("xray", "xray", "xray", "xray", "ct", "ct",
"ct", "mri", "mri", "mri")), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -10L))
Names <- structure(list(name = c("alex", "brad", "charlie")), class = c("tbl_df",
"tbl", "data.frame"), row.names = c(NA, -3L))

Answer 3

请尝试

library(tidyverse)
data <- tibble(name = c('alex', 'alex', 'brad', 'brad', 'alex', 'alex', 'brad', 'alex', 'brad', 'brad'),
side = c('right', 'left', 'right', 'left', 'right', 'left', 'right', 'right','right','left'),
modality = c('xray','xray','xray','xray','ct','ct','ct','mri','mri','mri'))
names <- tibble(name = c('alex', 'brad', 'charlie'))
side <- data %>% select(side) %>% unique()
modality <- data %>% select(modality) %>% unique()

data2 <- names %>% full_join(side, by=character(0)) %>% full_join(modality, by=character(0)) %>% mutate(new_col=paste0(side,'_',modality))
data3 <- data2 %>% left_join(data %>% mutate(id='present'), by=c('name','side','modality')) %>% mutate(id=ifelse(is.na(id), 'absent', id)) %>% 
pivot_wider(c('name'), names_from = 'new_col', values_from = 'id') 
named <- names(data3)[2:6]
data4 <- data3 %>% mutate(n_absent=rowSums(.[,named]=='absent'))

Answer 4

您可以首先将side和modality列连接在一起，然后生成它和名称的complete组合。然后把这个"长"形成"宽度"格式化，并计算缺勤次数。

更新我在我的解决方案中添加了full_join(tmp, by = "name")，以适应OP的更新请求。

library(tidyverse)
data %>% mutate(tmp = paste0(side, "_", modality), 
tmp2 = 1, 
.keep = "unused") %>% 
complete(name, tmp) %>% 
pivot_wider(names_from = tmp, values_from = tmp2) %>% 
full_join(tmp, by = "name") %>% 
mutate(across(-name, ~ifelse(is.na(.x), "absent", "present"))) %>% 
rowwise() %>% 
mutate(n_absent = sum(c_across(-name) == "absent")) %>% 
ungroup()
# A tibble: 3 × 8
name    left_ct left_mri left_xray right_ct right_…¹ right…² n_abs…³
<chr>   <chr>   <chr>    <chr>     <chr>    <chr>    <chr>     <int>
1 alex    present absent   present   present  present  present       1
2 brad    absent  present  present   present  present  present       1
3 charlie absent  absent   absent    absent   absent   absent        6
# … with abbreviated variable names ¹​right_mri, ²​right_xray,
#   ³​n_absent