Skip to main content

Spreadsheet analysis · turn dirty data into conclusions

Don't send the full table to the model. Only send the header and a sample so it writes a cleaning and analysis script, and run the data locally.

Disclaimer: The content shown on this page is generated by an AI model and is for reference only. It does not constitute any guarantee of effectiveness. Actual output and Credits consumption are subject to "Usage Details" in the console.
Tables exported from business systems may be untidy: dates mix several formats, amounts carry ¥ and commas, missing values come in four variants, and duplicate rows are sprinkled in. The right approach is not to feed the table to the model, but to have it write a cleaning and analysis script and run the data on your own machine—cheaper, no data leaves your side, and reusable. Call chain: qwen3.7-plus (writes the cleaning and analysis script) · a dirty order table exported from a business system (3,695 rows / 11 columns, messy dates, amounts with ¥, duplicates and blanks) · three stages: (1) send only the header + a 20-row sample to the model so it writes a ready-to-run script → (2) the script runs locally, the full data never uploads → (3) read its conclusions. One-sentence output: The Douyin channel has a 16.15% refund rate, the only outlier among five channels; customer C2077 placed 38 orders in a single day, suspected of brushing—expand for the diagnostic numbers, the channel table, and 4 pitfalls you must double-check.

Steps: Have it write a script instead of giving it the data

Send only the header and the first 20 rows of sample data, and have qwen3.7-plus write a script that runs directly:
I exported a full-year 2025 order detail CSV from the order system, 3695 rows in total. I won't send you the data (it contains customer info and is too large),
I'll only give you the header and the first 20 rows of sample data. Based on that, write a Python script that runs directly.
Sample (including header): [paste header + first 20 rows]
The script should do four things and print the result of each:
1. Data profiling: check item by item and report affected row counts (column name conventions, date format variants, non-numeric amount notations,
    missing-value representations, fully duplicate rows, negative or extreme outlier amounts, blanks in key fields).
    The sample is only 20 rows; the real data may be dirty in more ways, so the checks must be robust. For unseen formats, bucket them as "unparseable" and count.
2. Cleaning: clean according to the rules you judge and print the rules. At least handle: unify dates to YYYY-MM-DD, convert amounts to float,
    unify missing values, deduplicate, normalize province names, handle outliers.
3. Statistics: row counts before and after cleaning, full-year net sales, monthly order counts and sales, order counts / sales / refund rate by channel,
    sales by province, max single-customer single-day order count and the top 3.
4. Suspicious items: automatically flag months with abnormal volume, channels with abnormal refund rates, customers with abnormal ordering behavior. Judgment should be based on computed numbers, do not hardcode thresholds.
Hard requirements: use only the standard library (csv/re/collections/datetime/statistics), no pandas;
file path via command-line argument; the file is UTF-8 with BOM so use utf-8-sig; the code must run directly with no placeholders or TODOs; output in Chinese.
Only output one complete Python script, no explanation.
3. Data statistics
============================================================
[3] Order count, sales, and refund rate by channel:
Channel           |  Order count |       Total sales |  Refund rate
---------------------------------------------------------
JD                |          743 | ¥   941,442.03 |    5.04%
Tmall             |          737 | ¥   836,619.78 |    4.35%
Douyin            |          700 | ¥   959,251.32 |   16.15%
Private mini-program |       710 | ¥ 1,091,549.47 |    2.37%
Offline store     |          745 | ¥ 1,088,475.68 |    6.17%

4. Suspicious-item analysis
============================================================
[1] Months with clearly abnormal volume (|Z-score| > 1.5):
  - 2025-08: order count 519, Z-score: 1.51
  - 2025-10: order count 11, Z-score: -2.04

[2] Channels with clearly abnormal refund rate (|Z-score| > 1.5):
  - Douyin: refund rate 16.15%, Z-score: 1.73

[3] Customers with abnormal ordering behavior (single-day order count Z-score > 2.0 or very high total refund rate):
  - Customer C2077: max 38 single-day orders (Z=41.73)
  - Customer C2271: total refund rate 97.42% (Z=4.50)
  - Customer C2134: total refund rate 90.37% (Z=4.15)
  ……(16 listed in total)

Notes

  • Consumption: This playbook involves text generation (qwen3.7-plus). The input is only a 20-row sample rather than the full table; when data is large, do not feed the whole table (it tends to time out). Have it write a script and run it locally for stability.
  • Data stays local: The model only sees the 20-row sample used to write the script. The full order table (including customer IDs and amounts) always runs on your own machine and is never uploaded.
  • Double-check key numbers yourself: The script's conclusions are readable as-is, but numbers involving amount definitions or anomaly judgments should be verified against the script (the script is plain text; tweak a few lines as needed).
Model Playground
Fine-tuning
Model Compression
Statistics and Monitoring
Support