Calculate mean of all groups except the current group

user_15497280 0
Viewed 136

I have a data frame with two grouping variables, 'mkt' and 'mdl', and some values 'pr':

df <- data.frame(mkt = c(1,1,1,1,2,2,2,2,2),
                 mdl = c('a','a','b','b','b','a','b','a','b'),
                 pr = c(120,120,110,110,145,130,145,130, 145))

df

  mkt mdl  pr
1   1   a 120
2   1   a 120
3   1   b 110
4   1   b 110
5   2   b 145
6   2   a 130
7   2   b 145
8   2   a 130
9   2   b 145

Within each 'mkt', the mean 'pr' for each 'mdl' should be calculated as the mean of 'pr' of all other 'mdl' in the same 'mkt', except the current 'mdl'.

For example, for the group defined by mkt == 1 and mdl == a, the 'avgother' is calculated as the average of 'pt' for mkt == 1 (same 'mkt') and mdl == b (all other 'mdl' than the current group a).

Desired result:

#   mkt mdl  pr avgother
# 1   1   a 120      110
# 2   1   a 120      110
# 3   1   b 110      120
# 4   1   b 110      120
# 5   2   b 145      130
# 6   2   a 130      145
# 7   2   b 145      130
# 8   2   a 130      145
# 9   2   b 145      130
mean r group-by average
5 Answers

First get the average of each mkt and mdl values and for each mkt exclude the current value and get the average of remaining values.

library(dplyr)
library(purrr)

df %>%
  group_by(mkt, mdl) %>%
  summarise(avgother = mean(pr)) %>%
  mutate(avgother = map_dbl(row_number(), ~mean(avgother[-.x]))) %>%
  ungroup %>%
  inner_join(df, by = c('mkt', 'mdl'))

#    mkt mdl   avgother    pr
#  <dbl> <chr>    <dbl> <dbl>
#1     1 a          110   120
#2     1 a          110   120
#3     1 b          120   110
#4     1 b          120   110
#5     2 a          145   130
#6     2 a          145   130
#7     2 b          130   145
#8     2 b          130   145
#9     2 b          130   145
user_39629140

Using data.table, calculate sum and length by 'mkt'. Then, within each mkt-mdl group, calculate mean as (mkt sum - group sum) / (mkt length - group length)

library(data.table)
setDT(df)[ , `:=`(s = sum(pr), n = .N), by = mkt]
df[ , avgother := (s - sum(pr)) / (n - .N), by = .(mkt, mdl)]
df[ , `:=`(s = NULL, n = NULL)]
#    mkt mdl  pr avgother
# 1:   1   a 120      110
# 2:   1   a 120      110
# 3:   1   b 110      120
# 4:   1   b 110      120
# 5:   2   b 145      130
# 6:   2   a 130      145
# 7:   2   b 145      130
# 8:   2   a 130      145
# 9:   2   b 145      130
user_18517120

Consider base R with multiple ave calls for different level grouping calculation using the decomposed version of mean with sum / count:

df <- within(df, {
      avgoth <- (ave(pr, mkt, FUN=sum) - ave(pr, mkt, mdl, FUN=sum)) /
                  (ave(pr, mkt, FUN=length) - ave(pr, mkt, mdl, FUN=length))
})

df
#   mkt mdl  pr avgoth
# 1   1   a 120    110
# 2   1   a 120    110
# 3   1   b 110    120
# 4   1   b 110    120
# 5   2   b 145    130
# 6   2   a 130    145
# 7   2   b 145    130
# 8   2   a 130    145
# 9   2   b 145    130
user_14224510

For the sake of completeness, here is another data.table approach which uses grouping by each i, i.e., join and aggregate simultaneously.

For demonstration, an enhanced sample dataset is used which has a third market with 3 products:

df <- data.frame(mkt = c(1,1,1,1,2,2,2,2,2,3,3,3),
                 mdl = c('a','a','b','b','b','a','b','a','b', letters[1:3]),
                 pr = c(120,120,110,110,145,130,145,130, 145, 1:3))

library(data.table)
mdt <- setDT(df)[, .(mdl, s = sum(pr), .N), by = .(mkt)]
df[mdt, on = .(mkt, mdl), avgother := (sum(pr) - s) / (.N - N), by = .EACHI][]

    mkt mdl  pr avgother
 1:   1   a 120    110.0
 2:   1   a 120    110.0
 3:   1   b 110    120.0
 4:   1   b 110    120.0
 5:   2   b 145    130.0
 6:   2   a 130    145.0
 7:   2   b 145    130.0
 8:   2   a 130    145.0
 9:   2   b 145    130.0
10:   3   a   1      2.5
11:   3   b   2      2.0
12:   3   c   3      1.5

The temporay table mdt contains the sum and count of prices within each mkt but replicated for each product mdl within the market:

mdt

    mkt mdl   s N
 1:   1   a 460 4
 2:   1   a 460 4
 3:   1   b 460 4
 4:   1   b 460 4
 5:   2   b 695 5
 6:   2   a 695 5
 7:   2   b 695 5
 8:   2   a 695 5
 9:   2   b 695 5
10:   3   a   6 3
11:   3   b   6 3
12:   3   c   6 3

Having mkt and mdl in mdt allows for grouping by each i (by = .EACHI)

user_38170040

Here is an approach which computes avgother directly by subsetting pr values which do not belong to the actual value of mdl before computing the averages.

This is quite different to the other answers posted so far which justifies to post this as a separate answer, IMHO.

# enhanced sample dataset covering more corner cases
df <- data.frame(mkt = c(1,1,1,1,2,2,2,2,2,3,3,3,4),
                 mdl = c('a','a','b','b','b','a','b','a','b', letters[1:3],'d'),
                 pr = c(120,120,110,110,145,130,145,130, 145, 1:3, 9))

library(data.table)
setDT(df)[, avgother := sapply(mdl, function(m) mean(pr[m != mdl])), by = mkt][]

    mkt mdl  pr avgother
 1:   1   a 120    110.0
 2:   1   a 120    110.0
 3:   1   b 110    120.0
 4:   1   b 110    120.0
 5:   2   b 145    130.0
 6:   2   a 130    145.0
 7:   2   b 145    130.0
 8:   2   a 130    145.0
 9:   2   b 145    130.0
10:   3   a   1      2.5
11:   3   b   2      2.0
12:   3   c   3      1.5
13:   4   d   9      NaN

Difference between approaches

The other answers share more or less the same approach (although implemented in different manners)

  1. compute sums and counts of pr for each mkt
  2. compute sums and counts of prfor each mkt and mdl
  3. subtract mkt/mdl sums and counts from mkt sums and counts
  4. compute avgother

This approach

  • groups by mkt
  • loops through mdl within each mkt,
  • subsets pr to drop values which do not belong to the actual value of mdl
  • before computing mean() directly.

Caveat concerning performance: Although the code essentially is a one-liner it does not imply it is the fastest.

user_38170040
Related