import pandas as pd
import numpy as np
import openml
from collections import Counter
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')

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

# Calculate basic gender proportions in the satisfaction survey
gender_counts = df_csat['gender'].value_counts(normalize=True)
print('Gender distribution (fraction):\n', gender_counts)

# Find the percentage of customers who churned (a key business driver)
churn_rate = df_csat['Churn'].value_counts(normalize=True)['Yes'] * 100
print(f'Churn rate: {churn_rate:.2f}%')

# Load a real marketing campaign dataset from OpenML (Bank Marketing)
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('Shape:', df_campaign.shape)
print(df_campaign.head(3))

# Calculate overall marketing response rate
response_rate = df_campaign['response'].value_counts(normalize=True)['2'] * 100
print(f'Response rate: {response_rate:.2f}%')

# Visualize campaign response by marital status
sns.barplot(x='marital', y='response', data=df_campaign.groupby('marital')['response'].apply(lambda x: (x=='2').mean()).reset_index())
plt.ylabel('Response Rate')
plt.title('Marketing Response Rate by Marital Status')
plt.show()

# Load a Net Promoter Score (NPS) survey dataset (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('Shape:', df_nps.shape)
print(df_nps.head(3))

# Classify each row as Detractor, Passive, or Promoter (standard NPS categories)
def nps_group(score):
    if score <= 6:
        return 'Detractor'
    elif score <= 8:
        return 'Passive'
    else:
        return 'Promoter'
df_nps['NPS_Type'] = df_nps['NPS_Score'].apply(nps_group)
counts = df_nps['NPS_Type'].value_counts()
print('NPS breakdown:\n', counts)

# Calculate the official NPS metric: % Promoters - % Detractors
promoter_pct = (df_nps['NPS_Type']=='Promoter').mean()*100
detractor_pct = (df_nps['NPS_Type']=='Detractor').mean()*100
nps_score = promoter_pct - detractor_pct
print(f'NPS = {nps_score:.1f} (Promoters: {promoter_pct:.1f}%, Detractors: {detractor_pct:.1f}%)')

# Load a real online retail customer transaction dataset
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('Shape:', df_retail.shape)
print(df_retail.head(3))

# Find top 5 countries by transaction count (excluding UK, the source country)
country_counts = df_retail['Country'].value_counts()
top_countries = country_counts.drop('United Kingdom').head(5)
print('Top 5 non-UK countries by transactions:\n', top_countries)

# Segment retail customers by how much they spend per transaction
df_retail['Total'] = df_retail['Quantity'] * df_retail['Price']
spending_bins = pd.cut(df_retail['Total'], bins=[-1,10,50,100,1e6], labels=['Low','Medium','High','Very High'])
df_retail['Spending_Segment'] = spending_bins
print(df_retail[['Customer ID','Total','Spending_Segment']].dropna().head(3))

# Load and analyze 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.head(3))

# Count the most common words in feedback (simple sentiment signal)
all_words = ' '.join(df_feedback['Feedback']).lower().split()
word_counts = Counter(all_words)
print('Most common words:', word_counts.most_common(5))

# Create Net Promoter Score segment breakdown by region
region_nps = df_nps.groupby('Region')['NPS_Type'].value_counts(normalize=True).unstack().fillna(0)
print('NPS type percentage by region:\n', (region_nps*100).round(1))

# Cross-tab: Churn rate by contract type in satisfaction data
churn_contract = pd.crosstab(df_csat['Contract'], df_csat['Churn'], normalize='index')
print('Churn rate by contract type:\n', churn_contract['Yes'].round(2))

# Time trend: Churned customers over customer tenure (experience in months)
df_csat['tenure_group'] = pd.cut(df_csat['tenure'], bins=[0,12,24,48,72], labels=['<1yr','1-2yr','2-4yr','4-6yr'])
churn_trend = df_csat.groupby('tenure_group')['Churn'].value_counts(normalize=True).unstack()['Yes']
churn_trend.plot(kind='bar', color='orange')
plt.ylabel('Churn Rate')
plt.title('Churn Rate by Tenure Group')
plt.show()

# Handle missing survey responses in the NPS data
df_nps_missing = df_nps.copy()
df_nps_missing.loc[df_nps_missing.sample(frac=0.05, random_state=42).index, 'NPS_Score'] = np.nan
missing_n = df_nps_missing['NPS_Score'].isna().sum()
print(f'Missing NPS responses: {missing_n}')

# Remove or replace missing NPS scores before analysis
df_nps_dropna = df_nps_missing.dropna(subset=['NPS_Score'])
print('After removing missing:', df_nps_dropna.shape[0], 'responses left')

# Example: Incorrect grouping can hide or distort insights
# Suppose we forget to group by region when calculating NPS
wrong_nps = (df_nps_dropna['NPS_Score'] >= 9).mean()*100 - (df_nps_dropna['NPS_Score'] <= 6).mean()*100
print(f'Incorrect NPS calculation (ignores groups): {wrong_nps:.1f}')

# Misinterpreting text scales (e.g., Likert or NPS) can mislead decision makers
example = pd.Series(['Strongly disagree','Disagree','Agree','Strongly agree'])
# Convert to numeric for averaging (arbitrary mapping, must match survey meaning)
likert_map = {'Strongly disagree':1, 'Disagree':2, 'Neutral':3, 'Agree':4, 'Strongly agree':5}
numbers = example.map(likert_map)
print(numbers)

# END-TO-END EXAMPLE: From raw survey to business insight
# 1. Load customer satisfaction data, 2. Calculate churn and NPS, 3. Segment by contract, 4. Recommend action
dataset = openml.datasets.get_dataset(42178)
df = dataset.get_data(dataset_format='dataframe')[0].copy()
df['tenure_group'] = pd.cut(df['tenure'],[0,12,24,48,72],labels=['<1yr','1-2yr','2-4yr','4-6yr'])
contract_churn = pd.crosstab(df['Contract'],df['Churn'],normalize='index').get('Yes',pd.Series(0))
avg_monthly = df.groupby('Contract')['MonthlyCharges'].mean().round(2)
insight = f"Highest churn: {contract_churn.idxmax()} ({contract_churn.max():.2%}), lowest churn: {contract_churn.idxmin()} ({contract_churn.min():.2%})"
print(insight)
print('Average monthly charges by contract:\n', avg_monthly)
if contract_churn.idxmax() == 'Month-to-month':
    print('Recommendation: Target month-to-month customers for retention with better offers.')
else:
    print('Recommendation: Investigate drivers for churn in highest risk contract segment.')