# How to extract both first and last rows of each group from unbalanced panel data

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.

• 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. Mar 2, 2015 at 19:43
• is your data in external file? can you import it to mathematics or not? Mar 2, 2015 at 21:05
• @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. Mar 2, 2015 at 21:42

If you data is sorted:

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


if not:

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

• Thank you Kuba for taking time to answer my question. Mar 2, 2015 at 20:07

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}} *)

• Thanks kguler for taking time to answer this question. Mar 2, 2015 at 21:34
• @ramesh, my pleasure.
– kglr
Mar 2, 2015 at 21:35
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}}*)

• Thank you Algohi for taking time to answer this question. Mar 2, 2015 at 21:35
• My pleasure :), Mar 2, 2015 at 21:37