text: # Databricks notebook source
  #!/usr/bin python3
  
  # -------------------------------------------------------------------------
  # Copyright (c) 2021 NHS England and NHS Improvement. All rights reserved.
  # Licensed under the MIT License. See license.txt in the project root for
  # license information.
  # -------------------------------------------------------------------------
  
  """
  FILE:           dbrks_pomi_vision_gp_practice_month_count.py
  DESCRIPTION:
                  Databricks notebook with processing code for the NHSX Analyticus unit metric: No. of Vision GP Practices (M058)
  USAGE:
                  ...
  CONTRIBUTORS:   Craig Shenton, Mattia Ficarelli
  CONTACT:        data@nhsx.nhs.uk
  CREATED:        13 Dec 2021
  VERSION:        0.0.1
  """
  
  # COMMAND ----------
  
  # Install libs
  # -------------------------------------------------------------------------
  %pip install geojson==2.5.* tabulate requests pandas pathlib azure-storage-file-datalake beautifulsoup4 numpy urllib3 lxml regex pyarrow==5.0.*
  
  # COMMAND ----------
  
  # Imports
  # -------------------------------------------------------------------------
  # Python:
  import os
  import io
  import tempfile
  from datetime import datetime
  import json
  
  # 3rd party:
  import pandas as pd
  from pathlib import Path
  from azure.storage.filedatalake import DataLakeServiceClient
  
  # Connect to Azure datalake
  # -------------------------------------------------------------------------
  # !env from databricks secrets
  CONNECTION_STRING = dbutils.secrets.get(scope="datalakefs", key="CONNECTION_STRING")
  
  # COMMAND ----------
  
  # MAGIC %run /Repos/dev/au-azure-databricks/functions/dbrks_helper_functions
  
  # COMMAND ----------
  
  #Download JSON config from Azure datalake
  file_path_config = "/config/pipelines/nhsx-au-analytics/"
  file_name_config = "config_pomi_dbrks.json"
  file_system_config = "nhsxdatalakesagen2fsprod"
  config_JSON = datalake_download(CONNECTION_STRING, file_system_config, file_path_config, file_name_config)
  config_JSON = json.loads(io.BytesIO(config_JSON).read())
  
  # COMMAND ----------
  
  #Get parameters from JSON config
  source_path = config_JSON['pipeline']['project']['source_path']
  source_file = config_JSON['pipeline']['project']['source_file']
  file_system = config_JSON['pipeline']['adl_file_system']
  sink_path = config_JSON['pipeline']['project']['databricks'][10]['sink_path']
  sink_file = config_JSON['pipeline']['project']['databricks'][10]['sink_file']  
  
  # COMMAND ----------
  
  #Processing
  latestFolder = datalake_latestFolder(CONNECTION_STRING, file_system, source_path)
  file = datalake_download(CONNECTION_STRING, file_system, source_path+latestFolder, source_file)
  df = pd.read_parquet(io.BytesIO(file), engine="pyarrow")
  df1 = df.groupby(["Report_Period_End", "Practice_Code", "System_Supplier"]).count().reset_index()
  df2 = df1[["Report_Period_End","Practice_Code", "System_Supplier"]]
  df2['System_Supplier_bool'] = df2['System_Supplier'].str.contains("VISION")
  def vision_gp_practice(c):
    if c['System_Supplier_bool'] == True:
      return 1
    else:
      return 0
  df2['VISION GP Practices'] = df2.apply(vision_gp_practice, axis=1)
  df3 = df2.sort_values("Report_Period_End")
  df4 = df3.reset_index(drop = True)
  df5 = df4.drop(columns={"System_Supplier", 
                           "System_Supplier_bool"})
  df5.rename(columns={
      "Report_Period_End": "Date",
      "Practice_Code": "Practice code"},
       inplace=True)
  df5.index.name = "Unique ID"
  df_processed = df5.copy()
  
  # COMMAND ----------
  
  #Upload processed data to datalake
  file_contents = io.StringIO()
  df_processed.to_csv(file_contents)
  datalake_upload(file_contents, CONNECTION_STRING, file_system, sink_path+latestFolder, sink_file)
  