For Demand Planners ·
What you'll accomplish
By the end of this guide, you'll have a Python script that automatically cleans your weekly actuals data export — removing duplicates, filling missing values, fixing date formats, and flagging anomalies — in 30 seconds instead of 60–90 minutes of manual work. ChatGPT writes the script; you run it. No prior Python experience required.
What you'll need
Download Python from python.org → click Download Python → install (accept defaults). After installation, open your computer's Terminal (Mac) or Command Prompt (Windows). Type:
pip install pandas openpyxl
Press Enter and wait for installation to complete.
What you should see: Lines of installation text, ending with "Successfully installed pandas..."
Troubleshooting: If you get "pip not found," try python -m pip install pandas openpyxl
Export your ERP/planning system actuals data to CSV. You only need 50–100 rows for setup — just enough to see the common quality issues.
Open the CSV in Excel and look at what's there. Common issues to note:
Write down 3–5 specific quality issues you see.
Open chatgpt.com. Type:
I need a Python script to clean weekly sales actuals data exported from [your ERP system] as a CSV file. Here are my column names and data issues:
Columns: [paste your exact column names]
Issues:
1. [e.g., duplicate rows — same SKU + date combination sometimes appears twice]
2. [e.g., dates in MM/DD/YYYY format — need YYYY-MM-DD]
3. [e.g., missing values in the Actuals column — should be filled with 0 for known stockout SKUs, but I'll provide a separate stockout list]
4. [e.g., occasional negative values in Actuals — flag these don't delete them]
5. [e.g., SKU codes sometimes have leading zeros dropped — should always be 8 digits]
Output: a cleaned CSV plus a separate log file listing every change made.
Please write the complete Python script with comments explaining each step.
What you should see: ChatGPT writes a complete Python script, typically 40–80 lines, with comments.
Copy the script from ChatGPT. Open a text editor (Notepad on Windows, TextEdit on Mac). Paste the script and save as clean_actuals.py in the same folder as your sample CSV.
Open Terminal/Command Prompt. Navigate to that folder:
cd path/to/your/folder
Run the script:
python clean_actuals.py
What you should see: A cleaned CSV file and a log file appear in the same folder.
Open both output files. Does the cleaned data look right? Is the log file capturing the changes you expected?
If anything is wrong, go back to ChatGPT and describe the issue:
Ask ChatGPT to fix the issue. Repeat until the output is correct.
Once the script runs correctly on the sample data, use it on your full weekly export. Add it to a folder called demand-planning-tools. Every Monday when you get your actuals file:
actuals_raw.csv in that folderpython clean_actuals.pyactuals_clean.csv — ready to useAdd a new check to an existing script:
Add a check to my existing Python script that flags rows where this week's quantity is more than 200% of the same SKU's average over the prior 4 weeks. Add a column "Spike_Flag" = True for these rows. Here is my current script: [paste]
Handle a new data format:
Update my script to handle a new column that appeared in this week's export: [column name, description]. It should be [processed this way].
Generate a data quality summary:
Add a section to my script that prints a data quality summary: total rows, rows with duplicates removed, rows with blanks filled, rows flagged as negative, rows flagged as spikes.