Breaking News: Grepper is joining You.com. Read the official announcement!

Select rows in pandas MultiIndex DataFrame

Worried Wren answered on December 19, 2021 Popularity 9/10 Helpfulness 5/10

answer Select rows in pandas MultiIndex DataFrame

related dataframe multiindex query

Select rows in pandas MultiIndex DataFrame

Comment

col
one two     
a   t      0
    u      1
    v      2
    w      3
df.loc[['a']] # TypeError: Expected tuple, got str
df.loc[('a', slice(None)), :] # No error
# Or use crosssection
df.xs('a', level=0, axis=0, drop_level=False)
# df.xs('a', drop_level=False)
#Here, the drop_level=False argument is needed to prevent xs 
#from dropping level "one" in the result (the level we sliced on).
# Yet another option here is using query:
df.query("one == 'a'")
#If the index did not have a name, you would need to change your query 
#string to be "ilevel_0 == 'a'".
#Finally, using get_level_values:
df[df.index.get_level_values('one') == 'a']
# If your levels are unnamed, or if you need to select by position (not label),
# df[df.index.get_level_values(0) == 'a']
## How do I slice all rows with value "t" on level "two"?
df.loc[(slice(None), 't'), :]
#OR
idx = pd.IndexSlice
df.loc[idx[:, 't'], :]                                                                           
#OR
df.loc(axis=0)[pd.IndexSlice[:, 't']]
#OR
df.xs('t', axis=0, level=1, drop_level=False)                                                                           
#OR                                                                           
df.query("two == 't'")
# Or, if the first level has no name, 
# df.query("ilevel_1 == 't'")                                                                            
#And finally, with get_level_values, you may do
df[df.index.get_level_values('two') == 't']
# Or, to perform selection by position/integer,
# df[df.index.get_level_values(1) == 't']
##
#How can I select rows corresponding to items "b" and "d" in level "one"?
df.loc[['b', 'd']] #b and d are outer (level0) indexes.
#OR                                                                           
items = ['b', 'd']
df.query("one in @items")
# df.query("one == @items", parser='pandas')
# df.query("one in ['b', 'd']")
# df.query("one == ['b', 'd']", parser='pandas')
#OR
df[df.index.get_level_values("one").isin(['b', 'd'])]
#Level 2 access
df.loc[pd.IndexSlice[:, ['t', 'w']], :] 
#OR
items = ['t', 'w']
df.query("two in @items")
# df.query("two == @items", parser='pandas') 
# df.query("two in ['t', 'w']")
# df.query("two == ['t', 'w']", parser='pandas')
#OR
df[df.index.get_level_values('two').isin(['t', 'w'])]
#
         col
one two     
c   u      9
df.loc[('c', 'u'), :]
#OR
df.loc[pd.IndexSlice[('c', 'u')]] # Give -PerformanceWarning: indexing past lexsort depth may impact performance.
# Solution
df_sort = df.sort_index()
df_sort.loc[('c', 'u')]
#OR
df.xs(('c', 'u'))
#OR
df.query("one == 'c' and two == 'u'")
#OR
m1 = (df.index.get_level_values('one') == 'c')
m2 = (df.index.get_level_values('two') == 'u')
df[m1 & m2]
         col
one two     
c   u      9
a   w      3
df.loc[[('c', 'u'), ('a', 'w')]]
# df.loc[pd.IndexSlice[[('c', 'u'), ('a', 'w')]]]
#OR
df[df.index.droplevel(unused_level).isin([('c', 'u'), ('a', 'w')])]
         col
one two     
a   t      0
    u      1
    v      2
    w      3
b   t      4
    t      8
d   t     12
#How can I retrieve all rows corresponding to "a" in level "one" or "t" in level "two"?
df.query("one == 'a' or two == 't'")
#OR
m1 = (df.index.get_level_values('one') == 'a')
m2 = (df.index.get_level_values('two') == 't')
df[m1 | m2] 
         col
one two     
b   7      4
    9      5
c   7     10
d   6     11
    8     12
    8     13
    6     15
 # How do I get all rows where values in level "two" are greater than 5?
df2.query("two > 5")
#OR
df2[df2.index.get_level_values('two') > 5]
##############################
index = pd.MultiIndex.from_product([['a','b'],
                               ['stock1','stock2','stock3'],
                               ['price','volume','velocity']])
df = pd.DataFrame([1,2,3,4,5,6,7,8,9,
                      10,11,12,13,14,15,16,17,18], 
                       index)
df.xs(('stock1', 'velocity'), level=(1,2))
#OR
(
  df.iloc[
    	   df.index.isin(['stock1'], level=1) & 
           df.index.isin(['velocity'], level=2)
          ]
)
(
df.iloc[
  			df.index.isin(['stock1','stock3'], level=1) & 
            df.index.isin(['velocity'], level=2)
       ]
)

Popularity 9/10 Helpfulness 5/10 Language python

Source: stackoverflow.com

Tags: dataframe multi-index pandas python select

Link to this answer
Share Copy Link

Contributed on May 29 2022

Worried Wren

0 Answers Avg Quality 2/10

Closely Related Answers

dataframe multiindex query

Comment

result_df = df.loc[df.index.get_level_values('A') > 1.7]

Popularity 8/10 Helpfulness 4/10 Language python

Source: stackoverflow.com

Tags: dataframe multi-index multi-inde

Link to this answer
Share Copy Link

Contributed on Dec 19 2021

Benja

0 Answers Avg Quality 2/10

Select rows in pandas MultiIndex DataFrame

Contents

More Related Answers

Select rows in pandas MultiIndex DataFrame

Closely Related Answers

dataframe multiindex query

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.