{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Instructions\n", "\n", "Get data\n", "- Download the latest backup via `wget` from [Render Dashboard](https://dashboard.render.com/d/dpg-cgfrde82qv28tc0tavcg-a/recovery). Ask Martin for access. Name it something like `studio.sql.gz`\n", "- wget -O studio.sql.gz link\n", "- copy the link and `wget link`. Note this can take ~ 0.5hr to download\n", "- Unzip it with `gzip -d studio.sql.gz` (also takes a bit of time)\n", "\n", "Some of these need to be done once:\n", "- sudo apt-get install postgresql\n", "- sudo service postgresql start\n", "- sudo service postgresql status\n", "- export PATH=$PATH:/usr/lib/postgresql/12/bin (maybe?)\n", "- need to move it to local dir (pgdata), see: https://fitodic.github.io/how-to-change-postgresql-data-directory-on-linux\n", "- vim /etc/postgresql/12/main/pg_hba.conf and change to \n", " - `local all all trust` \n", " - `local all all trust` \n", "- sudo service postgresql restart\n", "- createdb -U postgres mydatabase (this can take a while)\n", "\n", "Finally:\n", "Load it into postres via `psql -U postgres -d mydatabase -f studio.sql`\n", "This is taking forever now `6:54:43.33` \n", "\n", "Things that can be useful (some debugging mumble jumble for imgrating data disk):\n", "- psql -U postgres -d mydatabase\n", "- ALTER SYSTEM SET max_wal_size = '1GB';\n", "- SHOW max_wal_size;\n", "- pg_lsclusters\n", "- sudo pg_ctlcluster 12 main start\n", "- Check postgres user belongs to ssl-cert user group: \n", "- chown -R postgres:postgres pgdata\n", "- chmod -R u+rwx,g-rwx,o-rwx pgdata\n", "- sudo chown postgres.postgres /var/lib/postgresql/12/main/global/pg_internal.init\n", "- sudo rm -rf 12/main/global/pg_internal.init\n", "- sudo rm -rf /var/lib/postgresql/12/main/pg_logical/replorigin_checkpoint\n", "- sudo -i -u postgres\n", "- /usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/12/main\n", "\n", "\n", "Create user? (only first time)\n", "- psql -U postgres\n", "- CREATE ROLE tony WITH LOGIN PASSWORD '123';\n", "- \\q\n", "\n", "Then need to authenticate? (need to redo this after recreating a new database everytime) \n", "- psql -U postgres -d mydatabase\n", "- \\du\n", "- \\dt\n", "- \\l+ (check size)\n", "- SELECT COUNT(*) FROM bots_generatedclip;\n", "- GRANT ALL PRIVILEGES ON DATABASE mydatabase TO tony;\n", "- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO tony;\n", "- \\q" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T12:30:38.245398Z", "start_time": "2024-04-10T12:30:36.386477Z" } }, "outputs": [], "source": [ "# pip install psycopg2-binary\n", "# make sure sqlalchemy is >=2\n", "import pandas as pd\n", "import sqlalchemy\n", "from suno_utils.audio import Audio\n", "from suno_utils.utils.s3 import open_from_s3\n", "import numpy as np\n", "import ast\n", "import tqdm\n", "import matplotlib.pyplot as plt\n", "\n", "from preference_helper import *\n", "\n", "# %#load_ext autoreload\n", "# %#autoreload 2\n", "\n", "# engine = sqlalchemy.create_engine(\"postgresql://tony:123@localhost/mydatabase\")\n", "# alternative...\n", "engine = sqlalchemy.create_engine(\n", " \"postgresql://postgres:cfAoZgBhraL0bDRd2TAn@suno-rds-prod.cluster-cnfvffydbwvc.us-east-2.rds.amazonaws.com/studio_hga1\"\n", ")\n", "# connection = engine.raw_connection()\n", "# %#load_ext autoreload\n", "# %#autoreload 2" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T14:17:51.505314Z", "start_time": "2024-04-10T12:30:44.294786Z" } }, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT * FROM bots_generatedclip\n", "WHERE status='complete'\n", "\"\"\"\n", "all_clip_df = pd.read_sql_query(query, engine)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T16:58:23.727794Z", "start_time": "2024-04-10T16:58:23.725780Z" } }, "outputs": [], "source": [ "# all_clip_df.to_csv(\"/app/suno/data/dpo/data_prep/clip_df_20240411.csv\", index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-11T02:50:18.417155Z", "start_time": "2024-04-11T02:50:18.383562Z" } }, "outputs": [], "source": [ "all_clip_df.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-11T02:54:38.679933Z", "start_time": "2024-04-11T02:52:08.163450Z" } }, "outputs": [], "source": [ "all_clip_df[all_clip_df[\"id\"].astype(str) == \"e6a6fcfc-ab56-4a11-9fcf-a4fac5b238b7\"]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T04:03:44.377869Z", "start_time": "2024-04-10T04:03:44.376183Z" } }, "outputs": [], "source": [ "# # Read the sql file and execute the query\n", "# with open('/home/tony/Data/Preference/studio.sql', 'r') as query:\n", "# # connection == the connection to your database, in your case prob_db\n", "# df = pd.read_sql_query(query.read(), connection)\n", "\n", "# pd.read_sql_query(\n", "# \"SELECT COUNT(*) FROM bots_generatedclip;\",\n", "# engine,\n", "# )" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Validate some info" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T04:03:45.253407Z", "start_time": "2024-04-10T04:03:44.379438Z" } }, "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", "print(df_all_tables[\"table_name\"].values)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T04:04:56.739823Z", "start_time": "2024-04-10T04:03:45.254753Z" } }, "outputs": [], "source": [ "# bots_generatedclipextra\n", "# these are all the logged actions\n", "query = \"\"\"\n", "SELECT * FROM bots_generatedclipextra\n", "\"\"\"\n", "bots_action_df = pd.read_sql_query(query, engine)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T04:05:50.524862Z", "start_time": "2024-04-10T04:04:56.741965Z" } }, "outputs": [], "source": [ "# bots_userreaction\n", "# id\tplay_count\tskip_count\tflagged\tflagged_reason\treaction_type\tupdated_at\tclip_id\tuser_id\n", "# this turns out to be much smaller ~ 570k\n", "query = \"\"\"\n", "SELECT * FROM bots_userreaction\n", "WHERE reaction_type='L' AND play_count>0\n", "\"\"\"\n", "upvoted_df = pd.read_sql_query(query, engine)\n", "print(f\"{upvoted_df.shape[0]:,} rows\")\n", "upvoted_ids = upvoted_df[\"clip_id\"]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T04:05:53.736000Z", "start_time": "2024-04-10T04:05:50.526523Z" } }, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT * FROM bots_userreaction\n", "WHERE flagged=TRUE AND play_count>0\n", "\"\"\"\n", "flagged_df = pd.read_sql_query(query, engine)\n", "print(f\"{flagged_df.shape[0]:,} rows\")\n", "flagged_ids = flagged_df[\"clip_id\"]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T04:16:17.291255Z", "start_time": "2024-04-10T04:05:53.737556Z" } }, "outputs": [], "source": [ "# ~ 4 min...X.x\n", "query = \"\"\"\n", "SELECT * FROM bots_userreaction\n", "WHERE updated_at>'2024-02-20' 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-04-10T04:16:17.296630Z", "start_time": "2024-04-10T04:16:17.294338Z" } }, "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}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:43:02.990127Z", "start_time": "2024-04-10T04:16:17.297887Z" } }, "outputs": [], "source": [ "# ~ 1h 25 mins...\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", "query = \"\"\"\n", "SELECT * FROM bots_generatedclip\n", "WHERE status='complete' AND created_at>'2024-02-20' \n", "\"\"\"\n", "clip_df = pd.read_sql_query(query, engine)\n", "print(f\"{clip_df.shape[0]:,} rows\")\n", "# filter on versions\n", "clip_df = clip_df[\n", " (clip_df[\"model_name\"].str.contains(\"v3\")) # or v3...\n", " & (clip_df[\"created_at\"] >= \"2024-02-20\")\n", "]\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", "# 9,745,619 rows\n", "# total v3 selected fraction = 0.11235114663577185\n", "# total clips: 9745619" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:43:46.317333Z", "start_time": "2024-04-10T05:43:02.991616Z" } }, "outputs": [], "source": [ "# get playlists\n", "query = \"\"\"\n", "SELECT * FROM bots_playlistclip\n", "\"\"\"\n", "playlist_clip_df = pd.read_sql_query(query, engine)\n", "print(f\"{playlist_clip_df.shape[0]:,} clips\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Proceed with feature engineering and cleaning up" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:44:30.994655Z", "start_time": "2024-04-10T05:43:46.319439Z" } }, "outputs": [], "source": [ "# add clip is in playlist feature\n", "clip_df[\"is_in_playlist\"] = clip_df[\"id\"].isin(playlist_clip_df[\"clip_id\"].unique())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:45:38.403703Z", "start_time": "2024-04-10T05:44:30.996655Z" } }, "outputs": [], "source": [ "def parse_parent_id(x):\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", " out = out[0]\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", "clip_df[\"continued_parent\"] = clip_df[\"metadata\"].apply(lambda x: parse_parent_id(x))\n", "clip_history_df = clip_df[~clip_df[\"continued_parent\"].isna()].copy()\n", "continued_ids = clip_history_df[\"id\"]\n", "has_continued_children_ids = clip_history_df[\n", " \"continued_parent\"\n", "] # these are the parent's ids\n", "print(\n", " \"clips that have children\",\n", " len(has_continued_children_ids),\n", " len(has_continued_children_ids.unique()),\n", " \"\\n\",\n", " \"Average continues from clip = \",\n", " len(has_continued_children_ids) / len(has_continued_children_ids.unique()),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:46:27.610400Z", "start_time": "2024-04-10T05:45:38.405811Z" } }, "outputs": [], "source": [ "# 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(f\"concat clips frac = {concated_clips.shape[0] / total_clip_counts}\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:46:35.811210Z", "start_time": "2024-04-10T05:46:27.612132Z" } }, "outputs": [], "source": [ "clip_df[\"model_name\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:47:15.812625Z", "start_time": "2024-04-10T05:46:35.813499Z" } }, "outputs": [], "source": [ "print(clip_df.shape)\n", "clip_df = clip_df[\n", " clip_df[\"model_name\"].isin(\n", " [\n", " \"chirp-v3-engine-d\",\n", " \"chirp-v3-engine-v0\",\n", " \"chirp-v3-engine-i\",\n", " \"chirp-v3-engine-i-d\",\n", " \"chirp-v3-engine-s\",\n", " ]\n", " )\n", "]\n", "print(clip_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:47:15.825213Z", "start_time": "2024-04-10T05:47:15.814415Z" } }, "outputs": [], "source": [ "# I fucking hate this but what can I do\n", "# DO NOT FILTER ON play counts yet..." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:48:55.502730Z", "start_time": "2024-04-10T05:47:15.826799Z" } }, "outputs": [], "source": [ "# ~ only 1 min :) \n", "concat_reaction_df = reaction_df[reaction_df[\"clip_id\"].isin(concated_clips[\"id\"])].copy()\n", "print(concat_reaction_df.shape, concat_reaction_df['clip_id'].nunique())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:49:14.668757Z", "start_time": "2024-04-10T05:48:55.505189Z" } }, "outputs": [], "source": [ "concat_total_play_reaction_df_sum = concat_reaction_df.groupby(\"clip_id\")[\n", " \"play_count\"\n", "].sum()\n", "concat_total_play_reaction_df_sum_df = (\n", " concat_total_play_reaction_df_sum.reset_index().rename(\n", " columns={\"clip_id\": \"id\", \"play_count\": \"reaction_play_count\"}\n", " )\n", ")\n", "concated_clips = concated_clips.merge(\n", " concat_total_play_reaction_df_sum_df, on=\"id\", how=\"left\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:49:15.867457Z", "start_time": "2024-04-10T05:49:14.671694Z" } }, "outputs": [], "source": [ "print(concated_clips.shape)\n", "concated_clips = concated_clips[concated_clips[\"reaction_play_count\"] > 0]\n", "print(concated_clips.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:49:15.875183Z", "start_time": "2024-04-10T05:49:15.870407Z" } }, "outputs": [], "source": [ "# concated_clips[[\"upvote_count\", \"dislike_count\"]].describe()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:50:11.260396Z", "start_time": "2024-04-10T05:49:15.879253Z" } }, "outputs": [], "source": [ "# this is each clip. and the mapped start time of the clip\n", "concat_clips_ids = {}\n", "for _, row in tqdm.tqdm(concated_clips.iterrows()):\n", " if history_ids := row[\"metadata\"][\"concat_history\"]:\n", " total_duration = row[\"metadata\"][\"duration\"]\n", " start_s = 0\n", " for history_id in history_ids:\n", " # print(history_ids, row[\"metadata\"][\"duration\"])\n", " if isinstance(history_id, dict) and \"id\" in history_id:\n", " # the other key is `continue_at`\n", " concat_clips_ids[history_id[\"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[\"upvote_count\"],\n", " }\n", " start_s = history_id[\"continue_at\"]\n", "print(\"total concat unique clips are:\", len(concat_clips_ids))" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:50:12.258330Z", "start_time": "2024-04-10T05:50:11.263683Z" } }, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT *\n", "FROM auth_user_groups\n", "\"\"\"\n", "auth_user_df = pd.read_sql_query(query, engine)\n", "auth_user_df.shape" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Features" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:50:13.848703Z", "start_time": "2024-04-10T05:50:12.263009Z" } }, "outputs": [], "source": [ "clip_df[\"is_pro_user\"] = clip_df[\"user_id\"].isin(auth_user_df[\"user_id\"].unique())\n", "clip_df[\"is_pro_user\"].value_counts()\n", "clip_df[\"user_id\"][clip_df[\"is_pro_user\"]].nunique()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:50:16.481581Z", "start_time": "2024-04-10T05:50:13.850272Z" } }, "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-04-10T05:50:59.292623Z", "start_time": "2024-04-10T05:50:16.483035Z" } }, "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", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:50:59.738095Z", "start_time": "2024-04-10T05:50:59.294074Z" } }, "outputs": [], "source": [ "print(\n", " \"has upvoted\",\n", " clip_df[\"upvoted\"].value_counts(),\n", " clip_df[\"upvoted\"].value_counts(normalize=True),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:51:00.066393Z", "start_time": "2024-04-10T05:50:59.739505Z" } }, "outputs": [], "source": [ "(clip_df[\"upvote_count\"] >= 1).value_counts(normalize=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "start_time": "2024-04-10T04:03:39.684Z" } }, "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-04-10T05:54:51.771470Z", "start_time": "2024-04-10T05:53:04.500042Z" } }, "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", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:56:14.467501Z", "start_time": "2024-04-10T05:54:51.772984Z" } }, "outputs": [], "source": [ "# verify bots action are all non-empty\n", "# assert (\n", "# bots_action_df[\n", "# bots_action_df[\"download_audio_count\"]\n", "# + bots_action_df[\"download_video_count\"]\n", "# + bots_action_df[\"share_count\"]\n", "# == 0\n", "# ].shape[0]\n", "# == 0\n", "# )\n", "action_mask = (\n", " bots_action_df[\"download_audio_count\"] + bots_action_df[\"download_video_count\"]\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-04-10T05:56:56.674351Z", "start_time": "2024-04-10T05:56:14.468954Z" } }, "outputs": [], "source": [ "# add downvoted column\n", "clip_df[\"downvoted\"] = clip_df[\"id\"].isin(flagged_ids)\n", "print(\n", " \"has downvoted\",\n", " clip_df[\"downvoted\"].value_counts(),\n", " clip_df[\"downvoted\"].value_counts(normalize=True),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T05:57:05.296989Z", "start_time": "2024-04-10T05:56:56.675781Z" } }, "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\"] == True)\n", " | (clip_df[\"has_action\"] == True)\n", " | (clip_df[\"part_of_concat\"] == True)\n", ")\n", "must_be_not_negative_mask = clip_df[\"downvoted\"] == False\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)}, pos {sum(must_be_positive_mask)}, neg {sum(must_be_not_negative_mask)}\"\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:01:41.120122Z", "start_time": "2024-04-10T05:57:05.298382Z" } }, "outputs": [], "source": [ "total_unique_requests = clip_df[\"request_id\"].nunique()\n", "liked_requests = clip_df[mask][\"request_id\"].unique()\n", "unliked_requests = clip_df[~mask][\"request_id\"].unique()\n", "has_liked_requests = set(liked_requests).intersection(set(unliked_requests))\n", "print(\"liked\", len(liked_requests), \"unliked\", 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": { "ExecuteTime": { "end_time": "2024-04-10T06:01:41.123914Z", "start_time": "2024-04-10T06:01:41.121576Z" } }, "outputs": [], "source": [ "requests = has_liked_requests\n", "len(requests)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:01:41.197984Z", "start_time": "2024-04-10T06:01:41.125016Z" } }, "outputs": [], "source": [ "# this used to be a terrible bug...X.x\n", "assert mask.shape[0] == clip_df.shape[0]\n", "clip_df[\"preference\"] = mask" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:03:52.106081Z", "start_time": "2024-04-10T06:01:41.199584Z" } }, "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": { "ExecuteTime": { "end_time": "2024-04-10T06:04:45.468679Z", "start_time": "2024-04-10T06:03:52.107853Z" } }, "outputs": [], "source": [ "# get df of requests -- let's move on!\n", "print(interesting_clips.shape)\n", "interesting_clips = interesting_clips[\n", " (\n", " interesting_clips[\"model_name\"].isin(\n", " [\n", " \"chirp-v3-engine-d\",\n", " \"chirp-v3-engine-v0\",\n", " \"chirp-v3-engine-i\",\n", " \"chirp-v3-engine-i-d\",\n", " \"chirp-v3-engine-s\",\n", " ]\n", " )\n", " ) # or v3...\n", " & (interesting_clips[\"created_at\"] >= \"2024-02-14\")\n", "]\n", "print(interesting_clips.shape)\n", "print(interesting_clips[\"request_id\"].nunique(), interesting_clips[\"id\"].nunique())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:06:25.298843Z", "start_time": "2024-04-10T06:04:45.470026Z" } }, "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-04-10T06:06:26.614891Z", "start_time": "2024-04-10T06:06:25.300288Z" } }, "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 = interesting_clips[\"downvoted\"] == False\n", "interesting_clips_mask = interesting_clips_must_be_positive_mask & interesting_clips_must_be_not_negative_mask\n", "assert interesting_clips_mask.eq(interesting_clips[\"preference\"]).all()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:06:26.658513Z", "start_time": "2024-04-10T06:06:26.616795Z" } }, "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-04-10T06:06:26.725368Z", "start_time": "2024-04-10T06:06:26.659639Z" } }, "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-04-10T06:06:27.112959Z", "start_time": "2024-04-10T06:06:26.726473Z" } }, "outputs": [], "source": [ "interesting_clips.groupby(\"batch_index\")[\"preference\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:06:27.473222Z", "start_time": "2024-04-10T06:06:27.114676Z" } }, "outputs": [], "source": [ "interesting_clips[\"user_id\"].nunique(), interesting_clips[\"user_id\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:06:29.667337Z", "start_time": "2024-04-10T06:06:27.474677Z" } }, "outputs": [], "source": [ "interesting_clips[\"model_name\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:06:30.011086Z", "start_time": "2024-04-10T06:06:29.669422Z" } }, "outputs": [], "source": [ "print(\n", " \"time validation\",\n", " interesting_clips[\"created_at\"].min(),\n", " interesting_clips[\"created_at\"].max(),\n", " clip_df[\"created_at\"].min(),\n", " clip_df[\"created_at\"].max(),\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:07:16.129787Z", "start_time": "2024-04-10T06:06:30.013192Z" } }, "outputs": [], "source": [ "print(interesting_clips[\"request_id\"].nunique(), interesting_clips[\"id\"].nunique())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:08:53.118663Z", "start_time": "2024-04-10T06:07:16.131098Z" } }, "outputs": [], "source": [ "interesting_clips = interesting_clips.sort_values(by=[\"request_id\", \"preference\"])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:09:09.315839Z", "start_time": "2024-04-10T06:08:53.120083Z" } }, "outputs": [], "source": [ "interesting_clips[interesting_clips[\"preference\"] == True][\n", " \"model_name\"\n", "].value_counts() / clip_df[\"model_name\"].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:10:38.677551Z", "start_time": "2024-04-10T06:09:09.317120Z" } }, "outputs": [], "source": [ "get_preferfence_counts(interesting_clips)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:10:38.680765Z", "start_time": "2024-04-10T06:10:38.679190Z" } }, "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-04-10T06:10:40.770845Z", "start_time": "2024-04-10T06:10:38.681824Z" } }, "outputs": [], "source": [ "plt.hist(interesting_clips[\"play_count\"], bins=np.linspace(0, 100, 50))\n", "plt.yscale(\"log\")\n", "plt.xlabel(\"number of play counts\")\n", "plt.ylabel(\"number of clips\")\n", "plt.show()\n", "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-04-10T06:10:41.418469Z", "start_time": "2024-04-10T06:10:40.772232Z" } }, "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": { "start_time": "2024-04-10T04:03:39.708Z" } }, "outputs": [], "source": [ "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\", \"play_count\", \"part_of_concat\", \"time_used\"], ascending=False\n", " )\n", " .groupby(\"user_id\")\n", " .head(MAX_PREFERENCE_PER_USER)\n", ")\n", "print(user_top_df.shape, interesting_clips.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "start_time": "2024-04-10T04:03:39.709Z" } }, "outputs": [], "source": [ "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": { "start_time": "2024-04-10T04:03:39.709Z" } }, "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": { "start_time": "2024-04-10T04:03:39.710Z" } }, "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": { "start_time": "2024-04-10T04:03:39.710Z" } }, "outputs": [], "source": [ "user_intersting_clips.describe()\n", "# 214202\n", "# 608730\n", "# 1376250\n", "# 4625842" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "start_time": "2024-04-10T04:03:39.711Z" } }, "outputs": [], "source": [ "date_cut = '2024-04-05 07:30:00' # v3 launch test time # 2024-04-03 07:47:01.770988+00:00 t1" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "start_time": "2024-04-10T04:03:39.711Z" } }, "outputs": [], "source": [ "user_intersting_clips[\"is_pro_user\"] = user_intersting_clips[\"user_id\"].isin(auth_user_df[\"user_id\"].unique())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "start_time": "2024-04-10T04:03:39.712Z" } }, "outputs": [], "source": [ "user_compare_mask = (user_intersting_clips[\"created_at\"] >= date_cut) # & (user_intersting_clips[\"is_pro_user\"] == True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "start_time": "2024-04-10T04:03:39.712Z" } }, "outputs": [], "source": [ "get_preferfence_counts(\n", " user_intersting_clips[user_compare_mask]\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:12:46.343930Z", "start_time": "2024-04-10T06:12:31.826766Z" } }, "outputs": [], "source": [ "print(\"first gen\")\n", "get_preferfence_counts(\n", " user_intersting_clips[\n", " (user_intersting_clips[\"continued_parent\"].isna()) & user_compare_mask\n", " ],\n", " title_name=\"first generation\",\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:12:50.443147Z", "start_time": "2024-04-10T06:12:46.345527Z" } }, "outputs": [], "source": [ "get_preferfence_counts(\n", " user_intersting_clips[\n", " (~user_intersting_clips[\"continued_parent\"].isna())\n", " & user_compare_mask\n", " ],\n", " \"is continue\",\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Clean up SHIT" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:12:50.446535Z", "start_time": "2024-04-10T06:12:50.444918Z" } }, "outputs": [], "source": [ "# to get the right play conts, we need the right df..." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:16:05.773953Z", "start_time": "2024-04-10T06:12:50.447594Z" } }, "outputs": [], "source": [ "# ~ only 1 min :) \n", "partial_reaction_df = reaction_df[reaction_df[\"clip_id\"].isin(user_intersting_clips[\"id\"])].copy()\n", "print(partial_reaction_df.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:19:06.148760Z", "start_time": "2024-04-10T06:16:05.775305Z" } }, "outputs": [], "source": [ "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-04-10T06:19:42.314278Z", "start_time": "2024-04-10T06:19:06.150228Z" } }, "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", " }\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", " ]\n", "] = extra_cols_df" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:19:42.365125Z", "start_time": "2024-04-10T06:19:42.320257Z" } }, "outputs": [], "source": [ "too_much_data_mask = (\n", " (user_intersting_clips[\"preference\"] == True)\n", " & (\n", " (user_intersting_clips[\"reaction_play_count\"] >= 5) # single play is super catchy\n", " | (user_intersting_clips[\"concat_play_counts\"] >= 5) # or the concat play is super catchy\n", " )\n", ")\n", "too_much_data_mask.sum() / ((user_intersting_clips[\"preference\"] == True).sum())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:19:48.234805Z", "start_time": "2024-04-10T06:19:42.366855Z" } }, "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-04-10T06:20:15.303932Z", "start_time": "2024-04-10T06:19:48.236550Z" } }, "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": { "ExecuteTime": { "end_time": "2024-04-10T06:20:15.399785Z", "start_time": "2024-04-10T06:20:15.305771Z" } }, "outputs": [], "source": [ "print(final_interesting_clips.groupby(\"batch_index\")[\"preference\"].value_counts())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:20:39.710961Z", "start_time": "2024-04-10T06:20:15.401632Z" } }, "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": { "ExecuteTime": { "end_time": "2024-04-10T06:20:39.714654Z", "start_time": "2024-04-10T06:20:39.712446Z" } }, "outputs": [], "source": [ "# final_interesting_clips.to_csv(\n", "# \"/home/tony/Data/Preference/7b_v1/interesting_clips.csv\", 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-04-10T06:21:21.791539Z", "start_time": "2024-04-10T06:20:39.716693Z" } }, "outputs": [], "source": [ "neg_filter_selection_mask = interesting_clips[\"dislike_count\"] > 0\n", "pos_filter_selectin_mask = interesting_clips[\"play_count\"] > 1\n", "print(sum(neg_filter_selection_mask), sum(pos_filter_selectin_mask))\n", "# v1: 176273 1171102\n", "# v2: 229891 1616473\n", "# after launch: 228428 2494919\n", "neg_filter_requests = interesting_clips[neg_filter_selection_mask][\n", " \"request_id\"\n", "].unique()\n", "pos_filter_requests = interesting_clips[pos_filter_selectin_mask][\"request_id\"].unique()\n", "# looking for very strong signal here:\n", "# listen to the positive/negative more than once\n", "# disliked one of the clips\n", "unique_requests = set(pos_filter_requests).intersection(neg_filter_requests)\n", "print(interesting_clips[\"request_id\"].nunique(), len(unique_requests))\n", "# v1 853102 135360\n", "# v2 1170736 176676\n", "# after launch: 2273271 156605\n", "# final_interesting_clips = interesting_clips[\n", "# interesting_clips[\"request_id\"].isin(set(unique_requests))\n", "# ].copy()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:21:22.653565Z", "start_time": "2024-04-10T06:21:21.792886Z" } }, "outputs": [], "source": [ "print(\"total unique users\", clip_df[\"user_id\"].nunique())" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:22:03.766173Z", "start_time": "2024-04-10T06:21:22.655835Z" } }, "outputs": [], "source": [ "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-04-10T06:22:03.840664Z", "start_time": "2024-04-10T06:22:03.767476Z" } }, "outputs": [], "source": [ "clip_df[clip_df[\"user_id\"] == 4688272][\"created_at\"].apply(lambda x: str(x)[:10]).value_counts()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:22:03.906437Z", "start_time": "2024-04-10T06:22:03.842719Z" } }, "outputs": [], "source": [ "clip_df[clip_df[\"user_id\"] == 4688272].shape" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:22:03.935222Z", "start_time": "2024-04-10T06:22:03.907818Z" } }, "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-04-10T06:22:03.992991Z", "start_time": "2024-04-10T06:22:03.937040Z" } }, "outputs": [], "source": [ "user_df[user_df[\"id\"] == 3877426]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:22:11.248999Z", "start_time": "2024-04-10T06:22:03.994633Z" } }, "outputs": [], "source": [ "clip_df[clip_df[\"model_name\"] == \"chirp-v3-0\"]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:22:18.493260Z", "start_time": "2024-04-10T06:22:11.250346Z" } }, "outputs": [], "source": [ "# wtf\n", "for i, x in enumerate(clip_df[clip_df[\"model_name\"] == \"chirp-v3-0\"][\"metadata\"]):\n", " print(x)\n", " if i > 10:\n", " break" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "ExecuteTime": { "end_time": "2024-04-10T06:22:18.648615Z", "start_time": "2024-04-10T06:22:18.495137Z" } }, "outputs": [], "source": [ "query = \"\"\"\n", "SELECT *\n", "FROM auth_user\n", "WHERE id=186417\n", "\"\"\"\n", "user_df = pd.read_sql_query(query, engine)\n", "user_df" ] }, { "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 }