Pandas Pivot: A Guide with Examples

Table of Contents

• Why to pivot your data
• How to use the Pandas pivot method
• When to use pivot vs pivot_table in Pandas
• How to use the Pandas pivot_table method
• Conclusion

Python’s Pandas library is one of the most popular tools in the data scientist’s toolbelt. Data scientists use Pandas to explore, clean, and understand datasets.

In a previous article, we saw how to work with the Pandas DataFrame, the core data structure in the Pandas library. If you’re new to Pandas, that post is a great way to get started.

In this post, we’ll learn how to reshape your DataFrames by using the Pivot method. Pivot allows you to twist your data into a different shape for easier analysis.

We’ll cover:

• Why to pivot your data;
• How to use the pivot method;
• When to use pivot method vs pivot_table method;
• How to use the pivot_table method.

Let’s get started.

Why to pivot your data

Before we get into details how to pivot, it’s important to know why you want to pivot.

Pivoting your data allows you to reshape it in a way that makes it easier to understand or analyze. Often you’ll use a pivot to demonstrate the relationship between two columns that can be difficult to reason about before the pivot.

If you’re an Excel wizard who has spent a lot of time in spreadsheets, the idea of a pivot may be easy for you. If not, it can be hard to understand without an example. Let’s see how it works.

In the example below, I’ll create a Pandas DataFrame from some stock trading data that I’ve used in the previous Pandas articles.

When we print out the code, you can see that the data has a number of columns and that the rows are organized by trading date and stock symbol.

That organization may be helpful for some analysis, but it can be hard to glean information about trading volume across dates and stock symbols. Let’s reshape our data to look closer at volume.

In the example above, I use the pivot method to reshape the data so that the rows are indexed by stock symbol and the columns are trading dates. The value in each cell is the volume on that day. This makes it easy to compare the volume for a stock over time, by reading horizontally, or to compare volume across stocks on a particular day, by reading vertically.

This reshaping power of pivot makes it much easier to understand relationships in your datasets.

How to use the Pandas pivot method

To use the pivot method in Pandas, you need to specify three parameters:

• Index: Which column should be used to identify and order your rows vertically
• Columns: Which column should be used to create the new columns in our reshaped DataFrame. Each unique value in the column stated here will create a column in our new DataFrame.
• Values: Which column(s) should be used to fill the values in the cells of our DataFrame.

Let’s walk through these in an example with our stock trading data. In the example below, I use pivot to examine the closing trading price for each stock symbol over our trading window.

First, I printed out our DataFrame to see how it is organized by default. Then, I use the pivot method to change the data.

Notice that for the index parameter, I used symbol. As a result, each unique value for the symbol column — AAPL, AMZN, GOOG — is used as the index, the leftmost column in our DataFrame.

I used date for the column parameter. This resulted in five non-index columns across the top of our DataFrame, one for each unique value in the date column of our initial DataFrame.

Finally, I used close as the values parameter. This specifies which value should be placed in each column. I’m interested in the closing price for each stock across the trading days, so I use the close column.

Note that the index and column parameters are interchangeable. If you want to reorganize so that the dates are used as the index and the stock symbols are my columns, you can just flip the parameters:

When to use pivot vs pivot_table in Pandas

So far we’ve only been using the term ‘pivot’ broadly, but there are actually two Pandas methods for pivoting. The first is the pivot method, which we reviewed in this section. The second is the pivot_table method, which we’ll learn about in the next section.

When deciding between using the pivot or pivot_table method, you need to ask yourself one question:

Will the results of my pivot have more than one entry in any index + column?

If the answer to this question is “yes”, you must use the pivot_table method. If the answer to this question is “no”, you may use the pivot method.

Note that any use of pivot can be switched to pivot_table, but the reverse is not true. If you try to use the pivot method where there would be more than one entry in any index + column combination, it will throw a ValueError.

In the next section, we’ll take a look at how the pivot_table method works in practice.

How to use the Pandas pivot_table method

For those familiar with Excel or other spreadsheet tools, the pivot table is more familiar as an aggregation tool. Pandas pivot tables are used to group similar columns to find totals, averages, or other aggregations.

You can accomplish this same functionality in Pandas with the pivot_table method. For example, imagine we wanted to find the mean trading volume for each stock symbol in our DataFrame. You could do so with the following use of pivot_table:

Like with pivot, we specify the index we want to to use for our data as well as the column to use to fill in the values.

Notice that each stock symbol in our index will have five values for the volume column as there are five trading days for each stock. The pivot_table method aggregates these values and applies an aggregate function to reduce it to a single value.

By default, the aggregate function is the mean function from NumPy, but you can pass in a custom aggregation function. If I want to combine my values into a total, I could use NumPy’s sum function:

The pivot table method is really powerful when using it with a MultiIndex, which allows you to have hierarchies in your index. For example, imagine you had a larger stock trading dataset that included training data over an entire year. You could use a MultiIndex to create a pivot table where values were grouped by stock symbol and month, allowing you to quickly explore how trading volume and other statistics changed on a month-over-month basis for particular stocks.

Conclusion

In this post, we learned about pivoting your DataFrames in Pandas with the pivot and pivot_table functions. We saw why you would want to pivot your data as well as walkthroughs of using both pivot and pivot_table.

Just like Pandas makes it easy to work with data, the Kite plugin for your IDE makes it easy to work with Python. Kite gives you an AI-powered autocomplete in the editor, which saves you keystrokes and helps you code faster on the fly. It also has inline documentation for popular libraries so you don’t have to break your flow. Download it today!