Piecing Together Inconsistent Data: A Generative AI Approach

Introduction

Ever tried solving a jigsaw puzzle with pieces from different sets? Welcome to the world of data cleaning.

In today's data-driven landscape, one of the most formidable challenges organizations face is the task of cleaning up years of inconsistently entered data. Imagine having tens of thousands of rows of data that are as mismatched as jigsaw puzzle pieces from different sets. Now, what if solving this complex puzzle was the key to unlocking your company's potential for growth? Intriguing, isn't it?

The Business Problem

The stakes for our client were high. Amidst a significant reorganization, they were in dire need of accurate, reliable data to make informed decisions about which business lines were poised for rapid growth. The repercussions of not solving this data inconsistency issue were severe. They risked making ill-advised investments, potentially pouring resources into areas of their business that weren't as profitable as they seemed.

The Technical Challenge

Traditional data cleansing methods can feel like trying to solve a jigsaw puzzle while blindfolded. They are often limited to surface-level fixes, lacking the nuanced understanding of semantic meaning behind the data. These methods rely heavily on keyword matching, pattern recognition, and other rule-based techniques. As for manual data entry, that was a road our client had already gone down. It was a time-consuming, labor-intensive process that, despite weeks of effort, was still prone to human error and inconsistencies.

Discovering PandasAI

The idea came when I was scrolling through LinkedIn and stumbled upon an article about the newly released PandasAI. https://docs.pandas-ai.com/en/latest/. What immediately caught my eye was its promise of easy integration with existing datasets and its compatibility with the latest generative AI models like GPT-4.0. It seemed like the perfect tool to tackle our client's complex data puzzle.

Easy Implementation

A quick tutorial on how the new library worked and I was able to throw together a simple proof of concept. The entire program is less than 40 lines long.

import pandas as pd
from pandasai.llm import OpenAI
from pandasai import SmartDataframe
from datetime import datetime

llm = OpenAI(api_token="<yourKeyHere>")

#    Load a CSV file into a SmartDataframe object.
def load_dataframe(csv_file):
    with open(csv_file, 'r', encoding='utf-8', errors='replace') as f:
        document = pd.read_csv(f)
    return SmartDataframe(document, config={"llm": llm})

def generate_timestamp():
    now = datetime.now()
    return now.strftime("%Y-%m-%d_%H-%M-%S")

def save_dataframe(dataframe, filename):
    dataframe.to_csv(filename, index=False)
    print(f"Output saved to {filename}")

csv_file = 'active.csv'
smart_dataframe = load_dataframe(csv_file)

with open('classifyPromptV4.txt', 'r') as f:
    prompt = f.read()

# Use the chat method to ask a question and get a response from the SmartDataframe
response = smart_dataframe.chat(prompt)

# Create a new dataframe from the response
result_dataframe = pd.DataFrame(response)

timestamp = generate_timestamp()
filename = f"output_{timestamp}.csv"

save_dataframe(result_dataframe, filename)        

Some Prompt Engineering

Now that I had the code working, it was time to get the prompt working. Here's where it landed:

You are an expert human resources coordinator. You will assist me in a data classification activity related to position descriptions. I want you to analyze the data in the "Position Title" column. I am looking to classify the data into one of several categories.

The categories and some common titles that go in each category are listed below. Pay attention to the context of each category. Look for words that are synonymous with these to help you decide which category the titles should be grouped into.

1. IT Support - Operations, operations support, helpdesk, support, production support, help desk, help desk tech, service desk, tech writer, contract IT roles, computer operator

2. Accounting - Accounting Temp, Temporary Accounts Clerk, Interim Accounts Assistant, Accounts Payable Temp, Accounts Receivable Temp, Financial Data Entry Temp, Temporary Bookkeeper, Short-term Account Analyst, Contract Account Coordinator, Seasonal Tax Assistant

3. Call Center - Customer Service Temp, Temporary Support Agent, Interim Sales Representative, Short-term Billing Specialist, Contract Help Desk Operator, Seasonal Order Processor, Temporary Survey Coordinator, Emergency Response Operator, Temporary Quality Analyst, Bilingual Call Center Temp

4. Warehouse - Warehouse Temp, Temporary Picker, Interim Packer, Short-term Forklift Operator, Contract Inventory Clerk, Seasonal Shipping Coordinator, Temporary Receiving Clerk, Emergency Response Team Member, Temporary Quality Inspector, Contract Material Handler

5. Other - Anything that doesn't clearly fit into one of these categories

Rapidly Improving Results

The initial results were promising. The first pass yielded an accuracy rate of nearly 70%. By the fourth iteration, the model's accuracy had soared to over 95%, effectively turning our jigsaw puzzle into a coherent picture.

Results

The client's reaction was nothing short of ecstatic. We had not only solved a problem that had been a thorn in their side for weeks but also laid down a roadmap for efficiently tackling similar data-related challenges in the future. This was a win-win, saving them considerable time and resources while also opening doors for further collaboration. The pattern could be applied to a range of other data quality their analytics group had uncovered.

Conclusion

The broader implications of this project are nothing short of revolutionary. Generative AI technologies like PandasAI are changing the game in data analysis. What used to be a daunting task, requiring specialized skills and endless hours of manual labor, can now be accomplished with an open-source library and a few dozen lines of Python code.

As AI technologies continue to evolve and mature, we can expect even more robust and efficient solutions for data analysis and other complex tasks. The jigsaw puzzles of yesterday are rapidly transforming into the crystal-clear, actionable insights of tomorrow.

#GenerativeAI #DataCleaning #DataAnalysis #AIInnovation