<jupyter_start><jupyter_text>ST PC TERMINAL  Import helsinki map<jupyter_code># map path
map_pc_geo_path = "map/map_pc_geo"
map_pc_geo = gpd.read_file(map_pc_geo_path)
# Helsinki center pc map
filter_map_adm_helsinki = ["Helsinki", "Vantaa", "Espoo"]
map_pc_helsinki = map_pc_geo.query("NAME_4 in @filter_map_adm_helsinki").reset_index(
    drop=True
)<jupyter_output><empty_output><jupyter_text>Import Data<jupyter_code># path and import
data_st_grid_ter_interpolate_path = 'data/st/03b_data_st_ter_grid_pred.csv'
data_st_grid_ter_interpolate_raw = pd.read_csv(data_st_grid_ter_interpolate_path, na_values=[0])
# convert to gdf
data_st_grid_ter_interpolate_geo = gpd.GeoDataFrame(data_st_grid_ter_interpolate_raw,
                                                    geometry=gpd.points_from_xy(
                                                        data_st_grid_ter_interpolate_raw.Longitude,
                                                        data_st_grid_ter_interpolate_raw.Latitude),
                                                    crs=4326
                                                    )

data_st_grid_ter_interpolate_geo.info()<jupyter_output><class 'geopandas.geodataframe.GeoDataFrame'>
RangeIndex: 22221 entries, 0 to 22220
Data columns (total 40 columns):
 #   Column                 Non-Null Count  Dtype   
---  ------                 --------------  -----   
 0   Longitude              22221 non-null  float64 
 1   Latitude               22221 non-null  float64 
 2   operator_name          22221 non-null  object  
 3   sample_origin_4g_ios   10815 non-null  float64 
 4   sample_count_4g_ios    10815 non-null  float64 
 5   device_count_4g_ios    10815 non-null  float64 
 6   sample_origin_4g_ad    12676 non-null  float64 
 7   sample_count_4g_ad     12676 non-null  float64 
 8   device_count_4g_ad     12676 non-null  float64 
 9   sample_origin_ip12_5g  3667 non-null   float64 
 10  sample_count_ip12_5g   3667 non-null   float64 
 11  device_count_ip12_5g   3667 non-null   float64 
 12  sample_origin_ip12_fb  1636 non-null   float64 
 13  sample_count_ip12_fb   1636 non-null   float64 
 14  device_count_ip12_fb   1636 no[...]<jupyter_text>Aggregate Data<jupyter_code># initial parameters
filter_st_pc_ter_regex = "device_count"
filter_st_pc_ter_operators = ["DNA", "Elisa", "Telia"]
col_st_pc_ter_operator = "operator_name"
col_st_pc_ter_group = ["NAME_1", "NAME_2", "NAME_3", "NAME_4", "posti_alue"]
col_st_pc_ter_sum = [
    "sample_origin_4g_ios",
    "sample_count_4g_ios",
    "device_count_4g_ios",
    "sample_origin_4g_ad",
    "sample_count_4g_ad",
    "device_count_4g_ad",
    "sample_origin_ip12_5g",
    "sample_count_ip12_5g",
    "device_count_ip12_5g",
    "sample_origin_ip12_fb",
    "sample_count_ip12_fb",
    "device_count_ip12_fb",
    "sample_origin_ip12_lk",
    "sample_count_ip12_lk",
    "device_count_ip12_lk",
    "sample_origin_ad_5g",
    "sample_count_ad_5g",
    "device_count_ad_5g",
    "sample_origin_ad_fb",
    "sample_count_ad_fb",
    "device_count_ad_fb",
    "sample_origin_ad_lk",
    "sample_count_ad_lk",
    "device_count_ad_lk",
]
col_st_pc_ter_mean = [
    "avg_dl_4g_ios",
    "avg_dl_4g_ad",
    "avg_dl_ip12_5g",
    "avg_dl_ip12_fb",
    "avg_dl_ip12_lk",
    "avg_dl_ad_5g",
    "avg_dl_ad_fb",
    "avg_dl_ad_lk",
    "avg_dl_4g_ios_pred",
    "avg_dl_4g_ad_pred",
    "avg_dl_ip12_5g_pred",
    "avg_dl_ad_5g_pred",
]
col_st_pc_ter_kpi = [
    "LTE_Average_Speed",
    "NR_Samples",
    "NR_Average_Speed",
    "NR_Speed_Gain",
    "NR_Terminal_Rate",
    "NR_Register_Rate",
    "NR_Function_Rate",
    "NR_Fallback_Rate",
    "I12_Samples",
    "I12_Average_Speed",
    "I12_Speed_Gain",
    "I12_Terminal_Rate",
    "I12_Register_Rate",
    "I12_Function_Rate",
    "I12_Fallback_Rate",
]
col_st_pc_ter_function = [
    cal_4g_average_speed,
    cal_nr_total_samples,
    cal_nr_average_speed,
    cal_nr_speed_gain_ratio,
    cal_nr_terminal_ratio,
    cal_nr_register_ratio,
    cal_nr_function_ratio,
    cal_nr_fallback_ratio,
    cal_ip12_total_samples,
    cal_ip12_average_speed,
    cal_ip12_speed_gain_ratio,
    cal_ip12_terminal_ratio,
    cal_ip12_register_ratio,
    cal_ip12_function_ratio,
    cal_ip12_fallback_ratio,
]
# group calculation
data_st_pc_ter_group = (
    gpd.sjoin(data_st_grid_ter_interpolate_geo, map_pc_helsinki)
    .query("{} in @filter_st_pc_ter_operators".format(col_st_pc_ter_operator))
    .drop(["index_right", "geometry"], axis=1)
    .groupby(col_st_pc_ter_group + [col_st_pc_ter_operator])
    .agg(
        {
            **{kpi: np.sum for kpi in col_st_pc_ter_sum},
            **{kpi: np.mean for kpi in col_st_pc_ter_mean},
        }
    )
    .reset_index()
    .replace(0, np.nan)
)<jupyter_output><empty_output><jupyter_text>Calculate KPI<jupyter_code># calculate market share kpi
for kpi, function in zip(col_st_pc_ter_kpi, col_st_pc_ter_function):
    data_st_pc_ter_group = data_st_pc_ter_group.assign(
        **{kpi: lambda x: function(x, filter_st_pc_ter_regex)}
    )

data_st_pc_ter_group.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 483 entries, 0 to 482
Data columns (total 57 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   NAME_1                 483 non-null    object 
 1   NAME_2                 483 non-null    object 
 2   NAME_3                 483 non-null    object 
 3   NAME_4                 483 non-null    object 
 4   posti_alue             483 non-null    object 
 5   operator_name          483 non-null    object 
 6   sample_origin_4g_ios   481 non-null    float64
 7   sample_count_4g_ios    481 non-null    float64
 8   device_count_4g_ios    481 non-null    float64
 9   sample_origin_4g_ad    483 non-null    float64
 10  sample_count_4g_ad     483 non-null    float64
 11  device_count_4g_ad     483 non-null    float64
 12  sample_origin_ip12_5g  415 non-null    float64
 13  sample_count_ip12_5g   415 non-null    float64
 14  device_count_ip12_5g   415 non-null    float64
 15  sample[...]<jupyter_text>Convert to gdf<jupyter_code># convert to geo data by joining adm map
col_st_pc_ter_merge_left = ["NAME_1", "NAME_2", "NAME_3", "NAME_4", "posti_alue"]
col_st_pc_ter_merge_right = ["NAME_1", "NAME_2", "NAME_3", "NAME_4", "posti_alue"]

data_st_pc_ter_geo = map_pc_helsinki.merge(
    data_st_pc_ter_group,
    left_on=col_st_pc_ter_merge_left,
    right_on=col_st_pc_ter_merge_right,
)

data_st_pc_ter_geo.info()<jupyter_output><class 'geopandas.geodataframe.GeoDataFrame'>
Int64Index: 483 entries, 0 to 482
Data columns (total 58 columns):
 #   Column                 Non-Null Count  Dtype   
---  ------                 --------------  -----   
 0   posti_alue             483 non-null    object  
 1   NAME_1                 483 non-null    object  
 2   NAME_2                 483 non-null    object  
 3   NAME_3                 483 non-null    object  
 4   NAME_4                 483 non-null    object  
 5   geometry               483 non-null    geometry
 6   operator_name          483 non-null    object  
 7   sample_origin_4g_ios   481 non-null    float64 
 8   sample_count_4g_ios    481 non-null    float64 
 9   device_count_4g_ios    481 non-null    float64 
 10  sample_origin_4g_ad    483 non-null    float64 
 11  sample_count_4g_ad     483 non-null    float64 
 12  device_count_4g_ad     483 non-null    float64 
 13  sample_origin_ip12_5g  415 non-null    float64 
 14  sample_count_ip12_5g   415 non-nul[...]<jupyter_text>Spatial lag<jupyter_code># spatial weight matrix
col_st_pc_ter_tns = [
    "NR_Terminal_Rate",
    "I12_Terminal_Rate",
    "NR_Register_Rate",
    "NR_Average_Speed",
    "NR_Function_Rate",
]
# spatial weight
model_st_pc_ter_geo_weight = weights.Queen.from_dataframe(data_st_pc_ter_geo)
model_st_pc_ter_geo_weight.transform = "R"
# spatial lag kpi
for col in col_st_pc_ter_tns:
    data_st_pc_ter_geo[col + "_lag"] = weights.lag_spatial(
        model_st_pc_ter_geo_weight, data_st_pc_ter_geo[col]
    )<jupyter_output><empty_output><jupyter_text>Calculate TNS<jupyter_code># kpi dict 1
data_st_pc_ter_tns1_dict = {
    "TNS_T_lag": ["NR_Terminal_Rate_lag", "I12_Terminal_Rate_lag"],
    "TNS_N_lag": ["NR_Register_Rate_lag", "NR_Average_Speed_lag"],
    "TNS_S_lag": ["NR_Function_Rate_lag"],
}
# kpi threshold 1
print(data_st_pc_ter_geo[set(sum(data_st_pc_ter_tns1_dict.values(), []))].quantile(q=[0, .25, .5, .75, 1]))

# auto
# data_st_pc_ter_tns1_thd = data_st_pc_ter_geo[set(sum(data_st_pc_ter_tns1_dict.values(), []))].quantile(q=[.25])

# mannual
data_st_pc_ter_tns1_thd = pd.DataFrame({
    'NR_Terminal_Rate_lag': [25],
    'I12_Terminal_Rate_lag': [25],
    'NR_Register_Rate_lag': [50],
    'NR_Average_Speed_lag': [250],
    'NR_Function_Rate_lag': [75],
})
# TNS kpi cat 1
data_st_pc_ter_tns1_thd.index = ["Ready"]
data_st_pc_ter_tns1_thd_default = "Not Ready"
# TNS categorization 1
data_st_pc_ter_tns = add_kpi_cat(
    kpi_df=data_st_pc_ter_geo,
    kpi_dict=data_st_pc_ter_tns1_dict,
    kpi_thd=data_st_pc_ter_tns1_thd,
    cat_default=data_st_pc_ter_tns1_thd_default,
)
# kpi dict 2
data_st_pc_ter_tns2_dict = {
    "TNS_TNS": ["TNS_T_lag", "TNS_N_lag", "TNS_S_lag"],
}
# kpi threshold 2
data_st_pc_ter_tns2_thd = pd.DataFrame(
    {
        "TNS_T_lag": ["Ready", "Ready", "Ready", "*"],
        "TNS_N_lag": ["Ready", "Ready", "*", "Ready"],
        "TNS_S_lag": ["Ready", "*", "Ready", "Ready"],
    }
)
# TNS kpi cat 2
data_st_pc_ter_tns2_thd.index = [
    "01 TNS Ready",
    "02 TN Ready",
    "03 TS Ready",
    "04 NS Ready",
]
data_st_pc_ter_tns2_thd_default = "05 Not Ready"
# TNS categorization 2
data_st_pc_ter_tns = add_kpi_cat(
    kpi_df=data_st_pc_ter_tns,
    kpi_dict=data_st_pc_ter_tns2_dict,
    kpi_thd=data_st_pc_ter_tns2_thd,
    cat_default=data_st_pc_ter_tns2_thd_default,
)
print(data_st_pc_ter_tns['TNS_TNS'].value_counts())<jupyter_output>01 TNS Ready    173
03 TS Ready     163
05 Not Ready    146
02 TN Ready       1
Name: TNS_TNS, dtype: int64<jupyter_text>Visualize geo data<jupyter_code># initial parameter
vis_output_path = r"output\ST_PostCode_Terminal_"
vis_dataset = data_st_pc_ter_tns
vis_n_col = np.r_[: vis_dataset.shape[1]]
vis_col_group = ["NAME_1", "NAME_2", "NAME_3", "NAME_4", "posti_alue"]
vis_col_operator = "operator_name"
vis_col_nr_sample = "I12_Samples"
vis_filter_sample = 3
vis_filter_operator = ["DNA", "Elisa", "Telia"]
vis_col_list = [
    "NR_Samples",
    "NR_Average_Speed",
    "NR_Terminal_Rate",
    "NR_Register_Rate",
    "NR_Function_Rate",
    "NR_Fallback_Rate",
    "I12_Samples",
    "I12_Average_Speed",
    "I12_Terminal_Rate",
    "I12_Register_Rate",
    "I12_Function_Rate",
    "I12_Fallback_Rate",
]
vis_color_dict = {"DNA": "deeppink", "Elisa": "darkblue", "Telia": "blueviolet"}
vis_layout = (2, 2)
# use for loop to create multiple layers
for vis_col in vis_col_list:
    vis_palette_n = palette_n_dict(
        vis_dataset[vis_dataset[vis_col_nr_sample] > vis_filter_sample],
        vis_col,
        5,
        0,
        "RdYlGn",
    )
    layer_list = []
    
    # kpi layer
    for vis_operator in vis_filter_operator:
        vis_gdf = vis_dataset[
            (vis_dataset[vis_col_operator] == vis_operator)
            & (vis_dataset[vis_col_nr_sample] > vis_filter_sample)
        ]
        layer_list.append(dict(gdf=vis_gdf, col=vis_col, group_name=vis_operator))
    
    # competition layer
    vis_gdf = vis_dataset.loc[
        vis_dataset.groupby(vis_col_group)[vis_col].idxmax().dropna()
    ]
    layer_list.append(
        dict(
            gdf=vis_gdf[(vis_gdf[vis_col_nr_sample] > vis_filter_sample)],
            col=vis_col_operator,
            group_name=vis_col,
            color_dict=vis_color_dict,
        )
    )
    
    # folium layout
    m = folium_layout(
        layer_list=layer_list,
        layout=vis_layout,
        palette_n=vis_palette_n,
        n_col=vis_n_col,
        groups=True,
        legend=True,
        weight=1,
        opacity=0.9,
        fill_opacity=1.0,
        color="lightgrey",
    )
    
    # output
    output_file = vis_output_path + vis_col + ".html"
    m.save(output_file)<jupyter_output><empty_output><jupyter_text>Visualize TSN<jupyter_code># initial parameter
vis_output_path = r"output\ST_PostCode_Terminal"
vis_col = "TNS_TNS"
color_dict = {
    "01 TNS Ready": "Red",
    "02 TN Ready": "Blue",
    "03 TS Ready": "Green",
    "04 NS Ready": "Orange",
    "05 Not Ready": "Grey",
}
vis_layout = (1, 3)
# use for loop to create multiple layers
layer_list = []
for filter_operator in vis_filter_operator:
    vis_gdf = vis_dataset[
        (vis_dataset[vis_col_operator] == filter_operator)
        & (vis_dataset[vis_col_nr_sample] > vis_filter_sample)
    ]
    layer_list.append(dict(gdf=vis_gdf, col=vis_col, group_name=filter_operator))
# folium layout
m = folium_layout(
    layer_list=layer_list,
    layout=vis_layout,
    n_col=vis_n_col,
    color_dict=color_dict,
    weight=1,
    opacity=0.8,
    fill_opacity=0.6,
)
# output
output_file = vis_output_path + vis_col + ".html"
m.save(output_file)<jupyter_output><empty_output><jupyter_text>ST PC VALUE  Import Data<jupyter_code># path and import
data_value_pc_path = 'data/Finland_PostCode_Info.csv'
data_value_pc_raw = pd.read_csv(data_value_pc_path)

# convert postcode format from int to str
data_value_pc_raw['PostCode'] = data_value_pc_raw['PostCode'].map(lambda x: str(x).zfill(5))<jupyter_output><empty_output><jupyter_text>Convert to gdf<jupyter_code># merge data to gdf
col_value_pc_merge_left = ["NAME_1", "NAME_2", "NAME_3", "NAME_4", "posti_alue"]
col_value_pc_merge_right = ["NAME_1", "NAME_2", "NAME_3", "NAME_4", "PostCode"]
map_value_pc_geo = map_pc_geo.merge(
    data_value_pc_raw,
    left_on=col_value_pc_merge_left,
    right_on=col_value_pc_merge_right,
)
# Helsinki center pc value map
map_value_pc_helsinki = map_value_pc_geo.query(
    "NAME_4 in @filter_map_adm_helsinki"
).reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Calculate KPI<jupyter_code># kpi dict
data_value_pc_dict = {
    "2c_value": ["Inhabitants_Density", "Average_income"],
    "2b_value": ["Enterprise_Density"],
    "2h_value": ["Building_Density", "Average_income"],
}
# kpi threshold
print(map_value_pc_geo[set(sum(data_value_pc_dict.values(), []))].quantile(q=[0, .25, .5, .75, 1]))

# auto
data_value_pc_thd = map_value_pc_geo[set(sum(data_value_pc_dict.values(), []))].quantile(q=[.75, .5])

# manual
# data_value_pc_thd = pd.DataFrame({'Inhabitants_Density': [50, 10],
#                                   'Average_income': [25000, 20000],
#                                   'Enterprise_Density': [15, 1],
#                                   'Building_Density': [20, 5],
#                                   })
# kpi cat
data_value_pc_thd.index = ['High', 'Medium']
data_value_pc_thd_default = 'Low'
# categorization
data_value_pc_kpi = add_kpi_cat(
    kpi_df=map_value_pc_geo,
    kpi_dict=data_value_pc_dict,
    kpi_thd=data_value_pc_thd,
    cat_default=data_value_pc_thd_default,
)
data_value_pc_kpi["2c_value"].value_counts()<jupyter_output><empty_output><jupyter_text>Visualize geo data<jupyter_code># initial parameter
vis_output_path = r"output\ST_PostCode_Value_"
vis_gdf_left = data_st_pc_ter_geo
vis_gdf_left_col_group = ["NAME_1", "NAME_2", "NAME_3", "NAME_4", "posti_alue"]
vis_gdf_left_col = "NR_Average_Speed"
vis_gdf_left_col_nr_sample = "I12_Samples"
vis_gdf_left_filter_sample = 3
vis_gdf_left_col_operator = "operator_name"

vis_gdf_right = data_value_pc_kpi
vis_gdf_right_col = "2c_value"
gdf_left[
            (gdf_left[vis_gdf_left_col_nr_sample] > vis_gdf_left_filter_sample)
        ][vis_gdf_left_col_operator]
layer_list = []
# competition layer
gdf_left = vis_gdf_left.loc[
    vis_gdf_left.groupby(vis_gdf_left_col_group)[vis_gdf_left_col].idxmax().dropna()
]
layer_list.append(
    dict(
        gdf=gdf_left[
            (gdf_left[vis_gdf_left_col_nr_sample] > vis_gdf_left_filter_sample)
        ],
        col=vis_gdf_left_col_operator,
        group_name=vis_gdf_left_col,
        color_dict={"DNA": "deeppink", "Elisa": "darkblue", "Telia": "blueviolet"},
    )
)
# value_layer
layer_list.append(
    dict(
        gdf=vis_gdf_right,
        col=vis_gdf_right_col,
        color_dict={"High": "Red", "Medium": "Orange", "Low": "Green"},
    )
)

m = folium_layout(
    layer_list=layer_list,
    layout=(1, 2),
    weight=1,
    opacity=0.9,
    fill_opacity=1.0,
    color="lightgrey",
)
# output
output_file = vis_output_path + vis_gdf_right_col + ".html"
m.save(output_file)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10516194940207921, 'fraction_numerical': 0.03912024517757346, 'mean_word_length': 3.437866666666667, 'pattern_counts': {'":': 24, '<': 52, '<?xml version=': 0, '>': 58, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '42204', 'license': 'MIT', 'n_tokens_mistral': 7020, 'n_tokens_neox': 6542, 'n_words': 1014, 'path': '04_Postcode_Level.ipynb', 'repo_name': 'lucassu2012/CMO_APP', 'size': '16641'}