<jupyter_start><jupyter_text>PRMT-1984 [HYPOTHESIS] The spread of EMIS-EMIS pending (without error) transfers is even across GP Practices NB: Could this be extended to Integrated Late?? HypothesisWe believe that EMIS-EMIS pending transfers are spread evenly across practicesWe will know this to be true when we see in the data that for these transfers, they are consistently spread cross the majority of practices rather than clustered in a smaller group of practices  Subsequent questions (later hypotheses)If transfers are in a cluster of smaller practices, what do the integrations look like for those practices? I.e. are they bad at integration and therefore makes sense that they have poor pending?<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
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
))
# This is only needed when using transfers-duplicates-hypothesis datasets
transfers_raw = transfers_raw.drop(["sending_supplier", "requesting_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)
# Given the findings in PRMT-1742 - many duplicate EHR errors are misclassified, the below reclassifies the relevant data
successful_transfers_bool = transfers_raw['request_completed_ack_codes'].apply(lambda x: True in [(np.isnan(i) or i==15) for i in x])
transfers = transfers_raw.copy()
transfers.loc[successful_transfers_bool, "status"] = "INTEGRATED"
transfers.loc[transfers['status']=='FAILED','sla_duration'].dropna()
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'
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'
twenty_eight_days_in_seconds=28*24*60*60
transfers_after_month_bool=transfers['sla_duration']>twenty_eight_days_in_seconds
transfers_pending_at_month_bool=transfers_after_month_bool & transfers_integrated_late_bool
transfers.loc[transfers_pending_at_month_bool,'status']='PENDING'
transfers_with_early_error_bool=(~transfers.loc[:,'sender_error_code'].isna()) |(~transfers.loc[:,'intermediate_error_codes'].apply(len)>0)
transfers.loc[transfers_with_early_error_bool & transfers_pending_at_month_bool,'status']='PENDING_WITH_ERROR'
# Supplier name mapping
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", "NACS"]]

transfers = transfers.merge(lookup, left_on='requesting_practice_asid',right_on='ASID',how='left').drop("NACS", axis=1)
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', 'NACS': 'sending_ods_code'}, 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())<jupyter_output><empty_output><jupyter_text>Select just relevant data: EMIS-EMIS<jupyter_code>both_suppliers_EMIS_bool=(transfers['requesting_supplier']=='EMIS') & (transfers['sending_supplier']=='EMIS')

relevant_transfers=transfers.loc[both_suppliers_EMIS_bool]
emis_emis_status_table=relevant_transfers['status'].value_counts()

emis_emis_status_table_pc=(100*emis_emis_status_table/emis_emis_status_table.sum()).round(2)

pd.concat([emis_emis_status_table.rename('Volume'),emis_emis_status_table_pc.rename('%')],axis=1)<jupyter_output><empty_output><jupyter_text>Pivot table of practice vs status volumes<jupyter_code>practice_status_table=pd.pivot_table(relevant_transfers,index='requesting_practice_asid',columns='status',values='conversation_id',aggfunc='count').fillna(0)
practice_status_table['TOTAL']=practice_status_table.sum(axis=1)
practice_status_pc=100*practice_status_table.div(practice_status_table['TOTAL'],axis=0).drop('TOTAL',axis=1)
practice_status_pc.columns=practice_status_pc.columns + " %"
practice_status_pc=practice_status_pc.round(2)
practice_status_table=pd.concat([practice_status_table,practice_status_pc],axis=1)
print(practice_status_table.shape[0])
print(practice_status_table['PENDING %'].median())
print(practice_status_table['PENDING %'].mean())
ax =practice_status_table['PENDING %'].plot.hist(bins=range(101),ylim=[0,50])
ax.set_xlabel('% Pending')
ax.set_ylabel('Number of GP Practices')
ax.set_title('Distribution of % transfers pending across GP Practices')
practice_status_table['PENDING %'].quantile(np.arange(0.1, 1.1, 0.1))
plt.gcf().savefig('EMIS_EMIS_pending_distribution.png')
practice_status_table.plot.scatter(x='TOTAL',y='PENDING %')
practice_status_table.plot.scatter(x='TOTAL',y='PENDING %',xlim=[0,1000])
practice_status_table.plot.scatter(x='PENDING %',y='INTEGRATED LATE %',xlim=[0,0.1])
practice_status_table[["FAILED %","INTEGRATED %","INTEGRATED LATE %","PENDING %","PENDING_WITH_ERROR %"]].corr().round(2)
practice_status_table.plot.scatter(x='PENDING %',y='FAILED %',ylim=[0,0.2])
practice_status_table.plot.scatter(x='TOTAL',y='PENDING')
practice_status_table.corr()
practice_status_table['PENDING'].max()/practice_status_table['PENDING'].sum()
practice_status_table=practice_status_table.sort_values(by='PENDING',ascending=False)
full_practice_status_table=asid_lookup.merge(practice_status_table, left_on='ASID', right_index=True, how='right')
full_practice_status_table=full_practice_status_table.drop(['MName','PName'],axis=1)
full_practice_status_table.head()

def generate_gp_table(gp_asid): 
    gp_practice_transfers=transfers.loc[transfers['requesting_practice_asid']==gp_asid].copy()
    gp_practice_transfers.loc[:,'Month']=gp_practice_transfers['date_requested'].copy().dt.to_period('M')

    gp_totals=pd.pivot_table(gp_practice_transfers,index='Month',columns='status',values='conversation_id',aggfunc='count').fillna(0)
    gp_percentages=pd.pivot_table(gp_practice_transfers,index='Month',columns='status',values='conversation_id',aggfunc='count').fillna(0).div(gp_practice_transfers['Month'].value_counts(),axis=0)
    gp_percentages=(100*gp_percentages).round(2)
    gp_percentages.columns='% '+gp_percentages.columns

    return pd.concat([gp_totals, gp_percentages], axis=1)<jupyter_output><empty_output><jupyter_text>Break down by user profile:  Ie is the issue related to a handful of practices or distributed?Consider for each practice, it's total number of transfers, it's outcomes - Rank practices by the total number of issues (eg Pending)- Break into quantiles based on number of issues- Identify what % of users and what % of total transfers each quantile represents<jupyter_code>practice_profile_data=practice_status_table.copy()

cumulative_percentage=practice_status_table['PENDING'].cumsum()/practice_status_table['PENDING'].sum()
quantiles=5
practice_profile_data['Quantile Group']=np.ceil(cumulative_percentage*quantiles)

practice_profile_data=practice_profile_data.groupby('Quantile Group').agg({'PENDING':'sum','TOTAL':'sum','INTEGRATED':'count'}).astype(int)
practice_profile_data=practice_profile_data.rename({'PENDING':'Total Pending','TOTAL':'Total Transfers','INTEGRATED':'Total Practices'},axis=1)

practice_profile_data_percentages=(100*practice_profile_data/practice_profile_data.sum()).round(2)
practice_profile_data_percentages.columns= "% " + practice_profile_data_percentages.columns

practice_profile_table=pd.concat([practice_profile_data,practice_profile_data_percentages],axis=1)
practice_profile_table
ax=practice_status_table['PENDING'].cumsum().reset_index(drop=True).plot(figsize=(8,5))
ax.set_ylabel('Number of Pending without error Transfers')
ax.set_xlabel('Number of GP Practices')
ax.set_title('Cumulative graph of total pending transfers for GP Practices')
plt.gcf().savefig('EMIS_EMIS_cumulative_pending_transfers.jpg')
def make_autopct(values):
    def my_autopct(pct):
        total = sum(values)
        val = int(round(pct*total/100.0))
        return '{p:.2f}%  ({v:d})'.format(p=pct,v=val)
    return my_autopct

practices_by_total_pending=practice_status_table['PENDING'].value_counts()
practices_by_total_pending.index=practices_by_total_pending.index.astype(int)


reduced_df=practices_by_total_pending.head(3)
reduced_df['3+']=practices_by_total_pending.tail(-3).sum()
reduced_df.index=reduced_df.index.astype(str) + ' pending transfers'
practices_by_total_pending=reduced_df

#practices_by_total_pending.plot.pie(autopct=make_autopct(values))

plt.pie(practices_by_total_pending.values, labels=practices_by_total_pending.index, autopct=make_autopct(practices_by_total_pending.values))
plt.gcf().set_size_inches(7,7)
plt.gca().set_title('Number of pending Transfers by % of GP Practices')
#plt.show()
plt.gcf().savefig('EMIS_EMIS_pie_number_pending_pc_practices.jpg')<jupyter_output><empty_output><jupyter_text>Output to Excel<jupyter_code>writer = pd.ExcelWriter('prmt-1984-emis-emis-practice-level-statuses.xlsx', engine='xlsxwriter')

practice_profile_table.to_excel(writer, sheet_name='Breakdown')
full_practice_status_table.to_excel(writer, sheet_name='Overview')

asid_of_interest=full_practice_status_table.head(10)['ASID'].values
[generate_gp_table(gp_asid).to_excel(writer,sheet_name=gp_asid) for gp_asid in asid_of_interest] 

writer.save()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12799245994344957, 'fraction_numerical': 0.014891611687087653, 'mean_word_length': 10.750830564784053, 'pattern_counts': {'":': 4, '<': 21, '<?xml version=': 0, '>': 24, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '494588', 'license': 'Apache-2.0', 'n_tokens_mistral': 3927, 'n_tokens_neox': 3629, 'n_words': 492, 'path': 'notebooks/26-PRMT-1984--emis-emis-practice-level-behaviour.ipynb', 'repo_name': 'nhsconnect/prm-gp2gp-data-sandbox', 'size': '10610'}