def get_encounters():
client = BigQueryClient()
result = client.query(
"SELECT PATIENT, START, STOP, ORGANIZATION, ENCOUNTERCLASS FROM Synthea2.Encounters WHERE PATIENT IN ({}) AND DATE_DIFF(CURRENT_DATE, CAST(START as DATE), YEAR) <= 1"
.format(",".join(["'{}'".format(x)
for x in session["activeprofile"]])))
return pd.DataFrame([dict(zip(x.keys(), x.values())) for x in result])
def filter_patients():
genderfilter = [
x.strip for x in request.form.get("genderfilter").strip().split(",")
]
if len(genderfilter) == 1:
genderquery = "{}.{} = '{}'".format(PATIENT, PATIENT_GENDER,
genderfilter[0])
else:
genderquery = ""
positiveconditions = [
x.strip()
for x in request.form.get("positiveconditions").strip().split(",")
]
if len(positiveconditions) > 0:
positiveconditionsquery = "{}.{} IN (SELECT {} FROM {}.{} WHERE {} IN ({}))".format(
PATIENT, PATIENT_ID, CONDITION_PATIENT, DATABASE, CONDITIONS,
CONDITIONS_DESCRIPTION,
",".join(["'{}'".format(x) for x in positiveconditions]))
else:
positiveconditionsquery = ""
client = BigQueryClient()
overallquery = "SELECT {}.{} FROM {}.{}".format(PATIENT, PATIENT_ID,
DATABASE, PATIENT)
if genderquery != "" and overallquery.find("WHERE") > -1:
overallquery += " AND " + genderquery
elif genderquery != "" and overallquery.find("WHERE") == -1:
overallquery += " WHERE " + genderquery
if positiveconditionsquery != "" and overallquery.find("WHERE") > -1:
overallquery += " AND " + positiveconditionsquery
elif positiveconditionsquery != "" and overallquery.find("WHERE") == -1:
overallquery += " WHERE " + positiveconditionsquery
session["activeprofile"] = [row.Id for row in client.query(overallquery)]
return redirect("/population_summary")
def get_medications():
client = BigQueryClient()
result = client.query(
"SELECT PATIENT, START, STOP, DESCRIPTION, ENCOUNTER, REASONDESCRIPTION FROM Synthea2.Medications WHERE PATIENT IN ({})"
.format(",".join(["'{}'".format(x)
for x in session["activeprofile"]])))
return pd.DataFrame([dict(zip(x.keys(), x.values())) for x in result])
def get_conditions():
client = BigQueryClient()
result = client.query(
"SELECT {}, {}, {}, {} FROM {}.{} WHERE {} IN ({})".format(
CONDITION_PATIENT, CONDITION_DESCRIPTION, CONDITION_START,
CONDITION_STOP, DATABASE, CONDITIONS, CONDITION_PATIENT,
",".join(["'{}'".format(x) for x in session["activeprofile"]])))
return pd.DataFrame([dict(zip(x.keys(), x.values())) for x in result])
def get_demographics(profile):
client = BigQueryClient()
result = client.query(
"SELECT {}, {}, DATE_DIFF(CURRENT_DATE, {}, YEAR) as AGE, {} FROM {}.{} WHERE {} IN ({})"
.format(PATIENT_GENDER, PATIENT_RACE, PATIENT_BIRTHDATE,
PATIENT_DEATHDATE, DATABASE, PATIENT, PATIENT_ID,
",".join(["'{}'".format(x) for x in profile])))
return pd.DataFrame([dict(zip(x.keys(), x.values())) for x in result])
def root():
client = BigQueryClient()
gender = [
row[PATIENT_GENDER]
for row in client.query("SELECT DISTINCT {} FROM Synthea2.{}".format(
PATIENT_GENDER, PATIENT))
]
race = [
row[PATIENT_RACE]
for row in client.query("SELECT DISTINCT {} FROM Synthea2.{}".format(
PATIENT_RACE, PATIENT))
]
conditions = [(row[CONDITION_CODE], row[CONDITION_DESCRIPTION]) for row in
client.query("SELECT DISTINCT {},{} from Synthea2.{}".format(
CONDITION_CODE, CONDITION_DESCRIPTION, CONDITIONS))]
practices = [(row[ORGANIZATION_ID], row[ORGANIZATION_NAME]) for row in
client.query("SELECT DISTINCT {},{} from Synthea2.{}".format(
ORGANIZATION_ID, ORGANIZATION_NAME, ORGANIZATIONS))]
patientno = [
row.count for row in client.query(
"SELECT COUNT(DISTINCT {}) as count FROM Synthea2.{}".format(
PATIENT_ID, PATIENT))
][0]
return render_template('index.html',
gender=gender,
race=race,
conditions=conditions,
practices=practices,
patientno=patientno)
def filter_patients():
genderfilter = list(
filter(lambda x: x, [
x.strip()
for x in request.form.get("genderfilter").strip().split(",")
]))
session["genderfilter"] = genderfilter
positiveconditions = list(
filter(lambda x: x, [
x.strip()
for x in request.form.get("positiveconditions").strip().split(",")
]))
session["positiveconditions"] = positiveconditions
negativeconditions = list(
filter(lambda x: x, [
x.strip()
for x in request.form.get("negativeconditions").strip().split(",")
]))
session["negativeconditions"] = negativeconditions
upperagefilter = request.form.get("upperage").strip()
session["upperagefilter"] = upperagefilter
loweragefilter = request.form.get("lowerage").strip()
session["loweragefilter"] = loweragefilter
practicesfilter = list(
filter(lambda x: x, [
x.strip() for x in request.form.get("practices").strip().split(",")
]))
session["practicesfilter"] = practicesfilter
client = BigQueryClient()
temp = [
row.Id for row in client.query(
construct_filter_query(genderfilter, positiveconditions,
negativeconditions, upperagefilter,
loweragefilter, practicesfilter))
]
if len(temp) == 0:
return make_response("Sorry no patients found meeting that criteria",
200)
else:
return redirect("/population_summary")
def root():
client = BigQueryClient()
gender = [
row[PATIENT_GENDER]
for row in client.query("SELECT DISTINCT {} FROM {}.{}".format(
PATIENT_GENDER, DATABASE, PATIENT))
]
race = [
row[PATIENT_RACE]
for row in client.query("SELECT DISTINCT {} FROM {}.{}".format(
PATIENT_RACE, DATABASE, PATIENT))
]
conditions = [
(row[CONDITION_CODE], row[CONDITION_DESCRIPTION])
for row in client.query("SELECT DISTINCT {},{} from {}.{}".format(
CONDITION_CODE, CONDITION_DESCRIPTION, DATABASE, CONDITIONS))
]
practices = [
(row[ORGANIZATION_ID], row[ORGANIZATION_NAME])
for row in client.query("SELECT DISTINCT {},{} from {}.{}".format(
ORGANIZATION_ID, ORGANIZATION_NAME, DATABASE, ORGANIZATIONS))
]
return render_template('index.html',
gender=gender,
race=race,
conditions=conditions,
practices=practices)
def filter_patients():
genderfilter = list(
filter(lambda x: x, [
x.strip
for x in request.form.get("genderfilter").strip().split(",")
]))
if len(genderfilter) == 1:
genderquery = "{}.{} = '{}'".format(PATIENT, PATIENT_GENDER,
genderfilter[0])
else:
genderquery = ""
positiveconditions = list(
filter(lambda x: x, [
x.strip()
for x in request.form.get("positiveconditions").strip().split(",")
]))
if len(positiveconditions) > 0:
positiveconditionsquery = "{}.{} IN (SELECT {} FROM {}.{} WHERE {} IN ({}))".format(
PATIENT, PATIENT_ID, CONDITION_PATIENT, DATABASE, CONDITIONS,
CONDITIONS_DESCRIPTION,
",".join(["'{}'".format(x) for x in positiveconditions]))
else:
positiveconditionsquery = ""
negativeconditions = list(
filter(lambda x: x, [
x.strip()
for x in request.form.get("negativeconditions").strip().split(",")
]))
if len(negativeconditions) > 0:
negativeconditionsquery = "{}.{} NOT IN (SELECT {} FROM {}.{} WHERE {} IN ({}))".format(
PATIENT, PATIENT_ID, CONDITION_PATIENT, DATABASE, CONDITIONS,
CONDITIONS_DESCRIPTION,
",".join(["'{}'".format(x) for x in positiveconditions]))
else:
negativeconditionsquery = ""
upperagefilter = request.form.get("upperage").strip()
if upperagefilter != "":
upperagequery = "DATE_DIFF(CURRENT_DATE,{}.{},YEAR) <= {}".format(
PATIENT, PATIENT_BIRTHDATE, upperagefilter)
else:
upperagequery = ""
loweragefilter = request.form.get("lowerage").strip()
if loweragefilter != "":
loweragequery = "DATE_DIFF(CURRENT_DATE,{}.{},YEAR) >= {}".format(
PATIENT, PATIENT_BIRTHDATE, loweragefilter)
else:
loweragequery = ""
client = BigQueryClient()
overallquery = "SELECT {}.{} FROM {}.{} WHERE {} IS NULL".format(
PATIENT, PATIENT_ID, DATABASE, PATIENT, PATIENT_DEATHDATE)
if genderquery != "" and overallquery.find("WHERE") > -1:
overallquery += " AND " + genderquery
elif genderquery != "" and overallquery.find("WHERE") == -1:
overallquery += " WHERE " + genderquery
if positiveconditionsquery != "" and overallquery.find("WHERE") > -1:
overallquery += " AND " + positiveconditionsquery
elif positiveconditionsquery != "" and overallquery.find("WHERE") == -1:
overallquery += " WHERE " + positiveconditionsquery
if negativeconditionsquery != "" and overallquery.find("WHERE") > -1:
overallquery += " AND " + negativeconditionsquery
elif negativeconditionsquery != "" and overallquery.find("WHERE") == -1:
overallquery += " WHERE " + negativeconditionsquery
if loweragequery != "" and overallquery.find("WHERE") > -1:
overallquery += " AND " + loweragequery
elif loweragequery != "" and overallquery.find("WHERE") == -1:
overallquery += " WHERE " + loweragequery
if upperagequery != "" and overallquery.find("WHERE") > -1:
overallquery += " AND " + upperagequery
elif upperagequery != "" and overallquery.find("WHERE") == -1:
overallquery += " WHERE " + upperagequery
temp = [row.Id for row in client.query(overallquery)]
if len(temp) == 0:
return make_response("Sorry no patients found meeting that criteria",
200)
else:
f = open("activeprofile.txt", "w")
f.write(",".join(temp))
f.close()
return redirect("/population_summary")
def population_summary():
client = BigQueryClient()
data = [
row.Id for row in client.query(
construct_filter_query(
session["genderfilter"], session["positiveconditions"],
session["negativeconditions"], session["upperagefilter"],
session["loweragefilter"], session["practicesfilter"]))
]
conditions = get_conditions(data)
condition_counts = conditions["DESCRIPTION"].value_counts().values
condition_count_labels = conditions["DESCRIPTION"].value_counts().index
condition_counts = list(condition_counts[condition_count_labels != ""])
active_condition_counts = conditions.loc[
conditions["STOP"].isnull()]["DESCRIPTION"].value_counts().values
active_condition_count_labels = conditions.loc[
conditions["STOP"].isnull()]["DESCRIPTION"].value_counts().index
active_condition_counts = list(
active_condition_counts[active_condition_count_labels != ""])
unresolved = conditions.loc[conditions["STOP"].isnull()]
unresolved = unresolved.loc[~conditions["DESCRIPTION"].isnull()]
conditions_per_patient = list(
unresolved["PATIENT"].value_counts().value_counts().values)
conditions_per_patient_labels = unresolved["PATIENT"].value_counts(
).value_counts().index.tolist()
#co_occurence = np.zeros((len(active_condition_count_labels),len(active_condition_count_labels)))
#for i in range(len(active_condition_count_labels)):
# unresolved["DESCRIPTION"] == active_condition_count_labels[i]
demographics = get_demographics()
gender_ratio = list(demographics["GENDER"].value_counts().values)
gender_ratio_labels = demographics["GENDER"].value_counts().index.tolist()
ages = list(demographics["AGE"].values)
races = list(demographics["RACE"].value_counts().values)
races_labels = demographics["RACE"].value_counts().index.tolist()
medications = get_medications()
active_medications_counts = medications.loc[
medications["STOP"].isnull()]["DESCRIPTION"].value_counts().values
active_medications_count_labels = medications.loc[
medications["STOP"].isnull()]["DESCRIPTION"].value_counts().index
active_medications_counts = list(
active_medications_counts[active_medications_count_labels != ""])
unresolved = medications.loc[medications["STOP"].isnull()]
unresolved = unresolved.loc[~medications["DESCRIPTION"].isnull()]
medications_per_patient = list(
unresolved["PATIENT"].value_counts().value_counts().values)
medications_per_patient_labels = unresolved["PATIENT"].value_counts(
).value_counts().index.tolist()
encounters = get_encounters()
overallencounters = [
datetime.fromtimestamp(int(x) //
1000000000).strftime('%Y-%m-%d %H:%M:%S')
for x in list(encounters["START"].values)
]
emergency = [
datetime.fromtimestamp(int(x) //
1000000000).strftime('%Y-%m-%d %H:%M:%S')
for x in list(encounters.loc[encounters["ENCOUNTERCLASS"] ==
"emergency"]["START"].values)
]
outpatient = [
datetime.fromtimestamp(int(x) //
1000000000).strftime('%Y-%m-%d %H:%M:%S')
for x in list(encounters.loc[encounters["ENCOUNTERCLASS"] ==
"outpatient"]["START"].values)
]
ambulatory = [
datetime.fromtimestamp(int(x) //
1000000000).strftime('%Y-%m-%d %H:%M:%S')
for x in list(encounters.loc[encounters["ENCOUNTERCLASS"] ==
"ambulatory"]["START"].values)
]
encounters_per_patient = list(
encounters["PATIENT"].value_counts().value_counts().values)
encounters_per_patient_label = encounters_per_patient = encounters[
"PATIENT"].value_counts().value_counts().index.tolist()
return render_template(
"summarypage.html",
population_size=len(session["activeprofile"]),
condition_counts=condition_counts,
condition_labels=condition_count_labels.tolist(),
active_condition_counts=active_condition_counts,
active_condition_count_labels=active_condition_count_labels.tolist(),
conditions_per_patient=conditions_per_patient,
conditions_per_patient_labels=conditions_per_patient_labels,
ages=ages,
gender_ratio=gender_ratio,
gender_ratio_labels=gender_ratio_labels,
races=races,
races_labels=races_labels,
active_medications_counts=active_medications_counts,
active_medications_count_labels=active_medications_count_labels.tolist(
),
medications_per_patient=medications_per_patient,
medications_per_patient_labels=medications_per_patient_labels,
overallencounters=overallencounters,
emergency=emergency,
outpatient=outpatient,
ambulatory=ambulatory,
encounters_per_patient=encounters_per_patient,
encounters_per_patient_label=encounters_per_patient_label)
