pydata/pandas — pull request #6301 by jreback (2014-02-08 02:00:53+00:00)
Title: ENH: per axis and per level indexing (orig GH6134)

This is a reprise of #6134, with tests, and multi-axis support; it is dependent on #6299

- [ ] docs
- [ ] v0.14.0 example
- [ ] release notes

Essentially this allows you to select portions of a multi-index on either (or both axes)
that is itself multilevel. You can use the usual slices / lists / scalars to access. The selection does not have to extend to the fullest extent of levels, and missing levels can be specified with ``slice(None)``.

Here's an example

```
In [6]: index = MultiIndex.from_tuples([('A',1),('A',2),('A',3),('B',1)],
...: names=['one','two'])

In [7]: columns = MultiIndex.from_tuples([('a','foo'),('a','bar'),('b','hello'),('b','world')],
...: names=['lvl0', 'lvl1'])

In [8]: df = DataFrame(np.arange(16).reshape(4, 4), index=index, columns=columns)

In [9]: df = df.sortlevel(axis=0).sortlevel(axis=1)

In [10]: df
Out[10]:
lvl0 a b
lvl1 bar foo hello world
one two
A 1 1 0 2 3
2 5 4 6 7
3 9 8 10 11
B 1 13 12 14 15

[4 rows x 4 columns]
```

select out the 2nd level of the index where it is 1
Note that this is effictivly ``df.xs(1,level=1)``

```
In [11]: df.loc[(slice(None),[1]),:]
Out[11]:
lvl0 a b
lvl1 bar foo hello world
one two
A 1 1 0 2 3
B 1 13 12 14 15

[2 rows x 4 columns]
```

select same as above, plus only columns that have their 2nd level as 'world'
Note that here the scalar ``1`` is used rather than the list ``[1]`` and you get the same result. This type of selection will not be able to 'drop' the dimensions of the resulting object; it will always be a DataFrame.

```
In [12]: df.loc[(slice(None),1),(slice(None),['world'])]
Out[12]:
lvl0 b
lvl1 world
one two
A 1 3
B 1 15

[2 rows x 1 columns]
```

this looks very much like the first example, but does not include the column indexer (e.g. the ``:`` after the comma), and will raise because the ``1`` is not found. Note that you can contrive an example where this would actually work, so its an ambiguous specification. E.g is it a 1 on the columns, or is it the spec for the index multi-index.

Best to simply fully specify all of the axes when in doubt

```
In [13]: df.loc[(slice(None),[1])]

KeyError: '[[1]] are not in ALL in the [columns]'
```

@y-p original example showing the interesting selections you can do

Note that this is equivalent to this expression!
```
df.loc[[ tuple([a,b,c,d]) for a,b,c,d in df.index.values if (a == 'A1' or a == 'A2') and (c == 'C1' or c == 'C3')]]
```

```
In [1]: def mklbl(prefix,n):
return ["%s%s" % (prefix,i) for i in range(n)]

In [3]: ix = MultiIndex.from_product([mklbl('A',5),mklbl('B',7),mklbl('C',4),mklbl('D',2)])

In [4]: df = DataFrame(np.arange(len(ix.get_values())),index=ix)

In [5]: df.loc[(slice('A1','A3'),slice(None), ['C1','C3']),:]
Out[5]:
0
A1 B0 C1 D0 58
D1 59
C3 D0 62
D1 63
B1 C1 D0 66
D1 67
C3 D0 70
D1 71
B2 C1 D0 74
D1 75
C3 D0 78
D1 79
B3 C1 D0 82
D1 83
C3 D0 86
D1 87
B4 C1 D0 90
D1 91
C3 D0 94
D1 95
B5 C1 D0 98
D1 99
C3 D0 102
D1 103
B6 C1 D0 106
D1 107
C3 D0 110
D1 111
A2 B0 C1 D0 114
D1 115
C3 D0 118
D1 119
B1 C1 D0 122
D1 123
C3 D0 126
D1 127
B2 C1 D0 130
D1 131
C3 D0 134
D1 135
B3 C1 D0 138
D1 139
C3 D0 142
D1 143
B4 C1 D0 146
D1 147
C3 D0 150
D1 151
B5 C1 D0 154
B5 C1 D0 154
D1 155
C3 D0 158
D1 159
B6 C1 D0 162
D1 163
C3 D0 166
D1 167

[56 rows x 1 columns]
```