如何在 pandas 中按子级索引过滤 [英] How to filter by sub-level index in Pandas
问题描述
我有一个具有多级索引(STK_ID,RPT_Date)的'df'
I have a 'df' which have a multilevel index (STK_ID,RPT_Date)
sales cogs net_pft
STK_ID RPT_Date
000876 20060331 NaN NaN NaN
20060630 857483000 729541000 67157200
20060930 1063590000 925140000 50807000
20061231 853960000 737660000 51574000
20070331 -2695245000 -2305078000 -167642500
20070630 1146245000 1050808000 113468500
20070930 1327970000 1204800000 84337000
20071231 1439140000 1331870000 53398000
20080331 -3135240000 -2798090000 -248054300
20080630 1932470000 1777010000 133756300
20080930 1873240000 1733660000 92099000
002254 20061231 -16169620000 -15332705000 -508333200
20070331 -763844000 -703460000 -1538000
20070630 501221000 289167000 118012200
20070930 460483000 274026000 95967000
如何编写命令以过滤"RPT_Date"包含"0630"(第二季度报告)的行?结果应该是:
How to write a command to filter the rows whose 'RPT_Date' contains '0630' (which is the Q2 report) ? the result should be :
sales cogs net_pft
STK_ID RPT_Date
000876 20060630 857483000 729541000 67157200
20070630 1146245000 1050808000 113468500
20080630 1932470000 1777010000 133756300
002254 20070630 501221000 289167000 118012200
我正在尝试使用df[df['RPT_Date'].str.contains('0630')]
,但是Pandas拒绝工作,因为'RPT_Date'
不是列,而是sub_level索引.
I am trying to use df[df['RPT_Date'].str.contains('0630')]
, but Pandas refuses to work as 'RPT_Date'
is not a column but a sub_level index.
感谢您的提示...
推荐答案
要在列上使用"str.*"方法,可以重置索引,使用列"str.*"方法调用过滤行,并重新创建索引.
To use the "str.*" methods on a column, you could reset the index, filter rows with a column "str.*" method call, and re-create the index.
In [72]: x = df.reset_index(); x[x.RPT_Date.str.endswith("0630")].set_index(['STK_ID', 'RPT_Date'])
Out[72]:
sales cogs net_pft
STK_ID RPT_Date
000876 20060630 857483000 729541000 67157200
20070630 1146245000 1050808000 113468500
20080630 1932470000 1777010000 133756300
002254 20070630 501221000 289167000 118012200
但是,这种方法并不是特别快.
However, this approach is not particularly fast.
In [73]: timeit x = df.reset_index(); x[x.RPT_Date.str.endswith("0630")].set_index(['STK_ID', 'RPT_Date'])
1000 loops, best of 3: 1.78 ms per loop
另一种方法基于MultiIndex对象的行为类似于 元组列表.
Another approach builds on the fact that a MultiIndex object behaves much like a list of tuples.
In [75]: df.index
Out[75]:
MultiIndex
[('000876', '20060331') ('000876', '20060630') ('000876', '20060930')
('000876', '20061231') ('000876', '20070331') ('000876', '20070630')
('000876', '20070930') ('000876', '20071231') ('000876', '20080331')
('000876', '20080630') ('000876', '20080930') ('002254', '20061231')
('002254', '20070331') ('002254', '20070630') ('002254', '20070930')]
在此基础上,您可以使用df.index.map()从MultiIndex创建一个布尔数组,然后使用结果过滤框架.
Building on that, you can create a boolean array from a MultiIndex with df.index.map() and use the result to filter the frame.
In [76]: df[df.index.map(lambda x: x[1].endswith("0630"))]
Out[76]:
sales cogs net_pft
STK_ID RPT_Date
000876 20060630 857483000 729541000 67157200
20070630 1146245000 1050808000 113468500
20080630 1932470000 1777010000 133756300
002254 20070630 501221000 289167000 118012200
这也快了很多.
In [77]: timeit df[df.index.map(lambda x: x[1].endswith("0630"))]
1000 loops, best of 3: 240 us per loop
这篇关于如何在 pandas 中按子级索引过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!