Spaces:
Sleeping
Sleeping
| import streamlit as st | |
| import pandas as pd | |
| import matplotlib.pyplot as plt | |
| def process_data(df): | |
| # Clean and prepare data | |
| df = df[['Project Category', 'Logged']].copy() | |
| df = df.dropna(subset=['Project Category']) | |
| # Consolidate categories | |
| df['Main Category'] = df['Project Category'].apply( | |
| lambda x: 'Billable' if 'Billable' in str(x) else str(x).strip() | |
| ) | |
| # Group by main categories | |
| main_categories = df.groupby('Main Category')['Logged'].sum().reset_index() | |
| total_hours = main_categories['Logged'].sum() | |
| main_categories['Percentage'] = (main_categories['Logged'] / total_hours * 100).round(1) | |
| # Prepare non-billable breakdown | |
| non_billable = df[df['Main Category'] == 'Non-Billable'] | |
| non_billable_breakdown = non_billable.groupby('Project Category')['Logged'].sum().reset_index() | |
| return main_categories, non_billable_breakdown | |
| def create_pie_chart(data): | |
| fig, ax = plt.subplots(figsize=(6, 6)) | |
| wedges, texts, autotexts = ax.pie( | |
| data['Logged'], | |
| labels=data['Main Category'], | |
| autopct='%1.1f%%', | |
| colors=['#4CAF50', '#FFC107', '#9E9E9E'], | |
| startangle=90 | |
| ) | |
| plt.setp(autotexts, size=10, weight="bold", color='white') | |
| ax.set_title('Overall Utilization Distribution', pad=20) | |
| return fig | |
| def create_bar_chart(data): | |
| fig, ax = plt.subplots(figsize=(10, 5)) | |
| data.plot(kind='bar', x='Project Category', y='Logged', ax=ax, legend=False) | |
| ax.set_title('Non-Billable Time Breakdown') | |
| ax.set_ylabel('Hours') | |
| ax.set_xlabel('') | |
| plt.xticks(rotation=45, ha='right') | |
| plt.tight_layout() | |
| return fig | |
| def main(): | |
| st.title('QA Team Utilization Dashboard') | |
| uploaded_file = st.file_uploader("Upload Timesheet Excel File", type=['xls', 'xlsx']) | |
| if uploaded_file: | |
| try: | |
| df = pd.read_excel(uploaded_file, sheet_name='Report') | |
| main_cats, non_billable = process_data(df) | |
| # Main utilization section | |
| st.header("Overall Utilization") | |
| col1, col2 = st.columns([2, 1]) | |
| with col1: | |
| st.pyplot(create_pie_chart(main_cats)) | |
| with col2: | |
| st.dataframe( | |
| main_cats[['Main Category', 'Logged', 'Percentage']], | |
| hide_index=True, | |
| column_config={ | |
| 'Main Category': 'Category', | |
| 'Logged': st.column_config.NumberColumn('Hours', format="%.2f"), | |
| 'Percentage': st.column_config.NumberColumn(format="%.1f%%") | |
| } | |
| ) | |
| # Non-billable breakdown | |
| st.header("Non-Billable Detailed Breakdown") | |
| st.pyplot(create_bar_chart(non_billable)) | |
| # Raw data preview | |
| st.subheader("Raw Data Preview") | |
| st.dataframe(df.head(10), hide_index=True) | |
| except Exception as e: | |
| st.error(f"Error processing file: {str(e)}") | |
| if __name__ == "__main__": | |
| main() |