Search
 
SCRIPT & CODE EXAMPLE
 

PYTHON

Select rows in pandas MultiIndex DataFrame

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)
       ]
)















                                                                           
Comment

dataframe multiindex query

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

PREVIOUS NEXT
Code Example
Python :: python time function in for loop 
Python :: plot second y axis matplotlib 
Python :: pandas drop row from a list of value 
Python :: how to find last index of list in python 
Python :: How do you print a integer in python 
Python :: python remove everything after character 
Python :: python currency format locale 
Python :: multiple pdf in a directory to csv python 
Python :: python print show special characters 
Python :: how to use static files in django 
Python :: simple way of finding file extension python programming 
Python :: python create file if doesnt exist 
Python :: label encoding in python 
Python :: how to sort list of dictionaries in python 
Python :: graph 3d python 
Python :: python input timeout 
Python :: concat columns pandas dataframe 
Python :: how to take input for list in python 
Python :: evaluate how much a python program memory 
Python :: insert data in table python 
Python :: pandas index to datetime 
Python :: sort columns dataframe 
Python :: pandas data profiling 
Python :: apply lambda function to multiple columns pandas 
Python :: flask blueprint static folder 
Python :: how to load mnist dataset in python 
Python :: WebDriverWait 
Python :: python if in range 
Python :: requests.Session() proxies 
Python :: matplotlib savefig size 
ADD CONTENT
Topic
Content
Source link
Name
4+6 =