Reshaping and pivot tables

Reshaping by pivoting DataFrame objects

reshaping_pivot

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

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

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

  1. import pandas.util.testing as tm
  3. tm.N = 3
  6. def unpivot(frame):
  7.     N, K = frame.shape
  8.     data = {'value': frame.to_numpy().ravel('F'),
  9.             'variable': np.asarray(frame.columns).repeat(N),
  10.             'date': np.tile(np.asarray(frame.index), K)}
  11.     return pd.DataFrame(data, columns=['date', 'variable', 'value'])
  14. df = unpivot(tm.makeTimeDataFrame())

To select out everything for variable A we could do:

  1. In [2]: df[df['variable'] == 'A']
  2. Out[2]: 
  3.         date variable     value
  4. 0 2000-01-03        A  0.469112
  5. 1 2000-01-04        A -0.282863
  6. 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, we use the DataFrame.pivot() method (also implemented as a top level function pivot()):

  1. In [3]: df.pivot(index='date', columns='variable', values='value')
  2. Out[3]: 
  3. variable           A         B         C         D
  4. date                                              
  5. 2000-01-03  0.469112 -1.135632  0.119209 -2.104569
  6. 2000-01-04 -0.282863  1.212112 -1.044236 -0.494929
  7. 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:

  1. In [4]: df['value2'] = df['value'] * 2
  3. In [5]: pivoted = df.pivot(index='date', columns='variable')
  5. In [6]: pivoted
  6. Out[6]: 
  7.                value                                  value2                              
  8. variable           A         B         C         D         A         B         C         D
  9. date                                                                                      
  10. 2000-01-03  0.469112 -1.135632  0.119209 -2.104569  0.938225 -2.271265  0.238417 -4.209138
  11. 2000-01-04 -0.282863  1.212112 -1.044236 -0.494929 -0.565727  2.424224 -2.088472 -0.989859
  12. 2000-01-05 -1.509059 -0.173215 -0.861849  1.071804 -3.018117 -0.346429 -1.723698  2.143608

You can then select subsets from the pivoted DataFrame:

  1. In [7]: pivoted['value2']
  2. Out[7]: 
  3. variable           A         B         C         D
  4. date                                              
  5. 2000-01-03  0.938225 -2.271265  0.238417 -4.209138
  6. 2000-01-04 -0.565727  2.424224 -2.088472 -0.989859
  7. 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.

::: tip Note

pivot() will error with a ValueError: Index contains duplicate entries, cannot reshape if the index/column pair is not unique. In this case, consider using pivot_table() which is a generalization of pivot that can handle duplicate values for one index/column pair.

:::

Reshaping by stacking and unstacking

reshaping_stack

Closely related to the pivot() method are the related stack() and unstack() methods available on Series and DataFrame. These methods are designed to work together with MultiIndex objects (see the section on hierarchical indexing). Here are essentially what these methods 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 of 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.

reshaping_unstack

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

  1. In [8]: tuples = list(zip(*[['bar', 'bar', 'baz', 'baz',
  2.    ...:                      'foo', 'foo', 'qux', 'qux'],
  3.    ...:                     ['one', 'two', 'one', 'two',
  4.    ...:                      'one', 'two', 'one', 'two']]))
  5.    ...: 
  7. In [9]: index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
  9. In [10]: df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=['A', 'B'])
  11. In [11]: df2 = df[:4]
  13. In [12]: df2
  14. Out[12]: 
  15.                      A         B
  16. first second                    
  17. bar   one     0.721555 -0.706771
  18.       two    -1.039575  0.271860
  19. baz   one    -0.424972  0.567020
  20.       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:

  1. In [13]: stacked = df2.stack()
  3. In [14]: stacked
  4. Out[14]: 
  5. first  second   
  6. bar    one     A    0.721555
  7.                B   -0.706771
  8.        two     A   -1.039575
  9.                B    0.271860
  10. baz    one     A   -0.424972
  11.                B    0.567020
  12.        two     A    0.276232
  13.                B   -1.087401
  14. 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:

  1. In [15]: stacked.unstack()
  2. Out[15]: 
  3.                      A         B
  4. first second                    
  5. bar   one     0.721555 -0.706771
  6.       two    -1.039575  0.271860
  7. baz   one    -0.424972  0.567020
  8.       two     0.276232 -1.087401
  10. In [16]: stacked.unstack(1)
  11. Out[16]: 
  12. second        one       two
  13. first                      
  14. bar   A  0.721555 -1.039575
  15.       B -0.706771  0.271860
  16. baz   A -0.424972  0.276232
  17.       B  0.567020 -1.087401
  19. In [17]: stacked.unstack(0)
  20. Out[17]: 
  21. first          bar       baz
  22. second                      
  23. one    A  0.721555 -0.424972
  24.        B -0.706771  0.567020
  25. two    A -1.039575  0.276232
  26.        B  0.271860 -1.087401

reshaping_unstack_1

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

  1. In [18]: stacked.unstack('second')
  2. Out[18]: 
  3. second        one       two
  4. first                      
  5. bar   A  0.721555 -1.039575
  6.       B -0.706771  0.271860
  7. baz   A -0.424972  0.276232
  8.       B  0.567020 -1.087401

reshaping_unstack_0

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

  1. In [19]: index = pd.MultiIndex.from_product([[2, 1], ['a', 'b']])
  3. In [20]: df = pd.DataFrame(np.random.randn(4), index=index, columns=['A'])
  5. In [21]: df
  6. Out[21]: 
  7.             A
  8. 2 a -0.370647
  9.   b -1.157892
  10. 1 a -1.344312
  11.   b  0.844885
  13. In [22]: all(df.unstack().stack() == df.sort_index())
  14. Out[22]: True

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.

  1. In [23]: columns = pd.MultiIndex.from_tuples([
  2.    ....:     ('A', 'cat', 'long'), ('B', 'cat', 'long'),
  3.    ....:     ('A', 'dog', 'short'), ('B', 'dog', 'short')],
  4.    ....:     names=['exp', 'animal', 'hair_length']
  5.    ....: )
  6.    ....: 
  8. In [24]: df = pd.DataFrame(np.random.randn(4, 4), columns=columns)
  10. In [25]: df
  11. Out[25]: 
  12. exp                 A         B         A         B
  13. animal            cat       cat       dog       dog
  14. hair_length      long      long     short     short
  15. 0            1.075770 -0.109050  1.643563 -1.469388
  16. 1            0.357021 -0.674600 -1.776904 -0.968914
  17. 2           -1.294524  0.413738  0.276662 -0.472035
  18. 3           -0.013960 -0.362543 -0.006154 -0.923061
  20. In [26]: df.stack(level=['animal', 'hair_length'])
  21. Out[26]: 
  22. exp                          A         B
  23.   animal hair_length                    
  24. 0 cat    long         1.075770 -0.109050
  25.   dog    short        1.643563 -1.469388
  26. 1 cat    long         0.357021 -0.674600
  27.   dog    short       -1.776904 -0.968914
  28. 2 cat    long        -1.294524  0.413738
  29.   dog    short        0.276662 -0.472035
  30. 3 cat    long        -0.013960 -0.362543
  31.   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).

  1. # df.stack(level=['animal', 'hair_length'])
  2. # from above is equivalent to:
  3. In [27]: df.stack(level=[1, 2])
  4. Out[27]: 
  5. exp                          A         B
  6.   animal hair_length                    
  7. 0 cat    long         1.075770 -0.109050
  8.   dog    short        1.643563 -1.469388
  9. 1 cat    long         0.357021 -0.674600
  10.   dog    short       -1.776904 -0.968914
  11. 2 cat    long        -1.294524  0.413738
  12.   dog    short        0.276662 -0.472035
  13. 3 cat    long        -0.013960 -0.362543
  14.   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:

  1. In [28]: columns = pd.MultiIndex.from_tuples([('A', 'cat'), ('B', 'dog'),
  2.    ....:                                      ('B', 'cat'), ('A', 'dog')],
  3.    ....:                                     names=['exp', 'animal'])
  4.    ....: 
  6. In [29]: index = pd.MultiIndex.from_product([('bar', 'baz', 'foo', 'qux'),
  7.    ....:                                     ('one', 'two')],
  8.    ....:                                    names=['first', 'second'])
  9.    ....: 
  11. In [30]: df = pd.DataFrame(np.random.randn(8, 4), index=index, columns=columns)
  13. In [31]: df2 = df.iloc[[0, 1, 2, 4, 5, 7]]
  15. In [32]: df2
  16. Out[32]: 
  17. exp                  A         B                   A
  18. animal             cat       dog       cat       dog
  19. first second                                        
  20. bar   one     0.895717  0.805244 -1.206412  2.565646
  21.       two     1.431256  1.340309 -1.170299 -0.226169
  22. baz   one     0.410835  0.813850  0.132003 -0.827317
  23. foo   one    -1.413681  1.607920  1.024180  0.569605
  24.       two     0.875906 -2.211372  0.974466 -2.006747
  25. 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:

  1. In [33]: df2.stack('exp')
  2. Out[33]: 
  3. animal                 cat       dog
  4. first second exp                    
  5. bar   one    A    0.895717  2.565646
  6.              B   -1.206412  0.805244
  7.       two    A    1.431256 -0.226169
  8.              B   -1.170299  1.340309
  9. baz   one    A    0.410835 -0.827317
  10.              B    0.132003  0.813850
  11. foo   one    A   -1.413681  0.569605
  12.              B    1.024180  1.607920
  13.       two    A    0.875906 -2.006747
  14.              B    0.974466 -2.211372
  15. qux   two    A   -1.226825 -0.727707
  16.              B   -1.281247  0.769804
  18. In [34]: df2.stack('animal')
  19. Out[34]: 
  20. exp                         A         B
  21. first second animal                    
  22. bar   one    cat     0.895717 -1.206412
  23.              dog     2.565646  0.805244
  24.       two    cat     1.431256 -1.170299
  25.              dog    -0.226169  1.340309
  26. baz   one    cat     0.410835  0.132003
  27.              dog    -0.827317  0.813850
  28. foo   one    cat    -1.413681  1.024180
  29.              dog     0.569605  1.607920
  30.       two    cat     0.875906  0.974466
  31.              dog    -2.006747 -2.211372
  32. qux   two    cat    -1.226825 -1.281247
  33.              dog    -0.727707  0.769804

Unstacking can result in missing values if subgroups do not have the same set of labels. By default, missing values will be replaced with the default fill value for that data type, NaN for float, NaT for datetimelike, etc. For integer types, by default data will converted to float and missing values will be set to NaN.

  1. In [35]: df3 = df.iloc[[0, 1, 4, 7], [1, 2]]
  3. In [36]: df3
  4. Out[36]: 
  5. exp                  B          
  6. animal             dog       cat
  7. first second                    
  8. bar   one     0.805244 -1.206412
  9.       two     1.340309 -1.170299
  10. foo   one     1.607920  1.024180
  11. qux   two     0.769804 -1.281247
  13. In [37]: df3.unstack()
  14. Out[37]: 
  15. exp            B                              
  16. animal       dog                 cat          
  17. second       one       two       one       two
  18. first                                         
  19. bar     0.805244  1.340309 -1.206412 -1.170299
  20. foo     1.607920       NaN  1.024180       NaN
  21. qux          NaN  0.769804       NaN -1.281247

New in version 0.18.0.

Alternatively, unstack takes an optional fill_value argument, for specifying the value of missing data.

  1. In [38]: df3.unstack(fill_value=-1e9)
  2. Out[38]: 
  3. exp                B                                          
  4. animal           dog                         cat              
  5. second           one           two           one           two
  6. first                                                         
  7. bar     8.052440e-01  1.340309e+00 -1.206412e+00 -1.170299e+00
  8. foo     1.607920e+00 -1.000000e+09  1.024180e+00 -1.000000e+09
  9. qux    -1.000000e+09  7.698036e-01 -1.000000e+09 -1.281247e+00

With a MultiIndex

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

  1. In [39]: df[:3].unstack(0)
  2. Out[39]: 
  3. exp            A                   B                                      A          
  4. animal       cat                 dog                cat                 dog          
  5. first        bar       baz       bar      baz       bar       baz       bar       baz
  6. second                                                                               
  7. one     0.895717  0.410835  0.805244  0.81385 -1.206412  0.132003  2.565646 -0.827317
  8. two     1.431256       NaN  1.340309      NaN -1.170299       NaN -0.226169       NaN
  10. In [40]: df2.unstack(1)
  11. Out[40]: 
  12. exp            A                   B                                       A          
  13. animal       cat                 dog                 cat                 dog          
  14. second       one       two       one       two       one       two       one       two
  15. first                                                                                 
  16. bar     0.895717  1.431256  0.805244  1.340309 -1.206412 -1.170299  2.565646 -0.226169
  17. baz     0.410835       NaN  0.813850       NaN  0.132003       NaN -0.827317       NaN
  18. foo    -1.413681  0.875906  1.607920 -2.211372  1.024180  0.974466  0.569605 -2.006747
  19. qux          NaN -1.226825       NaN  0.769804       NaN -1.281247       NaN -0.727707

Reshaping by Melt

reshaping_melt

The top-level melt() function and the corresponding DataFrame.melt() are 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,

  1. In [41]: cheese = pd.DataFrame({'first': ['John', 'Mary'],
  2.    ....:                        'last': ['Doe', 'Bo'],
  3.    ....:                        'height': [5.5, 6.0],
  4.    ....:                        'weight': [130, 150]})
  5.    ....: 
  7. In [42]: cheese
  8. Out[42]: 
  9.   first last  height  weight
  10. 0  John  Doe     5.5     130
  11. 1  Mary   Bo     6.0     150
  13. In [43]: cheese.melt(id_vars=['first', 'last'])
  14. Out[43]: 
  15.   first last variable  value
  16. 0  John  Doe   height    5.5
  17. 1  Mary   Bo   height    6.0
  18. 2  John  Doe   weight  130.0
  19. 3  Mary   Bo   weight  150.0
  21. In [44]: cheese.melt(id_vars=['first', 'last'], var_name='quantity')
  22. Out[44]: 
  23.   first last quantity  value
  24. 0  John  Doe   height    5.5
  25. 1  Mary   Bo   height    6.0
  26. 2  John  Doe   weight  130.0
  27. 3  Mary   Bo   weight  150.0

Another way to transform is to use the wide_to_long() panel data convenience function. It is less flexible than melt(), but more user-friendly.

  1. In [45]: dft = pd.DataFrame({"A1970": {0: "a", 1: "b", 2: "c"},
  2.    ....:                     "A1980": {0: "d", 1: "e", 2: "f"},
  3.    ....:                     "B1970": {0: 2.5, 1: 1.2, 2: .7},
  4.    ....:                     "B1980": {0: 3.2, 1: 1.3, 2: .1},
  5.    ....:                     "X": dict(zip(range(3), np.random.randn(3)))
  6.    ....:                    })
  7.    ....: 
  9. In [46]: dft["id"] = dft.index
  11. In [47]: dft
  12. Out[47]: 
  13.   A1970 A1980  B1970  B1980         X  id
  14. 0     a     d    2.5    3.2 -0.121306   0
  15. 1     b     e    1.2    1.3 -0.097883   1
  16. 2     c     f    0.7    0.1  0.695775   2
  18. In [48]: pd.wide_to_long(dft, ["A", "B"], i="id", j="year")
  19. Out[48]: 
  20.                 X  A    B
  21. id year                  
  22. 0  1970 -0.121306  a  2.5
  23. 1  1970 -0.097883  b  1.2
  24. 2  1970  0.695775  c  0.7
  25. 0  1980 -0.121306  d  3.2
  26. 1  1980 -0.097883  e  1.3
  27. 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.

  1. In [49]: df
  2. Out[49]: 
  3. exp                  A         B                   A
  4. animal             cat       dog       cat       dog
  5. first second                                        
  6. bar   one     0.895717  0.805244 -1.206412  2.565646
  7.       two     1.431256  1.340309 -1.170299 -0.226169
  8. baz   one     0.410835  0.813850  0.132003 -0.827317
  9.       two    -0.076467 -1.187678  1.130127 -1.436737
  10. foo   one    -1.413681  1.607920  1.024180  0.569605
  11.       two     0.875906 -2.211372  0.974466 -2.006747
  12. qux   one    -0.410001 -0.078638  0.545952 -1.219217
  13.       two    -1.226825  0.769804 -1.281247 -0.727707
  15. In [50]: df.stack().mean(1).unstack()
  16. Out[50]: 
  17. animal             cat       dog
  18. first second                    
  19. bar   one    -0.155347  1.685445
  20.       two     0.130479  0.557070
  21. baz   one     0.271419 -0.006733
  22.       two     0.526830 -1.312207
  23. foo   one    -0.194750  1.088763
  24.       two     0.925186 -2.109060
  25. qux   one     0.067976 -0.648927
  26.       two    -1.254036  0.021048
  28. # same result, another way
  29. In [51]: df.groupby(level=1, axis=1).mean()
  30. Out[51]: 
  31. animal             cat       dog
  32. first second                    
  33. bar   one    -0.155347  1.685445
  34.       two     0.130479  0.557070
  35. baz   one     0.271419 -0.006733
  36.       two     0.526830 -1.312207
  37. foo   one    -0.194750  1.088763
  38.       two     0.925186 -2.109060
  39. qux   one     0.067976 -0.648927
  40.       two    -1.254036  0.021048
  42. In [52]: df.stack().groupby(level=1).mean()
  43. Out[52]: 
  44. exp            A         B
  45. second                    
  46. one     0.071448  0.455513
  47. two    -0.424186 -0.204486
  49. In [53]: df.mean().unstack(0)
  50. Out[53]: 
  51. exp            A         B
  52. animal                    
  53. cat     0.060843  0.018596
  54. dog    -0.413580  0.232430

Pivot tables

While pivot() provides general purpose pivoting with various data types (strings, numerics, etc.), pandas also provides pivot_table() for pivoting with aggregation of numeric data.

The function 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:

  1. In [54]: import datetime
  3. In [55]: df = pd.DataFrame({'A': ['one', 'one', 'two', 'three'] * 6,
  4.    ....:                    'B': ['A', 'B', 'C'] * 8,
  5.    ....:                    'C': ['foo', 'foo', 'foo', 'bar', 'bar', 'bar'] * 4,
  6.    ....:                    'D': np.random.randn(24),
  7.    ....:                    'E': np.random.randn(24),
  8.    ....:                    'F': [datetime.datetime(2013, i, 1) for i in range(1, 13)]
  9.    ....:                    + [datetime.datetime(2013, i, 15) for i in range(1, 13)]})
  10.    ....: 
  12. In [56]: df
  13. Out[56]: 
  14.         A  B    C         D         E          F
  15. 0     one  A  foo  0.341734 -0.317441 2013-01-01
  16. 1     one  B  foo  0.959726 -1.236269 2013-02-01
  17. 2     two  C  foo -1.110336  0.896171 2013-03-01
  18. 3   three  A  bar -0.619976 -0.487602 2013-04-01
  19. 4     one  B  bar  0.149748 -0.082240 2013-05-01
  20. ..    ... ..  ...       ...       ...        ...
  21. 19  three  B  foo  0.690579 -2.213588 2013-08-15
  22. 20    one  C  foo  0.995761  1.063327 2013-09-15
  23. 21    one  A  bar  2.396780  1.266143 2013-10-15
  24. 22    two  B  bar  0.014871  0.299368 2013-11-15
  25. 23  three  C  bar  3.357427 -0.863838 2013-12-15
  27. [24 rows x 6 columns]

We can produce pivot tables from this data very easily:

  1. In [57]: pd.pivot_table(df, values='D', index=['A', 'B'], columns=['C'])
  2. Out[57]: 
  3. C             bar       foo
  4. A     B                    
  5. one   A  1.120915 -0.514058
  6.       B -0.338421  0.002759
  7.       C -0.538846  0.699535
  8. three A -1.181568       NaN
  9.       B       NaN  0.433512
  10.       C  0.588783       NaN
  11. two   A       NaN  1.000985
  12.       B  0.158248       NaN
  13.       C       NaN  0.176180
  15. In [58]: pd.pivot_table(df, values='D', index=['B'], columns=['A', 'C'], aggfunc=np.sum)
  16. Out[58]: 
  17. A       one               three                 two          
  18. C       bar       foo       bar       foo       bar       foo
  19. B                                                            
  20. A  2.241830 -1.028115 -2.363137       NaN       NaN  2.001971
  21. B -0.676843  0.005518       NaN  0.867024  0.316495       NaN
  22. C -1.077692  1.399070  1.177566       NaN       NaN  0.352360
  24. In [59]: pd.pivot_table(df, values=['D', 'E'], index=['B'], columns=['A', 'C'],
  25.    ....:                aggfunc=np.sum)
  26.    ....: 
  27. Out[59]: 
  28.           D                                                           E                                                  
  29. A       one               three                 two                 one               three                 two          
  30. C       bar       foo       bar       foo       bar       foo       bar       foo       bar       foo       bar       foo
  31. B                                                                                                                        
  32. A  2.241830 -1.028115 -2.363137       NaN       NaN  2.001971  2.786113 -0.043211  1.922577       NaN       NaN  0.128491
  33. B -0.676843  0.005518       NaN  0.867024  0.316495       NaN  1.368280 -1.103384       NaN -2.128743 -0.194294       NaN
  34. C -1.077692  1.399070  1.177566       NaN       NaN  0.352360 -1.976883  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:

  1. In [60]: pd.pivot_table(df, index=['A', 'B'], columns=['C'])
  2. Out[60]: 
  3.                 D                   E          
  4. C             bar       foo       bar       foo
  5. A     B                                        
  6. one   A  1.120915 -0.514058  1.393057 -0.021605
  7.       B -0.338421  0.002759  0.684140 -0.551692
  8.       C -0.538846  0.699535 -0.988442  0.747859
  9. three A -1.181568       NaN  0.961289       NaN
  10.       B       NaN  0.433512       NaN -1.064372
  11.       C  0.588783       NaN -0.131830       NaN
  12. two   A       NaN  1.000985       NaN  0.064245
  13.       B  0.158248       NaN -0.097147       NaN
  14.       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.

  1. In [61]: pd.pivot_table(df, values='D', index=pd.Grouper(freq='M', key='F'),
  2.    ....:                columns='C')
  3.    ....: 
  4. Out[61]: 
  5. C                bar       foo
  6. F                             
  7. 2013-01-31       NaN -0.514058
  8. 2013-02-28       NaN  0.002759
  9. 2013-03-31       NaN  0.176180
  10. 2013-04-30 -1.181568       NaN
  11. 2013-05-31 -0.338421       NaN
  12. 2013-06-30 -0.538846       NaN
  13. 2013-07-31       NaN  1.000985
  14. 2013-08-31       NaN  0.433512
  15. 2013-09-30       NaN  0.699535
  16. 2013-10-31  1.120915       NaN
  17. 2013-11-30  0.158248       NaN
  18. 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:

  1. In [62]: table = pd.pivot_table(df, index=['A', 'B'], columns=['C'])
  3. In [63]: print(table.to_string(na_rep=''))
  4.                 D                   E          
  5. C             bar       foo       bar       foo
  6. A     B                                        
  7. one   A  1.120915 -0.514058  1.393057 -0.021605
  8.       B -0.338421  0.002759  0.684140 -0.551692
  9.       C -0.538846  0.699535 -0.988442  0.747859
  10. three A -1.181568            0.961289          
  11.       B            0.433512           -1.064372
  12.       C  0.588783           -0.131830          
  13. two   A            1.000985            0.064245
  14.       B  0.158248           -0.097147          
  15.       C            0.176180            0.436241

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

Adding margins

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:

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

Cross tabulations

Use crosstab() 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)
  • normalize: boolean, {‘all’, ‘index’, ‘columns’}, or {0,1}, default False. Normalize by dividing all values by the sum of values.

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

For example:

  1. In [65]: foo, bar, dull, shiny, one, two = 'foo', 'bar', 'dull', 'shiny', 'one', 'two'
  3. In [66]: a = np.array([foo, foo, bar, bar, foo, foo], dtype=object)
  5. In [67]: b = np.array([one, one, two, one, two, one], dtype=object)
  7. In [68]: c = np.array([dull, dull, shiny, dull, dull, shiny], dtype=object)
  9. In [69]: pd.crosstab(a, [b, c], rownames=['a'], colnames=['b', 'c'])
  10. Out[69]: 
  11. b    one        two      
  12. c   dull shiny dull shiny
  13. a                        
  14. bar    1     0    0     1
  15. foo    2     1    1     0

If crosstab receives only two Series, it will provide a frequency table.

  1. In [70]: df = pd.DataFrame({'A': [1, 2, 2, 2, 2], 'B': [3, 3, 4, 4, 4],
  2.    ....:                    'C': [1, 1, np.nan, 1, 1]})
  3.    ....: 
  5. In [71]: df
  6. Out[71]: 
  7.    A  B    C
  8. 0  1  3  1.0
  9. 1  2  3  1.0
  10. 2  2  4  NaN
  11. 3  2  4  1.0
  12. 4  2  4  1.0
  14. In [72]: pd.crosstab(df.A, df.B)
  15. Out[72]: 
  16. B  3  4
  17. A      
  18. 1  1  0
  19. 2  1  3

Any input passed containing Categorical data will have all of its categories included in the cross-tabulation, even if the actual data does not contain any instances of a particular category.

  1. In [73]: foo = pd.Categorical(['a', 'b'], categories=['a', 'b', 'c'])
  3. In [74]: bar = pd.Categorical(['d', 'e'], categories=['d', 'e', 'f'])
  5. In [75]: pd.crosstab(foo, bar)
  6. Out[75]: 
  7. col_0  d  e
  8. row_0      
  9. a      1  0
  10. b      0  1

Normalization

New in version 0.18.1.

Frequency tables can also be normalized to show percentages rather than counts using the normalize argument:

  1. In [76]: pd.crosstab(df.A, df.B, normalize=True)
  2. Out[76]: 
  3. B    3    4
  4. A          
  5. 1  0.2  0.0
  6. 2  0.2  0.6

normalize can also normalize values within each row or within each column:

  1. In [77]: pd.crosstab(df.A, df.B, normalize='columns')
  2. Out[77]: 
  3. B    3    4
  4. A          
  5. 1  0.5  0.0
  6. 2  0.5  1.0

crosstab can also be passed a third Series and an aggregation function (aggfunc) that will be applied to the values of the third Series within each group defined by the first two Series:

  1. In [78]: pd.crosstab(df.A, df.B, values=df.C, aggfunc=np.sum)
  2. Out[78]: 
  3. B    3    4
  4. A          
  5. 1  1.0  NaN
  6. 2  1.0  2.0

Adding margins

Finally, one can also add margins or normalize this output.

  1. In [79]: pd.crosstab(df.A, df.B, values=df.C, aggfunc=np.sum, normalize=True,
  2.    ....:             margins=True)
  3.    ....: 
  4. Out[79]: 
  5. B       3    4   All
  6. A                   
  7. 1    0.25  0.0  0.25
  8. 2    0.25  0.5  0.75
  9. All  0.50  0.5  1.00

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:

  1. In [80]: ages = np.array([10, 15, 13, 12, 23, 25, 28, 59, 60])
  3. In [81]: pd.cut(ages, bins=3)
  4. Out[81]: 
  5. [(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.0], (43.333, 60.0]]
  6. Categories (3, interval[float64]): [(9.95, 26.667] < (26.667, 43.333] < (43.333, 60.0]]

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

  1. In [82]: c = pd.cut(ages, bins=[0, 18, 35, 70])
  3. In [83]: c
  4. Out[83]: 
  5. [(0, 18], (0, 18], (0, 18], (0, 18], (18, 35], (18, 35], (18, 35], (35, 70], (35, 70]]
  6. Categories (3, interval[int64]): [(0, 18] < (18, 35] < (35, 70]]

New in version 0.20.0.

If the bins keyword is an IntervalIndex, then these will be used to bin the passed data.:

  1. pd.cut([25, 20, 50], bins=c.categories)

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 using get_dummies():

  1. In [84]: df = pd.DataFrame({'key': list('bbacab'), 'data1': range(6)})
  3. In [85]: pd.get_dummies(df['key'])
  4. Out[85]: 
  5.    a  b  c
  6. 0  0  1  0
  7. 1  0  1  0
  8. 2  1  0  0
  9. 3  0  0  1
  10. 4  1  0  0
  11. 5  0  1  0

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

  1. In [86]: dummies = pd.get_dummies(df['key'], prefix='key')
  3. In [87]: dummies
  4. Out[87]: 
  5.    key_a  key_b  key_c
  6. 0      0      1      0
  7. 1      0      1      0
  8. 2      1      0      0
  9. 3      0      0      1
  10. 4      1      0      0
  11. 5      0      1      0
  13. In [88]: df[['data1']].join(dummies)
  14. Out[88]: 
  15.    data1  key_a  key_b  key_c
  16. 0      0      0      1      0
  17. 1      1      0      1      0
  18. 2      2      1      0      0
  19. 3      3      0      0      1
  20. 4      4      1      0      0
  21. 5      5      0      1      0

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

  1. In [89]: values = np.random.randn(10)
  3. In [90]: values
  4. Out[90]: 
  5. array([ 0.4082, -1.0481, -0.0257, -0.9884,  0.0941,  1.2627,  1.29  ,
  6.         0.0824, -0.0558,  0.5366])
  8. In [91]: bins = [0, 0.2, 0.4, 0.6, 0.8, 1]
  10. In [92]: pd.get_dummies(pd.cut(values, bins))
  11. Out[92]: 
  12.    (0.0, 0.2]  (0.2, 0.4]  (0.4, 0.6]  (0.6, 0.8]  (0.8, 1.0]
  13. 0           0           0           1           0           0
  14. 1           0           0           0           0           0
  15. 2           0           0           0           0           0
  16. 3           0           0           0           0           0
  17. 4           1           0           0           0           0
  18. 5           0           0           0           0           0
  19. 6           0           0           0           0           0
  20. 7           1           0           0           0           0
  21. 8           0           0           0           0           0
  22. 9           0           0           1           0           0

See also Series.str.get_dummies.

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.

  1. In [93]: df = pd.DataFrame({'A': ['a', 'b', 'a'], 'B': ['c', 'c', 'b'],
  2.    ....:                    'C': [1, 2, 3]})
  3.    ....: 
  5. In [94]: pd.get_dummies(df)
  6. Out[94]: 
  7.    C  A_a  A_b  B_b  B_c
  8. 0  1    1    0    0    1
  9. 1  2    0    1    0    1
  10. 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.

  1. In [95]: pd.get_dummies(df, columns=['A'])
  2. Out[95]: 
  3.    B  C  A_a  A_b
  4. 0  c  1    1    0
  5. 1  c  2    0    1
  6. 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.
  1. In [96]: simple = pd.get_dummies(df, prefix='new_prefix')
  3. In [97]: simple
  4. Out[97]: 
  5.    C  new_prefix_a  new_prefix_b  new_prefix_b  new_prefix_c
  6. 0  1             1             0             0             1
  7. 1  2             0             1             0             1
  8. 2  3             1             0             1             0
  10. In [98]: from_list = pd.get_dummies(df, prefix=['from_A', 'from_B'])
  12. In [99]: from_list
  13. Out[99]: 
  14.    C  from_A_a  from_A_b  from_B_b  from_B_c
  15. 0  1         1         0         0         1
  16. 1  2         0         1         0         1
  17. 2  3         1         0         1         0
  19. In [100]: from_dict = pd.get_dummies(df, prefix={'B': 'from_B', 'A': 'from_A'})
  21. In [101]: from_dict
  22. Out[101]: 
  23.    C  from_A_a  from_A_b  from_B_b  from_B_c
  24. 0  1         1         0         0         1
  25. 1  2         0         1         0         1
  26. 2  3         1         0         1         0

New in version 0.18.0.

Sometimes it will be useful to only keep k-1 levels of a categorical variable to avoid collinearity when feeding the result to statistical models. You can switch to this mode by turn on drop_first.

  1. In [102]: s = pd.Series(list('abcaa'))
  3. In [103]: pd.get_dummies(s)
  4. Out[103]: 
  5.    a  b  c
  6. 0  1  0  0
  7. 1  0  1  0
  8. 2  0  0  1
  9. 3  1  0  0
  10. 4  1  0  0
  12. In [104]: pd.get_dummies(s, drop_first=True)
  13. Out[104]: 
  14.    b  c
  15. 0  0  0
  16. 1  1  0
  17. 2  0  1
  18. 3  0  0
  19. 4  0  0

When a column contains only one level, it will be omitted in the result.

  1. In [105]: df = pd.DataFrame({'A': list('aaaaa'), 'B': list('ababc')})
  3. In [106]: pd.get_dummies(df)
  4. Out[106]: 
  5.    A_a  B_a  B_b  B_c
  6. 0    1    1    0    0
  7. 1    1    0    1    0
  8. 2    1    1    0    0
  9. 3    1    0    1    0
  10. 4    1    0    0    1
  12. In [107]: pd.get_dummies(df, drop_first=True)
  13. Out[107]: 
  14.    B_b  B_c
  15. 0    0    0
  16. 1    1    0
  17. 2    0    0
  18. 3    1    0
  19. 4    0    1

By default new columns will have np.uint8 dtype. To choose another dtype, use the dtype argument:

  1. In [108]: df = pd.DataFrame({'A': list('abc'), 'B': [1.1, 2.2, 3.3]})
  3. In [109]: pd.get_dummies(df, dtype=bool).dtypes
  4. Out[109]: 
  5. B      float64
  6. A_a       bool
  7. A_b       bool
  8. A_c       bool
  9. dtype: object

New in version 0.23.0.

Factorizing values

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

  1. In [110]: x = pd.Series(['A', 'A', np.nan, 'B', 3.14, np.inf])
  3. In [111]: x
  4. Out[111]: 
  5. 0       A
  6. 1       A
  7. 2     NaN
  8. 3       B
  9. 4    3.14
  10. 5     inf
  11. dtype: object
  13. In [112]: labels, uniques = pd.factorize(x)
  15. In [113]: labels
  16. Out[113]: array([ 0,  0, -1,  1,  2,  3])
  18. In [114]: uniques
  19. Out[114]: Index(['A', 'B', 3.14, inf], dtype='object')

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

::: tip Note

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

:::

  1. In [1]: x = pd.Series(['A', 'A', np.nan, 'B', 3.14, np.inf])
  2. In [2]: pd.factorize(x, sort=True)
  3. Out[2]:
  4. (array([ 2,  2, -1,  3,  0,  1]),
  5.  Index([3.14, inf, 'A', 'B'], dtype='object'))
  7. In [3]: np.unique(x, return_inverse=True)[::-1]
  8. Out[3]: (array([3, 3, 0, 4, 1, 2]), array([nan, 3.14, inf, 'A', 'B'], dtype=object))

::: tip 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.

:::

Examples

In this section, we will review frequently asked questions and examples. The column names and relevant column values are named to correspond with how this DataFrame will be pivoted in the answers below.

  1. In [115]: np.random.seed([3, 1415])
  3. In [116]: n = 20
  5. In [117]: cols = np.array(['key', 'row', 'item', 'col'])
  7. In [118]: df = cols + pd.DataFrame((np.random.randint(5, size=(n, 4))
  8.    .....:                          // [2, 1, 2, 1]).astype(str))
  9.    .....: 
  11. In [119]: df.columns = cols
  13. In [120]: df = df.join(pd.DataFrame(np.random.rand(n, 2).round(2)).add_prefix('val'))
  15. In [121]: df
  16. Out[121]: 
  17.      key   row   item   col  val0  val1
  18. 0   key0  row3  item1  col3  0.81  0.04
  19. 1   key1  row2  item1  col2  0.44  0.07
  20. 2   key1  row0  item1  col0  0.77  0.01
  21. 3   key0  row4  item0  col2  0.15  0.59
  22. 4   key1  row0  item2  col1  0.81  0.64
  23. ..   ...   ...    ...   ...   ...   ...
  24. 15  key0  row3  item1  col1  0.31  0.23
  25. 16  key0  row0  item2  col3  0.86  0.01
  26. 17  key0  row4  item0  col3  0.64  0.21
  27. 18  key2  row2  item2  col0  0.13  0.45
  28. 19  key0  row2  item0  col4  0.37  0.70
  30. [20 rows x 6 columns]

Pivoting with single aggregations

Suppose we wanted to pivot df such that the col values are columns, row values are the index, and the mean of val0 are the values? In particular, the resulting DataFrame should look like:

::: tip Note

col col0 col1 col2 col3 col4 row row0 0.77 0.605 NaN 0.860 0.65 row2 0.13 NaN 0.395 0.500 0.25 row3 NaN 0.310 NaN 0.545 NaN row4 NaN 0.100 0.395 0.760 0.24

:::

This solution uses pivot_table(). Also note that aggfunc='mean' is the default. It is included here to be explicit.

  1. In [122]: df.pivot_table(
  2.    .....:     values='val0', index='row', columns='col', aggfunc='mean')
  3.    .....: 
  4. Out[122]: 
  5. col   col0   col1   col2   col3  col4
  6. row                                  
  7. row0  0.77  0.605    NaN  0.860  0.65
  8. row2  0.13    NaN  0.395  0.500  0.25
  9. row3   NaN  0.310    NaN  0.545   NaN
  10. row4   NaN  0.100  0.395  0.760  0.24

Note that we can also replace the missing values by using the fill_value parameter.

  1. In [123]: df.pivot_table(
  2.    .....:     values='val0', index='row', columns='col', aggfunc='mean', fill_value=0)
  3.    .....: 
  4. Out[123]: 
  5. col   col0   col1   col2   col3  col4
  6. row                                  
  7. row0  0.77  0.605  0.000  0.860  0.65
  8. row2  0.13  0.000  0.395  0.500  0.25
  9. row3  0.00  0.310  0.000  0.545  0.00
  10. row4  0.00  0.100  0.395  0.760  0.24

Also note that we can pass in other aggregation functions as well. For example, we can also pass in sum.

  1. In [124]: df.pivot_table(
  2.    .....:     values='val0', index='row', columns='col', aggfunc='sum', fill_value=0)
  3.    .....: 
  4. Out[124]: 
  5. col   col0  col1  col2  col3  col4
  6. row                               
  7. row0  0.77  1.21  0.00  0.86  0.65
  8. row2  0.13  0.00  0.79  0.50  0.50
  9. row3  0.00  0.31  0.00  1.09  0.00
  10. row4  0.00  0.10  0.79  1.52  0.24

Another aggregation we can do is calculate the frequency in which the columns and rows occur together a.k.a. “cross tabulation”. To do this, we can pass size to the aggfunc parameter.

  1. In [125]: df.pivot_table(index='row', columns='col', fill_value=0, aggfunc='size')
  2. Out[125]: 
  3. col   col0  col1  col2  col3  col4
  4. row                               
  5. row0     1     2     0     1     1
  6. row2     1     0     2     1     2
  7. row3     0     1     0     2     0
  8. row4     0     1     2     2     1

Pivoting with multiple aggregations

We can also perform multiple aggregations. For example, to perform both a sum and mean, we can pass in a list to the aggfunc argument.

  1. In [126]: df.pivot_table(
  2.    .....:     values='val0', index='row', columns='col', aggfunc=['mean', 'sum'])
  3.    .....: 
  4. Out[126]: 
  5.       mean                              sum                        
  6. col   col0   col1   col2   col3  col4  col0  col1  col2  col3  col4
  7. row                                                                
  8. row0  0.77  0.605    NaN  0.860  0.65  0.77  1.21   NaN  0.86  0.65
  9. row2  0.13    NaN  0.395  0.500  0.25  0.13   NaN  0.79  0.50  0.50
  10. row3   NaN  0.310    NaN  0.545   NaN   NaN  0.31   NaN  1.09   NaN
  11. row4   NaN  0.100  0.395  0.760  0.24   NaN  0.10  0.79  1.52  0.24

Note to aggregate over multiple value columns, we can pass in a list to the values parameter.

  1. In [127]: df.pivot_table(
  2.    .....:     values=['val0', 'val1'], index='row', columns='col', aggfunc=['mean'])
  3.    .....: 
  4. Out[127]: 
  5.       mean                                                           
  6.       val0                             val1                          
  7. col   col0   col1   col2   col3  col4  col0   col1  col2   col3  col4
  8. row                                                                  
  9. row0  0.77  0.605    NaN  0.860  0.65  0.01  0.745   NaN  0.010  0.02
  10. row2  0.13    NaN  0.395  0.500  0.25  0.45    NaN  0.34  0.440  0.79
  11. row3   NaN  0.310    NaN  0.545   NaN   NaN  0.230   NaN  0.075   NaN
  12. row4   NaN  0.100  0.395  0.760  0.24   NaN  0.070  0.42  0.300  0.46

Note to subdivide over multiple columns we can pass in a list to the columns parameter.

  1. In [128]: df.pivot_table(
  2.    .....:     values=['val0'], index='row', columns=['item', 'col'], aggfunc=['mean'])
  3.    .....: 
  4. Out[128]: 
  5.       mean                                                                   
  6.       val0                                                                   
  7. item item0             item1                         item2                   
  8. col   col2  col3  col4  col0  col1  col2  col3  col4  col0   col1  col3  col4
  9. row                                                                          
  10. row0   NaN   NaN   NaN  0.77   NaN   NaN   NaN   NaN   NaN  0.605  0.86  0.65
  11. row2  0.35   NaN  0.37   NaN   NaN  0.44   NaN   NaN  0.13    NaN  0.50  0.13
  12. row3   NaN   NaN   NaN   NaN  0.31   NaN  0.81   NaN   NaN    NaN  0.28   NaN
  13. row4  0.15  0.64   NaN   NaN  0.10  0.64  0.88  0.24   NaN    NaN   NaN   NaN

Exploding a list-like column

New in version 0.25.0.

Sometimes the values in a column are list-like.

  1. In [129]: keys = ['panda1', 'panda2', 'panda3']
  3. In [130]: values = [['eats', 'shoots'], ['shoots', 'leaves'], ['eats', 'leaves']]
  5. In [131]: df = pd.DataFrame({'keys': keys, 'values': values})
  7. In [132]: df
  8. Out[132]: 
  9.      keys            values
  10. 0  panda1    [eats, shoots]
  11. 1  panda2  [shoots, leaves]
  12. 2  panda3    [eats, leaves]

We can ‘explode’ the values column, transforming each list-like to a separate row, by using explode(). This will replicate the index values from the original row:

  1. In [133]: df['values'].explode()
  2. Out[133]: 
  3. 0      eats
  4. 0    shoots
  5. 1    shoots
  6. 1    leaves
  7. 2      eats
  8. 2    leaves
  9. Name: values, dtype: object

You can also explode the column in the DataFrame.

  1. In [134]: df.explode('values')
  2. Out[134]: 
  3.      keys  values
  4. 0  panda1    eats
  5. 0  panda1  shoots
  6. 1  panda2  shoots
  7. 1  panda2  leaves
  8. 2  panda3    eats
  9. 2  panda3  leaves

Series.explode() will replace empty lists with np.nan and preserve scalar entries. The dtype of the resulting Series is always object.

  1. In [135]: s = pd.Series([[1, 2, 3], 'foo', [], ['a', 'b']])
  3. In [136]: s
  4. Out[136]: 
  5. 0    [1, 2, 3]
  6. 1          foo
  7. 2           []
  8. 3       [a, b]
  9. dtype: object
  11. In [137]: s.explode()
  12. Out[137]: 
  13. 0      1
  14. 0      2
  15. 0      3
  16. 1    foo
  17. 2    NaN
  18. 3      a
  19. 3      b
  20. dtype: object

Here is a typical usecase. You have comma separated strings in a column and want to expand this.

  1. In [138]: df = pd.DataFrame([{'var1': 'a,b,c', 'var2': 1},
  2.    .....:                    {'var1': 'd,e,f', 'var2': 2}])
  3.    .....: 
  5. In [139]: df
  6. Out[139]: 
  7.     var1  var2
  8. 0  a,b,c     1
  9. 1  d,e,f     2

Creating a long form DataFrame is now straightforward using explode and chained operations

  1. In [140]: df.assign(var1=df.var1.str.split(',')).explode('var1')
  2. Out[140]: 
  3.   var1  var2
  4. 0    a     1
  5. 0    b     1
  6. 0    c     1
  7. 1    d     2
  8. 1    e     2
  9. 1    f     2