<jupyter_start><jupyter_text>Loss Ratio Prediction<jupyter_code>import os
import math
import csv
import collections
import pdb

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

from sklearn.preprocessing import StandardScaler
from sklearn.linear_model import LassoCV
from sklearn.ensemble import RandomForestRegressor
from sklearn.model_selection import cross_val_score
from sklearn.preprocessing import LabelEncoder
from sklearn.model_selection import train_test_split
from sklearn.tree import DecisionTreeRegressor
from sklearn.metrics import mean_absolute_error
from sklearn.neighbors import KNeighborsRegressor

%matplotlib inline<jupyter_output><empty_output><jupyter_text>Load Dataset<jupyter_code>path_training_dataset = "../../dataset/training_data.csv"
df_data = pd.read_csv(path_training_dataset)
df_data.head()
df_data.columns<jupyter_output><empty_output><jupyter_text>Common Functions<jupyter_code>def get_selected_features(feature_list_file_path):
    with open(feature_list_file_path) as f:
        features_list = f.read().splitlines() 
        return features_list
def strip_white_spaces(input_df):
    columns = list(input_df.columns)
    for column in columns:
        input_df[column] = input_df[column].str.strip()
    return input_df
def convert_df_objects_to_str(df_dataset, df_columns):
    for column in df_columns:
        if df_dataset[column].dtype == 'object':
            df_dataset[column] = df_dataset[column].astype(str)
    return df_dataset
def get_num_cat_features(df_dataset, df_columns):
    categorical_variables = []
    numerical_variables = []
    for column in df_columns:
        print(column)
        if df_dataset[column].dtype == 'object':
            categorical_variables.append(column)
        else:
            numerical_variables.append(column)
    return numerical_variables, categorical_variables
selected_features_train = [
    'Vehicle_Youthful_Good_Student_Code',
    'Vehicle_Driver_Points',
    'Driver_Total_Female',
    'Driver_Total_Male',
    'Driver_Total_Teenager_Age_15_19',
    'Driver_Total_College_Ages_20_23',
    'Driver_Total_Young_Adult_Ages_24_29',
    'Driver_Total_Low_Middle_Adult_Ages_30_39',
    'Driver_Total_Middle_Adult_Ages_40_49',
    'Driver_Total_Adult_Ages_50_64',
    'Driver_Total_Senior_Ages_65_69',
    'Driver_Total_Upper_Senior_Ages_70_plus',
    'Driver_Total_Married',
    'Driver_Total_Single',
    'Vehicle_Usage',
    'Vehicle_Miles_To_Work',
    'Vehicle_Territory',
    'Annual_Premium',
    'Loss_Amount']
df_data = df_data[selected_features_train]
num_features, cat_features = get_num_cat_features(df_data, list(df_data.columns))
num_features
cat_features<jupyter_output><empty_output><jupyter_text>Cleaning Training Dataset<jupyter_code>df_num = df_data[num_features]
df_num.head()
df_cat = df_data[cat_features]
df_cat.head()
df_cat_strip = strip_white_spaces(df_cat)
df_data_clean = df_num.join(df_cat, how='outer')
df_data_clean.head()<jupyter_output><empty_output><jupyter_text>Find useful features<jupyter_code>def get_unique_values_with_count(df_input):
    columns = df_input.columns
    for column in columns:
        unique_value_count = df_input[column].value_counts()
        print(column)
        print()
        print(unique_value_count)
        print('--'*50)
get_unique_values_with_count(df_data)<jupyter_output>Vehicle_Youthful_Good_Student_Code

Not Eligible for Good Student Credit    418562
Eligible for Good Student Credit          5869
Name: Vehicle_Youthful_Good_Student_Code, dtype: int64
----------------------------------------------------------------------------------------------------
Vehicle_Driver_Points

0    381289
1     36431
2      5905
3       670
4       108
5        21
6         5
7         2
Name: Vehicle_Driver_Points, dtype: int64
----------------------------------------------------------------------------------------------------
Driver_Total_Female

1    238676
0    181914
2      3837
3         4
Name: Driver_Total_Female, dtype: int64
----------------------------------------------------------------------------------------------------
Driver_Total_Male

1    222967
0    198925
2      2537
3         2
Name: Driver_Total_Male, dtype: int64
----------------------------------------------------------------------------------------------------
Driver_Total_Teenager_Age_15_19

0  [...]<jupyter_text>Features to be used in portfolio - Vehicle_Make_Year_Mean- Vehicle_Performance_Standard_Minus_all- Vehicle_Number_Of_Drivers_Assigned_Mean_Ignore_gt_10- Vehicle_Usage_Pleasure_Minus_all- Vehicle_Anti_Theft_Device_anything_but_not_applicable- Vehicle_Age_In_Years_Mean- Vehicle_Collision_Coverage_Indicator_Y_minus_N- Driver_Total_Single_Sum_not_include_0- Driver_Total_Mean- Driver_Total_Male_Mean- Driver_Total_Female_Mean- Driver_Total_Married_Mean- Driver_Total_Teenager_Age_15_19_Mean- Driver_Total_College_Ages_20_23_Sum- Driver_Total_Young_Adult_Ages_24_29_Mean- Driver_Total_Upper_Senior_Ages_70_plus_Mean- Annual_Premium_Sum<jupyter_code>selected_features_test = ['Vehicle_Make_Year',
                        'Vehicle_Number_Of_Drivers_Assigned',
                        'Vehicle_Age_In_Years',
                        'Driver_Total_Single',
                        'Driver_Total',
                        'Driver_Total_Male',
                        'Driver_Total_Female',
                        'Driver_Total_Married',
                        'Driver_Total_Teenager_Age_15_19',
                        'Driver_Total_College_Ages_20_23',
                        'Driver_Total_Young_Adult_Ages_24_29',
                        'Driver_Total_Upper_Senior_Ages_70_plus',
                        'Vehicle_Usage',
                        'Vehicle_Anti_Theft_Device',
                        'Vehicle_Performance',
                        'Annual_Premium']<jupyter_output><empty_output><jupyter_text>Create Portfolios in training data like testing portfolios<jupyter_code>df_data.shape
policies_in_portfolios = [1000, 3000, 5000]
percentage_losses = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20]
df_list_portfolios = []
df_data_oh = pd.get_dummies(df_data)
df_data_oh.shape
df_data_oh_columns = list(df_data_oh.columns)
for i in range(10):
    df_data = df_data_oh.sample(frac=1)
    df_data_loss_zero = df_data[df_data['Loss_Amount'] == 0]
    df_data_loss_non_zero = df_data[df_data['Loss_Amount'] != 0]
    current_df_without_loss = 0
    current_df_with_loss = 0
    for i in range(len(policies_in_portfolios)):
        for j in range(len(percentage_losses)):
            without_loss_size = int(((100 - percentage_losses[j])/100) * policies_in_portfolios[i])
            with_loss_size = int(policies_in_portfolios[i] - without_loss_size )

            df_without_loss = df_data_loss_zero.iloc[current_df_without_loss: current_df_without_loss + without_loss_size]
            df_with_loss = df_data_loss_non_zero.iloc[current_df_with_loss: current_df_with_loss + with_loss_size]

            current_df_without_loss = current_df_without_loss + without_loss_size
            current_df_with_loss = current_df_with_loss + with_loss_size

            df_merge = pd.concat([df_without_loss, df_with_loss])
            df_list_portfolios.append(df_merge)
len(df_list_portfolios)
df_list_portfolios[0].shape
df_data_final = None
df_data_oh_columns
selected_features_mean = num_features.copy()
selected_features_mean.remove('Annual_Premium')
selected_features_mean.remove('Loss_Amount')
mean_columns = selected_features_mean

sum_columns = ['Annual_Premium']

for cat_f in cat_features:
    for col in list(df_list_portfolios[0].columns):
        if col.startswith(cat_f):
            sum_columns.append(col)

target_column = ['Loss_Amount']
sum_columns
ls_final_training = []
def portfolio_to_features(df_portfolio, is_training=True):
    df_portfolio_mean = df_portfolio[mean_columns]
    df_portfolio_mean = df_portfolio_mean.mean()
    ls_portfolio_mean = df_portfolio_mean.to_list()
    
    df_portfolio_sum = df_portfolio[sum_columns]
    df_portfolio_sum = df_portfolio_sum.sum()
    ls_portfolio_sum = df_portfolio_sum.to_list()
    
    if is_training:
        df_portfolio_target = df_portfolio[target_column]
        df_portfolio_target_sum = df_portfolio_target.sum()
        ls_portfolio_target_sum = df_portfolio_target_sum.to_list()
    
    list_merge = ls_portfolio_mean + ls_portfolio_sum
    
    if is_training:
        list_merge = list_merge + ls_portfolio_target_sum
    
    return list_merge
def features_to_training_df(df_portfolios_list):
    features_list = mean_columns + sum_columns + target_column
    final_list = []
    for single_portfolio in df_portfolios_list:
        ls_portfolio = portfolio_to_features(single_portfolio)
        final_list.append(ls_portfolio)
    df_final_training = pd.DataFrame(final_list, columns = features_list)
    return df_final_training
df_final_train = features_to_training_df(df_list_portfolios)
df_final_train.head()<jupyter_output><empty_output><jupyter_text>Training the model with Decision Tree<jupyter_code>X_train = df_final_train.loc[:, df_final_train.columns != 'Loss_Amount']
y_train = df_final_train['Loss_Amount']
X_train.shape, y_train.shape
X_train.columns
decision_tree_regressor = DecisionTreeRegressor()
decision_tree_regressor.fit(X_train, y_train)
y_train_pred = decision_tree_regressor.predict(X_train)
mae = mean_absolute_error(y_train, y_train_pred)
mae
from xgboost import XGBRegressor
xg_regressor = XGBRegressor(objective ='reg:squarederror', colsample_bytree = 0.3, learning_rate = 0.01, max_depth = 3, alpha = 10, n_estimators = 100)
xg_regressor.fit(np.nan_to_num(X_train.to_numpy()), y_train)
y_train_pred_xg = xg_regressor.predict(np.nan_to_num(X_train.to_numpy()))
mae_xg = mean_absolute_error(y_train, y_train_pred_xg)
mae_xg<jupyter_output><empty_output><jupyter_text>Train Using KNN Model<jupyter_code>knn_regressor = KNeighborsRegressor(5)
knn_regressor.fit(np.nan_to_num(X_train.to_numpy()), y_train)
y_train_pred_knn = knn_regressor.predict(np.nan_to_num(X_train.to_numpy()))
mae_knn = mean_absolute_error(y_train, y_train_pred_knn)
mae_knn
def load_test_portfolios():
    path_testing_dataset = "../dataset/testing_portfolios"
    portfolio_files = os.listdir(path_testing_dataset)
    portfolio_dict = {}
    for i in range(len(portfolio_files)):
        portfolio_id = int(portfolio_files[i].split('.')[0][15:])
        portfolio_dict[portfolio_id] = portfolio_files[i]
    od = collections.OrderedDict(sorted(portfolio_dict.items()))
    po_list = []
    for key,val in od.items():
        po_list.append(val)
    df_portfolio_list = []
    for portfolio_file in po_list:
        file_path = os.path.join(path_testing_dataset, portfolio_file)
        df_test_portfolio = pd.read_csv(file_path)
        test_num_features = num_features.copy()
        test_num_features.remove('Loss_Amount')
        
        df_test_num = df_test_portfolio[test_num_features]
        df_test_cat = df_test_portfolio[cat_features]
        df_test_cat_strip = strip_white_spaces(df_test_cat)
        df_test_portfolio = df_test_num.join(df_test_cat_strip, how='outer')
        df_test_portfolio = pd.get_dummies(df_test_portfolio)
        df_test_portfolio = df_test_portfolio.reindex(sorted(X_train.columns), axis=1)
        
        df_portfolio_list.append(df_test_portfolio)
        
    result_dict = collections.OrderedDict()
    for i in range(len(po_list)):
        portfolio_id = po_list[i].split('.')[0][5:]
        result_dict[portfolio_id] = df_portfolio_list[i] 
    return result_dict
def features_to_testing_df(df_portfolios_ordered_dict):
    features_list = mean_columns + sum_columns
    features_list.insert(0, 'ID')
    final_list = []
    for portfolio_id, single_portfolio in df_portfolios_ordered_dict.items():
        ls_portfolio = portfolio_to_features(single_portfolio, False)
        ls_portfolio.insert(0, portfolio_id)
        final_list.append(ls_portfolio)
    df_final_testing = pd.DataFrame(final_list, columns = features_list)
    return df_final_testing
test_portfolios_dict = load_test_portfolios()
for key, value in test_portfolios_dict.items():
    print(key, value.shape)
df_test_final = features_to_testing_df(test_portfolios_dict)
def generate_test_results(regressor, X_test, output_dir):
    # for each policy
    result_data = collections.OrderedDict()
    portfolio_ids_df = X_test.iloc[:, 0:1]
    X_test_features = X_test.iloc[:, 1:]
    
    y_pred = regressor.predict(np.nan_to_num(X_test_features.to_numpy()))
    loss_ratio = y_pred/X_test['Annual_Premium']
    loss_ratio_log = np.log(loss_ratio)
    loss_ratio_log_df = loss_ratio_log.to_frame(name='ln_LR')
    result_df = portfolio_ids_df.join(loss_ratio_log_df, how='outer')
    
    result_df.to_csv('output.csv', index=False)
generate_test_results(knn_regressor, df_test_final, '')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10877165721389169, 'fraction_numerical': 0.017403465154222673, 'mean_word_length': 3.8536953242835597, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 36, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '661719', 'license': 'Apache-2.0', 'n_tokens_mistral': 4570, 'n_tokens_neox': 4337, 'n_words': 637, 'path': 'notebooks/prediction/loss-ratio-prediction-notebook.ipynb', 'repo_name': 'adityakamble49/loss-ratio-prediction', 'size': '12871'}