将具有相似索引但级别不相等的数据帧相乘

Question

我有两个数据帧，如下所示

DF1

Location  Month  Date  Ratio
A         June   Jun 1  0.2
A         June   Jun 2  0.3
A         June   Jun 3  0.4
B         June   Jun 1  0.6
B         June   Jun 2  0.7
B         June   Jun 3  0.8

和 df2

Location  Month   Value
A         June    1000
B         June    2000

结果应为： DF3

Location  Month  Date   Value
A         June   Jun 1  200
A         June   Jun 2  300
A         June   Jun 3  400
B         June   Jun 1  1200
B         June   Jun 2  1400
B         June   Jun 3  1600

我该怎么做。我能够毫无问题地进行除法，因为熊猫不知何故在除法时在匹配索引方面做得很好，但在乘法结果中到处都是。

谢谢。

Answer 1

您可以使用df.merge和df.assign

df.assign(Value = df.merge(df1,how='inner',on=['Location','Month'])['Value'].
mul(df['Ratio']))
#or
# df = df.merge(df1,how='inner',on=['Location','Month'])
# df['Value']*=df['Ratio']
Location Month   Date  Ratio   Value
0        A  June  Jun 1    0.2   200.0
1        A  June  Jun 2    0.3   300.0
2        A  June  Jun 3    0.4   400.0
3        B  June  Jun 1    0.6  1200.0
4        B  June  Jun 2    0.7  1400.0
5        B  June  Jun 3    0.8  1600.0

或 使用df.set_index

df.set_index(['Location','Month'],inplace=True)
df1.set_index(['Location','Month'],inplace=True)
df['Value'] = df['Ratio']*df1['Value']

Answer 2

IIUC 和Location是两个数据帧的索引，那么您可以使用pandas.Series.mul

df1["Value"] = df1.Ratio.mul(df2.Value)
df1
Month   Date  Ratio   Value
Location
A         June  Jun 1    0.2   200.0
A         June  Jun 2    0.3   300.0
A         June  Jun 3    0.4   400.0
B         June  Jun 1    0.6  1200.0
B         June  Jun 2    0.7  1400.0
B         June  Jun 3    0.8  1600.0