CODEWITHSUNDEEP
pythonpandas
acakaliman
acakaliman

Reputation: 83

How to: Pandas division of columns when multiindex present due to pivot table

I have a sales dataset which I read into a Pandas dataframe and pivoted to group information. Now columns are a Multiindex and looks like this:

MultiIndex([('Orders', 'Pants'),
        ('Orders', 'Shirts'),
        ('Orders', 'Shoes'),
        ( 'Spend', 'Pants'),
        ( 'Spend', 'Shirts'),
        ( 'Spend', 'Shoes'),,
       names=[None, 'Product'])

Now, I am trying to calculate an "average order value" for each "product". I want to do something like:

# Calculate Avg. Order Value by dividing Spend Series by Orders Series
dataframe['AOV'] = dataframe['Spend'] / dataframe['Orders']

But I cannot. How do I end up with a dataframe that has these column multiindex:

MultiIndex([('Orders', 'Pants'),
            ('Orders', 'Shirts'),
            ('Orders', 'Shoes'),
            ( 'Spend', 'Pants'),
            ( 'Spend', 'Shirts'),
            ( 'Spend', 'Shoes'),
            ( 'AOV', 'Pants'),
            ( 'AOV', 'Shirts'),
            ( 'AOV', 'Shoes'),
           names=[None, 'Product'])

Upvotes: 1

Views: 109

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71687

Divide Spend by Orders then concat with keys parameter to append additional index level called AOV then concat with the original dataframe to get the result

AOV = pd.concat([df.loc['Spend'] / df.loc['Orders']], keys=['AOV'])
result = pd.concat([df, AOV])

Alternatively you can unstack the dataframe then divide and stack back

s = df.unstack()
s.loc['AOV'] = s.loc['Spend'] / s.loc['Orders']
result = s.stack()

Upvotes: 1

Related Questions