# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# +
df_1 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20161101.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_2 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20161108.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_3 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20161116.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_4 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20161122.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_5 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20161129.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])

df_6 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20161206.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_7 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20170110.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_8 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20170116.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_9 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20170117.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_10 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20170124.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])

df_11 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20170131.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_12 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20170207.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_13 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20170214.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_14 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20170221.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])
df_15 = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/Rx_BenefitPlan_20170301.csv', sep='|', index_col='ClaimID', dtype=str, na_values=['nan', '  ', ' '])

# -

df_1["OriginalDataset"] = 1
df_2["OriginalDataset"] = 2
df_3["OriginalDataset"] = 3
df_4["OriginalDataset"] = 4
df_5["OriginalDataset"] = 5
df_6["OriginalDataset"] = 6
df_7["OriginalDataset"] = 7
df_8["OriginalDataset"] = 8
df_9["OriginalDataset"] = 9
df_10["OriginalDataset"] = 10
df_11["OriginalDataset"] = 11
df_12["OriginalDataset"] = 12
df_13["OriginalDataset"] = 13
df_14["OriginalDataset"] = 14
df_15['OriginalDataset'] = 0

df = pd.concat([df_1, df_2, df_3, df_4, df_5, df_6, df_7, df_8, df_9, df_10, df_11, df_12, df_13, df_14, df_15])

df.columns

df_P = df[df.ClaimStatus=='P']

print(df_P.isnull().sum())

df_P.shape

print(df_P[df_P['IngredientCost'].isnull()])

print(df_P[df_P['IngredientCost'].isnull()].isnull().sum())

print(df_P.DispensingFee.describe())

print(df_P[df_P['IngredientCost'].isnull()].DispensingFee.describe())


# +
def get_total(row):
    if row['IngredientCost'] and row['DispensingFee']:
        cost1 = float(row['IngredientCost']) + float(row['DispensingFee'])
    elif row['IngredientCost']:
        cost1 = float(row['IngredientCost'])
    else:
        cost1 = 0
        
    cost2 = float(row['OutOfPocket']) + float(row['PaidAmount'])
        
    return max(cost1, cost2)

df_P['TotalCost'] = df_P.apply(lambda row: get_total(row), axis=1)
# -

print(df_P.TotalCost.describe())

print(df_P[df_P.TotalCost < 0])

df_P[df_P.TotalCost <= 0].count()

df_neg = df_P[df_P.TotalCost < 0]

df_pos = df_P[df_P.TotalCost > 0]

print(df_pos.TotalCost.describe())

df_pos.to_csv(path_or_buf='/Users/joannejordan/Desktop/RxClaims/All_data.csv', sep='|')

print(df_pos.isnull().sum())

#ndc_product = pd.read_table('/Users/joannejordan/Desktop/ndctext/product.txt')
ndc_package = pd.read_table('/Users/joannejordan/Desktop/ndctext/package.txt')

ndc_product = pd.read_table('/Users/joannejordan/Desktop/ndctext/product.txt', encoding = "ISO-8859-1")

ndc_package.head()

ndc_product.head()

df_pos.MailOrderPharmacy[df_pos.PharmacyZip.isnull()].unique()

df_pos.PharmacyState.unique()

df_nonan = df_pos.drop(columns=['PharmacyStreetAddress2', 'PrescriberFirstName', 'PresriberLastName', 'ClaimStatus']).dropna()

df_nonan.to_csv(path_or_buf='/Users/joannejordan/Desktop/RxClaims/first_pass_noNaN.csv', sep='|')

df_nonan.PharmacyState.unique()

df_pos.AHFSTherapeuticClassCode

ndc_rx = df_pos.copy()

ndc_package['PRODUCTNDC'] = ndc_package['PRODUCTNDC'].apply(lambda ndc: ndc.replace("-",""))

df_nonan.UnitMeasure.unique()


# +
def get_name(row):
    if row['DrugLabelName']:
        return row['DrugLabelName']
    else:
        global ndc_product
        global ndc_package
        ndc_pack = row['NDCCode']
        ndc = ndc_package.PRODUCTNDC[ndc_package.NDCPACKAGECODE==ndc_pack]
        DrugLabelName = ndc_product.PROPRIETARYNAME[ndc_product.PRODUCTND==ndc]
        return DrugLabelName

def get_unit(row):
    if row['DrugLabelName']:
        return row['UnitMeasure']
    else:
        global ndc_product
        global ndc_package
        ndc_pack = row['NDCCode']
        ndc = ndc_package.PRODUCTNDC[ndc_package.NDCPACKAGECODE==ndc_pack]
        UnitMeasure = ndc_product.DOSAGEFORMNAME[ndc_product.PRODUCTND==ndc]
        return UnitMeasure
    
def get_quant(row):
    if row['DrugLabelName']:
        return row['Quantity']
    else:
        global ndc_package
        ndc_pack = row['NDCCode']
        quantity = ndc_package.PACKAGEDESCRIPTION[ndc_package.NDCPACKAGECODE==ndc_pack]
        return Quantity[:2]


# -

ndc_rx['DrugLabelName'] = ndc_rx.apply(lambda row: get_name(row), axis=1)

ndc_rx['Quantity'] = ndc_rx.apply(lambda row: get_quant(row), axis=1)

ndc_rx['UnitMeasure'] = ndc_rx.apply(lambda row: get_unit(row), axis=1)

ndc_rx.isnull().sum()

ndc_rx.NDCCode[ndc_rx.DrugLabelName.isnull()]

df_nonan[:10000].to_csv(path_or_buf='/Users/joannejordan/Desktop/RxClaims/third_pass_noNaN.csv')

rx_info = ndc_rx.drop(columns=['PharmacyStreetAddress2', 'PrescriberFirstName', 'PresriberLastName', 'ClaimStatus']).dropna(subset=['DrugLabelName'])

rx_info.isnull().sum()


def get_unit_cost(row):
    if float(row['Quantity']) > 0:
        return float(row['TotalCost'])/float(row['Quantity'])
    else:
        return row['TotalCost']


rx_info['UnitCost'] = rx_info.apply(lambda row: get_unit_cost(row), axis=1)

rx_info.UnitCost.describe()

rx_info.isnull().sum()


def get_zip(row):
    if len(str(row['PharmacyZip'])) > 5:
        return str(row['PharmacyZip'])[:5]
    else:
        return row['PharmacyZip']


rx_info['PharmacyZipCode'] = rx_info.apply(lambda row: get_zip(row), axis=1)

rx_info.PharmacyZipCode.isnull().sum()

dropped_zips = rx_info.dropna(subset=['PharmacyZipCode'])

dropped_zips.drop(columns=['PharmacyZip'], inplace=True)

dropped_zips.to_csv(path_or_buf='/Users/joannejordan/Desktop/RxClaims/all_data_w_zips.csv')

dropped_zips.isnull().sum()


#get mail order pharmacies back.
def mail_order_pharm(row):
    if row['MailOrderPharmacy']=='Y':
        return 99999
    else:
        return row['PharmacyZipCode']


rx_info.drop(columns=['PharmacyZip'], inplace=True)

rx_info['PharmacyZip'] = rx_info.apply(lambda row: mail_order_pharm(row), axis=1)

rx_info.isnull().sum()

inc_mail_order = rx_info.drop(columns=['PharmacyZipCode'])

grouped_meds = inc_mail_order.groupby(['NDCCode']).count()

grouped_meds

drug_by_pharm = inc_mail_order.groupby(['NDCCode', 'PharmacyZip']).count()

drug_by_pharm

'CLONAZEPAM' in inc_mail_order.DrugLabelName.unique()

drug_by_ph = inc_mail_order.groupby(['DrugLabelName', 'PharmacyZip']).count()

drug_by_ph

drugs = inc_mail_order.DrugLabelName.unique()

# +
med_freqs = []

for drug in drugs:
    med_freqs.append(inc_mail_order.DrugLabelName.tolist().count(drug))

# +
zips = inc_mail_order.PharmacyZip.unique()

zip_freqs = []

for zip_code in zips:
    zip_freqs.append(inc_mail_order.PharmacyZip.tolist().count(zip_code))

# +
#https://simplemaps.com/data/us-zips

zip_code_info = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/uszips.csv', index_col='zip')
# -

print(med_freqs)

print(zip_freqs)

zip_freqs

plt.hist(zip_freqs, log=True, range=(0,1000))

# +
from scipy import stats

stats.describe(zip_freqs)
# -

plt.hist(med_freqs, log=True, range=(0,100))

'HYDROCHLOROTHIAZIDE                               ' in inc_mail_order.DrugLabelName.tolist()

drugs

inc_mail_order

drug_names = inc_mail_order.copy()

#get rid of erroneous white space in DrugLabelName
drug_names['DrugLabelName'] = drug_names['DrugLabelName'].apply(lambda drug: ' '.join(drug.split()))

all_drugs = drug_names.DrugLabelName.unique()

all_drugs

# +
drug_freqs = []

for drug in all_drugs:
    med_freqs.append(drug_names.DrugLabelName.tolist().count(drug))
# -

plt.hist(med_freqs, log=True)

# +
#make better notebook put on GitHub
#county data?
#first 3 digits of zip column?
#drug categories?

drug_names.to_csv(path_or_buf='/Users/joannejordan/Desktop/RxClaims/current_wk2_end.csv')
# -

drug_names.columns

EOW2_nonan = drug_names.drop(columns=['AHFSTherapeuticClassCode', 'CoInsurance', 'DateFilled',
                                     'Deductible', 'DispensingFee', 'FillNumber', 'FillNumber',
                                     'MemberID', 'GroupNumber', 'MailOrderPharmacy', 'PaidOrAdjudicatedDate',
                                     'RxNumber', 'SeqNum', 'punbr_grnbr', 'CompoundDrugIndicator',
                                     'Copay', 'IngredientCost', 'NDCCode', 'OutOfPocket', 'PaidAmount',
                                     'Quantity', 'RxNumber', 'SeqNum', 'UnitMeasure']).dropna()

EOW2_nonan.to_csv(path_or_buf='/Users/joannejordan/Desktop/RxClaims/EOW2_simplified_df.csv')

EOW2_nonan

drug_by_pharm = EOW2_nonan.groupby(['PharmacyZip','PharmacyNPI', 'DrugLabelName']).mean()

simple = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/EOW2_simplified_df.csv', index_col='ClaimID', dtype=str)

# +
zips = simple.PharmacyZip.unique()

zip_freqs = []

for zip_code in zips:
    zip_freqs.append(simple.PharmacyZip.tolist().count(zip_code))

# +
args = np.argpartition(np.array(zip_freqs), -6)[-6:]

top6 = zips[args]
top6
# -

simple.UnitCost = simple.UnitCost.astype(float)

simple.PharmacyNPI = simple.PharmacyNPI.apply(lambda ndc: str(ndc).replace(" ",""))

top_zip0 = simple[simple.PharmacyZip == top6[0]]
top_zip1 = simple[simple.PharmacyZip == top6[1]]
top_zip2 = simple[simple.PharmacyZip == top6[2]]
top_zip3 = simple[simple.PharmacyZip == top6[3]]
top_zip4 = simple[simple.PharmacyZip == top6[4]]
top_zip5 = simple[simple.PharmacyZip == top6[5]]

top_zip0.PharmacyNPI.unique()

t0_drug_by_ph = top_zip0.groupby(['DrugLabelName', 'PharmacyNPI']).mean()

t0_drug_by_ph = pd.DataFrame(t0_drug_by_ph)

# +
meds = simple.DrugLabelName.unique()

med_freqs = []

for drug in meds:
    med_freqs.append(simple.DrugLabelName.tolist().count(drug))

# +
args_med = np.argpartition(np.array(med_freqs), -5)[-5:]

top5 = meds[args_med]
top5
# -

drug_names = pd.read_csv('/Users/joannejordan/Desktop/RxClaims/current_wk2_end.csv', index_col='ClaimID', dtype=str)

drug_names.PharmacyNPI = drug_names.PharmacyNPI.apply(lambda ndc: str(ndc).replace(" ",""))

simple.PharmacyNumber.unique().size

table = simple[simple.PharmacyZip=='03431']
table = table[table.DrugLabelName=='SIMVASTATIN']

pharmacies = table.groupby(['PharmacyName']).mean()
pharmacies

table = table[table.PharmacyName==pharmacies.UnitCost.idxmin()]

print('Pharmacy:\n{}\nAddress:\n{}\n{}'.format(table.PharmacyName.iloc[0], table.PharmacyStreetAddress1.iloc[0], table.PharmacyCity.iloc[0]))


def get_cheapest_pharm(zipcode, drug, table):
    table = table[table.PharmacyZip==str(zipcode)]
    table = table[table.DrugLabelName==str(drug)]
    pharmacies = table.groupby(['PharmacyName']).mean()
    pharmacy = pharmacies.UnitCost.idxmin()
    table = table[table.PharmacyName==pharmacy]
    print('Pharmacy:\n{}\nAddress:\n{}\n{}\n{}\n{}'.format(table.PharmacyName.iloc[0], 
                                                   table.PharmacyStreetAddress1.iloc[0],
                                                   table.PharmacyCity.iloc[0],
                                                  table.PharmacyNPI.iloc[0],
                                                  table.PharmacyNumber.iloc[0]))


get_cheapest_pharm('03431', 'OMEPRAZOLE CAP 20MG', simple)

get_cheapest_pharm('03431', 'FLUTICASONE SPR 50MCG', simple)

get_cheapest_pharm('03431', 'LISINOPRIL', simple)

get_cheapest_pharm('03431', 'PROAIR HFA AER', simple)

get_cheapest_pharm('02128', 'PROAIR HFA AER', simple)

get_cheapest_pharm('02128', 'LISINOPRIL', simple)

get_cheapest_pharm('02128', 'FLUTICASONE SPR 50MCG', simple)

get_cheapest_pharm('02128', 'SIMVASTATIN', simple)

get_cheapest_pharm('02128', 'OMEPRAZOLE CAP 20MG', simple)



StarcoderdataJupyterScriptsDedupFiltered