Calculate weighted standard deviation on large dataframe

user_12771047 0
Viewed 303

How can I compute the weighted standard deviation per game round? The dataframe is in reality quite wide (with many players: from r001 up to r100), and long (many game rounds). The weights differ per game round.

df <- data.frame(gameround= c("1_1", "1_2", "1_3"),
  r001 = c(3,5,4), r002 = c(2,3,5), r003 = c(1,2,2), weight001=c(0.7,0.8,0.7), 
                 weight002 = c(0.6,0.1,0.6), weight003=c(0.2,0.7,0.2) ,weightedsd = NA)

 #gameround r001 r002 r003 weight001 weight002 weight003 weightedsd
 # 1_1       3    2    1       0.7       0.6       0.2         NA
 # 1_2       5    3    2       0.8       0.1       0.7         NA
 # 1_3       4    5    2       0.7       0.6       0.2         NA
weighted dplyr tidyverse standard-deviation r
1 Answers

You will need two pivots here:

  1. First, transform the data into long format with respect to the rounds and weights
  2. Next, separate rounds and weights into separate columns.
  3. This creates list columns; as a last preprocessing step we unnest those
long = df %>%
    pivot_longer(matches('\\d{3}'), names_pattern = '(r|weight)\\d+') %>%
    pivot_wider(values_fn = list) %>%
    unnest(c(r, weight))

And now calculating weighted standard deviations is straightforward using its definition:

weighted_var = function (x, w) sum(w * (x - weighted.mean(x, w)) ^ 2) / sum(w)
weighted_sd = function (x, w) sqrt(weighted_var(x, w))

long %>% group_by(gameround) %>% summarize(sd = weighted_sd(r, weight))

# A tibble: 3 x 2
  gameround    sd
  <chr>     <dbl>
1 1_1       0.699
2 1_2       1.46
3 1_3       0.957
user_19680
Related