6
$\begingroup$

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.

$\endgroup$
4
$\begingroup$

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 *)
$\endgroup$
  • $\begingroup$ Kguler, thanks for your time and reply. $\endgroup$ – ramesh Sep 14 '14 at 3:27
  • 1
    $\begingroup$ @rka, please see Help Center > Asking $\endgroup$ – kglr Sep 14 '14 at 15:14
10
$\begingroup$

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

$\endgroup$
  • 1
    $\begingroup$ You are correct, I did misread, sorry. :) Will edit then. $\endgroup$ – rhermans Sep 13 '14 at 16:39
  • $\begingroup$ See answer by @kguler for a faster solution (+1). $\endgroup$ – rhermans Sep 13 '14 at 22:09
4
$\begingroup$

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}}
$\endgroup$
  • $\begingroup$ For speed is better Tally[data2[[All, 1]]] $\endgroup$ – rhermans Sep 13 '14 at 16:48
  • 1
    $\begingroup$ @rhermans Have you really done a reliable test? I'm sure Transpose is better. $\endgroup$ – Artes Sep 13 '14 at 16:50
  • $\begingroup$ I added my test in my answer, probably its not reliable, please do tell me. $\endgroup$ – rhermans Sep 13 '14 at 16:52
  • 1
    $\begingroup$ @rka I provided results in the desired format, I guess CountsBy can be advantageous in various cases. $\endgroup$ – Artes Sep 13 '14 at 18:01
  • 1
    $\begingroup$ @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. $\endgroup$ – Artes Sep 14 '14 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.