NHSDigital · NatashaChetwynd · Feb 29, 2024 · Feb 29, 2024
diff --git a/CHANGELOG.txt b/CHANGELOG.txt
@@ -0,0 +1,40 @@
+LDHC Regional excel report discovery update.
+
+Changelog v1.2
+------------------------------------------------------------------
+On title page, the date on row 9 is bold
+Changed notes on title page to introduction and changed introduction
+Lead analyst row 30 on title page is now bold
+On the Sub ICB, ICB and Region tabs, the MMM YYYY Publication text from row 3 is removed
+On the sub ICB and ICB pages, the table lines now stop after the data stops.
+
+
+Changelog V1.1
+------------------------------------------------------------------
+Automated CSV and Zip processes. Code now zips and allocates both CSV and Zip files to right location.
+Automated Year folder creation. Detects year and creates folder if necessary.
+Changed trend monitor path to root/Output/Opt Out Tracking.
+Automated Archiving of trend monitor.
+Cleaned up NCDes ethinicity report. Changed font, fixed formatting errors.
+Fixed date error in NCdes ethnicty report.
+Updated code so it gets practice details from SQL database.
+Updated code to find practices not in our SQL database, and tag them as "Unknown" in report.
+Fixed Data mismatch in ethinicity report.
+
+
+Changelog V1.0
+------------------------------------------------------------------
+Updated two file:
+create_publication.py - added lines of code to run excel job and print statement saying its running
+output/outputs.py - added function to run excel job
+
+Added four files:
+Changelog.txt - to report changes made
+output/fonts.py - contains various fonts for excel
+output/outputcsvtoinputexcel.py - converts the NCDes output dataframe to a dataframe for the excel job
+output/outputexcel.py - prints excel file out
+
+Added three files in root_directory:
+templates/template_AHC_practice_YYYY_MMM.xlsx : A template file to write over
+templates/CONTROL_FILE_NCDes : Contains information for title page
+templates/practice_mapping : A csv which contains Sub_ICB, ICB and region mapping for each practice.
diff --git a/README.md b/README.md
@@ -1,4 +1,4 @@
-> Warning: This is the README for the publically accessible version of the NCDes package. If you are an analyst please don't use the below instructions to run the publication process.
+> #Warning: This branch contains retired code which was used for the NCDES data source emailed to the PCD team each month. This pipeline is no longer in use as the data source has now been replaced with directly sourcing data from the CQRS self-serve tool. The 'main' branch now uses the CQRS self-serve data source. This branch should only be used if data is emailed to us and should be treated with caution as it may not be up to date if subsequent updates have been pushed to main.
 
 <p>&nbsp;</p>
 
@@ -46,7 +46,7 @@ root
 |   |---Current
 |   |   |---ncdes_synthetic_data.csv
 |   |---Archive
-|   |---Data dictionary yy_yy
+|   |---Data_dictionary_current
 |       |---indicator dictionary.csv
 |       |---measure dictionary.csv
 |
@@ -56,7 +56,7 @@ root
 |   |   |---Missing Measures History.csv
 |   |   |---Unexpected Indicators History.csv
 |   |   |---Unexpected Measures History.csv
-|   |---NCD_yy_yy
+|   |
 |   |---Opt Out Tracking
 |   |   |---Archive
 |   |   |   |---NCD_Opt_Out_Tracking_month.xlsx
@@ -74,21 +74,19 @@ root
 ## Instructions for publication production
 After the above set up steps have been completed you can follow the below instructions to create the publication. Please note that you will not be able to run the code as this requires access to a private server. The data on the private server contains reference data that is used for mapping purposes. The reference tables used contain data from the [epraccur file](https://digital.nhs.uk/services/organisation-data-service/file-downloads/gp-and-gp-practice-related-data) and the [ONS code history database](https://www.ons.gov.uk/methodology/geography/geographicalproducts/namescodesandlookups/codehistorydatabasechd)
 
-1) Move the 'config.json' from the 'public_meta_data' folder into the package at the same level as this 'README'.
+1) In the config file edit the root directory value so that it matches the root of the directory that you set up. Make use of escape characters and end path with a double "\\\\" e.g. "\\\\\\\example\\\root\\\directory\\\\".
 
-2) In the config file edit the root directory value so that it matches the root of the directory that you set up. Make use of escape characters and end path with a double "\\\\" e.g. "\\\\\\\example\\\root\\\directory\\\\".
-
-3) Download the epcn excel file from this [webpage](https://digital.nhs.uk/services/organisation-data-service/file-downloads/gp-and-gp-practice-related-data). Move it to the location specified in the above diagram. Copy the absolute path of this file and use it as the "epcn_path" in the config.json.
+2) Download the epcn excel file from this [webpage](https://digital.nhs.uk/services/organisation-data-service/file-downloads/gp-and-gp-practice-related-data). Move it to the location specified in the above diagram. Copy the absolute path of this file and use it as the "epcn_path" in the config.json.
 
 3) Move the 'ncdes_synthetic_data.csv' from the 'public_meta_data' folder into your '{root_directory}\Input\Current' folder.
 
-4) Next you will need to move the indicator dictionary.csv and measure dictionary.csv into the Data dictionary yy_yy folder. To ensure you have the most up to date files download the latest data dictionary, this can be found on the relevant publication page. As an example the 22/23 service's data dictionary can be found [Here](https://files.digital.nhs.uk/1A/E5649B/NCDes_Data_Dictionary_22_23_v2.0.xlsx). You will then need to split the indicator and measure sheets into two individual csv files and name them 'indicator dictionary.csv' and 'measure dictionary.csv' respectively. An example of these files is given in the public_meta_data folder. Remember to move these files to the 'Data dictionary yy_yy' folder in your file tree. 
+4) Next you will need to move the indicator dictionary.csv and measure dictionary.csv into the Data_dictionary_current folder. To ensure you have the most up to date files download the latest data dictionary, this can be found on the relevant publication page. As an example the 22/23 service's data dictionary can be found [Here](https://files.digital.nhs.uk/1A/E5649B/NCDes_Data_Dictionary_22_23_v2.0.xlsx). You will then need to split the indicator and measure sheets into two individual csv files and name them 'indicator dictionary.csv' and 'measure dictionary.csv' respectively. An example of these files is given in the public_meta_data folder.
 
 5) Run the 'create_publication.py' file by typing the below command into your terminal
     ```
     python -m ncdes.create_publication
     ```
-The output of the job can then be found in the '{root_directory}\Output\NCD_yy_yy' folder.  
+The output of the job can then be found in the '{root_directory}\Output\YY_YY' folder.  
 
 > WARNING: Please note that python uses the '\\' character as an escape character. To ensure your inserted paths work insert an additional '\\' each time it appears in your defined path. E.g.  'C:\Python25\Test scripts' becomes 'C:\\\Python25\\\Test scripts'  
 

diff --git a/ncdes/create_publication.py b/ncdes/create_publication.py
@@ -11,7 +11,10 @@
 from .output import outputs
 
 from .utils.adhoc_fix import remove_problem_indicators, remove_problem_measures, remove_problem_indicator_measure_pairs
+import warnings
 
+warnings.simplefilter(action="ignore", category=UserWarning)
+pd.options.mode.chained_assignment = None
 
 CORRECT_COLUMN_ORDER_NCDes_with_geogs = [
     "PRACTICE_CODE",
@@ -28,12 +31,12 @@ def main() -> None:
     print("\n"*3,"Loading config file")
     config = data_load.load_json_config_file(".\\config.json")
 
-    print("Establish SQL connection")
+    print("Establishing SQL connection")
     connection = sql_connection.connect(server=config["server"], database=config["database"])
 
     root_directory = config["root_directory"]
 
-    print("Loading in NCDes data")
+    print("Loading NCDes data")
     ncdes_raw = data_load.load_csvs_in_directory_as_concat_dataframe(f"{root_directory}\\Input\\Current")
 
     print("Cleaning NCDes data")
@@ -42,7 +45,7 @@ def main() -> None:
 
     geo_ccg_sql_str, geo_reg_sql_str, stp_sql_str, prac_sql_str = data_load.get_sql_query_strings(reporting_period)
 
-    print("Loading in SQL mapping data")
+    print("Loading SQL mapping data")
     geo_ccg_df = pd.read_sql(sql=geo_ccg_sql_str, con=connection)
     geo_reg_df = pd.read_sql(sql=geo_reg_sql_str, con=connection)
     stp_df = pd.read_sql(sql=stp_sql_str, con=connection)
@@ -51,14 +54,14 @@ def main() -> None:
     print("Formatting SQL mapping data")
     geo_ccg_df, geo_reg_df, stp_df = processing_steps.sql_df_cols_to_upper_case(geo_ccg_df, geo_reg_df, stp_df)
 
-    print("Loading in epcn data")
+    print("Loading ePCN data")
     raw_epcn = data_load.load_epcn_excel_table(epcn_path=config["epcn_path"])
     epcn_df = processing_steps.epcn_transform(raw_epcn)
 
-    print("Creating super mapping table")
+    print("Creating mapping table")
     mapping_table = processing_steps.create_mapping_table(geo_ccg_df, geo_reg_df, stp_df, prac_df, epcn_df)
 
-    print("Merging NCDes data with supermapping data")
+    print("Merging NCDes data with mapping data")
     NCDes_with_geogs = processing_steps.merge_tables_fill_Na_reorder_cols(mapping_df=mapping_table, ncdes_df_cleaned=ncdes_clean, CORRECT_COLUMN_ORDER_NCDes_with_geogs=CORRECT_COLUMN_ORDER_NCDes_with_geogs)
 
     print("Starting validation checks")
@@ -86,7 +89,8 @@ def main() -> None:
     NCDes_problem_meas_rem = remove_problem_measures.remove(NCDes_problem_ind_rem, ["Num Patients in Set"])
     print("Removing problem indicator measure combos")
     NCDes_problem_meas_rem = remove_problem_indicator_measure_pairs.remove(NCDes_problem_ind_rem, [("NCDMI198", "Numerator")])
-
+    NCDes_problem_meas_rem = remove_problem_indicator_measure_pairs.remove(NCDes_problem_ind_rem, [("NCD012", "Denominator")])
+
     print("Joining ruleset ID to copy of output data for ruleset-specific outputs")
     NCDes_with_rulesets = processing_steps.merge_data_with_ruleset_id(NCDes_problem_meas_rem, root_directory)
 
@@ -118,4 +122,4 @@ def main() -> None:
 
 
 if __name__ == "__main__":
-    main()
+    main()
diff --git a/ncdes/data/data_load.py b/ncdes/data/data_load.py
@@ -17,7 +17,7 @@ def load_csvs_in_directory_as_concat_dataframe(directory):
     """
     phase_file_names = os.listdir(directory)
 
-    print(f"loading in files {phase_file_names}")
+    print(f"Loading in files {phase_file_names}")
 
     holder = []
     for phase in phase_file_names:
@@ -26,7 +26,7 @@ def load_csvs_in_directory_as_concat_dataframe(directory):
 
     output_df = pd.concat(holder, ignore_index=True)
 
-    print("data loaded")
+    print("Data loaded")
 
     return output_df
 
@@ -99,8 +99,7 @@ def load_indicator_and_measure_data_dictionaries(root):
         Measure and Indicator dictionary in pandas dataframe
     """
 
-    indicator_dictionary = pd.read_csv(f"{root}Input\\Data dictionary 23_24\\indicator dictionary.csv")
-    measure_dictionary = pd.read_csv(f"{root}Input\\Data dictionary 23_24\\measure dictionary.csv")
-
+    indicator_dictionary = pd.read_csv(f"{root}Input\\Data_dictionary_current\\indicator dictionary.csv")
+    measure_dictionary = pd.read_csv(f"{root}Input\\Data_dictionary_current\\measure dictionary.csv")
     return indicator_dictionary, measure_dictionary
 
diff --git a/ncdes/output/outputcsvtoinputexcel.py b/ncdes/output/outputcsvtoinputexcel.py
@@ -148,8 +148,8 @@ def add_summary(df:pd.DataFrame, num_prac_cols : int) -> pd.DataFrame:
         result_series.append(num)
 
     #append the summary, add England as first value, and move to top row  
-    result_series = pd.Series(result_series, index = df.columns)
-    df2 = df.append(result_series, ignore_index=True)
+    df2 = df.copy()
+    df2.loc[len(df2)] = result_series
     df2 = pd.concat([df2.iloc[-1:], df2.iloc[:-1]], ignore_index=True)
 
     return df2

diff --git a/ncdes/output/outputexcel.py b/ncdes/output/outputexcel.py
@@ -17,7 +17,7 @@ def set_border(ws, df, end_col):
             cell.border = Border(top=thin, left=thin, right=thin, bottom=thin)
 
 
-def main_to_excel(NCDes_main_df: pd.DataFrame, root_directory :str, server, database, service_year="NCD_23_24") -> None:
+def main_to_excel(NCDes_main_df: pd.DataFrame, root_directory :str, server, database, service_year) -> None:
     """
     Converts LDHC_output_df into an excel file.
     root_directory = directory of root

diff --git a/ncdes/output/outputs.py b/ncdes/output/outputs.py
@@ -25,7 +25,6 @@ def check_and_create_folder(folder_path):
         os.makedirs(folder_path)
 
 
-
 def save_NCDes_main_to_csv(NCDes_problem_ind_rem, root_directory):
     dates_table = get_date_for_name(NCDes_problem_ind_rem)
     file_name = get_file_name(dates_table)
@@ -36,11 +35,11 @@ def save_NCDes_main_to_csv(NCDes_problem_ind_rem, root_directory):
 
     #check if month folder exists, if not create one
     data_month = get_data_month(NCDes_problem_ind_rem)
-    print(f"data month is {data_month}")
+    print(f"Data month is {data_month}")
     check_and_create_folder(f"{root_directory}Output\\{file_folder}\\CSV_archive\\{data_month}")
 
     #to csv
-    print("converting main df to csv")
+    print("Converting main df to csv (takes a while)")
     NCDes_problem_ind_rem.to_csv(f"{root_directory}Output\\{file_folder}\\CSV_archive\\{data_month}" + r"\\" + file_name + ".csv",
                                      index=False)
 
@@ -53,13 +52,15 @@ def save_NCDes_main_to_zip(NCDes_problem_ind_rem, root_directory):
     #to zip
     with zipfile.ZipFile(f'{root_directory}Output\\{file_folder}\\Zip_archive\\{data_month}\\{file_name}.zip','w') as zipMe:
             filenamecsv = f"{file_name}.csv"
-            print(f"filename is {filenamecsv}")
+            print(f"File name is {filenamecsv}")
             file = f"{root_directory}Output\\{file_folder}\\CSV_archive\\{data_month}\\" + filenamecsv
             zipMe.write(file, arcname=filenamecsv, compress_type=zipfile.ZIP_DEFLATED)
 
 
 def save_NCDes_main_to_excel(NCDes_problem_ind_rem, root_directory, server, database):
-    main_to_excel(NCDes_problem_ind_rem, root_directory, server, database)
+    dates_table = get_date_for_name(NCDes_problem_ind_rem)
+    file_folder = get_file_folder(dates_table) 
+    main_to_excel(NCDes_problem_ind_rem, root_directory, server, database, file_folder)
 
 def save_trendmonitor(NCDes_problem_ind_rem, root_directory):
     data_month = get_data_month(NCDes_problem_ind_rem)   
@@ -69,7 +70,6 @@ def save_NCDes_by_ruleset_to_csvs(NCDes_with_rulesets, root_directory):
     dates_table = get_date_for_name(NCDes_with_rulesets)
     file_name = get_file_name(dates_table)
     file_folder = get_file_folder(dates_table)
-
     data_month = get_data_month(NCDes_with_rulesets) 
     #to csv
     for RULESET_ID in NCDes_with_rulesets['Ruleset ID'].unique():
@@ -102,8 +102,7 @@ def get_date_for_name(NCDes_with_geogs):
         An object that has all the correct date data we need to create the filename
     """
 
-
-    date = pd.to_datetime(NCDes_with_geogs["ACH_DATE"].iloc[0], infer_datetime_format=True)
+    date = pd.to_datetime(NCDes_with_geogs["ACH_DATE"].iloc[0])
 
     dates_table = NCDes_with_geogs[["ACH_DATE"]].drop_duplicates()
 
@@ -137,15 +136,14 @@ def get_file_folder(dates_table):
     year = dates_table["Year"].iloc[0]
 
     if int(month_num) >= 4:
-        file_folder = r"NCD_" + year + "_" + str(int(year) + 1)
+        file_folder = year + "_" + str(int(year) + 1)
 
     elif int(month_num) < 4:
-        file_folder = r"NCD_" + str(int(year) - 1) + "_" + year
+        file_folder = str(int(year) - 1) + "_" + year
 
     return file_folder
 
 def archive_input_as_csv(ncdes_raw, root_directory):
-
     today = (datetime2.today()).strftime("%Y_%m_%d")
     ncdes_raw.to_csv(f"{root_directory}Input\\Archive\\NCDes_" + today + ".csv", index=False)
 

diff --git a/ncdes/output/trendmonitor.py b/ncdes/output/trendmonitor.py
@@ -107,7 +107,7 @@ def write_trend_monitor(NCDes_main_df: pd.DataFrame, root_directory :str, data_m
     override = check_if_copy(excel_df, month)
 
     if override == 0:
-        print("Done no changes to trend monitor.")
+        print("Done - No changes to trend monitor.")
     #Else override, THIS WILL ONLY OVERRIDE LAST ROW, ASSUMING DATA IS NEWEST
     elif override == 1:
         row_num, col_num = excel_df.shape

diff --git a/ncdes/processing/validation_check.py b/ncdes/processing/validation_check.py
@@ -156,7 +156,7 @@ def save_check_results(path, type_of_check, df_check_results, ncd_table_col_name
     else:
         new_file = df_check_results
     # Overwrite existing file
-    print(f"overwriting previous {type_of_check} {ncd_table_col_name_str} archive file")
+    print(f"Overwriting previous {type_of_check} {ncd_table_col_name_str} archive file")
     new_file.to_csv(path, index=False)
 
     return

diff --git a/public_meta_data/indicator dictionary.csv b/public_meta_data/indicator dictionary.csv
@@ -1,5 +1,4 @@
 Indicator ID,Indicator Description,Ruleset ID,Payment or Management Information (MI)
-
 NCD001,Percentage of registered patients referred to a social prescribing service.,Personalised Care,MI
 NCD003,Percentage of at-risk patients aged 18 to 64 years inclusive who received a seasonal influenza vaccination between 1 September and 31 March.,Vaccination & Immunisation,Payment
 NCD004,Percentage of patients aged two or three years on 31 August of the relevant financial year who received a seasonal influenza vaccination between 1 September and 31 March.,Vaccination & Immunisation,Payment
@@ -118,6 +117,4 @@ NCDMI189,"Percentage of permanent care home residents aged 18 years or over, who
 NCDMI198,Mean number of patient contacts as part of weekly care home round per care home resident aged 18 years and over.,Enhanced Health in Care Homes,MI
 NCDMI199,"Percentage of patients aged 65 years or over, who received a seasonal influenza vaccination between 1 September and 31 March.",Vaccination & Immunisation,MI
 NCDMI200,Percentage of registered patients with a recording of ethnicity.,Tackling Health Inequalities,MI
-
-NCDMI205,"Percentage of patients on the QOF Coronary Heart Disease, Peripheral Arterial Disease, or Stroke/TIA Register, who have a recording of non-HDL cholesterol in the preceding 12 months that is lower than 2.5 mmol/L, or where non-HDL cholesterol is not recorded then� a recording of LDL cholesterol in the preceding 12 months that is lower than 1.8 mmol/L.",Cardiovascular Disease Prevention,MI
-
+NCDMI205,"Percentage of patients on the QOF Coronary Heart Disease, Peripheral Arterial Disease, or Stroke/TIA Register, who have a recording of non-HDL cholesterol in the preceding 12 months that is lower than 2.5 mmol/L, or where non-HDL cholesterol is not recorded then� a recording of LDL cholesterol in the preceding 12 months that is lower than 1.8 mmol/L.",Cardiovascular Disease Prevention,MI