Keeping duplicate values with idxmax in Pandas

user_4623619 0
Viewed 818

I have a data frame where I have two columns, id and date.

df = pd.DataFrame([[1, '2019-05-20'], [1, '2019-05-20'], [1, '2018-04-23'], [2, '2020-01-01'], [2, '2020-01-01'], [2, '2019-12-31']], columns=['id', 'date'])

   id   date
    1   2019-05-20
    1   2019-05-20
    1   2018-04-23
    2   2020-01-01
    2   2020-01-01
    2   2019-12-31

For each unique id, I want to select all rows that have the latest date. So my ideal solution should be as follows:

id  date
1   2019-05-20
1   2019-05-20
2   2020-01-01
2   2020-01-01

I implemented this by grouping the data frame by id and then using the idxmax function to select the latest date for each `id, as follows:

df[df.groupby('id').date.idxmax()]

However, this only gives me the first row for each unique id which has the latest date, so I end up getting the following result:

id  date
1   2019-05-20
2   2020-01-01

Is there a way that I can select all rows with the idxmax function that have the highest date value for each id? I saw on the pandas github repo that there was a PR that addressed this(https://github.com/pandas-dev/pandas/pull/35257), but this PR was closed and not approved. Thank you in advance.

pandas python
1 Answers

You can use max and self-merge:

df.groupby('id', as_index=False).date.max().merge(df)

Output:

   id       date
0   1 2019-05-20
1   1 2019-05-20
2   2 2020-01-01
3   2 2020-01-01

Alternatively, you can set the index with repeated values per date, then use idxmax:

df.index = df.groupby('date').ngroup()
df.loc[df.groupby('id').date.idxmax()]
user_70768190
Related