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

dataset = openml.datasets.get_dataset(42178)
df_cs, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df_cs.shape)
print(df_cs.head(3))

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))

summary = df_cs[['gender','SeniorCitizen','tenure','MonthlyCharges','TotalCharges','Churn']].describe(include='all')
print(summary)

summary.to_csv('customer_summary.csv')

nps_grouped = df_nps.groupby('Region')['NPS_Score'].mean().reset_index()
print(nps_grouped)

nps_grouped.to_excel('regional_nps_report.xlsx', index=False)

# Text-based insights for lightweight sharing
with open('key_metrics.txt', 'w') as f:
    f.write('Key Customer Metrics\n')
    f.write(f'Total Customers: {df_cs.shape[0]}\n')
    f.write(f'Churn Rate: {df_cs["Churn"].value_counts(normalize=True).to_dict()}\n')
print('Text report saved.')

# Handling missing survey responses before export
missing = df_cs.isna().sum()
print(missing)
df_cs_clean = df_cs.dropna()

# Export cleaned data for downstream analysis
df_cs_clean.to_csv('clean_customer_survey.csv', index=False)

# Exporting selected columns only (privacy, focus)
selected = df_cs[['gender','MonthlyCharges','Churn']]
selected.to_csv('selected_columns_survey.csv', index=False)

# Exporting to Excel with multiple sheets (advanced)
with pd.ExcelWriter('multi_report.xlsx') as writer:
    summary.to_excel(writer, sheet_name='Summary')
    nps_grouped.to_excel(writer, sheet_name='NPS_by_Region')

# Exporting with open-ended text feedback
feedback_df = 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']
})
feedback_df.to_csv('customer_feedback.csv', index=False)

# Marketing campaign export example
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']
df_mkt.to_excel('marketing_campaign.xlsx', index=False)

# Errorincorrect groupby: mistyped field
try:
    bad_group = df_cs.groupby('Gendre')['MonthlyCharges'].mean()
except Exception as e:
    print('Error:', e)

# Errormisinterpreted Likert/NPS scales: mean instead of %
nps_pct = (df_nps['NPS_Score'] >= 9).mean()
print(f'Pct Promoters (score 9 or 10): {nps_pct:.2%}')

# Best practice: Export cross-tabs of churn by gender
churn_ct = pd.crosstab(df_cs['gender'], df_cs['Churn'])
print(churn_ct)
churn_ct.to_csv('churn_xtab.csv')

# Trend analysis: Monthly new customer count
dates = pd.date_range('2021-01-01', periods=24, freq='M')
cohort_df = pd.DataFrame({'CustomerID': np.random.randint(1000,2000,len(dates)), 'Signup_Month': dates, 'Active_Users': np.random.randint(50,300,len(dates))})
monthly_counts = cohort_df.groupby(cohort_df['Signup_Month'].dt.to_period('M'))['Active_Users'].sum().reset_index()
monthly_counts.to_csv('monthly_new_users.csv', index=False)
print(monthly_counts.head())

# End-to-end: Exporting an actionable insight from survey data
avg_charge = df_cs['MonthlyCharges'].mean()
churn_rate = df_cs['Churn'].value_counts(normalize=True).get('Yes',0)
insight = f'Average monthly charge: ${avg_charge:.2f}\nChurn rate: {churn_rate:.2%}\nRecommendation: Focus retention efforts on high-churn segments with above-average charges.'
with open('actionable_insight.txt', 'w') as f:
    f.write(insight)
print(insight)