如何在Python Pandas中按匹配键相乘不同形状的同表头DataFrame？

嘿，这个问题我之前也碰到过！你遇到的TypeError: can't multiply sequence by non-int of type 'str'本质有两个原因：

1. 直接用df1.mul(df2.values)时，不小心把两个DataFrame里的字符串列（Devices、Sources、Status）也卷进了乘法操作，字符串和数值当然没法相乘；
2. df1和df2的行数不匹配（df1有7行，df2只有4行），直接用values会跳过索引对齐，就算没报错，结果也完全不符合预期。

下面给你两种靠谱的解决方案，都是以Devices和Sources为共同键来匹配计算：

方法一：先合并再逐列相乘 #

这种方法逻辑清晰，适合新手理解：

1. 先把两个DataFrame按Devices和Sources合并，确保每一行的产品和来源都对应正确；
2. 提取日期类的数值列，逐列计算数量×MRP；
3. 整理结果，保留需要的列。

具体代码：

import pandas as pd

# 生成示例数据
df1 = pd.DataFrame({'Devices':['Mobile','Mobile','Mobile','Mobile','Mobile','Laptop','Desktop'],'Sources':['India','India','India','India','UK','UK','US'],'Status':['ok','ok','notok','ok','ok','notok','ok'],'10/01/2020':[45,45,60,56,50,65,50],'10/02/2020':[45,60,56,56,50,65,50],'10/03/2020':[45,60,56,56,50,65,50],'10/04/2020':[45,60,56,15,25,26,20]})
df2 = pd.DataFrame({'Devices':['Mobile','Mobile','Laptop','Desktop'],'Sources':['India','UK','UK','US'],'Status':['MRP','MRP','MRP','MRP'],'10/01/2020':[8000,8200,7800,8500],'10/02/2020':[8200,6500,7900,8000],'10/03/2020':[7800,13000,12500,7800],'10/04/2020':[8500,7800,21000,8500]})

# 1. 按共同键合并，保留df1的所有行，用后缀区分两个表的列
merged_df = pd.merge(df1, df2, on=['Devices', 'Sources'], suffixes=('_qty', '_mrp'), how='left')

# 2. 获取所有日期列名
date_cols = [col for col in df1.columns if '/' in col]

# 3. 逐列计算数量×MRP
for col in date_cols:
    merged_df[f'{col}_total'] = merged_df[f'{col}_qty'] * merged_df[f'{col}_mrp']

# 4. 整理最终结果，保留需要的列
final_df = merged_df[['Devices', 'Sources', 'Status_qty'] + [f'{col}_total' for col in date_cols]]
final_df.rename(columns={'Status_qty': 'Status'}, inplace=True)

print(final_df)

方法二：用映射字典直接匹配计算 #

如果不想合并DataFrame，可以先把df2的MRP数据做成一个以(Devices,Sources)为键的映射字典，然后在df1中直接匹配计算，效率更高：

import pandas as pd

# 生成示例数据（同上）
df1 = pd.DataFrame({'Devices':['Mobile','Mobile','Mobile','Mobile','Mobile','Laptop','Desktop'],'Sources':['India','India','India','India','UK','UK','US'],'Status':['ok','ok','notok','ok','ok','notok','ok'],'10/01/2020':[45,45,60,56,50,65,50],'10/02/2020':[45,60,56,56,50,65,50],'10/03/2020':[45,60,56,56,50,65,50],'10/04/2020':[45,60,56,15,25,26,20]})
df2 = pd.DataFrame({'Devices':['Mobile','Mobile','Laptop','Desktop'],'Sources':['India','UK','UK','US'],'Status':['MRP','MRP','MRP','MRP'],'10/01/2020':[8000,8200,7800,8500],'10/02/2020':[8200,6500,7900,8000],'10/03/2020':[7800,13000,12500,7800],'10/04/2020':[8500,7800,21000,8500]})

# 1. 创建(Devices,Sources)到MRP列的映射字典
date_cols = [col for col in df1.columns if '/' in col]
mrp_map = df2.set_index(['Devices', 'Sources'])[date_cols].to_dict('index')

# 2. 在df1中逐列匹配MRP并计算
for col in date_cols:
    df1[f'{col}_total'] = df1.apply(lambda row: row[col] * mrp_map[(row['Devices'], row['Sources'])][col], axis=1)

print(df1)

两种方法都能得到正确的结果，你可以根据自己的习惯选择~

内容的提问来源于stack exchange，提问作者Ghanshyam Savaliya