R语言处理测序Excel数据：批量过滤NA并保留物种关联

现有xlsx格式测序数据，第一列为生物科（含数百种生物），其余列为各样本的计数/百分比数据。需要解决以下问题：

• 将计数<5、百分比<0.05的数值设为NA并过滤
• 替代手动逐个处理样本的低效方式，适配不同数据集
• 过滤后保留生物科与样本值的关联，生成可导出至Excel的DataFrame

现有低效代码（手动处理且丢失关联）：

# Files work, I just omitted my directories to place online
my_counts <- read_excel("...Family_120821.xlsx" , sheet = "family_Counts") 
my_perc <- read_excel("...Family_120821.xlsx" , sheet = "family_Percentages")
my_counts[my_counts < 5] <- NA
my_counts
my_perc[my_perc < 0.05] <- NA
my_perc

S13 <- my_counts$family , my_counts$Sample.13
S13A <- na.omit(S13)
S13A

S14 <- my_counts$Sample.14
S14A <- na.omit(S14)
S14A

S15 <- my_counts$Sample.15
S15A <- na.omit(S15)
S15A

...

1. 批量替换NA值 #

直接对所有样本列批量处理，无需逐个指定样本：

library(readxl)
library(dplyr)

# 读取原始数据
my_counts <- read_excel("...Family_120821.xlsx", sheet = "family_Counts")
my_perc <- read_excel("...Family_120821.xlsx", sheet = "family_Percentages")

# 批量处理计数数据：生物科列以外，所有值<5设为NA
my_counts_clean <- my_counts %>%
  mutate(across(-1, ~ifelse(.x < 5, NA, .x)))

# 批量处理百分比数据：生物科列以外，所有值<0.05设为NA
my_perc_clean <- my_perc %>%
  mutate(across(-1, ~ifelse(.x < 0.05, NA, .x)))

2. 过滤NA并保留生物科关联 #

方式一：批量生成单个样本的独立DataFrame #

如果需要每个样本单独的过滤后数据，可批量存储到列表中，每个元素都保留生物科关联：

# 处理计数数据的样本列
count_sample_cols <- colnames(my_counts_clean)[-1]
count_filtered_list <- lapply(count_sample_cols, function(col) {
  my_counts_clean %>%
    select(family, all_of(col)) %>%
    na.omit()
})
names(count_filtered_list) <- count_sample_cols

# 同理处理百分比数据
perc_sample_cols <- colnames(my_perc_clean)[-1]
perc_filtered_list <- lapply(perc_sample_cols, function(col) {
  my_perc_clean %>%
    select(family, all_of(col)) %>%
    na.omit()
})
names(perc_filtered_list) <- perc_sample_cols

# 示例：导出Sample.13的计数过滤数据到Excel
write_excel_csv(count_filtered_list$Sample.13, "Sample13_counts_filtered.csv")

方式二：生成合并式长格式DataFrame #

如果需要将所有样本的有效数据整合为一个表格，适合整体导出：

library(tidyr)

# 计数数据转长格式并过滤NA
count_long <- my_counts_clean %>%
  pivot_longer(-family, names_to = "sample", values_to = "count") %>%
  na.omit()

# 百分比数据转长格式并过滤NA
perc_long <- my_perc_clean %>%
  pivot_longer(-family, names_to = "sample", values_to = "percentage") %>%
  na.omit()

# 可选：合并计数与百分比数据
combined_data <- inner_join(count_long, perc_long, by = c("family", "sample"))

# 导出合并后的数据到Excel
write_excel_csv(combined_data, "filtered_combined_data.csv")

关键说明 #

• 使用dplyr::across实现批量列处理，自动适配任意数量的样本列，无需手动修改代码
• 所有过滤操作始终保留family列，确保生物科与样本值的关联不丢失
• 列表存储或长格式DataFrame都支持直接导出为Excel/CSV格式

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