Decoding Social Media Engagement

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "post_id":     ["P001","P002","P003","P004","P005","P006","P007","P008",
                    "P009","P010","P011","P012","P013","P014","P015","P016",
                    "P017","P018","P019","P020"],
    "platform":    ["Instagram","Facebook","TikTok","LinkedIn",
                    "Instagram","Facebook","TikTok","LinkedIn",
                    "Instagram","Facebook","TikTok","LinkedIn",
                    "Instagram","Facebook","TikTok","LinkedIn",
                    "Instagram","Facebook","TikTok","LinkedIn"],
    "format":      ["Video","Image","Video","Text",
                    "Carousel","Text","Video","Image",
                    "Image","Video","Carousel","Text",
                    "Video","Carousel","Image","Video",
                    "Carousel","Image","Text","Carousel"],
    "reach":       [14200,8400,52000,3100,11600,7200,61000,2800,
                    9800,9100,48000,3400,15800,6600,55000,4200,
                    12400,7800,44000,3800],
    "likes":       [1820,390,6800,180,1340,210,8100,95,
                    880,540,5900,140,2100,280,7200,310,
                    1560,420,5100,320],
    "comments":    [142,48,820,64,108,32,940,28,
                    76,62,710,52,168,44,860,88,
                    124,56,640,74],
    "shares":      [310,62,1240,95,220,41,1580,38,
                    140,88,1020,60,380,72,1340,120,
                    280,94,880,98],
    "post_week":   [1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,5,5],
    "day_of_week": ["Monday","Tuesday","Wednesday","Thursday",
                    "Monday","Wednesday","Friday","Thursday",
                    "Tuesday","Monday","Wednesday","Thursday",
                    "Monday","Tuesday","Wednesday","Friday",
                    "Monday","Tuesday","Wednesday","Thursday"]
})

# Derive engagement metrics — must happen before any groupby
df["total_interactions"] = df["likes"] + df["comments"] + df["shares"]
df["engagement_rate"]    = (df["total_interactions"] / df["reach"]).round(4)

print(f"Posts analysed: {len(df)}")
print(f"Total reach:    {df['reach'].sum():,}")
print(f"Total interactions: {df['total_interactions'].sum():,}")
print(f"\nEngagement Rate — summary stats:")
print(df["engagement_rate"].describe().round(4).to_string())

# Top 3 posts by engagement rate
top3 = df.nlargest(3, "engagement_rate")[
    ["post_id","platform","format","reach","engagement_rate"]
]
print("\nTop 3 posts by engagement rate:")
print(top3.to_string(index=False))

# Platform-level performance aggregation
platform_stats = df.groupby("platform").agg(
    posts              = ("post_id",           "count"),
    total_reach        = ("reach",             "sum"),
    mean_reach         = ("reach",             "mean"),
    mean_eng_rate      = ("engagement_rate",   "mean"),
    total_interactions = ("total_interactions","sum")
).round(2).reset_index()

platform_stats = platform_stats.sort_values("mean_eng_rate", ascending=False)
platform_stats["eng_rate_pct"] = (platform_stats["mean_eng_rate"] * 100).round(1)

print("Platform performance — ranked by mean engagement rate:")
print(platform_stats[[
    "platform","posts","mean_reach","eng_rate_pct","total_interactions"
]].to_string(index=False))

# How much more engaged is TikTok vs Facebook?
tiktok_er = platform_stats.loc[platform_stats["platform"]=="TikTok","mean_eng_rate"].values[0]
fb_er     = platform_stats.loc[platform_stats["platform"]=="Facebook","mean_eng_rate"].values[0]
print(f"\nTikTok engagement rate is {tiktok_er/fb_er:.1f}x Facebook's rate")

# Reach share by platform
platform_stats["reach_share"] = (
    platform_stats["total_reach"] / platform_stats["total_reach"].sum() * 100
).round(1)
print("\nReach share by platform (%):")
print(platform_stats[["platform","total_reach","reach_share"]].to_string(index=False))

# Format performance
format_stats = df.groupby("format").agg(
    posts         = ("post_id",          "count"),
    mean_reach    = ("reach",            "mean"),
    mean_eng_rate = ("engagement_rate",  "mean"),
    mean_shares   = ("shares",           "mean"),
    mean_comments = ("comments",         "mean")
).round(2).reset_index()
format_stats = format_stats.sort_values("mean_eng_rate", ascending=False)
format_stats["eng_rate_pct"] = (format_stats["mean_eng_rate"] * 100).round(1)

print("Content format performance — ranked by engagement rate:")
print(format_stats[[
    "format","posts","mean_reach","eng_rate_pct","mean_shares","mean_comments"
]].to_string(index=False))

# Aggregate to weekly level, then correlate frequency with performance
weekly = df.groupby("post_week").agg(
    posts_that_week = ("post_id",         "count"),
    mean_reach      = ("reach",           "mean"),
    mean_eng_rate   = ("engagement_rate", "mean")
).reset_index()

freq_reach_corr = weekly["posts_that_week"].corr(weekly["mean_reach"]).round(3)
freq_eng_corr   = weekly["posts_that_week"].corr(weekly["mean_eng_rate"]).round(3)

print(f"\nWeekly posting frequency vs mean reach:       r = {freq_reach_corr:+.3f}")
print(f"Weekly posting frequency vs mean eng rate:    r = {freq_eng_corr:+.3f}")

# Day of week engagement
day_eng = df.groupby("day_of_week")["engagement_rate"].mean().round(4).sort_values(ascending=False)
print("\nMean engagement rate by day of week:")
print((day_eng * 100).round(1).to_string())

# Pivot: mean engagement rate by platform x format
pivot_eng = pd.pivot_table(
    df,
    index      = "platform",
    columns    = "format",
    values     = "engagement_rate",
    aggfunc    = "mean",
    fill_value = np.nan   # NaN = not tested, not zero
).round(4)

pivot_pct = (pivot_eng * 100).round(1)
print("Mean engagement rate (%) by platform x format:")
print(pivot_pct.to_string())

# Pivot: total reach by platform x format
pivot_reach = pd.pivot_table(
    df,
    index      = "platform",
    columns    = "format",
    values     = "reach",
    aggfunc    = "sum",
    fill_value = 0
)
print("\nTotal reach by platform x format:")
print(pivot_reach.to_string())

# Best-performing combination — stack collapses 2D to 1D, idxmax returns the label
best_idx = pivot_pct.stack().idxmax()
best_val = pivot_pct.stack().max()
print(f"\nBest platform-format combination: {best_idx[0]} · {best_idx[1]} at {best_val}% engagement")

# Share rate: fraction of viewers who share
df["share_rate"] = (df["shares"] / df["reach"]).round(5)

# Weighted engagement: likes x1, comments x2, shares x3
df["weighted_score"] = (
    (df["likes"] * 1 + df["comments"] * 2 + df["shares"] * 3) / df["reach"]
).round(4)

# Top 5 by weighted score
top5 = df.nlargest(5, "weighted_score")[
    ["post_id","platform","format","reach","engagement_rate","share_rate","weighted_score"]
]
print("Top 5 posts by share-weighted engagement score:")
print(top5.to_string(index=False))

# Share rate by platform and format
print("\nMean share rate by platform:")
print(df.groupby("platform")["share_rate"].mean().round(5).sort_values(ascending=False).to_string())

print("\nMean share rate by format:")
print(df.groupby("format")["share_rate"].mean().round(5).sort_values(ascending=False).to_string())

# Organic amplifiers: share rate >= 75th percentile
threshold  = df["share_rate"].quantile(0.75)
amplifiers = df[df["share_rate"] >= threshold][["post_id","platform","format","share_rate"]]
print(f"\nOrganic amplifiers (share rate >= {threshold:.4f}):")
print(amplifiers.sort_values("share_rate", ascending=False).to_string(index=False))