基于NA位置的变量缺失值填充方案（DPLYR框架）

原始数据 #

带缺失值的数据集df #

df <- data.frame(Date = rep(seq(2000:2014), 2),
                 Country=c(rep("Italy",15),rep("Germany",15)),
                 var1= c(NA, NA, NA, NA, NA, 20:21, NA, NA, NA, 27:28, NA, NA, NA, NA, NA, NA, NA, 74:77, NA, NA, 68:70, NA, NA))

数据预览：

> df
   Date Country var1
1  2000   Italy   NA
2  2001   Italy   NA
3  2002   Italy   NA
4  2003   Italy   NA
5  2004   Italy   NA
6  2005   Italy   20
7  2006   Italy   21
8  2007   Italy   NA
9  2008   Italy   NA
10 2009   Italy   NA
11 2010   Italy   27
12 2011   Italy   28
13 2012   Italy   NA
14 2013   Italy   NA
15 2014   Italy   NA
16 2000 Germany   NA
17 2001 Germany   NA
18 2002 Germany   NA
19 2003 Germany   NA
20 2004 Germany   74
21 2005 Germany   75
22 2006 Germany   76
23 2007 Germany   77
24 2008 Germany   NA
25 2009 Germany   NA
26 2010 Germany   68
27 2011 Germany   69
28 2012 Germany   70
29 2013 Germany   NA
30 2014 Germany   NA

目标填充结果数据集df1（参考用） #

df1 <- data.frame(Date = rep(seq(2000:2014), 2),
                 Country=c(rep("Italy",15),rep("Germany",15)),
                 var1= c(15.67052, 16.45405, 17.27675, 18.14059, 19.04762, 20:21, 22.36173, 23.81176, 25.35582, 27:28, 29.03704, 30.11249, 31.22777, 63.12417, 65.68326, 68.34609, 71.11688, 74:77, 73.87488, 70.8766, 68:70, 72.05882, 76.3599))

数据预览：

> df1
   Date Country     var1
1  2000   Italy 15.67052
2  2001   Italy 16.45405
3  2002   Italy 17.27675
4  2003   Italy 18.14059
5  2004   Italy 19.04762
6  2005   Italy 20.00000
7  2006   Italy 21.00000
8  2007   Italy 22.36173
9  2008   Italy 23.81176
10 2009   Italy 25.35582
11 2010   Italy 27.00000
12 2011   Italy 28.00000
13 2012   Italy 29.03704
14 2013   Italy 30.11249
15 2014   Italy 31.22777
16 2000 Germany 63.12417
17 2001 Germany 65.68326
18 2002 Germany 68.34609
19 2003 Germany 71.11688
20 2004 Germany 74.00000
21 2005 Germany 75.00000
22 2006 Germany 76.00000
23 2007 Germany 77.00000
24 2008 Germany 73.87488
25 2009 Germany 70.87660
26 2010 Germany 68.00000
27 2011 Germany 69.00000
28 2012 Germany 70.00000
29 2013 Germany 72.05882
30 2014 Germany 76.35990

缺失值类型 #

数据中的NA分为三类：

• 起始NA：每个国家时间序列开头的连续缺失值
• 中间NA：时间序列中间的非连续/连续数据缺口
• 末尾NA：时间序列末尾因数据未发布产生的缺失值

填充规则要求 #

需基于dplyr框架实现，沿用历史Excel处理逻辑，具体规则如下：

1. 起始NA填充：优先使用当前国家前两个非NA值的增长率倒推填充；若没有足够数据，可参考代理国家的增长率进行填充。
2. 中间NA填充：采用复合年均增长率（CAGR）填充，计算方式为：取缺口前后的有效数值，增长因子 = (后值/前值)^(1/间隔年份数)，然后按该因子逐期填充中间的NA。
3. 末尾NA填充：默认使用当前国家最后两个非NA值的增长率顺推填充；支持针对特定国家，切换为参考代理国家的增长率进行调整。

dplyr实现代码 #

library(dplyr)
library(tidyr)

# 预处理：按国家分组，标记有效数据点并计算基础参数
filled_df <- df %>%
  group_by(Country) %>%
  arrange(Date) %>%
  mutate(
    valid_flag = !is.na(var1),
    # 提取有效日期和值，用于后续填充参考
    valid_date = ifelse(valid_flag, Date, NA),
    valid_value = ifelse(valid_flag, var1, NA)
  ) %>%
  # 向前后填充最近的有效日期和值，覆盖NA区间
  fill(valid_date, valid_value, .direction = "downup") %>%
  # 计算前后有效点的间隔和CAGR因子
  mutate(
    prev_valid_date = lag(valid_date),
    prev_valid_value = lag(valid_value),
    next_valid_date = lead(valid_date),
    next_valid_value = lead(valid_value),
    total_gap_years = next_valid_date - prev_valid_date,
    cagr_factor = ifelse(is.na(var1) & !is.na(total_gap_years),
                        (next_valid_value / prev_valid_value)^(1/total_gap_years),
                        NA)
  ) %>%
  ungroup()

# 处理起始NA：用前两个有效点的增长率倒推
start_growth_df <- filled_df %>%
  group_by(Country) %>%
  filter(valid_flag) %>%
  slice_head(n=2) %>%
  mutate(start_growth = var1[2]/var1[1]) %>%
  select(Country, start_growth, first_valid_date = Date[1], first_valid_value = var1[1]) %>%
  ungroup()

filled_df <- filled_df %>%
  left_join(start_growth_df, by="Country") %>%
  group_by(Country) %>%
  mutate(
    var1 = case_when(
      is.na(var1) & Date < first_valid_date ~
        first_valid_value / (start_growth)^(first_valid_date - Date),
      TRUE ~ var1
    )
  ) %>%
  ungroup()

# 处理中间NA：按CAGR因子逐期填充
filled_df <- filled_df %>%
  group_by(Country) %>%
  arrange(Date) %>%
  mutate(
    # 跟踪当前区间的起始有效数值
    segment_start = ifelse(valid_flag, var1, NA),
    segment_start = fill(segment_start, .direction = "down")$segment_start,
    # 计算当前日期与区间起始的间隔
    year_offset = Date - fill(valid_date, .direction = "down")$valid_date,
    var1 = case_when(
      is.na(var1) & Date > first_valid_date & Date < last(valid_date, na.rm=T) ~
        segment_start * (cagr_factor)^year_offset,
      TRUE ~ var1
    )
  ) %>%
  ungroup()

# 处理末尾NA：默认用自身增长率，支持切换代理国家
end_growth_df <- filled_df %>%
  group_by(Country) %>%
  filter(valid_flag) %>%
  slice_tail(n=2) %>%
  mutate(end_growth = var1[2]/var1[1]) %>%
  select(Country, end_growth, last_valid_date = Date[2], last_valid_value = var1[2]) %>%
  ungroup()

# 示例：设置代理国家为Italy，可按需修改
proxy_country <- "Italy"
proxy_growth <- end_growth_df %>% filter(Country == proxy_country) %>% pull(end_growth)

filled_df <- filled_df %>%
  left_join(end_growth_df, by="Country") %>%
  group_by(Country) %>%
  mutate(
    var1 = case_when(
      # 默认逻辑：用自身末尾增长率填充
      is.na(var1) & Date > last_valid_date ~
        last_valid_value * (end_growth)^(Date - last_valid_date),
      # 可选：特定国家用代理国家增长率，比如Germany
      # is.na(var1) & Date > last_valid_date & Country == "Germany" ~
      #   last_valid_value * (proxy_growth)^(Date - last_valid_date),
      TRUE ~ var1
    )
  ) %>%
  select(Date, Country, var1) %>%
  ungroup()

# 查看最终填充结果
print(filled_df, n=30)

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