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

# Load the Social Media Content Dataset (synthetic data with reproducibility)
np.random.seed(42)
n_posts = 500
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='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.head(3))
print(df.shape)

# Let us quickly check the columns and missing values
print(df.columns.tolist())
print(df.isnull().sum())

# Let us intentionally introduce duplicate content records to simulate real-world issues
dupes = df.sample(frac=0.05, random_state=42)
df_with_dupes = pd.concat([df, dupes], ignore_index=True)
print(f'Total rows after inserting duplicates: {df_with_dupes.shape[0]}')

# BEGINNER 1: Count duplicated rows in the dataset
num_duplicates = df_with_dupes.duplicated().sum()
print(f'Number of fully duplicated rows: {num_duplicates}')

# BEGINNER 2: Preview the first few duplicate rows
duplicates = df_with_dupes[df_with_dupes.duplicated()]
print(duplicates.head(2))

# BEGINNER 3: Remove all fully duplicate rows
df_nodup = df_with_dupes.drop_duplicates()
print(f'Rows remaining after removal: {df_nodup.shape[0]}')

# BEGINNER 4: Check if post_id is unique after removing all duplicates
postid_unique = df_nodup['post_id'].is_unique
print(f'post_id uniqueness: {postid_unique}')

# BEGINNER 5: Remove duplicates based on only 'post_id', keeping the most recent date
df_latest = df_with_dupes.sort_values('date').drop_duplicates('post_id', keep='last')
print(df_latest[['post_id','platform','date']].head(3))

# INTERMEDIATE 1: Find posts duplicated by content columns (ignoring post_id)
dupe_columns = ['platform','date','views','likes','comments','shares']
partial_dupes = df_with_dupes.duplicated(subset=dupe_columns)
print(f'Partial duplicates based on engagement and time: {partial_dupes.sum()}')

# INTERMEDIATE 2: Visualize number of duplicates by platform
dupe_counts = df_with_dupes[df_with_dupes.duplicated('post_id', keep=False)].groupby('platform').size()
print('Duplicate counts by platform:')
print(dupe_counts)

# INTERMEDIATE 3: Aggregate total likes and comments per post with and without duplicates
engagement_original = df_with_dupes.groupby('post_id')[['likes','comments']].sum().sum()
engagement_nodup = df_nodup.groupby('post_id')[['likes','comments']].sum().sum()
print(f'Total likes+comments (with dupes): {engagement_original}')
print(f'Total likes+comments (no dupes): {engagement_nodup}')

# INTERMEDIATE 4: Detect duplicated engagement for the same post_id
dupe_postids = df_with_dupes['post_id'][df_with_dupes.duplicated('post_id')]
print('Sample duplicated post IDs:')
print(dupe_postids.head(5).tolist())

# INTERMEDIATE 5: Compare engagement distributions before and after deduplication
import matplotlib.pyplot as plt
plt.figure(figsize=(10,4))
df_with_dupes['likes'].hist(alpha=0.5, label='With Duplicates')
df_nodup['likes'].hist(alpha=0.5, label='No Duplicates')
plt.legend()
plt.xlabel('Likes per row')
plt.ylabel('Frequency')
plt.title('Likes Distribution With and Without Duplicates')
plt.tight_layout()
plt.savefig('likes_hist.png')
plt.show()