Pythonic / Panda Way To Create Function To Groupby

I am fairly new to programming & am looking for a more pythonic way to implement some code. Here is dummy data: df = pd.DataFrame({ 'Category':np.random.choice( ['Group A','Gr

Solution 1:

For a DRY-er solution, consider generalizing your current method into a defined module that filters original data frame by date ranges and runs aggregations, receiving the group_by levels and date ranges (latter being optional) as passed in parameters:

Method

defmultiple_agg(mylevels, start_date='2016-01-01', end_date='2018-12-31'):

    filter_df = df[df['Date'].between(start_date, end_date)]

    master = (filter_df.groupby(['Customer', 'Category', 'Sub-Category', 'Product', 
                     pd.Grouper(key='Date',freq='A')])['Units_Sold']
                .sum()
                .unstack()
              )

    y = master.groupby(level=mylevels[:-1]).sum()
    y.index = pd.MultiIndex.from_arrays([
        y.index.get_level_values(0),
        y.index.get_level_values(1),
        y.index.get_level_values(2) + ' Total',
        len(y.index)*['']
    ])

    y1 = master.groupby(level=mylevels[0:2]).sum()
    y1.index = pd.MultiIndex.from_arrays([
        y1.index.get_level_values(0),
        y1.index.get_level_values(1)+ ' Total',
        len(y1.index)*[''],
        len(y1.index)*['']
    ])

    y2 = master.groupby(level=mylevels[0]).sum()
    y2.index = pd.MultiIndex.from_arrays([
        y2.index.get_level_values(0)+ ' Total',
        len(y2.index)*[''],
        len(y2.index)*[''],
        len(y2.index)*['']
    ])

    final_df = (pd.concat([master,y,y1,y2])
                         .sort_index()
                         .assign(Diff = lambda x: x.iloc[:,-1] - x.iloc[:,-2])
                         .assign(Diff_Perc = lambda x: (x.iloc[:,-2] / x.iloc[:,-3])- 1)
                         .dropna(how='all')
                         .reorder_levels(mylevels)
                )

    return final_df

Aggregation Runs(of different levels and date ranges)

agg_df1 = multiple_agg([0,1,2,3])

agg_df2 = multiple_agg([1,3,0,2], '2016-01-01', '2017-12-31')

agg_df3 = multiple_agg([2,3,1,0], start_date='2017-01-01', end_date='2018-12-31')

Testing(final_df being OP'S pd.concat() output)

# EQUALITY TESTING OF FIRST 10 ROWSprint(final_df.head(10).eq(agg_df1.head(10)))

# Date                                        2016-12-31 00:00:00  2017-12-31 00:00:00  2018-12-31 00:00:00  Diff  Diff_Perc# Customer   Category Sub-Category Product                                                                                  # 45mhn4PU1O Group A  X            Product 1                 True                 True                 True  True       True#                                  Product 2                 True                 True                 True  True       True#                                  Product 3                 True                 True                 True  True       True#                     X Total                                True                 True                 True  True       True#                     Y            Product 1                 True                 True                 True  True       True#                                  Product 2                 True                 True                 True  True       True#                                  Product 3                 True                 True                 True  True       True#                     Y Total                                True                 True                 True  True       True#                     Z            Product 1                 True                 True                 True  True       True#                                  Product 2                 True                 True                 True  True       True

Solution 2:

I think you can do it using sum with the level parameter:

master = df.groupby(['Customer','Category','Sub-Category','Product',pd.Grouper(key='Date',freq='A')])['Units_Sold'].sum()\
.unstack()
s1 = master.sum(level=[0,1,2]).assign(Product='Total').set_index('Product',append=True)
s2 = master.sum(level=[0,1])

# Wanted to use assign method but because of the hyphen in the column name you can't.# Also use the Z in front for sorting purposes
s2['Sub-Category'] = 'ZTotal'
s2['Product'] = ''
s2 = s2.set_index(['Sub-Category','Product'], append=True)

s3 = master.sum(level=[0])
s3['Category'] = 'Total'
s3['Sub-Category'] = ''
s3['Product'] = ''
s3 = s3.set_index(['Category','Sub-Category','Product'], append=True)

master_new = pd.concat([master,s1,s2,s3]).sort_index()
master_new

Output:

Date2016-12-31  2017-12-31  2018-12-31CustomerCategorySub-CategoryProduct30XWmt1jm0GroupAXProduct1651.0341.0453.0Product2267.0445.0117.0Product3186.0280.0352.0Total1104.0      1066.0       922.0YProduct1426.0417.0670.0Product2362.0210.0380.0Product3232.0290.0430.0Total1020.0       917.01480.0ZProduct1196.0212.0703.0Product2277.0340.0579.0Product3416.0392.0259.0Total889.0944.01541.0ZTotal3013.0      2927.0      3943.0GroupBXProduct1356.0230.0407.0Product2402.0370.0590.0Product3262.0381.0377.0Total1020.0       981.01374.0YProduct1575.0314.0643.0Product2557.0375.0411.0Product3344.0246.0280.0Total1476.0       935.01334.0ZProduct1278.0152.0392.0Product2149.0596.0303.0Product3234.0505.0521.0Total661.01253.0      1216.0ZTotal3157.0      3169.0      3924.0Total6170.0      6096.0      7867.03U2anYOD6oGroupAXProduct1214.0443.0195.0Product2170.0220.0423.0Product3111.0469.0369.0............somc22Y2HiGroupBZTotal906.01063.0       680.0ZTotal3070.0      3751.0      2736.0Total6435.0      7187.0      6474.0zRZq6MSKuSGroupAXProduct1421.0182.0387.0Product2359.0287.0331.0Product3232.0394.0279.0Total1012.0       863.0997.0YProduct1245.0366.0111.0Product2377.0148.0239.0Product3372.0219.0310.0Total994.0733.0660.0ZProduct1280.0363.0354.0Product2384.0604.0178.0Product3219.0462.0366.0Total883.01429.0       898.0ZTotal2889.0      3025.0      2555.0GroupBXProduct1466.0413.0187.0Product2502.0370.0368.0Product3745.0480.0318.0Total1713.0      1263.0       873.0YProduct1218.0226.0385.0Product2123.0382.0570.0Product3173.0572.0327.0Total514.01180.0      1282.0ZProduct1480.0317.0604.0Product2256.0215.0572.0Product3463.050.0349.0Total1199.0       582.01525.0ZTotal3426.0      3025.0      3680.0Total6315.0      6050.0      6235.0

[675rowsx3columns]