Data Cleaning Prompt Templates

Data Quality Issues Found:

1. DUPLICATES (High Impact): Row 1 and 3 are exact duplicates. Check for more by grouping on customer_id or email.

2. INCONSISTENT FORMATS (High Impact):
 - Names: Mixed case (John Smith vs jane doe)
 - Phone: Three different formats (555-1234, (555) 5678, 555.9999)
 - Dates: Two formats (2024-01-15 vs 01/15/2024)
 - Country: Three variations (USA, United States, US)

3. MISSING VALUES (Medium Impact): Row 4 has no email. Count total missing per column.

4. INVALID DATA (High Impact): Row 4 has date 2024-13-45 which is impossible (no month 13).

Cleaning Plan:
1. Remove exact duplicates
2. Standardize country to ISO codes
3. Parse and reformat all phone numbers
4. Convert all dates to YYYY-MM-DD, flag invalid dates for review
5. Normalize name capitalization
6. Decide on missing email handling (flag, exclude, or lookup)

For income data going into a segmentation model, here's my recommendation:

Don't use mean: Income is usually right-skewed (few high earners pull up the average). Mean would inflate missing values.

Median is safer but still loses information.

Best approach: Impute based on correlated columns if available (age, education, zip code are often good predictors).

Python code for median imputation:
```python
df['income'] = df['income'].fillna(df['income'].median())
```

Python code for group-based imputation (better):
```python
df['income'] = df.groupby('education_level')['income'].transform(
 lambda x: x.fillna(x.median())
)
```

Python code for model-based imputation (best but more complex):
```python
from sklearn.impute import KNNImputer
imputer = KNNImputer(n_neighbors=5)
df[['age', 'income', 'education_years']] = imputer.fit_transform(
 df[['age', 'income', 'education_years']]
)
```

At 12% missing, you have enough data to impute well. I'd avoid dropping rows since that's a lot of lost records.

```python
import re

def standardize_company_name(name):
 if pd.isna(name):
 return name
 
 # Convert to lowercase for processing
 name = str(name).lower().strip()
 
 # Remove common legal suffixes
 suffixes = r'\b(inc\.?|incorporated|llc|corp\.?|corporation|ltd\.?|limited|co\.?)\b'
 name = re.sub(suffixes, '', name, flags=re.IGNORECASE)
 
 # Remove 'the' prefix
 name = re.sub(r'^the\s+', '', name)
 
 # Remove punctuation
 name = re.sub(r'[^\w\s]', '', name)
 
 # Remove extra whitespace
 name = ' '.join(name.split())
 
 # Convert to title case
 name = name.title()
 
 return name

df['company_clean'] = df['company_name'].apply(standardize_company_name)
```

Results:
- 'ACME Inc.' → 'Acme'
- 'Acme, Inc' → 'Acme'
- 'The Acme Company' → 'Acme Company'

Tip: After standardizing, check unique values to catch edge cases. Run `df['company_clean'].value_counts()` and look for near-duplicates that might need fuzzy matching.

This needs fuzzy matching since the records aren't exact duplicates.

```python
import pandas as pd
from fuzzywuzzy import fuzz
from itertools import combinations

def find_fuzzy_duplicates(df, name_col='name', email_col='email', phone_col='phone', threshold=80):
 duplicates = []
 
 for i, j in combinations(df.index, 2):
 score = 0
 matches = 0
 
 # Compare names (fuzzy)
 if pd.notna(df.loc[i, name_col]) and pd.notna(df.loc[j, name_col]):
 name_score = fuzz.ratio(df.loc[i, name_col].lower(), df.loc[j, name_col].lower())
 if name_score >= threshold:
 matches += 1
 
 # Compare email (exact)
 if pd.notna(df.loc[i, email_col]) and pd.notna(df.loc[j, email_col]):
 if df.loc[i, email_col].lower() == df.loc[j, email_col].lower():
 matches += 1
 
 # Compare phone (exact, after cleaning)
 if pd.notna(df.loc[i, phone_col]) and pd.notna(df.loc[j, phone_col]):
 phone_i = re.sub(r'\D', '', str(df.loc[i, phone_col]))
 phone_j = re.sub(r'\D', '', str(df.loc[j, phone_col]))
 if phone_i == phone_j:
 matches += 1
 
 if matches >= 2: # At least 2 fields match
 duplicates.append((i, j))
 
 return duplicates

# Keep most complete record
def completeness_score(row):
 return row.notna().sum()

# For each duplicate pair, keep the one with more data
dupe_pairs = find_fuzzy_duplicates(df)
to_drop = []
for i, j in dupe_pairs:
 if completeness_score(df.loc[i]) >= completeness_score(df.loc[j]):
 to_drop.append(j)
 else:
 to_drop.append(i)

df_clean = df.drop(index=set(to_drop))
```

For large datasets, consider using recordlinkage or dedupe libraries instead of this pairwise approach.