Series as generalized NumPy array

From what we’ve seen so far, it may look like the Series object is basically interchangeable with a one-dimensional NumPy array. The essential difference is the presence of the index: while the NumPy array has an implicitly defined integer index used to access the values, the Pandas Series has an explicitly defined index associated with the values.

This explicit index definition gives the Series object additional capabilities. For example, the index need not be an integer, but can consist of values of any desired type. For example, if we wish, we can use strings as an index:

 In   [   7   ]:    data    =    pd   .   Series   ([   0.25   ,    0.5   ,    0.75   ,    1.0   ],                            index   =   [   'a'   ,    'b'   ,    'c'   ,    'd'   ])           data   

Out[7]: a    0.25         b    0.50         c    0.75         d    1.00         dtype: float64

And the item access works as expected:

 In   [   8   ]:    data   [   'b'   ]   

Out[8]: 0.5

We can even use noncontiguous or nonsequential indices:

 In   [   9   ]:    data    =    pd   .   Series   ([   0.25   ,    0.5   ,    0.75   ,    1.0   ],                            index   =   [   2   ,    5   ,    3   ,    7   ])           data   

Out[9]: 2    0.25         5    0.50         3    0.75         7    1.00         dtype: float64

 In   [   10   ]:    data   [   5   ]   

Out[10]: 0.5

Series as specialized dictionary

In this way, you can think of a Pandas Series a bit like a specialization of a Python dictionary. A dictionary is a structure that maps arbitrary keys to a set of arbitrary values, and a Series is a structure that maps typed keys to a set of typed values. This typing is important: just as the type-specific compiled code behind a NumPy array makes it more efficient than a Python list for certain operations, the type information of a Pandas Series makes it much more efficient than Python dictionaries for certain operations.

We can make the Series -as-dictionary analogy even more clear by constructing a Series object directly from a Python dictionary:

 In   [   11   ]:    population_dict    =    {   'California'   :    38332521   ,                               'Texas'   :    26448193   ,                               'New York'   :    19651127   ,                               'Florida'   :    19552860   ,                               'Illinois'   :    12882135   }            population    =    pd   .   Series   (   population_dict   )            population   

Out[11]: California    38332521          Florida       19552860          Illinois      12882135          New York      19651127          Texas         26448193          dtype: int64

By default, a Series will be created where the index is drawn from the sorted keys. From here, typical dictionary-style item access can be performed:

 In   [   12   ]:    population   [   'California'   ]   

Out[12]: 38332521

Unlike a dictionary, though, the Series also supports array-style operations such as slicing:

 In   [   13   ]:    population   [   'California'   :   'Illinois'   ]   

Out[13]: California    38332521          Florida       19552860          Illinois      12882135          dtype: int64

We’ll discuss some of the quirks of Pandas indexing and slicing in .

Constructing Series objects

We’ve already seen a few ways of constructing a Pandas Series from scratch; all of them are some version of the following:

 >>>    pd   .   Series   (   data   ,    index   =   index   )   

where index is an optional argument, and data can be one of many entities.

For example, data can be a list or NumPy array, in which case index defaults to an integer sequence:

 In   [   14   ]:    pd   .   Series   ([   2   ,    4   ,    6   ])   

Out[14]: 0    2          1    4          2    6          dtype: int64

data can be a scalar, which is repeated to fill the specified index:

 In   [   15   ]:    pd   .   Series   (   5   ,    index   =   [   100   ,    200   ,    300   ])   

Out[15]: 100    5          200    5          300    5          dtype: int64

data can be a dictionary, in which index defaults to the sorted dictionary keys:

 In   [   16   ]:    pd   .   Series   ({   2   :   'a'   ,    1   :   'b'   ,    3   :   'c'   })   

Out[16]: 1    b          2    a          3    c          dtype: object

In each case, the index can be explicitly set if a different result is preferred:

 In   [   17   ]:    pd   .   Series   ({   2   :   'a'   ,    1   :   'b'   ,    3   :   'c'   },    index   =   [   3   ,    2   ])   

Out[17]: 3    c          2    a          dtype: object

Notice that in this case, the Series is populated only with the explicitly identified keys.

DataFrame as a generalized NumPy array

If a Series is an analog of a one-dimensional array with flexible indices, a DataFrame is an analog of a two-dimensional array with both flexible row indices and flexible column names. Just as you might think of a two-dimensional array as an ordered sequence of aligned one-dimensional columns, you can think of a DataFrame as a sequence of aligned Series objects. Here, by “aligned” we mean that they share the same index.

To demonstrate this, let’s first construct a new Series listing the area of each of the five states discussed in the previous section:

 In   [   18   ]:    area_dict    =    {   'California'   :    423967   ,    'Texas'   :    695662   ,    'New York'   :    141297   ,                 'Florida'   :    170312   ,    'Illinois'   :    149995   }    area    =    pd   .   Series   (   area_dict   )    area   

Out[18]: California    423967          Florida       170312          Illinois      149995          New York      141297          Texas         695662          dtype: int64

Now that we have this along with the population Series from before, we can use a dictionary to construct a single two-dimensional object containing this information:

 In   [   19   ]:    states    =    pd   .   DataFrame   ({   'population'   :    population   ,                                   'area'   :    area   })            states   

Out[19]:             area      population          California  423967    38332521          Florida     170312    19552860          Illinois    149995    12882135          New York    141297    19651127          Texas       695662    26448193

Like the Series object, the DataFrame has an index attribute that gives access to the index labels:

 In   [   20   ]:    states   .   index   

Out[20]: Index(['California', 'Florida', 'Illinois', 'New York', 'Texas'], dtype='object')

Additionally, the DataFrame has a columns attribute, which is an Index object holding the column labels:

 In   [   21   ]:    states   .   columns   

Out[21]: Index(['area', 'population'], dtype='object')

Thus the DataFrame can be thought of as a generalization of a two-dimensional NumPy array, where both the rows and columns have a generalized index for accessing the data.

DataFrame as specialized dictionary

Similarly, we can also think of a DataFrame as a specialization of a dictionary. Where a dictionary maps a key to a value, a DataFrame maps a column name to a Series of column data. For example, asking for the 'area' attribute returns the Series object containing the areas we saw earlier:

 In   [   22   ]:    states   [   'area'   ]   

Out[22]: California    423967          Florida       170312          Illinois      149995          New York      141297          Texas         695662          Name: area, dtype: int64

Notice the potential point of confusion here: in a two-dimensional NumPy array, data[0] will return the first row . For a DataFrame , data['col0'] will return the first column . Because of this, it is probably better to think about DataFrame s as generalized dictionaries rather than generalized arrays, though both ways of looking at the situation can be useful. We’ll explore more flexible means of indexing DataFrame s in .

Constructing DataFrame objects

A Pandas DataFrame can be constructed in a variety of ways. Here we’ll give several examples.

 In   [   23   ]:    pd   .   DataFrame   (   population   ,    columns   =   [   'population'   ])   

Out[23]:               population          California    38332521          Florida       19552860          Illinois      12882135          New York      19651127          Texas         26448193

 In   [   24   ]:    data    =    [{   'a'   :    i   ,    'b'   :    2    *    i   }                    for    i    in    range   (   3   )]            pd   .   DataFrame   (   data   )   

Out[24]:    a  b          0  0  0          1  1  2          2  2  4

 In   [   25   ]:    pd   .   DataFrame   ([{   'a'   :    1   ,    'b'   :    2   },    {   'b'   :    3   ,    'c'   :    4   }])   

Out[25]:    a    b  c          0  1.0  2  NaN          1  NaN  3  4.0

 In   [   26   ]:    pd   .   DataFrame   ({   'population'   :    population   ,                          'area'   :    area   })   

Out[26]:             area      population          California  423967    38332521          Florida     170312    19552860          Illinois    149995    12882135          New York    141297    19651127          Texas       695662    26448193

 In   [   27   ]:    pd   .   DataFrame   (   np   .   random   .   rand   (   3   ,    2   ),                         columns   =   [   'foo'   ,    'bar'   ],                         index   =   [   'a'   ,    'b'   ,    'c'   ])   

Out[27]:    foo       bar          a  0.865257  0.213169          b  0.442759  0.108267          c  0.047110  0.905718

 In   [   28   ]:    A    =    np   .   zeros   (   3   ,    dtype   =   [(   'A'   ,    'i8'   ),    (   'B'   ,    'f8'   )])            A   

Out[28]: array([(0, 0.0), (0, 0.0), (0, 0.0)],                dtype=[('A', '<i8'), ('B', '<f8')])

 In   [   29   ]:    pd   .   DataFrame   (   A   )   

Out[29]:    A  B          0  0  0.0          1  0  0.0          2  0  0.0

Index as immutable array

The Index object in many ways operates like an array. For example, we can use standard Python indexing notation to retrieve values or slices:

 In   [   31   ]:    ind   [   1   ]   

Out[31]: 3

 In   [   32   ]:    ind   [::   2   ]   

Out[32]: Int64Index([2, 5, 11], dtype='int64')

Index objects also have many of the attributes familiar from NumPy arrays:

 In   [   33   ]:    print   (   ind   .   size   ,    ind   .   shape   ,    ind   .   ndim   ,    ind   .   dtype   )   

5 (5,) 1 int64

One difference between Index objects and NumPy arrays is that indices are immutable — that is, they cannot be modified via the normal means:

 In   [   34   ]:    ind   [   1   ]    =    0   

 ---------------------------------------------------------------------------  TypeError                                 Traceback (most recent call last)  <ipython-input-34-40e631c82e8a> in <module>() ----> 1 ind[1] = 0   /Users/jakevdp/anaconda/lib/python3.5/site-packages/pandas/indexes/base.py ...    1243    1244     def __setitem__(self, key, value): -> 1245         raise TypeError("Index does not support mutable operations")    1246    1247     def __getitem__(self, key):   TypeError: Index does not support mutable operations

This immutability makes it safer to share indices between multiple DataFrame s and arrays, without the potential for side effects from inadvertent index modification.

Index as ordered set

Pandas objects are designed to facilitate operations such as joins across datasets, which depend on many aspects of set arithmetic. The Index object follows many of the conventions used by Python’s built-in set data structure, so that unions, intersections, differences, and other combinations can be computed in a familiar way:

 In   [   35   ]:    indA    =    pd   .   Index   ([   1   ,    3   ,    5   ,    7   ,    9   ])            indB    =    pd   .   Index   ([   2   ,    3   ,    5   ,    7   ,    11   ])   

 In   [   36   ]:    indA    &    indB     # intersection   

Out[36]: Int64Index([3, 5, 7], dtype='int64')

 In   [   37   ]:    indA    |    indB     # union   

Out[37]: Int64Index([1, 2, 3, 5, 7, 9, 11], dtype='int64')

 In   [   38   ]:    indA    ^    indB     # symmetric difference   

Out[38]: Int64Index([1, 2, 9, 11], dtype='int64')

These operations may also be accessed via object methods — for example, indA.intersection(indB) .

Series as dictionary

Like a dictionary, the Series object provides a mapping from a collection of keys to a collection of values:

 In   [   1   ]:    import    pandas    as    pd           data    =    pd   .   Series   ([   0.25   ,    0.5   ,    0.75   ,    1.0   ],                            index   =   [   'a'   ,    'b'   ,    'c'   ,    'd'   ])           data   

Out[1]: a    0.25         b    0.50         c    0.75         d    1.00         dtype: float64

 In   [   2   ]:    data   [   'b'   ]   

Out[2]: 0.5

We can also use dictionary-like Python expressions and methods to examine the keys/indices and values:

 In   [   3   ]:    'a'    in    data   

Out[3]: True

 In   [   4   ]:    data   .   keys   ()   

Out[4]: Index(['a', 'b', 'c', 'd'], dtype='object')

 In   [   5   ]:    list   (   data   .   items   ())   

Out[5]: [('a', 0.25), ('b', 0.5), ('c', 0.75), ('d', 1.0)]

Series objects can even be modified with a dictionary-like syntax. Just as you can extend a dictionary by assigning to a new key, you can extend a Series by assigning to a new index value:

 In   [   6   ]:    data   [   'e'   ]    =    1.25           data   

Out[6]: a    0.25         b    0.50         c    0.75         d    1.00         e    1.25         dtype: float64

This easy mutability of the objects is a convenient feature: under the hood, Pandas is making decisions about memory layout and data copying that might need to take place; the user generally does not need to worry about these issues.

Series as one-dimensional array

A Series builds on this dictionary-like interface and provides array-style item selection via the same basic mechanisms as NumPy arrays — that is, slices , masking , and fancy indexing . Examples of these are as follows:

 In   [   7   ]:    # slicing by explicit index           data   [   'a'   :   'c'   ]   

Out[7]: a    0.25         b    0.50         c    0.75         dtype: float64

 In   [   8   ]:    # slicing by implicit integer index           data   [   0   :   2   ]   

Out[8]: a    0.25         b    0.50         dtype: float64

 In   [   9   ]:    # masking           data   [(   data    >    0.3   )    &    (   data    <    0.8   )]   

Out[9]: b    0.50         c    0.75         dtype: float64

 In   [   10   ]:    # fancy indexing            data   [[   'a'   ,    'e'   ]]   

Out[10]: a    0.25          e    1.25          dtype: float64

Among these, slicing may be the source of the most confusion. Notice that when you are slicing with an explicit index (i.e., data['a':'c'] ), the final index is included in the slice, while when you’re slicing with an implicit index (i.e., data[0:2] ), the final index is excluded from the slice.

Indexers: loc, iloc, and ix

These slicing and indexing conventions can be a source of confusion. For example, if your Series has an explicit integer index, an indexing operation such as data[1] will use the explicit indices, while a slicing operation like data[1:3] will use the implicit Python-style index.

 In   [   11   ]:    data    =    pd   .   Series   ([   'a'   ,    'b'   ,    'c'   ],    index   =   [   1   ,    3   ,    5   ])            data   

Out[11]: 1    a          3    b          5    c          dtype: object

 In   [   12   ]:    # explicit index when indexing            data   [   1   ]   

Out[12]: 'a'

 In   [   13   ]:    # implicit index when slicing            data   [   1   :   3   ]   

Out[13]: 3    b          5    c          dtype: object

Because of this potential confusion in the case of integer indexes, Pandas provides some special indexer attributes that explicitly expose certain indexing schemes. These are not functional methods, but attributes that expose a particular slicing interface to the data in the Series .

First, the loc attribute allows indexing and slicing that always references the explicit index:

 In   [   14   ]:    data   .   loc   [   1   ]   

Out[14]: 'a'

 In   [   15   ]:    data   .   loc   [   1   :   3   ]   

Out[15]: 1    a          3    b          dtype: object

The iloc attribute allows indexing and slicing that always references the implicit Python-style index:

 In   [   16   ]:    data   .   iloc   [   1   ]   

Out[16]: 'b'

 In   [   17   ]:    data   .   iloc   [   1   :   3   ]   

Out[17]: 3    b          5    c          dtype: object

A third indexing attribute, ix , is a hybrid of the two, and for Series objects is equivalent to standard [] -based indexing. The purpose of the ix indexer will become more apparent in the context of DataFrame objects, which we will discuss in a moment.

One guiding principle of Python code is that “explicit is better than implicit.” The explicit nature of loc and iloc make them very useful in maintaining clean and readable code; especially in the case of integer indexes, I recommend using these both to make code easier to read and understand, and to prevent subtle bugs due to the mixed indexing/slicing convention.

DataFrame as a dictionary

The first analogy we will consider is the DataFrame as a dictionary of related Series objects. Let’s return to our example of areas and populations of states:

 In   [   18   ]:    area    =    pd   .   Series   ({   'California'   :    423967   ,    'Texas'   :    695662   ,                              'New York'   :    141297   ,    'Florida'   :    170312   ,                              'Illinois'   :    149995   })            pop    =    pd   .   Series   ({   'California'   :    38332521   ,    'Texas'   :    26448193   ,                             'New York'   :    19651127   ,    'Florida'   :    19552860   ,                             'Illinois'   :    12882135   })            data    =    pd   .   DataFrame   ({   'area'   :   area   ,    'pop'   :   pop   })            data   

Out[18]:             area    pop          California  423967  38332521          Florida     170312  19552860          Illinois    149995  12882135          New York    141297  19651127          Texas       695662  26448193

The individual Series that make up the columns of the DataFrame can be accessed via dictionary-style indexing of the column name:

 In   [   19   ]:    data   [   'area'   ]   

Out[19]: California    423967          Florida       170312          Illinois      149995          New York      141297          Texas         695662          Name: area, dtype: int64

Equivalently, we can use attribute-style access with column names that are strings:

 In   [   20   ]:    data   .   area   

Out[20]: California    423967          Florida       170312          Illinois      149995          New York      141297          Texas         695662          Name: area, dtype: int64

This attribute-style column access actually accesses the exact same object as the dictionary-style access:

 In   [   21   ]:    data   .   area    is    data   [   'area'   ]   

Out[21]: True

Though this is a useful shorthand, keep in mind that it does not work for all cases! For example, if the column names are not strings, or if the column names conflict with methods of the DataFrame , this attribute-style access is not possible. For example, the DataFrame has a pop() method, so data.pop will point to this rather than the "pop" column:

 In   [   22   ]:    data   .   pop    is    data   [   'pop'   ]   

Out[22]: False

In particular, you should avoid the temptation to try column assignment via attribute (i.e., use data['pop'] = z rather than data.pop = z ).

Like with the Series objects discussed earlier, this dictionary-style syntax can also be used to modify the object, in this case to add a new column:

 In   [   23   ]:    data   [   'density'   ]    =    data   [   'pop'   ]    /    data   [   'area'   ]            data   

Out[23]:             area    pop       density          California  423967  38332521   90.413926          Florida     170312  19552860  114.806121          Illinois    149995  12882135   85.883763          New York    141297  19651127  139.076746          Texas       695662  26448193   38.018740

This shows a preview of the straightforward syntax of element-by-element arithmetic between Series objects; we’ll dig into this further in .

DataFrame as two-dimensional array

As mentioned previously, we can also view the DataFrame as an enhanced two-dimensional array. We can examine the raw underlying data array using the values attribute:

 In   [   24   ]:    data   .   values   

Out[24]: array([[  4.23967000e+05,   3.83325210e+07,   9.04139261e+01],                 [  1.70312000e+05,   1.95528600e+07,   1.14806121e+02],                 [  1.49995000e+05,   1.28821350e+07,   8.58837628e+01],                 [  1.41297000e+05,   1.96511270e+07,   1.39076746e+02],                 [  6.95662000e+05,   2.64481930e+07,   3.80187404e+01]])

With this picture in mind, we can do many familiar array-like observations on the DataFrame itself. For example, we can transpose the full DataFrame to swap rows and columns:

 In   [   25   ]:    data   .   T   

Out[25]:          California    Florida       Illinois      New York      Texas area     4.239670e+05  1.703120e+05  1.499950e+05  1.412970e+05  6.956620e+05 pop      3.833252e+07  1.955286e+07  1.288214e+07  1.965113e+07  2.644819e+07 density  9.041393e+01  1.148061e+02  8.588376e+01  1.390767e+02  3.801874e+01

When it comes to indexing of DataFrame objects, however, it is clear that the dictionary-style indexing of columns precludes our ability to simply treat it as a NumPy array. In particular, passing a single index to an array accesses a row:

 In   [   26   ]:    data   .   values   [   0   ]   

Out[26]: array([  4.23967000e+05,   3.83325210e+07,   9.04139261e+01])

and passing a single “index” to a DataFrame accesses a column:

 In   [   27   ]:    data   [   'area'   ]   

Out[27]: California    423967          Florida       170312          Illinois      149995          New York      141297          Texas         695662          Name: area, dtype: int64

Thus for array-style indexing, we need another convention. Here Pandas again uses the loc , iloc , and ix indexers mentioned earlier. Using the iloc indexer, we can index the underlying array as if it is a simple NumPy array (using the implicit Python-style index), but the DataFrame index and column labels are maintained in the result:

 In   [   28   ]:    data   .   iloc   [:   3   ,    :   2   ]   

Out[28]:             area    pop          California  423967  38332521          Florida     170312  19552860          Illinois    149995  12882135

 In   [   29   ]:    data   .   loc   [:   'Illinois'   ,    :   'pop'   ]   

Out[29]:             area    pop          California  423967  38332521          Florida     170312  19552860          Illinois    149995  12882135

The ix indexer allows a hybrid of these two approaches:

 In   [   30   ]:    data   .   ix   [:   3   ,    :   'pop'   ]   

Out[30]:             area    pop          California  423967  38332521          Florida     170312  19552860          Illinois    149995  12882135

Keep in mind that for integer indices, the ix indexer is subject to the same potential sources of confusion as discussed for integer-indexed Series objects.

Any of the familiar NumPy-style data access patterns can be used within these indexers. For example, in the loc indexer we can combine masking and fancy indexing as in the following:

 In   [   31   ]:    data   .   loc   [   data   .   density    >    100   ,    [   'pop'   ,    'density'   ]]   

Out[31]:           pop       density          Florida   19552860  114.806121          New York  19651127  139.076746

Any of these indexing conventions may also be used to set or modify values; this is done in the standard way that you might be accustomed to from working with NumPy:

 In   [   32   ]:    data   .   iloc   [   0   ,    2   ]    =    90            data   

Out[32]:             area    pop       density          California  423967  38332521   90.000000          Florida     170312  19552860  114.806121          Illinois    149995  12882135   85.883763          New York    141297  19651127  139.076746          Texas       695662  26448193   38.018740

To build up your fluency in Pandas data manipulation, I suggest spending some time with a simple DataFrame and exploring the types of indexing, slicing, masking, and fancy indexing that are allowed by these various indexing approaches.

Additional indexing conventions

There are a couple extra indexing conventions that might seem at odds with the preceding discussion, but nevertheless can be very useful in practice. First, while indexing refers to columns, slicing refers to rows:

 In   [   33   ]:    data   [   'Florida'   :   'Illinois'   ]   

Out[33]:           area    pop       density          Florida   170312  19552860  114.806121          Illinois  149995  12882135   85.883763

Such slices can also refer to rows by number rather than by index:

 In   [   34   ]:    data   [   1   :   3   ]   

Out[34]:           area    pop       density          Florida   170312  19552860  114.806121          Illinois  149995  12882135   85.883763

Similarly, direct masking operations are also interpreted row-wise rather than column-wise:

 In   [   35   ]:    data   [   data   .   density    >    100   ]   

Out[35]:           area    pop       density          Florida   170312  19552860  114.806121          New York  141297  19651127  139.076746

These two conventions are syntactically similar to those on a NumPy array, and while these may not precisely fit the mold of the Pandas conventions, they are nevertheless quite useful in practice.

Index alignment in Series

As an example, suppose we are combining two different data sources, and find only the top three US states by area and the top three US states by population :

 In   [   6   ]:    area    =    pd   .   Series   ({   'Alaska'   :    1723337   ,    'Texas'   :    695662   ,                             'California'   :    423967   },    name   =   'area'   )           population    =    pd   .   Series   ({   'California'   :    38332521   ,    'Texas'   :    26448193   ,                                   'New York'   :    19651127   },    name   =   'population'   )   

Let’s see what happens when we divide these to compute the population density:

 In   [   7   ]:    population    /    area   

Out[7]: Alaska              NaN         California    90.413926         New York            NaN         Texas         38.018740         dtype: float64

The resulting array contains the union of indices of the two input arrays, which we could determine using standard Python set arithmetic on these indices:

 In   [   8   ]:    area   .   index    |    population   .   index   

Out[8]: Index(['Alaska', 'California', 'New York', 'Texas'], dtype='object')

Any item for which one or the other does not have an entry is marked with NaN , or “Not a Number,” which is how Pandas marks missing data (see further discussion of missing data in ). This index matching is implemented this way for any of Python’s built-in arithmetic expressions; any missing values are filled in with NaN by default:

 In   [   9   ]:    A    =    pd   .   Series   ([   2   ,    4   ,    6   ],    index   =   [   0   ,    1   ,    2   ])           B    =    pd   .   Series   ([   1   ,    3   ,    5   ],    index   =   [   1   ,    2   ,    3   ])           A    +    B   

Out[9]: 0    NaN         1    5.0         2    9.0         3    NaN         dtype: float64

If using NaN values is not the desired behavior, we can modify the fill value using appropriate object methods in place of the operators. For example, calling A.add(B) is equivalent to calling A + B , but allows optional explicit specification of the fill value for any elements in A or B that might be missing:

 In   [   10   ]:    A   .   add   (   B   ,    fill_value   =   0   )   

Out[10]: 0    2.0          1    5.0          2    9.0          3    5.0          dtype: float64

Index alignment in DataFrame

A similar type of alignment takes place for both columns and indices when you are performing operations on DataFrame s:

 In   [   11   ]:    A    =    pd   .   DataFrame   (   rng   .   randint   (   0   ,    20   ,    (   2   ,    2   )),                             columns   =   list   (   'AB'   ))            A   

Out[11]:    A   B          0  1  11          1  5   1

 In   [   12   ]:    B    =    pd   .   DataFrame   (   rng   .   randint   (   0   ,    10   ,    (   3   ,    3   )),                             columns   =   list   (   'BAC'   ))            B   

Out[12]:    B  A  C          0  4  0  9          1  5  8  0          2  9  2  6

 In   [   13   ]:    A    +    B   

Out[13]:       A     B   C          0   1.0  15.0 NaN          1  13.0   6.0 NaN          2   NaN   NaN NaN

Notice that indices are aligned correctly irrespective of their order in the two objects, and indices in the result are sorted. As was the case with Series , we can use the associated object’s arithmetic method and pass any desired fill_value to be used in place of missing entries. Here we’ll fill with the mean of all values in A (which we compute by first stacking the rows of A ):

 In   [   14   ]:    fill    =    A   .   stack   ()   .   mean   ()            A   .   add   (   B   ,    fill_value   =   fill   )   

Out[14]:       A     B     C          0   1.0  15.0  13.5          1  13.0   6.0   4.5          2   6.5  13.5  10.5

lists Python operators and their equivalent Pandas object methods.

None: Pythonic missing data

The first sentinel value used by Pandas is None , a Python singleton object that is often used for missing data in Python code. Because None is a Python object, it cannot be used in any arbitrary NumPy/Pandas array, but only in arrays with data type 'object' (i.e., arrays of Python objects):

 In   [   1   ]:    import    numpy    as    np           import    pandas    as    pd   

 In   [   2   ]:    vals1    =    np   .   array   ([   1   ,    None   ,    3   ,    4   ])           vals1   

Out[2]: array([1, None, 3, 4], dtype=object)

This dtype=object means that the best common type representation NumPy could infer for the contents of the array is that they are Python objects. While this kind of object array is useful for some purposes, any operations on the data will be done at the Python level, with much more overhead than the typically fast operations seen for arrays with native types:

 In   [   3   ]:    for    dtype    in    [   'object'   ,    'int'   ]:               print   (   "dtype ="   ,    dtype   )               %   timeit    np   .   arange   (   1E6   ,    dtype   =   dtype   )   .   sum   ()               print   ()   

dtype = object 10 loops, best of 3: 78.2 ms per loop  dtype = int 100 loops, best of 3: 3.06 ms per loop

The use of Python objects in an array also means that if you perform aggregations like sum() or min() across an array with a None value, you will generally get an error:

 In   [   4   ]:    vals1   .   sum   ()   

 TypeError                                 Traceback (most recent call last)  <ipython-input-4-749fd8ae6030> in <module>() ----> 1 vals1.sum()   /Users/jakevdp/anaconda/lib/python3.5/site-packages/numpy/core/_methods.py ...      30      31 def _sum(a, axis=None, dtype=None, out=None, keepdims=False): ---> 32     return umr_sum(a, axis, dtype, out, keepdims)      33      34 def _prod(a, axis=None, dtype=None, out=None, keepdims=False):   TypeError: unsupported operand type(s) for +: 'int' and 'NoneType'

This reflects the fact that addition between an integer and None is undefined.

NaN: Missing numerical data

The other missing data representation, NaN (acronym for Not a Number ), is different; it is a special floating-point value recognized by all systems that use the standard IEEE floating-point representation:

 In   [   5   ]:    vals2    =    np   .   array   ([   1   ,    np   .   nan   ,    3   ,    4   ])           vals2   .   dtype   

Out[5]: dtype('float64')

Notice that NumPy chose a native floating-point type for this array: this means that unlike the object array from before, this array supports fast operations pushed into compiled code. You should be aware that NaN is a bit like a data virus — it infects any other object it touches. Regardless of the operation, the result of arithmetic with NaN will be another NaN :

 In   [   6   ]:    1    +    np   .   nan   

Out[6]: nan

 In   [   7   ]:    0    *     np   .   nan   

Out[7]: nan

Note that this means that aggregates over the values are well defined (i.e., they don’t result in an error) but not always useful:

 In   [   8   ]:    vals2   .   sum   (),    vals2   .   min   (),    vals2   .   max   ()   

Out[8]: (nan, nan, nan)

NumPy does provide some special aggregations that will ignore these missing values:

 In   [   9   ]:    np   .   nansum   (   vals2   ),    np   .   nanmin   (   vals2   ),    np   .   nanmax   (   vals2   )   

Out[9]: (8.0, 1.0, 4.0)

Keep in mind that NaN is specifically a floating-point value; there is no equivalent NaN value for integers, strings, or other types.

NaN and None in Pandas

NaN and None both have their place, and Pandas is built to handle the two of them nearly interchangeably, converting between them where appropriate:

 In   [   10   ]:    pd   .   Series   ([   1   ,    np   .   nan   ,    2   ,    None   ])   

Out[10]: 0    1.0          1    NaN          2    2.0          3    NaN          dtype: float64

For types that don’t have an available sentinel value, Pandas automatically type-casts when NA values are present. For example, if we set a value in an integer array to np.nan , it will automatically be upcast to a floating-point type to accommodate the NA:

 In   [   11   ]:    x    =    pd   .   Series   (   range   (   2   ),    dtype   =   int   )            x   

Out[11]: 0    0          1    1          dtype: int64

 In   [   12   ]:    x   [   0   ]    =    None            x   

Out[12]: 0    NaN          1    1.0          dtype: float64

Notice that in addition to casting the integer array to floating point, Pandas automatically converts the None to a NaN value. (Be aware that there is a proposal to add a native integer NA to Pandas in the future; as of this writing, it has not been included.)

While this type of magic may feel a bit hackish compared to the more unified approach to NA values in domain-specific languages like R, the Pandas sentinel/casting approach works quite well in practice and in my experience only rarely causes issues.

lists the upcasting conventions in Pandas when NA values are introduced.

Keep in mind that in Pandas, string data is always stored with an object dtype.

Detecting null values

Pandas data structures have two useful methods for detecting null data: isnull() and notnull() . Either one will return a Boolean mask over the data. For example:

 In   [   13   ]:    data    =    pd   .   Series   ([   1   ,    np   .   nan   ,    'hello'   ,    None   ])   

 In   [   14   ]:    data   .   isnull   ()   

Out[14]: 0    False          1     True          2    False          3     True          dtype: bool

As mentioned in , Boolean masks can be used directly as a Series or DataFrame index:

 In   [   15   ]:    data   [   data   .   notnull   ()]   

Out[15]: 0        1          2    hello          dtype: object

The isnull() and notnull() methods produce similar Boolean results for DataFrame s.

Dropping null values

In addition to the masking used before, there are the convenience methods, dropna() (which removes NA values) and fillna() (which fills in NA values). For a Series , the result is straightforward:

 In   [   16   ]:    data   .   dropna   ()   

Out[16]: 0        1          2    hello          dtype: object

For a DataFrame , there are more options. Consider the following DataFrame :

 In   [   17   ]:    df    =    pd   .   DataFrame   ([[   1   ,         np   .   nan   ,    2   ],                               [   2   ,         3   ,         5   ],                               [   np   .   nan   ,    4   ,         6   ]])            df   

Out[17]:      0    1  2          0  1.0  NaN  2          1  2.0  3.0  5          2  NaN  4.0  6

We cannot drop single values from a DataFrame ; we can only drop full rows or full columns. Depending on the application, you might want one or the other, so dropna() gives a number of options for a DataFrame .

By default, dropna() will drop all rows in which any null value is present:

 In   [   18   ]:    df   .   dropna   ()   

Out[18]:      0    1  2          1  2.0  3.0  5

Alternatively, you can drop NA values along a different axis; axis=1 drops all columns containing a null value:

 In   [   19   ]:    df   .   dropna   (   axis   =   'columns'   )   

Out[19]:    2          0  2          1  5          2  6

But this drops some good data as well; you might rather be interested in dropping rows or columns with all NA values, or a majority of NA values. This can be specified through the how or thresh parameters, which allow fine control of the number of nulls to allow through.

The default is how='any' , such that any row or column (depending on the axis keyword) containing a null value will be dropped. You can also specify how='all' , which will only drop rows/columns that are all null values:

 In   [   20   ]:    df   [   3   ]    =    np   .   nan            df   

Out[20]:      0    1  2   3          0  1.0  NaN  2 NaN          1  2.0  3.0  5 NaN          2  NaN  4.0  6 NaN

 In   [   21   ]:    df   .   dropna   (   axis   =   'columns'   ,    how   =   'all'   )   

Out[21]:      0    1  2          0  1.0  NaN  2          1  2.0  3.0  5          2  NaN  4.0  6

For finer-grained control, the thresh parameter lets you specify a minimum number of non-null values for the row/column to be kept:

 In   [   22   ]:    df   .   dropna   (   axis   =   'rows'   ,    thresh   =   3   )   

Out[22]:      0    1  2   3          1  2.0  3.0  5 NaN

Here the first and last row have been dropped, because they contain only two non-null values.

Filling null values

Sometimes rather than dropping NA values, you’d rather replace them with a valid value. This value might be a single number like zero, or it might be some sort of imputation or interpolation from the good values. You could do this in-place using the isnull() method as a mask, but because it is such a common operation Pandas provides the fillna() method, which returns a copy of the array with the null values replaced.

Consider the following Series :

 In   [   23   ]:    data    =    pd   .   Series   ([   1   ,    np   .   nan   ,    2   ,    None   ,    3   ],    index   =   list   (   'abcde'   ))            data   

Out[23]: a    1.0          b    NaN          c    2.0          d    NaN          e    3.0          dtype: float64

We can fill NA entries with a single value, such as zero:

 In   [   24   ]:    data   .   fillna   (   0   )   

Out[24]: a    1.0          b    0.0          c    2.0          d    0.0          e    3.0          dtype: float64

We can specify a forward-fill to propagate the previous value forward:

 In   [   25   ]:    # forward-fill            data   .   fillna   (   method   =   'ffill'   )   

Out[25]: a    1.0          b    1.0          c    2.0          d    2.0          e    3.0          dtype: float64

Or we can specify a back-fill to propagate the next values backward:

 In   [   26   ]:    # back-fill            data   .   fillna   (   method   =   'bfill'   )   

Out[26]: a    1.0          b    2.0          c    2.0          d    3.0          e    3.0          dtype: float64

For DataFrame s, the options are similar, but we can also specify an axis along which the fills take place:

 In   [   27   ]:    df   

Out[27]:      0    1  2   3          0  1.0  NaN  2 NaN          1  2.0  3.0  5 NaN          2  NaN  4.0  6 NaN

 In   [   28   ]:    df   .   fillna   (   method   =   'ffill'   ,    axis   =   1   )   

Out[28]:      0    1    2    3          0  1.0  1.0  2.0  2.0          1  2.0  3.0  5.0  5.0          2  NaN  4.0  6.0  6.0

Notice that if a previous value is not available during a forward fill, the NA value remains .

The bad way

Suppose you would like to track data about states from two different years. Using the Pandas tools we’ve already covered, you might be tempted to simply use Python tuples as keys:

 In   [   2   ]:    index    =    [(   'California'   ,    2000   ),    (   'California'   ,    2010   ),                    (   'New York'   ,    2000   ),    (   'New York'   ,    2010   ),                    (   'Texas'   ,    2000   ),    (   'Texas'   ,    2010   )]           populations    =    [   33871648   ,    37253956   ,                          18976457   ,    19378102   ,                          20851820   ,    25145561   ]           pop    =    pd   .   Series   (   populations   ,    index   =   index   )           pop   

Out[2]: (California, 2000)    33871648         (California, 2010)    37253956         (New York, 2000)      18976457         (New York, 2010)      19378102         (Texas, 2000)         20851820         (Texas, 2010)         25145561         dtype: int64

With this indexing scheme, you can straightforwardly index or slice the series based on this multiple index:

 In   [   3   ]:    pop   [(   'California'   ,    2010   ):(   'Texas'   ,    2000   )]   

Out[3]: (California, 2010)    37253956         (New York, 2000)      18976457         (New York, 2010)      19378102         (Texas, 2000)         20851820         dtype: int64

But the convenience ends there. For example, if you need to select all values from 2010, you’ll need to do some messy (and potentially slow) munging to make it happen :

 In   [   4   ]:    pop   [[   i    for    i    in    pop   .   index    if    i   [   1   ]    ==    2010   ]]   

Out[4]: (California, 2010)    37253956         (New York, 2010)      19378102         (Texas, 2010)         25145561         dtype: int64

This produces the desired result, but is not as clean (or as efficient for large datasets) as the slicing syntax we’ve grown to love in Pandas.

The better way: Pandas MultiIndex

Fortunately, Pandas provides a better way. Our tuple-based indexing is essentially a rudimentary multi-index, and the Pandas MultiIndex type gives us the type of operations we wish to have. We can create a multi-index from the tuples as follows:

 In   [   5   ]:    index    =    pd   .   MultiIndex   .   from_tuples   (   index   )           index   

Out[5]: MultiIndex(levels=[['California', 'New York', 'Texas'], [2000, 2010]],                    labels=[[0, 0, 1, 1, 2, 2], [0, 1, 0, 1, 0, 1]])

Notice that the MultiIndex contains multiple levels of indexing — in this case, the state names and the years, as well as multiple labels for each data point which encode these levels.

If we reindex our series with this MultiIndex , we see the hierarchical representation of the data:

 In   [   6   ]:    pop    =    pop   .   reindex   (   index   )           pop   

Out[6]: California  2000    33871648                     2010    37253956         New York    2000    18976457                     2010    19378102         Texas       2000    20851820                     2010    25145561         dtype: int64

Here the first two columns of the Series representation show the multiple index values, while the third column shows the data. Notice that some entries are missing in the first column: in this multi-index representation, any blank entry indicates the same value as the line above it.

Now to access all data for which the second index is 2010, we can simply use the Pandas slicing notation:

 In   [   7   ]:    pop   [:,    2010   ]   

Out[7]: California    37253956         New York      19378102         Texas         25145561         dtype: int64

The result is a singly indexed array with just the keys we’re interested in. This syntax is much more convenient (and the operation is much more efficient!) than the home-spun tuple-based multi-indexing solution that we started with. We’ll now further discuss this sort of indexing operation on hierarchically indexed data.

MultiIndex as extra dimension

You might notice something else here: we could easily have stored the same data using a simple DataFrame with index and column labels. In fact, Pandas is built with this equivalence in mind. The unstack() method will quickly convert a multiply-indexed Series into a conventionally indexed DataFrame :

 In   [   8   ]:    pop_df    =    pop   .   unstack   ()           pop_df   

Out[8]:                 2000      2010         California  33871648  37253956         New York    18976457  19378102         Texas       20851820  25145561

Naturally, the stack() method provides the opposite operation:

 In   [   9   ]:    pop_df   .   stack   ()   

Out[9]:  California  2000    33871648                      2010    37253956          New York    2000    18976457                      2010    19378102          Texas       2000    20851820                      2010    25145561          dtype: int64

Seeing this, you might wonder why would we would bother with hierarchical indexing at all. The reason is simple: just as we were able to use multi-indexing to represent two-dimensional data within a one-dimensional Series , we can also use it to represent data of three or more dimensions in a Series or DataFrame . Each extra level in a multi-index represents an extra dimension of data; taking advantage of this property gives us much more flexibility in the types of data we can represent. Concretely, we might want to add another column of demographic data for each state at each year (say, population under 18); with a MultiIndex this is as easy as adding another column to the DataFrame :

 In   [   10   ]:    pop_df    =    pd   .   DataFrame   ({   'total'   :    pop   ,                                   'under18'   :    [   9267089   ,    9284094   ,                                               4687374   ,    4318033   ,                                               5906301   ,    6879014   ]})            pop_df   

Out[10]:                     total  under18          California 2000  33871648  9267089                     2010  37253956  9284094          New York   2000  18976457  4687374                     2010  19378102  4318033          Texas      2000  20851820  5906301                     2010  25145561  6879014

In addition, all the ufuncs and other functionality discussed in work with hierarchical indices as well. Here we compute the fraction of people under 18 by year, given the above data:

 In   [   11   ]:    f_u18    =    pop_df   [   'under18'   ]    /    pop_df   [   'total'   ]            f_u18   .   unstack   ()   

Out[11]:                 2000      2010          California  0.273594  0.249211          New York    0.247010  0.222831          Texas       0.283251  0.273568

This allows us to easily and quickly manipulate and explore even high-dimensional data.

Explicit MultiIndex constructors

For more flexibility in how the index is constructed, you can instead use the class method constructors available in the pd.MultiIndex . For example, as we did before, you can construct the MultiIndex from a simple list of arrays, giving the index values within each level:

 In   [   14   ]:    pd   .   MultiIndex   .   from_arrays   ([[   'a'   ,    'a'   ,    'b'   ,    'b'   ],    [   1   ,    2   ,    1   ,    2   ]])   

Out[14]: MultiIndex(levels=[['a', 'b'], [1, 2]],                     labels=[[0, 0, 1, 1], [0, 1, 0, 1]])

You can construct it from a list of tuples, giving the multiple index values of each point:

 In   [   15   ]:    pd   .   MultiIndex   .   from_tuples   ([(   'a'   ,    1   ),    (   'a'   ,    2   ),    (   'b'   ,    1   ),    (   'b'   ,    2   )])   

Out[15]: MultiIndex(levels=[['a', 'b'], [1, 2]],                     labels=[[0, 0, 1, 1], [0, 1, 0, 1]])

You can even construct it from a Cartesian product of single indices:

 In   [   16   ]:    pd   .   MultiIndex   .   from_product   ([[   'a'   ,    'b'   ],    [   1   ,    2   ]])   

Out[16]: MultiIndex(levels=[['a', 'b'], [1, 2]],                     labels=[[0, 0, 1, 1], [0, 1, 0, 1]])

Similarly, you can construct the MultiIndex directly using its internal encoding by passing levels (a list of lists containing available index values for each level) and labels (a list of lists that reference these labels):

 In   [   17   ]:    pd   .   MultiIndex   (   levels   =   [[   'a'   ,    'b'   ],    [   1   ,    2   ]],                           labels   =   [[   0   ,    0   ,    1   ,    1   ],    [   0   ,    1   ,    0   ,    1   ]])   

Out[17]: MultiIndex(levels=[['a', 'b'], [1, 2]],                     labels=[[0, 0, 1, 1], [0, 1, 0, 1]])

You can pass any of these objects as the index argument when creating a Series or DataFrame , or to the reindex method of an existing Series or DataFrame .

MultiIndex level names

Sometimes it is convenient to name the levels of the MultiIndex . You can accomplish this by passing the names argument to any of the above MultiIndex constructors, or by setting the names attribute of the index after the fact:

 In   [   18   ]:    pop   .   index   .   names    =    [   'state'   ,    'year'   ]            pop   

Out[18]: state       year          California  2000    33871648                      2010    37253956          New York    2000    18976457                      2010    19378102          Texas       2000    20851820                      2010    25145561          dtype: int64

With more involved datasets, this can be a useful way to keep track of the meaning of various index values.

MultiIndex for columns

In a DataFrame , the rows and columns are completely symmetric, and just as the rows can have multiple levels of indices, the columns can have multiple levels as well. Consider the following, which is a mock-up of some (somewhat realistic) medical data:

 In   [   19   ]:    # hierarchical indices and columns    index    =    pd   .   MultiIndex   .   from_product   ([[   2013   ,    2014   ],    [   1   ,    2   ]],                                       names   =   [   'year'   ,    'visit'   ])    columns    =    pd   .   MultiIndex   .   from_product   ([[   'Bob'   ,    'Guido'   ,    'Sue'   ],    [   'HR'   ,    'Temp'   ]],                                         names   =   [   'subject'   ,    'type'   ])     # mock some data    data    =    np   .   round   (   np   .   random   .   randn   (   4   ,    6   ),    1   )    data   [:,    ::   2   ]    *=    10    data    +=    37     # create the DataFrame    health_data    =    pd   .   DataFrame   (   data   ,    index   =   index   ,    columns   =   columns   )    health_data   

Out[19]: subject      Bob       Guido         Sue          type          HR  Temp    HR  Temp    HR  Temp          year visit          2013 1      31.0  38.7  32.0  36.7  35.0  37.2               2      44.0  37.7  50.0  35.0  29.0  36.7          2014 1      30.0  37.4  39.0  37.8  61.0  36.9               2      47.0  37.8  48.0  37.3  51.0  36.5

Here we see where the multi-indexing for both rows and columns can come in very handy. This is fundamentally four-dimensional data, where the dimensions are the subject, the measurement type, the year, and the visit number. With this in place we can, for example, index the top-level column by the person’s name and get a full DataFrame containing just that person’s information:

 In   [   20   ]:    health_data   [   'Guido'   ]   

Out[20]: type          HR  Temp          year visit          2013 1      32.0  36.7               2      50.0  35.0          2014 1      39.0  37.8               2      48.0  37.3

For complicated records containing multiple labeled measurements across multiple times for many subjects (people, countries, cities, etc.), use of hierarchical rows and columns can be extremely convenient!

Multiply indexed Series

Consider the multiply indexed Series of state populations we saw earlier:

 In   [   21   ]:    pop   

Out[21]: state       year          California  2000    33871648                      2010    37253956          New York    2000    18976457                      2010    19378102          Texas       2000    20851820                      2010    25145561          dtype: int64

We can access single elements by indexing with multiple terms:

 In   [   22   ]:    pop   [   'California'   ,    2000   ]   

Out[22]: 33871648

The MultiIndex also supports partial indexing , or indexing just one of the levels in the index. The result is another Series , with the lower-level indices maintained:

 In   [   23   ]:    pop   [   'California'   ]   

Out[23]: year          2000    33871648          2010    37253956          dtype: int64

Partial slicing is available as well, as long as the MultiIndex is sorted (see discussion in ):

 In   [   24   ]:    pop   .   loc   [   'California'   :   'New York'   ]   

Out[24]: state       year          California  2000    33871648                      2010    37253956          New York    2000    18976457                      2010    19378102          dtype: int64

With sorted indices, we can perform partial indexing on lower levels by passing an empty slice in the first index:

 In   [   25   ]:    pop   [:,    2000   ]   

Out[25]: state          California    33871648          New York      18976457          Texas         20851820          dtype: int64

Other types of indexing and selection (discussed in ) work as well; for example, selection based on Boolean masks:

 In   [   26   ]:    pop   [   pop    >    22000000   ]   

Out[26]: state       year          California  2000    33871648                      2010    37253956          Texas       2010    25145561          dtype: int64

Selection based on fancy indexing also works:

 In   [   27   ]:    pop   [[   'California'   ,    'Texas'   ]]   

Out[27]: state       year          California  2000    33871648                      2010    37253956          Texas       2000    20851820                      2010    25145561          dtype: int64

Multiply indexed DataFrames

A multiply indexed DataFrame behaves in a similar manner. Consider our toy medical DataFrame from before:

 In   [   28   ]:    health_data   

Out[28]: subject      Bob       Guido         Sue          type          HR  Temp    HR  Temp    HR  Temp          year visit          2013 1      31.0  38.7  32.0  36.7  35.0  37.2               2      44.0  37.7  50.0  35.0  29.0  36.7          2014 1      30.0  37.4  39.0  37.8  61.0  36.9               2      47.0  37.8  48.0  37.3  51.0  36.5

Remember that columns are primary in a DataFrame , and the syntax used for multiply indexed Series applies to the columns. For example, we can recover Guido’s heart rate data with a simple operation:

 In   [   29   ]:    health_data   [   'Guido'   ,    'HR'   ]   

Out[29]: year  visit          2013  1        32.0                2        50.0          2014  1        39.0                2        48.0          Name: (Guido, HR), dtype: float64

Also, as with the single-index case, we can use the loc , iloc , and ix indexers introduced in . For example:

 In   [   30   ]:    health_data   .   iloc   [:   2   ,    :   2   ]   

Out[30]: subject      Bob          type          HR  Temp          year visit          2013 1      31.0  38.7               2      44.0  37.7

These indexers provide an array-like view of the underlying two-dimensional data, but each individual index in loc or iloc can be passed a tuple of multiple indices. For example:

 In   [   31   ]:    health_data   .   loc   [:,    (   'Bob'   ,    'HR'   )]   

Out[31]: year  visit          2013  1        31.0                2        44.0          2014  1        30.0                2        47.0          Name: (Bob, HR), dtype: float64

Working with slices within these index tuples is not especially convenient; trying to create a slice within a tuple will lead to a syntax error:

 In   [   32   ]:    health_data   .   loc   [(:,    1   ),    (:,    'HR'   )]   

  File "<ipython-input-32-8e3cc151e316>", line 1     health_data.loc[(:, 1), (:, 'HR')]                      ^ SyntaxError: invalid syntax

You could get around this by building the desired slice explicitly using Python’s built-in slice() function, but a better way in this context is to use an IndexSlice object, which Pandas provides for precisely this situation. For example:

 In   [   33   ]:    idx    =    pd   .   IndexSlice            health_data   .   loc   [   idx   [:,    1   ],    idx   [:,    'HR'   ]]   

Out[33]: subject      Bob Guido   Sue          type          HR    HR    HR          year visit          2013 1      31.0  32.0  35.0          2014 1      30.0  39.0  61.0

There are so many ways to interact with data in multiply indexed Series and DataFrame s, and as with many tools in this book the best way to become familiar with them is to try them out!

Sorted and unsorted indices

Earlier, we briefly mentioned a caveat, but we should emphasize it more here. Many of the MultiIndex slicing operations will fail if the index is not sorted. Let’s take a look at this here.

We’ll start by creating some simple multiply indexed data where the indices are not lexographically sorted :

 In   [   34   ]:    index    =    pd   .   MultiIndex   .   from_product   ([[   'a'   ,    'c'   ,    'b'   ],    [   1   ,    2   ]])            data    =    pd   .   Series   (   np   .   random   .   rand   (   6   ),    index   =   index   )            data   .   index   .   names    =    [   'char'   ,    'int'   ]            data   

Out[34]: char  int          a     1      0.003001                2      0.164974          c     1      0.741650                2      0.569264          b     1      0.001693                2      0.526226          dtype: float64

If we try to take a partial slice of this index, it will result in an error:

 In   [   35   ]:    try   :                data   [   'a'   :   'b'   ]            except    KeyError    as    e   :                print   (   type   (   e   ))                print   (   e   )   

<class 'KeyError'> 'Key length (1) was greater than MultiIndex lexsort depth (0)'

Although it is not entirely clear from the error message, this is the result of the MultiIndex not being sorted. For various reasons, partial slices and other similar operations require the levels in the MultiIndex to be in sorted (i.e., lexographical) order. Pandas provides a number of convenience routines to perform this type of sorting; examples are the sort_index() and sortlevel() methods of the DataFrame . We’ll use the simplest, sort_index() , here:

 In   [   36   ]:    data    =    data   .   sort_index   ()            data   

Out[36]: char  int          a     1      0.003001                2      0.164974          b     1      0.001693                2      0.526226          c     1      0.741650                2      0.569264          dtype: float64

With the index sorted in this way, partial slicing will work as expected:

 In   [   37   ]:    data   [   'a'   :   'b'   ]   

Out[37]: char  int          a     1      0.003001                2      0.164974          b     1      0.001693                2      0.526226          dtype: float64

Stacking and unstacking indices

As we saw briefly before, it is possible to convert a dataset from a stacked multi-index to a simple two-dimensional representation, optionally specifying the level to use:

 In   [   38   ]:    pop   .   unstack   (   level   =   0   )   

Out[38]:  state  California   New York     Texas           year           2000     33871648   18976457  20851820           2010     37253956   19378102  25145561

 In   [   39   ]:    pop   .   unstack   (   level   =   1   )   

Out[39]: year            2000      2010          state          California  33871648  37253956          New York    18976457  19378102          Texas       20851820  25145561

The opposite of unstack() is stack() , which here can be used to recover the original series:

 In   [   40   ]:    pop   .   unstack   ()   .   stack   ()   

Out[40]: state       year          California  2000    33871648                      2010    37253956          New York    2000    18976457                      2010    19378102          Texas       2000    20851820                      2010    25145561          dtype: int64

Index setting and resetting

Another way to rearrange hierarchical data is to turn the index labels into columns; this can be accomplished with the reset_index method. Calling this on the population dictionary will result in a DataFrame with a state and year column holding the information that was formerly in the index. For clarity, we can optionally specify the name of the data for the column representation:

 In   [   41   ]:    pop_flat    =    pop   .   reset_index   (   name   =   'population'   )            pop_flat   

Out[41]:         state  year  population          0  California  2000    33871648          1  California  2010    37253956          2    New York  2000    18976457          3    New York  2010    19378102          4       Texas  2000    20851820          5       Texas  2010    25145561

Often when you are working with data in the real world, the raw input data looks like this and it’s useful to build a MultiIndex from the column values. This can be done with the set_index method of the DataFrame , which returns a multiply indexed DataFrame :

 In   [   42   ]:    pop_flat   .   set_index   ([   'state'   ,    'year'   ])   

Out[42]:                  population          state      year          California 2000    33871648                     2010    37253956          New York   2000    18976457                     2010    19378102          Texas      2000    20851820                     2010    25145561

In practice, I find this type of reindexing to be one of the more useful patterns when I encounter real-world datasets.