import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')

# Beginner Example 1: Create a DataFrame of open-ended customer 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'
                  ]})
print(df.shape)
print(df.head(3))

# Beginner Example 2: Count the number of feedback responses
feedback_count = df['Feedback'].count()
print('Number of feedback responses:', feedback_count)

# Beginner Example 3: Preview unique words in feedback
all_feedback = ' '.join(df['Feedback'].values)
unique_words = set(all_feedback.lower().split())
print('Sample unique words in feedback:', list(unique_words)[:8])

# Beginner Example 4: Count keyword mentions (simple case-insensitive search)
keyword = 'service'
df['Has_Service'] = df['Feedback'].str.lower().str.contains(keyword)
print(df[['Feedback','Has_Service']])

import openml
dataset = openml.datasets.get_dataset(42178)
customer_df, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(customer_df.columns.tolist())
print(customer_df.shape)

# Intermediate Example 2: Simulate adding open-ended text to a large survey
customer_df = customer_df.head(12).copy()
feedbacks = [
    'Friendly service and fast response',
    'Unhelpful support, very disappointed',
    'Quick installation and polite staff',
    'Hold times were too long',
    'Great offers and discounts',
    'Billing was confusing',
    'Received wrong product',
    'App is buggy but staff tried to help',
    'Outstanding experience',
    'Hard to reach support team',
    'Rep did not understand my problem',
    'Will recommend to friends' ]
customer_df['Feedback'] = feedbacks
print(customer_df[['gender','Churn','Feedback']].head())

# Intermediate Example 3: Reporting the main complaint themes with keyword flags
themes = ['support', 'billing', 'discount', 'help', 'recommend']
for theme in themes:
    theme_col = f'Has_{theme.capitalize()}'
    customer_df[theme_col] = customer_df['Feedback'].str.lower().str.contains(theme)

print(customer_df[['Feedback'] + [f'Has_{t.capitalize()}' for t in themes]])

# Intermediate Example 4: Calculate frequency of each theme
theme_counts = {theme: customer_df[f'Has_{theme.capitalize()}'].sum() for theme in themes}
print('Number of responses per theme:')
print(theme_counts)

# Intermediate Example 5: Visualizing comment themes (bar chart)
import matplotlib.pyplot as plt
plt.bar(theme_counts.keys(), theme_counts.values(), color='royalblue')
plt.title('Frequency of Comment Themes')
plt.xlabel('Theme')
plt.ylabel('Number of Mentions')
plt.show()

# Advanced Example 2: Cross-tabulation of themes by churn status
crosstab = customer_df.groupby('Churn')[[f'Has_{t.capitalize()}' for t in themes]].sum()
print('Theme mentions by churn status:')
print(crosstab)

# Advanced Example 3: Export categorized feedback to CSV
output_path = 'categorized_feedback.csv'
customer_df[['gender','Churn','Feedback'] + [f'Has_{t.capitalize()}' for t in themes]].to_csv(output_path, index=False)
print('Categorized feedback exported to', output_path)

# Missing survey responses (blank feedback)
df_with_missing = df.copy()
df_with_missing.loc[2, 'Feedback'] = None
print(df_with_missing)

# Handle missing text safely in keyword search
keyword = 'value'
df_with_missing['Has_Value'] = df_with_missing['Feedback'].fillna('').str.lower().str.contains(keyword)
print(df_with_missing[['Feedback','Has_Value']])

# Incorrect grouping: counting feedback by wrong column (example mistake)
try:
    print(df.groupby('NonexistentColumn')['Feedback'].count())
except Exception as e:
    print('Error:', str(e))

# Misinterpreting rating scales or NPS scores (example correction)
nps_df = pd.DataFrame({'CustomerID':[1,2,3,4,5], 'NPS_Score':[10,9,6,4,7]})
nps_df['Promoter'] = nps_df['NPS_Score'] >= 9
nps_df['Detractor'] = nps_df['NPS_Score'] <= 6
nps_df['Passive'] = ~nps_df['Promoter'] & ~nps_df['Detractor']
print(nps_df)

# Pattern: Segmentation by demographic variable (gender)
seg_table = customer_df.groupby('gender')[[f'Has_{t.capitalize()}' for t in themes]].sum()
print('Feedback themes by gender:')
print(seg_table)

# Pattern: Cross-tabulation of two variables (Churn & Discount theme)
discount_cross = pd.crosstab(customer_df['Churn'], customer_df['Has_Discount'])
print('Churn vs Discount mentions:')
print(discount_cross)

# Pattern: Summarize qualitative and quantitative together
avg_monthly_charges = customer_df.groupby('Has_Billing')['MonthlyCharges'].mean()
print('Average monthly charges, grouped by mentions of billing issues:')
print(avg_monthly_charges)

# Pattern: Trend analysis (simulate by reshuffling feedback and grouping by first letter)
customer_df['LetterGroup'] = customer_df['Feedback'].str[0].str.upper()
trend_counts = customer_df.groupby('LetterGroup')[f'Has_Support'].sum()
print('Support theme mentions grouped by first letter of feedback:')
print(trend_counts)

# Mini business case: QA team reviews negative feedback for top themes
raw_feedback = pd.Series([
    'Support did not resolve my issue',
    'Very happy with new features',
    'Staff was polite but slow',
    'I love the app update',
    'Unclear billing, extra fees appeared',
    'Setup process was easier than expected',
    'App kept crashing for two days',
    'Promo offer did not apply correctly'
])
themes_case = ['support','billing','app','offer']
flags = pd.DataFrame({theme: raw_feedback.str.lower().str.contains(theme) for theme in themes_case})
summary = flags.sum().sort_values(ascending=False)
print('Count of flag mentions in feedback:')
print(summary)

# Forming an actionable business recommendation
top_issue = summary.idxmax()
recommendation = f'Our immediate focus should be addressing {top_issue}-related complaints, as it was mentioned most often.'
print(recommendation)