I will try to be as clear as possible in this question. Let's say I have a dataframe formed as:
Date Quantity
05/05/2017 34
12/05/2017 24
19/05/2017 45
26/05/2017 23
2/06/2017 56
9/06/2017 32
I would like to convert this dataframe having weekly data (as you see) into one having daily data. However, there will be some "holes" in my dataframe (ex. week-end days). I already have stored the daily dates into another variable.
I would like to obtain something like this:
Date Quantity
05/05/2017 34
08/05/2017 34
09/05/2017 34
... ...
2/06/2017 56
5/06/2017 56
6/06/2017 56
... ...
My idea is to have a loop that says "whenever the date is prior to the date in the weekly dataframe (ex. 19/05/2017) but higher than the previous date (so 12/05/2017), I want to append to the column "Quantity" in the daily dataframe the correct number (in this case 45).
However, I do not know how to do this in Python. Should I convert the dates into numbers and then back to dates? Does anyone have any faster way to do this? Thank you very much
解决方案
Here's an option using resample with business day frequency (B) and forward fill:
df['Date'] = pd.to_datetime(df.Date, format='%d/%m/%Y')
df.set_index('Date').resample('B').ffill().reset_index()
# Date Quantity
#0 2017-05-05 34
#1 2017-05-08 34
#2 2017-05-09 34
#...
#20 2017-06-02 56
#21 2017-06-05 56
#22 2017-06-06 56
#...