4
$\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.

Improve this question
$\endgroup$
3
  • 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
    Commented Mar 2, 2015 at 19:43
  • $\begingroup$ is your data in external file? can you import it to mathematics or not? $\endgroup$
    – Basheer Algohi
    Commented Mar 2, 2015 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
    Commented Mar 2, 2015 at 21:42

4 Answers 4

Reset to default
4
$\begingroup$

If you data is sorted:

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

if not:

Flatten[GatherBy[pdata, #[[1]] &][[All, {1, -1}]], 1]
Improve this answer
$\endgroup$
1
  • $\begingroup$ Thank you Kuba for taking time to answer my question. $\endgroup$
    – ramesh
    Commented Mar 2, 2015 at 20:07
4
$\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}} *)
Improve this answer
$\endgroup$
2
  • $\begingroup$ Thanks kguler for taking time to answer this question. $\endgroup$
    – ramesh
    Commented Mar 2, 2015 at 21:34
  • $\begingroup$ @ramesh, my pleasure. $\endgroup$
    – kglr
    Commented Mar 2, 2015 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}}*)
Improve this answer
$\endgroup$
2
  • $\begingroup$ Thank you Algohi for taking time to answer this question. $\endgroup$
    – ramesh
    Commented Mar 2, 2015 at 21:35
  • $\begingroup$ My pleasure :), $\endgroup$
    – Basheer Algohi
    Commented Mar 2, 2015 at 21:37
2
$\begingroup$ 
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}, {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}};

A variant of kglr's first solution using Query and Catenate

Catenate @ GroupBy[data, First, Query @ {1, -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}}

Improve this answer
$\endgroup$

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.