apps/kpi_analysis/wcel_capacity.py

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

from process_kpi.process_wcel_capacity import (
    WcelCapacity,
    load_and_process_wcel_capacity_data,
)
from utils.convert_to_excel import convert_wcel_capacity_dfs

# Streamlit UI

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

with doc_col:
    st.write(
        """This app allows you to analyze the capacity of WCELS in a network. 
        It provides insights into the utilization of BB and CE resources, 
        helping you identify potential capacity issues and plan for upgrades.
        
        The report should be run with a minimum of 3 days of data.
        - Daily Aggregated
        - WCEL level
        - Exported in CSV format.
        """
    )

with image_col:
    st.image("./assets/wcel_capacity.png", width=400)

uploaded_file = st.file_uploader(
    "Upload WCEL capacity report in CSV format", type="csv"
)

param_col1, param_col2, param_col3 = st.columns(3)
param_col4, param_col5, param_col6 = st.columns(3)


if uploaded_file is not None:
    WcelCapacity.final_results = None
    with param_col1:
        num_last_days = st.number_input(
            "Number of days for analysis",
            min_value=3,
            max_value=30,
            value=7,
        )
    with param_col2:
        num_threshold_days = st.number_input(
            "Number of days for threshold",
            min_value=1,
            max_value=30,
            value=2,
        )
    with param_col3:
        availability_threshold = st.number_input(
            "Availability threshold (%)", value=99, min_value=0, max_value=100
        )
    with param_col4:
        iub_frameloss_threshold = st.number_input(
            "IUB frameloss threshold (%)",
            value=100,
            min_value=0,
            max_value=10000000,
        )
    with param_col5:
        hsdpa_congestion_rate_iub_threshold = st.number_input(
            "HSDPA Congestion Rate IUB threshold (%)",
            value=10,
            min_value=0,
            max_value=100,
        )
    with param_col6:
        fails_treshold = st.number_input(
            "Fails threshold (%)", value=10, min_value=0, max_value=10000000
        )

    if st.button("Analyze Data", type="primary"):
        with st.spinner("Processing data..."):
            results = load_and_process_wcel_capacity_data(
                uploaded_file,
                num_last_days,
                num_threshold_days,
                availability_threshold,
                iub_frameloss_threshold,
                hsdpa_congestion_rate_iub_threshold,
                fails_treshold,
            )

        if results is not None:
            wcel_analysis_df = results[0]
            kpi_df = results[1]

            WcelCapacity.final_results = convert_wcel_capacity_dfs(
                [wcel_analysis_df, kpi_df], ["wcel_analysis", "kpi"]
            )
            st.download_button(
                on_click="ignore",
                type="primary",
                label="Download the Analysis Report",
                data=WcelCapacity.final_results,
                file_name="WCEL_Capacity_Report.xlsx",
                mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            )
        st.write(wcel_analysis_df)
        # Add dataframe and Bar chart with "final_comments" distribution
        st.markdown("***")
        st.markdown(":blue[**Final comment distribution**]")
        final_comments_df = (
            wcel_analysis_df.groupby("final_comments")
            .size()
            .reset_index(name="count")
            .sort_values(by="count", ascending=False)
        )
        final_comments_col1, final_comments_col2 = st.columns((1, 3))
        with final_comments_col1:
            st.write(final_comments_df)
        with final_comments_col2:
            fig = px.bar(
                final_comments_df,
                x="final_comments",
                y="count",
                title="Final Comments Distribution",
                text="count",
            )
            fig.update_traces(textposition="outside")
            fig.update_layout(height=600)
            st.plotly_chart(fig)

        # Add dataframe and Pie chart with "operational_comments" distribution
        st.markdown("***")
        st.markdown(":blue[**Operational comment distribution**]")
        operational_comments_df = (
            wcel_analysis_df.groupby("operational_comments")
            .size()
            .reset_index(name="count")
            .sort_values(by="count", ascending=False)
        )
        operational_comments_df["percent"] = (
            operational_comments_df["count"] / operational_comments_df["count"].sum()
        ) * 100
        operational_comments_col1, operational_comments_col2 = st.columns((1, 3))
        with operational_comments_col1:
            st.write(operational_comments_df)
        with operational_comments_col2:
            fig = px.pie(
                operational_comments_df,
                names="operational_comments",
                values="count",
                hover_name="operational_comments",
                hover_data=["count", "percent"],
                title="Operational Comments Distribution",
            )
            fig.update_layout(height=600)
            fig.update_traces(
                texttemplate="<b>%{label}</b><br> %{value}  <b>(%{customdata[1]:.1f}%)</b>",
                textfont_size=15,
                textposition="outside",
            )
            st.plotly_chart(fig)

        # Add dataframe and Bar chart with "operational_comments" distribution per Region
        st.markdown("***")
        st.markdown(":blue[**Operational comment distribution per Region**]")
        operational_comments_df = (
            wcel_analysis_df.groupby(["Region", "operational_comments"])
            .size()
            .reset_index(name="count")
            .sort_values(by="count", ascending=False)
        )
        operational_comments_col1, operational_comments_col2 = st.columns((1, 3))
        with operational_comments_col1:
            st.write(operational_comments_df)
        with operational_comments_col2:
            fig = px.bar(
                operational_comments_df,
                x="Region",
                y="count",
                color="operational_comments",
                title="Operational Comments Distribution per Region",
                text="count",
            )
            fig.update_traces(textposition="outside")
            fig.update_layout(height=600)
            st.plotly_chart(fig)

        # Add dataframe and Pie chart with "fails_comments" distribution
        st.markdown("***")
        st.markdown(":blue[**Fails comment distribution**]")
        fails_comments_df = (
            wcel_analysis_df.groupby("fails_comments")
            .size()
            .reset_index(name="count")
            .sort_values(by="count", ascending=False)
        )

        # replace empty strings with "Cell OK"
        fails_comments_df["fails_comments"] = fails_comments_df[
            "fails_comments"
        ].replace("", "Cell OK")

        fails_comments_df["percent"] = (
            fails_comments_df["count"] / fails_comments_df["count"].sum()
        ) * 100
        fails_comments_col1, fails_comments_col2 = st.columns((1, 3))
        with fails_comments_col1:
            st.write(fails_comments_df)
        with fails_comments_col2:
            fig = px.pie(
                fails_comments_df,
                names="fails_comments",
                values="count",
                hover_name="fails_comments",
                hover_data=["count", "percent"],
                title="Fails Comments Distribution",
            )
            fig.update_layout(height=600)
            fig.update_traces(
                texttemplate="<b>%{label}</b><br> %{value}  <b>(%{customdata[1]:.1f}%)</b>",
                textfont_size=15,
                textposition="outside",
            )
            st.plotly_chart(fig)

        # Add dataframe and Bar chart with "fails_comments" distribution per Region
        st.markdown("***")
        st.markdown(":blue[**Fails comment distribution per Region**]")
        fails_comments_df = (
            wcel_analysis_df.groupby(["Region", "fails_comments"])
            .size()
            .reset_index(name="count")
            .sort_values(by="count", ascending=False)
        )

        # replace empty strings with "Cell OK"
        fails_comments_df["fails_comments"] = fails_comments_df[
            "fails_comments"
        ].replace("", "Cell OK")

        fails_comments_col1, fails_comments_col2 = st.columns((1, 3))
        with fails_comments_col1:
            st.write(fails_comments_df)
        with fails_comments_col2:
            fig = px.bar(
                fails_comments_df,
                x="Region",
                y="count",
                color="fails_comments",
                title="Fails Comments Distribution per Region",
                text="count",
            )
            fig.update_traces(textposition="outside", textfont_size=15)
            fig.update_layout(height=600)
            st.plotly_chart(fig)

        # create a map plot with scatter_map with code ,Longitude,Latitude,fails_comments
        st.markdown("***")
        st.markdown(":blue[**Fails comments distribution**]")
        fails_comments_map_df = wcel_analysis_df[
            ["code", "Longitude", "Latitude", "fails_comments"]
        ].dropna(subset=["code", "Longitude", "Latitude", "fails_comments"])

        # replace empty strings with "Cell OK"
        fails_comments_map_df["fails_comments"] = fails_comments_map_df[
            "fails_comments"
        ].replace("", "Cell OK")

        # add size column equalt to 20
        fails_comments_map_df["size"] = 20

        fig = px.scatter_map(
            fails_comments_map_df,
            lat="Latitude",
            lon="Longitude",
            color="fails_comments",
            size="size",
            zoom=10,
            height=600,
            title="Fails comments distribution",
            hover_data={
                "code": True,
                "fails_comments": True,
            },
            hover_name="code",
        )
        fig.update_layout(mapbox_style="open-street-map")
        st.plotly_chart(fig, use_container_width=True)

        # create a map plot with scatter_map with code ,Longitude,Latitude,operational_comments
        operational_comments_map_df = wcel_analysis_df[
            ["code", "Longitude", "Latitude", "operational_comments"]
        ].dropna(subset=["code", "Longitude", "Latitude", "operational_comments"])

        # replace empty strings with "Cell OK"
        operational_comments_map_df["operational_comments"] = (
            operational_comments_map_df["operational_comments"].replace("", "Cell OK")
        )

        # add size column equalt to 20
        operational_comments_map_df["size"] = 20

        fig = px.scatter_map(
            operational_comments_map_df,
            lat="Latitude",
            lon="Longitude",
            color="operational_comments",
            size="size",
            zoom=10,
            height=600,
            title="Operational comments distribution",
            hover_data={
                "code": True,
                "operational_comments": True,
            },
            hover_name="code",
        )
        fig.update_layout(mapbox_style="open-street-map")
        st.plotly_chart(fig, use_container_width=True)