CODEWITHSUNDEEP
pythonpandasdataframe
WhelanG
WhelanG

Reputation: 235

Convert weekly data into daily data with Python

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

Upvotes: 4

Views: 6497

Answers (2)

akuiper
akuiper

Reputation: 214927

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
#...

Upvotes: 4

EFT
EFT

Reputation: 2369

If we say that your daily data is in a variable named days and Date is your index, then

>>>df.reindex(days).fillna(method='ffill')

should return what you want.

Upvotes: 2

Related Questions