First and last value of grouped series

user_12839498 0
Viewed 35

I have a dataframe and I would like to create two columns containing respectively the first value of a group for column and the last value of the same group but for another column.

My df looks like this :

   one  two    ID  number
0  AAA  BBB  idx1       1
1  BBB  CCC  idx1       2
2  DDD  EEE  idx2       1
3  CCC  KKK  idx1       3

And I want to create a column firstthat contains the first one of each group (grouped by ID), and a column lastthat contains the last two of each group. Everything sorted by numbercolumn (and grouped by ID.

So the output should look like :

   one  two    ID  number first last
0  AAA  BBB  idx1       1   AAA  KKK
1  BBB  CCC  idx1       2   AAA  KKK
2  DDD  EEE  idx2       1   DDD  EEE
3  CCC  KKK  idx1       3   AAA  KKK
pandas-groupby pandas python
1 Answers

Use DataFrame.sort_values with aggregate GroupBy.first and GroupBy.last and then append by DataFrame.join:

df = (df.join(df.sort_values(['ID','number'])
                .groupby('ID')
                .agg(first=('one','first'),last= ('two','last')), on='ID'))
print (df)
   one  two    ID  number first last
0  AAA  BBB  idx1       1   AAA  KKK
1  BBB  CCC  idx1       2   AAA  KKK
2  DDD  EEE  idx2       1   DDD  EEE
3  CCC  KKK  idx1       3   AAA  KKK
user_29010020
Related