How can I pivot a dataframe?

Michael Zippo

👻 Check our latest review to choose the best laptop for Machine Learning engineers and Deep learning tasks!

What is pivot?
How do I pivot?
Is this a pivot?
Long format to wide format?

I"ve seen a lot of questions that ask about pivot tables. Even if they don"t know that they are asking about pivot tables, they usually are. It is virtually impossible to write a canonical question and answer that encompasses all aspects of pivoting...

... But I"m going to give it a go.

The problem with existing questions and answers is that often the question is focused on a nuance that the OP has trouble generalizing in order to use a number of the existing good answers. However, none of the answers attempt to give a comprehensive explanation (because it"s a daunting task)

Look a few examples from my Google Search

How to pivot a dataframe in Pandas?

Good question and answer. But the answer only answers the specific question with little explanation.

pandas pivot table to data frame

In this question, the OP is concerned with the output of the pivot. Namely how the columns look. OP wanted it to look like R. This isn"t very helpful for pandas users.

pandas pivoting a dataframe, duplicate rows

Another decent question but the answer focuses on one method, namely pd.DataFrame.pivot

So whenever someone searches for pivot they get sporadic results that are likely not going to answer their specific question.

Setup

You may notice that I conspicuously named my columns and relevant column values to correspond with how I"m going to pivot in the answers below.

import numpy as np
import pandas as pd
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")
)
print(df)

     key   row   item   col  val0  val1
0   key0  row3  item1  col3  0.81  0.04
1   key1  row2  item1  col2  0.44  0.07
2   key1  row0  item1  col0  0.77  0.01
3   key0  row4  item0  col2  0.15  0.59
4   key1  row0  item2  col1  0.81  0.64
5   key1  row2  item2  col4  0.13  0.88
6   key2  row4  item1  col3  0.88  0.39
7   key1  row4  item1  col1  0.10  0.07
8   key1  row0  item2  col4  0.65  0.02
9   key1  row2  item0  col2  0.35  0.61
10  key2  row0  item2  col1  0.40  0.85
11  key2  row4  item1  col2  0.64  0.25
12  key0  row2  item2  col3  0.50  0.44
13  key0  row4  item1  col4  0.24  0.46
14  key1  row3  item2  col3  0.28  0.11
15  key0  row3  item1  col1  0.31  0.23
16  key0  row0  item2  col3  0.86  0.01
17  key0  row4  item0  col3  0.64  0.21
18  key2  row2  item2  col0  0.13  0.45
19  key0  row2  item0  col4  0.37  0.70

Question(s)

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

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

 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

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?

 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

Can I get something other than mean , like maybe 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

Can I do more that one aggregation at a time?

        sum                           mean 
 col   col0  col1  col2  col3  col4  col0   col1   col2   col3  col4
 row
 row0  0.77  1.21  0.00  0.86  0.65  0.77  0.605  0.000  0.860  0.65
 row2  0.13  0.00  0.79  0.50  0.50  0.13  0.000  0.395  0.500  0.25
 row3  0.00  0.31  0.00  1.09  0.00  0.00  0.310  0.000  0.545  0.00
 row4  0.00  0.10  0.79  1.52  0.24  0.00  0.100  0.395  0.760  0.24

Can I aggregate over multiple value columns?

       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

Can Subdivide by multiple columns?

 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

 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

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

 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

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

np.random.seed([3, 1415])
df2 = pd.DataFrame({"A": list("aaaabbbc"), "B": np.random.choice(15, 8)})
df2
   A   B
0  a   0
1  a  11
2  a   2
3  a  11
4  b  10
5  b  10
6  b  14
7  c   7

The expected should look something like

      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

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

From

   1|1  2|1  2|2
a    2    1    1
b    2    1    0
c    1    0    0

👻 Read also: what is the best laptop for engineering students?

We hope this article has helped you to resolve the problem. Apart from How can I pivot a dataframe?, check other array Python module-related topics.

Want to excel in Python? See our review of the best Python online courses 2023. If you are interested in Data Science, check also how to learn programming in R.

By the way, this material is also available in other languages:

Javier OConnell

Shanghai | 2023-04-01

I was preparing for my coding interview, thanks for clarifying this - How can I pivot a dataframe? in Python is not the simplest one. Checked yesterday, it works!

Anna Nickolson

Massachussetts | 2023-04-01

time Python module is always a bit confusing 😭 How can I pivot a dataframe? is not the only problem I encountered. Checked yesterday, it works!

Walter Emmerson

Moscow | 2023-04-01

I was preparing for my coding interview, thanks for clarifying this - How can I pivot a dataframe? in Python is not the simplest one. I just hope that will not emerge anymore