<jupyter_start><jupyter_text>NDC Data CleaningThis data could be useful for data studies I'd like to do. Lets prep the data for further use in future projects.First things first, lets load the data.<jupyter_code>from IPython.display import display, HTML
import pandas as pd
import numpy as np

pack_df = pd.read_excel("./data/package.xls")<jupyter_output><empty_output><jupyter_text>Looks like relying on pandas isn't going to be good enough. Lets parse the files myself.<jupyter_code>def xlsParser(fileobj):
    text_lines = fileobj.readlines()
    columns = [x.strip() for x in text_lines[0].split("\t")]
    data = [x.strip().split('\t') for x in text_lines[1:]]
    df = pd.DataFrame(columns=columns, data=data)
    return df

with open("./data/package.xls","r") as package_file:
    pack_df = xlsParser(package_file)
    
print(pack_df.shape)
pack_df.head()
with open("./data/product.xls","r") as product_file:
    prod_df = xlsParser(product_file)

print(prod_df.shape)
prod_df.head()<jupyter_output>(121047, 20)<jupyter_text>Data InvestigationLets see if there are duplicated entries, nulls etc. and try and understand the data here.<jupyter_code>print("Package:")
for col in pack_df.columns:
    print(col.strip()," has ",pack_df[pack_df.duplicated(subset=col)].shape[0]," duplicated values.")
print()
print("Product:")
for col in prod_df.columns:
    print(col.strip()," has ",prod_df[prod_df.duplicated(subset=col)].shape[0]," duplicated values.")
prod_df.isnull().sum()
pack_df.isnull().sum()<jupyter_output><empty_output><jupyter_text>Where to begin?Firstly there are some empty strings that should be nulls. Lets clean that up.<jupyter_code>prod_df.replace('',np.nan,inplace=True)
pack_df.replace('',np.nan,inplace=True)
prod_df.isnull().sum()
#Lets save the non-null columns for possible use later.
prod_non_null_cols = prod_df.columns[prod_df.isnull().sum()==0].values.tolist()
pack_non_null_cols = pack_df.columns[pack_df.isnull().sum()==0].values.tolist()
prod_non_null_cols
pack_non_null_cols
prod_df[prod_df['NDC_EXCLUDE_FLAG']=='E']<jupyter_output><empty_output><jupyter_text>Whats next?The product dataframe looks to contain information on formulations of drugs. What's interesting is the PRODUCTID column is the only column in both dataframes to have zero duplicated values. It appears to be the unique identifier for all drugs. But how will people query this dataset? I doubt people will look for the PRODUCTID, rather they would look for the Proprietary or Non-Proprietary name(s). So lets see if we can condense and group this dataset by proprietary name.<jupyter_code>prod_df.head()
prod_df_grouped_01 = prod_df.groupby('PROPRIETARYNAME')

#Lets just look at a few
for group in list(prod_df_grouped_01.groups)[:10]:
    group_vals = prod_df_grouped_01.get_group(group)
    if len(group_vals)>1:
        display(prod_df_grouped_01.get_group(group))<jupyter_output><empty_output><jupyter_text>It appears that some duplicates are there because the producing company changed name, or the product changed hands in the market. Lets remove duplicate rows that contain the same drug but changed companies, selecting the most recent company to own the drug.<jupyter_code>def checkAllMatch(values,checkcols):
    """Given a set of dframe rows, return a count of the columns that contain the same values."""
    non_unique_dict = {}
    for col in [x for x in values.columns if x in checkcols]:
        if len(values[col].unique())<=1:
            continue
        else:
            return False
    return True

def companyChangeRemover(dataframe):
    """I'm looking for rows that are identical except for their company name.
    If there are such rows, remove the row with the older marketing approval date."""
    return_df = dataframe.copy()
    grouped_df = dataframe.groupby(["NONPROPRIETARYNAME"])
    duplicate_index = []
    ignore_cols = ['PRODUCTID','PRODUCTNDC','STARTMARKETINGDATE',"APPLICATIONNUMBER"]
    check_cols = [x for x in dataframe.columns if x not in ignore_cols]
    for group in grouped_df.groups:
        group_vals = grouped_df.get_group(group)
        if len(group_vals) > 1:
            if checkAllMatch(group_vals,check_cols):
                #If there is only one unique start marketing date, take the original NDC.
                if len(group_vals['STARTMARKETINGDATE'].unique())<=1:
                    #display(group_vals)
                    group_vals.sort_values('PRODUCTNDC',inplace=True)
                    drop_indices = group_vals.index[1:].values.tolist()
                    duplicate_index.extend(drop_indices)
                #Otherwise, sort by starting marketing date, keep the latest one. Drop the rest.
                else:
                    #display(group_vals)
                    group_vals.sort_values(['STARTMARKETINGDATE'],ascending=False,inplace=True)
                    drop_indices = group_vals.index[1:].values.tolist()
                    duplicate_index.extend(drop_indices)
    duplicate_index.sort()
    return_df.drop(duplicate_index,axis=0,inplace=True)
    return_df.reset_index(drop=True,inplace=True)
    return return_df
            
prod_df_nodupmarkets = companyChangeRemover(prod_df.iloc[:100])
prod_df_nodupmarkets.head()<jupyter_output>C:\Users\Jake\Anaconda3\lib\site-packages\ipykernel_launcher.py:26: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
C:\Users\Jake\Anaconda3\lib\site-packages\ipykernel_launcher.py:32: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy<jupyter_text>Now there are no duplicate drug entries that were sold to new companies and re-listed!<jupyter_code>grouped = prod_df_nodupmarkets.groupby("PROPRIETARYNAME")
ignore_cols = ["PRODUCTID","PRODUCTNDC","ACTIVE_NUMERATOR_STRENGTH","ACTIVE_INGRED_UNIT",
              "STARTMARKETINGDATE","DOSAGEFORMNAME"]
for group in list(grouped.groups)[:100]:
    group_vals = grouped.get_group(group)
    if len(group_vals)>1:
        unique_tf = False
        non_unique_cols = []
        for col in [x for x in group_vals.columns if x not in ignore_cols]:
            if len(group_vals[col].unique())<=1:
                continue
            else:
                non_unique_cols.append(col)
                unique_tf = True
        if unique_tf:
            print(non_unique_cols)
            display(grouped.get_group(group)[non_unique_cols])<jupyter_output>['PROPRIETARYNAMESUFFIX', 'ROUTENAME', 'APPLICATIONNUMBER']
{'fraction_non_alphanumeric': 0.10098302055406613, 'fraction_numerical': 0.006702412868632708, 'mean_word_length': 4.075585789871504, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '228452', 'license': 'MIT', 'n_tokens_mistral': 2147, 'n_tokens_neox': 2081, 'n_words': 550, 'path': 'National Drug Code Directory Cleaning.ipynb', 'repo_name': 'DataAndDeadlifts/Data-Cleaning', 'size': '6714'}