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

# Beginner Example 1: Load a customer satisfaction survey dataset (OpenML)
dataset = openml.datasets.get_dataset(42178)
df_cs, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df_cs.shape)
print(df_cs.head(3))

# Beginner Example 2: Load a bank marketing campaign dataset (OpenML)
dataset = openml.datasets.get_dataset(1461)
df_mkt, _, _, _ = dataset.get_data(dataset_format='dataframe')
df_mkt.columns = ['age','job','marital','education','default','balance','housing','loan','contact','day','month','duration','campaign','pdays','previous','poutcome','response']
print(df_mkt.shape)
print(df_mkt.head(3))

# Beginner Example 3: Load an online retail transaction dataset (UCI)
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df_retail = pd.read_excel(url, sheet_name='Year 2010-2011')
df_retail['InvoiceDate'] = pd.to_datetime(df_retail['InvoiceDate'])
print(df_retail.shape)
print(df_retail.head(3))

# Beginner Example 4: Creating a synthetic NPS survey results dataset
np.random.seed(42)
df_nps = pd.DataFrame({
    'CustomerID': range(1,501),
    'Age': np.random.randint(18,70,500),
    'Region': np.random.choice(['North','South','East','West'],500),
    'NPS_Score': np.random.randint(0,11,500)
})
print(df_nps.shape)
print(df_nps.head(3))

# Beginner Example 5: Importing open-ended customer feedback data
df_feedback = pd.DataFrame({
    'CustomerID':[1,2,3,4,5],
    'Feedback':[
        'Great service and friendly staff',
        'Delivery was slow and packaging was poor',
        'Excellent quality, will buy again',
        'Customer support needs improvement',
        'Good value for money'
    ]
})
print(df_feedback.shape)
print(df_feedback.head(3))

# Beginner Example 6: Create customer cohort and retention dataset
np.random.seed(0)
dates = pd.date_range('2021-01-01', periods=24, freq='ME')
df_cohort = pd.DataFrame({
    'CustomerID': np.random.randint(1000,2000,len(dates)),
    'Signup_Month': dates,
    'Active_Users': np.random.randint(50,300,len(dates))
})
print(df_cohort.shape)
print(df_cohort.head(3))

# Intermediate Example 1: Detect and summarize missing data (customer satisfaction)
missing_counts = df_cs.isnull().sum()
print('Missing values by column:')
print(missing_counts[missing_counts > 0])

# Intermediate Example 2: Check for and drop duplicate rows
print('Number of duplicates:', df_cs.duplicated().sum())
df_cs_clean = df_cs.drop_duplicates()
print('Shape after deduplication:', df_cs_clean.shape)

# Intermediate Example 3: Convert TotalCharges to float and handle errors
df_cs_clean['TotalCharges'] = pd.to_numeric(df_cs_clean['TotalCharges'], errors='coerce')
print('Total null values after conversion:', df_cs_clean['TotalCharges'].isnull().sum())

# Intermediate Example 4: Standardize category labels (marketing campaign jobs)
print('Unique job values before cleaning:', sorted(df_mkt['job'].unique()))
df_mkt['job'] = df_mkt['job'].str.lower().str.strip()
print('Unique job values after cleaning:', sorted(df_mkt['job'].unique()))

# Intermediate Example 5: Identify negative or out-of-range values
invalid_ages = df_nps[(df_nps['Age'] < 0) | (df_nps['Age'] > 120)]
print('Number of invalid ages:', invalid_ages.shape[0])

# Advanced Example 1: Calculate customer tenure groups
df_cs_clean['tenure_group'] = pd.cut(df_cs_clean['tenure'], bins=[0, 12, 24, 48, 72], labels=['<1yr','1-2yr','2-4yr','4-6yr'])
print(df_cs_clean[['tenure','tenure_group']].head(5))

# Advanced Example 2: Aggregate retail transactions to find total spend per customer
df_retail_cust = df_retail.dropna(subset=['Customer ID'])
df_retail_cust['TotalSpend'] = df_retail_cust['Quantity'] * df_retail_cust['Price']
spend_by_customer = df_retail_cust.groupby('Customer ID')['TotalSpend'].sum().reset_index()
print(spend_by_customer.head())

# Advanced Example 3: Segment NPS respondents
def nps_segment(score):
    if score >= 9:
        return 'Promoter'
    elif score >= 7:
        return 'Passive'
    else:
        return 'Detractor'
df_nps['NPS_Type'] = df_nps['NPS_Score'].apply(nps_segment)
print(df_nps[['NPS_Score','NPS_Type']].head(10))

# Advanced Example 4: Create cohort month from signup dates for retention analysis
df_cohort['CohortMonth'] = df_cohort['Signup_Month'].dt.to_period('M')
print(df_cohort[['Signup_Month','CohortMonth']].head(5))

# Advanced Example 5: Merge NPS and Feedback for richer analytics
df_merged = pd.merge(df_nps, df_feedback, on='CustomerID', how='left')
print(df_merged.head(5))

# Debug Example 1: Finding rows with all survey items missing
missing_items = df_cs_clean[df_cs_clean[['MonthlyCharges','TotalCharges','tenure']].isnull().all(axis=1)]
print('Rows with all key items missing:', missing_items.shape[0])

# Debug Example 2: Aggregating NPS scores by group, check for misgroupings
nps_by_region = df_nps.groupby('Region')['NPS_Score'].mean()
print(nps_by_region)

# Debug Example 3: Checking NPS scoring conformity
not_in_range = df_nps[~df_nps['NPS_Score'].between(0,10)]
print('Out-of-range NPS records:', not_in_range.shape[0])

# Pattern Example 1: Cross-tabulate segment with churn outcome
if 'Churn' in df_cs_clean.columns:
    crosstab = pd.crosstab(df_cs_clean['tenure_group'], df_cs_clean['Churn'])
    print(crosstab)

# Pattern Example 2: Construct a simple customer value index
if 'MonthlyCharges' in df_cs_clean.columns and 'tenure' in df_cs_clean.columns:
    df_cs_clean['CustomerValue'] = df_cs_clean['MonthlyCharges'] * df_cs_clean['tenure']
    print(df_cs_clean[['MonthlyCharges','tenure','CustomerValue']].head())

# Pattern Example 3: Analyze trend in average NPS by region
avg_nps_by_region = df_nps.groupby('Region')['NPS_Score'].mean()
print('Average NPS by region:')
print(avg_nps_by_region)

# Pattern Example 4: Visualize active users per cohort month (trend analysis)
import matplotlib.pyplot as plt
plt.figure(figsize=(7,3))
df_cohort.groupby('CohortMonth')['Active_Users'].mean().plot(marker='o')
plt.title('Active Users per CohortMonth')
plt.ylabel('Active Users')
plt.xlabel('CohortMonth')
plt.tight_layout()
plt.show()

# End-to-End Example: Prepare a pipeline for analysis-ready customer satisfaction data
def prepare_cs(df):
    df = df.drop_duplicates()
    df['TotalCharges'] = pd.to_numeric(df['TotalCharges'], errors='coerce')
    df = df.dropna(subset=['MonthlyCharges','TotalCharges','tenure'])
    df['tenure_group'] = pd.cut(df['tenure'],bins=[0,12,24,48,72],labels=['<1yr','1-2yr','2-4yr','4-6yr'])
    if 'Churn' in df.columns:
        crosstab = pd.crosstab(df['tenure_group'], df['Churn'])
        print(crosstab)
    else:
        print('Churn column not found!')
prepare_cs(df_cs)