def create_delinq_features(table, **kwargs):
chronometer = Chronometer.makeStarted()
query = """SELECT loan_id,
min(monthly_reporting_period) as delinquency_30
FROM main.perf where current_loan_delinquency_status >= 1 group by loan_id"""
result_delinq_30 = pyblazing.run_query(query, {table.name: table.columns})
query = """SELECT loan_id,
min(monthly_reporting_period) as delinquency_90
FROM main.perf where current_loan_delinquency_status >= 3 group by loan_id"""
result_delinq_90 = pyblazing.run_query(query, {table.name: table.columns})
query = """SELECT loan_id,
min(monthly_reporting_period) as delinquency_180
FROM main.perf where current_loan_delinquency_status >= 6 group by loan_id"""
result_delinq_180 = pyblazing.run_query(query, {table.name: table.columns})
new_tables = {
"delinq_30": result_delinq_30.columns,
"delinq_90": result_delinq_90.columns,
"delinq_180": result_delinq_180.columns
}
query = """SELECT d30.loan_id, delinquency_30, COALESCE(delinquency_90, DATE '1970-01-01') as delinquency_90,
COALESCE(delinquency_180, DATE '1970-01-01') as delinquency_180 FROM main.delinq_30 as d30
LEFT OUTER JOIN main.delinq_90 as d90 ON d30.loan_id = d90.loan_id
LEFT OUTER JOIN main.delinq_180 as d180 ON d30.loan_id = d180.loan_id"""
result_merge = pyblazing.run_query(query, new_tables)
Chronometer.show(chronometer, 'Create deliquency features')
return result_merge
def create_12_mon_features_union(joined_df, **kwargs):
chronometer = Chronometer.makeStarted()
tables = {"joined_df": joined_df}
josh_mody_n_str = "timestamp_year * 12 + timestamp_month - 24000.0"
query = ("SELECT loan_id, " + josh_mody_n_str +
" as josh_mody_n, max(delinquency_12) as max_d12," +
" min(upb_12) as min_upb_12" +
" FROM main.joined_df as joined_df GROUP BY loan_id, " +
josh_mody_n_str)
mastertemp = pyblazing.run_query(query, tables)
all_temps = []
all_tokens = []
tables = {"joined_df": mastertemp.columns}
n_months = 12
for y in range(1, n_months + 1):
josh_mody_n_str = "floor((josh_mody_n - " + str(y) + ")/12.0)"
query = ("SELECT loan_id, " + josh_mody_n_str +
" as josh_mody_n, max(max_d12) > 3 as max_d12_gt3," +
" min(min_upb_12) = 0 as min_upb_12_eq0," +
" min(min_upb_12) as upb_12" +
" FROM main.joined_df as joined_df GROUP BY loan_id, " +
josh_mody_n_str)
metaToken = pyblazing.run_query_get_token(query, tables)
all_tokens.append(metaToken)
for metaToken in all_tokens:
temp = pyblazing.run_query_get_results(metaToken)
all_temps.append(temp)
y = 1
tables2 = {"temp1": all_temps[0].columns}
union_query = (
"""(SELECT loan_id, max_d12_gt3 + min_upb_12_eq0 as delinquency_12,
upb_12, floor(((josh_mody_n * 12) + """ + str(24000 + (y - 1)) +
")/12) as timestamp_year, josh_mody_n * 0 + " + str(y) +
" as timestamp_month from main.temp" + str(y) + ")")
for y in range(2, n_months + 1):
tables2["temp" + str(y)] = all_temps[y - 1].columns
query = (""" UNION ALL (SELECT loan_id, max_d12_gt3 + min_upb_12_eq0 as
delinquency_12, upb_12, floor(((josh_mody_n * 12) + """ +
str(24000 +
(y - 1)) + ")/12) as timestamp_year, josh_mody_n * 0 + " +
str(y) + " as timestamp_month from main.temp" + str(y) + ")")
union_query = union_query + query
results = pyblazing.run_query(union_query, tables2)
Chronometer.show(chronometer, "Create 12 month features once")
return results
def create_delinq_features(table, **kwargs):
chronometer = Chronometer.makeStarted()
query = """
SELECT loan_id,
min(monthly_reporting_period) as delinquency_30
FROM main.perf
where current_loan_delinquency_status >= 1 group by loan_id
"""
result_delinq_30 = pyblazing.run_query(query, {table.name: table.columns})
query = """
SELECT loan_id,
min(monthly_reporting_period) as delinquency_90
FROM main.perf
where current_loan_delinquency_status >= 3 group by loan_id
"""
result_delinq_90 = pyblazing.run_query(query, {table.name: table.columns})
query = """
SELECT loan_id,
min(monthly_reporting_period) as delinquency_180
FROM main.perf
where current_loan_delinquency_status >= 6 group by loan_id
"""
result_delinq_180 = pyblazing.run_query(query, {table.name: table.columns})
new_tables = {
"delinq_30": result_delinq_30.columns,
"delinq_90": result_delinq_90.columns,
"delinq_180": result_delinq_180.columns,
}
query = """
SELECT d30.loan_id, delinquency_30, delinquency_90,
delinquency_180 FROM main.delinq_30 as d30
LEFT OUTER JOIN main.delinq_90 as d90 ON d30.loan_id = d90.loan_id
LEFT OUTER JOIN main.delinq_180 as d180 ON d30.loan_id = d180.loan_id
"""
result_merge = pyblazing.run_query(query, new_tables)
result_merge.columns["delinquency_90"] = result_merge.columns[
"delinquency_90"].fillna(
np.dtype("datetime64[ms]").type("1970-01-01").astype(
"datetime64[ms]"))
result_merge.columns["delinquency_180"] = result_merge.columns[
"delinquency_180"].fillna(
np.dtype("datetime64[ms]").type("1970-01-01").astype(
"datetime64[ms]"))
Chronometer.show(chronometer, "Create deliquency features")
return result_merge
def create_joined_df(gdf, everdf, **kwargs):
chronometer = Chronometer.makeStarted()
tables = {"perf": gdf, "everdf": everdf}
query = """SELECT perf.loan_id as loan_id,
perf.monthly_reporting_period as mrp_timestamp,
EXTRACT(MONTH FROM perf.monthly_reporting_period)
as timestamp_month,
EXTRACT(YEAR FROM perf.monthly_reporting_period)
as timestamp_year,
COALESCE(perf.current_loan_delinquency_status, -1)
as delinquency_12,
COALESCE(perf.current_actual_upb, 999999999.9) as upb_12,
everdf.ever_30 as ever_30,
everdf.ever_90 as ever_90,
everdf.ever_180 as ever_180,
COALESCE(everdf.delinquency_30, DATE '1970-01-01')
as delinquency_30,
COALESCE(everdf.delinquency_90, DATE '1970-01-01')
as delinquency_90,
COALESCE(everdf.delinquency_180, DATE '1970-01-01')
as delinquency_180
FROM main.perf as perf
LEFT OUTER JOIN main.everdf as everdf
ON perf.loan_id = everdf.loan_id"""
results = pyblazing.run_query(query, tables)
Chronometer.show(chronometer, "Create Joined DF")
return results
def join_ever_delinq_features(everdf_tmp, delinq_merge, **kwargs):
chronometer = Chronometer.makeStarted()
tables = {"everdf": everdf_tmp, "delinq": delinq_merge}
query = """
SELECT everdf.loan_id as loan_id, ever_30, ever_90, ever_180,
delinquency_30,
delinquency_90,
delinquency_180 FROM main.everdf as everdf
LEFT OUTER JOIN main.delinq as delinq
ON everdf.loan_id = delinq.loan_id
"""
result_merge = pyblazing.run_query(query, tables)
result_merge.columns["delinquency_30"] = result_merge.columns[
"delinquency_30"].fillna(
np.dtype("datetime64[ms]").type("1970-01-01").astype(
"datetime64[ms]"))
result_merge.columns["delinquency_90"] = result_merge.columns[
"delinquency_90"].fillna(
np.dtype("datetime64[ms]").type("1970-01-01").astype(
"datetime64[ms]"))
result_merge.columns["delinquency_180"] = result_merge.columns[
"delinquency_180"].fillna(
np.dtype("datetime64[ms]").type("1970-01-01").astype(
"datetime64[ms]"))
Chronometer.show(chronometer, "Create ever deliquency features")
return result_merge
def final_performance_delinquency(gdf, joined_df, **kwargs):
chronometer = Chronometer.makeStarted()
tables = {"gdf": gdf, "joined_df": joined_df}
query = """SELECT g.loan_id, current_actual_upb, current_loan_delinquency_status, delinquency_12, interest_rate, loan_age, mod_flag, msa, non_interest_bearing_upb
FROM main.gdf as g LEFT OUTER JOIN main.joined_df as j
ON g.loan_id = j.loan_id and EXTRACT(YEAR FROM g.monthly_reporting_period) = j.timestamp_year and EXTRACT(MONTH FROM g.monthly_reporting_period) = j.timestamp_month """
results = pyblazing.run_query(query, tables)
Chronometer.show(chronometer, 'Final performance delinquency')
return results
def join_perf_acq_gdfs(perf, acq, **kwargs):
chronometer = Chronometer.makeStarted()
tables = {"perf": perf, "acq": acq}
query = """SELECT p.loan_id, current_actual_upb, current_loan_delinquency_status, delinquency_12, interest_rate, loan_age, mod_flag, msa, non_interest_bearing_upb,
borrower_credit_score, dti, first_home_buyer, loan_purpose, mortgage_insurance_percent, num_borrowers, num_units, occupancy_status,
orig_channel, orig_cltv, orig_date, orig_interest_rate, orig_loan_term, orig_ltv, orig_upb, product_type, property_state, property_type,
relocation_mortgage_indicator, seller_name, zip FROM main.perf as p LEFT OUTER JOIN main.acq as a ON p.loan_id = a.loan_id"""
results = pyblazing.run_query(query, tables)
Chronometer.show(chronometer, 'Join performance acquitistion gdfs')
return results
def create_ever_features(table, **kwargs):
chronometer = Chronometer.makeStarted()
query = """SELECT loan_id,
max(current_loan_delinquency_status) >= 1 as ever_30,
max(current_loan_delinquency_status) >= 3 as ever_90,
max(current_loan_delinquency_status) >= 6 as ever_180
FROM main.perf group by loan_id"""
result = pyblazing.run_query(query, {table.name: table.columns})
Chronometer.show(chronometer, 'Create Ever Features')
return result
def combine_joined_12_mon(joined_df, testdf, **kwargs):
chronometer = Chronometer.makeStarted()
tables = {"joined_df": joined_df, "testdf": testdf}
query = """SELECT j.loan_id, j.mrp_timestamp, j.timestamp_month, j.timestamp_year,
j.ever_30, j.ever_90, j.ever_180, j.delinquency_30, j.delinquency_90, j.delinquency_180,
t.delinquency_12, t.upb_12
FROM main.joined_df as j LEFT OUTER JOIN main.testdf as t
ON j.loan_id = t.loan_id and j.timestamp_year = t.timestamp_year and j.timestamp_month = t.timestamp_month"""
results = pyblazing.run_query(query, tables)
Chronometer.show(chronometer, 'Combine joind 12 month')
return results
def join_ever_delinq_features(everdf_tmp, delinq_merge, **kwargs):
chronometer = Chronometer.makeStarted()
tables = {"everdf": everdf_tmp, "delinq": delinq_merge}
query = """SELECT everdf.loan_id as loan_id, ever_30, ever_90, ever_180,
COALESCE(delinquency_30, DATE '1970-01-01') as delinquency_30,
COALESCE(delinquency_90, DATE '1970-01-01') as delinquency_90,
COALESCE(delinquency_180, DATE '1970-01-01') as delinquency_180 FROM main.everdf as everdf
LEFT OUTER JOIN main.delinq as delinq ON everdf.loan_id = delinq.loan_id"""
result_merge = pyblazing.run_query(query, tables)
Chronometer.show(chronometer, 'Create ever deliquency features')
return result_merge
def merge_names(names_table, acq_table):
chronometer = Chronometer.makeStarted()
tables = {
names_table.name: names_table.columns,
acq_table.name: acq_table.columns
}
query = """SELECT loan_id, orig_channel, orig_interest_rate, orig_upb, orig_loan_term,
orig_date, first_pay_date, orig_ltv, orig_cltv, num_borrowers, dti, borrower_credit_score,
first_home_buyer, loan_purpose, property_type, num_units, occupancy_status, property_state,
zip, mortgage_insurance_percent, product_type, coborrow_credit_score, mortgage_insurance_type,
relocation_mortgage_indicator, new_seller_name as seller_name
FROM main.acq as a LEFT OUTER JOIN main.names as n ON a.seller_name = n.seller_name"""
result = pyblazing.run_query(query, tables)
Chronometer.show(chronometer, 'Create Acquisition (Merge Names)')
return result
def create_joined_df(gdf, everdf, **kwargs):
chronometer = Chronometer.makeStarted()
tables = {"perf": gdf, "everdf": everdf}
query = """SELECT perf.loan_id as loan_id,
perf.monthly_reporting_period as mrp_timestamp,
EXTRACT(MONTH FROM perf.monthly_reporting_period) as timestamp_month,
EXTRACT(YEAR FROM perf.monthly_reporting_period) as timestamp_year,
perf.current_loan_delinquency_status as delinquency_12,
perf.current_actual_upb as upb_12,
everdf.ever_30 as ever_30,
everdf.ever_90 as ever_90,
everdf.ever_180 as ever_180,
everdf.delinquency_30 as delinquency_30,
everdf.delinquency_90 as delinquency_90,
everdf.delinquency_180 as delinquency_180
FROM main.perf as perf
LEFT OUTER JOIN main.everdf as everdf ON perf.loan_id = everdf.loan_id"""
results = pyblazing.run_query(query, tables)
results.columns['upb_12'] = results.columns['upb_12'].fillna(999999999)
results.columns['delinquency_12'] = results.columns[
'delinquency_12'].fillna(-1)
results.columns['ever_30'] = results.columns['ever_30'].fillna(-1)
results.columns['ever_90'] = results.columns['ever_90'].fillna(-1)
results.columns['ever_180'] = results.columns['ever_180'].fillna(-1)
results.columns['delinquency_30'] = results.columns[
'delinquency_30'].fillna(-1)
results.columns['delinquency_90'] = results.columns[
'delinquency_90'].fillna(-1)
results.columns['delinquency_180'] = results.columns[
'delinquency_180'].fillna(-1)
Chronometer.show(chronometer, 'Create Joined DF')
return results
def open_perf_table(table_ref):
for key in table_ref.keys():
sql = 'select * from main.%(table_name)s' % {
"table_name": key.table_name
}
return pyblazing.run_query(sql, table_ref)
