0
$\begingroup$

I have a Pandas dataframe that contains three columns: ID, name and date. The name column is not unique and may contain duplicates. I want to change the date of the duplicated name to the earliest date. For example:

Input:

id  name    date
1   Rachel  3/10/2020
2   Dave    1/5/2020
3   Rachel  1/10/2020
4   Rachel  1/1/2020
5   Jason   4/15/2020
6   Dave    7/1/2020

Output:

id  name    date
1   Rachel  1/1/2020
2   Dave    1/5/2020
3   Rachel  1/1/2020
4   Rachel  1/1/2020
5   Jason   4/15/2020
6   Dave    1/5/2020

In the sample data above, there are three dates associated to Rachel, the earliest date is 1/1/2020, therefore, in the output, all the dates associated to Rachel are changed to 1/1/2020.

$\endgroup$
2
$\begingroup$

A cleaner way would be:

df["date"] = df.groupby("name").date.transform("min")

Outputs:

enter image description here

$\endgroup$
1
  • $\begingroup$ This works great. Thank you! $\endgroup$
    – Larry
    Dec 24 '20 at 3:02
0
$\begingroup$

Try this:

# step 1, create minimum date dataframe
df_min = df[["name", "date"]].groupby("name").min().reset_index() 
# step 2, get new dates in a joined df
df_joined = df.join(df_min.set_index("name"), rsuffix="_r", on="name")
# step 3, cleanup
df_result = df_joined[["id","name","date_r"]]
df_result.columns = ["id","name","date"]
df_result
id name date
0 1 Rachel 2020-01-01 00:00:00
1 2 Dave 2020-01-05 00:00:00
2 3 Rachel 2020-01-01 00:00:00
3 4 Rachel 2020-01-01 00:00:00
4 5 Jason 2020-04-15 00:00:00
5 6 Dave 2020-01-05 00:00:00

Done!


Details:

Assuming your dataframe has a lot of records and is given as:

df = pd.DataFrame(
    {
        "date": ["3/10/2020", "1/5/2020", "1/10/2020", "1/1/2020", "4/15/2020", "7/1/2020"],
        "id": ["1", "2", "3", "4", "5", "6"],
        "name": ["Rachel", "Dave", "Rachel", "Rachel", "Jason", "Dave"], 
    }
)
df['date'] = pd.to_datetime(df['date'], format="%m/%d/%Y") # only needed if dates are given as string, change format according to your dates
date id name
0 2020-03-10 00:00:00 1 Rachel
1 2020-01-05 00:00:00 2 Dave
2 2020-01-10 00:00:00 3 Rachel
3 2020-01-01 00:00:00 4 Rachel
4 2020-04-15 00:00:00 5 Jason
5 2020-07-01 00:00:00 6 Dave
  1. From the original dataframe, you can create a new dataframe containing names and minimum dates
df_joined = df.join(df_min.set_index("name"), rsuffix="_r", on="name")
name date
0 Dave 2020-01-05 00:00:00
1 Jason 2020-04-15 00:00:00
2 Rachel 2020-01-01 00:00:00
  1. Then you can join these two to get the minimum date from the new df:
df_joined = df.join(df_min.set_index("name"), rsuffix="_new", on="name")
date id name date_new
0 2020-03-10 00:00:00 1 Rachel 2020-01-01 00:00:00
1 2020-01-05 00:00:00 2 Dave 2020-01-05 00:00:00
2 2020-01-10 00:00:00 3 Rachel 2020-01-01 00:00:00
3 2020-01-01 00:00:00 4 Rachel 2020-01-01 00:00:00
4 2020-04-15 00:00:00 5 Jason 2020-04-15 00:00:00
5 2020-07-01 00:00:00 6 Dave 2020-01-05 00:00:00
  1. Cleanup
df_result = df_joined[["id","name","date_new"]]
df_result.columns = ["id","name","date"]
id name date
0 1 Rachel 2020-01-01 00:00:00
1 2 Dave 2020-01-05 00:00:00
2 3 Rachel 2020-01-01 00:00:00
3 4 Rachel 2020-01-01 00:00:00
4 5 Jason 2020-04-15 00:00:00
5 6 Dave 2020-01-05 00:00:00
$\endgroup$
1
  • $\begingroup$ Thanks! It's a valid solution but the other answer is cleaner. $\endgroup$
    – Larry
    Dec 24 '20 at 3:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.