<jupyter_start><jupyter_text>**Notebook display settings**This cell enables inline charts and sets table display settings<jupyter_code>%matplotlib inline

import matplotlib
matplotlib.style.use('ggplot')
matplotlib.rcParams['figure.figsize'] = (10.0, 8.0)

import pandas as pd
pd.options.display.max_columns = 10
pd.options.display.max_rows = 10<jupyter_output><empty_output><jupyter_text>**Importing the MIMIC `session` module**The `session` module provides the ability to log in for easy access to the  MIMIC database. Once logged in, the rest of the library just works.Another ability provided by `session` is to enable local storage. Once enabled, this will cause `chatto-transform` to store the results of all queries it makes to the mimic database on the filesystem. Then, if the same queries are requested again later, they'll be loaded into the program without connecting to the remote database. Loading from local storage is about 10 times faster than going to the database.<jupyter_code>from chatto_transform.lib.mimic import session

session.login()
session.enable_local_storage('/Users/dan/dev/data/mimic')<jupyter_output><empty_output><jupyter_text>**Importing pre-defined transforms**The two transforms **AgeTransform** and **AgeHistTransform** capture this query from the MIMIC cookbook:```select bucket+15, count(*) from (    select        months_between(ad.admit_dt, dp.dob)/12,        width_bucket(months_between(ad.admit_dt, dp.dob)/12, 15, 100, 85) as bucket    from        mimic2v26.admissions ad,        mimic2v26.d_patients dp    where        ad.subject_id = dp.subject_id        and months_between(ad.admit_dt, dp.dob)/12 between 15 and 199) subgroup by bucketorder by bucket;```<jupyter_code>from chatto_transform.transforms.mimic.age_transform import AgeTransform, AgeHistTransform
# load the tables 'admissions' and 'd_patients' and merges them
age_df = AgeTransform().load_transform()
age_df<jupyter_output><empty_output><jupyter_text>**Creating a histogram of age at admission**The functionality for creating this histogram is wrapped up in the AgeHistTransform class, but it's quite simple. Pass in a DataFrame with an 'age_at_admission' column, get out a histogram.<jupyter_code>age_count_df = AgeHistTransform().transform(age_df)
age_count_df<jupyter_output><empty_output><jupyter_text>**Plot the histogram**(Axes need work...)<jupyter_code>age_count_df.plot(kind='bar')<jupyter_output><empty_output><jupyter_text>**Importing the Blood Urea Nitrogen transform**Capturing the 'bun' recipe from the MIMIC cookbook:```select bucket, count(*) from (  select width_bucket(valuenum, 0, 280, 280) as bucket    from mimic2v26.labevents le,         mimic2v26.d_patients dp   where itemid in (50177)     and le.subject_id = dp.subject_id     and months_between(le.charttime, dp.dob)/12 > 15  ) group by bucket order by bucket;```<jupyter_code>from chatto_transform.transforms.mimic.bun_transform import BUNTransform, BUNHistTransform<jupyter_output><empty_output><jupyter_text>Loading a subset of the large table, **labevents**. This table has over 10 million rows, but the subset we're selecting (BUN tests) is about 500k. Because we have enabled **local storage**, this query takes a bit under 2 minutes to run the first time, and about 7 seconds every time thereafter.<jupyter_code>bun_df = BUNTransform().load_transform()<jupyter_output><empty_output><jupyter_text>Scrolling to the right side of this table reveals that in addition to joining the **labevents** and **d_patients** columns, we have also computed 2 new columns: "age_at_labevent" and "bun".See the code in `chatto_transform/transforms/mimic/bun_transform.py` to get a sense of what computing those extra columns looks like.<jupyter_code>bun_df<jupyter_output><empty_output><jupyter_text>Now we use the `BUNHistTransform` to get a histogram of BUN levels across all lab tests available in the database.<jupyter_code>bun_count_df = BUNHistTransform().transform(bun_df)
bun_count_df.plot(kind='bar')<jupyter_output><empty_output><jupyter_text>We now examine the relationship between `age_at_labevent` and `bun`, and show the results in a another chart.<jupyter_code>age_vs_mean = bun_df.groupby('age_at_labevent')['bun'].mean()
age_vs_mean
age_vs_mean.plot(kind='bar')<jupyter_output><empty_output><jupyter_text>**Loading raw tables**Here we demonstrate how to load the contents of mimic tables without using transforms. To start, we import the `mimic_schema` module. It contains information about all the tables in the mimic database.<jupyter_code>from chatto_transform.lib.mimic.session import load_table, store_csv
from chatto_transform.schema.mimic import mimic_schema<jupyter_output><empty_output><jupyter_text>You can inspect the schemas for each table in the database. In this representation, you can see the types of each column in the `d_patients` table.<jupyter_code>mimic_schema.d_patients_schema<jupyter_output><empty_output><jupyter_text>Now we use `load_table` to load the d_patients table. Because local storage is enabled, all tables loaded from the database are stored locally for later use.<jupyter_code>d_patients = load_table(mimic_schema.d_patients_schema)
d_patients<jupyter_output><empty_output><jupyter_text>Now let's load blood urea nitrogen information. This data is stored in the `labevents` table, with `itemid` equal to 50177.<jupyter_code>labevents_schema = mimic_schema.labevents_schema
labevents_schema<jupyter_output><empty_output><jupyter_text>Rather than load the full `labevents` table, we can load just the subset that we want, by passing a condition string to the call to `load_table`:<jupyter_code>bun_labevents = load_table(labevents_schema, "itemid=50177")<jupyter_output><empty_output><jupyter_text>(A note about condition strings- you can write anything here that would go in the WHERE clause of a SQL query, including using AND and OR to combine multiple conditions.)<jupyter_code>bun_labevents
store_csv('/Users/dan/dev/data/d_patients.csv', mimic_schema.d_patients_schema)
!head /Users/dan/dev/data/d_patients.csv<jupyter_output>subject_id,sex,dob,dod,hospital_expire_flg
1,F,2840-08-10 00:00:00,,N
2,M,2806-06-15 00:00:00,,N
3,M,2606-02-28 00:00:00,2683-05-02 00:00:00,N
4,F,3351-05-30 00:00:00,,N
5,M,2579-04-09 00:00:00,,N
6,F,3323-07-30 00:00:00,,N
7,F,2666-06-22 00:00:00,,N
8,M,2854-01-10 00:00:00,,N
9,M,2943-02-28 00:00:00,2984-12-17 00:00:00,Y
{'fraction_non_alphanumeric': 0.09321238661244917, 'fraction_numerical': 0.03753518923991242, 'mean_word_length': 6.5591016548463354, 'pattern_counts': {'":': 0, '<': 64, '<?xml version=': 0, '>': 65, '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': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '195658', 'license': 'MIT', 'n_tokens_mistral': 2255, 'n_tokens_neox': 2056, 'n_words': 607, 'path': 'notebooks/Chatto-Transform Mimic Demo.ipynb', 'repo_name': 'chatto-hub-test2/A-new-project-', 'size': '6394'}