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.

Improve this question
$\endgroup$
0

3 Answers 3

Reset to default
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 *)
Improve this answer
$\endgroup$
2
  • $\begingroup$ Kguler, thanks for your time and reply. $\endgroup$
    – ramesh
    Commented Sep 14, 2014 at 3:27
  • 1
    $\begingroup$ @rka, please see Help Center > Asking $\endgroup$
    – kglr
    Commented Sep 14, 2014 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

Improve this answer
$\endgroup$
2
  • 1
    $\begingroup$ You are correct, I did misread, sorry. :) Will edit then. $\endgroup$
    – rhermans
    Commented Sep 13, 2014 at 16:39
  • $\begingroup$ See answer by @kguler for a faster solution (+1). $\endgroup$
    – rhermans
    Commented Sep 13, 2014 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}}
Improve this answer
$\endgroup$
7
  • $\begingroup$ For speed is better Tally[data2[[All, 1]]] $\endgroup$
    – rhermans
    Commented Sep 13, 2014 at 16:48
  • 1
    $\begingroup$ @rhermans Have you really done a reliable test? I'm sure Transpose is better. $\endgroup$
    – Artes
    Commented Sep 13, 2014 at 16:50
  • $\begingroup$ I added my test in my answer, probably its not reliable, please do tell me. $\endgroup$
    – rhermans
    Commented Sep 13, 2014 at 16:52
  • 1
    $\begingroup$ @rka I provided results in the desired format, I guess CountsBy can be advantageous in various cases. $\endgroup$
    – Artes
    Commented Sep 13, 2014 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
    Commented Sep 14, 2014 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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