{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Select Preference Data" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:04.757824Z", "start_time": "2024-05-26T00:11:04.555293Z" } }, "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": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:11:08.392310Z", "start_time": "2024-05-26T00:11:04.759383Z" } }, "outputs": [], "source": [ "# pip install psycopg2-binary\n", "# make sure sqlalchemy is >=2\n", "import ast\n", "import json\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", "import snowflake.connector\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_X_SMAL\",\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" ] }, { "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": [ "# ~ 4 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", "query = f\"\"\"\n", "SELECT * FROM bots_generatedclip\n", "WHERE status='complete' AND created_at>='{cutoff_date}'\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 = f\"\"\"\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": "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-ft\",\n", " \"chirp-v3p5-engine-ft-1\",\n", " \"chirp-v3p5-engine-s-8-no-top-p\",\n", " \"chirp-v3p5-engine-t\",\n", " \"chirp-v3p5-engine-ft-2\",\n", " \"\",\n", " \"chirp-v3p5-engine-upload\",\n", " \"chirp-v3p5-engine-upload-4\",\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": [ "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(\"Concats without reaction play count\", 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]:.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())" ] }, { "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(\n", " \"total downvoted\",\n", " disliked_ids.shape,\n", " \"has downvoted\",\n", " clip_df[\"downvoted\"].value_counts(),\n", " clip_df[\"downvoted\"].value_counts(normalize=True),\n", " (clip_df[\"dislike_count\"] >= 1).value_counts(normalize=True),\n", ")" ] }, { "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(\n", " \"has has_continued\",\n", " clip_df[\"has_continued\"].value_counts(),\n", " clip_df[\"has_continued\"].value_counts(normalize=True),\n", ")" ] }, { "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(\n", " \"is part of a concat\",\n", " clip_df[\"part_of_concat\"].value_counts(),\n", " clip_df[\"part_of_concat\"].value_counts(normalize=True),\n", " \"\\n\",\n", " \"model countdowns\",\n", " clip_df[clip_df[\"part_of_concat\"]][\"model_name\"].value_counts(normalize=True),\n", ")" ] }, { "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(\n", " \"has has_action\",\n", " clip_df[\"has_action\"].value_counts(),\n", " clip_df[\"has_action\"].value_counts(normalize=True),\n", ")" ] }, { "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(\n", " \"has flagged\",\n", " clip_df[\"flagged\"].value_counts(),\n", " clip_df[\"flagged\"].value_counts(normalize=True),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "clip_df[\"deleted\"] = clip_df[\"is_deleted\"]\n", "print(\n", " \"has deleted\",\n", " clip_df[\"deleted\"].value_counts(),\n", " clip_df[\"deleted\"].value_counts(normalize=True),\n", ")" ] }, { "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\"]) | (clip_df[\"has_action\"]) | (clip_df[\"part_of_concat\"])\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", "print(\n", " f\"\"\"total_clips, {clip_df.shape[0]}, total preference, {sum(mask)}, \n", " must be pos {sum(must_be_positive_mask)}, def not neg {sum(must_be_not_negative_mask)},\n", " must be neg {sum(must_be_negative_mask)}.\n", " \"\"\"\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(\"liked\", len(liked_requests), \"not liked\", len(unliked_requests))\n", "print(\n", " f\"{len(has_liked_requests)} requests have preference paired generations, {len(has_liked_requests) / total_unique_requests:.3f}\"\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(\n", " \"disliked\",\n", " len(has_disliked_half_requests),\n", " \"not disliked\",\n", " len(not_have_disliked_requests),\n", ")\n", "print(\n", " f\"{len(has_disliked_requests)} requests have preference paired generations, {len(has_disliked_requests) / total_unique_requests:.3f}\"\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(\n", " f\"total selected pairs of requests {len(requests)}, {len(requests) / total_unique_requests:.3f}\"\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", "clip_df[\"diff_preference\"].value_counts()\n", "# clip_df[\"preference\"] = mask" ] }, { "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", "interesting_clips[\"diff_preference\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "diff_series = interesting_clips[\"diff_preference\"].diff()\n", "print(\n", " diff_series[1::2].value_counts()\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": {}, "outputs": [], "source": [ "interesting_clips[\"preference\"] = interesting_clips.index % 2 == 1\n", "interesting_clips[\"preference\"].value_counts()" ] }, { "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": [ "# get df of requests -- let's move on!\n", "print(interesting_clips[\"request_id\"].nunique(), 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(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": [ "interesting_clips.groupby(\"batch_index\")[\"preference\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:43.972044Z", "start_time": "2024-05-26T00:24:43.738263Z" } }, "outputs": [], "source": [ "# interesting_clips[\"user_id\"].nunique(), interesting_clips[\"user_id\"].value_counts()" ] }, { "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(\n", " \"time validation\",\n", " interesting_clips[\"created_at\"].min(),\n", " interesting_clips[\"created_at\"].max(),\n", " \"\\n MIN_TIME\",\n", " clip_df[\"created_at\"].min(),\n", " \"\\n MAX_TIME\",\n", " clip_df[\"created_at\"].max(),\n", ")" ] }, { "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": [ "print(interesting_clips[\"request_id\"].nunique(), interesting_clips[\"id\"].nunique())" ] }, { "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": [ "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": [ "interesting_clips[interesting_clips[\"preference\"]][\n", " \"model_name\"\n", "].value_counts() / clip_df[\"model_name\"].value_counts()" ] }, { "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:56.305431Z", "start_time": "2024-05-26T00:24:56.303928Z" } }, "outputs": [], "source": [ "# top_user_df = interesting_clips.groupby([\"user_id\"]).filter(lambda x: len(x) > 500)\n", "# get_preferfence_counts(top_user_df)" ] }, { "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": [ "plt.hist(interesting_clips[\"dislike_count\"], bins=np.linspace(0, 10, 30))\n", "plt.yscale(\"log\")\n", "plt.xlabel(\"number of dislike_count\")\n", "plt.ylabel(\"number of clips\")\n", "plt.show()\n", "plt.hist(interesting_clips[\"upvote_count\"], bins=np.linspace(0, 10, 30))\n", "plt.yscale(\"log\")\n", "plt.xlabel(\"number of like_count\")\n", "plt.ylabel(\"number of clips\")\n", "plt.show()\n", "plt.hist(interesting_clips[\"is_public\"], bins=np.linspace(0, 10, 30))\n", "plt.yscale(\"log\")\n", "plt.xlabel(\"number of is_public\")\n", "plt.ylabel(\"number of clips\")\n", "plt.show()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:57.802320Z", "start_time": "2024-05-26T00:24:57.444414Z" } }, "outputs": [], "source": [ "plt.hist(interesting_clips[\"user_id\"].value_counts(), bins=np.linspace(0, 1000, 100))\n", "plt.yscale(\"log\")\n", "plt.xlabel(\"number of preferences clips\")\n", "plt.ylabel(\"number of users\")\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": [ "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(user_intersting_clips.shape)" ] }, { "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": [ "plt.hist(user_intersting_clips[\"user_id\"].value_counts(), bins=np.linspace(0, 802, 100))\n", "plt.yscale(\"log\")\n", "plt.xlabel(\"number of preferences\")\n", "plt.ylabel(\"number of users\")\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(\n", " \"finally\",\n", " user_intersting_clips.shape[0],\n", " \"requests\",\n", " user_intersting_clips.shape[0] / 2,\n", " \"frac\",\n", " user_intersting_clips.shape[0] / total_clip_counts,\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()\n", "# 214202\n", "# 608730\n", "# 1376250\n", "# 4625842" ] }, { "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(\n", " \"time validation\",\n", " user_intersting_clips[\"created_at\"].min(),\n", " user_intersting_clips[\"created_at\"].max(),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:24:59.900268Z", "start_time": "2024-05-26T00:24:59.686528Z" } }, "outputs": [], "source": [ "print(\n", " \"time validation\",\n", " user_intersting_clips[\n", " user_intersting_clips[\"model_name\"] == \"chirp-v3p5-engine-s-18\"\n", " ][\"created_at\"].max(),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:00.171091Z", "start_time": "2024-05-26T00:24:59.901340Z" } }, "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", "date_cut = \"2024-06-04 15:21:36\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:00.375833Z", "start_time": "2024-05-26T00:25:00.172648Z" } }, "outputs": [], "source": [ "user_intersting_clips[\"is_pro_user\"] = user_intersting_clips[\"user_id\"].isin(pro_users)" ] }, { "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": [ "# 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-v3-engine-d\",\n", " # \"chirp-v3-engine-i\",\n", " # \"chirp-v3-engine-i-tp\",\n", " # \"chirp-v3-engine-s\",\n", " # \"chirp-v3p5-engine-d\",\n", " # \"chirp-v3p5-engine-s\",\n", " # \"chirp-v3p5-engine-s-2\",\n", " # \"chirp-v3p5-engine-s-3\",\n", " # \"chirp-v3p5-engine-s-4\",\n", " # \"chirp-v3p5-engine-s-5\",\n", " # \"chirp-v3p5-engine-s-6\",\n", " # \"chirp-v3p5-engine-s-7\",\n", " \"chirp-v3p5-engine-s-2\",\n", " \"chirp-v3p5-engine-s-8\",\n", " \"chirp-v3p5-engine-s-11\",\n", " \"chirp-v3p5-engine-s-12\",\n", " \"chirp-v3p5-engine-s-13\",\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-8-no-top-p\",\n", " \"chirp-v3p5-engine-upload\",\n", " \"chirp-v3p5-engine-upload-1\",\n", " \"chirp-v3p5-engine-upload-2\",\n", " \"chirp-v3p5-engine-upload-3\",\n", " \"chirp-v3p5-engine-upload-4\",\n", " \"chirp-v3p5-engine-ft\",\n", " \"chirp-v3p5-engine-ft-1\",\n", " \"chirp-v3p5-engine-ft-2\",\n", " \"chirp-v3p5-engine-t\",\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)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:01.522500Z", "start_time": "2024-05-26T00:25:01.521140Z" } }, "outputs": [], "source": [ "# %load_ext autoreload\n", "# %autoreload 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": [ "get_preferfence_counts(\n", " user_intersting_clips_3p5[(~user_intersting_clips_3p5[\"continued_parent\"].isna())],\n", " \"is continue\",\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "user_intersting_clips_3p5[\n", " user_intersting_clips_3p5[\"model_name\"]\n", " == \"chirp-v3p5-engine-s_cfg_tags_max_steps_none\"\n", "]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Clean up SHIT" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:02.592376Z", "start_time": "2024-05-26T00:25:02.575733Z" } }, "outputs": [], "source": [ "# to get the right play conts, we need the right df..." ] }, { "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", "partial_reaction_df = reaction_df[\n", " reaction_df[\"clip_id\"].isin(user_intersting_clips[\"id\"])\n", "].copy()\n", "print(partial_reaction_df.shape)\n", "total_play_reaction_df_sum = partial_reaction_df.groupby(\"clip_id\")[\"play_count\"].sum()\n", "total_play_reaction_df_sum_df = total_play_reaction_df_sum.reset_index().rename(\n", " columns={\"clip_id\": \"id\", \"play_count\": \"reaction_play_count\"}\n", ")\n", "user_intersting_clips = user_intersting_clips.merge(\n", " total_play_reaction_df_sum_df, on=\"id\", how=\"left\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:07.781022Z", "start_time": "2024-05-26T00:25:07.684000Z" } }, "outputs": [], "source": [ "partial_reaction_df_pro = reaction_df[\n", " (reaction_df[\"clip_id\"].isin(user_intersting_clips[\"id\"]))\n", " & (reaction_df[\"is_pro_user\"])\n", "].copy()\n", "print(partial_reaction_df_pro.shape)\n", "total_play_reaction_df_sum_pro = partial_reaction_df_pro.groupby(\"clip_id\")[\n", " \"play_count\"\n", "].sum()\n", "total_play_reaction_df_sum_pro_df = total_play_reaction_df_sum_pro.reset_index().rename(\n", " columns={\"clip_id\": \"id\", \"play_count\": \"reaction_pro_play_count\"}\n", ")\n", "user_intersting_clips = user_intersting_clips.merge(\n", " total_play_reaction_df_sum_pro_df, 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": {}, "outputs": [], "source": [ "# user_intersting_clips[\n", "# (\n", "# user_intersting_clips[\"reaction_play_count\"]\n", "# - user_intersting_clips[\"reaction_pro_play_count\"]\n", "# )\n", "# >= 5\n", "# ][\"user_id\"].value_counts()" ] }, { "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": [ "# too_much_data_mask = (\n", "# (user_intersting_clips[\"preference\"] == False)\n", "# & (\n", "# (user_intersting_clips[\"dislike_count\"] >= 1) # single play is super catchy\n", "# | (user_intersting_clips[\"flag_count\"] >= 1) # or the concat play is super catchy\n", "# )\n", "# )\n", "# too_much_data_mask.sum() / ((user_intersting_clips[\"preference\"] == True).sum())\n", "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\"] >= 10)\n", " # & (user_intersting_clips[\"continued_parent\"].isna())\n", ")\n", "too_much_data_mask.sum() / ((user_intersting_clips[\"preference\"]).sum())" ] }, { "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 = user_intersting_clips[too_much_data_mask][\n", " \"request_id\"\n", "].unique()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:08.703927Z", "start_time": "2024-05-26T00:25:08.525193Z" } }, "outputs": [], "source": [ "final_interesting_clips = user_intersting_clips[\n", " user_intersting_clips[\"request_id\"].isin(set(final_good_enough_requests))\n", "].copy()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "final_interesting_clips[final_interesting_clips[\"preference\"]][\n", " \"model_name\"\n", "].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:08.884389Z", "start_time": "2024-05-26T00:25:08.705530Z" } }, "outputs": [], "source": [ "print(final_interesting_clips.groupby(\"batch_index\")[\"preference\"].value_counts())" ] }, { "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": [ "final_interesting_clips[\n", " final_interesting_clips[\"model_name\"] == \"chirp-v3p5-engine-ft-1\"\n", "].shape" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:36:45.167690Z", "start_time": "2024-05-26T00:36:45.164768Z" } }, "outputs": [], "source": [ "# final_interesting_clips[final_interesting_clips[\"model_name\"] == \"chirp-v3p5-engine-ft-1\"].to_csv(\n", "# \"/home/tony/Data/Preference/13b_v0/interesting_clips_ft_1_20240711.csv\", index=False\n", "# )\n", "# final_interesting_clips[final_interesting_clips[\"model_name\"] == \"chirp-v3p5-engine-upload\"].to_csv(\n", "# \"/home/tony/Data/Preference/13b_v0/interesting_clips_20240623_extend.csv\", index=False\n", "# )\n", "print(\"done\", final_interesting_clips.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:09.681554Z", "start_time": "2024-05-26T00:25:09.441803Z" } }, "outputs": [], "source": [ "# smaller_mask = final_interesting_clips[\"model_name\"].isin([\"chirp-v3-engine-d\", \"chirp-v3-engine-v0\"])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-05-26T00:25:09.933748Z", "start_time": "2024-05-26T00:25:09.683018Z" } }, "outputs": [], "source": [ "# final_interesting_clips[smaller_mask].to_csv(\n", "# \"/home/tony/Data/Preference/7b_v2/interesting_clips_20240421_prev_model.csv\", index=False\n", "# )" ] }, { "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": [ "print(\"total unique users\", clip_df[\"user_id\"].nunique())" ] }, { "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(no_reaction_clip_df.shape)\n", "print(no_reaction_clip_df.shape[0] / total_clip_df.shape[0])" ] }, { "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", "print(no_reaction_clip_df[bot_user_mask].shape[0] / sub_total_clip_df.shape[0])" ] }, { "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(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(len(super_bad_pro_user_id))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from datetime import datetime\n", "\n", "curr_date = datetime.today().strftime(\"%Y_%m_%d\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# 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": [ "total_clip_df[total_clip_df[\"user_id\"].isin(super_bad_user_id)].shape[\n", " 0\n", "] / total_clip_df.shape[0]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "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": [ "# session_query = snow_session.sql(f\"\"\" select *\n", "# from ML_SONG_SUMMARY_INFO\n", "# where song_id in ('7e7da06b-5d1f-4cc9-8e7c-54bb7c9bd3f1')\n", "# and p_date = DATE(SYSDATE() - INTERVAL '1 HOUR')\n", "# order by p_hour desc\n", "# limit 1;\"\"\")\n", "# df_snow_test = pd.DataFrame(session_query.collect())" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# df_snow_test" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# user_intersting_clips_3p5[\"prompt_text\"]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Express Feedback dataset" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "query = f\"\"\"\n", "SELECT * FROM bots_userreaction\n", "WHERE feedback_reason IS NOT NULL\n", "\"\"\"\n", "feedback_reaction_df = pd.read_sql_query(query, engine)\n", "print(f\"{feedback_reaction_df.shape[0]:,} rows\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(feedback_reaction_df.shape)\n", "feedback_reaction_df = feedback_reaction_df[\n", " feedback_reaction_df[\"feedback_reason\"] != \"\"\n", "]\n", "print(\"after reemoving no feedback parts\", feedback_reaction_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "bad_audio_quality_ids = list(\n", " str(s)\n", " for s in feedback_reaction_df[\n", " feedback_reaction_df[\"feedback_reason\"].str.contains(\"bad_poor_audio_quality\")\n", " ][\"clip_id\"].unique()\n", ")\n", "# with open(\n", "# \"/home/tony/Data/Preference/13b_v0/interesting_clips_20240627_feedback_bad_audio_quality_ids.json\",\n", "# \"w\",\n", "# ) as fp:\n", "# json.dump(bad_audio_quality_ids, fp)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# id_query_str = \",\".join(\"'\" + x + \"'\" for x in bad_audio_quality_ids)\n", "\n", "# query = f\"\"\"\n", "# SELECT * FROM bots_generatedclip\n", "# WHERE status='complete' AND id IN ({id_query_str})\n", "# \"\"\"\n", "# sub_feedback_clip_df = pd.read_sql_query(query, engine)\n", "\n", "# sub_feedback_request_ids = list(str(s) for s in sub_feedback_clip_df[\"request_id\"].unique())\n", "\n", "# id_value_counts = sub_feedback_clip_df[\"request_id\"].value_counts()\n", "# # Filter to keep only values with a count of 1\n", "# unique_request_values = id_value_counts[id_value_counts == 1].reset_index()[\"request_id\"].unique()\n", "# print(len(unique_request_values))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "feedback_clip_ids = feedback_reaction_df[\"clip_id\"].unique()\n", "print(\"unique_feedback_clip_ids\", len(feedback_clip_ids))\n", "subset_of_clips_df = total_clip_df[total_clip_df[\"id\"].isin(feedback_clip_ids)].copy()\n", "print(subset_of_clips_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "feedback_requests = subset_of_clips_df[\"request_id\"].unique()\n", "print(f\"total feedback requets, {len(feedback_requests)}\")\n", "feedback_clip_df = clip_df[clip_df[\"request_id\"].isin(feedback_requests)].copy()\n", "print(feedback_clip_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# missing_requests = set(feedback_requests).difference(clip_df[\"request_id\"].unique())\n", "# print(\"missing requests\", len(missing_requests))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "positive_feedback_ids = feedback_reaction_df[\n", " feedback_reaction_df[\"feedback_reason\"] == \"good_quality\"\n", "][\"clip_id\"].unique()\n", "negative_feedback_ids = feedback_reaction_df[\n", " feedback_reaction_df[\"feedback_reason\"] != \"good_quality\"\n", "][\"clip_id\"].unique()\n", "print(\n", " f\"postive feedbacks {len(positive_feedback_ids)}, negative feedbacks {len(negative_feedback_ids)}\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(\n", " feedback_clip_df[\"pos_preference\"].value_counts(),\n", " feedback_clip_df[\"neg_preference\"].value_counts(),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# request has a positive\n", "# postive_feedback_mask = feedback_clip_df[\"id\"].isin(positive_feedback_ids) & (\n", "# ~feedback_clip_df[\"id\"].isin(negative_feedback_ids)\n", "# )\n", "postive_feedback_mask = feedback_clip_df[\"id\"].isin(positive_feedback_ids)\n", "negative_feedback_mask = feedback_clip_df[\"id\"].isin(negative_feedback_ids)\n", "print(sum(postive_feedback_mask), sum(negative_feedback_mask))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "positive_feedback_requests = set(\n", " feedback_clip_df[postive_feedback_mask][\"request_id\"].unique()\n", ")\n", "non_positive_feedback_requests = set(\n", " feedback_clip_df[~postive_feedback_mask][\"request_id\"].unique()\n", ")\n", "non_negative_feedback_requests = set(\n", " feedback_clip_df[~negative_feedback_mask][\"request_id\"].unique()\n", ")\n", "negative_feedback_requests = set(\n", " feedback_clip_df[negative_feedback_mask][\"request_id\"].unique()\n", ")\n", "print(\n", " f\"total unique requets {feedback_clip_df['request_id'].nunique()}, \\n\"\n", " f\"postive feedbacks requests {len(positive_feedback_requests)},\"\n", " f\"non-positive feedback requets {len(non_positive_feedback_requests)}, \\n\"\n", " f\"non-negative feedback requets {len(non_negative_feedback_requests)},\"\n", " f\"negative feedbacks requests {len(negative_feedback_requests)}\"\n", ")\n", "positive_request_pairs = positive_feedback_requests.intersection(\n", " non_positive_feedback_requests\n", ")\n", "negative_request_pairs = negative_feedback_requests.intersection(\n", " non_negative_feedback_requests\n", ")\n", "total_feedback_requests = positive_request_pairs.union(negative_request_pairs)\n", "print(\n", " f\"positive pairs {len(positive_request_pairs)}\",\n", " f\"negative pairs {len(negative_request_pairs)}\",\n", " f\"total pairs {len(total_feedback_requests)}\",\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df = feedback_clip_df[\n", " feedback_clip_df[\"request_id\"].isin(total_feedback_requests)\n", "].copy()\n", "print(paired_feedback_clip_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df[\"pos_feedback\"] = paired_feedback_clip_df[\"id\"].isin(\n", " positive_feedback_ids\n", ") & (~paired_feedback_clip_df[\"id\"].isin(negative_feedback_ids))\n", "paired_feedback_clip_df[\"neg_feedback\"] = paired_feedback_clip_df[\"id\"].isin(\n", " negative_feedback_ids\n", ") & (~paired_feedback_clip_df[\"id\"].isin(positive_feedback_ids))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "(\n", " paired_feedback_clip_df[\"pos_feedback\"].value_counts(),\n", " paired_feedback_clip_df[\"neg_feedback\"].value_counts(),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df = paired_feedback_clip_df.sort_values(\n", " by=[\"request_id\"]\n", ").reset_index(drop=True)\n", "paired_feedback_clip_df[\"diff_preference\"] = paired_feedback_clip_df[\n", " \"pos_preference\"\n", "].astype(int) - paired_feedback_clip_df[\"neg_preference\"].astype(int)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df[\"diff_preference\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "feedback_diff_series = paired_feedback_clip_df[\"diff_preference\"].diff()\n", "print(\n", " feedback_diff_series[1::2].value_counts()\n", ") # 1 is pos, not neg pair or nothing, neg; 2 is pos / neg (hence the larger difference)\n", "# but this can be wilder...as we didn't filter on requests!" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df = paired_feedback_clip_df.sort_values(\n", " by=[\"request_id\", \"diff_preference\"]\n", ").reset_index(drop=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df[\"preference\"] = paired_feedback_clip_df.index % 2 == 1\n", "paired_feedback_clip_df[\"preference\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df[\"diff_feedback\"] = paired_feedback_clip_df[\n", " \"pos_feedback\"\n", "].astype(int) - paired_feedback_clip_df[\"neg_feedback\"].astype(int)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df[\"diff_feedback\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df = paired_feedback_clip_df.sort_values(\n", " by=[\"request_id\", \"diff_feedback\"]\n", ").reset_index(drop=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "feedback_diff_feedback_series = paired_feedback_clip_df[\"diff_feedback\"].diff()\n", "\n", "print(\n", " feedback_diff_feedback_series[1::2].value_counts()\n", ") # 1 is pos, not neg pair or nothing, neg; 2 is pos / neg (hence the larger difference)\n", "# but this can be wilder...as we didn't filter on requests!" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df[\"feedback_preference\"] = paired_feedback_clip_df.index % 2 == 1" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "(\n", " paired_feedback_clip_df[\"feedback_preference\"]\n", " == paired_feedback_clip_df[\"preference\"]\n", ").value_counts(normalize=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "(\n", " paired_feedback_clip_df[\"pos_feedback\"] == paired_feedback_clip_df[\"pos_preference\"]\n", ").value_counts(normalize=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "(\n", " paired_feedback_clip_df[\"neg_feedback\"] == paired_feedback_clip_df[\"neg_preference\"]\n", ").value_counts(normalize=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# overlapping with existing?\n", "paired_feedback_clip_df[\"request_id\"].isin(\n", " final_interesting_clips[\"request_id\"]\n", ").sum() // 2" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# paired_feedback_clip_df.to_csv(\"/home/tony/Data/Preference/13b_v0/interesting_clips_20240627_feedback.csv\", index=False)\n", "print(paired_feedback_clip_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "weird_feedback_mask = (paired_feedback_clip_df[\"pos_feedback\"]) & (\n", " paired_feedback_clip_df[\"dislike_count\"] > 0\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "paired_feedback_clip_df[weird_feedback_mask][\"user_id\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# feedback_reaction_df[feedback_reaction_df[\"clip_id\"].isin(paired_feedback_clip_df[weird_feedback_mask][\"id\"].unique())]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "my_sub_set_request_ids = paired_feedback_clip_df[paired_feedback_clip_df[\"model_name\"] == \"chirp-v3p5-engine-t\"][\"request_id\"].unique()\n", "paired_feedback_clip_df[paired_feedback_clip_df[\"request_id\"].isin(my_sub_set_request_ids)]" ] }, { "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 }