尝试基于切片另一个数据帧在数据帧中创建列

Question

在我的玩具示例中，我有一个原始的数据帧df1。我想基于df1中的值在新的数据帧df2中创建一列

如果我在SQL中这样做的话

Update df2.value = (
   select df1.value where df1.settlement_date = df2.index 
        AND 
   df1.contract_date = df2.contract date
)

我试图通过切片来实现这一点，但它抛出ValueError: Lengths must match to compare

什么是干净的，pythonic/panda ic方法来做这样的手术

df1 = pd.DataFrame([[1,'2016-01-01','2016-06-01'], 
                    [2,'2016-01-01','2016-07-01'],
                    [2,'2016-01-01','2016-08-01'],
                    [2,'2016-01-02','2016-06-01'],
                    [2,'2016-01-02','2016-07-01'],
                    [2,'2016-01-02','2016-08-01'],
                    [2,'2016-01-03','2016-06-01'],
                    [2,'2016-01-03','2016-07-01'],
                    [2,'2016-01-03','2016-08-01'],
                    [2,'2016-01-04','2016-06-01'],
                    [3,'2016-01-04','2016-07-01'],
                    [4,'2016-01-04','2016-08-01']],
                   columns=['value', 'settlement_date', 'contract_date'])

df1['settlement_date'] = df1['settlement_date'].astype('datetime64')
df1['contract_date'] = df1['contract_date'].astype('datetime64')

df2 =pd.DataFrame([['2016-01-01','2016-06-01'], 
                   ['2016-01-02','2016-06-01'],
                   ['2016-01-03','2016-06-01'],
                   ['2016-01-04','2016-06-01']],
                   columns=['settlement_date', 'contract_date'])
df2['settlement_date'] = df2['settlement_date'].astype('datetime64')
df2['contract_date'] = df2['contract_date'].astype('datetime64')
df2.set_index('settlement_date', inplace=True)


df2['value']= df1[(df1['settlement_date']==df2.index) & (df1['contract_date']==df2['contract_date'])]['value']

Answer 1

您可以简单地执行join或merge这样的操作：

df2 = df2.merge(df1,how="left",on=["settlement_date","contract_date"])

在上述情况下，不要使用set_index，或者如果要使用index，可以使用join