Creating silky online desktops with Python, Taipa and Google sheets

Photo by the author Ideogram

# Entry

The data has become an significant resource for each company, because they are funds for companies to obtain valuable information, especially when making decisions. Without data, decisions are based solely on instinct and happiness, which is not the most effective approach.

However, huge amounts of raw data are complex to understand. It does not provide direct information and requires further processing. That is why many people involve using data desktops to summarize, visualize and move on raw data. By developing an elegant dashboard, we can provide a elementary way for non -technical users to easily obtain information from data.

That is why this article will examine how to create an elegant data desktop by using PythonIN TaipsAND Google sheets.

Let’s get it.

# Development of a chopped data desktop

We will start a tutorial by preparing all necessary certificates to access Google sheets via Python. First, create a Google account and go to Google Cloud Console. Then go to the API & Services> Library, where you need to enable the API Sheets Google Sheets and API Drive Google.

After enabling the API interfaces, return to API interfaces and services> certificates and go to the creation of a certificate> service account. Follow the instructions and assign a role such as the editor or owner, so that we can read and write to Google Arkus. Select the service account that we have just created, and then go to the keys> Add key> Create a up-to-date key. Choose Json and download credentials.json file. Store somewhere and open the file; Then copy the value e -mail below client_email.

In the case of a set of data we will utilize Set of heart data With Kaggle as an example. Store the file in Google and open it as Google sheets. In the Google Arreets file, go to the File> Share and add just copied E -Mail message. Finally, copy the URL of the Google sheet file file, because we will be available to data later via the URL.

Open your favorite IDE, and then struct our project as follows:

taipy_gsheet/
│
├── config/
│   └── credentials.json         
├── app.py                   
└── requirements.txt

Create all the necessary files and then start developing our navigation desktop. We will utilize taipa for application framework, Pandy for manipulation of data, Gspread AND Oauth2client for interaction with the API Sheets Google interface and conspiracy to create visualization. IN requirements.txt File, add the following packages:

taipy
pandas
gspread
oauth2client
plotly

These are necessary libraries for our tutorial and we will install them in our environment. Do not forget to utilize the virtual environment to prevent breaking the main environment. We will also utilize Python 3.12; Since this article is written, this is a Python version that currently works for the above libraries.

Install the libraries using the following command:

pip install -r requirements.txt

If the installation is successful, we will prepare our application. IN app.pyWe will build a code to configure our navigation desktop.

First of all, we import all the necessary libraries that we will utilize to develop the application.

import pandas as pd
import gspread
import plotly.express as px
import taipy as tp
from taipy import Config
from taipy.gui import Gui
import taipy.gui.builder as tgb

Then load the data from Google sheets using the following code. Change SHEET_URL Value using the actual URL data address. In addition, we will pre -process data to make sure they work well.

SHEET_URL = "https://docs.google.com/spreadsheets/d/1Z4S3hnV3710OJi4yu5IG0ZB5w0q4pmNPKeYy8BTyM8A/"
client = gspread.service_account(filename="config/credentials.json")
df_raw = pd.DataFrame(client.open_by_url(SHEET_URL).get_worksheet(0).get_all_records())
df_raw["sex"] = pd.to_numeric(df_raw["sex"], errors="coerce").fillna(0).astype(int)
df_raw["sex_label"] = df_raw["sex"].map({0: "Female", 1: "Male"})

Then we will prepare the dashboard with Taips. Taipy is an open source library for data-based applications, including both front-end development and in the back room. Let’s utilize the library to build a distribution board of data with basic functions that we can utilize with Taipa.

In the code below we will develop a script that is a pipeline that the user can do to analyze if-if. Basically, it is an experimenting framework with various parameters that we can transfer to the pipeline. For example, this is the way we prepare the script for the average age at the sex filter input.

def compute_avg_age(filtered_df: pd.DataFrame, gender_filter: str) -> float:
    data = (
        filtered_df
        if gender_filter == "All"
        else filtered_df[filtered_df["sex_label"] == gender_filter]
    )
    return round(data["age"].mean(), 1) if not data.empty else 0

filtered_df_cfg = Config.configure_data_node("filtered_df")
gender_filter_cfg = Config.configure_data_node("gender_filter")
avg_age_cfg = Config.configure_data_node("avg_age")

task_cfg = Config.configure_task(
    "compute_avg_age", compute_avg_age, [filtered_df_cfg, gender_filter_cfg], avg_age_cfg
)
scenario_cfg = Config.configure_scenario("cardiac_scenario", [task_cfg])
Config.export("config.toml")

Later we will come back the script, but let’s prepare the choice of sex and its default condition.

gender_lov = ["All", "Male", "Female"]
gender_selected = "All"
filtered_df = df_raw.copy()
pie_fig = px.pie()
box_fig = px.box()
avg_age = 0

Then we will create functions that update our variables and data visualizations when the user interacts with the dashboard desktop, for example by choosing sex or sending the script.

def update_dash(state):
    subset = (
        df_raw if state.gender_selected == "All"
        else df_raw[df_raw["sex_label"] == state.gender_selected]
    )
    state.filtered_df = subset
    state.avg_age = round(subset["age"].mean(), 1) if not subset.empty else 0

    state.pie_fig = px.pie(
        subset.groupby("sex_label")["target"].count().reset_index(name="count"),
        names="sex_label", values="count",
        title=f"Target Count -- {state.gender_selected}"
    )
    state.box_fig = px.box(subset, x="sex_label", y="chol", title="Cholesterol by Gender")

def save_scenario(state):
    state.scenario.filtered_df.write(state.filtered_df)
    state.scenario.gender_filter.write(state.gender_selected)
    state.refresh("scenario")
    tp.gui.notify(state, "s", "Scenario saved -- submit to compute!")

Thanks to ready functions, we will prepare a navigation desktop with a basic composition with the code below:

with tgb.Page() as page:
    tgb.text("# Cardiac Arrest Dashboard")
    tgb.selector(value="{gender_selected}", lov="{gender_lov}",
                 label="Select Gender:", on_change=update_dash)

    with tgb.layout(columns="1 1", gap="20px"):
        tgb.chart(figure="{pie_fig}")
        tgb.chart(figure="{box_fig}")

    tgb.text("### Average Age (Live): {avg_age}")
    tgb.table(data="{filtered_df}", pagination=True)

    tgb.text("---")
    tgb.text("## Scenario Management")
    tgb.scenario_selector("{scenario}")
    tgb.selector(label="Scenario Gender:", lov="{gender_lov}",
                 value="{gender_selected}", on_change=save_scenario)
    tgb.scenario("{scenario}")
    tgb.scenario_dag("{scenario}")
    tgb.text("**Avg Age (Scenario):**")
    tgb.data_node("{scenario.avg_age}")
    tgb.table(data="{filtered_df}", pagination=True)

The above dashboard is elementary, but it will change depending on our choices.

Finally, we will prepare the orchestration process with the following code:

if __name__ == "__main__":
    tp.Orchestrator().run()
    scenario = tp.create_scenario(scenario_cfg)
    scenario.filtered_df.write(df_raw)
    scenario.gender_filter.write("All")
    Gui(page).run(title="Cardiac Arrest Dashboard", dark_mode=True)

After preparing the code, we will launch the navigation desktop using the following command:

The dashboard will automatically appear in your browser. For example, it is a elementary distribution board of cardiac arrest with visualizations and sex selection.

If you scroll down, here is the script. You can try to choose gender and send a scenario to see middle -aged differences.

In this way, you can build a skillful data desktop with just a few components. Discover Taipa documentation To add visualizations and functions suitable for the needs of the dashboard.

# Wrapping

The data is a resource that every company needs, but obtaining information from data is more complex if they are not visualized. In this article, we created an elegant data desktop using Python, Taipa and Google sheets. We showed how to connect with data from Google sheets and utilize the Taipa library to build an interactive navigation desktop.

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.