5
$\begingroup$

I have a huge panel data with more than 2.5 million observations and more than 15000 groups. I want to calculate group-wise mean and I also need Id variable in my result. This is what I have done.

dat = {{2, .1, .2}, {2, .2, .4}, {2, .3, .6}, {2, .4, .8}, {5, 1, 
    2}, {5, 2, 4}, {7, 20, 10}, {7, 40, 20}, {7, 60, 30}, {7, 80, 
    40}, {7, 100, 50}, {10, 30, 50}};

results =  Table[N[Mean[Select[dat, #[[1]] == i &]]], {i, {2, 5, 7, 10}}]; 

But it is taking too long time. How can we make it quicker. Also, I want to calculate group-wise max value of second column.

Any help is greatly appreciated.

$\endgroup$
  • 1
    $\begingroup$ Is this what you want Mean /@ GatherBy[dat, First] OR Mean /@ GroupBy[dat, First] ? $\endgroup$ – RunnyKine Sep 5 '14 at 16:23
  • $\begingroup$ @RunnyKine. I am so stupid. Its that simple. It works and pretty fact. Thank you. $\endgroup$ – ramesh Sep 5 '14 at 17:14
  • $\begingroup$ You're welcome. Glad I could help. $\endgroup$ – RunnyKine Sep 5 '14 at 17:17
7
$\begingroup$

For the first case, the following should work:

Mean /@ GatherBy[dat, First]

OR

Mean /@ GroupBy[dat, First]

The second case gives an association with index as Keys

For the maximum by second value, try:

MaximalBy[#, #[[2]] &] & /@ GatherBy[dat, First]

OR

MaximalBy[#, #[[2]] &] & /@ GroupBy[dat, First]
$\endgroup$
  • $\begingroup$ SplitBy[dat, First] works also. $\endgroup$ – Algohi Sep 6 '14 at 2:13
  • $\begingroup$ @Algohi, that will only work if common terms are next to each other. $\endgroup$ – RunnyKine Sep 6 '14 at 2:16
  • $\begingroup$ yes of course if they are next to each other. $\endgroup$ – Algohi Sep 6 '14 at 2:29
3
$\begingroup$
Values @ GroupBy[dat, First, Mean]

{{2, 0.25, 0.5}, {5, 3/2, 3}, {7, 60, 30}, {10, 30, 50}}

Values @ Merge[Mean][# -> {##} & @@@ dat]

{{2, 0.25, 0.5}, {5, 3/2, 3}, {7, 60, 30}, {10, 30, 50}}

$\endgroup$
  • $\begingroup$ Thank you kglr. This was my question toward the beginning of my journey. I started to get it now. $\endgroup$ – ramesh Jan 17 '18 at 5:01
  • $\begingroup$ @ramesh, my pleasure. $\endgroup$ – kglr Jan 17 '18 at 5:29
2
$\begingroup$

Since you said your dataset had 2.5 million entries and 15000 groups, you might try using my GatherByList function to gain a small improvement in speed when compared to the other answers. Here is the definition of GatherByList:

GatherByList[list_, representatives_] := Module[{func},
    func /: Map[func, _] := representatives;
    GatherBy[list, func]
]

And here is a dataset with 2.5 million entries and ~15000 groups:

data = RandomInteger[15000, {2500000, 3}];

Here's a comparison with the other answers for obtaining the Mean:

r1 = Values @ GroupBy[data, First, Mean]; //AbsoluteTiming (* kglr *)
r2 = Mean /@ GatherBy[data, First]; //AbsoluteTiming (* Runnykine *)
r3 = Mean /@ GatherByList[data, data[[All, 1]]]; //AbsoluteTiming (* Carl *)

r1 === r2 === r3

{0.355567, Null}

{0.311108, Null}

{0.278283, Null}

True

And a comparison with @Runnykine's answer for obtaining the element with the group-wise max of the second column:

r1 = Last @ MaximalBy[#, #[[2]] &] & /@ GatherBy[data, First]; //AbsoluteTiming
r2 = Module[{r = GatherByList[data, data[[All, 1]]]},
    #[[First @ Ordering[#[[All, 2]], -1]]]& /@ r
]; //AbsoluteTiming

r1 === r2

{1.525, Null}

{0.275054, Null}

True

$\endgroup$
  • $\begingroup$ @ Carl, thank you for answer. $\endgroup$ – ramesh Jan 17 '18 at 5:02

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.