Pandas - properly indexing by position on integer axis and label on the other (to avoid chained assignment)

Question

I am aware that pandas provides:

1. .ix - mixed label and position indexing (primarily label), if index is integer - it will be interpreted as label
2. .loc - explicit indexing by label
3. .iloc - explicit indexing by position

That's cool.

What would be the proper way to index the columns by label (ala .loc) and rows by position (ala .iloc) in one shot to avoid chained assignment? Preferably, it would avoid reset_index().

To provide an example, assuming the following DataFrame df:

   col1  col2  col3
3     1     4     2
2     2     3     3
4     3     2     1
1     4     1     4

Does pandas have something like some_indexer, which behaves as follows?

In[2]: df.some_indexer[2,'col2':'col3']
Out[2]:
col2    2
col3    1
Name: 4, dtype: object

Thanks!

Answer 1

I know this is an old question but this can indeed be done without chained indexing or using reset_index(). You just need to use df.index[2] inside the .loc indexer.

df.loc[df.index[2],'col2':'col3']

This will also work correctly with assignments.

Answer 2

Normally we would do df.ix[2, 'col2':'col3'] but because your index is ambiguous you get the 2nd rather than the 3rd row as 2 appears as a value in the index at position 1 so the label selection succeeds as ix tries label selection first and then position selection.

From the docs:

.ix supports mixed integer and label based access. It is primarily label based, but will fall back to integer positional access unless the corresponding axis is of integer type. .ix is the most general and will support any of the inputs in .loc and .iloc. .ix also supports floating point label schemes. .ix is exceptionally useful when dealing with mixed positional and label based hierachical indexes.

In [246]:

df.ix[2,'col2':'col3']
Out[246]:
col2    3
col3    3
Name: 2, dtype: int64

The following would work but this is chained calling and assignments are likely to operate on a copy and raise a warning:

In [247]:

df.iloc[2][['col2','col3']]
Out[247]:
col2    2
col3    1
Name: 4, dtype: int64

The simplest thing would be to reset the index and then you can call ix, we have to drop the index again as this is inserted as a column:

In [250]:

df = df.reset_index().drop('index',axis=1)
df
Out[250]:
   col1  col2  col3
0     1     4     2
1     2     3     3
2     3     2     1
3     4     1     4
In [251]:

df.ix[2,'col2':'col3']
Out[251]:
col2    2
col3    1
Name: 2, dtype: int64