Set the date for rows with common value in a column based on a 3rd column

user_16759052 0
Viewed 45

I have a dataframe:

df1

S.No   T.No    Amt    Date
1      34       10    20-6-2021
2      34       30    21-6-2021
3      34       20    22-6-2021
4      21       50    23-6-2021

Objective: In cases of same T.No the date for all of those should be the one associated with the highest amt.

Output:

S.No   T.No    Amt    Date
1      34       10    21-6-2021
2      34       30    21-6-2021
3      34       20    21-6-2021
4      21       50    23-6-2021

Any help is appreciated.

pandas dataframe python
2 Answers

Use DataFrameGroupBy.idxmax for indices, here Dates, because converted by DataFrame.set_index with GroupBy.transform to new column:

df['Date'] = df.set_index('Date').groupby('T.No')['Amt'].transform('idxmax').to_numpy()
print (df)
   S.No  T.No  Amt       Date
0     1    34   10  21-6-2021
1     2    34   30  21-6-2021
2     3    34   20  21-6-2021
3     4    21   50  23-6-2021

Or instead transform is used Series.map:

df['Date'] = df['T.No'].map(df.set_index('Date').groupby('T.No')['Amt'].idxmax())
user_29010020

Maybe try:

>>> df['Date'] = df.loc[df.groupby('T.No')['Amt'].transform('idxmax'), 'Date'].reset_index(drop=True)
>>> df
   S.No  T.No  Amt       Date
0     1    34   10  21-6-2021
1     2    34   30  21-6-2021
2     3    34   20  21-6-2021
3     4    21   50  23-6-2021
>>>

First solution, use groupby and transform with idxmax and loc with reset_index.

user_87083640
Related