Boring Data? Visualize it!

Data visualization is a crucial component of data analysis, helping to transform complex data insights into a clear and easier way of understanding. In this article, we’ll explore how to extract data from a PostgreSQL database, manipulate it using Pandas, and visualize it. To demonstrate the visualization capabilities, we’ll specifically focus on creating a bar plot showing the top customers by the total amount spent.

Set it up!

First, we need to establish a connection to our PostgreSQL database. We’ll use the psycopg2 library for this purpose. Ensure you have the necessary packages installed:

pip install psycopg2 pandas seaborn matplotlib

Next, let’s write the Python code to connect to the database and execute a query to fetch the required data.

Connect it!

import psycopg2
import psycopg2.extras
import pandas as pd

# Connect to PostgreSQL database
conn = psycopg2.connect(
    host="your_host",
    database="your_database",
    user="your_user",
    password="your_password"
)

# Execute SQL query
sql_query = """
SELECT c.first_name, c.last_name, SUM(t.amount) AS total_amount, COUNT(t.amount) AS num_transactions 
FROM transactions t
INNER JOIN customer c ON c.pk_customer_id = t.fk_customer_id
GROUP BY c.first_name, c.last_name
HAVING SUM(t.amount) > 800
ORDER BY total_amount DESC
LIMIT 10;
"""
df = pd.read_sql_query(sql_query, conn)
print(df)

# Close database connection
conn.close()

Here, we connect to the PostgreSQL database and execute a SQL query to retrieve the first names, last names, and total amounts spent by customers who have spent more than $800. The results are limited to the top 10 customers if available.
If we retrieve this data directly from the database, we would see something like this:

It shows a nice and simple table, but satabase tables can become quite complex, so to simplify understanding and highlight key information, visualizing the data is always a helpful approach.

Visualize it!

Once we have our data in a Pandas DataFrame, we can use Seaborn to create a bar plot. Seaborn is a powerful visualization library built on top of Matplotlib, providing a high-level interface for drawing attractive and informative statistical graphics.

import seaborn as sns
import matplotlib.pyplot as plt

# Set seaborn style
sns.set_style("whitegrid")

# Create a bar plot of total amount spent by top customers
plt.figure(figsize=(10, 6))
barplot = sns.barplot(data=df, x='first_name', y='total_amount', palette="viridis")
plt.title('Top Customers by Total Amount Spent', fontsize=16)
plt.xlabel('Customers', fontsize=14)
plt.ylabel('Total Amount Spent', fontsize=14)
plt.xticks(fontsize=12, rotation=45)
plt.yticks(fontsize=12)
plt.tight_layout()
# Add labels above the bars
for p in barplot.patches:
    height = p.get_height()
    barplot.annotate(f'{height:.0f}',
                     xy=(p.get_x() + p.get_width() / 2., height),
                     xytext=(0, 3),  # 3 points vertical offset
                     textcoords="offset points",
                     ha='center', va='center')
plt.show()

In this visualization:

We set the Seaborn style to “whitegrid” for better aesthetics.
A bar plot is created using the sns.barplot function, where the x-axis represents the first names of the top customers, and the y-axis represents the total amount spent.
The palette="viridis" argument specifies the color palette for the bars.
Labels are added above each bar to show the exact amount spent.

Recap:

You’ve seen how to set up, connect, and visualize your data. Follow these steps to recreate and customize it even more to your needs.

Thank you for reading! If you enjoyed this article, please consider supporting me in the following ways:

Like this story 👏 as many times as you like to show your appreciation.
Follow me on Medium.
Leave a comment 💬 to share your thoughts and feedback.
Buy me a coffee ☕️.
Visit My Digital Store for PostgreSQL Tips, Ebooks, Excel Templates and more!

Your support means the world to me. Thank you!

TheCodeCraft