def aggregate_output(run_id, groups=350, agg_to_national=True, cause_type='icg', cf_prep=True): """ MS is processed by enrolid now, which results in very large output files This function waits until all jobs are finished then aggregates them all together. Params: groups: (int) the number of groups that were used to process the MS claims data. This is dependent on the ms db helpers module, so do not change it unless you're certain agg_to_national (bool) if True, re-assign all location IDs to the national level in order to save space """ job_holder() print("All jobs have finished, aggregating data to the national level and writing the condensed CSV and H5 files.") files = glob.glob("FILEPATH".format(run_id, cause_type)) if len(files) != groups: warnings.warn("The number of files we read back in doesn't match the number of jobs sent out") assert False, "Look to the logs for failed jobs" # read in 1 file to append all groups to df = pd.read_hdf(files[0]) df = hosp_prep.age_binning(df, terminal_age_in_data=False, drop_age=True) if agg_to_national: df['location_id'] = 102 # case sum to check aggregation pre = df[(df['estimate_type'] == 'inp_any_indv_cases')].val.sum() # make first otp_cf_df outside of loop if cf_prep: otp_cf_df = outpatient_aggregations(df) counter = 1 for f in files[1:]: print("starting {}".format(f)) tmp = pd.read_hdf(f) if len(tmp) == 0: continue tmp = hosp_prep.age_binning(tmp, terminal_age_in_data=False, drop_age=True) if agg_to_national: tmp['location_id'] = 102 if cf_prep: otp_tmp = outpatient_aggregations(tmp) otp_cf_df = pd.concat([otp_cf_df, otp_tmp], ignore_index=True, sort=False) pre += tmp[(tmp['estimate_type'] == 'inp_any_indv_cases')].val.sum() df = pd.concat([df, tmp], ignore_index=True, sort=False) # we can drop inp_otp claims cases cause they're unused df = df[df['estimate_type'] != 'inp_otp_any_adjusted_otp_only_claims_cases'] df = df.fillna(0) if counter % 25 == 0 or counter == groups: df = df.groupby(df.columns.drop('val').tolist()).agg({'val': 'sum'}).reset_index() if cf_prep: otp_cf_df = otp_cf_df.groupby(otp_cf_df.drop(['val'], axis=1).columns.tolist()).agg({'val': 'sum'}).reset_index() print("There are {} rows in df object".format(df.shape[0])) print("{}% done".format(round(counter/float(groups), 3) * 100)) counter += 1 if cf_prep: # one last groupby otp_cf_df = otp_cf_df.groupby(otp_cf_df.drop(['val'], axis=1).columns.tolist()).agg({'val': 'sum'}).reset_index() # save otp_cf_base_dir = "FILEPATH".format(run_id) otp_cf_filepath = "{}/bundle_condensed_otp_repreped_claims_process.csv".format(otp_cf_base_dir) otp_cf_df.to_csv(otp_cf_filepath, index=False) # a final groupby for good measure, I think the counter was perhaps not hitting the groups value above so the last # groupby didn't occur creating a bit of a larger data set than necessary df = df.groupby(df.columns.drop('val').tolist()).agg({'val': 'sum'}).reset_index() df_pri = df[(df['estimate_type'] == 'inp_any_indv_cases')].val.sum() print("pre equals {} and df.val sum equals {} It's {} they're equal!".format(pre, df_pri, pre == df_pri)) assert df_pri == pre, "we expect primary inpatient indv cases to be equal and they're not" # write to a csv for use with R scripts filepath = "FILEPATH".format(run_id, cause_type) filepath = filepath.replace("\r", "") df.to_csv(filepath, index=False) # Saving the file to H5 as well and back it up write_path = "FILEPATH".format(run_id, cause_type) write_path = write_path.replace("\r", "") hosp_prep.write_hosp_file(df, write_path, backup=False) if cause_type == 'icg': prep_for_upload(df=df, run_id=run_id) return
str_cols = ['source', 'facility_id', 'outcome_id'] if df[str_cols].isnull().any().any(): warnings.warn("\n\n There are NaNs in the column(s) {}".format( df[str_cols].columns[df[str_cols].isnull().any()]) + "\n These NaNs will be converted to the string 'nan' \n") for col in int_cols: df[col] = pd.to_numeric(df[col], errors='raise', downcast='integer') for col in str_cols: df[col] = df[col].astype(str) df.loc[df['age'] > 95, 'age'] = 95 df = hosp_prep.age_binning(df) df.loc[(df['sex_id'] != 1) & (df['sex_id'] != 2), 'sex_id'] = 3 diagnosis_feats = df.columns[df.columns.str.startswith('dx_')] for feat in diagnosis_feats: df[feat] = hosp_prep.sanitize_diagnoses(df[feat]) if len(diagnosis_feats) > 1: df = hosp_prep.stack_merger(df) elif len(diagnosis_feats) == 1: df.rename(columns={'dx_1': 'cause_code'}, inplace=True) df['diagnosis_id'] = 1
def prep_2014(): """ Funtion to read in and prepare data from GEO for the years 2012-2013. Returns: Pandas DataFrame with data for the years 2012-2013-2011 in a format consistent with the other years of data. """ df = pd.read_excel(r"FILEPATH/" r"GEO_HOSPITAL_DISCHARGE_DATA_2014_Y2015M06D29.XLSX") # Select features from raw data to keep keep = [ 'Sex', 'Age (years)', 'Main Diagnosis (ICD10)', 'External causes (ICD10)', 'Disharge status', # typo: Discharge 'Complication (ICD 10)', 'Comorbidity (ICD 10)', 'Beddays' ] df = df[keep].copy() # we need to provide 2014 wide on diagnoses for EN matrix, but long on # diagnoses for the final product. And we don't want to format twice, # so we're going to make it so all years can be stuck together, save 2014 # wide, then make the whole thing long. # rename the diagnosis columns df = df.rename( columns={ 'Main Diagnosis (ICD10)': 'dx_1', 'External causes (ICD10)': 'dx_2', 'Complication (ICD 10)': 'dx_3', 'Comorbidity (ICD 10)': 'dx_4' }) # make outcome id # this is from the codebook on the second sheet of the file outcome_dict = {1: 'discharge', 2: 'discharge', 3: 'discharge', 4: 'death'} df['outcome_id'] = df["Disharge status"].map(outcome_dict) # now can drop "Disharge status" df.drop("Disharge status", axis=1, inplace=True) # Need to get rid of bed days. before that, need to use bed days # can't have bed days of 0, unless they died. df = df[(df.outcome_id != "discharge") | (df.Beddays >= 1)] # stuff to keep # now can drop Beddays df = df.drop("Beddays", axis=1) # rename and make columns df = df.rename(columns={"Sex": "sex_id", "Age (years)": "age"}) df['year_start'] = 2014 df['year_end'] = 2014 # make a val column. every row is an admission df["val"] = 1 df['age'] = pd.to_numeric(df['age'], errors="coerce") # bin the ages df.loc[df.age > 99, 'age'] = 99 # 100 and up were not binning df = hosp_prep.age_binning(df) # don't need age after binning. df = df.drop("age", axis=1) # take care of some nulls df.loc[df.sex_id.isnull(), "sex_id"] = 3 df = df[df.dx_1.notnull()] df = df[df.outcome_id.notnull()] # just 4 rows return df
def apply_restrictions(df, age_set, cause_type, map_version='current', prod=True): """ Apply age and sex restrictions by ICG or bundle to a dataframe of clinical data Params: df: (pd.DataFrame) clinical data age_set: (str) is the data in indv year ages, binned age groups with start/end or age_group_ids acceptable params are "indv", "binned", "age_group_id" cause_type: (str) do we want icg restricts or bundle restricts Returns: df: (pd.DataFrame) with rows that fall outside of age-sex restrictions dropped """ warnings.warn("apply_restrictions needs a testing suite!!") sex_diff = set(df.sex_id.unique()).symmetric_difference([1, 2]) if sex_diff: warnings.warn( f"There are sex_id values that won't have restrictions applied to them. These are {sex_diff}" ) assert age_set in ['indv', 'binned', 'age_group_id' ], "{} is not an acceptable age set".format(age_set) check_map_version(map_version) start_cols = df.columns if age_set == "age_group_id": import gbd_hosp_prep df = gbd_hosp_prep.all_group_id_start_end_switcher(df) elif age_set == 'indv': df = hosp_prep.age_binning(df, drop_age=False, terminal_age_in_data=False) df['to_keep'] = 1 if cause_type == 'icg': restrict = get_clinical_process_data('age_sex_restrictions', map_version, prod=prod) elif cause_type == 'bundle': restrict = create_bundle_restrictions(map_version) else: assert False, "pick an acceptable restriction type" assert set(restrict.loc[restrict['yld_age_start'] < 1, 'yld_age_start'].unique()) == {0} keep_cols = [ cause_type + '_id', 'male', 'female', 'yld_age_start', 'yld_age_end' ] pre = df.shape[0] df = df.merge(restrict[keep_cols], how='left', on=cause_type + '_id') assert pre == df.shape[0], ("merge made more rows, there's something wrong" " in the restrictions file") df.loc[(df['male'] == 0) & (df['sex_id'] == 1), 'to_keep'] = np.nan df.loc[(df['female'] == 0) & (df['sex_id'] == 2), 'to_keep'] = np.nan df.loc[df['age_end'] <= df['yld_age_start'], 'to_keep'] = np.nan df.loc[df['age_start'] > df['yld_age_end'], 'to_keep'] = np.nan df = df[df['to_keep'].notnull()] df.drop(['male', 'female', 'yld_age_start', 'yld_age_end', 'to_keep'], axis=1, inplace=True) if age_set == "age_group_id": df = gbd_hosp_prep.all_group_id_start_end_switcher(df) elif age_set == "indv": df.drop(['age_start', 'age_end'], axis=1, inplace=True) diff_cols = set(start_cols).symmetric_difference(set(df.columns)) assert not diff_cols, "The diff columns are {}".format(diff_cols) return df
2012: 121282, 2013: 150449, 2014: 220205, 2015: 281773 } df = hosp_prep.fill_nid(df, nid_dict) df.los.value_counts(dropna=False).head() df.los.isnull().sum() df = df[df['los'] > 0] final_admits = len(df) df['facility_id'] = 'hospital' df = hosp_prep.age_binning(df, drop_age=True) int_cols = [ 'location_id', 'year_start', 'year_end', 'age_group_unit', 'age_start', 'age_end', 'sex_id', 'nid', 'representative_id', 'metric_id' ] str_cols = ['source', 'facility_id', 'outcome_id'] for col in int_cols: df[col] = pd.to_numeric(df[col], errors='raise', downcast='integer') for col in str_cols: df[col] = df[col].astype(str) df.loc[~df['sex_id'].isin([1, 2]), 'sex_id'] = 3 diagnosis_feats = df.columns[df.columns.str.startswith('dx_')]
def prep_2014(): """ Funtion to read in and prepare data from GEO for the years 2012-2013. Returns: Pandas DataFrame with data for the years 2012-2013-2011 in a format consistent with the other years of data. """ df = pd.read_excel(root + r"FILENAME" r"FILEPATH") keep = ['Sex', 'Age (years)', 'Main Diagnosis (ICD10)', 'External causes (ICD10)', 'Disharge status', 'Complication (ICD 10)', 'Comorbidity (ICD 10)', 'Beddays'] df = df[keep].copy() df = df.rename(columns={'Main Diagnosis (ICD10)': 'dx_1', 'External causes (ICD10)': 'dx_2', 'Complication (ICD 10)': 'dx_3', 'Comorbidity (ICD 10)': 'dx_4'}) outcome_dict = {1: 'discharge', 2: 'discharge', 3: 'discharge', 4: 'death'} df['outcome_id'] = df["Disharge status"].map(outcome_dict) df.drop("Disharge status", axis=1, inplace=True) df = df[(df.outcome_id != "discharge")|(df.Beddays >= 1)] df = df.drop("Beddays", axis=1) df = df.rename(columns={"Sex": "sex_id", "Age (years)": "age"}) df['year_start'] = 2014 df['year_end'] = 2014 df["val"] = 1 df['age'] = pd.to_numeric(df['age'], errors="coerce") df.loc[df.age > 99, 'age'] = 99 df = hosp_prep.age_binning(df) df = df.drop("age", axis=1) df.loc[df.sex_id.isnull(), "sex_id"] = 3 df = df[df.dx_1.notnull()] df = df[df.outcome_id.notnull()] return df