<jupyter_start><jupyter_text>https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_sql.html<jupyter_code># Import libraries
import numpy as np
import pandas as pd
from pandas import DataFrame
import matplotlib.pyplot as plt
import psycopg2
import getpass
import pdvega

# for configuring connection 
from configobj import ConfigObj
import os

%matplotlib inline
# display all rows of pandas data frame
pd.set_option('display.max_rows', None)
pd.set_option('display.max_colwidth', -1)

# Create a database connection using settings from config file
config='../db/config.ini'

# connection info
conn_info = dict()
if os.path.isfile(config):
    config = ConfigObj(config)
    conn_info["sqluser"] = config['username']
    conn_info["sqlpass"] = config['password']
    conn_info["sqlhost"] = config['host']
    conn_info["sqlport"] = config['port']
    conn_info["dbname"] = config['dbname']
    conn_info["schema_name"] = config['schema_name']
else:
    conn_info["sqluser"] = 'postgres'
    conn_info["sqlpass"] = ''
    conn_info["sqlhost"] = '127.0.0.1'
    conn_info["sqlport"] = 5432
    conn_info["dbname"] = 'eicu'
    conn_info["schema_name"] = 'eicu'
    
# Connect to the eICU database
print('Database: {}'.format(conn_info['dbname']))
print('Username: {}'.format(conn_info["sqluser"]))
if conn_info["sqlpass"] == '':
    # try connecting without password, i.e. peer or OS authentication
    try:
        if (conn_info["sqlhost"] == 'localhost') & (conn_info["sqlport"]=='5432'):
            con = psycopg2.connect(dbname=conn_info["dbname"],
                                   user=conn_info["sqluser"])            
        else:
            con = psycopg2.connect(dbname=conn_info["dbname"],
                                   host=conn_info["sqlhost"],
                                   port=conn_info["sqlport"],
                                   user=conn_info["sqluser"])
    except:
        conn_info["sqlpass"] = getpass.getpass('Password: ')

        con = psycopg2.connect(dbname=conn_info["dbname"],
                               host=conn_info["sqlhost"],
                               port=conn_info["sqlport"],
                               user=conn_info["sqluser"],
                               password=conn_info["sqlpass"])
query_schema = 'set search_path to ' + conn_info['schema_name'] + ';'

query = query_schema + """
SELECT *
FROM diagnosis
"""

diagnosis_df = pd.read_sql_query(query, con)
diagnosis_df.to_pickle("pickles/diagnosis.pkl")

query = query_schema + """
SELECT *
FROM patient
"""

patient_df = pd.read_sql_query(query, con)
patient_df.to_pickle("pickles/patient.pkl")

patient_df.shape
# join patient and diagnosis
patient_diagonsis_df = pd.merge(patient_df, diagnosis_df, on="patientunitstayid")
pd.DataFrame.head(patient_diagonsis_df)
sepsis_icd9 = '038.'
sepsis_df = patient_diagonsis_df[patient_diagonsis_df["icd9code"].str.contains(sepsis_icd9)]
sepsis_df.shape
sepsis_df = patient_diagonsis_df[patient_diagonsis_df["apacheadmissiondx"].str.contains("Sepsis")]
sepsis_df.shape
query = query_schema + """
SELECT *
FROM apacheapsvar
"""

apache_aps_df = pd.read_sql_query(query, con)
apache_aps_df.to_pickle("pickles/apacheapsvar.pkl")
sepsis_df = pd.merge(sepsis_df, apache_aps_df, on="patientunitstayid")
sepsis_df.shape
sepsis_df.to_pickle("pickles/sepsis_df.pkl")
query = query_schema + """
SELECT *
FROM hospital
"""

hospital_df = pd.read_sql_query(query, con)
hospital_df.to_pickle("pickles/hospital.pkl")
hospital_df.shape
sepsis_df["hospitalid"].value_counts()
# 89878 people
big_hospital_id = 420 

# 11885 people
medium_hospital_id = 142

# 5428 people
small_hospital_id = 390
big_hospital_df = sepsis_df[sepsis_df["hospitalid"] == big_hospital_id]
big_hospital_df.to_pickle("pickles/big_hospital.pkl")

medium_hospital_df = sepsis_df[sepsis_df["hospitalid"] == medium_hospital_id]
medium_hospital_df.to_pickle("pickles/medium_hospital.pkl")

small_hospital_df = sepsis_df[sepsis_df["hospitalid"] == small_hospital_id]
small_hospital_df.to_pickle("pickles/small_hospital.pkl")

print("big hospital dims:\t", big_hospital_df.shape)
print("medium hospital dims:\t", medium_hospital_df.shape)
print("small hospital dims:\t", small_hospital_df.shape)
three_hospital_df = sepsis_df[(sepsis_df["hospitalid"] == big_hospital_id) |
                             (sepsis_df["hospitalid"] == medium_hospital_id) |
                             (sepsis_df["hospitalid"] == small_hospital_id)]
three_hospital_df.to_pickle("pickles/three_hospital.pkl")
three_hospital_df.shape
89878 +11885 + 5428<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1294604003481288, 'fraction_numerical': 0.013489991296779809, 'mean_word_length': 4.136312849162011, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '224894', 'license': 'MIT', 'n_tokens_mistral': 1641, 'n_tokens_neox': 1520, 'n_words': 247, 'path': 'notebooks/my_scripts/pickle_db.ipynb', 'repo_name': 'JimmyJHickey/eicu-code', 'size': '4596'}