CODEWITHSUNDEEP
pythonpandasdataframe
Spring
Spring

Reputation: 193

multiply two columns from two different pandas dataframes

I have a 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

I have another pandas.DataFrame

           df2  
              Year    Price_factor
              2024      1
              2025      0.98

I want to apply df2['Price_factor'] to df1['Price'] column. I tried my code but it didn't work.

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

Thank you for your help in advance.

Upvotes: 1

Views: 2743

Answers (2)

Scott Boston
Scott Boston

Reputation: 153460

Use 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

Output:

|    |   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  |

Upvotes: 2

divingTobi
divingTobi

Reputation: 2300

You do not need a groupby, but rather merge the two tables, and then multiply the columns. I had to convert the original price column to a float by removing the $-sign and using .astype(float) in order to be able to calculate the new price:

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

Upvotes: 3

Related Questions