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

# Load customer satisfaction survey data from OpenML
dataset = openml.datasets.get_dataset(42178)
df_cs, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(df_cs.shape)
print(df_cs.head(3))

# Check for missing values in key trend columns
missing = df_cs[['tenure', 'MonthlyCharges', 'Churn']].isnull().sum()
print(missing)

# Beginner trend: Average monthly charges by customer tenure
df_cs_grouped = df_cs.groupby('tenure')['MonthlyCharges'].mean().reset_index()
plt.plot(df_cs_grouped['tenure'], df_cs_grouped['MonthlyCharges'])
plt.xlabel('Tenure (months)')
plt.ylabel('Average Monthly Charges')
plt.title('Trend: Avg Monthly Charges vs. Tenure')
plt.show()

# Load online retail behavior data (real transactions)
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(df_retail.shape)
print(df_retail.head(3))

# Beginner trend: Number of transactions per month
df_retail['Month'] = df_retail['InvoiceDate'].dt.to_period('M')
monthly_counts = df_retail.groupby('Month').size()
monthly_counts.plot(kind='line', marker='o')
plt.xlabel('Month')
plt.ylabel('Number of Transactions')
plt.title('Number of Transactions per Month')
plt.show()

# Intermediate: Trend of total sales value per month
df_retail['TotalSales'] = df_retail['Quantity'] * df_retail['Price']
monthly_sales = df_retail.groupby('Month')['TotalSales'].sum()
monthly_sales.plot(kind='bar', color='skyblue')
plt.ylabel('Total Sales Value')
plt.title('Total Sales Value per Month')
plt.xticks(rotation=45)
plt.show()

# Load marketing campaign performance data
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']
print(df_mkt.shape)
print(df_mkt.head(3))

# Intermediate trend: Campaign response rates by month
response_by_month = df_mkt.groupby('month')['response'].value_counts().unstack().fillna(0)
response_by_month['response_rate'] = response_by_month['2'] / (response_by_month['2'] + response_by_month['1'])
response_by_month['response_rate'].plot(kind='bar', color='green')
plt.ylabel('Response Rate')
plt.xlabel('Campaign Month')
plt.title('Marketing Campaign Response Rates by Month')
plt.show()

# Intermediate: Trend of average customer satisfaction by churn status
satisfaction_churn = df_cs.groupby('Churn')['MonthlyCharges'].mean()
satisfaction_churn.plot(kind='bar', color=['orange','gray'])
plt.ylabel('Average Monthly Charges')
plt.title('Avg Monthly Charges: Churned vs. Retained Customers')
plt.xticks(rotation=0)
plt.show()

# Advanced: Calculate rolling trend of transactions (moving average)
transactions_daily = df_retail.resample('D', on='InvoiceDate').size()
rolling_tx = transactions_daily.rolling(window=14, min_periods=1).mean()
plt.plot(rolling_tx)
plt.xlabel('Date')
plt.ylabel('14-day Moving Avg: Transactions')
plt.title('Smoothed Trend of Daily Transactions')
plt.show()

# Advanced: Segment trend analysis by country
countries = ['United Kingdom', 'Germany', 'France', 'Netherlands']
for country in countries:
    sales = df_retail[df_retail['Country'] == country].groupby('Month')['TotalSales'].sum()
    plt.plot(sales.index.astype(str), sales, label=country)
plt.legend()
plt.xlabel('Month')
plt.ylabel('Total Sales Value')
plt.title('Monthly Sales Trend by Country')
plt.xticks(rotation=45)
plt.show()

# Error example: What happens with missing values in grouping?
df_missing = df_cs.copy()
df_missing.loc[0:5, 'MonthlyCharges'] = np.nan
grouped = df_missing.groupby('tenure')['MonthlyCharges'].mean()
print(grouped.head(10))

# Debug: Identifying missing and correcting with imputation
imputed = df_missing['MonthlyCharges'].fillna(df_missing['MonthlyCharges'].mean())
print('Original mean:', df_missing['MonthlyCharges'].mean())
print('Imputed mean:', imputed.mean())

# Error: Misinterpreting NPS scaleload and plot synthetic NPS data
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)})
df_nps['Month'] = np.random.choice(list(range(1, 13)), size=500)
monthly_nps = df_nps.groupby('Month')['NPS_Score'].mean()
plt.plot(monthly_nps.index, monthly_nps.values, marker='o', linestyle='-')
plt.xlabel('Month')
plt.ylabel('Avg NPS Score')
plt.title('Average NPS Score by Month')
plt.show()

# Best practice demo: Cross-tab trend by customer contract type
df_cs_contract = df_cs.groupby(['tenure','Contract'])['MonthlyCharges'].mean().unstack()
df_cs_contract.plot(figsize=(8,5))
plt.xlabel('Tenure (months)')
plt.ylabel('Average Monthly Charges')
plt.title('Trend: Monthly Charges by Contract Type Over Tenure')
plt.show()

# Pattern: Construct a composite satisfaction index
df_cs['SatIndex'] = (
    df_cs['OnlineSecurity'].map({'No': 0, 'Yes': 1}) +
    df_cs['OnlineBackup'].map({'No': 0, 'Yes': 1}) +
    df_cs['TechSupport'].map({'No': 0, 'Yes': 1})
) / 3
monthly_sat = df_cs.groupby('tenure')['SatIndex'].mean()
plt.plot(monthly_sat.index, monthly_sat.values)
plt.xlabel('Tenure (months)')
plt.ylabel('Satisfaction Index (0-1)')
plt.title('Composite Satisfaction Index by Tenure')
plt.show()

# End-to-end: Find evidence for customer churn risk over time
df_cs_trend = df_cs.groupby('tenure')['Churn'].value_counts(normalize=True).unstack().fillna(0)
plt.plot(df_cs_trend.index, df_cs_trend['Yes'], label='Churned')
plt.plot(df_cs_trend.index, df_cs_trend['No'], label='Retained')
plt.xlabel('Tenure (months)')
plt.ylabel('Fraction of Customers')
plt.title('Customer Churn vs. Tenure Trend')
plt.legend()
plt.show()
churn_tipping_point = df_cs_trend['Yes'].idxmax()
print(f'Business insight: Churn risk peaks at {churn_tipping_point} months tenure.')