Mastering Pandas DataFrame Pivot Table

Introduction

In this lab, we will learn about the pivot_table() method in the Python pandas library. The pivot_table() method is used to aggregate and summarize data in a DataFrame. It returns a spreadsheet-style pivot table as a new DataFrame.

VM Tips

After the VM startup is done, click the top left corner to switch to the Notebook tab to access Jupyter Notebook for practice.

Sometimes, you may need to wait a few seconds for Jupyter Notebook to finish loading. The validation of operations cannot be automated because of limitations in Jupyter Notebook.

If you face issues during learning, feel free to ask Labby. Provide feedback after the session, and we will promptly resolve the problem for you.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL pandas(("`Pandas`")) -.-> pandas/DataSelectionGroup(["`Data Selection`"]) pandas(("`Pandas`")) -.-> pandas/DataAnalysisGroup(["`Data Analysis`"]) python(("`Python`")) -.-> python/DataStructuresGroup(["`Data Structures`"]) python(("`Python`")) -.-> python/ModulesandPackagesGroup(["`Modules and Packages`"]) python(("`Python`")) -.-> python/DataScienceandMachineLearningGroup(["`Data Science and Machine Learning`"]) python(("`Python`")) -.-> python/FunctionsGroup(["`Functions`"]) pandas/DataSelectionGroup -.-> pandas/select_columns("`Select Columns`") pandas/DataAnalysisGroup -.-> pandas/pivot_tables("`Pivot Tables`") python/DataStructuresGroup -.-> python/lists("`Lists`") python/DataStructuresGroup -.-> python/tuples("`Tuples`") python/DataStructuresGroup -.-> python/dictionaries("`Dictionaries`") python/ModulesandPackagesGroup -.-> python/importing_modules("`Importing Modules`") python/DataScienceandMachineLearningGroup -.-> python/numerical_computing("`Numerical Computing`") python/DataScienceandMachineLearningGroup -.-> python/data_analysis("`Data Analysis`") python/FunctionsGroup -.-> python/build_in_functions("`Build-in Functions`") subgraph Lab Skills pandas/select_columns -.-> lab-68694{{"`Pandas DataFrame Pivot Table Method`"}} pandas/pivot_tables -.-> lab-68694{{"`Pandas DataFrame Pivot Table Method`"}} python/lists -.-> lab-68694{{"`Pandas DataFrame Pivot Table Method`"}} python/tuples -.-> lab-68694{{"`Pandas DataFrame Pivot Table Method`"}} python/dictionaries -.-> lab-68694{{"`Pandas DataFrame Pivot Table Method`"}} python/importing_modules -.-> lab-68694{{"`Pandas DataFrame Pivot Table Method`"}} python/numerical_computing -.-> lab-68694{{"`Pandas DataFrame Pivot Table Method`"}} python/data_analysis -.-> lab-68694{{"`Pandas DataFrame Pivot Table Method`"}} python/build_in_functions -.-> lab-68694{{"`Pandas DataFrame Pivot Table Method`"}} end

Import the required libraries and create the DataFrame

First, let's import the pandas library and create a DataFrame with some sample data. We will create a DataFrame with columns 'Date', 'State', 'Temperature', and 'Humidity'.

import pandas as pd

df = pd.DataFrame({'Date': ['1/1/2021', '1/1/2021', '2/1/2021', '2/1/2021', '1/1/2021', '1/1/2021', '2/1/2021', '2/1/2021'],
                   'State': ['Karnataka', 'Karnataka', 'Karnataka', 'Karnataka', 'Tamilnadu', 'Tamilnadu', 'Tamilnadu', 'Tamilnadu'],
                   'Temperature': [25, 29, 28, 31, 26, 27, 22, 32],
                   'Humidity': [46, 50, 52, 59, 42, 45, 46, 43]})

Aggregate the DataFrame using the `pivot_table()` method

To aggregate the data in the DataFrame using the pivot_table() method, we need to specify the columns we want to use as indices, columns, and the values we want to aggregate.

pivot_df = df.pivot_table(index='Date', columns='State', aggfunc='mean')

Display the resulting DataFrame

Finally, let's display the resulting pivot table DataFrame.

print(pivot_df)

Summary

By following these steps, we were able to use the pivot_table() method in the pandas library to aggregate and summarize data in a DataFrame. This method is useful for analyzing and visualizing data in a tabular format. The resulting pivot table DataFrame provides a convenient way to see the aggregated values based on different indices and columns.

Pandas DataFrame Pivot Table Method