python - Pandas asfreq with weekly frequency

Question

I have weekly data of logs for some devices. For some device it start on Monday, for some on Wednesday etc. Sometimes there are gaps of ~month in this data, but I want the DataFrame index to still contain rows for each week with NaN value.

I am trying to use asfreq('W') in Python, but I cannot get what I expect.

Example:

What I have:

Date            Some_Value
====            ==========
2019-04-10      2
2019-04-17      1
2019-04-24      3
2019-05-01      1
2019-05-08      3
2019-05-15      2
2019-06-06      3
2019-06-13      2

What I expect/want to have (note 2 new rows with NaNs):

Date            Some_Value
====            ==========
2019-04-10      2
2019-04-17      1
2019-04-24      3
2019-05-01      1
2019-05-08      3
2019-05-15      2
2019-05-22      NaN
2019-05-30      NaN
2019-06-06      3
2019-06-13      2

What I get with asfreq('W'):

Date            Some_Value
====            ==========
2019-03-31      NaN
2019-04-07      NaN
2019-04-14      NaN
...................

So, I get all NaN values and the dates from each Sunday. But I do not need dates from each Sunday. I need to take the first date of a DataFrame (of first row in a group in pandas' groupby in case of many time-series) and resample weekly form that first row.

Is it achievable directly with pandas asfreq? With some other pandas method? Or should it be some more complex custom function?

Thanks.

Answer 1

Problem is in your data are first values in Wednesday, last 2 in Thursday, so asfreq return NaNs, because try change it to Sunday weekday freq - docs:

W-SUN weekly frequency (Sundays). Same as 'W'

One possible solution, but DatetimeIndex is changed for Sundays:

print (df.resample('W').first())
            Some_Value
Date                  
2019-04-14         2.0
2019-04-21         1.0
2019-04-28         3.0
2019-05-05         1.0
2019-05-12         3.0
2019-05-19         2.0
2019-05-26         NaN
2019-06-02         NaN
2019-06-09         3.0
2019-06-16         2.0

If change frequency in asfreq:

print (df.asfreq('W-Wed'))
            Some_Value
Date                  
2019-04-10         2.0
2019-04-17         1.0
2019-04-24         3.0
2019-05-01         1.0
2019-05-08         3.0
2019-05-15         2.0
2019-05-22         NaN
2019-05-29         NaN
2019-06-05         NaN
2019-06-12         NaN

print (df.asfreq('W-Thu'))
            Some_Value
Date                  
2019-04-11         NaN
2019-04-18         NaN
2019-04-25         NaN
2019-05-02         NaN
2019-05-09         NaN
2019-05-16         NaN
2019-05-23         NaN
2019-05-30         NaN
2019-06-06         3.0
2019-06-13         2.0