Get rows with min date associated with every ID

user_11592540 0
Viewed 1109

I have a pandas dataframe with multiple IDs and with other columns I have one date columns say : 'date1'. I want to get all the rows with minimum date associated with all the IDs. The others column values should also be retained.

What I have:

ID   date1        value
1    1/1/2013     a
1    4/1/2013     a
1    8/3/2014     b
2    11/4/2013    a
2    19/5/2016    b
2    8/4/2017     b

The output I want :

ID   date1      value
1    1/1/2013   a
2    11/4/2013  a

Thank you

pandas python-3.x date python
2 Answers

Assuming you have IDs in ID and dates in DATE:

df.groupby('ID')['DATE'].min()

Groups by your ID and then selects the minimum in each group. Returns a series. If you want a data frame for that, then call _.reset_index() on the output.

If you instead want to select only the minimum rows, I would set the output as keys and then new_df.join(old_df.set_index(['ID', 'DATE']) rather than dealing with some index-based shenanigans.

user_27410910

Convert to datetime:

df = df.assign(date1 = pd.to_datetime(df.date1))

Get the label index of the minimum and subset:

df.loc[df.groupby("ID").date1.idxmin()]

   ID      date1 value
0   1 2013-01-01     a
3   2 2013-11-04     a
user_71757130
Related