Aggregating and group by in Pandas considering some conditions

user_14954932 0
Viewed 78

I have an excel file which simplified has the following structure and which I read as a dataframe:

df = pd.DataFrame({'ISIN':['US02079K3059', 'US02079K3059', 'US02079K3059', 'US02079K3059', 'US02079K3059', 'US02079K3059', 'US02079K3059', 'US02079K3059', 'US00206R1023'],
               'Name':['ALPHABET INC.CL.A DL-,001', 'Alphabet Inc Class A', 'ALPHABET INC CLASS A', 'ALPHABET A', 'ALPHABET INC CLASS A', 'ALPHABET A', 'Alphabet Inc. Class C', 'Alphabet Inc. Class A', 'AT&T Inc'], 
               'Country':['United States', 'United States', 'United States', '', 'United States', 'United States', 'United States', 'United States', 'United States'],
               'Category':[ '', 'big', 'big', '', 'big', 'test', 'test', 'test', 'average'],
               'Category2':['important', '', 'important', '', '', '', '', '', 'irrelevant'],
               'Value':[1000, 750, 60, 50, 160, 9, 10, 10, 1]})

I would love to group by ISIN and add up the values and calculate the sum like

df1 = df.groupby('ISIN').sum(['Value'])

The problem with this approach is, I dont get the other fields 'Name', 'Country', 'Category', 'Category2'.

My objective is to get as a result the following data aggregated dataframe:

df1 = pd.DataFrame({'ISIN':['US02079K3059',  'US00206R1023'],
               'Name':['ALPHABET A', 'AT&T Inc'], 
               'Country':['United States', 'United States'],
               'Category':['big', 'average'],
               'Category2':['important', 'irrelevant'],
               'Value':[2049, 1]})

If you compare df to df1, you will recognize some criteria/conditions I applied:

  • for every 'ISIN' most commonly appearing field value should be used, e.g. 'United States' in column 'Country'
  • If field values are equally most common, the first appearing of the most common should be used, e.g. 'big' and 'test' in column 'Category'
  • Exception: empty values don't count, e.g. Category2, even though '' is the most common value, 'important' is used as final value.

How can I achieve this goal? Anyone who can help me out?

pandas-groupby pandas dataframe python
1 Answers

try convert '' to NaN then drop 'Value' column then groupby 'ISIN' and calculate mode then map the values of sum of 'Value' column grouped by 'ISIN' to 'ISIN' column so to create 'Value' column in your Final result:

Basically the idea is to converting empty string '' to NaN so that it doesn't count in the mode and we are defining a function to handle such cases when mode of particular column groupedby 'ISIN' is NaN because of dropna=True in mode() method

def f(x):
    try:
        return x.mode().iat[0]
    except IndexError:
        return float('NaN')

Finally:

out=(df.replace('',float('NaN'))
       .drop(columns='Value')
       .groupby('ISIN',as_index=False).agg(f))
out['Value']=out['ISIN'].map(df.groupby('ISIN')['Value'].sum())
out['Value_perc']=out['Value'].div(out['Value'].sum()).round(5)

OR

Via passing dropna=False in mode() method and anonymous function:

out=(df.replace('',float('NaN'))
       .drop(columns='Value')
       .groupby('ISIN',as_index=False).agg(lambda x:x.mode(dropna=False).iat[0]))
out['Value']=out['ISIN'].map(df.groupby('ISIN')['Value'].sum())
out['Value_perc']=out['Value'].div(out['Value'].sum()).round(5)

Now If you print out you will get your desired output

user_142898920
Related