Spaces:
Sleeping
Sleeping
| import streamlit as st | |
| import pandas as pd | |
| import altair as alt | |
| st.set_page_config(page_title="Shipment Monitoring Dashboard", layout="wide") | |
| st.title("Shipment Monitoring Dashboard") | |
| tabs = st.tabs(["Main Dashboard", "Last Mile Summary"]) | |
| with tabs[0]: | |
| uploaded_file = st.file_uploader("Upload your Excel file", type=["xlsx"], key="main_upload") | |
| if uploaded_file: | |
| df = pd.read_excel(uploaded_file, sheet_name=0) | |
| # Rename columns | |
| df.columns = ['MAWB', 'MAWB Serial Number', 'Injection Gateway', 'Clearance Port', 'Latest Status', | |
| 'Cartons', 'Packages', 'Weights', 'Not online packages count', 'ETD', 'ETA', 'ATD', 'ATA', | |
| 'Last Mile Carrier', 'Readynotice has been notified', 'Vehicle has been arranged', | |
| 'Pick-upcompletedfrom', 'WH-IN Scan', 'Cargo Ready', 'Ship Out', 'Note', | |
| 'Exception Note', 'Exception Note Description'] | |
| # Add status column | |
| def determine_status(row): | |
| if pd.isna(row['WH-IN Scan']): | |
| return "Pending" | |
| elif pd.isna(row['Cargo Ready']): | |
| return "WH-IN" | |
| elif pd.isna(row['Ship Out']): | |
| return "Cargo Ready" | |
| else: | |
| return "Ship Out" | |
| df['Shipment Status'] = df.apply(determine_status, axis=1) | |
| df['ETA'] = pd.to_datetime(df['ETA']) | |
| df['ATA'] = pd.to_datetime(df['ATA']) | |
| # Fallback date: ATA if available, otherwise ETA | |
| df['Final Arrival Date'] = df['ATA'].fillna(df['ETA']) | |
| df['Final Arrival Date'] = pd.to_datetime(df['Final Arrival Date']).dt.date | |
| # Filter for Injection Gateway | |
| injection_gateways = df['Injection Gateway'].dropna().unique().tolist() | |
| selected_gateway = st.selectbox("Filter by Injection Gateway", options=["All"] + injection_gateways) | |
| if selected_gateway != "All": | |
| df = df[df['Injection Gateway'] == selected_gateway] | |
| # Filter by ETA date range | |
| st.subheader("Filter by ETA Date Range") | |
| min_date, max_date = df['ETA'].min(), df['ETA'].max() | |
| start_date, end_date = st.date_input("Select ETA date range:", [min_date, max_date], min_value=min_date, max_value=max_date) | |
| df = df[(df['ETA'] >= pd.to_datetime(start_date)) & (df['ETA'] <= pd.to_datetime(end_date))] | |
| # Shipment status distribution | |
| st.subheader("Shipment Status Distribution") | |
| status_order = ["Pending", "WH-IN", "Cargo Ready", "Ship Out"] | |
| status_counts = df['Shipment Status'].value_counts().reindex(status_order).reset_index() | |
| status_counts.columns = ['Shipment Status', 'Count'] | |
| status_counts = status_counts.dropna() | |
| chart = alt.Chart(status_counts).mark_bar().encode( | |
| x=alt.X('Shipment Status', sort=status_order), | |
| y='Count', | |
| color='Shipment Status', | |
| tooltip=['Shipment Status', 'Count'] | |
| ).properties(height=300) | |
| st.altair_chart(chart, use_container_width=True) | |
| # Volume by final arrival date (ATA preferred, fallback to ETA) | |
| st.subheader("Shipment Volume by ETA") | |
| volume_by_arrival = df.groupby('Final Arrival Date').agg({ | |
| 'MAWB': 'count', | |
| 'Cartons': 'sum', | |
| 'Packages': 'sum', | |
| 'Weights': 'sum' | |
| }).reset_index().rename(columns={'MAWB': 'Shipment Count'}) | |
| st.dataframe(volume_by_arrival, use_container_width=True) | |
| line_chart = alt.Chart(volume_by_arrival).mark_line(point=True).encode( | |
| x='Final Arrival Date:T', | |
| y='Cartons:Q', | |
| tooltip=['Final Arrival Date:T', 'Shipment Count:Q', 'Cartons:Q', 'Packages:Q', 'Weights:Q'] | |
| ).properties(height=400) | |
| st.altair_chart(line_chart, use_container_width=True) | |
| # Status filter before detail view | |
| st.subheader("Shipment Detail View by ETA") | |
| status_options = ["All"] + status_order | |
| selected_status = st.radio("Select a Shipment Status to filter the details below:", options=status_options, index=0) | |
| if selected_status == "All": | |
| filtered_df = df.copy() | |
| else: | |
| filtered_df = df[df['Shipment Status'] == selected_status] | |
| sorted_df = filtered_df[['MAWB', 'ETA', 'ATA', 'Shipment Status', 'Cartons', 'Last Mile Carrier']].sort_values(by='ETA') | |
| st.dataframe(sorted_df, use_container_width=True) | |
| # Add download as Excel button | |
| import io | |
| excel_buffer = io.BytesIO() | |
| with pd.ExcelWriter(excel_buffer, engine='xlsxwriter') as writer: | |
| sorted_df.to_excel(writer, index=False, sheet_name='Shipment Details') | |
| excel_data = excel_buffer.getvalue() | |
| st.download_button( | |
| label="📥 Download Shipment Details as Excel", | |
| data=excel_data, | |
| file_name="shipment_details.xlsx", | |
| mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" | |
| ) | |
| with tabs[1]: | |
| st.header("Upload CTN File for Last Mile Summary") | |
| ctn_files = st.file_uploader("Upload one or more Excel files with Last Mile Service", | |
| type=["xlsx"], key="ctn_upload", accept_multiple_files=True) | |
| if ctn_files: | |
| df_list = [] | |
| for file in ctn_files: | |
| df_part = pd.read_excel(file, sheet_name=0) | |
| df_list.append(df_part) | |
| df_ctn = pd.concat(df_list, ignore_index=True) | |
| # Columns of interest | |
| carton_col = "No. Large box" | |
| last_mile_col = "Last mile service" | |
| # ✅ Count unique cartons by last mile service | |
| st.subheader("Unique Cartons by Last Mile Service") | |
| if carton_col in df_ctn.columns and last_mile_col in df_ctn.columns: | |
| unique_summary = ( | |
| df_ctn.groupby(last_mile_col)[carton_col] | |
| .nunique() | |
| .reset_index(name="Unique Carton Count") | |
| ) | |
| st.dataframe(unique_summary, use_container_width=True) | |
| else: | |
| st.warning(f"Columns '{carton_col}' or '{last_mile_col}' not found in uploaded file(s).") | |