{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import sys" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "sys.path.insert(0, '/Users/joel/src/thundr/tracker')" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "No buildsha.py defined. sha will be 'NOT_SET'\n", "Not using secrets manager\n", "use_my_whitelist_cache False\n" ] } ], "source": [ "from tracker import config" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "from tracker import spotify, db\n", "from tracker.unicorn import mysql\n", "\n", "from importlib import reload\n", "\n", "from datetime import datetime, timedelta, date\n", "\n", "from collections import namedtuple\n", "\n", "from tracker import sheet, bangers\n", "\n", "from tracker.bangers import query_to_sheet" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "import csv\n", "\n", "def convert_tsdata(row, columns):\n", " row = list(row)\n", " sparkline_ix = columns.index('daily_streams')\n", " if row[sparkline_ix]:\n", " row[sparkline_ix] = [r[1] for r in csv.reader(row[sparkline_ix].splitlines()) if r[1] != 'num']\n", " return row[1:] # drop the spyid column\n", "\n", "def snake_to_title(name):\n", " return \" \".join([w.title() for w in name.split('_')])\n", "\n", "def get_sheet_csv(sql, args=None):\n", " with mysql.get_cursor() as cur:\n", " cur.execute(sql, args)\n", " columns = [c[0] for c in cur.description]\n", " newly_added = [convert_tsdata(r, columns) for r in cur.fetchall()]\n", "\n", " header = [snake_to_title(c) for c in columns[1:]]\n", " print(f\"Found {len(newly_added) - 1} artists\")\n", " return [header] + [list(r) for r in newly_added]\n", "\n", "def get_artist_sheet_csv(artist_spyids):\n", " spyids_args = \",\".join([repr(s) for s in artist_spyids])\n", " return get_sheet_csv(f'''\n", " select\n", " st.spyid as spyid,\n", " concat('https://open.spotify.com/track/', st.spyid) as link,\n", " st.title,\n", " sa.name,\n", " st.release_date,\n", " sal.monthly_listeners,\n", " sal.monthly_listeners_delta,\n", " coalesce(alb.p_line, alb.c_line) as p_line,\n", " upper(substring(st.isrc, 1, 2)) as release_country,\n", " -- st.isrc,\n", " sad.tsdata as daily_streams,\n", " sad.s0 as last_streams,\n", " sad.smax as max_daily,\n", " datediff(current_date, sad.smax_date) as max_streams_days_ago,\n", " datediff(current_date, st.first_seen) as first_seen_days_ago,\n", " count(distinct svp.name) as num_viral_adds,\n", " group_concat( replace(svp.name, ' Viral 50', '') ) as playlists\n", " from spotify_artist sa \n", " join spotify_track st on st.first_artist = sa.spyid\n", " join spotify_album alb on alb.spyid = st.album_id\n", " left join spotify_track_on_playlist top on st.spyid = top.track_id\n", " left join spotify_viral_playlist svp on svp.spyid = top.playlist_id\n", " left join spotify_artist_listeners sal on sa.spyid = sal.spyid\n", " left join spotify_daily_streams sad on sa.spyid = sad.spyid \n", " where sa.spyid in ({spyids_args})\n", " and not is_signed\n", " group by 1 having num_viral_adds > 0\n", " order by st.first_seen desc\n", " ''')\n", "\n", "def create_sheet_for_artists(artist_spyids, sheet_name=None, spr=None):\n", " if spr is None:\n", " sh = sheet.Sheet()\n", " spr = sh.client.open('Viral Adds')\n", " sheet_csv = get_artist_sheet_csv(artist_spyids)\n", " return query_to_sheet.csv_to_sheet(sheet_csv, spr, sheet_name=sheet_name)" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "spr.share('joel@whtlst.in', perm_type='user', role='writer', notify=False)" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Found 76 artist ids\n" ] } ], "source": [ "viral_chart_sql = '''\n", "select\n", " distinct t.first_artist\n", "from spotify_track t\n", "join spotify_album a on a.spyid = t.album_id\n", "join spotify_artist_listeners sal on t.first_artist = sal.spyid\n", "join country_settings cs on cs.isrc_code = upper(substring(isrc, 1, 2)) and cs.is_source_for_releases\n", "where not is_signed\n", " and sal.monthly_listeners_delta > 0\n", " and sal.monthly_listeners between 5000 and 3000000\n", " and t.spyid in (\n", " -- recent added to a viral:\n", " select track_id\n", " from spotify_track_on_playlist\n", " where first_seen > date_sub(now(), interval 3 day)\n", " )\n", "order by t.first_seen desc\n", "'''\n", "\n", "arts = mysql.query_single_column(viral_chart_sql)\n", "print(f\"Found {len(arts)} artist ids\")" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Found 182 artist ids\n" ] } ], "source": [ "reddit_chart_sql = '''\n", "select\n", " distinct t.first_artist\n", "from spotify_track t\n", "join spotify_album a on a.spyid = t.album_id\n", "join spotify_artist_listeners sal on t.first_artist = sal.spyid\n", "join spotify_track_on_playlist where playlist_id = \n", "\n", "where not is_signed\n", " and sal.monthly_listeners_delta > 0\n", " and sal.monthly_listeners between 5000 and 3000000\n", " and t.first_seen > date_sub(now(), interval 12 hours)\n", " \n", "order by t.first_seen desc\n", "'''\n", "\n", "arts = mysql.query_single_column(viral_chart_sql)\n", "print(f\"Found {len(arts)} artist ids\")" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Found 99 artists\n" ] } ], "source": [ "row_data = get_artist_sheet_csv(arts)" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "101" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "len(row_data)" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "sh = sheet.Sheet()\n", "spr = sh.client.open_by_url('https://docs.google.com/spreadsheets/d/1L3-w0Vi3tyDhnB1Kkje1bnTmvApMJU-A9EZeERFOmQk')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\n", "query_to_sheet.csv_to_sheet(row_data, spr, sheet_name='Viral Playlists')" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [], "source": [ "orig = json.loads('''{\n", " \"itemInfos\": {\n", " \"id\": \"6761790731683253510\",\n", " \"text\": \"Funfact: the song playing in the background during the cafe scene is sung by Robert Pattinson♥️ #twilight #bellaswan #cosplay #foryou\",\n", " \"createTime\": \"1574352091\",\n", " \"authorId\": \"6579403097516032006\",\n", " \"musicId\": \"6680416918303804166\",\n", " \"covers\": [\"https://p16.muscdn.com/obj/tos-maliva-p-0068/83df8dd17f144c0e9fc4911d83744734_1574352097\"],\n", " \"coversOrigin\": [\"https://p16.muscdn.com/obj/tos-maliva-p-0068/0a49f2b86495456890467147c1a62980_1574352096\"],\n", " \"coversDynamic\": [\"https://p16.muscdn.com/obj/tos-maliva-p-0068/9a4a64f885034284abfee5984d6d6be4_1574352097\"],\n", " \"video\": {\n", " \"urls\": [\"https://v16.muscdn.com/7f6f977f92ed6d058e79b1b48e7f755c/5dd7284b/video/tos/maliva/tos-maliva-v-0068/0fb654f81ca249e4a070e609416b601b/?a=1233&br=1316&cr=0&cs=0&dr=0&ds=3&er=&l=201911211813500101150770361838DD7A&lr=tiktok_m&qs=0&rc=ajdpNHd2eW9lcTMzNzczM0ApZ2c3ZWU7ZmQ0NzhlMzQ0OGdqcm8taGNeNl9fLS1fMTZzczU2MzVgYDNfNjBeLmM1YmM6Yw%3D%3D\"],\n", " \"videoMeta\": {\n", " \"width\": 720,\n", " \"height\": 1280,\n", " \"ratio\": 13,\n", " \"duration\": 13\n", " }\n", " },\n", " \"diggCount\": 4006,\n", " \"shareCount\": 40,\n", " \"commentCount\": 33\n", " },\n", " \"authorInfos\": {\n", " \"secUid\": \"MS4wLjABAAAAr9tbKLtmb65JFHigz5w3WynZjGOfT7n1Oi0yV_AqP34UUrCGPw70qd2_eGRO1wZb\",\n", " \"userId\": \"6579403097516032006\",\n", " \"uniqueId\": \"french_friar\",\n", " \"nickName\": \"FrenchFriar_cosplay\",\n", " \"signature\": \"sig\",\n", " \"covers\": [\"https://p16.muscdn.com/img/musically-maliva-obj/1648492927781893~c5_100x100.jpeg\"],\n", " \"coversMedium\": [\"https://p16.muscdn.com/img/musically-maliva-obj/1648492927781893~c5_720x720.jpeg\"],\n", " \"coversLarger\": [\"https://p16.muscdn.com/img/musically-maliva-obj/1648492927781893~c5_1080x1080.jpeg\"]\n", " },\n", " \"musicInfos\": {\n", " \"musicId\": \"6680416918303804166\",\n", " \"musicName\": \"original sound\",\n", " \"authorName\": \"allykat73\",\n", " \"original\": true,\n", " \"playUrl\": [\"https://p16.muscdn.com/obj/musically-maliva-obj/1630985412738053.mp3\"],\n", " \"covers\": [\"https://p16.muscdn.com/img/musically-maliva-obj/1630985412738069~c5_100x100.jpeg\"],\n", " \"coversMedium\": [\"https://p16.muscdn.com/img/musically-maliva-obj/1630985412738069~c5_200x200.jpeg\"],\n", " \"coversLarger\": [\"https://p16.muscdn.com/img/musically-maliva-obj/1630985412738069~c5_720x720.jpeg\"]\n", " }\n", "}''')" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [], "source": [ "import json" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": [ "from pandas import json_normalize" ] }, { "cell_type": "code", "execution_count": 46, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "itemInfos.id,6761790731683253510\n", "itemInfos.text,Funfact: the song playing in the background during the cafe \n", "itemInfos.createTime,1574352091\n", "itemInfos.authorId,6579403097516032006\n", "itemInfos.musicId,6680416918303804166\n", "itemInfos.covers,['https://p16.muscdn.com/obj/tos-maliva-p-0068/83df8dd17f144\n", "itemInfos.coversOrigin,['https://p16.muscdn.com/obj/tos-maliva-p-0068/0a49f2b864954\n", "itemInfos.coversDynamic,['https://p16.muscdn.com/obj/tos-maliva-p-0068/9a4a64f885034\n", "itemInfos.video.urls,['https://v16.muscdn.com/7f6f977f92ed6d058e79b1b48e7f755c/5d\n", "itemInfos.video.videoMeta.width,720\n", "itemInfos.video.videoMeta.height,1280\n", "itemInfos.video.videoMeta.ratio,13\n", "itemInfos.video.videoMeta.duration,13\n", "itemInfos.diggCount,4006\n", "itemInfos.shareCount,40\n", "itemInfos.commentCount,33\n", "authorInfos.secUid,MS4wLjABAAAAr9tbKLtmb65JFHigz5w3WynZjGOfT7n1Oi0yV_AqP34UUrCG\n", "authorInfos.userId,6579403097516032006\n", "authorInfos.uniqueId,french_friar\n", "authorInfos.nickName,FrenchFriar_cosplay\n", "authorInfos.signature,sig\n", "authorInfos.covers,['https://p16.muscdn.com/img/musically-maliva-obj/1648492927\n", "authorInfos.coversMedium,['https://p16.muscdn.com/img/musically-maliva-obj/1648492927\n", "authorInfos.coversLarger,['https://p16.muscdn.com/img/musically-maliva-obj/1648492927\n", "musicInfos.musicId,6680416918303804166\n", "musicInfos.musicName,original sound\n", "musicInfos.authorName,allykat73\n", "musicInfos.original,True\n", "musicInfos.playUrl,['https://p16.muscdn.com/obj/musically-maliva-obj/1630985412\n", "musicInfos.covers,['https://p16.muscdn.com/img/musically-maliva-obj/1630985412\n", "musicInfos.coversMedium,['https://p16.muscdn.com/img/musically-maliva-obj/1630985412\n", "musicInfos.coversLarger,['https://p16.muscdn.com/img/musically-maliva-obj/1630985412\n" ] } ], "source": [ "d = json_normalize(orig).to_dict('records')[0]\n", "for k, v in d.items():\n", " print(k + ',' + str(v)[0:60])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.7.1" } }, "nbformat": 4, "nbformat_minor": 2 }