sum a column based on another column in R, but skip the rows with NAs

user_15202181 0
Viewed 402

I have this dataframe.

library(tidyverse)
df <- tibble(
  n = c(50, 40, 70),
  WC = c(.012, NA, -.02),
  Dic = c(.03, .06, NA),
  I = c(NA, -.09, .0)
)

I want to sum the values in column n if there is a value in column WC. If there are rows with missing values in column WC, I want to skip them (i.e., value 40 in n should be excluded from sum, because there is a NA in WC row). Then, I want to do the same with columns Dic and I - sum the values in n only if rows in columns Dic and I have no missing values, but skip the rows with NAs - and store the three sums in a vector.

conditional-statements r sum
2 Answers

Here is a base R option :

result <- colSums(df$n * +(!is.na(df[-1])))
result
# WC Dic   I 
#120  90 110

where +(!is.na(df[-1])) returns 1 if the value is not missing and 0 if missing.

+(!is.na(df[-1]))
#     WC Dic I
#[1,]  1   1 0
#[2,]  0   1 1
#[3,]  1   0 1

We multiply this result with n and sum the values columnwise.

user_39629140

We can also do

library(dplyr)
df %>%
    summarise(across(-n, ~ sum(n * !is.na(.))))
# A tibble: 1 x 3
#     WC   Dic     I
#  <dbl> <dbl> <dbl>
#1   120    90   110
user_37322710
Related