obtain corresponding values from 3rd column after groupby 2 columns

user_16513288 0
Viewed 89

The following code groups max values of 'time' per category. I would like to obtain the corresponding value in the 'target' variable but I am not sure how to do it. Any hints?

import numpy as np
import pandas as pd

data=[[1,1,2,2,'A'],
      [2,5,5,1,'A'],
      [3,7,9,4,'B'],
      [1,5,1,9,'B'],
      [1,8,2,8,'C'],
      [2,8,5,10,'C'],
      [0,1,2,3,'D']]

df=pd.DataFrame(data, columns=['time','x','y','target','categ'])
res = ((df.groupby('categ')['time'].max().value_counts(ascending=True).sort_index()))
print(res)
pandas group-by python
2 Answers

We can use loc + groupby idxmax:

res = df.loc[
    df.groupby('categ')['time'].idxmax(),
    ['time', 'target']
].sort_values('time')['target']

res:

6     3
1     1
5    10
2     4
Name: target, dtype: int64

Or filter with groupby transform max if all rows which match the group maximum are wanted:

res = df.loc[
    df.groupby('categ')['time'].transform('max').eq(df['time']),
    ['time', 'target']
].sort_values('time')['target']

res:

6     3
1     1
5    10
2     4
Name: target, dtype: int64

*Output is the same in this case, but will not be if there are duplicate maximal values per categ.

Per the comments with groupby agg on time and aggregate into list:

res = df.groupby('time')['target'].agg(list)

res:

time
0          [3]
1    [2, 9, 8]
2      [1, 10]
3          [4]
Name: target, dtype: object
user_154978880

In your case do drop_duplicates after sort_values

df.sort_values(['time']).drop_duplicates(['categ'],keep='last')['target']
Out[184]: 
6     3
1     1
5    10
2     4
Name: target, dtype: int64

Or do transform when a group of categ have all same value , this method will output all of them

df.loc[df.time.eq(df.groupby('categ').time.transform('max')),'target']
Out[187]: 
1     1
2     4
5    10
6     3
Name: target, dtype: int64
user_79645270
Related