Element-wise division between two dataframes with tickers and Date index

Viewed 300

I have two dataframes with Date as index,
df1 as follows:

ticker          AAPL       AMD      BIDU     GOOGL      IXIC      MSFT   
Date                                                                     
2011-06-29  0.017664  0.024379  0.029592  0.016947  0.010632  0.014888   
2011-07-14  0.011054  0.023751  0.020682  0.018319  0.011720  0.008974   
2011-07-28  0.015654  0.061570  0.027052  0.040214  0.013136  0.018023   
2011-08-11  0.033560  0.051619  0.050874  0.033624  0.039744  0.033243   
2011-08-25  0.023524  0.037700  0.039888  0.026866  0.025491  0.017059   
...              ...       ...       ...       ...       ...       ...   
2021-03-25  0.019135  0.022755  0.058591  0.013458  0.013935  0.014096   
2021-04-09  0.011087  0.015793  0.027506  0.014799  0.008590  0.014066   
2021-04-23  0.015193  0.035461  0.021222  0.011206  0.010541  0.010495   
2021-05-07  0.013870  0.019531  0.009501  0.014049  0.008334  0.012325   
2021-05-21  0.017664  0.022013  0.028316  0.017536  0.016039  0.016377   

And df2 as follows:

ticker               AAPL       AMD      BIDU     GOOGL      IXIC      MSFT   
Date                                                                          
2011-06-29       0.005953  0.010331  0.008564  0.010089  0.000288  0.004542   
2011-07-14       0.006609  0.000628  0.008910  0.001372  0.001088  0.005914   
2011-07-28       0.004600  0.037819  0.006369  0.021895  0.001416  0.009049   
2011-08-11       0.017906  0.009951  0.023822  0.006591  0.026608  0.015220   
2011-08-25       0.010036  0.013919  0.010986  0.006758  0.014252  0.016183   
...                   ...       ...       ...       ...       ...       ...   
2021-03-25       0.010546  0.017421  0.015649  0.010738  0.009917  0.005328   
2021-04-09       0.008049  0.006963  0.031085  0.001341  0.005345  0.000031   
2021-04-23       0.004107  0.019669  0.006285  0.003594  0.001951  0.003571   
2021-05-07       0.001323  0.015930  0.011720  0.002843  0.002206  0.001831   
2021-05-21       0.003793  0.002482  0.018815  0.003487  0.007704  0.004052   

I checked that both dataframes have the same number of rows and columns, and no NaN. ie, for both dataframes, df.isnull().values.any() returns False.

And I would like to calculate the element-wise division between those two division, ie, for the first element, I want the value of 0.017664/0.005953.
I've seen the post here: What does .div do in Pandas (Python)
So I simply try df1.div(df2) which returns all NaN values, but doubles the number of columns.

Thanks for @Rafa's comment, I checked with the columns, but

df1.columns

returns

MultiIndex([('Close',  'AAPL'),
            ('Close',   'AMD'),
            ('Close',  'BIDU'),
            ('Close', 'GOOGL'),
            ('Close',  'IXIC'),
            ('Close',  'MSFT'),
           names=[None, 'ticker'])

So I checked with the index.

My

df1.index

returns

Index(['2011-06-29', '2011-07-14', '2011-07-28', '2011-08-11', '2011-08-25',
       '2011-09-09', '2011-09-23', '2011-10-07', '2011-10-21', '2011-11-04',
       ...
       '2021-01-12', '2021-01-27', '2021-02-10', '2021-02-25', '2021-03-11',
       '2021-03-25', '2021-04-09', '2021-04-23', '2021-05-07', '2021-05-21'],
      dtype='object', name='Date', length=250)

but if tried

df1.index.get_level_values(1)

it says

IndexError: Too many levels: Index has only 1 level, not 2

Now I am confused with whether my df1 is a multiindex dataframe or not?

And how I should proceed? Thanks for help.

1 Answers

The problem you're having is that the columns are different - that's why the number of columns doubled, but indices stayed the same. As both df1 and df2 are multiindex and you need second level of multiindex, the only thing needed to be done is:

df1.columns = df1.columns.get_level_values(1)
df2.columns = df2.columns.get_level_values(1)

in case it won't work (for any reason), just make sure that both dataframes have the same list of columns:

df1.columns = df2.columns

and then perform the dividing.

Related