pythonpandasdataframemultiplication

Multiply two columns of different dataframes in Python

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

df Prices

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

df Table_ScenarioPercentages

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:

result multiplication

Does someone know how to do this in Python?

Solution

  • 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