解决R中经纬度网格聚合数据合并后的NA值问题

问题原因 #

你的代码出现NA主要有两个核心原因：

• 网格仅基于df_sf的范围生成，若df2_sf的点落在该范围外，聚合后不会生成对应网格的统计值
• 使用st_join()默认是左连接，仅保留左表（aggregated_data_df）的所有行，若某网格只有df的数据、没有df2的数据，就会出现NA值

修正方案 #

1. 生成覆盖两个数据集的统一网格

首先要确保网格范围包含df_sf和df2_sf的所有点，避免部分点落在网格外：

# 合并两个sf对象，获取完整的 bounding box
combined_sf <- rbind(df_sf, df2_sf)
bbox <- st_bbox(combined_sf)

# 基于合并后的范围创建10m×10m网格
grid <- st_make_grid(combined_sf, cellsize = c(10, 10), square = TRUE)
grid_sf <- st_sf(geometry = grid)

2. 使用全连接合并聚合数据

不要用st_join()（默认左连接），而是用dplyr::full_join()结合空间几何匹配，确保所有网格都被保留，同时匹配两个数据集的统计值：

# 合并两个聚合数据集，基于几何完全匹配
merged_data <- full_join(aggregated_data_df, aggregated_data_df2, by = "geometry")

3. 处理NA值（可选，根据业务需求）

如果某些网格只有一个数据集的数据，你可以根据实际情况填充NA，比如填充为0，或者标记为无数据：

# 用0填充NA（如果业务允许无数据视为0）
merged_data <- merged_data %>%
  mutate(
    measurement_avg_df = replace_na(measurement_avg_df, 0),
    measurement_avg_df2 = replace_na(measurement_avg_df2, 0),
    ratio = ifelse(measurement_avg_df2 == 0, NA, measurement_avg_df / measurement_avg_df2)
  )

完整修正后的代码

library(sf)
library(dplyr)

# 示例数据
df <- data.frame(
  lat = c(7, 8, 9),
  lon = c(3, 3, 3),
  measurement = c(10, 20, 30)
)

df2 <- data.frame(
  lat = c(7.00001, 8.00001, 9.00001),
  lon = c(3, 3, 3),
  measurement = c(5, 15, 25)
)

# 转换为sf对象并转投影
df_sf <- st_as_sf(df, coords = c("lon", "lat"), crs = 4326) %>% st_transform(32630)
df2_sf <- st_as_sf(df2, coords = c("lon", "lat"), crs = 4326) %>% st_transform(32630)

# 生成覆盖两个数据集的统一网格
combined_sf <- rbind(df_sf, df2_sf)
grid <- st_make_grid(combined_sf, cellsize = c(10, 10), square = TRUE)
grid_sf <- st_sf(geometry = grid)

# 空间连接与聚合
df_joined <- st_join(df_sf, grid_sf, join = st_intersects)
aggregated_data_df <- df_joined %>%
  group_by(geometry) %>%
  summarize(measurement_avg_df = mean(measurement, na.rm = TRUE), .groups = 'drop')

df2_joined <- st_join(df2_sf, grid_sf, join = st_intersects)
aggregated_data_df2 <- df2_joined %>%
  group_by(geometry) %>%
  summarize(measurement_avg_df2 = mean(measurement, na.rm = TRUE), .groups = 'drop')

# 全连接合并聚合数据
merged_data <- full_join(aggregated_data_df, aggregated_data_df2, by = "geometry")

# 处理NA并计算比值
merged_data <- merged_data %>%
  mutate(
    measurement_avg_df = replace_na(measurement_avg_df, 0),
    measurement_avg_df2 = replace_na(measurement_avg_df2, 0),
    ratio = ifelse(measurement_avg_df2 == 0, NA, measurement_avg_df / measurement_avg_df2)
  )

# 后续转换与坐标提取
merged_data <- merged_data %>%
  st_centroid() %>%
  st_transform(4326) %>%
  mutate(
    lon = st_coordinates(geometry)[, 1],
    lat = st_coordinates(geometry)[, 2]
  ) %>%
  select(-geometry)

额外说明 #

如果你的业务场景中，只有同时存在两个数据集数据的网格才需要保留，可以把full_join()换成inner_join()，这样会自动过滤掉只有单个数据集数据的网格，不会出现NA。

内容的提问来源于stack exchange，提问作者s.eyal