如何在R中计算两期面板数据的变量变化（含NA与单期样本）

我有一个包含两期（Wave）的面板数据集，需要计算选定变量从Wave 1到Wave 2的变化值，并将结果作为新变量添加到数据集中。目前遇到两个问题：

• 大量受访者仅参与了第一期或第二期
• 数据中存在较多NA值，导致lag()或diff()函数无法直接使用

示例数据代码：

RespondentID <- c("id_001","id_002","id_002","id_003", "id_004", "id_004", "id_005", "id_005")
Wave <- c(1,1,2,2,1,2,1,2)
Value <- c(2,2,3,4,3,1,NA,3)
panel <- cbind(RespondentID,Wave,Value)

panel

示例数据输出：

RespondentID Wave Value
[1,] "id_001"     "1"  "2"  
[2,] "id_002"     "1"  "2"  
[3,] "id_002"     "2"  "3"  
[4,] "id_003"     "2"  "4"  
[5,] "id_004"     "1"  "3"  
[6,] "id_004"     "2"  "1"  
[7,] "id_005"     "1"  NA   
[8,] "id_005"     "2"  "3"  

期望得到的结果：

RespondentID Wave Value Change
[1,] "id_001"     "1"  "2"   NA    
[2,] "id_002"     "1"  "2"   NA    
[3,] "id_002"     "2"  "3"   "1"   
[4,] "id_003"     "2"  "4"   NA    
[5,] "id_004"     "1"  "3"   NA    
[6,] "id_004"     "2"  "1"   "-2"  
[7,] "id_005"     "1"  NA    NA    
[8,] "id_005"     "2"  "3"   NA   

注意：你用cbind()创建的是字符矩阵，建议先转成数据框并修正变量类型，否则数值计算会出问题。以下是两种可行的处理方法：

方法一：dplyr分组直接计算 #

library(dplyr)

# 转换为数据框并修正变量类型
panel_df <- as.data.frame(panel) %>%
  mutate(Wave = as.integer(Wave),
         Value = as.numeric(Value))

# 分组计算变化值
panel_result <- panel_df %>%
  group_by(RespondentID) %>%
  # 提取当前受访者Wave1的数值，保留NA
  mutate(Wave1_val = ifelse(Wave == 1, Value, NA),
         Wave1_val = max(Wave1_val, na.rm = FALSE),
         # 仅在Wave2行计算差值，其余行设为NA
         Change = ifelse(Wave == 2, Value - Wave1_val, NA)) %>%
  select(-Wave1_val) %>%
  ungroup()

# 查看结果
panel_result

逻辑说明：

• 按受访者ID分组，确保仅计算同一受访者的两期差值
• 自动处理单期受访者：这类受访者无对应Wave数据，Change自动为NA
• 若Wave1的Value为NA，Change也会保持NA，符合预期

方法二：tidyr宽转长间接计算 #

library(tidyr)
library(dplyr)

# 转换为数据框并修正变量类型
panel_df <- as.data.frame(panel) %>%
  mutate(Wave = as.integer(Wave),
         Value = as.numeric(Value))

# 转宽格式，同一受访者两期数据同行
panel_wide <- panel_df %>%
  pivot_wider(names_from = Wave, values_from = Value, names_prefix = "Wave")

# 计算变化值
panel_wide <- panel_wide %>%
  mutate(Change = Wave2 - Wave1)

# 转回长格式并匹配原数据结构
panel_result <- panel_df %>%
  left_join(panel_wide %>% select(RespondentID, Change), by = "RespondentID") %>%
  mutate(Change = ifelse(Wave == 2, Change, NA))

panel_result

逻辑说明：

• 先将长格式数据转为宽格式，让同一受访者的两期数据处于同一行，差值计算更直观
• 通过左连接将差值合并回原长格式数据，仅在Wave2行保留差值，其余行设为NA

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