How ChatGPT Can Generate Ready‑to‑Run Pandas Code for Excel Data Analysis

In recent years Python, especially the pandas library, has begun to replace traditional VBA for Excel data analysis, and the arrival of ChatGPT has accelerated this shift.

By crafting precise prompts, users can ask ChatGPT to produce pandas code that reads an Excel file, performs group‑by calculations, and outputs results without any manual editing. An example prompt asks the model to read D:/Samples/ch03/02 各班学生成绩.xlsx, calculate each class's average score to one decimal place, and add comments.

import pandas as pd
# 读取Excel文件数据
df = pd.read_excel('D:/Samples/ch01/各班学生成绩.xlsx', engine='openpyxl')
# 计算各班学生成绩的平均分
average_scores = df.groupby('班级')['成绩'].mean().round(1)
# 输出各班平均成绩
for index, value in average_scores.items():
    print(f'{index}班 {value}')

The generated script runs directly in Python IDLE, producing the average scores for each class.

Comparing Python packages, pandas offers the highest success rate for ChatGPT‑generated code, while xlwings and OpenPyXL are more error‑prone and often require additional prompt refinement.

Excel’s built‑in Python (Beta) runs on Microsoft’s cloud, eliminating local installation issues and fully supporting pandas. The same pandas logic can be adapted for this environment with minimal changes:

df = xl("A1:C26", headers=True)
df.groupby('班级')['成绩'].mean().round(1)

When entered in an Excel cell using the =PY( formula, the result is a Series object that expands to show each class’s average score.

Beyond code generation, the article proposes building a prompt‑template library that maps typical data‑analysis problems to effective prompts, enabling systematic reuse and faster problem solving.

Overall, the recommendation is to prefer Python’s pandas for data analysis tasks, leverage ChatGPT for code generation, and use Excel’s built‑in Python to execute the scripts seamlessly.