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

# Load a YouTube analytics synthetic dataset for CTR analysis
np.random.seed(42)
n_videos = 300
views = np.random.randint(100, 500000, n_videos)
df = pd.DataFrame({
    'video_id': range(1, n_videos+1),
    'publish_date': pd.date_range('2022-01-01', periods=n_videos, freq='D'),
    'views': views,
    'watch_time': np.random.randint(1000, 500000, n_videos),
    'likes': (views * np.random.uniform(0.01, 0.08, n_videos)).astype(int),
    'comments': (views * np.random.uniform(0.001, 0.02, n_videos)).astype(int),
    'ctr': np.round(np.random.uniform(2, 10, n_videos), 2)
})
print(df.shape)
print(df.head(3))

# Beginner Example 1: Calculate average CTR for all videos
average_ctr = df['ctr'].mean()
print(f'Average Click-Through Rate across all videos: {average_ctr:.2f}%')

# Beginner Example 2: Find videos with CTR above 8%
high_ctr_videos = df[df['ctr'] > 8]
print(f'Number of videos with CTR above 8%: {len(high_ctr_videos)}')
print(high_ctr_videos[['video_id', 'views', 'ctr']].head(3))

# Beginner Example 3: Show videos with the lowest CTR
lowest_ctr = df.nsmallest(5, 'ctr')
print('Videos with the lowest CTR:')
print(lowest_ctr[['video_id', 'views', 'ctr']])

# Intermediate Example 1: Relationship between CTR and watch time
import matplotlib.pyplot as plt
plt.scatter(df['ctr'], df['watch_time'], alpha=0.6)
plt.xlabel('Click-Through Rate (%)')
plt.ylabel('Watch Time (minutes)')
plt.title('CTR vs Watch Time for YouTube Videos')
plt.show()

# Intermediate Example 2: Find top 10 videos by CTR AND by views
top_by_ctr = df.nlargest(10, 'ctr')
top_by_views = df.nlargest(10, 'views')
print('Top 10 by CTR:')
print(top_by_ctr[['video_id', 'ctr', 'views']])
print('Top 10 by Views:')
print(top_by_views[['video_id', 'ctr', 'views']])

# Intermediate Example 3: Calculate average watch time for videos with high vs low CTR
high_ctr_watch_time = df[df['ctr'] > 6]['watch_time'].mean()
low_ctr_watch_time = df[df['ctr'] <= 6]['watch_time'].mean()
print(f'Avg watch time (CTR > 6%): {high_ctr_watch_time:.1f} minutes')
print(f'Avg watch time (CTR <= 6%): {low_ctr_watch_time:.1f} minutes')

# Advanced Example 1: Analyze CTR distribution by month of publication
df['month'] = df['publish_date'].dt.to_period('M')
monthly_ctr = df.groupby('month')['ctr'].mean()
monthly_ctr.plot(kind='bar', figsize=(12,4), color='skyblue')
plt.title('Average CTR by Month of Publication')
plt.xlabel('Month')
plt.ylabel('Average CTR (%)')
plt.tight_layout()
plt.show()

# Advanced Example 2: Correlate likes, comments, and CTR to spot viral signals
correlations = df[['likes','comments','ctr']].corr()
print('Correlation matrix:')
print(correlations)

# Advanced Example 3: Detect outliers in CTR
q1 = df['ctr'].quantile(0.25)
q3 = df['ctr'].quantile(0.75)
iqr = q3 - q1
upper_bound = q3 + 1.5 * iqr
outliers = df[df['ctr'] > upper_bound]
print(f'Videos with CTR outliers (above {upper_bound:.2f}%):')
print(outliers[['video_id', 'ctr', 'views']])

# Error Handling Example 1: Handle missing or zero views
test_df = df.copy()
test_df.loc[0, 'views'] = 0
try:
    ctr_recalc = 100 * test_df.loc[0, 'likes'] / test_df.loc[0, 'views']
except ZeroDivisionError:
    ctr_recalc = None
print('Recalculated CTR with zero views:', ctr_recalc)

# Error Handling Example 2: Avoid summing CTR directly
sum_ctr_wrong = df['ctr'].sum()
corrected_avg_ctr = np.average(df['ctr'])
print(f'Wrong: Summing all CTRs = {sum_ctr_wrong:.2f}')
print(f'Right: Average CTR = {corrected_avg_ctr:.2f}%')

# Error Handling Example 3: Wrong grouping logic in CTR aggregation
monthly_total_views = df.groupby('month')['views'].sum()
monthly_ctr_wrong = df.groupby('month')['ctr'].sum()
monthly_ctr_right = df.groupby('month')['ctr'].mean()
print('CTR by summing per month (incorrect):')
print(monthly_ctr_wrong.head())
print('CTR by averaging per month (correct):')
print(monthly_ctr_right.head())

# Best Practice Example 1: Benchmark videos with above-average CTR
avg_ctr = df['ctr'].mean()
benchmarked = df[df['ctr'] > avg_ctr]
print(f'Total videos above average CTR ({avg_ctr:.2f}%): {benchmarked.shape[0]}')
print(benchmarked[['video_id', 'views', 'ctr']].head(3))

# Best Practice Example 2: Segment performance by top/bottom quartile of views
q25 = df['views'].quantile(0.25)
q75 = df['views'].quantile(0.75)
low_views = df[df['views'] <= q25]
high_views = df[df['views'] >= q75]
print(f'Average CTR for bottom quartile (low views): {low_views['ctr'].mean():.2f}%')
print(f'Average CTR for top quartile (high views): {high_views['ctr'].mean():.2f}%')

# Best Practice Example 3: Content optimization based on CTR and engagement
df['like_rate'] = 100 * df['likes'] / df['views']
df['engagement_score'] = (df['like_rate'] + df['ctr']) / 2
optimized = df.nlargest(5, 'engagement_score')
print('Top 5 optimized videos by combined CTR and like rate:')
print(optimized[['video_id', 'ctr', 'like_rate', 'engagement_score']])

# Strategy: Identify publish months with highest average CTR
ctr_by_month = df.groupby('month')['ctr'].mean()
best_month = ctr_by_month.idxmax()
print(f'Month with highest avg CTR: {best_month} ({ctr_by_month.max():.2f}%)')

# Strategy: Find traits of top CTR videos
top_ctr_videos = df.nlargest(10, 'ctr')
avg_like_rate_top = top_ctr_videos['like_rate'].mean()
avg_watch_time_top = top_ctr_videos['watch_time'].mean()
print('Top 10 CTR videos characteristics:')
print(f'Average like rate: {avg_like_rate_top:.2f}%')
print(f'Average watch time: {avg_watch_time_top:.1f} minutes')

# End-to-end recommendation: If high-CTR and high like rate cluster in certain months, focus content then
if best_month is not None:
    print(f'Recommend posting more high-quality videos targeting {str(best_month)} when audience CTR peaks.')
else:
    print('No strong monthly pattern detected. Focus on optimizing thumbnails and titles for higher CTR.')