Prompt Guide: From Data Cleaning to an Interactive Dashboard

A step-by-step Gemini workflow for cleaning files, validating data quality, choosing suitable visuals and producing a Streamlit and Plotly dashboard.

You are an expert in data cleaning, quality assurance, visualisation and dashboard development. Guide me step by step from a raw file to a tested interactive dashboard.

My context:
- Data file/source: [FILE NAME OR TO BE UPLOADED]
- Analysis objective: [OBJECTIVE]
- Dashboard audience: [AUDIENCE]
- Decision to support: [DECISION]
- Preferred tools: [GEMINI / PYTHON / STREAMLIT / PLOTLY / OTHER]
- Output language: [MALAY / ENGLISH]

How to assist me:
1. Review the context and ask only essential unanswered questions. Never invent file structure, values or KPIs.
2. After receiving the file, explain its structure and produce a concise data dictionary.
3. Audit duplicates, missing values, data types, dates, numbers, categories, outliers and impossible ranges. Classify issues as Critical, Medium or Low.
4. Propose a cleaning plan with before/after examples, reasons and affected-row counts. Do not change or delete data before I approve the plan.
5. After my approval, produce dataset_clean, quality_report, change_log and unresolved_issues.
6. Run quality tests and return PASS/FAIL. Stop if any Critical test fails.
7. Recommend 4-6 KPIs or charts based on the analysis questions. State the fields, aggregation, filters, selection rationale and interpretation risks.
8. After I approve the visual plan, produce the complete interactive dashboard, requirements.txt, run instructions and acceptance tests.
9. At every stage show: Findings, Recommendation, Decisions needed from me, and Next step.

Begin by listing the information you need and asking me to upload the file. Do not build the dashboard yet.

From raw file to trustworthy decisions

This guide organises the work into four stages: understand the file, clean and validate the data, select the right visuals, and then build an interactive dashboard. Run each stage separately so errors are easier to detect before they enter charts or KPIs.

1. Establish the file context and intended outcome

Upload a CSV or XLSX file and tell Gemini the analysis purpose, target users and decisions the dashboard needs to support. Use this opening prompt.

I will provide [FILE NAME]. Act as a senior data analyst.

Analysis objective: [OBJECTIVE]
Dashboard users: [TARGET GROUP]
Decision to support: [DECISION]

Before changing the data:
1. Explain the file structure, number of records and number of fields.
2. Produce a concise data dictionary: field name, meaning, current data type and recommended type.
3. Identify assumptions that cannot yet be verified.
4. Do not invent missing values or create charts yet.

2. Audit and clean the data

The audit should cover duplicate records, missing values, data types, date formats, currency or percentage symbols, whitespace, category spelling and outliers. Ask Gemini for a change log so every correction can be traced.

Audit the quality of this file. Rate each issue as Critical, Medium or Low.

Check:
- duplicate records and non-unique ID keys;
- missing values and placeholders such as N/A, -, 0 or blank text;
- incorrect data types;
- inconsistent dates, numbers, currency and percentage formats;
- equivalent categories with different spelling or capitalisation;
- outliers and impossible ranges.

Recommend corrections without silently deleting records. Show before/after examples, the reason for each change and the number of affected records. If information is insufficient, flag it for human review.

Cleaning execution prompt

Apply the corrections I have approved. Preserve the original data as a reference copy.

Produce:
1. dataset_clean;
2. quality_report summarising issues and actions;
3. change_log containing the field, original value, new value and reason;
4. unresolved_issues still requiring a human decision.

Do not fill missing values with a mean, median or invented value without explicit instructions.

3. Validate logic and quality after cleaning

Repeat the checks after transformation and adapt the rules to the domain. For training data, for example, registered participants should equal attendance plus absence; the attendance rate should match its formula; and feedback scores should remain within the allowed scale.

Validate dataset_clean and return PASS or FAIL for each test:
- record counts before and after;
- primary-key uniqueness;
- missing values in required fields;
- numeric and date ranges;
- consistency of cross-field formulas;
- aggregate totals before and after;
- at least five sampled row checks.

Do not continue to visualisation if any Critical test fails. Explain how each failure affects the analysis.

4. Select charts based on the question

Recommend visualisations based on the business questions and field types in dataset_clean.
For each visual, state:
- the question it answers;
- fields on the X and Y axes;
- aggregation method;
- category or time ordering;
- required filters;
- why it is better than the alternatives;
- risk of misinterpretation.

Limit the dashboard to 4-6 visuals that genuinely support decisions.

5. Master prompt for building the Gemini dashboard

Use the following prompt after the data and visual design have been approved.

Using dataset_clean and the approved visual plan, build an interactive web dashboard with Python, Streamlit, Pandas and Plotly Express.

Requirements:
1. Provide a complete runnable app.py.
2. Load the data with error handling and required-field checks.
3. Display the 4-5 most relevant KPI cards.
4. Add sidebar filters for [CATEGORY], [PERIOD] and [SEGMENT].
5. Make every chart respond to the filters.
6. Format dates, percentages and currency correctly.
7. Sort time data chronologically.
8. Provide an empty state when filters return no records.
9. Include a title, concise explanation and data source on the dashboard.
10. Add a download button for the filtered data.

After the code, provide requirements.txt, run instructions and acceptance tests. Do not invent KPIs that cannot be calculated from the file.

6. Test before publishing

• Compare at least three KPIs with manual calculations.
• Test every filter individually and in combination.
• Ensure charts do not mislead through truncated axes or unsorted categories.
• Test empty files, missing values and unexpected data formats.
• Review desktop and mobile layouts as well as loading time.
• Record the refresh date, data owner and definition of every KPI.