返回连续的丢失工作日日期，并在丢失的日期旁边分配费率 [英] Return the consecutive missing weekdays dates and assign rate next to missing dates

问题描述

Dates       rates
7/26/2019   1.04
7/30/2019   1.0116
7/31/2019   1.005
8/1/2019    1.035
8/2/2019    1.01
8/6/2019    0.9886
8/12/2019   0.965

df = df.merge(
    pd.DataFrame({'Dates':df['Dates'] + pd.offsets.BDay()}), on='Dates', how='outer'
).sort_values('Dates').bfill().dropna().reset_index(drop=True)

print(df)

我尝试了上面的代码，但无法修复连续丢失的工作日.它只能修复1天.在上述数据框中，缺少了2019年7月29日以及8月5日，7月8日，8月​​9日.这些是工作日.我需要填充缺少的工作日日期，并分配缺少日期旁边的费率".例如:将2019年7月30日的'费率'分配给缺失的2019年7月29日，依此类推，对于所有缺失的日期.请提出建议.谢谢，我期待以下输出

I tried the above code but its unable to fix the consecutive missing business days. It can fix only for 1 day. In the above dataframe, 29th July 2019 then 5th, 7th , 8th , 9th August are missing. These are weekdays. I need to populate the missing weekdays dates and assign the 'rate' which is next to missing date. For example: Assign the 30th july 2019 'rate' to the missing 29th july 2019 as well and so on for all missing dates. Please suggest. Thanks I expect the following output

Dates       rates
7/26/2019   1.04
7/29/2019   1.021
7/30/2019   1.0116
7/31/2019   1.005
8/1/2019    1.035
8/2/2019    1.01
8/5/2019    0.9886
8/6/2019    0.9886
8/7/2019    0.965
8/8/2019    0.965
8/8/2019    0.965
8/12/2019   0.965

推荐答案

尝试如下所示的选项1，其中包括创建新的df&合并(您已经走过的路线)

Try option 1 as below which includes creating a new df & merging it (the route you are already on)

或@piRSquared(bfill()代替ffill())建议的选项2

or option 2 as suggested by @piRSquared (bfill() instead of ffill())

df['Dates'] = pd.to_datetime(df['Dates'])
a=pd.DataFrame({'Dates':pd.bdate_range('2019-07-26', '2019-08-12')})
df.merge(a, on='Dates', how='outer').sort_values('Dates').bfill().dropna().reset_index(drop=True) 

输出

        Dates   rates
0   2019-07-26  1.0400
1   2019-07-29  1.0116
2   2019-07-30  1.0116
3   2019-07-31  1.0050
4   2019-08-01  1.0350
5   2019-08-02  1.0100
6   2019-08-05  0.9886
7   2019-08-06  0.9886
8   2019-08-07  0.9650
9   2019-08-08  0.9650
10  2019-08-09  0.9650
11  2019-08-12  0.9650

df.set_index('Dates').asfreq('B').bfill().reset_index()

输出

         Dates  rates
0   2019-07-26  1.0400
1   2019-07-29  1.0116
2   2019-07-30  1.0116
3   2019-07-31  1.0050
4   2019-08-01  1.0350
5   2019-08-02  1.0100
6   2019-08-05  0.9886
7   2019-08-06  0.9886
8   2019-08-07  0.9650
9   2019-08-08  0.9650
10  2019-08-09  0.9650
11  2019-08-12  0.9650

这篇关于返回连续的丢失工作日日期，并在丢失的日期旁边分配费率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！