Automate Medical Coding Using BERT

Target audience: Beginner
Estimated reading time: 5'

Posts history

Transformers and self-attention models are increasingly taking center stage in the NLP toolkit of data scientists [ref 1]. This article delves into the design, deployment, and assessment of a specialized transformer tasked with extracting medical codes from Electronic Health Records (EHR) [ref 2]. The focus is on curbing development and training expenses while ensuring the model remains current.

Table of contents

Introduction

Challenges

       Extracting medical codes

       Minimizing costs

       Keeping models up-to-date

Architecture

Tokenizer

BERT encoder

       Context embedding

       Segmentation

       Transformer

       Self-attention

Classifier

Active learning

References

Glossary

Important notes: 

• This piece doesn't serve as a primer or detailed account of transformer-based encoders,  Bidirectional Encoder Representations from Transformers (BERT), multi-label classification or active learning. Detailed and technical information on these models is available in the References section. [ref 1, 3, 8, 12]. 
• The terms medical document, medical note and clinical notes are used interchangeably
• Some functionalities discussed here are protected intellectual property, hence the omission of source code.

Introduction

Autonomous medical coding refers to the use of artificial intelligence (AI) and machine learning (ML) technologies to automatically assign medical codes to patient records [ref 4]. Medical coding is the process of assigning standardized codes to diagnoses, medical procedures, and services provided during a patient's visit to a healthcare facility. These codes are used for billing, reimbursement, and research purposes.

By automating the medical coding process, healthcare organizations can improve efficiency, accuracy, and consistency, while also reducing costs associated with manual coding.

 

A health insurance claim is an indication of the service given by a provider, even though the medical records associated with this service can greatly vary in content and structure. It's crucial to precisely extract medical codes from clinical notes since outcomes, like hospitalizations, treatments, or procedures, are directly tied to these diagnostic codes. Even if there are minor variations in the codes, claims can still be valid for specific services, provided the clinical notes, patient history, diagnosis, and advised procedures align.

fig. 1 Extraction of knowledge, predictions from electronic medical records 

Medical coding is the transformation of healthcare diagnosis, procedures, medical services described in electronic health records, physician's notes or laboratory results into alphanumeric codes.  This study focuses on automated generation of medical codes and health insurance claims from a given clinical note or electronic health record.

Challenges

There are 3 issues to address:

1. How to extract medical codes reliably, given that labeling of medical codes is error prone and the clinical documents are very inconsistent?
2. How to minimize the cost of self- training complex deep models such as transformers while preserving an acceptable accuracy?
3. How to continuously keep models up to date in production environment?

Extracting medical codes

Medical codes are derived from patient records and clinical notes to forecast procedural results, determine the length of hospital stays, or generate insurance claims. The most prevalent medical coding systems include:

• International Classification of Diseases (ICD-10) for diagnosis (with roughly 72,000 codes)
• Current Procedural Terminology (CPT) for procedures and medications (encompassing around 19,000 codes)
• Along with others like Modifiers, SNOMED, and so forth.

The vast array of medical codes poses significant challenges in extraction due to:

• The seemingly endless combinations of codes linked to a specific medical document
• Varied and inconsistent formats of patient records (in terms of terminology, structure, and length.
• Complications in gleaning context from medical information systems.

Minimizing costs

A study on deep learning models suggests that training a significant language model (LLM) results in the emission of 626,155 pounds of CO2, comparable to the total emissions from five vehicles over their lifespan.

To illustrate, GPT-3/ChatGPT underwent training on 500 billion words with a model size of 175 billion parameters. A single training session would require 355 GPU-years and bear a cost of no less than $4.6M. Efforts are currently being made to fine-tune resource utilization for the development of upcoming models [ref 5].

Keeping models up-to-date

Customer data in real-time is continuously changing, often deviating from the distribution patterns the models were originally trained on (due to concept and covariate shifts).

This challenge is particularly pronounced for transformers that need task-specific fine-tuning and might even necessitate restarting the pre-training process — both of which are resource-intensive actions.

Architecture

To tackle the challenges highlighted earlier, the proposed solution should encompass four essential AI/NLP elements:

• Tokenizer to extract tokens, segments & vocabulary from a corpus of medical documents.
• Bidirectional Encoder Representations from Transformers (BERT) to generate a representation (embedding) of the documents [ref 3].
• Neural-based classifier to predict a set of diagnostic codes or insurance claim given the embeddings.
• Active/transfer learning framework to update model through optimized selection/sampling of training data from production environment.

From a software engineering perspective, the system architecture should provide a modular integration capability with current IT infrastructures. It also requires an asynchronous messaging system with streaming capabilities, such as Kafka, and REST API endpoints to facilitate testing and seamless production deployment.

fig. 2  Architecture for integration of AI components with external medical IT systems 

Tokenizer 

The effectiveness of a transformer encoder's output hinges on the quality of its input: tokens and segments or sentences derived from clinical documents. Several pressing questions need addressing:

1. Which vocabulary is most suitable for token extraction from these notes? Do we consider domain-specific terms, abbreviations, Tf-Idf scores, etc.?
2. What's the best approach to segmenting a note into coherent units, such as sections or sentences?
3. How do we incorporate or embed pertinent contextual data about the patient or provider into the encoder?

Tokens play a pivotal role in formulating a dynamic vocabulary. This vocabulary can be enriched by incorporating words or N-grams from various sources like:

• Terminology from the American Medical Association (AMA)
• Common medical terms with high TF-IDF scores
• Different senses of words
• Abbreviations
• Semantic descriptions
• Stems
• .....

fig. 3 Generation of a vocabulary using training corpus and knowledge base

Our optimal approach is based on utilizing uncased words from the American Medical Association, coupled with the top 85% of terms derived from training medical notes, ranked by their highest TF-IDF scores. It's worth noting that this method can be resource-intensive.

BERT encoder

In NLP, words and documents are represented in the form of numeric vectors allowing similar words to have similar vector representations [ref 6].

The objective is to generate embeddings for medical documents including contextual data to be feed into a deep learning classifier to extract diagnostic codes or generate a medical insurance claim [ref 7].

Context embedding 

Contextual information such as patient data (age, gender,...), medical service provider, specialty, or location is categorized (or bucked for continuous values) and added to the tokens extracted from the medical note. 

Segmentation

Structuring electronic health records into logical or random groups of segments/sentences presents a significant challenge. Segmentation involves dividing a medical document into segments (or sections), each with an equal number of tokens that consist of sentences and relevant contextual data.

Several methods can be employed to segment a document:

1. Isolating the contextual data as a standalone segment.
2. Integrating the contextual data into the document's initial segment.
3. Embedding the contextual data into any arbitrarily chosen segment [Ref 6].

fig. 4 Embedding of medical note with contextual data using 2 segments

Our study show the option 2 provides the best embedding for the feed forward neural network classifier.

Interestingly, treating the entire note as a single sentence and using the AMA vocabulary leads to diminished accuracy in subsequent classification tasks.

Transformer

We employ the self-supervised Bidirectional Representation for Transformer (BERT) with the objectives to:

• Grasp the contextual significance of medical phrases.
• Create embeddings/representations that merge clinical notes with contextual data.

The model construction involves two phases:

1. Pretraining on an extensive, domain-specific corpus [ref 8].
2. Fine-tuning tailored for specific tasks, like classification [ref 9].

After the pretraining phase concludes, the document embedding is introduced to the classifier training. This can be sourced:

1. Directly from the output of the pretrained model (document embeddings).
2. During the fine-tuning process of the pretrained model. Concurrently, fine-tuning operates alongside active learning for model updates."\

fig. 5 Model weights update with features extraction vs fine tuning

It's strongly advised to utilize one of the pretrained BERT models like ClinicalBERT [ref 10] or GatorTron [ref 11], and then adapt the transformer for classification purposes. However, for this particular project, we initiated BERT's pretraining on a distinct set of clinical notes to gauge the influence of vocabulary and segmentation on prediction accuracy.

Self-attention

Here's a concise overview of the multi-head self-attention model for context:
The foundation of a transformer module is the self-attention block that processes token, position, and type embeddings prior to normalization. Multiple such modules are layered together to construct the encoder. A similar architecture is employed for the decoder.

fig. 6 Schematic for transformer encoder block

Classifier

The classifier is structured as a straightforward feed-forward neural network (fully connected), since a more intricate design might not considerably enhance prediction accuracy. In addition to the standard hyper-parameter optimization, different network configurations were assessed.
The network's structure, including the number and dimensions of hidden layers, doesn't have a significant influence on the overall predictive performance.

Active learning

The goal is to modify models to tackle the issue of covariate shifts observed in the distribution of real-time/production data during inference.

The dual-faceted approach involves:

1. Selecting data samples with labels that deviate from the distribution initially employed during training (Active learning) [ref 12].
2. Adjusting the transformer for the classification objective using these samples (Transfer learning)

A significant obstacle in predicting diagnostic codes or medical claims is the steep labeling expense. In this context, learning algorithms can proactively seek labels from domain experts. This iterative form of supervised learning is known as active learning.

Because the learning algorithm selectively picks the examples, the quantity of samples needed to grasp a concept is frequently less than that required in traditional supervised learning. In this aspect, active learning parallels optimal experimental design, a standard approach in data analysis [ref 13].

fig. 6 Simplified data pipeline for active learning.

In our scenario, the active learning algorithm picks an unlabeled medical note, termed note-91, and sends it to a human coder who assigns it the diagnostic code S31.623A. Once a substantial number of notes are newly labeled, the model undergoes retraining. Subsequently, the updated model is rolled out and utilized to forecast diagnostic codes on notes in production.

Thank you for reading this article. For more information ...

References

[1] Attention is all you need

[2] What is medical coding?

[3] Towards data science: BERT Explained: State of the art language model for NLP

[4] Automated clinical coding: what, why, and where we are?

[5] Mitigating the Water Consumption and Carbon Emissions of ChatGPT

[6] Towards data science: What Is Embedding and What Can You Do with It

[7] A Guide on Word Embeddings in NLP

[8] Hugging face: State Of The Art NLP Model Explained
[9] What Is Fine-Tuning and How Does It Work in Neural Networks?

[10] ClinicalBERT: Modeling Clinical Notes and Predicting Hospital Readmission
[11] A Large Language Model for Electronic Health Records
[12] Towards data science: Active Learning in Machine Learning

[13] Active learning strategies

A formal presentation of this project is available at

Autonomous Medical Coding with Discriminative Transformers

Glossary

• Electronic health record (EHR):  An Electronic version of a patients medical history, that is maintained by the provider over time, and may include all of the key administrative clinical data relevant to that persons care under a particular provider, including demographics, progress notes, problems, medications, vital signs, past medical history, immunizations, laboratory data and radiology reports.
• Medical document: Any medical artifact related to the health of a patient. Clinical note, X-rays, lab analysis results,...
• Clinical note: Medical document written by physicians following a visit. This is a textual description of the visit, focusing on vital signs, diagnostic, recommendation and follow-up.
• ICD (International Classification of Diseases):  Diagnostic codes that serve a broad range of uses globally and provides critical knowledge on the extent, causes and consequences of human disease and death worldwide via data that is reported and coded with the ICD. Clinical terms coded with ICD are the main basis for health recording and statistics on disease in primary, secondary and tertiary care, as well as on cause of death certificates
• CPT (Current Procedural Terminology):  Codes that offer health care professionals a uniform language for coding medical services and procedures to streamline reporting, increase accuracy and efficiency. CPT codes are also used for administrative management purposes such as claims processing and developing guidelines for medical care review.

---------------------------

Patrick Nicolas has over 25 years of experience in software and data engineering, architecture design and end-to-end deployment and support with extensive knowledge in machine learning. 
He has been director of data engineering at Aideo Technologies since 2017 and he is the author of "Scala for Machine Learning" Packt Publishing ISBN 978-1-78712-238-3

Generate Code with ChatGPT Reusable Prompt Patterns

Target audience: Advanced

Estimated reading time: 4'  

Posts history

Wouldn't it be fantastic if ChatGPT could generate source code? If it could, what level of quality could we expect from the generated output?

This post introduces the idea of employing reusable prompt patterns with large language models, focusing on their application in generating Python code. 

To illustrate the concept, we present a straightforward use case: generating a PostgreSQL database application that includes update and query functionalities, showcasing the effectiveness of these prompt patterns

Table of contents

Overview
     Reusable patterns
     Use case
Persona pattern
Template pattern
Visualization generator pattern
Reflection pattern
Conclusion
References

Overview

Strategically crafting prompts for interacting with conversational large language models plays a vital role in enhancing the quality of their responses and suggestions.

The OpenAI documentation states: "Designing your prompt is essentially how you “program” the model, usually by providing some instructions or a few examples. This is different from most other NLP services which are designed for a single task, such as sentiment classification or named entity recognition. Instead, the completions and chat completions endpoint can be used for virtually any task including content or code generation, summarization, expansion, conversation, creative writing, and style transfer.."[ref 1]

Notes:

• See post on creating a client application to ChatGPT ChatGPT API Python client [ref 2] or Secure ChatGPT API client in Scala [ref 3] for experimenting with prompt engineering 
• This post uses gpt-3.5-turbo model 

Reusable patterns

Since 1995, the field of software engineering has greatly benefited from the introduction and evolution of design patterns [ref 4]. These design patterns serve to name, abstract, and identify key elements of common design structures, offering software developers reusable solutions. Over time, various design patterns, such as factory, template, composite, and observer, have been applied, assessed, and documented.

It's logical to apply a similar approach to the emerging discipline of prompt engineering, considering the existing literature that identifies patterns within conversational prompts. 

This article draws upon the framework outlined in the seminal paper titled "A Prompt Pattern Catalog to Enhance Prompt Engineering with ChatGPT" [ref 5]. Prompt patterns, much like their counterparts in software engineering, provide reusable solutions to specific problems. They are categorized based on attributes such as Category (or purpose), Intent and context, Motivation, Contextual statements, Examples, and Consequences.

Here a subset of prompt patterns for the catalog

Pattern	Purpose	Contextual statements
Persona	Output customization Assign a persona/role/domain expert to LLM. The persona can be expressed as a role, job description, title, historical or known figure.	I would like you to ask me questions to achieve X You should ask questions until X is achieved or the condition X is met. Ask me questions regarding X one at the time.
Template	Output customization Ensure output follows a precise template (i.e. format, URL, example…). This pattern instructs LLM to use a unique format in specific portion of the answer.	I am going to provide you with a template for your output X is my place holder for content Try to fit the output into one or more of the placeholders that I listed Please preserve the formatting and template I provided Here is the template to follow: PATTERN with PLACEHOLDER.
Cognitive verifier	Prompt Improvement Quality of LLM answers improves if the initial questions is broken into additional sub-questions.	When you asked a question, follow these rules Generate several additional questions that would help more accurately answer the original question Combine the answers to the individual questions to produce the final aggregate answer to the overall question.
Fact check list	Error identification Request LLM to provide/append a list of facts/assumptions to the answer, so the user may perform due-diligence.	Generate a set of facts that are contained in the output The set of facts should be inserted/appended to the output The set of facts should be the fundamental facts that could undermine the veracity of the output if any of them are incorrect.
Output automater	Output customization Having LLM generate a script or automated task that can be execute any steps the LLM recommends.	Whenever you produce an output with steps, always do this. Produce an executable artifact of type X that will automate these steps.
Reflection	Error identification Ask LLM to automatically explain the rationale behind a given answers to the user. The pattern clarifies any points of confusion, underlying assumptions, gaps in knowledge….	Whenever you generate an answer, explain the reasoning and assumptions behind your answer, so I can improve my question.
Visualization Generator	Output customization Use generated text to create a visualization as complex concepts are easier to grasp with diagrams and images. The LLM output should create a pathway for the tool to produce imagery.	Generate an X that I can provide to tool Y to visualize it.

Use case

To assess the influence of different prompt patterns on the quality of ChatGPT responses, we employ a specific use case: building a basic Python application for interacting with a PostgreSQL database, encompassing functionalities for access, update, and querying.

The evaluation procedure involves initiating a conversation with ChatGPT using an initial request or prompt. Subsequently, we iteratively enhance the prompt by applying various patterns, including Persona, Template, Visualization generator, and Reflection patterns.

Each interaction or prompt within the conversation includes:

1. The prompt's objective.
2. The prompt message itself.
3. ChatGPT's response, accompanied by comments and source code.
4. Our subsequent analysis.

Initial request

Prompt

"I would like you to generate Python code for create the table ’predictions’ with the following fields, ‘id’, ‘num_entries’, ‘’variable’, ‘mean’’ and variance’ for the PostgreSQL database ‘EHR’, then write a query for large number of rows."

Answer

ChatGPT code

Initialization of connection, creation of table and insertion of random values.

import psycopg2
from psycopg2 import sql
import random

  # Define connection parameters
db_name = "EHR"
db_user = "username"
db_password = "password"
db_host = "localhost"
db_port = "5432"

   # Connect to the PostgreSQL database
connection = psycopg2.connect(
  dbname=db_name,
  user=db_user,
  password=db_password,
  host=db_host,
  port=db_port
)

   # Create a cursor object
cursor = connection.cursor()

   # Create the 'predictions' table
cursor.execute("""CREATE TABLE predictions (
  id SERIAL PRIMARY KEY,
  num_entries INTEGER,
  variable VARCHAR(255),
  mean FLOAT,
  variance FLOAT);"""
)
connection.commit()

   # Insert a large number of rows into the 'predictions' table
for i in range(10000):      # adjust this number as necessary
  cursor.execute("""INSERT INTO predictions (
    num_entries, variable, mean, variance)VALUES (%s, %s, %s, %s
    )""", 
    (
      random.randint(1, 100),     # num_entries
      f'variable_{i}',                     # variable
      random.uniform(0, 100),    # mean
      random.uniform(0, 10)      # variance
    )
  )
connection.commit()

  # Close the cursor and connection
cursor.close()
connection.close()

Implementation of the query

# Re-establish connection and cursor
connection = psycopg2.connect(
    dbname=db_name,
    user=db_user,
    password=db_password,
    host=db_host,
    port=db_port
)
cursor = connection.cursor()

# Execute a SELECT query
cursor.execute("SELECT * FROM predictions;")

# Fetch all rows from the query
rows = cursor.fetchall()
# Print each row
for row in rows:
    print(row)

# Close the cursor and connection
cursor.close()
connection.close()

Analysis

• The code functions as intended. ChatGPT accurately infers that PostgreSQL is locally deployed and employs randomly generated values for code evaluation. The only minor issue is that the response assumes the absence of a table creation check ('CREATE TABLE IF NOT EXIST').
• In the second code snippet, the query is implemented with a straightforward print statement for each row, serving as a validation measure. To prevent excessive standard output, these print statements could have been applied to a subset of rows.
• ChatGPT doesn't include an exception handling mechanism, as it wasn't explicitly required. Software developers should anticipate incorporating proper error handling in the generated source code for safety.

Persona Pattern

The objective is to assess ChatGPT's ability to identify an erroneous input 'VARCHAR2(128)' and utilize the context with minimal latency. The modifications made to the original prompt are indicated in red.

Prompt

"I would like you to generate Python code for create the table ‘predictions’ with the following fields, ‘id’ of type SERIAL, ‘num_entries’ with type INT,  ‘’variable’ with type VARCHAR2(128), ‘mean’’ with type FLOAT and variance’ with type FLOAT, for the PostgreSQL database ‘EHR’, then write a query for 2000000 rows.
Context: I am a software engineer developing an application with the lowest latency possible."

Answer

ChatGPT comments: "In PostgreSQL, VARCHAR2 is not a valid datatype. We use VARCHAR instead.

For querying 2,000,000 rows efficiently, it's recommended to use a server-side cursor. This will allow you to fetch the rows in manageable chunks, rather than trying to load all the rows into memory at once, which may not be possible with this volume of data."

ChatGPT source code update:

# Re-establish connection and cursor
connection = psycopg2.connect(
   dbname=db_name,
   user=db_user,
   password=db_password,
   host=db_host,
   port=db_port
)

# Create a server-side cursor
cursor_name = "my_cursor"
cursor = connection.cursor(cursor_name)

# Execute a SELECT query
cursor.execute("SELECT * FROM predictions;")

# Fetch rows in manageable chunks
while True:
   rows = cursor.fetchmany(size=1000)  # adjust this number as necessary
   if not rows:
      break

    # Process each row
   for row in rows:
      print(row)

# Close the cursor and connection
cursor.close()
connection.close()

Analysis

• ChatGPT detected the incorrect type VARCHAR2 for the variable.  
• The constraint of low latency query is enforced by fetching a batch of 1000 rows at the time.

Template Pattern

We introduce the template pattern to our initial prompt, instructing ChatGPT to adhere to a specific format for its response. The modification made to the preceding prompt is marked in red.

Prompt

"I would like you to generate Python code for create the table ‘predictions’ with the following fields, ‘id’ of type SERIAL, ‘num_entries’ with type INT,  ‘’variable’ with type VARCHAR(128), ‘mean’’ with type FLOAT and variance’ with type FLOAT, for the PostgreSQL database ‘EHR’, then write a query for large number of rows. 
The output has to be formatted in HTML. I provide a template for you output with the following placeholders in all caps: <title>MYTITLE</title> and CSS style background-color:BACKGROUNDCOLOR. Please fit the output into the two place holders MYTITLE and BACKGROUNDCOLOR.
Context: I am a software engineer developing an application with the lowest latency possible."

Answer

ChatGPT comments: "In this code, a server-side cursor is used to fetch rows from the 'predictions' table, and the fetched rows are stored in a pandas DataFrame. This DataFrame is then converted to HTML and inserted into the HTML template. The HTML template is then saved to an HTML file named output.html."

ChatGPT code update:

import pandas as pd

# Execute a SELECT query
cursor.execute("SELECT * FROM predictions;")

# Fetch all rows into pandas DataFrame
df = pd.read_sql_query("SELECT * FROM predictions;", connection)

# Convert DataFrame to HTML
html = df.to_html()

# HTML template
html_output = f"""
<!DOCTYPE html>
<html>
<head>
    <title>MYTITLE</title>  <!-- replace with your actual title -->
    <style>
    body {{
        background-color: BACKGROUNDCOLOR;  <!-- replace with your actual background color -->
    }}
    table {{
        width: 100%;
        border-collapse: collapse;
    }}
    th, td {{
        border: 1px solid black;
        padding: 8px;
        text-align: left;
    }}
    </style>
</head>
<body>
    {html}
</body>
</html>
"""

# Save the HTML output
with open("output.html", "w") as file:
    file.write(html_output)

# Close the cursor and connection
cursor.close()
connection.close()

Analysis

• The code executes as intended
• ChatGPT generates the HTML output by dumping the results set into a panda dataset and invoking to_htm() method as we did not specify an approach in our request.
• However, ChatGPT did not parameterize html_output, merely suggesting replacing the place holder as an HTML comment.

Visualization generator

As explained in the introduction, we utilize this pattern to instruct ChatGPT to generate a script that will render HTML output in a specified web browser.

.

Prompt

"I would like you to generate Python code for create the table ‘predictions’ with the following fields, ‘id’ of type SERIAL, ‘num_entries’ with type INT,  ‘’variable’ with type VARCHAR(128), ‘mean’’ with type FLOAT and variance’ with type FLOAT, for the PostgreSQL database ‘EHR’, then write a query for large number of rows. 

Context: I am a software engineer developing an application with the lowest latency possible.

The output has to be formatted in HTML. I provide a template for you output with the following placeholders in all caps: <title>MYTITLE</title> and CSS style background-color:BACKGROUNDCOLOR. Please fit the output into the two place holders MYTITLE and BACKGROUNDCOLOR.

Please write the script to display the HTML output on Chrome browser."

Answer

ChatGPT comments: "The resulting HTML output of the query will be displayed in a new tab in your default web browser. If you wish to open it specifically in Chrome, you'll need to provide the path to your Chrome executable and uncomment the relevant lines."

ChatGPT code update

import webbrowser

# Open the web page in the web browser
filepath = 'file://' + os.path.realpath(filename)

# To open in your default web browser
webbrowser.open_new_tab(filepath)

# Or to specifically open in Chrome, uncomment the following lines
# and replace the path_to_chrome with the path to your Chrome executable
# path_to_chrome = r'/path/to/chrome'  

# update path as per your configuration
webbrowser.get(path_to_chrome).open_new_tab(filepath)

Analysis

• We didn't specify the programming language for the visualization generator/script in our request. Consequently, ChatGPT correctly assumes that Python is the language to be used for rendering the HTML output of the query. 
• It's worth noting that ChatGPT provided two statements for launching and opening the browser: one for the default browser and another tailored for Chrome.
• Similar to the previous response, ChatGPT did not include error handling code.

Reflection pattern

Up to this point, ChatGPT has not incorporated error handling code into its responses. Consequently, we've opted to include it in our existing set of contextual statements within the prompt. In order to demonstrate the application of the reflection pattern, we've asked ChatGPT to provide its rationale for each component of its response.

Prompt

"I would like you to generate Python code for create the table ‘predictions’ with the following fields, ‘id’ of type SERIAL, ‘num_entries’ with type INT,  ‘’variable’ with type VARCHAR(128), ‘mean’’ with type FLOAT and variance’ with type FLOAT, for the PostgreSQL database ‘EHR’, then write a query for large number of rows. 

Please add exception handling in connecting to the database

Can you explain your reasoning and assumption behind your answer

Context: I am a software engineer developing an application with the lowest latency possible.

The output has to be formatted in HTML. I provide a template for you output with the following placeholders in all caps: <title>MYTITLE</title> and CSS style background-color:BACKGROUNDCOLOR. Please fit the output into the two place holders MYTITLE and BACKGROUNDCOLOR.

Please write the script to display the HTML output on Chrome browser."

Answer

ChatGPT comments: 
"In the context of your request, here is the reasoning and assumptions:
PostgreSQL Connection: You mentioned that the PostgreSQL database is hosted on a remote service with the URL "mypostgresql". This implies that the Python script will require network access and the necessary credentials (username, password) to access the database.

Data Query: You want to query a large number of rows. This implies that the 'predictions' table will hold a large amount of data. For large result sets, it's good practice to use a server-side cursor, which fetches and processes the rows in manageable chunks. This reduces memory consumption and can potentially speed up the response times of your queries.

Table Creation: You want to create a table named "predictions" with specific fields and data types. This implies that you have the required permissions to create tables in the database.

HTML Output: You want to format the query output in HTML with a specific title and background color. The pandas library's to_html() method is ideal for this. It converts a DataFrame into an HTML table, which can then be customized with additional HTML and CSS.

Browser Display: You want the HTML output to be displayed in the Chrome browser. The Python webbrowser module can open URLs in new browser windows or tabs. However, it uses the system's default browser by default. If you specifically want to open the page in Chrome, you can do so by providing the path to the Chrome executable.
Assumptions:

Libraries: This solution assumes that you have the necessary Python libraries (psycopg2 and pandas) installed. If not, you can install them using pip (pip install psycopg2-binary pandas).

Permissions: The solution assumes that you have the necessary permissions to create tables and query data in the specified PostgreSQL database.

Data Existence: The solution assumes that there is data in the 'predictions' table when querying. If the table is empty, the HTML output will only include the table headers.

Server Performance: Handling large number of rows depends on the capabilities of your database server. The server needs to have enough resources to handle such large data sets efficiently.

Browser: This solution assumes that Chrome browser is installed on your system. If it's not, the webbrowser module's open methods will not work as expected. The exact path to Chrome will also depend on your operating system and the specific location where Chrome is installed.

Now, with this reasoning and assumptions, I'll provide the Python script in a follow-up response."

ChatGPT final source code

import psycopg2
import pandas as pd
import webbrowser
import os

# Define connection parameters
db_name = "EHR"
db_user = "username"  # replace with your actual username
db_password = "password"  # replace with your actual password
db_host =  "localhost"  # replace with your actual host

db_port = "5432"  # default PostgreSQL port

try:
    # Connect to the PostgreSQL database
  connection = psycopg2.connect(
     dbname=db_name,
     user=db_user,
     password=db_password,
     host=db_host,
     port=db_port
  )

    # Create a cursor object
  cursor = connection.cursor()

    # Create the 'predictions' table
  cursor.execute("""
     CREATE TABLE predictions (
         id SERIAL PRIMARY KEY,
         num_entries INT,
         variable VARCHAR(128),
         mean FLOAT,
         variance FLOAT
     );""")
  connection.commit()

    # Close the cursor
  cursor.close()

    # Re-establish cursor with server-side cursor for fetching large amount of rows
  cursor = connection.cursor("my_cursor")

    # Execute a SELECT query
  cursor.execute("SELECT * FROM predictions;")

    # Fetch all rows into pandas DataFrame
  df = pd.read_sql_query("SELECT * FROM predictions;", connection)

    # Convert DataFrame to HTML
  html = df.to_html()

    # HTML template
  html_output = f"""
    <!DOCTYPE html>
    <html>
    <head>
        <title>MYTITLE</title>  <!-- replace with your actual title -->
        <style>
        body {{
            background-color: BACKGROUNDCOLOR;  <!-- replace with your actual background color -->
        }}
        table {{
            width: 100%;
            border-collapse: collapse;
        }}
        th, td {{
            border: 1px solid black;
            padding: 8px;
            text-align: left;
        }}
        </style>
    </head>
    <body>
        {html}
    </body>
    </html>
    """

    # Save the HTML output
    filename = "output.html"
    with open(filename, "w") as file:
        file.write(html_output)

    # Open the web page in the web browser
    filepath = 'file://' + os.path.realpath(filename)

    # Or to specifically open in Chrome, uncomment the following lines
    # and replace the path_to_chrome with the path to your Chrome executable
    # path_to_chrome = r'/path/to/chrome'  # update path as per your configuration
    # webbrowser.get(path_to_chrome).open_new_tab(filepath)

except psycopg2.Error as error:
    print(f'An error occurred while connecting to the PostgreSQL database {error}")

finally:
      # Always close the cursor and connection, whether an error occurred or not
    if 'cursor' in locals():
        cursor.close()
    if 'connection' in locals():
        connection.close()

Analysis

• In accordance with our specific request, ChatGPT has incorporated exception handling into the most recent version of the code.
• Furthermore, ChatGPT offers insights into its choice of using Pandas for HTML generation and makes assumptions about the PostgreSQL installation status, table condition, query size, and other aspects.
• It's worth noting that the comment regarding server/database performance could have included additional guidance on multi-threaded execution, creating views, and manipulating cursors within the Python code.

Conclusion

We've showcased that the quality of the generated code significantly improves when we enhance an initial, basic prompt with successive patterns.

The synergy or integration of these prompt patterns enhances the overall quality of the generated Python code, sometimes even more so than the patterns individually contribute.

It's worth noting that there are numerous other patterns that are worth exploring [ref 4].

Thank you for reading this article. For more information ...

References

[1] Open AI platform: Key concepts

[2] ChatGPT API Python client 

[3] Secure ChatGPT API client in Scala
[4] Design Patterns: Elements of reusable Object-Oriented Software E. Gamma, R. Helm, R. Johnson, J. Vlissides - Addison-Wesley professional computing series - 1995

[5] A Prompt Pattern Catalog to Enhance Prompt Engineering with ChatGPT J. White, Q. Fu, S. Hays, M. Sandborn, C. Olea, H. Gilbert, A. Elnashar, J. Spencer-Smith, D. C. Douglas

---------------------------

Patrick Nicolas has over 25 years of experience in software and data engineering, architecture design and end-to-end deployment and support with extensive knowledge in machine learning. 
He has been director of data engineering at Aideo Technologies since 2017 and he is the author of "Scala for Machine Learning" Packt Publishing ISBN 978-1-78712-238-3