AI Prompts for Data Analysis: Cleaning, Exploration, Statistical

Published: July 2026 | 9 min read

Data analysis is the process of turning raw data into decisions. Whether you are working in Python, R, SQL, or Excel, AI can help you clean data faster, choose the right statistical tests, build visualizations that actually communicate, and find patterns you might have missed. These prompts are for analysts who want to go beyond basic summaries.

Data Cleaning & Preparation

1. Data Cleaning Strategy

Role: Data preparation specialist
Task: Create a data cleaning strategy for [dataset description].
Dataset: [Source, format, size, what it contains, known issues]
Strategy:
1. Data Profiling:
   - Row count, column count, memory usage
   - Data types per column (are they correct?)
   - Missing values per column (count and %)
   - Unique values per column (cardinality)
   - Min/max/mean/median for numeric columns
   - Value distribution for categorical columns
2. Missing Data Strategy (per column):
   - MCAR (missing completely at random): drop or impute
   - MAR (missing at random): impute with model
   - MNAR (missing not at random): flag and investigate
   - Method: drop, mean/median, forward fill, KNN, regression, MICE
   - Justification: why this method for this column
3. Outlier Handling (per numeric column):
   - Detection method (IQR, z-score, modified z-score, isolation forest)
   - Treatment: cap, remove, transform, or flag
   - Justification: is this a data error or a real extreme?
4. Type Conversion:
   - Dates as text -> datetime objects
   - Numbers as text -> numeric
   - Categories as text -> categorical dtype
   - Boolean as 0/1/text -> boolean
5. Deduplication:
   - What defines a duplicate? (all columns? business key?)
   - How to handle: drop, keep first, keep last, aggregate
6. Standardization:
   - Text: trim, case, standardize categories
   - Dates: timezone, format
   - Numeric: units, decimal places
7. Validation:
   - Referential integrity (do foreign keys match?)
   - Business rules (e.g., end date after start date)
   - Domain constraints (e.g., age between 0-120)
Output: Step-by-step cleaning plan with Python code structure.

2. Pandas Data Cleaning Script

Role: Python data analyst (pandas expert)
Task: Write a pandas script to clean [dataset].
Data source: [CSV path / Excel / SQL query / parquet]
Known issues: [List what you know — column types, missing patterns, anomalies]
Script structure:
1. Import and config (pandas, numpy, settings)
2. Load data (with dtype hints, parse_dates, na_values)
3. Profile (shape, dtypes, missing summary, sample)
4. Clean:
   a. Column names (snake_case, no spaces, no special chars)
   b. Type conversion (to_datetime, to_numeric, astype)
   c. Missing values (per-column strategy with fillna, dropna, or interpolation)
   d. Duplicates (identify and remove/merge)
   e. String cleaning (strip, case, replace patterns)
   f. Outlier treatment (cap/floor/flag)
   g. Derived columns (feature engineering if needed)
5. Validate (assertions: no nulls in required fields, ranges, uniqueness)
6. Save cleaned data (to CSV/parquet/database)
7. Summary report (before/after: row count, missing %, changes made)
Requirements:
- Each step as a function
- Logging with timestamps
- Config section (file paths, thresholds, rules)
- Idempotent (safe to run multiple times)
- Type hints on all functions
Output: Production-ready cleaning script.

3. Data Quality Assessment

Role: Data quality analyst
Task: Create a data quality assessment for [dataset].
Dataset: [Description, source, size, purpose]
Assessment dimensions:
1. Completeness:
   - Overall: (total cells filled / total cells) x 100
   - Per column: missing % for each
   - Critical fields: missing % for must-have columns
   - Row completeness: rows with all fields vs partial
2. Accuracy:
   - Known errors (compare to source of truth if available)
   - Type checks (numeric in numeric, dates in date)
   - Range checks (values within expected bounds)
   - Format checks (email, phone, postal code formats)
3. Consistency:
   - Cross-field rules (e.g., ship_date >= order_date)
   - Cross-table rules (e.g., order.customer_id exists in customers)
   - Historical consistency (does this match last months pattern?)
4. Timeliness:
   - Data freshness (when was it last updated?)
   - Delay (expected vs actual update frequency)
   - Late-arriving data (%)
5. Uniqueness:
   - Duplicate rate (by business key)
   - Near-duplicates (fuzzy match needed?)
6. Validity:
   - Schema compliance (types, constraints)
   - Domain values (categorical columns — any unexpected values?)
   - Business rule compliance
7. Integrity:
   - Referential integrity (foreign keys)
   - Orphan records (child without parent)
For each dimension: metric, target, actual, score (1-5), action if below target
Output: Data quality scorecard with overall grade and remediation plan.

Statistical Analysis

4. Statistical Test Selector

Role: Statistics consultant
Task: Recommend the right statistical test for this analysis.
Research question: [What are you trying to determine?]
Data: [Types of variables involved — continuous, categorical, binary, ordinal]
Sample: [Size, independent vs paired, how many groups]
Assumptions to check:
1. Normality (Shapiro-Wilk, Q-Q plot — if n < 30, use non-parametric)
2. Homogeneity of variance (Levene test)
3. Independence of observations
4. Equal sample sizes (if not, use Welch correction)
Decision tree:
- 1 continuous outcome, 1 categorical predictor (2 groups): t-test (or Mann-Whitney)
- 1 continuous outcome, 1 categorical predictor (3+ groups): ANOVA (or Kruskal-Wallis)
- 1 continuous outcome, 1 continuous predictor: correlation (Pearson or Spearman)
- 1 continuous outcome, 2+ predictors: regression (linear, logistic, etc.)
- 1 categorical outcome, 1 categorical predictor: chi-square (or Fisher exact)
- Paired/repeated measures: paired t-test or repeated measures ANOVA
Recommendation:
1. Test name
2. Why this test (based on the decision tree)
3. Assumptions and how to verify them
4. Effect size measure (which one, how to interpret)
5. Power analysis (is sample size adequate? post-hoc power)
6. How to run (Python: scipy/statsmodels, R: which function)
7. How to interpret results (what p-value, effect size, CI mean)
8. Alternative if assumptions are violated
Format: Statistical test recommendation with implementation guide.

5. Regression Analysis Plan

Role: Statistical modeling analyst
Task: Create a regression analysis plan for [question/dataset].
Question: [What are you predicting? What are you trying to understand?]
Dependent variable: [Name, type (continuous/binary/count), distribution]
Independent variables: [List with type and expected relationship]
Plan:
1. Model selection:
   - Linear regression (continuous outcome, linear relationships)
   - Logistic regression (binary outcome)
   - Poisson/negative binomial (count outcome)
   - Cox proportional hazards (time-to-event)
   - Multilevel/mixed effects (clustered/nested data)
2. Feature preparation:
   - Encoding: one-hot, label, target, embedding
   - Scaling: standardization, normalization, log transform
   - Interaction terms (which interactions to test and why)
   - Polynomial terms (if non-linear relationship suspected)
3. Assumption checking:
   - Linearity (partial regression plots)
   - Independence (study design)
   - Homoscedasticity (residual plot)
   - Normality of residuals (Q-Q plot)
   - Multicollinearity (VIF, correlation matrix)
   - Influential points (Cook distance, leverage)
4. Model building:
   - Entry method (hierarchical, stepwise, all-at-once)
   - Variable selection (LASSO, ridge, or domain knowledge)
   - Train/test split or cross-validation strategy
5. Evaluation:
   - Goodness of fit (R-squared, AIC, BIC)
   - Predictive accuracy (RMSE, MAE, MAPE for linear; AUC, precision/recall for logistic)
   - Residual analysis (patterns in residuals?)
   - Validation (holdout, k-fold, or bootstrap)
6. Interpretation:
   - Coefficients (direction, magnitude, significance)
   - Confidence intervals
   - Odds ratios (for logistic)
   - Practical significance vs statistical significance
Output: Regression analysis plan ready for execution.

6. Time Series Analysis Framework

Role: Time series analysis specialist
Task: Create a time series analysis framework for [data].
Series: [What it measures, frequency (daily/weekly/monthly), length, any known patterns]
Analysis framework:
1. Decomposition:
   - Trend (linear, polynomial, moving average)
   - Seasonality (additive or multiplicative, period)
   - Residual/irregular component
   - Method: classical, STL, or X-13ARIMA-SEATS
2. Stationarity:
   - Visual check (does it look stationary?)
   - ADF test (Augmented Dickey-Fuller)
   - KPSS test
   - If non-stationary: differencing (how many orders?), log transform
3. Autocorrelation:
   - ACF plot (autocorrelation function)
   - PACF plot (partial autocorrelation)
   - Identify AR and MA orders
4. Modeling:
   - ARIMA/SARIMA (if traditional)
   - Prophet (if multiple seasonality or holidays)
   - Exponential smoothing (if simple pattern)
   - LSTM/ML (if complex non-linear)
   - VAR (if multivariate)
5. Forecasting:
   - Train/test split (time-based, no random split)
   - Forecast horizon (how far ahead?)
   - Prediction intervals (not just point estimates)
   - Forecast evaluation (MAPE, RMSE, MASE)
6. Anomaly detection:
   - Residual analysis (outliers in residuals)
   - Change point detection (structural breaks)
   - Seasonal anomaly (abnormal for the season)
7. Cross-validation:
   - Time series CV (expanding window, rolling window)
   - Do NOT use random k-fold (breaks temporal order)
Output: Time series analysis plan with code structure (Python: statsmodels, Prophet).

Visualization & Communication

7. Chart Selection Guide

Role: Data visualization specialist
Task: Recommend the best chart type for [describe what you want to show].
Data: [What variables, how many, what types]
Message: [What insight are you trying to communicate?]
Audience: [Who will see this? technical or non-technical?]
Comparison scenarios:
1. Comparison: Bar chart (vertical for few categories, horizontal for many/long labels)
2. Trend over time: Line chart (continuous), bar chart (discrete periods)
3. Composition: Stacked bar (few periods), area chart (many periods), donut (2-4 parts)
4. Distribution: Histogram (single variable), box plot (comparison), violin (density)
5. Relationship: Scatter plot (two continuous), bubble (three variables), heatmap (matrix)
6. Ranking: Horizontal bar chart (sorted)
7. Part-to-whole: Stacked bar (not pie for >4 segments)
8. Geographic: Choropleth (intensity), bubble map (volume), tile grid
Recommendation:
1. Primary chart (what to use)
2. Alternative chart (if primary does not work)
3. Design notes:
   - Color: use semantic colors (good/bad, high/low), avoid rainbow
   - Labels: direct labels on chart if < 8 series, legend if more
   - Axes: start at 0 for bar charts, label units, avoid dual-axis
   - Annotations: call out the key insight with text
4. What NOT to use (and why — e.g., no pie chart for 7 categories)
Format: Visualization recommendation with design guidelines.

8. Dashboard Data Story

Role: Data storytelling consultant
Task: Help me tell a story with this dashboard.
Dashboard: [Describe what metrics and charts are on the dashboard]
Audience: [Who will use it, what decisions they make]
Story framework:
1. Headline metric (the one number everyone should look at first)
2. Supporting metrics (3-5 numbers that explain the headline)
3. Trend context (is this good or bad? compared to what?)
4. Drill-down path (how to explore from headline to root cause)
5. Narrative structure:
   - The "So What?" (what does this dashboard tell us?)
   - The "Now What?" (what should we do about it?)
6. Annotation plan:
   - What to annotate on the dashboard (key callouts)
   - When to add commentary (monthly summary? weekly note?)
7. Alert design:
   - What triggers a story (metric crosses threshold)
   - Who gets the story (which audience)
   - How it is delivered (email, Slack, annotation)
Output: Dashboard storytelling guide with annotation examples and narrative template.

9. Analysis Report Section from Findings

Role: Data analyst writing a report section
Task: Write an analysis section from these findings.
Findings: [Paste key numbers, comparisons, patterns, anomalies]
Context: [What was analyzed, why, what period]
Audience: [Who will read this — what do they need to know?]
Format:
1. Subheading (descriptive: "[What the data shows]")
2. Opening finding (the most important thing — lead with it)
3. Supporting data (2-3 key numbers with context)
4. Comparison (vs last period, vs benchmark, vs target — something to compare to)
5. Interpretation (what does this mean for the business?)
6. Limitations (what this data does NOT tell us)
7. Recommendation (what to do based on this finding)
8. Chart reference ("As shown in Figure X...")
Rules:
- Lead with the insight, not the methodology
- Numbers in context (not "1,234" but "up 18% from last quarter")
- No data dump (if there are 50 metrics, pick the 3 that matter)
- End with action, not observation
Length: 200-300 words per section.

Advanced Analysis

10. Customer Segmentation Analysis

Role: Customer analytics specialist
Task: Design a customer segmentation analysis for [customer dataset].
Data available: [Demographics, behavior, transactions, engagement, tenure]
Business question: [What decisions will segmentation inform?]
Approach:
1. Segmentation methods to compare:
   - RFM (Recency, Frequency, Monetary — simple, actionable)
   - K-means (if many variables, need to scale)
   - Hierarchical clustering (if we want nested segments)
   - Latent class (if mix of variable types)
   - Manual rules (if domain knowledge suggests clear segments)
2. Feature selection:
   - Which variables to include (and which to exclude)
   - Transformation needed (log, standardize, one-hot)
   - Correlation check (remove highly correlated features)
3. Cluster evaluation:
   - Elbow method (for K-means)
   - Silhouette score
   - Calinski-Harabasz index
   - Business interpretability (can we name and act on these segments?)
4. Segment profiling:
   - Size (how many in each segment?)
   - Key characteristics (what defines each segment?)
   - Behavior (how do they differ in actions?)
   - Value (revenue, LTV, margin per segment)
   - Name and description (human-readable segment labels)
5. Action plan:
   - Per-segment strategy (acquire, grow, retain, win-back, ignore)
   - Targeting (which segments to prioritize)
   - Messaging (what resonates with each segment)
   - Measurement (how to track segment-level performance)
Output: Segmentation analysis plan with implementation code structure.

11. Funnel Analysis Template

Role: Product/Marketing analytics analyst
Task: Design a funnel analysis for [product/campaign/journey].
Funnel stages: [List each step from awareness to conversion]
Data available: [What events are tracked, what identifiers exist]
Analysis:
1. Stage-by-stage:
   - Volume (how many at each stage)
   - Conversion rate (stage N / stage N-1)
   - Drop-off (how many left at each stage, where to)
   - Time to convert (median, p25, p75 — per stage and overall)
2. Segmentation:
   - By channel (organic, paid, referral, direct)
   - By device (desktop, mobile, tablet)
   - By geography (country, region)
   - By cohort (when did they enter the funnel?)
   - By user attributes (new vs returning, plan, segment)
3. Bottleneck identification:
   - Which stage has the lowest conversion rate?
   - Is it consistent across segments or specific to one?
   - Is it a specific step or a gradual decline?
4. Hypothesis generation:
   - Why is the drop-off happening at [stage]? (3-5 hypotheses)
   - What data would confirm each hypothesis?
   - What experiment could fix it?
5. Benchmarking:
   - Industry benchmark (what is typical for this funnel type?)
   - Historical benchmark (has our funnel gotten better or worse?)
6. Recommendations:
   - Quick wins (which fix is easiest?)
   - Strategic (which needs product/marketing change?)
   - Measurement (how to track improvement)
Output: Funnel analysis framework with SQL/query structure and visualization plan.

12. Analysis Documentation Template

Role: Analytics documentation specialist
Task: Create an analysis documentation template for [team/project].
Purpose: Ensure analyses are reproducible, understandable, and reusable.
Template sections:
1. Title and metadata (analyst, date, project, status: draft/final)
2. Business Question (what was asked, who asked, why)
3. Data Sources (tables, columns, time range, filters applied)
4. Methodology (what analysis was done, why this approach, alternatives)
5. Assumptions (what was assumed, what would change if wrong)
6. Code (Python/R/SQL — with comments, version-controlled)
7. Results (key findings, with tables and charts)
8. Limitations (what the analysis does NOT cover, caveats)
9. Conclusions (what we learned, with confidence level)
10. Recommendations (what to do, with expected impact)
11. Reproducibility (how to re-run, dependencies, environment)
12. Peer Review (reviewer, date, comments, sign-off)
13. Change Log (what changed, when, why)
For each section:
1. What goes here (1-2 sentences)
2. Common mistakes (what to avoid)
3. Example (1-2 sentences of good content)
Output: Analysis documentation template + example filled in.
Rule: An analysis that cannot be reproduced is not finished.

Best Practices

For more data content, see our AI prompts for data analysts and AI prompts for Excel automation.