Multiply two columns of different dataframes in Python

Question

I have two dataframes (both are datatype float). The first one is called Prices which contains only prices:

The second one is called Table_ScenarioPercentages which contains percentages where the prices need to be multiplied by:

So what I want to do is multiply the value of Period 20240201 (Prices) by Month 0 (Table_ScenarioPercentages), the value of Period 20240301 (Prices) by Month 1 (Table_ScenarioPercentages), etc.

I've tried:

Prices.iloc[:, 0]*Table_ScenarioPercentages.iloc[:, 0] 

but this just gives a lot of NaN's and the wrong format:

Does someone know how to do this in Python?

Answer 1

TL;DR

Use Prices['DATED BRENT']*Table_ScenarioPercentages['Supply Side Shock Up - Crude'].values

---

As per the docs on pd.Series (italics mine):

Operations between Series (+, -, /, *, **) align values based on their associated index values

Since the indices for both dfs (20240201, 20240301, ... and 0, 1, ...) share 0 values, you end up with a new Series with the index values of both and NaN values as the result of some value * NaN or vice versa for each index value.

If you want to multiply on index position, use Series.values to get an ndarray (or use: Series.to_numpy). N.B. both Series need to have the same length in this case.

Data sample

import pandas as pd

df1 = pd.DataFrame({'Col1': [20, 10]}, index=[1, 'A'])
df2 = pd.DataFrame({'Col2': [10, 20]})

print(df1)

   Col1
1    20
A    10

print(df2)

   Col2
0    10
1    20

Index-based multiplication (cf. OP's undesired result)

print(df1['Col1']*df2['Col2'])

0      NaN    # NaN * 10 (0 not in `df.index`)
1    400.0    # 20 * 20 (1 in both indices)
A      NaN    # 10 * NaN ('A' not in `df2.index`)
dtype: float64

Multiplication by index position (desired, with Series.values)

print(df1['Col1']*df2['Col2'].values)

1    200    # 20 * 10
A    200    # 10 * 20
Name: Col1, dtype: int64