Gather elements in sublist by first element for the purpose of summing

Question

I have data which looks like this:

{{1891, 10, ""}, {1892, 38, ""}, {1897, 24, ""}, {1898, 16,    ""}, {1903, 21, ""}, {1904, 26, ""}, {1905, 13, ""}, {1908, 48,    "TRUE"}, {1908, 15, ""}, {1908, 34, ""}, {1908, 7, ""}, {1909, 7,    ""}, {1909, 173, "TRUE"}, {1909, 27, "TRUE"}, {1909, 14, ""}, {1909,    77, ""}, {1909, 118, ""}, {1910, 136, ""}, {1910, 94,    "TRUE"}, {1910, 7, ""}, {1910, 73, ""}, {1910, 68, ""}, v{1910, 81,    "TRUE"}, {1911, 46, "TRUE"}, {1911, 77, "TRUE"}, {1911, 132,    "TRUE"}, {1911, 15, ""}, {1911, 79, ""}, {1911, 47, ""}, {1911, 13,    ""}}

I want to turn this into a new list grouped by year for the purpose of summing the second values (or averaging them) for a particular year. For example, 1908 has four records: {1908, 48, "TRUE"}, {1908, 15, ""}, {1908, 34, ""}, {1908, 7, ""}. One way I can see to get the totals would be to somehow convert these records to something like this: { 1908, { 48, 15, 34, 7 } }. Then I might be able to sum them like this:

Table[{ data[[i, 1]], Total[data[[i, 2]]] }, {i, Length[data]}]

Althought I am not 100% sure that would work.

GatherBy is perfect for this! – Marius Ladegård Meyer Apr 23 '15 at 20:47 — Marius Ladegård Meyer, Apr 23 '15 at 20:47

score 2 · Accepted Answer · answered Apr 23 '15 at 20:54

data = {{1891, 10, ""}, {1892, 38, ""}, {1897, 24, ""}, {1898, 16, ""}, {1903,
     21, ""}, {1904, 26, ""}, {1905, 13, ""}, {1908, 48, "TRUE"}, {1908, 15, 
    ""}, {1908, 34, ""}, {1908, 7, ""}, {1909, 7, ""}, {1909, 173, 
    "TRUE"}, {1909, 27, "TRUE"}, {1909, 14, ""}, {1909, 77, ""}, {1909, 118, 
    ""}, {1910, 136, ""}, {1910, 94, "TRUE"}, {1910, 7, ""}, {1910, 73, 
    ""}, {1910, 68, ""}, {1910, 81, "TRUE"}, {1911, 46, "TRUE"}, {1911, 77, 
    "TRUE"}, {1911, 132, "TRUE"}, {1911, 15, ""}, {1911, 79, ""}, {1911, 47, 
    ""}, {1911, 13, ""}};

To sum second element by year

{#[[1, 1]], Total[#[[All, 2]]]} & /@
 GatherBy[Most /@ data, First]

{{1891, 10}, {1892, 38}, {1897, 24}, {1898, 16}, {1903, 21}, {1904,
26}, {1905, 13}, {1908, 104}, {1909, 416}, {1910, 459}, {1911, 409}}

To average second element by year

Mean /@ GatherBy[Most /@ data, First]

{{1891, 10}, {1892, 38}, {1897, 24}, {1898, 16}, {1903, 21}, {1904,
26}, {1905, 13}, {1908, 26}, {1909, 208/3}, {1910, 153/2}, {1911, 409/7}}

kglr · Answer 2 · 2015-04-23T21:10:18.500

data= {{1891, 10, ""}, {1892, 38, ""}, {1897, 24, ""}, {1898, 16,    ""}, 
 {1903, 21, ""}, {1904, 26, ""}, {1905, 13, ""}, {1908, 48,    "TRUE"},
 {1908, 15, ""}, {1908, 34, ""}, {1908, 7, ""}, {1909, 7,    ""}, 
 {1909, 173, "TRUE"}, {1909, 27, "TRUE"}, {1909, 14, ""}, {1909,    77, ""},
 {1909, 118, ""}, {1910, 136, ""}, {1910, 94,    "TRUE"}, {1910, 7, ""}, 
 {1910, 73, ""}, {1910, 68, ""}, {1910, 81,    "TRUE"}, {1911, 46, "TRUE"}, 
 {1911, 77, "TRUE"}, {1911, 132,    "TRUE"}, {1911, 15, ""}, {1911, 79, ""}, 
 {1911, 47, ""}, {1911, 13,    ""}};

GroupBy[data[[All,;;2]],First->Last, Total]
(* <|1891->10,1892->38,1897->24,1898->16,1903->21,1904->26,1905->13,
     1908->104,1909->416,1910->459,1911->409 |> *)

List@@@Normal@%
(* {{1891,10},{1892,38},{1897,24},{1898,16},{1903,21},{1904,26},{1905,13},
    {1908,104},{1909,416},{1910,459},{1911,409}} *)

Gather elements in sublist by first element for the purpose of summing

2 Answers2