如何在R语言数据框df中根据列名提取最后一个非NA值

给定如下R语言数据框df，我们需要实现：仅输入目标列名，即可返回该列的最后一个非NA值，无需指定具体日期参数。

示例数据展示 #

date cumul_val1 cumul_val2 month_val1 month_val2
1  2020-05-31   48702.97   45919.59         NA         NA
2  2020-06-30   69403.68   62780.21   20700.71   16860.62
3  2020-07-31   83631.36   75324.61   14227.68   12544.40
4  2020-08-31   98485.95   88454.14   14854.59   13129.53
5  2020-09-30  117072.67  103484.20   18586.72   15030.06
6  2020-10-31  133293.80  116555.76   16221.13   13071.56
7  2020-11-30  150834.45  129492.36   17540.65   12936.60
8  2020-12-31  176086.22  141442.95   25251.77   11950.59
9  2021-02-28         NA   13985.87         NA   13985.87
10 2021-03-31         NA         NA         NA   13589.95
11 2021-04-30         NA         NA         NA   12663.94
12 2021-05-31         NA         NA         NA   14078.32

期望效果（无需指定日期） #

> get_last_non_na(df, "cumul_val1")
[1] 176086.2
> get_last_non_na(df, "cumul_val2")
[1] 13985.87
> get_last_non_na(df, "month_val1")
[1] 25251.77
> get_last_non_na(df, "month_val2")
[1] 14078.32

数据定义 #

df <- structure(list(date = c("2020-05-31", "2020-06-30", "2020-07-31", 
"2020-08-31", "2020-09-30", "2020-10-31", "2020-11-30", "2020-12-31", 
"2021-02-28", "2021-03-31", "2021-04-30", "2021-05-31"), cumul_val1 = c(48702.97, 
69403.68, 83631.36, 98485.95, 117072.67, 133293.8, 150834.45, 
176086.22, NA, NA, NA, NA), cumul_val2 = c(45919.59, 62780.21, 
75324.61, 88454.14, 103484.2, 116555.76, 129492.36, 141442.95, 
13985.87, NA, NA, NA), month_val1 = c(NA, 20700.71, 14227.68, 
14854.59, 18586.72, 16221.13, 17540.65, 25251.77, NA, NA, NA, 
NA), month_val2 = c(NA, 16860.62, 12544.4, 13129.53, 15030.06, 
13071.56, 12936.6, 11950.59, 13985.87, 13589.95, 12663.94, 14078.32
)), class = "data.frame", row.names = c(NA, -12L))

解决方案 #

方法1：基础R自定义函数 #

编写一个通用函数，提取目标列后筛选非NA值，再取最后一个元素：

get_last_non_na <- function(data, col_name) {
  col_data <- data[[col_name]]
  last_val <- tail(col_data[!is.na(col_data)], 1)
  return(last_val)
}

调用示例：

get_last_non_na(df, "cumul_val1")
get_last_non_na(df, "cumul_val2")
get_last_non_na(df, "month_val1")
get_last_non_na(df, "month_val2")

方法2：dplyr工具链实现 #

如果习惯使用tidyverse生态，用dplyr的语法更简洁：

library(dplyr)

get_last_non_na_dplyr <- function(data, col_name) {
  data %>%
    filter(!is.na({{col_name}})) %>%
    pull({{col_name}}) %>%
    tail(1)
}

调用示例：

get_last_non_na_dplyr(df, cumul_val1)
get_last_non_na_dplyr(df, cumul_val2)
get_last_non_na_dplyr(df, month_val1)
get_last_non_na_dplyr(df, month_val2)

临时单行调用（无需函数） #

如果只是单次使用，直接写单行代码即可，比如获取cumul_val1的最后非NA值：

tail(df$cumul_val1[!is.na(df$cumul_val1)], 1)

内容的提问来源于stack exchange，提问作者ah bon