Wsrtjtyk

is there a way to reindex two dataframes (of differing levels) so that they share a common index across all levels?

Demo:

Create a basic Dataframe named 'A':

index = np.array(['AUD','BRL','CAD','EUR','INR'])

data = np.random.randint(1, 20, (5,5))

A = pd.DataFrame(data=data, index=index, columns=index)  

Create a MultiIndex Dataframe named 'B':

np.random.seed(42)

midx1 = pd.MultiIndex.from_product([['Bank_1', 'Bank_2'], 

['AUD','CAD','EUR']], names=['Bank', 'Curency'])

B = pd.DataFrame(np.random.randint(10,25,6), midx1)

B.columns = ['Notional']

Basic DF:

>>> Dataframe A:



        AUD     BRL     CAD     EUR     INR

AUD     7       19      11      11      4

BRL     8       3       2       12      6

CAD     2       1       12      12      17

EUR     10      16      15      15      19

INR     12      3       5       19      7

MultiIndex DF:

>>> Dataframe B:



                    Notional

Bank    Curency     

Bank_1  AUD         16

        CAD         13

        EUR         22

Bank_2  AUD         24

        CAD         20

        EUR         17

The goal is to:

1) reindex B so that its currency level includes each currency in A's index. B would then look like this (see BRL and INR included, their Notional values are not important):

                    Notional

Bank    Curency     

Bank_1  AUD         16

        CAD         13

        EUR         22

        BRL         0

        INR         0

Bank_2  AUD         24

        CAD         20

        EUR         17

        BRL         0

        INR         0

2) reindex A so that it includes each Bank from the first level of B's index. A would then look like this:

               AUD      BRL     CAD     EUR     INR

Bank_1  AUD     7       19      11      11      4

        BRL     8       3       2       12      6

        CAD     2       1       12      12      17

        EUR     10      16      15      15      19

        INR     12      3       5       19      7

Bank_2  AUD     7       19      11      11      4

        BRL     8       3       2       12      6

        CAD     2       1       12      12      17

        EUR     10      16      15      15      19

        INR     12      3       5       19      7

The application of this will be on much larger dataframes so I need a pythonic way to do this.

For context, ultimately I want to multiply A and B. I am trying to reindex to get matching indices as that was shown as a clean way to multiply dataframes of various index levels here:
Pandas multiply dataframes with multiindex and overlapping index levels

Thank you for any help.

To get the B using reindex

B.reindex( pd.MultiIndex.from_product([B.index.levels[0], 

A.index], names=['Bank', 'Curency']),fill_value=0)



Out[62]: 

                Notional

Bank   Curency          

Bank_1 AUD            16

       BRL             0

       CAD            13

       EUR            22

       INR             0

Bank_2 AUD            24

       BRL             0

       CAD            20

       EUR            17

       INR             0

To get the A using concat

pd.concat([A]*2,keys=B.index.levels[0])

Out[69]: 

            AUD  BRL  CAD  EUR  INR

Bank                               

Bank_1 AUD   10    5   10   14    1

       BRL   17    1   14   10    8

       CAD    3    7    3   15    2

       EUR   17    1   15    2   16

       INR    7   15    6    7    4

Bank_2 AUD   10    5   10   14    1

       BRL   17    1   14   10    8

       CAD    3    7    3   15    2

       EUR   17    1   15    2   16

       INR    7   15    6    7    4