Plotting with plotly#

Plotly is a visualization library specifically designed for dynamic interactive plots. The library offers several additional effects to visualizations, such as zooming, panning, and hovering effects. This library is especially known for being easily deployed with web applications.

For more on plotly, visit: https://plotly.com/python/

Let’s see how we can apply plotly to our familiar bank marketing data sets.

Install - execute this once.#

Important

Note: The --save and %sqlcmd features used require the latest JupySQL version. Ensure you run the code below to update JupySQL.

This code installs JupySQL, DuckDB, and Pandas in your environment. We will be using these moving forward.

%pip install jupysql pandas plotly --quiet

Load the data#

Important

Note: If you are following these lessons locally and not on Google Colab, then there is no need to load the data again.

This section was covered in detail in the previous tutorial: Joining Data in SQL. We will be using the same data in this tutorial as well.

import sys
import plotly.express as px
import pandas as pd

sys.path.insert(0, "../../")
import banking  # noqa: E402

_ = banking.MarketData(
    "https://web.archive.org/web/20070214120527/http://lisp.vse.cz/pkdd99/DATA/data_berka.zip",  # noqa E501
    "expanded_data",
)

_.convert_asc_to_csv(banking.district_column_names)
Error, could not download data: HTTP Error 429: Too Many Requests
Error, could not convert ASC to CSV: 'NoneType' object has no attribute 'namelist'

If you ran the above cell, you should have a folder expanded_data in your current directory that contains the .csv files we will be using. In this tutorial, we will be focusing on three of these files: loan.csv, account.csv, district.csv.

Load Engine#

We now load in our SQL extension that allows us to execute SQL queries in Jupyter Notebooks.

Important

Note Ensure you restart any previous notebook that has the same database name as the one initialized below.

# Loading in SQL extension
%reload_ext sql
# Initiating a DuckDB database named 'bank_data.duck.db' to run SQL queries
%sql duckdb:///bank_data.duck.db
Found pyproject.toml from '/home/docs/checkouts/readthedocs.org/user_builds/ploomber-sql/checkouts/latest'
Settings changed:
Config value
displaycon False
feedback True
autopandas False
named_parameters True

Creating Tables#

Let’s start off with loading three of the eight .csv files from the expanded_data folder in the current directory to our newly created DuckDB database. Like in the previous tutorial, we will create a schema s1 in which we will store the tables. Here we use the CREATE TABLE syntax in DuckDB to ingest four of the eight .csv files. The read_csv_auto is a function that helps SQL understand our local .csv file for creation into our database.

%%sql
CREATE SCHEMA s1;
CREATE TABLE s1.account AS
FROM read_csv_auto('expanded_data/account.csv', header=True, sep=',');
CREATE TABLE s1.district AS
FROM read_csv_auto('expanded_data/district.csv', header=True, sep=',');
CREATE TABLE s1.loan AS
FROM read_csv_auto('expanded_data/loan.csv', header=True, sep=',');
Count

The code above will create three tables in the database schema: s1.account, s1.district, s1.loan.

Exploring the data#

Let’s take a look at each table.

%sqlcmd explore --table s1.account
%sqlcmd explore --table s1.district
%sqlcmd explore --table s1.loan

Bar Plots#

Let’s create a bar plot in plotly using the s1.district table. This plot will visualize the count of each region in the data set.

First, let’s query the count of each region in SQL.

%%sql --save region_count
SELECT region, COUNT(*) as count
FROM s1.district
GROUP BY region
ORDER BY count DESC
region count
south Moravia 14
central Bohemia 12
east Bohemia 11
north Moravia 11
west Bohemia 10
north Bohemia 10
south Bohemia 8
Prague 1

Then, let’s save the CTE as a Pandas DataFrame.

query = %sql SELECT * FROM region_count
region_count_df = pd.DataFrame(query)
region_count_df
Generating CTE with stored snippets: 'region_count'
region count
0 south Moravia 14
1 central Bohemia 12
2 east Bohemia 11
3 north Moravia 11
4 west Bohemia 10
5 north Bohemia 10
6 south Bohemia 8
7 Prague 1

We can now plot the count of each region with this Pandas DataFrame.

fig = px.bar(
    region_count_df,
    x="region",
    y="count",
    title="Region Count",
    color_discrete_sequence=["#7d26cd"],
)
fig.show()