Create rule for sets of duplicates in a Pandas Dataframe

user_14086974 0
Viewed 304

I have a large dataframe with different duplicate values. How can I iterate through these different sets of duplicates and set column values accordingly?

For example, let's say I have the following dataframe:

dates = pd.to_datetime(['2021-1-1', '2017-2-1', '2016-5-1', '2020-6-1', '2020-11-1', '2012-4-1'])
animals = pd.Series(['cat', 'dog', 'dog', 'cat', 'fish', 'fish'])

df = pd.DataFrame({'DoB' : dates, 'Animal' : animals})

df[r'Born in 2020?'] = df['DoB'].apply(lambda x: True if x.year==2020 else False)

df

    DoB         Animal  Born in 2020?
0   2021-01-01  cat     False
1   2017-02-01  dog     False
2   2016-05-01  dog     False
3   2020-06-01  cat     True
4   2020-11-01  fish    True
5   2012-04-01  fish    False

I want to keep the oldest duplicate of each animal, and fill a column titled "Duplicate born in 2020?".

Essentially, before dropping duplicates, how can I create a script that says "if the 'Born in 2020' value for one of the duplicates for each animal is True, then set 'True' for all duplicates in 'Duplicate born in 2020' column".?

Ultimately, the dataframe I want to end up with would look like this:

    DoB         Animal  Duplicate born in 2020?  
0   2020-06-01  cat     True
2   2016-05-01  dog     False
5   2012-04-01  fish    True

How would I do this?

pandas dataframe duplicates boolean python
2 Answers

For This condition: We still need something that essentially says "if the 'Born in 2020' value for one of the duplicates in each set is True, then set 'True' for all duplicates in 'Duplicate born in 2020' column".

Use boolean masking,groupby() method and assign() method:

condition=(df[df['Born in 2020?']==True].groupby('Animal')['Born in 2020?'].size()>=1)
df['Born in 2020?']=df.set_index('Animal')['Born in 2020?'].to_frame().assign(result=condition)['result'].values

For keeping oldest duplicate of Animal column

use sort_values() and drop_duplicates() method:

df=df.sort_values('DoB').drop_duplicates(subset=['Animal'],ignore_index=True)

Now If you print df you will get your desired output:

    DoB        Animal   Born in 2020?
0   2012-04-01  fish    True
1   2016-05-01  dog     True
2   2020-06-01  cat     True
user_142898920

Just apply another lambda function where you can check for the "duplicates born in 2020". Thereafter just use drop_duplicates function from pandas (https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.drop_duplicates.html) .

df[r'Duplicate Born in 2020?'] = df['Animal'].apply(lambda x: True if (True in df.loc[df['Animal']==x,['Born in 2020?']]['Born in 2020?'].tolist()) else False)
df.drop_duplicates(subset=['Animal'],keep='last', inplace=True)
df.drop(['Born in 2020?'], axis=1, inplace=True)  #remoe unwanted columns
df
    DoB         Animal  Duplicate Born in 2020?
2   2016-05-01  dog     False
3   2020-06-01  cat     True
5   2012-04-01  fish    True

I guess this was your final ask.

user_35674730
Related