pandas group dataframe with category column with max sum value

user_13052379 0
Viewed 205

I want to group pandas dataframe with columns

datetime index
category
product_name
sale_price.

I need to group it in such a way that i have 

year_month
sum_of_sale_price_for_year_month
product_name_max_contributed_price_year_month

if a data frame has values like


| datetime  |  category  |  product_name  | sale_price|
|                                                     |
|2012-07-04 | category_1 |  product_1     | 120       |
|2012-07-07 | category_1 |  product_2     | 270       |
|2012-07-09 | category_1 |  product_7     | 100       |
|2012-07-12 | category_1 |  product_5     | 315       |

The output should be

| year_month | product_name_max_contributed_price_year_month | sum_of_sale_price_for_year_month|
|                                                                                              |
|  2012-07   |  product_5                                    |  805                            |

The column names can be anything, its just for understanding. I have been able to apply following process :

grouped_df = df.groupby([(df.index.year.rename('year')),(df.index.month.rename('month'))]).agg({"sale_price:np.sum"})
grouped_df['year_month'] = grouped_df [['year','month']].apply(lambda x: datetime.strptime('{}-{}'.format(x[1],x[0]), '%m-%Y').strftime('%b-%y'), axis=1)

i need additional column that gives product_name with max contribution for the time range. its either i search for each product_name corresponding to max value for year and month in grouped_df and create a series and append to it. what is the best way ?

pandas-groupby pandas
1 Answers

Creating the dataframe

import pandas as pd

df = pd.DataFrame({'datetime': ['2012-07-04', '2012-07-07', '2012-07-09 ', '2012-07-12'], 
                   'category': ['category1', 'category1', 'category1', 'category1'],
                   'product_name': ['product_1','product_2','product_7','product_5'],
                   'sale_price': [120,270,100,315]})

Creating year-month column:

df['year_month'] = pd.to_datetime(df['datetime']).dt.to_period('M')

Finding sales for month:

s = df.groupby(['year_month'])['sale_price'].sum().to_frame().rename(columns={"sale_price": "sum_of_sale_price_year_month"}).reset_index()

Finding product with maximum sale amount. idxmax is the key function. It returns the index of the first occurrence of maximum of sale price over the requested groupby axis and puts it into the loc function which pulls out the whole row associated with that index. Then year_month and product_name are extracted from this row and one of them is renamed and the index is reset in order to do the join in the next step.

grouped_df = df.loc[df.groupby(['year_month'])['sale_price'].idxmax()][['year_month','product_name']].rename(columns={"product_name": "product_name_max_contributed_price_year_month"}).reset_index(drop=True)

Merging on year-month field:

df2 = pd.merge(s, grouped_df, on='year_month')

      year_month    sum_of_sale_price_year_month product_name_max_contributed_price_year_month
    0 2012-07       805                          product_5
user_31637460
Related