python - 将具有 multiindex 的 Pandas 数据框除以另一个具有较小 multiindex 的数据框

Question

我想将具有多索引的数据帧中的所有列除以另一个具有多索引的数据帧，更小一级。前两个水平的蛾指数是相同的。并且应该播放第三级。

df_0 = pd.DataFrame( {
    "col0": [ 1, 2, 3, 4, 5 ],
    "col1": [ 3, 6, 9, 12, 15 ],
} )
df_0.index = pd.MultiIndex.from_tuples(
    [ ( "A", "a", 0 ), ( "A", "a", 1 ), ( "A", "b", 0 ), ( "A", "b", 1 ), ( "B", "b", 0 ) ]
)
df_0.index.names = [ "foo", "bar", "baz" ]

df_1 = pd.DataFrame( {
    "stuff": [ 100, 110, 120, 130, ],
} )
df_1.index = pd.MultiIndex.from_tuples(
    [ ( "A", "a" ), ( "A", "b" ), ( "B", "a" ), ( "B", "b" ) ]
)
df_1.index.names = [ "foo", "bar" ]
print( df_0 )
print( df_1 )

这给了我以下两个数据帧 df_0

             col0  col1
foo bar baz            
A   a   0       1     3
        1       2     6
    b   0       3     9
        1       4    12
B   b   0       5    15

和df_1

         stuff
foo bar       
A   a      100
    b      110
B   a      120
    b      130

如果我尝试将每个列值除以相应的 stuff 列，我会收到一条错误消息

print( df_0.div( df_1 ) )
Join on level between two MultiIndex objects is ambiguous

我想要达到的结果是:

              col0    col1
foo bar baz            
A   a   0    1/100   3/100
        1    2/100   6/100
    b   0    3/110   9/110
        1    4/110  12/110
B   b   0    5/130  15/130

Answer 1

使用DataFrame.reindex通过第一个 DataFrame，所以在两者中有相同的 3 个级别，所以可能由 stuff 选择的 Series 划分:

df = df_0.div(df_1.reindex(df_0.index)['stuff'], axis=0)
print (df)
                 col0      col1
foo bar baz                    
A   a   0    0.010000  0.030000
        1    0.020000  0.060000
    b   0    0.027273  0.081818
        1    0.036364  0.109091
B   b   0    0.038462  0.115385

详细信息:

print( df_1.reindex(df_0.index)['stuff'] )
foo  bar  baz
A    a    0      100
          1      100
     b    0      110
          1      110
B    b    0      130
Name: stuff, dtype: int64