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

# Beginner Example 1: Frequency table of gender in customer satisfaction survey
dataset = openml.datasets.get_dataset(42178)
df_cs, _, _, _ = dataset.get_data(dataset_format='dataframe')
freq_gender = df_cs['gender'].value_counts()
print('Gender Frequency Table:')
print(freq_gender)

# Beginner Example 2: Creating a percentage table for contract types
contract_counts = df_cs['Contract'].value_counts(normalize=True) * 100
print('Contract Type Percentage Table:')
print(contract_counts.round(2))

# Beginner Example 3: Frequency table for churned customers
freq_churn = df_cs['Churn'].value_counts()
print('Customer Churn Frequency Table:')
print(freq_churn)

# Intermediate Example 1: Cross-tabulating gender vs churn
cross_table = pd.crosstab(df_cs['gender'], df_cs['Churn'])
print('Cross-Tab: Gender vs Churn')
print(cross_table)

# Intermediate Example 2: Add margins (totals) to crosstab
cross_table_margins = pd.crosstab(df_cs['gender'], df_cs['Churn'], margins=True)
print('Cross-Tab with Totals: Gender vs Churn')
print(cross_table_margins)

# Intermediate Example 3: Percentage breakdown by payment method
payment_perc = df_cs['PaymentMethod'].value_counts(normalize=True) * 100
print('Payment Method Percentages:')
print(payment_perc.round(1))

# Intermediate Example 4: Frequency table from synthetic NPS survey data
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)
})
freq_nps = df_nps['NPS_Score'].value_counts().sort_index()
print('NPS Score Frequency Table:')
print(freq_nps)

# Intermediate Example 5: Percentage analysis of regions in NPS survey
region_perc = df_nps['Region'].value_counts(normalize=True) * 100
print('Region Percentage Table:')
print(region_perc.round(1))

# Advanced Example 1: Frequency table for multi-categorical market campaign responses
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']
freq_response = df_marketing['response'].value_counts()
print('Marketing Campaign Response Frequency:')
print(freq_response)

# Advanced Example 2: Percentage analysis by education for campaign responders
edu_response = pd.crosstab(df_marketing['education'], df_marketing['response'], normalize='index') * 100
print('Education vs Response Rate (%)')
print(edu_response.round(1))

# Advanced Example 3: Multi-level frequency table: Marital, Job, Campaign Response
multi_table = pd.crosstab([df_marketing['marital'], df_marketing['job']], df_marketing['response'])
print('Multi-level Frequency Table: Marital Status, Job, Campaign Response')
print(multi_table.head(10))

# Advanced Example 4: Trend analysismonthly percentage of positive campaign responses
df_marketing['month'] = df_marketing['month'].astype(str)
monthly_responses = df_marketing[df_marketing['response']=='yes']['month'].value_counts()
total_per_month = df_marketing['month'].value_counts()
percentage_positive = (monthly_responses / total_per_month * 100).sort_index()
print('Monthly Percentage of Yes Responses:')
print(percentage_positive.round(2))

# Error Handling Example 1: Detecting and handling missing survey responses
missing_contract = df_cs['Contract'].isnull().sum()
print(f'Total missing Contract responses: {missing_contract}')
if missing_contract > 0:
    # Optionally: fill or remove missing values
    df_cs = df_cs.dropna(subset=['Contract'])
    print('Missing Contract responses have been removed.')

# Error Handling Example 2: Grouping issueaccidentally grouping on incorrect key
try:
    bad_group = df_cs.groupby('CustomerID')['gender'].value_counts()
    print('Bad grouping result:')
    print(bad_group.head())
except Exception as e:
    print('Grouping error:', e)

# Error Handling Example 3: Misinterpreting NPS scalepercentages for Promoters, Passives, Detractors
nps_counts = df_nps['NPS_Score'].value_counts().sort_index()
nps_labels = pd.cut(df_nps['NPS_Score'], bins=[-1,6,8,10], labels=['Detractor','Passive','Promoter'])
category_perc = nps_labels.value_counts(normalize=True) * 100
print('NPS Categories (%):')
print(category_perc.round(1))

# Pattern Example: Segmentation on payment method in customer satisfaction data
segmented = df_cs.groupby('PaymentMethod')['Churn'].value_counts().unstack().fillna(0)
print('Churn Segmentation by Payment Method:')
print(segmented)

# Pattern Example: Cross-tab on contract and tenure to monitor customer retention
df_cs['tenure_bin'] = pd.cut(df_cs['tenure'], bins=[0,12,24,36,48,60,df_cs['tenure'].max()], labels=['0-12','13-24','25-36','37-48','49-60','60+'])
contract_tenure = pd.crosstab(df_cs['Contract'], df_cs['tenure_bin'])
print('Contract vs Tenure Cross-Tab:')
print(contract_tenure)

# Step 1: Label NPS categories and count promoters per region
df_nps['Category'] = pd.cut(df_nps['NPS_Score'], bins=[-1,6,8,10], labels=['Detractor','Passive','Promoter'])
region_promoters = df_nps[df_nps['Category']=='Promoter']['Region'].value_counts()
region_total = df_nps['Region'].value_counts()
region_promoter_rate = (region_promoters / region_total * 100).fillna(0)
best_region = region_promoter_rate.idxmax()
print('Promoter Percentage by Region:')
print(region_promoter_rate.round(1))
print(f'Highest promoter rate: {best_region}')