search
Search
Join our weekly DS/ML newsletter layers DS/ML Guides
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
brightness_medium
share
arrow_backShare
Twitter
Facebook

Pandas | pivot method

Programming
chevron_right
Python
chevron_right
Pandas
chevron_right
Documentation
chevron_right
General Functions
schedule Jul 1, 2022
Last updated
local_offer PythonPandas
Tags

Pandas pivot(~) method converts the input DataFrame into what is called a pivot table. Please check the examples below to understand what a pivot table is.

Parameters

1. indexlink | string or object | optional

The label of the column that will become the index of the resulting pivot table.

2. columnslink | string or object

The label of the columns whose values will become the new column labels.

3. valueslink | string or object or list | optional

The label of the columns whose values will fill the resulting pivot table.

WARNING

If you wish to aggregate values (e.g. compute the mean, count and so on), use pivot_table(~) instead.

Return value

A new DataFrame that represents a pivot table.

Examples

Basic usage

Consider the following DataFrame about the height of people over time:

df = pd.DataFrame({"name":["alex","bob","alex","bob","cathy"], "year":[2012,2012,2013,2013,2013], "height":[150,160,160,170,160]})
df
name year height
0 alex 2012 150
1 bob 2012 160
2 alex 2013 160
3 bob 2013 170
4 cathy 2013 160

To create a pivot table using this DataFrame:

pd.pivot(df, index="name", columns="year", values="height")
year 2012 2013
name
alex 150.0 160.0
bob 160.0 170.0
cathy NaN 160.0

Here, note the following:

  • the name column became the new index

  • the values of the year column became the new column labels

  • the values of the height column was used to populate the new DataFrame

  • values that are not found (e.g. height of Cathy in 2012) resulted in NaN.

Multiple values

Consider the following DataFrame:

df = pd.DataFrame({"name":["alex","bob","alex","bob","cathy"], "year":[2012,2012,2013,2013,2013], "height":[150,160,160,170,160], "weight":[50,60,60,70,60]})
df
name year height weight
0 alex 2012 150 50
1 bob 2012 160 60
2 alex 2013 160 60
3 bob 2013 170 70
4 cathy 2013 160 60

Here, in addition to the height column, we have a weight column.

To generate a pivot table using both of these columns pass a list for values like so:

pd.pivot(df, index="name", columns="year", values=["height","weight"])
height weight
year 2012 2013 2012 2013
name
alex 150.0 160.0 50.0 60.0
bob 160.0 170.0 60.0 70.0
cathy NaN 160.0 NaN 60.0

Dealing with ValueError

Consider the following DataFrame:

df = pd.DataFrame({"name":["alex","alex","bob"], "year":[2012,2012,2013], "height":[150,160,160]})
df
name year height
0 alex 2012 150
1 alex 2012 160
2 bob 2013 160

Here, we have two people called alex whose heights are both taken in 2012.

Creating a pivot table results in an ValueError like so:

pd.pivot(df, index="name", columns="year", values="height")
ValueError: Index contains duplicate entries, cannot reshape

To understand why this happens, let us temporarily rename the second alex to *, and create the pivot table:

df = pd.DataFrame({"name":["alex","*","bob"], "year":[2012,2012,2013], "height":[150,160,160]})
pd.pivot(df, index="name", columns="year", values="height")
year 2012 2013
name
* 160.0 NaN
alex 150.0 NaN
bob NaN 160.0

If * was alex, then it's easy to see that the resulting index would contain duplicate values, which is undesirable in Pandas. This is the cause of the ValueError.

The fix for this ValueError, then, is to ensure that duplicate values in the new index column do not exist. You can use properties like loc and iloc to update the individual entries to avoid duplication.

mail
Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)
robocat
Published by Isshin Inada
Edited by 0 others
Did you find this page useful?
Ask a question or leave a feedback...
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!