NHSDigital · SamHollings · Feb 7, 2023 · Feb 7, 2023 · Feb 7, 2023
diff --git a/.gitignore b/.gitignore
@@ -1,13 +1,18 @@
-
-
-
 ####################################################################
 # GENERIC GITIGNORE FILE FOR PYTHON 
-# Copied from https://github.com/github/gitignore/blob/master/Python.gitignore
-# This is a useful thing to just copy and paste since it should work for most use cases. 
-# If you have additional things to exclude from the .gitignore file you should add it above
-# this section.
+# Adapted from https://github.com/github/gitignore/blob/master/Python.gitignore
+# The below should be useful for most Python projects. 
+# Add any additional things to exclude.
 #####################################################################
+# Avoid committing any data. If you need to make an exception for one file you can do that separately
+*.csv
+*.ipynb
+*.ipynb_checkpoints
+*.xlsx
+*.xls
+
+# Exceptions- use ! to include files that would otherwise be ignored
+!publication_template.xlsx
 
 # Byte-compiled / optimized / DLL files
 __pycache__/
@@ -146,4 +151,7 @@ dmypy.json
 .pytype/
 
 # Cython debug symbols
-cython_debug/
+cython_debug/
+
+# VS Code project files
+.vscode/
diff --git a/LICENCE b/LICENCE
@@ -1,6 +1,6 @@
 MIT License
 
-Copyright (c) 2022 Crown Copyright NHS Digital
+Copyright (c) 2023 Crown Copyright NHS England.
 
 Permission is hereby granted, free of charge, to any person obtaining a copy
 of this software and associated documentation files (the "Software"), to deal

diff --git a/README.md b/README.md
diff --git a/config.toml b/config.toml
@@ -0,0 +1,20 @@
+project_name = "Example-Publication"
+
+server = ''                   
+database = ''          
+schema = ''
+table = ''  
+# The above variables reference an example location in SQL Server, they will need to be changed for your project
+# If you are using Databricks instead of SQL Server you will not need to set the 'server' variable
+# Your project may use more than 1 table, you should add more table names and assignments as needed
+
+# Include any other configuration parameters, e.g. dates
+start_date = "1970-01-01"
+end_date = "2022-01-01"
+
+# Fill missing value for specific column
+filled_value = 2022
+
+# Here we describe where the output and logs are saved, change as necessary
+output_dir = ''
+log_dir = '' 
diff --git a/create_publication.py b/create_publication.py
@@ -0,0 +1,79 @@
+"""
+Purpose of the script:  to provide a starting point for the basis of your pipeline using example data from SQL server
+
+The script loads Python packages but also internal modules (e.g. modules.helpers, helpers script from the modules folder).
+It then loads various configuration variables and a logger, for more info on this find the RAP Community of Practice Repo on Github
+
+Then, we call some basic SQL functions to load in our data, process it and write our outputs to an appropriate file type (e.g. CSV, Excel)
+For more info on automated excel outputs, find the automated-excel-publications repo on Gitlab.
+"""
+
+# this part imports our Python packages, including our project's modules
+import logging
+import timeit 
+from pathlib import Path
+from src.utils.data_connections import read_sql_file, get_df_from_server, make_database_connection
+from src.utils.file_paths import get_config
+from src.utils.logging_config import configure_logging 
+from src.processing.clean import calculate_years, process_columns
+from src.processing.derive_fields import gp_count_by_region, calculate_mean_years
+
+logger = logging.getLogger(__name__)
+
+def main():
+
+    # load config, here we load our project's parameters from the config.toml file
+    config = get_config("config.toml") 
+    server = config ['server']
+    database = config['database']
+    schema = config['schema']
+    table = config['table']
+    filled_value = config['filled_value']
+    output_dir = Path(config['output_dir'])
+    log_dir = Path(config['log_dir'])
+
+    # configure logging
+    configure_logging(log_dir, config)
+    logger.info(f"Configured logging with log folder: {log_dir}.")
+
+    # sets up database connection
+    conn = make_database_connection(server, database)
+
+    # load data, this part handles importing our data sources     
+    query = read_sql_file('sql', 'example.sql', database, schema, table)
+    gp_df = get_df_from_server(conn, server, database, query)
+
+    # follow pre-processing steps  
+    gp_df.rename(columns={'ADDRESS_LINE_5': 'REGION', 
+                       'OPEN_DATE': 'OPENED', 
+                       'CLOSE_DATE': 'CLOSED'}, inplace=True)
+
+    gp_df = process_columns(gp_df, 
+        date_col_names = ['OPENED', 'CLOSED'], 
+        string_col_names= ['REGION', 'NAME']
+        )
+
+    gp_df = calculate_years(filled_value, gp_df)
+
+    # prepare data for CSV
+    publication_breakdowns = {}
+    publication_breakdowns['gp_data'] = gp_df
+
+    # follow data processing steps
+    region_df = gp_count_by_region(gp_df)
+    region_df = calculate_mean_years(region_df, gp_df)
+
+    publication_breakdowns['region_data'] = region_df
+
+    # produce outputs
+    for table_name, df in publication_breakdowns.items():
+        df.to_csv(output_dir / f'{table_name}.csv', index=False)
+        logger.info('\n\n%s.csv created!\n', table_name)
+    logger.info(f"Produced output(s) in folder: {output_dir}.")
+
+if __name__ == "__main__":
+    print(f"Running create_publication script")
+    start_time = timeit.default_timer()
+    main()
+    total_time = timeit.default_timer() - start_time
+    print(f"Running time of create_publication script: {int(total_time / 60)} minutes and {round(total_time%60)} seconds.\n")
diff --git a/environment.yml b/environment.yml
@@ -1,19 +1,13 @@
-#A template of the conda environment
-#The template contains commonly used packages
-#For details: https://github.com/NHSDigital/rap-community-of-practice/blob/main/python/virtual-environments.md
-name: rap
-
+# The libraries used by your code should be listed here
+name: rap_template # your project name (no spaces!)
 channels:
-  - defaults
-
+  - default
+  - conda-forge
 dependencies:
-  - flake8=3.9.0
-  - openpyxl
-  - pip==20.2.4
   - python=3.9
-  - pyodbc
-  - ipykernel
-  - ipython
-  - nbconvert==6.0.7
-  - numpy==1.19.5
-  - pandas
+  - pip
+  - pandas=1.4.4
+  - pathlib2=2.3.7
+  - pyodbc=4.0.32
+  - sqlalchemy=1.4.36
+  - toml=0.10.2
diff --git a/my_project/__init__.py b/my_project/__init__.py
diff --git a/my_project/create_publication.py b/my_project/create_publication.py
diff --git a/my_project/params.py b/my_project/params.py
diff --git a/my_project/utilities/__init__.py b/my_project/utilities/__init__.py
diff --git a/my_project/utilities/data_connections.py b/my_project/utilities/data_connections.py
diff --git a/my_project/utilities/field_definitions.py b/my_project/utilities/field_definitions.py
diff --git a/my_project/utilities/processing_steps.py b/my_project/utilities/processing_steps.py
diff --git a/package_exercises.md b/package_exercises.md
diff --git a/pyproject.toml b/pyproject.toml
@@ -0,0 +1,15 @@
+[build-system]
+requires = ["setuptools>=61.0"]
+build-backend = "setuptools.build_meta"
+
+[project]
+name = "rap-package-template"
+version = "1.0.0"
+authors = [
+    { name="Data Science Skilled team", email="datascience@nhs.net" }
+]
+readme = "README.md"
+requires-python = ">=3.10"
+
+[project.urls]
+"Homepage" = "https://nhsdigital.github.io/rap-community-of-practice/"
diff --git a/requirements.txt b/requirements.txt
@@ -1,6 +1,25 @@
 # The libraries used by your code should be listed here
-# See https://github.com/NHSDigital/rap-community-of-practice/blob/main/python/project-structure-and-packaging.md
+# See https://nhsd-git.digital.nhs.uk/data-services/analytics-service/iuod/rap-community-of-practice/-/blob/master/python/project-structure-and-packaging.md
 
--e .
+# Python version = 3.10.*
 
-ipykernel
+# Data manipulation
+numpy==1.21.5
+pandas==1.3.5
+
+# SQL connections
+pyodbc==4.0.35
+sqlalchemy==1.4.46
+
+# Excel output
+#openpyxl==3.0.9
+
+# Testing
+pytest==6.2.5
+pytest-html==3.1.1
+
+# Dependencies of the above packages
+#ipykernel==6.9.0
+#nbformat==5.1.3
+toml==0.10.2
+#pathlib2==2.3.6