<jupyter_start><jupyter_text>Predicting Hospitalization Costs <NAME>November 2018Bryant University  Data Cleaning and Transformation  Imports Import libraries<jupyter_code>import numpy as np
import pandas as pd
import copy
from pprint import pprint
import warnings
warnings.filterwarnings('ignore')<jupyter_output><empty_output><jupyter_text>Import dataset<jupyter_code># Specify the selected field and their datatypes 

type_arrival = {'sex': 'float16',
                'er_mode': 'float16',
                'admtype': 'float16',
                'yoa': 'float16',
                'campus': 'category',
                'pay_ub92': 'category',
                'provider': 'category', 
                'asource': 'category',
                'moa': 'float16',
                'age': 'float16',
                'race': 'float16',
                'diag_adm': 'category',
                'pt_state': 'category',
               }

type_other = {}

type_target = {'tot': 'float64'}

col_arrival = [*type_arrival]
col_other = [*type_other]
col_target = [*type_target]

usecols = col_arrival + col_other + col_target
dtype = {}
for d in [type_arrival, type_other, type_target]:
    for k, v in d.items():
        dtype[k] = v
# import data using relevant columns and datatypes
df0 = pd.read_csv('hdd0313cy.csv', 
                  usecols=usecols, 
                  dtype=dtype,
                 )
print(df0.shape)<jupyter_output>(1544747, 14)<jupyter_text>Missing Data Filter on yoa of at least 2005<jupyter_code>df1 = df0.copy()
def getFullYear(y):
    '''Converts yoa from yy format to yyyy format'''
    if y == 0:
        return 0
    elif y < 10:
        return float("200"+str(y)) - 2000
    elif y < 25:
        return float("20"+str(y)) - 2000
    elif y < 100:
        return float("19"+str(y)) - 2000
    else:
        return y - 2000
df1.yoa = df1.yoa.apply(getFullYear)
df1 = df1[df1.yoa >= 5]
print(df1.shape)<jupyter_output>(1260408, 14)<jupyter_text>Drop values with negative total cost and missing ages<jupyter_code>df1 = df1[df1.tot > 0]
print(df1.shape)
df1 = df1[False == df1.age.isna()]
print(df1.shape)
df1[col_arrival + col_target].isna().sum()
df1[col_arrival + col_target].describe(include='all')
print([*df1.pt_state.unique()])
state_codes = {'RI', 'MA', 'MN', 'CT', 'CA', 'ME', 'IL', 'KY', 'VA', 'FL', 'PA', 'TX', 'GA', 'NH', 'SC', 'AL', 'NC', 
              'WI', 'NY', 'QC', 'ID', 'LA', 'NE', 'MD', 'MI', 'TN', 'NJ', 'NM', 'VT', 'KS', 'OR', 'NV', 'OH', 'PR',
              'AZ', 'WV', 'AK', 'DE', 'IN', 'OK', 'CO', 'MO', 'HI', 'UT', 'IA', 'ON', 'YT', 'DC', 'AR', 'MS', 'VI',
              'NB', 'UK', 'EN', 'ND', 'WA', 'SD', 'FI', 'FC', 'MT', 'UN', 'WY', 'BC', 'RD', 'PQ', 'CD', 'RO', 'RT',
              'AB', 'AS', 'GU', '9'}
print(len(state_codes))
ansi = {"AK", "AL", "AR", "AS", "AZ", "CA", "CO", "CT", "DC", "DE", "FL", "FM", "GA", "GU", "HI", "IA", "ID", "IL", "IN", "KS", "KY", "LA", "MA", "MD", "ME", "MH", "MI", "MN", "MO", "MP", "MS", "MT", "NC", "ND", "NE", "NH", "NJ", "NM", "NV", "NY", "OH", "OK", "OR", "PA", "PR", "PW", "RI", "SC", "SD", "TN", "TX", "UM", "UT", "VA", "VI", "VT", "WA", "WI", "WV", "WY"}

state_codes = state_codes.union(ansi)
print(len(state_codes))

df1.pt_state = df1.pt_state.cat.set_categories(state_codes)
df1.pt_state = df1.pt_state.fillna('9')
print(df1.groupby(['pt_state']).size().sort_values(ascending=False))<jupyter_output>72
77
pt_state
RI    1157711
MA      71857
CT      20816
FL       1908
NY       1677
9         667
NJ        604
NH        502
PA        412
CA        405
ME        374
VA        289
TX        259
NC        237
MD        225
GA        205
IL        155
VT        142
SC        138
OH        137
AZ        124
MI        105
IN         67
WA         66
CO         62
MN         60
NV         58
TN         57
LA         51
WI         49
       ...   
PR         14
KS         12
IA         12
AK         11
ND          9
MT          9
SD          7
QC          6
BC          5
WY          5
VI          4
FI          2
RO          2
NB          2
PQ          2
AB          1
RT          1
GU          1
CD          1
RD          1
UK          1
EN          1
YT          1
UN          1
AS          1
MP          0
FM          0
MH          0
UM          0
PW          0
Length: 77, dtype: int64<jupyter_text>Code missing values as 9<jupyter_code>df1.er_mode = df1.er_mode.fillna(0)
df1.race = df1.race.fillna(9)
df1.race = df1.race.where(df1.race > 0, 9)
#df1.sex = df1.sex.fillna(9)
df1.asource = df1.asource.fillna('9')
df1.admtype = df1.admtype.fillna(9)
df1.age = df1.age.fillna(df1.age.median())
df1.diag_adm = df1.diag_adm.cat.add_categories(['XXX'])
df1.diag_adm = df1.diag_adm.fillna('XXX')
df2 = df1.copy()
print(df2.shape)
df2[col_arrival + col_target].isna().sum()
df2[col_arrival + col_target].describe(include='all')<jupyter_output><empty_output><jupyter_text>Categorize Diagnosis Codes<jupyter_code>df3 = df2.copy()
df3.head()
diag_cat3 = df1.diag_adm.astype(str).str[:3]
df3 = df3.assign(diag_cat3=diag_cat3.values)
diag_cat4 = df1.diag_adm.astype(str).str[:4]
df3 = df3.assign(diag_cat4=diag_cat4.values)
df3.head()
df4 = df3.copy()<jupyter_output><empty_output><jupyter_text>Change Datatypes<jupyter_code>print(df4.dtypes)
df4.provider = df4.provider.astype('int16') - 7200
types = {'pay_ub92': 'int8', 
         'sex': 'int8', 
         'provider': 'int8',
         'moa': 'int8',
         'yoa': 'int8',
         'admtype': 'int8',
         'er_mode': 'int8',
         'race': 'int8',
         'diag_cat3': 'category',
         'diag_cat4': 'category',
        }
for t, _ in types.items():
    print(t, list(df4[t].unique()))
df4 = df4.astype(types)
df4.head()
df4.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 1260006 entries, 282340 to 1544746
Data columns (total 16 columns):
pay_ub92     1260006 non-null int8
age          1260006 non-null float16
sex          1260006 non-null int8
provider     1260006 non-null int8
moa          1260006 non-null int8
yoa          1260006 non-null int8
admtype      1260006 non-null int8
asource      1260006 non-null category
tot          1260006 non-null float64
pt_state     1260006 non-null category
diag_adm     1260006 non-null category
campus       1260006 non-null category
er_mode      1260006 non-null int8
race         1260006 non-null int8
diag_cat3    1260006 non-null category
diag_cat4    1260006 non-null category
dtypes: category(6), float16(1), float64(1), int8(8)
memory usage: 42.7 MB<jupyter_text>Print out datatypes as a dictionary to make importing easier in the next notebook<jupyter_code>dtypes = df4.dtypes
dt = {}
for i in range(len(dtypes)):
    dt[dtypes.index[i]] = str(dtypes.iloc[i])
pprint(dt)<jupyter_output>{'admtype': 'int8',
 'age': 'float16',
 'asource': 'category',
 'campus': 'category',
 'diag_adm': 'category',
 'diag_cat3': 'category',
 'diag_cat4': 'category',
 'er_mode': 'int8',
 'moa': 'int8',
 'pay_ub92': 'int8',
 'provider': 'int8',
 'pt_state': 'category',
 'race': 'int8',
 'sex': 'int8',
 'tot': 'float64',
 'yoa': 'int8'}<jupyter_text>Filter Data<jupyter_code>#df4.to_csv("df_cleansed2_all.csv", index=False)
df4 = df4[df4.tot <= 30000]<jupyter_output><empty_output><jupyter_text>Save to CSV<jupyter_code>#df_sample = df4.sample(20000, random_state=0)
#df_sample.to_csv("df_sample_cleansed2.csv", index=False)
#df4.to_csv("df_cleansed2.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1654744229990552, 'fraction_numerical': 0.07085976515049264, 'mean_word_length': 2.7386478304742683, 'pattern_counts': {'":': 0, '<': 42, '<?xml version=': 0, '>': 41, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '95718', 'license': 'MIT', 'n_tokens_mistral': 3155, 'n_tokens_neox': 2819, 'n_words': 678, 'path': 'Data Cleansing.ipynb', 'repo_name': 'cd412/MATH490-Hospital-Data', 'size': '7418'}