How can I pivot a dataframe?

We start by answering the first question:

Question 1

Why do I get ValueError: Index contains duplicate entries, cannot reshape

This occurs because pandas is attempting to reindex either a columns or index object with duplicate entries. There are varying methods to use that can perform a pivot. Some of them are not well suited to when there are duplicates of the keys in which it is being asked to pivot on. For example. Consider pd.DataFrame.pivot. I know there are duplicate entries that share the row and col values:

df.duplicated(['row', 'col']).any()

True

So when I pivot using

df.pivot(index='row', columns='col', values='val0')

I get the error mentioned above. In fact, I get the same error when I try to perform the same task with:

df.set_index(['row', 'col'])['val0'].unstack()

Here is a list of idioms we can use to pivot

pd.DataFrame.groupby + pd.DataFrame.unstack Good general approach for doing just about any type of pivot You specify all columns that will constitute the pivoted row levels and column levels in one group by. You follow that by selecting the remaining columns you want to aggregate and the function(s) you want to perform the aggregation. Finally, you unstack the levels that you want to be in the column index. pd.DataFrame.pivot_table A glorified version of groupby with more intuitive API. For many people, this is the preferred approach. And is the intended approach by the developers. Specify row level, column levels, values to be aggregated, and function(s) to perform aggregations. pd.DataFrame.set_index + pd.DataFrame.unstack Convenient and intuitive for some (myself included). Cannot handle duplicate grouped keys. Similar to the groupby paradigm, we specify all columns that will eventually be either row or column levels and set those to be the index. We then unstack the levels we want in the columns. If either the remaining index levels or column levels are not unique, this method will fail. pd.DataFrame.pivot Very similar to set_index in that it shares the duplicate key limitation. The API is very limited as well. It only takes scalar values for index, columns, values. Similar to the pivot_table method in that we select rows, columns, and values on which to pivot. However, we cannot aggregate and if either rows or columns are not unique, this method will fail. pd.crosstab This a specialized version of pivot_table and in its purest form is the most intuitive way to perform several tasks. pd.factorize + np.bincount This is a highly advanced technique that is very obscure but is very fast. It cannot be used in all circumstances, but when it can be used and you are comfortable using it, you will reap the performance rewards. pd.get_dummies + pd.DataFrame.dot I use this for cleverly performing cross tabulation.

Examples

What I'm going to do for each subsequent answer and question is to answer it using pd.DataFrame.pivot_table. Then I'll provide alternatives to perform the same task.

Question 3

How do I pivot df such that the col values are columns, row values are the index, mean of val0 are the values, and missing values are 0?

pd.DataFrame.pivot_table fill_value is not set by default. I tend to set it appropriately. In this case I set it to 0. Notice I skipped question 2 as it's the same as this answer without the fill_value aggfunc='mean' is the default and I didn't have to set it. I included it to be explicit. df.pivot_table( values='val0', index='row', columns='col', fill_value=0, aggfunc='mean') col col0 col1 col2 col3 col4 row row0 0.77 0.605 0.000 0.860 0.65 row2 0.13 0.000 0.395 0.500 0.25 row3 0.00 0.310 0.000 0.545 0.00 row4 0.00 0.100 0.395 0.760 0.24

fill_value is not set by default. I tend to set it appropriately. In this case I set it to 0. Notice I skipped question 2 as it's the same as this answer without the fill_value

aggfunc='mean' is the default and I didn't have to set it. I included it to be explicit. df.pivot_table( values='val0', index='row', columns='col', fill_value=0, aggfunc='mean') col col0 col1 col2 col3 col4 row row0 0.77 0.605 0.000 0.860 0.65 row2 0.13 0.000 0.395 0.500 0.25 row3 0.00 0.310 0.000 0.545 0.00 row4 0.00 0.100 0.395 0.760 0.24

pd.DataFrame.groupby df.groupby(['row', 'col'])['val0'].mean().unstack(fill_value=0)

pd.crosstab pd.crosstab( index=df['row'], columns=df['col'], values=df['val0'], aggfunc='mean').fillna(0)

Question 4

Can I get something other than mean, like maybe sum?

pd.DataFrame.pivot_table df.pivot_table( values='val0', index='row', columns='col', fill_value=0, aggfunc='sum') col col0 col1 col2 col3 col4 row row0 0.77 1.21 0.00 0.86 0.65 row2 0.13 0.00 0.79 0.50 0.50 row3 0.00 0.31 0.00 1.09 0.00 row4 0.00 0.10 0.79 1.52 0.24

pd.DataFrame.groupby df.groupby(['row', 'col'])['val0'].sum().unstack(fill_value=0)

pd.crosstab pd.crosstab( index=df['row'], columns=df['col'], values=df['val0'], aggfunc='sum').fillna(0)

Question 5

Can I do more that one aggregation at a time?

Notice that for pivot_table and crosstab I needed to pass list of callables. On the other hand, groupby.agg is able to take strings for a limited number of special functions. groupby.agg would also have taken the same callables we passed to the others, but it is often more efficient to leverage the string function names as there are efficiencies to be gained.

pd.DataFrame.pivot_table df.pivot_table( values='val0', index='row', columns='col', fill_value=0, aggfunc=[np.size, np.mean]) size mean col col0 col1 col2 col3 col4 col0 col1 col2 col3 col4 row row0 1 2 0 1 1 0.77 0.605 0.000 0.860 0.65 row2 1 0 2 1 2 0.13 0.000 0.395 0.500 0.25 row3 0 1 0 2 0 0.00 0.310 0.000 0.545 0.00 row4 0 1 2 2 1 0.00 0.100 0.395 0.760 0.24

pd.DataFrame.groupby df.groupby(['row', 'col'])['val0'].agg(['size', 'mean']).unstack(fill_value=0)

pd.crosstab pd.crosstab( index=df['row'], columns=df['col'], values=df['val0'], aggfunc=[np.size, np.mean]).fillna(0, downcast='infer')

Question 6

Can I aggregate over multiple value columns?

pd.DataFrame.pivot_table we pass values=['val0', 'val1'] but we could've left that off completely df.pivot_table( values=['val0', 'val1'], index='row', columns='col', fill_value=0, aggfunc='mean') val0 val1 col col0 col1 col2 col3 col4 col0 col1 col2 col3 col4 row row0 0.77 0.605 0.000 0.860 0.65 0.01 0.745 0.00 0.010 0.02 row2 0.13 0.000 0.395 0.500 0.25 0.45 0.000 0.34 0.440 0.79 row3 0.00 0.310 0.000 0.545 0.00 0.00 0.230 0.00 0.075 0.00 row4 0.00 0.100 0.395 0.760 0.24 0.00 0.070 0.42 0.300 0.46

pd.DataFrame.groupby df.groupby(['row', 'col'])['val0', 'val1'].mean().unstack(fill_value=0)

Question 7

Can Subdivide by multiple columns?

pd.DataFrame.pivot_table df.pivot_table( values='val0', index='row', columns=['item', 'col'], fill_value=0, aggfunc='mean') item item0 item1 item2 col col2 col3 col4 col0 col1 col2 col3 col4 col0 col1 col3 col4 row row0 0.00 0.00 0.00 0.77 0.00 0.00 0.00 0.00 0.00 0.605 0.86 0.65 row2 0.35 0.00 0.37 0.00 0.00 0.44 0.00 0.00 0.13 0.000 0.50 0.13 row3 0.00 0.00 0.00 0.00 0.31 0.00 0.81 0.00 0.00 0.000 0.28 0.00 row4 0.15 0.64 0.00 0.00 0.10 0.64 0.88 0.24 0.00 0.000 0.00 0.00

pd.DataFrame.groupby df.groupby( ['row', 'item', 'col'] )['val0'].mean().unstack(['item', 'col']).fillna(0).sort_index(1)

Question 8

Can Subdivide by multiple columns?

pd.DataFrame.pivot_table df.pivot_table( values='val0', index=['key', 'row'], columns=['item', 'col'], fill_value=0, aggfunc='mean') item item0 item1 item2 col col2 col3 col4 col0 col1 col2 col3 col4 col0 col1 col3 col4 key row key0 row0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.86 0.00 row2 0.00 0.00 0.37 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.50 0.00 row3 0.00 0.00 0.00 0.00 0.31 0.00 0.81 0.00 0.00 0.00 0.00 0.00 row4 0.15 0.64 0.00 0.00 0.00 0.00 0.00 0.24 0.00 0.00 0.00 0.00 key1 row0 0.00 0.00 0.00 0.77 0.00 0.00 0.00 0.00 0.00 0.81 0.00 0.65 row2 0.35 0.00 0.00 0.00 0.00 0.44 0.00 0.00 0.00 0.00 0.00 0.13 row3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.28 0.00 row4 0.00 0.00 0.00 0.00 0.10 0.00 0.00 0.00 0.00 0.00 0.00 0.00 key2 row0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.40 0.00 0.00 row2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.13 0.00 0.00 0.00 row4 0.00 0.00 0.00 0.00 0.00 0.64 0.88 0.00 0.00 0.00 0.00 0.00

pd.DataFrame.groupby df.groupby( ['key', 'row', 'item', 'col'] )['val0'].mean().unstack(['item', 'col']).fillna(0).sort_index(1)

pd.DataFrame.set_index because the set of keys are unique for both rows and columns df.set_index( ['key', 'row', 'item', 'col'] )['val0'].unstack(['item', 'col']).fillna(0).sort_index(1)

Question 9

Can I aggregate the frequency in which the column and rows occur together, aka "cross tabulation"?

pd.DataFrame.pivot_table df.pivot_table(index='row', columns='col', fill_value=0, aggfunc='size') col col0 col1 col2 col3 col4 row row0 1 2 0 1 1 row2 1 0 2 1 2 row3 0 1 0 2 0 row4 0 1 2 2 1

pd.DataFrame.groupby df.groupby(['row', 'col'])['val0'].size().unstack(fill_value=0)

pd.crosstab pd.crosstab(df['row'], df['col'])

pd.factorize + np.bincount # get integer factorization `i` and unique values `r` # for column `'row'` i, r = pd.factorize(df['row'].values) # get integer factorization `j` and unique values `c` # for column `'col'` j, c = pd.factorize(df['col'].values) # `n` will be the number of rows # `m` will be the number of columns n, m = r.size, c.size # `i * m + j` is a clever way of counting the # factorization bins assuming a flat array of length # `n * m`. Which is why we subsequently reshape as `(n, m)` b = np.bincount(i * m + j, minlength=n * m).reshape(n, m) # BTW, whenever I read this, I think 'Bean, Rice, and Cheese' pd.DataFrame(b, r, c) col3 col2 col0 col1 col4 row3 2 0 0 1 0 row2 1 2 1 0 2 row0 1 0 1 2 1 row4 2 2 0 1 1

pd.get_dummies pd.get_dummies(df['row']).T.dot(pd.get_dummies(df['col'])) col0 col1 col2 col3 col4 row0 1 2 0 1 1 row2 1 0 2 1 2 row3 0 1 0 2 0 row4 0 1 2 2 1

Question 10

How do I convert a DataFrame from long to wide by pivoting on ONLY two columns?

DataFrame.pivot The first step is to assign a number to each row - this number will be the row index of that value in the pivoted result. This is done using GroupBy.cumcount: df2.insert(0, 'count', df2.groupby('A').cumcount()) df2 count A B 0 0 a 0 1 1 a 11 2 2 a 2 3 3 a 11 4 0 b 10 5 1 b 10 6 2 b 14 7 0 c 7 The second step is to use the newly created column as the index to call DataFrame.pivot. df2.pivot(*df2) # df2.pivot(index='count', columns='A', values='B') A a b c count 0 0.0 10.0 7.0 1 11.0 10.0 NaN 2 2.0 14.0 NaN 3 11.0 NaN NaN

DataFrame.pivot_table Whereas DataFrame.pivot only accepts columns, DataFrame.pivot_table also accepts arrays, so the GroupBy.cumcount can be passed directly as the index without creating an explicit column. df2.pivot_table(index=df2.groupby('A').cumcount(), columns='A', values='B') A a b c 0 0.0 10.0 7.0 1 11.0 10.0 NaN 2 2.0 14.0 NaN 3 11.0 NaN NaN

Question 11

How do I flatten the multiple index to single index after pivot

If columns type object with string join

df.columns = df.columns.map('|'.join)

else format

df.columns = df.columns.map('{0[0]}|{0[1]}'.format)

---

To extend @piRSquared's answer another version of Question 10

Question 10.1

DataFrame:

d = data = {'A': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 3, 6: 5},
 'B': {0: 'a', 1: 'b', 2: 'c', 3: 'a', 4: 'b', 5: 'a', 6: 'c'}}
df = pd.DataFrame(d)

   A  B
0  1  a
1  1  b
2  1  c
3  2  a
4  2  b
5  3  a
6  5  c

Output:

   0     1     2
A
1  a     b     c
2  a     b  None
3  a  None  None
5  c  None  None

Using df.groupby and pd.Series.tolist

t = df.groupby('A')['B'].apply(list)
out = pd.DataFrame(t.tolist(),index=t.index)
out
   0     1     2
A
1  a     b     c
2  a     b  None
3  a  None  None
5  c  None  None

Or A much better alternative using pd.pivot_table with df.squeeze.

t = df.pivot_table(index='A',values='B',aggfunc=list).squeeze()
out = pd.DataFrame(t.tolist(),index=t.index)

---

To better understand how pivot works you can look at the example from Pandas documentation:

https://i.stack.imgur.com/GPsB5.png

df = pd.DataFrame({
    'foo': ['one', 'one', 'one', 'two', 'two', 'two'],
    'bar': ['A', 'B', 'C', 'A', 'B', 'C'],
    'baz': [1, 2, 3, 4, 5, 6],
    'zoo': ['x', 'y', 'z', 'q', 'w', 't']
})

Input Table:

   foo bar  baz zoo
0  one   A    1   x
1  one   B    2   y
2  one   C    3   z
3  two   A    4   q
4  two   B    5   w
5  two   C    6   t

Pivot:

pd.pivot(
    data=df,        
    index='foo',    # Column to use to make new frame’s index. If None, uses existing index.
    columns='bar',  # Column to use to make new frame’s columns.
    values='baz'    # Column(s) to use for populating new frame’s values.
)

Output table:

bar  A  B  C
foo         
one  1  2  3
two  4  5  6

---

You can use list of column names as index, columns and values arguments.

rows, cols, vals, aggfuncs = ['row', 'key'], ['col', 'item'], ['val0', 'val1'], ['mean', 'sum']

df.groupby(rows+cols)[vals].agg(aggfuncs).unstack(cols)
# equivalently,
df.pivot_table(vals, rows, cols, aggfuncs)


df.set_index(rows+cols)[vals].unstack(cols)
# equivalently, 
df.pivot(rows, cols, vals)

You can also apply the insight from Question 10 to multi-column pivot operation as well. Simply append the auxiliary index from groupby().cumcount() to either rows or cols depending on how you want your result to be (appending it to rows makes the result "long", and appending it to cols makes it "wide"). Additionally, calling droplevel().reset_index() fixes the surplus and duplicate index issue.

# for "long" result
df.assign(ix=df.groupby(rows+cols).cumcount()).pivot(rows+['ix'], cols, vals).droplevel(-1).reset_index()

# for "wide" result
df.assign(ix=df.groupby(rows+cols).cumcount()).pivot(rows, cols+['ix'], vals).droplevel(-1, axis=1).reset_index()

For example, the following doesn't work.

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

But the following work:

# long
(
    df.assign(ix=df.groupby(['A','B']).cumcount())
    .pivot(['A','ix'], 'B', 'C')
    .droplevel(-1).reset_index()
)

B  A    a    b
0  1  0.0  NaN
1  1  1.0  NaN
2  2  NaN  2.0



# wide
(
    df.assign(ix=df.groupby(['A','B']).cumcount())
    .pivot('A', ['B', 'ix'], 'C')
    .droplevel(-1, axis=1).reset_index()
)

B  A    a    a    b
0  1  0.0  1.0  NaN
1  2  NaN  NaN  2.0

pivot_table() with aggfunc results in aggregated data, which is very similar to a groupby.agg(). pivot() is simply reshaping and/or stacking data (reminiscent of numpy reshape and stack methods), so naturally, it's related to their pandas cousins, unstack() and stack().

In fact, if we check the source code, internally, each method pair are the same.

pivot_table = groupby + unstack pivot = set_index + unstack crosstab = pivot_table

Using the setup in the OP:

from numpy.core.defchararray import add
np.random.seed([3,1415])
n = 20

cols = np.array(['key', 'row', 'item', 'col'])
arr1 = (np.random.randint(5, size=(n, 4)) // [2, 1, 2, 1]).astype(str)

df = pd.DataFrame(add(cols, arr1), columns=cols).join(pd.DataFrame(np.random.rand(n, 2).round(2)).add_prefix('val'))

rows, cols, vals, aggfuncs = ['row', 'key'], ['col', 'val1'], ['val0'], ['mean', 'sum']

pivot_table() aggregates the values and unstacks it. Specifically, it creates a single flat list out of index and columns, calls groupby() with this list as the grouper and aggregates using the passed aggregator methods (the default is mean). Then after aggregation, it calls unstack() by the list of columns. So internally, pivot_table = groupby + unstack. Moreover, if fill_value is passed, fillna() is called. In other words, the method that produces pv_1 is the same as the method that produces gb_1 in the example below.

pv_1 = df.pivot_table(index=rows, columns=cols, values=vals, aggfunc=aggfuncs, fill_value=0)
# internal operation of `pivot_table()`
gb_1 = df.groupby(rows+cols)[vals].agg(aggfuncs).unstack(cols).fillna(0, downcast="infer")
pv_1.equals(gb_1) # True

pivot() creates a MultiIndex from the column values passed as index and columns, builds a MultiIndex DataFrame and calls unstack() by the list of columns. So internally, pivot = set_index + unstack. In other words, all of the following are True:

# if the entire df needs to be pivoted
pv_2 = df.pivot(index=rows, columns=cols)
# internal operation of `pivot()`
su_2 = df.set_index(rows+cols).unstack(cols)
pv_2.equals(su_2) # True

# if only subset of df.columns need to be considered for pivot, specify so
pv_3 = df.pivot(index=rows, columns=cols, values=vals)
su_3 = df.set_index(rows+cols)[vals].unstack(cols)
pv_3.equals(su_3) # True

# this is the precise method used internally (building a new DF seems to be faster than set_index of an existing one)
pv_4 = df.pivot(index=rows, columns=cols, values=vals)
su_4 = pd.DataFrame(df[vals].values, index=pd.MultiIndex.from_arrays([df[c] for c in rows+cols]), columns=vals).unstack(cols)
pv_4.equals(su_4) # True

crosstab() calls pivot_table(), i.e., crosstab = pivot_table. Specifically, it builds a DataFrame out of the passed arrays of values, filters it by the common indices and calls pivot_table(). It's more limited than pivot_table() because it only allows a one-dimensional array-like as values, unlike pivot_table() that can have multiple columns as values. In other words, the following is True.

indexes, columns, values = [df[r] for r in rows], [df[c] for c in cols], next(df[v] for v in vals)
# crosstab
ct_5 = pd.crosstab(indexes, columns, values, aggfunc=aggfuncs)
# internal operation (abbreviated)
from functools import reduce
data = pd.DataFrame({f'row_{i}': r for i, r in enumerate(indexes)} | {f'col_{i}': c for i, c in enumerate(columns)} | {'v': values}, 
                    index = reduce(lambda x, y: x.intersection(y.index), indexes[1:]+columns, indexes[0].index)
                   )
pv_5 = data.pivot_table('v', [k for k in data if k[:4]=='row_'], [k for k in data if k[:4]=='col_'], aggfuncs)
ct_5.equals(pv_5) # True

---