def _getClinicalItems(self):
query = SQLQuery()
# SELECT
query.addSelect(CLINICAL_ITEM_ID)
query.addSelect('name')
# FROM
query.addFrom('clinical_item')
print query
print query.getParams()
DBUtil.runDBScript(self.SCRIPT_FILE, False)
results = DBUtil.execute(str(query), query.getParams())
pd.DataFrame(results, columns=query.select).to_csv('clinical_items.csv', index=False)
def _getNonNullBaseNames(self):
query = SQLQuery()
# SELECT
query.addSelect(BASE_NAME)
query.addSelect('max_result_flag')
# FROM
query.addFrom('order_result_stat')
# WHERE
query.addWhere('max_result_flag is not null')
print query
print query.getParams()
DBUtil.runDBScript(self.SCRIPT_FILE, False)
results = DBUtil.execute(str(query), query.getParams())
pd.DataFrame(results, columns=query.select).to_csv('base_names.csv', index=False)
def _getClinicalItemCounts(self):
query = SQLQuery()
# SELECT
query.addSelect(CLINICAL_ITEM_ID)
query.addSelect('COUNT(' + CLINICAL_ITEM_ID + ') as total')
# FROM
query.addFrom('patient_item')
# OTHER
query.addGroupBy(CLINICAL_ITEM_ID)
query.addOrderBy('total', dir='desc')
print(query)
print(query.getParams())
DBUtil.runDBScript(self.SCRIPT_FILE, False)
results = DBUtil.execute(str(query), query.getParams())
pd.DataFrame(results, columns=[CLINICAL_ITEM_ID, 'count']).to_csv('data_summary_stats/item_counts.csv', index=False)
def _getLabs(self):
query = SQLQuery()
# SELECT
query.addSelect(CLINICAL_ITEM_ID)
query.addSelect('name')
query.addSelect('description')
# FROM
query.addFrom('clinical_item')
# WHERE
query.addWhereLike('name','LAB%')
# OTHER
query.addOrderBy(CLINICAL_ITEM_ID, dir='asc')
print(query)
print(query.getParams())
DBUtil.runDBScript(self.SCRIPT_FILE, False)
results = DBUtil.execute(str(query), query.getParams())
pd.DataFrame(results, columns=query.select).to_csv('data_summary_stats/labs.csv', index=False)
def grade_cases(self, sim_patient_ids, sim_grader_id, conn=None):
"""Given the identifiers for a bunch of simulated physician-patient case
records, and the identifier for a particular grading key to use,
calculate what grade each case would get based on the choices made
and return a dictionary of case grades (keyed by the case ID).
"""
ext_conn = True
if conn is None:
conn = self.connFactory.connection()
ext_conn = False
try:
# Inner query retrieves physician-patient cases with ranking group_names (to later select first)
# per case for specified cases. Each NULL group_name is treated as a separate group by assigning it
# sim_patient_order_id. It also omits Default user (sim_user_id = 0) from grading.
inner_query = SQLQuery()
inner_query.addSelect("score")
inner_query.addSelect(
"rank() over (" # ranks rows incrementally in the same group
" partition by coalesce(group_name, sim_patient_order_id::text), sim_patient_id"
" order by sim_patient_order_id"
")")
inner_query.addSelect("sim_user_id")
inner_query.addSelect("sim_patient_id")
inner_query.addSelect("sim_grader_id")
inner_query.addFrom("sim_patient_order spo")
inner_query.addJoin(
"sim_grading_key sgk",
"sgk.clinical_item_id = spo.clinical_item_id"
" and sgk.sim_state_id = spo.sim_state_id")
inner_query.addWhereEqual("sgk.sim_grader_id", sim_grader_id)
inner_query.addWhereNotEqual(
"spo.sim_user_id",
0) # 0 = ignore 'Default user', sets up initial cases
inner_query.addWhereIn("spo.sim_patient_id", sim_patient_ids)
inner_query.addOrderBy("relative_time_start")
inner_query.addOrderBy("sim_patient_order_id")
# Outer query sums the score per patient case and selects most graded physician for the case.
# Theoretically, it isn't necessarily the most active physician for the case since his orders
# might have been dropped by selecting only the first record within group_name group.
query = SQLQuery()
query.addSelect("sim_patient_id")
query.addSelect("sim_grader_id")
query.addSelect("sum(score) as total_score")
query.addSelect(
"mode() within group (" # mode() selects most frequent value within group
" order by sim_user_id"
") as most_graded_user_id")
query.addFrom("(" + str(inner_query) + ") as ranked_groups")
query.addWhereEqual("ranked_groups.rank",
1) # count only first order in the same group
query.addGroupBy("sim_patient_id")
query.addGroupBy("sim_grader_id")
query_params = inner_query.getParams() + query.getParams()
grades_table = DBUtil.execute(query,
query_params,
includeColumnNames=True,
conn=conn)
grades_model = modelListFromTable(grades_table)
# get most active users for the cases
most_active_user_query = SQLQuery()
most_active_user_query.addSelect("sim_patient_id")
most_active_user_query.addSelect("mode() within group ("
" order by sim_user_id"
") as most_active_user_id")
most_active_user_query.addFrom("sim_patient_order")
most_active_user_query.addWhereNotEqual("sim_user_id",
0) # ignore Default user
most_active_user_query.addWhereIn("sim_patient_id",
sim_patient_ids)
most_active_user_query.addGroupBy("sim_patient_id")
most_active_user_query.addOrderBy("sim_patient_id")
most_active_user_table = DBUtil.execute(most_active_user_query,
includeColumnNames=True,
conn=conn)
most_active_user_model = modelListFromTable(most_active_user_table)
# make a dict by sim_patient_id out of results - will be used for combining
most_active_user_dict = {
most_active_user["sim_patient_id"]: most_active_user
for most_active_user in most_active_user_model
}
# combine results
complete_grades = [
grade.update(most_active_user_dict[grade["sim_patient_id"]])
for grade in grades_model
]
return complete_grades
finally:
if not ext_conn:
conn.close()
