Ignoring NA in calculation while doing calculation on data frame columns

Question

I have a data frame in the form shown below:

structure(list(First = c("3.2", "3.2", "3.2", NA, "3.3", NA, 
NA), `cut-off` = c("100000", "100000", "100000", NA, "100000", 
NA, NA), Second = c("1.1", "1.2", "1.2", NA, "1.2", "1.6", NA
), Bonus = c(NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_), Fixed = c(NA, NA, 
NA, "4000", NA, NA, NA)), row.names = c(NA, -7L), class = c("tbl_df", 
"tbl", "data.frame"))

# A tibble: 6 x 5
  First `cut-off` Second Bonus Fixed
  <chr> <chr>     <chr>  <chr> <chr>
1 3.2   100000    1.1    NA    NA   
2 3.2   100000    1.2    NA    NA   
3 3.2   100000    1.2    NA    NA   
4 NA    NA        NA     NA    4000 
5 3.3   100000    1.2    NA    NA   
6 NA    NA        1.6    NA    NA 
7 NA    NA        NA     NA    NA

What I want to do is calculating a new column called Total which will be calculated from the equation shown below:

Total=(First*cut-off/100)+(second*300000/100)+Bonus+Fixed

The problem I have is that based on the situation, a few cells in each row will be NA but I want to ignore NA and considering it as 0 in mathematical calculation (but I can't replace it with 0 as for some cases when all cells in a row are NA, I want to show NA in Total column). So the expeted result for the above dataframe would be:

  First `cut-off` Second Bonus Fixed   Total
  <chr> <chr>     <chr>  <chr> <chr>
1 3.2   100000    1.1    NA    NA       6500
2 3.2   100000    1.2    NA    NA       6800
3 3.2   100000    1.2    NA    NA       6800 
4 NA    NA        NA     NA    4000     4000
5 3.3   100000    1.2    NA    NA       6900
6 NA    NA        1.6    NA    NA       4800
7 NA    NA        NA     NA    NA       NA

How can I do this?

akrun · Accepted Answer · 2020-11-23 22:20:52Z

1

We can use rowSums after converting the type because some of the column types are character

library(dplyr)
df1 %>%
   type.convert(as.is = TRUE) %>%        
   mutate(Bonus = as.numeric(Bonus), 
   Total=rowSums(cbind(First*`cut-off`/100, 
         (Second*300000/100), Bonus, Fixed), na.rm = TRUE))

-output

# A tibble: 7 x 6
#  First `cut-off` Second Bonus Fixed Total
#  <dbl>     <int>  <dbl> <dbl> <int> <dbl>
#1   3.2    100000    1.1    NA    NA  6500
#2   3.2    100000    1.2    NA    NA  6800
#3   3.2    100000    1.2    NA    NA  6800
#4  NA          NA   NA      NA  4000  4000
#5   3.3    100000    1.2    NA    NA  6900
#6  NA          NA    1.6    NA    NA  4800
#7  NA          NA   NA      NA    NA     0

edited Nov 23, 2020 at 22:20

answered Nov 23, 2020 at 22:12

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yellow_truffle Over a year ago

Thanks akrun. I got error message though saying that Error: Problem with mutate() input Total. x object 'second' not found i Input Total is rowSums(...). Also, not only is Bonus column a charachter column but also other columns. Is there any reason you only converted bounu column? Can you also explain why we have type.convert?

akrun Over a year ago

@Roozbeh_you you have Second as column name instead of second corrected

Collectives™ on Stack Overflow

Ignoring NA in calculation while doing calculation on data frame columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related