Pivot Table in Jupyter Notebook and Web App

In this example, you will learn how to create a web app that can do a pivot table on CSV data uploaded by the user.

To create a pivot table, we will use a custom widget pivottablejs (opens in a new tab). It is compatible with Jupyter Notebook/Lab and Mercury.

The dataset will be loaded from a CSV file with File widget from Mercury. The notebook and web app are presented in the below animation.

Required packages

You will need the following packages to run this example:

mercury
pandas
pivottablejs

• mercury is for the file upload widget and for serving notebook as a web app,
• pandas is for reading CSV file,
• pivottablejs is for displaying the pivot table user interface.

Notebook

In the first cell, please import the required packages:

import pandas as pd
import mercury as mr
from pivottablejs import pivot_ui

Let's setup Mercury app properties:

app = mr.App(title="Pivot table", 
             description="Do pivot table on any CSV file")

The file upload widget is added with one line of code:

my_file = mr.File(label="Upload CSV file")

We stop the execution of the notebook until the file is uploaded. We use Stop widget to stop the execution of the notebook.

if my_file.filepath is None:
    mr.Markdown("Please upload CSV file")
    mr.Stop()

We load the dataset with the pandas package:

# load CSV data
df = pd.read_csv(my_file.filepath)

The pivot table for our DataFrame is displayed with the following code:

# display pivot table 
pivot_ui(df, height="800px")

You can analyze your DataFrame with a pivot table in the Jupyter Notebook. Here is the screenshot with the code in Jupyter Notebook:

Mercury App

You can share your notebook with non-technical users. They will be able to upload their dataset in CSV format and perform analysis with a pivot table (you will make them happy!).

Please start Mercury Server in the same directory as notebook:

mercury run

You will see the app running at http://127.0.0.1:8000. You will see a home view with your notebook. Please click on its title. You should see the web app running. After uploading an example CSV file, you should see an app similar to in the screenshot below:

The notebook from this example is available in the mercury-examples (opens in a new tab) repository.