iterate over a column check condition and carry calculations with values of other data frames

Viewed 20

import pandas as pd import numpy as np

I do have 3 dataframes df1, df2 and df3. df1=

data = {'Period': ['2024-04-O1', '2024-07-O1', '2024-10-O1', '2025-01-O1', '2025-04-O1', '2025-07-O1', '2025-10-O1', '2026-01-O1', '2026-04-O1', '2026-07-O1', '2026-10-O1', '2027-01-O1', '2027-04-O1', '2027-07-O1', '2027-10-O1', '2028-01-O1', '2028-04-O1', '2028-07-O1', '2028-10-O1'], 
        'Price': ['NaN','NaN','NaN','NaN', 'NaN','NaN','NaN','NaN', 'NaN','NaN','NaN','NaN', 
                  'NaN','NaN','NaN','NaN', 'NaN','NaN','NaN'], 
        'years': [2024,2024,2024,2025,2025,2025,2025,2026,2026,2026,2026,2027,2027,2027,2027,2028, 
                 2028,2028,2028],
        'quarters':[2,3,4, 1,2,3,4, 1,2,3,4, 1,2,3,4, 1,2,3,4]
}
df1 = pd.DataFrame(data=data)

df2=

data = {'price': [473.26,244,204,185, 152, 157], 
        'year': [2023, 2024, 2025, 2026, 2027, 2028] 
}
df3 = pd.DataFrame(data=data)

df3=

data = {'quarters': [1,2,3,4], 
        'weights': [1.22, 0.81, 0.83, 1.12] 
}
df2 = pd.DataFrame(data=data)

My aim is to compute the price of df1. For each iteration through df1 check condition and carry calculations accordingly. For example for the 1st iteration, check if df1['year']=2024 and df1['quarters']=2. Then df1['price']=df2.loc[df2['year']=='2024', 'price'] * df3.loc[df3['quarters']==2, 'weights'].

===>>> df1['price'][0]=**473.26*0.81**.

       df1['price'][1]=**473.26*0.83**.

        ...
        ...
        ...

and so on.

I could ha used this method but i want to write a code in a more efficient way. I would like to use the following code structure.

for i in range(len(df1)):
    if (df1['year']==2024) & (df1['quarter']==2):
        df1['Price']= df2.loc[df2['year']==2024, 'price'] * df3.loc[df3['quarters']==2, 'weights']
    elif (df1['year']==2024) & (df1['quarter']==3):
        df1['price']= df2.loc[df2['year']=='2024', 'price'] * df3.loc[df3['quarters']==3, 'weights'] 
    elif (df1['year']==2024) & (df1['quarters']==4):
        df1['Price']= df2.loc[df2['year']=='2024', 'price'] * df3.loc[df3['quarters']==4, 'weights']
     ...
     ...
     ...

Thanks!!!

1 Answers

I think if I understand correctly you can use pd.merge to bring these fields together first.

df1 = df1.merge(df2, how='left' , left_on='years', right_on='year')
df1 = df1.merge(df3, how='left' , left_on='quarters', right_on='quarters')
df1['Price'] = df1['price']*df1['weights']
Related