From JSON to the Navigation Desktop

Photo via editor Chatgpt

# Entry

The data is the most essential resource of the company, and the insights from the data may have a difference between profit and failure. However, raw data is arduous to understand, which is why we visualize it in navigation desktops so that non -technical people can move better with them.

Building a dashboard is not basic, especially when working with JSON data. Fortunately, many Python libraries can be combined to create a helpful tool.

In this article, we will learn how to develop a navigation desktop using Smurtlit and a conspiracy to visualize DuckDB queries regarding data from the JSON file.

Inquisitive? Let’s get it.

# District development of the dashboard

Before developing our navigation desktop, let’s learn a bit about the tools we will apply.

First of all, the notation of JSON or JavaScript is a text format for storing and sending data using key pairs and boards. This is a commonly used format for API interfaces and data exchange between systems.

Next, Duckdb It is Open Source RDBMS (relational database management system) designed for analytical loads. It is a SQL database of analytical processing (OLAP), which operates directly in the Python process without having to manage a separate server. It is also optimized for quick performance, ideal for data analysis with enormous data sets.

Tasty It is often used to develop the navigation desktop. It is an open source structure for creating interactive internet data applications using Python. To develop a navigation desktop, we do not need to understand HTML, CSS or JavaScript.

We will also apply PandyA powerful library of data manipulation and python analysis.

At last, Conspiracy It is an open source library to develop interactive charts and charts. It can be integrated with the navigation desktop development libraries, such as outflow.

This is the basic explanation of the tools we will apply. Let’s start developing our JSON desktop desktop. We will apply the following structure, so try to create it as follows.

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

Then fill out the files with all the required information. First, let’s have our sample JSON data, such as those below. You can always apply your own data, but here is an example that you can apply.

[
  {"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"}
]

Then fill out requirements.txt File with libraries that we will apply to develop the navigation desktop.

streamlit
duckdb
pandas
plotly

Then run the following code to install the required libraries. When configuring the environment, it is recommended to apply the virtual environment.

pip install -r requirements.txt

When everything is ready, we will develop our navigation desktop. We will examine the application code step by step so that you can track logic.

Let’s start by importing the necessary libraries for our navigation desktop.

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

Then we will configure the connection that we need to DuckDB.

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

The above code bufores the DuckDB connection, so that the navigation desktop is slowly slowing down again after the navigation desktop is restarted, which avoids performance delays.

Then we prepare the code to read 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 transform the JSON file into Panda DataFrame And bake the data so that we do not have to read them again after changing the filter.

After preparing data charging and connection, we will connect to DuckDB to store JSON data. You can always change data location and table name.

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

We register in the above code DataFrame As a SQL table called sales Inside Duckdb. The table will be refreshed from memory at each replay, because we do not configure perseverance in a separate script.

It’s all for Backend; Let’s prepare resolution discharge. First, prepare the title of the dashboard 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 side belt above will become a vigorous filter for loaded data, in which we can change the SQL query based on these filters.

Then we build the SQL inquiry according to the filters using 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 query is dynamically built based on the user’s selection. We start with WHERE TRUE The condition for simplifying the attachment of additional filters with AND.

By generating a query, we will display the query and the resulting data with the following code.

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 above code shows the SQL query used to download data from DuckDB and transforms the result into Panda DataFrame To display a filtered table.

Finally, we will prepare story visualizations 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 above code, we create three different charts: dispersion chart, bar chart and linear chart. You can always change the chart type according to your needs.

When preparing the entire code, we will launch the following command to start our size desktop.

You can now access the navigation desktop, which looks like a picture below.

The charts will look like a picture below.

Since the visualizations apply the plot, you can navigate them interactive in them, as shown on the line table below.

That’s all you need to know. You can always augment the complexity of the navigation desktop and even implement it in your company.

# Application

The data is the most valuable resource that the company may have, and visualization in the navigation desktop is a way to get business information. In this article, we have learned to develop a uncomplicated navigation desktop with outflow and registrar, connecting to the data from the JSON file stored in DuckDB.

I hope it helped!

Cornellius Yudha Wijaya He is a data assistant and data writer. Working full -time at Allianz Indonesia, he loves to share Python and data tips through social media and media writing. Cornellius writes on various AI topics and machine learning.