process_kpi/process_gsm_capacity.py

import numpy as np
import pandas as pd

from queries.process_gsm import combined_gsm_database
from utils.check_sheet_exist import execute_checks_sheets_exist
from utils.convert_to_excel import convert_dfs, save_dataframe
from utils.kpi_analysis_utils import (
    GsmAnalysis,
    GsmCapacity,
    analyze_sdcch_call_blocking,
    analyze_tch_abis_fails,
    analyze_tch_call_blocking,
    cell_availability_analysis,
    combine_comments,
    create_daily_date,
    create_dfs_per_kpi,
    create_hourly_date,
    kpi_naming_cleaning,
)
from utils.utils_functions import calculate_distances

GSM_ANALYSIS_COLUMNS = [
    "ID_BTS",
    "site_name",
    "name",
    "BSC",
    "BCF",
    "BTS",
    "code",
    "Region",
    "adminState",
    "frequencyBandInUse",
    "cellId",
    "band",
    "site_config_band",
    "trxRfPower",
    "BCCH",
    "Longitude",
    "Latitude",
    "TRX_TCH",
    "MAL_TCH",
    "amrSegLoadDepTchRateLower",
    "amrSegLoadDepTchRateUpper",
    "btsSpLoadDepTchRateLower",
    "btsSpLoadDepTchRateUpper",
    "amrWbFrCodecModeSet",
    "dedicatedGPRScapacity",
    "defaultGPRScapacity",
    "number_trx_per_cell",
    "number_trx_per_bcf",
    "number_tch_per_cell",
    "number_sd_per_cell",
    "number_bcch_per_cell",
    "number_ccch_per_cell",
    "number_cbc_per_cell",
    "number_total_channels_per_cell",
    "number_signals_per_cell",
    "hf_rate_coef",
    "GPRS",
    "TCH Actual HR%",
    "Offered Traffic BH",
    "Max_Traffic BH",
    "Avg_Traffic BH",
    "TCH UTILIZATION (@Max Traffic)",
    "Tch utilization comments",
    "ErlabngB_value",
    "Target FR CHs",
    "Target HR CHs",
    "Target TCHs",
    "Target TRXs",
    "Number of required TRXs",
    "max_tch_call_blocking_bh",
    "avg_tch_call_blocking_bh",
    "number_of_days_with_tch_blocking_exceeded_bh",
    "tch_call_blocking_bh_comment",
    "max_sdcch_real_blocking_bh",
    "avg_sdcch_real_blocking_bh",
    "number_of_days_with_sdcch_blocking_exceeded_bh",
    "sdcch_real_blocking_bh_comment",
    "Average_cell_availability_bh",
    "number_of_days_exceeding_availability_threshold_bh",
    "availability_comment_bh",
    "max_tch_abis_fail_bh",
    "avg_tch_abis_fail_bh",
    "number_of_days_with_tch_abis_fail_exceeded_bh",
    "tch_abis_fail_bh_comment",
    "Average_cell_availability_daily",
    "number_of_days_exceeding_availability_threshold_daily",
    "availability_comment_daily",
    "max_tch_abis_fail_daily",
    "avg_tch_abis_fail_daily",
    "number_of_days_with_tch_abis_fail_exceeded_daily",
    "tch_abis_fail_daily_comment",
    "BH Congestion status",
    "operational_comment",
    "Final comment",
    "Final comment summary",
]

OPERATIONAL_NEIGHBOURS_COLUMNS = [
    "ID_BTS",
    "name",
    "operational_comment",
    "BH Congestion status",
    "Longitude",
    "Latitude",
]

GSM_COLUMNS = [
    "ID_BTS",
    "site_name",
    "name",
    "BSC",
    "BCF",
    "BTS",
    "code",
    "Region",
    "adminState",
    "frequencyBandInUse",
    "amrSegLoadDepTchRateLower",
    "amrSegLoadDepTchRateUpper",
    "btsSpLoadDepTchRateLower",
    "btsSpLoadDepTchRateUpper",
    "amrWbFrCodecModeSet",
    "dedicatedGPRScapacity",
    "defaultGPRScapacity",
    "cellId",
    "band",
    "site_config_band",
    "trxRfPower",
    "BCCH",
    "number_trx_per_cell",
    "number_trx_per_bcf",
    "TRX_TCH",
    "MAL_TCH",
    "Longitude",
    "Latitude",
]

TRX_COLUMNS = [
    "ID_BTS",
    "number_tch_per_cell",
    "number_sd_per_cell",
    "number_bcch_per_cell",
    "number_ccch_per_cell",
    "number_cbc_per_cell",
    "number_total_channels_per_cell",
    "number_signals_per_cell",
]

KPI_COLUMNS = [
    "date",
    "BTS_name",
    "TCH_availability_ratio",
    "2G_Carried_Traffic",
    "TCH_call_blocking",
    "TCH_ABIS_FAIL_CALL_c001084",
    "SDCCH_real_blocking",
]
BH_COLUMNS_FOR_CAPACITY = [
    "Max_Traffic BH",
    "Avg_Traffic BH",
    "max_tch_call_blocking_bh",
    "avg_tch_call_blocking_bh",
    "number_of_days_with_tch_blocking_exceeded_bh",
    "tch_call_blocking_bh_comment",
    "max_sdcch_real_blocking_bh",
    "avg_sdcch_real_blocking_bh",
    "number_of_days_with_sdcch_blocking_exceeded_bh",
    "sdcch_real_blocking_bh_comment",
    "Average_cell_availability_bh",
    "number_of_days_exceeding_availability_threshold_bh",
    "availability_comment_bh",
    "max_tch_abis_fail_bh",
    "avg_tch_abis_fail_bh",
    "number_of_days_with_tch_abis_fail_exceeded_bh",
    "tch_abis_fail_bh_comment",
]

DAILY_COLUMNS_FOR_CAPACITY = [
    "Average_cell_availability_daily",
    "number_of_days_exceeding_availability_threshold_daily",
    "availability_comment_daily",
    "max_tch_abis_fail_daily",
    "avg_tch_abis_fail_daily",
    "number_of_days_with_tch_abis_fail_exceeded_daily",
    "tch_abis_fail_daily_comment",
]


def bh_traffic_analysis(
    df: pd.DataFrame,
    number_of_kpi_days: int,
) -> pd.DataFrame:

    result_df = df.copy()
    last_days_df: pd.DataFrame = result_df.iloc[:, -number_of_kpi_days:]
    # last_days_df = last_days_df.fillna(0)

    result_df["Avg_Traffic BH"] = last_days_df.mean(axis=1).round(2)
    result_df["Max_Traffic BH"] = last_days_df.max(axis=1)
    return result_df


def bh_dfs_per_kpi(
    df: pd.DataFrame,
    number_of_kpi_days: int = 7,
    tch_blocking_threshold: int = 0.50,
    sdcch_blocking_threshold: int = 0.50,
    number_of_threshold_days: int = 3,
    tch_abis_fails_threshold: int = 10,
    availability_threshold: int = 95,
) -> pd.DataFrame:
    """
    Create pivoted DataFrames for each KPI and perform analysis.

    Args:
        df: DataFrame containing KPI data
        number_of_kpi_days: Number of days to analyze
        threshold: Utilization threshold percentage for flagging
        number_of_threshold_days: Minimum days above threshold to flag for upgrade

    Returns:
        DataFrame with combined analysis results
    """
    pivoted_kpi_dfs = {}

    pivoted_kpi_dfs = create_dfs_per_kpi(
        df=df,
        pivot_date_column="date",
        pivot_name_column="BTS_name",
        kpi_columns_from=2,
    )

    tch_call_blocking_df: pd.DataFrame = pivoted_kpi_dfs["TCH_call_blocking"]
    sdcch_real_blocking_df: pd.DataFrame = pivoted_kpi_dfs["SDCCH_real_blocking"]
    Carried_Traffic_df: pd.DataFrame = pivoted_kpi_dfs["2G_Carried_Traffic"]
    tch_availability_ratio_df: pd.DataFrame = pivoted_kpi_dfs["TCH_availability_ratio"]
    tch_abis_fails_df: pd.DataFrame = pivoted_kpi_dfs["TCH_ABIS_FAIL_CALL_c001084"]

    # ANALISYS

    tch_call_blocking_df = analyze_tch_call_blocking(
        df=tch_call_blocking_df,
        number_of_kpi_days=number_of_kpi_days,
        number_of_threshold_days=number_of_threshold_days,
        tch_blocking_threshold=tch_blocking_threshold,
        analysis_type="BH",
    )

    sdcch_real_blocking_df = analyze_sdcch_call_blocking(
        df=sdcch_real_blocking_df,
        number_of_kpi_days=number_of_kpi_days,
        sdcch_blocking_threshold=sdcch_blocking_threshold,
        number_of_threshold_days=number_of_threshold_days,
        analysis_type="BH",
    )

    Carried_Traffic_df = bh_traffic_analysis(
        df=Carried_Traffic_df,
        number_of_kpi_days=number_of_kpi_days,
    )

    tch_abis_fails_df = analyze_tch_abis_fails(
        df=tch_abis_fails_df,
        number_of_kpi_days=number_of_kpi_days,
        tch_abis_fails_threshold=tch_abis_fails_threshold,
        number_of_threshold_days=number_of_threshold_days,
        analysis_type="BH",
    )
    tch_availability_ratio_df = cell_availability_analysis(
        df=tch_availability_ratio_df,
        days=number_of_kpi_days,
        availability_threshold=availability_threshold,
        analysis_type="BH",
    )

    bh_kpi_df = pd.concat(
        [
            Carried_Traffic_df,
            tch_call_blocking_df,
            sdcch_real_blocking_df,
            tch_availability_ratio_df,
            tch_abis_fails_df,
        ],
        axis=1,
    )
    return bh_kpi_df


def analyse_bh_data(
    bh_report_path: str,
    number_of_kpi_days: int,
    tch_blocking_threshold: int,
    sdcch_blocking_threshold: int,
    number_of_threshold_days: int,
    tch_abis_fails_threshold: int,
    availability_threshold: int,
) -> pd.DataFrame:
    df = pd.read_csv(bh_report_path, delimiter=";")
    df = kpi_naming_cleaning(df)
    df = create_hourly_date(df)
    df = df[KPI_COLUMNS]
    df = bh_dfs_per_kpi(
        df=df,
        number_of_kpi_days=number_of_kpi_days,
        tch_blocking_threshold=tch_blocking_threshold,
        sdcch_blocking_threshold=sdcch_blocking_threshold,
        number_of_threshold_days=number_of_threshold_days,
        tch_abis_fails_threshold=tch_abis_fails_threshold,
        availability_threshold=availability_threshold,
    )

    bh_df_for_capacity = df.copy()
    bh_df_for_capacity = bh_df_for_capacity[BH_COLUMNS_FOR_CAPACITY]
    bh_df_for_capacity = bh_df_for_capacity.reset_index()

    # If columns have multiple levels (MultiIndex), flatten them
    if isinstance(bh_df_for_capacity.columns, pd.MultiIndex):
        bh_df_for_capacity.columns = [
            "_".join([str(el) for el in col if el])
            for col in bh_df_for_capacity.columns.values
        ]
    # bh_df_for_capacity = bh_df_for_capacity.reset_index()

    # rename Bts_name to name
    bh_df_for_capacity = bh_df_for_capacity.rename(columns={"BTS_name": "name"})

    return [bh_df_for_capacity, df]


def daily_dfs_per_kpi(
    df: pd.DataFrame,
    number_of_kpi_days: int = 7,
    availability_threshold: int = 95,
    number_of_threshold_days: int = 3,
    tch_abis_fails_threshold: int = 10,
    sdcch_blocking_threshold: int = 0.5,
    tch_blocking_threshold: int = 0.5,
) -> pd.DataFrame:
    """
    Create pivoted DataFrames for each KPI and perform analysis.

    Args:
        df: DataFrame containing KPI data
        number_of_kpi_days: Number of days to analyze
        threshold: Utilization threshold percentage for flagging
        number_of_threshold_days: Minimum days above threshold to flag for upgrade

    Returns:
        DataFrame with combined analysis results
    """
    pivoted_kpi_dfs = {}

    pivoted_kpi_dfs = create_dfs_per_kpi(
        df=df,
        pivot_date_column="date",
        pivot_name_column="BTS_name",
        kpi_columns_from=2,
    )

    tch_call_blocking_df: pd.DataFrame = pivoted_kpi_dfs["TCH_call_blocking"]
    sdcch_real_blocking_df: pd.DataFrame = pivoted_kpi_dfs["SDCCH_real_blocking"]
    Carried_Traffic_df: pd.DataFrame = pivoted_kpi_dfs["2G_Carried_Traffic"]
    tch_availability_ratio_df: pd.DataFrame = pivoted_kpi_dfs["TCH_availability_ratio"]
    tch_abis_fails_df: pd.DataFrame = pivoted_kpi_dfs["TCH_ABIS_FAIL_CALL_c001084"]

    tch_availability_ratio_df = cell_availability_analysis(
        df=tch_availability_ratio_df,
        days=number_of_kpi_days,
        availability_threshold=availability_threshold,
    )
    sdcch_real_blocking_df = analyze_sdcch_call_blocking(
        df=sdcch_real_blocking_df,
        number_of_kpi_days=number_of_kpi_days,
        sdcch_blocking_threshold=sdcch_blocking_threshold,
        number_of_threshold_days=number_of_threshold_days,
        analysis_type="Daily",
    )
    tch_call_blocking_df = analyze_tch_call_blocking(
        df=tch_call_blocking_df,
        number_of_kpi_days=number_of_kpi_days,
        number_of_threshold_days=number_of_threshold_days,
        tch_blocking_threshold=tch_blocking_threshold,
        analysis_type="Daily",
    )
    tch_abis_fails_df = analyze_tch_abis_fails(
        df=tch_abis_fails_df,
        number_of_kpi_days=number_of_kpi_days,
        tch_abis_fails_threshold=tch_abis_fails_threshold,
        number_of_threshold_days=number_of_threshold_days,
        analysis_type="Daily",
    )

    daily_kpi_df = pd.concat(
        [
            tch_availability_ratio_df,
            Carried_Traffic_df,
            tch_call_blocking_df,
            sdcch_real_blocking_df,
            tch_abis_fails_df,
        ],
        axis=1,
    )

    daily_kpi_df = combine_comments(
        daily_kpi_df,
        "availability_comment_daily",
        "tch_abis_fail_daily_comment",
        "sdcch_real_blocking_daily_comment",
        new_column="sdcch_comments",
    )

    daily_kpi_df = combine_comments(
        daily_kpi_df,
        "availability_comment_daily",
        "tch_abis_fail_daily_comment",
        "tch_call_blocking_daily_comment",
        new_column="tch_comments",
    )
    return daily_kpi_df


def analyse_daily_data(
    daily_report_path: str,
    number_of_kpi_days: int,
    tch_abis_fails_threshold: int,
    availability_threshold: int,
    number_of_threshold_days: int,
    sdcch_blocking_threshold: int,
    tch_blocking_threshold: int,
) -> pd.DataFrame:
    df = pd.read_csv(daily_report_path, delimiter=";")
    df = kpi_naming_cleaning(df)
    df = create_daily_date(df)
    df = df[KPI_COLUMNS]
    df = daily_dfs_per_kpi(
        df=df,
        number_of_kpi_days=number_of_kpi_days,
        availability_threshold=availability_threshold,
        tch_abis_fails_threshold=tch_abis_fails_threshold,
        number_of_threshold_days=number_of_threshold_days,
        sdcch_blocking_threshold=sdcch_blocking_threshold,
        tch_blocking_threshold=tch_blocking_threshold,
    )
    daily_df_for_capacity = df.copy()
    daily_df_for_capacity = daily_df_for_capacity[DAILY_COLUMNS_FOR_CAPACITY]
    daily_df_for_capacity = daily_df_for_capacity.reset_index()

    if isinstance(daily_df_for_capacity.columns, pd.MultiIndex):
        daily_df_for_capacity.columns = [
            "_".join([str(el) for el in col if el])
            for col in daily_df_for_capacity.columns.values
        ]
    # Rename "BTS_name" to "name"
    daily_df_for_capacity = daily_df_for_capacity.rename(columns={"BTS_name": "name"})

    return daily_df_for_capacity, df


def get_gsm_databases(dump_path: str) -> pd.DataFrame:

    dfs = combined_gsm_database(dump_path)
    bts_df: pd.DataFrame = dfs[0]
    trx_df: pd.DataFrame = dfs[2]

    # Clean GSM df
    bts_df = bts_df[GSM_COLUMNS]
    trx_df = trx_df[TRX_COLUMNS]

    # Remove duplicate in TRX df
    trx_df = trx_df.drop_duplicates(subset=["ID_BTS"])

    gsm_df = pd.merge(bts_df, trx_df, on="ID_BTS", how="left")

    # add hf_rate_coef
    gsm_df["hf_rate_coef"] = gsm_df["amrSegLoadDepTchRateLower"].map(
        GsmAnalysis.hf_rate_coef
    )
    # Add "GPRS" colomn equal to (dedicatedGPRScapacity * number_tch_per_cell)/100
    gsm_df["GPRS"] = (
        gsm_df["dedicatedGPRScapacity"] * gsm_df["number_tch_per_cell"]
    ) / 100

    # "TCH Actual HR%" equal to "number of TCH" multiplyed by "Coef HF rate"
    gsm_df["TCH Actual HR%"] = gsm_df["number_tch_per_cell"] * gsm_df["hf_rate_coef"]

    # Remove empty rows
    gsm_df = gsm_df.dropna(subset=["TCH Actual HR%"])

    # Get "Offered Traffic BH" by mapping approximate "TCH Actual HR%" to 2G analysis_utility "erlangB" dict
    gsm_df["Offered Traffic BH"] = gsm_df["TCH Actual HR%"].apply(
        lambda x: GsmAnalysis.erlangB_table.get(int(x), 0)
    )

    return gsm_df


def get_operational_neighbours(distance: int) -> pd.DataFrame:

    operational_df: pd.DataFrame = GsmCapacity.operational_neighbours_df
    operational_df = operational_df[
        ["ID_BTS", "name", "operational_comment", "Longitude", "Latitude"]
    ]
    # keep row only if column "operational_comment" is not "Operational is OK"
    operational_df = operational_df[
        operational_df["operational_comment"] != "Operational is OK"
    ]
    operational_df = operational_df[
        operational_df[["Latitude", "Longitude"]].notna().all(axis=1)
    ]

    # Rename all columns in operational_df by adding "Dataset2_" prefix
    operational_df = operational_df.add_prefix("Dataset2_")

    congested_df: pd.DataFrame = GsmCapacity.operational_neighbours_df
    congested_df = congested_df[
        ["ID_BTS", "name", "BH Congestion status", "Longitude", "Latitude"]
    ]

    # Remove rows where "BH Congestion status" is empty or NaN
    congested_df = congested_df[
        congested_df["BH Congestion status"].notna()
        & congested_df["BH Congestion status"].astype(str).str.len().astype(bool)
    ]
    # Remove rows where "BH Congestion status" is "nan, nan"
    congested_df = congested_df[congested_df["BH Congestion status"] != "nan, nan"]

    # Remove rows where Latitude and Longitude are empty
    congested_df = congested_df[
        congested_df[["Latitude", "Longitude"]].notna().all(axis=1)
    ]

    # Rename all columns in congested_df by adding "Dataset1_" prefix
    congested_df = congested_df.add_prefix("Dataset1_")

    distances_dfs = calculate_distances(
        congested_df,
        operational_df,
        "Dataset1_ID_BTS",
        "Dataset1_Latitude",
        "Dataset1_Longitude",
        "Dataset2_ID_BTS",
        "Dataset2_Latitude",
        "Dataset2_Longitude",
    )
    distances_df = distances_dfs[0]
    df1 = distances_df[distances_df["Distance_km"] <= distance]

    # Rename all columns in df1
    df1 = df1.rename(
        columns={
            "Dataset1_ID_BTS": "Source_ID_BTS",
            "Dataset1_name": "Source_name",
            "Dataset1_BH Congestion status": "Source_BH Congestion status",
            "Dataset1_Longitude": "Source_Longitude",
            "Dataset1_Latitude": "Source_Latitude",
            "Dataset2_ID_BTS_Dataset2": "Neighbour_ID_BTS",
            "Dataset2_name_Dataset2": "Neighbour_name",
            "Dataset2_operational_comment_Dataset2": "Neighbour_operational_comment",
            "Dataset2_Longitude_Dataset2": "Neighbour_Longitude",
            "Dataset2_Latitude_Dataset2": "Neighbour_Latitude",
        }
    )

    # Remove rows if Source_name = Neighbour_name
    df1 = df1[df1["Source_name"] != df1["Neighbour_name"]]

    # Reset index
    df1 = df1.reset_index(drop=True)
    return df1


def analyze_gsm_data(
    dump_path: str,
    daily_report_path: str,
    bh_report_path: str,
    number_of_kpi_days: int,
    number_of_threshold_days: int,
    availability_threshold: int,
    tch_abis_fails_threshold: int,
    sdcch_blocking_threshold: float,
    tch_blocking_threshold: float,
    max_traffic_threshold: int,
    operational_neighbours_distance: int,
):
    GsmCapacity.operational_neighbours_df = None

    daily_kpi_dfs: pd.DataFrame = analyse_daily_data(
        daily_report_path=daily_report_path,
        number_of_kpi_days=number_of_kpi_days,
        availability_threshold=availability_threshold,
        tch_abis_fails_threshold=tch_abis_fails_threshold,
        number_of_threshold_days=number_of_threshold_days,
        sdcch_blocking_threshold=sdcch_blocking_threshold,
        tch_blocking_threshold=tch_blocking_threshold,
    )

    gsm_database_df: pd.DataFrame = get_gsm_databases(dump_path)

    bh_kpi_dfs = analyse_bh_data(
        bh_report_path=bh_report_path,
        number_of_kpi_days=number_of_kpi_days,
        tch_blocking_threshold=tch_blocking_threshold,
        sdcch_blocking_threshold=sdcch_blocking_threshold,
        number_of_threshold_days=number_of_threshold_days,
        tch_abis_fails_threshold=tch_abis_fails_threshold,
        availability_threshold=availability_threshold,
    )

    bh_kpi_df = bh_kpi_dfs[0]
    bh_kpi_full_df = bh_kpi_dfs[1]

    daily_kpi_df = daily_kpi_dfs[0]
    daily_kpi_full_df = daily_kpi_dfs[1]

    gsm_analysis_df = gsm_database_df.merge(bh_kpi_df, on="name", how="left")
    gsm_analysis_df = gsm_analysis_df.merge(daily_kpi_df, on="name", how="left")

    # "TCH UTILIZATION (@Max Traffic)" equal to "(Max_Trafic" divided by "Offered Traffic BH)*100"
    gsm_analysis_df["TCH UTILIZATION (@Max Traffic)"] = (
        gsm_analysis_df["Max_Traffic BH"] / gsm_analysis_df["Offered Traffic BH"]
    ) * 100

    # Add column "Tch utilization comments" : if "TCH UTILIZATION (@Max Traffic)" exceeded it's threshold then "Tch utilization exceeded threshold else None
    gsm_analysis_df["Tch utilization comments"] = np.where(
        gsm_analysis_df["TCH UTILIZATION (@Max Traffic)"] > max_traffic_threshold,
        "Tch utilization exceeded threshold",
        None,
    )
    # Add "BH Congestion status" : concatenate "Tch utilization comments" + "tch_call_blocking_bh_comment" + "sdcch_real_blocking_bh_comment"
    gsm_analysis_df = combine_comments(
        gsm_analysis_df,
        "Tch utilization comments",
        "tch_call_blocking_bh_comment",
        "sdcch_real_blocking_bh_comment",
        new_column="BH Congestion status",
    )

    # Add "ERLANGB value" =MAX TRAFFIC/(1-(MAX TCH call blocking/200))
    gsm_analysis_df["ErlabngB_value"] = gsm_analysis_df["Max_Traffic BH"] / (
        1 - (gsm_analysis_df["max_tch_call_blocking_bh"] / 200)
    )

    # - Get "Target FR CHs" by mapping "ERLANG value" to 2G analysis_utility "erlangB" dict
    gsm_analysis_df["Target FR CHs"] = gsm_analysis_df["ErlabngB_value"].apply(
        lambda x: GsmAnalysis.erlangB_table.get(int(x) if pd.notnull(x) else 0, 0)
    )

    # "Target HR CHs" equal to  "Target FR CHs" * 2
    gsm_analysis_df["Target HR CHs"] = gsm_analysis_df["Target FR CHs"] * 2

    # - Target TCHs equal to Target HR CHs + Signal + GPRS + SDCCH
    gsm_analysis_df["Target TCHs"] = (
        gsm_analysis_df["Target HR CHs"]
        + gsm_analysis_df["number_signals_per_cell"]
        + gsm_analysis_df["GPRS"]
        + gsm_analysis_df["number_sd_per_cell"]
    )
    # "Target TRXs" equal to roundup(Target TCHs/8)
    gsm_analysis_df["Target TRXs"] = np.ceil(
        gsm_analysis_df["Target TCHs"] / 8
    )  # df["Target TCHs"] / 8

    # "Number of required TRXs" equal to difference between "Target TRXs" and "number_trx_per_cell"
    gsm_analysis_df["Number of required TRXs"] = (
        gsm_analysis_df["Target TRXs"] - gsm_analysis_df["number_trx_per_cell"]
    )

    # if "availability_comment_daily" equal to "Down Site" then "Down Site"
    # if "availability_comment_daily" is not "Availability OK" and  "tch_abis_fail_daily_comment" equal to "tch abis fail exceeded threshold" then "Availability and TX issues"
    # if "availability_comment_daily" is not "Availability OK" and  "tch_abis_fail_daily_comment" is empty then "Availability issues"
    # if "availability_comment_daily" is "Availability OK" and  "tch_abis_fail_daily_comment" equal to "tch abis fail exceeded threshold" then "TX issues"
    # Else "Operational is OK"
    gsm_analysis_df["operational_comment"] = np.select(
        [
            gsm_analysis_df["availability_comment_daily"] == "Down Site",  # 1
            (gsm_analysis_df["availability_comment_daily"] != "Availability OK")
            & (
                gsm_analysis_df["tch_abis_fail_daily_comment"]
                == "tch abis fail exceeded threshold"
            ),  # 2
            (gsm_analysis_df["availability_comment_daily"] != "Availability OK")
            & pd.isna(gsm_analysis_df["tch_abis_fail_daily_comment"]),  # 3
            (gsm_analysis_df["availability_comment_daily"] == "Availability OK")
            & (
                gsm_analysis_df["tch_abis_fail_daily_comment"]
                == "tch abis fail exceeded threshold"
            ),  # 4
        ],
        [
            "Down Site",  # 1
            "Availability and TX issues",  # 2
            "Availability issues",  # 3
            "TX issues",  # 4
        ],
        default="Operational is OK",
    )

    # Add "Final comment" with "BH Congestion status" + "operational_comment"
    gsm_analysis_df = combine_comments(
        gsm_analysis_df,
        "BH Congestion status",
        "operational_comment",
        new_column="Final comment",
    )
    # Map the final comment using final_comment_mapping
    gsm_analysis_df["Final comment summary"] = gsm_analysis_df["Final comment"].map(
        GsmCapacity.final_comment_mapping
    )
    gsm_analysis_df = gsm_analysis_df[GSM_ANALYSIS_COLUMNS]

    GsmCapacity.operational_neighbours_df = gsm_analysis_df[
        OPERATIONAL_NEIGHBOURS_COLUMNS
    ]
    distance_df = get_operational_neighbours(operational_neighbours_distance)

    return [gsm_analysis_df, bh_kpi_full_df, daily_kpi_full_df, distance_df]
    # return [gsm_analysis_df, bh_kpi_full_df, daily_kpi_full_df]