{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Select Preference Data" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:04.757824Z", "start_time": "2024-05-26T00:11:04.555293Z" }, "execution": { "iopub.execute_input": "2024-08-10T12:02:09.573025Z", "iopub.status.busy": "2024-08-10T12:02:09.572523Z", "iopub.status.idle": "2024-08-10T12:02:09.734304Z", "shell.execute_reply": "2024-08-10T12:02:09.733794Z", "shell.execute_reply.started": "2024-08-10T12:02:09.573005Z" } }, "outputs": [], "source": [ "# setup tailscale if you haven't\n", "# https://tailscale.com/kb/1031/install-linux\n", "!sudo tailscale up --accept-routes=true\n", "\n", "# setup autoload\n", "%load_ext autoreload\n", "%autoreload 2" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:08.392310Z", "start_time": "2024-05-26T00:11:04.759383Z" }, "execution": { "iopub.execute_input": "2024-08-10T12:02:10.225037Z", "iopub.status.busy": "2024-08-10T12:02:10.224697Z", "iopub.status.idle": "2024-08-10T12:02:16.234381Z", "shell.execute_reply": "2024-08-10T12:02:16.233839Z", "shell.execute_reply.started": "2024-08-10T12:02:10.225019Z" } }, "outputs": [], "source": [ "# pip install psycopg2-binary\n", "# make sure sqlalchemy is >=2\n", "import ast\n", "import json\n", "from datetime import datetime\n", "from urllib.parse import quote\n", "\n", "import boto3\n", "import matplotlib.pyplot as plt\n", "import numpy as np\n", "import pandas as pd\n", "import sqlalchemy\n", "import tqdm\n", "from botocore.exceptions import ClientError\n", "from preference_helper import *\n", "from preference_helper import get_preferfence_counts\n", "from suno_utils.audio import Audio\n", "from suno_utils.utils.s3 import open_from_s3\n", "\n", "pd.set_option(\"display.max_rows\", 500)\n", "pd.set_option(\"display.max_columns\", 500)\n", "pd.set_option(\"display.width\", 1000)\n", "\n", "\n", "def get_secret():\n", " secret_name = \"rds!cluster-a3b66c33-40a7-47dd-bd6e-32b1c17c9124\"\n", " region_name = \"us-east-2\"\n", " # Create a Secrets Manager client\n", " session = boto3.session.Session()\n", " client = session.client(service_name=\"secretsmanager\", region_name=region_name)\n", " try:\n", " get_secret_value_response = client.get_secret_value(SecretId=secret_name)\n", " except ClientError as e:\n", " raise e\n", " secret = get_secret_value_response[\"SecretString\"]\n", " return json.loads(secret)\n", "\n", "\n", "my_secrets = get_secret()\n", "\n", "# engine = sqlalchemy.create_engine(\"postgresql://tony:123@localhost/mydatabase\")\n", "# alternative...\n", "engine = sqlalchemy.create_engine(\n", " \"postgresql://postgres:%s@suno-main-pgdb-prod-analytics.cnfvffydbwvc.us-east-2.rds.amazonaws.com/suno_main\"\n", " % quote(my_secrets[\"password\"])\n", ")\n", "# connection = engine.raw_connection()\n", "\n", "# !pip install snowflake\n", "from snowflake.core import Root\n", "from snowflake.snowpark import Session\n", "\n", "snow_password_path = \"/home/tony/.aws/snow_pw.txt\"\n", "with open(snow_password_path, \"r\") as fp:\n", " snow_password = fp.readlines()[0].strip()\n", "\n", "CONNECTION_PARAMETERS = {\n", " \"account\": \"fu90569.us-east-2.aws\",\n", " \"user\": \"TONY\",\n", " \"password\": snow_password,\n", " \"role\": \"ACCOUNTADMIN\",\n", " \"database\": \"SUNO_PROD\",\n", " \"warehouse\": \"SUNO_PROD_LARGE\",\n", " \"schema\": \"PROD\",\n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Validate some info" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:08.550447Z", "start_time": "2024-05-26T00:11:08.397196Z" } }, "outputs": [], "source": [ "# cutoff_date = \"2024-05-24 05:14:04\" # v3.5 early\n", "# cutoff_date = \"2024-05-30 02:03:11\" # v3.5 - 6\n", "# cutoff_date = \"2024-06-02 01:55:17\"\n", "# cutoff_date = \"2024-06-04 13:21:36\"\n", "# cutoff_date = \"2024-06-12 13:00:00\" # v3.5 extend\n", "# cutoff_date = \"2024-06-18 00:00:00\" # user feedback out\n", "# cutoff_date = \"2024-06-24 16:34:00\" # current time\n", "# 2 cutoff_date = \"2024-06-26 03:50:00\" # s-11 out\n", "cutoff_date = \"2024-06-27 03:50:00\" # s-8 out\n", "# cutoff_date = \"2024-06-27 03:50:00\" # s-12 out\n", "# cutoff_date = \" 2024-06-29 03:00:00\" # s-13 end\n", "# cutoff_date = \"2024-07-01 05:00:00\" # s-14 out\n", "# cutoff_date = \"2024-07-05 03:10:00\" # s-14 out\n", "# cutoff_date = \"2024-07-10 04:10:00\" # ft-1 out\n", "# cutoff_date = \"2024-07-10 14:45:00\" # no-top-p out\n", "# cutoff_date = \"2024-07-10 19:35:00\" # 2h ft end ~ 4hr difference\n", "# cutoff_date = \"2024-07-11 23:35:00\" # v4 first test\n", "# cutoff_date = \"2024-07-13 04:30:00\" # s-18/19 out\n", "# cutoff_date = \"2024-07-14 04:45:00\" # ft-2 out\n", "# cutoff_date = \"2024-07-15 12:45:00\" # test\n", "# cutoff_date = \"2024-07-16 00:00:00\" # v4 collection out\n", "# cutoff_date = \"2024-07-26 00:00:00\" # v4 collection out\n", "# cutoff_date = \"2024-08-09 21:00:00\" # v4-t2 out" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:09.349857Z", "start_time": "2024-05-26T00:11:08.551408Z" } }, "outputs": [], "source": [ "df_all_tables = pd.read_sql_query(\n", " \"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'\",\n", " engine,\n", ")\n", "# should have all the basic table names here\n", "assert df_all_tables[\"table_name\"].nunique() >= 61" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Query the DB" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:16.899029Z", "start_time": "2024-05-26T00:11:09.351909Z" } }, "outputs": [], "source": [ "# bots_generatedclipextra\n", "# 'clip_id', 'created_at', 'updated_at', 'download_audio_count', 'download_video_count', 'share_count', 'is_public_approved', 'inferred_language', 'download_audio_wav_count\n", "# these are all the logged actions in the prod db\n", "query = f\"\"\"\n", "SELECT * FROM bots_generatedclipextra\n", "WHERE updated_at>='{cutoff_date}'\n", "\"\"\"\n", "bots_action_df = pd.read_sql_query(query, engine)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:12:13.867775Z", "start_time": "2024-05-26T00:11:16.900416Z" } }, "outputs": [], "source": [ "# ~ 16 min...X.x\n", "# bots_userreaction\n", "# 'id', 'play_count', 'skip_count', 'flagged', 'flagged_reason', 'reaction_type', 'updated_at', 'clip_id', 'user_id', 'feedback_reason'\n", "# this turns out to be much smaller ~ 570k\n", "query = f\"\"\"\n", "SELECT * FROM bots_userreaction\n", "WHERE updated_at>='{cutoff_date}' AND play_count>0\n", "\"\"\"\n", "reaction_df = pd.read_sql_query(query, engine)\n", "print(f\"{reaction_df.shape[0]:,} rows\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:12:14.002431Z", "start_time": "2024-05-26T00:12:13.869298Z" } }, "outputs": [], "source": [ "upvoted_df = reaction_df[reaction_df[\"reaction_type\"] == \"L\"].copy()\n", "print(f\"number of upvoates: {upvoted_df.shape[0]:,} rows\")\n", "upvoted_ids = upvoted_df[\"clip_id\"]\n", "\n", "flagged_df = reaction_df[reaction_df[\"flagged\"]].copy()\n", "print(f\"number of flagged reports: {flagged_df.shape[0]:,} rows\")\n", "flagged_ids = flagged_df[\"clip_id\"]\n", "\n", "# check basic reaction -- the rate should be very low\n", "print(reaction_df.tail(n=10000)[\"reaction_type\"].value_counts(normalize=True))" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:12:14.504607Z", "start_time": "2024-05-26T00:12:14.296331Z" } }, "outputs": [], "source": [ "# these are continues, ~ 1,485k (much more than likes) ~ takes 3.5 mins\n", "# columns are:\n", "# 'id', 'created_at', 'updated_at', 'time_used', 'metadata', 'user_id',\n", "# 'status', 'discord_message_id', 'prompt_id', 'request_id',\n", "# 'is_generated', 's3_id', 'upvote_count', 'batch_index', 'model_name',\n", "# 'prompt_text', 'daily_theme_id', 'is_deleted', 'image_s3_id',\n", "# 'is_public', 'dislike_count', 'flag_count', 'play_count', 'skip_count',\n", "# 'title', 'is_public_approved', 'slug'\n", "\n", "# find all the complete clips -- this query takes ~ 10 sec\n", "# query = \"\"\"\n", "# SELECT COUNT(*) FROM bots_generatedclip\n", "# \"\"\"\n", "# clip_counts = pd.read_sql_query(query, engine)\n", "# all_total_clip_counts = clip_counts[\"count\"][0]\n", "# print(f\"all version total clips: {all_total_clip_counts}\")\n", "\n", "# query = \"\"\"\n", "# SELECT COUNT(*) FROM bots_generatedclip\n", "# WHERE status='complete' AND model_name::text LIKE '%%v3%%'\n", "# \"\"\"\n", "# clip_counts = pd.read_sql_query(query, engine)\n", "# all_total_clip_counts = clip_counts[\"count\"][0]\n", "# print(f\"v3 version total clips: {all_total_clip_counts}\")\n", "\n", "# ~ 1h 25 mins...or, 3 days takes ~ 15 mins\n", "# NOTE that we need to query everything cause contact / continue can come from another model\n", "\n", "# TODO: query only v3 here....\n", "# This is still a lot...we will have to do this in steps very soon\n", "# Some data eng required, disk is much cheaper\n", "# the generated clips table has play count issues (we need to read it without filtering on playcounts)\n", "# AND model_name::text LIKE '%%v3%%' AND play_count>=1\n", "# AND model_name::text LIKE '%%v3p5%%'\n", "# WHERE status='complete' AND created_at>='{cutoff_date}' AND model_name::text='chirp-v3p5-engine-t-1'\n", "query = f\"\"\"\n", "SELECT * FROM bots_generatedclip\n", "WHERE status='complete' AND created_at>='{cutoff_date}' AND model_name::text='chirp-v3p5-engine-upload-4'\n", "\"\"\"\n", "total_clip_df = pd.read_sql_query(query, engine)\n", "print(f\"{total_clip_df.shape[0]:,} rows\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:22:56.476914Z", "start_time": "2024-05-26T00:22:06.973099Z" } }, "outputs": [], "source": [ "# get playlists\n", "query = f\"\"\"\n", "SELECT * FROM bots_playlistclip\n", "WHERE updated_at>='{cutoff_date}'\n", "\"\"\"\n", "playlist_clip_df = pd.read_sql_query(query, engine)\n", "print(f\"{playlist_clip_df.shape[0]:,} playlists updated\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT *\n", "FROM auth_user_groups\n", "\"\"\"\n", "auth_user_df = pd.read_sql_query(query, engine)\n", "print(\"authenticated users\", auth_user_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT * FROM bots_discordinfo\n", "WHERE subscription_status IN ('active', 'past_due')\n", "\"\"\"\n", "df_discord_info = pd.read_sql_query(\n", " query,\n", " engine,\n", ")\n", "# current active subscribers?\n", "print(df_discord_info[\"subscription_status\"].value_counts())\n", "# this is probably the right way to figure out the pro user group\n", "pro_users = set(df_discord_info[\"user_id\"].unique())" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# filter on versions\n", "# clip_df = total_clip_df[\n", "# ((total_clip_df[\"model_name\"].str.contains(\"v3\")) | (total_clip_df[\"model_name\"].str.contains(\"v4\"))) # or v3...\n", "# & (total_clip_df[\"created_at\"] >= \"2024-02-20\")\n", "# ].copy()\n", "clip_df = total_clip_df.copy()\n", "# print(f\"total v3 selected fraction = {clip_df.shape[0] / all_total_clip_counts}\")\n", "total_clip_counts = clip_df.shape[0]\n", "print(f\"total clips: {total_clip_counts}\")\n", "# check the number of audio uploads\n", "upload_clip_df = total_clip_df[total_clip_df[\"s3_id\"].str.startswith(\"m_\")].copy()\n", "print(\"total uploads:\", (total_clip_df[\"model_name\"] == \"\").sum(), upload_clip_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Pluse time series check\n", "(total_clip_df[\"created_at\"].dt.hour + total_clip_df[\"created_at\"].dt.day * 24).hist(\n", " bins=100\n", ")\n", "plt.show()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Proceed with feature engineering and cleaning up" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# this is very interesting....\n", "# reaction check\n", "reaction_df[\"is_pro_user\"] = reaction_df[\"user_id\"].isin(pro_users)\n", "pro_reactions_frac = reaction_df[\"is_pro_user\"].value_counts(normalize=True)\n", "print(\"Pro reactions fraction by category:\")\n", "for category, fraction in pro_reactions_frac.items():\n", " print(f\"{category}: {fraction:.2%}\")\n", "# clip check\n", "clip_df[\"is_pro_user\"] = clip_df[\"user_id\"].isin(pro_users)\n", "pro_gen_frac = clip_df[\"is_pro_user\"].value_counts(normalize=True)\n", "print(\"Pro generation fraction by category:\")\n", "for category, fraction in pro_gen_frac.items():\n", " print(f\"{category}: {fraction:.2%}\")\n", "print(\n", " \"pro users with generations\", clip_df[\"user_id\"][clip_df[\"is_pro_user\"]].nunique()\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:12.019778Z", "start_time": "2024-05-26T00:22:57.637371Z" } }, "outputs": [], "source": [ "# add clip is in playlist feature\n", "# check if a clip is in a playlist\n", "clip_df[\"is_in_playlist\"] = clip_df[\"id\"].isin(playlist_clip_df[\"clip_id\"].unique())" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def parse_parent_id(x):\n", " \"\"\"Find out a clip's parent id.\"\"\"\n", " if \"history\" not in x:\n", " return None\n", " out = x.get(\"history\", [])\n", " if not isinstance(out, list) or len(out) == 0:\n", " return None\n", " # take the last one cause we continue off the children?\n", " out = out[-1]\n", " if isinstance(out, dict):\n", " # this is the continued info, which is a dict with id and continue_at\n", " return out[\"id\"]\n", " else:\n", " return None\n", "\n", "\n", "def parse_duration(x):\n", " \"\"\"Find out a clip's duration.\"\"\"\n", " if \"duration\" not in x:\n", " return None\n", " return x.get(\"duration\")\n", "\n", "\n", "def parse_source(x):\n", " \"\"\"Find out a clip's source (web/ios).\"\"\"\n", " if \"source\" not in x:\n", " return None\n", " return x.get(\"source\")\n", "\n", "\n", "def parse_metadata_for_basics(x):\n", " \"\"\"Parse the metadata for basics.\"\"\"\n", " parent_id = parse_parent_id(x)\n", " duration = parse_duration(x)\n", " source = parse_source(x)\n", " return parent_id, duration, source\n", "\n", "\n", "clip_df[[\"continued_parent\", \"duration\", \"source\"]] = pd.DataFrame(\n", " clip_df[\"metadata\"].map(parse_metadata_for_basics).tolist(), index=clip_df.index\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:17.912428Z", "start_time": "2024-05-26T00:23:12.021726Z" } }, "outputs": [], "source": [ "clip_history_df = clip_df[~clip_df[\"continued_parent\"].isna()].copy()\n", "continued_ids = clip_history_df[\"id\"]\n", "# these are the direct parent's ids -- not grandparents\n", "has_continued_children_ids = clip_history_df[\"continued_parent\"]\n", "print(\n", " \"clips that have children:\",\n", " len(has_continued_children_ids),\n", " \"\\n clips that are parents:\",\n", " has_continued_children_ids.nunique(),\n", " \"\\n\",\n", " \"Average continues from clip = \",\n", " round(\n", " len(has_continued_children_ids) / len(has_continued_children_ids.unique()), 2\n", " ),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:22.092835Z", "start_time": "2024-05-26T00:23:17.914456Z" } }, "outputs": [], "source": [ "print(\"total uploads:\", (clip_df[\"model_name\"] == \"\").sum())\n", "# the nans are concats, we want to drop them for now\n", "concated_clips = clip_df[clip_df[\"request_id\"].isna()].copy()\n", "# need to kick them out...\n", "clip_df = clip_df[~clip_df[\"request_id\"].isna()]\n", "print(\n", " f\"Clips without request id (concat, uploads...) frac = {concated_clips.shape[0] / total_clip_counts:.5f}\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:22.833148Z", "start_time": "2024-05-26T00:23:22.094796Z" } }, "outputs": [], "source": [ "# check the model conts\n", "value_counts = clip_df[\"model_name\"].value_counts()\n", "total_count = value_counts.sum()\n", "for model_name, count in value_counts.items():\n", " model_fraction = round(count / total_count, 5)\n", " print(f\"{model_name} --> {model_fraction:.5f}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:26.317699Z", "start_time": "2024-05-26T00:23:22.835074Z" } }, "outputs": [], "source": [ "print(\"pre-filter model type clip_df shape:\", clip_df.shape)\n", "clip_df = clip_df[\n", " clip_df[\"model_name\"].isin(\n", " [\n", " \"chirp-v2-xxl-alpha\",\n", " \"chirp-v3-engine-i\",\n", " \"chirp-v3p5-engine-d\",\n", " \"chirp-v3p5-engine-s\",\n", " \"chirp-v3p5-engine-s-8\",\n", " \"chirp-v3p5-engine-s-14\",\n", " \"chirp-v3p5-engine-s-15\",\n", " \"chirp-v3p5-engine-s-18\",\n", " \"chirp-v3p5-engine-s-19\",\n", " \"chirp-v3p5-engine-s-20\",\n", " \"chirp-v3p5-engine-s-21\",\n", " \"chirp-v3p5-engine-s-8-paged\",\n", " \"chirp-v3p5-engine-ft\",\n", " \"chirp-v3p5-engine-ft-1\",\n", " \"chirp-v3p5-engine-s-8-no-top-p\",\n", " \"chirp-v3p5-engine-ft-2\",\n", " \"chirp-v3p5-engine-ft-3\",\n", " \"chirp-v3p5-engine-ft-4\",\n", " \"chirp-v3p5-engine-ft-5\",\n", " \"chirp-v3p5-engine-ft-6\",\n", " \"\",\n", " \"chirp-v3p5-engine-b\",\n", " \"chirp-v3p5-engine-upload\",\n", " \"chirp-v3p5-engine-upload-4\",\n", " \"chirp-v3p5-engine-t\",\n", " \"chirp-v3p5-engine-t-1\",\n", " \"chirp-v3p5-engine-t-1-5\",\n", " \"chirp-v3p5-engine-t-1-7\",\n", " \"chirp-v3p5-engine-t-1-fast\",\n", " \"chirp-v3p5-engine-t-1-fast-sem\",\n", " \"chirp-v3p5-engine-t-1-10\",\n", " \"chirp-v3p5-engine-t-1-11\",\n", " \"chirp-v3p5-engine-t-1-12\",\n", " \"chirp-v3p5-engine-t-1-13\",\n", " \"chirp-v3p5-engine-t-1-14\",\n", " \"chirp-v3p5-engine-t-2\",\n", " ]\n", " )\n", "]\n", "print(\"post-filter model type clip_df shape:\", clip_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:26.994407Z", "start_time": "2024-05-26T00:23:26.319359Z" } }, "outputs": [], "source": [ "print(clip_df[\"model_name\"].value_counts())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:33.488829Z", "start_time": "2024-05-26T00:23:27.161663Z" } }, "outputs": [], "source": [ "# ~ only 1 min :)\n", "# find all the concact clip reactions\n", "concat_reaction_df = reaction_df[\n", " reaction_df[\"clip_id\"].isin(concated_clips[\"id\"])\n", "].copy()\n", "print(\n", " \"concat reactions:\",\n", " concat_reaction_df.shape[0],\n", " \"unique concat clips:\",\n", " concat_reaction_df[\"clip_id\"].nunique(),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "concat_reaction_df[\"upvote_count\"] = concat_reaction_df[\"reaction_type\"] == \"L\"\n", "concat_reaction_df[\"dislike_count\"] = concat_reaction_df[\"reaction_type\"] == \"D\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:34.169887Z", "start_time": "2024-05-26T00:23:33.491114Z" } }, "outputs": [], "source": [ "concat_total_reaction_df_sum = concat_reaction_df.groupby(\"clip_id\")[\n", " [\"play_count\", \"upvote_count\", \"dislike_count\"]\n", "].sum()\n", "concat_total_reaction_df_sum_df = concat_total_reaction_df_sum.reset_index().rename(\n", " columns={\n", " \"clip_id\": \"id\",\n", " \"play_count\": \"reaction_play_count\",\n", " \"upvote_count\": \"reaction_upvote_count\",\n", " \"dislike_count\": \"reaction_dislike_count\",\n", " }\n", ")\n", "concated_clips = concated_clips.merge(\n", " concat_total_reaction_df_sum_df, on=\"id\", how=\"left\"\n", ")\n", "\n", "print(\"total concats\", concated_clips.shape)\n", "print(\n", " \"check \\n\",\n", " concated_clips[\n", " [\"reaction_play_count\", \"reaction_upvote_count\", \"reaction_dislike_count\"]\n", " ].describe(),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# TODO: why so many clips are concats without plays??? -- oh probably they concat multiple times?\n", "print(\"All concats\", concated_clips.shape[0])\n", "concated_clips = concated_clips[concated_clips[\"reaction_play_count\"] > 0]\n", "print(\"total concats with plays\", concated_clips.shape[0])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:38.974479Z", "start_time": "2024-05-26T00:23:34.385507Z" } }, "outputs": [], "source": [ "# this is each clip. and the mapped start time of the clip\n", "# for a clip in the concat history, we want to know which part it is starting / ending\n", "concat_clips_ids = {}\n", "history_error_counter = 0\n", "history_duplicate_error_counter = 0\n", "for _, row in tqdm.tqdm(concated_clips.iterrows()):\n", " if concat_history_clips := row[\"metadata\"].get(\"concat_history\"):\n", " total_duration = row[\"metadata\"].get(\"duration\", 0)\n", " if total_duration == 0:\n", " print(row[\"metadata\"], row[\"model_name\"])\n", " continue\n", " start_s = 0\n", " for history_clip in concat_history_clips:\n", " if isinstance(history_clip, dict) and \"id\" in history_clip:\n", " # the other key is `continue_at`\n", " if history_clip[\"id\"]:\n", " # in case a clip ends up in multiple concats, we need to choose the optimal one\n", " if history_clip[\"id\"] in concat_clips_ids:\n", " # keep the highest upvote clip\n", " if (\n", " row[\"reaction_upvote_count\"]\n", " < concat_clips_ids[history_clip[\"id\"]][\"concat_likes\"]\n", " ):\n", " continue\n", " # then keep the highest play count clip\n", " if (\n", " row[\"reaction_play_count\"]\n", " < concat_clips_ids[history_clip[\"id\"]][\"concat_play_counts\"]\n", " ):\n", " continue\n", " # multi-seed to concats\n", " history_duplicate_error_counter += 1\n", " concat_clips_ids[history_clip[\"id\"]] = {\n", " \"total_start_s\": start_s,\n", " \"total_clip_s\": total_duration,\n", " \"concat_play_counts\": row[\"reaction_play_count\"],\n", " \"concat_in_playlist\": row[\"is_in_playlist\"],\n", " \"concat_likes\": row[\"reaction_upvote_count\"],\n", " \"concat_dislikes\": row[\"reaction_dislike_count\"],\n", " }\n", " else:\n", " history_error_counter += 1\n", " try:\n", " # but we always update the start_s -- but keep the relative orders\n", " if history_clip[\"continue_at\"] is None:\n", " # we need to go back and fetch the duration\n", " if history_clip[\"id\"] in clip_df[\"id\"]:\n", " start_s += clip_df[clip_df[\"id\"] == history_clip[\"id\"]][\n", " \"duration\"\n", " ].iloc[0]\n", " else:\n", " # This is wrong but what can we do...\n", " # this clip isn't kept in the query\n", " start_s = 0\n", " else:\n", " start_s += history_clip[\"continue_at\"]\n", " except:\n", " print(history_clip)\n", " raise\n", "\n", "n_unique_uploads_in_concats = len(\n", " set(i for i in concat_clips_ids if i.startswith(\"m_\"))\n", ")\n", "print(\n", " \"total concat unique clips are:\",\n", " len(concat_clips_ids),\n", " f\"with error: {history_error_counter}, duplicate {history_duplicate_error_counter}\",\n", " \"\\n\",\n", " \"uploads are in concats\",\n", " n_unique_uploads_in_concats,\n", " \"frac\",\n", " f\"{n_unique_uploads_in_concats / (upload_clip_df.shape[0] or 1):.3f}\",\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Features" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:40.236690Z", "start_time": "2024-05-26T00:23:39.995713Z" } }, "outputs": [], "source": [ "# set user number of clips generated\n", "clip_df[\"user_n_clips\"] = clip_df[\"user_id\"].map(clip_df[\"user_id\"].value_counts())\n", "print(clip_df[\"user_n_clips\"].describe())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:44.826860Z", "start_time": "2024-05-26T00:23:40.238330Z" } }, "outputs": [], "source": [ "# add upvoted column\n", "clip_df[\"upvoted\"] = clip_df[\"id\"].isin(upvoted_ids)\n", "print(\n", " \"has upvoted\",\n", " clip_df[\"upvoted\"].value_counts(),\n", " clip_df[\"upvoted\"].value_counts(normalize=True),\n", " (clip_df[\"upvote_count\"] >= 1).value_counts(normalize=True),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:48.404433Z", "start_time": "2024-05-26T00:23:44.857788Z" } }, "outputs": [], "source": [ "disliked_ids = reaction_df[reaction_df[\"reaction_type\"] == \"D\"][\"clip_id\"].unique()\n", "\n", "clip_df[\"downvoted\"] = clip_df[\"id\"].isin(disliked_ids)\n", "print(\"Downvoted fraction by category:\")\n", "for category, fraction in clip_df[\"downvoted\"].value_counts(normalize=True).items():\n", " print(f\"{category}: {fraction:.2%}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:23:56.590022Z", "start_time": "2024-05-26T00:23:48.405668Z" } }, "outputs": [], "source": [ "# add continued column -- uuid and str are not compatible X.x\n", "clip_df[\"has_continued\"] = (\n", " clip_df[\"id\"].astype(str).isin(set(list(has_continued_children_ids)))\n", ")\n", "print(\"has_continued fraction by category:\")\n", "for category, fraction in clip_df[\"has_continued\"].value_counts(normalize=True).items():\n", " print(f\"{category}: {fraction:.2%}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:04.704306Z", "start_time": "2024-05-26T00:23:56.591353Z" } }, "outputs": [], "source": [ "# add concat column\n", "clip_df[\"part_of_concat\"] = clip_df[\"id\"].astype(str).isin(concat_clips_ids)\n", "print(\"part_of_concat fraction by category:\")\n", "for category, fraction in (\n", " clip_df[\"part_of_concat\"].value_counts(normalize=True).items()\n", "):\n", " print(f\"{category}: {fraction:.2%}\")\n", "\n", "print(\"\\nModel distribution for part_of_concat clips:\")\n", "for model, fraction in (\n", " clip_df[clip_df[\"part_of_concat\"]][\"model_name\"]\n", " .value_counts(normalize=True)\n", " .items()\n", "):\n", " print(f\"{model}: {fraction:.2%}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:09.954233Z", "start_time": "2024-05-26T00:24:04.705554Z" } }, "outputs": [], "source": [ "# verify bots action are all non-empty\n", "action_mask = (\n", " bots_action_df[\"download_audio_count\"]\n", " + bots_action_df[\"download_video_count\"]\n", " + bots_action_df[\"download_audio_wav_count\"]\n", " # + bots_action_df[\"share_count\"] # will remove share cause it can be negative, just can be...\n", ") >= 1\n", "has_action_ids = set(i for i in bots_action_df[action_mask][\"clip_id\"].unique())\n", "clip_df[\"has_action\"] = clip_df[\"id\"].isin(has_action_ids)\n", "print(\"has_action fraction by category:\")\n", "for category, fraction in clip_df[\"has_action\"].value_counts(normalize=True).items():\n", " print(f\"{category}: {fraction:.2%}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:14.579841Z", "start_time": "2024-05-26T00:24:09.955568Z" } }, "outputs": [], "source": [ "# add downvoted column\n", "clip_df[\"flagged\"] = clip_df[\"id\"].isin(flagged_ids)\n", "print(\"flagged fraction by category:\")\n", "for category, fraction in clip_df[\"flagged\"].value_counts(normalize=True).items():\n", " print(f\"{category}: {fraction:.2%}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "clip_df[\"deleted\"] = clip_df[\"is_deleted\"]\n", "print(\"deleted fraction by category:\")\n", "for category, fraction in clip_df[\"deleted\"].value_counts(normalize=True).items():\n", " print(f\"{category}: {fraction:.2%}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:15.382315Z", "start_time": "2024-05-26T00:24:14.581073Z" } }, "outputs": [], "source": [ "# This is probably the most important cell of this notebook -- what are good labels, and not having good label makes it a bad label\n", "must_be_positive_mask = (\n", " (clip_df[\"upvoted\"])\n", " | (clip_df[\"has_action\"])\n", " | (clip_df[\"part_of_concat\"])\n", " | (clip_df[\"is_in_playlist\"])\n", ")\n", "must_be_not_negative_mask = (\n", " (~clip_df[\"downvoted\"]) & (~clip_df[\"deleted\"]) & (~clip_df[\"flagged\"])\n", ")\n", "must_be_negative_mask = (\n", " (clip_df[\"downvoted\"]) | (clip_df[\"deleted\"]) | (clip_df[\"flagged\"])\n", ")\n", "mask = must_be_positive_mask & must_be_not_negative_mask\n", "total_clips_count = clip_df.shape[0]\n", "total_preference_count = sum(mask)\n", "must_be_positive_count = sum(must_be_positive_mask)\n", "definitely_not_negative_count = sum(must_be_not_negative_mask)\n", "must_be_negative_count = sum(must_be_negative_mask)\n", "\n", "print(\n", " f\"Total clips: {total_clips_count:,}\\n\"\n", " f\"Total preference: {total_preference_count:,} ({total_preference_count/total_clips_count:.2%})\\n\"\n", " f\"Must be positive: {must_be_positive_count:,} ({must_be_positive_count/total_clips_count:.2%})\\n\"\n", " f\"Definitely not negative: {definitely_not_negative_count:,} ({definitely_not_negative_count/total_clips_count:.2%})\\n\"\n", " f\"Must be negative: {must_be_negative_count:,} ({must_be_negative_count/total_clips_count:.2%})\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:31.095990Z", "start_time": "2024-05-26T00:24:15.383572Z" } }, "outputs": [], "source": [ "total_unique_requests = clip_df[\"request_id\"].nunique()\n", "liked_requests = clip_df[mask][\"request_id\"].unique() # requests with at least 1 like\n", "unliked_requests = clip_df[~mask][\"request_id\"].unique() # requests without like\n", "has_liked_requests = set(liked_requests).intersection(\n", " set(unliked_requests)\n", ") # the request must have 1 like and one without like\n", "print(f\"Liked requests: {len(liked_requests):,}\")\n", "print(f\"Not liked requests: {len(unliked_requests):,}\")\n", "print(f\"Requests with preference paired generations: {len(has_liked_requests):,}\")\n", "print(\n", " f\"Percentage of total unique requests: {len(has_liked_requests) / total_unique_requests:.2%}\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# introduce a negative preference count\n", "has_disliked_half_requests = clip_df[must_be_negative_mask][\n", " \"request_id\"\n", "].unique() # requests with at least 1 dislike\n", "not_have_disliked_requests = clip_df[~must_be_negative_mask][\n", " \"request_id\"\n", "].unique() # request without dislike\n", "has_disliked_requests = set(has_disliked_half_requests).intersection(\n", " set(not_have_disliked_requests)\n", ") # the request must have 1 dislike and one without dislike\n", "print(f\"Disliked requests: {len(has_disliked_half_requests):,}\")\n", "print(f\"Not disliked requests: {len(not_have_disliked_requests):,}\")\n", "print(f\"Requests with preference paired generations: {len(has_disliked_requests):,}\")\n", "print(\n", " f\"Percentage of total unique requests: {len(has_disliked_requests) / total_unique_requests:.2%}\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:31.099372Z", "start_time": "2024-05-26T00:24:31.097244Z" } }, "outputs": [], "source": [ "requests = has_liked_requests.union(has_disliked_requests)\n", "print(f\"Total selected pairs of requests: {len(requests):,}\")\n", "print(\n", " f\"Percentage of total unique requests: {len(requests) / total_unique_requests:.2%}\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:31.239254Z", "start_time": "2024-05-26T00:24:31.100389Z" } }, "outputs": [], "source": [ "# this used to be a terrible bug...X.x\n", "assert mask.shape[0] == clip_df.shape[0]\n", "clip_df[\"pos_preference\"] = mask\n", "clip_df[\"neg_preference\"] = must_be_negative_mask\n", "clip_df[\"diff_preference\"] = clip_df[\"pos_preference\"].astype(int) - clip_df[\n", " \"neg_preference\"\n", "].astype(int)\n", "print(\"Difference in preference counts:\")\n", "value_counts = clip_df[\"diff_preference\"].value_counts()\n", "total = value_counts.sum()\n", "for value, count in value_counts.items():\n", " fraction = count / total\n", " print(f\"{value}: {count:,} ({fraction:.2%})\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:37.322031Z", "start_time": "2024-05-26T00:24:31.240829Z" } }, "outputs": [], "source": [ "# creation of interesting_clips\n", "interesting_clips = clip_df[clip_df[\"request_id\"].isin(requests)].copy()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "interesting_clips = interesting_clips.sort_values(\n", " by=[\"request_id\", \"diff_preference\"]\n", ").reset_index()\n", "interesting_clips[\n", " [\"request_id\", \"pos_preference\", \"neg_preference\", \"diff_preference\"]\n", "].head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# this is a mix now\n", "value_counts = interesting_clips[\"diff_preference\"].value_counts()\n", "total = value_counts.sum()\n", "for value, count in value_counts.items():\n", " fraction = count / total\n", " print(f\"Difference {value}: {count:,} ({fraction:.2%})\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "diff_series = interesting_clips[\"diff_preference\"].diff()\n", "value_counts = diff_series[1::2].value_counts()\n", "total = value_counts.sum()\n", "for value, count in value_counts.items():\n", " fraction = count / total\n", " print(f\"Value {value}: {count:,} ({fraction:.2%})\")\n", "# 1 is pos, not neg pair or nothing, neg; 2 is pos / neg (hence the larger difference)\n", "# there are only two values for this positive pair\n", "assert diff_series[1::2].nunique() == 2" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:38.960130Z", "start_time": "2024-05-26T00:24:37.323369Z" } }, "outputs": [], "source": [ "interesting_clips[\"preference\"] = interesting_clips.index % 2 == 1\n", "# get df of requests -- let's move on!\n", "print(f\"Number of unique request_ids: {interesting_clips['request_id'].nunique():,}\")\n", "print(f\"Number of unique ids: {interesting_clips['id'].nunique():,}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:43.156873Z", "start_time": "2024-05-26T00:24:38.961258Z" } }, "outputs": [], "source": [ "# some validations\n", "assert interesting_clips[interesting_clips[\"request_id\"].isna()].shape[0] == 0\n", "check_df = interesting_clips.groupby(\"request_id\")[\"id\"].nunique()\n", "check_df[check_df.values != 2]\n", "assert check_df[check_df.values != 2].shape[0] == 0" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:43.164669Z", "start_time": "2024-05-26T00:24:43.158223Z" } }, "outputs": [], "source": [ "# validation...\n", "# TODO: refactor this with above into a func\n", "# interesting_clips_must_be_positive_mask = (\n", "# (interesting_clips[\"upvoted\"] == True)\n", "# | (interesting_clips[\"has_action\"] == True)\n", "# | (interesting_clips[\"part_of_concat\"] == True)\n", "# )\n", "# interesting_clips_must_be_not_negative_mask = (\n", "# interesting_clips[\"downvoted\"] == False\n", "# ) & (interesting_clips[\"deleted\"] == False)\n", "# interesting_clips_mask = (\n", "# interesting_clips_must_be_positive_mask\n", "# & interesting_clips_must_be_not_negative_mask\n", "# )\n", "# assert interesting_clips_mask.eq(interesting_clips[\"preference\"]).all()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:43.332222Z", "start_time": "2024-05-26T00:24:43.166461Z" } }, "outputs": [], "source": [ "# # listen to some pairs\n", "# test_requests = interesting_clips[\"request_id\"].sample(10)\n", "\n", "# for i in range(1):\n", "# rows = interesting_clips[interesting_clips[\"request_id\"] == test_requests.iloc[i]]\n", "# assert rows.shape[0] == 2\n", "# # Audio.from_s3(f\"s3://suno-data-uploads/studio/uploads/{row['s3_id']}.mp3\").play()\n", "# # sort by likes\n", "# rows = rows.sort_values(\"upvoted\", ascending=True)\n", "# print(rows.iloc[0][\"prompt_text\"])\n", "# print(rows.iloc[0][\"metadata\"])\n", "# for _, row in rows.iterrows():\n", "# print(row[\"id\"], row[\"preference\"], row[\"upvoted\"])\n", "# Audio.from_s3(\n", "# f\"s3://suno-data-uploads/studio/uploads/{row['s3_id']}.mp3\"\n", "# ).play()\n", "# with open_from_s3(\n", "# f\"s3://suno-data-uploads/studio/uploads/{row['s3_id']}.npz\", as_binary=True\n", "# ) as f:\n", "# # read numpy array\n", "# npz_a = np.load(f)\n", "# if \"v1_raw\" in npz_a:\n", "# a = np.load(f)[\"v1_raw\"]\n", "# elif \"v3.0_raw\" in npz_a:\n", "# a = np.load(f)[\"v3.0_raw\"]\n", "# else:\n", "# print(\"npz_a\", npz_a)\n", "# raise ValueError\n", "# print(a.shape)\n", "# print()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Further cuts and selections" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:43.562167Z", "start_time": "2024-05-26T00:24:43.333784Z" } }, "outputs": [], "source": [ "# interesting_clips[\"has_gpt_prompt\"] = interesting_clips[\"metadata\"].apply(\n", "# lambda x: ast.literal_eval(str(x)).get(\"gpt_description_prompt\", None) is not None\n", "# )\n", "# print(len(interesting_clips))\n", "# interesting_clips = interesting_clips[~interesting_clips[\"has_gpt_prompt\"]]\n", "print(f\"Number of interesting clips: {len(interesting_clips):,}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:43.737067Z", "start_time": "2024-05-26T00:24:43.563216Z" } }, "outputs": [], "source": [ "preference_counts = interesting_clips.groupby(\"batch_index\")[\n", " \"preference\"\n", "].value_counts()\n", "total_counts = preference_counts.groupby(level=0).sum()\n", "\n", "print(\"Preference counts and fractions by batch index:\")\n", "print(\"-\" * 50)\n", "for batch_index in [0, 1]:\n", " print(f\"Batch Index: {batch_index}\")\n", " for preference in [False, True]:\n", " count = preference_counts[batch_index, preference]\n", " fraction = count / total_counts[batch_index]\n", " print(f\" Preference {preference}:\")\n", " print(f\" Count: {count:,}\")\n", " print(f\" Fraction: {fraction:.2%}\")\n", " print()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:44.304573Z", "start_time": "2024-05-26T00:24:43.973218Z" } }, "outputs": [], "source": [ "interesting_clips[\"model_name\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:44.533983Z", "start_time": "2024-05-26T00:24:44.305722Z" } }, "outputs": [], "source": [ "print(\"Time Validation:\")\n", "print(\"-\" * 20)\n", "print(\"Interesting Clips:\")\n", "print(f\" Earliest: {interesting_clips['created_at'].min()}\")\n", "print(f\" Latest: {interesting_clips['created_at'].max()}\")\n", "print(\"\\nAll Clips:\")\n", "print(f\" Earliest: {clip_df['created_at'].min()}\")\n", "print(f\" Latest: {clip_df['created_at'].max()}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:46.365344Z", "start_time": "2024-05-26T00:24:44.535249Z" } }, "outputs": [], "source": [ "# Get unique counts for request_id and id\n", "unique_request_ids = interesting_clips[\"request_id\"].nunique()\n", "unique_clip_ids = interesting_clips[\"id\"].nunique()\n", "\n", "# Print the results in a formatted manner\n", "print(\"Unique counts in interesting_clips:\")\n", "print(f\"{'Request IDs:':<15} {unique_request_ids:,}\")\n", "print(f\"{'Clip IDs:':<15} {unique_clip_ids:,}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:50.674838Z", "start_time": "2024-05-26T00:24:46.366677Z" } }, "outputs": [], "source": [ "# make sure we sort here before proceed\n", "interesting_clips = interesting_clips.sort_values(by=[\"request_id\", \"preference\"])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:51.600621Z", "start_time": "2024-05-26T00:24:50.676186Z" } }, "outputs": [], "source": [ "# Calculate the ratio of preferred clips to total clips for each model\n", "preference_ratio = (\n", " interesting_clips[interesting_clips[\"preference\"]][\"model_name\"].value_counts()\n", " / clip_df[\"model_name\"].value_counts()\n", ")\n", "\n", "# Print the results in a formatted manner\n", "print(\"Ratio of preferred clips to total clips for each model:\")\n", "print(\"-\" * 60)\n", "for model, ratio in preference_ratio.items():\n", " print(f\"{model:<30} {ratio:.6f}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:56.302599Z", "start_time": "2024-05-26T00:24:51.601863Z" } }, "outputs": [], "source": [ "get_preferfence_counts(interesting_clips)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:57.443236Z", "start_time": "2024-05-26T00:24:56.306473Z" } }, "outputs": [], "source": [ "fig, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, figsize=(16, 12))\n", "\n", "ax1.hist(interesting_clips[\"dislike_count\"], bins=np.linspace(0, 10, 30))\n", "ax1.set_yscale(\"log\")\n", "ax1.set_xlabel(\"Number of dislike_count\")\n", "ax1.set_ylabel(\"Number of clips\")\n", "ax1.set_title(\"Dislike Count Distribution\")\n", "\n", "ax2.hist(interesting_clips[\"upvote_count\"], bins=np.linspace(0, 10, 30))\n", "ax2.set_yscale(\"log\")\n", "ax2.set_xlabel(\"Number of like_count\")\n", "ax2.set_ylabel(\"Number of clips\")\n", "ax2.set_title(\"Upvote Count Distribution\")\n", "\n", "ax3.hist(interesting_clips[\"is_public\"], bins=np.linspace(0, 10, 30))\n", "ax3.set_yscale(\"log\")\n", "ax3.set_xlabel(\"Number of is_public\")\n", "ax3.set_ylabel(\"Number of clips\")\n", "ax3.set_title(\"Public Clips Distribution\")\n", "\n", "ax4.hist(interesting_clips[\"user_id\"].value_counts(), bins=np.linspace(0, 1000, 100))\n", "ax4.set_yscale(\"log\")\n", "ax4.set_xlabel(\"Number of preferences clips\")\n", "ax4.set_ylabel(\"Number of users\")\n", "ax4.set_title(\"User Preferences Distribution\")\n", "\n", "plt.tight_layout()\n", "plt.show()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:58.153310Z", "start_time": "2024-05-26T00:24:57.858363Z" } }, "outputs": [], "source": [ "# FUCK THIS FOR NOW\n", "# MAX_PREFERENCE_PER_USER = 400\n", "# grouped_interesting_clips = interesting_clips.groupby([\"user_id\"])\n", "# user_top_df = (\n", "# interesting_clips.sort_values(\n", "# [\"preference\", \"upvote_count\", \"part_of_concat\", \"is_in_playlist\"], ascending=False\n", "# )\n", "# .groupby(\"user_id\")\n", "# .head(MAX_PREFERENCE_PER_USER)\n", "# )\n", "# print(user_top_df.shape, interesting_clips.shape)\n", "\n", "# user_top_requests = user_top_df[\"request_id\"].unique()\n", "# user_intersting_clips = interesting_clips[\n", "# interesting_clips[\"request_id\"].isin(user_top_requests)\n", "# ].copy()\n", "# print(user_intersting_clips.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:58.890520Z", "start_time": "2024-05-26T00:24:58.154350Z" } }, "outputs": [], "source": [ "# subselect interesting clips\n", "user_intersting_clips = interesting_clips[\n", " interesting_clips[\"model_name\"].str.contains(\"v3p5\") # general 3.5\n", " # interesting_clips[\"model_name\"].str.contains(\"upload\") # only uploads\n", "].copy()\n", "print(\"Number of clips in user_interesting_clips:\")\n", "print(f\"{user_intersting_clips.shape[0]:,}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:59.204547Z", "start_time": "2024-05-26T00:24:58.891851Z" } }, "outputs": [], "source": [ "# Calculate the number of preferences per user\n", "preferences_per_user = user_intersting_clips[\"user_id\"].value_counts()\n", "\n", "# Determine the maximum number of preferences\n", "max_preferences = preferences_per_user.max()\n", "\n", "# Choose bins using Sturges' rule, but ensure a minimum of 15 bins and a maximum of 30\n", "n_bins = max(30, min(100, int(np.ceil(np.log2(len(preferences_per_user)) + 1))))\n", "\n", "# Calculate bin edges using a linear scale\n", "bin_edges = np.linspace(preferences_per_user.min(), max_preferences, n_bins)\n", "\n", "plt.figure(figsize=(10, 6))\n", "plt.hist(preferences_per_user, bins=bin_edges, edgecolor=\"black\")\n", "plt.yscale(\"log\")\n", "plt.xlabel(\"Number of preferences per user\")\n", "plt.ylabel(\"Number of users (log scale)\")\n", "plt.title(\"Distribution of User Preferences\")\n", "plt.grid(axis=\"both\", linestyle=\"--\", alpha=0.7)\n", "plt.show()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:59.207707Z", "start_time": "2024-05-26T00:24:59.205659Z" } }, "outputs": [], "source": [ "print(\"Summary of user_interesting_clips:\")\n", "print(f\"Total requests: {user_intersting_clips.shape[0]:,}\")\n", "print(f\"Unique clips: {user_intersting_clips.shape[0] // 2:,}\")\n", "print(\n", " f\"Fraction of total clips: {user_intersting_clips.shape[0] / total_clip_counts:.2%}\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:59.496996Z", "start_time": "2024-05-26T00:24:59.208750Z" } }, "outputs": [], "source": [ "user_intersting_clips.describe()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:59.685498Z", "start_time": "2024-05-26T00:24:59.498024Z" } }, "outputs": [], "source": [ "print(\"Time Validation:\")\n", "print(f\"Earliest timestamp: {user_intersting_clips['created_at'].min()}\")\n", "print(f\"Latest timestamp: {user_intersting_clips['created_at'].max()}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:00.484734Z", "start_time": "2024-05-26T00:25:00.377280Z" } }, "outputs": [], "source": [ "# def parse_for_tag(x):\n", "# if \"tags\" not in x:\n", "# return \"\"\n", "# out = x.get(\"tags\", \"\")\n", "# return out.lower() if out else \"\"\n", "\n", "# def parse_for_one_box(x):\n", "# if \"gpt_description_prompt\" not in x:\n", "# return False\n", "# out = x.get(\"gpt_description_prompt\", \"\")\n", "# return out != None\n", "# user_intersting_clips[\"tags\"] = user_intersting_clips[\"metadata\"].apply(parse_for_tag)\n", "# user_intersting_clips[\"is_onebox\"] = user_intersting_clips[\"metadata\"].apply(parse_for_one_box)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:00.893917Z", "start_time": "2024-05-26T00:25:00.485775Z" } }, "outputs": [], "source": [ "# v3 launch test time # 2024-04-03 07:47:01.770988+00:00 t1\n", "# v3.5 launch time: 2024-05-19 05:21:54\n", "# latest exp time: '2024-05-29 04:01:39'\n", "\n", "date_cut = \"2024-06-04 15:21:36\"\n", "\n", "# user_compare_mask = (\n", "# user_intersting_clips[\"created_at\"] >= date_cut\n", "# ) # & (user_intersting_clips[\"is_pro_user\"] == True)\n", "user_compare_mask = (\n", " (user_intersting_clips[\"created_at\"] >= date_cut)\n", " & (\n", " user_intersting_clips[\"model_name\"].isin(\n", " [\n", " \"chirp-v2-xxl-alpha\",\n", " \"chirp-v3-engine-i\",\n", " \"chirp-v3p5-engine-d\",\n", " \"chirp-v3p5-engine-s\",\n", " \"chirp-v3p5-engine-s-8\",\n", " \"chirp-v3p5-engine-s-14\",\n", " \"chirp-v3p5-engine-s-15\",\n", " \"chirp-v3p5-engine-s-18\",\n", " \"chirp-v3p5-engine-s-19\",\n", " \"chirp-v3p5-engine-s-20\",\n", " \"chirp-v3p5-engine-s-21\",\n", " \"chirp-v3p5-engine-s-8-paged\",\n", " \"chirp-v3p5-engine-ft\",\n", " \"chirp-v3p5-engine-ft-1\",\n", " \"chirp-v3p5-engine-s-8-no-top-p\",\n", " \"chirp-v3p5-engine-ft-2\",\n", " \"chirp-v3p5-engine-ft-3\",\n", " \"chirp-v3p5-engine-ft-4\",\n", " \"chirp-v3p5-engine-ft-5\",\n", " \"chirp-v3p5-engine-ft-6\",\n", " \"\",\n", " \"chirp-v3p5-engine-b\",\n", " \"chirp-v3p5-engine-upload\",\n", " \"chirp-v3p5-engine-upload-4\",\n", " \"chirp-v3p5-engine-t\",\n", " \"chirp-v3p5-engine-t-1\",\n", " \"chirp-v3p5-engine-t-1-5\",\n", " \"chirp-v3p5-engine-t-1-7\",\n", " \"chirp-v3p5-engine-t-1-fast\",\n", " \"chirp-v3p5-engine-t-1-fast-sem\",\n", " \"chirp-v3p5-engine-t-1-10\",\n", " \"chirp-v3p5-engine-t-1-11\",\n", " \"chirp-v3p5-engine-t-1-12\",\n", " \"chirp-v3p5-engine-t-1-13\",\n", " \"chirp-v3p5-engine-t-1-14\",\n", " \"chirp-v3p5-engine-t-2\",\n", " ]\n", " )\n", " )\n", " # & (user_intersting_clips[\"is_pro_user\"] == True)\n", " # & (user_intersting_clips[\"is_onebox\"] == True)\n", ")\n", "# user_compare_mask = (user_intersting_clips[\"created_at\"] >= date_cut) & (\n", "# user_intersting_clips[\"tags\"].apply(lambda x: not \"pop\" in x.lower())\n", "# )\n", "# # this is fucked up sometimes one box doesn't give prompt to one generation\n", "extra_compare_mask = user_intersting_clips[user_compare_mask][\"request_id\"].isin(\n", " user_intersting_clips[user_compare_mask][\"request_id\"]\n", " .value_counts()\n", " .index[user_intersting_clips[user_compare_mask][\"request_id\"].value_counts() == 2]\n", ")\n", "\n", "user_compare_mask = user_compare_mask & extra_compare_mask\n", "\n", "# for _, row in user_intersting_clips[user_intersting_clips[\"request_id\"].astype(str) == \"87c45d24-68ae-45dd-b5b7-92cd70bd0ab5\"].iterrows():\n", "# print(row[\"metadata\"])\n", "\n", "# for _, row in user_intersting_clips[user_intersting_clips[\"request_id\"].astype(str) == \"fa86f07f-4476-406f-b756-7166e0b08679\"].iterrows():\n", "# print(row[\"metadata\"])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:01.140472Z", "start_time": "2024-05-26T00:25:00.895575Z" } }, "outputs": [], "source": [ "user_intersting_clips_3p5 = (\n", " user_intersting_clips[user_compare_mask].reset_index().copy()\n", ")\n", "\n", "\n", "def parse_inference_exp(x):\n", " # print(x)\n", " if \"param_experiment\" not in x:\n", " return \"\"\n", " out = x.get(\"param_experiment\", \"\")\n", " if out:\n", " return \"_\" + out\n", " return \"\"\n", "\n", "\n", "user_intersting_clips_3p5[\"model_name\"] = user_intersting_clips_3p5[\n", " \"model_name\"\n", "] + user_intersting_clips_3p5[\"metadata\"].apply(parse_inference_exp)\n", "user_intersting_clips_3p5 = user_intersting_clips_3p5.sort_values(\n", " by=[\"request_id\", \"preference\"]\n", ")\n", "print(user_intersting_clips_3p5.shape)\n", "\n", "# second_user_compare_mask = (\n", "# user_intersting_clips_3p5[\"model_name\"].str.contains(\"all\")\n", "# ) | (user_intersting_clips_3p5[\"model_name\"] == \"chirp-v3p5-engine-t-1\")\n", "# extra_compare_mask = user_intersting_clips[second_user_compare_mask][\"request_id\"].isin(\n", "# user_intersting_clips[second_user_compare_mask][\"request_id\"]\n", "# .value_counts()\n", "# .index[\n", "# user_intersting_clips[second_user_compare_mask][\"request_id\"].value_counts()\n", "# == 2\n", "# ]\n", "# )\n", "\n", "# second_user_compare_mask = second_user_compare_mask & extra_compare_mask\n", "\n", "# user_intersting_clips_3p5 = user_intersting_clips_3p5[second_user_compare_mask].copy()\n", "\n", "# user_intersting_clips_3p5 = user_intersting_clips_3p5.sort_values(\n", "# by=[\"request_id\", \"preference\"]\n", "# )\n", "print(user_intersting_clips_3p5.shape)\n", "model_counts = user_intersting_clips_3p5[\"model_name\"].value_counts()\n", "model_fracs = model_counts / model_counts.sum()\n", "\n", "print(\"Model Name Value Counts and Fractions:\")\n", "for model, count in model_counts.items():\n", " frac = model_fracs[model]\n", " print(f\"{model}: {count} ({frac:.2%})\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:02.009450Z", "start_time": "2024-05-26T00:25:01.523515Z" } }, "outputs": [], "source": [ "get_preferfence_counts(user_intersting_clips_3p5)\n", "# user_intersting_clips[user_compare_mask]\n", "# )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:02.332947Z", "start_time": "2024-05-26T00:25:02.010694Z" } }, "outputs": [], "source": [ "print(\"first gen\")\n", "first_gen_slice_df = user_intersting_clips_3p5[\n", " (user_intersting_clips_3p5[\"continued_parent\"].isna())\n", "].copy()\n", "if first_gen_slice_df.shape[0] > 0:\n", " get_preferfence_counts(\n", " user_intersting_clips_3p5[\n", " (user_intersting_clips_3p5[\"continued_parent\"].isna())\n", " ],\n", " title_name=\"first generation\",\n", " )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:02.574659Z", "start_time": "2024-05-26T00:25:02.334214Z" } }, "outputs": [], "source": [ "print(\"is continue\")\n", "get_preferfence_counts(\n", " user_intersting_clips_3p5[(~user_intersting_clips_3p5[\"continued_parent\"].isna())],\n", " \"is continue\",\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Clean up SHIT\n", "\n", "to get the right play conts, we need the right df..." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "user_intersting_clips.shape" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:07.682737Z", "start_time": "2024-05-26T00:25:02.593456Z" } }, "outputs": [], "source": [ "# ~ only 1 min :)\n", "# Filter reaction_df for relevant clip_ids\n", "partial_reaction_df = reaction_df[\n", " reaction_df[\"clip_id\"].isin(user_intersting_clips[\"id\"])\n", "].copy()\n", "\n", "# Calculate total play counts\n", "total_play_counts = (\n", " partial_reaction_df.groupby(\"clip_id\")[\"play_count\"].sum().reset_index()\n", ")\n", "total_play_counts = total_play_counts.rename(\n", " columns={\"clip_id\": \"id\", \"play_count\": \"reaction_play_count\"}\n", ")\n", "\n", "# Calculate pro user play counts\n", "pro_play_counts = (\n", " partial_reaction_df[partial_reaction_df[\"is_pro_user\"]]\n", " .groupby(\"clip_id\")[\"play_count\"]\n", " .sum()\n", " .reset_index()\n", ")\n", "pro_play_counts = pro_play_counts.rename(\n", " columns={\"clip_id\": \"id\", \"play_count\": \"reaction_pro_play_count\"}\n", ")\n", "\n", "# Merge with user_intersting_clips\n", "user_intersting_clips = user_intersting_clips.merge(\n", " total_play_counts, on=\"id\", how=\"left\"\n", ")\n", "user_intersting_clips = user_intersting_clips.merge(\n", " pro_play_counts, on=\"id\", how=\"left\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "(\n", " user_intersting_clips[\"reaction_play_count\"]\n", " - user_intersting_clips[\"reaction_pro_play_count\"]\n", ").describe()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:08.095035Z", "start_time": "2024-05-26T00:25:07.782738Z" } }, "outputs": [], "source": [ "def unpack_dict(x):\n", " if v := concat_clips_ids.get(str(x)):\n", " return v\n", " else:\n", " return {\n", " \"total_start_s\": None,\n", " \"total_clip_s\": None,\n", " \"concat_play_counts\": None,\n", " \"concat_in_playlist\": None,\n", " \"concat_likes\": None,\n", " \"concat_dislikes\": None,\n", " }\n", "\n", "\n", "extra_cols = user_intersting_clips[\"id\"].apply(unpack_dict)\n", "extra_cols_df = pd.DataFrame.from_records(extra_cols.values, index=extra_cols.index)\n", "user_intersting_clips[\n", " [\n", " \"total_start_s\",\n", " \"total_clip_s\",\n", " \"concat_play_counts\",\n", " \"concat_in_playlist\",\n", " \"concat_likes\",\n", " \"concat_dislikes\",\n", " ]\n", "] = extra_cols_df" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:08.361849Z", "start_time": "2024-05-26T00:25:08.199583Z" } }, "outputs": [], "source": [ "pos_too_much_data_mask = (\n", " (user_intersting_clips[\"preference\"])\n", " & (\n", " (\n", " user_intersting_clips[\"reaction_play_count\"] >= 2\n", " ) # single play is super catchy\n", " | (\n", " user_intersting_clips[\"concat_play_counts\"] >= 2\n", " ) # or the concat play is super catchy\n", " )\n", " & (user_intersting_clips[\"user_n_clips\"] >= 4)\n", " # & (user_intersting_clips[\"continued_parent\"].isna())\n", ")\n", "neg_too_much_data_mask = (\n", " (~user_intersting_clips[\"preference\"])\n", " & (user_intersting_clips[\"reaction_play_count\"] >= 1) # single play is super catchy\n", " & (user_intersting_clips[\"user_n_clips\"] >= 4)\n", " # & (user_intersting_clips[\"continued_parent\"].isna())\n", ")\n", "# Calculate and print the proportion of data that meets our criteria\n", "pos_proportion = pos_too_much_data_mask.sum() / user_intersting_clips.shape[0] * 2\n", "print(f\"Positive proportion of data meeting criteria: {pos_proportion:.2%}\")\n", "neg_proportion = neg_too_much_data_mask.sum() / user_intersting_clips.shape[0] * 2\n", "print(f\"Negative proportion of data meeting criteria: {neg_proportion:.2%}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:08.523643Z", "start_time": "2024-05-26T00:25:08.367348Z" } }, "outputs": [], "source": [ "final_good_enough_requests = set(\n", " user_intersting_clips[pos_too_much_data_mask][\"request_id\"].unique()\n", ").intersection(\n", " set(user_intersting_clips[neg_too_much_data_mask][\"request_id\"].unique())\n", ")\n", "final_interesting_clips = user_intersting_clips[\n", " user_intersting_clips[\"request_id\"].isin(final_good_enough_requests)\n", "].copy()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Get the value counts of model_name for preferred clips\n", "model_counts = final_interesting_clips[final_interesting_clips[\"preference\"]][\n", " \"model_name\"\n", "].value_counts()\n", "\n", "# Print the results in a nicely formatted way\n", "total_count = model_counts.sum()\n", "print(\"Model Name Value Counts for Preferred Clips:\")\n", "print(\"-\" * 70)\n", "print(f\"{'Model':<30} {'Count':>10} {'Fraction':>15}\")\n", "print(\"-\" * 70)\n", "for model, count in model_counts.items():\n", " fraction = count / total_count\n", " print(f\"{model:<30} {count:>10,d} {fraction:>15.2%}\")\n", "print(\"-\" * 70)\n", "print(f\"{'Total':<30} {total_count:>10,d} {1:>15.2%}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "final_interesting_clips[final_interesting_clips[\"preference\"]][\n", " (final_interesting_clips[\"reaction_play_count\"] >= 2) # single play is super catchy\n", " | (\n", " final_interesting_clips[\"concat_play_counts\"] >= 2\n", " ) # or the concat play is super catchy]\n", "].shape" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "final_interesting_clips.shape" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:09.198504Z", "start_time": "2024-05-26T00:25:08.885507Z" } }, "outputs": [], "source": [ "assert (\n", " final_interesting_clips[final_interesting_clips[\"request_id\"].isna()].shape[0] == 0\n", ")\n", "check_df = final_interesting_clips.groupby(\"request_id\")[\"id\"].nunique()\n", "check_df[check_df.values != 2]\n", "assert check_df[check_df.values != 2].shape[0] == 0" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Get the number of rows for final_interesting_clips with the specific model\n", "row_count = final_interesting_clips[\n", " final_interesting_clips[\"model_name\"] == \"chirp-v3p5-engine-upload-4\"\n", "].shape[0]\n", "\n", "# Print the row count in a nicely formatted way\n", "print(\"Number of rows in final_interesting_clips for model 'chirp-v3p5-engine-upload-4':\")\n", "print(f\"{row_count:,}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:36:45.167690Z", "start_time": "2024-05-26T00:36:45.164768Z" }, "scrolled": true }, "outputs": [], "source": [ "# final_interesting_clips[\n", "# final_interesting_clips[\"model_name\"] == \"chirp-v3p5-engine-t-1\"\n", "# ].to_csv(\n", "# \"/home/tony/Data/Preference/30b_v0/interesting_clips_v4_t_1_20240724.csv\",\n", "# index=False,\n", "# )\n", "print(\"done\", final_interesting_clips.shape)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# For faster processing once" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:10.265241Z", "start_time": "2024-05-26T00:25:09.934743Z" } }, "outputs": [], "source": [ "# Calculate the number of unique users\n", "total_unique_users = clip_df[\"user_id\"].nunique()\n", "\n", "# Print the result in a nicely formatted way\n", "print(\"Total Unique Users:\")\n", "print(\"-\" * 20)\n", "print(f\"{total_unique_users:,}\")\n", "print(\"-\" * 20)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:26:43.409660Z", "start_time": "2024-05-26T00:25:10.266379Z" } }, "outputs": [], "source": [ "# This can take a while cause we have a lot of users...\n", "# query = \"\"\"\n", "# SELECT *\n", "# FROM auth_user\n", "# \"\"\"\n", "# user_df = pd.read_sql_query(query, engine)\n", "# user_df.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:26:43.423857Z", "start_time": "2024-05-26T00:26:43.411248Z" } }, "outputs": [], "source": [ "test_user_id = 4688272\n", "print(\n", " clip_df[clip_df[\"user_id\"] == test_user_id][\"created_at\"]\n", " .apply(lambda x: str(x)[:10])\n", " .value_counts()\n", ")\n", "print(clip_df[clip_df[\"user_id\"] == test_user_id].shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:26:43.803559Z", "start_time": "2024-05-26T00:26:43.622922Z" } }, "outputs": [], "source": [ "user_intersting_clips[user_intersting_clips[\"user_n_clips\"] > 10000][\"user_id\"].unique()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:26:45.770044Z", "start_time": "2024-05-26T00:26:44.912085Z" } }, "outputs": [], "source": [ "# # wtf is going on with these requests\n", "# print(total_clip_df[total_clip_df[\"model_name\"] == \"chirp-v3-0\"].shape)\n", "# print(\n", "# total_clip_df[total_clip_df[\"model_name\"] == \"chirp-v3-0\"][\"user_id\"].value_counts()\n", "# )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:26:46.088296Z", "start_time": "2024-05-26T00:26:45.772102Z" } }, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT *\n", "FROM auth_user\n", "WHERE id=27205089\n", "\"\"\"\n", "# 3 keenan\n", "# 6 martin\n", "# 8 tony -- that's me!\n", "# 186417 georg\n", "test_user_df = pd.read_sql_query(query, engine)\n", "test_user_df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Find some weird generations" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:27:00.130778Z", "start_time": "2024-05-26T00:26:46.089923Z" } }, "outputs": [], "source": [ "no_reaction_clip_df = total_clip_df[\n", " ~total_clip_df[\"id\"].isin(reaction_df[\"clip_id\"])\n", "].copy()\n", "print(\"Shape of no_reaction_clip_df:\")\n", "print(no_reaction_clip_df.shape)\n", "print(\"\\nProportion of clips without reactions:\")\n", "print(f\"{no_reaction_clip_df.shape[0] / total_clip_df.shape[0]:.2%}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:27:01.185878Z", "start_time": "2024-05-26T00:27:00.132882Z" } }, "outputs": [], "source": [ "min_generations_for_no_reaction = 20\n", "inspection_date_cut = \"2024-05-10\"\n", "# inspection_date_cut = \"2024-06-18\"\n", "no_reaction_clip_df[\"no_reaction_count\"] = no_reaction_clip_df.groupby(\"user_id\")[\n", " \"user_id\"\n", "].transform(\"count\")\n", "no_reaction_clip_df[\"is_pro_user\"] = no_reaction_clip_df[\"user_id\"].isin(pro_users)\n", "no_reaction_clip_df[\"user_id\"].nunique()\n", "bot_user_mask = (\n", " (no_reaction_clip_df[\"no_reaction_count\"] >= min_generations_for_no_reaction)\n", " & (no_reaction_clip_df[\"created_at\"] >= inspection_date_cut)\n", " # & (no_reaction_clip_df[\"is_pro_user\"] == True)\n", ")\n", "sub_total_clip_df = total_clip_df[\n", " total_clip_df[\"user_id\"].isin(\n", " no_reaction_clip_df[bot_user_mask][\"user_id\"].unique()\n", " )\n", "].copy()\n", "sub_total_clip_df[\"is_pro_user\"] = sub_total_clip_df[\"user_id\"].isin(pro_users)\n", "ratio = no_reaction_clip_df[bot_user_mask].shape[0] / sub_total_clip_df.shape[0]\n", "print(\"Ratio of clips without reactions to total clips from the same users:\")\n", "print(f\"{ratio:.4f}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:27:02.022423Z", "start_time": "2024-05-26T00:27:01.187814Z" } }, "outputs": [], "source": [ "sub_total_clip_df[\"gen_count\"] = sub_total_clip_df.groupby(\"user_id\")[\n", " \"user_id\"\n", "].transform(\"count\")\n", "user_id_no_reaction_dict = no_reaction_clip_df.set_index(\"user_id\")[\n", " \"no_reaction_count\"\n", "].to_dict()\n", "user_id_total_dict = (\n", " sub_total_clip_df[~sub_total_clip_df[\"is_pro_user\"]]\n", " .set_index(\"user_id\")[\"gen_count\"]\n", " .to_dict()\n", ")\n", "pro_user_id_total_dict = (\n", " sub_total_clip_df[sub_total_clip_df[\"is_pro_user\"]]\n", " .set_index(\"user_id\")[\"gen_count\"]\n", " .to_dict()\n", ")\n", "\n", "user_ratio_dict = {}\n", "pro_user_ratio_dict = {}\n", "for user_id, total_gen in user_id_total_dict.items():\n", " user_ratio = user_id_no_reaction_dict.get(user_id, 0) / total_gen\n", " user_ratio_dict[user_id] = user_ratio\n", "for user_id, total_gen in pro_user_id_total_dict.items():\n", " user_ratio = user_id_no_reaction_dict.get(user_id, 0) / total_gen\n", " pro_user_ratio_dict[user_id] = user_ratio" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:27:02.191083Z", "start_time": "2024-05-26T00:27:02.024409Z" } }, "outputs": [], "source": [ "plt.hist(\n", " user_ratio_dict.values(), bins=np.linspace(0, 1, 50), alpha=0.5, label=\"free user\"\n", ")\n", "plt.hist(\n", " pro_user_ratio_dict.values(),\n", " bins=np.linspace(0, 1, 50),\n", " alpha=0.5,\n", " label=\"pro user\",\n", ")\n", "plt.xlabel(\n", " f\"fraction of generations (min {min_generations_for_no_reaction}) that have no actions\"\n", ")\n", "plt.ylabel(\"number of users\")\n", "plt.yscale(\"log\")\n", "plt.legend()\n", "plt.title(f\"Potential bots since {max(cutoff_date, inspection_date_cut)}\")\n", "plt.show()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:27:02.229332Z", "start_time": "2024-05-26T00:27:02.192457Z" } }, "outputs": [], "source": [ "super_bad_user_id = set()\n", "for user_id, user_ratio in user_ratio_dict.items():\n", " if user_ratio >= 0.99:\n", " super_bad_user_id.add(user_id)\n", "print(\"free\", len(super_bad_user_id))\n", "super_bad_pro_user_id = set()\n", "for user_id, user_ratio in pro_user_ratio_dict.items():\n", " if user_ratio >= 0.99:\n", " super_bad_pro_user_id.add(user_id)\n", "print(\"pro\", len(super_bad_pro_user_id))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# curr_date = datetime.today().strftime(\"%Y_%m_%d\")\n", "# with open(f\"/home/tony/Data/bots/bad_user_{curr_date}.json\", \"w\") as fp:\n", "# json.dump(list(super_bad_user_id), fp)\n", "# with open(f\"/home/tony/Data/bots/bad_pro_user_{curr_date}.json\", \"w\") as fp:\n", "# json.dump(list(super_bad_pro_user_id), fp)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:27:02.725735Z", "start_time": "2024-05-26T00:27:02.230988Z" } }, "outputs": [], "source": [ "# Calculate the ratio of clips from super bad users\n", "bad_user_clip_ratio = (\n", " total_clip_df[total_clip_df[\"user_id\"].isin(super_bad_user_id)].shape[0]\n", " / total_clip_df.shape[0]\n", ")\n", "\n", "# Print the result nicely\n", "print(f\"Ratio of clips from super bad users: {bad_user_clip_ratio:.2%}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Alpha testing user selection" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Alpha testing user selection\n", "# we focus on the folks who are good good\n", "\n", "# # 0526 is v2 -- prod\n", "# # 0529 is v4 -- still good IMO, more data\n", "# early_v3p5_data = pd.read_csv(\"/home/tony/Data/Preference/13b_v0/interesting_clips_20240529.csv\")\n", "\n", "# print(\"uqniue users for vp5\", early_v3p5_data[\"user_id\"].nunique())\n", "\n", "# early_v3_data = pd.read_csv(\"/home/tony/Data/Preference/7b_v0_interesting_clips.csv\")\n", "\n", "# print(\"uqniue users for v3\", early_v3_data[\"user_id\"].nunique())\n", "\n", "# early_v2_data = pd.read_csv(\"/home/tony/Data/Preference/3b_v0_interesting_clips.csv\")\n", "\n", "# print(\"uqniue users for v2\", early_v2_data[\"user_id\"].nunique())\n", "\n", "# intersection_user_ids_super = set(early_v3p5_data[\"user_id\"].unique()).intersection(set(early_v3_data[\"user_id\"].unique())).intersection(set(early_v2_data[\"user_id\"].unique()))\n", "\n", "# intersection_user_ids_v3_on = set(early_v3p5_data[\"user_id\"].unique()).intersection(set(early_v3_data[\"user_id\"].unique())).difference(intersection_user_ids_super)\n", "\n", "# print(len(intersection_user_ids_super), len(intersection_user_ids_v3_on))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# super_user_df = user_df[user_df[\"id\"].isin(intersection_user_ids_super)].copy()\n", "# print(super_user_df.shape)\n", "# v3_onward_user_df = user_df[user_df[\"id\"].isin(intersection_user_ids_v3_on)].copy()\n", "# print(v3_onward_user_df.shape)\n", "# super_user_df.to_csv(\"/home/tony/Data/Preference/alpha_users/super_user.csv\", index=False)\n", "# v3_onward_user_df.to_csv(\"/home/tony/Data/Preference/alpha_users/v3_onward_user.csv\", index=False)\n", "# print(\"Done!!\")" ] }, { "cell_type": "markdown", "metadata": { "execution": { "iopub.execute_input": "2024-06-21T19:35:17.755108Z", "iopub.status.busy": "2024-06-21T19:35:17.754937Z", "iopub.status.idle": "2024-06-21T19:35:17.774581Z", "shell.execute_reply": "2024-06-21T19:35:17.774106Z", "shell.execute_reply.started": "2024-06-21T19:35:17.755091Z" } }, "source": [ "# Snow flake access" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "snow_session = Session.builder.configs(CONNECTION_PARAMETERS).create()\n", "\n", "snow_root = Root(snow_session)\n", "snow_schema = snow_root.databases[\"SUNO_PROD\"].schemas[\"PROD\"]\n", "print(snow_schema.name)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# select the df we want to squery for play counts\n", "subset_v4_clips_df = final_interesting_clips[\n", " final_interesting_clips[\"model_name\"] == \"chirp-v3p5-engine-upload-4\"\n", "].copy()\n", "\n", "v4_clip_ids = list(str(s) for s in subset_v4_clips_df[\"id\"].unique())" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "snow_batch_size = 100_000\n", "snow_results = []\n", "for clip_ids_chunk in tqdm.tqdm(\n", " [\n", " v4_clip_ids[i : i + snow_batch_size]\n", " for i in range(0, len(v4_clip_ids), snow_batch_size)\n", " ]\n", "):\n", " id_query_str = \",\".join(\"'\" + x + \"'\" for x in clip_ids_chunk)\n", " print(f\"Number of clip IDs in this chunk: {len(clip_ids_chunk)}\")\n", " print(f\"Length of the ID query string: {len(id_query_str)}\")\n", "\n", " session_query = snow_session.sql(\n", " f\"\"\" select *\n", " from ML_SONG_SUMMARY_INFO\n", " where p_date = DATE(SYSDATE() - INTERVAL '2 HOUR')\n", " and p_hour = hour(SYSDATE() - INTERVAL '2 HOUR')\n", " and song_id in ({id_query_str})\n", " order by p_hour desc;\"\"\"\n", " )\n", " temp_df_snow_test = pd.DataFrame(session_query.collect())\n", " snow_results.append(temp_df_snow_test)\n", "print(len(snow_results))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df_snow_test = pd.concat(snow_results)\n", "df_snow_test = df_snow_test.rename(columns=lambda x: x.lower())\n", "df_snow_test = df_snow_test.rename(columns={\"song_id\": \"str_id\"})\n", "print(\"Shape of df_snow_test:\")\n", "print(f\"Rows: {df_snow_test.shape[0]}\")\n", "print(f\"Columns: {df_snow_test.shape[1]}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "subset_v4_clips_df[\"str_id\"] = subset_v4_clips_df[\"id\"].astype(str)\n", "subset_v4_clips_df_test = subset_v4_clips_df.merge(\n", " df_snow_test, on=\"str_id\", how=\"left\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "subset_v4_clips_df_test[\"norm_play_frac\"] = (\n", " subset_v4_clips_df_test[\"total_play_time\"].fillna(0)\n", " / subset_v4_clips_df_test[\"duration\"]\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# subset_v4_clips_df_test[subset_v4_clips_df_test[\"total_play_time\"] == 0].to_csv(\"/home/tony/Work/bugs.csv\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "subset_v4_clips_df_test.describe()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Create a figure with two subplots\n", "fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(16, 6))\n", "\n", "# First subplot: Total play duration\n", "pos_play_time = subset_v4_clips_df_test[subset_v4_clips_df_test[\"preference\"]][\n", " \"total_play_time\"\n", "]\n", "neg_play_time = subset_v4_clips_df_test[~subset_v4_clips_df_test[\"preference\"]][\n", " \"total_play_time\"\n", "]\n", "\n", "pos_play_time.hist(\n", " bins=np.linspace(0, 400, 100),\n", " alpha=0.5,\n", " label=f\"pos (mean={pos_play_time.mean():.2f}, median={pos_play_time.median():.2f})\",\n", " ax=ax1,\n", ")\n", "neg_play_time.hist(\n", " bins=np.linspace(0, 400, 100),\n", " alpha=0.5,\n", " label=f\"neg (mean={neg_play_time.mean():.2f}, median={neg_play_time.median():.2f})\",\n", " ax=ax1,\n", ")\n", "ax1.legend()\n", "ax1.set_xlabel(\"Total play duration in seconds\")\n", "ax1.set_ylabel(\"counts\")\n", "ax1.set_title(\"Play duration comparison\")\n", "\n", "# Second subplot: Normalized play fraction\n", "pos_norm_play_frac = subset_v4_clips_df_test[subset_v4_clips_df_test[\"preference\"]][\n", " \"norm_play_frac\"\n", "]\n", "neg_norm_play_frac = subset_v4_clips_df_test[~subset_v4_clips_df_test[\"preference\"]][\n", " \"norm_play_frac\"\n", "]\n", "\n", "pos_norm_play_frac.hist(\n", " bins=np.linspace(0, 10, 100),\n", " alpha=0.5,\n", " label=f\"pos (mean={pos_norm_play_frac.mean():.2f}, median={pos_norm_play_frac.median():.2f})\",\n", " ax=ax2,\n", ")\n", "neg_norm_play_frac.hist(\n", " bins=np.linspace(0, 10, 100),\n", " alpha=0.5,\n", " label=f\"neg (mean={neg_norm_play_frac.mean():.2f}, median={neg_norm_play_frac.median():.2f})\",\n", " ax=ax2,\n", ")\n", "ax2.legend()\n", "ax2.set_xlabel(\"Normalized play counts (play duration/duration)\")\n", "ax2.set_ylabel(\"Log counts\")\n", "ax2.set_yscale(\"log\")\n", "ax2.set_title(\"Normalized play duration comparison (Log scale)\")\n", "\n", "# Adjust layout and display the plot\n", "plt.tight_layout()\n", "plt.show()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pos_play_duration_mask = (\n", " subset_v4_clips_df_test[\"preference\"]\n", " & (subset_v4_clips_df_test[\"norm_play_frac\"] >= 0.95) # default 0.95\n", " & (subset_v4_clips_df_test[\"total_play_time\"] >= 10)\n", ")\n", "\n", "neg_play_duration_mask = (\n", " (~subset_v4_clips_df_test[\"preference\"])\n", " & (subset_v4_clips_df_test[\"norm_play_frac\"] <= 3.1)\n", " & (subset_v4_clips_df_test[\"total_play_time\"] >= 10)\n", ")\n", "# Get unique request IDs that pass the play duration criteria\n", "pos_unique_requests_pass_play_durations = subset_v4_clips_df_test[\n", " pos_play_duration_mask\n", "][\"request_id\"].unique()\n", "neg_unique_requests_pass_play_durations = subset_v4_clips_df_test[\n", " neg_play_duration_mask\n", "][\"request_id\"].unique()\n", "unique_requests_pass_play_durations = set(\n", " pos_unique_requests_pass_play_durations\n", ").intersection(set(neg_unique_requests_pass_play_durations))\n", "\n", "# Print the number of unique requests that pass the play duration criteria\n", "print(\n", " f\"Number of unique requests passing play duration criteria: {len(unique_requests_pass_play_durations)}\"\n", ")\n", "\n", "# Calculate the fraction of unique requests that pass play duration criteria\n", "fraction_requests_pass = (\n", " len(unique_requests_pass_play_durations)\n", " / subset_v4_clips_df_test[\"request_id\"].nunique()\n", ")\n", "\n", "# Print the result with a formatted string\n", "print(\n", " f\"Fraction of unique requests that pass play duration criteria: {fraction_requests_pass:.4f}\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "subset_v4_clips_df_pass_duration = subset_v4_clips_df_test[\n", " subset_v4_clips_df_test[\"request_id\"].isin(unique_requests_pass_play_durations)\n", "].copy()\n", "play_duration_mask_request_mask = subset_v4_clips_df_pass_duration[\"request_id\"].isin(\n", " subset_v4_clips_df_pass_duration[\"request_id\"]\n", " .value_counts()\n", " .index[subset_v4_clips_df_pass_duration[\"request_id\"].value_counts() == 2]\n", ")\n", "final_subset_v4_clips_df = subset_v4_clips_df_pass_duration[\n", " play_duration_mask_request_mask\n", "].copy()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# Count and print the number of unique request IDs\n", "unique_request_count = final_subset_v4_clips_df[\"request_id\"].nunique()\n", "print(\n", " f\"Number of unique request IDs: {unique_request_count:,}, Total {subset_v4_clips_df_test['request_id'].nunique()}\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# final_subset_v4_clips_df[final_subset_v4_clips_df[\"preference\"]].describe()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(final_subset_v4_clips_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# final_subset_v4_clips_df.to_csv(\n", "# \"/home/tony/Data/Preference/30b_v0/interesting_clips_v4_t_1_20240807.csv\",\n", "# index=False,\n", "# )\n", "# print(final_subset_v4_clips_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.14" }, "toc": { "base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": false, "toc_position": {}, "toc_section_display": true, "toc_window_display": false } }, "nbformat": 4, "nbformat_minor": 4 }