<jupyter_start><jupyter_text>[“HYPOTHESIS”] Creation of tables to determine and assess the most common causes of failure to integrate - Import Data and adjust for duplicates issue- Implement the sender error pipeline change- Add in supplierTable 1:- Relabel status to reflect integrated late- Add in column for absolutely no errors- Create seperate dataframes for each error type and concatenate- generate the pivot table- Add in Error Descriptions/Name- Add in Supplier Pathway %s- Create Monthly Views<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Import 6 months of data and adjust for duplicates issue<jupyter_code>transfer_file_location = "s3://prm-gp2gp-data-sandbox-dev/transfers-duplicates-hypothesis/"
transfer_files = [
    "9-2020-transfers.parquet",
    "10-2020-transfers.parquet",
    "11-2020-transfers.parquet",
    "12-2020-transfers.parquet",
    "1-2021-transfers.parquet",
    "2-2021-transfers.parquet"
]
transfer_input_files = [transfer_file_location + f for f in transfer_files]
transfers_raw = pd.concat((
    pd.read_parquet(f)
    for f in transfer_input_files
))

transfers_raw=transfers_raw.drop(['requesting_supplier','sending_supplier'],axis=1)

asid_lookup_file = "s3://prm-gp2gp-data-sandbox-dev/asid-lookup/asidLookup-Mar-2021.csv.gz"
asid_lookup = pd.read_csv(asid_lookup_file)
transfers=transfers_raw.copy()
successful_transfers_bool = transfers['request_completed_ack_codes'].apply(lambda x: True in [(np.isnan(i) or i==15) for i in x])
transfers.loc[successful_transfers_bool,'status']='INTEGRATED'
pending_sender_error_codes=[6,7,10,24,30,23,14,99]
transfers_with_pending_sender_code_bool=transfers['sender_error_code'].isin(pending_sender_error_codes)
transfers_with_pending_with_error_bool=transfers['status']=='PENDING_WITH_ERROR'
transfers_which_need_pending_to_failure_change_bool=transfers_with_pending_sender_code_bool & transfers_with_pending_with_error_bool
transfers.loc[transfers_which_need_pending_to_failure_change_bool,'status']='FAILED'
# Add in who the supplier is
supplier_renaming = {
    "EGTON MEDICAL INFORMATION SYSTEMS LTD (EMIS)":"EMIS",
    "IN PRACTICE SYSTEMS LTD":"Vision",
    "MICROTEST LTD":"Microtest",
    "THE PHOENIX PARTNERSHIP":"TPP",
    None: "Unknown"
}

lookup = asid_lookup[["ASID", "MName"]]
transfers = transfers.merge(lookup, left_on='requesting_practice_asid',right_on='ASID',how='left')
transfers = transfers.rename({'MName': 'requesting_supplier', 'ASID': 'requesting_supplier_asid'}, axis=1)
transfers = transfers.merge(lookup, left_on='sending_practice_asid',right_on='ASID',how='left')
transfers = transfers.rename({'MName': 'sending_supplier', 'ASID': 'sending_supplier_asid'}, axis=1)

transfers["sending_supplier"] = transfers["sending_supplier"].replace(supplier_renaming.keys(), supplier_renaming.values())
transfers["requesting_supplier"] = transfers["requesting_supplier"].replace(supplier_renaming.keys(), supplier_renaming.values())
error_code_lookup_file = pd.read_csv("https://raw.githubusercontent.com/nhsconnect/prm-gp2gp-data-sandbox/master/data/gp2gp_response_codes.csv")
error_code_lookup_file<jupyter_output><empty_output><jupyter_text>Table 1: High Level View of Issues  Relabel status to reflect integrated late<jupyter_code>eight_days_in_seconds=8*24*60*60
transfers_after_sla_bool=transfers['sla_duration']>eight_days_in_seconds
transfers_with_integrated_bool=transfers['status']=='INTEGRATED'
transfers_integrated_late_bool=transfers_after_sla_bool & transfers_with_integrated_bool
transfers.loc[transfers_integrated_late_bool,'status']='INTEGRATED LATE'<jupyter_output><empty_output><jupyter_text>Add in column for absolutely no errors<jupyter_code>transfers_without_sender_error_bool=transfers['sender_error_code'].isna()
transfers_without_intermediate_error_bool=transfers['intermediate_error_codes'].apply(len)==0
transfers_without_final_ack_error_bool=transfers['request_completed_ack_codes'].apply(lambda lis: [x for x in lis if np.isfinite(x)]).apply(len)==0

transfers_without_any_error_bool=transfers_without_sender_error_bool & transfers_without_intermediate_error_bool & transfers_without_final_ack_error_bool
transfers['No error codes']=np.nan
transfers.loc[transfers_without_any_error_bool,'No error codes']='No Error'<jupyter_output><empty_output><jupyter_text>Create seperate dataframes for each error type and concatenate<jupyter_code>reduced_transfers=transfers[['requesting_supplier','sending_supplier','sender_error_code','intermediate_error_codes','request_completed_ack_codes','No error codes','status','conversation_id']]<jupyter_output><empty_output><jupyter_text>Sender Error Table<jupyter_code>sender_table=reduced_transfers.drop(['intermediate_error_codes','request_completed_ack_codes','No error codes'],axis=1)
sender_table=sender_table.loc[~sender_table['sender_error_code'].isna()]
sender_table=sender_table.rename({'sender_error_code':'Error Code'},axis=1)
sender_table['Error Type']='Sender'
sender_table=sender_table.groupby(['sending_supplier','requesting_supplier','Error Type','Error Code','status']).agg('count').rename({'conversation_id':'Total Volume'},axis=1).reset_index()<jupyter_output><empty_output><jupyter_text>Intermediate Error Table<jupyter_code>intermediate_table=reduced_transfers.drop(['sender_error_code','request_completed_ack_codes','No error codes'],axis=1)
intermediate_table=intermediate_table.loc[intermediate_table['intermediate_error_codes'].apply(len)>0]
intermediate_table=intermediate_table.explode('intermediate_error_codes')
intermediate_table=intermediate_table.rename({'intermediate_error_codes':'Error Code'},axis=1)
intermediate_table['Error Type']='Intermediate'
intermediate_table=intermediate_table.groupby(['sending_supplier','requesting_supplier','Error Type','Error Code','status']).agg('count').rename({'conversation_id':'Total Volume'},axis=1).reset_index()<jupyter_output><empty_output><jupyter_text>Final Request Acknowledgements Table<jupyter_code>reqack_table=reduced_transfers.drop(['sender_error_code','intermediate_error_codes','No error codes'],axis=1)
reqack_table['request_completed_ack_codes']=reqack_table['request_completed_ack_codes'].apply(lambda lis: [x for x in lis if np.isfinite(x)])
reqack_table=reqack_table.loc[reqack_table['request_completed_ack_codes'].apply(len)>0]
reqack_table=reqack_table.explode('request_completed_ack_codes')
reqack_table=reqack_table.rename({'request_completed_ack_codes':'Error Code'},axis=1)
reqack_table['Error Type']='Final Request Acknowledgment'
reqack_table=reqack_table.groupby(['sending_supplier','requesting_supplier','Error Type','Error Code','status']).agg('count').rename({'conversation_id':'Total Volume'},axis=1).reset_index()<jupyter_output><empty_output><jupyter_text>No error codes Table<jupyter_code>noerror_table=reduced_transfers.drop(['intermediate_error_codes','request_completed_ack_codes','sender_error_code'],axis=1)
noerror_table=noerror_table.loc[~noerror_table['No error codes'].isna()]
noerror_table=noerror_table.rename({'No error codes':'Error Code'},axis=1)
noerror_table['Error Type']='No Error Code'
noerror_table=noerror_table.groupby(['sending_supplier','requesting_supplier','Error Type','Error Code','status']).agg('count').rename({'conversation_id':'Total Volume'},axis=1).reset_index()<jupyter_output><empty_output><jupyter_text>Concatenate Tables<jupyter_code>high_level_table=pd.concat([sender_table,intermediate_table,reqack_table,noerror_table])
high_level_table=high_level_table.sort_values(by='Total Volume',ascending=False)

high_level_table=high_level_table.reset_index().drop('index',axis=1)
high_level_table.index=high_level_table.index+1

high_level_table=high_level_table.merge(error_code_lookup_file,left_on='Error Code',right_on='ErrorCode',how='left')
high_level_table.loc[high_level_table['Error Code']=='No Error',['ErrorName','ResponseText']]='No Error'
high_level_table=high_level_table.drop(['ErrorCode','ErrorName'],axis=1)<jupyter_output><empty_output><jupyter_text>Add in Supplier Pathway %<jupyter_code>full_high_level_table=high_level_table.copy()
total_transfers_supplier_pathway=transfers.groupby(['sending_supplier','requesting_supplier']).agg({'conversation_id':'count'}).rename({'conversation_id':'Total Supplier Pathway Transfers'},axis=1).reset_index()
full_high_level_table=full_high_level_table.merge(total_transfers_supplier_pathway,left_on=['sending_supplier','requesting_supplier'],right_on=['sending_supplier','requesting_supplier'])
full_high_level_table['% Supplier Pathway Transfers']=(100*full_high_level_table['Total Volume']/full_high_level_table['Total Supplier Pathway Transfers']).round(2)
full_high_level_table=full_high_level_table.drop('Total Supplier Pathway Transfers',axis=1)
high_level_table=full_high_level_table.copy()
high_level_table<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12680831826401448, 'fraction_numerical': 0.010962929475587703, 'mean_word_length': 14.126495726495726, 'pattern_counts': {'":': 4, '<': 45, '<?xml version=': 0, '>': 48, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '481898', 'license': 'Apache-2.0', 'n_tokens_mistral': 3035, 'n_tokens_neox': 2933, 'n_words': 348, 'path': 'notebooks/24-PRMT-1975-generate-top-issues-assessment-tables.ipynb', 'repo_name': 'uk-gov-mirror/nhsconnect.prm-gp2gp-data-sandbox', 'size': '8848'}