Spaces:
Sleeping
Sleeping
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() |