sum duplicate row with condition using pandas

user_12496869 0
Viewed 311

I have a dataframe who looks like this:

  Name  rent  sale
0    A   180     2
1    B     1     4
2    M    12     1
3    O    10     1
4    A   180     5
5    M     2    19

that i want to make condition that if i have a duplicate row and a duplicate value in column field => Example :

  • duplicate row A have duplicate value 180 in rent column I keep only one (without making the sum)
  • Or make the sum => Example duplicate row A with different values 2 & 5 in Sale column and duplicate row M with different values in rent & sales columns

Expected output:

  Name  rent  sale
0    A   180     7
1    B     1     4
2    M    14     20
3    O    10     1

I tried this code but it's not workin as i want

import pandas as pd 
    
df=pd.DataFrame({'Name':['A','B','M','O','A','M'],
                 'rent':[180,1,12,10,180,2],
                 'sale':[2,4,1,1,5,19]})
df2 = df.drop_duplicates().groupby('Name',sort=False,as_index=False).agg(Name=('Name','first'),
                                                                            rent=('rent', 'sum'),
                                                                            sale=('sale','sum'))
    
print(df2)

I got this output

  Name  rent  sale
0    A   360     7
1    B     1     4
2    M    14    20
3    O    10     1
pandas python
2 Answers

Can try summing only the unique values per group:

def sum_unique(s):
    return s.unique().sum()


df2 = df.groupby('Name', sort=False, as_index=False).agg(
    Name=('Name', 'first'),
    rent=('rent', sum_unique),
    sale=('sale', sum_unique)
)

df2:

  Name  rent  sale
0    A   180     7
1    B     1     4
2    M    14    20
3    O    10     1
user_154978880

You can first groupby by Name and rent, and then just by Name:

df2 = df.groupby(['Name', 'rent'], as_index=False).sum().groupby('Name', as_index=False).sum()
user_119450020
Related