2
$\begingroup$

This is a follow up question form my last question click here. I have unbalanced panel data such as :

pdata = {{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}, {EE, 11, 110}, {EE, 12, 120}, {EE, 13, 130}, {HH, 14, 
    140}, {HH, 15, 150}, {HH, 16, 160}, {HH, 17, 170}, {HH, 18, 
    180}, {HH, 19, 190}, {HH, 20, 200}};

I want to extract firs and last rows of each group. Thank to both Verbeia and belisarius. With their help I can write:

firstrow = First /@ GatherBy[pdata, First];
lastrow = Last /@ GatherBy[pdata, First];
bothrows = Join @@ Flatten[{firstrow, lastrow}, {{2}, {1}}];

and get my desired result. But my actual data is very big and it takes time if I do this way. I am looking for better and faster way of doing the same. Any help is greatly appreciated.

$\endgroup$
  • 1
    $\begingroup$ Is your data sorted by first entry (i.e., AA, CC, DD, ...)? And how big is your dataset? You should be able to extract the required data using the Verbeia and belisarius method for millions of entries within a few seconds. $\endgroup$ – David G. Stork Mar 2 '15 at 19:43
  • $\begingroup$ is your data in external file? can you import it to mathematics or not? $\endgroup$ – Algohi Mar 2 '15 at 21:05
  • $\begingroup$ @Algohi, the original data is in txt format. However, I have converted to .mat format so that I can read it faster. The data has over 50 columns and over 53m obs. $\endgroup$ – ramesh Mar 2 '15 at 21:42
3
$\begingroup$

If you data is sorted:

 Flatten[SplitBy[pdata, #[[1]] &][[All, {1, -1}]], 1]

if not:

Flatten[GatherBy[pdata, #[[1]] &][[All, {1, -1}]], 1]
$\endgroup$
  • $\begingroup$ Thank you Kuba for taking time to answer my question. $\endgroup$ – ramesh Mar 2 '15 at 20:07
3
$\begingroup$

You can also use the Version 10 functions GroupBy and Merge:

Join@@Values@GroupBy[pdata, First, Through@{First,Last}@#&]

and

Join@@Values@Merge[#->{##}&@@@pdata,Through@{First,Last}@#&]

to get

(* {{AA, 1, 10}, {AA, 2, 20}, {CC, 3, 30}, {CC, 7, 70}, {DD, 8, 80}, {DD,
   10, 100}, {EE, 11, 110}, {EE, 13, 130}, {HH, 14, 140}, {HH, 20, 200}} *)
$\endgroup$
  • $\begingroup$ Thanks kguler for taking time to answer this question. $\endgroup$ – ramesh Mar 2 '15 at 21:34
  • $\begingroup$ @ramesh, my pleasure. $\endgroup$ – kglr Mar 2 '15 at 21:35
2
$\begingroup$
pdata[[Flatten[
   Values[Map[{#[[1]], #[[-1]]} &, PositionIndex[pdata[[;; , 1]]]]]]]]

(*{{AA, 1, 10}, {AA, 2, 20}, {CC, 3, 30}, {CC, 7, 70}, {DD, 8, 80}, {DD,
   10, 100}, {EE, 11, 110}, {EE, 13, 130}, {HH, 14, 140}, {HH, 20, 
  200}}*)
$\endgroup$
  • $\begingroup$ Thank you Algohi for taking time to answer this question. $\endgroup$ – ramesh Mar 2 '15 at 21:35
  • $\begingroup$ My pleasure :), $\endgroup$ – Algohi Mar 2 '15 at 21:37

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.