R语言：数据框列表提取对角线遇NA问题及简化实现

云原生阿岚

2026-7-23

问题描述

拥有8个调查站点A至H、9个年份（1999、2000、2001、2006、2008、2011、2013、2016、2019）的数据，先通过以下代码生成模拟数据：

library (dplyr)
# create column names for years
years <- c(1999, 2000, 2001, 2006, 2008, 2011, 2013, 2016, 2019)

# create an empty data frame with sites A:H in the first column
df <- data.frame(Site = c("A", "B", "C", "D", "E", "F", "G", "H"),
                 matrix(NA, nrow = 8, ncol = length(years), 
                        dimnames = list(NULL, years)))

# remove the X before the year in the column names
colnames(df)[-1] <- years

# generate random numbers for each site and year
set.seed(123)  # for reproducibility
for (i in 1:nrow(df)) {
  df[i, 2:ncol(df)] <- runif(length(years))
}

随后计算各站点和年份间的欧氏距离，将距离矩阵转为数据框并按Site拆分为数据框列表：

# gather to long form to calculate dist.
df = df %>% gather (key = "Year", value = "Value", c(2:last_col()))

# calculate dist and set as a df
dist.df <- df %>% 
  mutate(YrSi = paste(substr(Year, 3, 4), Site)) %>%
  select(-Year, -Site) %>%
  column_to_rownames(var = "YrSi") %>%
  dist() %>%
  as.matrix() %>%
  as.data.frame()

#split dist.df to a list of dfs per site

dist.df.list = dist.df %>% 
  rownames_to_column("YrSi") %>% 
  separate(YrSi, c("Year", "Site"), sep = " ") %>% 
  mutate (Year =  as.numeric (ifelse (Year == "99", sprintf("19%s", Year),sprintf("20%s", Year)))) %>% # Change to yyyy
  gather(key = "YrSi", value = "Dist", c(3:last_col())) %>% 
  separate(YrSi, c("Year2", "Site2"), sep = " ") %>% 
  mutate (Year2 =  as.numeric (ifelse (Year2 == "99", sprintf("19%s", Year2),sprintf("20%s", Year2)))) %>% # Change to yyyy
  arrange(Site, Year, Site2, Year2) %>%
  spread (key = "Year2", value = "Dist") %>% 
  group_by(Site, Site2) %>% 
  subset (Site == Site2) %>%
  relocate (Year, .after = Site2) %>% 
  group_split()

想要创建新数据框result.df，第一列为Site，后续列为2000年及以后的调查年份，每列对应站点该年份与前一调查年份的欧氏距离，即提取数据框中的对角线数据。为便于处理，将数据转为长格式：

dist.df.list = dist.df %>% 
  rownames_to_column("YrSi") %>% 
  separate(YrSi, c("Year", "Site"), sep = " ") %>% 
  mutate (Year =  as.numeric (ifelse (Year == "99", sprintf("19%s", Year),sprintf("20%s", Year)))) %>% # Change to yyyy
  gather(key = "YrSi", value = "Dist", c(3:last_col())) %>% 
  separate(YrSi, c("Year2", "Site2"), sep = " ") %>% 
  mutate (Year2 =  as.numeric (ifelse (Year2 == "99", sprintf("19%s", Year2),sprintf("20%s", Year2)))) %>% # Change to yyyy
  arrange(Site, Year, Site2, Year2) %>%
  spread (key = "Year2", value = "Dist") %>% 
  group_by(Site, Site2) %>% 
  subset (Site == Site2) %>%
  relocate (Year, .after = Site2) %>% 
  gather (key="Year2", value = "dist", c(4:last_col())) %>%
  group_split()

接着通过for循环生成result.df：

# Initialize an empty data frame to store the results
result.df <- data.frame(Site = character(), stringsAsFactors = FALSE)

# Loop through each data frame in the list
for(i in 1:length(dist.df.list)) {
  # Extract the site name
  site <- dist.df.list[[i]]$Site[1]
  
  # Initialize a new row for the site in the result data frame
  new.row <- data.frame(Site = site, stringsAsFactors = FALSE)
  
  # Loop through each survey year and extract the distance between consecutive years
  for(j in c(2000, 2001, 2006,2008, 2011, 2013, 2016, 2019)) {
    col.name <- as.character(j)
    if(col.name %in% colnames(dist.df.list[[i]])) {
      # Extract the distance value from the test data frame
      dist <- dist.df.list[[i]] %>% 
        filter(Year2 == j) %>% 
        select(dist) %>% 
        pull()
      
      # If the distance value is missing, set it to NA
      if(is.na(dist)) {
        new.row[[col.name]] <- NA
      } else {
        # Otherwise, add the distance value to the new row
        new.row[[col.name]] <- dist
      }
    } else {
      # If the distance column doesn't exist, set the value to NA
      new.row[[col.name]] <- NA
    }
  }
  
  # Add the new row to the result data frame
  result.df <- rbind(result.df, new.row)
}

但得到的result.df全为NA：

result.df

  Site 2000 2001 2006 2008 2011 2013 2016 2019
1    A   NA   NA   NA   NA   NA   NA   NA   NA
2    B   NA   NA   NA   NA   NA   NA   NA   NA
3    C   NA   NA   NA   NA   NA   NA   NA   NA
4    D   NA   NA   NA   NA   NA   NA   NA   NA
5    E   NA   NA   NA   NA   NA   NA   NA   NA
6    F   NA   NA   NA   NA   NA   NA   NA   NA
7    G   NA   NA   NA   NA   NA   NA   NA   NA
8    H   NA   NA   NA   NA   NA   NA   NA   NA

问题原因分析

全NA的核心原因是类型不匹配导致筛选失败：

在最后一次生成dist.df.list的代码中，gather(key="Year2", value = "dist", c(4:last_col()))将列名（字符型年份，如"2000"）转换为Year2列的字符值，但后续循环中用filter(Year2 == j)时，j是数值型（如2000），字符与数值无法匹配，导致筛选结果为空，pull()返回NA。
此外，你的处理逻辑绕了不必要的弯路：计算全局距离矩阵后再拆分筛选，不仅效率低，还容易引入格式、类型错误。

简化实现方法

因为你要的是同一站点自身相邻调查年份的欧氏距离，而单变量的欧氏距离就是两个数值差的绝对值，完全不需要计算全局距离矩阵。直接基于原始数据处理即可，代码简洁且不易出错：

library(dplyr)
library(tidyr)

# 原始模拟数据生成（保留你的代码）
years <- c(1999, 2000, 2001, 2006, 2008, 2011, 2013, 2016, 2019)
df <- data.frame(Site = c("A", "B", "C", "D", "E", "F", "G", "H"),
                 matrix(NA, nrow = 8, ncol = length(years), 
                        dimnames = list(NULL, years)))
colnames(df)[-1] <- years
set.seed(123)
for (i in 1:nrow(df)) {
  df[i, 2:ncol(df)] <- runif(length(years))
}

# 核心处理步骤
result.df <- df %>%
  # 转为长格式
  pivot_longer(cols = -Site, names_to = "Year", values_to = "Value") %>%
  # 年份转数值型
  mutate(Year = as.numeric(Year)) %>%
  # 按站点分组，计算当前年份与前一年份的欧氏距离（单变量即绝对值差）
  group_by(Site) %>%
  arrange(Year, .by_group = TRUE) %>%
  mutate(Dist = abs(Value - lag(Value))) %>%
  # 只保留2000年及以后的距离数据
  filter(Year %in% c(2000, 2001, 2006, 2008, 2011, 2013, 2016, 2019)) %>%
  # 转回宽格式，得到目标结构
  select(Site, Year, Dist) %>%
  pivot_wider(names_from = Year, values_from = Dist) %>%
  ungroup()

# 查看结果
print(result.df)

运行后就能得到正确的非NA结果，逻辑清晰且效率更高。

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