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

np.random.seed(42)
n_posts = 12
views = np.random.randint(100, 100000, n_posts)
df = pd.DataFrame({
    'post_id': range(1, n_posts+1),
    'platform': np.random.choice(['YouTube','Instagram','TikTok'], n_posts),
    'date': pd.date_range('2023-01-01', periods=n_posts, freq='1D'),
    'views': views,
    'likes': (views * np.random.uniform(0.04, 0.12, n_posts)).astype(int),
    'comments': (views * np.random.uniform(0.002, 0.03, n_posts)).astype(int),
    'shares': (views * np.random.uniform(0.001, 0.02, n_posts)).astype(int)
})
print(df.head())

df['engagement_rate'] = ((df['likes'] + df['comments'] + df['shares']) / df['views']).round(3)
print(df[['platform','views','likes','comments','shares','engagement_rate']].head())

top = df.sort_values('engagement_rate', ascending=False).head(3)
print('Top 3 posts by engagement rate:')
print(top[['post_id', 'platform', 'engagement_rate']])

by_platform = df.groupby('platform').agg({'views':'mean','likes':'mean','comments':'mean','shares':'mean','engagement_rate':'mean'})
print('Average metrics by platform:')
print(by_platform.round(2))

recent = df[df['date']>='2023-01-06']
trend = recent.groupby('platform').agg({'engagement_rate':'mean','views':'sum'})
print('Engagement rate and total views for recent posts:')
print(trend.round(3))

df['likes_per_comment'] = (df['likes'] / df['comments']).replace([np.inf, -np.inf], np.nan).round(2)
print(df[['platform','likes','comments','likes_per_comment']].head(7))

# Load YouTube video analytics data
np.random.seed(42)
n_videos = 10
views = np.random.randint(150, 50000, n_videos)
df_yt = pd.DataFrame({
    'video_id': range(1, n_videos+1),
    'publish_date': pd.date_range('2023-01-01', periods=n_videos, freq='D'),
    'views': views,
    'watch_time': np.random.randint(1000, 20000, n_videos),
    'likes': (views * np.random.uniform(0.01, 0.09, n_videos)).astype(int),
    'comments': (views * np.random.uniform(0.002, 0.018, n_videos)).astype(int),
    'ctr': np.round(np.random.uniform(2, 11, n_videos), 2)
})
print(df_yt.head())

df_yt['engagement_rate'] = ((df_yt['likes'] + df_yt['comments']) / df_yt['views']).round(3)
print('YouTube engagement rate vs CTR:')
print(df_yt[['video_id','ctr','engagement_rate']])

viral = df_yt[(df_yt['views'] > 30000) & (df_yt['ctr'] > 8)]
print('Viral candidates:')
print(viral[['video_id','views','ctr','engagement_rate']])

# Simulate missing engagement data for one video
df_yt.loc[3, 'likes'] = np.nan
df_yt.loc[3, 'comments'] = np.nan
df_yt['engagement_rate'] = ((df_yt['likes'] + df_yt['comments']) / df_yt['views']).round(3)
print(df_yt[['video_id','views','likes','comments','engagement_rate']])

# Example: Incorrect aggregation mistake
totals_wrong = df_yt.groupby('publish_date')['likes'].mean()
print('INCORRECT: average likes by date (should sum or median for daily recap):')
print(totals_wrong.head())

# Example: Misinterpreting CTR calculation
impressions = np.random.randint(5000, 100000, n_videos)
df_yt['computed_ctr'] = (df_yt['views'] / impressions * 100).round(2)
print(df_yt[['video_id','views','computed_ctr','ctr']].head())

# Example: Wrong grouping logic for engagement by category
df_yt['category'] = np.random.choice(['Education','Entertainment','News'], n_videos)
wrong_group = df_yt.groupby('video_id')['engagement_rate'].mean()
correct_group = df_yt.groupby('category')['engagement_rate'].mean()
print('Wrong: Grouping by video_id gives no summary.')
print(wrong_group.head(3))
print('Correct: Grouping by category for overall engagement.')
print(correct_group.round(3))

# Benchmarking content performance
mean_eng = df_yt['engagement_rate'].mean()
top_videos = df_yt[df_yt['engagement_rate'] > mean_eng]
print(f'Average engagement rate: {mean_eng:.3f}')
print(f'Number of above-average videos: {len(top_videos)}')

# Segment by content publish timing
df_yt['weekday'] = df_yt['publish_date'].dt.day_name()
weekday_eng = df_yt.groupby('weekday')['engagement_rate'].mean().sort_values(ascending=False)
print('Average engagement rate by day of week:')
print(weekday_eng.round(3))

# Analyze trends and growth
df_yt = df_yt.sort_values('publish_date')
rolling = df_yt['views'].rolling(window=3, min_periods=1).mean()
print('Three-video rolling average for views:')
print(rolling.round(1).values)

# End-to-end example: Recommend posting strategy
weekday_avg = df_yt.groupby('weekday')['engagement_rate'].mean()
best_day = weekday_avg.idxmax()
print(f'Recommendation: Based on current data, posting on {best_day} yields the highest engagement rate.')

# Optional: Save results to CSV
df_yt.to_csv('yt_engagement_summary.csv', index=False)
print('Analytics summary saved as yt_engagement_summary.csv.')