如何根据另一个数据框的条件从多索引数据框中选择一个子集 [英] How to select a subset from a Multi-Index Dataframe based on conditions from another DataFrame
本文介绍了如何根据另一个数据框的条件从多索引数据框中选择一个子集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个如下数据框:
dates 0
numbers letters
0 a 2013-01-01 0.261092
2013-01-02 -1.267770
2013-01-03 0.008230
b 2013-01-01 -1.515866
2013-01-02 0.351942
2013-01-03 -0.245463
c 2013-01-01 -0.253103
2013-01-02 -0.385411
2013-01-03 -1.740821
1 a 2013-01-01 -0.108325
2013-01-02 -0.212350
2013-01-03 0.021097
b 2013-01-01 -1.922214
2013-01-02 -1.769003
2013-01-03 -0.594216
c 2013-01-01 -0.419775
2013-01-02 1.511700
2013-01-03 0.994332
2 a 2013-01-01 -0.020299
2013-01-02 -0.749474
2013-01-03 -1.478558
b 2013-01-01 -1.357671
2013-01-02 0.161185
2013-01-03 -0.658246
c 2013-01-01 -0.564796
2013-01-02 -0.333106
2013-01-03 -2.814611
现在,我得到了一个像这样的列表:
Now I was given a list like:
numbers letters
0 0 b
1 1 c
我需要选择索引满足列表要求的数据.答案是这样的:
I need to select data whose indexs satisfy the list. The answer is like:
dates 0
numbers letters
0 b 2013-01-01 -1.515866
2013-01-02 0.351942
2013-01-03 -0.245463
1 c 2013-01-01 -0.419775
2013-01-02 1.511700
2013-01-03 0.994332
如何从MultiIndex的数据框中选择特定数据?
How can I select the specific data from the Dataframe of MultiIndex?
推荐答案
您还可以使用索引交集:
You can also use index intersection:
In [39]: l
Out[39]:
numbers letters
0 0 b
1 1 c
In [40]: df.loc[df.index.intersection(l.set_index(['numbers','letters']).index)]
Out[40]:
dates 0
numbers letters
0 b 2013-01-01 -1.515866
b 2013-01-02 0.351942
b 2013-01-03 -0.245463
1 c 2013-01-01 -0.108325
c 2013-01-02 -0.212350
c 2013-01-03 0.021097
c 2013-01-01 -0.419775
c 2013-01-02 1.511700
c 2013-01-03 0.994332
或 时间:
用于27.000行多索引DF
for 27.000 rows Multi-Index DF
In [156]: df = pd.concat([df.reset_index()] * 10**3, ignore_index=True).set_index(['numbers','letters'])
In [157]: df.shape
Out[157]: (27000, 2)
In [158]: %%timeit
...: q = l.apply(lambda r: "(numbers == {} and letters == '{}')".format(r.numbers, r.letters),
...: axis=1) \
...: .str.cat(sep=' or ')
...: df.query(q)
...:
10 loops, best of 3: 21.3 ms per loop
In [159]: %%timeit
...: df.loc[l.set_index(['numbers','letters']).index]
...:
10 loops, best of 3: 20.2 ms per loop
In [160]: %%timeit
...: df.loc[df.index.intersection(l.set_index(['numbers','letters']).index)]
...:
10 loops, best of 3: 27.2 ms per loop
用于270.000行多索引DF
for 270.000 rows Multi-Index DF
In [163]: %%timeit
...: q = l.apply(lambda r: "(numbers == {} and letters == '{}')".format(r.numbers, r.letters),
...: axis=1) \
...: .str.cat(sep=' or ')
...: df.query(q)
...:
10 loops, best of 3: 117 ms per loop
In [164]: %%timeit
...: df.loc[l.set_index(['numbers','letters']).index]
...:
1 loop, best of 3: 142 ms per loop
In [165]: %%timeit
...: df.loc[df.index.intersection(l.set_index(['numbers','letters']).index)]
...:
10 loops, best of 3: 185 ms per loop
结论:对于更大的DF,内部使用numexpr
模块的df.query()
方法似乎更快
Conclusion: df.query()
method which uses numexpr
module internaly seems to be faster for bigger DFs
这篇关于如何根据另一个数据框的条件从多索引数据框中选择一个子集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文