Determine the weighted mean of different columns in a data frame

Question

I have a data frame Mesure and I wish to determine for each row, a weighted mean like this :

weighted_mean = ((mean_Mesure x nbr_Mesure) + (mean_DL x nbr_DL)) / (nbr_Mesure + nbr_DL)

I know there is a weighted.mean function but I failed to obtain a new column "weighted_mean"

And, is it an issue if each row does not necessary have the 4 values to obtain this formula (such as row 6 in Mesure) ?

> head(Mesure)
         Row.names         mean_Mesure nbr_Mesure  mean_DL    nbr_DL
2    Aquatic_moss.BE-7     123            4         542        12
3   Aquatic_moss.CO-57     100            7         117        14         
4   Aquatic_moss.CO-58     120            5         145        12           
5   Aquatic_moss.CO-60     140            5         153        12 
6  Aquatic_moss.CS-134                              146        15

GKi · Answer 1 · 2020-06-30T13:03:39.050

1

In your case you can use your equation like it is to get weighted means per row like:

with(Mesure, ((mean_Mesure * nbr_Mesure) + (mean_DL * nbr_DL)) / (nbr_Mesure + nbr_DL))
#[1] 437.2500 111.3333 137.6471 149.1765       NA

When there are missing values it will return NA. In case NA is 0 you can set it to 0:

Mesure[is.na(Mesure)] <- 0

what gives:

#[1] 437.2500 111.3333 137.6471 149.1765 146.0000

edited Jun 30 '20 at 13:03

answered Jun 30 '20 at 12:46

GKi

37,245
2
26
48

First, thank you for your help. The problem is that for about 30% of the rows (>10000 rows in total in my df), this returns NA while empty spaces should be considered as "0". Is it possible to modify it ? – Sylvain Jun 30 '20 at 12:52

kmacierzanka · Accepted Answer · 2020-06-30T13:20:46.947

You can use the rowwise() function in the new dplyr:

library(dplyr) # 1.0.0

Mesure %>%
        rowwise() %>%
        mutate(weighted.mean = ((mean_Mesure * nbr_Mesure) + (mean_DL * nbr_DL)) / (nbr_Mesure + nbr_DL))

# A tibble: 5 x 6
# Rowwise: 
  Row.names           mean_Mesure nbr_Mesure mean_DL nbr_DL weighted.mean
  <chr>                     <dbl>      <dbl>   <dbl>  <dbl>         <dbl>
1 Aquatic_moss.BE-7           123          4     542     12          437.
2 Aquatic_moss.CO-57          100          7     117     14          111.
3 Aquatic_moss.CO-58          120          5     145     12          138.
4 Aquatic_moss.CO-60          140          5     153     12          149.
5 Aquatic_moss.CS-134          NA         NA     146     15           NA

EDIT

If we want to replace NAs with 0, then we can use the na_replace() function from tidyr:

library(dplyr)
library(tidyr) # 1.1.0

Mesure %>%
        replace_na(list(mean_Mesure = 0,
                        nbr_Mesure = 0,
                        mean_DL = 0,
                        nbr_DL = 0)) %>%
        rowwise() %>%
        mutate(weighted.mean = ((mean_Mesure * nbr_Mesure) + (mean_DL * nbr_DL)) / (nbr_Mesure + nbr_DL))

# A tibble: 5 x 6
# Rowwise: 
  Row.names           mean_Mesure nbr_Mesure mean_DL nbr_DL weighted.mean
  <chr>                     <dbl>      <dbl>   <dbl>  <dbl>         <dbl>
1 Aquatic_moss.BE-7           123          4     542     12          437.
2 Aquatic_moss.CO-57          100          7     117     14          111.
3 Aquatic_moss.CO-58          120          5     145     12          138.
4 Aquatic_moss.CO-60          140          5     153     12          149.
5 Aquatic_moss.CS-134           0          0     146     15          146

DATA

Mesure <- structure(list(Row.names = c("Aquatic_moss.BE-7", "Aquatic_moss.CO-57", 
"Aquatic_moss.CO-58", "Aquatic_moss.CO-60", "Aquatic_moss.CS-134"
), mean_Mesure = c(123, 100, 120, 140, NA), nbr_Mesure = c(4, 
7, 5, 5, NA), mean_DL = c(542, 117, 145, 153, 146), nbr_DL = c(12, 
14, 12, 12, 15)), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

First, thank you for your help. The problem is that for about 30% of the rows (>10000 rows in total in my df), this returns NA while empty spaces should be considered as "0". Is it possible to modify it ? — Sylvain, Jun 30 '20 at 12:53
No new function needed, just 'na_replace()` from `tidyr`. Please see the edit — kmacierzanka, Jun 30 '20 at 13:15

score 1 · Answer 3 · answered Jun 30 '20 at 13:08

You can also use mapply. That way you can use a generic function and pass any columns to it:

df <- read.table(text = "
                         Row.names         mean_Mesure nbr_Mesure  mean_DL    nbr_DL
2 Aquatic_moss.BE-7     123            4         542        12
3 Aquatic_moss.CO-57     100            7         117        14         
4 Aquatic_moss.CO-58     120            5         145        12           
5 Aquatic_moss.CO-60     140            5         153        12 
6 Aquatic_moss.CS-134   NA            NA         146        15 ")


df$mean_Mesure[is.na(df$mean_Mesure)] <- 0
df$nbr_Mesure[is.na(df$nbr_Mesure)] <- 0

df$weighted.mean <- mapply(function(x1,x2,x3,x4) (x1*x2 + x3*x4)/(x2+x4), df$mean_Mesure, df$nbr_Mesure,  df$mean_DL, df$nbr_DL)

Output

Row.names mean_Mesure nbr_Mesure mean_DL nbr_DL weighted.mean
2   Aquatic_moss.BE-7         123          4     542     12      437.2500
3  Aquatic_moss.CO-57         100          7     117     14      111.3333
4  Aquatic_moss.CO-58         120          5     145     12      137.6471
5  Aquatic_moss.CO-60         140          5     153     12      149.1765
6 Aquatic_moss.CS-134           0          0     146     15      146.0000

Determine the weighted mean of different columns in a data frame

3 Answers3