Geek Answers Handbook

How to select df observations using date and time index attributes in Pandas?

For df of this type, where we have a DateTime index:

DateTime               A                           
2007-08-07 18:00:00    1
2007-08-08 00:00:00    2
2007-08-08 06:00:00    3
2007-08-08 12:00:00    4
2007-08-08 18:00:00    5
2007-11-02 18:00:00    6
2007-11-03 00:00:00    7
2007-11-03 06:00:00    8
2007-11-03 12:00:00    9
2007-11-03 18:00:00   10

I would like to multiply observations using index attributes, for example:

  • First business day of the month
  • Last business day of the month
  • First Friday of the Month WOM-1FRI
  • Third Friday of the month WOM-3FRI

I am particularly interested to know if this can be done using something like:

df.loc[(df['A'] < 5) & (df.index == 'WOM-3FRI'), 'Signal'] = 1

thank

+4
source share
1 answer

You can try...

# FIRST DAY OF MONTH
df.loc[df[1:][df.index.month[:-1]!=df.index.month[1:]].index]

# LAST DAY OF MONTH
df.loc[df[:-1][df.index.month[:-1]!=df.index.month[1:]].index]

# 1st Friday
fr1 = df.groupby(df.index.year*100+df.index.month).apply(lambda x: x[(x.index.week==1)*(x.index.weekday==4)])

# 3rd Friday
fr3 = df.groupby(df.index.year*100+df.index.month).apply(lambda x: x[(x.index.week==3)*(x.index.weekday==4)])

If you want to remove additional levels in the index fr1and fr3:

fr1.index=fr1.index.droplevel(0)
fr3.index=fr3.index.droplevel(0)
0
source

More articles:


All Articles