Prompt Engineering: Achieving Error-Free Code Generation

ChatGPT operates as a conversational tool—users submit questions and receive answers. The critical factor determining output quality is the prompt itself.

For data analysts, the goal is to generate working code without modifications. Code requiring changes becomes useless for non-programmers and counterproductive for those with basic programming knowledge, as debugging often takes longer than writing the code themselves.

Consider a practical scenario: a worksheet contains student exam scores in columns A through C, with columns representing class, name, and score respectively. The task requires calculating the average score for each class—a grouping aggregation problem.

An effective prompt structure:

You are a pandas expert. File path: D:/Samples/ch03/02/class_scores.xlsx
The first worksheet contains data in columns A-C, representing 'Class', 'Name', and 'Score'. 
Import the Excel data using pandas with engine='openpyxl'. 
Calculate the average score per class, rounded to 1 decimal place. 
Output format: ClassName average_score. Add code comments.

ChatGPT returns:

import pandas as pd
# Read Excel file
df = pd.read_excel('D:/Samples/ch03/02/class_scores.xlsx', engine='openpyxl')
# Calculate average score per class
average_scores = df.groupby('Class')['Score'].mean().round(1)
# Display results
for index, value in average_scores.items():
    print(f'{index}: {value}')

Users save this to a script file and execute it—programming tasks handled entirely by ChatGPT.

Knowledge Architecture: Problem-Oriented Refactoring

ChatGPT solves problems through Q&A interactions. Systematically addressing data analysis tasks requires restructuring analytical knowledge around problem domains, creating a repository of typical scenarios.

Each typical problem connects to validated prompts that generate working code. From these examples, prompt templates emerge—reusable patterns for similar problem categories.

This yields a prompt template library capable of addressing data analysis scenarios.

When encountering complex problems, apply a divide-and-conquer strategy: decompose into simpler sub-problems, match each against known templates, and generate code accordingly.

Some scenarios demand programming awareness—detailed algorithm descriptions, specific function specifications—requiring templates authored by experienced developers.

A typical template library structure organizes problems by category, containing sample datasets, verified prompts, and executable code for each scenario.

Tool Selection: Python Over VBA

Excel data analysis currently supports VBA and Python, with Python offering pandas, xlwings, and OpenPyXL as primary libraries.

Extensive testing demonstrates Python code generation success rates significantly exceed VBA. Prompt crafting proves simpler for Python as well.

For instance, implementing Chinese-style ranking (where ties share the lowest rank):

Python pandas prompt:

You are a pandas expert. File: D:/Samples/07/ranking/sprint_times.xlsx
First worksheet contains A1:B8 with columns 'Name' and 'Sprint_Time(seconds)'. 
First row as header, engine='openpyxl'. 
Rank by sprint time (lower is better). Integer ranking, Chinese-style (ties get minimum rank). 
Add ranking as final column. Sort rows by rank ascending. Add comments.

VBA equivalent requires describing the ranking algorithm in detail—essentially providing pseudo-code to ChatGPT.

This difference stems from two factors: ChatGPT itself is built with Python and leverages Python-based deep learning libraries aligned with pandas; pandas already implements ranking through the rank() method, while VBA requires explicit algorithm specification.

Therefore: choose Python.

Among Python libraries, pandas handles data processing rapidly with concise code and suports large datasets. However, pandas lacks Excel object model support—no direct cell range access or writing.

xlwings provides comprehensive Excel object modeel support but requires Excel installation and handles only traditional small-to-medium datasets.

OpenPyXL can operate without Excel installed, making it suitable for software development, but offers incomplete object model support and limited data handling capacity.

In practice: use pandas for data operations, OpenPyXL or xlwings for Excel interaction like cell formatting and data I/O.

Code generation success rates: pandas highest (often no modifications needed), OpenPyXL moderate (more errors), xlwings lowest (VBA-style API mixed with new syntax creates confusion).

Thus, prioritize pandas for ChatGPT-based data analysis.

Excel Python Integration: Native pandas Support

In August 2023, Microsoft released Excel with built-in Python (Beta). This cloud-based solution eliminates local Python installation requirements, ensuring consistent versions across users.

Excel Python integrates pandas natively, allowing formula-style Python usage.

Adapting ChatGPT-generated pandas code for Excel Python requires minimal modification—primarily adjusting input/output handling.

For example, with data in columns A-C:

df=xl("A1:C26",headers=True)
df.groupby('Class')['Score'].mean().round(1)

Enter Python mode with =PY( in a cell, input the adapted code, press Ctrl+Enter. The cell returns a pandas Series object, expandable to display per-class averages.

This demonstrates ChatGPT-generated pandas code transfers easily to Excel Python with minor adjustments.

Optimized Combinations: When to Use Each Library

Each library serves specific purposes:

pandas: Fast processing, clean syntax, large dataset support. Use for data transformation, statistical analysis, grouping operations.

xlwings: Full Excel integration, VBA compatibility. Requires Excel installation. Ideal for tasks requiring cell formatting, chart creation, or Workbook-level operations.

OpenPyXL: Standalone operation without Excel. Best for serveer-side processing, automated pipelines, or environments without Office installation.

Typical workflow: process data with pandas, then use xlwings or OpenPyXL for Excel formatting and output.

For prompt writing: pandas requires straightforward descriptions, xlwings demands precision due to mixed API styles, OpenPyXL needs patience due to inconsistent documentation.

Foundational Knowledge: Domain Expertise as the Foundation

Regardless of AI advancement, domain expertise remains essential. Deep analytical knowledge enables crafting precise prompts that generate accurate solutions.

Build strong fundamentals, then leverage tools like ChatGPT to amplify productivity—technical mastery amplified by proper application.