如何创建Total变量：1-2个NA转0求和，全NA则保留NA

Great question! The problem with your current code data$Total <- A + B + C is that R returns NA as soon as any of the values in the sum is NA—which doesn’t align with your rule of treating missing values as 0 unless all three are missing. Here are two straightforward solutions to fix this:

Base R Solution #

This uses rowSums() to handle the NA-to-0 conversion, plus a check to ensure we return NA only when all three variables are missing:

# Example test data matching your scenario
data <- data.frame(
  A = c(10, NA, 10),
  B = c(10, NA, NA),
  C = c(10, NA, NA)
)

# Calculate Total with proper NA handling
data$Total <- ifelse(
  # Check if all three columns are NA in the current row
  rowSums(is.na(data[, c("A", "B", "C")])) == 3,
  NA,  # Return NA if all are missing
  # Sum values, replacing NA with 0 for non-all-missing rows
  rowSums(data[, c("A", "B", "C")], na.rm = TRUE)
)

Running this gives exactly the output you want:

A  B  C Total
1 10 10 10    30
2 NA NA NA    NA
3 10 NA NA    10

Tidyverse (dplyr) Solution #

If you prefer using the tidyverse ecosystem, this approach uses coalesce() to replace NA with 0, and case_when() to handle the all-NA edge case cleanly:

library(dplyr)

data <- data %>%
  mutate(
    Total = case_when(
      # Condition: all three variables are missing
      is.na(A) & is.na(B) & is.na(C) ~ NA_real_,
      # For all other cases, sum each variable (NA becomes 0)
      TRUE ~ coalesce(A, 0) + coalesce(B, 0) + coalesce(C, 0)
    )
  )

This produces the same correct result. coalesce() is ideal here because it returns the first non-NA value—so coalesce(A, 0) gives you A if it exists, otherwise 0.

Both methods handle all your required scenarios perfectly: summing non-missing values, treating isolated NAs as 0, and returning NA only when all three variables are missing.

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