20 Essential Prompts for Data Science: Pandas, Analysis & Visualization

I have a dataset loaded in a Pandas DataFrame called `df`. Generate a comprehensive data inspection report that includes:
- Shape and column data types
- Missing value counts and percentages
- Unique value counts for categorical columns
- Basic descriptive statistics for numerical columns (mean, median, std, min, max, quartiles)
- The first 3 and last 3 rows
Output the report as a formatted text summary. Do not use external libraries beyond Pandas.

# After loading your data
df = pd.read_csv('sales_data.csv')
# Run the prompt in your AI assistant
# The assistant returns a structured summary you can paste into your notebook or report.

Given a Pandas DataFrame `df`, write a function `auto_impute(df)` that:
- For numerical columns with <5% missing: fill with median
- For numerical columns with >=5% missing: fill using forward fill (method='ffill')
- For categorical columns with <5% missing: fill with mode
- For categorical columns with >=5% missing: create a new category "Unknown"
- Return the imputed DataFrame and a log of all changes
Use only Pandas. Document the function with a docstring.

# Apply to your data
cleaned_df, log = auto_impute(df)
print(log)  # See what was changed

The DataFrame `df` has a column 'date' of type datetime64. Write code to create these new features:
- day_of_week (Monday=0, Sunday=6)
- is_weekend (boolean)
- month
- quarter
- day_of_year
- week_of_year
- is_month_start, is_month_end
- days_since_last_record (difference from previous row in days, NaN for first row)
Add them to the original DataFrame. Use vectorized Pandas operations only (no loops).

# Assuming df already has a 'date' column
df['date'] = pd.to_datetime(df['date'])
# Run the prompt to get the feature engineering code

I have a DataFrame `df` with columns: 'category', 'sales', 'quantity', 'profit', 'region'. 
Write code to group by 'category' and 'region' and calculate:
- Total sales, average quantity, median profit
- Count of transactions
- Standard deviation of sales
- Min and max of profit
- The most common category within each group (use lambda)
Output the result as a clean DataFrame with multi-level columns. Sort by total sales descending.

summary = df.groupby(['category', 'region']).agg(...)
summary.sort_values(('sales', 'total'), ascending=False)

Write a function `cap_outliers(df, columns, method='iqr', multiplier=1.5)` that:
- For each specified column, calculates Q1, Q3, and IQR
- Identifies outliers as values below Q1 - multiplier*IQR or above Q3 + multiplier*IQR
- Caps outliers to the nearest non-outlier boundary
- Returns the capped DataFrame and a dictionary with outlier count per column
Use only Pandas and NumPy. Handle the case where all values are identical (IQR=0).

capped_df, outlier_report = cap_outliers(df, ['age', 'income'], multiplier=2.0)
print(outlier_report)

I have a DataFrame `df` with 12 numerical columns. Write Matplotlib code to create a 4x3 grid of histograms (one per column) with:
- 20 bins each
- Kernel density estimate overlay (using Seaborn's kdeplot)
- Column name as title for each subplot
- Shared x-axis within each row
- Figure size: 16x12 inches
- Tight layout
Use Seaborn for KDE overlay and Matplotlib for the rest.

# The AI generates the complete plotting code
# You just copy-paste into your notebook

Create a correlation heatmap for all numerical columns in `df` using Seaborn. Requirements:
- Colormap: 'coolwarm' centered at 0
- Annotate each cell with correlation coefficient rounded to 2 decimals
- Font size for annotations: 8
- Figure size: proportional to number of columns (e.g., 10x8 for 10 columns)
- Mask the upper triangle to avoid redundancy
- Add the title 'Feature Correlation Matrix'
- Rotate x and y labels 45 degrees

# Run the prompt to get the heatmap code
# This is especially useful before feature selection

The DataFrame `df` has columns: 'date' (datetime) and 'value' (float). Write Matplotlib code to create a time series plot with:
- Original data as a thin gray line (alpha=0.5)
- 7-day rolling average as a thick blue line
- 30-day rolling average as a dashed red line
- Shaded area between the rolling averages (alpha=0.1)
- Date on x-axis with monthly ticks and rotated labels
- Grid on y-axis only
- Title: 'Time Series with Moving Averages'
- Use Seaborn style 'darkgrid'

# Perfect for sales data, website traffic, or sensor readings

I want to compare the distribution of 'sales' across different 'region' categories. Create a Seaborn box plot with:
- X-axis: 'region' sorted by median sales descending
- Y-axis: 'sales'
- Add swarm plot overlay (small points) with alpha=0.3
- Color the boxes by region using a palette
- Remove top and right spines
- Add a horizontal line at the overall median sales value (dashed, red)
- Title: 'Sales Distribution by Region'

# Reveals outliers and distribution shape per category

Select 5 numerical columns from `df` that have the highest variance. Create a Seaborn pair plot for these columns with:
- Diagonal: KDE plots
- Off-diagonal: scatter plots with low alpha (0.4)
- Color points by the categorical column 'segment'
- Legend outside the plot
- Figure size: auto-calculated (3 inches per column)
- Title: 'Pairwise Relationships (Top 5 by Variance)'

# Identifies clusters, correlations, and non-linear relationships quickly

I have a DataFrame `df` with 20 columns (mix of numerical and categorical). Write a Python script that produces a complete EDA report as a Markdown string, including:
1. Dataset overview: shape, memory usage, column list with types
2. Missing value analysis: table with counts and percentages, visual bar chart
3. Univariate analysis: histograms for numerical, bar charts for categorical (top 10 categories)
4. Bivariate analysis: correlation heatmap, top 5 strongest correlations explained
5. Outlier detection: columns with outliers (IQR method), count per column
6. Key insights summary: 3-5 bullet points
Save the report as 'eda_report.md' and all figures as PNG files in a 'figures' folder.

# Run once to get a reusable EDA pipeline for any dataset

Generate a data quality report for `df` that checks:
- Duplicate rows (exact and near-duplicates with 90% similarity)
- Inconsistent categorical values (e.g., 'Yes', 'yes', 'Y')
- Out-of-range numerical values (negative ages, future dates)
- Skewness for numerical columns (report if >1 or <-1)
- Cardinality for categorical columns (report if >50 unique values)
Output as a structured JSON with issue type, column, severity (high/medium/low), and suggested fix.

# Automate data validation before model training

I have two groups in `df`: 'control' and 'treatment' in the column 'group'. The metric is 'conversion_rate' (continuous). Write code to:
- Check normality for both groups using Shapiro-Wilk test
- If normal: use independent t-test; else use Mann-Whitney U test
- Calculate effect size (Cohen's d for normal, rank-biserial for non-normal)
- Generate a plot: two histograms with KDE overlays, vertical lines at means
- Output a summary with test statistic, p-value, effect size, and interpretation (significant or not at alpha=0.05)

# Directly applicable to marketing experiments

Given `df` with target column 'target' (binary classification), perform feature selection using:
- Mutual information (from sklearn) for all numerical features
- Chi-squared test for categorical features (after encoding)
- Feature importance from a Random Forest classifier (100 trees)
- Correlation with target for numerical features
Output a ranked table with: feature name, method, score, p-value (if applicable), and recommendation (keep/drop).
Visualize top 10 features using a horizontal bar chart (one color per method).

# Reduces dimensionality before modeling

Write a function `optimize_memory(df)` that:
- For numerical columns: downcast int64 to int32/int16/int8 where possible, float64 to float32
- For object columns: convert to category if unique values < 50% of total rows
- For datetime columns: ensure proper datetime64 type
- Log original vs new memory usage (in MB) and percentage reduction
- Return the optimized DataFrame
Use Pandas' .info(memory_usage='deep') for measurement.

# Can reduce memory by 50-80% on large datasets

I have three DataFrames: `orders` (order_id, customer_id, date, amount), `customers` (customer_id, name, segment, signup_date), and `returns` (order_id, return_date, reason). Write code to:
- Left join orders with customers on customer_id
- Left join the result with returns on order_id
- For unmatched returns, fill return_date with NaT and reason with 'No Return'
- Create a flag column 'has_returned'
- Create a column 'days_to_return' (return_date - date) where applicable
- Output the final merged DataFrame and a summary of row counts before/after each join

# Typical e-commerce data consolidation task

For the time series DataFrame `df` with columns 'date' and 'value', calculate:
- 7-day rolling mean, standard deviation, min, max
- 30-day rolling mean
- Exponential weighted moving average (span=14)
- Rolling correlation with a 14-day window between 'value' and a second column 'indicator'
- Rolling z-score (current value minus rolling mean, divided by rolling std) with 30-day window
Output all as new columns with descriptive names. Handle NaN at the beginning of the series.

# For financial data or sensor anomaly detection

Create a pivot table from `df` with:
- Index: 'year', 'quarter'
- Columns: 'product_category'
- Values: 'revenue'
- Aggregation: sum, mean, and count (separate value columns)
- Fill missing values with 0
- Add row and column totals (margins=True)
- Sort columns by total revenue descending
- Output as a DataFrame with a clean multi-level index

# Perfect for business reports and dashboards

The DataFrame `df` has a column 'review_text'. Write a function `clean_text(text_series)` that:
- Converts to lowercase
- Removes punctuation, numbers, and extra whitespace
- Removes stopwords (use NLTK's English stopwords list)
- Lemmatizes words (use NLTK's WordNetLemmatizer)
- Returns a cleaned Series
Then write code to create a TF-IDF matrix (using sklearn) from the cleaned text, limiting to top 500 features. Add the top 5 TF-IDF terms per document as a new column.

# For sentiment analysis or topic modeling

Write a function `send_report(df, report_type='daily')` that:
- Computes key metrics: total sales, average order value, top 5 products, new customers
- Creates a summary plot (bar chart of top products)
- Formats the report as a Markdown string
- Sends the report via SMTP email (use environment variables for credentials)
- Saves a copy locally as 'report_{date}.md'
Use Python's built-in smtplib and email.mime modules. Handle errors gracefully (log to file).

# Automate end-of-day reporting