# Indexing and Selecting Data

The axis labeling information in pandas objects serves many purposes:

> - Identifies data (i.e. provides metadata) using known indicators, important for analysis, visualization, and interactive console display
> - Enables automatic and explicit data alignment
> - Allows intuitive getting and setting of subsets of the data set

In this section, we will focus on the final point: namely, how to slice, dice, and generally get and set subsets of pandas objects. The primary focus will be on Series and DataFrame as they have received more development attention in this area. Expect more work to be invested in higher-dimensional data structures (including `Panel`) in the future, especially in label-based advanced indexing.

Note

The Python and NumPy indexing operators `[]` and attribute operator `.` provide quick and easy access to pandas data structures across a wide range of use cases. This makes interactive work intuitive, as there’s little new to learn if you already know how to deal with Python dictionaries and NumPy arrays. However, since the type of the data to be accessed isn’t known in advance, directly using standard operators has some optimization limits. For production code, we recommended that you take advantage of the optimized pandas data access methods exposed in this chapter.

Warning

Whether a copy or a reference is returned for a setting operation, may depend on the context. This is sometimes called `chained assignment` and should be avoided. See Returning a View versus Copy

Warning

In 0.15.0 `Index` has internally been refactored to no longer subclass `ndarray` but instead subclass `PandasObject`, similarly to the rest of the pandas objects. This should be a transparent change with only very limited API implications (See the Internal Refactoring)

See the MultiIndex / Advanced Indexing for `MultiIndex` and more advanced indexing documentation.

See the cookbook for some advanced strategies

## Different Choices for Indexing

New in version 0.11.0.

Object selection has had a number of user-requested additions in order to support more explicit location based indexing. pandas now supports three types of multi-axis indexing.

- `.loc` is primarily label based, but may also be used with a boolean array. `.loc` will raise `KeyError` when the items are not found. Allowed inputs are:

  - A single label, e.g. `5` or `'a'`, (note that `5` is interpreted as a label of the index. This use is not an integer position along the index)
  - A list or array of labels `['a', 'b', 'c']`
  - A slice object with labels `'a':'f'`, (note that contrary to usual python slices, both the start and the stop are included!)
  - A boolean array

  See more at Selection by Label

- `.iloc` is primarily integer position based (from `0` to `length-1` of the axis), but may also be used with a boolean array. `.iloc` will raise `IndexError` if a requested indexer is out-of-bounds, except slice indexers which allow out-of-bounds indexing. (this conforms with python/numpy slice semantics). Allowed inputs are:

  - An integer e.g. `5`
  - A list or array of integers `[4, 3, 0]`
  - A slice object with ints `1:7`
  - A boolean array

  See more at Selection by Position

- `.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 hierarchical indexes.

  However, when an axis is integer based, ONLY label based access and not positional access is supported. Thus, in such cases, it’s usually better to be explicit and use `.iloc` or `.loc`.

  See more at Advanced Indexing and Advanced Hierarchical.

Getting values from an object with multi-axes selection uses the following notation (using `.loc` as an example, but applies to `.iloc` and `.ix` as well). Any of the axes accessors may be the null slice `:`. Axes left out of the specification are assumed to be `:`. (e.g. `p.loc['a']` is equiv to `p.loc['a', :, :]`)

Object Type | Indexers
--- | ---
Series | `s.loc[indexer]`
DataFrame | `df.loc[row_indexer,column_indexer]`
Panel | `p.loc[item_indexer,major_indexer,minor_indexer]`

## Basics

As mentioned when introducing the data structures in the last section, the primary function of indexing with `[]` (a.k.a. `__getitem__` for those familiar with implementing class behavior in Python) is selecting out lower-dimensional slices. Thus,

Object Type | Selection | Return Value Type
--- | --- | ---
Series | `series[label]` | scalar value
DataFrame | `frame[colname]` | `Series` corresponding to colname
Panel | `panel[itemname]` | `DataFrame` corresponing to the itemname

Here we construct a simple time series data set to use for illustrating the indexing functionality:

    In [1]: dates = pd.date_range('1/1/2000', periods=8)

    In [2]: df = pd.DataFrame(np.random.randn(8, 4), index=dates, columns=['A', 'B', 'C', 'D'])

    In [3]: df
    Out[3]:
                       A         B         C         D
    2000-01-01  0.469112 -0.282863 -1.509059 -1.135632
    2000-01-02  1.212112 -0.173215  0.119209 -1.044236
    2000-01-03 -0.861849 -2.104569 -0.494929  1.071804
    2000-01-04  0.721555 -0.706771 -1.039575  0.271860
    2000-01-05 -0.424972  0.567020  0.276232 -1.087401
    2000-01-06 -0.673690  0.113648 -1.478427  0.524988
    2000-01-07  0.404705  0.577046 -1.715002 -1.039268
    2000-01-08 -0.370647 -1.157892 -1.344312  0.844885

    In [4]: panel = pd.Panel({'one' : df, 'two' : df - df.mean()})

    In [5]: panel
    Out[5]:
    <class 'pandas.core.panel.Panel'>
    Dimensions: 2 (items) x 8 (major_axis) x 4 (minor_axis)
    Items axis: one to two
    Major_axis axis: 2000-01-01 00:00:00 to 2000-01-08 00:00:00
    Minor_axis axis: A to D

Note

None of the indexing functionality is time series specific unless specifically stated.

Thus, as per above, we have the most basic indexing using `[]`:

    In [6]: s = df['A']

    In [7]: s[dates[5]]
    Out[7]: -0.67368970808837059

    In [8]: panel['two']
    Out[8]:
                       A         B         C         D
    2000-01-01  0.409571  0.113086 -0.610826 -0.936507
    2000-01-02  1.152571  0.222735  1.017442 -0.845111
    2000-01-03 -0.921390 -1.708620  0.403304  1.270929
    2000-01-04  0.662014 -0.310822 -0.141342  0.470985
    2000-01-05 -0.484513  0.962970  1.174465 -0.888276
    2000-01-06 -0.733231  0.509598 -0.580194  0.724113
    2000-01-07  0.345164  0.972995 -0.816769 -0.840143
    2000-01-08 -0.430188 -0.761943 -0.446079  1.044010

You can pass a list of columns to `[]` to select columns in that order. If a column is not contained in the DataFrame, an exception will be raised. Multiple columns can also be set in this manner:

    In [9]: df
    Out[9]:
                       A         B         C         D
    2000-01-01  0.469112 -0.282863 -1.509059 -1.135632
    2000-01-02  1.212112 -0.173215  0.119209 -1.044236
    2000-01-03 -0.861849 -2.104569 -0.494929  1.071804
    2000-01-04  0.721555 -0.706771 -1.039575  0.271860
    2000-01-05 -0.424972  0.567020  0.276232 -1.087401
    2000-01-06 -0.673690  0.113648 -1.478427  0.524988
    2000-01-07  0.404705  0.577046 -1.715002 -1.039268
    2000-01-08 -0.370647 -1.157892 -1.344312  0.844885

    In [10]: df[['B', 'A']] = df[['A', 'B']]

    In [11]: df
    Out[11]:
                       A         B         C         D
    2000-01-01 -0.282863  0.469112 -1.509059 -1.135632
    2000-01-02 -0.173215  1.212112  0.119209 -1.044236
    2000-01-03 -2.104569 -0.861849 -0.494929  1.071804
    2000-01-04 -0.706771  0.721555 -1.039575  0.271860
    2000-01-05  0.567020 -0.424972  0.276232 -1.087401
    2000-01-06  0.113648 -0.673690 -1.478427  0.524988
    2000-01-07  0.577046  0.404705 -1.715002 -1.039268
    2000-01-08 -1.157892 -0.370647 -1.344312  0.844885

You may find this useful for applying a transform (in-place) to a subset of the columns.

## Attribute Access

You may access an index on a `Series`, column on a `DataFrame`, and a item on a `Panel` directly as an attribute:

    In [12]: sa = pd.Series([1,2,3],index=list('abc'))

    In [13]: dfa = df.copy()

    In [14]: sa.b
    Out[14]: 2

    In [15]: dfa.A
    Out[15]:
    2000-01-01   -0.282863
    2000-01-02   -0.173215
    2000-01-03   -2.104569
    2000-01-04   -0.706771
    2000-01-05    0.567020
    2000-01-06    0.113648
    2000-01-07    0.577046
    2000-01-08   -1.157892
    Freq: D, Name: A, dtype: float64

    In [16]: panel.one
    Out[16]:
                       A         B         C         D
    2000-01-01  0.469112 -0.282863 -1.509059 -1.135632
    2000-01-02  1.212112 -0.173215  0.119209 -1.044236
    2000-01-03 -0.861849 -2.104569 -0.494929  1.071804
    2000-01-04  0.721555 -0.706771 -1.039575  0.271860
    2000-01-05 -0.424972  0.567020  0.276232 -1.087401
    2000-01-06 -0.673690  0.113648 -1.478427  0.524988
    2000-01-07  0.404705  0.577046 -1.715002 -1.039268
    2000-01-08 -0.370647 -1.157892 -1.344312  0.844885

You can use attribute access to modify an existing element of a Series or column of a DataFrame, but be careful; if you try to use attribute access to create a new column, it fails silently, creating a new attribute rather than a new column.

    In [17]: sa.a = 5

    In [18]: sa
    Out[18]:
    a    5
    b    2
    c    3
    dtype: int64

    In [19]: dfa.A = list(range(len(dfa.index)))  # ok if A already exists

    In [20]: dfa
    Out[20]:
                A         B         C         D
    2000-01-01  0  0.469112 -1.509059 -1.135632
    2000-01-02  1  1.212112  0.119209 -1.044236
    2000-01-03  2 -0.861849 -0.494929  1.071804
    2000-01-04  3  0.721555 -1.039575  0.271860
    2000-01-05  4 -0.424972  0.276232 -1.087401
    2000-01-06  5 -0.673690 -1.478427  0.524988
    2000-01-07  6  0.404705 -1.715002 -1.039268
    2000-01-08  7 -0.370647 -1.344312  0.844885

    In [21]: dfa['A'] = list(range(len(dfa.index)))  # use this form to create a new column

    In [22]: dfa
    Out[22]:
                A         B         C         D
    2000-01-01  0  0.469112 -1.509059 -1.135632
    2000-01-02  1  1.212112  0.119209 -1.044236
    2000-01-03  2 -0.861849 -0.494929  1.071804
    2000-01-04  3  0.721555 -1.039575  0.271860
    2000-01-05  4 -0.424972  0.276232 -1.087401
    2000-01-06  5 -0.673690 -1.478427  0.524988
    2000-01-07  6  0.404705 -1.715002 -1.039268
    2000-01-08  7 -0.370647 -1.344312  0.844885

Warning

- You can use this access only if the index element is a valid python identifier, e.g. `s.1` is not allowed. See here for an explanation of valid identifiers.
- The attribute will not be available if it conflicts with an existing method name, e.g. `s.min` is not allowed.
- Similarly, the attribute will not be available if it conflicts with any of the following list: `index`, `major_axis`, `minor_axis`, `items`, `labels`.
- In any of these cases, standard indexing will still work, e.g. `s['1']`, `s['min']`, and `s['index']` will access the corresponding element or column.
- The `Series/Panel` accesses are available starting in 0.13.0.

If you are using the IPython environment, you may also use tab-completion to see these accessible attributes.

You can also assign a `dict` to a row of a `DataFrame`:

    In [23]: x = pd.DataFrame({'x': [1, 2, 3], 'y': [3, 4, 5]})

    In [24]: x.iloc[1] = dict(x=9, y=99)

    In [25]: x
    Out[25]:
       x   y
    0  1   3
    1  9  99
    2  3   5

## Slicing ranges

The most robust and consistent way of slicing ranges along arbitrary axes is described in the Selection by Position section detailing the `.iloc` method. For now, we explain the semantics of slicing using the `[]` operator.

With Series, the syntax works exactly as with an ndarray, returning a slice of the values and the corresponding labels:

    In [26]: s[:5]
    Out[26]:
    2000-01-01   -0.282863
    2000-01-02   -0.173215
    2000-01-03   -2.104569
    2000-01-04   -0.706771
    2000-01-05    0.567020
    Freq: D, Name: A, dtype: float64

    In [27]: s[::2]
    Out[27]:
    2000-01-01   -0.282863
    2000-01-03   -2.104569
    2000-01-05    0.567020
    2000-01-07    0.577046
    Freq: 2D, Name: A, dtype: float64

    In [28]: s[::-1]
    Out[28]:
    2000-01-08   -1.157892
    2000-01-07    0.577046
    2000-01-06    0.113648
    2000-01-05    0.567020
    2000-01-04   -0.706771
    2000-01-03   -2.104569
    2000-01-02   -0.173215
    2000-01-01   -0.282863
    Freq: -1D, Name: A, dtype: float64

Note that setting works as well:

    In [29]: s2 = s.copy()

    In [30]: s2[:5] = 0

    In [31]: s2
    Out[31]:
    2000-01-01    0.000000
    2000-01-02    0.000000
    2000-01-03    0.000000
    2000-01-04    0.000000
    2000-01-05    0.000000
    2000-01-06    0.113648
    2000-01-07    0.577046
    2000-01-08   -1.157892
    Freq: D, Name: A, dtype: float64

With DataFrame, slicing inside of `[]` slices the rows. This is provided largely as a convenience since it is such a common operation.

    In [32]: df[:3]
    Out[32]:
                       A         B         C         D
    2000-01-01 -0.282863  0.469112 -1.509059 -1.135632
    2000-01-02 -0.173215  1.212112  0.119209 -1.044236
    2000-01-03 -2.104569 -0.861849 -0.494929  1.071804

    In [33]: df[::-1]
    Out[33]:
                       A         B         C         D
    2000-01-08 -1.157892 -0.370647 -1.344312  0.844885
    2000-01-07  0.577046  0.404705 -1.715002 -1.039268
    2000-01-06  0.113648 -0.673690 -1.478427  0.524988
    2000-01-05  0.567020 -0.424972  0.276232 -1.087401
    2000-01-04 -0.706771  0.721555 -1.039575  0.271860
    2000-01-03 -2.104569 -0.861849 -0.494929  1.071804
    2000-01-02 -0.173215  1.212112  0.119209 -1.044236
    2000-01-01 -0.282863  0.469112 -1.509059 -1.135632

## Selection By Label

Warning

Whether a copy or a reference is returned for a setting operation, may depend on the context. This is sometimes called `chained assignment` and should be avoided. See Returning a View versus Copy

Warning

> `.loc` is strict when you present slicers that are not compatible (or convertible) with the index type. For example using integers in a `DatetimeIndex`. These will raise a `TypeError`.

    In [34]: dfl = pd.DataFrame(np.random.randn(5,4), columns=list('ABCD'), index=pd.date_range('20130101',periods=5))

    In [35]: dfl
    Out[35]:
                       A         B         C         D
    2013-01-01  1.075770 -0.109050  1.643563 -1.469388
    2013-01-02  0.357021 -0.674600 -1.776904 -0.968914
    2013-01-03 -1.294524  0.413738  0.276662 -0.472035
    2013-01-04 -0.013960 -0.362543 -0.006154 -0.923061
    2013-01-05  0.895717  0.805244 -1.206412  2.565646

    In [4]: dfl.loc[2:3]
    TypeError: cannot do slice indexing on <class 'pandas.tseries.index.DatetimeIndex'> with these indexers [2] of <type 'int'>

String likes in slicing can be convertible to the type of the index and lead to natural slicing.

    In [36]: dfl.loc['20130102':'20130104']
    Out[36]:
                       A         B         C         D
    2013-01-02  0.357021 -0.674600 -1.776904 -0.968914
    2013-01-03 -1.294524  0.413738  0.276662 -0.472035
    2013-01-04 -0.013960 -0.362543 -0.006154 -0.923061

pandas provides a suite of methods in order to have purely label based indexing. This is a strict inclusion based protocol. At least 1 of the labels for which you ask, must be in the index or a `KeyError` will be raised! When slicing, the start bound is included, AND the stop bound is included. Integers are valid labels, but they refer to the label and not the position.

The `.loc` attribute is the primary access method. The following are valid inputs:

- A single label, e.g. `5` or `'a'`, (note that `5` is interpreted as a label of the index. This use is not an integer position along the index)
- A list or array of labels `['a', 'b', 'c']`
- A slice object with labels `'a':'f'` (note that contrary to usual python slices, both the start and the stop are included!)
- A boolean array

    In [37]: s1 = pd.Series(np.random.randn(6),index=list('abcdef'))

    In [38]: s1
    Out[38]:
    a    1.431256
    b    1.340309
    c   -1.170299
    d   -0.226169
    e    0.410835
    f    0.813850
    dtype: float64

    In [39]: s1.loc['c':]
    Out[39]:
    c   -1.170299
    d   -0.226169
    e    0.410835
    f    0.813850
    dtype: float64

    In [40]: s1.loc['b']
    Out[40]: 1.3403088497993827

Note that setting works as well:

    In [41]: s1.loc['c':] = 0

    In [42]: s1
    Out[42]:
    a    1.431256
    b    1.340309
    c    0.000000
    d    0.000000
    e    0.000000
    f    0.000000
    dtype: float64

With a DataFrame

    In [43]: df1 = pd.DataFrame(np.random.randn(6,4),
       ....:                    index=list('abcdef'),
       ....:                    columns=list('ABCD'))
       ....:

    In [44]: df1
    Out[44]:
              A         B         C         D
    a  0.132003 -0.827317 -0.076467 -1.187678
    b  1.130127 -1.436737 -1.413681  1.607920
    c  1.024180  0.569605  0.875906 -2.211372
    d  0.974466 -2.006747 -0.410001 -0.078638
    e  0.545952 -1.219217 -1.226825  0.769804
    f -1.281247 -0.727707 -0.121306 -0.097883

    In [45]: df1.loc[['a','b','d'],:]
    Out[45]:
              A         B         C         D
    a  0.132003 -0.827317 -0.076467 -1.187678
    b  1.130127 -1.436737 -1.413681  1.607920
    d  0.974466 -2.006747 -0.410001 -0.078638

Accessing via label slices

    In [46]: df1.loc['d':,'A':'C']
    Out[46]:
              A         B         C
    d  0.974466 -2.006747 -0.410001
    e  0.545952 -1.219217 -1.226825
    f -1.281247 -0.727707 -0.121306

For getting a cross section using a label (equiv to `df.xs('a')`)

    In [47]: df1.loc['a']
    Out[47]:
    A    0.132003
    B   -0.827317
    C   -0.076467
    D   -1.187678
    Name: a, dtype: float64

For getting values with a boolean array

    In [48]: df1.loc['a']>0
    Out[48]:
    A     True
    B    False
    C    False
    D    False
    Name: a, dtype: bool

    In [49]: df1.loc[:,df1.loc['a']>0]
    Out[49]:
              A
    a  0.132003
    b  1.130127
    c  1.024180
    d  0.974466
    e  0.545952
    f -1.281247

For getting a value explicitly (equiv to deprecated `df.get_value('a','A')`)

    # this is also equivalent to ``df1.at['a','A']``
    In [50]: df1.loc['a','A']
    Out[50]: 0.13200317033032932

## Selection By Position

Warning

Whether a copy or a reference is returned for a setting operation, may depend on the context. This is sometimes called `chained assignment` and should be avoided. See Returning a View versus Copy

pandas provides a suite of methods in order to get purely integer based indexing. The semantics follow closely python and numpy slicing. These are `0-based` indexing. When slicing, the start bounds is included, while the upper bound is excluded. Trying to use a non-integer, even a valid label will raise a `IndexError`.

The `.iloc` attribute is the primary access method. The following are valid inputs:

- An integer e.g. `5`
- A list or array of integers `[4, 3, 0]`
- A slice object with ints `1:7`
- A boolean array

    In [51]: s1 = pd.Series(np.random.randn(5), index=list(range(0,10,2)))

    In [52]: s1
    Out[52]:
    0    0.695775
    2    0.341734
    4    0.959726
    6   -1.110336
    8   -0.619976
    dtype: float64

    In [53]: s1.iloc[:3]
    Out[53]:
    0    0.695775
    2    0.341734
    4    0.959726
    dtype: float64

    In [54]: s1.iloc[3]
    Out[54]: -1.1103361028911669

Note that setting works as well:

    In [55]: s1.iloc[:3] = 0

    In [56]: s1
    Out[56]:
    0    0.000000
    2    0.000000
    4    0.000000
    6   -1.110336
    8   -0.619976
    dtype: float64

With a DataFrame

    In [57]: df1 = pd.DataFrame(np.random.randn(6,4),
       ....:                    index=list(range(0,12,2)),
       ....:                    columns=list(range(0,8,2)))
       ....:

    In [58]: df1
    Out[58]:
               0         2         4         6
    0   0.149748 -0.732339  0.687738  0.176444
    2   0.403310 -0.154951  0.301624 -2.179861
    4  -1.369849 -0.954208  1.462696 -1.743161
    6  -0.826591 -0.345352  1.314232  0.690579
    8   0.995761  2.396780  0.014871  3.357427
    10 -0.317441 -1.236269  0.896171 -0.487602

Select via integer slicing

    In [59]: df1.iloc[:3]
    Out[59]:
              0         2         4         6
    0  0.149748 -0.732339  0.687738  0.176444
    2  0.403310 -0.154951  0.301624 -2.179861
    4 -1.369849 -0.954208  1.462696 -1.743161

    In [60]: df1.iloc[1:5,2:4]
    Out[60]:
              4         6
    2  0.301624 -2.179861
    4  1.462696 -1.743161
    6  1.314232  0.690579
    8  0.014871  3.357427

Select via integer list

    In [61]: df1.iloc[[1,3,5],[1,3]]
    Out[61]:
               2         6
    2  -0.154951 -2.179861
    6  -0.345352  0.690579
    10 -1.236269 -0.487602

    In [62]: df1.iloc[1:3,:]
    Out[62]:
              0         2         4         6
    2  0.403310 -0.154951  0.301624 -2.179861
    4 -1.369849 -0.954208  1.462696 -1.743161

    In [63]: df1.iloc[:,1:3]
    Out[63]:
               2         4
    0  -0.732339  0.687738
    2  -0.154951  0.301624
    4  -0.954208  1.462696
    6  -0.345352  1.314232
    8   2.396780  0.014871
    10 -1.236269  0.896171

    # this is also equivalent to ``df1.iat[1,1]``
    In [64]: df1.iloc[1,1]
    Out[64]: -0.15495077442490321

For getting a cross section using an integer position (equiv to `df.xs(1)`)

    In [65]: df1.iloc[1]
    Out[65]:
    0    0.403310
    2   -0.154951
    4    0.301624
    6   -2.179861
    Name: 2, dtype: float64

Out of range slice indexes are handled gracefully just as in Python/Numpy.

    # these are allowed in python/numpy.
    # Only works in Pandas starting from v0.14.0.
    In [66]: x = list('abcdef')

    In [67]: x
    Out[67]: ['a', 'b', 'c', 'd', 'e', 'f']

    In [68]: x[4:10]
    Out[68]: ['e', 'f']

    In [69]: x[8:10]
    Out[69]: []

    In [70]: s = pd.Series(x)

    In [71]: s
    Out[71]:
    0    a
    1    b
    2    c
    3    d
    4    e
    5    f
    dtype: object

    In [72]: s.iloc[4:10]
    Out[72]:
    4    e
    5    f
    dtype: object

    In [73]: s.iloc[8:10]
    Out[73]: Series([], dtype: object)

Note

Prior to v0.14.0, `iloc` would not accept out of bounds indexers for slices, e.g. a value that exceeds the length of the object being indexed.

Note that this could result in an empty axis (e.g. an empty DataFrame being returned)

    In [74]: dfl = pd.DataFrame(np.random.randn(5,2), columns=list('AB'))

    In [75]: dfl
    Out[75]:
              A         B
    0 -0.082240 -2.182937
    1  0.380396  0.084844
    2  0.432390  1.519970
    3 -0.493662  0.600178
    4  0.274230  0.132885

    In [76]: dfl.iloc[:,2:3]
    Out[76]:
    Empty DataFrame
    Columns: []
    Index: [0, 1, 2, 3, 4]

    In [77]: dfl.iloc[:,1:3]
    Out[77]:
              B
    0 -2.182937
    1  0.084844
    2  1.519970
    3  0.600178
    4  0.132885

    In [78]: dfl.iloc[4:6]
    Out[78]:
             A         B
    4  0.27423  0.132885

A single indexer that is out of bounds will raise an `IndexError`. A list of indexers where any element is out of bounds will raise an `IndexError`

    dfl.iloc[[4,5,6]]
    IndexError: positional indexers are out-of-bounds

    dfl.iloc[:,4]
    IndexError: single positional indexer is out-of-bounds

## Selecting Random Samples

A random selection of rows or columns from a Series, DataFrame, or Panel with the `sample()` method. The method will sample rows by default, and accepts a specific number of rows/columns to return, or a fraction of rows.

    In [79]: s = pd.Series([0,1,2,3,4,5])

    # When no arguments are passed, returns 1 row.
    In [80]: s.sample()
    Out[80]:
    4    4
    dtype: int64

    # One may specify either a number of rows:
    In [81]: s.sample(n=3)
    Out[81]:
    5    5
    0    0
    1    1
    dtype: int64

    # Or a fraction of the rows:
    In [82]: s.sample(frac=0.5)
    Out[82]:
    5    5
    1    1
    3    3
    dtype: int64

By default, `sample` will return each row at most once, but one can also sample with replacement using the `replace` option:

    In [83]: s = pd.Series([0,1,2,3,4,5])

     # Without replacement (default):
    In [84]: s.sample(n=6, replace=False)
    Out[84]:
    4    4
    2    2
    5    5
    0    0
    1    1
    3    3
    dtype: int64

     # With replacement:
    In [85]: s.sample(n=6, replace=True)
    Out[85]:
    2    2
    1    1
    2    2
    1    1
    1    1
    4    4
    dtype: int64

By default, each row has an equal probability of being selected, but if you want rows to have different probabilities, you can pass the `sample` function sampling weights as `weights`. These weights can be a list, a numpy array, or a Series, but they must be of the same length as the object you are sampling. Missing values will be treated as a weight of zero, and inf values are not allowed. If weights do not sum to 1, they will be re-normalized by dividing all weights by the sum of the weights. For example:

    In [86]: s = pd.Series([0,1,2,3,4,5])

    In [87]: example_weights = [0, 0, 0.2, 0.2, 0.2, 0.4]

    In [88]: s.sample(n=3, weights=example_weights)
    Out[88]:
    2    2
    4    4
    5    5
    dtype: int64

    # Weights will be re-normalized automatically
    In [89]: example_weights2 = [0.5, 0, 0, 0, 0, 0]

    In [90]: s.sample(n=1, weights=example_weights2)
    Out[90]:
    0    0
    dtype: int64

When applied to a DataFrame, you can use a column of the DataFrame as sampling weights (provided you are sampling rows and not columns) by simply passing the name of the column as a string.

    In [91]: df2 = pd.DataFrame({'col1':[9,8,7,6], 'weight_column':[0.5, 0.4, 0.1, 0]})

    In [92]: df2.sample(n = 3, weights = 'weight_column')
    Out[92]:
       col1  weight_column
    1     8            0.4
    0     9            0.5
    2     7            0.1

`sample` also allows users to sample columns instead of rows using the `axis` argument.

    In [93]: df3 = pd.DataFrame({'col1':[1,2,3], 'col2':[2,3,4]})

    In [94]: df3.sample(n=1, axis=1)
    Out[94]:
       col2
    0     2
    1     3
    2     4

Finally, one can also set a seed for `sample`‘s random number generator using the `random_state` argument, which will accept either an integer (as a seed) or a numpy RandomState object.

    In [95]: df4 = pd.DataFrame({'col1':[1,2,3], 'col2':[2,3,4]})

    # With a given seed, the sample will always draw the same rows.
    In [96]: df4.sample(n=2, random_state=2)
    Out[96]:
       col1  col2
    2     3     4
    1     2     3

    In [97]: df4.sample(n=2, random_state=2)
    Out[97]:
       col1  col2
    2     3     4
    1     2     3

## Setting With Enlargement

New in version 0.13.

The `.loc/.ix/[]` operations can perform enlargement when setting a non-existant key for that axis.

In the `Series` case this is effectively an appending operation

    In [98]: se = pd.Series([1,2,3])

    In [99]: se
    Out[99]:
    0    1
    1    2
    2    3
    dtype: int64

    In [100]: se[5] = 5.

    In [101]: se
    Out[101]:
    0    1
    1    2
    2    3
    5    5
    dtype: float64

A `DataFrame` can be enlarged on either axis via `.loc`

    In [102]: dfi = pd.DataFrame(np.arange(6).reshape(3,2),
       .....:                 columns=['A','B'])
       .....:

    In [103]: dfi
    Out[103]:
       A  B
    0  0  1
    1  2  3
    2  4  5

    In [104]: dfi.loc[:,'C'] = dfi.loc[:,'A']

    In [105]: dfi
    Out[105]:
       A  B  C
    0  0  1  0
    1  2  3  2
    2  4  5  4

This is like an `append` operation on the `DataFrame`.

    In [106]: dfi.loc[3] = 5

    In [107]: dfi
    Out[107]:
       A  B  C
    0  0  1  0
    1  2  3  2
    2  4  5  4
    3  5  5  5

## Fast scalar value getting and setting

Since indexing with `[]` must handle a lot of cases (single-label access, slicing, boolean indexing, etc.), it has a bit of overhead in order to figure out what you’re asking for. If you only want to access a scalar value, the fastest way is to use the `at` and `iat` methods, which are implemented on all of the data structures.

Similarly to `loc`, `at` provides label based scalar lookups, while, `iat` provides integer based lookups analogously to `iloc`

    In [108]: s.iat[5]
    Out[108]: 5

    In [109]: df.at[dates[5], 'A']
    Out[109]: 0.1136484096888855

    In [110]: df.iat[3, 0]
    Out[110]: -0.70677113363008448

You can also set using these same indexers.

    In [111]: df.at[dates[5], 'E'] = 7

    In [112]: df.iat[3, 0] = 7

`at` may enlarge the object in-place as above if the indexer is missing.

    In [113]: df.at[dates[-1]+1, 0] = 7

    In [114]: df
    Out[114]:
                       A         B         C         D   E   0
    2000-01-01 -0.282863  0.469112 -1.509059 -1.135632 NaN NaN
    2000-01-02 -0.173215  1.212112  0.119209 -1.044236 NaN NaN
    2000-01-03 -2.104569 -0.861849 -0.494929  1.071804 NaN NaN
    2000-01-04  7.000000  0.721555 -1.039575  0.271860 NaN NaN
    2000-01-05  0.567020 -0.424972  0.276232 -1.087401 NaN NaN
    2000-01-06  0.113648 -0.673690 -1.478427  0.524988   7 NaN
    2000-01-07  0.577046  0.404705 -1.715002 -1.039268 NaN NaN
    2000-01-08 -1.157892 -0.370647 -1.344312  0.844885 NaN NaN
    2000-01-09       NaN       NaN       NaN       NaN NaN   7

## Boolean indexing

Another common operation is the use of boolean vectors to filter the data. The operators are: `|` for `or`, `&` for `and`, and `~` for `not`. These must be grouped by using parentheses.

Using a boolean vector to index a Series works exactly as in a numpy ndarray:

    In [115]: s = pd.Series(range(-3, 4))

    In [116]: s
    Out[116]:
    0   -3
    1   -2
    2   -1
    3    0
    4    1
    5    2
    6    3
    dtype: int64

    In [117]: s[s > 0]
    Out[117]:
    4    1
    5    2
    6    3
    dtype: int64

    In [118]: s[(s < -1) | (s > 0.5)]
    Out[118]:
    0   -3
    1   -2
    4    1
    5    2
    6    3
    dtype: int64

    In [119]: s[~(s < 0)]
    Out[119]:
    3    0
    4    1
    5    2
    6    3
    dtype: int64

You may select rows from a DataFrame using a boolean vector the same length as the DataFrame’s index (for example, something derived from one of the columns of the DataFrame):

    In [120]: df[df['A'] > 0]
    Out[120]:
                       A         B         C         D   E   0
    2000-01-04  7.000000  0.721555 -1.039575  0.271860 NaN NaN
    2000-01-05  0.567020 -0.424972  0.276232 -1.087401 NaN NaN
    2000-01-06  0.113648 -0.673690 -1.478427  0.524988   7 NaN
    2000-01-07  0.577046  0.404705 -1.715002 -1.039268 NaN NaN

List comprehensions and `map` method of Series can also be used to produce more complex criteria:

    In [121]: df2 = pd.DataFrame({'a' : ['one', 'one', 'two', 'three', 'two', 'one', 'six'],
       .....:                     'b' : ['x', 'y', 'y', 'x', 'y', 'x', 'x'],
       .....:                     'c' : np.random.randn(7)})
       .....:

    # only want 'two' or 'three'
    In [122]: criterion = df2['a'].map(lambda x: x.startswith('t'))

    In [123]: df2[criterion]
    Out[123]:
           a  b         c
    2    two  y  1.450520
    3  three  x  0.206053
    4    two  y -0.251905

    # equivalent but slower
    In [124]: df2[[x.startswith('t') for x in df2['a']]]
    Out[124]:
           a  b         c
    2    two  y  1.450520
    3  three  x  0.206053
    4    two  y -0.251905

    # Multiple criteria
    In [125]: df2[criterion & (df2['b'] == 'x')]
    Out[125]:
           a  b         c
    3  three  x  0.206053

Note, with the choice methods Selection by Label, Selection by Position, and Advanced Indexing you may select along more than one axis using boolean vectors combined with other indexing expressions.

    In [126]: df2.loc[criterion & (df2['b'] == 'x'),'b':'c']
    Out[126]:
       b         c
    3  x  0.206053

## Indexing with isin

Consider the `isin` method of Series, which returns a boolean vector that is true wherever the Series elements exist in the passed list. This allows you to select rows where one or more columns have values you want:

    In [127]: s = pd.Series(np.arange(5), index=np.arange(5)[::-1], dtype='int64')

    In [128]: s
    Out[128]:
    4    0
    3    1
    2    2
    1    3
    0    4
    dtype: int64

    In [129]: s.isin([2, 4, 6])
    Out[129]:
    4    False
    3    False
    2     True
    1    False
    0     True
    dtype: bool

    In [130]: s[s.isin([2, 4, 6])]
    Out[130]:
    2    2
    0    4
    dtype: int64

The same method is available for `Index` objects and is useful for the cases when you don’t know which of the sought labels are in fact present:

    In [131]: s[s.index.isin([2, 4, 6])]
    Out[131]:
    4    0
    2    2
    dtype: int64

    # compare it to the following
    In [132]: s[[2, 4, 6]]
    Out[132]:
    2     2
    4     0
    6   NaN
    dtype: float64

In addition to that, `MultiIndex` allows selecting a separate level to use in the membership check:

    In [133]: s_mi = pd.Series(np.arange(6),
       .....:                  index=pd.MultiIndex.from_product([[0, 1], ['a', 'b', 'c']]))
       .....:

    In [134]: s_mi
    Out[134]:
    0  a    0
       b    1
       c    2
    1  a    3
       b    4
       c    5
    dtype: int64

    In [135]: s_mi.iloc[s_mi.index.isin([(1, 'a'), (2, 'b'), (0, 'c')])]
    Out[135]:
    0  c    2
    1  a    3
    dtype: int64

    In [136]: s_mi.iloc[s_mi.index.isin(['a', 'c', 'e'], level=1)]
    Out[136]:
    0  a    0
       c    2
    1  a    3
       c    5
    dtype: int64

DataFrame also has an `isin` method. When calling `isin`, pass a set of values as either an array or dict. If values is an array, `isin` returns a DataFrame of booleans that is the same shape as the original DataFrame, with True wherever the element is in the sequence of values.

    In [137]: df = pd.DataFrame({'vals': [1, 2, 3, 4], 'ids': ['a', 'b', 'f', 'n'],
       .....:                    'ids2': ['a', 'n', 'c', 'n']})
       .....:

    In [138]: values = ['a', 'b', 1, 3]

    In [139]: df.isin(values)
    Out[139]:
         ids   ids2   vals
    0   True   True   True
    1   True  False  False
    2  False  False   True
    3  False  False  False

Oftentimes you’ll want to match certain values with certain columns. Just make values a `dict` where the key is the column, and the value is a list of items you want to check for.

    In [140]: values = {'ids': ['a', 'b'], 'vals': [1, 3]}

    In [141]: df.isin(values)
    Out[141]:
         ids   ids2   vals
    0   True  False   True
    1   True  False  False
    2  False  False   True
    3  False  False  False

Combine DataFrame’s `isin` with the `any()` and `all()` methods to quickly select subsets of your data that meet a given criteria. To select a row where each column meets its own criterion:

    In [142]: values = {'ids': ['a', 'b'], 'ids2': ['a', 'c'], 'vals': [1, 3]}

    In [143]: row_mask = df.isin(values).all(1)

    In [144]: df[row_mask]
    Out[144]:
      ids ids2  vals
    0   a    a     1

## The `where()` Method and Masking

Selecting values from a Series with a boolean vector generally returns a subset of the data. To guarantee that selection output has the same shape as the original data, you can use the `where` method in `Series` and `DataFrame`.

To return only the selected rows

    In [145]: s[s > 0]
    Out[145]:
    3    1
    2    2
    1    3
    0    4
    dtype: int64

To return a Series of the same shape as the original

    In [146]: s.where(s > 0)
    Out[146]:
    4   NaN
    3     1
    2     2
    1     3
    0     4
    dtype: float64

Selecting values from a DataFrame with a boolean criterion now also preserves input data shape. `where` is used under the hood as the implementation. Equivalent is `df.where(df < 0)`

    In [147]: df[df < 0]
    Out[147]:
                       A         B         C         D
    2000-01-01       NaN       NaN -0.863838       NaN
    2000-01-02 -1.048089 -0.025747 -0.988387       NaN
    2000-01-03       NaN       NaN       NaN -0.055758
    2000-01-04       NaN -0.489682       NaN -0.034571
    2000-01-05 -2.484478 -0.281461       NaN       NaN
    2000-01-06       NaN -0.977349       NaN -0.064034
    2000-01-07 -1.282782       NaN -1.071357       NaN
    2000-01-08       NaN       NaN       NaN -0.744471

In addition, `where` takes an optional `other` argument for replacement of values where the condition is False, in the returned copy.

    In [148]: df.where(df < 0, -df)
    Out[148]:
                       A         B         C         D
    2000-01-01 -1.266143 -0.299368 -0.863838 -0.408204
    2000-01-02 -1.048089 -0.025747 -0.988387 -0.094055
    2000-01-03 -1.262731 -1.289997 -0.082423 -0.055758
    2000-01-04 -0.536580 -0.489682 -0.369374 -0.034571
    2000-01-05 -2.484478 -0.281461 -0.030711 -0.109121
    2000-01-06 -1.126203 -0.977349 -1.474071 -0.064034
    2000-01-07 -1.282782 -0.781836 -1.071357 -0.441153
    2000-01-08 -2.353925 -0.583787 -0.221471 -0.744471

You may wish to set values based on some boolean criteria. This can be done intuitively like so:

    In [149]: s2 = s.copy()

    In [150]: s2[s2 < 0] = 0

    In [151]: s2
    Out[151]:
    4    0
    3    1
    2    2
    1    3
    0    4
    dtype: int64

    In [152]: df2 = df.copy()

    In [153]: df2[df2 < 0] = 0

    In [154]: df2
    Out[154]:
                       A         B         C         D
    2000-01-01  1.266143  0.299368  0.000000  0.408204
    2000-01-02  0.000000  0.000000  0.000000  0.094055
    2000-01-03  1.262731  1.289997  0.082423  0.000000
    2000-01-04  0.536580  0.000000  0.369374  0.000000
    2000-01-05  0.000000  0.000000  0.030711  0.109121
    2000-01-06  1.126203  0.000000  1.474071  0.000000
    2000-01-07  0.000000  0.781836  0.000000  0.441153
    2000-01-08  2.353925  0.583787  0.221471  0.000000

By default, `where` returns a modified copy of the data. There is an optional parameter `inplace` so that the original data can be modified without creating a copy:

    In [155]: df_orig = df.copy()

    In [156]: df_orig.where(df > 0, -df, inplace=True);

    In [157]: df_orig
    Out[157]:
                       A         B         C         D
    2000-01-01  1.266143  0.299368  0.863838  0.408204
    2000-01-02  1.048089  0.025747  0.988387  0.094055
    2000-01-03  1.262731  1.289997  0.082423  0.055758
    2000-01-04  0.536580  0.489682  0.369374  0.034571
    2000-01-05  2.484478  0.281461  0.030711  0.109121
    2000-01-06  1.126203  0.977349  1.474071  0.064034
    2000-01-07  1.282782  0.781836  1.071357  0.441153
    2000-01-08  2.353925  0.583787  0.221471  0.744471

alignment

Furthermore, `where` aligns the input boolean condition (ndarray or DataFrame), such that partial selection with setting is possible. This is analogous to partial setting via `.ix` (but on the contents rather than the axis labels)

    In [158]: df2 = df.copy()

    In [159]: df2[ df2[1:4] > 0 ] = 3

    In [160]: df2
    Out[160]:
                       A         B         C         D
    2000-01-01  1.266143  0.299368 -0.863838  0.408204
    2000-01-02 -1.048089 -0.025747 -0.988387  3.000000
    2000-01-03  3.000000  3.000000  3.000000 -0.055758
    2000-01-04  3.000000 -0.489682  3.000000 -0.034571
    2000-01-05 -2.484478 -0.281461  0.030711  0.109121
    2000-01-06  1.126203 -0.977349  1.474071 -0.064034
    2000-01-07 -1.282782  0.781836 -1.071357  0.441153
    2000-01-08  2.353925  0.583787  0.221471 -0.744471

New in version 0.13.

Where can also accept `axis` and `level` parameters to align the input when performing the `where`.

    In [161]: df2 = df.copy()

    In [162]: df2.where(df2>0,df2['A'],axis='index')
    Out[162]:
                       A         B         C         D
    2000-01-01  1.266143  0.299368  1.266143  0.408204
    2000-01-02 -1.048089 -1.048089 -1.048089  0.094055
    2000-01-03  1.262731  1.289997  0.082423  1.262731
    2000-01-04  0.536580  0.536580  0.369374  0.536580
    2000-01-05 -2.484478 -2.484478  0.030711  0.109121
    2000-01-06  1.126203  1.126203  1.474071  1.126203
    2000-01-07 -1.282782  0.781836 -1.282782  0.441153
    2000-01-08  2.353925  0.583787  0.221471  2.353925

This is equivalent (but faster than) the following.

    In [163]: df2 = df.copy()

    In [164]: df.apply(lambda x, y: x.where(x>0,y), y=df['A'])
    Out[164]:
                       A         B         C         D
    2000-01-01  1.266143  0.299368  1.266143  0.408204
    2000-01-02 -1.048089 -1.048089 -1.048089  0.094055
    2000-01-03  1.262731  1.289997  0.082423  1.262731
    2000-01-04  0.536580  0.536580  0.369374  0.536580
    2000-01-05 -2.484478 -2.484478  0.030711  0.109121
    2000-01-06  1.126203  1.126203  1.474071  1.126203
    2000-01-07 -1.282782  0.781836 -1.282782  0.441153
    2000-01-08  2.353925  0.583787  0.221471  2.353925

mask

`mask` is the inverse boolean operation of `where`.

    In [165]: s.mask(s >= 0)
    Out[165]:
    4   NaN
    3   NaN
    2   NaN
    1   NaN
    0   NaN
    dtype: float64

    In [166]: df.mask(df >= 0)
    Out[166]:
                       A         B         C         D
    2000-01-01       NaN       NaN -0.863838       NaN
    2000-01-02 -1.048089 -0.025747 -0.988387       NaN
    2000-01-03       NaN       NaN       NaN -0.055758
    2000-01-04       NaN -0.489682       NaN -0.034571
    2000-01-05 -2.484478 -0.281461       NaN       NaN
    2000-01-06       NaN -0.977349       NaN -0.064034
    2000-01-07 -1.282782       NaN -1.071357       NaN
    2000-01-08       NaN       NaN       NaN -0.744471

## The `query()` Method (Experimental)

New in version 0.13.

`DataFrame` objects have a `query()` method that allows selection using an expression.

You can get the value of the frame where column `b` has values between the values of columns `a` and `c`. For example:

    In [167]: n = 10

    In [168]: df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))

    In [169]: df
    Out[169]:
              a         b         c
    0  0.687704  0.582314  0.281645
    1  0.250846  0.610021  0.420121
    2  0.624328  0.401816  0.932146
    3  0.011763  0.022921  0.244186
    4  0.590198  0.325680  0.890392
    5  0.598892  0.296424  0.007312
    6  0.634625  0.803069  0.123872
    7  0.924168  0.325076  0.303746
    8  0.116822  0.364564  0.454607
    9  0.986142  0.751953  0.561512

    # pure python
    In [170]: df[(df.a < df.b) & (df.b < df.c)]
    Out[170]:
              a         b         c
    3  0.011763  0.022921  0.244186
    8  0.116822  0.364564  0.454607

    # query
    In [171]: df.query('(a < b) & (b < c)')
    Out[171]:
              a         b         c
    3  0.011763  0.022921  0.244186
    8  0.116822  0.364564  0.454607

Do the same thing but fall back on a named index if there is no column with the name `a`.

    In [172]: df = pd.DataFrame(np.random.randint(n / 2, size=(n, 2)), columns=list('bc'))

    In [173]: df.index.name = 'a'

    In [174]: df
    Out[174]:
       b  c
    a
    0  0  4
    1  2  2
    2  1  4
    3  3  3
    4  0  4
    5  1  1
    6  0  3
    7  2  4
    8  3  1
    9  3  2

    In [175]: df.query('a < b and b < c')
    Out[175]:
    Empty DataFrame
    Columns: [b, c]
    Index: []

If instead you don’t want to or cannot name your index, you can use the name `index` in your query expression:

    In [176]: df = pd.DataFrame(np.random.randint(n, size=(n, 2)), columns=list('bc'))

    In [177]: df
    Out[177]:
       b  c
    0  9  8
    1  0  5
    2  4  7
    3  2  0
    4  5  1
    5  4  0
    6  2  0
    7  6  7
    8  3  4
    9  9  7

    In [178]: df.query('index < b < c')
    Out[178]:
       b  c
    2  4  7

Note

If the name of your index overlaps with a column name, the column name is given precedence. For example,

    In [179]: df = pd.DataFrame({'a': np.random.randint(5, size=5)})

    In [180]: df.index.name = 'a'

    In [181]: df.query('a > 2') # uses the column 'a', not the index
    Out[181]:
       a
    a
    2  4
    3  4
    4  3

You can still use the index in a query expression by using the special identifier ‘index’:

    In [182]: df.query('index > 2')
    Out[182]:
       a
    a
    3  4
    4  3

If for some reason you have a column named `index`, then you can refer to the index as `ilevel_0` as well, but at this point you should consider renaming your columns to something less ambiguous.

### `MultiIndex` `query()` Syntax

You can also use the levels of a `DataFrame` with a `MultiIndex` as if they were columns in the frame:

    In [183]: n = 10

    In [184]: colors = np.random.choice(['red', 'green'], size=n)

    In [185]: foods = np.random.choice(['eggs', 'ham'], size=n)

    In [186]: colors
    Out[186]:
    array(['green', 'green', 'red', 'red', 'green', 'green', 'red', 'green',
           'red', 'green'],
          dtype='|S5')

    In [187]: foods
    Out[187]:
    array(['eggs', 'eggs', 'ham', 'ham', 'eggs', 'ham', 'eggs', 'ham', 'eggs',
           'eggs'],
          dtype='|S4')

    In [188]: index = pd.MultiIndex.from_arrays([colors, foods], names=['color', 'food'])

    In [189]: df = pd.DataFrame(np.random.randn(n, 2), index=index)

    In [190]: df
    Out[190]:
                       0         1
    color food
    green eggs  1.555563 -0.823761
          eggs  0.535420 -1.032853
    red   ham   1.469725  1.304124
          ham   1.449735  0.203109
    green eggs -1.032011  0.969818
          ham  -0.962723  1.382083
    red   eggs -0.938794  0.669142
    green ham  -0.433567 -0.273610
    red   eggs  0.680433 -0.308450
    green eggs -0.276099 -1.821168

    In [191]: df.query('color == "red"')
    Out[191]:
                       0         1
    color food
    red   ham   1.469725  1.304124
          ham   1.449735  0.203109
          eggs -0.938794  0.669142
          eggs  0.680433 -0.308450

If the levels of the `MultiIndex` are unnamed, you can refer to them using special names:

    In [192]: df.index.names = [None, None]

    In [193]: df
    Out[193]:
                       0         1
    green eggs  1.555563 -0.823761
          eggs  0.535420 -1.032853
    red   ham   1.469725  1.304124
          ham   1.449735  0.203109
    green eggs -1.032011  0.969818
          ham  -0.962723  1.382083
    red   eggs -0.938794  0.669142
    green ham  -0.433567 -0.273610
    red   eggs  0.680433 -0.308450
    green eggs -0.276099 -1.821168

    In [194]: df.query('ilevel_0 == "red"')
    Out[194]:
                     0         1
    red ham   1.469725  1.304124
        ham   1.449735  0.203109
        eggs -0.938794  0.669142
        eggs  0.680433 -0.308450

The convention is `ilevel_0`, which means “index level 0” for the 0th level of the `index`.

### `query()` Use Cases

A use case for `query()` is when you have a collection of `DataFrame` objects that have a subset of column names (or index levels/names) in common. You can pass the same query to both frames without having to specify which frame you’re interested in querying

    In [195]: df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))

    In [196]: df
    Out[196]:
              a         b         c
    0  0.449167  0.447421  0.557792
    1  0.427877  0.690714  0.035221
    2  0.543944  0.507917  0.468025
    3  0.973561  0.727371  0.700486
    4  0.515391  0.337711  0.107630
    5  0.697879  0.541870  0.043587
    6  0.074629  0.717019  0.661852
    7  0.524683  0.446253  0.066302
    8  0.744757  0.361155  0.205344
    9  0.948737  0.555495  0.357893

    In [197]: df2 = pd.DataFrame(np.random.rand(n + 2, 3), columns=df.columns)

    In [198]: df2
    Out[198]:
               a         b         c
    0   0.164291  0.785148  0.847700
    1   0.953696  0.111044  0.252125
    2   0.033633  0.892920  0.037910
    3   0.662580  0.219559  0.243745
    4   0.885036  0.790218  0.224283
    5   0.736107  0.139168  0.302827
    6   0.657803  0.713897  0.611185
    7   0.136624  0.984960  0.195246
    8   0.123436  0.627712  0.618673
    9   0.371660  0.047902  0.480088
    10  0.062993  0.185760  0.568018
    11  0.483467  0.445289  0.309040

    In [199]: expr = '0.0 <= a <= c <= 0.5'

    In [200]: map(lambda frame: frame.query(expr), [df, df2])
    Out[200]:
    [Empty DataFrame
     Columns: [a, b, c]
     Index: [],           a         b         c
     2  0.033633  0.892920  0.037910
     7  0.136624  0.984960  0.195246
     9  0.371660  0.047902  0.480088]

### `query()` Python versus pandas Syntax Comparison

Full numpy-like syntax

    In [201]: df = pd.DataFrame(np.random.randint(n, size=(n, 3)), columns=list('abc'))

    In [202]: df
    Out[202]:
       a  b  c
    0  8  8  1
    1  1  1  9
    2  9  9  8
    3  7  0  1
    4  8  6  6
    5  8  2  8
    6  3  2  2
    7  9  4  8
    8  7  2  7
    9  9  4  8

    In [203]: df.query('(a < b) & (b < c)')
    Out[203]:
    Empty DataFrame
    Columns: [a, b, c]
    Index: []

    In [204]: df[(df.a < df.b) & (df.b < df.c)]
    Out[204]:
    Empty DataFrame
    Columns: [a, b, c]
    Index: []

Slightly nicer by removing the parentheses (by binding making comparison operators bind tighter than `&`/`|`)

    In [205]: df.query('a < b & b < c')
    Out[205]:
    Empty DataFrame
    Columns: [a, b, c]
    Index: []

Use English instead of symbols

    In [206]: df.query('a < b and b < c')
    Out[206]:
    Empty DataFrame
    Columns: [a, b, c]
    Index: []

Pretty close to how you might write it on paper

    In [207]: df.query('a < b < c')
    Out[207]:
    Empty DataFrame
    Columns: [a, b, c]
    Index: []

### The `in` and `not in` operators

`query()` also supports special use of Python’s `in` and `not in` comparison operators, providing a succinct syntax for calling the `isin` method of a `Series` or `DataFrame`.

    # get all rows where columns "a" and "b" have overlapping values
    In [208]: df = pd.DataFrame({'a': list('aabbccddeeff'), 'b': list('aaaabbbbcccc'),
       .....:                    'c': np.random.randint(5, size=12),
       .....:                    'd': np.random.randint(9, size=12)})
       .....:

    In [209]: df
    Out[209]:
        a  b  c  d
    0   a  a  1  7
    1   a  a  0  1
    2   b  a  2  0
    3   b  a  1  7
    4   c  b  0  8
    5   c  b  1  1
    6   d  b  1  0
    7   d  b  1  7
    8   e  c  4  2
    9   e  c  2  7
    10  f  c  2  2
    11  f  c  4  6

    In [210]: df.query('a in b')
    Out[210]:
       a  b  c  d
    0  a  a  1  7
    1  a  a  0  1
    2  b  a  2  0
    3  b  a  1  7
    4  c  b  0  8
    5  c  b  1  1

    # How you'd do it in pure Python
    In [211]: df[df.a.isin(df.b)]
    Out[211]:
       a  b  c  d
    0  a  a  1  7
    1  a  a  0  1
    2  b  a  2  0
    3  b  a  1  7
    4  c  b  0  8
    5  c  b  1  1

    In [212]: df.query('a not in b')
    Out[212]:
        a  b  c  d
    6   d  b  1  0
    7   d  b  1  7
    8   e  c  4  2
    9   e  c  2  7
    10  f  c  2  2
    11  f  c  4  6

    # pure Python
    In [213]: df[~df.a.isin(df.b)]
    Out[213]:
        a  b  c  d
    6   d  b  1  0
    7   d  b  1  7
    8   e  c  4  2
    9   e  c  2  7
    10  f  c  2  2
    11  f  c  4  6

You can combine this with other expressions for very succinct queries:

    # rows where cols a and b have overlapping values and col c's values are less than col d's
    In [214]: df.query('a in b and c < d')
    Out[214]:
       a  b  c  d
    0  a  a  1  7
    1  a  a  0  1
    3  b  a  1  7
    4  c  b  0  8

    # pure Python
    In [215]: df[df.b.isin(df.a) & (df.c < df.d)]
    Out[215]:
        a  b  c  d
    0   a  a  1  7
    1   a  a  0  1
    3   b  a  1  7
    4   c  b  0  8
    7   d  b  1  7
    9   e  c  2  7
    11  f  c  4  6

Note

Note that `in` and `not in` are evaluated in Python, since `numexpr` has no equivalent of this operation. However, only the `in`/`not in` expression itself is evaluated in vanilla Python. For example, in the expression

    df.query('a in b + c + d')

`(b + c + d)` is evaluated by `numexpr` and then the `in` operation is evaluated in plain Python. In general, any operations that can be evaluated using `numexpr` will be.

### Special use of the `==` operator with `list` objects

Comparing a `list` of values to a column using `==`/`!=` works similarly to `in`/`not in`

    In [216]: df.query('b == ["a", "b", "c"]')
    Out[216]:
        a  b  c  d
    0   a  a  1  7
    1   a  a  0  1
    2   b  a  2  0
    3   b  a  1  7
    4   c  b  0  8
    5   c  b  1  1
    6   d  b  1  0
    7   d  b  1  7
    8   e  c  4  2
    9   e  c  2  7
    10  f  c  2  2
    11  f  c  4  6

    # pure Python
    In [217]: df[df.b.isin(["a", "b", "c"])]
    Out[217]:
        a  b  c  d
    0   a  a  1  7
    1   a  a  0  1
    2   b  a  2  0
    3   b  a  1  7
    4   c  b  0  8
    5   c  b  1  1
    6   d  b  1  0
    7   d  b  1  7
    8   e  c  4  2
    9   e  c  2  7
    10  f  c  2  2
    11  f  c  4  6

    In [218]: df.query('c == [1, 2]')
    Out[218]:
        a  b  c  d
    0   a  a  1  7
    2   b  a  2  0
    3   b  a  1  7
    5   c  b  1  1
    6   d  b  1  0
    7   d  b  1  7
    9   e  c  2  7
    10  f  c  2  2

    In [219]: df.query('c != [1, 2]')
    Out[219]:
        a  b  c  d
    1   a  a  0  1
    4   c  b  0  8
    8   e  c  4  2
    11  f  c  4  6

    # using in/not in
    In [220]: df.query('[1, 2] in c')
    Out[220]:
        a  b  c  d
    0   a  a  1  7
    2   b  a  2  0
    3   b  a  1  7
    5   c  b  1  1
    6   d  b  1  0
    7   d  b  1  7
    9   e  c  2  7
    10  f  c  2  2

    In [221]: df.query('[1, 2] not in c')
    Out[221]:
        a  b  c  d
    1   a  a  0  1
    4   c  b  0  8
    8   e  c  4  2
    11  f  c  4  6

    # pure Python
    In [222]: df[df.c.isin([1, 2])]
    Out[222]:
        a  b  c  d
    0   a  a  1  7
    2   b  a  2  0
    3   b  a  1  7
    5   c  b  1  1
    6   d  b  1  0
    7   d  b  1  7
    9   e  c  2  7
    10  f  c  2  2

### Boolean Operators

You can negate boolean expressions with the word `not` or the `~` operator.

    In [223]: df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))

    In [224]: df['bools'] = np.random.rand(len(df)) > 0.5

    In [225]: df.query('~bools')
    Out[225]:
              a         b         c  bools
    1  0.023729  0.458852  0.284632  False
    2  0.004236  0.442347  0.767020  False
    3  0.890592  0.568747  0.332692  False
    5  0.843864  0.902394  0.538418  False
    6  0.428277  0.870859  0.380827  False
    7  0.321819  0.559198  0.382567  False
    8  0.119041  0.715950  0.448156  False
    9  0.606506  0.300726  0.666418  False

    In [226]: df.query('not bools')
    Out[226]:
              a         b         c  bools
    1  0.023729  0.458852  0.284632  False
    2  0.004236  0.442347  0.767020  False
    3  0.890592  0.568747  0.332692  False
    5  0.843864  0.902394  0.538418  False
    6  0.428277  0.870859  0.380827  False
    7  0.321819  0.559198  0.382567  False
    8  0.119041  0.715950  0.448156  False
    9  0.606506  0.300726  0.666418  False

    In [227]: df.query('not bools') == df[~df.bools]
    Out[227]:
          a     b     c bools
    1  True  True  True  True
    2  True  True  True  True
    3  True  True  True  True
    5  True  True  True  True
    6  True  True  True  True
    7  True  True  True  True
    8  True  True  True  True
    9  True  True  True  True

Of course, expressions can be arbitrarily complex too

    # short query syntax
    In [228]: shorter = df.query('a < b < c and (not bools) or bools > 2')

    # equivalent in pure Python
    In [229]: longer = df[(df.a < df.b) & (df.b < df.c) & (~df.bools) | (df.bools > 2)]

    In [230]: shorter
    Out[230]:
              a         b        c  bools
    2  0.004236  0.442347  0.76702  False

    In [231]: longer
    Out[231]:
              a         b        c  bools
    2  0.004236  0.442347  0.76702  False

    In [232]: shorter == longer
    Out[232]:
          a     b     c bools
    2  True  True  True  True

### Performance of `query()`

`DataFrame.query()` using `numexpr` is slightly faster than Python for large frames

Note

You will only see the performance benefits of using the `numexpr` engine with `DataFrame.query()` if your frame has more than approximately 200,000 rows

This plot was created using a `DataFrame` with 3 columns each containing floating point values generated using `numpy.random.randn()`.

## Duplicate Data

If you want to identify and remove duplicate rows in a DataFrame, there are two methods that will help: `duplicated` and `drop_duplicates`. Each takes as an argument the columns to use to identify duplicated rows.

- `duplicated` returns a boolean vector whose length is the number of rows, and which indicates whether a row is duplicated.
- `drop_duplicates` removes duplicate rows.

By default, the first observed row of a duplicate set is considered unique, but each method has a `keep` parameter to specify targets to be kept.

- `keep='first'` (default): mark / drop duplicates except for the first occurrence.
- `keep='last'`: mark / drop duplicates except for the last occurrence.
- `keep=False`: mark / drop all duplicates.

    In [233]: df2 = pd.DataFrame({'a': ['one', 'one', 'two', 'two', 'two', 'three', 'four'],
       .....:                     'b': ['x', 'y', 'x', 'y', 'x', 'x', 'x'],
       .....:                     'c': np.random.randn(7)})
       .....:

    In [234]: df2
    Out[234]:
           a  b         c
    0    one  x -0.599770
    1    one  y -0.120145
    2    two  x -1.403265
    3    two  y  0.162169
    4    two  x  0.232777
    5  three  x  1.048589
    6   four  x  0.070084

    In [235]: df2.duplicated('a')
    Out[235]:
    0    False
    1     True
    2    False
    3     True
    4     True
    5    False
    6    False
    dtype: bool

    In [236]: df2.duplicated('a', keep='last')
    Out[236]:
    0     True
    1    False
    2     True
    3     True
    4    False
    5    False
    6    False
    dtype: bool

    In [237]: df2.duplicated('a', keep=False)
    Out[237]:
    0     True
    1     True
    2     True
    3     True
    4     True
    5    False
    6    False
    dtype: bool

    In [238]: df2.drop_duplicates('a')
    Out[238]:
           a  b         c
    0    one  x -0.599770
    2    two  x -1.403265
    5  three  x  1.048589
    6   four  x  0.070084

    In [239]: df2.drop_duplicates('a', keep='last')
    Out[239]:
           a  b         c
    1    one  y -0.120145
    4    two  x  0.232777
    5  three  x  1.048589
    6   four  x  0.070084

    In [240]: df2.drop_duplicates('a', keep=False)
    Out[240]:
           a  b         c
    5  three  x  1.048589
    6   four  x  0.070084

Also, you can pass a list of columns to identify duplications.

    In [241]: df2.duplicated(['a', 'b'])
    Out[241]:
    0    False
    1    False
    2    False
    3    False
    4     True
    5    False
    6    False
    dtype: bool

    In [242]: df2.drop_duplicates(['a', 'b'])
    Out[242]:
           a  b         c
    0    one  x -0.599770
    1    one  y -0.120145
    2    two  x -1.403265
    3    two  y  0.162169
    5  three  x  1.048589
    6   four  x  0.070084

To drop duplicates by index value, use `Index.duplicated` then perform slicing. Same options are available in `keep` parameter.

    In [243]: df3 = pd.DataFrame({'a': np.arange(6),
       .....:                     'b': np.random.randn(6)},
       .....:                    index=['a', 'a', 'b', 'c', 'b', 'a'])
       .....:

    In [244]: df3
    Out[244]:
       a         b
    a  0 -0.774247
    a  1 -0.436052
    b  2  0.274093
    c  3  1.386100
    b  4  0.705452
    a  5  0.515768

    In [245]: df3.index.duplicated()
    Out[245]: array([False,  True, False, False,  True,  True], dtype=bool)

    In [246]: df3[~df3.index.duplicated()]
    Out[246]:
       a         b
    a  0 -0.774247
    b  2  0.274093
    c  3  1.386100

    In [247]: df3[~df3.index.duplicated(keep='last')]
    Out[247]:
       a         b
    c  3  1.386100
    b  4  0.705452
    a  5  0.515768

    In [248]: df3[~df3.index.duplicated(keep=False)]
    Out[248]:
       a       b
    c  3  1.3861

## Dictionary-like `get()` method

Each of Series, DataFrame, and Panel have a `get` method which can return a default value.

    In [249]: s = pd.Series([1,2,3], index=['a','b','c'])

    In [250]: s.get('a')               # equivalent to s['a']
    Out[250]: 1

    In [251]: s.get('x', default=-1)
    Out[251]: -1

## The `select()` Method

Another way to extract slices from an object is with the `select` method of Series, DataFrame, and Panel. This method should be used only when there is no more direct way. `select` takes a function which operates on labels along `axis` and returns a boolean. For instance:

    In [252]: df.select(lambda x: x == 'A', axis=1)
    Out[252]:
                       A
    2000-01-01 -0.226151
    2000-01-02  1.380589
    2000-01-03  1.185125
    2000-01-04 -0.124870
    2000-01-05 -0.926415
    2000-01-06 -0.447053
    2000-01-07  0.183787
    2000-01-08 -1.544483

## The `lookup()` Method

Sometimes you want to extract a set of values given a sequence of row labels and column labels, and the `lookup` method allows for this and returns a numpy array. For instance,

    In [253]: dflookup = pd.DataFrame(np.random.rand(20,4), columns = ['A','B','C','D'])

    In [254]: dflookup.lookup(list(range(0,10,2)), ['B','C','A','B','D'])
    Out[254]: array([ 0.9242,  0.3338,  0.7562,  0.6023,  0.7212])

## Index objects

The pandas `Index` class and its subclasses can be viewed as implementing an ordered multiset. Duplicates are allowed. However, if you try to convert an `Index` object with duplicate entries into a `set`, an exception will be raised.

`Index` also provides the infrastructure necessary for lookups, data alignment, and reindexing. The easiest way to create an `Index` directly is to pass a `list` or other sequence to `Index`:

    In [255]: index = pd.Index(['e', 'd', 'a', 'b'])

    In [256]: index
    Out[256]: Index([u'e', u'd', u'a', u'b'], dtype='object')

    In [257]: 'd' in index
    Out[257]: True

You can also pass a `name` to be stored in the index:

    In [258]: index = pd.Index(['e', 'd', 'a', 'b'], name='something')

    In [259]: index.name
    Out[259]: 'something'

The name, if set, will be shown in the console display:

    In [260]: index = pd.Index(list(range(5)), name='rows')

    In [261]: columns = pd.Index(['A', 'B', 'C'], name='cols')

    In [262]: df = pd.DataFrame(np.random.randn(5, 3), index=index, columns=columns)

    In [263]: df
    Out[263]:
    cols         A         B         C
    rows
    0     0.352642  1.996218 -1.293063
    1    -0.393590  0.034083  0.293336
    2    -0.448824  0.224249  0.709838
    3    -0.331820 -0.548158 -1.313391
    4    -1.549151  0.635278 -1.103599

    In [264]: df['A']
    Out[264]:
    rows
    0    0.352642
    1   -0.393590
    2   -0.448824
    3   -0.331820
    4   -1.549151
    Name: A, dtype: float64

### Setting metadata

New in version 0.13.0.

Indexes are “mostly immutable”, but it is possible to set and change their metadata, like the index `name` (or, for `MultiIndex`, `levels` and `labels`).

You can use the `rename`, `set_names`, `set_levels`, and `set_labels` to set these attributes directly. They default to returning a copy; however, you can specify `inplace=True` to have the data change in place.

See Advanced Indexing for usage of MultiIndexes.

    In [265]: ind = pd.Index([1, 2, 3])

    In [266]: ind.rename("apple")
    Out[266]: Int64Index([1, 2, 3], dtype='int64', name=u'apple')

    In [267]: ind
    Out[267]: Int64Index([1, 2, 3], dtype='int64')

    In [268]: ind.set_names(["apple"], inplace=True)

    In [269]: ind.name = "bob"

    In [270]: ind
    Out[270]: Int64Index([1, 2, 3], dtype='int64', name=u'bob')

New in version 0.15.0.

`set_names`, `set_levels`, and `set_labels` also take an optional level` argument

    In [271]: index = pd.MultiIndex.from_product([range(3), ['one', 'two']], names=['first', 'second'])

    In [272]: index
    Out[272]:
    MultiIndex(levels=[[0, 1, 2], [u'one', u'two']],
               labels=[[0, 0, 1, 1, 2, 2], [0, 1, 0, 1, 0, 1]],
               names=[u'first', u'second'])

    In [273]: index.levels[1]
    Out[273]: Index([u'one', u'two'], dtype='object', name=u'second')

    In [274]: index.set_levels(["a", "b"], level=1)
    Out[274]:
    MultiIndex(levels=[[0, 1, 2], [u'a', u'b']],
               labels=[[0, 0, 1, 1, 2, 2], [0, 1, 0, 1, 0, 1]],
               names=[u'first', u'second'])

### Set operations on Index objects

Warning

In 0.15.0. the set operations `+` and `-` were deprecated in order to provide these for numeric type operations on certain index types. `+` can be replace by `.union()` or `|`, and `-` by `.difference()`.

The two main operations are `union (|)`, `intersection (&)` These can be directly called as instance methods or used via overloaded operators. Difference is provided via the `.difference()` method.

    In [275]: a = pd.Index(['c', 'b', 'a'])

    In [276]: b = pd.Index(['c', 'e', 'd'])

    In [277]: a | b
    Out[277]: Index([u'a', u'b', u'c', u'd', u'e'], dtype='object')

    In [278]: a & b
    Out[278]: Index([u'c'], dtype='object')

    In [279]: a.difference(b)
    Out[279]: Index([u'a', u'b'], dtype='object')

Also available is the `sym_diff (^)` operation, which returns elements that appear in either `idx1` or `idx2` but not both. This is equivalent to the Index created by `idx1.difference(idx2).union(idx2.difference(idx1))`, with duplicates dropped.

    In [280]: idx1 = pd.Index([1, 2, 3, 4])

    In [281]: idx2 = pd.Index([2, 3, 4, 5])

    In [282]: idx1.sym_diff(idx2)
    Out[282]: Int64Index([1, 5], dtype='int64')

    In [283]: idx1 ^ idx2
    Out[283]: Int64Index([1, 5], dtype='int64')

### Missing values

New in version 0.17.1.

Important

Even though `Index` can hold missing values (`NaN`), it should be avoided if you do not want any unexpected results. For example, some operations exclude missing values implicitly.

`Index.fillna` fills missing values with specified scalar value.

    In [284]: idx1 = pd.Index([1, np.nan, 3, 4])

    In [285]: idx1
    Out[285]: Float64Index([1.0, nan, 3.0, 4.0], dtype='float64')

    In [286]: idx1.fillna(2)
    Out[286]: Float64Index([1.0, 2.0, 3.0, 4.0], dtype='float64')

    In [287]: idx2 = pd.DatetimeIndex([pd.Timestamp('2011-01-01'), pd.NaT, pd.Timestamp('2011-01-03')])

    In [288]: idx2
    Out[288]: DatetimeIndex(['2011-01-01', 'NaT', '2011-01-03'], dtype='datetime64[ns]', freq=None)

    In [289]: idx2.fillna(pd.Timestamp('2011-01-02'))
    Out[289]: DatetimeIndex(['2011-01-01', '2011-01-02', '2011-01-03'], dtype='datetime64[ns]', freq=None)

## Set / Reset Index

Occasionally you will load or create a data set into a DataFrame and want to add an index after you’ve already done so. There are a couple of different ways.

### Set an index

DataFrame has a `set_index` method which takes a column name (for a regular `Index`) or a list of column names (for a `MultiIndex`), to create a new, indexed DataFrame:

    In [290]: data
    Out[290]:
         a    b  c  d
    0  bar  one  z  1
    1  bar  two  y  2
    2  foo  one  x  3
    3  foo  two  w  4

    In [291]: indexed1 = data.set_index('c')

    In [292]: indexed1
    Out[292]:
         a    b  d
    c
    z  bar  one  1
    y  bar  two  2
    x  foo  one  3
    w  foo  two  4

    In [293]: indexed2 = data.set_index(['a', 'b'])

    In [294]: indexed2
    Out[294]:
             c  d
    a   b
    bar one  z  1
        two  y  2
    foo one  x  3
        two  w  4

The `append` keyword option allow you to keep the existing index and append the given columns to a MultiIndex:

    In [295]: frame = data.set_index('c', drop=False)

    In [296]: frame = frame.set_index(['a', 'b'], append=True)

    In [297]: frame
    Out[297]:
               c  d
    c a   b
    z bar one  z  1
    y bar two  y  2
    x foo one  x  3
    w foo two  w  4

Other options in `set_index` allow you not drop the index columns or to add the index in-place (without creating a new object):

    In [298]: data.set_index('c', drop=False)
    Out[298]:
         a    b  c  d
    c
    z  bar  one  z  1
    y  bar  two  y  2
    x  foo  one  x  3
    w  foo  two  w  4

    In [299]: data.set_index(['a', 'b'], inplace=True)

    In [300]: data
    Out[300]:
             c  d
    a   b
    bar one  z  1
        two  y  2
    foo one  x  3
        two  w  4

### Reset the index

As a convenience, there is a new function on DataFrame called `reset_index` which transfers the index values into the DataFrame’s columns and sets a simple integer index. This is the inverse operation to `set_index`

    In [301]: data
    Out[301]:
             c  d
    a   b
    bar one  z  1
        two  y  2
    foo one  x  3
        two  w  4

    In [302]: data.reset_index()
    Out[302]:
         a    b  c  d
    0  bar  one  z  1
    1  bar  two  y  2
    2  foo  one  x  3
    3  foo  two  w  4

The output is more similar to a SQL table or a record array. The names for the columns derived from the index are the ones stored in the `names` attribute.

You can use the `level` keyword to remove only a portion of the index:

    In [303]: frame
    Out[303]:
               c  d
    c a   b
    z bar one  z  1
    y bar two  y  2
    x foo one  x  3
    w foo two  w  4

    In [304]: frame.reset_index(level=1)
    Out[304]:
             a  c  d
    c b
    z one  bar  z  1
    y two  bar  y  2
    x one  foo  x  3
    w two  foo  w  4

`reset_index` takes an optional parameter `drop` which if true simply discards the index, instead of putting index values in the DataFrame’s columns.

Note

The `reset_index` method used to be called `delevel` which is now deprecated.

### Adding an ad hoc index

If you create an index yourself, you can just assign it to the `index` field:

    data.index = index

## Returning a view versus a copy

When setting values in a pandas object, care must be taken to avoid what is called `chained indexing`. Here is an example.

    In [305]: dfmi = pd.DataFrame([list('abcd'),
       .....:                      list('efgh'),
       .....:                      list('ijkl'),
       .....:                      list('mnop')],
       .....:                     columns=pd.MultiIndex.from_product([['one','two'],
       .....:                                                         ['first','second']]))
       .....:

    In [306]: dfmi
    Out[306]:
        one          two
      first second first second
    0     a      b     c      d
    1     e      f     g      h
    2     i      j     k      l
    3     m      n     o      p

Compare these two access methods:

    In [307]: dfmi['one']['second']
    Out[307]:
    0    b
    1    f
    2    j
    3    n
    Name: second, dtype: object

    In [308]: dfmi.loc[:,('one','second')]
    Out[308]:
    0    b
    1    f
    2    j
    3    n
    Name: (one, second), dtype: object

These both yield the same results, so which should you use? It is instructive to understand the order of operations on these and why method 2 (`.loc`) is much preferred over method 1 (chained `[]`)

`dfmi['one']` selects the first level of the columns and returns a data frame that is singly-indexed. Then another python operation `dfmi_with_one['second']` selects the series indexed by `'second'` happens. This is indicated by the variable `dfmi_with_one` because pandas sees these operations as separate events. e.g. separate calls to `__getitem__`, so it has to treat them as linear operations, they happen one after another.

Contrast this to `df.loc[:,('one','second')]` which passes a nested tuple of `(slice(None),('one','second'))` to a single call to `__getitem__`. This allows pandas to deal with this as a single entity. Furthermore this order of operations can be significantly faster, and allows one to index both axes if so desired.

### Why does the assignment when using chained indexing fail!

So, why does this show the `SettingWithCopy` warning / and possibly not work when you do chained indexing and assignment:

    dfmi['one']['second'] = value

Since the chained indexing is 2 calls, it is possible that either call may return a copy of the data because of the way it is sliced. Thus when setting, you are actually setting a copy, and not the original frame data. It is impossible for pandas to figure this out because their are 2 separate python operations that are not connected.

The `SettingWithCopy` warning is a ‘heuristic’ to detect this (meaning it tends to catch most cases but is simply a lightweight check). Figuring this out for real is way complicated.

The `.loc` operation is a single python operation, and thus can select a slice (which still may be a copy), but allows pandas to assign that slice back into the frame after it is modified, thus setting the values as you would think.

The reason for having the `SettingWithCopy` warning is this. Sometimes when you slice an array you will simply get a view back, which means you can set it no problem. However, even a single dtyped array can generate a copy if it is sliced in a particular way. A multi-dtyped DataFrame (meaning it has say `float` and `object` data), will almost always yield a copy. Whether a view is created is dependent on the memory layout of the array.

### Evaluation order matters

Furthermore, in chained expressions, the order may determine whether a copy is returned or not. If an expression will set values on a copy of a slice, then a `SettingWithCopy` exception will be raised (this raise/warn behavior is new starting in 0.13.0)

You can control the action of a chained assignment via the option `mode.chained_assignment`, which can take the values `['raise','warn',None]`, where showing a warning is the default.

    In [309]: dfb = pd.DataFrame({'a' : ['one', 'one', 'two',
       .....:                            'three', 'two', 'one', 'six'],
       .....:                     'c' : np.arange(7)})
       .....:

    # This will show the SettingWithCopyWarning
    # but the frame values will be set
    In [310]: dfb['c'][dfb.a.str.startswith('o')] = 42

This however is operating on a copy and will not work.

    >>> pd.set_option('mode.chained_assignment','warn')
    >>> dfb[dfb.a.str.startswith('o')]['c'] = 42
    Traceback (most recent call last)
         ...
    SettingWithCopyWarning:
         A value is trying to be set on a copy of a slice from a DataFrame.
         Try using .loc[row_index,col_indexer] = value instead

A chained assignment can also crop up in setting in a mixed dtype frame.

Note

These setting rules apply to all of `.loc/.iloc/.ix`

This is the correct access method

    In [311]: dfc = pd.DataFrame({'A':['aaa','bbb','ccc'],'B':[1,2,3]})

    In [312]: dfc.loc[0,'A'] = 11

    In [313]: dfc
    Out[313]:
         A  B
    0   11  1
    1  bbb  2
    2  ccc  3

This can work at times, but is not guaranteed, and so should be avoided

    In [314]: dfc = dfc.copy()

    In [315]: dfc['A'][0] = 111

    In [316]: dfc
    Out[316]:
         A  B
    0  111  1
    1  bbb  2
    2  ccc  3

This will not work at all, and so should be avoided

    >>> pd.set_option('mode.chained_assignment','raise')
    >>> dfc.loc[0]['A'] = 1111
    Traceback (most recent call last)
         ...
    SettingWithCopyException:
         A value is trying to be set on a copy of a slice from a DataFrame.
         Try using .loc[row_index,col_indexer] = value instead

Warning

The chained assignment warnings / exceptions are aiming to inform the user of a possibly invalid assignment. There may be false positives; situations where a chained assignment is inadvertently reported.