> ## Content Index
> Fetch the complete content index at: https://datascientyst.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How To Create a Pivot Table in Pandas?
- URL: https://datascientyst.com/how-to-create-a-pivot-table-in-pandas/
- Published: 2023-02-15T11:42:14.000Z
- Updated: 2023-09-07T07:01:35.000Z
- Author: John D K
- Tags: Pivot

We can use the following syntax to **create a pivot table in Python using Pandas**:

```python
df_pivot = df.pivot_table(values='D', index=['A', 'B'], columns='C')

```

Next, we'll see the full steps to create pivot tables in Pandas using a simple example.

![](https://datascientyst.com/content/images/2023/02/how-to-create-a-pivot-table-in-pandas.webp)

Steps to create pivot table:

## Step 1: Get data for pivot

Suppose we have the following DataFrame which contains 4 columns:

- 3 string columns
- 2 numeric

We will pivot on multiple columns in next sections

```python
import pandas as pd

data = {"A": ["foo", "foo", "foo", "foo", "foo",
          	"bar", "bar", "bar", "bar"],
    	"B": ["one", "one", "one", "two", "two",
          	"one", "one", "two", "two"],
    	"C": ["small", "large", "large", "small",
          	"small", "large", "small", "small",
          	"large"],
    	"D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
    	"E": [2, 4, 5, 5, 6, 6, 8, 9, 9]}

df = pd.DataFrame(data)

```

Let's see how to pivot based on data below:

|   | A   | B   | C     | D | E |
| - | --- | --- | ----- | - | - |
| 0 | foo | one | small | 1 | 2 |
| 1 | foo | one | large | 2 | 4 |
| 2 | foo | one | large | 2 | 5 |
| 3 | foo | two | small | 3 | 5 |
| 4 | foo | two | small | 3 | 6 |
| 5 | bar | one | large | 4 | 6 |
| 6 | bar | one | small | 5 | 8 |
| 7 | bar | two | small | 6 | 9 |
| 8 | bar | two | large | 7 | 9 |

## Step 2: Select columns indexes and values

Next, determine the:

- `index` \- keys to group by on the pivot table index
- `columns` \- keys to group by on the pivot table column
- `values` \- columns used for aggregation data of the pivot table
- `aggfunc` \- functions or list of functions used for aggregation

We will demonstrate a pivot table using all of the above.

## Step 3: Create the pivot table

Finally, create the pivot table using method: `pivot_table()` based on the following syntax:

```python
pd.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean')

```

In our example we will create pivot table:

- using column 'D' as aggregating values
- for index we will have 2 columns - 'A' and 'B'  
  - this creates multi-index
- for columns we will use column 'C'
- as aggregating function we will use Python method `sum`

```python
df_pivot = pd.pivot_table(df, values='D', index=['A', 'B'],
                      	columns=['C'], aggfunc=sum)
df_pivot

```

The pivot table looks like:

|     | C   | large | small |
| --- | --- | ----- | ----- |
| A   | B   |       |       |
| bar | one | 4.0   | 5.0   |
| two | 7.0 | 6.0   |       |
| foo | one | 4.0   | 1.0   |
| two | NaN | 6.0   |       |

## Step 4: Advanced Pivot options

In the previous example we saw the basic usage of the pivot\_table() method with most used options.

Alternatively, we may use more options with the following default values:

```python
pd.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', fill_value=None,
               margins=False, dropna=True, margins_name='All', observed=False, sort=True)

```

Useful pivot options are:

- `fill_value` \- value to replace missing values with
- `dropna` \- exclude columns whose entries are all NaN
- `margins` \- add all row/columns (subtotal / grand totals)
- `sort` \- sort the results

## Pivot table examples

### Pivot Table with Multiple aggfunc

We can use multiple aggregation functions. The functions might be different for different columns:

- 'D' - `mean`
- 'E' - `min` and `max`

```python
df_pivot = pd.pivot_table(df, values=['D', 'E'], index=['A', 'C'],
                      	aggfunc={'D': 'mean',
                               	'E': [min, max]})
df_pivot

```

Result:

|       |          | D        | E   |     |
| ----- | -------- | -------- | --- | --- |
|       |          | mean     | max | min |
| A     | C        |          |     |     |
| bar   | large    | 5.500000 | 9   | 6   |
| small | 5.500000 | 9        | 8   |     |
| foo   | large    | 2.000000 | 5   | 4   |
| small | 2.333333 | 6        | 2   |     |

### Pivot table replace NaN

To replace NaN values in the pivot table we can use the parameter `fill_value`. We can replace NaN values with 0 by:

```python
df_pivot = pd.pivot_table(df, values='D', index=['A', 'B'],
                	columns=['C'], aggfunc=sum, fill_value=0)
df_pivot

```

Result of replace NaN values by 0 in pivot table:

|     | C   | large | small |
| --- | --- | ----- | ----- |
| A   | B   |       |       |
| bar | one | 4     | 5     |
| two | 7   | 6     |       |
| foo | one | 4     | 1     |
| two | 0   | 6     |       |

### Pivot table remove NaN

To drop columns with NaN values we can use option `dropna=True`:

```python
pd.pivot_table(df, values=['D'],
           	index=['A'], columns=['C', 'E'],
           	aggfunc=sum, dropna=True)

```

The result is pivot table without NaN columns:

![pandas-pivot-drop-na](https://datascientyst.com/content/images/2023/02/pandas-pivot-drop-na.png)

## Summary

To summarize, in this article, we've seen an example of a creation pivot table in Pandas. We've briefly discussed syntax and options.

## Resources

- [pivot\_table](https://pandas.pydata.org/docs/reference/api/pandas.pivot%5Ftable.html?ref=datascientyst.com)
- [List of Aggregation Functions(aggfunc) for GroupBy in Pandas](https://datascientyst.com/list-aggregation-functions-aggfunc-groupby-pandas/)