使用reshape/pivot_wider转换鸟类观测数据时丢失观测值的问题

问题描述 #

我正在尝试将记录20年间6种鸟类观测情况的长数据表data转换为宽表，目标是按地点/日期合并，未观测物种以0填充，但转换过程中出现了观测值丢失的问题。

数据集样例 #

head(data)
COMMON.NAME LOCALITY.ID OBSERVATION.DATE OBSERVATION.COUNT
1 Bonellis_Eagle    L1210237       12/17/2007                 1
2     Boreal_Owl   L11834228         9/3/2020                 1
3   Saker_Falcon   L12137171        6/27/2021                 1
4   Saker_Falcon    L1218263        4/27/2004                 1
5 Brown_Fish_Owl   L13864707        2/26/2021                 1
6 Bonellis_Eagle   L16000115         8/6/2021                 2

具体问题 #

• 统计显示Saker_Falcon共69条观测记录：61次数量为1，2次为2，1次为4
• 使用base R的reshape函数转换后，替换NA为0，发现Saker_Falcon数量为1的记录丢失4条（仅剩57条）：

d_sens = reshape(data, idvar=c('LOCALITY.ID','OBSERVATION.DATE'),
timevar = 'COMMON.NAME', direction='wide')

• 尝试tidyr的pivot_wider时参数错误，触发警告：

data %>%
pivot_wider(names_from=c(LOCALITY.ID,OBSERVATION.DATE), values_from=OBSERVATION.COUNT)

Warning message: Values from OBSERVATION.COUNT are not uniquely identified; output will contain list-cols.

解决方案 #

1. 问题根源 #

• reshape丢失数据：当同一LOCALITY.ID + OBSERVATION.DATE组合下存在同一物种的多条记录时，reshape仅保留最后一条，导致前面的记录被覆盖。
• pivot_wider警告：参数逻辑错误，错误地将地点+日期设为列名来源，正确逻辑应该是物种名为列名，地点+日期为行标识。

2. 正确的转宽步骤 #

先处理重复的LOCALITY.ID + OBSERVATION.DATE + COMMON.NAME组合（对同一地点日期同一物种的观测数量求和），再执行转宽：

library(tidyr)
library(dplyr)

# 聚合重复记录，避免数据覆盖
data_clean <- data %>%
  group_by(LOCALITY.ID, OBSERVATION.DATE, COMMON.NAME) %>%
  summarise(OBSERVATION.COUNT = sum(OBSERVATION.COUNT), .groups = "drop")

# 转换为宽表，未观测物种填充0
data_wide <- data_clean %>%
  pivot_wider(
    id_cols = c(LOCALITY.ID, OBSERVATION.DATE),  # 行唯一标识：地点+日期
    names_from = COMMON.NAME,                    # 列名来源：鸟类物种名
    values_from = OBSERVATION.COUNT,             # 单元格填充值：观测数量
    values_fill = 0                              # 未观测的物种用0填充
  )

3. 结果验证 #

执行以下代码验证Saker_Falcon的记录数是否正确：

sum(data_wide$Saker_Falcon == 1)  # 应返回61
sum(data_wide$Saker_Falcon == 2)  # 应返回2
sum(data_wide$Saker_Falcon == 4)  # 应返回1

4. 保留所有原始观测的方案 #

如果同一地点日期同一物种的多条记录是独立观测，需要保留所有行，可以添加分组内序号作为行标识的一部分：

# 添加分组内序号
data_with_seq <- data %>%
  group_by(LOCALITY.ID, OBSERVATION.DATE, COMMON.NAME) %>%
  mutate(obs_seq = row_number()) %>%
  ungroup()

# 转宽，保留所有原始观测
data_wide_all <- data_with_seq %>%
  pivot_wider(
    id_cols = c(LOCALITY.ID, OBSERVATION.DATE, obs_seq),
    names_from = COMMON.NAME,
    values_from = OBSERVATION.COUNT,
    values_fill = 0
  )

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