Python Data Analysis Prompt Templates

```python
import pandas as pd

# Sort by product and date for correct rolling calculation
df = df.sort_values(['product_id', 'date'])

# Calculate rolling average per product
df['sales_7d_avg'] = df.groupby('product_id')['sales'].transform(
 lambda x: x.rolling(window=7, min_periods=1).mean()
)
```

For 1M rows, this should complete in a few seconds. If you need faster performance:

```python
# Option 2: Use numba for speed on very large datasets
from pandas.api.types import is_numeric_dtype

df['sales_7d_avg'] = df.groupby('product_id')['sales'].transform(
 lambda x: x.rolling(7, min_periods=1).mean().values
)
```

Note: `min_periods=1` means the first 6 days will have averages based on fewer than 7 days. Set `min_periods=7` if you want NaN for those rows instead.

```python
import pandas as pd

# Load CSV, skip title row, handle encoding
df = pd.read_csv(
 'sales_data.csv',
 skiprows=1, # Skip the title row
 encoding='utf-8', # Try 'latin-1' if this fails
 thousands=',', # Handle comma separators in numbers
 dtype=str # Load everything as string first for cleaning
)

# Clean currency columns (remove $ and convert to float)
currency_cols = ['revenue', 'cost'] # Adjust to your column names
for col in currency_cols:
 df[col] = df[col].str.replace('[$,]', '', regex=True).astype(float)

# Parse mixed-format dates
df['date'] = pd.to_datetime(df['date'], format='mixed', dayfirst=False)

# Verify dtypes
print(df.dtypes)
print(df.head())

# Export to Parquet (much faster to read later)
df.to_parquet('sales_data.parquet', index=False)

# Next time, load with:
# df = pd.read_parquet('sales_data.parquet')
```

Tips:
- `format='mixed'` handles multiple date formats (Pandas 2.0+)
- Parquet files are typically 2-10x smaller than CSV and load much faster
- If you have really large files, consider `chunksize` parameter to load in batches

```python
import pandas as pd

# Calculate total for percentage later
total_sales = df['amount'].sum()

# Group and aggregate
result = df.groupby(['region', 'product_category']).agg(
 total_sales=('amount', 'sum'),
 avg_order_value=('amount', 'mean'),
 order_count=('order_id', 'count')
).reset_index()

# Add percentage of total
result['pct_of_total'] = (result['total_sales'] / total_sales * 100).round(2)

# Sort by total sales descending
result = result.sort_values('total_sales', ascending=False)

print(result)
```

Output columns: region, product_category, total_sales, avg_order_value, order_count, pct_of_total

Alternative using named aggregation (cleaner syntax):
```python
result = df.groupby(['region', 'product_category'], as_index=False).agg(
 total_sales=pd.NamedAgg(column='amount', aggfunc='sum'),
 avg_order_value=pd.NamedAgg(column='amount', aggfunc='mean'),
 order_count=pd.NamedAgg(column='order_id', aggfunc='count')
)
```

Note: `reset_index()` or `as_index=False` gives you a flat DataFrame instead of a MultiIndex.

```python
import pandas as pd

# First, fix the type mismatch
# Convert both to same type (string is safer for IDs)
orders['customer_id'] = orders['customer_id'].astype(str)
customers['customer_id'] = customers['customer_id'].astype(str)

# Handle the invalid customer_id = '0' case
# Option 1: Replace with NaN before merge
orders.loc[orders['customer_id'] == '0', 'customer_id'] = pd.NA

# Perform the left join
result = orders.merge(
 customers,
 on='customer_id',
 how='left',
 indicator=True # Adds column showing match status
)

# Check the merge results
print(result['_merge'].value_counts())
# both = matched
# left_only = order with no customer match
# right_only = (won't appear in left join)

# Optional: drop the indicator column if not needed
result = result.drop('_merge', axis=1)
```

Diagnostics to run:
```python
# Check for duplicate keys (can cause row explosion)
print(f"Orders with duplicate customer_id: {orders['customer_id'].duplicated().sum()}")
print(f"Customers with duplicate customer_id: {customers['customer_id'].duplicated().sum()}")

# If customers has duplicates, the merge will create multiple rows per order!
# Fix by deduplicating first:
# customers = customers.drop_duplicates(subset='customer_id', keep='first')
```

Expected result: 50K rows (same as orders, since left join preserves all left rows). If you get more rows, you have duplicate keys in the customers table.

**Problem**: `iterrows()` is almost always the wrong approach in Pandas. It processes one row at a time in Python, which is extremely slow.

**Solution**: Use vectorized operations.

```python
# Replace the loop with a single vectorized operation
df['new_col'] = df['price'] * df['quantity'] * (1 - df['discount'])
```

This will run in milliseconds, not minutes.

**Why it's faster**:
- Vectorized operations use optimized C code under the hood
- They process the entire column at once instead of row by row
- No Python interpreter overhead for each iteration

**General rules to avoid slow Pandas:**

1. **Never use iterrows() for calculations** - Use vectorized operations instead
2. **Avoid apply() when vectorized alternatives exist** - apply() is faster than iterrows but still slow
3. **Use appropriate dtypes** - category for low-cardinality strings, int32 instead of int64 when possible
4. **Filter early** - If you only need 10% of rows, filter before doing expensive operations

**If you must loop** (rare cases like complex state-dependent logic):
```python
# Use itertuples() instead of iterrows() - 10x faster
results = []
for row in df.itertuples():
 results.append(row.price * row.quantity * (1 - row.discount))
df['new_col'] = results
```

**For truly large datasets (10M+ rows)**, consider:
- Polars instead of Pandas (often 5-10x faster)
- Dask for parallel processing
- Loading only needed columns: `pd.read_csv('file.csv', usecols=['price', 'quantity', 'discount'])`