如何计算DAC列值为Yes的Project_Amount列总和（R语言）

问题背景 #

需要计算数据集中DAC列值为"Yes"的项目的Project_Amount总和：

• 数据集共2738条观测，其中827条DAC为"Yes"
• Project_Amount列无NA值，缺失值已用0.00填充
• DAC列仅包含"Yes"和"NA"两种取值（注：此处"NA"为字符串类型，非R原生缺失值）

数据预览：

> head(DAC_indicators)
# A tibble: 6 × 12
  Recipient_Contractor    Project_Title Project_Amount Project_City  DAC
  <chr>                   <chr>                  <dbl> <chr>         <chr>
1 ABAG                    Bay Area EV …         14533. San Francis…   Yes
2 CA EV Alliance          Bay Area Cha…         12474. Fremont        Yes
3 RTC Fuels, LLC dba Pea… Pearson Fuel…         71053  La Mesa        NA
4 Blink Acquisition       Nissan Elect…          6849. El Cajon       Yes
5 Redwood Coast Energy A… North Coast …         70000  Orleans        NA
6 ABAG                    Bay Area EV …          7266. Hollister      NA
# ℹ 8 more variables: Project_Zip <chr>, County <chr>,
#   Disadvantaged_Community_OEHHA <chr>,
#   Disadvantaged_Community_SB535 <chr>,
#   `DAC(CARB)_LIC(CARB)` <chr>,
#   Disadvantaged_Community_CES3 <chr>,
#   Disadvantaged_Community_CES4 <chr>, DAC <chr>

尝试以下代码均返回NA：

> with(DAC_indicators, sum(Project_Amount[DAC == 'Yes']))
[1] NA
> with(DAC_indicators, sum(DAC_indicators$Project_Amount[DAC_indicators$DAC == "Yes"]))
[1] NA

原因分析 #

返回NA的核心原因是：当用DAC == 'Yes'筛选时，若DAC列存在R原生缺失值（而非字符串"NA"），筛选结果会包含NA索引，导致提取的Project_Amount向量混入NA，最终sum()返回NA。即使你认为DAC只有"Yes"和字符串"NA"，也可能存在隐形的原生缺失值。

解决方案 #

方法1：基础R语法（添加na.rm = TRUE） #

直接在sum()中忽略NA值：

with(DAC_indicators, sum(Project_Amount[DAC == 'Yes'], na.rm = TRUE))

方法2：dplyr包（更清晰的筛选逻辑） #

使用tidyverse工具链，先精准筛选再求和，避免索引NA问题：

library(dplyr)
DAC_indicators %>%
  filter(DAC == "Yes") %>%
  summarise(total_dac_amount = sum(Project_Amount))

额外排查：处理可能的字符空格 #

若DAC列的"Yes"存在前后空格（如"Yes "），可先去除空格再筛选：

# 基础R
with(DAC_indicators, sum(Project_Amount[trimws(DAC) == 'Yes'], na.rm = TRUE))

# dplyr
DAC_indicators %>%
  filter(trimws(DAC) == "Yes") %>%
  summarise(total_dac_amount = sum(Project_Amount))

验证DAC列取值 #

若仍有问题，先确认DAC列的所有取值（包括原生缺失值）：

table(DAC_indicators$DAC, useNA = "always")

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