import pandas as pd
import numpy as np
import openml
import warnings
warnings.filterwarnings('ignore')

# Load a real customer satisfaction survey dataset
dataset = openml.datasets.get_dataset(42178)
df, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df.shape)
print(df.head(3))

# Check for duplicate entries based on 'customerID' if available
if 'customerID' in df.columns:
    duplicates = df.duplicated(subset=['customerID'])
    print(duplicates.value_counts())
else:
    print('No customerID column available, proceeding differently.')

# Remove exact duplicate rows (all columns identical)
df_no_exact_dupes = df.drop_duplicates()
print('Original shape:', df.shape)
print('After removing exact duplicates:', df_no_exact_dupes.shape)

# Example of keeping the first response from each duplicate customer (if customerID exists)
if 'customerID' in df.columns:
    df_unique_customer = df.drop_duplicates(subset=['customerID'], keep='first')
    print(df_unique_customer.shape)
else:
    print('No customerID field; skipping.')

# Check for duplicated rows based on core demographic features
core_cols = ['gender', 'SeniorCitizen', 'Partner', 'Dependents', 'tenure']
shared_cols = [col for col in core_cols if col in df.columns]
dupes_by_demo = df.duplicated(subset=shared_cols)
print(f'Duplicate rows based on core: {dupes_by_demo.sum()}')

# Load a synthetic NPS survey dataset with possible duplicates
np.random.seed(42)
df_nps = pd.DataFrame({
    'CustomerID': np.random.randint(1, 120, 200),
    'Age': np.random.randint(18, 65, 200),
    'Region': np.random.choice(['North','South','East','West'], 200),
    'NPS_Score': np.random.randint(0, 11, 200),
})
print(df_nps.head(3))

# Find customers who submitted more than one NPS response
dupes_nps = df_nps[df_nps.duplicated(subset=['CustomerID'], keep=False)]
print(f'Number of customers with multiple entries: {dupes_nps.CustomerID.nunique()}')

# Remove duplicate customer responses in NPS, keeping the latest response
df_nps['Idx'] = df_nps.index
df_nps_latest = df_nps.sort_values('Idx').drop_duplicates(subset=['CustomerID'], keep='last')
print(f'Shape after removal: {df_nps_latest.shape}')

# Explore the impact of removing duplicates on NPS calculation
original_nps = ((df_nps['NPS_Score']>=9).mean() - (df_nps['NPS_Score']<=6).mean())*100
clean_nps = ((df_nps_latest['NPS_Score']>=9).mean() - (df_nps_latest['NPS_Score']<=6).mean())*100
print(f'NPS with duplicates: {original_nps:.1f}')
print(f'NPS after removing duplicates: {clean_nps:.1f}')

# Load a marketing campaign response dataset for bias detection
dataset = openml.datasets.get_dataset(1461)
df_marketing, _, _, _ = dataset.get_data(dataset_format='dataframe')
df_marketing.columns = ['age','job','marital','education','default','balance','housing','loan','contact','day','month','duration','campaign','pdays','previous','poutcome','response']
print(df_marketing.head(3))

# Check for repeated campaign responses from the same age-job group
df_marketing['group_id'] = df_marketing['age'].astype(str) + '-' + df_marketing['job'].astype(str)
group_dupes = df_marketing.duplicated(subset=['group_id','contact','month','day'])
print(f'Repeated group responses: {group_dupes.sum()}')

# Generate a synthetic open feedback dataset to explore non-numeric biases
df_feedback = pd.DataFrame({
    'CustomerID':[1,2,2,3,4],
    'Feedback':['Love it!','Great service','Great service','Could be better','No comment']
})
print(df_feedback)

# Remove rows where both text and customer ID match (duplicate free text)
feedback_deduped = df_feedback.drop_duplicates(subset=['CustomerID','Feedback'])
print(feedback_deduped)

# Detect suspicious feedback: users who repeat exact same text as others
suspect_comments = df_feedback['Feedback'].value_counts()
repeated_comments = suspect_comments[suspect_comments>1].index.tolist()
print('Repeated comments:', repeated_comments)

# Automated pattern bias check: maximum scores on all rating columns
columns_with_yes_no = [col for col in df.columns if df[col].nunique()<=3 and df[col].dtype==object]
suspicious_rows = df[(df[columns_with_yes_no]=='Yes').all(axis=1)] if columns_with_yes_no else pd.DataFrame()
print(f'Survey rows with all options as Yes: {suspicious_rows.shape[0]}')

# Remove low-variance respondents (same answer for all optional questions)
optional_cols = [col for col in df.columns if col not in ['gender','SeniorCitizen','Partner','Dependents','customerID','Churn']]
low_var_rows = df[optional_cols].nunique(axis=1)==1
df_cleaned = df[~low_var_rows]
print(f'Removed {low_var_rows.sum()} low-variance survey responses.')

# Handling missing survey responses in cleaning pipeline
missing_counts = df.isnull().sum()
print(missing_counts[missing_counts>0])

# Ensure proper groupings: avoid grouping by columns with missing values
safe_group_cols = [col for col in core_cols if df[col].notnull().all()]
dupes_for_grouping = df.duplicated(subset=safe_group_cols)
print(f'Duplicates (using only complete columns): {dupes_for_grouping.sum()}')

# Catching Likert scale or NPS misinterpretation: extreme repeat values
likert_cols = [col for col in df.columns if df[col].dtype in [np.int64, np.float64] and df[col].nunique()<=7]
suspect_likert = df[likert_cols].apply(lambda row: row.nunique()==1 and row.iloc[0] in [0,1,5,10], axis=1)
print(f'Extreme Likert responses: {suspect_likert.sum()}')

# Tiny end-to-end example: find true churn correlates after deduplication
# 1. Remove exact duplicates
step1 = df.drop_duplicates()
# 2. Exclude low-variance and extreme patterned responses
optional_cols = [col for col in step1.columns if col not in ['gender','SeniorCitizen','Partner','Dependents','customerID','Churn']]
step2 = step1[step1[optional_cols].nunique(axis=1) > 1]
# 3. Remove all-Yes and all-No rows in categorical columns
cat_cols = [col for col in step2.columns if step2[col].nunique()<=3 and step2[col].dtype==object]
sane_responses = step2[(step2[cat_cols]!='Yes').any(axis=1) & (step2[cat_cols]!='No').any(axis=1)] if cat_cols else step2
# 4. Analyze true churn rates by contract type
if 'Contract' in sane_responses.columns and 'Churn' in sane_responses.columns:
    churn_by_contract = sane_responses.groupby('Contract')['Churn'].value_counts(normalize=True).unstack().fillna(0)
    print('Churn by contract type after cleaning:')
    print(churn_by_contract)
else:
    print('Contract or Churn variable missing.')