Spaces:
Sleeping
Sleeping
File size: 6,692 Bytes
1de23bb da94df9 cb9c4c3 1de23bb da94df9 22ac20e da94df9 1de23bb da94df9 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
import streamlit as st
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
# Sample Data Format
st.subheader('Sample Data Format:')
st.write("""
The data should be in the following format with the listed columns:
| Customer Name | Date | City | Country | State | Product Name | Product Attribute 1 | Product Attribute 2 | Product Attribute 3 | Product Attribute 4 | Net Sales Value | Margin Amount | Cost |
|----------------|------------|--------------|-------------|-------------|-----------------|---------------------|---------------------|---------------------|---------------------|------------------|---------------|-------|
| John Doe | 2024-01-01 | New York | USA | NY | Product A | Attribute 1A | Attribute 2A | Attribute 3A | Attribute 4A | 1000 | 300 | 700 |
| Jane Smith | 2024-01-02 | Los Angeles | USA | CA | Product B | Attribute 1B | Attribute 2B | Attribute 3B | Attribute 4B | 1500 | 400 | 1100 |
| Bob Johnson | 2024-02-15 | Chicago | USA | IL | Product A | Attribute 1A | Attribute 2A | Attribute 3A | Attribute 4A | 1200 | 350 | 850 |
| Alice Williams | 2024-03-10 | Miami | USA | FL | Product C | Attribute 1C | Attribute 2C | Attribute 3C | Attribute 4C | 2000 | 500 | 1500 |
| Charlie Brown | 2024-04-05 | Houston | USA | TX | Product B | Attribute 1B | Attribute 2B | Attribute 3B | Attribute 4B | 1800 | 450 | 1350 |
""")
# File upload functionality
st.sidebar.header("Upload Data")
uploaded_file = st.sidebar.file_uploader("Choose a CSV file", type=["csv"])
# Check if file is uploaded
if uploaded_file is not None:
# Load the data
df = pd.read_csv(uploaded_file)
# Convert Date to datetime format if it's in string format
if 'Date' in df.columns:
df['Date'] = pd.to_datetime(df['Date'])
# Add 'Year' and 'Month' columns for easy filtering and analysis
df['Year'] = df['Date'].dt.year
df['Month'] = df['Date'].dt.month
# Sidebar Filters
st.sidebar.header("Filter Options")
# Text Input for Customer and Product
customer_query = st.sidebar.text_input('Enter Customer Name (partial or full):').strip().lower()
product_query = st.sidebar.text_input('Enter Product Name (partial or full):').strip().lower()
city_query = st.sidebar.text_input('Enter City (partial or full):').strip().lower()
state_query = st.sidebar.text_input('Enter State (partial or full):').strip().lower()
country_query = st.sidebar.text_input('Enter Country (partial or full):').strip().lower()
# Date Range Selection
start_date = st.sidebar.date_input('Start Date:', df['Date'].min())
end_date = st.sidebar.date_input('End Date:', df['Date'].max())
# Filter Data by Date Range
filtered_df = df[
(df['Date'] >= pd.to_datetime(start_date)) &
(df['Date'] <= pd.to_datetime(end_date))
]
# Filter Data by Customer Name
if customer_query:
filtered_df = filtered_df[filtered_df['Customer Name'].str.contains(customer_query, case=False, na=False)]
# Filter Data by Product Name
if product_query:
filtered_df = filtered_df[filtered_df['Product Name'].str.contains(product_query, case=False, na=False)]
# Filter Data by City
if city_query:
filtered_df = filtered_df[filtered_df['City'].str.contains(city_query, case=False, na=False)]
# Filter Data by State
if state_query:
filtered_df = filtered_df[filtered_df['State'].str.contains(state_query, case=False, na=False)]
# Filter Data by Country
if country_query:
filtered_df = filtered_df[filtered_df['Country'].str.contains(country_query, case=False, na=False)]
# Add 'Net Sales Value - Cost' as a new column
filtered_df['Net Sales Value - Cost'] = filtered_df['Net Sales Value'] - filtered_df['Cost']
# Display Filtered Data
st.write(f"Filtered Data: {len(filtered_df)} records found.")
st.dataframe(filtered_df)
if not filtered_df.empty:
# KPI Metrics
st.subheader("Key Financial Metrics")
# Profit for the Year (Calculated as Net Sales Value - Cost)
profit_for_the_year = filtered_df['Net Sales Value'] - filtered_df['Cost']
st.metric("Profit for the Year", f"${profit_for_the_year.sum():,.2f}")
# Gross Margin (Net Sales Value - Cost)
gross_margin = filtered_df['Net Sales Value'] - filtered_df['Cost']
st.metric("Gross Margin", f"${gross_margin.sum():,.2f}")
# Total Sales
total_sales = filtered_df['Net Sales Value'].sum()
st.metric("Total Sales", f"${total_sales:,.2f}")
# Visualization 1: Profit and Loss Overview (Table)
st.subheader("Profit and Loss Overview")
pnl_data = filtered_df[['Customer Name', 'Product Name', 'Net Sales Value', 'Cost', 'Net Sales Value - Cost']]
pnl_data.columns = ['Customer', 'Product', 'Sales', 'Cost', 'Profit']
st.dataframe(pnl_data)
# Visualization 2: Matrix View (like Power BI Matrix)
st.subheader("Matrix View of Financial Data")
matrix_data = filtered_df.pivot_table(
values='Net Sales Value',
index=['Year', 'Customer Name'],
columns=['Product Name'],
aggfunc='sum',
fill_value=0
)
st.dataframe(matrix_data)
# Visualization 3: Sales Trend over Time
st.subheader("Sales Trend over Time")
sales_trend = filtered_df.groupby(['Year', 'Month'])['Net Sales Value'].sum().reset_index()
fig = px.line(sales_trend, x='Month', y='Net Sales Value', color='Year', title="Sales Trend over Time")
st.plotly_chart(fig)
# Visualization 4: Profit Margin Visualization (Bar Chart)
st.subheader("Profit Margin per Product")
profit_margin_data = filtered_df.groupby('Product Name').apply(
lambda x: (x['Net Sales Value'] - x['Cost']).sum() / x['Net Sales Value'].sum()
).reset_index(name="Profit Margin")
fig = px.bar(profit_margin_data, x='Product Name', y='Profit Margin', title="Profit Margin per Product")
st.plotly_chart(fig)
else:
st.write("No data available for the selected filters.")
else:
st.write("Please upload a CSV file to get started.")
|