CODEWITHSUNDEEP
pythonpandasindexing
Dominique Paul
Dominique Paul

Reputation: 1759

Filling "blank" value in pandas multiindex to access lower level index

I am using a dataframe with a two-level index. The first level is for item names, the second is for item colours. In my second level index I always have an index name called "total" for the sum of all colours.

I would like to query the data frame in a manner that python returns the "total" values for all shoes. I could reorder the index, but I am looking cleaner solution. How could I do this?

something I was thinking that might help would be something link a "blank" term for the index. Does something like this maybe already exist?

e.g. 

df.loc[*blank*,"total",:]

Upvotes: 1

Views: 590

Answers (1)

jezrael
jezrael

Reputation: 863256

I think you need : with IndexSlice for select all values:

arrays = [np.array(['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux', 'bar', 'foo']),
          np.array(['one','two','one','two','total','two','total', 'two','total','four'])]
df = pd.DataFrame(np.random.randn(10), index=arrays)
print (df)
                  0
bar one   -0.152506
    two   -0.492401
baz one   -1.528111
    two   -3.284650
foo total -0.346641
    two    0.630630
qux total -0.232299
    two    0.361744
bar total -2.170350
foo four  -2.332996

idx = pd.IndexSlice
df1 = df.loc[idx[:,"total"],:]
print (df1)
                  0
foo total -0.346641
qux total -0.232299
bar total -2.170350

Or use DataFrame.xs:

df1 = df.xs('total', level=1)
print (df1)
            0
foo -0.099117
qux  0.381831
bar  1.638784

df1 = df.xs('total', level=1, drop_level=False)
print (df1)
                  0
foo total -0.570454
qux total  0.015090
bar total -1.084960

Upvotes: 2

Related Questions