I have huge a dataframe with millions of rows and id. My data looks like this:
Time ID X Y
8:00 A 23 100
9:00 B 24 110
10:00 B 25 120
11:00 C 26 130
12:00 C 27 140
13:00 A 28 150
14:00 A 29 160
15:00 D 30 170
16:00 C 31 180
17:00 B 32 190
18:00 A 33 200
19:00 C 34 210
20:00 A 35 220
21:00 B 36 230
22:00 C 37 240
23:00 B 38 250
When I sort the data on id and time, the result looks like this:
Time ID X Y
8:00 A 23 100
13:00 A 28 150
14:00 A 29 160
18:00 A 33 200
20:00 A 35 220
9:00 B 24 110
10:00 B 25 120
17:00 B 32 190
21:00 B 36 230
23:00 B 38 250
11:00 C 26 130
12:00 C 27 140
16:00 C 31 180
19:00 C 34 210
22:00 C 37 240
15:00 D 30 170
From here, I want to pick only "The first and the last" of the id and eliminate the rest. The expected result looks like this:
Time ID X Y
8:00 A 23 100
20:00 A 35 220
9:00 B 24 110
23:00 B 38 250
11:00 C 26 130
22:00 C 37 240
15:00 D 30 170
How to do it in pandas?
Use groupby, find the head and tail for each group, and concat the two.
g = df.groupby('ID')
(pd.concat([g.head(1), g.tail(1)])
.drop_duplicates()
.sort_values('ID')
.reset_index(drop=True))
Time ID X Y
0 8:00 A 23 100
1 20:00 A 35 220
2 9:00 B 24 110
3 23:00 B 38 250
4 11:00 C 26 130
5 22:00 C 37 240
6 15:00 D 30 170
If you can guarantee each ID group has at least two rows, the drop_duplicates call is not needed.
Details
g.head(1)
Time ID X Y
0 8:00 A 23 100
1 9:00 B 24 110
3 11:00 C 26 130
7 15:00 D 30 170
g.tail(1)
Time ID X Y
7 15:00 D 30 170
12 20:00 A 35 220
14 22:00 C 37 240
15 23:00 B 38 250
pd.concat([g.head(1), g.tail(1)])
Time ID X Y
0 8:00 A 23 100
1 9:00 B 24 110
3 11:00 C 26 130
7 15:00 D 30 170
7 15:00 D 30 170
12 20:00 A 35 220
14 22:00 C 37 240
15 23:00 B 38 250