Summing Up and Averaging Rows Based on Column Value

Question

I would like to know how to get the average (or mean) of each individual players stats (note that each player will have more than one row of stats). This is a small selection of the data table and it's defined as AFL_Table.

Player No.  K  H  D CP UP DE. CLG  M CM M50 HO CLR CC ST R50 FF FA T X1PC Bo i50 G B GA  G. TG.  AF  Opp
1        Isaac Smith  16 14 12 26  9 17  88   1  8  0   0  1   1  0  1   1  0  0 4    0  1   3 0 0  1   0  86 107 PORT
2      Taylor Duryea   8 18  2 20  2 18  70   3 10  0   0  0   0  0  0   4  3  0 2    2  1   2 0 0  0   0  80  99 PORT
3       Jack Gunston  19 13  4 17  2 15  76   0  9  0   3  0   1  1  0   2  1  0 1    4  1   3 3 1  0  60  88  98 PORT
4        Josh Gibson   6 15  9 24  7 18  95   2  9  1   0  0   0  0  0   5  0  1 2    8  0   2 0 0  1   0 100  95 PORT
5       Jordan Lewis   3 15 11 26  9 18  65   4  5  0   0  3   4  0  4   1  1  2 2    1  0   6 1 0  0 100  82  94 PORT
6       Sam Mitchell   5 15 12 27 13 14  70   5  3  0   0  1   8  3  5   3  0  2 3    2  1   4 0 0  0   0  89  85 PORT
7     Shaun Burgoyne   9  8  9 17  9  7  88   2  4  0   2  0   4  1  3   1  1  2 7    2  0   1 1 1  0  50  85  84 PORT
8     Grant Birchall  14 15  6 21  3 15  76   1  5  0   0  0   0  0  0   2  0  0 2    2  0   3 0 1  1   0  75  81 PORT
9    Jarryd Roughead   2 10  5 15  4 12  60   2  5  0   3  5   2  0  2   1  0  1 1    3  0   1 2 2  0  50  93  75 PORT
10        Luke Hodge  15 12  7 19  8 11  57   3  2  0   0  5   6  1  5   2  2  2 4    0  0   4 0 0  0   0  86  73 PORT
11  Matthew Suckling   4  9  2 11  3  8  72   3  4  0   1  0   0  0  0   1  1  0 4    3  0   3 2 1  1  50  78  73 PORT
12        Ben McEvoy   7  2 12 14  7  8  92   1  3  2   0 22   3  0  3   1  1  0 2    6  0   0 0 0  0   0  81  70 PORT
13      Ben Stratton  24 10  2 12  0 12  83   2  7  0   0  0   0  0  0   3  0  1 3    5  0   0 0 0  0   0  92  64 PORT
14      Bradley Hill  10 10  7 17  3 14  70   2  3  0   0  0   1  0  1   1  0  0 2    1  0   3 0 1  0   0  77  62 PORT
15     James Frawley  12  7  4 11  3  8  90   1  7  1   1  0   0  0  0   1  0  0 2    3  0   1 0 0  1   0  99  58 PORT
16       Liam Shiels  26  7  8 15  9  7  66   5  2  1   0  0   4  0  4   2  1  3 4    1  0   3 1 0  0 100  86  57 PORT
17      Paul Puopolo  28  4  5  9  8  3  66   1  1  0   1  0   2  1  1   0  4  0 5    0  0   3 1 0  1 100  85  55 PORT
18 Ryan Schoenmakers  25  9  2 11  7  5  63   1  5  1   0  0   1  1  0   1  2  0 1    9  0   2 0 1  0   0  85  53 PORT
19       Cyril Rioli  33  5  5 10  4  4  80   2  3  0   3  0   2  1  1   0  1  1 2    3  0   1 2 0  3 100  86  52 PORT
20       Luke Breust  22 11  1 12  6  7  66   3  3  0   0  1   2  1  1   0  2  1 1    0  1   8 0 0  0   0  74  48 PORT
21        Brian Lake  17  2  3  5  4  1 100   1  2  1   0  0   0  0  0   2  1  0 2    1  0   1 0 0  0   0  56  27 PORT
22     Billy Hartung  40  3  4  7  2  5 100   0  3  1   0  0   1  1  0   2  0  0 0    0  1   0 0 0  0   0  37  26 PORT
24      Jack Gunston  19 15  9 24  9 15  87   3 10  3   1  0   0  0  0   2  0  1 3    2  0   3 4 0  0 100  89 126   BL
25   Jarryd Roughead   2 15 15 30  8 20  73   3  6  0   3  0   5  0  5   0  3  1 1    0  0   4 3 3  2  50  93 118   BL
26       Liam Shiels  26 12 22 34 16 20  70   3  4  0   1  0   7  5  2   0  0  0 4    0  0   4 1 0  0 100  89 114   BL
27        Ben McEvoy   7  8  7 15  9  7  80   1  6  2   1 23   3  2  1   0  4  1 6    2  0   1 1 0  0 100  74 110   BL
28      Bradley Hill  10 21  9 30  2 30  90   4  4  0   0  0   0  0  0   2  0  2 4    1  0   6 1 0  2 100  87 109   BL
29      Jordan Lewis   3 15 16 31 10 21  83   2  5  0   0  2   3  1  2   1  3  1 3    0  0   5 0 0  3   0  82 106   BL
30    Shaun Burgoyne   9  8 21 29 14 16  82   5  4  0   0  0   1  1  0   1  1  2 5    2  0   2 1 0  0 100  85  99   BL
31     James Frawley  12 11  3 14  4 10  78   0  4  0   1  0   0  0  0   1  1  0 4    0  0   3 3 1  1  75  85  87   BL
32       Josh Gibson   6 18  5 23  4 19  78   4  7  1   0  0   0  0  0   2  1  1 0    4  0   3 0 0  1   0 100  83   BL
33        David Hale  20  8  7 15  8  8  60   5  4  0   1 23   3  1  2   1  3  4 3    3  1   2 1 1  1  33  74  83   BL
34  Matthew Suckling   4 10  6 16  3 13  87   2  7  0   0  0   1  0  1   0  0  0 2    0  1   3 2 0  2 100  79  83   BL
35       Cyril Rioli  33 10  9 19  9 10  84   0  3  0   0  0   5  3  2   1  1  0 4    2  1   3 1 2  1  33  70  82   BL
36    Grant Birchall  14 13  8 21  2 16  90   2  4  0   0  0   1  0  1   1  0  2 5    2  0   2 0 0  0   0  83  81   BL
37      James Sicily  21  7  9 16  5 11  81   2  6  1   4  0   2  0  2   0  2  0 2    0  0   0 2 0  1 100  82  79   BL
38       Isaac Smith  16  9  9 18  2 16  83   0  7  0   0  2   0  0  0   1  0  0 1    0  0   5 1 0  0 100  61  78   BL
39       Luke Breust  22  6  8 14  8  6  71   0  5  1   1  0   2  1  1   0  0  0 6    1  0   3 0 1  0   0  85  74   BL
40     Taylor Duryea   8 11  2 13  1 12  76   0  7  1   0  0   0  0  0   4  0  0 4    3  0   1 0 0  1   0  85  74   BL
41       Daniel Howe  41  7  8 15  2 14  93   0  4  0   0  0   1  0  1   3  0  0 5    2  0   0 0 0  0   0  84  69   BL
42        Brian Lake  17  9  2 11  6  8  90   3  6  1   0  0   0  0  0   2  3  2 5    7  0   1 0 0  0   0 100  66   BL
43     Will Langford  29  7  7 14  8  7  78   2  1  1   0  4   5  1  4   1  2  2 7    0  0   5 0 0  1   0  84  66   BL
44      Ben Stratton  24  1  5  6  2  4  66   1  0  0   0  0   0  0  0   0  0  0 5    2  0   1 0 0  1   0  96  33   BL
45     Billy Hartung  40  4  6 10  4  5  70   1  1  0   0  0   0  0  0   0  1  0 0    0  0   2 0 0  0   0  32  28   BL

Also, for note and as requested, the output as per dput(droplevels(head(AFL_Table, 20))) is:

structure(list(Player = c("Isaac Smith", "Taylor Duryea", "Jack Gunston", 
"Josh Gibson", "Jordan Lewis", "Sam Mitchell", "Shaun Burgoyne", 
"Grant Birchall", "Jarryd Roughead", "Luke Hodge", "Matthew Suckling", 
"Ben McEvoy", "Ben Stratton", "Bradley Hill", "James Frawley", 
"Liam Shiels", "Paul Puopolo", "Ryan Schoenmakers", "Cyril Rioli", 
"Luke Breust"), No. = structure(c(5L, 19L, 6L, 17L, 13L, 16L, 
20L, 3L, 7L, 4L, 15L, 18L, 9L, 1L, 2L, 11L, 12L, 10L, 14L, 8L
), .Label = c("10", "12", "14", "15", "16", "19", "2", "22", 
"24", "25", "26", "28", "3", "33", "4", "5", "6", "7", "8", "9"
), class = "factor"), K = structure(c(5L, 7L, 4L, 6L, 6L, 6L, 
12L, 6L, 1L, 3L, 13L, 8L, 1L, 1L, 11L, 11L, 9L, 13L, 10L, 2L), .Label = c("10", 
"11", "12", "13", "14", "15", "18", "2", "4", "5", "7", "8", 
"9"), class = "factor"), H = structure(c(3L, 4L, 5L, 10L, 2L, 
3L, 10L, 7L, 6L, 8L, 4L, 3L, 4L, 8L, 5L, 9L, 6L, 4L, 6L, 1L), .Label = c("1", 
"11", "12", "2", "4", "5", "6", "7", "8", "9"), class = "factor"), 
    D = structure(c(11L, 8L, 6L, 10L, 11L, 12L, 6L, 9L, 5L, 7L, 
    2L, 4L, 3L, 6L, 2L, 5L, 13L, 2L, 1L, 3L), .Label = c("10", 
    "11", "12", "14", "15", "17", "19", "20", "21", "24", "26", 
    "27", "9"), class = "factor"), CP = structure(c(9L, 3L, 3L, 
    7L, 9L, 2L, 9L, 4L, 5L, 8L, 4L, 7L, 1L, 4L, 4L, 9L, 8L, 7L, 
    5L, 6L), .Label = c("0", "13", "2", "3", "4", "6", "7", "8", 
    "9"), class = "factor"), UP = structure(c(5L, 6L, 4L, 6L, 
    6L, 3L, 10L, 4L, 2L, 1L, 11L, 11L, 2L, 3L, 11L, 10L, 7L, 
    9L, 8L, 10L), .Label = c("11", "12", "14", "15", "17", "18", 
    "3", "4", "5", "7", "8"), class = "factor"), DE. = structure(c(11L, 
    6L, 8L, 14L, 4L, 6L, 11L, 8L, 2L, 1L, 7L, 13L, 10L, 6L, 12L, 
    5L, 5L, 3L, 9L, 5L), .Label = c("57", "60", "63", "65", "66", 
    "70", "72", "76", "80", "83", "88", "90", "92", "95"), class = "factor"), 
    CLG = structure(c(2L, 4L, 1L, 3L, 5L, 6L, 3L, 2L, 3L, 4L, 
    4L, 2L, 3L, 3L, 2L, 6L, 2L, 2L, 3L, 4L), .Label = c("0", 
    "1", "2", "3", "4", "5"), class = "factor"), M = structure(c(8L, 
    2L, 9L, 9L, 6L, 4L, 5L, 6L, 6L, 3L, 5L, 4L, 7L, 4L, 7L, 3L, 
    1L, 6L, 4L, 4L), .Label = c("1", "10", "2", "3", "4", "5", 
    "7", "8", "9"), class = "factor"), CM = structure(c(1L, 1L, 
    1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 3L, 1L, 1L, 2L, 2L, 1L, 
    2L, 1L, 1L), .Label = c("0", "1", "2"), class = "factor"), 
    M50 = structure(c(1L, 1L, 4L, 1L, 1L, 1L, 3L, 1L, 4L, 1L, 
    2L, 1L, 1L, 1L, 2L, 1L, 2L, 1L, 4L, 1L), .Label = c("0", 
    "1", "2", "3"), class = "factor"), HO = structure(c(2L, 1L, 
    1L, 1L, 4L, 2L, 1L, 1L, 5L, 5L, 1L, 3L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 2L), .Label = c("0", "1", "22", "3", "5"), class = "factor"), 
    CLR = structure(c(2L, 1L, 2L, 1L, 5L, 7L, 5L, 1L, 3L, 6L, 
    1L, 4L, 1L, 2L, 1L, 5L, 3L, 2L, 3L, 3L), .Label = c("0", 
    "1", "2", "3", "4", "6", "8"), class = "factor"), CC = structure(c(1L, 
    1L, 2L, 1L, 1L, 3L, 2L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 
    2L, 2L, 2L, 2L), .Label = c("0", "1", "3"), class = "factor"), 
    ST = structure(c(2L, 1L, 1L, 1L, 5L, 6L, 4L, 1L, 3L, 6L, 
    1L, 4L, 1L, 2L, 1L, 5L, 2L, 1L, 2L, 2L), .Label = c("0", 
    "1", "2", "3", "4", "5"), class = "factor"), R50 = structure(c(2L, 
    5L, 3L, 6L, 2L, 4L, 2L, 3L, 2L, 3L, 2L, 2L, 4L, 2L, 2L, 3L, 
    1L, 2L, 1L, 1L), .Label = c("0", "1", "2", "3", "4", "5"), class = "factor"), 
    FF = structure(c(1L, 4L, 2L, 1L, 2L, 1L, 2L, 1L, 1L, 3L, 
    2L, 2L, 1L, 1L, 1L, 2L, 5L, 3L, 2L, 3L), .Label = c("0", 
    "1", "2", "3", "4"), class = "factor"), FA = structure(c(1L, 
    1L, 1L, 2L, 3L, 3L, 3L, 1L, 2L, 3L, 1L, 1L, 2L, 1L, 1L, 4L, 
    1L, 1L, 2L, 2L), .Label = c("0", "1", "2", "3"), class = "factor"), 
    T = structure(c(4L, 2L, 1L, 2L, 2L, 3L, 6L, 2L, 1L, 4L, 4L, 
    2L, 3L, 2L, 2L, 4L, 5L, 1L, 2L, 1L), .Label = c("1", "2", 
    "3", "4", "5", "7"), class = "factor"), X1PC = structure(c(1L, 
    3L, 5L, 8L, 2L, 3L, 3L, 3L, 4L, 1L, 4L, 7L, 6L, 2L, 4L, 2L, 
    1L, 9L, 4L, 1L), .Label = c("0", "1", "2", "3", "4", "5", 
    "6", "8", "9"), class = "factor"), Bo = structure(c(2L, 2L, 
    2L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 2L), .Label = c("0", "1"), class = "factor"), i50 = structure(c(4L, 
    3L, 4L, 3L, 6L, 5L, 2L, 4L, 2L, 5L, 4L, 1L, 1L, 4L, 2L, 4L, 
    4L, 3L, 2L, 7L), .Label = c("0", "1", "2", "3", "4", "6", 
    "8"), class = "factor"), G = structure(c(1L, 1L, 4L, 1L, 
    2L, 1L, 2L, 1L, 3L, 1L, 3L, 1L, 1L, 1L, 1L, 2L, 2L, 1L, 3L, 
    1L), .Label = c("0", "1", "2", "3"), class = "factor"), B = structure(c(1L, 
    1L, 2L, 1L, 1L, 1L, 2L, 2L, 3L, 1L, 2L, 1L, 1L, 2L, 1L, 1L, 
    1L, 2L, 1L, 1L), .Label = c("0", "1", "2"), class = "factor"), 
    GA = structure(c(2L, 1L, 1L, 2L, 1L, 1L, 1L, 2L, 1L, 1L, 
    2L, 1L, 1L, 1L, 2L, 1L, 2L, 1L, 3L, 1L), .Label = c("0", 
    "1", "3"), class = "factor"), G. = structure(c(1L, 1L, 4L, 
    1L, 2L, 1L, 3L, 1L, 3L, 1L, 3L, 1L, 1L, 1L, 1L, 2L, 2L, 1L, 
    2L, 1L), .Label = c("0", "100", "50", "60"), class = "factor"), 
    TG. = structure(c(10L, 6L, 11L, 1L, 8L, 12L, 9L, 3L, 14L, 
    10L, 5L, 7L, 13L, 4L, 15L, 10L, 9L, 9L, 10L, 2L), .Label = c("100", 
    "74", "75", "77", "78", "80", "81", "82", "85", "86", "88", 
    "89", "92", "93", "99"), class = "factor"), AF = structure(c(1L, 
    19L, 18L, 17L, 16L, 15L, 14L, 13L, 12L, 11L, 11L, 10L, 9L, 
    8L, 7L, 6L, 5L, 4L, 3L, 2L), .Label = c("107", "48", "52", 
    "53", "55", "57", "58", "62", "64", "70", "73", "75", "81", 
    "84", "85", "94", "95", "98", "99"), class = "factor")), .Names = c("Player", 
"No.", "K", "H", "D", "CP", "UP", "DE.", "CLG", "M", "CM", "M50", 
"HO", "CLR", "CC", "ST", "R50", "FF", "FA", "T", "X1PC", "Bo", 
"i50", "G", "B", "GA", "G.", "TG.", "AF"), row.names = c(NA, 
20L), class = "data.frame")

akrun · Accepted Answer · 2016-03-01T03:49:21.713

0

We can use aggregate

aggregate(.~PlayerNo, AFL_Table[setdiff(names(AFL_Table), "Opp")], 
     FUN= mean, na.rm=TRUE, na.action=NULL)

Or summarise_each from dplyr

library(dplyr)
AFL_Table %>%
      group_by(PlayerNo) %>%
      summarise_each(funs(mean=mean(., na.rm=TRUE)), -matches("Opp"))

Update

Based on the dput output, all the columns are factor class except the "Player" (which is character class). We have to convert the columns to numeric before doing the mean

 AFL_Table %>% 
     mutate_each(funs(as.numeric(as.character(.))), K:AF) %>%
     group_by(Player) %>%
     summarise_each(funs(mean=mean(., na.rm=TRUE)), -matches("No."))

edited Mar 01 '16 at 03:49

answered Feb 29 '16 at 10:57

akrun

874,273
37
540
662

I get a bunch of 'NA' for the dplyr option. – Morts81 Feb 29 '16 at 12:18
The aggregate option gives me values but they don't seem to be correct – Morts81 Feb 29 '16 at 12:18
@Morts81 Please check the corrected option. It is because your data has NA. – akrun Feb 29 '16 at 12:32
All my data is numeric, there are no 'NA' values or blank values. There is the OPP column that is text though. This is stored as a data.frame brought in from csv file. Not sure if that makes a difference, sorry but I'm extremely new to programming. Appreciate all assistance – Morts81 Feb 29 '16 at 12:51
@Morts81 THe "Opp" column makes the difference – akrun Feb 29 '16 at 12:52
@Morts81 Can you try the corrected one. – akrun Feb 29 '16 at 12:54
Still getting the same weird values – Morts81 Feb 29 '16 at 12:55
Player No. K H D CP UP DE. CLG M CM M50 HO CLR CC ST R50 FF FA T X1PC Bo i50 G B GA G. TG. AF Opp 1 Ben McEvoy 24 12.5 8.0 5.5 13.0 19.5 17.0 2.0 6.5 3.0 1.5 4.5 4.0 2.0 3.0 1.5 3.5 1.5 5.0 6.0 1.0 1.5 1.5 1.0 1.0 1.5 10.0 13.0 2 That is McEvoy's 'means' yet from above we can see his mean 'No' should be 7, not 24 – Morts81 Feb 29 '16 at 12:56
@Morts81 By looking at your example in the post, it seems that there are more columns than the column names. – akrun Feb 29 '16 at 12:59
AFL_Table<-read.csv("AFL_Model_Trial.csv") AFL_Table$Player<-gsub("[0-9] ","",gsub("[0-9][0-9] ","",AFL_Table$Player)) AFL_Table<-data.frame(AFL_Table) AFL_Table<-AFL_Table[!AFL_Table$Player=="Player",] – Morts81 Feb 29 '16 at 13:11
That is the code that gives me the table, am I doing something wrong? – Morts81 Feb 29 '16 at 13:11
@Morts81 Can you update your post with the `dput` output i.e. `dput(droplevels(head(AFL_Table, 20)))` – akrun Feb 29 '16 at 13:13
Done, I think maybe the 1L, 2L, etc code is the issue? – Morts81 Feb 29 '16 at 22:28
@Morts81 It's the integer representation. YOu have `factor` columns. It needs to be converted to `numeric`. Please check the updated solution. – akrun Mar 01 '16 at 03:49
That does the job, thankyou so much, very much appreciated. Was doing my head in. – Morts81 Mar 01 '16 at 05:32

Summing Up and Averaging Rows Based on Column Value

1 Answers1

Update