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

# Example 1: Load Social Media Content Dataset (cross-platform posts)
np.random.seed(42)
n_posts = 500
views = np.random.randint(100, 100000, n_posts)
df_content = 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='6h'),
    'views': views,
    'likes': (views * np.random.uniform(0.02, 0.15, n_posts)).astype(int),
    'comments': (views * np.random.uniform(0.001, 0.05, n_posts)).astype(int),
    'shares': (views * np.random.uniform(0.001, 0.03, n_posts)).astype(int)
})
print(df_content.shape)
print(df_content.head(3))

# Example 2: Load YouTube Analytics Dataset (video-level metrics)
np.random.seed(42)
n_videos = 300
views_yta = np.random.randint(100, 500000, n_videos)
df_yt = pd.DataFrame({
    'video_id': range(1, n_videos+1),
    'publish_date': pd.date_range('2022-01-01', periods=n_videos, freq='D'),
    'views': views_yta,
    'watch_time': np.random.randint(1000, 500000, n_videos),
    'likes': (views_yta * np.random.uniform(0.01, 0.08, n_videos)).astype(int),
    'comments': (views_yta * np.random.uniform(0.001, 0.02, n_videos)).astype(int),
    'ctr': np.round(np.random.uniform(2, 10, n_videos), 2)
})
print(df_yt.shape)
print(df_yt.head(3))

# Beginner Example 1: Calculate simple engagement rate (likes/views) for all posts
df_content['engagement_rate'] = np.round(df_content['likes'] / df_content['views'] * 100, 2)
print(df_content[['platform', 'likes', 'views', 'engagement_rate']].head(5))

# Beginner Example 2: Find posts with highest engagement rate
top_eng_posts = df_content.sort_values('engagement_rate', ascending=False).head(5)
print(top_eng_posts[['platform', 'date', 'views', 'likes', 'engagement_rate']])

# Beginner Example 3: Basic content volume chart by platform
ax = df_content['platform'].value_counts().plot(kind='bar', color=['red','mediumvioletred','orange'])
plt.title('Content Volume by Platform')
plt.ylabel('Number of Posts')
plt.xlabel('Platform')
plt.tight_layout()
plt.show()

# Intermediate Example 1: Analyze average engagement rate over time
df_content['date_day'] = df_content['date'].dt.date
daily_eng = df_content.groupby('date_day')['engagement_rate'].mean()
plt.figure(figsize=(10,4))
daily_eng.plot()
plt.title('Average Engagement Rate Over Time')
plt.ylabel('Engagement Rate (%)')
plt.xlabel('Date')
plt.tight_layout()
plt.show()

# Intermediate Example 2: Top 5 days with most viral content (high shares)
viral_days = df_content.groupby('date_day')['shares'].sum().sort_values(ascending=False).head(5)
print(viral_days)

# Intermediate Example 3: Calculating total and average YouTube metrics by month
df_yt['month'] = df_yt['publish_date'].dt.to_period('M')
monthly_stats = df_yt.groupby('month')[['views', 'likes', 'comments', 'watch_time', 'ctr']].agg(['sum', 'mean'])
print(monthly_stats.head(3))

# Intermediate Example 4: Correlation heatmap of YouTube metrics
plt.figure(figsize=(8,6))
corr = df_yt[['views', 'likes', 'comments', 'watch_time', 'ctr']].corr()
sns.heatmap(corr, annot=True, cmap='coolwarm', fmt='.2f')
plt.title('Correlation Heatmap of YouTube Video Metrics')
plt.tight_layout()
plt.show()

# Intermediate Example 5: Identify YouTube videos above 90th percentile for both views and CTR
views_cutoff = df_yt['views'].quantile(0.9)
ctr_cutoff = df_yt['ctr'].quantile(0.9)
viral_videos = df_yt[(df_yt['views'] >= views_cutoff) & (df_yt['ctr'] >= ctr_cutoff)]
print('Number of ultra-viral videos:', len(viral_videos))
print(viral_videos[['video_id', 'publish_date', 'views', 'ctr']])

# Advanced Example 1: Platform engagement benchmarking (boxplot comparison)
plt.figure(figsize=(8,6))
sns.boxplot(x='platform', y='engagement_rate', data=df_content, palette='Set2')
plt.title('Engagement Rate Distribution by Platform')
plt.ylabel('Engagement Rate (%)')
plt.xlabel('Platform')
plt.tight_layout()
plt.show()

# Advanced Example 2: Identify underperforming dates (low overall engagement)
low_eng = daily_eng.nsmallest(5)
print('Bottom engagement days:')
print(low_eng)

# Advanced Example 3: Rolling average analysis for engagement trends
df_content['rolling_engagement_7d'] = df_content.sort_values('date')['engagement_rate'].rolling(28).mean()
plt.figure(figsize=(10,4))
plt.plot(df_content['date'], df_content['rolling_engagement_7d'], label='28-Post Rolling Average')
plt.title('Rolling Average Engagement Rate (28 posts)')
plt.ylabel('Engagement Rate (%)')
plt.xlabel('Date')
plt.legend()
plt.tight_layout()
plt.show()

# Advanced Example 4: Content category performance - group by platform and engagement
platform_perf = df_content.groupby('platform')['engagement_rate'].agg(['mean', 'median', 'std', 'count'])
print(platform_perf)

# Error Example 1: Handle missing likes values (simulate by corrupting some rows)
df_error = df_content.copy()
df_error.loc[df_error.sample(frac=0.03, random_state=42).index, 'likes'] = np.nan
missing_likes = df_error['likes'].isna().sum()
print(f'Missing values in likes: {missing_likes}')
df_error['likes_filled'] = df_error['likes'].fillna(df_error['likes'].median())

# Error Example 2: Incorrect aggregation (sum of engagement rate)
wrong_agg = df_content.groupby('platform')['engagement_rate'].sum()
right_agg = df_content.groupby('platform').apply(lambda d: d['likes'].sum() / d['views'].sum() * 100)
print('Incorrect sum:', wrong_agg.head(3))
print('Correct engagement %:', right_agg.head(3))

# Error Example 3: Misinterpreting CTR (Click-Through Rate)
avg_ctr = df_yt['ctr'].mean()
print(f'Average CTR across all YouTube videos: {avg_ctr:.2f}%')
low_ctr_videos = df_yt[df_yt['ctr'] < 3.0]
print(f'Videos with very low CTR (<3%):', len(low_ctr_videos))

# Error Example 4: Grouping by wrong column (group by video_id instead of date)
wrong_group = df_yt.groupby('video_id')['views'].sum().head()
correct_group = df_yt.groupby(df_yt['publish_date'].dt.month)['views'].sum().head()
print('Sum by video_id:', wrong_group)
print('Sum by month:', correct_group)

# End-to-End Example: Build a simple cross-platform analytics dashboard
summary = df_content.groupby('platform').agg({
    'views': 'sum',
    'likes': 'sum',
    'comments': 'sum',
    'shares': 'sum',
    'engagement_rate': 'mean'
})
plt.figure(figsize=(9,7))
summary[['views','likes','comments','shares']].plot(kind='bar', subplots=True, layout=(2,2), sharex=True, legend=False, figsize=(9,7))
plt.suptitle('Cross-Platform Content Engagement Dashboard')
plt.tight_layout(rect=[0,0,1,0.95])
plt.show()
print('Mean engagement rate by platform:')
print(summary['engagement_rate'])

# Strategy Recommendation: Choose the strongest platform and best post timing
best_platform = summary['engagement_rate'].idxmax()
busiest_times = df_content.groupby(df_content['date'].dt.hour)['engagement_rate'].mean().sort_values(ascending=False).head(3)
print(f'The platform with the highest avg engagement: {best_platform}')
print('The hours with highest engagement rates:')
print(busiest_times)

# Export dashboard summary to CSV for sharing
summary.to_csv('dashboard_summary.csv')

# Draw a simple dashboard header as an image for reporting
import matplotlib.patches as mpatches
fig, ax = plt.subplots(figsize=(6,1))
ax.set_axis_off()
ax.text(0.5, 0.5, 'Social Media Content Analytics Dashboard',
        va='center', ha='center', fontsize=20, weight='bold', color='navy')
plt.savefig('dashboard_header.png', bbox_inches='tight', pad_inches=0.2)
plt.close(fig)