Your Data Science Copilot: 15 Battle-Tested Prompts to Automate Pandas, Python & Jupyter Workflows
You've probably been there: staring at a messy CSV, writing the same data-cleaning boilerplate for the hundredth time, or googling 'how to pivot a DataFrame' yet again. What if you could just describe what you want in plain English and let an AI assistant generate the code, explain the logic, and even catch subtle bugs? That's not sci-fi — it's the reality of modern data work. This guide gives you 15 concrete, copy-paste-ready prompts that will turn your Jupyter notebook into a powerhouse of productivity. Whether you're a data analyst, a scientist, or a Python developer, these prompts will save you hours of manual work and elevate the quality of your analysis.
Why AI-Powered Prompts Are a Game-Changer for Data Work
Data analysis is iterative: you clean, explore, model, and visualize. Each step involves coding patterns that are often repetitive but require precision. AI models like GPT-4o, Claude 3.5 Sonnet, and Llama 3.1 405B are trained on vast amounts of public code and documentation, making them surprisingly good at generating idiomatic Pandas code, suggesting optimal approaches, and even spotting edge cases. According to a 2024 GitHub survey, 92% of developers use AI coding tools, and a large share report increased productivity (source: GitHub, “The State of the Developer Experience,” 2024). By crafting effective prompts, you tap into this power to automate the boring parts and focus on the insights.
How to Get the Most Out of These Prompts
Before diving in, here are a few tips:
1. Be specific: Include column names, data types, and the desired output format.
2. Provide context: Mention the size of your dataset (e.g., “50k rows”) and any quirks (e.g., “dates are strings”).
3. Iterate: Don't expect perfection on the first try. Use the AI's response as a starting point and refine.
4. Always review: AI can hallucinate or produce incorrect code. Always test on a sample.
Now, let's dive into the prompts.
1. Data Profiling: Get a Quick Overview of Any Dataset
Prompt:
You are a data scientist. I have a Pandas DataFrame named 'df' loaded from 'sales_data.csv'. Write Python code to perform a comprehensive data profiling, including:
- Display the shape, column names, and data types.
- Show summary statistics for numerical and categorical columns.
- Count missing values per column and calculate the percentage.
- Identify duplicate rows.
- List unique values for categorical columns with fewer than 10 unique values.
- Suggest potential data quality issues based on the output.
Why it works: This prompt gives the AI a clear role, specifies the exact DataFrame, and asks for both code and interpretation. It’s perfect for the initial exploration of a new dataset.
Example output snippet:
# Code generated by AI
print(df.shape)
print(df.info())
print(df.describe(include='all'))
print('Missing values:\n', df.isnull().sum())
print('Duplicates:', df.duplicated().sum())
for col in df.select_dtypes(include='object').columns:
if df[col].nunique() < 10:
print(f'{col} unique values:', df[col].unique())
2. Data Cleaning: Handle Missing Values Intelligently
Prompt:
Given the DataFrame 'df' with columns 'age', 'income', 'gender', and 'purchase_amount', write Python code to handle missing values:
- For 'age': fill with the median.
- For 'income': use interpolation (linear) because it's a time series.
- For 'gender': fill with the mode.
- For 'purchase_amount': drop rows where it's missing.
Explain why each method is appropriate.
Why it works: This prompt demonstrates how to combine domain knowledge with AI. It asks for an explanation, which helps you learn and validates the approach.
Example output snippet:
# Code generated by AI
df['age'] = df['age'].fillna(df['age'].median())
df['income'] = df['income'].interpolate(method='linear')
df['gender'] = df['gender'].fillna(df['gender'].mode()[0])
df = df.dropna(subset=['purchase_amount'])
3. Data Transformation: From Messy to Tidy with melt and pivot
Prompt:
I have a DataFrame 'df' with columns: 'id', 'product_A_sales', 'product_B_sales', 'product_C_sales'. I want to convert it to long format using `pd.melt` so that each product has its own row. Write the code and also show how to pivot it back using `pivot_table`.
Why it works: This prompt is specific about the desired transformation and asks for the reverse operation, ensuring you understand both directions.
Example output snippet:
# Long format
df_long = pd.melt(df, id_vars=['id'], value_vars=['product_A_sales', 'product_B_sales', 'product_C_sales'],
var_name='product', value_name='sales')
# Back to wide
df_wide = df_long.pivot_table(index='id', columns='product', values='sales').reset_index()
4. Feature Engineering: Create New Columns from Existing Data
Prompt:
Create a new column 'age_group' in DataFrame 'df' that categorizes people into 'Youth' (age < 18), 'Adult' (18-64), and 'Senior' (65+). Also create a 'total_spend' column that sums 'product_A_spend', 'product_B_spend', and 'product_C_spend'. Provide the Pandas code and explain the logic.
Why it works: This prompt asks for two common feature engineering tasks, clearly defining the bins and the new columns. It's a great template for any custom feature creation.
Example output snippet:
import pandas as pd
bins = [0, 18, 65, 100]
labels = ['Youth', 'Adult', 'Senior']
df['age_group'] = pd.cut(df['age'], bins=bins, labels=labels, right=False)
df['total_spend'] = df[['product_A_spend', 'product_B_spend', 'product_C_spend']].sum(axis=1)
5. Grouped Analysis: Insights from GroupBy Operations
Prompt:
Using the 'df' DataFrame with columns 'region', 'sales', 'profit', and 'date', write Python code to:
- Group by 'region' and compute total sales, average profit, and count of transactions.
- Find the region with the highest total sales.
- Show the monthly sales trend for the top region using a line plot.
Why it works: This prompt combines aggregation, ranking, and visualization, which are core to EDA. It also asks for a plot, making the output more actionable.
Example output snippet:
# Groupby and aggregate
region_stats = df.groupby('region').agg(total_sales=('sales', 'sum'),
avg_profit=('profit', 'mean'),
transaction_count=('sales', 'count')).reset_index()
top_region = region_stats.loc[region_stats['total_sales'].idxmax(), 'region']
# Monthly trend for top region
top_region_df = df[df['region'] == top_region]
top_region_df['month'] = pd.to_datetime(top_region_df['date']).dt.to_period('M')
monthly_trend = top_region_df.groupby('month')['sales'].sum().reset_index()
monthly_trend.plot(x='month', y='sales')
6. Time Series Resampling: From Daily to Weekly Aggregates
Prompt:
I have a DataFrame 'df' with a datetime index and a column 'value'. Write code to resample this data to weekly frequency, calculating the mean for each week. Also, show how to forward-fill missing weeks and how to compute a rolling 4-week average. Provide the code and a brief explanation.
Why it works: Time series resampling is a common but tricky task. This prompt covers three variations: aggregation, filling, and rolling windows, which are all essential.
Example output snippet:
# Weekly mean
df_weekly = df.resample('W').mean()
# Forward fill
df_weekly_ffill = df_weekly.ffill()
# Rolling 4-week average
df_weekly['rolling_avg'] = df_weekly['value'].rolling(window=4).mean()
7. Merging DataFrames: Joins Without the Headache
Prompt:
I have two DataFrames: 'df1' with columns 'customer_id', 'name', and 'city'; 'df2' with columns 'customer_id', 'purchase_date', and 'amount'. Write Python code to perform a left join on 'customer_id' to get all customers with their purchases, including those with no purchases. Show how to handle duplicate keys and discuss the difference between left, inner, and outer joins.
Why it works: This prompt covers the most common join types and asks for a discussion, which reinforces understanding.
Example output snippet:
merged_df = pd.merge(df1, df2, on='customer_id', how='left')
# To handle duplicates, you might need to aggregate first
# e.g., df2_agg = df2.groupby('customer_id').agg(total_amount=('amount', 'sum')).reset_index()
8. Handling Outliers: Detect and Treat with IQR and Z-score
Prompt:
For the 'sales' column in DataFrame 'df', write Python code to detect outliers using both the IQR method and the Z-score method (threshold > 3). Then, create a new DataFrame 'df_no_outliers' that removes these outliers. Compare the number of rows before and after. Provide the code and a brief explanation of each method.
Why it works: Outlier detection is a critical step in data cleaning. This prompt asks for two methods, which gives the AI a chance to show best practices and lets you choose the most suitable.
Example output snippet:
# IQR method
Q1 = df['sales'].quantile(0.25)
Q3 = df['sales'].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
outliers_iqr = df[(df['sales'] < lower_bound) | (df['sales'] > upper_bound)]
# Z-score method
from scipy import stats
z_scores = stats.zscore(df['sales'])
abs_z_scores = np.abs(z_scores)
outliers_z = df[abs_z_scores > 3]
# Combined removal
df_no_outliers = df[abs_z_scores <= 3]
print(f'Original: {len(df)}, After IQR: {len(df) - len(outliers_iqr)}, After Z-score: {len(df) - len(outliers_z)}')
9. String Operations: Clean and Extract Text Data
Prompt:
The 'description' column in DataFrame 'df' contains messy text: leading/trailing spaces, inconsistent capitalization, and embedded URLs. Write Python code to:
- Strip whitespace and convert to lowercase.
- Remove all URLs using regex.
- Replace multiple spaces with a single space.
- Extract any email addresses into a new column 'email'.
Why it works: This prompt addresses common text cleaning tasks and uses regex, which is a typical pain point. It also asks for extraction, which is a valuable skill.
Example output snippet:
import re
# Clean text
df['description'] = df['description'].str.strip().str.lower()
df['description'] = df['description'].str.replace(r'http\S+', '', regex=True)
df['description'] = df['description'].str.replace(r'\s+', ' ', regex=True)
# Extract emails
df['email'] = df['description'].str.extract(r'([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})')
10. Apply Custom Functions: Vectorize Your Code
Prompt:
Write Python code to apply a custom function to the 'revenue' column in DataFrame 'df' that categorizes revenue into 'low' (< 1000), 'medium' (1000-5000), and 'high' (> 5000) using `apply` with a lambda function. Also show how to do the same using `pd.cut` for comparison. Discuss performance implications.
Why it works: This prompt contrasts apply with vectorized operations, teaching a key lesson in Pandas efficiency.
Example output snippet:
# Using apply with lambda
def categorize_revenue(x):
if x < 1000:
return 'low'
elif 1000 <= x <= 5000:
return 'medium'
else:
return 'high'
df['revenue_category_apply'] = df['revenue'].apply(categorize_revenue)
# Using pd.cut
bins = [0, 1000, 5000, float('inf')]
labels = ['low', 'medium', 'high']
df['revenue_category_cut'] = pd.cut(df['revenue'], bins=bins, labels=labels)
# Note: pd.cut is faster for large datasets.
11. Visualization with Matplotlib and Seaborn: Generate Insights at a Glance
Prompt:
For DataFrame 'df' with columns 'age', 'income', 'purchase_amount', and 'gender', create a set of plots to explore relationships:
- Histogram of 'age'.
- Boxplot of 'income' by 'gender'.
- Scatter plot of 'income' vs 'purchase_amount' colored by 'gender'.
- Correlation heatmap for numerical columns.
Use Seaborn and Matplotlib, with appropriate titles and labels. Provide the code.
Why it works: This prompt generates a comprehensive set of exploratory plots, covering univariate, bivariate, and multivariate analysis.
Example output snippet:
import matplotlib.pyplot as plt
import seaborn as sns
plt.figure(figsize=(12, 8))
# Histogram
plt.subplot(2, 2, 1)
sns.histplot(df['age'], bins=20)
# Boxplot
plt.subplot(2, 2, 2)
sns.boxplot(x='gender', y='income', data=df)
# Scatter
plt.subplot(2, 2, 3)
sns.scatterplot(x='income', y='purchase_amount', hue='gender', data=df)
# Heatmap
plt.subplot(2, 2, 4)
sns.heatmap(df[['age', 'income', 'purchase_amount']].corr(), annot=True, cmap='coolwarm')
plt.tight_layout()
plt.show()
12. Statistical Tests: Automate Hypothesis Testing
Prompt:
I want to test if there is a significant difference in 'purchase_amount' between two groups 'male' and 'female' in DataFrame 'df'. Write Python code to perform an independent t-test using scipy.stats, and also a Mann-Whitney U test as a non-parametric alternative. Print the test statistics, p-values, and interpret the results (assume alpha = 0.05). Explain when to use each test.
Why it works: This prompt covers two common hypothesis tests and asks for interpretation, which is crucial for correct usage.
Example output snippet:
from scipy.stats import ttest_ind, mannwhitneyu
group_male = df[df['gender'] == 'male']['purchase_amount']
group_female = df[df['gender'] == 'female']['purchase_amount']
# T-test
t_stat, p_val_t = ttest_ind(group_male, group_female)
# Mann-Whitney U
u_stat, p_val_u = mannwhitneyu(group_male, group_female, alternative='two-sided')
print(f'T-test: t={t_stat}, p={p_val_t}')
print(f'Mann-Whitney: U={u_stat}, p={p_val_u}')
if p_val_t < 0.05:
print('Reject null: significant difference in means')
else:
print('Fail to reject null')
13. Machine Learning: Quick Model Building and Evaluation
Prompt:
Using the 'df' DataFrame with target column 'purchase_amount', build a linear regression model to predict it. Split the data into training and test sets (80/20). Scale the features using StandardScaler. Train a LinearRegression model and evaluate using R-squared, MAE, and RMSE. Print the feature importances if available. Provide the full code.
Why it works: This prompt covers a typical ML workflow: preprocessing, modeling, and evaluation. It's a great starting point for predictive analysis.
Example output snippet:
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score, mean_absolute_error, mean_squared_error
import numpy as np
X = df.drop('purchase_amount', axis=1)
y = df['purchase_amount']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)
model = LinearRegression()
model.fit(X_train_scaled, y_train)
y_pred = model.predict(X_test_scaled)
print('R2:', r2_score(y_test, y_pred))
print('MAE:', mean_absolute_error(y_test, y_pred))
print('RMSE:', np.sqrt(mean_squared_error(y_test, y_pred)))
print('Coefficients:', model.coef_)
14. Exporting Results: Save Processed Data and Generate Reports
Prompt:
After all the cleaning and feature engineering, I have a final DataFrame 'df_clean'. Write code to:
- Save it to a CSV file 'cleaned_data.csv' without the index.
- Save it to an Excel file with two sheets: 'data' and 'summary', where 'summary' contains the mean, median, and standard deviation of all numerical columns.
- Generate a text summary report that includes the row count, column names, and any key statistics, and save it as 'report.txt'.
Why it works: This prompt covers data export and report generation, which are essential for sharing results.
Example output snippet:
df_clean.to_csv('cleaned_data.csv', index=False)
with pd.ExcelWriter('output.xlsx') as writer:
df_clean.to_excel(writer, sheet_name='data', index=False)
summary = df_clean.describe().loc[['mean', '50%', 'std']]
summary.to_excel(writer, sheet_name='summary')
# Text report
with open('report.txt', 'w') as f:
f.write(f'Row count: {len(df_clean)}\n')
f.write(f'Columns: {list(df_clean.columns)}\n')
f.write(f'Mean values:\n{df_clean.mean()}\n')
15. Debugging: Fix Errors in Your Pandas Code
Prompt:
I'm getting a KeyError when trying to access column 'revenue' in my DataFrame 'df'. I thought it existed. Write Python code to check if the column exists, list all column names, and suggest possible reasons for the error (e.g., typos, leading/trailing spaces). Also show how to rename columns if needed.
Why it works: Debugging is part of everyday work. This prompt teaches how to systematically diagnose and fix a common error.
Example output snippet:
# Check if column exists
if 'revenue' in df.columns:
print('Column exists')
else:
print('Column not found. Available columns:', list(df.columns))
# Rename if similar
# df.rename(columns={'Revenue': 'revenue'}, inplace=True)
16. Jupyter Notebook Automation: Turning Prompts into a Pipeline
Prompt:
I want to automate the entire data processing pipeline for my CSV files. Write Python code that:
- Reads all CSV files in a directory.
- Performs basic cleaning (remove duplicates, fill missing values with column mean).
- Adds a 'source_file' column.
- Concatenates them into one DataFrame.
- Saves the combined data to a single CSV file.
Include error handling and a progress print statement.
Why it works: This prompt creates a reusable script that saves time on repetitive tasks. It also demonstrates how to structure code for maintainability.
Example output snippet:
import pandas as pd
import glob
import os
all_files = glob.glob('data/*.csv')
df_list = []
for file in all_files:
print(f'Processing {file}')
df = pd.read_csv(file)
df.drop_duplicates(inplace=True)
df = df.fillna(df.mean(numeric_only=True))
df['source_file'] = os.path.basename(file)
df_list.append(df)
combined = pd.concat(df_list, ignore_index=True)
combined.to_csv('combined_data.csv', index=False)
17. SQL and Pandas: Bridging the Gap
Prompt:
I have a SQLite database with a table 'sales' containing columns 'date', 'region', 'product', 'revenue'. Write Python code to connect to the database, run a query that gets monthly revenue by region, and load the result into a Pandas DataFrame. Then, create a pivot table showing revenue by region and month.
Why it works: Integrating SQL and Pandas is common in real-world data analysis. This prompt covers both querying and reshaping.
Example output snippet:
import sqlite3
import pandas as pd
conn = sqlite3.connect('mydb.db')
query = '''
SELECT strftime('%Y-%m', date) as month, region, SUM(revenue) as total_revenue
FROM sales
GROUP BY month, region
'''
df = pd.read_sql_query(query, conn)
pivot = df.pivot_table(index='region', columns='month', values='total_revenue')
conn.close()
18. Best Practices and Code Review: Get a Professional Review
Prompt:
I've written the following code to clean data and compute some metrics. Please review it for potential bugs, inefficiencies, and best practices. Suggest improvements with specific code examples.
```python
df = pd.read_csv('data.csv')
df = df[df['age'] > 0]
df['total'] = df['a'] + df['b']
result = df.groupby('category')['total'].mean()
print(result)
**Why it works:** This prompt leverages the AI as a code reviewer, helping you learn best practices and catch subtle issues.
**Example output snippet:**
```python
# Potential improvements:
# 1. Use .query() for readability: df = df.query('age > 0')
# 2. Use .eval() for efficient arithmetic: df['total'] = df.eval('a + b')
# 3. Consider using .agg() for multiple metrics.
# 4. Add error handling for missing columns.
Putting It All Together: Your AI-Powered Data Workflow
These 18 prompts are just the beginning. The real power comes from combining them. For instance, you can start with the profiling prompt, then use the cleaning prompt, then the feature engineering prompt, and finally the modeling prompt. Each step builds on the previous, creating a streamlined pipeline. As you use these prompts, you'll naturally develop your own variations tailored to your specific datasets and problems.
Remember, the key to effective AI collaboration is clarity and context. The more precise you are, the better the output. And always validate the results — AI is a tool, not a replacement for your expertise.
So, go ahead: open your Jupyter notebook, copy the prompt that fits your current task, and watch your productivity soar. Happy analyzing!
Comments