2

I have a timeseries 

date    
2009-12-23    0.0
2009-12-28    0.0
2009-12-29    0.0
2009-12-30    0.0
2009-12-31    0.0
2010-01-04    0.0
2010-01-05    0.0
2010-01-06    0.0
2010-01-07    0.0
2010-01-08    0.0
2010-01-11    0.0
2010-01-12    0.0
2010-01-13    0.0
2010-01-14    0.0
2010-01-15    0.0
2010-01-18    0.0
2010-01-19    0.0
2010-01-20    0.0
2010-01-21    0.0
2010-01-22    0.0
2010-01-25    0.0
2010-01-26    0.0
2010-01-27    0.0
2010-01-28    0.0
2010-01-29    0.0
2010-02-01    0.0
2010-02-02    0.0

I would like to set the value to 1 based on the following rule:

  • If the constant is set 9 this means the 9th of each month. Due to that that 2010-01-09 doesn't exist I would like to set the next date that exists in the series to 1 which is 2010-01-11 above.

I have tried to create two series one (series1) with day < 9 set to 1 and one (series2) with day > 9 to 1 and then series1.shift(1) * series2 It works in the middle of the month but not if day is set to 1 due to that the last date in previous month is set to 0 in series1.

piRSquared
  • 285,575
  • 57
  • 475
  • 624
Jonas
  • 617
  • 3
  • 8
  • 22
  • 1
    Usually, it'll help to have one question per post, and having expected output with your attempt of the problem is appreciated. – Zero Jan 22 '17 at 10:12
  • OK! I will edit the post so it only contains one question. – Jonas Jan 22 '17 at 10:42

1 Answers1

3

Assume your timeseries is s with a datetimeindex

I want to create a groupby object of all index values whose days are greater than or equal to 9.

g = s.index.to_series().dt.day.ge(9).groupby(pd.TimeGrouper('M'))

Then I'll check that there is at least one day past >= 9 and grab the first among them. With those, I'll assign the value of 1.

s.loc[g.idxmax()[g.any()]] = 1
s

date
2009-12-23    1.0
2009-12-28    0.0
2009-12-29    0.0
2009-12-30    0.0
2009-12-31    0.0
2010-01-04    0.0
2010-01-05    0.0
2010-01-06    0.0
2010-01-07    0.0
2010-01-08    0.0
2010-01-11    1.0
2010-01-12    0.0
2010-01-13    0.0
2010-01-14    0.0
2010-01-15    0.0
2010-01-18    0.0
2010-01-19    0.0
2010-01-20    0.0
2010-01-21    0.0
2010-01-22    0.0
2010-01-25    0.0
2010-01-26    0.0
2010-01-27    0.0
2010-01-28    0.0
2010-01-29    0.0
2010-02-01    0.0
2010-02-02    0.0
Name: val, dtype: float64

Note that 2009-12-23 also was assigned a 1 as it satisfies this requirement as well.

piRSquared
  • 285,575
  • 57
  • 475
  • 624