在R中按总计约束插值DataFrame（人口数据需为整数）

针对你需要满足每年A+B+C=Total且结果为整数的插值需求，直接用na.approx()单独插值各列会打破总和约束，这里提供两种可行的R实现方案：

方法1：先插值趋势再整数修正 #

先对A、B、C做线性插值得到连续值，再根据总和差值调整为整数，尽量保留原始趋势：

library(dplyr)
library(zoo)

# 原始数据
data <- data.frame( Year = c(2000, 2001, 2002, 2003, 2004, 2005),  
                   Total = c(50, 52, 53, 57, 60, 61),   
                       A = c(12, NA, NA, NA, NA, 17),  
                       B = c(22, NA, NA, NA, NA, 24),   
                       C = c(16, NA, NA, NA, NA, 20) )

# 1. 对A/B/C做线性插值（保留小数）
data_interp <- data %>%
  mutate(
    A_interp = na.approx(A),
    B_interp = na.approx(B),
    C_interp = na.approx(C)
  )

# 2. 计算每行插值总和与Total的差值
data_interp <- data_interp %>%
  mutate(
    sum_interp = A_interp + B_interp + C_interp,
    diff = Total - sum_interp
  )

# 3. 按年增量优先级分配差值，转为整数
delta_A <- (17 - 12)/5
delta_B <- (24 - 22)/5
delta_C <- (20 - 16)/5
adjust_order <- order(-abs(c(delta_A, delta_B, delta_C)))
cols <- c("A_interp", "B_interp", "C_interp")[adjust_order]

for (i in 1:nrow(data_interp)) {
  current_diff <- round(data_interp$diff[i])
  temp_vals <- as.numeric(data_interp[i, cols])
  
  for (j in 1:length(cols)) {
    if (current_diff == 0) break
    adjust <- sign(current_diff)
    temp_vals[j] <- temp_vals[j] + adjust
    current_diff <- current_diff - adjust
  }
  
  data_interp[i, cols] <- temp_vals
}

# 生成最终结果
data_final <- data_interp %>%
  mutate(A = as.integer(A_interp), B = as.integer(B_interp), C = as.integer(C_interp)) %>%
  select(Year, Total, A, B, C)

print(data_final)

方法2：基于比例的插值修正 #

先插值各部分占Total的比例，再计算整数结果并修正总和，更贴合人口结构变化趋势：

library(dplyr)
library(zoo)

# 原始数据（若已定义可跳过）
data <- data.frame( Year = c(2000, 2001, 2002, 2003, 2004, 2005),  
                   Total = c(50, 52, 53, 57, 60, 61),   
                       A = c(12, NA, NA, NA, NA, 17),  
                       B = c(22, NA, NA, NA, NA, 24),   
                       C = c(16, NA, NA, NA, NA, 20) )

# 1. 计算基准年份的比例
base_props <- data %>%
  filter(!is.na(A)) %>%
  mutate(
    prop_A = A/Total,
    prop_B = B/Total,
    prop_C = C/Total
  ) %>%
  select(Year, prop_A, prop_B, prop_C)

# 2. 插值所有年份的比例
all_props <- data %>%
  select(Year) %>%
  left_join(base_props, by = "Year") %>%
  mutate(across(starts_with("prop"), na.approx))

# 3. 计算初始整数并修正总和
data_final2 <- data %>%
  left_join(all_props, by = "Year") %>%
  mutate(
    A = round(Total * prop_A),
    B = round(Total * prop_B),
    C = round(Total * prop_C)
  ) %>%
  mutate(sum_rounded = A+B+C, diff = Total - sum_rounded)

# 按比例变化率优先级调整差值
for (i in 1:nrow(data_final2)) {
  current_diff <- data_final2$diff[i]
  if (current_diff == 0) next
  
  delta_prop_A <- (base_props$prop_A[2] - base_props$prop_A[1])/5
  delta_prop_B <- (base_props$prop_B[2] - base_props$prop_B[1])/5
  delta_prop_C <- (base_props$prop_C[2] - base_props$prop_C[1])/5
  adjust_order <- order(-abs(c(delta_prop_A, delta_prop_B, delta_prop_C)))
  cols <- c("A", "B", "C")[adjust_order]
  
  for (j in 1:length(cols)) {
    if (current_diff == 0) break
    adjust <- sign(current_diff)
    data_final2[i, cols[j]] <- data_final2[i, cols[j]] + adjust
    current_diff <- current_diff - adjust
  }
}

# 整理结果
data_final2 <- data_final2 %>% select(Year, Total, A, B, C)
print(data_final2)

方案说明 #

• 方法1优先保留A/B/C各自的线性增长趋势，适合关注单类人口绝对变化的场景
• 方法2优先保留人口结构比例变化，适合关注人口构成相对变化的场景
• 两种方法都会确保最终结果满足A+B+C=Total且为整数的约束

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