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

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[['Invoice', 'InvoiceDate', 'Price']].head(3))

df_retail['YearMonth'] = df_retail['InvoiceDate'].dt.to_period('M')
monthly_sales = df_retail.groupby('YearMonth')['Price'].sum()
print(monthly_sales.head(5))

plt.figure(figsize=(10,4))
monthly_sales.plot(marker='o')
plt.title('Monthly Online Retail Sales Trends')
plt.ylabel('Total Sales')
plt.xlabel('Year-Month')
plt.tight_layout()
plt.show()

dataset = openml.datasets.get_dataset(42178)
df_satisfaction, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df_satisfaction[['tenure', 'MonthlyCharges', 'Churn']].head(3))

tenure_churn = df_satisfaction.groupby('tenure')['Churn'].value_counts(normalize=True).unstack().fillna(0)
tenure_churn_pct = tenure_churn * 100
print(tenure_churn_pct.head(10))

plt.figure(figsize=(8,5))
plt.plot(tenure_churn_pct.index, tenure_churn_pct.get('Yes', 0), label='Churned')
plt.plot(tenure_churn_pct.index, tenure_churn_pct.get('No', 0), label='Retained')
plt.xlabel('Customer Tenure (months)')
plt.ylabel('Percentage')
plt.title('Churn vs Retention Rates by Tenure')
plt.legend()
plt.tight_layout()
plt.show()

df_retail['Year'] = df_retail['InvoiceDate'].dt.year
country_year = df_retail.groupby(['Country', 'Year'])['Price'].sum().unstack()
top_countries = country_year.sum(axis=1).sort_values(ascending=False).head(5).index
display(country_year.loc[top_countries])

sns.heatmap(country_year.loc[top_countries], annot=True, fmt='.0f', cmap='Blues')
plt.title('Annual Sales Heatmap for Top 5 Countries')
plt.xlabel('Year')
plt.ylabel('Country')
plt.tight_layout()
plt.show()

import calendar
df_retail['Month'] = df_retail['InvoiceDate'].dt.month
monthly_avg = df_retail.groupby(['Month'])['Price'].mean()
plt.bar([calendar.month_abbr[m] for m in monthly_avg.index], monthly_avg.values)
plt.title('Average Transaction Amount by Month')
plt.xlabel('Month')
plt.ylabel('Average Transaction Amount')
plt.tight_layout()
plt.show()

import random
random.seed(42)
sample_ids = random.sample(list(df_retail['Customer ID'].dropna().unique()), 3)
df_sample = df_retail[df_retail['Customer ID'].isin(sample_ids)]
for cid, group in df_sample.groupby('Customer ID'):
    plt.plot(group['InvoiceDate'], group['Price'].cumsum(), label=f'Customer {int(cid)}')
plt.legend()
plt.title('Cumulative Spending Over Time for Sample Customers')
plt.xlabel('Date')
plt.ylabel('Cumulative Spend')
plt.tight_layout()
plt.show()

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[['month', 'campaign', 'response']].head())

campaign_month = df_campaign.groupby('month')['response'].value_counts().unstack().fillna(0)
campaign_month['total'] = campaign_month.sum(axis=1)
campaign_month['response_rate'] = 100 * campaign_month.get('yes', 0) / campaign_month['total']
campaign_month_response = campaign_month[['response_rate']]
print(campaign_month_response)

plt.plot(campaign_month_response.index, campaign_month_response['response_rate'], marker='o')
plt.title('Monthly Marketing Campaign Response Rate')
plt.xlabel('Month')
plt.ylabel('Response Rate (%)')
plt.tight_layout()
plt.show()

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),
    'Survey_Month': np.random.choice(['2022-06','2022-07','2022-08','2022-09'], 500)
})
print(df_nps.head(3))

def nps_category(score):
    if score >= 9:
        return 'Promoter'
    elif score >= 7:
        return 'Passive'
    else:
        return 'Detractor'
df_nps['NPS_Category'] = df_nps['NPS_Score'].apply(nps_category)
monthly_nps = df_nps.groupby('Survey_Month')['NPS_Category'].value_counts().unstack().fillna(0)
monthly_nps_pct = monthly_nps.div(monthly_nps.sum(axis=1), axis=0) * 100
print(monthly_nps_pct.round(1))

plt.figure(figsize=(7,5))
monthly_nps_pct.plot(kind='bar', stacked=True, colormap='Set2', ax=plt.gca())
plt.title('NPS Categories by Survey Month')
plt.ylabel('Percentage of Respondents')
plt.xlabel('Survey Month')
plt.tight_layout()
plt.show()

df_cohort = pd.DataFrame({
    'CustomerID': np.random.randint(1000,2000,24),
    'Signup_Month': pd.date_range('2021-01-01', periods=24, freq='M'),
    'Active_Users': np.random.randint(50,300,24)
})
df_cohort['Signup_Month'] = df_cohort['Signup_Month'].dt.to_period('M')
print(df_cohort.head(3))

plt.plot(df_cohort['Signup_Month'].astype(str), df_cohort['Active_Users'], marker='o')
plt.title('Active Users Per Cohort Over Time')
plt.xlabel('Signup Month')
plt.ylabel('Active Users')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

plt.bar(df_cohort['Signup_Month'].astype(str), df_cohort['Active_Users'])
plt.title('Monthly New Active Users (Cohort Analysis)')
plt.xlabel('Cohort Signup Month')
plt.ylabel('Active Users')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

# Introduce artificial missing values
df_nps_missing = df_nps.copy()
df_nps_missing.loc[df_nps_missing.sample(frac=0.1, random_state=42).index, 'NPS_Score'] = np.nan
print(df_nps_missing['NPS_Score'].isna().sum(), 'NPS scores missing')

# Grouping error: try grouping by wrong field
try:
    wrong_group = df_retail.groupby('RandomField')['Price'].sum()
except KeyError as e:
    print('Error:', e)

# Misinterpretation of NPS score as continuous value
avg_score = df_nps['NPS_Score'].mean()
print('Average NPS score (not recommended as key metric):', round(avg_score,2))

# Age group segmentation example
bins = [17, 29, 39, 49, 59, 70]
labels = ['18-29', '30-39', '40-49', '50-59', '60-70']
df_nps['Age_Group'] = pd.cut(df_nps['Age'], bins=bins, labels=labels)
seg_nps = df_nps.groupby('Age_Group')['NPS_Category'].value_counts(normalize=True).unstack()
print(seg_nps.round(2))

# Market index construction example
monthly_sales_index = 100 * monthly_sales / monthly_sales.iloc[0]
plt.plot(monthly_sales.index.astype(str), monthly_sales_index, marker='o')
plt.title('Indexed Sales Trend (Base Month = 100)')
plt.xlabel('Year-Month')
plt.ylabel('Sales Index (Base=100)')
plt.tight_layout()
plt.show()

# End-to-end: Identify month with biggest NPS improvement
monthly_nps['Net_NPS'] = (monthly_nps['Promoter'] - monthly_nps['Detractor']) / monthly_nps.sum(axis=1) * 100
print(monthly_nps[['Net_NPS']].round(1))
best_change = monthly_nps['Net_NPS'].diff().idxmax()
print('Month with biggest NPS improvement:', best_change)