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

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

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

# Beginner Example 3: Load open-ended customer feedback
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 4: Load Net Promoter Score (NPS) survey data (synthetic)
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: Load online retail transaction data (public UCI data)
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))

# Intermediate Example 1: Check for missing survey responses in the satisfaction dataset
missing_counts = df_satisfaction.isnull().sum()
print(missing_counts)

# Intermediate Example 2: Explore value counts in key categorical variables
print(df_satisfaction['gender'].value_counts())
print(df_satisfaction['Contract'].value_counts())

# Intermediate Example 3: Calculate churn rate from satisfaction dataset
churn_rate = df_satisfaction['Churn'].value_counts(normalize=True)
print('Churn Rate by Category:')
print(churn_rate)

# Intermediate Example 4: Average NPS score and regional differences
avg_nps = df_nps.groupby('Region')['NPS_Score'].mean()
print('Average NPS Score by Region:')
print(avg_nps)

# Intermediate Example 5: Convert NPS scores to promoter, passive, detractor categories
def nps_category(score):
    if score >= 9:
        return 'Promoter'
    elif score >= 7:
        return 'Passive'
    else:
        return 'Detractor'
df_nps['Category'] = df_nps['NPS_Score'].apply(nps_category)
print(df_nps['Category'].value_counts())

# Intermediate Example 6: Quick keyword extraction demo on feedback text
all_feedback = ' '.join(df_feedback['Feedback'])
keywords = pd.Series(all_feedback.lower().replace(',','').replace('.','').split()).value_counts().head(5)
print('Top keywords in feedback:')
print(keywords)

# Advanced Example 1: Automated missing value imputation for satisfaction survey
df_satisfaction_filled = df_satisfaction.copy()
for col in df_satisfaction_filled.columns:
    if df_satisfaction_filled[col].dtype == 'O':
        df_satisfaction_filled[col].fillna(df_satisfaction_filled[col].mode()[0], inplace=True)
    else:
        df_satisfaction_filled[col].fillna(df_satisfaction_filled[col].median(), inplace=True)
print('Remaining missing values:')
print(df_satisfaction_filled.isnull().sum().sum())

# Advanced Example 2: Cross-tabulation of contract type vs churn outcome
crosstab = pd.crosstab(df_satisfaction['Contract'], df_satisfaction['Churn'], normalize='index')
print('Churn rate by contract type:')
print(crosstab)

# Advanced Example 3: Construct a monthly cohort for online retail customers
df_retail['Signup_Month'] = df_retail['InvoiceDate'].dt.to_period('M')
cohort_counts = df_retail.groupby('Signup_Month')['Customer ID'].nunique()
print('Number of unique customers by month:')
print(cohort_counts.head(6))

# Advanced Example 4: Segment campaign audience by response
segment_counts = df_campaign.groupby('response')['age'].count()
print('Number of responses to campaign:')
print(segment_counts)

# Advanced Example 5: Construct a composite satisfaction score from multiple indicators
df_satisfaction['SatisfactionScore'] = (df_satisfaction['MonthlyCharges']*0.3 + df_satisfaction['tenure']*0.5 - df_satisfaction['SeniorCitizen']*10)
print(df_satisfaction[['MonthlyCharges','tenure','SeniorCitizen','SatisfactionScore']].head(5))

# Error Handling 1: What if column names are misspelled?
try:
    print(df_satisfaction['Gendre'].value_counts())
except KeyError as e:
    print('Error:', str(e), '-- Did you mean gender?')

# Error Handling 2: Handling missing values when grouping
try:
    churn_group = df_satisfaction.groupby('Contract')['Churn'].mean()
    print(churn_group)
except Exception as e:
    print('Error in groupby calculation:', e)

# Error Handling 3: Detect likely scale interpretation mistakes in NPS
if df_nps['NPS_Score'].max() > 10 or df_nps['NPS_Score'].min() < 0:
    print('Warning: NPS Scores should be between 0 and 10! Possible scale alignment issue.')
else:
    print('NPS score range is valid.')

# Best Practice Example: Age segmentation on churn
bins = [17, 30, 40, 50, 60, 100]
labels = ['18-29','30-39','40-49','50-59','60+']
df_satisfaction['AgeGroup'] = pd.cut(df_satisfaction['SeniorCitizen']*60 + 30, bins, labels=labels)
print(df_satisfaction.groupby('AgeGroup')['Churn'].value_counts(normalize=True))

# End-to-End Step 1: Calculate churn and average charges by contract type
results = df_satisfaction.groupby(['Contract','Churn'])['MonthlyCharges'].mean().unstack()
print('Average Monthly Charges by Churn and Contract Type:')
print(results)

# End-to-End Step 2: Write a summary insight to a text file
with open('churn_insight.txt', 'w') as f:
    f.write('Customers with monthly contracts and high monthly charges have the highest churn risk. We recommend new incentives or personalized offers for this group.')