从特定列中选择值并跳过R中的NA值 [英] Selecting values from specific columns and skipping NA values in R

问题描述

我正在处理癌症登记数据。在下面的数据示例(Ex_Data)中，变量id和诊断_yr代表接受癌症诊断的ID和年份。列x_2005到x_2010和y_2005到y_2010分别代表每个年份(2005到2010)的x和y状态。在我的实际工作数据中，我有很多很多年(2005-2020)的列。我想从最早的可用年、最晚的可用年和诊断年(即，x_est，y_Latest，x_at_诊断，y_at_诊断变量，不包括Nas。例如，对于id1，我想通过跳过Nas从最早的年份提取x值，从最近的年份提取y值。对于诊断年的x和y值，如果诊断年有Nas，我想跳过Nas并提取前一年的可用数据。我如何实现在R中获取所需的变量？

library(tidyverse)

#example data
ex_data <- tribble(
~id,~diagnosis_yr,~x_2005,~x_2006,~x_2007,~x_2008,~x_2009,~x_2010,~y_2005,~y_2006,~y_2007,~y_2008,~y_2009,~y_2010,
1,  2007,   NA, NA, 1,  2,  2,  3,  "a",    "b",    "c",    "d",    "e",    NA, 
2,  2008,   1,  3,  1,  NA, 1,  2,   NA,    "b",    "b",    "e",    "d", "d",
3,  2010,   NA, 2,  2,  2,  3,  NA, "a",    "b",    "c",     NA,     NA,    NA,
4,  2009, 1,    3,  1,  NA, 1,  2,   NA,     NA,     NA,     NA,     NA,    NA,
5,  2005, NA,   1,  1,  2,  2,  3,  "a",    "b",    "c",    "d",    "e",    "e"
)

#wanted variables
wanted <- tribble(
  ~id,~diagnosis_yr,~x_earliest,~y_latest,~x_at_diagnosis,~y_at_diagnosis,
  1,    2007,   1,  "e",    1,  "c",
  2,    2008,   1,  "d",    1,  "e",
  3,    2010,   2,  "c",    3,  "c",
  4,  2009, 1,   NA,  1,  NA,
  5,  2005, 1,  "e", NA,  "a"
)

推荐答案

借助@martin和@tarjae的建议代码和策略，我想分享以下代码(Martin和tarjae建议代码的组合)来回答我的问题(编辑版)。

library (zoo)
library(dplyr)
library(tidyverse) 

ex_data %>% 
  pivot_longer(-c(id, diagnosis_yr), 
               names_to = c(".value", "year"),
               names_pattern = "(.*)_(\d+)") %>% 
  group_by(id) %>% 
  mutate(x_earliest     = first(na.locf(x,fromLast=T,na.rm = F)),
         x_at_diagnosis = last(na.locf(x[diagnosis_yr >= year],na.rm = F)), #na.rm=F is to keep as it is if there is no replacement 
         y_latest       = last(na.locf(y,fromLast=F, na.rm =F)), 
         y_at_diagnosis = last(na.locf(y[diagnosis_yr >= year],na.rm=F))) %>% 
  dplyr::select(id, diagnosis_yr, x_earliest, y_latest, x_at_diagnosis, y_at_diagnosis) %>% 
  distinct() %>% 
  ungroup()

输出

id       diagnosis_yr x_earliest y_latest  x_at_diagnosis  y_at_diagnosis
  <dbl>        <dbl>      <dbl>  <chr>             <dbl> <chr>         
     1         2007          1    e                    1   c             
     2         2008          1    d                    1   e             
     3         2010          2    c                    3   c             
     4         2009          1    NA                   1   NA            
     5         2005          1    e                   NA   a   

这篇关于从特定列中选择值并跳过R中的NA值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！