def imbalance_weight_step(run_id): """ Author : Thomas Mahoney / Elinor Thorne Date : 30 April 2018 / 2 October 2018 Purpose : Runs the imbalance weight steps of the ips process Params : run_id - the id for the current run. connection - a connection object pointing at the database. Returns : NA """ # Load configuration variables config = ServicesConfiguration().get_imbalance_weight() # Populate Survey Data For Imbalance Wt idm.populate_survey_data_for_step(run_id, config) # Copy Imbalance Wt PVs For Survey Data idm.copy_step_pvs_for_survey_data(run_id, config) # Apply Imbalance Wt PVs On Survey Data process_variables.process(dataset='survey', in_table_name='SAS_SURVEY_SUBSAMPLE', out_table_name='SAS_IMBALANCE_SPV', in_id='serial') # Update Survey Data With Imbalance Wt PVs Output idm.update_survey_data_with_step_pv_output(config) # Retrieve data from SQL survey_data = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) # Calculate Imbalance Weight survey_data_out, summary_data_out = \ calculate_imb_weight.do_ips_imbweight_calculation(survey_data, serial="SERIAL", shift_weight="SHIFT_WT", non_response_weight="NON_RESPONSE_WT", min_weight="MINS_WT", traffic_weight="TRAFFIC_WT", oo_weight="UNSAMP_TRAFFIC_WT", imbalance_weight="IMBAL_WT") # Insert data to SQL db.insert_dataframe_into_table(config["temp_table"], survey_data_out) db.insert_dataframe_into_table(config["sas_ps_table"], summary_data_out) # Update Survey Data With Imbalance Wt Results idm.update_survey_data_with_step_results(config) # Store Survey Data With Imbalance Wt Results idm.store_survey_data_with_step_results(run_id, config) # Store Imbalance Weight Summary idm.store_step_summary(run_id, config)
def minimums_weight_step(run_id): """ Author : Thomas Mahoney / Elinor Thorne Date : 30 April 2018 / 2 October 2018 Purpose : Runs the minimums weight steps of the ips process Params : run_id - the id for the current run. connection - a connection object pointing at the database. Returns : NA """ # Load configuration variables config = ServicesConfiguration().get_minimums_weight() # Populate Survey Data For Minimums Wt idm.populate_survey_data_for_step(run_id, config) # Copy Minimums Wt PVs For Survey Data idm.copy_step_pvs_for_survey_data(run_id, config) # Apply Minimums Wt PVs On Survey Data process_variables.process(dataset='survey', in_table_name='SAS_SURVEY_SUBSAMPLE', out_table_name='SAS_MINIMUMS_SPV', in_id='serial') # Update Survey Data with Minimums Wt PVs Output idm.update_survey_data_with_step_pv_output(config) # Retrieve data from SQL survey_data = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) # Calculate Minimums Weight output_data, summary_data = \ calculate_minimums_weight.do_ips_minweight_calculation(df_surveydata=survey_data, serial_num='SERIAL', shift_weight='SHIFT_WT', nr_weight='NON_RESPONSE_WT', min_weight='MINS_WT') # Insert data to SQL db.insert_dataframe_into_table(config["temp_table"], output_data) db.insert_dataframe_into_table(config["sas_ps_table"], summary_data) # Update Survey Data With Minimums Wt Results idm.update_survey_data_with_step_results(config) # Store Survey Data With Minimums Wt Results idm.store_survey_data_with_step_results(run_id, config) # Store Minimums Wt Summary idm.store_step_summary(run_id, config)
def rail_imputation_step(run_id): """ Author : Thomas Mahoney / Elinor Thorne Date : 30 April 2018 / 2 October 2018 Purpose : Runs the rail imputation steps of the ips process Params : run_id - the id for the current run. connection - a connection object pointing at the database. Returns : NA """ # Load configuration variables config = ServicesConfiguration().get_rail_imputation() # Populate Survey Data For Rail Imputation idm.populate_survey_data_for_step(run_id, config) # Copy Rail Imp PVs For Survey Data idm.copy_step_pvs_for_survey_data(run_id, config) # Apply Rail Imp PVs On Survey Data process_variables.process(dataset='survey', in_table_name='SAS_SURVEY_SUBSAMPLE', out_table_name='SAS_RAIL_SPV', in_id='serial') # Update Survey Data with Rail Imp PV Output idm.update_survey_data_with_step_pv_output(config) # Retrieve data from SQL survey_data = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) # Calculate Rail Imputation survey_data_out = calculate_rail_imputation.do_ips_railex_imp( survey_data, var_serial='SERIAL', var_final_weight='FINAL_WT', minimum_count_threshold=30) # Insert data to SQL db.insert_dataframe_into_table(config["temp_table"], survey_data_out) # Update Survey Data With Rail Imp Results idm.update_survey_data_with_step_results(config) # Store Survey Data With Rail Imp Results idm.store_survey_data_with_step_results(run_id, config)
def fares_imputation_step(run_id): """ Author : Thomas Mahoney / Elinor Thorne Date : 30 April 2018 / 2 October 2018 Purpose : Runs the fares imputation steps of the ips process Params : run_id - the id for the current run. connection - a connection object pointing at the database. Returns : NA """ # Load configuration variables config = ServicesConfiguration().get_fares_imputation() # Populate Survey Data For Fares Imputation idm.populate_survey_data_for_step(run_id, config) # Copy Fares Imp PVs For Survey Data idm.copy_step_pvs_for_survey_data(run_id, config) # Apply Fares Imp PVs On Survey Data process_variables.process(dataset='survey', in_table_name='SAS_SURVEY_SUBSAMPLE', out_table_name='SAS_FARES_SPV', in_id='serial') # Update Survey Data with Fares Imp PV Output idm.update_survey_data_with_step_pv_output(config) # Retrieve data from SQL survey_data = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) # Calculate Fares Imputation survey_data_out = calculate_fares_imputation.do_ips_fares_imputation(survey_data, var_serial='SERIAL', num_levels=9, measure='mean') # Insert data to SQL db.insert_dataframe_into_table(config["temp_table"], survey_data_out) # Update Survey Data With Fares Imp Results idm.update_survey_data_with_step_results(config) # Store Survey Data With Fares Imp Results idm.store_survey_data_with_step_results(run_id, config)
def town_stay_expenditure_imputation_step(run_id): """ Author : Thomas Mahoney / Elinor Thorne Date : 30 April 2018 / 2 October 2018 Purpose : Runs the town stay expenditure imputation steps of the ips process Params : run_id - the id for the current run. connection - a connection object pointing at the database. Returns : NA """ # Load configuration variables config = ServicesConfiguration().get_town_and_stay_expenditure() # Populate Survey Data For TSE Imputation idm.populate_survey_data_for_step(run_id, config) # Copy TSE Imputation PVs For Survey Data idm.copy_step_pvs_for_survey_data(run_id, config) # Apply TSE Imputation PVs On Survey Data process_variables.process(dataset='survey', in_table_name='SAS_SURVEY_SUBSAMPLE', out_table_name='SAS_TOWN_STAY_SPV', in_id='serial') # Update Survey Data with TSE Imputation PV Output idm.update_survey_data_with_step_pv_output(config) # Retrieve data from SQL survey_data = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) # Calculate TSE Imputation survey_data_out = calculate_town_and_stay_expenditure.do_ips_town_exp_imp( survey_data, var_serial="SERIAL", var_final_wt="FINAL_WT") # Insert data to SQL db.insert_dataframe_into_table(config["temp_table"], survey_data_out) # Update Survey Data With TSE Imputation Results idm.update_survey_data_with_step_results(config) # Store Survey Data With TSE Imputation Results idm.store_survey_data_with_step_results(run_id, config)
def non_response_weight_step(run_id): """ Author : Thomas Mahoney / Elinor Thorne Date : 26 April 2018 / 2 October 2018 Purpose : Runs the non response weight steps of the ips process Params : run_id - the id for the current run. Returns : NA """ # Load configuration variables config = ServicesConfiguration().get_non_response() # Populate Survey Data For Non Response Wt idm.populate_survey_data_for_step(run_id, config) # Populate Non Response Data idm.populate_step_data(run_id, config) # Copy Non Response Wt PVs For Survey Data idm.copy_step_pvs_for_survey_data(run_id, config) # Apply Non Response Wt PVs On Survey Data process_variables.process(dataset='survey', in_table_name='SAS_SURVEY_SUBSAMPLE', out_table_name='SAS_NON_RESPONSE_SPV', in_id='serial') # Update Survey Data with Non Response Wt PVs Output idm.update_survey_data_with_step_pv_output(config) # Copy Non Response Wt PVs for Non Response Data idm.copy_step_pvs_for_step_data(run_id, config) # Apply Non Response Wt PVs On Non Response Data process_variables.process(dataset='non_response', in_table_name='SAS_NON_RESPONSE_DATA', out_table_name='SAS_NON_RESPONSE_PV', in_id='REC_ID') # Update NonResponse Data With PVs Output idm.update_step_data_with_step_pv_output(config) # Retrieve data from SQL survey_data = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) non_response_data = db.get_table_values(config["data_table"]) # Calculate Non Response Weight survey_data_out, summary_data_out = \ calculate_nonresponse_weight.do_ips_nrweight_calculation(survey_data, non_response_data, 'NON_RESPONSE_WT', 'SERIAL') db.insert_dataframe_into_table(config["temp_table"], survey_data_out) db.insert_dataframe_into_table(config["sas_ps_table"], summary_data_out) # Update Survey Data With Non Response Wt Results idm.update_survey_data_with_step_results(config) # Store Survey Data With NonResponse Wt Results idm.store_survey_data_with_step_results(run_id, config) # Store Non Response Wt Summary idm.store_step_summary(run_id, config)
def traffic_weight_step(run_id): """ Author : Thomas Mahoney / Elinor Thorne Date : 30 April 2018 / 2 October 2018 Purpose : Runs the traffic weight steps of the ips process Params : run_id - the id for the current run. connection - a connection object pointing at the database. Returns : NA """ # Load configuration variables config = ServicesConfiguration().get_traffic_weight() # Populate Survey Data For Traffic Wt idm.populate_survey_data_for_step(run_id, config) # Populate Traffic Data idm.populate_step_data(run_id, config) # Copy Traffic Wt PVs For Survey Data idm.copy_step_pvs_for_survey_data(run_id, config) # Apply Traffic Wt PV On Survey Data process_variables.process(dataset='survey', in_table_name='SAS_SURVEY_SUBSAMPLE', out_table_name='SAS_TRAFFIC_SPV', in_id='serial') # Update Survey Data with Traffic Wt PV Output idm.update_survey_data_with_step_pv_output(config) # Copy Traffic Wt PVs For Traffic Data idm.copy_step_pvs_for_step_data(run_id, config) # Apply Traffic Wt PV On Traffic Data process_variables.process(dataset='traffic', in_table_name='SAS_TRAFFIC_DATA', out_table_name='SAS_TRAFFIC_PV', in_id='REC_ID') # Update Traffic Data With Traffic Wt PV Output idm.update_step_data_with_step_pv_output(config) # Retrieve data from SQL survey_data = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) traffic_data = db.get_table_values(config["data_table"]) # Calculate Traffic Weight output_data, summary_data = do_ips_trafweight_calculation_with_R( survey_data, traffic_data) # Insert data to SQL db.insert_dataframe_into_table(config["temp_table"], output_data) db.insert_dataframe_into_table(config["sas_ps_table"], summary_data) # Update Survey Data With Traffic Wt Results idm.update_survey_data_with_step_results(config) # Store Survey Data With Traffic Wt Results idm.store_survey_data_with_step_results(run_id, config) # Store Traffic Wt Summary idm.store_step_summary(run_id, config)
def shift_weight_step(run_id): """ Author : Thomas Mahoney / Elinor Thorne Date : 26 April 2018 / 2 October 2018 Purpose : Runs the shift weight steps of the ips process Params : run_id - the id for the current run. connection - a connection object pointing at the database. Returns : NA """ # Load configuration variables config = ServicesConfiguration().get_shift_weight() # Populate Survey Data For Shift Wt idm.populate_survey_data_for_step(run_id, config) # Populate Shift Data idm.populate_step_data(run_id, config) # Copy Shift Wt PVs For Survey Data idm.copy_step_pvs_for_survey_data(run_id, config) # Apply Shift Wt PVs On Survey Data process_variables.process(dataset='survey', in_table_name='SAS_SURVEY_SUBSAMPLE', out_table_name='SAS_SHIFT_SPV', in_id='serial') # Update Survey Data with Shift Wt PV Output idm.update_survey_data_with_step_pv_output(config) # Copy Shift Wt PVs For Shift Data idm.copy_step_pvs_for_step_data(run_id, config) # Apply Shift Wt PVs On Shift Data process_variables.process(dataset='shift', in_table_name='SAS_SHIFT_DATA', out_table_name='SAS_SHIFT_PV', in_id='REC_ID') # Update Shift Data with PVs Output idm.update_step_data_with_step_pv_output(config) # Retrieve data from SQL survey_data = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) shift_data = db.get_table_values(config["data_table"]) # shift_data = sas_shift_schema.convert_dtype(shift_data) # Calculate Shift Weight survey_data_out, summary_data_out = \ calculate_shift_weight.do_ips_shift_weight_calculation(survey_data, shift_data, serial_number='SERIAL', shift_weight='SHIFT_WT') # Insert data to SQL db.insert_dataframe_into_table(config["temp_table"], survey_data_out) db.insert_dataframe_into_table(config["sas_ps_table"], summary_data_out) # Update Survey Data With Shift Wt Results idm.update_survey_data_with_step_results(config) # Store Survey Data With Shift Wt Results idm.store_survey_data_with_step_results(run_id, config) # Store Shift Wt Summary idm.store_step_summary(run_id, config)
def unsampled_weight_step(run_id): """ Author : Thomas Mahoney / Elinor Thorne Date : 30 April 2018 / 2 October 2018 Purpose : Runs the unsampled weight steps of the ips process Params : run_id - the id for the current run. Returns : None """ # Load configuration variables config = ServicesConfiguration().get_unsampled_weight() # Populate Survey Data For Unsampled Wt idm.populate_survey_data_for_step(run_id, config) # Populate Unsampled Data idm.populate_step_data(run_id, config) # Copy Unsampled Wt PVs For Survey Data idm.copy_step_pvs_for_survey_data(run_id, config) # Apply Unsampled Wt PV On Survey Data process_variables.process(dataset='survey', in_table_name='SAS_SURVEY_SUBSAMPLE', out_table_name='SAS_UNSAMPLED_OOH_SPV', in_id='serial') # Update Survey Data with Unsampled Wt PV Output idm.update_survey_data_with_step_pv_output(config) # Copy Unsampled Wt PVs For Unsampled Data idm.copy_step_pvs_for_step_data(run_id, config) # Apply Unsampled Wt PV On Unsampled Data process_variables.process(dataset='unsampled', in_table_name='SAS_UNSAMPLED_OOH_DATA', out_table_name='SAS_UNSAMPLED_OOH_PV', in_id='REC_ID') # Update Unsampled Data With PV Output idm.update_step_data_with_step_pv_output(config) # Retrieve data from SQL survey_data = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) unsampled_data = db.get_table_values(config["data_table"]) # Calculate Unsampled Weight output_data, summary_data = calculate_unsampled_weight.do_ips_unsampled_weight_calculation( df_surveydata=survey_data, serial_num='SERIAL', shift_weight='SHIFT_WT', nr_weight='NON_RESPONSE_WT', min_weight='MINS_WT', traffic_weight='TRAFFIC_WT', out_of_hours_weight="UNSAMP_TRAFFIC_WT", df_ustotals=unsampled_data, min_count_threshold=30) # Insert data to SQL db.insert_dataframe_into_table(config["temp_table"], output_data) db.insert_dataframe_into_table(config["sas_ps_table"], summary_data) # Update Survey Data With Unsampled Wt Results idm.update_survey_data_with_step_results(config) # Store Survey Data With Unsampled Wt Results idm.store_survey_data_with_step_results(run_id, config) # Store Unsampled Weight Summary idm.store_step_summary(run_id, config)
def test_update_survey_data_with_step_pv_output_with_name_minimums_weight( self, database_connection): step_config = { 'name': "MINIMUMS_WEIGHT", 'spv_table': 'SAS_MINIMUMS_SPV', "pv_columns": [ "'MINS_FLAG_PV'", "'MINS_PORT_GRP_PV'", "'MINS_CTRY_GRP_PV'", "'MINS_NAT_GRP_PV'", "'MINS_CTRY_PORT_GRP_PV'" ], "temp_table": "SAS_MINIMUMS_WT", "sas_ps_table": "SAS_PS_MINIMUMS", } run_id = 'update-survey-pvs' # delete the data in the table so that we have no data in table for test db.delete_from_table(idm.SAS_SURVEY_SUBSAMPLE_TABLE) db.delete_from_table(step_config['spv_table']) # read and insert into the database the survey data test_survey_data = pd.read_pickle(STEP_PV_OUTPUT_PATH + 'update_survey_data_pvs.pkl') db.insert_dataframe_into_table(idm.SAS_SURVEY_SUBSAMPLE_TABLE, test_survey_data, database_connection) # read and insert into the database the pvs test_nr_pv_data = pd.read_csv(STEP_PV_OUTPUT_PATH + 'test_mw_pv_data.csv') db.insert_dataframe_into_table(step_config['spv_table'], test_nr_pv_data, database_connection) # call the test function idm.update_survey_data_with_step_pv_output(database_connection, step_config) # get the newly updated table data write the results back to csv to read back and resolve formatting results = db.get_table_values(idm.SAS_SURVEY_SUBSAMPLE_TABLE) # write the results back to csv, and read the csv back (this solves the data type matching issues) temp_output = STEP_PV_OUTPUT_PATH + 'update_survey_data_pvs_result_results.csv' results.to_csv(temp_output, index=False) results = pd.read_csv(temp_output) # remove the temporary written file os.remove(temp_output) # clean test data before actually testing results db.delete_from_table(idm.SAS_SURVEY_SUBSAMPLE_TABLE) db.delete_from_table(step_config['spv_table']) # check ONLY updated pv columns are as expected in results, check NaN values are handled correctly stripped_pv_cols = [ item.replace("'", "") for item in step_config['pv_columns'] ] stripped_pv_cols.insert(0, 'SERIAL') # add the SERIAL column test_dummy_1 = results[stripped_pv_cols] # get the SERIAL column values as a list, and select rows from updated data that match input data serials = test_nr_pv_data['SERIAL'] test_dummy_2 = test_dummy_1[test_dummy_1['SERIAL'].isin(serials)] # clean test data before actually testing results db.delete_from_table(idm.SAS_SURVEY_SUBSAMPLE_TABLE) db.delete_from_table(step_config['spv_table']) # check updated pv columns match the corresponding dummy values assert_frame_equal(test_dummy_2, test_nr_pv_data, check_dtype=False, check_like=True) # check that the non-pv column values are still the same by dropping pv columns columns_to_drop = [ item.replace("'", "") for item in step_config['pv_columns'] ] new_res = results.drop(columns_to_drop, axis=1) new_test_res = test_survey_data.drop(columns_to_drop, axis=1) assert_frame_equal(new_res, new_test_res, check_dtype=False, check_like=True) # check that spv_table has been deleted results_2 = db.get_table_values(step_config['spv_table']) assert len(results_2) == 0 results = db.get_table_values(idm.SAS_PROCESS_VARIABLES_TABLE) assert len(results) == 0 results = db.get_table_values(step_config["temp_table"]) assert len(results) == 0 results = db.get_table_values(step_config["sas_ps_table"]) assert len(results) == 0