Python DataFrame Multiple Column Groupby With Max Value From First Group

Data Frame is:

df=pd.DataFrame([[1,'a',30],[1,'b',60],[1,'b',40],[2,'g',40],[2,'g',9],[3,'k',20],[3,'k',69],[3,'k',29],[3,'f',99]], columns = ['Lot','Item','Date'])

     Lot Item  Date
0    1    a    30
1    1    b    60
2    1    b    40
3    2    g    40
4    2    g     9
5    3    k    20
6    3    k    69
7    3    k    29
8    3    f    99

I need to

1. Group By Lot and then Item
2. Get the First Item in each Lot
3. Finally Get the Max Date for each First Item in Each Lot

The OUTPUT should be:

     Lot Item  Date
0    1    a    30
3    2    g    40
6    3    k    69

Thank you !!!

>Solution :

I have done this using a few intermediary steps (there may be a simpler way, but this is how I would do it).

Firstly, group your df by Lot and return the first item for each lot using df.groupby to create a temporary df:

temp_df1 = df.groupby('Lot', as_index=False).first()[['Lot', 'Item']]

   Lot Item
0    1    a
1    2    g
2    3    k

I then used df.merge() to merge the original df onto temp_df to get only the rows from df that contain the first item for each lot:

temp_df2 = df.merge(temp_df1, on=['Lot', 'Item'], how='inner')

   Lot Item  Date
0    1    a    30
1    2    g    40
2    2    g     9
3    3    k    20
4    3    k    69
5    3    k    29

Then you can group by Lot and Item on this data frame to get your desired output

df_out = temp_df2.groupby(['Lot', 'Item'], as_index=False).max()

   Lot Item  Date
0    1    a    30
1    2    g    40
2    3    k    69

Full code:

temp_df1 = df.groupby('Lot', as_index=False).first()[['Lot', 'Item']]
temp_df2 = df.merge(temp_df1, on=['Lot', 'Item'], how='inner')
df_out = temp_df2.groupby(['Lot', 'Item'], as_index=False).max()

Or the below without creating temporary dfs:

df_out = (df.merge(df.groupby('Lot', as_index=False).first()[['Lot', 'Item']],
                   on=['Lot', 'Item'], how='inner').groupby(['Lot', 'Item'], as_index=False).max())