Akshayram1's picture
Update app.py
9f9b802 verified
raw
history blame
2.99 kB
import streamlit as st
import pandas as pd
import matplotlib.pyplot as plt
def process_data(df):
# Clean data and handle date parsing
df = df[df['Project Category'].notna()]
# Convert date strings to datetime
df['Start Date'] = pd.to_datetime(
df['Date'].str.split(' to ').str[0],
format='%d/%b/%y',
errors='coerce'
)
# Filter valid dates and assign weeks
df = df.dropna(subset=['Start Date'])
df['Week'] = df['Start Date'].apply(
lambda x: 1 if x <= pd.Timestamp('2025-01-05') else 2
)
# Consolidate billable categories
df['Category'] = df['Project Category'].apply(
lambda x: 'Billable' if 'Billable' in x else x
)
# Aggregate data
utilization = df.groupby(['Week', 'Category'])['Logged'].sum().unstack(fill_value=0)
# Select relevant categories and calculate percentages
categories = ['Billable', 'Non-Billable', 'Leaves']
utilization = utilization.reindex(categories, axis=1, fill_value=0)
total_hours = utilization.sum(axis=1)
utilization_percent = utilization.div(total_hours, axis=0) * 100
return utilization_percent
def create_utilization_chart(week_data, week_number):
fig, ax = plt.subplots(figsize=(6, 6))
labels = week_data.index[week_data > 0]
sizes = week_data[week_data > 0]
wedges, texts, autotexts = ax.pie(
sizes,
labels=labels,
autopct='%1.1f%%',
colors=['#4CAF50', '#FFC107', '#9E9E9E'],
startangle=90
)
plt.setp(autotexts, size=10, weight="bold", color='white')
ax.set_title(f'Week {week_number} Utilization', pad=20)
return fig
def main():
st.title('QA Team Utilization Dashboard')
uploaded_file = st.file_uploader("Upload Tempo Timesheet", type=['xls', 'xlsx'])
if uploaded_file:
try:
df = pd.read_excel(uploaded_file, sheet_name='Report')
utilization_percent = process_data(df)
# Page 4 Visualization
st.header("Bi-Weekly Utilization Report")
col1, col2 = st.columns(2)
with col1:
if 1 in utilization_percent.index:
week1 = utilization_percent.loc[1]
st.pyplot(create_utilization_chart(week1, 1))
else:
st.warning("No data for Week 1")
with col2:
if 2 in utilization_percent.index:
week2 = utilization_percent.loc[2]
st.pyplot(create_utilization_chart(week2, 2))
else:
st.warning("No data for Week 2")
# Show raw data for verification
st.subheader("Processed Data Preview")
st.dataframe(utilization_percent)
except Exception as e:
st.error(f"Error processing file: {str(e)}")
if __name__ == "__main__":
main()