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
date variable value
0 2000-01-03 00:00:00 A 0.469112
1 2000-01-04 00:00:00 A -0.282863
2 2000-01-05 00:00:00 A -1.509059
3 2000-01-03 00:00:00 B -1.135632
4 2000-01-04 00:00:00 B 1.212112
5 2000-01-05 00:00:00 B -0.173215
6 2000-01-03 00:00:00 C 0.119209
7 2000-01-04 00:00:00 C -1.044236
8 2000-01-05 00:00:00 C -0.861849
9 2000-01-03 00:00:00 D -2.104569
10 2000-01-04 00:00:00 D -0.494929
11 2000-01-05 00:00:00 D 1.071804
[12 rows x 3 columns]
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 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']
date variable value
0 2000-01-03 00:00:00 A 0.469112
1 2000-01-04 00:00:00 A -0.282863
2 2000-01-05 00:00:00 A -1.509059
[3 rows x 3 columns]
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')
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
[3 rows x 4 columns]
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
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
[3 rows x 8 columns]
You of course can then select subsets from the pivoted DataFrame:
In [7]: pivoted['value2']
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
[3 rows x 4 columns]
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 = MultiIndex.from_tuples(tuples, names=['first', 'second'])
In [10]: df = DataFrame(randn(8, 2), index=index, columns=['A', 'B'])
In [11]: df2 = df[:4]
In [12]: df2
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
[4 rows x 2 columns]
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
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()
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
[4 rows x 2 columns]
In [16]: stacked.unstack(1)
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
[4 rows x 2 columns]
In [17]: stacked.unstack(0)
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
[4 rows x 2 columns]
If the indexes have names, you can use the level names instead of specifying the level numbers:
In [18]: stacked.unstack('second')
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
[4 rows x 2 columns]
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.
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 sortlevel, of course). Here is a more complex example:
In [19]: columns = MultiIndex.from_tuples([('A', 'cat'), ('B', 'dog'),
....: ('B', 'cat'), ('A', 'dog')],
....: names=['exp', 'animal'])
....:
In [20]: df = DataFrame(randn(8, 4), index=index, columns=columns)
In [21]: df2 = df.ix[[0, 1, 2, 4, 5, 7]]
In [22]: df2
exp A B A
animal cat dog cat dog
first second
bar one -0.370647 -1.157892 -1.344312 0.844885
two 1.075770 -0.109050 1.643563 -1.469388
baz one 0.357021 -0.674600 -1.776904 -0.968914
foo one -0.013960 -0.362543 -0.006154 -0.923061
two 0.895717 0.805244 -1.206412 2.565646
qux two 0.410835 0.813850 0.132003 -0.827317
[6 rows x 4 columns]
As mentioned above, stack can be called with a level argument to select which level in the columns to stack:
In [23]: df2.stack('exp')
animal cat dog
first second exp
bar one A -0.370647 0.844885
B -1.344312 -1.157892
two A 1.075770 -1.469388
B 1.643563 -0.109050
baz one A 0.357021 -0.968914
B -1.776904 -0.674600
foo one A -0.013960 -0.923061
B -0.006154 -0.362543
two A 0.895717 2.565646
B -1.206412 0.805244
qux two A 0.410835 -0.827317
B 0.132003 0.813850
[12 rows x 2 columns]
In [24]: df2.stack('animal')
exp A B
first second animal
bar one cat -0.370647 -1.344312
dog 0.844885 -1.157892
two cat 1.075770 1.643563
dog -1.469388 -0.109050
baz one cat 0.357021 -1.776904
dog -0.968914 -0.674600
foo one cat -0.013960 -0.006154
dog -0.923061 -0.362543
two cat 0.895717 -1.206412
dog 2.565646 0.805244
qux two cat 0.410835 0.132003
dog -0.827317 0.813850
[12 rows x 2 columns]
Unstacking when the columns are a MultiIndex is also careful about doing the right thing:
In [25]: df[:3].unstack(0)
exp A B A \
animal cat dog cat dog
first bar baz bar baz bar baz bar
second
one -0.370647 0.357021 -1.157892 -0.6746 -1.344312 -1.776904 0.844885
two 1.075770 NaN -0.109050 NaN 1.643563 NaN -1.469388
exp
animal
first baz
second
one -0.968914
two NaN
[2 rows x 8 columns]
In [26]: df2.unstack(1)
exp A B A \
animal cat dog cat dog
second one two one two one two one
first
bar -0.370647 1.075770 -1.157892 -0.109050 -1.344312 1.643563 0.844885
baz 0.357021 NaN -0.674600 NaN -1.776904 NaN -0.968914
foo -0.013960 0.895717 -0.362543 0.805244 -0.006154 -1.206412 -0.923061
qux NaN 0.410835 NaN 0.813850 NaN 0.132003 NaN
exp
animal
second two
first
bar -1.469388
baz NaN
foo 2.565646
qux -0.827317
[4 rows x 8 columns]
Reshaping by Melt¶
The melt function found in pandas.core.reshape 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 “pivoted” 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 [27]: cheese = DataFrame({'first' : ['John', 'Mary'],
....: 'last' : ['Doe', 'Bo'],
....: 'height' : [5.5, 6.0],
....: 'weight' : [130, 150]})
....:
In [28]: cheese
first height last weight
0 John 5.5 Doe 130
1 Mary 6.0 Bo 150
[2 rows x 4 columns]
In [29]: melt(cheese, id_vars=['first', 'last'])
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
[4 rows x 4 columns]
In [30]: melt(cheese, id_vars=['first', 'last'], var_name='quantity')
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
[4 rows x 4 columns]
Another way to transform is to use the wide_to_long panel data convenience function.
In [31]: 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 [32]: dft["id"] = dft.index
In [33]: df
exp A B A
animal cat dog cat dog
first second
bar one -0.370647 -1.157892 -1.344312 0.844885
two 1.075770 -0.109050 1.643563 -1.469388
baz one 0.357021 -0.674600 -1.776904 -0.968914
two -1.294524 0.413738 0.276662 -0.472035
foo one -0.013960 -0.362543 -0.006154 -0.923061
two 0.895717 0.805244 -1.206412 2.565646
qux one 1.431256 1.340309 -1.170299 -0.226169
two 0.410835 0.813850 0.132003 -0.827317
[8 rows x 4 columns]
In [34]: pd.wide_to_long(dft, ["A", "B"], i="id", j="year")
X A B
id year
0 1970 -0.076467 a 2.5
1 1970 -1.187678 b 1.2
2 1970 1.130127 c 0.7
0 1980 -0.076467 d 3.2
1 1980 -1.187678 e 1.3
2 1980 1.130127 f 0.1
[6 rows x 3 columns]
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 [35]: df
exp A B A
animal cat dog cat dog
first second
bar one -0.370647 -1.157892 -1.344312 0.844885
two 1.075770 -0.109050 1.643563 -1.469388
baz one 0.357021 -0.674600 -1.776904 -0.968914
two -1.294524 0.413738 0.276662 -0.472035
foo one -0.013960 -0.362543 -0.006154 -0.923061
two 0.895717 0.805244 -1.206412 2.565646
qux one 1.431256 1.340309 -1.170299 -0.226169
two 0.410835 0.813850 0.132003 -0.827317
[8 rows x 4 columns]
In [36]: df.stack().mean(1).unstack()
animal cat dog
first second
bar one -0.857479 -0.156504
two 1.359666 -0.789219
baz one -0.709942 -0.821757
two -0.508931 -0.029148
foo one -0.010057 -0.642802
two -0.155347 1.685445
qux one 0.130479 0.557070
two 0.271419 -0.006733
[8 rows x 2 columns]
# same result, another way
In [37]: df.groupby(level=1, axis=1).mean()
animal cat dog
first second
bar one -0.857479 -0.156504
two 1.359666 -0.789219
baz one -0.709942 -0.821757
two -0.508931 -0.029148
foo one -0.010057 -0.642802
two -0.155347 1.685445
qux one 0.130479 0.557070
two 0.271419 -0.006733
[8 rows x 2 columns]
In [38]: df.stack().groupby(level=1).mean()
exp A B
second
one 0.016301 -0.644049
two 0.110588 0.346200
[2 rows x 2 columns]
In [39]: df.mean().unstack(0)
exp A B
animal
cat 0.311433 -0.431481
dog -0.184544 0.133632
[2 rows x 2 columns]
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
- rows: list of columns to group by on the table rows
- cols: list of columns to group by on the table columns
- aggfunc: function to use for aggregation, defaulting to numpy.mean
Consider a data set like this:
In [40]: df = 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)})
....:
In [41]: df
A B C D E
0 one A foo -1.436737 0.149748
1 one B foo -1.413681 -0.732339
2 two C foo 1.607920 0.687738
3 three A bar 1.024180 0.176444
4 one B bar 0.569605 0.403310
5 one C bar 0.875906 -0.154951
6 two A foo -2.211372 0.301624
7 three B foo 0.974466 -2.179861
8 one C foo -2.006747 -1.369849
9 one A bar -0.410001 -0.954208
10 two B bar -0.078638 1.462696
11 three C bar 0.545952 -1.743161
12 one A foo -1.219217 -0.826591
13 one B foo -1.226825 -0.345352
14 two C foo 0.769804 1.314232
... .. ... ... ...
[24 rows x 5 columns]
We can produce pivot tables from this data very easily:
In [42]: pivot_table(df, values='D', rows=['A', 'B'], cols=['C'])
C bar foo
A B
one A 0.274863 -1.327977
B -0.079051 -1.320253
C 0.377300 -0.832506
three A -0.128534 NaN
B NaN 0.835120
C -0.037012 NaN
two A NaN -1.154627
B -0.594487 NaN
C NaN 1.188862
[9 rows x 2 columns]
In [43]: pivot_table(df, values='D', rows=['B'], cols=['A', 'C'], aggfunc=np.sum)
A one three two
C bar foo bar foo bar foo
B
A 0.549725 -2.655954 -0.257067 NaN NaN -2.309255
B -0.158102 -2.640506 NaN 1.670241 -1.188974 NaN
C 0.754600 -1.665013 -0.074024 NaN NaN 2.377724
[3 rows x 6 columns]
In [44]: pivot_table(df, values=['D','E'], rows=['B'], cols=['A', 'C'], aggfunc=np.sum)
D E \
A one three two one
C bar foo bar foo bar foo bar
B
A 0.549725 -2.655954 -0.257067 NaN NaN -2.309255 -2.190477
B -0.158102 -2.640506 NaN 1.670241 -1.188974 NaN 1.399070
C 0.754600 -1.665013 -0.074024 NaN NaN 2.377724 2.241830
A three two
C foo bar foo bar foo
B
A -0.676843 0.867024 NaN NaN 0.316495
B -1.077692 NaN 1.177566 2.358867 NaN
C -1.687290 -2.230762 NaN NaN 2.001971
[3 rows x 12 columns]
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 [45]: pivot_table(df, rows=['A', 'B'], cols=['C'])
D E
C bar foo bar foo
A B
one A 0.274863 -1.327977 -1.095238 -0.338421
B -0.079051 -1.320253 0.699535 -0.538846
C 0.377300 -0.832506 1.120915 -0.843645
three A -0.128534 NaN 0.433512 NaN
B NaN 0.835120 NaN 0.588783
C -0.037012 NaN -1.115381 NaN
two A NaN -1.154627 NaN 0.158248
B -0.594487 NaN 1.179433 NaN
C NaN 1.188862 NaN 1.000985
[9 rows x 4 columns]
You can render a nice output of the table omitting the missing values by calling to_string if you wish:
In [46]: table = pivot_table(df, rows=['A', 'B'], cols=['C'])
In [47]: print(table.to_string(na_rep=''))
D E
C bar foo bar foo
A B
one A 0.274863 -1.327977 -1.095238 -0.338421
B -0.079051 -1.320253 0.699535 -0.538846
C 0.377300 -0.832506 1.120915 -0.843645
three A -0.128534 0.433512
B 0.835120 0.588783
C -0.037012 -1.115381
two A -1.154627 0.158248
B -0.594487 1.179433
C 1.188862 1.000985
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
- rows: array-like, values to group by in the rows
- cols: 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 [48]: foo, bar, dull, shiny, one, two = 'foo', 'bar', 'dull', 'shiny', 'one', 'two'
In [49]: a = np.array([foo, foo, bar, bar, foo, foo], dtype=object)
In [50]: b = np.array([one, one, two, one, two, one], dtype=object)
In [51]: c = np.array([dull, dull, shiny, dull, dull, shiny], dtype=object)
In [52]: crosstab(a, [b, c], rownames=['a'], colnames=['b', 'c'])
b one two
c dull shiny dull shiny
a
bar 1 0 0 1
foo 2 1 1 0
[2 rows x 4 columns]
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 [53]: df.pivot_table(rows=['A', 'B'], cols='C', margins=True, aggfunc=np.std)
D E
C bar foo All bar foo All
A B
one A 0.968543 0.153810 1.084870 0.199447 0.690376 0.602542
B 0.917338 0.132127 0.894343 0.418926 0.273641 0.771139
C 0.705136 1.660627 1.254131 1.804346 0.744165 1.598848
three A 1.630183 NaN 1.630183 0.363548 NaN 0.363548
B NaN 0.197065 0.197065 NaN 3.915454 3.915454
C 0.824435 NaN 0.824435 0.887815 NaN 0.887815
two A NaN 1.494463 1.494463 NaN 0.202765 0.202765
B 0.729521 NaN 0.729521 0.400594 NaN 0.400594
C NaN 0.592638 0.592638 NaN 0.442998 0.442998
All 0.816058 1.294620 1.055572 1.190502 1.403041 1.249705
[10 rows x 6 columns]
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 [54]: ages = np.array([10, 15, 13, 12, 23, 25, 28, 59, 60])
In [55]: cut(ages, bins=3)
(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]
Levels (3): Index(['(9.95, 26.667]', '(26.667, 43.333]', '(43.333, 60]'], dtype=object)
If the bins keyword is an integer, then equal-width bins are formed. Alternatively we can specify custom bin-edges:
In [56]: cut(ages, bins=[0, 18, 35, 70])
(0, 18]
(0, 18]
(0, 18]
(0, 18]
(18, 35]
(18, 35]
(18, 35]
(35, 70]
(35, 70]
Levels (3): Index(['(0, 18]', '(18, 35]', '(35, 70]'], dtype=object)
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 [57]: df = DataFrame({'key': list('bbacab'), 'data1': range(6)})
In [58]: get_dummies(df['key'])
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
[6 rows x 3 columns]
Sometimes it’s useful to prefix the column names, for example when merging the result with the original DataFrame:
In [59]: dummies = get_dummies(df['key'], prefix='key')
In [60]: dummies
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
[6 rows x 3 columns]
In [61]: df[['data1']].join(dummies)
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
[6 rows x 4 columns]
This function is often used along with discretization functions like cut:
In [62]: values = randn(10)
In [63]: values
array([-0.0822, -2.1829, 0.3804, 0.0848, 0.4324, 1.52 , -0.4937,
0.6002, 0.2742, 0.1329])
In [64]: bins = [0, 0.2, 0.4, 0.6, 0.8, 1]
In [65]: get_dummies(cut(values, bins))
(0, 0.2] (0.2, 0.4] (0.4, 0.6] (0.6, 0.8]
0 0 0 0 0
1 0 0 0 0
2 0 1 0 0
3 1 0 0 0
4 0 0 1 0
5 0 0 0 0
6 0 0 0 0
7 0 0 0 1
8 0 1 0 0
9 1 0 0 0
[10 rows x 4 columns]
Factorizing values¶
To encode 1-d values as an enumerated type use factorize:
In [66]: x = pd.Series(['A', 'A', np.nan, 'B', 3.14, np.inf])
In [67]: x
0 A
1 A
2 NaN
3 B
4 3.14
5 inf
dtype: object
In [68]: labels, uniques = pd.factorize(x)
In [69]: labels
array([ 0, 0, -1, 1, 2, 3])
In [70]: uniques
array(['A', 'B', 3.14, inf], dtype=object)
Note that factorize is similar to numpy.unique, but differs in its handling of NaN:
In [71]: pd.factorize(x, sort=True)
(array([ 2, 2, -1, 3, 0, 1]), array([3.14, inf, 'A', 'B'], dtype=object))
In [72]: np.unique(x, return_inverse=True)[::-1]
(array([3, 3, 0, 4, 1, 2]), array([nan, 3.14, inf, 'A', 'B'], dtype=object))