CODEWITHSUNDEEP
rdataframe
Antonio López Ruiz
Antonio López Ruiz

Reputation: 1466

Average in dataframes with subsets of rows - R

I have a dataframe as the following:

Member Category Total
1001      1       5  
1001      2       4  
1001      3       9 
1003      1       7
1003      2       5
1003      3       2
1005      1       2 
1005      3       5

I need to get:

 Member Category Total  Average
    1001      1       5     0.27
    1001      2       4     0.22
    1001      3       9     0.5
    1003      1       7     0.5
    1003      2       5     0.35
    1003      3       2     0.15
    1005      1       2     0.28
    1005      3       5     0.72

That is, the average of totals for each member. For instance, the member 1001 has a total of 18, in which the category 1 represents 27% of the total. Therefore the average would be 0.27.

Upvotes: 0

Views: 52

Answers (3)

akrun
akrun

Reputation: 886938

Or we can use data.table

library(data.table)
setDT(df1)[, Average := round(Total/sum(Total), 2), Member]

Upvotes: 2

nghauran
nghauran

Reputation: 6768

One solution with dplyr

library(dplyr)
df %>% group_by(Member) %>% 
  mutate(Average = Total/sum(Total)) # to round use mutate(Average = round(Total/sum(Total), 2))

# A tibble: 8 x 4
# Groups:   Member [3]
  Member Category Total   Average
   <int>    <int> <int>     <dbl>
1   1001        1     5 0.2777778
2   1001        2     4 0.2222222
3   1001        3     9 0.5000000
4   1003        1     7 0.5000000
5   1003        2     5 0.3571429
6   1003        3     2 0.1428571
7   1005        1     2 0.2857143
8   1005        3     5 0.7142857

Upvotes: 3

BENY
BENY

Reputation: 323226

In base R 

df['Average']=df$Total/ave(df$Total,df$Member,FUN=sum)
df
  Member Category Total   Average
1   1001        1     5 0.2777778
2   1001        2     4 0.2222222
3   1001        3     9 0.5000000
4   1003        1     7 0.5000000
5   1003        2     5 0.3571429
6   1003        3     2 0.1428571
7   1005        1     2 0.2857143
8   1005        3     5 0.7142857

Upvotes: 1

Related Questions