# How to count number of observations/rows within each group

I have a huge unbalanced panel data. I want to count number of rows within each group. For example, I have the following data:

data ={{AA,1,10},{AA,2,20},{CC,3,30},{CC,4,40},{CC,5,50},{CC,6,60},{CC,7,70},{DD,8,80},
{DD,9,90},{DD,10,100}};


I want to calculate number of rows within each group without using do loop. My result should be

result={{AA,2},{CC,5},{DD,3}};


Any help is greatly appreciated.

For the test case in @rhermans' answer, an approach based on Gather is more than twice as fast as Tally:

ClearAll[tllyF];
tllyF = {#[[1]], Length@#} & /@ Gather[#[[All, 1]]] &;

data1 = Table[{RandomChoice[{AA, BB, CC, DD}], RandomInteger[10],
RandomInteger[10]}, {1000000}];

First[Timing[res0 = Tally[data1[[All, 1]]]]]
(* 0.234375 *)
First[Timing[res1 = (tllyF@data1)]]
(* 0.078125 *)
res0 == res1
(* True *)

data2 = Table[{RandomChoice[{AA, BB, CC, DD}], RandomInteger[10],
RandomInteger[10]}, {10000000}];
First[Timing[res0 = Tally[data2[[All, 1]]]]]
(* 2.53125 *)
First[Timing[res1 = (tllyF@data2)]]
(* 0.93750 *)
res0 == res1
(* True *)

• Kguler, thanks for your time and reply. Sep 14, 2014 at 3:27
– kglr
Sep 14, 2014 at 15:14

Without a Do loop

Tally[First /@ data]


{{AA, 2}, {CC, 5}, {DD, 3}}

or for speed

Tally[data[[All, 1]]]


{{AA, 2}, {CC, 5}, {DD, 3}}

EDIT:

Testing for a bigger set of data:

data2 =  Table[{RandomChoice[{AA, BB, CC, DD}], RandomInteger[10],RandomInteger[10]}, {10000000}];


We get:

First@Timing@Tally[data2[[All, 1]]]


2.683217

First@Timing@Tally[First@Transpose[data2]]


3.416422

First@Timing@Tally[First /@ data2]


5.304034

So mapping is the slowest as suggested by @Artes

• You are correct, I did misread, sorry. :) Will edit then. Sep 13, 2014 at 16:39
• See answer by @kguler for a faster solution (+1). Sep 13, 2014 at 22:09

This should be faster than mapping First:

Tally[ First @ Transpose[data]]

{{AA, 2}, {CC, 5}, {DD, 3}}


One can use also new functions (in version 10) like Count, CountsBy and GroupBy to get similar results, e.g. :

CountsBy[ data, First]

<|AA -> 2, CC -> 5, DD -> 3|>


If the format is crucial we can do this

  List @@@ Normal @ CountsBy[ data, First]

{{AA, 2}, {CC, 5}, {DD, 3}}

• For speed is better Tally[data2[[All, 1]]] Sep 13, 2014 at 16:48
• @rhermans Have you really done a reliable test? I'm sure Transpose is better. Sep 13, 2014 at 16:50
• I added my test in my answer, probably its not reliable, please do tell me. Sep 13, 2014 at 16:52
• @rka I provided results in the desired format, I guess CountsBy can be advantageous in various cases. Sep 13, 2014 at 18:01
• @rhermans You have a slightly better performance for Tally[data2[[All, 1]]] than Tally[First@Transpose[data2]] because data2 is a very special list i.e. a long list of lists of length 3. If small lists are of comparable lenghts to a big list, Transpose will appear much better. Sep 14, 2014 at 15:51