Image for post
Image for post

How to Create a Pivot Table with Python/Pandas in a Jupyter Notebook

Translating one of Excel’s most useful tools into Python/Pandas

Image for post
Image for post

Step 1: Setting up your notebook

import pandas as pd
import numpy as np
Image for post
Image for post

Step 2: Grab some CSV data from Kaggle

Image for post
Image for post

Step 3: CSV to Dataframe

df = pd.read_csv('')
Image for post
Image for post
Image for post
Image for post
df = pd.read_csv('~/Desktop/MoviesOnStreamingPlatforms_updated.csv')

Step 4: Pivot Table, and Playing with the Pivot Table

pivot_table = df.pivot_table()
.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All', observed=False
pd.pivot_table(df, index='', values='', aggfunc='')
df.pivot_table(index='', values='', aggfunc='')
df.head()
Image for post
Image for post
df.pivot_table(
index='Year',
values='IMDb',
aggfunc=np.count_nonzero
)
Image for post
Image for post
df.pivot_table(
index='Year',
values='IMDb',
aggfunc=np.count_nonzero
).reset_index()
Image for post
Image for post
pivot_table = df.pivot_table(
index='Year',
values='IMDb',
aggfunc=np.count_nonzero
).reset_index()
pivot_table.columns = ['Year','Count_of_movies']
Image for post
Image for post
pivot_table[-10:]
Image for post
Image for post
pivot_table[:10]
Image for post
Image for post
pivot_table.sort_values('Count_of_movies',ascending=False)[:10]
Image for post
Image for post
mean_pivot_table = df.pivot_table(
index='Year',
values='IMDb',
aggfunc=np.mean).reset_index()
mean_pivot_table.columns = ['Year','avg_IMDB_rating']
mean_pivot_table.sort_values('avg_IMDB_rating',ascending=False)[:10]
Image for post
Image for post
filtered_pivot = pivot_table.loc[pivot_table.Year >= 2000]
filtered_mean_pivot.sort_values('avg_IMDB_rating',ascending=False)
Image for post
Image for post
filtered_mean_pivot['count'] = filtered_mean_pivot.Year.map(
pivot_table.set_index('Year').Count_of_movies)
Image for post
Image for post
Image for post
Image for post

Written by

Documentation and tutorials on Python, Programming, and Data Analysis. FPL Addict. Occasionally writing about biohacking, PMing, and food.

Get the Medium app