# Reshaping and Pivot Tables

## Reshaping by pivoting DataFrame objects

Data is often stored in CSV files or databases in so-called “stacked” or “record” format:

    In [1]: df
    Out[1]:
             date variable     value
    0  2000-01-03        A  0.469112
    1  2000-01-04        A -0.282863
    2  2000-01-05        A -1.509059
    3  2000-01-03        B -1.135632
    4  2000-01-04        B  1.212112
    5  2000-01-05        B -0.173215
    6  2000-01-03        C  0.119209
    7  2000-01-04        C -1.044236
    8  2000-01-05        C -0.861849
    9  2000-01-03        D -2.104569
    10 2000-01-04        D -0.494929
    11 2000-01-05        D  1.071804

For the curious here is how the above DataFrame was created:

    import pandas.util.testing as tm; tm.N = 3
    def unpivot(frame):
        N, K = frame.shape
        data = {'value' : frame.values.ravel('F'),
                'variable' : np.asarray(frame.columns).repeat(N),
                'date' : np.tile(np.asarray(frame.index), K)}
        return pd.DataFrame(data, columns=['date', 'variable', 'value'])
    df = unpivot(tm.makeTimeDataFrame())

To select out everything for variable `A` we could do:

    In [2]: df[df['variable'] == 'A']
    Out[2]:
            date variable     value
    0 2000-01-03        A  0.469112
    1 2000-01-04        A -0.282863
    2 2000-01-05        A -1.509059

But suppose we wish to do time series operations with the variables. A better representation would be where the `columns` are the unique variables and an `index` of dates identifies individual observations. To reshape the data into this form, use the `pivot` function:

    In [3]: df.pivot(index='date', columns='variable', values='value')
    Out[3]:
    variable           A         B         C         D
    date
    2000-01-03  0.469112 -1.135632  0.119209 -2.104569
    2000-01-04 -0.282863  1.212112 -1.044236 -0.494929
    2000-01-05 -1.509059 -0.173215 -0.861849  1.071804

If the `values` argument is omitted, and the input DataFrame has more than one column of values which are not used as column or index inputs to `pivot`, then the resulting “pivoted” DataFrame will have hierarchical columns whose topmost level indicates the respective value column:

    In [4]: df['value2'] = df['value'] * 2

    In [5]: pivoted = df.pivot('date', 'variable')

    In [6]: pivoted
    Out[6]:
                   value                                  value2            \
    variable           A         B         C         D         A         B
    date
    2000-01-03  0.469112 -1.135632  0.119209 -2.104569  0.938225 -2.271265
    2000-01-04 -0.282863  1.212112 -1.044236 -0.494929 -0.565727  2.424224
    2000-01-05 -1.509059 -0.173215 -0.861849  1.071804 -3.018117 -0.346429

    variable           C         D
    date
    2000-01-03  0.238417 -4.209138
    2000-01-04 -2.088472 -0.989859
    2000-01-05 -1.723698  2.143608

You of course can then select subsets from the pivoted DataFrame:

    In [7]: pivoted['value2']
    Out[7]:
    variable           A         B         C         D
    date
    2000-01-03  0.938225 -2.271265  0.238417 -4.209138
    2000-01-04 -0.565727  2.424224 -2.088472 -0.989859
    2000-01-05 -3.018117 -0.346429 -1.723698  2.143608

Note that this returns a view on the underlying data in the case where the data are homogeneously-typed.

## Reshaping by stacking and unstacking

Closely related to the `pivot` function are the related `stack` and `unstack` functions currently available on Series and DataFrame. These functions are designed to work together with `MultiIndex` objects (see the section on hierarchical indexing). Here are essentially what these functions do:

> - `stack`: “pivot” a level of the (possibly hierarchical) column labels, returning a DataFrame with an index with a new inner-most level of row labels.
> - `unstack`: inverse operation from `stack`: “pivot” a level of the (possibly hierarchical) row index to the column axis, producing a reshaped DataFrame with a new inner-most level of column labels.

The clearest way to explain is by example. Let’s take a prior example data set from the hierarchical indexing section:

    In [8]: tuples = list(zip(*[['bar', 'bar', 'baz', 'baz',
       ...:                      'foo', 'foo', 'qux', 'qux'],
       ...:                     ['one', 'two', 'one', 'two',
       ...:                      'one', 'two', 'one', 'two']]))
       ...:

    In [9]: index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])

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

    In [11]: df2 = df[:4]

    In [12]: df2
    Out[12]:
                         A         B
    first second
    bar   one     0.721555 -0.706771
          two    -1.039575  0.271860
    baz   one    -0.424972  0.567020
          two     0.276232 -1.087401

The `stack` function “compresses” a level in the DataFrame’s columns to produce either:

> - A Series, in the case of a simple column Index
> - A DataFrame, in the case of a `MultiIndex` in the columns

If the columns have a `MultiIndex`, you can choose which level to stack. The stacked level becomes the new lowest level in a `MultiIndex` on the columns:

    In [13]: stacked = df2.stack()

    In [14]: stacked
    Out[14]:
    first  second
    bar    one     A    0.721555
                   B   -0.706771
           two     A   -1.039575
                   B    0.271860
    baz    one     A   -0.424972
                   B    0.567020
           two     A    0.276232
                   B   -1.087401
    dtype: float64

With a “stacked” DataFrame or Series (having a `MultiIndex` as the `index`), the inverse operation of `stack` is `unstack`, which by default unstacks the last level:

    In [15]: stacked.unstack()
    Out[15]:
                         A         B
    first second
    bar   one     0.721555 -0.706771
          two    -1.039575  0.271860
    baz   one    -0.424972  0.567020
          two     0.276232 -1.087401

    In [16]: stacked.unstack(1)
    Out[16]:
    second        one       two
    first
    bar   A  0.721555 -1.039575
          B -0.706771  0.271860
    baz   A -0.424972  0.276232
          B  0.567020 -1.087401

    In [17]: stacked.unstack(0)
    Out[17]:
    first          bar       baz
    second
    one    A  0.721555 -0.424972
           B -0.706771  0.567020
    two    A -1.039575  0.276232
           B  0.271860 -1.087401

If the indexes have names, you can use the level names instead of specifying the level numbers:

    In [18]: stacked.unstack('second')
    Out[18]:
    second        one       two
    first
    bar   A  0.721555 -1.039575
          B -0.706771  0.271860
    baz   A -0.424972  0.276232
          B  0.567020 -1.087401

Notice that the `stack` and `unstack` methods implicitly sort the index levels involved. Hence a call to `stack` and then `unstack`, or viceversa, will result in a sorted copy of the original DataFrame or Series:

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

    In [20]: df = pd.DataFrame(np.random.randn(4), index=index, columns=['A'])

    In [21]: df
    Out[21]:
                A
    2 a -0.370647
      b -1.157892
    1 a -1.344312
      b  0.844885

    In [22]: all(df.unstack().stack() == df.sort_index())
    Out[22]: True

while the above code will raise a `TypeError` if the call to `sort_index` is removed.

### Multiple Levels

You may also stack or unstack more than one level at a time by passing a list of levels, in which case the end result is as if each level in the list were processed individually.

    In [23]: columns = pd.MultiIndex.from_tuples([
       ....:         ('A', 'cat', 'long'), ('B', 'cat', 'long'),
       ....:         ('A', 'dog', 'short'), ('B', 'dog', 'short')
       ....:     ],
       ....:     names=['exp', 'animal', 'hair_length']
       ....: )
       ....:

    In [24]: df = pd.DataFrame(np.random.randn(4, 4), columns=columns)

    In [25]: df
    Out[25]:
    exp                 A         B         A         B
    animal            cat       cat       dog       dog
    hair_length      long      long     short     short
    0            1.075770 -0.109050  1.643563 -1.469388
    1            0.357021 -0.674600 -1.776904 -0.968914
    2           -1.294524  0.413738  0.276662 -0.472035
    3           -0.013960 -0.362543 -0.006154 -0.923061

    In [26]: df.stack(level=['animal', 'hair_length'])
    Out[26]:
    exp                          A         B
      animal hair_length
    0 cat    long         1.075770 -0.109050
      dog    short        1.643563 -1.469388
    1 cat    long         0.357021 -0.674600
      dog    short       -1.776904 -0.968914
    2 cat    long        -1.294524  0.413738
      dog    short        0.276662 -0.472035
    3 cat    long        -0.013960 -0.362543
      dog    short       -0.006154 -0.923061

The list of levels can contain either level names or level numbers (but not a mixture of the two).

    # df.stack(level=['animal', 'hair_length'])
    # from above is equivalent to:
    In [27]: df.stack(level=[1, 2])
    Out[27]:
    exp                          A         B
      animal hair_length
    0 cat    long         1.075770 -0.109050
      dog    short        1.643563 -1.469388
    1 cat    long         0.357021 -0.674600
      dog    short       -1.776904 -0.968914
    2 cat    long        -1.294524  0.413738
      dog    short        0.276662 -0.472035
    3 cat    long        -0.013960 -0.362543
      dog    short       -0.006154 -0.923061

### Missing Data

These functions are intelligent about handling missing data and do not expect each subgroup within the hierarchical index to have the same set of labels. They also can handle the index being unsorted (but you can make it sorted by calling `sort_index`, of course). Here is a more complex example:

    In [28]: columns = pd.MultiIndex.from_tuples([('A', 'cat'), ('B', 'dog'),
       ....:                                      ('B', 'cat'), ('A', 'dog')],
       ....:                                     names=['exp', 'animal'])
       ....:

    In [29]: index = pd.MultiIndex.from_product([('bar', 'baz', 'foo', 'qux'),
       ....:                                     ('one', 'two')],
       ....:                                    names=['first', 'second'])
       ....:

    In [30]: df = pd.DataFrame(np.random.randn(8, 4), index=index, columns=columns)

    In [31]: df2 = df.ix[[0, 1, 2, 4, 5, 7]]

    In [32]: df2
    Out[32]:
    exp                  A         B                   A
    animal             cat       dog       cat       dog
    first second
    bar   one     0.895717  0.805244 -1.206412  2.565646
          two     1.431256  1.340309 -1.170299 -0.226169
    baz   one     0.410835  0.813850  0.132003 -0.827317
    foo   one    -1.413681  1.607920  1.024180  0.569605
          two     0.875906 -2.211372  0.974466 -2.006747
    qux   two    -1.226825  0.769804 -1.281247 -0.727707

As mentioned above, `stack` can be called with a `level` argument to select which level in the columns to stack:

    In [33]: df2.stack('exp')
    Out[33]:
    animal                 cat       dog
    first second exp
    bar   one    A    0.895717  2.565646
                 B   -1.206412  0.805244
          two    A    1.431256 -0.226169
                 B   -1.170299  1.340309
    baz   one    A    0.410835 -0.827317
                 B    0.132003  0.813850
    foo   one    A   -1.413681  0.569605
                 B    1.024180  1.607920
          two    A    0.875906 -2.006747
                 B    0.974466 -2.211372
    qux   two    A   -1.226825 -0.727707
                 B   -1.281247  0.769804

    In [34]: df2.stack('animal')
    Out[34]:
    exp                         A         B
    first second animal
    bar   one    cat     0.895717 -1.206412
                 dog     2.565646  0.805244
          two    cat     1.431256 -1.170299
                 dog    -0.226169  1.340309
    baz   one    cat     0.410835  0.132003
                 dog    -0.827317  0.813850
    foo   one    cat    -1.413681  1.024180
                 dog     0.569605  1.607920
          two    cat     0.875906  0.974466
                 dog    -2.006747 -2.211372
    qux   two    cat    -1.226825 -1.281247
                 dog    -0.727707  0.769804

### With a MultiIndex

Unstacking when the columns are a `MultiIndex` is also careful about doing the right thing:

    In [35]: df[:3].unstack(0)
    Out[35]:
    exp            A                   B                                      A  \
    animal       cat                 dog                cat                 dog
    first        bar       baz       bar      baz       bar       baz       bar
    second
    one     0.895717  0.410835  0.805244  0.81385 -1.206412  0.132003  2.565646
    two     1.431256       NaN  1.340309      NaN -1.170299       NaN -0.226169

    exp
    animal
    first        baz
    second
    one    -0.827317
    two          NaN

    In [36]: df2.unstack(1)
    Out[36]:
    exp            A                   B                                       A  \
    animal       cat                 dog                 cat                 dog
    second       one       two       one       two       one       two       one
    first
    bar     0.895717  1.431256  0.805244  1.340309 -1.206412 -1.170299  2.565646
    baz     0.410835       NaN  0.813850       NaN  0.132003       NaN -0.827317
    foo    -1.413681  0.875906  1.607920 -2.211372  1.024180  0.974466  0.569605
    qux          NaN -1.226825       NaN  0.769804       NaN -1.281247       NaN

    exp
    animal
    second       two
    first
    bar    -0.226169
    baz          NaN
    foo    -2.006747
    qux    -0.727707

## Reshaping by Melt

The `melt()` function is useful to massage a DataFrame into a format where one or more columns are identifier variables, while all other columns, considered measured variables, are “unpivoted” to the row axis, leaving just two non-identifier columns, “variable” and “value”. The names of those columns can be customized by supplying the `var_name` and `value_name` parameters.

For instance,

    In [37]: cheese = pd.DataFrame({'first' : ['John', 'Mary'],
       ....:                        'last' : ['Doe', 'Bo'],
       ....:                        'height' : [5.5, 6.0],
       ....:                        'weight' : [130, 150]})
       ....:

    In [38]: cheese
    Out[38]:
      first  height last  weight
    0  John     5.5  Doe     130
    1  Mary     6.0   Bo     150

    In [39]: pd.melt(cheese, id_vars=['first', 'last'])
    Out[39]:
      first last variable  value
    0  John  Doe   height    5.5
    1  Mary   Bo   height    6.0
    2  John  Doe   weight  130.0
    3  Mary   Bo   weight  150.0

    In [40]: pd.melt(cheese, id_vars=['first', 'last'], var_name='quantity')
    Out[40]:
      first last quantity  value
    0  John  Doe   height    5.5
    1  Mary   Bo   height    6.0
    2  John  Doe   weight  130.0
    3  Mary   Bo   weight  150.0

Another way to transform is to use the `wide_to_long` panel data convenience function.

    In [41]: dft = pd.DataFrame({"A1970" : {0 : "a", 1 : "b", 2 : "c"},
       ....:                     "A1980" : {0 : "d", 1 : "e", 2 : "f"},
       ....:                     "B1970" : {0 : 2.5, 1 : 1.2, 2 : .7},
       ....:                     "B1980" : {0 : 3.2, 1 : 1.3, 2 : .1},
       ....:                     "X"     : dict(zip(range(3), np.random.randn(3)))
       ....:                    })
       ....:

    In [42]: dft["id"] = dft.index

    In [43]: dft
    Out[43]:
      A1970 A1980  B1970  B1980         X  id
    0     a     d    2.5    3.2 -0.121306   0
    1     b     e    1.2    1.3 -0.097883   1
    2     c     f    0.7    0.1  0.695775   2

    In [44]: pd.wide_to_long(dft, ["A", "B"], i="id", j="year")
    Out[44]:
                    X  A    B
    id year
    0  1970 -0.121306  a  2.5
    1  1970 -0.097883  b  1.2
    2  1970  0.695775  c  0.7
    0  1980 -0.121306  d  3.2
    1  1980 -0.097883  e  1.3
    2  1980  0.695775  f  0.1

## Combining with stats and GroupBy

It should be no shock that combining `pivot` / `stack` / `unstack` with GroupBy and the basic Series and DataFrame statistical functions can produce some very expressive and fast data manipulations.

    In [45]: df
    Out[45]:
    exp                  A         B                   A
    animal             cat       dog       cat       dog
    first second
    bar   one     0.895717  0.805244 -1.206412  2.565646
          two     1.431256  1.340309 -1.170299 -0.226169
    baz   one     0.410835  0.813850  0.132003 -0.827317
          two    -0.076467 -1.187678  1.130127 -1.436737
    foo   one    -1.413681  1.607920  1.024180  0.569605
          two     0.875906 -2.211372  0.974466 -2.006747
    qux   one    -0.410001 -0.078638  0.545952 -1.219217
          two    -1.226825  0.769804 -1.281247 -0.727707

    In [46]: df.stack().mean(1).unstack()
    Out[46]:
    animal             cat       dog
    first second
    bar   one    -0.155347  1.685445
          two     0.130479  0.557070
    baz   one     0.271419 -0.006733
          two     0.526830 -1.312207
    foo   one    -0.194750  1.088763
          two     0.925186 -2.109060
    qux   one     0.067976 -0.648927
          two    -1.254036  0.021048

    # same result, another way
    In [47]: df.groupby(level=1, axis=1).mean()
    Out[47]:
    animal             cat       dog
    first second
    bar   one    -0.155347  1.685445
          two     0.130479  0.557070
    baz   one     0.271419 -0.006733
          two     0.526830 -1.312207
    foo   one    -0.194750  1.088763
          two     0.925186 -2.109060
    qux   one     0.067976 -0.648927
          two    -1.254036  0.021048

    In [48]: df.stack().groupby(level=1).mean()
    Out[48]:
    exp            A         B
    second
    one     0.071448  0.455513
    two    -0.424186 -0.204486

    In [49]: df.mean().unstack(0)
    Out[49]:
    exp            A         B
    animal
    cat     0.060843  0.018596
    dog    -0.413580  0.232430

## Pivot tables and cross-tabulations

The function `pandas.pivot_table` can be used to create spreadsheet-style pivot tables. See the cookbook for some advanced strategies

It takes a number of arguments

- `data`: A DataFrame object
- `values`: a column or a list of columns to aggregate
- `index`: a column, Grouper, array which has the same length as data, or list of them. Keys to group by on the pivot table index. If an array is passed, it is being used as the same manner as column values.
- `columns`: a column, Grouper, array which has the same length as data, or list of them. Keys to group by on the pivot table column. If an array is passed, it is being used as the same manner as column values.
- `aggfunc`: function to use for aggregation, defaulting to `numpy.mean`

Consider a data set like this:

    In [50]: import datetime

    In [51]: df = pd.DataFrame({'A': ['one', 'one', 'two', 'three'] * 6,
       ....:                    'B': ['A', 'B', 'C'] * 8,
       ....:                    'C': ['foo', 'foo', 'foo', 'bar', 'bar', 'bar'] * 4,
       ....:                    'D': np.random.randn(24),
       ....:                    'E': np.random.randn(24),
       ....:                    'F': [datetime.datetime(2013, i, 1) for i in range(1, 13)] +
       ....:                         [datetime.datetime(2013, i, 15) for i in range(1, 13)]})
       ....:

    In [52]: df
    Out[52]:
            A  B    C         D         E          F
    0     one  A  foo  0.341734 -0.317441 2013-01-01
    1     one  B  foo  0.959726 -1.236269 2013-02-01
    2     two  C  foo -1.110336  0.896171 2013-03-01
    3   three  A  bar -0.619976 -0.487602 2013-04-01
    4     one  B  bar  0.149748 -0.082240 2013-05-01
    5     one  C  bar -0.732339 -2.182937 2013-06-01
    6     two  A  foo  0.687738  0.380396 2013-07-01
    ..    ... ..  ...       ...       ...        ...
    17    one  C  bar -0.345352  0.206053 2013-06-15
    18    two  A  foo  1.314232 -0.251905 2013-07-15
    19  three  B  foo  0.690579 -2.213588 2013-08-15
    20    one  C  foo  0.995761  1.063327 2013-09-15
    21    one  A  bar  2.396780  1.266143 2013-10-15
    22    two  B  bar  0.014871  0.299368 2013-11-15
    23  three  C  bar  3.357427 -0.863838 2013-12-15

    [24 rows x 6 columns]

We can produce pivot tables from this data very easily:

    In [53]: pd.pivot_table(df, values='D', index=['A', 'B'], columns=['C'])
    Out[53]:
    C             bar       foo
    A     B
    one   A  1.120915 -0.514058
          B -0.338421  0.002759
          C -0.538846  0.699535
    three A -1.181568       NaN
          B       NaN  0.433512
          C  0.588783       NaN
    two   A       NaN  1.000985
          B  0.158248       NaN
          C       NaN  0.176180

    In [54]: pd.pivot_table(df, values='D', index=['B'], columns=['A', 'C'], aggfunc=np.sum)
    Out[54]:
    A       one               three                 two
    C       bar       foo       bar       foo       bar       foo
    B
    A  2.241830 -1.028115 -2.363137       NaN       NaN  2.001971
    B -0.676843  0.005518       NaN  0.867024  0.316495       NaN
    C -1.077692  1.399070  1.177566       NaN       NaN  0.352360

    In [55]: pd.pivot_table(df, values=['D','E'], index=['B'], columns=['A', 'C'], aggfunc=np.sum)
    Out[55]:
              D                                                           E  \
    A       one               three                 two                 one
    C       bar       foo       bar       foo       bar       foo       bar
    B
    A  2.241830 -1.028115 -2.363137       NaN       NaN  2.001971  2.786113
    B -0.676843  0.005518       NaN  0.867024  0.316495       NaN  1.368280
    C -1.077692  1.399070  1.177566       NaN       NaN  0.352360 -1.976883

    A               three                 two
    C       foo       bar       foo       bar       foo
    B
    A -0.043211  1.922577       NaN       NaN  0.128491
    B -1.103384       NaN -2.128743 -0.194294       NaN
    C  1.495717 -0.263660       NaN       NaN  0.872482

The result object is a DataFrame having potentially hierarchical indexes on the rows and columns. If the `values` column name is not given, the pivot table will include all of the data that can be aggregated in an additional level of hierarchy in the columns:

    In [56]: pd.pivot_table(df, index=['A', 'B'], columns=['C'])
    Out[56]:
                    D                   E
    C             bar       foo       bar       foo
    A     B
    one   A  1.120915 -0.514058  1.393057 -0.021605
          B -0.338421  0.002759  0.684140 -0.551692
          C -0.538846  0.699535 -0.988442  0.747859
    three A -1.181568       NaN  0.961289       NaN
          B       NaN  0.433512       NaN -1.064372
          C  0.588783       NaN -0.131830       NaN
    two   A       NaN  1.000985       NaN  0.064245
          B  0.158248       NaN -0.097147       NaN
          C       NaN  0.176180       NaN  0.436241

Also, you can use `Grouper` for `index` and `columns` keywords. For detail of `Grouper`, see Grouping with a Grouper specification.

    In [57]: pd.pivot_table(df, values='D', index=Grouper(freq='M', key='F'), columns='C')
    Out[57]:
    C                bar       foo
    F
    2013-01-31       NaN -0.514058
    2013-02-28       NaN  0.002759
    2013-03-31       NaN  0.176180
    2013-04-30 -1.181568       NaN
    2013-05-31 -0.338421       NaN
    2013-06-30 -0.538846       NaN
    2013-07-31       NaN  1.000985
    2013-08-31       NaN  0.433512
    2013-09-30       NaN  0.699535
    2013-10-31  1.120915       NaN
    2013-11-30  0.158248       NaN
    2013-12-31  0.588783       NaN

You can render a nice output of the table omitting the missing values by calling `to_string` if you wish:

    In [58]: table = pd.pivot_table(df, index=['A', 'B'], columns=['C'])

    In [59]: print(table.to_string(na_rep=''))
                    D                   E
    C             bar       foo       bar       foo
    A     B
    one   A  1.120915 -0.514058  1.393057 -0.021605
          B -0.338421  0.002759  0.684140 -0.551692
          C -0.538846  0.699535 -0.988442  0.747859
    three A -1.181568            0.961289
          B            0.433512           -1.064372
          C  0.588783           -0.131830
    two   A            1.000985            0.064245
          B  0.158248           -0.097147
          C            0.176180            0.436241

Note that `pivot_table` is also available as an instance method on DataFrame.

### Cross tabulations

Use the `crosstab` function to compute a cross-tabulation of two (or more) factors. By default `crosstab` computes a frequency table of the factors unless an array of values and an aggregation function are passed.

It takes a number of arguments

- `index`: array-like, values to group by in the rows
- `columns`: array-like, values to group by in the columns
- `values`: array-like, optional, array of values to aggregate according to the factors
- `aggfunc`: function, optional, If no values array is passed, computes a frequency table
- `rownames`: sequence, default None, must match number of row arrays passed
- `colnames`: sequence, default None, if passed, must match number of column arrays passed
- `margins`: boolean, default False, Add row/column margins (subtotals)

Any Series passed will have their name attributes used unless row or column names for the cross-tabulation are specified

For example:

    In [60]: foo, bar, dull, shiny, one, two = 'foo', 'bar', 'dull', 'shiny', 'one', 'two'

    In [61]: a = np.array([foo, foo, bar, bar, foo, foo], dtype=object)

    In [62]: b = np.array([one, one, two, one, two, one], dtype=object)

    In [63]: c = np.array([dull, dull, shiny, dull, dull, shiny], dtype=object)

    In [64]: pd.crosstab(a, [b, c], rownames=['a'], colnames=['b', 'c'])
    Out[64]:
    b    one        two
    c   dull shiny dull shiny
    a
    bar    1     0    0     1
    foo    2     1    1     0

### Adding margins (partial aggregates)

If you pass `margins=True` to `pivot_table`, special `All` columns and rows will be added with partial group aggregates across the categories on the rows and columns:

    In [65]: df.pivot_table(index=['A', 'B'], columns='C', margins=True, aggfunc=np.std)
    Out[65]:
                    D                             E
    C             bar       foo       All       bar       foo       All
    A     B
    one   A  1.804346  1.210272  1.569879  0.179483  0.418374  0.858005
          B  0.690376  1.353355  0.898998  1.083825  0.968138  1.101401
          C  0.273641  0.418926  0.771139  1.689271  0.446140  1.422136
    three A  0.794212       NaN  0.794212  2.049040       NaN  2.049040
          B       NaN  0.363548  0.363548       NaN  1.625237  1.625237
          C  3.915454       NaN  3.915454  1.035215       NaN  1.035215
    two   A       NaN  0.442998  0.442998       NaN  0.447104  0.447104
          B  0.202765       NaN  0.202765  0.560757       NaN  0.560757
          C       NaN  1.819408  1.819408       NaN  0.650439  0.650439
    All      1.556686  0.952552  1.246608  1.250924  0.899904  1.059389

## Tiling

The `cut` function computes groupings for the values of the input array and is often used to transform continuous variables to discrete or categorical variables:

    In [66]: ages = np.array([10, 15, 13, 12, 23, 25, 28, 59, 60])

    In [67]: pd.cut(ages, bins=3)
    Out[67]:
    [(9.95, 26.667], (9.95, 26.667], (9.95, 26.667], (9.95, 26.667], (9.95, 26.667], (9.95, 26.667], (26.667, 43.333], (43.333, 60], (43.333, 60]]
    Categories (3, object): [(9.95, 26.667] < (26.667, 43.333] < (43.333, 60]]

If the `bins` keyword is an integer, then equal-width bins are formed. Alternatively we can specify custom bin-edges:

    In [68]: pd.cut(ages, bins=[0, 18, 35, 70])
    Out[68]:
    [(0, 18], (0, 18], (0, 18], (0, 18], (18, 35], (18, 35], (18, 35], (35, 70], (35, 70]]
    Categories (3, object): [(0, 18] < (18, 35] < (35, 70]]

## Computing indicator / dummy variables

To convert a categorical variable into a “dummy” or “indicator” DataFrame, for example a column in a DataFrame (a Series) which has `k` distinct values, can derive a DataFrame containing `k` columns of 1s and 0s:

    In [69]: df = pd.DataFrame({'key': list('bbacab'), 'data1': range(6)})

    In [70]: pd.get_dummies(df['key'])
    Out[70]:
       a  b  c
    0  0  1  0
    1  0  1  0
    2  1  0  0
    3  0  0  1
    4  1  0  0
    5  0  1  0

Sometimes it’s useful to prefix the column names, for example when merging the result with the original DataFrame:

    In [71]: dummies = pd.get_dummies(df['key'], prefix='key')

    In [72]: dummies
    Out[72]:
       key_a  key_b  key_c
    0      0      1      0
    1      0      1      0
    2      1      0      0
    3      0      0      1
    4      1      0      0
    5      0      1      0

    In [73]: df[['data1']].join(dummies)
    Out[73]:
       data1  key_a  key_b  key_c
    0      0      0      1      0
    1      1      0      1      0
    2      2      1      0      0
    3      3      0      0      1
    4      4      1      0      0
    5      5      0      1      0

This function is often used along with discretization functions like `cut`:

    In [74]: values = np.random.randn(10)

    In [75]: values
    Out[75]:
    array([ 0.4082, -1.0481, -0.0257, -0.9884,  0.0941,  1.2627,  1.29  ,
            0.0824, -0.0558,  0.5366])

    In [76]: bins = [0, 0.2, 0.4, 0.6, 0.8, 1]

    In [77]: pd.get_dummies(pd.cut(values, bins))
    Out[77]:
       (0, 0.2]  (0.2, 0.4]  (0.4, 0.6]  (0.6, 0.8]  (0.8, 1]
    0         0           0           1           0         0
    1         0           0           0           0         0
    2         0           0           0           0         0
    3         0           0           0           0         0
    4         1           0           0           0         0
    5         0           0           0           0         0
    6         0           0           0           0         0
    7         1           0           0           0         0
    8         0           0           0           0         0
    9         0           0           1           0         0

See also `Series.str.get_dummies`.

New in version 0.15.0.

`get_dummies()` also accepts a DataFrame. By default all categorical variables (categorical in the statistical sense, those with object or categorical dtype) are encoded as dummy variables.

    In [78]: df = pd.DataFrame({'A': ['a', 'b', 'a'], 'B': ['c', 'c', 'b'],
       ....:                    'C': [1, 2, 3]})
       ....:

    In [79]: pd.get_dummies(df)
    Out[79]:
       C  A_a  A_b  B_b  B_c
    0  1    1    0    0    1
    1  2    0    1    0    1
    2  3    1    0    1    0

All non-object columns are included untouched in the output.

You can control the columns that are encoded with the `columns` keyword.

    In [80]: pd.get_dummies(df, columns=['A'])
    Out[80]:
       B  C  A_a  A_b
    0  c  1    1    0
    1  c  2    0    1
    2  b  3    1    0

Notice that the `B` column is still included in the output, it just hasn’t been encoded. You can drop `B` before calling `get_dummies` if you don’t want to include it in the output.

As with the Series version, you can pass values for the `prefix` and `prefix_sep`. By default the column name is used as the prefix, and ‘_’ as the prefix separator. You can specify `prefix` and `prefix_sep` in 3 ways

- string: Use the same value for `prefix` or `prefix_sep` for each column to be encoded
- list: Must be the same length as the number of columns being encoded.
- dict: Mapping column name to prefix

    In [81]: simple = pd.get_dummies(df, prefix='new_prefix')

    In [82]: simple
    Out[82]:
       C  new_prefix_a  new_prefix_b  new_prefix_b  new_prefix_c
    0  1             1             0             0             1
    1  2             0             1             0             1
    2  3             1             0             1             0

    In [83]: from_list = pd.get_dummies(df, prefix=['from_A', 'from_B'])

    In [84]: from_list
    Out[84]:
       C  from_A_a  from_A_b  from_B_b  from_B_c
    0  1         1         0         0         1
    1  2         0         1         0         1
    2  3         1         0         1         0

    In [85]: from_dict = pd.get_dummies(df, prefix={'B': 'from_B', 'A': 'from_A'})

    In [86]: from_dict
    Out[86]:
       C  from_A_a  from_A_b  from_B_b  from_B_c
    0  1         1         0         0         1
    1  2         0         1         0         1
    2  3         1         0         1         0

## Factorizing values

To encode 1-d values as an enumerated type use `factorize`:

    In [87]: x = pd.Series(['A', 'A', np.nan, 'B', 3.14, np.inf])

    In [88]: x
    Out[88]:
    0       A
    1       A
    2     NaN
    3       B
    4    3.14
    5     inf
    dtype: object

    In [89]: labels, uniques = pd.factorize(x)

    In [90]: labels
    Out[90]: array([ 0,  0, -1,  1,  2,  3])

    In [91]: uniques
    Out[91]: Index([u'A', u'B', 3.14, inf], dtype='object')

Note that `factorize` is similar to `numpy.unique`, but differs in its handling of NaN:

Note

The following `numpy.unique` will fail under Python 3 with a `TypeError` because of an ordering bug. See also Here

    In [92]: pd.factorize(x, sort=True)
    Out[92]:
    (array([ 2,  2, -1,  3,  0,  1]),
     Index([3.14, inf, u'A', u'B'], dtype='object'))

    In [93]: np.unique(x, return_inverse=True)[::-1]
    Out[93]: (array([3, 3, 0, 4, 1, 2]), array([nan, 3.14, inf, 'A', 'B'], dtype=object))

Note

If you just want to handle one column as a categorical variable (like R’s factor), you can use `df["cat_col"] = pd.Categorical(df["col"])` or `df["cat_col"] = df["col"].astype("category")`. For full docs on `Categorical`, see the Categorical introduction and the API documentation. This feature was introduced in version 0.15.