def getPrediction(textInput, k):
text_list = textInput.split(' ')
data = []
for word in text_list:
data.append((regex.sub(' ', word).lower(), 1))
wordsInThatDoc = sqlContext.createDataFrame(data, ["word", "count"])
allDictionaryWordsInThatDoc = dictionary.join(
wordsInThatDoc, wordsInThatDoc.word == dictionary.words, 'inner')
myArray_idf = allDictionaryWordsInThatDoc.groupBy(wordsInThatDoc.word).agg(
sum_("count").alias("count"),
buildArray_udf(func.collect_list("position")).alias("idf")).orderBy(
"count", ascending=False).limit(1).select('idf')
myArray = np.multiply(myArray_idf.select('idf').collect(), idfArray)
distance_udf = udf(lambda x: float(np.dot(np.array(x), myArray[0][0])),
FloatType())
distances = features.withColumn('distance', distance_udf('position_array'))
topK = distances.orderBy('distance', ascending=False).limit(k)
docIDRepresented = topK.withColumn('cnt', lit(1)).drop('tfidf')
numTimes = docIDRepresented.groupBy("Category").agg(
sum_("cnt").alias("count")).drop("cnt")
numTimes_order = numTimes.orderBy('count', ascending=False).limit(k)
return numTimes_order.collect()
def _transform_data(self, df, df_base):
"""Transform original dataset.
:param df: Input DataFrame.
:return: Transformed DataFrame.
"""
if self._is_diario:
df = df.withColumn('TEST', lit(1))
df_base = df_base.withColumn('TEST', lit(0))
df = df.union(df_base)
del df_base
# Cast key variables and rename headers
df = df.withColumnRenamed('auditCodigoSiniestroReferencia',
'id_siniestro')
df = df.withColumn('id_siniestro',
df['id_siniestro'].cast(IntegerType()))
df = df.dropna(subset=['id_siniestro'])
df = df.withColumnRenamed('auditFechaAperturaSiniestroReferencia',
'fecha_apertura_siniestro')
# VARIABLES DE FECHA: We convert every date variable to a same format and we control the date
variable_fecha = [
"hist_poliza_fecha_inicio", "hist_poliza_fecha_efecto_natural",
"hist_poliza_fecha_efecto_mvto", "hist_poliza_fecha_vto_mvto",
"hist_poliza_vto_natural"
]
for col in variable_fecha:
df = df.withColumn(
col,
when(df[col] == '1900-01-01', None).otherwise(df[col]))
control_fecha = [
"hist_poliza_fecha_inicio", "hist_poliza_fecha_efecto_natural"
]
func = udf(lambda x: datetime.datetime.strptime(x, '%Y/%m/%d'),
DateType())
for col in control_fecha:
df = df.dropna(subset=[col])
df = df.filter(df[col] <= STRING.DAY)
df = df.filter(
df[col] > (datetime.datetime.today() +
relativedelta(years=-5)).strftime('%Y-%m-%d'))
for col in variable_fecha:
df = df.withColumn(col, func(df[col]))
df = df.orderBy(['id_siniestro'], ascending=[True])
# COUNT POLIZAS POR SINIESTRO: We weight the policies by sinister
df = df.withColumn('pondera_poliza', lit(1))
w = (Window().partitionBy(df.id_siniestro).rowsBetween(
-sys.maxsize, sys.maxsize))
df = df.withColumn('hist_mov_poliza_otro_count',
sum_(df['pondera_poliza']).over(w))
# POR PRODUCTO: We use an extra table to group the products
types = df.select('hist_id_producto').distinct().collect()
types = [ty['hist_id_producto'] for ty in types]
types_list = [
when(df['hist_id_producto'] == ty,
1).otherwise(0).alias('d_d_hist_mov_poliza_otro_producto_' +
ty) for ty in types
]
df = df.select(list(df.columns) + types_list)
df.drop('hist_id_producto')
# PROMEDIO DE VERSIONES: We count the versions by policy and then we get the average by policy.
df = df.withColumn(
'hist_mov_poliza_otro_version_count',
sum_(df['hist_poliza_version'].cast(IntegerType())).over(w))
df = df.withColumn(
'hist_mov_poliza_otro_version_promedioxpoliza',
df['hist_mov_poliza_otro_version_count'] /
df['hist_mov_poliza_otro_count'])
# PROMEDIO DE SUPLEMENTOS: We count the suplements by policy and then we get the average by policy.
df = df.withColumn(
'hist_mov_poliza_otro_suplemento_count',
sum_(df['hist_poliza_suplementos'].cast(IntegerType())).over(w))
df = df.withColumn(
'hist_mov_poliza_otro_suplemento_promedioxpoliza',
df['hist_mov_poliza_otro_suplemento_count'] /
df['hist_mov_poliza_otro_count'])
# ULTIMO MOVIMIENTO: We group the policy last movement by ANULACION, CARTERA, REGULARIZACION or SUPLMENTO
dict_replace = {
'ANULACION': 'ANULACION',
'CARTERA': 'CARTERA',
'REGULARIZACION': 'REGULARIZACION',
'SUPLEMENTO': 'SUPLEMENTO'
}
func = udf(
lambda x: f.replace_dict_starswith(x, key_values=dict_replace),
StringType())
df = df.withColumn('hist_poliz_ultimo_movimiento',
func(df['hist_poliz_ultimo_movimiento']))
# VARIABLES CATEGÓRICAS: We get dummies from categorical variables
variable_categorica = [
"hist_poliz_ultimo_movimiento", "hist_poliza_estado"
]
variable_dummy = []
for col in variable_categorica:
types = df.select(col).distinct().collect()
types = [ty[col] for ty in types]
types_list = [
when(df[col] == ty,
1).otherwise(0).alias('d_' + col + '_' + ty)
for ty in types if ty is not None
]
variable_dummy += [
'd_' + col + '_' + ty for ty in types if ty is not None
]
df = df.select(list(df.columns) + types_list)
variable_dummy += ['hist_poliza_sospechoso']
for col in variable_dummy:
name = col + '_count'
df = df.fillna({col: 0})
df = df.withColumn(name, sum_(df[col].cast(IntegerType())).over(w))
# FECHAS
# 1) Inicio-Efecto:
# Efecto Natural de la Póliza - Fecha Inicio Póliza
df = df.withColumn(
'hist_mov_poliza_otro_dif_inicio_efecto',
datediff('hist_poliza_fecha_efecto_natural',
'hist_poliza_fecha_inicio'))
df = df.withColumn(
'hist_mov_poliza_otro_dif_inicio_efecto_negativo',
when(df['hist_mov_poliza_otro_dif_inicio_efecto'] < 0,
1).otherwise(0))
df = df.withColumn(
'hist_mov_poliza_otro_dif_inicio_efecto_negativo',
sum_(
df['hist_mov_poliza_otro_dif_inicio_efecto_negativo']).over(w))
# Acumulamos la diferencia efecto-inicio, sacamos el promedio por siniestro de la póliza
df = df.withColumn(
'hist_mov_poliza_otro_dif_inicio_efecto_sum',
sum_(df['hist_mov_poliza_otro_dif_inicio_efecto']).over(w))
df = df.withColumn(
'hist_mov_poliza_otro_dif_inicio_efecto_promedio',
df['hist_mov_poliza_otro_dif_inicio_efecto_sum'] /
df['hist_mov_poliza_otro_count'])
# 2) Efecto- Efecto vto.
# Vencimiento Natural - Efecto Natural de la Póliza
df = df.withColumn(
'hist_mov_poliza_otro_dif_efecto_vto',
datediff('hist_poliza_vto_natural',
'hist_poliza_fecha_efecto_natural'))
# Acumulamos vto-efecto y obtenemos el promedio por siniestro de la póliza
df = df.withColumn(
'hist_mov_poliza_otro_dif_efecto_vto_promedio',
sum_(df['hist_mov_poliza_otro_dif_efecto_vto']).over(w))
df = df.withColumn(
'hist_mov_poliza_otro_dif_efecto_vto_promedio',
df['hist_mov_poliza_otro_dif_efecto_vto_promedio'] /
df['hist_mov_poliza_otro_count'])
# VARIABLES INT: For the INT variables we acumulate and obtain the average by sinister.
variables_int = ["hist_poliza_numero_siniestros"]
for col in variables_int:
count = col + '_count'
promedio = col + '_promedio'
df = df.withColumn(count,
sum_(df[col].cast(IntegerType())).over(w))
df = df.withColumn(promedio,
df[count] / df['hist_mov_poliza_otro_count'])
# CARGA SINIESTRAL
df = df.withColumnRenamed('coste_del_siniestro_por_rol',
'hist_poliza_carga_siniestral')
df = df.fillna({'hist_poliza_carga_siniestral': 0})
df = df.withColumn(
'hist_poliza_carga_siniestral',
df['hist_poliza_carga_siniestral'].cast(FloatType()))
# VARIABLES FLOAT:
variables_float = [
"hist_poliza_carga_siniestral", "hist_poliza_recibos_pagos_importe"
]
# First, before group we calculate outliers. We do it before because we want to count the outliers sinister
# by sinister. If we first group, we lost the intra-effect of the sinister
for col in variables_float:
df = df.withColumn(col, df[col].cast(FloatType()))
df = outliers.Outliers.outliers_mad(df, col, not_count_zero=True)
count = col + '_count'
promedio = col + '_promedio'
name_outlier = str(col) + '_mad_outlier'
count_outlier = name_outlier + '_count'
promedio_outlier = name_outlier + '_promedio'
df = df.withColumn(count_outlier, sum_(df[name_outlier]).over(w))
df = df.withColumn(
promedio_outlier,
df[count_outlier] / df['hist_mov_poliza_otro_count'])
df = df.drop(name_outlier)
df = df.withColumn(count, sum_(df[col]).over(w))
df = df.withColumn(promedio,
df[count] / df['hist_mov_poliza_otro_count'])
df = df.dropDuplicates(subset=['id_siniestro'])
# VARIABLES DEL
variable_del = [
'id_fiscal', 'id_poliza', 'hist_id_producto',
'hist_poliza_version', 'hist_poliza_suplementos',
'hist_poliz_ultimo_movimiento',
'hist_poliz_motivo_ultimo_movimiento', 'hist_poliza_estado',
'hist_poliza_fecha_inicio', 'hist_poliza_fecha_efecto_natural',
'hist_poliza_fecha_efecto_mvto', 'hist_poliza_fecha_vto_mvto',
'hist_poliza_vto_natural', 'hist_poliza_numero_siniestros',
'hist_poliza_carga_siniestral',
'hist_poliza_recibos_pagos_importe', 'fecha_apertura_siniestro',
'pondera_poliza', 'Agrupación productos', 'Producto',
'hist_mov_poliza_otro_dif_inicio_efecto',
'hist_mov_poliza_otro_dif_efecto_vto', 'cliente_codfiliacion'
]
df = df.drop(*variable_del)
if self._is_diario:
df = df.filter(df['TEST'] == 1)
df = df.drop('TEST')
return df
def _transform_data(self, df, df_base, bl_processed):
"""Transform original dataset.
:param df: Input DataFrame.
:param bl_processed
:return: Transformed DataFrame.
"""
if self._is_diario:
df = df.withColumn('TEST', lit(1))
df_base = df_base.withColumn('TEST', lit(0))
df = df.union(df_base)
# Cast key variables and rename headers
exprs = [
df[column].alias(column.replace('"', '')) for column in df.columns
]
df = df.select(*exprs)
exprs = [
df[column].alias(column.replace(' ', '')) for column in df.columns
]
df = df.select(*exprs)
df = df.withColumnRenamed('hist_siniestro_poliza_otro_id_siniestro',
'id_siniestro')
df = df.withColumnRenamed('auditCodigoSiniestroReferencia',
'id_siniestro_ref')
df = df.withColumn('id_siniestro_ref',
df.id_siniestro_ref.cast(IntegerType()))
df = df.withColumn('id_siniestro', df.id_siniestro.cast(IntegerType()))
df = df.dropna(subset=['id_siniestro_ref'])
df = df.dropna(subset=['id_siniestro'])
# DATE VARIABLES FORMAT
fecha_variables = [
"hist_siniestro_poliza_otro_fecha_ocurrencia",
"hist_siniestro_poliza_otro_fecha_terminado",
"auditFechaAperturaSiniestroReferencia"
]
func = udf(lambda x: datetime.datetime.strptime(x, '%Y/%m/%d'),
DateType())
for col in fecha_variables:
df = df.fillna({col: '1900/01/01'})
df = df.withColumn(col, func(df[col]))
df = df.withColumn(
col,
when(df[col] == '1900-01-01', None).otherwise(df[col]))
df = df.filter(df[col] <= time.strftime('%Y-%m-%d'))
# We check that the sinister in the other policy is before the reference sinister, because we want to know the
# past values
df = df.filter(df['auditFechaAperturaSiniestroReferencia'] >=
df['hist_siniestro_poliza_otro_fecha_ocurrencia'])
# COUNT POLIZA-VERSION: We count how many sinisters before have the costumer. It counts how many times appear a
# row in the table, because each line is referred to a unique sinister
df = df.withColumn('hist_sin_poliza_otro_count_version', lit(1))
w = (Window().partitionBy(df.id_siniestro_ref).rowsBetween(
-sys.maxsize, sys.maxsize))
df = df.withColumn(
'hist_sin_poliza_otro_count',
count_(df.hist_sin_poliza_otro_count_version).over(w))
# COUNT POLIZAS: We count how many policies has the customer. We have to construct another table so we can
# group at the level of policies.
count_poliza = df.select(
['id_siniestro_ref', 'hist_siniestro_poliza_otro_id_poliza'])
count_poliza = count_poliza.dropDuplicates()
count_poliza = count_poliza.withColumnRenamed(
'hist_siniestro_poliza_otro_id_poliza',
'hist_sin_poliza_otro_count_polizas')
count_poliza = count_poliza.withColumn(
'hist_sin_poliza_otro_count_polizas',
count_(df['id_siniestro_ref']).over(w))
count_poliza = count_poliza.dropDuplicates(subset=['id_siniestro_ref'])
df = df.join(count_poliza, on='id_siniestro_ref', how='left')
# SINIESTROS/POLIZAS: Here we calculate the ratio nºsinisters/nº policies
df = df.withColumn(
'hist_siniestro_poliza_otro_siniestros_polizas',
df['hist_sin_poliza_otro_count'] /
df['hist_sin_poliza_otro_count_polizas'])
# FUE UN SINIESTRO FRAUDULENTO? We check if the id_siniestro is associated with a previous Fraud Sinister
bl_processed = bl_processed.select('id_siniestro').dropDuplicates(
subset=['id_siniestro'])
bl_processed = bl_processed.withColumn('hist_sin_poliza_otro_fraude',
lit(1))
df = df.join(bl_processed, on='id_siniestro', how='left')
df = df.withColumn(
'hist_sin_poliza_otro_fraude',
when(df['hist_sin_poliza_otro_fraude'].isNull(),
0).otherwise(df['hist_sin_poliza_otro_fraude']))
# POR PRODUCTO: We group the product number by predefined categories in tabla_productos. It permits a better
# classification. Here we have to pre-process the product label format to have coincidence.
types = df.select(
'hist_siniestro_poliza_otro_id_producto').distinct().collect()
types = [ty['hist_siniestro_poliza_otro_id_producto'] for ty in types]
types_list = [
when(df['hist_siniestro_poliza_otro_id_producto'] == ty,
1).otherwise(0).alias('d_hist_sin_poliza_otro_producto_' + ty)
for ty in types
]
df = df.select(list(df.columns) + types_list)
df.drop('hist_siniestro_poliza_otro_id_producto')
# DUMMIES: We acumulate the dummy variables to get the variables at cod_filiacion level
types = ['d_hist_sin_poliza_otro_producto_' + x for x in types]
var_dummies = [
"hist_siniestro_poliza_otro_bbdd",
"hist_siniestro_poliza_otro_unidad_investigacion",
"hist_siniestro_poliza_otro_incidencia_tecnica",
"hist_siniestro_poliza_otro_incidencia_tecnica_positiva",
"hist_siniestro_poliza_otro_incidencias",
"hist_siniestro_poliza_otro_cobertura"
] + types
for col in var_dummies:
df = df.withColumn(col + '_count', sum_(df[col]).over(w))
df = df.drop(col)
# FECHAS: We have two dates. fecha_ocurrencia and fecha_terminado. We have to take into account claims
# that are not finished. If the claim is notfinished we input today as date
# and create a variable that indicates the situation.
df = df.withColumn(
'hist_siniestro_poliza_otro_no_terminado',
when(df['hist_siniestro_poliza_otro_fecha_terminado'].isNull(),
1).otherwise(0))
df = df.fillna({
'hist_siniestro_poliza_otro_fecha_terminado':
time.strftime('%Y-%m-%d')
})
# Claim duration: We calculate the cumulated duration and the average duration.
df = df.withColumn(
'hist_poliza_otro_fecha_apertura_terminado',
datediff('hist_siniestro_poliza_otro_fecha_terminado',
'hist_siniestro_poliza_otro_fecha_ocurrencia'))
df = df.withColumn(
'hist_poliza_otro_fecha_apertura_terminado',
sum_(df['hist_poliza_otro_fecha_apertura_terminado']).over(w))
df = df.withColumn(
'hist_poliza_otro_duracion_promedio_sin',
df['hist_poliza_otro_fecha_apertura_terminado'] /
df['hist_sin_poliza_otro_count'])
# ULTIMO SINIESTRO DE LA POLIZA
df = df.withColumnRenamed(
'hist_siniestro_poliza_otro_fecha_ocurrencia',
'hist_siniestro_poliza_otro_ultimo_fecha_ocurrencia')
df = df.orderBy('hist_siniestro_poliza_otro_ultimo_fecha_ocurrencia',
ascending=False)
# CARGA SINIESTRAL
# Outlier: First we calculate the outliers quantity by cliente-sinister so we can get the intra-effect
df = df.withColumnRenamed(
'coste_del_siniestro_por_rol',
'hist_siniestro_poliza_otro_carga_siniestral')
df = df.fillna({'hist_siniestro_poliza_otro_carga_siniestral': 0})
df = df.withColumn(
'hist_siniestro_poliza_otro_carga_siniestral',
df.hist_siniestro_poliza_otro_carga_siniestral.cast(FloatType()))
# Construimos el outlier a nivel siniestro: Luego hacemos la suma de los casos de outlier por id_siniestro_ref
df = outliers.Outliers.outliers_mad(
df,
'hist_siniestro_poliza_otro_carga_siniestral',
not_count_zero=True)
df = df.withColumn(
'hist_siniestro_poliza_otro_carga_siniestral_mad_outlier_count',
sum_(df['hist_siniestro_poliza_otro_carga_siniestral_mad_outlier']
).over(w))
df = df.withColumn(
'hist_siniestro_poliza_otro_carga_siniestral_mad_outlier_promedio',
df['hist_siniestro_poliza_otro_carga_siniestral_mad_outlier_count']
/ df['hist_sin_poliza_otro_count'])
df = df.drop('hist_siniestro_poliza_otro_carga_siniestral_mad_outlier')
# We calculate the sum and the average by sinister
df = df.withColumn(
'hist_siniestro_poliza_otro_carga_siniestral_count',
sum_(df['hist_siniestro_poliza_otro_carga_siniestral']).over(w))
df = df.withColumn(
'hist_siniestro_poliza_otro_carga_siniestral_promedio',
df['hist_siniestro_poliza_otro_carga_siniestral_count'] /
df['hist_sin_poliza_otro_count'])
# COBERTURAS
# mayor a 3: we consider as outlier > 3, because the mean is concentrated around 1.28
df = df.withColumn(
'hist_sin_poliza_otro_mayor3coberturas',
when(df["hist_siniestro_poliza_otro_coberturas_involucradas"] > 3,
1).otherwise(0))
df = df.withColumn(
'hist_sin_poliza_otro_mayor3coberturas',
sum_(df['hist_sin_poliza_otro_mayor3coberturas']).over(w))
# promedio: Average by claim
df = df.withColumn(
'hist_sin_poliza_otro_cober_sum',
sum_(
df['hist_siniestro_poliza_otro_coberturas_involucradas']).over(
w))
df = df.withColumn(
'hist_sin_poliza_otro_cober_promedio',
df["hist_sin_poliza_otro_cober_sum"] /
df['hist_sin_poliza_otro_count'])
# pagas-cubiertas: We calculate this at the coustomer cumulated level and not to claim level
df = df.withColumn(
'hist_siniestro_poliza_otro_coberturas_involucradas_pagadas_sum',
sum_(df[
'hist_siniestro_poliza_otro_coberturas_involucradas_pagadas']).
over(w))
df = df.withColumn(
'hist_sin_poliza_otro_pagas_cubiertas',
df["hist_siniestro_poliza_otro_coberturas_involucradas_pagadas_sum"]
/ df['hist_sin_poliza_otro_cober_sum'])
# no pagas: Here we calculate at the claim level, counting the total unpaid coverages
df = df.withColumn(
'hist_sin_poliza_otro_cob_no_pagas',
when(
df['hist_siniestro_poliza_otro_coberturas_involucradas_pagadas']
== 0, 1).otherwise(0))
df = df.withColumn(
'hist_sin_poliza_otro_cob_no_pagas',
sum_(df['hist_sin_poliza_otro_cob_no_pagas']).over(w))
# DELETE VARIABLES: We delete variables that are not relevant or have been transformed
del_variables = [
'hist_siniestro_poliza_otro_id_poliza',
'hist_siniestro_poliza_otro_id_producto',
'hist_siniestro_poliza_otro_version',
'hist_siniestro_poliza_otro_id_siniestro',
'hist_siniestro_poliza_otro_fecha_terminado',
'hist_siniestro_poliza_otro_bbdd',
'hist_siniestro_poliza_otro_unidad_investigacion',
'hist_siniestro_poliza_otro_incidencia_tecnica',
'hist_siniestro_poliza_otro_incidencia_tecnica_positiva',
'hist_siniestro_poliza_otro_incidencias',
'hist_siniestro_poliza_otro_cobertura',
'hist_siniestro_poliza_otro_carga_siniestral',
'hist_siniestro_poliza_otro_coberturas_involucradas',
'hist_siniestro_poliza_otro_coberturas_involucradas_pagadas',
'id_fiscal', 'hist_sin_poliza_otro_count_version',
'Agrupación productos', 'Producto',
'auditFechaAperturaSiniestroReferencia', 'cliente_codfiliacion',
'audit_siniestro_codigo_compania', 'id_siniestro'
]
df = df.drop(*del_variables)
df = df.withColumnRenamed('id_siniestro_ref', 'id_siniestro')
df = df.dropDuplicates(subset=['id_siniestro'])
# OUTLIER: We calculate the outliers referred to the ratio claims/policies.
df = outliers.Outliers.outliers_mad(
df,
'hist_siniestro_poliza_otro_siniestros_polizas',
not_count_zero=False)
if self._is_diario:
df = df.filter(df['TEST'] == 1)
df = df.drop('TEST')
return df
def _transform(self, df, auxiliar_train):
if not self.train_file:
auxiliar_train = auxiliar_train.drop('WinningBid')
auxiliar_train = auxiliar_train.withColumn('test', lit(0))
df = df.withColumn('test', lit(1))
df = auxiliar_train.union(df)
del auxiliar_train
# We create the time as Index
split_col = split(df['ApproximateDate'], ' ')
df = df.withColumn('time', split_col.getItem(1)) # time
# Hour Index
func_index = udf(lambda x: auxiliar_func.time_to_num(x, index='hms'),
IntegerType())
df = df.withColumn('hms_index', func_index(df['time']))
# We order by UserId-Date
df = df.orderBy(['UserID', 'hms_index'])
# We check Null Values
df.select([count_(when(isnan(c), c)).alias(c)
for c in df.columns]).show()
# We create a rank of users by how many times in the past saw an ad
w = (Window().partitionBy(df.UserID).orderBy('time').rowsBetween(
Window.unboundedPreceding, 0))
df = df.withColumn('user_id_acumulative', count_(df['UserId']).over(w))
# Number of Ads/User/Second
df = df.withColumn('key_id',
concat(df['UserID'], lit(' '), df['hms_index']))
w = (Window().partitionBy(df.key_id).orderBy('hms_index').rowsBetween(
-sys.maxsize, sys.maxsize))
df = df.withColumn('number_ads_user_second', count_(df.key_id).over(w))
# Number of Ads/User
df_group = df.groupby(['key_id'
]).agg(count_('key_id').alias('count_ads'))
split_col = split(df_group['key_id'], ' ')
df_group = df_group.withColumn('UserID', split_col.getItem(0)) # time
w = (Window().partitionBy(
df_group.UserID).orderBy('key_id').rowsBetween(
Window.unboundedPreceding, 0))
df_group = df_group.withColumn('number_ads_user',
sum_(df_group.count_ads).over(w))
df_group = df_group.select(['key_id', 'number_ads_user'])
df = df.join(df_group, how='left', on='key_id')
del df_group
# Number of Users/Second
w = (Window().partitionBy(df.ApproximateDate).rowsBetween(
-sys.maxsize, sys.maxsize))
df = df.withColumn('number_user_second',
approx_count_distinct(df.UserID).over(w))
# Number of Ads/Second
df = df.withColumn('number_ads_second',
count_(df.ApproximateDate).over(w))
# Browser Dummy Transformation
types = df.select('Browser').distinct().collect()
types = [val['Browser'] for val in types]
new_cols = [
when(df['Browser'] == ty, 1).otherwise(0).alias('d_browser_' + ty)
for ty in types
]
df = df.select(df.columns + new_cols)
# Decompose Date Variables
df = df.withColumn('date', to_date(df['ApproximateDate'])) # date
df = df.withColumn('month', month(df['ApproximateDate'])) # month
df = df.withColumn('day', dayofmonth(df['ApproximateDate'])) # day
df = df.withColumn('weekday', dayofweek(
df['ApproximateDate'])) # weekday 1=Monday
df = df.withColumn('hour', hour(df['time'])) # hour
df = df.withColumn('minute', minute(df['time'])) # minute
# Peak Hour
df = df.withColumn('peak6am8am',
when(df['hour'].between(6, 8), 1).otherwise(0))
df = df.withColumn('peak14pm16pm',
when(df['hour'].between(14, 16), 1).otherwise(0))
# Minute Index
func_index = udf(lambda x: auxiliar_func.time_to_num(x, index='hm'),
IntegerType())
df = df.withColumn('hm_index', func_index(df['time']))
# Convert to time-series by Minute
# We reduce to minutes
df_time_serie_ads = df.select([
'hms_index', 'hm_index', 'number_user_second', 'number_ads_second'
]).drop_duplicates()
df_time_serie_user = df.select(['UserID',
'hm_index']).drop_duplicates()
# Group-by the values
df_time_serie_user = df_time_serie_user.groupBy('hm_index').agg(
approx_count_distinct('UserID'))
df_time_serie_ads = df_time_serie_ads.groupBy('hm_index').agg({
'number_ads_second':
'sum'
}).drop_duplicates(subset=['hm_index'])
# Join ads-users per minute
df_time_serie = df_time_serie_ads.join(df_time_serie_user,
how='left',
on='hm_index')
del df_time_serie_ads, df_time_serie_user
# Rename columns
df_time_serie = df_time_serie.withColumnRenamed(
'sum(number_ads_second)', 'number_ads_minute').withColumnRenamed(
'approx_count_distinct(UserID)', 'number_user_minute')
# Resample Range of Minutes
resample_range = list(
range(
df_time_serie.select(min_(
col('hm_index'))).limit(1).collect()[0][0],
df_time_serie.select(max_(
col('hm_index'))).limit(1).collect()[0][0] + 1, 1))
resample_range = self._spark.createDataFrame(resample_range,
IntegerType())
# Join the original df
df_time_serie = resample_range.join(
df_time_serie,
how='left',
on=resample_range.value == df_time_serie.hm_index).drop(
*['hm_index']).fillna(0)
# Create Lags By Minutes
w = Window().partitionBy().orderBy(col('value'))
if self.ar_min_lag > 0:
df_time_serie = df_time_serie.select(
'*',
lag('number_user_minute').over(w).alias(
'ar1_number_user_minute'))
df_time_serie = df_time_serie.select(
'*',
lag('number_ads_minute').over(w).alias(
'ar1_number_ads_minute'))
if self.ar_min_lag > 1:
for l in range(2, self.ar_min_lag + 1, 1):
df_time_serie = df_time_serie.select(
'*',
lag('ar' + str(l - 1) + '_number_user_minute').over(
w).alias('ar' + str(l) + '_number_user_minute'))
df_time_serie = df_time_serie.select(
'*',
lag('ar' + str(l - 1) + '_number_ads_minute').over(
w).alias('ar' + str(l) + '_number_ads_minute'))
# Remove the lagged Null Values
df_time_serie = df_time_serie.dropna()
# join and remove lag Null values of the first minute
df = df.orderBy(['UserID', 'hms_index'])
df = df.join(df_time_serie.orderBy(['hm_index']),
how='left',
on=df.hm_index == df_time_serie.value).drop('value')
# Convert to time-series and resample by Seconds
df_time_serie = df.select(
['hms_index', 'number_user_second',
'number_ads_second']).drop_duplicates()
resample_range = list(
range(
df_time_serie.select(min_(
col('hms_index'))).limit(1).collect()[0][0],
df_time_serie.select(max_(
col('hms_index'))).limit(1).collect()[0][0] + 1, 1))
resample_range = self._spark.createDataFrame(resample_range,
IntegerType())
# Join the original df
df_time_serie = resample_range.join(
df_time_serie,
how='left',
on=resample_range.value == df_time_serie.hms_index).drop(
*['hms_index']).fillna(0)
# Create lags
w = Window().partitionBy().orderBy(col('value'))
if self.ar_lags > 0:
df_time_serie = df_time_serie.select(
'*',
lag('number_user_second').over(w).alias(
'ar1_number_user_second'))
df_time_serie = df_time_serie.select(
'*',
lag('number_ads_second').over(w).alias(
'ar1_number_ads_second'))
if self.ar_lags > 1:
for l in range(2, self.ar_lags + 1, 1):
df_time_serie = df_time_serie.select(
'*',
lag('ar' + str(l - 1) + '_number_user_second').over(
w).alias('ar' + str(l) + '_number_user_second'))
df_time_serie = df_time_serie.select(
'*',
lag('ar' + str(l - 1) + '_number_ads_second').over(
w).alias('ar' + str(l) + '_number_ads_second'))
# Create Moving Average
if self.ma_ss_lag is not None:
# Get hour from index
func_index = udf(lambda x: auxiliar_func.num_to_time(x),
StringType())
df_time_serie = df_time_serie.withColumn(
'time', func_index(df_time_serie['value']))
# minute MA terms (Average per second last xx seconds)
if self.ma_ss_lag is not None:
for lag_val in self.ma_ss_lag:
# range to take into account
w = (Window.orderBy(df_time_serie['value']).rangeBetween(
-lag_val, 0))
# MA variables
df_time_serie = df_time_serie.withColumn(
'ma_seconds_' + str(lag_val) + '_number_user_second',
avg('number_user_second').over(w))
df_time_serie = df_time_serie.withColumn(
'ma_seconds_' + str(lag_val) + '_number_ads_second',
avg('number_ads_second').over(w))
# Increasing ID
df_time_serie = df_time_serie.withColumn(
'rn', monotonically_increasing_id())
# Replace first values by Null
df_time_serie = df_time_serie.withColumn(
'ma_seconds_' + str(lag_val) + '_number_user_second',
when(df_time_serie['rn'] < lag_val, None).otherwise(
df_time_serie['ma_seconds_' + str(lag_val) +
'_number_user_second']))
df_time_serie = df_time_serie.withColumn(
'ma_seconds_' + str(lag_val) + '_number_ads_second',
when(df_time_serie['rn'] < lag_val, None).otherwise(
df_time_serie['ma_seconds_' + str(lag_val) +
'_number_ads_second']))
# Get the average by Minute
df_time_serie = df_time_serie.withColumn(
'ma_minute_' + str(lag_val) + '_number_user_second',
df_time_serie['ma_seconds_' + str(lag_val) +
'_number_user_second'] * 60)
df_time_serie = df_time_serie.withColumn(
'ma_minute_' + str(lag_val) + '_number_ads_second',
df_time_serie['ma_seconds_' + str(lag_val) +
'_number_ads_second'] * 60)
df_time_serie = df_time_serie.drop(*['rn'])
# Remove the lagged Null Values
df_time_serie = df_time_serie.drop(
*['time', 'number_user_second', 'number_ads_second']).dropna()
# join and remove lag Null values of the first minute
df = df.join(
df_time_serie.orderBy(['value']),
how='left',
on=df.hms_index == df_time_serie.value).drop('value').dropna()
if self.train_file and not self.variable_analysis:
df = df.select([
'key_id', 'hms_index', 'number_ads_user', 'number_user_second',
'number_ads_second', 'number_ads_user_second', 'peak6am8am',
'peak14pm16pm', 'user_id_acumulative'
] + [x for x in df.columns if x.startswith('d_browser')] +
[x for x in df.columns if x.startswith('ar')] +
[x for x in df.columns if x.startswith('ma_')] +
['WinningBid'])
if not self.train_file:
df = df.filter(df['test'] == 1)
df = df.select([
'UserID', 'key_id', 'number_ads_user', 'hms_index',
'number_user_second', 'number_ads_second',
'number_ads_user_second', 'peak6am8am', 'peak14pm16pm',
'user_id_acumulative'
] + [x for x in df.columns if x.startswith('d_browser')] +
[x for x in df.columns if x.startswith('ar')] +
[x for x in df.columns if x.startswith('ma_')])
df = df.orderBy(['hms_index', 'UserID'])
df.show()
return df
def transform_data(self, df, bl_processed, peritos_aux, perito_nif_aux,
reparador_aux):
"""Transform original dataset.
:param df: Input DataFrame.
:return: Transformed DataFrame.
"""
# Cast key variables and rename headers
df = df.withColumnRenamed('auditCodigoSiniestroReferencia',
'id_siniestro')
df = df.withColumn('id_siniestro', df.id_siniestro.cast(IntegerType()))
df = df.withColumn(
'peritaje_transferencia_IBAN',
regexp_replace('peritaje_transferencia_IBAN', ' ', ''))
df = df.fillna({'peritaje_transferencia_IBAN': 0})
df = df.withColumn('peritaje_transferencia_IBAN',
df['peritaje_transferencia_IBAN'].cast('float'))
df = df.withColumn('peritaje_transferencia_nif',
df['peritaje_transferencia_nif'].cast('string'))
df = df.withColumn('peritaje_codigo',
df['peritaje_codigo'].cast(IntegerType()))
# VARIABLES DE FECHA
fecha_variables = ['fecha_encargo_peritaje', 'peritaje_fecha_informe']
func = udf(lambda x: datetime.datetime.strptime(x, '%Y/%m/%d'),
DateType())
for col in fecha_variables:
df = df.fillna({col: '1900/01/01'})
df = df.withColumn(col, func(df[col]))
df = df.withColumn(
col,
when(df[col] == '1900-01-01', None).otherwise(df[col]))
# Sort Values by Perito-Visita
df = df.orderBy(['id_siniestro', 'peritaje_codigo'],
ascending=[True, True])
# INFORME DEFINITIVO exist: First we count the null informe_definitivo. Then we cumulate the values.
# Finally, we replace positive values with one, because we just want to identify.
df = df.withColumn(
'peritaje_no_informe_definitivo',
when(df['peritaje_fecha_informe'].isNull(), 1).otherwise(0))
w = (Window().partitionBy(df.id_siniestro).rowsBetween(
-sys.maxsize, sys.maxsize))
df = df.withColumn('peritaje_no_informe_definitivo',
sum_(df.peritaje_no_informe_definitivo).over(w))
df = df.withColumn(
'peritaje_no_informe_definitivo',
when(df['peritaje_no_informe_definitivo'] > 0, 1).otherwise(0))
# COUNT PERITAJES: We count the adjust-loss visits using the number of times is repeated a sinister.
df = df.withColumn('peritaje_pondera', lit(1))
df = df.withColumn('peritaje_count', sum_(df.peritaje_pondera).over(w))
# FECHA PRIMER PERITAJE - ULTIMO PERITAJE: To each sinister we just keep the first date of the adjust-loss
# visit and the last date.
# First Date: We create a table that only keep the sinister and the first date. Then we remarge keeping the
# first value of each sinister
fecha_primer_df = df.select(['id_siniestro', 'fecha_encargo_peritaje'])
fecha_primer_df = fecha_primer_df.orderBy(
['id_siniestro', 'fecha_encargo_peritaje'],
ascending=[True, False])
fecha_primer_df = fecha_primer_df.dropDuplicates(
subset=['id_siniestro'])
fecha_primer_df = fecha_primer_df.withColumnRenamed(
'fecha_encargo_peritaje', 'fecha_primer_peritaje')
df = df.join(fecha_primer_df, on='id_siniestro', how='left')
del fecha_primer_df
fecha_ultimo_df = df.select(['id_siniestro', 'peritaje_fecha_informe'])
fecha_ultimo_df = fecha_ultimo_df.orderBy(
['id_siniestro', 'peritaje_fecha_informe'],
ascending=[True, False])
fecha_ultimo_df = fecha_ultimo_df.dropDuplicates(
subset=['id_siniestro'])
fecha_ultimo_df = fecha_ultimo_df.withColumnRenamed(
'peritaje_fecha_informe', 'fecha_ultimo_informe')
df = df.join(fecha_ultimo_df, on='id_siniestro', how='left')
del fecha_ultimo_df
# TIEMPO ENTRE PERITAJES: fecha_ultimo_informe - fecha_primer_peritaje
df = df.withColumn(
'peritaje_duracion',
datediff(df['fecha_ultimo_informe'], df['fecha_primer_peritaje']))
# DURACION PROMEDIO PERITAJE: We calculate the duration time average by number of visits in each sinister
df = df.withColumn('peritaje_duracion_promedio',
df['peritaje_duracion'] / df['peritaje_count'])
# FECHA_INFORME EXTENDIDA: We convert fecha_informe_encargo_extendida into two values S = 1, N = 0
df = df.withColumn(
'fecha_informe_encargo_extendida',
when(df['fecha_informe_encargo_extendida'] == 'S', 1).otherwise(0))
df = df.withColumn('fecha_informe_encargo_extendida',
sum_(df.fecha_informe_encargo_extendida).over(w))
# CATEGORICAL VAR: Using dummy variables, we generate the cumsum and the average of each value. 'count' will
# count the total value summing each survey visit value. 'promedio' will take the average of surveys in
# each sinister
var_dummies = [
'peritaje_pregunta_1', 'peritaje_pregunta_2',
'peritaje_pregunta_3', 'peritaje_pregunta_4',
'peritaje_pregunta_5', 'peritaje_pregunta_6a',
'peritaje_pregunta_7', 'peritaje_pregunta_8a',
'peritaje_pregunta_9', 'peritaje_pregunta_10',
'peritaje_pregunta_11', 'peritaje_pregunta_12',
'peritaje_pregunta_13', 'peritaje_pregunta_14',
'peritaje_pregunta_15', 'peritaje_pregunta_16',
'peritaje_pregunta_17', 'peritaje_pregunta_18',
'peritaje_pregunta_19', 'peritaje_pregunta_20',
'peritaje_negativo', 'peritaje_posible_fraude',
'peritaje_negativos_perito'
]
for col in var_dummies:
# We generate a table dummy so we can drop the nan values and therefore we do not take into account
# this values on sum and average value
dummie_sum = df.select(['id_siniestro', col])
# We drop the nan values in each loss adjuster visit
dummie_sum = dummie_sum.dropna(subset=[col])
# Now we count how many surveys were made
dummie_sum = dummie_sum.withColumn('peritaje_pondera', lit(1))
w_dummy = (Window().partitionBy(
dummie_sum.id_siniestro).rowsBetween(-sys.maxsize,
sys.maxsize))
dummie_sum = dummie_sum.withColumn(
'peritaje_count',
sum_(dummie_sum.peritaje_pondera).over(w_dummy))
# We take the total sum and the average
name = col + '_count'
promedio = col + '_promedio'
if col != 'peritaje_negativos_perito':
dummie_sum = dummie_sum.withColumn(
col, dummie_sum[col].cast(IntegerType()))
else:
dummie_sum = dummie_sum.withColumn(
col, dummie_sum[col].cast('float'))
dummie_sum = dummie_sum.withColumn(
name,
sum_(dummie_sum[col]).over(w_dummy))
dummie_sum = dummie_sum.withColumn(
promedio, dummie_sum[name] / dummie_sum['peritaje_count'])
# We delete peritaje_count and pondera so we do not have problem with the merge
dummie_sum = dummie_sum.drop(
*[col, 'peritaje_count', 'peritaje_pondera'])
# We drop the duplicates values in each siniester and keep the last which is the value that cumsum the total
# values
dummie_sum = dummie_sum.dropDuplicates(subset=['id_siniestro'])
# We merge the table with the original dataframe
df = df.join(dummie_sum, on='id_siniestro', how='left')
del dummie_sum
# PERITAJE_NEGATIVO: If peritaje_negativo_count > 0 => peritaje_negativo = 1
df = df.withColumn(
'peritaje_negativo',
when(df['peritaje_negativo_count'] > 0, 1).otherwise(0))
# PERITAJE NEGATIVO PERITO: We made delete the sum count, we just need the average
df = df.drop('peritaje_negativos_perito_count')
# IBAN DE PERITO: We check if the adjust losser's IBAN is registred in the blacklist
bl_processed_iban = bl_processed.filter(~(
(bl_processed['iban'].isNull()) | (bl_processed['iban'] == '?')))
bl_processed_iban = bl_processed_iban.select('iban')
bl_processed_iban = bl_processed_iban.dropDuplicates(subset=['iban'])
df = df.join(bl_processed_iban,
df.peritaje_transferencia_IBAN == bl_processed_iban.iban,
how='left')
df = df.withColumn('peritaje_iban_blacklist',
when(df['iban'].isNull(), 0).otherwise(1))
# PERITAJE INDEM INFORME PREVIO: We sum the total values
df = df.withColumn('peritaje_indem_informe_previo',
df['peritaje_indem_informe_previo'].cast('float'))
df = df.withColumn('peritaje_indem_informe_previo_sum',
sum_(df['peritaje_indem_informe_previo']).over(w))
# PERIAJTE_INDEM_INFORME_DEFNITIVO: We sum the total values
df = df.withColumn(
'peritaje_indem_informe_definitivo',
df['peritaje_indem_informe_definitivo'].cast('float'))
df = df.withColumn(
'peritaje_indem_informe_definitivo_sum',
sum_(df['peritaje_indem_informe_definitivo']).over(w))
# DIFERENCIA DEFINITIVO-PREVIO
df = df.withColumn(
'peritaje_indem_previo_definitivo_sum',
df['peritaje_indem_informe_definitivo_sum'] -
df['peritaje_indem_informe_previo_sum'])
# COBERTURAS: valores extraños
df = df.withColumn(
'peritaje_coberturas_indemnizar_previo',
df['peritaje_coberturas_indemnizar_previo'].cast(IntegerType()))
df = df.withColumn(
'peritaje_coberturas_indemnizar_MIGRA',
when(df['peritaje_coberturas_indemnizar_previo'] > 7,
1).otherwise(0))
df = df.withColumn(
'peritaje_coberturas_indemnizar_previo',
when(df['peritaje_coberturas_indemnizar_previo'] > 10,
df['peritaje_coberturas_indemnizar_definitivo']).otherwise(
df['peritaje_coberturas_indemnizar_previo']))
# COBERTURAS INFORME PREVIO
df = df.withColumn(
'peritaje_coberturas_indemnizar_previo',
df['peritaje_coberturas_indemnizar_previo'].cast('float'))
df = df.withColumn(
'peritaje_coberturas_indemnizar_previo_sum',
sum_(df.peritaje_coberturas_indemnizar_previo).over(w))
df = df.withColumn(
'peritaje_coberturas_indemnizar_previo_promedio',
df['peritaje_coberturas_indemnizar_previo_sum'] /
df['peritaje_count'])
# COBERTURAS INFORME DEFINITIVO
df = df.withColumn(
'peritaje_coberturas_indemnizar_definitivo',
df['peritaje_coberturas_indemnizar_definitivo'].cast('float'))
df = df.withColumn(
'peritaje_coberturas_indemnizar_definitivo_sum',
sum_(df.peritaje_coberturas_indemnizar_definitivo).over(w))
df = df.withColumn(
'peritaje_coberturas_indemnizar_definitivo_promedio',
df['peritaje_coberturas_indemnizar_definitivo_sum'] /
df['peritaje_count'])
# DIFERENCIA COBERTURAS DEFINITIVO-PREVIO
df = df.withColumn(
'peritaje_cobertura_previo_definitivo_sum',
df['peritaje_coberturas_indemnizar_definitivo_sum'] -
df['peritaje_coberturas_indemnizar_previo_sum'])
# PORCENTUAL VARIATION INDEM-COBERTURAS: Here we make the difference betweeen the last visit. So we do not have
# to acumulate the values. We take the variation and, at the end, we keep the last row. We have to normalize to
# avoid the zero division problem. For COBERTURAS, due it is an int value we can get the % value for zero
# division with the diff. However, for INDEM, it can generate extra large values, so we left it in Zero.
funct_indem = udf(
lambda x_definitivo, x_previo: x_definitivo / x_previo - 1
if x_previo != 0 else 0., FloatType())
funct_garan = udf(
lambda x_definitivo, x_previo, x_final_inicial: x_definitivo /
x_previo - 1 if x_previo != 0 else x_final_inicial, FloatType())
df = df.withColumn(
'peritaje_indem_final_inicial',
funct_indem(df.peritaje_indem_informe_definitivo,
df.peritaje_indem_informe_previo))
df = df.withColumn(
'peritaje_garantia_final_inicial',
funct_garan(df.peritaje_coberturas_indemnizar_definitivo,
df.peritaje_coberturas_indemnizar_previo,
df.peritaje_garantia_final_inicial))
df = df.withColumn(
'peritaje_indem_final_inicial',
funct_garan(df.peritaje_coberturas_indemnizar_definitivo,
df.peritaje_coberturas_indemnizar_previo,
df.peritaje_garantia_final_inicial))
# PERITO EN BLACKLIST POR NIF: We count how many times the loss-adjuster NIF appears in the historic blacklist
bl_processed = bl_processed.select('nif_o_intm')
bl_processed = bl_processed.dropDuplicates(subset=['nif_o_intm'])
df = df.join(bl_processed,
df.peritaje_transferencia_nif == bl_processed.nif_o_intm,
how='left')
df = df.withColumn('perito_fraud_claims',
when(df['nif_o_intm'].isNull(), 0).otherwise(1))
perito_base = df.select([
'id_siniestro', 'peritaje_nombre', 'peritaje_transferencia_nif',
'peritaje_aide_nombre_reparador', 'peritaje_pondera',
'peritaje_negativo', 'peritaje_indem_informe_definitivo',
'peritaje_coberturas_indemnizar_definitivo'
])
df = df.dropDuplicates(subset='id_siniestro')
# TABLA DE PERITOS BY AGRUPACION: Some statistics about the loss-adjuster only for the current DB
if self._is_prediction:
df = df.join(peritos_aux, on='peritaje_nombre', how='left')
peritos_cols = list(peritos_aux.columns)
peritos_cols.remove('peritaje_nombre')
for col in peritos_cols:
df = df.fillna({col: 0})
del peritos_cols
else:
peritos = perito_base.select([
'id_siniestro', 'peritaje_nombre', 'peritaje_pondera',
'peritaje_negativo', 'peritaje_indem_informe_definitivo',
'peritaje_coberturas_indemnizar_definitivo'
])
# paso 1: Eliminar NaN
peritos = peritos.dropna(subset='peritaje_nombre')
# How many visits by PERITAJE_NOMBRE
w_peritos = (Window().partitionBy(
peritos.peritaje_nombre).rowsBetween(-sys.maxsize,
sys.maxsize))
peritos = peritos.withColumn(
'peritaje_count_visitasxperito',
sum_(df.peritaje_pondera).over(w_peritos))
# How many negative claims by PERITAJE_NOMBRE
peritos_temp = peritos.filter(
~peritos['peritaje_negativo'].isNull())
peritos_temp = peritos_temp.withColumn(
'peritaje_negativo',
df['peritaje_negativo'].cast(IntegerType()))
w_peritos_temp = (
Window().partitionBy('peritaje_nombre').rowsBetween(
-sys.maxsize, sys.maxsize))
peritos_temp = peritos_temp.withColumn(
'peritaje_count_negativoxperito',
sum_(df.peritaje_negativo).over(w_peritos_temp))
peritos_temp = peritos_temp.fillna(
{'peritaje_count_negativoxperito': 0})
peritos_temp = peritos_temp.select(
['peritaje_nombre', 'peritaje_count_negativoxperito'])
peritos_temp = peritos_temp.dropDuplicates(
subset='peritaje_nombre')
peritos = peritos.join(peritos_temp,
on='peritaje_nombre',
how='left')
del peritos_temp, w_peritos_temp
peritos = peritos.drop('peritaje_negativo')
# Average of Negative Surveys by PERITAJE_NOMBRE
peritos = peritos.withColumn(
'peritaje_perito_porc_negativos',
peritos['peritaje_count_negativoxperito'] /
peritos['peritaje_count_visitasxperito'])
# Amount of Indem definitiva by PERITAJE_NOMBRE
peritos = peritos.withColumn(
'peritaje_sum_indem_definitivaxperito',
sum_('peritaje_indem_informe_definitivo').over(w_peritos))
peritos = peritos.drop('peritaje_indem_informe_definitivo')
# Amount of Coberturas by PERITAJE_ NOMBRE
peritos = peritos.withColumn(
'peritaje_sum_coberturaxperito',
sum_('peritaje_coberturas_indemnizar_definitivo').over(
w_peritos))
peritos = peritos.drop('peritaje_coberturas_indemnizar_definitivo')
# paso 2: Mantenemos el último siniestro para poder agrupar el fraude por siniestro
peritos = peritos.dropDuplicates(
subset=['id_siniestro', 'peritaje_nombre'])
# How many claims by PERITAJE_NOMBRE
w_peritos = (Window().partitionBy(
peritos.peritaje_nombre).rowsBetween(-sys.maxsize,
sys.maxsize))
peritos = peritos.withColumn(
'peritaje_count_siniestrosxperito',
sum_(df.peritaje_pondera).over(w_peritos))
peritos = peritos.drop('peritaje_pondera')
# Average of Indem definitiva by PERITAJE_NOMBRE
peritos = peritos.withColumn(
'peritaje_promedio_indem_definitivaxperito',
peritos['peritaje_sum_indem_definitivaxperito'] /
peritos['peritaje_count_siniestrosxperito'])
# We merge with the known cases of Fraud
file_blacklist_resume = bl_processed.select('id_siniestro')
file_blacklist_resume = file_blacklist_resume.dropDuplicates(
subset='id_siniestro')
file_blacklist_resume = file_blacklist_resume.withColumn(
'peritos_fraud', lit(1))
peritos = peritos.join(file_blacklist_resume,
on='id_siniestro',
how='left')
peritos = peritos.fillna({'peritos_fraud': 0})
# How many fraud sinister by PERITAJE_NOMBRE
peritos = peritos.withColumn(
'peritaje_count_FRAUDxperito',
sum_(peritos['peritos_fraud']).over(w_peritos))
# Average fraud sinister by PERITAJE_NOMBRE
peritos = peritos.withColumn(
'peritaje_promedio_FRAUDxsiniestroxperito',
peritos['peritaje_count_FRAUDxperito'] /
peritos['peritaje_count_siniestrosxperito'])
# We get the values by PERITAJE_NOMBRE and calculate
# paso 3: mantenemos solo al perito
peritos_temp = peritos.dropDuplicates(subset=['peritaje_nombre'])
peritos_temp = outliers.Outliers.outliers_mad(
peritos_temp,
'peritaje_promedio_FRAUDxsiniestroxperito',
not_count_zero=False)
peritos_temp = outliers.Outliers.outliers_mad(
peritos_temp,
'peritaje_promedio_indem_definitivaxperito',
not_count_zero=False)
peritos_temp = outliers.Outliers.outliers_mad(
peritos_temp,
'peritaje_sum_coberturaxperito',
not_count_zero=False)
peritos_temp = peritos_temp.drop('id_siniestro')
peritos = peritos.select(['id_siniestro', 'peritaje_nombre'])
peritos = peritos.join(peritos_temp,
on='peritaje_nombre',
how='left')
del peritos_temp
peritos = peritos.dropDuplicates(subset=['peritaje_nombre'])
peritos = peritos.drop('id_siniestro')
df = df.join(peritos, on='peritaje_nombre', how='left')
peritos_cols = list(peritos.columns)
peritos_cols.remove('peritaje_nombre')
for col in peritos_cols:
df = df.fillna({col: 0})
del peritos_cols
# TABLA DE PERITOS BY AGRUPACION: Some statistics about the loss-adjuster only for the current DB
if self._is_prediction:
df = df.join(perito_nif_aux,
how='left',
on='peritaje_transferencia_nif')
peritos_cols = perito_nif_aux.columns.values.tolist()
peritos_cols.remove('peritaje_transferencia_nif')
for col in peritos_cols:
df = df.fillna({col: 0})
del peritos_cols
else:
perito_nif = perito_base.select([
'id_siniestro', 'peritaje_transferencia_nif',
'peritaje_pondera', 'peritaje_negativo',
'peritaje_indem_informe_definitivo',
'peritaje_coberturas_indemnizar_definitivo'
])
# paso1: Eliminar NaN
perito_nif = perito_nif.dropna(subset='peritaje_transferencia_nif')
# How many visits by PERITAJE_NOMBRE
w_peritos = (Window().partitionBy(
perito_nif.peritaje_transferencia_nif).rowsBetween(
-sys.maxsize, sys.maxsize))
perito_nif = perito_nif.withColumn(
'peritaje_count_visitasxnif',
sum_(perito_nif['peritaje_pondera']).over(w_peritos))
# How many negative claims by PERITAJE_NIF
peritos_temp = perito_nif.filter(
~perito_nif['peritaje_negativo'].isNull())
w_peritos_temp = (Window().partitionBy(
peritos_temp.peritaje_transferencia_nif).rowsBetween(
-sys.maxsize, sys.maxsize))
peritos_temp = peritos_temp.withColumn(
'peritaje_negativo',
peritos_temp['peritaje_negativo'].cast(IntegerType()))
peritos_temp = peritos_temp.withColumn(
'peritaje_nif_count_negativoxperito',
sum_(peritos_temp['peritaje_negativo']).over(w_peritos_temp))
peritos_temp = peritos_temp.fillna(
{'peritaje_nif_count_negativoxperito': 0})
peritos_temp = peritos_temp.select([
'peritaje_transferencia_nif',
'peritaje_nif_count_negativoxperito'
])
peritos_temp = peritos_temp.dropDuplicates(
subset=['peritaje_transferencia_nif'])
perito_nif = perito_nif.join(peritos_temp,
on='peritaje_transferencia_nif',
how='left')
del peritos_temp, w_peritos_temp
perito_nif = perito_nif.drop('peritaje_negativo')
# Average of Negative Surveys by PERITAJE_NIF
perito_nif = perito_nif.withColumn(
'peritaje_nif_perito_porc_negativos',
perito_nif['peritaje_nif_count_negativoxperito'] /
perito_nif['peritaje_count_visitasxnif'])
# Amount of Indem definitiva by PERITAJE_NIF
perito_nif = perito_nif.withColumn(
'peritaje_indem_informe_definitivo',
perito_nif['peritaje_indem_informe_definitivo'].cast(
FloatType()))
perito_nif = perito_nif.withColumn(
'peritaje_nif_sum_indem_definitivaxperito',
sum_(perito_nif['peritaje_indem_informe_definitivo']).over(
w_peritos))
perito_nif = perito_nif.drop('peritaje_indem_informe_definitivo')
# Amount of Coberturas by PERITAJE_ NIF
perito_nif = perito_nif.withColumn(
'peritaje_nif_sum_coberturaxperito',
sum_(perito_nif['peritaje_coberturas_indemnizar_definitivo']).
over(w_peritos))
peritos_nif = perito_nif.drop(
'peritaje_coberturas_indemnizar_definitivo')
# paso 2: Mantenemos el último siniestro para poder agrupar el fraude por siniestro
# How many claims by PERITAJE_NIF
perito_nif = perito_nif.withColumn(
'peritaje_nif_count_siniestrosxperito',
sum_(perito_nif['peritaje_pondera']).over(w_peritos))
perito_nif = perito_nif.drop('peritaje_pondera')
# Average of Indem definitiva by PERITAJE_NIF
perito_nif = perito_nif.withColumn(
'peritaje_nif_promedio_indem_definitivaxperito',
perito_nif['peritaje_nif_sum_indem_definitivaxperito'] /
perito_nif['peritaje_nif_count_siniestrosxperito'])
# Average of Coberturas by PERITAJE_ NIF
perito_nif = perito_nif.withColumn(
'peritaje_nif_promedio_coberturaxperito',
perito_nif['peritaje_nif_sum_coberturaxperito'] /
perito_nif['peritaje_nif_count_siniestrosxperito'])
perito_nif = perito_nif.dropDuplicates(
subset=['id_siniestro', 'peritaje_transferencia_nif'])
# We merge with the known cases of Fraud
file_blacklist_resume = file_blacklist_resume.select(
'id_siniestro')
file_blacklist_resume = file_blacklist_resume.withColumn(
'perito_nif_fraud', lit(1))
perito_nif = perito_nif.join(file_blacklist_resume,
on='id_siniestro',
how='left')
perito_nif = perito_nif.fillna({'perito_nif_fraud': 0})
# How many fraud sinister by PERITAJE_NIF
perito_nif = perito_nif.withColumn(
'peritaje_nif_count_FRAUDxperito',
sum_(perito_nif['peritos_fraud']).over(w_peritos))
perito_nif = perito_nif.drop('peritos_fraud')
# Average fraud sinister by PERITAJE_NIF
perito_nif = perito_nif.withColumn(
'peritaje_nif_promedio_FRAUDxsiniestroxperito',
perito_nif['peritaje_nif_count_FRAUDxperito'] /
perito_nif['peritaje_nif_count_siniestrosxperito'])
# We get the values by PERITAJE_NIF and calculate
peritos_temp = perito_nif.dropDuplicates(
subset=['peritaje_transferencia_nif'])
peritos_temp = peritos_temp.drop('id_siniestro')
peritos_temp = outliers.Outliers.outliers_mad(
peritos_temp,
'peritaje_nif_promedio_FRAUDxsiniestroxperito',
not_count_zero=False)
peritos_temp = outliers.Outliers.outliers_mad(
peritos_temp,
'peritaje_nif_promedio_indem_definitivaxperito',
not_count_zero=False)
peritos_temp = outliers.Outliers.outliers_mad(
peritos_temp,
'peritaje_nif_sum_coberturaxperito',
not_count_zero=False)
peritos_nif = peritos_nif.select(
['id_siniestro', 'peritaje_transferencia_nif'])
peritos_nif = peritos_nif.join(peritos_temp,
on='peritaje_transferencia_nif',
how='left')
del peritos_temp
peritos_nif = peritos_nif.dropDuplicates(
subset=['peritaje_transferencia_nif'])
peritos_nif = peritos_nif.drop('id_siniestro')
# del peritos['peritaje_transferencia_nif']
df = df.join(peritos_nif,
on='peritaje_transferencia_nif',
how='left')
peritos_cols = peritos_nif.columns.values.tolist()
peritos_cols.remove('peritaje_transferencia_nif')
for col in peritos_cols:
df = df.fillna({col: 0})
del peritos_cols
# TABLA REPARADOR: Some statistics based on the current database
if self._is_prediction:
df = df.join(reparador_aux,
how='left',
on='peritaje_aide_nombre_reparador')
reparador_cols = reparador_aux.columns.values.tolist()
reparador_cols.remove('peritaje_aide_nombre_reparador')
for col in reparador_cols:
df = df.fillna({col: 0})
else:
reparador = perito_base.select([[
'id_siniestro', 'peritaje_aide_nombre_reparador',
'peritaje_pondera'
]])
del perito_base
reparador = reparador.dropna(
subset='peritaje_aide_nombre_reparador')
reparador = reparador.dropDuplicates(
subset=['peritaje_aide_nombre_reparador', 'id_siniestro'])
# We merge with the known cases of Fraud
file_blacklist_resume = file_blacklist_resume.select(
'id_siniestro')
file_blacklist_resume = file_blacklist_resume.withColumn(
'reparador_fraud', lit(1))
reparador = reparador.join(file_blacklist_resume,
on='id_siniestro',
how='left')
reparador = reparador.fillna({'reparador_fraud': 0})
w_reparador = (Window().partitionBy(
reparador.peritaje_aide_nombre_reparador).rowsBetween(
-sys.maxsize, sys.maxsize))
reparador = reparador.withColumn(
'peritaje_count_visitasxreparador',
sum_(reparador['peritaje_pondera']).over(w_reparador))
reparador = reparador.drop('peritaje_pondera')
reparador = reparador.withColumn(
'peritaje_count_FRAUDxreparador',
sum_(df['reparador_fraud']).over(w_reparador))
reparador = reparador.drop('reparador_fraud')
reparador = reparador.withColumn(
'peritaje_promedio_FRAUDxsiniestroxreparador',
reparador['peritaje_count_FRAUDxreparador'] /
reparador['peritaje_count_visitasxreparador'])
reparador = reparador.dropDuplicates(
subset=['peritaje_aide_nombre_reparador'])
reparador = outliers.Outliers.outliers_mad(
reparador,
'peritaje_promedio_FRAUDxsiniestroxreparador',
justnot_count_zero=True)
reparador = reparador.drop('id_siniestro')
df = df.join(reparador,
how='left',
on='peritaje_aide_nombre_reparador')
reparador_cols = reparador.columns.values.tolist()
reparador_cols.remove('peritaje_aide_nombre_reparador')
for col in reparador_cols:
df = df.fillna({col: 0})
del reparador_cols
# DELETE VARIABLES
del_variables = [
"peritaje_siniestro_causa",
"peritaje_informe_previo_reserva_estimada",
'peritaje_informe_previo_origen_siniestro', 'peritaje_pregunta_8b',
'peritaje_pregunta_6b', "peritaje_obs_documentacion",
"peritaje_obs_fotos", 'id_poliza', 'version_poliza',
'peritaje_codigo', 'fecha_encargo_peritaje',
'peritaje_numero_informe', 'peritaje_fecha_informe',
'fecha_informe_encargo_extendida', 'peritaje_nombre',
'peritaje_aide_nombre_reparador', 'peritaje_pregunta_1',
'peritaje_pregunta_2', 'peritaje_pregunta_3',
'peritaje_pregunta_4', 'peritaje_pregunta_5',
'peritaje_pregunta_6a', 'peritaje_pregunta_7',
'peritaje_pregunta_8a', 'peritaje_pregunta_9',
'peritaje_pregunta_10', 'peritaje_pregunta_11',
'peritaje_pregunta_12', 'peritaje_pregunta_13',
'peritaje_pregunta_14', 'peritaje_pregunta_15',
'peritaje_pregunta_16', 'peritaje_pregunta_17',
'peritaje_pregunta_18', 'peritaje_pregunta_19',
'peritaje_pregunta_20', 'peritaje_posible_fraude',
'peritaje_negativo', 'peritaje_transferencia_IBAN',
'peritaje_transferencia_nif', 'peritaje_indem_informe_previo',
'peritaje_indem_informe_definitivo', 'peritaje_pondera',
'id_fiscal', 'peritaje_negativos_perito',
'peritaje_coberturas_indemnizar_previo',
'peritaje_coberturas_indemnizar_definitivo',
'audit_siniestro_producto_tecnico',
'audit_siniestro_codigo_compania'
]
df = df.drop(*del_variables)
return df, peritos, peritos_nif, reparador
def _transform_data(df, bl_processed):
"""Transform original dataset.
:param df: Input DataFrame.
:param bl_processed
:return: Transformed DataFrame.
"""
# Cast key variables and rename headers
df = df.withColumn('mediador_cod_intermediario',
df.mediador_cod_intermediario.cast(IntegerType()))
df = df.orderBy('mediador_cod_intermediario')
# Count Productos
df = df.withColumn('pondera_producto', lit(1))
w_cod_intermediario = (Window().partitionBy(
df.mediador_cod_intermediario).rowsBetween(-sys.maxsize,
sys.maxsize))
df = df.withColumn(
'mediador_producto_count',
sum_(df['pondera_producto']).over(w_cod_intermediario))
# Blacklist
bl_processed_mediador = bl_processed.filter(
bl_processed['cod_rol'] == 3)
bl_processed_mediador = bl_processed_mediador.withColumn(
'pondera', lit(1))
bl_processed_mediador = bl_processed_mediador.select(
['nif_o_intm', 'pondera'])
w_mediador = (Window().partitionBy(
bl_processed_mediador['nif_o_intm']).rowsBetween(
-sys.maxsize, sys.maxsize))
bl_processed_mediador = bl_processed_mediador.withColumn(
'mediador_cod_count_blacklist',
sum_(bl_processed_mediador['pondera']).over(w_mediador))
bl_processed_mediador = bl_processed_mediador.dropDuplicates(
subset=['nif_o_intm'])
df = df.join(
bl_processed_mediador,
df.mediador_cod_intermediario == bl_processed_mediador.nif_o_intm,
how='left')
df = df.drop(*['nif_o_intm', 'pondera'])
df = df.fillna({'mediador_cod_count_blacklist': 0})
# Estado del mediador
estado = {
'1': 'Activo',
'2': 'Activo',
'3': 'Inactivo',
'4': 'Pendiente',
'5': 'Tramite'
}
funct = udf(
lambda x: f.replace_dict(x, key_values=estado, key_in_value=True),
StringType())
df = df.withColumn('mediador_estado', funct(df['mediador_estado']))
# Dummies var
dummy_var = [
'mediador_clase_intermediario', 'mediador_estado',
'id_agrup_producto'
]
for col in dummy_var:
df = df.fillna({col: 'No Identificado'})
df = df.withColumn(col, regexp_replace(col, '0', ''))
type_d = df.select(col).distinct().collect()
type_d = [ty[col] for ty in type_d]
col_list = [
when(df[col] == ty,
1).otherwise(0).alias('d_' + col + '_' + ty)
for ty in type_d
]
df = df.select(list(df.columns) + col_list)
df.drop(col)
# We sum the number of products
product_names = [
x for x in list(df.columns) if x.startswith('d_id_agrup_producto')
]
for col in product_names:
df = df.withColumn(col, sum_(df[col]).over(w_cod_intermediario))
# Antiguedad del mediador
today = datetime.date.today()
funct = udf(lambda x: datetime.datetime.strptime(x, '%Y%m%d'),
DateType())
df = df.withColumn(
'mediador_fecha_alta',
when(df['mediador_fecha_alta'].isNull(),
'19000101').otherwise(df['mediador_fecha_alta']))
df = df.withColumn('mediador_fecha_alta',
funct(df['mediador_fecha_alta']))
df = df.withColumn('fecha_hoy', lit(today))
df = df.withColumn(
'mediador_antiguedad',
round_(datediff(df['fecha_hoy'], df['mediador_fecha_alta']) / 365))
df = df.drop('fecha_hoy')
# Accumulative float variables
mediador_nan = df.filter(df['mediador_numero_polizas'].isNull()
) # Guardamos los mediadores que tienen nulos
df = df.dropna(subset=['mediador_numero_polizas'])
float_variables = [
'mediador_numero_polizas', 'mediador_numero_polizas_vigor',
'mediador_numero_siniestros', 'mediador_numero_siniestros_fraude',
'mediador_numero_siniestros_pagados'
]
for col in float_variables:
name_count = col + '_count'
df = df.withColumn(
name_count,
sum_(df[col].cast(IntegerType())).over(w_cod_intermediario))
# COUNT BY HOGAR: We now create an additional table to get the statistics for PROPERTY and then remarge
# (0000000002;HOGAR)
df_hogar = df.filter(df['id_agrup_producto'] == 2)
df_hogar = df_hogar.select(*['mediador_cod_intermediario'] +
float_variables)
float_cols = [
when(df_hogar[col].isNull(),
0).otherwise(df_hogar[col]).alias(col + '_hogar')
for col in float_variables
]
df_hogar = df_hogar.select(['mediador_cod_intermediario'] + float_cols)
df = df.dropDuplicates(subset=['mediador_cod_intermediario'])
df = df.join(df_hogar, on='mediador_cod_intermediario', how='left')
# 1) STATISTICS SAME SET BY ROW: Here we compare the same set (GLOBAL or HOGAR) respecto to their
# respectives columns
poliza_var = [
'mediador_numero_polizas', 'mediador_numero_polizas_vigor'
]
siniestro_var = [
'mediador_numero_siniestros', 'mediador_numero_siniestros_fraude',
'mediador_numero_siniestros_pagados'
]
# a) Global: Need to use '_count'
# polizas_vigor / polizas_total
df = df.withColumn(
'mediador_poliza_vigor_total',
df['mediador_numero_polizas_vigor_count'] /
df['mediador_numero_polizas_count'])
# siniestros / siniestros total
df = df.withColumn(
'mediador_siniestros_fraude_total',
df['mediador_numero_siniestros_fraude_count'] /
df['mediador_numero_siniestros_count'])
df = df.withColumn(
'mediador_siniestros_pagados_total',
df['mediador_numero_siniestros_pagados_count'] /
df['mediador_numero_siniestros_count'])
# siniestros / poliza
for sin_str in siniestro_var:
name = sin_str + '/poliza'
df = df.withColumn(
name,
df[sin_str + '_count'] / df['mediador_numero_polizas_count'])
# b) Hogar: Need to use '_hogar'
# polizas_vigor / polizas_total
df = df.withColumn(
'mediador_poliza_vigor_total_hogar',
df['mediador_numero_polizas_vigor_hogar'] /
df['mediador_numero_polizas_hogar'])
# siniestros / siniestros total
df = df.withColumn(
'mediador_siniestros_fraude_total_hogar',
df['mediador_numero_siniestros_fraude_hogar'] /
df['mediador_numero_siniestros_hogar'])
df = df.withColumn(
'mediador_siniestros_pagados_total_hogar',
df['mediador_numero_siniestros_pagados_hogar'] /
df['mediador_numero_siniestros_hogar'])
# siniestros / poliza
for sin_str in siniestro_var:
name = sin_str + '/poliza_hogar'
df = df.withColumn(
name,
df[sin_str + '_hogar'] / df['mediador_numero_polizas_hogar'])
# 2) STATISTICS SAME SET BY COLUMN: We compare the relative weight to the specific column.
# a) Global:
for i in poliza_var:
var = i + '_count'
name = i + '_weight'
# Solucion al error de array_list
total = df.select(var).groupBy().agg(
sum_(var).alias("total")).collect()
df = df.withColumn(name, df[var] / total[0].total)
for i in siniestro_var:
var = i + '_count'
name = i + '_weight'
total = df.select(var).groupBy().agg(
sum_(var).alias("total")).collect()
df = df.withColumn(name, df[var] / total[0].total)
# b) Hogar:
for i in poliza_var:
var = i + '_hogar'
name = i + '_weight'
total = df.select(var).groupBy().agg(
sum_(var).alias("total")).collect()
df = df.withColumn(name, df[var] / total[0].total)
for i in siniestro_var:
var = i + '_hogar'
name = var + '_weight'
total = df.select(var).groupBy().agg(
sum_(var).alias("total")).collect()
df = df.withColumn(name, df[var] / total[0].total)
# STATISTICS DIFFERENT SETS BY COLUMN: Here we compare the relatives between HOGAR / GLOBAL
for i in poliza_var:
global_var = i + '_count'
hogar_var = i + '_hogar'
name = i + 'hogar/total'
df = df.withColumn(name, df[hogar_var] / df[global_var])
for i in siniestro_var:
global_var = i + '_count'
hogar_var = i + '_hogar'
name = i + 'hogar/total'
df = df.withColumn(name, df[hogar_var] / df[global_var])
# OUTLIERS
for i in poliza_var:
global_var = i + '_count'
hogar_var = i + '_hogar'
df = outliers.Outliers.outliers_mad(df,
global_var,
not_count_zero=False)
df = outliers.Outliers.outliers_mad(df,
hogar_var,
not_count_zero=False)
for i in siniestro_var:
global_var = i + '_count'
hogar_var = i + '_hogar'
df = outliers.Outliers.outliers_mad(df,
global_var,
not_count_zero=False)
df = outliers.Outliers.outliers_mad(df,
hogar_var,
not_count_zero=False)
# Unimos otra vez los mediadores que tienen nulos
for col in list(df.columns):
if col not in list(mediador_nan.columns):
mediador_nan = mediador_nan.withColumn(col, lit(None))
df = df.union(mediador_nan)
# Outlier para el número de veces en la BL
df = outliers.Outliers.outliers_mad(df,
'mediador_cod_count_blacklist',
not_count_zero=True)
# DELETE VARIABLES
del_variables = float_variables + [
'mediador_denominacion_intermediario',
'mediador_nif_intermediario', 'mediador_fecha_alta', 'Description',
'pondera_producto'
]
df = df.drop(*del_variables)
df = df.fillna(0)
return df
mode="DROPMALFORMED", \
schema=schema \
)
line_decay_with_dates_df = line_decay_input_df.withColumn("lifespan", clean_string_for_int_udf(col_("lifespan"))) \
.withColumn("created", clean_string_for_date_udf(col_("created"))) \
.withColumn("removed", clean_string_for_date_udf(col_("removed"))) \
.withColumn("create_week", create_cohort_udf(col_("created"))) \
.withColumn("remove_week", create_cohort_udf(col_("removed")))
create_window = Window.partitionBy().orderBy(col_("create_week"))
lines_created_on_week_df = line_decay_with_dates_df.filter(col_("create_week") != -1) \
.groupBy(col_("create_week")) \
.count() \
.withColumnRenamed("count", "lines_created") \
.withColumn("running_created", sum_(col_("lines_created")).over(create_window)) \
.withColumnRenamed("create_week", "week")
#lines_created_on_week_df.show()
remove_window = Window.partitionBy().orderBy(col_("remove_week"))
lines_removed_on_week_df = line_decay_with_dates_df.filter(col_("remove_week") != -1)\
.groupBy(col_("remove_week")) \
.count() \
.withColumnRenamed("count", "lines_removed") \
.withColumn("running_removed", sum_(col_("lines_removed")).over(remove_window)) \
.withColumnRenamed("remove_week", "week")
lines_removed_on_week_df.show()
active_lines_df = lines_created_on_week_df.join(lines_removed_on_week_df, "week", "left_outer") \
def transform_data(self, df, df_reserva, df_reserva_new, df_fecha,
init_date_new_, init_date_historic_):
"""Transform original dataset.
:param df: Input DataFrame.
:param df_reserva
:param df_reserva_new
:param df_fecha
:param init_date_new_: Minimun date for new claims
:param init_date_historic_: Max historical data
:return: Transformed DataFrame.
"""
# Cast key variables and rename headers
df = df.withColumnRenamed('auditCodigoSiniestroReferencia',
'id_siniestro')
df = df.withColumn('id_siniestro', df.id_siniestro.cast(IntegerType()))
# CONSERVED VARIABLES: We drop the variables that are not well defined or that at wrong defined.
var_conserved = [
"id_siniestro", 'id_poliza', 'version_poliza',
"fecha_poliza_emision", "fecha_poliza_efecto_natural",
"fecha_poliza_efecto_mvto", "fecha_poliza_vto_movimiento",
"fecha_poliza_vto_natural", "fecha_siniestro_ocurrencia",
'fecha_siniestro_comunicacion', "fecha_primera_visita_peritaje",
"fecha_ultima_visita_peritaje"
]
df = df.select(*var_conserved)
# We fill siniestro_comunicacion with siniestro_ocurrencia
df = df.withColumn(
'fecha_siniestro_comunicacion',
coalesce('fecha_siniestro_comunicacion',
'fecha_siniestro_ocurrencia'))
# STRIP dates: YEAR, MONTH, WEEKDAY, DAY
var_fecha = [
"fecha_poliza_emision", "fecha_poliza_efecto_natural",
"fecha_poliza_efecto_mvto", "fecha_poliza_vto_movimiento",
"fecha_poliza_vto_natural", "fecha_siniestro_ocurrencia",
'fecha_primera_visita_peritaje', 'fecha_ultima_visita_peritaje',
'fecha_siniestro_comunicacion'
]
func = udf(lambda x: datetime.datetime.strptime(x, '%Y/%m/%d'),
DateType())
for col in var_fecha:
year_name = str(col) + '_year'
month_name = str(col) + '_month'
day_name = str(col) + '_day'
weekday_name = str(col) + '_weekday'
df = df.fillna({col: '1900/01/01'})
df = df.withColumn(col, func(df[col]))
df = df.withColumn(
col,
when(df[col] == '1900-01-01', None).otherwise(df[col]))
df = df.withColumn(year_name, year(df[col]))
df = df.withColumn(month_name, month(df[col]))
df = df.withColumn(day_name, dayofmonth(df[col]))
df = df.withColumn(weekday_name,
date_format(col, 'u') -
1) # We adapt to (0=Monday, 1=Tuesday...)
df = df.withColumn(weekday_name,
df[weekday_name].cast(IntegerType()))
# Filtering by INIT_DATE parameter
df = df.filter(df['fecha_siniestro_ocurrencia'] >= init_date_historic_)
# CHECKLIST 6a
df = df.withColumn('checklist6a', lit(0))
df = df.withColumn('checklist6a_PP', lit(0))
# CHECKLIST 6b
if self._is_diario:
# Filtering new Claims INIT_DATE
df = df.filter(
df['fecha_siniestro_comunicacion'] >= init_date_new_)
auxiliar_list = checklist_spark.checklist6b(
df, df_fecha, df_reserva_new, df_reserva)
else:
auxiliar_list = checklist_spark.checklist6b(
None, df, None, df_reserva)
if auxiliar_list:
r = Row('id_siniestro_c', 'checklist_6b')
df_claims = self._spark.createDataFrame(
r(i, x) for i, x in auxiliar_list)
df = df.join(df_claims,
df.id_siniestro == df_claims.id_siniestro_c,
how='left')
del df_claims, r, auxiliar_list
df = df.drop('id_siniestro_c')
df = df.fillna({'checklist_6b': 0})
else:
df = df.withColumn('checklist_6b', lit(0))
# CHECKLIST 7
if self._is_diario:
auxiliar_list = checklist_spark.checklist_7(
df, df_fecha, df_reserva_new, df_reserva)
else:
auxiliar_list = checklist_spark.checklist_7(
None, df, None, df_reserva)
if auxiliar_list:
r = Row('id_siniestro', 'checklist_7')
df_claims = self._spark.createDataFrame(
r(i, x) for i, x in auxiliar_list)
del auxiliar_list, r
df = df.join(df_claims, on='id_siniestro', how='left')
del df_claims
df = df.drop('id_siniestro_c')
df = df.fillna({'checklist_7': 0})
else:
df = df.withColumn('checklist_7', lit(0))
# CHECKLIST 14
if self._is_diario:
auxiliar_list = checklist_spark.checklist_14(
df, df_fecha, df_reserva_new, df_reserva)
else:
auxiliar_list = checklist_spark.checklist_14(
None, df, None, df_reserva)
if auxiliar_list:
r = Row('id_siniestro_c', 'checklist_14')
df_claims = self._spark.createDataFrame(
r(i, x) for i, x in auxiliar_list)
w = (Window().partitionBy(df_claims.id_siniestro_c).rowsBetween(
-sys.maxsize, sys.maxsize))
df_claims = df_claims.withColumn(
'checklist_14_coberturas_repetidas',
sum_(df_claims.checklist_14).over(w))
df_claims = df_claims.withColumn(
'checklist_14_siniestros_involucrados',
count_(df_claims.checklist_14).over(w))
df_claims = df_claims.dropDuplicates(subset=['id_siniestro_c'])
df_claims.drop('checklist_14')
df = df.join(df_claims,
df.id_siniestro == df_claims.id_siniestro_c,
how='left')
del df_claims, r, auxiliar_list
df = df.drop('id_siniestro_c')
df = df.fillna({'checklist_14_coberturas_repetidas': 0})
df = df.fillna({'checklist_14_siniestros_involucrados': 0})
else:
df = df.withColumn('checklist_14_coberturas_repetidas', lit(0))
df = df.withColumn('checklist_14_siniestros_involucrados', lit(0))
# COMPLEX NON-COMPLEX VARIABLES: We define two types of dates. That dates we want more detail we generate
# every type of possible variable. Non-complex will be more agroupated variables.
var_fecha_complex = ["fecha_siniestro_ocurrencia"]
var_fecha_less_complex = [
"fecha_poliza_efecto_natural", "fecha_poliza_vto_natural"
]
for i in var_fecha_complex:
# We create dummies
col_names = [
str(i) + '_year',
str(i) + '_month',
str(i) + '_weekday'
]
for col in col_names:
types = df.select(col).distinct().collect()
types = [ty[col] for ty in types]
type_list = [
when(df[col] == ty,
1).otherwise(0).alias('d_' + col + '_' + str(ty))
for ty in types
]
df = df.select(list(df.columns) + type_list)
# days range
day = str(i) + '_day'
df = df.withColumn(day + '1_10',
when(df[day].between(1, 10), 1).otherwise(0))
df = df.withColumn(day + '10_20',
when(df[day].between(11, 20), 1).otherwise(0))
df = df.withColumn(day + '20_30',
when(df[day].between(21, 31), 1).otherwise(0))
for i in var_fecha_less_complex:
# month in holiday
df = df.withColumn(
str(i) + '_month_holiday',
when(df[str(i) + '_month'].isin([1, 8, 12]), 1).otherwise(0))
# days range
day = str(i) + '_day'
df = df.withColumn(day + '1_10',
when(df[day].between(1, 10), 1).otherwise(0))
df = df.withColumn(day + '10_20',
when(df[day].between(11, 20), 1).otherwise(0))
df = df.withColumn(day + '20_30',
when(df[day].between(21, 31), 1).otherwise(0))
# weekend or monday
df = df.withColumn(
str(i) + '_weekday_weekend',
when(df[str(i) + '_weekday'].isin([6, 7]), 1).otherwise(0))
df = df.withColumn(
str(i) + '_weekday_monday',
when(df[str(i) + '_weekday'] == 0, 1).otherwise(0))
# FIRST DELETE: We delete that variables we generated before that are not relevant or are
# too specific.
del_variables = [
'fecha_poliza_emision_year', 'fecha_poliza_emision_month',
'fecha_poliza_emision_day', 'fecha_poliza_emision_weekday',
'fecha_poliza_efecto_natural_year',
'fecha_poliza_efecto_natural_month',
'fecha_poliza_efecto_natural_day',
'fecha_poliza_efecto_natural_weekday',
'fecha_poliza_efecto_mvto_year', 'fecha_poliza_efecto_mvto_month',
'fecha_poliza_efecto_mvto_day', 'fecha_poliza_efecto_mvto_weekday',
'fecha_poliza_vto_movimiento_year',
'fecha_poliza_vto_movimiento_month',
'fecha_poliza_vto_movimiento_day',
'fecha_poliza_vto_movimiento_weekday',
'fecha_poliza_vto_natural_year', 'fecha_poliza_vto_natural_month',
'fecha_poliza_vto_natural_day', 'fecha_poliza_vto_natural_weekday',
'fecha_siniestro_ocurrencia_year',
'fecha_siniestro_ocurrencia_month',
'fecha_siniestro_ocurrencia_day',
'fecha_siniestro_ocurrencia_weekday',
'fecha_primera_visita_peritaje_year',
'fecha_primera_visita_peritaje_month',
'fecha_primera_visita_peritaje_day',
'fecha_primera_visita_peritaje_weekday',
'fecha_ultima_visita_peritaje_year',
'fecha_ultima_visita_peritaje_month',
'fecha_ultima_visita_peritaje_day',
'fecha_ultima_visita_peritaje_weekday',
'fecha_siniestro_comunicación_year',
'fecha_siniestro_comunicación_month',
'fecha_siniestro_comunicación_day',
'fecha_siniestro_comunicación_weekday', 'id_poliza',
'hogar_poblacion', 'version_poliza'
]
df = df.drop(*del_variables)
# FECHAS LOGICAS: We create different types of dates var that can be relevant to fraud analysis.
# Diferencia entre primera póliza emisión y último vencimiento natural
df = df.withColumn(
'fecha_diferencia_vto_emision',
datediff(df['fecha_poliza_vto_natural'],
df['fecha_poliza_emision']))
# if fecha efecto < fecha emision => d = 1
df = df.withColumn(
'fecha_indicador_efecto_emision',
when(
df['fecha_poliza_emision'] > df['fecha_poliza_efecto_natural'],
1).otherwise(0))
# diferencia entre siniestro y efecto: 5, 15, 30 días
df = df.withColumn(
'fecha_diferencia_siniestro_efecto',
datediff(df['fecha_siniestro_ocurrencia'],
df['fecha_poliza_efecto_natural']))
days_var = [5, 15, 30]
for col in days_var:
df = df.withColumn(
'fecha_diferencia_siniestro_efecto_' + str(col),
when(df['fecha_diferencia_siniestro_efecto'] <= col,
1).otherwise(0))
# diferencia entre siniestro y primera emisión: 5, 15, 30 días
df = df.withColumn(
'fecha_diferencia_siniestro_emision',
datediff(df['fecha_siniestro_ocurrencia'],
df['fecha_poliza_emision']))
for col in days_var:
df = df.withColumn(
'fecha_diferencia_siniestro_emision_' + str(col),
when(df['fecha_diferencia_siniestro_emision'] <= col,
1).otherwise(0))
# diferencia entre siniestro y vencimiento 5, 15, 30 días
df = df.withColumn(
'fecha_diferencia_siniestro_vto_natural',
datediff(df['fecha_poliza_vto_natural'],
df['fecha_siniestro_ocurrencia']))
for col in days_var:
df = df.withColumn(
'fecha_diferencia_siniestro_vto_natural_' + str(col),
when(df['fecha_diferencia_siniestro_vto_natural'] <= col,
1).otherwise(0))
# if fecha comunicacion > fecha ocurrencia en 7 días, d = 1
df = df.withColumn(
'fecha_diferencia_siniestro_comunicacion',
datediff(df['fecha_siniestro_comunicacion'],
df['fecha_siniestro_ocurrencia']))
df = df.withColumn(
'fecha_diferencia_comunicacion_outlier',
when(df['fecha_diferencia_siniestro_comunicacion'] >= 7,
1).otherwise(0))
df = df.drop('fecha_siniestro_comunicacion')
df = df.dropDuplicates(subset=['id_siniestro'])
return df
def _transform_data(self, df, df_base, bl_processed):
"""Transform original dataset.
:param df: Input DataFrame.
:param bl_processed
:return: Transformed DataFrame.
"""
if self._is_diario:
df = df.withColumn('TEST', lit(1))
df_base = df_base.withColumn('TEST', lit(0))
df = df.union(df_base)
# Cast key variables and rename headers
df = df.withColumnRenamed('auditCodigoSiniestroReferencia', 'id_siniestro_ref')
df = df.withColumn('id_siniestro_ref', df.id_siniestro_ref.cast(IntegerType()))
df = df.withColumn('id_siniestro', df.id_siniestro.cast(IntegerType()))
df = df.dropna(subset=['id_siniestro_ref'])
df = df.dropna(subset=['id_siniestro'])
# DATE VARIABLES FORMAT
fecha_variables = ['hist_siniestro_otro_fecha_ocurrencia', 'hist_siniestro_fecha_terminado']
func = udf(lambda x: datetime.datetime.strptime(x, '%Y/%m/%d'), DateType())
for col in fecha_variables:
df = df.fillna({col: '1900/01/01'})
df = df.withColumn(col, func(df[col]))
df = df.withColumn(col, when(df[col] == '1900-01-01', None).otherwise(df[col]))
df = df.filter(df[col] <= time.strftime('%Y-%m-%d'))
# COUNT ID_SINIESTRO_REF CUANTOS SINIESTROS TIENE
df = df.withColumn('hist_sin_otros_count_version', lit(1))
w = (Window().partitionBy(df.id_siniestro_ref).rowsBetween(-sys.maxsize, sys.maxsize))
df = df.withColumn('hist_sin_otros_count', count_(df.hist_sin_otros_count_version).over(w))
# SINIESTRO TERMINADO: We transform in dummy variables the category siniestro_sit
types = df.select('hist_siniestro_otro_sit').distinct().collect()
types = [ty['hist_siniestro_otro_sit'] for ty in types]
type_list = [when(df['hist_siniestro_otro_sit'] == ty, 1).otherwise(0).alias('d_hist_siniestro_otro_sit_' + ty)
for ty in types]
df = df.select(list(df.columns) + type_list)
# DUMMIES ACUMULATIVAS
types = ['d_hist_siniestro_otro_sit_' + x for x in types]
var_dummies = ["hist_siniestro_otro_rehusado", "hist_siniestro_otro_bbdd",
"hist_siniestro_otro_unidad_investigacion", "hist_siniestro_otro_incidencia_tecnica",
"hist_siniestro_otro_incidencia_tecnica_positiva", "hist_siniestro_otro_incidencias",
"hist_siniestro_otro_cobertura", "hist_siniestro_otro_rehabilitado"] + types
for col in var_dummies:
df = df.withColumn(col + '_count', sum_(df[col]).over(w))
df = df.drop(col)
# DATE VARIABLES
# Duración = Fecha_Terminado - Fecha_Ocurrrencia
df = df.withColumn('hist_otros_fecha_apertura_terminado', datediff('hist_siniestro_fecha_terminado',
'hist_siniestro_otro_fecha_ocurrencia'))
df = df.withColumn('hist_otros_fecha_apertura_terminado',
sum_(df['hist_otros_fecha_apertura_terminado']).over(w))
# Duración Promedio
df = df.withColumn('hist_otros_duracion_promedio_sin', df['hist_otros_fecha_apertura_terminado'] /
df['hist_sin_otros_count'])
# Último Siniestro de la póliza: We are going to keep the first row, it is the last sinister.
df = df.withColumnRenamed('hist_siniestro_otro_fecha_ocurrencia', 'hist_siniestro_otro_ultimo_fecha_ocurrencia')
df = df.orderBy('hist_siniestro_otro_ultimo_fecha_ocurrencia', ascending=False)
# FUE UN SINIESTRO FRAUDULENTO? We check if the id_siniestro is associated with a previous Fraud Sinister
bl_processed = bl_processed.select('id_siniestro').dropDuplicates(subset=['id_siniestro'])
bl_processed = bl_processed.withColumn('hist_sin_otro_fraude_count', lit(1))
df = df.join(bl_processed, on='id_siniestro', how='left')
df = df.withColumn('hist_sin_otro_fraude_count', when(df['hist_sin_otro_fraude_count'].isNull(), 0).otherwise(
df['hist_sin_otro_fraude_count']))
df = df.withColumn('hist_sin_otro_fraude_count', sum_(df['hist_sin_otro_fraude_count']).over(w))
# CARGA SINIESTRAL
df = df.withColumnRenamed('coste_del_siniestro_por_rol', 'hist_siniestro_carga_siniestral')
df = df.fillna({'hist_siniestro_carga_siniestral': 0})
df = df.withColumn('hist_siniestro_carga_siniestral', df.hist_siniestro_carga_siniestral.cast(FloatType()))
# Construimos el outlier a nivel siniestro: Luego hacemos la suma de los casos de outlier por id_siniestro_ref
df = outliers.Outliers.outliers_mad(df, 'hist_siniestro_carga_siniestral', not_count_zero=True)
df = df.withColumn('hist_siniestro_carga_siniestral_mad_outlier_count',
sum_(df['hist_siniestro_carga_siniestral_mad_outlier']).over(w))
# suma total: Sumamos el total de la carga siniestral
df = df.withColumn('hist_siniestro_carga_siniestral_sum', sum_(df['hist_siniestro_carga_siniestral']).over(w))
# promedio
df = df.withColumn('hist_sin_carga_siniestral_promedio', df['hist_siniestro_carga_siniestral_sum']
/ df['hist_sin_otros_count'])
# COBERTURAS
# Outliers
df = outliers.Outliers.outliers_mad(df, 'hist_siniestro_coberturas_involucradas', not_count_zero=True)
df = df.withColumn('hist_siniestro_coberturas_involucradas_mad_outlier',
when(df['hist_siniestro_coberturas_involucradas'] > 3, 1).otherwise(0))
df = df.withColumn('hist_siniestro_coberturas_involucradas_mad_outlier_count',
sum_(df['hist_siniestro_coberturas_involucradas_mad_outlier']).over(w))
df = df.drop('hist_siniestro_coberturas_involucradas_mad_outlier')
# promedio
df = df.withColumn('hist_sin_otros_cober_sum', sum_(df['hist_siniestro_coberturas_involucradas']).over(w))
df = df.withColumn('hist_sin_otros_cober_promedio', df['hist_sin_otros_cober_sum'] / df['hist_sin_otros_count'])
# pagas-cubiertas
df = df.withColumn('hist_siniestro_coberturas_involucradas_pagadas_sum',
sum_(df['hist_siniestro_coberturas_involucradas_pagadas']).over(w))
df = df.withColumn('hist_sin_otros_pagas_cubiertas',
df['hist_siniestro_coberturas_involucradas_pagadas_sum'] / df['hist_sin_otros_cober_sum'])
# no-pagas
df = df.withColumn('hist_sin_otros_cob_no_pagas', df['hist_siniestro_coberturas_involucradas'] -
df['hist_siniestro_coberturas_involucradas_pagadas'])
df = df.withColumn('hist_sin_otros_cob_no_pagas', sum_(df['hist_sin_otros_cob_no_pagas']).over(w))
# VARIABLES DEL
del_variables = ['id_fiscal', 'hist_siniestro_otro_descripcion', "hist_siniestro_duracion",
'hist_siniestro_fecha_terminado', 'hist_sin_otros_count_version',
'hist_siniestro_otro_oficina_productora',
'hist_siniestro_carga_siniestral', 'hist_siniestro_coberturas_involucradas',
'hist_siniestro_coberturas_involucradas_pagadas',
'hist_otros_fecha_apertura_terminado',
'auditFechaAperturaSiniestroReferencia', 'id_siniestro', 'id_poliza', 'version_poliza',
'audit_siniestro_producto_tecnico',
'audit_siniestro_codigo_compania', 'hist_siniestro_otro_sit'
]
df = df.drop(*del_variables)
df = df.withColumnRenamed('id_siniestro_ref', 'id_siniestro')
# Tomamos el primero de cada uno de los siniestros
df = df.dropDuplicates(subset=['id_siniestro'])
if self._is_diario:
df = df.filter(df['TEST'] == 1)
df = df.drop('TEST')
return df
def _transform_data(self, df, df_base, df_processed, id_base, id_new, bl_processed, aux_perceptor, aux_servicios):
"""Transform original dataset.
:param df: Input DataFrame.
:param bl_processed
:return: Transformed DataFrame.
"""
# Cast key variables and rename headers
df = df.withColumn('id_siniestro', df.id_siniestro.cast(IntegerType()))
if self._is_diario:
checklist5 = checklist_spark.checklist5(df_reserva=df_base, df_reserva_new=df, df_id=id_base,
df_id_new=id_new)
else:
checklist5 = checklist_spark.checklist5(df_reserva=df, df_id=id_base)
df = df.join(checklist5, on='id_siniestro', how='left')
df = df.fillna({'checklist5_poliza': 0, 'checklist5_nif': 0})
del checklist5
# RESERVA INICIAL INDEMNIZACION
reserva_indem = df.select(['id_siniestro', 'po_res_cobertura_id', 'po_res_indem'])
reserva_indem = reserva_indem.dropDuplicates(subset=['id_siniestro', 'po_res_cobertura_id', 'po_res_indem'])
reserva_indem = reserva_indem.drop('po_res_cobertura_id')
reserva_indem = reserva_indem.withColumn('po_res_indem', reserva_indem.po_res_indem.cast(FloatType()))
reserva_indem = reserva_indem.groupBy(['id_siniestro']).agg(sum_('po_res_indem').alias('po_res_indem'))
reserva_indem = reserva_indem.withColumn('po_res_indem_mayor_5000',
when(reserva_indem['po_res_indem'] >= 5000, 1).otherwise(0))
df = df.drop('po_res_indem')
df = df.join(reserva_indem, on='id_siniestro', how='left')
del reserva_indem
# RESERVA INICIAL GASTO
reserva_gasto = df.select(['id_siniestro', 'po_res_cobertura_id', 'po_res_gasto'])
reserva_gasto = reserva_gasto.dropDuplicates(subset=['id_siniestro', 'po_res_cobertura_id', 'po_res_gasto'])
reserva_gasto = reserva_gasto.drop('po_res_cobertura_id')
reserva_gasto = reserva_gasto.withColumn('po_res_gasto', reserva_gasto.po_res_gasto.cast(FloatType()))
reserva_gasto = reserva_gasto.groupBy('id_siniestro').agg(sum_('po_res_gasto').alias('po_res_gasto'))
reserva_gasto = reserva_gasto.withColumn('po_res_gasto_mayor_1000',
when(reserva_gasto['po_res_gasto'] >= 1000, 1).otherwise(0))
reserva_gasto = reserva_gasto.drop('po_res_gasto')
df = df.join(reserva_gasto, on='id_siniestro', how='left')
del reserva_gasto
# COUNT POLIZAS POR SINIESTRO
df = df.withColumn('pondera_siniestro', lit(1))
w = (Window().partitionBy('id_siniestro').rowsBetween(-sys.maxsize, sys.maxsize))
df = df.withColumn('po_reserva_pagoxsiniestro_count', sum_(df['pondera_siniestro']).over(w))
df = df.withColumn('po_reserva_indemxsiniestro_count', sum_(df['po_pago_indicador_indem']).over(w))
# PAGO INDEM ANULADOS: Cuando la anulación es == 1 marca tanto el pago como su anulación
df = df.withColumn('po_pago_indemnizacion_importe_neto',
when(df['po_pago_es_anulacion'] == 1, 1).otherwise(df['po_pago_indemnizacion_importe_neto']))
# GASTO_INDEM_ANULADOS: Cuando el gasto es == 1 marca tanto el pago como su anulación
df = df.withColumn('po_gasto_indemnizacion_importe_neto',
when(df['po_gasto_es_anulacion'] == 1, 1).otherwise(df['po_gasto_es_anulacion']))
# PAGOS: Sumamos el importe neto de factura + los pagos netos por indemnizaciòn
df = df.withColumn('po_pago_importe_neto', df['po_pago_factura_importe_neto'].cast(FloatType()) + df[
'po_pago_indemnizacion_importe_neto'].cast(FloatType()))
# GASTOS:
df = df.withColumn('po_gasto_importe_neto', df['po_gasto_factura_importe_neto'].cast(FloatType()) +
df['po_gasto_indemnizacion_importe_neto'].cast(FloatType()))
# PAGO ASEGURADO: Si la persona no es el asegurado ponemos los importes del Asegurado en 0
df = df.withColumn('po_pago_importe_neto_ASEGURADO',
when(df['persona_objeto_asegurado'].cast(IntegerType()) == 0, 0).otherwise(
df['po_pago_importe_neto']))
# IMPORTE PORCENTUAL QUE EFECTIVAMENTE COBRA EL ASEGURADO: importe_neto_asegurado/importe_total
df = df.withColumn('po_pago_importe_porcentual_ASEGURADO', df['po_pago_importe_neto_ASEGURADO']
/ (df['po_pago_importe_neto'] + 1))
# IBAN Blacklist
bl_processed_iban = bl_processed.filter(~((bl_processed['iban'].isNull()) | (bl_processed['iban'] == '?')))
bl_processed_iban = bl_processed_iban.select('iban')
bl_processed_iban = bl_processed_iban.dropDuplicates(subset=['iban'])
df = df.join(bl_processed_iban, df.po_pago_IBAN == bl_processed_iban.iban, how='left')
df = df.withColumn('peritaje_pago_iban_blacklist', when(df['iban'].isNull(), 0).otherwise(1))
df = df.drop('iban')
df = df.join(bl_processed_iban, df.po_gasto_IBAN == bl_processed_iban.iban, how='left')
df = df.withColumn('peritaje_gasto_iban_blacklist', when(df['iban'].isNull(), 0).otherwise(1))
df = df.drop('iban')
del bl_processed_iban
# INT Variables
# Agrupamos los valores INT por Siniestro y lo guardamos en la lista de INT's
int_var = ['peritaje_pago_iban_blacklist', 'peritaje_gasto_iban_blacklist']
int_outliers = []
for col in int_var:
count = col + '_count'
df = df.withColumn(count, sum_(df[col]).over(w))
int_outliers.append(count)
df = df.drop(col)
# PERSONA OBJETO RESERVABLE
df = df.withColumn('id_persona_objeto_reservable_max',
max_(df['id_persona_objeto_reservable'].cast(IntegerType())).over(w))
df = df.drop('id_persona_objeto_reservable')
# CATEGORICAL VARIABLE
# Redefinimos la variable pago_gasto_codigo como categórica
asegurado = STRING.Parameters.Asegurado_Beneficiario_Perjudicado
profesional = STRING.Parameters.Profesional_Legal
detective = STRING.Parameters.Detective
perito = STRING.Parameters.Perito
reparador = STRING.Parameters.Reparador
otros = STRING.Parameters.todos
df = df.withColumn('po_pago_gasto_codigo', df.po_pago_gasto_codigo.cast(StringType()))
df = df.withColumn('po_pago_gasto_codigo',
when(df['po_pago_gasto_codigo'].isin(otros), df['po_pago_gasto_codigo']).otherwise(
'Otros'))
df = df.withColumn('po_pago_gasto_codigo',
when(df['po_pago_gasto_codigo'].isin(asegurado),
'Asegurado_Beneficiario_Perjudicado').otherwise(df['po_pago_gasto_codigo']))
df = df.withColumn('po_pago_gasto_codigo',
when(df['po_pago_gasto_codigo'].isin(profesional),
'Profesional_Legal').otherwise(df['po_pago_gasto_codigo']))
df = df.withColumn('po_pago_gasto_codigo',
when(df['po_pago_gasto_codigo'].isin(detective),
'Detective').otherwise(df['po_pago_gasto_codigo']))
df = df.withColumn('po_pago_gasto_codigo',
when(df['po_pago_gasto_codigo'].isin(perito),
'Perito').otherwise(df['po_pago_gasto_codigo']))
df = df.withColumn('po_pago_gasto_codigo',
when(df['po_pago_gasto_codigo'].isin(reparador),
'Reparador').otherwise(df['po_pago_gasto_codigo']))
# GARANTIA
garantia = STRING.Parameters.dict_garantias
funct = udf(lambda x: f.replace_dict_contain(x, key_values=garantia), StringType())
df = df.withColumn('po_res_garantia', funct(df['po_res_garantia']))
# COBERTURA
cobertura_1 = STRING.Parameters.dict_cobertura_1
funct = udf(lambda x: f.replace_dict_contain(x, key_values=cobertura_1), StringType())
df = df.withColumn('po_res_cobertura', funct(df['po_res_cobertura']))
df = df.withColumn('po_res_cobertura',
when(df['po_res_cobertura'] == 'DE', 'ELECTRICIDAD').otherwise(df['po_res_cobertura']))
cobertura_2 = STRING.Parameters.dict_cobertura_2
funct = udf(lambda x: f.replace_dict_contain(x, key_values=cobertura_2), StringType())
df = df.withColumn('po_res_cobertura', funct(df['po_res_cobertura']))
# Pasamos todas las categóricas para obtener dummies
categorical_var = ['po_res_garantia', 'po_res_cobertura', 'po_res_situacion',
'po_pago_medio', 'po_pago_gasto_codigo']
variable_dummy = []
funct = udf(lambda x: f.normalize_string(x), StringType())
for col in categorical_var:
df = df.withColumn(col, df[col].cast(StringType()))
df = df.withColumn(col, funct(df[col]))
types = df.select(col).distinct().collect()
types = [ty[col] for ty in types]
types_list = [when(df[col] == ty, 1).otherwise(0).alias('d_' + col + '_' + ty) for ty in types if
ty not in [None, '0', 0]]
df = df.select(list(df.columns) + types_list)
variable_dummy += ['d_' + col + '_' + ty for ty in types if ty not in [None, '0', 0]]
df = df.drop(col)
# Agrupamos las dummies por siniestro
variable_dummy += ['po_pago_indicador_indem', 'po_pago_rehusado', 'po_pago_gasto_codigo_detective',
'persona_objeto_asegurado']
for col in variable_dummy:
name = col + '_count'
df = df.fillna({col: 0})
df = df.withColumn(col, df[col].cast(IntegerType()))
df = df.withColumn(name, sum_(df[col]).over(w))
df = df.drop(col)
# Ajustamos las situaciones para representarlo porcentualmente:
for col in list(df.columns):
if col.startswith('d_po_res_situacion'):
df = df.withColumn(col, df[col] / df['po_reserva_pagoxsiniestro_count'])
# Ajustamos po_pago_indicador_indem para verlo porcentualmente
df = df.withColumn('po_pago_indicador_indem_count', df['po_pago_indicador_indem_count'] / df[
'po_reserva_pagoxsiniestro_count'])
# FLOAT VARIABLES
# Agrupamos las FLOAT por siniestro y las ponemos para analizar como Outliers
variable_float_perceptor = ['po_pago_factura_importe_neto', 'po_pago_indemnizacion_importe_neto',
'po_gasto_factura_importe_neto', 'po_gasto_indemnizacion_importe_neto',
'po_pago_importe_neto', 'po_gasto_importe_neto',
'po_pago_importe_neto_ASEGURADO',
'po_pago_importe_porcentual_ASEGURADO',
]
float_outliers = []
for col in variable_float_perceptor:
name = col + '_count'
df = df.fillna({col: 0})
df = df.withColumn(col, df[col].cast(FloatType()))
df = df.withColumn(name, sum_(df[col]).over(w))
float_outliers.append(name)
# Porcentual_Asegurado: Lo ajustamos para que refleje el valor porcentual
df = df.withColumn('po_pago_importe_porcentual_ASEGURADO_count',
df['po_pago_importe_porcentual_ASEGURADO_count'] / df['po_reserva_indemxsiniestro_count'])
# TABLA DE PERCEPTOR Y SERVICIOS
df = df.withColumn('po_pago_perceptor',
when(df['po_pago_perceptor'].startswith('AIDE ASISTENCIA'), 'AIDE ASISTENCIA').otherwise(
df['po_pago_perceptor']))
if self._is_diario:
df = df.join(aux_perceptor, on='po_pago_perceptor', how='left')
df = df.join(aux_servicios.drop('id_siniestro'), on='po_gasto_perceptor', how='left')
for col in list(aux_perceptor.columns) + list(aux_servicios.columns):
if col not in ['po_pago_perceptor', 'po_gasto_perceptor']:
df = df.fillna({col: 0})
del aux_perceptor
del aux_servicios
else:
# TABLA DE PERCEPTOR
df.withColumn('po_pago_perceptor',
when(df['po_pago_perceptor'].isin([0, '0']), None).otherwise(df['po_pago_perceptor']))
w_perceptor = (Window().partitionBy('po_pago_perceptor').rowsBetween(-sys.maxsize, sys.maxsize))
# Por Pagos
df = df.withColumn('pondera_perceptor', when(df['po_pago_perceptor'].isNotNull(), 1).otherwise(0))
df = df.withColumn('po_pagos_total_countxperceptor', sum_(df['pondera_perceptor']).over(w_perceptor))
df = df.drop('pondera_perceptor')
# Pago anulación
df = df.fillna({'po_pago_es_anulacion': 0})
df = df.withColumn('po_pago_es_anulacion_countxperceptor',
sum_(df['po_pago_es_anulacion'].cast(IntegerType())).over(w_perceptor))
df = df.drop('po_pago_es_anulacion')
variable_float_perceptor = ['po_pago_factura_importe_neto', 'po_pago_indemnizacion_importe_neto']
# Nota: está bien que haya valores negativos porque son los recobros a otras empresas
for col in variable_float_perceptor:
name = col + '_countxperceptor'
df = df.withColumn(name, sum_(df[col].cast(FloatType())).over(w_perceptor))
df = df.drop(col)
# Count a nivel siniestro
df = df.withColumn('po_pagoxsiniestro_countxperceptor',
size(collect_set(df['id_siniestro']).over(w_perceptor)))
# Obtenemos los niveles promedio por Perceptor-Siniestro
for col in ['po_pago_factura_importe_neto_countxperceptor',
'po_pago_indemnizacion_importe_neto_countxperceptor']:
df = df.withColumn(col + 'xpromediosiniestro', df[col] / df['po_pagoxsiniestro_countxperceptor'])
# Perceptor Aparece en blacklist y cuántas
# FUE UN SINIESTRO FRAUDULENTO? We check if the id_siniestro is associated with a previous Fraud Sinister
bl_processed = bl_processed.select('id_siniestro').dropDuplicates(subset=['id_siniestro'])
bl_processed = bl_processed.withColumn('po_reserva_perceptor_fraud', lit(1))
df = df.join(bl_processed, on='id_siniestro', how='left')
df = df.withColumn('po_reserva_perceptor_fraud',
when(df['po_reserva_perceptor_fraud'].isNull(), 0).otherwise(
df['po_reserva_perceptor_fraud']))
perc_aux = df.select(['po_pago_perceptor', 'id_siniestro', 'po_reserva_perceptor_fraud']).dropDuplicates(
subset=['po_pago_perceptor', 'id_siniestro'])
perc_aux = perc_aux.groupBy('po_pago_perceptor').agg(
sum_('po_reserva_perceptor_fraud').alias('po_fraude_countxperceptor'))
df = df.join(perc_aux, on='po_pago_perceptor', how='left')
del perc_aux
df = df.withColumn('po_fraude_porcentaje_perceptor',
df['po_fraude_countxperceptor'] / df['po_pagoxsiniestro_countxperceptor'])
df.select(['po_pago_perceptor', 'po_pagos_total_countxperceptor', 'po_pago_es_anulacion_countxperceptor',
'po_pago_factura_importe_neto_countxperceptor',
'po_pago_indemnizacion_importe_neto_countxperceptor', 'po_pagoxsiniestro_countxperceptor',
'po_pago_factura_importe_neto_countxperceptorxpromediosiniestro',
'po_pago_indemnizacion_importe_neto_countxperceptorxpromediosiniestro',
'po_fraude_countxperceptor', 'po_fraude_porcentaje_perceptor']
).dropDuplicates(subset=['po_pago_perceptor']).coalesce(1).write.mode("overwrite").option(
"header", "true").option("sep", ";").csv(STRING.training_auxiliar_perceptor)
# TABLA DE SERVICIOS
df = df.withColumn('po_gasto_perceptor',
when(df['po_gasto_perceptor'].isin([0, '0']), None).otherwise(df['po_gasto_perceptor']))
w_servicio = (Window().partitionBy('po_gasto_perceptor').rowsBetween(-sys.maxsize, sys.maxsize))
# Por Pagos
df = df.withColumn('pondera_servicio', when(df['po_gasto_perceptor'].isNotNull(), 1).otherwise(0))
df = df.withColumn('po_pagos_total_countxservicios', sum_(df['pondera_servicio']).over(w_servicio))
df = df.drop('pondera_servicio')
variable_float_servicio = ['po_gasto_factura_importe_neto', 'po_gasto_indemnizacion_importe_neto']
# Nota: está bien que haya valores negativos porque son los recobros a otras empresas
for col in variable_float_servicio:
name = col + '_countxservicios'
df = df.withColumn(name, sum_(df[col].cast(FloatType())).over(w_servicio))
df = df.drop(col)
# Count a nivel siniestro
df = df.withColumn('po_pagoxsiniestro_countxservicios',
size(collect_set(df['id_siniestro']).over(w_servicio)))
# Obtenemos el promedio global por Servicio
for col in ['po_gasto_factura_importe_neto_countxservicios',
'po_gasto_indemnizacion_importe_neto_countxservicios']:
df = df.withColumn(col + 'xpromediosiniestro', df[col] / df['po_pagoxsiniestro_countxservicios'])
# Perceptor Aparece en blacklist y cuántas
# FUE UN SINIESTRO FRAUDULENTO? We check if the id_siniestro is associated with a previous Fraud Sinister
bl_processed = bl_processed.withColumn('po_reserva_servicios_fraud', lit(1))
df = df.join(bl_processed[['id_siniestro', 'po_reserva_servicios_fraud']], on='id_siniestro', how='left')
df = df.withColumn('po_reserva_servicios_fraud',
when(df['po_reserva_servicios_fraud'].isNull(), 0).otherwise(
df['po_reserva_servicios_fraud']))
del bl_processed
serv_aux = df.select(['po_gasto_perceptor', 'id_siniestro', 'po_reserva_servicios_fraud']).dropDuplicates(
subset=['po_gasto_perceptor', 'id_siniestro'])
serv_aux = serv_aux.groupBy('po_gasto_perceptor').agg(
sum_('po_reserva_servicios_fraud').alias('po_fraude_countxservicios'))
df = df.join(serv_aux, on='po_gasto_perceptor', how='left')
del serv_aux
df = df.withColumn('po_fraude_porcentaje_servicios',
df['po_fraude_countxservicios'] / df['po_pagoxsiniestro_countxservicios'])
df.select(['po_gasto_perceptor', 'id_siniestro', 'po_pagos_total_countxservicios',
'po_gasto_factura_importe_neto_countxservicios',
'po_gasto_indemnizacion_importe_neto_countxservicios', 'po_pagoxsiniestro_countxservicios',
'po_gasto_factura_importe_neto_countxserviciosxpromediosiniestro',
'po_gasto_indemnizacion_importe_neto_countxserviciosxpromediosiniestro',
'po_fraude_countxservicios', 'po_fraude_porcentaje_servicios']
).dropDuplicates(subset=['po_gasto_perceptor']).coalesce(1).write.mode("overwrite").option(
"header", "true").option("sep", ";").csv(STRING.training_auxiliar_servicios)
df = df.drop(*['po_pago_perceptor', 'po_gasto_perceptor'])
df = df.dropDuplicates(subset=['id_siniestro'])
df = df.fillna({'po_fraude_countxservicios': 0, 'po_fraude_porcentaje_servicios': 0})
if self._is_diario:
df_processed = df_processed.select(int_outliers + float_outliers)
for col in int_outliers + float_outliers:
df = outliers.Outliers.outliers_test_values(df, df_processed, col, not_count_zero=True)
else:
for col in int_outliers + float_outliers:
df = outliers.Outliers.outliers_mad(df, col, not_count_zero=True)
# DELETE VARIABLES
del_variables = ['id_poliza', 'version_poliza', "po_res_garantia_id",
"po_res_cobertura_id", 'po_res_limite',
"po_pago_IBAN", "po_pago_emision", "po_pago_factura_fecha_emision",
'po_pago_factura_importe_neto', 'po_pago_indemnizacion_importe_neto',
'po_gasto_IBAN', 'po_gasto_emision', 'po_gasto_factura_fecha_emision',
'po_pago_es_anulacion', 'po_gasto_es_anulacion', 'pondera_siniestro',
'po_gasto_perceptor', 'po_gasto_factura_importe_neto', 'po_gasto_indemnizacion_importe_neto',
'po_pago_importe_neto', 'po_gasto_importe_neto',
'po_pago_importe_neto_ASEGURADO', 'po_pago_importe_porcentual_ASEGURADO',
'audit_poliza_producto_tecnico',
'audit_siniestro_codigo_compania', 'po_reserva_indemxsiniestro_count'
]
df = df.drop(*del_variables)
return df
