0
$\begingroup$

This is a pretty simple question and I'm wondering how to go about finding a solution. I have the following data.

ttt = data.frame(name=c("A","B","C","D","E","F"),
                 count=c(150,250,350,550,150,50),
                 returns=c(10,50,60,100,80,10),
                 calls_bad=c(5,30,20,15,15,20),
                 weight=c(0.20,0.30,0.40,0.40,0.20,0.10))
ttt

For each name, I'm trying to find the return rate, which is just returns/count. However, I want to know how to weight that by how high or low count is. Name F, which only has a count of 50, should be weighted less (weight is 0.10) than name D, who has a count of 550 (weight is 0.40).

ttt$returns_rate = round(ttt$returns/ttt$count, 2)
ttt
      name count returns weight returns_rate
    1    A   150      10    0.2         0.07
    2    B   250      50    0.3         0.20
    3    C   350      60    0.4         0.17
    4    D   550     100    0.4         0.18
    5    E   150      80    0.2         0.53
    6    F    50      10    0.1         0.20
d1 = ddply(ttt, .(name), function(x) data.frame(score=weighted.mean(x$returns_rate, x$weight)))
d1
  name score
1    A  0.07
2    B  0.20
3    C  0.17
4    D  0.18
5    E  0.53
6    F  0.20

Here are the weights:

Count <= 100 = 10%
Count >= 101 | Count <= 200 = 20%
Count >= 201 | Count <= 300 = 30%
Count >= 301 = 40%

Given what I'm trying to achieve, I thought I'd want a weight average but end up with the following results. However, this can't be right as the returns_rate is equal to the score.

How can I go about getting the rate while accounting for the number of count?

Thanks! I must've fallen asleep in basic math on this one.

EDIT: In lieu of the comments =

Eventually, the plan is to combine the call_bad rate (calls_bad/count) and the returns rate (returns/count) into one 'score'/'metric' for each name. However, because count varies significantly by name, I'm working under the assumption that I need to 'weight' the data in order to account for the impact of small 'counts'.

Basically, I want to find (returns/count) and (calls_bad/count), and combine these values into one value, while accounting for the fact that someone with a count of 50 could influence the data in a bad way, thus why I'm thinking of using weights.

EDIT 2:

ttt = data.frame(name=c("A","B","C","D","E","F"),
                 count=c(150,250,350,550,150,50),
                 returns=c(10,50,60,100,80,10),
                 calls_bad=c(5,30,20,15,15,20),
                 weight=c(0.20,0.30,0.40,0.40,0.20,0.10))

ttt$returns_rate = round(ttt$returns/ttt$count, 2)
    ttt$calls_bad_rate = round(ttt$calls_bad/ttt$count, 2)

ttt

There only two numbers to "combine" or "average", 'returns rate' and 'calls bad rate'.

ttt$combined = round(ttt$returns_rate + ttt$calls_bad_rate / 2, 2)

But given how count varies "significantly" by name, I thoughts weight were appropriate based on the number of count.

$\endgroup$
  • 1
    $\begingroup$ You have stated that you want to weight your data without mentioning why, so there's no basis here to formulate an objective answer to your question. (The only question in evidence is "For each name, I'm trying to find the return rate," whose answer does not involve any weighting.) Please tell us explicitly what you want to achieve. $\endgroup$ – whuber May 7 '13 at 21:31
  • 1
    $\begingroup$ Re the edit: what, exactly, is being combined when you have only one record per name? There doesn't seem to be anything to average in your example. $\endgroup$ – whuber May 7 '13 at 21:39
1
$\begingroup$

I was writing my own weighted average algorithm yesterday and it applies to what you’re looking to do. Your problem is what you're calling returns_rate and the logic behind it. For these examples we have a new field called weighted_score or you could call it weighted_returns if you wanted. The point is that its weighted.

id | name | count | returns | weight | weighted_score
-----------------------------------------------------
1     A      150      10       0.2         2
2     B      250      50       0.3         15
3     C      350      60       0.4         24
4     D      550      100      0.4         40
5     E      150      80       0.2         16
6     F      50       10       0.1         1
-----------------------------------------------------
sums                           1.6         98
weighted_average                           61.3

Logic:

weighted_score = returns*weight
weighted_average = sum(weighted_scores)/sum(weights)

I see that you have some logic to determine the weight however your upper limit is 40%. There really should be 0% through 100% in there. You can also calculate the weight on the fly for any given data set by finding the highest “count” and dividing the “count” in each row by that highest number and this gives you the appropriate 0% - 100% weights.
Example:

id | name | count | returns | weight | weighted_score
-----------------------------------------------------
1     A     150      10       0.2727         2.7
2     B     250      50       0.4545         22.7
3     C     350      60       0.6364         38.2
4     D     550      100      1.0000         100
5     E     150      80       0.2727         21.8
6     F     50       10       0.0909         0.9
-----------------------------------------------------
sums                          2.7273         186.3636
weighted_average                             68.3

Logic:

weight = count/550

Another simpler option that doesn’t require so much processing trying to figure out the appropriate weight is to just create a static variable to use as your ceiling. Any count lower than the ceiling will be weighted with lower importance and anything above the ceiling is weighted at 100%.

id | name | count | returns | weight | weighted_score
-----------------------------------------------------
1     A     150      10       0.4983         2.7
2     B     250      50       0.8306         22.7
3     C     350      60       1.0000         38.2
4     D     550      100      1.0000         100
5     E     150      80       0.4983         21.8
6     F     50       10       0.1661         0.9
-----------------------------------------------------
sums                          3.9934         248.0399
weighted_average                             62.1

Logic:

if(count < 301) weight = count/301
if(count > 301) weight = count/count
$\endgroup$
1
$\begingroup$

Strictly speaking, a true weighted average of your returns rate would be just (sum of returns)/(sum of counts). This would be like your overall mean return rate, but it's technically a weighted average of the individual return rates of each name.

What I'm not sure about is why you're looking to calculate a weighted average. If your eventual goal is to create a metric that measures the performance of each name in a certain way, what role does the weighted average play? It sounds like your concern is that if you create this metric/score for each name, then you're prone to having extreme values for names with small counts, which may be "false alarms" in a certain sense? If that's the case, then what you really want to do is create a credibility-weighting scheme for your metric/score where you combine the overall mean return rate with the individual mean return rate.

Specifically, it would go something like this. For name F, the cred-weighted return rate would be 10/50*Z+(Overall Mean)*(1-Z), and Z=50/(50+K) where K is some count value you consider to be adequate. You can choose K by judgement, and the beauty is that the result is not sensitive to K. As the count goes up, the weight on that name's actual results increases. There is some Bayesian basis for this formula, but that is beyond the scope of this comment :-).

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.