Calculate average based on condition

Question

I have a table

Country ClaimId ClaimItem   ClaimAmt
IN      C1      1           100
IN      C1      2           200
US      C2      1           100
US      C2      2           100
US      C2      3           100
US      C3      1           100
US      C3      2           100
UK      C4      1           100
UK      C4      2           200
UK      C1      1           100
UK      C1      2           200

Here I want to calculate average per country per claimID such that my expected table looks like

Country ClaimId ClaimItem   ClaimAmt  Avg
IN      C1      1           100       300
IN      C1      2           200       300
US      C2      1           100       250
US      C2      2           100       250
US      C2      3           100       250
US      C3      1           100       250
US      C3      2           100       250
UK      C4      1           100       300
UK      C4      2           200       300
UK      C1      1           100       300
UK      C1      2           200       300

Any idea on how the expected table can be achieved. Thanks

Here is the sample

> dput(claims)
structure(list(Country = structure(c(1L, 1L, 3L, 3L, 3L, 3L, 
3L, 2L, 2L, 2L, 2L), .Label = c("IN", "UK", "US"), class = "factor"), 
    ClaimId = structure(c(1L, 1L, 2L, 2L, 2L, 3L, 3L, 4L, 4L, 
    1L, 1L), .Label = c("C1", "C2", "C3", "C4"), class = "factor"), 
    ClaimItem = c(1L, 2L, 1L, 2L, 3L, 1L, 2L, 1L, 2L, 1L, 2L), 
    ClaimAmt = c(100L, 200L, 100L, 100L, 100L, 100L, 100L, 100L, 
    200L, 100L, 200L)), .Names = c("Country", "ClaimId", "ClaimItem", 
"ClaimAmt"), class = "data.frame", row.names = c(NA, -11L))

How come the average of `100` and `200` is `300`? (`ClaimId == 'C1'`). — Rui Barradas, Apr 24 '18 at 11:21
@Deepesh You have to mention that you want to sum for each (Country, ClaimId) and then to average over the sums for each Country. — jogo, Apr 24 '18 at 12:54

jogo · Answer 1 · 2018-04-24T18:25:46.977

Here is a solution with data.table:

claims <- 
structure(list(Country = structure(c(1L, 1L, 3L, 3L, 3L, 3L, 3L, 2L, 2L, 2L, 2L), 
  .Label = c("IN", "UK", "US"), class = "factor"), 
ClaimId = structure(c(1L, 1L, 2L, 2L, 2L, 3L, 3L, 4L, 4L, 1L, 1L), 
 .Label = c("C1", "C2", "C3", "C4"), class = "factor"), 
ClaimItem = c(1L, 2L, 1L, 2L, 3L, 1L, 2L, 1L, 2L, 1L, 2L), 
ClaimAmt = c(100L, 200L, 100L, 100L, 100L, 100L, 100L, 100L, 200L, 100L, 200L)), 
 .Names = c("Country", "ClaimId", "ClaimItem", "ClaimAmt"), 
class = "data.frame", row.names = c(NA, -11L))

library("data.table")
setDT(claims)
claims[, Avg:=sum(ClaimAmt)/uniqueN(ClaimId), Country][]

# > claims[, Avg:=sum(ClaimAmt)/uniqueN(ClaimId), Country][]
#     Country ClaimId ClaimItem ClaimAmt Avg
#  1:      IN      C1         1      100 300
#  2:      IN      C1         2      200 300
#  3:      US      C2         1      100 250
#  4:      US      C2         2      100 250
#  5:      US      C2         3      100 250
#  6:      US      C3         1      100 250
#  7:      US      C3         2      100 250
#  8:      UK      C4         1      100 300
#  9:      UK      C4         2      200 300
# 10:      UK      C1         1      100 300
# 11:      UK      C1         2      200 300

score 1 · Accepted Answer · answered Apr 24 '18 at 13:48

Consider the ratio of two base R ave calls for sum of ClaimAmt by Country and then length of unique ClaimID also by Country:

claims$Avg <- with(claims, ave(ClaimAmt, Country, FUN=sum) /
                    ave(as.integer(ClaimId), Country, FUN=function(g) length(unique(g)))
                   )    
claims

#    Country ClaimId ClaimItem ClaimAmt Avg
# 1       IN      C1         1      100 300
# 2       IN      C1         2      200 300
# 3       US      C2         1      100 250
# 4       US      C2         2      100 250
# 5       US      C2         3      100 250
# 6       US      C3         1      100 250
# 7       US      C3         2      100 250
# 8       UK      C4         1      100 300
# 9       UK      C4         2      200 300
# 10      UK      C1         1      100 300
# 11      UK      C1         2      200 300

Calculate average based on condition

2 Answers2