python - 来自另一个数据集的两个日期的语句之间的 Pandas

Question

我可以使用什么公式来根据特定日期确定介于开始日期和结束日期之间的支付期数？换句话说，从 df1 开始，我有一个日期需要与 df2 开始日期和结束日期进行比较，然后在新数据集中生成这些日期所在的支付期。

到目前为止，我使用的公式如下。请记住，我不是经验丰富的程序员:

第一次尝试:

def calc(row):
    if pf2(row['actn_dt']) >= pp_calendar([0], and pf2(row['actn_dt']) <= df2([1]):
        return df2([2,3])

第二次尝试:

pf2['pay'] = np.where (pf2['actn_dt'] >= df2[0]) | (pf2['actn_dt'] <= pf2[1]), pp_calendar[2]

第三次尝试:

def calc(row):
    if pf2(row['actn_dt']) >= df2(row[1]) | pf2(row['actn_dt']) <= df2(row[2]):
        return df2(row[3])


pf2['pay'] = pf2.apply (lambda row: calc(row), axis=1)

打印 df:

                actn_dt
16   2008-09-28 00:00:00
17   2008-03-16 00:00:00
18   2009-08-30 00:00:00
43   2008-06-22 00:00:00
89   2009-08-16 00:00:00
106  2009-03-29 00:00:00
244  2009-08-30 00:00:00
371  2009-09-13 00:00:00
400  2009-07-19 00:00:00
439  2007-12-23 00:00:00

打印 df2:

        START_DATE   END_DATE           PAY_PERIOD   CALENDAR_YEAR
0       2008-09-28   2008-10-11         10           2008
1       2008-03-16   2008-03-16         06           2008
2       2009-08-30   2009-09-12         18           2009
3       2008-06-22   2008-06-22         13           2008
4       2009-03-29   2009-04-11         07           2009

预期结果:

     actn_dt      START_DATE    END_DATE     PAY_PERIOD   CALENDAR_YEAR
16   2008-09-28   2008-09-28    2008-10-11   10           2008
17   2008-03-16   2008-03-16    2008-03-29   06           2008   
18   2009-08-30   2009-08-30    2009-09-12   18           2009
43   2008-06-22   2008-06-22    2008-07-05   13           2008
89   2009-08-16   2009-08-16    2008-08-29   17           2009
106  2009-03-29   2009-03-29    2009-04-11   07           2009
244  2009-08-30   2009-08-30    2009-09-12   18           2009

感谢您的知识和时间!

Answer 1

标题解决方案

def find_pay_period(date, df):
    df = df[(df.START_DATE <= date) & (date <= df.END_DATE)].iloc[0, :]
    df['actn_dt'] = date
    return df

df1.actn_dt.apply(lambda x: find_pay_period(x, df2))

说明

开始于

# apply() will take date in df1 and find the first row in df2
# such that the date is between START_DATE and END_DATE and
# then return the row.
df1.actn_dt.apply(lambda x: find_pay_period(x, df2))

现在这个

def find_pay_period(date, df):
    # df[] use boolean mask to filter
    # .iloc[0, :] grabs first row of filtered DataFrame.
    # Keep in mind this is a Series.
    df = df[(df.START_DATE <= date) & (date <= df.END_DATE)].iloc[0, :]
    # add back your date
    df['actn_dt'] = date
    return df