begin

    using DataFrames, CSV

    using Dates

    using Flux

end

splitLabel(label) = split(label, " ")[1]

function onehot_df(training::DataFrame, key::String, labels::Array, insert::Bool)

    if !insert

        return select(training, Not(key))

    end

    oh_key = Flux.onehotbatch(training[!, key], labels)

    return hcat(select(training, Not(key)), DataFrame(oh_key', (key[1:4]*"_") .* splitLabel.(labels)))

end

dateConv(d) = Dates.DateTime(d, "yyyy-mm-dd HH:MM:SS")

convertDay(array) = Dates.value.(convert.(Dates.Day, round.(array, Dates.Day)))

begin 

    filename_training = "training_SC_GGP_AXA_FR.csv"

    filename_scoring = "scoring_SC_GGP_AXA_FR.csv"

    training = DataFrame(CSV.File(filename_training))

    scoring = DataFrame(CSV.File(filename_scoring))

    # Date conversion

    # Missing

    today_ = Dates.now()

    today_string = Dates.format(today_, "yyyy-mm-dd HH:MM:SS")

    replace!(training.ouverture_dernier_sinistre, missing => today_string)

    replace!(scoring.ouverture_dernier_sinistre, missing => today_string)

    replace!(training.cloture_dernier_sinistre, missing => today_string)

    replace!(scoring.cloture_dernier_sinistre, missing => today_string)

​

    # Date conversion

    date_keys = ["debut_contrat", "ouverture_dernier_sinistre", "cloture_dernier_sinistre"]

    for key in date_keys

        training[!, key] = dateConv.(training[!, key])

        scoring[!, key] = dateConv.(scoring[!, key])

    end

    # Add duree

    insertcols!(training, "duree_contrat" => convertDay(today_ - training.debut_contrat))

    insertcols!(scoring, "duree_contrat" => convertDay(today_ - scoring.debut_contrat))

    insertcols!(training, "duree_dernier_sinistre" => convertDay(training.cloture_dernier_sinistre - training.ouverture_dernier_sinistre))

    insertcols!(scoring, "duree_dernier_sinistre" => convertDay(scoring.cloture_dernier_sinistre - scoring.ouverture_dernier_sinistre))

    insertcols!(training, "duree_zero_sinistre" => convertDay(today_ - training.cloture_dernier_sinistre))

    insertcols!(scoring, "duree_zero_sinistre" => convertDay(today_ - scoring.cloture_dernier_sinistre))

    # Remove dates

    for key in date_keys

        select!(training, Not(key))

        select!(scoring, Not(key))

    end

​

    # One hot encode categorical

    categorical_keys = ["departement", "categorie_socio_professionnelle", "type_de_bien", "statut"]

    insert_bools = [true, true, true, true]

    for i in 1:4

        key = categorical_keys[i]

        insert = insert_bools[i]

        labels = sort(unique(training[!, key]))

        global training = onehot_df(training, key, labels, insert)

        global scoring = onehot_df(scoring, key, labels, insert)

    end

​

    # drop useless

    select!(training, Not("index"))

    select!(scoring, Not("index"))

​

    training = Float32.(training)

    scoring = Float32.(scoring)

​

    #write to files

    Xscoring = Float32.(scoring)

    CSV.write("x"*filename_scoring, Xscoring)

​

    Xtraining = Float32.(select(training, Not("target")))

    CSV.write("x"*filename_training, Xtraining)

​

    Ytraining = Float32.(training[!, "target"])

    CSV.write("y"*filename_training, DataFrame(target=Ytraining));

​

end

training

scoring

describe(training)

describe(scoring)