将来自两个不同pandas数据帧的两列相乘

Question

我有一台pandas.DataFrame。

      df1
           Year    Class     Price    EL
           2024     PC1       $243    Base
           2025     PC1       $215    Base
           2024     PC1       $217    EL_1
           2025     PC1       $255    EL_1
           2024     PC2       $217    Base
           2025     PC2       $232    Base
           2024     PC2       $265    EL_1
           2025     PC2       $215    EL_1

我有另一个pandas.DataFrame

           df2  
              Year    Price_factor
              2024      1
              2025      0.98

我想将df2'Price_factor‘应用到df1'Price’列。我试过我的代码，但它不工作。

          df3=df1.groupby(['Class','EL'])['Price']*df2['Price_factor]

提前感谢您的帮助。

Answer 1

使用map，

df1['Price_factor'] = df1['Year'].map(df2.set_index('Year')['Price_factor'])
df1['Price_adjusted']= df1['Price'].str.strip('$').astype(int) * df1['Price_factor']
df1

输出：

|    |   Year | Class   | Price   | EL   |   Price_factor |   Price_adjusted |
|----|--------|---------|---------|------|----------------|------------------|
|  0 |   2024 | PC1     | $243    | Base |           1    |           243    |
|  1 |   2025 | PC1     | $215    | Base |           0.98 |           210.7  |
|  2 |   2024 | PC1     | $217    | EL_1 |           1    |           217    |
|  3 |   2025 | PC1     | $255    | EL_1 |           0.98 |           249.9  |
|  4 |   2024 | PC2     | $217    | Base |           1    |           217    |
|  5 |   2025 | PC2     | $232    | Base |           0.98 |           227.36 |
|  6 |   2024 | PC2     | $265    | EL_1 |           1    |           265    |
|  7 |   2025 | PC2     | $215    | EL_1 |           0.98 |           210.7  |

Answer 2

您不需要groupby，而是合并两个表，然后将列相乘。为了能够计算新价格，我必须将原始价格列转换为浮点数，方法是删除$-符号并使用.astype(float)：

import pandas as pdb

# df1 = pd.read_clipboard()
# df2 = pd.read_clipboard()

df3 = df1.merge(df2, how='left', on="Year")
df3['New Price'] = df3['Price'].str[1:].astype(float) *df3['Price_factor']
print(df3)

   Year Class Price    EL  Price_factor  New Price
0  2024   PC1  $243  Base          1.00     243.00
1  2025   PC1  $215  Base          0.98     210.70
2  2024   PC1  $217  EL_1          1.00     217.00
3  2025   PC1  $255  EL_1          0.98     249.90
4  2024   PC2  $217  Base          1.00     217.00
5  2025   PC2  $232  Base          0.98     227.36
6  2024   PC2  $265  EL_1          1.00     265.00
7  2025   PC2  $215  EL_1          0.98     210.7