CODEWITHSUNDEEP
python-3.xpandasduplicates
Zhubarb
Zhubarb

Reputation: 11885

Pandas - Conditional drop duplicates

I have a Pandas 0.19.2 dataframe for Python 3.6x as below. I want to drop_duplicates() with the same Id based on a conditional logic.

import pandas as pd
import numpy as np
np.random.seed(1)
df = pd.DataFrame({'Id':[1,2,3,4,3,2,6,7,1,8],
              'Name':['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'K'],
              'Size':np.random.rand(10),
              'Age':[19, 25, 22, 31, 43, 23, 44, 20, 51, 31]})

What would be the most efficient (if possible vectorised) way to achieve this based on the logic I describe below?

1) Before dropping duplicates, sum the Size of duplicate Id entries.

2) Drop duplicates for same Id records, keeping the one that has a larger Age.

The desired output would be:

   Age  Id Name      Size
1   25   2    B  0.812662
3   31   4    D  0.302333
4   43   3    E  0.146870
6   44   6    G  0.186260
7   20   7    H  0.345561
8   51   1    I  0.813790
9   31   8    K  0.538817

Upvotes: 3

Views: 1373

Answers (1)

jezrael
jezrael

Reputation: 862406

Use GroupBy.transform for aggregated values with same size as original DataFrame with sort_values and drop_duplicates for remove dupes:

df['Size'] = df.groupby('Id')['Size'].transform('sum')
df = df.sort_values('Age').drop_duplicates('Id', keep='last').sort_index()
print (df)
   Id Name      Size  Age
1   2    B  0.812663   25
3   4    D  0.302333   31
4   3    E  0.146870   43
6   6    G  0.186260   44
7   7    H  0.345561   20
8   1    I  0.813789   51
9   8    K  0.538817   31

Upvotes: 3

Related Questions