<jupyter_start><jupyter_text>Import tools and data  Import packages<jupyter_code>import pandas as pd
import glob
import numpy as np
import json
import sqlite3
import re
from sklearn.metrics import recall_score, precision_score, f1_score, accuracy_score<jupyter_output><empty_output><jupyter_text>Import utilities<jupyter_code>import sys
sys.path.append('../src/')
import utils

z_recode_stems = utils.z_recode_stems
Recode_z = utils.Recode_z
most_common = utils.most_common<jupyter_output><empty_output><jupyter_text>Import data<jupyter_code>#master data for reference
conn = sqlite3.connect("../data/db/gfm.db")
master = pd.read_sql_query("SELECT url,fund_description FROM master", conn)
#disease hierarchy
ccsr = pd.read_csv('../data/ccsr/disease_hierarchy.csv')
#categorization outout
path = '../data/disease_categorization/output_new' 
all_files = glob.glob(path + "/*.csv")

li = []

for filename in all_files:
    df = pd.read_csv(filename)
    li.append(df)

er = pd.concat(li, axis=0, ignore_index=True)
#categorization output deidentification map
deid = pd.read_csv('../data/disease_categorization/url_deid_map.csv')
#categorization output, additional campaigns
more = pd.read_csv('../data/disease_categorization/more_for_spark_output.csv')
#manually labeled reference
#ref = pd.read_csv('../data/disease_categorization/evaluation/reference_set_any_pivot.csv')
ref = pd.read_csv('../data/disease_categorization/evaluation/final_reference.csv')<jupyter_output><empty_output><jupyter_text>Clean data  Clean categorization output  Re-identify campaigns<jupyter_code>deid['url_deid'] = deid['url_deid'].astype(int)
er['url'] = er['url'].astype(int)
er = er.merge(deid, left_on='url', right_on='url_deid', how='left')
er = er[['url_y','chunks','begin','end','sent','code','results','resolutions','res_distances']]
er = er.rename(columns = {'url_y':'url', 'code':'entity_code'})<jupyter_output><empty_output><jupyter_text>Add additional campaigns<jupyter_code>more.rename(columns={'code':'entity_code'},inplace=True)
er = pd.concat([er, more], axis=0)<jupyter_output><empty_output><jupyter_text>Were any campaigns from master data not subjected to disease categorization?<jupyter_code>more_input = pd.read_csv('../data/disease_categorization/more_for_spark.csv')
urls = deid['url'].tolist() + more_input['url'].tolist()
#takes a little while
for url in master['url']:
    if url in urls:
        continue
    else:
        print(url)
print('all done')<jupyter_output>all done<jupyter_text>All campaigns from master data were subjected to disease categorization  Were any campaigns from reference data not subjected to disease categorization<jupyter_code>for url in ref['url']:
    if url in urls:
        continue
    else:
        print(url)
print('all done')<jupyter_output>https://www.gofundme.com/f/evp227-fernando-diaz
all done<jupyter_text>One campaign is not included in reference data.  Subset ER data<jupyter_code>er = er[er['url'].isin(master['url'].tolist())]<jupyter_output><empty_output><jupyter_text>Inspect data<jupyter_code>er.head()
er.shape
unique_codes_n = er['entity_code'].nunique()
print(unique_codes_n)
unique_urls_n = er['url'].nunique()
print(unique_urls_n)<jupyter_output>74256<jupyter_text>Recode "family/personal history of" codes (start with Z80 to Z87) and recode to second code in results<jupyter_code># #takes a little while
# recode_fh_map = Recode_z(er, z_recode_stems)
# #find the most common remapping and use that
# recode_fh_map_lofd = pd.DataFrame(recode_fh_map).groupby('code').agg(lambda x: most_common(list(x))).reset_index().to_dict(orient='records')

# recode_fh_map_dict = {}
# for d in recode_fh_map_lofd:
#     recode_fh_map_dict[d['code']] = d['res']
# z_to_recode = [k for k,v in recode_fh_map_dict.items()]
# unique_z_recoded_n = pd.Series(er['entity_code'].unique()).isin(z_to_recode).sum()
# print(unique_z_recoded_n)
# print(unique_z_recoded_n/unique_codes_n)
# #replace values
# er['entity_code'].replace(recode_fh_map_dict, inplace=True)<jupyter_output><empty_output><jupyter_text>Recode autism"resolutions" column is correct, but the ICD code does not seem correct<jupyter_code>er[er['chunks'].str.contains('autism')].head()
er.loc[er['chunks'].str.lower().str.contains('autism'),'entity_code'] = 'F840'
er.loc[er['chunks'].str.lower().str.contains('autistic'),'entity_code'] = 'F840'<jupyter_output><empty_output><jupyter_text>Match ICD codes from entity resolution with categories from CCSR<jupyter_code>raw_codes = er['entity_code'].unique().tolist()
ccsr_codes = ccsr['icd_10_cm_code'].tolist()
def StemCode(code, ccsr):
    res = 0
    while res == 0:
        sub = ccsr[ccsr['icd_10_cm_code'].str.startswith(code)]
        res = sub.shape[0]
        if res > 0:
            cats = sub['ccsr_category'].value_counts().index[0]
            return code, cats
        else:
            code = code[:-1]
            if len(code) == 0:
                print('error - no code stem')
                return np.nan, np.nan
def GetCodeMatch(raw_codes, ccsr_codes, ccsr):

    #define results container for best match
    best_match = []

    #loop through raw codes
    for raw in raw_codes:
        #if raw code exists in CCSR as-is
        if raw in ccsr_codes:
            #no need to define best match as exact match already exists
            continue
        else:
            #see if there are any codes that start with code
            #if not, remove final character, try again
            #once codes are found, select the ccsr_cateogry that is most common
            matched_code, ccsr_cat = StemCode(raw, ccsr)
            best_match.append({'raw_code':raw, 'matched_code_stem':matched_code,'matched_ccsr_category':ccsr_cat})

    return best_match
#takes a little while
best_match_map = GetCodeMatch(raw_codes, ccsr_codes, ccsr)
best_match_map = pd.DataFrame(best_match_map).merge(ccsr[['ccsr_category','disease_category']].drop_duplicates('ccsr_category'), 
                                                    left_on='matched_ccsr_category', 
                                                    right_on='ccsr_category', 
                                                    how='left')
#inspect code n=2 stem errors
best_match_map[best_match_map['matched_code_stem'].isna()]
er[(er['entity_code'] == 'q878')|(er['entity_code'] == 'd430')]
#both appear to be a capitalization issues
er.loc[er['entity_code'] == 'q878','entity_code'] = 'Q878'
er.loc[er['entity_code'] == 'd430','entity_code'] = 'D430'
#rerun after correcting capitalization issue from Spark output
raw_codes = er['entity_code'].unique().tolist()
best_match_map = GetCodeMatch(raw_codes, ccsr_codes, ccsr)<jupyter_output><empty_output><jupyter_text>No errors after running above cell<jupyter_code># best_match_map = pd.DataFrame(best_match_map).merge(ccsr[['ccsr_category','int_category','disease_category']].drop_duplicates('ccsr_category'), 
#                                                     left_on='matched_ccsr_category', 
#                                                     right_on='ccsr_category', 
#                                                     how='left')

best_match_map = pd.DataFrame(best_match_map).merge(ccsr[['ccsr_category','disease_category']].drop_duplicates('ccsr_category'), 
                                                    left_on='matched_ccsr_category', 
                                                    right_on='ccsr_category', 
                                                    how='left')
best_match_map.to_csv('../data/disease_categorization/best_match_map.csv', index=False)
unique_best_match_n = best_match_map.shape[0]
print(unique_best_match_n)
print(unique_best_match_n/unique_codes_n)
pd.Series([len(x) for x in best_match_map['matched_code_stem']]).value_counts()
17/unique_best_match_n<jupyter_output><empty_output><jupyter_text>Replace best-matched entity codes<jupyter_code>#subset dataframe for those that need best matched data
er_for_best_match = er[er['entity_code'].isin(best_match_map['raw_code'].tolist())]
er_rest = er[~er['entity_code'].isin(er_for_best_match['entity_code'].tolist())]<jupyter_output><empty_output><jupyter_text>Merge ER data with appropriate disease categories<jupyter_code>#best match
er_for_best_match = er_for_best_match.merge(best_match_map, left_on='entity_code', right_on='raw_code', how='left')
del er_for_best_match['matched_code_stem']
del er_for_best_match['matched_ccsr_category']
er_for_best_match.loc[:,'icd_10_cm_code_desc'] = ['best_match' for x in range(len(er_for_best_match))]
er_for_best_match.rename(columns={'raw_code':'icd_10_cm_code'}, inplace=True)

#exact match
er_rest = er_rest.merge(ccsr, left_on='entity_code', right_on='icd_10_cm_code', how='left')
#concatenate dataframes
er_final = pd.concat([er_for_best_match, er_rest])
print(er_final.shape)
print(er.shape)
print('\n')
print(er_final['url'].nunique())
print(er['url'].nunique())<jupyter_output>(272722, 13)
(272722, 9)


74256
74256<jupyter_text>Find additional clinical entities to infer disease category<jupyter_code>def SearchProcedures(df):

    r = {}

    terms = ['dialysis',
             'renal transplant',
             'kidney transplant',
             'lung transplant',
             'heart transplant',
             'heart surgery',
             'chemo',
             'chemotherapy',
             'radiation',
             'radiotherapy',
             'bone marrow transplant',
             'liver transplant',
             "accident","injury","injuries","injured","crash","collision","injured","burn","burned","burns"]

    for term in terms:

        #define regex
        regex = r"\b{}\b".format(term)

        #apply regex search
        search = df['fund_description'].apply(lambda x: re.search(regex, x))

        #get start and end
        start = [x.start() if x != None else np.nan for x in search]
        end = [x.end() if x != None else np.nan for x in search]

        #add to results
        r[term] = {'start':start,'end':end}
    
    return r

search_results = SearchProcedures(master) #takes about 30 seconds
def SearchToDF(search_results):

    r = []

    for k,v in search_results.items():

        new_df = pd.DataFrame({'url':master['url'].tolist(),'begin':v['start'],'end':v['end']})

        new_df = new_df.dropna()

        new_df['chunks'] = [k for x in range(len(new_df))]
        new_df['sent'] = [np.nan for x in range(len(new_df))]
        new_df['entity_code'] = ['regex_search' for x in range(len(new_df))]
        new_df['results'] = [np.nan for x in range(len(new_df))]
        new_df['resolutions'] = [np.nan for x in range(len(new_df))]
        new_df['res_distances'] = [np.nan for x in range(len(new_df))]
        new_df['icd_10_cm_code'] = ['regex_search' for x in range(len(new_df))]
        new_df['icd_10_cm_code_desc'] = [k for x in range(len(new_df))]


        if k in ['dialysis','renal transplant','kidney transplant']:
            new_df['ccsr_category'] = ['GU_from_proc' for x in range(len(new_df))]
            new_df['int_category'] = ['CKD and renal failure' for x in range(len(new_df))]
            new_df['disease_category'] = ['Genitourinary diseases' for x in range(len(new_df))]

        elif k in ['lung transplant']:
            new_df['ccsr_category'] = ['RESP_from_proc' for x in range(len(new_df))]
            new_df['int_category'] = ['Other respiratory disorders' for x in range(len(new_df))]
            new_df['disease_category'] = ['Respiratory diseases' for x in range(len(new_df))]

        elif k in ['heart transplant','heart surgery']:
            new_df['ccsr_category'] = ['CV_from_proc' for x in range(len(new_df))]
            new_df['int_category'] = ['Other cardiovascular disorders' for x in range(len(new_df))]
            new_df['disease_category'] = ['Cardiovascular diseases' for x in range(len(new_df))]

        elif k in ['chemo','chemotherapy','radiation','radiotherapy','bone marrow transplant']:
            new_df['ccsr_category'] = ['NEO_from_proc' for x in range(len(new_df))]
            new_df['int_category'] = ['Other neoplasms' for x in range(len(new_df))]
            new_df['disease_category'] = ['Neoplasms' for x in range(len(new_df))]

        elif k in ['liver transplant']:
            new_df['ccsr_category'] = ['GI_from_proc' for x in range(len(new_df))]
            new_df['int_category'] = ['Liver diseases' for x in range(len(new_df))]
            new_df['disease_category'] = ['Gastrointestinal diseases' for x in range(len(new_df))]
        
        elif k in ["accident","injury","injuries","injured","crash","collision","injured","burn","burned","burns"]:
            new_df['ccsr_category'] = ['INJ_from_proc' for x in range(len(new_df))]
            new_df['int_category'] = ['Other injuries' for x in range(len(new_df))]
            new_df['disease_category'] = ['Injuries and external causes' for x in range(len(new_df))]

        r.append(new_df)

    return pd.concat(r)
search_results_df = SearchToDF(search_results)<jupyter_output><empty_output><jupyter_text>Among newly-added campaigns, which search terms were responsible?<jupyter_code>search_results_df['url'].nunique()
search_sub = search_results_df[~search_results_df['url'].isin(er_final['url'].unique())]
search_sub['url'].nunique()
search_sub[['url','disease_category']].drop_duplicates()['disease_category'].value_counts()
search_sub[['url','disease_category']].drop_duplicates()['disease_category'].value_counts()/1255
er_final = pd.concat([er_final, search_results_df])<jupyter_output><empty_output><jupyter_text>Exclude named entities that map to certain disease categoriesThese categories are unable to be classified with adequate precision and recall.<jupyter_code>starting_n = er_final['entity_code'].nunique()
er_final = er_final[~er_final['disease_category'].isin(['Exclude'])]
exclude_cat_n = starting_n - er_final['entity_code'].nunique()
print(exclude_cat_n)
print(exclude_cat_n/starting_n)
er_final['url'].nunique()<jupyter_output><empty_output><jupyter_text>Evaluate classification performance  Get list of disease categories by url<jupyter_code>counts_list = er_final[['url','disease_category']].groupby('url').agg(lambda x: list(x))
counts_list = counts_list.reset_index()<jupyter_output><empty_output><jupyter_text>Format for comparison with reference data<jupyter_code>def list2lofd(df):
    '''convert cat column from comma delimited to list of dictionaries with 1 or 0 for each category'''
    
    disease_categories = [
        'Cardiovascular diseases',
        'Endocrine diseases',
        'Gastrointestinal diseases',
        'Genitourinary diseases',
        'Infections',
        'Injuries and external causes',
        'Mental and substance use disorders',
        'Musculoskeletal diseases',
        'Neoplasms',
        'Nervous system diseases',
        'Respiratory diseases'
    ]
    
    r = []
    
    for index,row in df.iterrows():
        lofd = []
        as_list = row['disease_category']
        for disease in disease_categories:
            if disease in as_list:
                lofd.append({disease:1})
            else:
                lofd.append({disease:0})
        r.append(lofd)
    return r
counts_list['lofd'] = list2lofd(counts_list)
#convert to long format
long = counts_list.explode('lofd')
#extract key, value from dictionary
long.insert(loc=2, column='cat', value=[list(x.items())[0][0] for x in long['lofd']])
long.insert(loc=3, column='count', value=[list(x.items())[0][1] for x in long['lofd']])
long = long[['url','cat','count']]
#create pivot table
#takes a little while
pivot = long.pivot_table(index=['url'], columns='cat', values='count', aggfunc=(pd.Series.sum), fill_value=0)
pivot.columns.name = ''
pivot = pivot.reset_index()
pivot.head()<jupyter_output><empty_output><jupyter_text>Prepare reference data<jupyter_code>ref.rename(columns={'Genitourinary disorders':'Genitourinary diseases'}, inplace=True)
del ref['Nonspecific']
del ref['Other noncommunicable diseases']
del ref['Pregnancy and childbirth']
ref.head()
ref.shape<jupyter_output><empty_output><jupyter_text>Add blank rows for urls with no identified NER/ER terms<jupyter_code>master_urls = master['url']
urls_to_add = master_urls[~master_urls.isin(pivot['url'].tolist())].tolist()
len(urls_to_add)
pivot.shape[0] + len(urls_to_add) == master.shape[0]
def AddBlank(urls, df):
    r = []
    cols = df.columns[1:].values.tolist()
    for url in urls:
        line = {}
        line['url'] = url
        for col in cols:
            line[col] = 0
        r.append(line)
    return pd.DataFrame(r)
blank_to_add = AddBlank(urls_to_add, pivot)
pivot = pd.concat([pivot, blank_to_add])
pivot.shape[0] == master.shape[0]<jupyter_output><empty_output><jupyter_text>Export sample of 50 campaigns that did not have any diseases identified<jupyter_code>#blank_sub = master[master['url'].isin(blank_to_add['url'].sample(50))]
#blank_sub.to_csv('../data/disease_categorization/blank_for_review.csv', index=False)<jupyter_output><empty_output><jupyter_text>Create evaluation subset<jupyter_code>for url in ref['url']:
    if url in master['url'].tolist():
        continue
    else:
        print(url)
print('all done')
sub = pivot[pivot['url'].isin(ref['url'].tolist())]
sub.shape
ref[~ref['url'].isin(sub['url'])]
sub[~sub['url'].isin(ref['url'])]
ref = ref[ref['url'].isin(sub['url'])]
sub = sub[sub['url'].isin(ref['url'])]
ref.shape
sub.shape
sub = sub.sort_values('url')
ref = ref.sort_values('url')
ref = ref.reset_index()
sub = sub.reset_index()
ref['url'].equals(sub['url'])<jupyter_output><empty_output><jupyter_text>Compute metrics<jupyter_code>def ComputeMetrics(ref, sub):
    cat = []
    precision = []
    recall = []
    f1 = []
    acc = []
    refe = []
    piv = []
    
    for col in sub.columns[2:]:
        cat.append(col)
        precision.append(round(precision_score(ref[col], sub[col]),2))
        recall.append(round(recall_score(ref[col], sub[col]),2))
        f1.append(round(f1_score(ref[col], sub[col]),2))
        acc.append(round(accuracy_score(ref[col], sub[col]),2))
        refe.append(sum(ref[col]))
        piv.append(sum(sub[col]))
    
    df = pd.DataFrame({'category':cat, 
                        'ref_count':refe,
                        'test_count':piv,
                        'precision':precision,
                        'recall':recall,
                        'f1':f1,
                        'acc':acc})
    return df
metrics = ComputeMetrics(ref, sub)
metrics
print('Weighted precision:', np.average(metrics['precision'], weights=metrics['ref_count']))
print('Weighted recall:', np.average(metrics['recall'], weights=metrics['ref_count']))
print('Weighted f1:', np.average(metrics['f1'], weights=metrics['ref_count']))
print('Weighted accuracy:', np.average(metrics['acc'], weights=metrics['ref_count']))<jupyter_output>Weighted precision: 0.8256737588652483
Weighted recall: 0.7797872340425531
Weighted f1: 0.7896099290780142
Weighted accuracy: 0.947304964539007<jupyter_text>Prepare data for export  Get primary category Summarize by disease category and location of chunk in text<jupyter_code>#er_final = er_final.reset_index()
%%time
er_cat = er_final[['url','begin','disease_category']].groupby(['url']).agg(list)
from collections import Counter
#get counts for each category
er_cat['disease_category_count'] = er_cat['disease_category'].apply(lambda x: Counter(x).most_common())
#separate counts into separate column
er_cat['disease_category_count_max'] = [x[0][1] for x in er_cat['disease_category_count']]
#explode
exl_d = er_cat.explode('disease_category_count')
#separate tuples
exl_d['disease_cat'] = [x[0] for x in exl_d['disease_category_count']]
exl_d['disease_cat_val'] = [x[1] for x in exl_d['disease_category_count']]
#remove categories that do not have max amount
exl_d = exl_d[exl_d['disease_cat_val'] == exl_d['disease_category_count_max']]
#reset indices
exl_d = exl_d.reset_index()
#separate urls that are duplicated (those who have multiple categories equal to max count)
exl_d_dup = exl_d[exl_d['url'].duplicated(keep=False)]

#separate urls that are not duplicated
exl_d_no_dup = exl_d[~exl_d['url'].duplicated(keep=False)]

print(exl_d_dup.shape)

print(exl_d_no_dup.shape)
def GetPrimaryCat(df, first_col, second_col):

    r = {}

    for url in df['url']:
        sub = df[df['url'] == url]
        candidates = sub[sub['url'] == url][first_col].tolist()
        pos = sub[sub['url'] == url].drop_duplicates('url')['begin'].tolist()[0]
        cats = sub[sub['url'] == url].drop_duplicates('url')[second_col].tolist()[0]
        new_df = pd.DataFrame({'cats':cats, 'pos':pos})
        new_df = new_df[new_df['cats'].isin(candidates)]
        final_cat = new_df.groupby('cats').agg('mean').reset_index().sort_values('pos', ascending=True)['cats'].tolist()[0]

        r[url] = final_cat
    
    return r
%%time
primary_disease_map = GetPrimaryCat(exl_d_dup,'disease_cat','disease_category') 
def SubsetPrimaryCat(df, subset_map, col):
    
    r = []
    
    for k,v in subset_map.items():
        sub = df[df['url'] == k]
        sub = sub[sub[col] == v]
        r.append(sub)
    
    return pd.concat(r)    
%%time
exl_d_dup_primary = SubsetPrimaryCat(exl_d_dup, primary_disease_map, 'disease_cat')
#concatenate primary results from duplicated
primary_disease = pd.concat([exl_d_no_dup,exl_d_dup_primary])
print(primary_disease.shape)
#primary_category = pd.merge(primary_disease[['url','disease_cat']], primary_int[['url','int_cat']], on='url', how='left')<jupyter_output><empty_output><jupyter_text>Export data<jupyter_code>er_final.to_sql('er_final', con=conn,if_exists='replace')
pivot = pivot.reset_index()
pivot.columns.name = ''
cols = pivot.columns
cols = [x.lower().replace(',','').replace(' ','_') for x in cols]
pivot.columns = cols

pivot.to_sql('pivot', con=conn, if_exists='replace')
# int_pivot.reset_index()
# int_pivot.columns.name = ''
# cols = int_pivot.columns
# cols = [x.lower().replace(',','').replace(' ','_') for x in cols]
# int_pivot.columns = cols

# int_pivot.to_sql('int_pivot', con=conn, if_exists='replace')
primary_disease = primary_disease[['url','disease_cat']]
primary_disease.to_sql('primary_disease', con=conn, if_exists='replace')<jupyter_output><empty_output><jupyter_text>Review 100 campaigns with no disease category identified<jupyter_code>no_disease = master[~master['url'].isin(primary_disease['url'].tolist())].sample(100)
no_disease.to_csv('../data/disease_categorization/no_disease_found_100.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1443871655852813, 'fraction_numerical': 0.009599146742511777, 'mean_word_length': 4.410675643183458, 'pattern_counts': {'":': 0, '<': 108, '<?xml version=': 0, '>': 109, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '28', 'dir': 'jupyter-structured-clean-dedup', 'id': '229255', 'license': 'MIT', 'n_tokens_mistral': 7953, 'n_tokens_neox': 7639, 'n_words': 1418, 'path': 'AC-new_disease_category.ipynb', 'repo_name': 'sdoerstling/medical_crowdfunding_analysis', 'size': '22502'}