<jupyter_start><jupyter_text>Kursarbeit Datenmanagement und -Archivierung WS 20/21
 Master-Code [IMECOS-Projekt](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung)


---

  Initialisierung für Projekt-Administratoren<jupyter_code>#Load encryption tools
!pip install cape_privacy<jupyter_output>Requirement already satisfied: cape_privacy in /usr/local/lib/python3.6/dist-packages (0.3.0)
Requirement already satisfied: pyyaml==5.3.1 in /usr/local/lib/python3.6/dist-packages (from cape_privacy) (5.3.1)
Requirement already satisfied: requests==2.23.0 in /usr/local/lib/python3.6/dist-packages (from cape_privacy) (2.23.0)
Requirement already satisfied: numpy==1.18.1 in /usr/local/lib/python3.6/dist-packages (from cape_privacy) (1.18.1)
Requirement already satisfied: pandas==1.0.3 in /usr/local/lib/python3.6/dist-packages (from cape_privacy) (1.0.3)
Requirement already satisfied: pycryptodome==3.9.8 in /usr/local/lib/python3.6/dist-packages (from cape_privacy) (3.9.8)
Requirement already satisfied: validators==0.18.0 in /usr/local/lib/python3.6/dist-packages (from cape_privacy) (0.18.0)
Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.6/dist-packages (from requests==2.23.0->cape_privacy) (2.10)
Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/[...]<jupyter_text>Datenbankinitialisierung & Quelldatenimport ([Wiki/Quelldatenmodell](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/wiki/Quelldatenmodelldatenbank-implementierung))<jupyter_code># initialize the database, see import statements in *implementation.py
import requests
exec(requests.get('https://raw.githubusercontent.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/main/src/source_database_implementation.py').text)<jupyter_output>Mounted at /content/gdrive
Successfully Connected to SQLite
Successfully created tables in the database
Successfully inserted source data
Source database ready for work
  
      conn    -    is the name of the connection object
      cur     -    is the name of the cursor<jupyter_text>*   Verbindung zu Googledrive herstellen (Ablageort der CSV-Dateien)
*   Verbindung zu SQLite herstellen
*   Datebankinitialisierung im *Memory*
*   [Quelldaten](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/wiki/Quelldatenmodell)import
*   Ausgabe der Connection- und Cursor-Syntax


---




  Datenqualität I - Datenexploration ([Wiki/Datenqualität](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/wiki/Datenqualit%C3%A4tidentifikation-von-redundanzen-und-unstimmigkeiten---data-clearing))
Die Folgenden beiden Zellen sind optional zur Ausführung der `data_clearing_SQL_queries`.

Legende zu den GitHub-Issues zur Datenqualität:


[12](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/issues/12), 
[13](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/issues/13),
[14](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/issues/14),
[15](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/issues/15),
[30](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/issues/30).<jupyter_code># decions about the design of the star scheme have followed several deeper looks into the data. Included here for documentation purpose.
# EXECUTION OF THIS CELL IS OPTIONAL!
# uncomment the following two lines after one another to see the documentation of this step.

# exec(requests.get('https://raw.githubusercontent.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/main/tools/data_clearing_SQL_queries.py').text)
# data_clearing_queries(30)<jupyter_output><empty_output><jupyter_text>---



  ETL-Prozess ([Wiki/ETL-Prozess](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/wiki/Datenbereinigung-im-ETL-Prozess))<jupyter_code># CREATE VIEW for central table  (but with DESCRIPTIONS)
cur.executescript(requests.get('https://raw.githubusercontent.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/main/tools/ETL_create_view_central.sql').text)
conn.commit()
# Perform Extraction, Transformation and Loading into new database
exec(requests.get('https://raw.githubusercontent.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/main/tools/ETL_process.py').text)<jupyter_output>Successfully Extracted Data
Successfully Transformed Data
Successfully Connected to SQLite Public Data Warehouse
Loaded research data
encounters :  2156497301953685746
patients :  -7379940451828567556
payers :  7404035236410964453
code_master :  -6726061372865899500
Successfully finished ETL process
Research database ready for work
  
      conn_new    -    is the name of the connection object
      cur_new     -    is the name of the cursor<jupyter_text>*   View auf Quelldatenbank erstellen ([ETL_create_view_central.sql](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/blob/main/tools/ETL_create_view_central.sql))
*   Datenextraktion ([ETL_process.py](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/blob/main/tools/ETL_process.py))
*   Verbindung zu SQLite herstellen
*   Datebankinitialisierung in öffentlichem Google Drive ([IMECOS_public/imecos_public.db](https://drive.google.com/file/d/1cnzmvXIE7CDIULh8hIHPCZJKEI4RKTiX/view?usp=sharing))
*   Anzeige der erstellten Post-ETL Tabellen inkl. Hash-Summen
*   Ausgabe der neuen Connection- und Cursor-Syntax


---
  Initialisierung für die Öffentlichkeit
Vor Ausführung ist eine Verlinkung der Datenbank in ein Google Drive notwendig.
 Siehe Hinweise unter [System-Umgebung](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/wiki/System-Umgebung)<jupyter_code>#Für Zugang von Extern beide Zeilen einkommentieren
#import requests
#exec(requests.get('https://raw.githubusercontent.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/main/src/public_datawarehouse_connection.py').text)<jupyter_output><empty_output><jupyter_text>*   Verbindung zu SQLite herstellen
*   Datebank in öffentlichem Google Drive ([IMECOS_public/imecos_public.db](https://drive.google.com/file/d/1cnzmvXIE7CDIULh8hIHPCZJKEI4RKTiX/view?usp=sharing)) verbinden
*   Anzeige der Post-ETL Tabellen inkl. Hash-Summen:

Tabelle | Hash-Summe
----- | -----
encounters | 2156497301953685746
patients | -7379940451828567556
payers | 7404035236410964453
code_master | -6726061372865899500

*   Ausgabe der neuen Connection- und Cursor-Syntax


---
  Deskriptive Analysen des Forschungsdatensatzes Import der [Seaborn](https://seaborn.pydata.org/)-Umgebungen zur grafischen Analyse & Befüllen der Dataframes:<jupyter_code># import plot libraries
import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib as mpl
%matplotlib inline

# save tables from DB into dataframe
encounters = pd.read_sql_query("SELECT * FROM encounters", conn_new)
patients = pd.read_sql_query("SELECT * FROM patients", conn_new)<jupyter_output><empty_output><jupyter_text>Zunächst Entfernung der Extremwerte:  Datenqualität II - Entfernung von Extremwerten ([Wiki/Datenqualität](https://github.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/wiki/Datenqualit%C3%A4tidentifikation-von-fehlern-und-ausrei%C3%9Fern-in-der-datenbasis---preliminary-data-analysis))<jupyter_code># BOXPLOT of patients COV_EXP_RATIO
print("""patients.COV_EXP_RATO

""")
sns.boxplot(x=patients["COV_EXP_RATIO"])
plt.savefig('gdrive/Shareddrives/IMECOS/plots/COV_EXP_outlier.png')
plt.show()
# remove outlier data from data warehouse via a SQL Delete Script
cur_new.executescript(requests.get('https://raw.githubusercontent.com/Fuenfgeld/ATeamDatenmanagementUndArchivierung/main/tools/data_quality_delete_outlier.sql').text)
conn_new.commit()

# update dataframes from DB
encounters = pd.read_sql_query("SELECT * FROM encounters", conn_new)
patients = pd.read_sql_query("SELECT * FROM patients", conn_new)

# verify successful deletion by again plotting BOXPLOT of patients COV_EXP_RATIO
print("""patients.COV_EXP_RATO nach Entfernung von Extremwerten

""")
sns.boxplot(x=patients["COV_EXP_RATIO"])<jupyter_output>patients.COV_EXP_RATO nach Entfernung von Extremwerten<jupyter_text>---

  Deskriptive Analysen<jupyter_code># Descriptive statistics PATIENTS
print("Patients:")
patients.info()
print()
print("Encounters:")
encounters.info()<jupyter_output>Patients:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 7352 entries, 0 to 7351
Data columns (total 9 columns):
 #   Column         Non-Null Count  Dtype  
---  ------         --------------  -----  
 0   Id             7352 non-null   object 
 1   BIRTHDATE      7352 non-null   object 
 2   DEATHDATE      252 non-null    object 
 3   MARITAL        4886 non-null   object 
 4   RACE           7352 non-null   object 
 5   ETHNICITY      7352 non-null   object 
 6   GENDER         7352 non-null   object 
 7   BIRTHPLACE     7352 non-null   object 
 8   COV_EXP_RATIO  7352 non-null   float64
dtypes: float64(1), object(8)
memory usage: 517.1+ KB

Encounters:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 181361 entries, 0 to 181360
Data columns (total 31 columns):
 #   Column                     Non-Null Count   Dtype  
---  ------                     --------------   -----  
 0   Id                         181361 non-null  object 
 1   DATASET_ORIGIN             181361 non-null  ob[...]<jupyter_text>---<jupyter_code># Convert date columns
patients["BIRTHDATE"] = pd.to_datetime(patients["BIRTHDATE"])
patients["DEATHDATE"] = pd.to_datetime(patients["DEATHDATE"])

# calcute patient age
patients["AGE"] = patients.DEATHDATE.dt.year - patients.BIRTHDATE.dt.year
patients["AGE"] = patients["AGE"].fillna(2021 - patients.BIRTHDATE.dt.year)

# patients age distribution
sns.displot(patients["AGE"])
plt.savefig('gdrive/Shareddrives/IMECOS/plots/AGE_hist.png')
plt.show()<jupyter_output><empty_output><jupyter_text>---<jupyter_code># patients gender
sns.countplot(x="variable", hue= "value", data=pd.melt(patients[["GENDER"]]))
plt.savefig('gdrive/Shareddrives/IMECOS/plots/GENDER_countplot.png')
plt.show()<jupyter_output><empty_output><jupyter_text>---<jupyter_code># distribution DATASET_ORIGIN
sns.countplot(x="variable", 
              hue="value", 
              data=pd.melt(encounters.groupby('Id').agg(np.unique)[['DATASET_ORIGIN']]))
plt.legend(loc='upper center', bbox_to_anchor=(1.45, 0.8), ncol=1)
plt.savefig('gdrive/Shareddrives/IMECOS/plots/DATASET_ORIGIN_countplot.png', bbox_inches='tight')
plt.show()<jupyter_output><empty_output><jupyter_text>---<jupyter_code># distribution ENCOUNTERCLASS
sns.countplot(x="variable", hue= "value", data=pd.melt(encounters.groupby('Id').agg(np.unique)[['ENCOUNTERCLASS']]))
plt.savefig('gdrive/Shareddrives/IMECOS/plots/ENCOUNTERCLASS_countplot.png')
plt.show()<jupyter_output><empty_output><jupyter_text>---<jupyter_code># Welche Aufenthalte kommen in welchem Datenset wie oft vor?? (aufgeteilt nach Krankheitsbild)
# sql_query
q1b2 = pd.read_sql_query("""
SELECT
Id,
ENCOUNTERCLASS,
DATASET_ORIGIN
FROM
encounters
GROUP BY Id, DATASET_ORIGIN
""", conn_new
)
# countplot
sns.catplot(data=pd.melt(q1b2, id_vars='DATASET_ORIGIN', value_vars='ENCOUNTERCLASS'), x='variable', hue='value', col='DATASET_ORIGIN', col_wrap=4, kind='count', height=3.7)
plt.yscale('log')
plt.savefig('gdrive/Shareddrives/IMECOS/plots/ENCOUNTERCLASS_by_disease.png')
plt.show()<jupyter_output><empty_output><jupyter_text>---<jupyter_code># bei welchen Aufenthalten erfolgen Impfungen zumeist (aufgeteilt nach Krankheitsbild)
# sql_query
q1b2 = pd.read_sql_query("""
SELECT
Id,
ENCOUNTERCLASS,
DATASET_ORIGIN
FROM
encounters
GROUP BY Id, DATASET_ORIGIN
HAVING COUNT(immunization_CODE)>0
""", conn_new
)
# countplot
sns.catplot(data=pd.melt(q1b2, id_vars='DATASET_ORIGIN', value_vars='ENCOUNTERCLASS'), x='variable', hue='value', col='DATASET_ORIGIN', col_wrap=4, kind='count', height=3.7)
plt.yscale('log')
plt.savefig('gdrive/Shareddrives/IMECOS/plots/Immunizations_during_ENCOUNTERCLASS_by_disease.png')
plt.show()<jupyter_output><empty_output><jupyter_text>---

  Forschungshypothesen  Hypothese 1 
_Die Anzahl der Impfungen von Patient*innen korreliert negativ mit der Anzahl von notfallmäßigen Vorstellungen in einem Krankenhaus._


---

 

* Abfrage aus den Tabellen 'Encounters' und 'Patients' zur Evaluation der stattgefundenen Impfungen
* Aufteilung in Impfgruppen
* Umwandlulng von objects in integers für Machinelearning-Skript
* Bündelung von 'emergency' und 'urgentcare' zu einer Vorstellungsklasse
* Target des ML auf die 'Immunization groups'<jupyter_code>patients.info()

#Abfrage der Impfanzahl ohne Einteilung in Bins
pat_count_imm_nobins = pd.read_sql_query("""
SELECT 
  patients.Id,
  (CASE
            WHEN marital LIKE '%m%'
            THEN 0
            WHEN marital LIKE '%s%'
            THEN 1
            ELSE NULL
        END) AS ML_marital_code,
  (CASE
            WHEN race LIKE '%white%'
            THEN 0
            WHEN race LIKE '%black%'
            THEN 1
            WHEN race LIKE '%asian%'
            THEN 2
            WHEN race LIKE '%native%'
            THEN 3
            ELSE NULL
        END) AS ML_race_code,
  (CASE
            WHEN ethnicity LIKE '%non%'
            THEN 0
            WHEN ethnicity LIKE '%hisp%'
            THEN 1
            ELSE NULL
        END) AS ML_ethnicity_code,
  (CASE
            WHEN gender LIKE '%f%'
            THEN 0
            WHEN gender LIKE '%m%'
            THEN 1
            ELSE NULL
        END) AS ML_gender_code,
  patients.COV_EXP_RATIO,
  count_pat_immunizations.IMMUNIZATIONS,
  (CASE
            WHEN encounterclass LIKE '%ambulatory%'
            THEN 0
            WHEN encounterclass LIKE '%wellness%'
            THEN 0
            WHEN encounterclass LIKE '%emergency%'
            THEN 1
            WHEN encounterclass LIKE '%inpatient%'
            THEN 0
            WHEN encounterclass LIKE '%outpatient%'
            THEN 0
            WHEN encounterclass LIKE '%urgentcare%'
            THEN 1            
            ELSE NULL
        END) AS ML_encounter_code
FROM   patients 
       JOIN (SELECT patient_id, 
                        encounterclass,
                        encounter_code,
                        COUNT(DISTINCT id 
                                        || dataset_origin 
                                        || immunization_date 
                                        || immunization_code) AS immunizations 
                  FROM   encounters 
                  GROUP  BY patient_id) AS count_pat_immunizations 
              ON patients.id = count_pat_immunizations.patient_id
""", conn_new)
pat_count_imm_nobins.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 7352 entries, 0 to 7351
Data columns (total 8 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   Id                 7352 non-null   object 
 1   ML_marital_code    4886 non-null   float64
 2   ML_race_code       7348 non-null   float64
 3   ML_ethnicity_code  7352 non-null   int64  
 4   ML_gender_code     7352 non-null   int64  
 5   COV_EXP_RATIO      7352 non-null   float64
 6   immunizations      7352 non-null   int64  
 7   ML_encounter_code  7352 non-null   int64  
dtypes: float64(3), int64(4), object(1)
memory usage: 459.6+ KB<jupyter_text>---<jupyter_code># Generating the correlating matrix
corr2 = pat_count_imm_nobins.corr()
plt.figure(figsize=(16, 6))
# Generating the correlation heat-map (seaborn = sns)
sns.heatmap(corr2, annot=True)
plt.savefig('gdrive/Shareddrives/IMECOS/plots/Hyp1_immunization_count_correlation.png')
plt.show()<jupyter_output><empty_output><jupyter_text>--> Hier zeigt sich eine Korrelation zwischen der Kostendeckung ('COV_EXP_RATIO') und der Anzahl der Immunisierungen.



---
Weitere Analyse des Datasets mit Erstellung von Patientengruppen nach Anzahl der stattgehabten Impfungen<jupyter_code>from sklearn.preprocessing import KBinsDiscretizer
X = pat_count_imm_nobins['COV_EXP_RATIO'].values
X = X.reshape(-1, 1)

enc_quantile = KBinsDiscretizer(n_bins=4, encode='ordinal', strategy='quantile')
X_binned = enc_quantile.fit_transform(X)

print("quantile bin_edges: ", enc_quantile.bin_edges_[0])
pat_count_imm_nobins['COV_EXP_RATIO_binned'] = X_binned
pat_count_imm_nobins
#quantile binned
g = sns.JointGrid(data=pat_count_imm_nobins, x="immunizations", y="COV_EXP_RATIO_binned", marginal_ticks=True)

# Set a log scaling on the y axis
#g.ax_joint.set(yscale="log")

# Create an inset legend for the histogram colorbar
cax = g.fig.add_axes([.15, .55, .02, .2])

# Add the joint and marginal histogram plots
g.plot_joint(
    sns.histplot, discrete=(True, False),
    cmap="light:#03012d", pmax=.8, cbar=True, cbar_ax=cax
)
g.plot_marginals(sns.histplot, element="step", color="#03012d")
# is saved later at Hyp3
#Einteilung der Impfanzahl in einzelne container für ML-readyness
pat_imm = pd.read_sql_query("""
SELECT 
  patients.Id,
  (CASE
            WHEN marital LIKE '%m%'
            THEN 0
            WHEN marital LIKE '%s%'
            THEN 1
            ELSE NULL
        END) AS ML_marital_code,
  (CASE
            WHEN race LIKE '%white%'
            THEN 0
            WHEN race LIKE '%black%'
            THEN 1
            WHEN race LIKE '%asian%'
            THEN 2
            WHEN race LIKE '%native%'
            THEN 3
            ELSE NULL
        END) AS ML_race_code,
  (CASE
            WHEN ethnicity LIKE '%non%'
            THEN 0
            WHEN ethnicity LIKE '%hisp%'
            THEN 1
            ELSE NULL
        END) AS ML_ethnicity_code,
  (CASE
            WHEN gender LIKE '%f%'
            THEN 0
            WHEN gender LIKE '%m%'
            THEN 1
            ELSE NULL
        END) AS ML_gender_code,
  patients.COV_EXP_RATIO,
  count_pat_immunizations.IMMUNIZATIONS,
  (CASE
            WHEN encounterclass LIKE '%ambulatory%'
            THEN 0
            WHEN encounterclass LIKE '%wellness%'
            THEN 0
            WHEN encounterclass LIKE '%emergency%'
            THEN 1
            WHEN encounterclass LIKE '%inpatient%'
            THEN 0
            WHEN encounterclass LIKE '%outpatient%'
            THEN 0
            WHEN encounterclass LIKE '%urgentcare%'
            THEN 1            
            ELSE NULL
        END) AS ML_encounter_code
FROM   patients 
       JOIN (SELECT patient_id, 
                        encounterclass,
                        encounter_code,
                        COUNT(DISTINCT id 
                                        || dataset_origin 
                                        || immunization_date 
                                        || immunization_code) AS immunizations 
                  FROM   encounters 
                  GROUP  BY patient_id) AS count_pat_immunizations 
              ON patients.id = count_pat_immunizations.patient_id
""", conn_new)
pat_imm.info()
# bin immunization count to groups instead of dividing data into 'vaccinated' and 'not-vaccinated'
from sklearn.preprocessing import KBinsDiscretizer
# extract values of 'immunizations' column
X = pat_imm['immunizations'].values
# make sure that X is an array of shape [len(X)<, 1]
X = X.reshape(-1, 1)
# set the parameters for the algorithm (see https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.KBinsDiscretizer.html#sklearn.preprocessing.KBinsDiscretizer for more info)
imm_binning = KBinsDiscretizer(n_bins=5, encode='ordinal', strategy='quantile')
# apply the binning algorithm on the data and print the edges
X_binned = imm_binning.fit_transform(X)
print("immunization_count bin_edges: ", imm_binning.bin_edges_[0])
# replace data in the dataframe (to have this colum ready for Machine Learning) and rename colums to unambiguous name
pat_imm['immunizations'] = X_binned
pat_imm = pat_imm.rename(columns={'immunizations': 'imm_group'})<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 7352 entries, 0 to 7351
Data columns (total 8 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   Id                 7352 non-null   object 
 1   ML_marital_code    4886 non-null   float64
 2   ML_race_code       7348 non-null   float64
 3   ML_ethnicity_code  7352 non-null   int64  
 4   ML_gender_code     7352 non-null   int64  
 5   COV_EXP_RATIO      7352 non-null   float64
 6   immunizations      7352 non-null   int64  
 7   ML_encounter_code  7352 non-null   int64  
dtypes: float64(3), int64(4), object(1)
memory usage: 459.6+ KB
immunization_count bin_edges:  [ 0.  7. 10. 12. 16. 36.]<jupyter_text>---<jupyter_code>#Zeige die Verteilung innerhalb der neu enstandenen Impfgruppen
pat_imm['imm_group'].plot(kind='hist', bins=5, figsize=(10,4))
plt.savefig('gdrive/Shareddrives/IMECOS/plots/imm_group_distribution.png')
plt.show()
#Ausschluss der NULL enthaltenden Tupel (vor allem durch den unklaren Familienstand enthalten)
pat_imm = pat_imm.dropna()
pat_imm.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 4882 entries, 0 to 7351
Data columns (total 8 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   Id                 4882 non-null   object 
 1   ML_marital_code    4882 non-null   float64
 2   ML_race_code       4882 non-null   float64
 3   ML_ethnicity_code  4882 non-null   int64  
 4   ML_gender_code     4882 non-null   int64  
 5   COV_EXP_RATIO      4882 non-null   float64
 6   imm_group          4882 non-null   float64
 7   ML_encounter_code  4882 non-null   int64  
dtypes: float64(4), int64(3), object(1)
memory usage: 343.3+ KB<jupyter_text>---
* Erstellung einer Korrelationsmatrix der neu codierten Tupel<jupyter_code># Generating the correlating matrix
corr = pat_imm.corr()
plt.figure(figsize=(16, 6))
# Generating the correlation heat-map (seaborn = sns)
sns.heatmap(corr, annot=True)
#Verteiliung des Familenstandes
sns.displot(data=pat_imm, x="ML_marital_code",  hue="imm_group", kind="kde")
#Verteilung der Kostendeckung
sns.displot(data=pat_imm, x="COV_EXP_RATIO",  hue="imm_group", kind="kde")<jupyter_output><empty_output><jupyter_text>--> Es scheint eine poitive Korrelation des Familienstandes mit der Verortung in einer Impfgruppe zu bestehen. 
--> Zudem zeigt sich eine Korrelation zwischen der Kostendeckung ('cov_exp_ratio' und der Verortung in einer Impfgruppe) 

---
* Machine learning: welcher Parameter kann Verortung in einer Impfgruppe vorhersagen?<jupyter_code>#Prepare Machine learning, x beeing variables, y beeing the desired descision target
X = pat_imm.loc[:,['ML_marital_code',
 'ML_race_code',
 'ML_ethnicity_code',
 'ML_gender_code',
 'COV_EXP_RATIO',
 'ML_encounter_code'
             ]].values
      
Y = pat_imm.loc[:,['imm_group']].values
#Split samplte into test-population and training-population fpr machine learnig. Define the desired depth of descision tree (here 4)
from sklearn.model_selection import train_test_split
X_train, X_test, Y_train, Y_test = train_test_split(X, Y, test_size = 0.25, random_state = 0)
from sklearn import tree
classifier = tree.DecisionTreeClassifier(max_depth=4)  # limit depth of tree
classifier.fit(X_train, Y_train)
#Calculate accuracy of prediction
Y_pred = classifier.predict(X_test)
from sklearn.metrics import confusion_matrix
cm = confusion_matrix(Y_test, Y_pred)
import sklearn.metrics
sklearn.metrics.accuracy_score(Y_test, Y_pred)
#Prepare functions for descision tree, fn beeing the variables and cb beeing the lables for the different immuization groups
import graphviz
fn=['ML_marital_code',
 'ML_race_code',
 'ML_ethnicity_code',
 'ML_gender_code',
 'COV_EXP_RATIO',
 'ML_encounter_code']
cn=['Imm_group1', 'Imm_group2', 'Imm_group3', 'Imm_group4', 'Imm_group5']
#plot descision tree
dot_data = tree.export_graphviz(classifier, out_file=None, 
                                feature_names = fn, 
                                class_names=cn,
                                filled=True
                                )
# Draw graph
graph = graphviz.Source(dot_data) 
graph.render('ML_tree_hyp_1', 'gdrive/Shareddrives/IMECOS/plots/', cleanup=True, format='png')
graph<jupyter_output><empty_output><jupyter_text>Hypothese 1:
_Die Anzahl der Impfungen von Patient*innen korreliert negativ mit der Anzahl von notfallmäßigen Vorstellungen in einem Krankenhaus._

--> Mithilfe der Kostendeckung ist eine Vorhersage zur Verortung in einer Immunisierungskategorie möglich

--> Die Anzahl der Impfungen korreliert **NICHT** mit der Anzahl notfallmäßiger Vorstellungen in einem Krankenhaus.


---

  Hypothese 2
_Die Kosten für Impfungen werden durch eingesparte Behandlungskosten im Notfallkontext aufgewogen (Impfkosten vs. Kosten der Notfallbehandlung)._ ongoing anaysis

---

  Hypothese 3
_Geimpfte Patienten zeichnen sich durch eine höhere Absicherung der insgesamt anfallenden Gesundheitskosten aus._<jupyter_code># design table ('patient_id', 'dataset_origin', 'immunizations', 'cov_exp_ratio')
count_pat_immunizations = pd.read_sql_query("""
SELECT encounters.patient_id,
       encounters.dataset_origin,
       Count(DISTINCT encounters.id 
             ||encounters.dataset_origin 
             ||encounters.immunization_date 
             ||encounters.immunization_code) AS immunizations,
        patients.cov_exp_ratio 
        FROM   encounters,
               patients
        WHERE  encounters.patient_id = patients.Id
               AND patients.cov_exp_ratio <= 1 
        GROUP  BY encounters.patient_id,
                  encounters.dataset_origin
""", conn_new)
# plot distribution of COV_EXP_RATIO by DATASET_ORIGIN
g = sns.FacetGrid(count_pat_immunizations, col="DATASET_ORIGIN", col_wrap=4)
g.map(sns.histplot, 'COV_EXP_RATIO')
plt.savefig('gdrive/Shareddrives/IMECOS/plots/COV_EXP_distribution_by_disease.png')<jupyter_output><empty_output><jupyter_text>All Datasets<jupyter_code># show distribution of COV_EXP_RATIO over all datasets (stacked bars)
f, ax = plt.subplots(figsize=(14, 10))
sns.despine(f)

sns.histplot(
    count_pat_immunizations,
    x="COV_EXP_RATIO", hue="DATASET_ORIGIN",
    multiple="stack",
    palette="light:m_r",
    edgecolor=".3",
    linewidth=.5
)
ax.xaxis.set_major_formatter(mpl.ticker.ScalarFormatter())
plt.savefig('gdrive/Shareddrives/IMECOS/plots/COV_EXP_distribution_stacked.png')
plt.show()
# binning of COV_EXP_RATIO to four groups with same amount of data points
from sklearn.preprocessing import KBinsDiscretizer
X = count_pat_immunizations['COV_EXP_RATIO'].values
X = X.reshape(-1, 1)

enc_quantile = KBinsDiscretizer(n_bins=4, encode='ordinal', strategy='quantile')
X_binned = enc_quantile.fit_transform(X)

# write binning group to df
count_pat_immunizations['COV_EXP_RATIO_binned'] = X_binned

# print bin_edges
print("quantile bin_edges: ", enc_quantile.bin_edges_[0])

#plot quantile binned distribution
g = sns.JointGrid(data=count_pat_immunizations, x="immunizations", y="COV_EXP_RATIO_binned", marginal_ticks=True)

# Create an inset legend for the histogram colorbar
cax = g.fig.add_axes([.15, .55, .02, .2])

# Add the joint and marginal histogram plots
g.plot_joint(
    sns.histplot, discrete=(True, False),
    cmap="light:#03012d", pmax=.8, cbar=True, cbar_ax=cax
)
g.plot_marginals(sns.histplot, element="step", color="#03012d")
plt.savefig('gdrive/Shareddrives/IMECOS/plots/immunizations_x_COV_EXP_RATIO_binned.png')
plt.show()<jupyter_output><empty_output><jupyter_text>* Es ist eine Tendenz ersichtlich, dass Patienten, die mehr geimpft sind, auf eine bessere Absicherung der Gesundheitskosten haben
* Für weitergehende Analysen wäre es notwendig, mehr Daten von Patienten mit einer höheren Absicherung zu acquirieren, um die Hypothese überprüfen zu können.

---

  Subset analysis<jupyter_code># only analysis of metabolic_syndrome_disease, covid19, breast_cancer, allergy
# design table ('patient_id', 'dataset_origin', 'immunizations', 'cov_exp_ratio')
count_pat_immunizations_subset = pd.read_sql_query("""
SELECT encounters.patient_id,
       encounters.dataset_origin,
       Count(DISTINCT encounters.id 
             ||encounters.dataset_origin 
             ||encounters.immunization_date 
             ||encounters.immunization_code) AS immunizations,
        patients.cov_exp_ratio 
        FROM   encounters,
               patients
        WHERE  encounters.patient_id = patients.Id
               AND patients.cov_exp_ratio <= 1
               AND encounters.dataset_origin IN (
                 'metabolic_syndrome_disease',
                 'covid19',
                 'breast_cancer',
                 'allergy'
               )
        GROUP  BY encounters.patient_id,
                  encounters.dataset_origin
""", conn_new)
# show distribution of COV_EXP_RATIO over all datasets in SUBSET ANALYSIS (stacked bars)
f, ax = plt.subplots(figsize=(14, 10))
sns.despine(f)

sns.histplot(
    count_pat_immunizations_subset,
    x="COV_EXP_RATIO", hue="DATASET_ORIGIN",
    multiple="stack",
    palette="light:m_r",
    edgecolor=".3",
    linewidth=.5
)
ax.xaxis.set_major_formatter(mpl.ticker.ScalarFormatter())
# no export of figure (because not shown in Paper Light)
# binning of COV_EXP_RATIO to four groups with same amount of data points
from sklearn.preprocessing import KBinsDiscretizer
X = count_pat_immunizations_subset['COV_EXP_RATIO'].values
X = X.reshape(-1, 1)

enc_quantile = KBinsDiscretizer(n_bins=4, encode='ordinal', strategy='quantile')
X_binned = enc_quantile.fit_transform(X)

# write binning group to df
count_pat_immunizations_subset['COV_EXP_RATIO_binned'] = X_binned

# print bin_edges
print("quantile bin_edges: ", enc_quantile.bin_edges_[0])

#plot quantile binned distribution
g = sns.JointGrid(data=count_pat_immunizations, x="immunizations", y="COV_EXP_RATIO_binned", marginal_ticks=True)

# Create an inset legend for the histogram colorbar
cax = g.fig.add_axes([.15, .55, .02, .2])

# Add the joint and marginal histogram plots
g.plot_joint(
    sns.histplot, discrete=(True, False),
    cmap="light:#03012d", pmax=.8, cbar=True, cbar_ax=cax
)
g.plot_marginals(sns.histplot, element="step", color="#03012d")
# no export of figure (because not shown in Paper Light)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09819324430479183, 'fraction_numerical': 0.025432050274941085, 'mean_word_length': 3.4518432172519304, 'pattern_counts': {'":': 0, '<': 94, '<?xml version=': 0, '>': 99, 'https://': 24, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '377771', 'license': 'MIT', 'n_tokens_mistral': 11078, 'n_tokens_neox': 10379, 'n_words': 2215, 'path': 'IMECOS_master.ipynb', 'repo_name': 'Fuenfgeld/ATeamDatenmanagementUndArchivierung', 'size': '30552'}