<jupyter_start><jupyter_text>Fraudulent Energy Client DetectionThe Tunisian Company of Electricity and Gas (STEG) is public and non-administrative. It is responsible for delivering electricity and gas across Tunisia. The company suffered tremendous losses in the order of **200 million** Tunisian Dinars due to fraudulent manipulations of meters by consumers.Using the client’s billing history, the challenge aims to detect and recognize clients involved in fraudulent activities.The findings will enhance the company’s revenues and reduce the losses caused by such fraudulent activities. For this purpose the CRISP-DM process will be used during the notebook to properly address this challenge.<jupyter_code>#@title Import revelant modules and install Facets
import numpy as np
import pandas as pd
import tensorflow as tf

from matplotlib import pyplot as plt
from matplotlib.pyplot import figure
import seaborn as sns

# The following lines adjust the granularity of reporting. 
pd.options.display.max_rows = 10
pd.options.display.float_format = "{:.1f}".format<jupyter_output><empty_output><jupyter_text>Business  and Data UnderstandingInstead of reducing their consumption, some clients play with their meters to consume energy without fees. This manipulation is more popular in countries where the clients are billed on a quarterly/biannual basis which is the case of Tunisia. This first step aims to understand, clear, sort, and extract important features from the data provided.We want to answer the following questions:1. What is the rate of fraudulent clients in the dataset?2. How are the fraudulent clients distributed across regions/ districts?3. What period did the most Fraudulent clients join the company?4. What variables/combination of variables can be used to predict if a client is fraudulent or not? The data provided by STEG is composed of two files. The first one is comprised of client data, and the second one contains billing history since 2005. * Client:	- Client_id: Unique id for client	- District: District where the client is	- Client_catg: Category client belongs to	- Region: Area where the client is	- Creation_date: Date client joined	- Target: fraud:1 , not fraud: 0* Invoice data	- Client_id: Unique id for the client	- Invoice_date: Date of the invoice	- Tarif_type: Type of tax	- Counter_number:	- Counter_statue: takes up to 5 values such as working fine, not working, on hold statue, ect	- Counter_code:	- Reading_remarque: notes that the STEG agent takes during his visit to the client (e.g: If the counter shows 		something wrong, the agent gives a bad score)	- Counter_coefficient: An additional coefficient to be added when standard consumption is exceeded	- Consommation_level_1: Consumption_level_1	- Consommation_level_2: Consumption_level_2	- Consommation_level_3: Consumption_level_3	- Consommation_level_4: Consumption_level_4	- Old_index: Old index	- New_index: New index	- Months_number: Month number	- Counter_type: Type of counter<jupyter_code>client_df = pd.read_csv("./data/train/client_train.csv")
invoice_df = pd.read_csv("./data/train/invoice_train.csv")
client_df.head()
client_df.info()<jupyter_output><empty_output><jupyter_text>All the columns have 0 Nan values. All types are as expected, except for the creation_date column that needs to be transformed into the datetime.<jupyter_code>client_df.describe()
def plt_stats(df, col):
    """
    plots the value_counts of a pd.DataFrame's column
    :param df: pd.DataFrame of data
    :param col: column of interest
    """
    figure(figsize=(20,5))
    plt.title(f'Number of clients per {col}')
    df[col].value_counts(ascending=True).plot(kind='barh')
    plt.ylabel(col)
    plt.xlabel('Count')
    plt.show()<jupyter_output><empty_output><jupyter_text>1. What is the rate of fraudulent clients in the dataset?<jupyter_code>plt_stats(client_df, col='target')
print(f'The pourcentage of fraudulent clients is:{client_df["target"].mean()}')<jupyter_output><empty_output><jupyter_text>The first observation is that the dataset is unbalanced and there could be possibility of bias where the pourcentage of Fraudulent clients is limited to a 5\%.  2. How are the fraudulent clients distributed cross regions/ districts?<jupyter_code>plt_stats(client_df, col='disrict')
plt_stats(client_df, col='region')<jupyter_output><empty_output><jupyter_text>The clients are distributed more or less homogeneously across districts, but this is not the case of the distribution across regions. The clients from this dataset are centred in some regions more than others. Nonetheless, it would be more interesting to look at the rate of fraudulent clients per region.<jupyter_code>figure(figsize=(20,5))
plt.title('The rate of fraudulent clients per region')
client_df.groupby('region').mean()['target'].sort_values().plot(kind='barh')
plt.ylabel('region')
plt.xlabel('Count')
plt.show()<jupyter_output><empty_output><jupyter_text>The last plot indicates that the region is essential for predicting fraudulent activities. The clients of some regions are more likely to be fraudulent than others.<jupyter_code>figure(figsize=(20,5))
plt.hist(client_df.loc[client_df['target']==1,'region'], bins=500)
plt.xlabel('region')
plt.ylabel('Count')
plt.title('The distribution of Faudulent clients per region')<jupyter_output><empty_output><jupyter_text>3. What is the period where the most Fraudulent clients joined the company?<jupyter_code>client_df['creation_date'] = pd.to_datetime(client_df['creation_date'])
figure(figsize=(20,5))
client_df.groupby('creation_date').sum().resample('2M').sum()['target'].plot()
plt.ylabel('Date joining the company')
plt.ylabel('Number of clients')
plt.title('Number of fraudulent clients joining the company')<jupyter_output><empty_output><jupyter_text>The plot shows that before 2006, the number of fraudulent clients joining the company was more or less steady. A peak in the number of fraudulent clients joining is evident around 2011 when the revolution of Tunisia occurred, taking advantage of the political crisis. However, starting from 2014, the number of fraudulent clients joining the company decreased.  3. What are the best predictors for a fraudulent client?<jupyter_code>invoice_df.head()
invoice_df.info()
invoice_df.describe()
df = pd.DataFrame(invoice_df.nunique())
df[df.values.reshape(-1)<40]
assert invoice_df.counter_number.nunique() >= invoice_df.client_id.nunique()
invoice_df.tarif_type.unique()
invoice_df.counter_statue.unique() # need to be cleaned, values should be 0-5 according to the meta-data file
invoice_df.counter_coefficient.unique()
invoice_df.counter_type.unique() # need to be mapped to abinary variable
invoice_df.counter_statue.unique()<jupyter_output><empty_output><jupyter_text>The above list is the list of categorical columns. The aggregation of these columns is thus a bit tricky. We will only consider the counter_type as a dummy variable. The other variables are considered ints since their values are highly unbalanced.  Data PreparationThe data of both DataFrames require some cleaning. For the clients dataframe, the type of categorical columns should be correctly set. During data understanding, the values were concentrated around 3 values. We create a new feature region_group to spot that information. The creation date in datetime type and can not be used during classification. but as we saw the time of joining has some influence on the target variable. Therefore, we derive two variables from this column, the join_time representing the number of months since he joined the company and the join_period which specify if the client joined before 2006 or after it.<jupyter_code>def clean_clients(clients):
    """
    cleans the DataFrame of the clients
    :param clients: pd.DataFrame of clients
    :return: pd.DataFrame of clients
    """
    # changing the type of categorical columns
    clients['client_catg'] = clients['client_catg'].astype('category')
    clients['disrict'] = clients['disrict'].astype('category')
    clients['region'] = clients['region'].astype('category')
    # Creating the group regions
    clients['region_group'] = clients['region'].apply(lambda x: 100 if x<100 else 390 if x>390 else 300 if x>300 else 200)
    clients['region_group'] = clients['region_group'].astype('category')
    # Deriving information from the creation time (when the client joined)
    clients['creation_date'] = pd.to_datetime(clients['creation_date'])
    clients['join_time'] = (2019 - clients['creation_date'].dt.year)*12 - clients['creation_date'].dt.month
    clients['join_period'] = (2006 - clients['creation_date'].dt.year)
    clients = clients.drop(['creation_date'], axis=1)
    
    return clients
cleaned_client_df = clean_clients(client_df)
cleaned_client_df.dtypes<jupyter_output><empty_output><jupyter_text>The data frame of the invoices also requires cleaning and<jupyter_code>def clean_invoices(invoices):
    """
    cleans the DataFrame of the clients
    :param invoices: a pd.DataFrame of invocies of the clients
    :return: pd.DataFrame
    """
    # Cleaning the statue values [0-5]
    invoices['counter_statue'] = invoices['counter_statue'].map({
        0:0,1:1,2:2,3:3,4:4,5:5,769:5,'0':0,'5':5,'1':1,'4':4,'A':0,618:5,269375:5,46:5,420:5
    })
    # Deriving information form the invoice dates
    invoices['invoice_date'] = pd.to_datetime(invoices['invoice_date'], dayfirst=True)
    invoices['invoice_month'] = invoices['invoice_date'].dt.month
    invoices['invoice_year'] = invoices['invoice_date'].dt.year
    invoices['is_weekday'] = ((pd.DatetimeIndex(invoices.invoice_date).dayofweek) // 5 == 1).astype(float)
    invoices = invoices.drop(['invoice_date'], axis=1)
    # Getting the power consumption of the meter
    invoices['delta_index'] = invoices['new_index'] - invoices['old_index']
    # Getting the sum of the 4 consumption levels
    invoices['total_consumption'] = invoices[['consommation_level_1',
                                             'consommation_level_2',
                                             'consommation_level_3',
                                             'consommation_level_4']].sum(axis=1)
    # Checking if numbers of the meter and the invoice are identical
    invoices['is_energy_equal'] = (invoices['total_consumption'] == invoices['delta_index'])
    # Turn the counter type into dummy columns
    invoices = pd.get_dummies(invoices, columns=[ 'counter_type'])
    
    return invoices
cleaned_invoices = clean_invoices(invoice_df)
cleaned_invoices.describe()
assert 'client_id' in cleaned_invoices.columns<jupyter_output><empty_output><jupyter_text>The aggregation of the invoices will be done using the standard measures of spread to have more information about the each column before merging them with the client list.<jupyter_code>rename_cols = lambda cols,prefix: [f'{col}_{prefix}' for col in cols]

def join_data(clients, invoice):
    """
    aggregate the invoices of each client and link them to the client
    the aggregation is done using the sum for dummy columns and the
    standard measures of spread for the other columns
    :param clients: pd.DataFrame of clients
    :param invoice: pd.dataFrame of invoices
    :return: a pd.DataFrame of clients and their invoices
    """
    # list of columns that we want to aggregate with a sum
    # cols_grp_sum = ['is_weekday', 'is_energy_equal', 'counter_type_ELEC','counter_type_GAZ','client_id' ]
    # invoice_agg = invoice[cols_grp_sum].groupby('client_id').sum()
    # # droping the already aggregated columns
    # invoice = invoice.drop(list(set(cols_grp_sum)- set(['client_id']) ), axis=1)

    invoice_max = invoice.groupby('client_id').max()
    invoice_max.columns = rename_cols(invoice_max.columns, 'max')
    
    invoice_min = invoice.groupby('client_id').min() 
    invoice_min.columns = rename_cols(invoice_min.columns, 'min')
    
    invoice_median = invoice.groupby('client_id').median()
    invoice_median.columns = rename_cols(invoice_median.columns, 'median')
    
    invoice_q25 =  invoice.groupby('client_id').quantile(.25)
    invoice_q25.columns = rename_cols(invoice_q25.columns, 'q25')
    
    invoice_q75 =  invoice.groupby('client_id').quantile(.75)
    invoice_q75.columns = rename_cols(invoice_q75.columns, 'q75')

    invoice_std = invoice.groupby('client_id').std()
    invoice_std.columns = rename_cols(invoice_std.columns, 'std')
    
    invoice_mean = invoice.groupby('client_id').mean()
    invoice_mean.columns = rename_cols(invoice_mean.columns, 'mean')

    invoice_summary = pd.concat([invoice_max, invoice_min,
                                 invoice_median, 
                                 invoice_q25, invoice_q75,
                                invoice_std, invoice_mean], axis=1).reset_index()
 

    return clients.merge(invoice_summary, on='client_id')

train_data = join_data(cleaned_client_df, cleaned_invoices)
def add_extra_features(df):
    """
    Adds the range and the inter-quartile measures to the features
    :param df:
    :return:
    """
    cols = df.columns[df.dtypes==int]
    for col in cols:
        original_col = col.split('_')
        if len(original_col) == 2 and col!='join_time':
            print(original_col)
            original_col = original_col[0]
            df[f'{original_col}_range'] =  df[f'{original_col}_max'] - df[f'{original_col}_min']
            df[f'{original_col}_iqr'] =  df[f'{original_col}_q75'] - df[f'{original_col}_q25']
    return df

train_data = add_extra_features(train_data)
train_data.describe().T
# Dropping the columns that are null most of the time
useless_col = train_data.columns[(train_data == 0).astype(int).sum(axis=0) / train_data.shape[0] >.98]
useless_col
train_data = train_data.drop(useless_col, axis=1)<jupyter_output><empty_output><jupyter_text>Data ModellingTo model the data and classify the clients, the decision trees are used from xgboost package. Cross validation is used to train the model on the 5 folds. The parameters of teh model were chosen manually.<jupyter_code>from sklearn.model_selection import train_test_split
from sklearn.metrics import f1_score, recall_score, precision_score, roc_auc_score
import lightgbm
from optuna import Trial
import optuna
import gc

from sklearn.model_selection import StratifiedKFold
from lightgbm import LGBMClassifier
X, y = train_data.drop(['client_id','target'], axis=1), train_data['target']
assert 'target' not in X.columns
X.describe().T.to_csv('./data/X_train.csv')
client_df = pd.read_csv("./data/test/client_test.csv")
invoice_df = pd.read_csv("./data/test/invoice_test.csv")

cleaned_invoices = clean_invoices(invoice_df)
cleaned_clients= clean_clients(client_df)

test_data = join_data(cleaned_clients, cleaned_invoices).drop(['counter_statue_min', 'consommation_level_3_min',
       'consommation_level_4_min', 'counter_statue_median',
       'consommation_level_4_median', 'counter_statue_q25',
       'consommation_level_3_q25', 'consommation_level_4_q25'], axis=1)

X_test = test_data.drop(['client_id'],axis=1)
%%time
seed= 42
model = LGBMClassifier(random_state=seed, n_estimators=10000,num_leaves=362, max_depth=124,
                           learning_rate=0.008, min_split_gain=0.001,
                           feature_fraction=0.8, bagging_freq=8)

stkfold = StratifiedKFold(n_splits=5, shuffle=True, random_state=seed)
res=[]
local_probs=pd.DataFrame()
probs = pd.DataFrame()

for i, (tdx, vdx) in enumerate(stkfold.split(X, y)):
    X_train, X_valid, y_train, y_valid = X.iloc[tdx], X.iloc[vdx], y[tdx], y[vdx]
    model.fit(X_train, y_train,
                  eval_set=[(X_valid, y_valid)],
                  early_stopping_rounds=30, verbose=False)
        
    preds = model.predict_proba(X_valid)
    oof_predict = model.predict_proba(X_test)
    local_probs['fold_%i'%i] = oof_predict[:,1]
    res.append(roc_auc_score(y_valid, preds[:,1]))
 
print('ROC AUC:', round(np.mean(res), 6))   
local_probs['res'] = local_probs.mean(axis=1)
probs['target'] = local_probs['res']
submission = pd.DataFrame({
            'client_id': test_data['client_id'],
            'target': probs['target']
    })
submission.to_csv('Samplesubmission.csv', index=False)<jupyter_output>/home/hafsa/anaconda3/envs/nilmtk-env/lib/python3.8/site-packages/lightgbm/sklearn.py:726: UserWarning: 'early_stopping_rounds' argument is deprecated and will be removed in a future release of LightGBM. Pass 'early_stopping()' callback via 'callbacks' argument instead.
  _log_warning("'early_stopping_rounds' argument is deprecated and will be removed in a future release of LightGBM. "
/home/hafsa/anaconda3/envs/nilmtk-env/lib/python3.8/site-packages/lightgbm/sklearn.py:736: UserWarning: 'verbose' argument is deprecated and will be removed in a future release of LightGBM. Pass 'log_evaluation()' callback via 'callbacks' argument instead.
  _log_warning("'verbose' argument is deprecated and will be removed in a future release of LightGBM. "
{'fraction_non_alphanumeric': 0.09778826304924801, 'fraction_numerical': 0.014921851961073429, 'mean_word_length': 4.8368330464716, 'pattern_counts': {'":': 0, '<': 54, '<?xml version=': 0, '>': 56, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '416034', 'license': 'MIT', 'n_tokens_mistral': 5398, 'n_tokens_neox': 5114, 'n_words': 1528, 'path': 'Fraud_Detection_STEG.ipynb', 'repo_name': 'BHafsa/STEG-Fraud-Detection', 'size': '16955'}