Skip to main content
TopMiniSite

Back to all posts

How to Create A Pivot Table In Pandas?

Published on
6 min read
How to Create A Pivot Table In Pandas? image

Best Python Data Tools to Buy in October 2025

1 Python for Data Analysis: Data Wrangling with pandas, NumPy, and Jupyter

Python for Data Analysis: Data Wrangling with pandas, NumPy, and Jupyter

BUY & SAVE
$43.99 $79.99
Save 45%
Python for Data Analysis: Data Wrangling with pandas, NumPy, and Jupyter
2 Python Data Science Handbook: Essential Tools for Working with Data

Python Data Science Handbook: Essential Tools for Working with Data

BUY & SAVE
$44.18 $79.99
Save 45%
Python Data Science Handbook: Essential Tools for Working with Data
3 Python Tools for Scientists: An Introduction to Using Anaconda, JupyterLab, and Python's Scientific Libraries

Python Tools for Scientists: An Introduction to Using Anaconda, JupyterLab, and Python's Scientific Libraries

BUY & SAVE
$40.35 $49.99
Save 19%
Python Tools for Scientists: An Introduction to Using Anaconda, JupyterLab, and Python's Scientific Libraries
4 Python for Excel: A Modern Environment for Automation and Data Analysis

Python for Excel: A Modern Environment for Automation and Data Analysis

BUY & SAVE
$39.98 $65.99
Save 39%
Python for Excel: A Modern Environment for Automation and Data Analysis
5 50 Days of Data Analysis with Python: The Ultimate Challenges Book for Beginners.: Hands-on Challenges with pandas, NumPy, Matplotlib, Sklearn and Seaborn

50 Days of Data Analysis with Python: The Ultimate Challenges Book for Beginners.: Hands-on Challenges with pandas, NumPy, Matplotlib, Sklearn and Seaborn

BUY & SAVE
$29.99
50 Days of Data Analysis with Python: The Ultimate Challenges Book for Beginners.: Hands-on Challenges with pandas, NumPy, Matplotlib, Sklearn and Seaborn
6 Python Polars: The Definitive Guide: Transforming, Analyzing, and Visualizing Data with a Fast and Expressive DataFrame API

Python Polars: The Definitive Guide: Transforming, Analyzing, and Visualizing Data with a Fast and Expressive DataFrame API

BUY & SAVE
$64.51 $79.99
Save 19%
Python Polars: The Definitive Guide: Transforming, Analyzing, and Visualizing Data with a Fast and Expressive DataFrame API
7 Mastering Data Analysis With Python: Wrangle, Clean, Analyze, and Visualize Data from Scratch

Mastering Data Analysis With Python: Wrangle, Clean, Analyze, and Visualize Data from Scratch

BUY & SAVE
$34.99
Mastering Data Analysis With Python: Wrangle, Clean, Analyze, and Visualize Data from Scratch
+
ONE MORE?

To create a pivot table in Pandas, you can use the pivot_table() function provided by the library. Here is how you can do it:

First, import the Pandas library:

import pandas as pd

Next, create a DataFrame with the data you want to use for the pivot table. Here is an example DataFrame representing sales data:

data = { 'Product': ['A', 'B', 'A', 'B', 'A', 'B'], 'Region': ['North', 'North', 'South', 'South', 'East', 'East'], 'Sales': [100, 200, 150, 250, 120, 180] }

df = pd.DataFrame(data)

To create a basic pivot table, use the pivot_table() function. Specify the DataFrame, the column(s) to use as index, the column(s) to use as columns, and the column(s) to use for the values. In this example, 'Product' and 'Region' are used as index, 'Sales' as values, and 'Region' as columns:

pivot_table = pd.pivot_table(df, index=['Product', 'Region'], columns='Region', values='Sales')

The resulting pivot table will have 'Product' and 'Region' as the index, 'Region' as the columns, and 'Sales' as the values. It will show the sales data for each product and region combination.

You can also apply different aggregation functions to the values by specifying the aggfunc parameter. For example, if you want to calculate the sum of sales instead of the default mean, you can use:

pivot_table = pd.pivot_table(df, index=['Product', 'Region'], columns='Region', values='Sales', aggfunc=sum)

By default, missing values are represented as NaN in the pivot table. If you want to fill in these missing values with a specific value, you can use the fill_value parameter. For example, to replace the missing values with 0, you can use:

pivot_table = pd.pivot_table(df, index=['Product', 'Region'], columns='Region', values='Sales', fill_value=0)

You can also use multiple columns for both index and columns by passing a list of column names to the respective parameters. The resulting pivot table will have a hierarchical index and columns.

That's it! Now you know how to create a pivot table in Pandas.

How to merge two DataFrames in Pandas?

To merge two DataFrames in Pandas, you can use the merge() function. Here is an example of how to do it:

import pandas as pd

Create two DataFrames

df1 = pd.DataFrame({'A': [1, 2, 3], 'B': ['a', 'b', 'c']}) df2 = pd.DataFrame({'A': [1, 3, 4], 'C': ['x', 'y', 'z']})

Merge the DataFrames on column 'A'

merged_df = pd.merge(df1, df2, on='A')

print(merged_df)

Output:

A B C 0 1 a x 1 3 c y

In this example, we have two DataFrames df1 and df2. We want to merge them based on the common values in column 'A'. The merge() function takes the two DataFrames to be merged as arguments, and we specify the column to merge on using the on parameter.

By default, merge() performs an inner join, meaning that only the rows with matching values in both DataFrames will be included in the merged DataFrame. If you want to include all rows from both DataFrames, you can use the how parameter and set it to 'outer'.

merged_df = pd.merge(df1, df2, on='A', how='outer')

Additionally, you can merge on multiple columns by passing a list of column names to the on parameter:

merged_df = pd.merge(df1, df2, on=['A', 'B'])

How to rename values in a pivot table using Pandas?

To rename values in a pivot table using Pandas, you can use the replace() or map() functions.

Here's an example of how you can rename values in a pivot table using the replace() function:

import pandas as pd

Create a pivot table

pivot_table = pd.pivot_table(data, values='Value', index='Category', columns='Year', aggfunc='sum')

Rename values in the pivot table

pivot_table = pivot_table.replace({'Old Value': 'New Value'})

print(pivot_table)

In this example, the pivot_table.replace() function is used to replace the old value with a new value in the pivot table.

Alternatively, you can also use the map() function to rename values in a pivot table. Here's an example:

import pandas as pd

Create a pivot table

pivot_table = pd.pivot_table(data, values='Value', index='Category', columns='Year', aggfunc='sum')

Define a dictionary to map the old values to new values

value_map = {'Old Value': 'New Value'}

Rename values in the pivot table using the map function

pivot_table = pivot_table.applymap(lambda x: value_map.get(x, x))

print(pivot_table)

In the second example, the pivot_table.applymap() function is used to apply a lambda function to each cell in the pivot table. The lambda function uses the value_map.get() method to replace the old values with new values from the dictionary.

How to read a CSV file using Pandas?

To read a CSV file using Pandas, you can follow these steps:

  1. Import the Pandas library:

import pandas as pd

  1. Use the read_csv() function to read the CSV file and store it in a DataFrame:

df = pd.read_csv('filename.csv')

  1. View the contents of the DataFrame:

print(df)

You can also specify additional parameters while reading the CSV file. Some commonly used parameters include:

  • delimiter: Specifies the delimiter used in the CSV file. By default, it is a comma (',').
  • header: Specifies the row number(s) to use as the column names. If not provided, the first row is assumed to be the column names.
  • index_col: Specifies which column(s) to use as the index (row labels) of the DataFrame.
  • usecols: Specifies a subset of columns to read from the CSV file.
  • dtype: Specifies the data type for the columns.

Here is an example that shows how to read a CSV file with a comma as the delimiter and the first row as the column names:

df = pd.read_csv('filename.csv', delimiter=',', header=0)

You can explore more about reading CSV files using Pandas in the official documentation: Pandas read_csv().

What is the purpose of a pivot table in data analysis?

The purpose of a pivot table in data analysis is to summarize, analyze, and interpret large amounts of data in a structured format. It allows users to reorganize and manipulate data in a flexible manner, providing a clearer understanding of the underlying trends, patterns, and relationships. Pivot tables provide a way to group and aggregate data based on specific variables or dimensions, enabling users to perform calculations, generate reports, and extract meaningful insights from the data. It helps in making data-driven decisions, identifying anomalies, and presenting information in a concise and visual manner.