<jupyter_start><jupyter_text>[HYPOTHESIS] All error codes can be classified as completely fatal or completely non fatal Hypothesis**We believe** all error codes can be classified as completely fatal or completely non fatal**We will know this to be true** when we can attribute every failed transfer to an error code which is not present in any successfully integrated transfer  Approach/Scope- Take 6 months of data - Sept 2020 to Feb 2021- Clearly label each transfer as integrated or failed   - Correct duplicate transfers   - Remove pending transfers [REMOVED!]- Merge intermediate and final error codes- Identify % failure for each error code and designate anything with 100% error code as fatal - Ensure that all failures contain one of these fatal error codes<jupyter_code>import pandas as pd
import numpy as np
def Series_of_lists_value_counts(Series):
    # Replace any nan values in list
    Series=Series.apply(lambda row: ['None' if np.isnan(x) else x for x in row])
    # Convert this into a dataframe of list items in order
    journey_frame=pd.DataFrame.from_records(Series.tolist())
    # To ensure grouping of different list lengths, fill gaps
    journey_frame=journey_frame.fillna('n/a')
    # Store index for grouping
    grouping_index=list(journey_frame.columns)
    # Add column to aggreate on for group
    journey_frame['Total Occurences']=1

    # Now do the actual aggregate
    journey_frame=journey_frame.groupby(grouping_index).agg('count').sort_values(by='Total Occurences',ascending=False)
    
    return journey_frame.reset_index().replace({'n/a':np.nan})<jupyter_output><empty_output><jupyter_text>Take 6 months of data<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 = pd.concat((
    pd.read_parquet(f)
    for f in transfer_input_files
))<jupyter_output><empty_output><jupyter_text>Clearly label each transfer as integrated or failed  Correct duplicate transfers<jupyter_code>successful_transfers_bool = transfers['request_completed_ack_codes'].apply(lambda x: True in [(np.isnan(i) or i==15) for i in x])
transfers_without_integrated_status_bool = transfers['status'] != 'INTEGRATED'
successful_transfers_without_integrated_status = transfers[(successful_transfers_bool & transfers_without_integrated_status_bool)]
successful_transfers_without_integrated_status['status'].value_counts()
Series_of_lists_value_counts(successful_transfers_without_integrated_status['request_completed_ack_codes'].apply(set))
transfers_with_final_outcome = transfers.copy()
transfers_with_final_outcome.loc[successful_transfers_bool, 'status'] = 'INTEGRATED'
transfers=transfers_with_final_outcome.copy()<jupyter_output><empty_output><jupyter_text>Remove pending transfers [Removed!]<jupyter_code>#transfers_with_final_outcome = transfers_with_final_outcome.loc[(transfers_with_final_outcome['status'] == 'INTEGRATED') | (transfers_with_final_outcome['status'] == 'FAILED')]<jupyter_output><empty_output><jupyter_text>Merge sender, intermediate and final error codes<jupyter_code>transfers['all_error_codes'] = transfers.apply(lambda x: [*[x['sender_error_code']],*x['intermediate_error_codes'], *x['request_completed_ack_codes']],axis=1)

# Remove the "None" tag that we used as a flag for a successfully integrated transfer
transfers['all_error_codes'] = transfers['all_error_codes'].apply(lambda x: [i for i in x if np.isfinite(i)])<jupyter_output><empty_output><jupyter_text>Identify % failure for each error code and designate anything with 100% error code as fatal<jupyter_code>def error_code_failure_rates(transfers_df):
    reduced_transfers = transfers_df[['status', 'all_error_codes','conversation_id']]
    has_errors = reduced_transfers["all_error_codes"].apply(len) > 0
    transfers_exploded = reduced_transfers[has_errors].explode("all_error_codes")
    error_code_status_counts=pd.pivot_table(transfers_exploded,index='all_error_codes',columns='status',values='conversation_id',aggfunc='count')
    error_code_status_counts=error_code_status_counts.fillna(0)
    error_code_summary=error_code_status_counts.copy()
    error_code_summary['Volume']=error_code_summary.sum(axis=1)
    error_code_summary['% Integrated']=100*(error_code_summary['INTEGRATED']/error_code_summary['Volume'])
    
    return error_code_summary.sort_values(by=['% Integrated','Volume'])
    
error_code_table=error_code_failure_rates(transfers)
error_code_table<jupyter_output><empty_output><jupyter_text>Allocate fatal error codes and see what happens if we remove them from the data<jupyter_code>def fatal_error_implementation_effect(transfers,integration_pc_threshold):
    fatal_codes=list(error_code_table.loc[(error_code_table['% Integrated']<=integration_pc_threshold)].index)

    # Find which conversations contain one of these fatal codes and see what number of integrations would contain these
    transfers_fatal_error_flagged=transfers.copy()
    transfers_fatal_error_flagged['Contains Fatal Error']='Yes'
    non_fatal_conversations_bool=transfers_fatal_error_flagged['all_error_codes'].apply(lambda x: len(set(fatal_codes).intersection(set(x)))==0)
    transfers_fatal_error_flagged.loc[non_fatal_conversations_bool,'Contains Fatal Error']='No'

    print("Fatal Error Code Threshold (Max % Integration rate Allowed: " +str(integration_pc_threshold) +"%")
    print("Fatal Error Codes:")
    print(fatal_codes)
    return pd.pivot_table(transfers_fatal_error_flagged,index='status',columns='Contains Fatal Error',aggfunc='count',values='conversation_id').fillna(0).astype(int)
fatal_error_implementation_effect(transfers,0)
fatal_error_implementation_effect(transfers,0.1)
fatal_error_implementation_effect(transfers,1)<jupyter_output>Fatal Error Code Threshold (Max % Integration rate Allowed: 1%
Fatal Error Codes:
[205.0, 9.0, 26.0, 24.0, 21.0, 7.0, 6.0, 10.0, 14.0, 30.0, 99.0, 23.0]<jupyter_text>Hmm.. interesting; let's also look at the success rate for each 'combination' of error codes<jupyter_code>def single_status_error_count(transfers_df,status):
    status_bool=transfers_df['status']==status
    status_count=Series_of_lists_value_counts(transfers_df.loc[status_bool,'set_error_codes'])
    status_count=status_count.rename({'Total Occurences':status},axis=1).fillna(0)
    
    for i in range(4):
        if i not in status_count.columns:
            status_count[i]=0

    return status_count
# Convert the list of error codes into the unique set of error codes
transfers_with_error_code_set=transfers.copy()
transfers_with_error_code_set['set_error_codes']=transfers_with_error_code_set['all_error_codes'].apply(set)

# For each status, generate a count of which set of error codes occurred
status_error_code_counts=dict()
unique_statuses=transfers_with_error_code_set['status'].value_counts().index
for status in unique_statuses:
    status_error_code_counts[status]=single_status_error_count(transfers_with_error_code_set,status)
    
# Merge this data together into one frame
error_combination_counts=status_error_code_counts['INTEGRATED'].merge(status_error_code_counts['FAILED'],left_on=[0,1,2,3],right_on=[0,1,2,3],how='outer').fillna(0)
error_combination_counts=error_combination_counts.merge(status_error_code_counts['PENDING'],left_on=[0,1,2,3],right_on=[0,1,2,3],how='outer').fillna(0)
error_combination_counts=error_combination_counts.merge(status_error_code_counts['PENDING_WITH_ERROR'],left_on=[0,1,2,3],right_on=[0,1,2,3],how='outer').fillna(0)

# Calculate the success rate
error_combination_counts['Volume']=error_combination_counts[unique_statuses].sum(axis=1)
error_combination_counts['% Integrated']=100*(error_combination_counts['INTEGRATED']/error_combination_counts['Volume'])
error_combination_counts.sort_values(by='Volume',ascending=False).set_index(list(range(4)))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10504926108374384, 'fraction_numerical': 0.016995073891625617, 'mean_word_length': 8.063616071428571, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, 'https://': 0, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '277752', 'license': 'Apache-2.0', 'n_tokens_mistral': 2717, 'n_tokens_neox': 2595, 'n_words': 493, 'path': 'notebooks/19-PRMT-1894--fatal-and-non-fatal-error-codes.ipynb', 'repo_name': 'uk-gov-mirror/nhsconnect.prm-gp2gp-data-sandbox', 'size': '8120'}