如何在多索引数据框上过滤日期 [英] How to filter dates on multiindex dataframe

问题描述

我正在寻找一种过滤多索引数据框的方法，例如按星期几和/或选定的日期进行过滤.假设我需要

• 对select only mondays的查询；
• 我要在其中进行的另一查询select all days except monday and friday;
• 第三个查询，用于选择日期输入列表中的数据，例如select all dates in ['2015-05-14', '2015-05-21', '2015-05-22'];
• 最后，一个查询结合了基于星期几的选择和日期列表，例如select all dates in ['2015-05-14', '2015-05-21', '2015-05-22'] and thursdays.

这是怎么做的?

                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

解决方案

如果您将Date设置为datetime类型，则可以使用dayofweek来获取星期几并基于该日期进行查询. /p>

仅选择星期一:

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

选择除星期一和星期五以外的日期:

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

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

解决方案

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

这篇关于如何在多索引数据框上过滤日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！