How to divide the data in multi-index dataframe

Question

As the picture, I have a multi-index dataframe named "overall_zx"[78].

There are sales and volumns for YTD2017/YTD2018/YTD2019. Now I want to calculate the price (sales divide volumns by individual YTD). I found the way to calculate the price as [79], but I don't know how to combine it in overall_zx this table.

Otherwise, is there any way can divide them directly? just like the sigle hierarchy dataframe. For example:

df['price']  = df['sales'] / df['volumns']

(In multi-index, this code doesn't work)

Dataframe

jezrael · Accepted Answer · 2019-11-30 06:57:47Z

You can use DataFrame.join with new MultIndex DataFrame by selecting by double [] and rename to same new name, here price:

df1  = (df.join(df[['sales']].rename(columns={'sales':'price'}) / 
                df[['volumns']].rename(columns={'volumns':'price'})))

Another idea with reshape by DataFrame.stack and DataFrame.unstack:

For new column is use DataFrame.assign for one line solution:

df1 = df.stack().assign(price = lambda x: x['sales'] / x['volumns']).unstack()

What is same like this alternative:

df1 = df.stack()
df1['price'] = df1['sales'] / df1['volumns']
df1 = df1.unstack()

Data borrow from @Valdi_Bo:

print (df1)
  volumns                    sales                        price             \
  YTD2017 YTD2018 YTD2019  YTD2017  YTD2018  YTD2019    YTD2017    YTD2018   
A   212.0   220.0  226.16  3654.04  4163.87  4695.12  17.236038  18.926682   
B    17.5    18.5   18.57   234.84   274.47   298.90  13.419429  14.836216   
C    18.5    14.5   12.66   213.02   191.14   177.46  11.514595  13.182069   


     YTD2019  
A  20.760170  
B  16.095854  
C  14.017378

Valdi_Bo · Accepted Answer · 2019-11-30 06:09:10Z

For my test I used the following DataFrame (using Latin characters as column names and index):

   Volume                     Sale                  
  YTD2017 YTD2018 YTD2019  YTD2017  YTD2018  YTD2019
A   212.0   220.0  226.16  3654.04  4163.87  4695.12
B    17.5    18.5   18.57   234.84   274.47   298.90
C    18.5    14.5   12.66   213.02   191.14   177.46

My first observation is that df.Sale / df.Volume alone does work, so let's save the result in an auxiliary DataFrame (df2). So far we have:

     YTD2017    YTD2018    YTD2019
A  17.236038  18.926682  20.760170
B  13.419429  14.836216  16.095854
C  11.514595  13.182069  14.017378

This result can not be joined to the original df, because df has a MultiIndex as columns, whereas df2 has an "ordinary" index.

But we can add the missing index level to columns in df2:

df2.columns = pd.MultiIndex.from_product([['Price'], df2.columns])

Now df2 contains also a MultiIndex on columns:

       Price                      
     YTD2017    YTD2018    YTD2019
A  17.236038  18.926682  20.760170
B  13.419429  14.836216  16.095854
C  11.514595  13.182069  14.017378

So now there is no obstacle to join these 2 DataFrames:

df = df.join(df2)

getting:

   Volume                     Sale                        Price             
  YTD2017 YTD2018 YTD2019  YTD2017  YTD2018  YTD2019    YTD2017    YTD2018    YTD2019 
A   212.0   220.0  226.16  3654.04  4163.87  4695.12  17.236038  18.926682  20.760170 
B    17.5    18.5   18.57   234.84   274.47   298.90  13.419429  14.836216  16.095854 
C    18.5    14.5   12.66   213.02   191.14   177.46  11.514595  13.182069  14.017378

Collectives™ on Stack Overflow

How to divide the data in multi-index dataframe

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related