import pandas as pd
import numpy as np
import openml
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))

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

print('Youngest customer age:', campaign_df['age'].min())
print('Oldest customer age:', campaign_df['age'].max())

url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
retail_df = pd.read_excel(url, sheet_name='Year 2010-2011')
retail_df['InvoiceDate'] = pd.to_datetime(retail_df['InvoiceDate'])
monthly_counts = retail_df.groupby(retail_df['InvoiceDate'].dt.to_period('M')).size()
print(monthly_counts.head())

contract_churn = csat_df.groupby('Contract')['Churn'].value_counts(normalize=True).unstack()
contract_churn.plot(kind='bar', stacked=True)
plt.title('Churn Rate by Contract Type')
plt.ylabel('Proportion of Customers')
plt.xlabel('Contract Type')
plt.legend(title='Churn')
plt.tight_layout()
plt.show()

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)
})
sns.histplot(nps_df['NPS_Score'], bins=11, color='skyblue', edgecolor='k')
plt.title('NPS Score Distribution')
plt.xlabel('NPS Score')
plt.ylabel('Number of Customers')
plt.show()

np.random.seed(0)
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))
})
plt.plot(cohort_df['Signup_Month'], cohort_df['Active_Users'], marker='o')
plt.title('Monthly Active Users in Each Customer Cohort')
plt.xlabel('Signup Month')
plt.ylabel('Active Users')
plt.grid(True)
plt.tight_layout()
plt.show()

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'
    ]
})
all_text = ' '.join(feedback_df['Feedback']).lower()
from collections import Counter
words = [word.strip('.,') for word in all_text.split()]
freq = Counter(words)
print('Most common words:', freq.most_common(5))

missing = csat_df.isnull().sum()
print('Missing values by column:')
print(missing[missing > 0])

# Incorrect: using mean on ID field
print('Average customer ID by age group (nonsense!):')
print(nps_df.groupby(pd.cut(nps_df['Age'], bins=[17,30,50,69]))['CustomerID'].mean())

def label_nps(score):
    if score >= 9:
        return 'Promoter'
    elif score >= 7:
        return 'Passive'
    else:
        return 'Detractor'
nps_df['Category'] = nps_df['NPS_Score'].apply(label_nps)
print(nps_df['Category'].value_counts())

mean_nps_region = nps_df.groupby('Region')['NPS_Score'].mean()
print('Mean NPS by region:')
print(mean_nps_region)

ct = pd.crosstab(csat_df['Contract'], csat_df['Churn'], normalize='index')
print('Churn distribution by contract type:')
print(ct)

# Use 'MonthlyCharges' and 'tenure' as a basic combined 'value index'
csat_df['Value_Index'] = csat_df['MonthlyCharges'] / csat_df['tenure'].replace(0, np.nan)
print('Sample Value_Index for first five customers:')
print(csat_df[['MonthlyCharges','tenure','Value_Index']].head())

smoothed = monthly_counts.rolling(window=3, min_periods=1).mean()
plt.plot(monthly_counts.index.to_timestamp(), monthly_counts.values, label='Monthly Counts', linestyle='--')
plt.plot(smoothed.index.to_timestamp(), smoothed.values, label='3-Month Rolling Mean', color='red')
plt.title('Monthly Transactions with Trendline')
plt.xlabel('Month')
plt.ylabel('Transactions')
plt.legend()
plt.tight_layout()
plt.show()

# Calculate company-level NPS score as (% promoters - % detractors) * 100
promoters = (nps_df['Category'] == 'Promoter').sum()
passives = (nps_df['Category'] == 'Passive').sum()
detractors = (nps_df['Category'] == 'Detractor').sum()
n_customers = len(nps_df)
nps_score = ((promoters - detractors) / n_customers) * 100
print(f'Company NPS Score: {nps_score:.1f}')
if nps_score < 0:
    print('Warning: More detractors than promoters. Immediate business action needed!')
elif nps_score < 40:
    print('NPS is moderate. Consider initiatives to turn passives into promoters.')
else:
    print('Excellent NPS! Maintain programs to keep promoters engaged.')