Pandas Pivot Table

The pivot_table() function in Pandas allows us to create a spreadsheet-style pivot table making it easier to group and analyze our data.

Let's look at an example.

 import pandas as pd # create a dataframe data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'], 'City': ['New York', 'Los Angeles', 'New York', 'Los Angeles'], 'Temperature': [32, 75, 30, 77]} df = pd.DataFrame(data) print("Original DataFrame\n", df) print() 
# pivot the dataframe pivot_df = df.pivot_table(index='Date', columns='City', values='Temperature')
 print("Reshaped DataFrame\n", pivot_df)

Output

 Original DataFrame Date City Temperature 0 2023-01-01 New York 32 1 2023-01-01 Los Angeles 75 2 2023-01-02 New York 30 3 2023-01-02 Los Angeles 77 Reshaped DataFrame City Los Angeles New York Date 2023-01-01 75 32 2023-01-02 77 30

In this example, we reshaped the DataFrame with Date as index, City as columns and Temperature as values.

The pivot_df DataFrame is a multidimensional table that shows the temperature based on the city and the date.

Thus the pivot_table() operation reshapes the data to make it clearer for further analysis.

pivot_table() Syntax

The syntax of pivot_table() in Pandas is:

 df.pivot_table(values=None, index=None, columns=None, aggfunc='mean', fill_value=None, dropna=True)

Here,

• index: the column to use as row labels
• columns: the column that will be reshaped as columns
• values: the column(s) to use for the new DataFrame's values
• aggfunc: the function to use for aggregation, defaulting to 'mean'
• fill_value: value to replace missing values with
• dropna: whether to exclude the columns whose entries are all NaN

Example: pivot_table() with Multiple Values

If we omit the values argument in pivot_table(), it selects all the remaining columns (besides the ones specified index and columns) as values for the pivot table.

 import pandas as pd # create a dataframe data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'], 'City': ['New York', 'Los Angeles', 'New York', 'Los Angeles'], 'Temperature': [32, 75, 30, 77], 'Humidity': [80, 10, 85, 5]} df = pd.DataFrame(data) print('Original DataFrame') print(df) print() 
# pivot the dataframe pivot_df = df.pivot_table(index='Date', columns='City')
 print('Reshaped DataFrame') print(pivot_df)

Output

 Original DataFrame Date City Temperature Humidity 0 2023-01-01 New York 32 80 1 2023-01-01 Los Angeles 75 10 2 2023-01-02 New York 30 85 3 2023-01-02 Los Angeles 77 5 Reshaped DataFrame Humidity Temperature City Los Angeles New York Los Angeles New York Date 2023-01-01 10 80 75 32 2023-01-02 5 85 77 30

In this example, we created a pivot table for multiple values i.e. Temperature and Humidity.

pivot_table() With Aggregate Functions

We can use the pivot_table() method with different aggregate functions using the aggfunc parameter. We can set the value of aggfunc to functions such as 'sum', 'mean', 'count', 'max' or 'min'.

Let's see an example.

 import pandas as pd data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02', '2023-01-03', '2023-01-03'], 'City': ['New York', 'Los Angeles', 'New York', 'Los Angeles', 'New York', 'Los Angeles'], 'Temperature': [32, 75, 30, 77, 33, 78], 'Humidity': [80, 10, 85, 5, 81, 7]} df = pd.DataFrame(data) 
# calculate mean temperature for each city using pivot_table() mean_temperature = df.pivot_table(index='City', values='Temperature', aggfunc='mean')
 print(mean_temperature)

Output

 Temperature City Los Angeles 76.666667 New York 31.666667

In the above example, we calculated the mean temperature of each city using the aggfunc='mean' argument in pivot_table().

Pivot Table With MultiIndex

We can create a pivot table with MultiIndex using the pivot_table() function.

Let's look at an example.

 import pandas as pd # create a dataframe data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02', '2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02'], 'City': ['New York', 'Los Angeles', 'New York', 'Los Angeles','Delhi', 'Chennai', 'Delhi', 'Chennai'], 'Country': ['USA', 'USA', 'USA', 'USA', 'India', 'India', 'India', 'India'], 'Temperature': [32, 75, 30, 77, 75, 80, 78, 79]} df = pd.DataFrame(data) print("Original DataFrame\n", df) print() 
# create a pivot table with multiindex pivot_df = df.pivot_table(index=['Country', 'City'], columns='Date', values='Temperature')
 print("Reshaped DataFrame\n", pivot_df)

Output

 Original DataFrame Date City Country Temperature 0 2023-01-01 New York USA 32 1 2023-01-01 Los Angeles USA 75 2 2023-01-02 New York USA 30 3 2023-01-02 Los Angeles USA 77 4 2023-01-01 Delhi India 75 5 2023-01-01 Chennai India 80 6 2023-01-02 Delhi India 78 7 2023-01-02 Chennai India 79 Reshaped DataFrame Date 2023-01-01 2023-01-02 Country City India Chennai 80 79 Delhi 75 78 USA Los Angeles 75 77 New York 32 30

In this example, we created a pivot table with a MultiIndex by passing a list of columns as an index argument.

A MultiIndex contains multiple levels of indexes with columns linked to one another through a parent/relationship. Here, Country is the parent column and City is the child column.

Handle Missing Values With pivot_table()

Sometimes while reshaping data using pivot_table(), missing values may occur in the pivot table. Such missing values or NaN values can be handled in a pivot_table() operation using the arguments fill_value and dropna.

The dropna argument specifies whether to remove the columns whose entries are all NaN. The default value of dropna is True.

Let's look at an example.

 import pandas as pd import numpy as np # Creating the DataFrame data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02', '2023-01-03', '2023-01-03', '2023-01-03'], 'City': ['New York', 'Los Angeles', 'New York', 'Los Angeles', 'New York', 'Los Angeles', 'Chicago'], 'Temperature': [32, 75, 30, 77, np.nan, 76, np.nan]} df = pd.DataFrame(data) # create a pivot table pivot_df = df.pivot_table(index='Date', columns='City', values='Temperature') print("\nDefault Pivot Table\n", pivot_df) 
# create a pivot table with dropna=True pivot_df_dropna = df.pivot_table(index='Date', columns='City', values='Temperature', dropna=False) 
 print("\nPivot Table with dropna=False:\n", pivot_df_dropna)

Output

 Default Pivot Table City Los Angeles New York Date 2023-01-01 75.0 32.0 2023-01-02 77.0 30.0 2023-01-03 76.0 NaN Pivot Table with dropna=False: City Chicago Los Angeles New York Date 2023-01-01 NaN 75.0 32.0 2023-01-02 NaN 77.0 30.0 2023-01-03 NaN 76.0 NaN

In this example, we used the dropna function to determine the handling of columns with entirely NaN entries. By default, the dropna parameter is set to True, resulting in the automatic removal of the Chicago column.

Notice that the New York column is not dropped despite having one NaN value. This is because dropna removes the columns whose entries are all NaN.

The fill_value argument on the other hand replaces all the NaN values with a specified value. For example,

 import pandas as pd import numpy as np # Creating the DataFrame data = {'Date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02', '2023-01-03', '2023-01-03'], 'City': ['New York', 'Los Angeles', 'New York', 'Los Angeles', 'New York', 'Los Angeles'], 'Temperature': [32, np.nan, 30, 77, np.nan, 76]} df = pd.DataFrame(data) # create a pivot table pivot_df = df.pivot_table(index='Date', columns='City', values='Temperature') print("\nDefault Pivot Table\n", pivot_df) 
# create a pivot table with fill_value=0 pivot_df_dropna = df.pivot_table(index='Date', columns='City', values='Temperature', fill_value=0)
 print("\nPivot Table with fill_value=0:\n", pivot_df_dropna)

Output

 Default Pivot Table City Los Angeles New York Date 2023-01-01 NaN 32.0 2023-01-02 77.0 30.0 2023-01-03 76.0 NaN Pivot Table with fill_value=0: City Los Angeles New York Date 2023-01-01 0 32 2023-01-02 77 30 2023-01-03 76 0

In this example, we replaced the NaN values with 0 using the fill_value=0 argument.

pivot() vs pivot_table()

The pivot() and pivot_table() functions perform similar operations but with few key differences.