54 lines
2.0 KiB
Python
54 lines
2.0 KiB
Python
import sqlite3
|
|
from pathlib import Path
|
|
import pandas as pd
|
|
|
|
PROJECT_ROOT = Path(__file__).resolve().parent.parent
|
|
DB_PATH = PROJECT_ROOT / "data" / "hiffi_catalog.db"
|
|
|
|
conn = sqlite3.connect(DB_PATH)
|
|
|
|
# Pull full telemetry alongside platform URLs
|
|
query = """
|
|
SELECT
|
|
a.artist_id,
|
|
a.artist_name,
|
|
a.youtube_url,
|
|
t.subscribers_count AS yt_subscribers,
|
|
a.spotify_url,
|
|
t.spotify_monthly_listeners AS sp_monthly_listeners,
|
|
a.instagram_url,
|
|
t.instagram_followers AS ig_followers
|
|
FROM artists a
|
|
JOIN telemetry_metrics t ON a.artist_id = t.artist_id
|
|
WHERE a.image_verified_flag = 1
|
|
ORDER BY t.spotify_monthly_listeners DESC
|
|
"""
|
|
|
|
df = pd.read_sql_query(query, conn)
|
|
conn.close()
|
|
|
|
# 1. Verification Integrity Checks
|
|
zero_yt = df[df["yt_subscribers"].fillna(0) == 0]
|
|
zero_sp = df[df["sp_monthly_listeners"].fillna(0) == 0]
|
|
zero_ig = df[df["ig_followers"].fillna(0) == 0]
|
|
|
|
print("=== 500-ARTIST TELEMETRY PROOF AUDIT ===")
|
|
print(f"Total Cohort Verified Artists: {len(df)}")
|
|
print(f"Artists with YouTube Subscribers > 0: {len(df) - len(zero_yt)} / {len(df)}")
|
|
print(f"Artists with Spotify Monthly Listeners > 0: {len(df) - len(zero_sp)} / {len(df)}")
|
|
print(f"Artists with Instagram Followers > 0: {len(df) - len(zero_ig)} / {len(df)}")
|
|
|
|
if not zero_yt.empty:
|
|
print(f"\n[FLAG] {len(zero_yt)} artists still have 0 or null YouTube subscribers:")
|
|
print(zero_yt[["artist_name", "youtube_url", "yt_subscribers"]].head(10).to_string(index=False))
|
|
|
|
# Export complete proof ledger to CSV
|
|
output_path = PROJECT_ROOT / "data" / "platform_telemetry_proof_500.csv"
|
|
df.to_csv(output_path, index=False, encoding="utf-8")
|
|
print(f"\nSaved complete proof ledger to: {output_path}")
|
|
|
|
print("\n--- SAMPLE HIGH-REACH ENTRIES (TOP 5) ---")
|
|
print(df[["artist_name", "yt_subscribers", "sp_monthly_listeners", "ig_followers"]].head(5).to_string(index=False))
|
|
|
|
print("\n--- SAMPLE LOWEST-REACH ENTRIES (TAIL 5) ---")
|
|
print(df[["artist_name", "yt_subscribers", "sp_monthly_listeners", "ig_followers"]].tail(5).to_string(index=False)) |