迭代列检查条件，并使用其他数据帧的值进行计算。

Question

进口熊猫为pd进口numpy为np

我有df1、df2和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)

我的目标是计算price of df1。对于通过df1的每一次迭代，检查条件并进行相应的计算。例如，在第一次迭代中，检查df1['year']=2024和df1['quarters']=2.然后是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.

我可以使用这种方法，但我想以更有效的方式编写代码。我想使用以下代码结构。

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']
     ...
     ...
     ...

谢谢！

Answer 1

我认为，如果我正确理解，您可以首先使用pd.merge将这些字段组合在一起。

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