apps/kpi_analysis/gsm_capacity.py

import pandas as pd
import plotly.express as px
import streamlit as st

from process_kpi.process_gsm_capacity import analyze_gsm_data
from utils.convert_to_excel import (  # Import convert_dfs from the appropriate module
    convert_gsm_dfs,
    save_dataframe,
)
from utils.kpi_analysis_utils import GsmCapacity

st.title(" 📊 GSM Capacity Analysis")
doc_col, image_col = st.columns(2)

with doc_col:
    st.write(
        """
        The report should be run with a minimum of 3 days of data.
        - Dump file required
        - Daily Cell level KPI report in CSV format
        - BH Cell level KPI report in CSV format
        """
    )

with image_col:
    st.image("./assets/gsm_capacity.png", width=250)

file1, file2, file3 = st.columns(3)

with file1:
    uploaded_dump = st.file_uploader("Upload Dump file in xlsb format", type="xlsb")
with file2:
    uploaded_daily_report = st.file_uploader(
        "Upload Daily Report in CSV format", type="csv"
    )
with file3:
    uploaded_bh_report = st.file_uploader(
        "Upload Busy Hour Report in CSV format", type="csv"
    )


col1, col2 = st.columns(2)

threshold_col1, threshold_col2 = st.columns(2)
threshold_col3, threshold_col4 = st.columns(2)
max_traffic_threshold_col1, operational_neighbours_distance_col1 = st.columns(2)


if (
    uploaded_dump is not None
    and uploaded_daily_report is not None
    and uploaded_bh_report is not None
):
    # WbtsCapacity.final_results = None
    with col1:
        number_of_kpi_days = st.number_input(
            "Number of days for analysis",
            min_value=3,
            max_value=30,
            value=7,
        )
    with col2:
        number_of_threshold_days = st.number_input(
            "Number of days for threshold",
            min_value=1,
            max_value=30,
            value=3,
        )

    with threshold_col1:
        availability_threshold = st.number_input(
            "Availability Threshold", min_value=1, max_value=100, value=95
        )
    with threshold_col2:
        tch_abis_fails_threshold = st.number_input(
            "TCH ABIS Fails Threshold", min_value=0, value=10
        )
    with threshold_col3:
        sdcch_blocking_threshold = st.number_input(
            "SDDCH Blocking Threshold", min_value=0.1, value=0.5
        )
    with threshold_col4:
        tch_blocking_threshold = st.number_input(
            "TCH Blocking Threshold", min_value=0.1, value=0.5
        )
    with max_traffic_threshold_col1:
        max_traffic_threshold = st.number_input(
            "TCH Utilization Max Traffic Threshold", min_value=0, value=90
        )
    with operational_neighbours_distance_col1:
        operational_neighbours_distance = st.number_input(
            "Operational Neighbours Distance", min_value=0, value=1
        )

    if st.button("Analyze Data", type="primary"):
        dfs = analyze_gsm_data(
            dump_path=uploaded_dump,
            daily_report_path=uploaded_daily_report,
            bh_report_path=uploaded_bh_report,
            number_of_kpi_days=number_of_kpi_days,
            number_of_threshold_days=number_of_threshold_days,
            availability_threshold=availability_threshold,
            tch_abis_fails_threshold=tch_abis_fails_threshold,
            sdcch_blocking_threshold=sdcch_blocking_threshold,
            tch_blocking_threshold=tch_blocking_threshold,
            max_traffic_threshold=max_traffic_threshold,
            operational_neighbours_distance=operational_neighbours_distance,
        )

        if dfs is not None:
            gsm_analysis_df: pd.DataFrame = dfs[0]
            bh_kpi_df: pd.DataFrame = dfs[1]
            daily_kpi_df: pd.DataFrame = dfs[2]
            distance_df: pd.DataFrame = dfs[3]
            GsmCapacity.final_results = convert_gsm_dfs(
                [gsm_analysis_df, distance_df, bh_kpi_df, daily_kpi_df],
                ["GSM_Analysis", "Distance", "BH_KPI_Analysis", "Daily_KPI_Analysis"],
            )

            # GsmCapacity.final_results = convert_gsm_dfs(
            #     [gsm_analysis_df, bh_kpi_df, daily_kpi_df],
            #     ["GSM_Analysis", "BH_KPI_Analysis", "Daily_KPI_Analysis"],
            # )

            if GsmCapacity.final_results is not None:
                st.download_button(
                    on_click="ignore",
                    type="primary",
                    label="Download the Analysis Report",
                    data=GsmCapacity.final_results,
                    file_name="GSM_Analysis_Report.xlsx",
                    mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                )

        st.write(daily_kpi_df)

        # Add dataframe and ploty bar chart with "Final comment" distribution in gsm_analysis_df in 2 columns
        final_comments_df = (
            gsm_analysis_df.groupby("Final comment").size().reset_index(name="count")
        )
        fig = px.bar(
            final_comments_df,
            x="Final comment",
            y="count",
            title="Final comment distribution",
        )
        fig.update_layout(height=1000)
        fig.update_traces(texttemplate="%{value}", textposition="outside")
        st.plotly_chart(fig, use_container_width=True)
        st.write(final_comments_df)

        # Add dataframe and ploty bar chart with "Final comment summary" distribution in gsm_analysis_df in 2 columns
        final_comments_summary_df = (
            gsm_analysis_df.groupby("Final comment summary")
            .size()
            .reset_index(name="count")
        )
        # Add Pie chart with "Final comment summary" distribution in gsm_analysis_df in 2 columns
        st.markdown("***")
        st.markdown(":blue[**Final comment summary distribution**]")
        final_comments_summary_col1, final_comments_summary_col2 = st.columns((1, 3))
        with final_comments_summary_col1:
            st.write(final_comments_summary_df)
        with final_comments_summary_col2:
            fig = px.pie(
                final_comments_summary_df,
                names="Final comment summary",
                values="count",
                hover_name="Final comment summary",
                hover_data=["count"],
                title="GSM Analysis comment distribution",
            )
            fig.update_layout(height=800)
            fig.update_traces(
                texttemplate="%{label}: %{value}",
                textfont_size=15,
                textposition="outside",
            )
            st.plotly_chart(fig, use_container_width=True)

        # Add dataframe and ploty bar chart with "BH Congestion status" distribution in gsm_analysis_df in 2 columns
        st.markdown("***")
        st.markdown(":blue[**BH Congestion status distribution**]")
        bh_congestion_status_df = (
            gsm_analysis_df.groupby("BH Congestion status")
            .size()
            .reset_index(name="count")
        )
        # Replace "" cell in "BH Congestion status" with "No Congestion"
        bh_congestion_status_df["BH Congestion status"] = bh_congestion_status_df[
            "BH Congestion status"
        ].replace("", "No Congestion")
        # Replace "nan, nan" cell in "BH Congestion status" with "No KPI"
        bh_congestion_status_df["BH Congestion status"] = bh_congestion_status_df[
            "BH Congestion status"
        ].replace("nan, nan", "No KPI")

        bh_congestion_status_col1, bh_congestion_status_col2 = st.columns((2, 1))
        with bh_congestion_status_col2:
            st.write(bh_congestion_status_df)
        with bh_congestion_status_col1:
            fig = px.pie(
                bh_congestion_status_df,
                names="BH Congestion status",
                values="count",
                hover_name="BH Congestion status",
                hover_data=["count"],
                title="BH Congestion status distribution",
            )
            fig.update_layout(height=800)
            fig.update_traces(
                texttemplate="%{label}: %{value}",
                textfont_size=15,
                textposition="outside",
            )
            st.plotly_chart(fig, use_container_width=True)

        # Add dataframe and ploty pie chart with "operational_comment" distribution in gsm_analysis_df in 2 columns
        st.markdown("***")
        st.markdown(":blue[**Operational comments distribution**]")
        operational_comments_df = (
            gsm_analysis_df.groupby("operational_comment")
            .size()
            .reset_index(name="count")
        )
        operational_comments_col1, operational_comments_col2 = st.columns((1, 2))
        with operational_comments_col1:
            st.write(operational_comments_df)
        with operational_comments_col2:
            fig = px.pie(
                operational_comments_df,
                names="operational_comment",
                values="count",
                hover_name="operational_comment",
                hover_data=["count"],
                title="Operational comments distribution",
            )
            fig.update_layout(height=600)
            fig.update_traces(
                texttemplate="%{label}: %{value}",
                textfont_size=15,
                textposition="outside",
            )
            st.plotly_chart(fig, use_container_width=True)

        # create a map plot with scatter_map with gsm_analysis_df and max_tch_call_blocking_bh
        st.markdown("***")
        st.markdown(":blue[**Max TCH Call Blocking BH distribution**]")

        # Select and clean the necessary columns
        map_df = gsm_analysis_df[
            ["code", "max_tch_call_blocking_bh", "Latitude", "Longitude"]
        ].dropna(subset=["code", "max_tch_call_blocking_bh", "Latitude", "Longitude"])

        # Group by code and max max_tch_call_blocking_bh, keep first occurrence of other columns
        map_df = (
            map_df.groupby("code")
            .agg(
                {
                    "max_tch_call_blocking_bh": "max",
                    "Latitude": "first",
                    "Longitude": "first",
                }
            )
            .reset_index()
        )
        # save_dataframe(map_df, "max_tch_call_blocking_bh_map")
        # Create a color column based on the threshold
        map_df["color"] = map_df["max_tch_call_blocking_bh"].apply(
            lambda x: (
                "Above Threshold" if x > tch_blocking_threshold else "Below Threshold"
            )
        )

        # Apply minimum size to make small values more visible
        min_bubble_size = 5  # Minimum size for visibility
        max_bubble_size = 30  # Maximum size for scaling

        # Scale the size to make small values more visible while maintaining relative sizes
        size_scale = max_bubble_size / map_df["max_tch_call_blocking_bh"].max()
        map_df["scaled_size"] = map_df["max_tch_call_blocking_bh"].apply(
            lambda x: max(x * size_scale, min_bubble_size)
        )

        fig = px.scatter_map(
            map_df,
            lat="Latitude",
            lon="Longitude",
            color="color",
            color_discrete_map={"Above Threshold": "red", "Below Threshold": "green"},
            size="scaled_size",
            size_max=max_bubble_size,
            hover_data={
                "code": True,  # Show code in hover data
                "max_tch_call_blocking_bh": ":.2f",
                "scaled_size": False,
            },
            hover_name="code",  # This will show as the title of the hover box
            zoom=10,
            height=600,
            title="Max TCH Call Blocking BH distribution",
        )

        # Update traces to show code on bubbles and customize hover
        fig.update_traces(
            text=map_df["code"],  # Show code on the bubble
            textposition="middle center",
            textfont=dict(size=18, color="black"),
            # hovertemplate="<b>%{hovertext}</b><br>"
            # + "Blocking: %{customdata[1]:.2f}%<extra></extra>",
        )

        # Adjust layout for better text visibility
        fig.update_layout(
            mapbox_style="open-street-map",
            showlegend=True,
            margin=dict(l=10, r=10, t=40, b=10),
        )

        st.plotly_chart(fig, use_container_width=True)