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

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

np.random.seed(42)
nps_df = 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(nps_df.shape)
print(nps_df.head(3))

dataset = openml.datasets.get_dataset(1461)
mkt_df, _, _, _ = dataset.get_data(dataset_format='dataframe')
mkt_df.columns = ['age','job','marital','education','default','balance','housing','loan','contact','day','month','duration','campaign','pdays','previous','poutcome','response']
print(mkt_df.shape)
print(mkt_df.head(3))

retail_url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
retail_df = pd.read_excel(retail_url, sheet_name='Year 2010-2011')
retail_df['InvoiceDate'] = pd.to_datetime(retail_df['InvoiceDate'])
print(retail_df.shape)
print(retail_df.head(3))

if 'Satisfaction' in csat_df.columns:
    plt.figure(figsize=(6,4))
    csat_df['Satisfaction'].hist(bins=10, color='skyblue', edgecolor='black')
    plt.xlabel('Satisfaction Score')
    plt.ylabel('Number of Customers')
    plt.title('Distribution of Customer Satisfaction Scores')
    plt.show()
else:
    print('The Satisfaction column is not present in the dataset.')

nps_by_region = nps_df.groupby('Region')['NPS_Score'].mean()
print(nps_by_region)
nps_by_region.plot(kind='bar', color='orange')
plt.title('Average NPS by Region')
plt.ylabel('NPS Score')
plt.show()

nps_df['Age_Group'] = pd.cut(nps_df['Age'], bins=[17,29,44,59,70], labels=['18-29','30-44','45-59','60-70'])
segment_counts = nps_df['Age_Group'].value_counts().sort_index()
print(segment_counts)
segment_counts.plot(kind='bar', color='purple')
plt.xlabel('Age Group')
plt.ylabel('Customers in Group')
plt.title('Customer Count by Age Segment')
plt.show()

if 'Satisfaction' in csat_df.columns and 'Churn' in csat_df.columns:
    churn_means = csat_df.groupby('Churn')['Satisfaction'].mean()
    print(churn_means)
    churn_means.plot(kind='bar', color='teal')
    plt.title('Avg Satisfaction by Customer Churn Status')
    plt.ylabel('Average Satisfaction Score')
    plt.show()
else:
    print('Required columns for Churn and Satisfaction are missing.')

response_counts = mkt_df['response'].value_counts()
print(response_counts)
rate = response_counts.get('yes', 0) / response_counts.sum() if response_counts.sum() > 0 else 0
print(f'Campaign Response Rate: {rate:.2%}')

np.random.seed(0)
dates = pd.date_range('2021-01-01', periods=24, freq='ME')
cohort_df = pd.DataFrame({
    'CustomerID': np.random.randint(1000,2000,len(dates)),
    'Signup_Month': dates,
    'Active_Users': np.random.randint(50,300,len(dates))
})
plt.figure(figsize=(8,4))
plt.plot(cohort_df['Signup_Month'], cohort_df['Active_Users'], marker='o')
plt.title('Customer Activity Over Time')
plt.xlabel('Month')
plt.ylabel('Active Users')
plt.grid(True)
plt.show()

if 'Contract' in csat_df.columns and 'Satisfaction' in csat_df.columns:
    ctab = pd.crosstab(csat_df['Contract'], csat_df['Satisfaction'])
    print(ctab.head())
    plt.figure(figsize=(8,4))
    sns.heatmap(ctab, annot=False, cmap='Blues')
    plt.title('Satisfaction Scores vs Contract Type')
    plt.xlabel('Satisfaction Score')
    plt.ylabel('Contract Type')
    plt.show()
else:
    print('Required columns for Contract and Satisfaction are missing.')

# Merge NPS and marketing response on random customer IDs
merged = nps_df.copy()
merged['response'] = np.where(np.random.rand(len(merged)) < 0.3, 'yes', 'no')
merged['Loyalty_Index'] = merged['NPS_Score'] * (merged['response'] == 'yes').astype(int)
print(merged[['Age','Region','NPS_Score','response','Loyalty_Index']].head(5))
print('Highest Loyalty Index:', merged['Loyalty_Index'].max())

if 'Price' in retail_df.columns and 'Quantity' in retail_df.columns:
    retail_df['Total'] = retail_df['Price'] * retail_df['Quantity']
    total_mean = retail_df['Total'].mean()
    total_std = retail_df['Total'].std()
    z_scores = (retail_df['Total'] - total_mean) / total_std
    outliers = retail_df[(z_scores > 3) | (z_scores < -3)]
    print(outliers[['Invoice','Quantity','Price','Total']].head())
    print(f'Outlier count: {len(outliers)}')
else:
    print('Price or Quantity column missing.')

if 'Satisfaction' in csat_df.columns:
    missing = csat_df['Satisfaction'].isnull().sum()
    print(f'Missing Satisfaction Responses: {missing}')
if 'NPS_Score' in nps_df.columns:
    missing_nps = nps_df['NPS_Score'].isnull().sum()
    print(f'Missing NPS Responses: {missing_nps}')

unique_customers = nps_df['CustomerID'].nunique()
agg_counts = nps_df.groupby('CustomerID').size().max()
if agg_counts > 1:
    print(f'Warning: Multiple records for same customer detected!')
else:
    print(f'Customer groupings are unique.')

detractors = ((nps_df['NPS_Score'] >= 0) & (nps_df['NPS_Score'] <= 6)).sum()
passives = ((nps_df['NPS_Score'] >= 7) & (nps_df['NPS_Score'] <= 8)).sum()
promoters = ((nps_df['NPS_Score'] >= 9) & (nps_df['NPS_Score'] <= 10)).sum()
n_total = len(nps_df)
nps_result = ((promoters - detractors) / n_total) * 100 if n_total else 0
print(f'NPS Calculation: {nps_result:.1f}')

loyalty_by_age = merged.groupby('Age_Group')['Loyalty_Index'].mean()
print(loyalty_by_age)
loyalty_by_age.plot(kind='bar', color='darkgreen')
plt.title('Average Loyalty Index by Age Group')
plt.xlabel('Age Group')
plt.ylabel('Mean Loyalty Index')
plt.show()

nps_by_region = nps_df.groupby('Region')['NPS_Score'].mean()
target_region = nps_by_region.idxmin()
min_score = nps_by_region.min()
print('Average NPS by Region:')
print(nps_by_region)
print(f'Lowest NPS region: {target_region} (Score: {min_score:.2f})')
print(f'Recommendation: Focus loyalty initiatives in {target_region} to close the gap.')