In this post, we’ll explore a Python code snippet that creates a dynamic sales data query application using Streamlit, Pandas, and PandasAI. This setup allows users to ask pre-defined questions or type their own to analyze sales data.
import os
import streamlit as st
import pandas as pd
from pandasai import Agent
import yaml
# Load API key from config.yaml
with open(“config.yaml”, “r”) as file:
config = yaml.safe_load(file)
os.environ[“PANDASAI_API_KEY”] = config[“PANDASAI_API_KEY”]
# Sample DataFrame
sales_by_country = pd.DataFrame({
“country”: [“United States”, “United Kingdom”, “France”, “Germany”, “Malta”, “Spain”, “United States”, “United Kingdom”,],
“sales”: [5000, 3200, 2900, 4100, 5200, 6300, 5200, 3400],
“year”: [2023, 2023, 2023, 2023, 2023, 2023, 2024, 2024]
})
# Initialize the PandasAI agent
agent = Agent(dfs=sales_by_country)
# Streamlit interface
st.title(“PandasAI Q&A Interface”)
# Display a smaller title for “Mindstorm.gr”
# st.markdown(“<h5 style=’text-align: color: white;’>mindstorm.gr</h5>”, unsafe_allow_html=True)
# Display the DataFrame
st.subheader(“Sales by Country Data / Πωλήσεις ανά Χώρα”)
#st.dataframe(sales_by_country)
st.dataframe(sales_by_country.style.format({“year”: “{:.0f}”}))
# Dropdown menu with predefined questions
predefined_questions = [
“Which are the top 5 countries by sales for year 2023?”,
“Ποιες είναι οι 5 χώρες με τις υψηλότερες πωλήσεις για το έτος 2023;”,
“What is the total sales for year 2023?”,
“Plot a barplot that shows sales for year 2023?”,
“What is the total sales for year 2024?”,
“Ποιες είναι οι συνολικές πωλήσεις για το έτος 2023;”,
“Ποιες είναι οι συνολικές πωλήσεις για το έτος 2024;”,
“What is the sum of sales for United States and United Kingdom for year 2023?”,
“Which country has the highest sales for year 2023?”,
“Ποια είναι η χώρα με τις υψηλότερες πωλήσεις για το έτος 2023;”,
“Which country has the lowest sales for year 2023?”,
“Ποια είναι η χώρα με τις χαμηλότερες πωλήσεις για το έτος 2023;”,
“List countries with sales over 3000 for year 2023.”,
“What is the percentage difference in sales for United States between year 2023 and 2024?”,
“What is the percentage difference in sales for United Kingdom between year 2023 and 2024?”,
“What is the average sales for all countries for year 2023?”,
“What is the average sales for all countries for year 2024?”,
“Ποιος είναι ο μέσος όρος πωλήσεων για όλες τις χώρες το 2024;”
]
# Initialize session state for `selected_question` and `custom_question` if not set
if “selected_question” not in st.session_state:
st.session_state.selected_question = “”
if “custom_question” not in st.session_state:
st.session_state.custom_question = “”
# Define a callback function to clear the custom question when selecting a dropdown option
def on_selectbox_change():
st.session_state.custom_question = “”
# Define a callback function to clear the dropdown selection when typing a custom question
def on_text_input_change():
st.session_state.selected_question = “”
# Dropdown selection with callback to clear `custom_question`
selected_question = st.selectbox(
“Select a predefined question: / Διαλέξτε μια προκαθορισμένη ερώτηση:”,
[“”] + predefined_questions,
key=”selected_question”,
on_change=on_selectbox_change # Callback to clear custom question
)
# Custom question input with callback to clear `selected_question`
custom_question = st.text_input(
“Or type your own question: / Ή πληκτρολογήστε τη δική σας ερώτηση:”,
key=”custom_question”,
on_change=on_text_input_change # Callback to clear selected question
)
# Determine the question to use (either from the dropdown or the text input)
question = st.session_state.custom_question if st.session_state.custom_question else st.session_state.selected_question
# Define a function to calculate average sales for 2023
def calculate_average_sales_2023(df):
# Filter for the year 2023 and calculate the average sales
filtered_df = df[df[“year”] == 2023]
avg_sales = filtered_df[“sales”].mean()
return avg_sales
# Define a function to calculate average sales for 2024
def calculate_average_sales_2024(df):
# Filter for the year 2024 and calculate the average sales
filtered_df = df[df[“year”] == 2024]
avg_sales = filtered_df[“sales”].mean()
return avg_sales
# Check if the question is specifically about average sales for 2023
if question == “What is the average sales for all countries for year 2023?”:
average_sales = calculate_average_sales_2023(sales_by_country)
st.write(f”Average Sales for 2023: {average_sales}”)
elif question == “What is the average sales for all countries for year 2024?”:
average_sales = calculate_average_sales_2024(sales_by_country)
st.write(f”Average Sales for 2024: {average_sales}”)
elif question == “Ποιος είναι ο μέσος όρος πωλήσεων για όλες τις χώρες το 2024;”:
average_sales = calculate_average_sales_2024(sales_by_country)
st.write(f”Average Sales for 2024 / Μέσος όρος πωλήσεων για το 2024: {average_sales}”)
elif question: # For all other questions, use PandasAI
response = agent.chat(question)
st.write(“Response / Απάντηση:”, response)
# Display a smaller title for “Mindstorm.gr”
st.markdown(“<h5 style=’text-align: color: white;’>Credit: Tryfon Papadopoulos | mindstorm.gr</h5>”, unsafe_allow_html=True)
We start by importing the necessary libraries and setting up the API key. The API key is loaded from a config.yaml file and stored in the environment.
The sales_by_country DataFrame simulates sales data by country and year. Each entry represents the sales for a country in a specific year.
The PandasAI Agent uses the DataFrame to answer questions through AI.
The Streamlit app displays the main title and DataFrame. Users can view the data and interact with the app by asking questions.
We define a list of predefined questions and initialize session state to handle dropdown and text input changes dynamically.
Users can select a predefined question or type their own question, with session state managing selections dynamically.
For specific average sales questions, custom functions are used. Other questions are handled by the PandasAI agent.
A footer with credit is added using st.markdown for additional styling and attribution.
This code demonstrates how to create an interactive app to analyze sales data with PandasAI and Streamlit. Users can choose predefined questions or type their own to get insights, with both dynamic AI-based responses and custom calculations for specific queries.
Comments are closed.