From JSON to Dashboard: Imagine Dick DB questions in Streamlit with Platley

Photo by Editor | Chat GPT

. Introduction

Data is the most important source of a company, and data can make the difference between profits and failure. However, it is difficult to understand the raw data, so we consider it in dashboards so that non -technical people can get it better.

Making a dashboard is not straightforward, especially when working with JSON data. Fortunately, many of the many libraries can be linked to a helpful tool.

In this article, we will learn that a dashboard should be developed using streamlets and plots to view Dickdb questions on data from JSON file.

Careful Let’s enter it.

. Dashboard Development

Before preparing our dashboard, let’s learn a little about the tools we will use.

First, JSON, or JavaScript is a text -based format for storing and transferring data using the object -value notification, key value pairs and ranks. This is a commonly used form for APIS and data interchange between systems.

Next, Duck db An open source RDBMS (relative database management system) designed for the burden of analytical work. It is a process online analytical processing (OLAP) SQL database that operates directly into the process without need to manage a separate server. It is also better for rapid processing, ideal for data analysis with large datases.

Streamlit Often used for dashboard development. This is an open source framework for developing interactive data web applications using Azigar. We do not need to understand the Dashboard, we do, HTML, CSS, or JavaScript.

We will also use PandasA powerful library for data manipulation and analysis in Azar.

Finally, From the plot There is an open source library to produce interactive graphs and charts. It can be connected with a dashboard development libraries such as streamlines.

This is the basic explanation of the tools we will use. Let’s start preparing your JSON dashboard. We will use the following structure, so try to make it as follows.

JSON_Dashboard/
├── data/
│   └── sample.json
├── app.py
└── requirements.txt

Next, let’s fill the files with all the required information. First, let’s keep our JSON exemplified figures below. You can always use your data, but here is an example you can use.

(
  {"id": 1, "category": "Electronics", "region": "North", "sales": 100, "profit": 23.5, "date": "2024-01-15"},
  {"id": 2, "category": "Furniture", "region": "South", "sales": 150, "profit": 45.0, "date": "2024-01-18"},
  {"id": 3, "category": "Electronics", "region": "East", "sales": 70, "profit": 12.3, "date": "2024-01-20"},
  {"id": 4, "category": "Clothing", "region": "West", "sales": 220, "profit": 67.8, "date": "2024-01-25"},
  {"id": 5, "category": "Furniture", "region": "North", "sales": 130, "profit": 38.0, "date": "2024-02-01"},
  {"id": 6, "category": "Clothing", "region": "South", "sales": 180, "profit": 55.2, "date": "2024-02-05"},
  {"id": 7, "category": "Electronics", "region": "West", "sales": 90, "profit": 19.8, "date": "2024-02-10"},
  {"id": 8, "category": "Furniture", "region": "East", "sales": 160, "profit": 47.1, "date": "2024-02-12"},
  {"id": 9, "category": "Clothing", "region": "North", "sales": 200, "profit": 62.5, "date": "2024-02-15"},
  {"id": 10, "category": "Electronics", "region": "South", "sales": 110, "profit": 30.0, "date": "2024-02-20"}
)

Next, we’ll fill requirements.txt File with these libraries we will use for the development of our dashboard.

streamlit
duckdb
pandas
plotly

Then, run the following code to install the desired libraries. It is recommended to use virtual environment when setting the environment.

pip install -r requirements.txt

Once everything is ready, we will prepare our dashboard. We will find the application code step by step so you can follow the logic.

Let’s start importing the necessary libraries for our dashboard.

import streamlit as st
import duckdb
import pandas as pd
import plotly.express as px

Next, we will set up a connection required for Dick DB.

@st.cache_resource
def get_conn():
    return duckdb.connect()

The aforementioned code Dick DB will catch connection so when the dashboard works again, the streamllate dashboard does not need to be re -contacted, which avoids the performance in any way.

After that, we create a code to read the JSON data using the following code.

@st.cache_data
def load_data(path):
    df = pd.read_json(path, convert_dates=("date"))
    return df

In the above code, we turn the JSON file into pandas DataFrame And cash the data so that when the filter changes, we don’t need to read it again.

After data loading and connection is developed, we will contact Dickdb to store JSON data. You can always rename data location and table.

conn = get_conn()
df_full = load_data("data/sample.json")
conn.execute("CREATE OR REPLACE TABLE sales AS SELECT * FROM df_full")

In the above code, we register DataFrame Nominated as SQL Table sales The table within the Dick DB will be refreshed from memory at every work again, as we are not setting up a separate script.

It’s all for bacland. Let’s prepare a streamllate dashboard. First, let’s create a dashboard title and sidebar filters.

st.title("From JSON to Dashboard: DuckDB SQL Visualizer")

st.sidebar.header("Filter Options")
category = st.sidebar.multiselect("Select Category:", df_full('category').unique())
region = st.sidebar.multiselect("Select Region:", df_full('region').unique())
date_range = st.sidebar.date_input("Select Date Range:", (df_full('date').min(), df_full('date').max()))

The aforementioned sidebar will become a dynamic filter for filled data, where we can change the SQL inquiry based on these filters.

We then prepare SQL inquiry according to the filters with the following code.

query = "SELECT * FROM sales WHERE TRUE"
if category:
    query += f" AND category IN {tuple(category)}"
if region:
    query += f" AND region IN {tuple(region)}"
query += f" AND date BETWEEN '{date_range(0)}' AND '{date_range(1)}'"

The above question is dynamic based on the user’s selection. We start with one WHERE TRUE Condition to make it easy to add with additional filters AND.

With the development of an inquiry generation, we will show the inquiry with the following code and the resulting data.

st.subheader("Generated SQL Query")
st.code(query, language="sql")

df = conn.execute(query).df()
st.subheader(f"Query Results: {len(df)} rows")
st.dataframe(df)

The aforementioned code shows the SQL inquiry used to retrieve data from Dickdb and convert results to pandas DataFrame To display the filtered table.

Finally, we will develop concepts from plot using filtered data.

if not df.empty:
    col1, col2 = st.columns(2)

    with col1:
        st.markdown("### Scatter Plot: Sales vs Profit by Region")
        scatter_fig = px.scatter(df, x="sales", y="profit", color="region", hover_data=("category", "date"))
        st.plotly_chart(scatter_fig, use_container_width=True)

    with col2:
        st.markdown("### Bar Chart: Total Sales by Category")
        bar_fig = px.bar(df.groupby("category", as_index=False)("sales").sum(), x="category", y="sales", text_auto=True)
        st.plotly_chart(bar_fig, use_container_width=True)

    st.markdown("### Line Chart: Daily Sales Trend")
    line_fig = px.line(df.groupby("date", as_index=False)("sales").sum(), x="date", y="sales")
    st.plotly_chart(line_fig, use_container_width=True)
else:
    st.warning("No data found for the selected filters.")

In the aforementioned code, we produce three different plots: a scrutory plot, once a chart, and a line chart. You can always change the type of chart according to your needs.

With all the code ready, we will run the following command to launch our streamlit dashboard.

Now you can access the dashboard, which looks like a picture below.

The plots will look like a picture below.

Since concepts use with plot, you can navigate them interactively, as shown in the line chart below.

That’s all you need to know. You can always add more complications to the dashboard and even deploy it in your business.

. Conclusion

Data is the most valuable resources of a company, and watching it on the dashboard is a way to get insights for business people. In this article, we learned that the Dick DB has to develop a simple dashboard with streamlets and plots while contacting data from the JSON file.

I hope it has helped!

Cornelius Yodha Vijaya Data Science is Assistant Manager and Data Writer. Elijan, working in Indonesia for a full time, likes to share indicators of data and data through social media and written media. CorneLius writes on various types of AI and machine learning titles.