Seeing or filtering using indexed columns in pandas?
Question: Using Python's pandas library, I imported a csv and set multiple columns as my
index. Unexpectedly, the indexed columns are no longer present when I display
the dataframe and I can't use the index columns as filter option. Google tells
me that when I set my index, I should set 'drop' to False. This has me
wondering if I am mistaken in thinking of pandas indexes as being similar to
SQL indexes.

Say my data looks like this (simplified dummy example for stock market
prices):

    
    
    date, exchange, symbol, low, high, open, close, last
    

`date`, `exchange`, and `symbol` are sufficient to uniquely identify a row.

First, what is the point of creating an index? Does it speed up lookups or
does it add some semantic information useful for things like
stack/unstack/pivot/groupby? Does it reduce memory usage?

Why are date, exchange, and symbol moved out of dataframe (and moved to index
series?)? Doesn't it make sense to leave them in the dataframe where I can
filter on them as df[df.symbol=='MSFT']? I guess 'drop' solves this, but the
fact that this isn't default means I'm misunderstanding something here.


Answer: Indices are used to select and align rows of a data frame. The way I think of
them is as labels for the dataframe rows, with the rows containing the data
values. They enable you to use a dataframe to store higher dimensional data in
a convenient way. I found them a little tricky starting out, but when you are
aligning data sets they really shine.

You need the xs method to access the inner levels of a multiindex. To do what
you want try the following.

    
    
    df.xs(('BATS', 'MSFT'), level=(1, 2))
    

Alternatively you can refrain from setting an index and use boolean indexing
to select multiple criteria.

    
    
    bats_mask = df.exchange == 'BATS'
    msft_mask = df.symbol == 'MSFT'
    df[bats_mask & msft_mask]
    

