How to filter dates on multiindex dataframe

Question

I'm looking for a way to filter a multiindex dataframe like the following by day of the week and/or selected dates. Let's say I need

a query to select only mondays;
another query in which I want to select all days except monday and friday;
a third query to select data present in an input list of dates, like select all dates in ['2015-05-14', '2015-05-21', '2015-05-22'];
and finally, a query combining selection based on day of week and a list of dates, like select all dates in ['2015-05-14', '2015-05-21', '2015-05-22'] and thursdays.

What's the way to do it?

                Col1        Col2     Col3    Col4
Date        Two 
2015-05-14  10   81.370003  6.11282  39.753  44.950001
            11   80.419998  6.03380  39.289  44.750000
            C3   80.879997  6.00746  41.249  44.360001
2015-05-19   3   80.629997  6.10465  41.047  40.980000
            S9   80.550003  6.14370  41.636  42.790001
2015-05-21  19   80.480003  6.16096  42.137  43.680000
2015-05-22  C3   80.540001  6.13916  42.179  43.490002

akuiper · Accepted Answer · 2017-07-03 01:48:38Z

8

If you have the Date as datetime type, you can just use dayofweek to get the day of week and query based on it.

Select only Mondays:

df[df.index.get_level_values('Date').dayofweek == 0]

Select days except Monday and Friday:

import numpy as np
df[np.in1d(df.index.get_level_values('Date').dayofweek, [1,2,3,5,6])]

#                    Col1      Col2   Col3       Col4
#      Date Two             
#2015-05-14 10  81.370003   6.11282 39.753  44.950001
#           11  80.419998   6.03380 39.289  44.750000
#           C3  80.879997   6.00746 41.249  44.360001
#2015-05-19 3   80.629997   6.10465 41.047  40.980000
#           S9  80.550003   6.14370 41.636  42.790001
#2015-05-21 19  80.480003   6.16096 42.137  43.680000

answered Jul 3, 2017 at 1:48

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bella Over a year ago

Cool! Let me just ask you for something more: what if I also want to select data present in an input list of dates? Like, select all dates in ['2015-05-14', '2015-05-21', '2015-05-22']; and more, what if I want to combine selecting based on day of week and a list of dates? I can edit the question. Thanks!

akuiper Over a year ago

You can use a or condition np.in1d(dayofweek, [1,2,3,5,6]) | np.in1d(datestr, ['2017-01-01', '2017-04-02']) for instance. You might need to convert the date to string in the second expression.

Collectives™ on Stack Overflow

How to filter dates on multiindex dataframe

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related