Pandas multi index dataset divide

user_12992141 0
Viewed 50

I have a multi index dataframe which has multiple rows with the same index, it looks like this :

table 1

                                m_value
LETTER     NUMBER
A                    1          45487
A                    1          7424
A                    1          1564
A                    1          486421
A                    2           7897
A                    2           12
A                    2           4897
...                 ...          ...
D                    4           78978
D                    4           12
D                    4           27827

And another one, smaller, which has the same multindex but only one association per key :

table 2

                              divide
LETTER     NUMBER
A                    1          45
A                    2          25
A                    3          7
A                    4          11
B                    1          25
B                    2          3
B                    3          45
...                 ...         ...
D                    2          89
D                    3          25
D                    4          10

I want to divide the first dataset by the second one, respecting the multindex keys.

I tried this 

table_A['result'].div(table_2['divide']).values

but the result is not the one expected, I do not know what happened.

Any better solution to do that?

pandas python
1 Answers

I believe this is due to unmatched index between tbl2 and tbl1. For example, the code you posted gives

LETTER  NUMBER
A       1          1010.822222
        1           164.977778
        1            34.755556
        1         10809.355556
        2           315.880000
        2             0.480000
        2           195.880000
        3                  NaN
        4                  NaN
B       1                  NaN
        2                  NaN
        3                  NaN
D       2                  NaN
        3                  NaN
        4          7897.800000
        4             1.200000
        4          2782.700000
dtype: float64

As we see, the NaN values correspond to the indexes that not in tbl1. So we can do:

tbl1['m_value'].div(tbl2['divide']).dropna()

And we get:

LETTER  NUMBER
A       1          1010.822222
        1           164.977778
        1            34.755556
        1         10809.355556
        2           315.880000
        2             0.480000
        2           195.880000
D       4          7897.800000
        4             1.200000
        4          2782.700000
dtype: float64

Which aligns with tbl1.

user_42384080
Related