{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "curl 'https://generic.wg.spotify.com/s4x-insights-api/v1/artist/3O5HD95HTEPgoPFOjAb7yV/audience/timeline/listeners/3oCVutdFfZrHovIQia7n8B?time-filter=since2015&aggregation-level=recording' -H 'authority: generic.wg.spotify.com' -H 'authorization: Bearer BQDpmnTI3QHxeyl_3NkEpbKqhE1RmIaAM_-GSdVBDv7RS5KBbPzdTp9S5OQnLPfnuxY1uHMr31jUOZ_SRNptUONlJ-jsp4ktSPKJfb8U_GMxOg5Zzn5vK0xwcDPXO8OV5k8lnbGT0Zxg0gd37mF519bW8b6C3iavmbebUDI' -H 'content-type: application/json' -H 'accept: application/json' -H 'x-cloud-trace-context: 1110f78a01c9713b0000000000000000/4751694939127157570;o=1' -H 'sec-fetch-dest: empty' -H 'user-agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36' -H 'spotify-app-version: 1.0.0.f935be2' -H 'app-platform: Browser' -H 'origin: https://artists.spotify.com' -H 'sec-fetch-site: same-site' -H 'sec-fetch-mode: cors' -H 'referer: https://artists.spotify.com/c/artist/3O5HD95HTEPgoPFOjAb7yV/audience?time-filter=since2015' -H 'accept-language: en-GB,en-US;q=0.9,en;q=0.8' --compressed" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import sys\n", "sys.path.insert(0, '/Users/joel/src/thundr/tracker')" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "from importlib import reload" ] }, { "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": [ "import requests\n", "import pandas as pd\n", "import numpy\n", "import matplotlib\n", "from io import StringIO\n", "from datetime import datetime, date, timedelta\n", "\n", "import random\n", "import traceback\n", "\n", "from tracker.unicorn import mysql, models, artist_ingestion, uniconfig, daily_streams, spartus_daily\n", "from tracker.utils import chunks\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "reload(daily_streams)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'\\n sp_dc=AQDKn9i20otvtv0eKuwUTvShedeuH_4bCWYQVCY--vKhEbj_w0JhDIurSrYuziSj212zf51Ij0oxGGd3XtbGRpp6vSJR-mSu1pCeVpsbRJI\\n sp_key=928f9483-e122-4610-92c5-22e0460b5f21\\n'" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "'''\n", " sp_dc=AQDKn9i20otvtv0eKuwUTvShedeuH_4bCWYQVCY--vKhEbj_w0JhDIurSrYuziSj212zf51Ij0oxGGd3XtbGRpp6vSJR-mSu1pCeVpsbRJI\n", " sp_key=928f9483-e122-4610-92c5-22e0460b5f21\n", "'''" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "sfa = spartus_daily.SpotifyForArtists(\n", " artist_id='3GxKJzJK4LpsYGXQrw77wz',\n", " track_id='6k9s52t7GyxRA4mNDERlwH',\n", " sp_dc='AQDKn9i20otvtv0eKuwUTvShedeuH_4bCWYQVCY--vKhEbj_w0JhDIurSrYuziSj212zf51Ij0oxGGd3XtbGRpp6vSJR-mSu1pCeVpsbRJI',\n", " sp_key='928f9483-e122-4610-92c5-22e0460b5f21',\n", ")" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'authority': 'generic.wg.spotify.com',\n", " 'authorization': 'Bearer BQC0XwSh8QIt1bChRkqrqjYfNk87YPXd2s2RTlviquRJHXeW-xSYkhAtl0D8F0izZ3PeB37FwxFsqAYSYFpuqtr47HcZ_Vv7nd-5YDY85HZYTGDfd-ajPn0Lau2PCZy6c7gE7Qbv028iAff-UA0s1EVzdE1pzL3X',\n", " 'content-type': 'application/json',\n", " 'accept': 'application/json',\n", " 'sec-fetch-dest': 'empty',\n", " 'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.116 Safari/537.36',\n", " 'spotify-app-version': '1.0.0.3229887',\n", " 'app-platform': 'Browser',\n", " 'origin': 'https://artists.spotify.com',\n", " 'sec-fetch-site': 'same-site',\n", " 'sec-fetch-mode': 'cors',\n", " 'referer': 'https://artists.spotify.com/c/artist/3GxKJzJK4LpsYGXQrw77wz/song/6k9s52t7GyxRA4mNDERlwH/stats?segment-filter=streams&time-filter=7day',\n", " 'accept-language': 'en-GB,en-US;q=0.9,en;q=0.8'}" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sfa.refresh_request_headers()" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "from tracker.unicorn import artist_ingestion" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "billieeilish,Billie Eilish,spotify:artist:6qqNVTkY8uBg9cP3Jd7DAH\n", "rinasonline,Rina Sawayama,spotify:artist:2KEqzdPS7M5YwGmiuPTdr5\n", "edgar_the_breathtaker,King Krule spotify:artist:4wyNyxs74Ux8UIDopNjIai\n", "celeste,Celeste,spotify:artist:49HlOY4gkHqsYG9GCuhkcc\n", "auroramusic,Aurora,spotify:artist:1WgXqy2Dd70QQOU7Ay074N" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "peeps = [x.split(' spotify:artist:') for x in \"\"\"Billie Eilish spotify:artist:6qqNVTkY8uBg9cP3Jd7DAH\n", "Rina Sawayama spotify:artist:2KEqzdPS7M5YwGmiuPTdr5\n", "King Krule spotify:artist:4wyNyxs74Ux8UIDopNjIai\n", "Celeste spotify:artist:49HlOY4gkHqsYG9GCuhkcc\n", "Aurora spotify:artist:1WgXqy2Dd70QQOU7Ay074N\"\"\".splitlines()]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "peeps" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "daily_streams.fetch_and_store('spotify:artist:0qrHiWumZtqyV65tKmFWFR', sfa)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "fmt = \",\".join([\"%s\"] * len(peeps))\n", "rows = mysql.query(\n", " f'select spyid, tsdata from spotify_daily_streams where spyid in ({fmt})', \n", " [r[1] for r in peeps]\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "expanded = []\n", "for spyid, tsdata in rows:\n", " for point in tsdata.splitlines():\n", " if 'num' not in point:\n", " t, v = point.split(',')\n", " expanded.append([spyid, int(t), int(v)])\n", " #expanded.extend([[spyid, int(t), int(v)] for r in tsdata.splitlines() for t,v in r.split(',') if 'num' not in r])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df1 = pd.DataFrame(expanded, columns=['spyid', 'daynum', 'streams'])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfpv = df1.pivot(index='daynum', columns='spyid', values='streams')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfpv.set_index(dfpv.index.to_series().apply(lambda x: date(2015,1,1) + timedelta(days=x)), inplace=True)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfpv = dfpv.rename(columns={k:v for v,k in peeps})" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfpv.to_csv('/Users/joel/Desktop/all_streams.tsv')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "spyid, tsdata = rows[0]\n", "print(spyid, tsdata.splitlines())\n", "[[spyid, int(t), int(v)] for r in tsdata.splitlines() for t,v in r.split(',') if 'num' not in r]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "t2 = daily_streams.fetch_timeline('spotify:artist:5f7DcyMqNPFYloZM82D61t', sfa)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": false }, "outputs": [], "source": [ "df2 = pd.DataFrame(t2)\n", "df2['date'] = df2.date.astype(numpy.datetime64)\n", "df2['num'] = df2.num.astype(int)\n", "df2 = df2.set_index('date')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# df2.sort_index().iloc[-365:].to_csv('/Users/joel/Desktop/S1MB.csv')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df2.sort_index().iloc[-365:].plot(title='DJ TikTok')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "assert set([models.extract_id(x) for x in [\n", " 'spotify:artist:39ZQx0618UYVBgGTDOJ2ds',\n", " 'https://open.spotify.com/artist/39ZQx0618UYVBgGTDOJ2ds?si=1F4gws3SThGRV4Yt-4xp_g',\n", " 'https://open.spotify.com/artist/39ZQx0618UYVBgGTDOJ2ds',\n", " '39ZQx0618UYVBgGTDOJ2ds',\n", " 'spotify:album:39ZQx0618UYVBgGTDOJ2ds',\n", " 'spotify:track:39ZQx0618UYVBgGTDOJ2ds',\n", "]]) == {'39ZQx0618UYVBgGTDOJ2ds'}" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from tracker import spotify, db" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "db.Session.remove()\n", "db.setup_session()\n", "db.Session.get_bind()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "spy = spotify._authenticate()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "a1 = spy.artists(['39ZQx0618UYVBgGTDOJ2ds'])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from collections import namedtuple\n", "import typing" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "reload(models)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def fetch_artists(artist_ids) -> typing.List[models.Artist]:\n", " \n", " spy = spotify._authenticate()\n", " utcnow = datetime.utcnow()\n", " all_results = []\n", " for chunk in chunks(artist_ids, 20):\n", " res = spy.artists([models.extract_id(i) for i in chunk])\n", " this_result = [\n", " models.Artist(\n", " spyid=a['id'],\n", " name=a['name'],\n", " first_seen=utcnow,\n", " last_seen=utcnow,\n", " popularity=a['popularity'],\n", " followers=(a['followers'] or {}).get('total'),\n", " )\n", " for a in res['artists']\n", " ]\n", " all_results.extend(this_result)\n", " return all_results" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "parsed1 = fetch_artists([\n", " 'spotify:artist:7fMr2Jp0r6RxfJhp46kW9C',\n", " 'https://open.spotify.com/artist/0TWkbr1C1Wv88FwmZc8Il3?si=YoBHFzBDSYe7rJiYYuLyZQ'\n", "])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "parsed1" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "mysql.std_upsert(parsed1)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def medians(df, window):\n", " if (df.size < int(window*0.75)):\n", " # not enough data\n", " return [None, None, None, None]\n", " \n", " ma, mb = df.iloc[-window:].resample(f'{window // 2}D').median().num.to_numpy().tolist()\n", " return [ma, mb, mb - ma, (mb - ma) / ma if ma > 0 else None]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_start(df):\n", " first_non_zero = (df.sort_index().num > 0).idxmax()\n", " return df.sort_index().loc[first_non_zero:].copy()" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "def to_csv_str(df):\n", " sio = StringIO()\n", " packed = df.copy()\n", " packed.index = (packed.index.to_series().dt.date - date(2015,1,1)).dt.days\n", " packed.to_csv(sio)\n", " return sio.getvalue()\n", "\n", "def request_data(artist_id, spartus_daily):\n", " url = make_url(artist_id, spartus_daily)\n", " r1 = requests.get(url, headers=spartus_daily.headers)\n", " if r1.status_code != 200:\n", " raise RuntimeError(f\"Non 200 request {url}, {r1}, {r1.reason}\")\n", " \n", " df1 = pd.DataFrame(data=r1.json()['timelinePoint'])\n", "\n", " df1['num'] = df1.num.astype(int)\n", " df1['date'] = df1.date.astype(numpy.datetime64)\n", " \n", " return r1, df1.set_index('date')\n", "\n", "def store_in_db(spyid, df):\n", " smaller_df = get_start(df)\n", " args = [spyid, smaller_df.index.min().date(), smaller_df.index.max().date(),]\n", " args.extend([int(smaller_df.num.iloc[i]) for i in [-1, -2, -8, -15, -29]])\n", " args.extend([int(smaller_df.num.max()), smaller_df.num.idxmax().date()])\n", " args.extend(medians(smaller_df, 14))\n", " args.extend(medians(smaller_df, 28))\n", " args.extend(medians(smaller_df, 56))\n", " args.extend(medians(smaller_df, 112))\n", " args.extend([to_csv_str(smaller_df)]) \n", "\n", " pcts = \",\".join(['%s']*len(args))\n", " \n", " sql = f'''replace into spotify_daily_streams (\n", " spyid, as_of, first_date, \n", " s0, s1, s7, s14, s28, \n", " smax, smax_date, \n", " m14a, m14b, d14, p14, \n", " m28a, m28b, d28, p28, \n", " m56a, m56b, d56, p56, \n", " m112a, m112b, d112, p112, \n", " tsdata\n", " ) values ({pcts})'''\n", " \n", " with get_cursor(True) as cur:\n", " cur.execute(sql, args)\n", " \n", "\n", "def make_df_from_row(spyid, tsdata):\n", " nf = pd.read_csv(StringIO(tsdata))\n", " nf['spyid'] = spyid\n", " return nf\n", " \n", "\n", "def load_streams(spyids:list):\n", " with mysql.get_cursor(False) as cur:\n", " pcts = \",\".join(['%s'] * len(spyids))\n", " cur.execute(f'select spyid, tsdata from spotify_daily_streams where spyid in ({pcts})', spyids)\n", " res = cur.fetchall()\n", " dfall = pd.concat([make_df_from_row(spyid, tsdata) for spyid, tsdata in res])\n", " dfall['date'] = dfall.date.apply(lambda x: date(2015,1,1) + timedelta(days=x)).astype(numpy.datetime64)\n", " return dfall.pivot(index='date', columns='spyid', values='num')\n", "\n", "def fetch_and_store(artist_id, spartus_daily):\n", " r1, df1 = request_data(artist_id, spartus_daily=spartus_daily)\n", " store_in_db(artist_id, df1)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "all_artists_fr = pd.concat([\n", " pd.read_csv('/Users/joel/Desktop/all_40_60.tsv', sep='\\t').set_index('artist_spyid'),\n", " pd.read_csv('/Users/joel/Desktop/all_61_70.tsv', sep='\\t').set_index('artist_spyid'),\n", "])" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
spyid0qrHiWumZtqyV65tKmFWFR
date
2018-03-071
2018-03-080
2018-03-090
2018-03-100
2018-03-110
......
2020-05-2718222
2020-05-2818213
2020-05-2917781
2020-05-3017135
2020-05-3121595
\n", "

817 rows × 1 columns

\n", "
" ], "text/plain": [ "spyid 0qrHiWumZtqyV65tKmFWFR\n", "date \n", "2018-03-07 1\n", "2018-03-08 0\n", "2018-03-09 0\n", "2018-03-10 0\n", "2018-03-11 0\n", "... ...\n", "2020-05-27 18222\n", "2020-05-28 18213\n", "2020-05-29 17781\n", "2020-05-30 17135\n", "2020-05-31 21595\n", "\n", "[817 rows x 1 columns]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "load_streams(['0qrHiWumZtqyV65tKmFWFR'])" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "df1 = _" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYMAAAEiCAYAAAAbJL5ZAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO3deXxU1f3/8dcnCyQIymK0ClhQsSwGoyCISBtFBLWKC0XRCvJ1aS3W4vcrrW2t+rOtpS1Wq7Vatbj0q6LVVq2CfBFFoVQBlc1AkQqV4AJlEZCEJJPP74+5M0xilpmsM5n38/GYR+6ce+65505u5pNzzr3nmrsjIiLpLaO1KyAiIq1PwUBERBQMREREwUBERFAwEBERFAxERAQFA5FGM7ONZnZ6Het/ZGYPxVHOI2b2s6atnUh8FAykTar+BW1mF5vZDjP7WkvXxd1vd/crm7JMM1tgZqVmtsfM/mNmfzGzw5pyH5JeFAykzTOzScC9wNnu/noL7zurGYu/1t07AscAnYE7m3Ff0sYpGEibZmbfAu4ARrv74pj0k8xssZntNLMVZlYYs26Bmf3UzP5uZrvN7P/M7OCY9ZeZ2b/NbJuZ/bja/m41s2fM7H/NbBdweZD2vzF5TonZ9yYzu7yGencys9fM7G4zs7qO0d23A88Cxyb8AYkEFAykLbsGuA0Y6e7LIolm1h14CfgZ0BW4AXjWzPJitr0EmAwcArQL8mBm/YH7gMuAw4FuQI9q+x0LPEP4v/XHY1eY2ZeBOcA9QB5QACyvlqcbMB/4u7tf5/XMGRMEqguBd+vKJ1IXBQNpy0YBbwKrqqV/E5jt7rPdvdLd5wHLgLNi8jzs7uvcvQR4mvCXNsA44EV3f8Pd9wE/ASqrlf8Pd38uKLuk2rpLgFfc/Ul3L3f3be4eGwwOB14H/uzuN9VzfHeb2U5gBfAx8N/15BeplYKBtGXXEO5Pf6haV8uXgW8E3TQ7gy/UU4DYAdhPYpb3Ah2D5cOBTZEV7v45sK3afjdRu57Av+pYfzaQC9xfR56I69y9s7t3d/dL3X1rHNuI1EjBQNqyT4GRwAjg9zHpm4A/BV+kkdcB7j49jjI/JvyFDoCZdSDcVRSrrm6dTcBRdax/EHgZmG1mB8RRH5EmoWAgbZq7f0Q4IIwxs8jVNv8LnGNmo80s08xyzKzQzKr3/dfkGeDrwSBwO8JjEon8HT0OnG5m480sy8y6mVlBtTzXAv8E/mZmuQmULdJgCgbS5rn7h8BpwDgz+4W7byI8yPsjYCvh/9anEcffg7u/B0wBniDcStgBFCdYl7OA/wG2Ex48Pq5aHgeuDsp93sxy4i1fpKFMD7cRERG1DERERMFAREQUDEREBAUDERFBwUBERIDmnFGxWR188MHeq1ev1q6GiEjKOPjgg5k7d+5cdx9TfV3KBoNevXqxbNmy+jOKiEhU7Ay8sdRNJCIiCgYiIqJgICIipPCYgUhzKC8vp7i4mNLS0tauikij5OTk0KNHD7Kzs+PKr2AgEqO4uJhOnTrRq1cv6nnapEjScne2bdtGcXExvXv3jmsbdROJxCgtLaVbt24KBJLSzIxu3bol1MJVy0CkGgUCaQuqn8el5SF27C2rNb9aBiLS6m6++WZeeeWVL6QvWLCAr3/9661Qo7bn3Q93MuwXr9a6Xi0DEWl1t912W2tXoc2rrOfZNWoZiEijfP7555x99tkcd9xxHHvssTz11FP06tWL73//++Tn5zNkyBDWr1/P7t276d27N+Xl5QDs2rUr+v7yyy/nmWeeAeDll1+mb9++nHDCCfzlL39pzUNrU0KVCgYi0oxefvllDj/8cFasWMHq1asZMyY87c1BBx3EqlWruPbaa5k6dSqdOnWisLCQl156CYBZs2ZxwQUXVLn0sbS0lKuuuoq//e1vvP3223zyySetckxtkVoGItKs8vPzmTdvHj/4wQ9YuHAhBx10EAATJkyI/vzHP/4BwJVXXsnDDz8MwMMPP8zkyZOrlLV27Vp69+5Nnz59MDO++c1vtuCRtG31BQONGYhIoxxzzDG88847zJ49m5tuuomRI0cCVa9miSwPHz6cjRs3smDBAkKhEMcee2yr1DkdVVbWvV4tAxFplI8++ogOHTrwzW9+k2nTpvHOO+8A8NRTT0V/Dhs2LJp/4sSJXHLJJV9oFQD07duXjRs38q9//QuAJ598sgWOID2E1DIQkea0atUqpk2bRkZGBtnZ2dx3332MGzeOHTt2MHDgQNq3b1/lS/3SSy/lpptuinYjxcrJyeGBBx7g7LPPpkOHDowYMYLdu3e35OG0Wa5gICLNafTo0YwePfoL6dOmTeOXv/zlF9IXLVrEuHHj6Ny5czTtkUceiS6PGTOGtWvXNktd01monm4iBQMRaTHf/e53mTNnDrNnz27tqqQddROJSIvbuHFjjen33HNPy1ZEourrJtIAsohIGmj0TWdm1tPMXjOzIjN7z8y+F6R3NbN5ZvZ+8LNLkG5mdreZrTezlWZ2QkxZk4L875vZpJj0QWa2KtjmbtNMYSIiTaqeWBBXy6AC+B937w+cBEwxs/7AjcB8d+8DzA/eA5wJ9AleVwP3QTh4ALcAQ4EhwC2RABLkuSpmuzHxHZ6IiMSjsrEtA3f/2N3fCZZ3A2uA7sBY4NEg26PAecHyWOAxD3sT6GxmhwGjgXnuvt3ddwDzgDHBugPd/U0Pd2o9FlOWiIg0gSadjsLMegHHA28Bh7r7x8GqT4BDg+XuwKaYzYqDtLrSi2tIr2n/V5vZMjNbtnXr1kSqLpIyXn75Zb7yla9w9NFHM3369IS2rWnK59hJ4K688kqKior47W9/y9SpU6N5vvWtb3H66adH399zzz1cd911jTiKsL/+9a8UFBRUeWVkZDBnzhw2btzIE0880eCyTz31VObOnVsl7a677uKaa64B4MMPP+SMM86gX79+9O/fPzqoffnll9O7d+9ofZYvXw6EP7vFixdHy7r11luZMWMGEJ4zadSoUdx6661x1++RRx4hLy8vup+JEyeyYsUKCgoKonmefPJJcnNzo5P3rVq1ioEDBwJQWFjIV77ylej2kd9hZmYmBQUFHHvssZxzzjns3LkzrvrUdzVR3MHAzDoCzwJT3X1X7LrgP/p6eqQaz90fcPfB7j44Ly+vuXcn0uJCoRBTpkxhzpw5FBUV8eSTT1JUVBTXthUVFfXmeeihh+jfvz/Dhw+v8sW3YsUKPvvsM0KhEACLFy/m5JNPbthBxDj//PNZvnx59PWd73yHESNGMHr06EYHgwkTJjBr1qwqabNmzYrezDZx4kSmTZvGmjVrWLJkCYccckg0369//etonSJfztWDQURZWRkXXnghgwYNSigYAFx00UXR/Tz22GPk5+fz4YcfRm+kW7x4Mf369ePdd9+Nvo/93B9//PHo9uPGjQMgNzeX5cuXs3r1arp27cq9994bV10a3U0EYGbZhAPB4+4emVP206CLh+DnliB9M9AzZvMeQVpd6T1qSBdJO0uWLOHoo4/myCOPpF27dlx88cU8//zzVaZ1vu6666L//d96661cdtllDB8+nMsuu6ze8gsLC1m2bBkFBQWsW7eOkpISPvvsM3JzcykoKGDVqlVA+EspMo9Q7PxBM2bMiH4hFhYWcv311zN48GD69evH0qVLueCCC+jTpw833XTTF/a9bt06brvtNv70pz+RkZHBjTfeyMKFCykoKODOO++kpKSEiy++mH79+nH++eczdOhQli1bxsyZM6u0Yh588EGuv/56xo0bx0svvURZWfjpXRs3buSjjz5ixIgRFBUVUVFRwahRowDo2LEjHTp0qPVz2bhxI/fffz933nknBQUFLFy4EAgH2Isuuog+ffpEW2kbN26kb9++XH755RxzzDFceumlvPLKKwwfPpw+ffqwZMmSWveTkZHB4MGDeeuttwB4++23mTJlSjQIRT73eA0bNozNm+P7uqxvALne+wyCK3v+CKxx99/ErHoBmARMD34+H5N+rZnNIjxY/Jm7f2xmc4HbYwaNzwB+6O7bzWyXmZ1EuPtpIqCLkaXV/b+/vUfRR7vqz5iA/ocfyC3nDKh1/ebNm+nZc///TD169OD111/n97//Pa+++ipHH300F110UZVtioqKWLRoEbm5uSxYsCD6BRvx4YcffqHrKCsri+OPP56lS5dSUlLC0KFD6dOnD4sXLyYvLw93p2fPnrXeLxDRrl07li1bxm9/+1vGjh3L22+/TdeuXTnqqKO4/vrr6datGwDl5eVccskl3HHHHRxxxBEATJ8+nRkzZvDiiy8C8Jvf/IYOHTqwZs0aVq5cyQknhC9EHD9+PD//+c/59a9/TXZ2Ng8//DB/+MMf6Nq1K0OGDGHOnDmMHTuWWbNmMX78eMyMdevW0blzZy644AI2bNjA6aefzvTp08nMzATgxz/+MbfddhsjR45k+vTp9OrVi29/+9t07NiRG264AYD58+fzq1/9ilGjRnHXXXdVOe7169fz5z//mZkzZ3LiiSfyxBNPsGjRIl544QVuv/12nnvuOSA8L9OiRYsA+N73vsfkyZOjrbJhw4aRkZFBYWEhP/zhD5k6dSqLFy/m5ptvju7n0ksvJTc3N1qfyOcJ4Vbk/PnzueKKK+r8HUXzN0HLYDhwGXCamS0PXmcRDgKjzOx94PTgPcBs4ANgPfAg8B0Ad98O/BRYGrxuC9II8jwUbPMvYE5cRyeSBj755JM6p3U+99xzo18YACNGjKjSNXPuuefWWO7JJ5/M4sWLo19Mw4YNi76Pt4soUnZ+fj4DBgzgsMMOo3379hx55JFs2rR/iPAnP/kJAwYM+EIgi/XGG29Ej23gwIHRvvOOHTty2mmn8eKLL7J27VrKy8vJz88HqnYVxXYRVVRUsHDhQmbMmMHSpUv54IMPolNe/OIXv2Dt2rUsXbqU7du31zhlRsQpp5zC4sWLWbduXZX03r17k5+fT0ZGBgMGDGDkyJGYGfn5+VUCaGw3UWRivsjnvmTJEk488USOOuoo1q9fz9atW9mzZw9HHXVUdPvYbqJIICgpKaGgoIAvfelLfPrpp9HWT30aPYW1uy8Carvuf2QN+R2YUktZM4GZNaQvAzSXrSSVuv6Dby7du3ev8iVaXFzM8OHDmTOn9v+PDjjggAbta/jw4dx///2UlpYyZcoU8vLyKCoqIi8vLxoMsrKyqIyZ+7i0tLRKGe3btwfC3R+R5cj7yBjGggULePbZZ6OzmTbElVdeye23307fvn2rzHY6duxYrr/+et555x327t3LoEGDgHCLqqCggCOPPBKA8847jzfffJMrrriCww47LFr3yZMnRweJa/LVr36VSZMmceaZZ7Jo0aIq28Yea+znUN/YzUknncTSpUv5+9//Hp3NtUePHsyaNavK7K61iYwZ7N27l9GjR3PvvffGNdivh9uIpJATTzyR999/nw0bNlBWVsasWbM499xzm2Va52HDhvHmm2+ydetWDjnkEMyMvLw8nn/++Wi/9aGHHsqWLVvYtm0b+/bti3bpxGvHjh1MnjyZxx57jE6dOlVZ16lTpyozkn71q1+NDiivXr2alStXRtcNHTqUTZs28cQTT1SZ7bRjx46ceuqp/Nd//VeV9BNPPJGdO3cSuerw1VdfpX///gB8/HH4Ikh357nnnouOiVSvT8SFF17IDTfcwJgxY+K+cqcunTp1omfPnjz88MPRL/9hw4Zx1113JTRe0KFDB+6++27uuOOOuC4eaIqbzkSkhWRlZfG73/2O0aNH069fP8aPH8+AAQOi0zqfcMIJVa6KaYwuXbqQl5fHgAH7W0DDhg1jy5YtHHfccQBkZ2dz8803M2TIEEaNGkXfvn0T2sf999/Pli1buOaaa6pcXvrUU08xcOBAMjMzOe6447jzzju55ppr2LNnD/369ePmm2+O/pcfMX78eIYPH06XLl2qpE+YMIEVK1ZUCQaZmZnMmDGDkSNHkp+fj7tz1VVXAeF++Pz8fPLz8/nPf/4THew+55xzopfCRgaQI6655hrOP/98zj333C+0jhpi+PDh7Nu3Lzo+NGzYMD744IOEr+A6/vjjGThwYFz/INQ3ZoC7p+Rr0KBBLtLUioqKWrsK9Xrttdf87LPPbu1qNLuvfe1rvnTp0uj7s88+21955ZVWrFHqiT2f735lnX/5By86sMxr+E5Vy0BEktrOnTs55phjyM3NjT5SUxLX6EtLRSS5FBYWUlhY2NrVaHYLFiyILle/mkcS12R3IIuISOpydzLqmA9awUCkGq/nPyiRVFD9PA5VOhl1PB1AwUAkRk5ODtu2bVNAkJTm7mzbto2cnJxoWqVDRh1NA40ZiMTo0aMHxcXFaFZcSXU5OTn06LF/2rfKerqJFAxEYmRnZ9O7d+/WroZIk9qyu5RPd5WSWUc3kYKBiEgb9/W7F7Fl9z5yszNrzaMxAxGRNqy0PMSW3fsAKCkP1ZpPwUBEpA0r3rE3rnwKBiIibVjxjpK48ikYiIi0YaXllfVnQsFARKRNq3e20oCCgYhIG1bfnEQRCgYiIm3Yll3xPX9BwUBEpI36cNtefvbSmrjyKhiIiLQx9762nqKPdrFh2+dxb6NgICLShlRWOr+e+0/O+d0i9sXcZFbHTBSApqMQEWlTyivDl5KGKp19FfsvK31j2qkcmJtN51/WvJ1aBiIibUhFaP/VQ7HBoFNOFgflZte6nYKBiEgbUh7aHwD2VezvJsqsa/5qFAxERNqU+xb8K7r82tot0eWsjLq/7hUMRETakD+88UF0+ZU1+4OBWgYiImli2559ta5TMBARSRMb/lP7fQX1xAIFAxGRtuLTXbW3DKyeGw0UDERE2ogtu+Obh6gmCgYiIm1E5PGWDaFgICLSRmxVMBARkT2lFQ3eVsFARKSN2BszMV2iFAxERNqIkjK1DERE0t7esmZsGZjZTDPbYmarY9JuNbPNZrY8eJ0Vs+6HZrbezP5pZqNj0scEaevN7MaY9N5m9laQ/pSZtWvw0YiIpLFmDQbAI8CYGtLvdPeC4DUbwMz6AxcDA4Jtfm9mmWaWCdwLnAn0ByYEeQF+GZR1NLADuKLBRyMiksb2Nmc3kbu/AWyPs7yxwCx33+fuG4D1wJDgtd7dP3D3MmAWMNbCt8SdBjwTbP8ocF6CxyAiIjR/y6A215rZyqAbqUuQ1h3YFJOnOEirLb0bsNPdK6qli4hIHP64aANLN4b/Xy8pC3Firy71bFGzhgaD+4CjgALgY+COBpaTEDO72syWmdmyrVu3tsQuRUSS2k9fLOIb9/+DmYs2UFHpfO2YPGZdfRIAfb/UKe5yGhQM3P1Tdw+5eyXwIOFuIIDNQM+YrD2CtNrStwGdzSyrWnpt+33A3Qe7++C8vLyGVF1EpE267cUiAA7plEO/ww7kyLwD+MUF+XFvn1V/li8ys8Pc/ePg7flA5EqjF4AnzOw3wOFAH2AJYEAfM+tN+Mv+YuASd3czew0YR3gcYRLwfEPqJCIiMKD7gRyUm82r/1MIwJs/HFnlUZi1qTcYmNmTQCFwsJkVA7cAhWZWADiwEfgWgLu/Z2ZPA0VABTDF3UNBOdcCc4FMYKa7vxfs4gfALDP7GfAu8Mf4DllEJL25e5X3B3dsxzGHVu0a+tJBOXGVVW8wcPcJNSTX+oXt7j8Hfl5D+mxgdg3pH7C/m0lEROIUqqwaDJ6bMpzszIYNBesOZBGRFFUREwxOOKIzPbp0aHBZCgYiIimqLGYsIKOeJ5nVR8FARCRFVYT2twwUDERE0lTsVUKNjAUKBiIiqapc3UQiIlIe203UyG9zBQMRkRRVoZaBiIjEtgxMwUBEJD1VHTNoXFkKBiIiKaqiMuZqokaWpWAgIpKiyirUTSQikvZiWwbVJ61LlIKBiEiKir0DuXGhQMFARCRlxc5N1MiGgYKBiEiqiuehNfFSMBARSVF794WarCwFAxGRFPV5WUV0WWMGIiJpam/Z/paBriYSEUlTe/ZV1J8pTgoGIiIpaq+CgYiIfF6lm6hxZSkYiIikqL1VBpA1ZiAikpb26NJSERGJHTNQN5GISJrSmIGIiLC3rILszPDU1RozEBFJU5/vq6BTTnaTlKVgICKSoj7fF6JTThagbiIRkbQUqnRKymOCQSPLUzAQEUlBJeXhweNO7YNuIrUMRETSz+fBZaWRlkFjKRiIiKSg/cEg3DLQ1UQiImkoMn21BpBFRNJYpGVwoLqJRETSV+QpZ7rPQEQkjX0eTFJ3QHtdWioikrYi01cf0D4TaIHHXprZTDPbYmarY9K6mtk8M3s/+NklSDczu9vM1pvZSjM7IWabSUH+981sUkz6IDNbFWxzt5lZo45IRCQNRFoGHdu33JjBI8CYamk3AvPdvQ8wP3gPcCbQJ3hdDdwH4eAB3AIMBYYAt0QCSJDnqpjtqu9LRESqiQwgt1g3kbu/AWyvljwWeDRYfhQ4Lyb9MQ97E+hsZocBo4F57r7d3XcA84AxwboD3f1ND7dxHospS0REavF5WYh2mRlkZ4a/xlvr0tJD3f3jYPkT4NBguTuwKSZfcZBWV3pxDekiIlKHkrIKcttlEulYb/UB5OA/+sbWIy5mdrWZLTOzZVu3bm2JXYqIJKXS8kpysjNoqkHWhgaDT4MuHoKfW4L0zUDPmHw9grS60nvUkF4jd3/A3Qe7++C8vLwGVl1EJPWVVoTIyc7cn9DcVxPV4gUgckXQJOD5mPSJwVVFJwGfBd1Jc4EzzKxLMHB8BjA3WLfLzE4KriKaGFOWiIjUorQ8RE5WJpELMBvbPVPvNUlm9iRQCBxsZsWErwqaDjxtZlcA/wbGB9lnA2cB64G9wGQAd99uZj8Flgb5bnP3yKD0dwhfsZQLzAleIiJSh+rdRI0dQK43GLj7hFpWjawhrwNTailnJjCzhvRlwLH11UNERPYrLQ/RPnv/AHJj6Q5kEZEUVFpRWWXMQFNYi4ikodKyEDlZGfQ5pBNf7taBH53Vr1HlNc19zCIi0qIiVxPltsvk9WmnNro8tQxERFJQaXmInOym+wpXMBARSUHhq4ky688YJwUDEZEUFG4ZKBiIiKStfRUh9lVUNtkjL0HBQEQk5ezcWw5A5w7tmqxMBQMRkRSz/fMyALooGIiIpK8deyPBILvJylQwEBFJMZFuoi4HqGUgIpK2dpWEg8GBuWoZiIikrZLyEAAddGmpiEj6igSD3HYKBiIiaau0vBKA9lmajkJEJG1F5iWypnqYAQoGIiIpp6QsRG4TjheAgoGISMpp6nmJQMFARCTllJSrZSAikvbUMhARkeBZBk379a1gICKSYkrKQ016jwEoGIiIpJx9FSHaZykYiIiktbKKStplqptIRCStlVVU0q4J7z4GBQMRkZRTHnKy1TIQEUlv+9QyEBGRsopQk05SBwoGIiIpJ9xN1HST1IGCgYhISikPVVJSHlI3kYhIOrv0obcAaJep+wxERNLWkg3bAcjOUjeRiEjay85QN5GISNorr6xs0vIUDEREUlBZhYKBiEja26dgICIi+8qTKBiY2UYzW2Vmy81sWZDW1czmmdn7wc8uQbqZ2d1mtt7MVprZCTHlTAryv29mkxp3SCIibV+le5OW1xQtg1PdvcDdBwfvbwTmu3sfYH7wHuBMoE/wuhq4D8LBA7gFGAoMAW6JBBAREalq2JHdAJh6ep8mLbc5uonGAo8Gy48C58WkP+ZhbwKdzewwYDQwz923u/sOYB4wphnqJSKS8rIyjeOP6EznDu2atNzGBgMH/s/M3jazq4O0Q93942D5E+DQYLk7sClm2+IgrbZ0ERGpJlTpZFrT3nAGkNXI7U9x981mdggwz8zWxq50dzezJuvYCgLO1QBHHHFEUxUrIpIyQpVORkbTB4NGtQzcfXPwcwvwV8J9/p8G3T8EP7cE2TcDPWM27xGk1ZZe0/4ecPfB7j44Ly+vMVUXEUlJle5kJVMwMLMDzKxTZBk4A1gNvABErgiaBDwfLL8ATAyuKjoJ+CzoTpoLnGFmXYKB4zOCNBERqaai0slshmDQmG6iQ4G/WrjvKgt4wt1fNrOlwNNmdgXwb2B8kH82cBawHtgLTAZw9+1m9lNgaZDvNnff3oh6iYi0WZWVTkYyjRm4+wfAcTWkbwNG1pDuwJRaypoJzGxoXURE0kXIm6dloDuQRURSSKiSZmkZKBiIiKSQysokG0AWEZGWV1FZqW4iEZF0V+kk330GIiLSssJ3IDd9uQoGIiIpJFTpZDbxIy9BwUBEJKWEg0HTl6tgICKSQnSfgYiINNsdyAoGIiIpRC0DEREhFFIwEBFJeyFvnofbKBiIiKSQUDNNYa1gICKSQio9CZ90JiIiLSukiepERNLb00s3hecm0piBiEj6+v6zKwE0ZiAiIpDVDDPVKRiIiKSAykqPLudkZTZ5+QoGIiIpYHdpRXQ5J1vBQEQkLe0qLY8u52RrCmsRkbT0WUlsMFDLQEQkLVXtJlLLQEQkLe2rCEWXNYAsIpKmyioqo8vt1TIQEUlPZaGYYKCWgYhIeoptGWgAWUQkTcUGA01HISKSpmK7iZohFigYiIikgkjLYPLwXhzRtUOTl69gICKSAiItgx+M6YtpCmsRkfQUaRm0y2yer20FAxGRFFBWUUlWhjXLIy9BwUBEJCWUVVTSLqv5vrIVDEREUkBZqJLsZuoiAgUDEZGUoJaBiIiEg0E6tAzMbIyZ/dPM1pvZja1dHxGRZFJaEaJ9W28ZmFkmcC9wJtAfmGBm/Vu3ViIiyWPV5s846pCOzVZ+VrOVnJghwHp3/wDAzGYBY4Gi2jb4z559PLTwA4L84Z/V8jjxc08kdyLlJpA3gRonVm4CeZOgvolI5PfWlj+zhD7eRD6zZig2GT6D5vqdNVNWSspCbNpewtUjjkxgq8QkSzDoDmyKeV8MDK2eycyuBq4GaPelo/nZS2tapnYibUwiN7AmclV7InfGJlZuAnkTKbnZPodEyo0v89GHdOSCE3okUIvEJEswiIu7PwA8AHD8oEH++q1n7I/wtYXZRH4p+gNJuNxEJEN9U+731ly/DJFqkiUYbAZ6xrzvEaTVKtOMA3Oym7trpiIAAAhlSURBVLVSIiLpIikGkIGlQB8z621m7YCLgRdauU4iImkjKVoG7l5hZtcCc4FMYKa7v9fK1RIRSRtJEQwA3H02MLu16yEiko6SpZtIRERakYKBiIgoGIiIiIKBiIgA1lzTMDQ3M9sN/DOOrAcD/0mg6IOAzxKsTqLbJFqnhuyjIdu0RL0achzNXa+WqJN+f4lJxnq1hTrlAMXuPuYLa9w9JV/AsqbMF5P/gQbUJaFtEq1TW6pXA4+jWevVEnXS7y/169VG6lRr+eom+qK/tdA2LbGPZKxXS9Qp0f0k4+fU0G2aex/J+PtrSP6GaAt1qlUqdxMtc/fBTZWvJSVjnUD1SoTqFD/VK37NXae6yk/llsEDTZyvJSVjnUD1SoTqFD/VK37NXaday0/ZloGIiDSdVG4ZiIhIE1EwEBGRthEMzOw8M3Mz69vadamJme2pZ/0CM2uRgSwz62Fmz5vZ+2b2LzP7bTBteG35p5pZhxaqW52fU0tL5vMqmc6pYH9JeV4l2zkFyXtetYlgAEwAFgU/42Zmmc1TneRk4cdm/QV4zt37AMcAHYGf17HZVKBFgkES0nkVB51XCUvO8yrRmyKS7UX4pNtM+AT8Z5BWCLwBvET4LuX7gYxg3R7gDmAFcEoL1XFPUKcXY9J+B1weLC8ABrdAPUYCb1RLOxDYBhwAzABWAyuB7wLXAWXAKuC1FvqcOgLzgXeC/Y4N1vUC1gAPAu8B/wfkput5lSznVLKfV8l0TiX7edUWWgZjgZfdfR2wzcwGBelDCJ94/YGjgAuC9AOAt9z9OHdf1OK1bV0DgLdjE9x9F/AhcCXhP44Cdx8IPO7udwMfAae6+6ktVMdS4Hx3PwE4FbjD9j8IuA9wr7sPAHYCFzZjPXRexS/Zz6tkOacgic+rthAMJgCzguVZ7G96LXH3D9w9BDwJnBKkh4BnW7aKKaEQ+IO7VwC4+/ZWqocBt5vZSuAVoDtwaLBug7svD5bfJvwl01x0XjWNQlr/vEqWcwqS+LxKmiedNYSZdQVOA/LNzAk/MtMJN7eq30AReV8afOAtrYKqwTenFepQBIyLTTCzA4EjgI2tUJ+aXArkAYPcvdzMNrL/s9oXky8E5DZHBVLovEqGcwqS/7xq9XMKkv+8SvWWwTjgT+7+ZXfv5e49gQ3ACGCImfU2swzgIsIDNq3p30B/M2tvZp0J97O2tPlABzObCNEBqTuARwg/f/pbZpYVrOsabLMb6NSCdTwI2BL80Z4KfLkF9x2RKudVMpxTkPznVTKcU5Dk51WqB4MJwF+rpT0bpC8lPKC2hvAHXj1fiwj+CPa5+ybgacIDaU8D77Z0XTw8InU+8A0zex9YR7g/9UfAQ4T7eFea2QrgkmCzB4CXzey15qxb5HMCHgcGm9kqYCKwtjn3W4ukPq+S6ZyC5D2vkuycgmQ/r4IR6zbFzAqBG9z960lQl+OAB919SGvXJZmlwueULOdVKnxWySBVPqdkOa9SvWWQ1Mzs24QHg25q7bokM31O8dNnFR99Tolrky0DERFJjFoG0uLMrKeZvWZmRWb2npl9L0jvambzgikN5plZlyC9r5n9w8z2mdkN1cr6npmtDsqZ2hrHI8mhAefVpWa20sxWmdnioFspUtYYM/unma03sxtb65hakloG0uLM7DDgMHd/x8w6Eb6++zzgcmC7u08P/gC7uPsPzOwQwleAnAfscPcZQTnHEr5WewjhO1pfBr7t7utb/KCk1TXgvDoZWOPuO8zsTOBWdx8aXA21DhgFFBMe3J3g7kWtcVwtRS0DaXHu/rG7vxMs7yZ8BUV3wndnPhpke5TwHzLuvsXdlwLl1YrqR/juzL3BTU2vs//OTUkzDTivFrv7jiD9TaBHsDwEWB/cBFZG+B+OsS1zFK1HwUBalZn1Ao4H3gIOdfePg1WfsP8u0dqsBkaYWbdgBsyzgJ7NVFVJIQ04r64A5gTL3YFNMeuKg7Q2LaXvQJbUZmYdCV9nPdXdd+2fLiZ87Xpwl2at3H2Nmf2S8ARjnwPLCd9FKmks0fMquBHtCvZPAZGW1DKQVmFm2YT/YB93978EyZ8G/b6R/t8t9ZXj7n9090Hu/lVgB+G+XklTiZ5XZjaQ8I1xY919W5C8maotzB5BWpumYCAtLpgx8o+EB+9+E7PqBWBSsDwJeD6Osg4Jfh5BeLzgiaatraSKRM+r4Jz5C3BZMItoxFKgTzA9RDvg4qCMNk1XE0mLM7NTgIWE55avDJJ/RLh/92nCE5z9Gxjv7tvN7EvAMsJz5FcSnuO9f9AFsBDoRnhw+b/dfX6LHowkjQacVw8RnrL630HeCncfHJR1FnAX4cnkZrp7XQ/qaRMUDERERN1EIiKiYCAiIigYiIgICgYiIoKCgYiIoGAg0iBmdmv1GVSrrT/PzPq3ZJ1EGkPBQKR5nAcoGEjK0H0GInEysx8TvoN1C+GJzN4GPgOuBtoB64HLgALgxWDdZ4RvbAK4F8gD9gJXuXtrPYtX5AsUDETiYGaDgEeAoYQneHwHuB94ODKnjZn9DPjU3e8xs0eAF939mWDdfMLPWnjfzIYCv3D301r+SERqpllLReIzAviru+8FMLPIXDXHBkGgM9ARmFt9w2AWzZOBP8fMoNm+2WsskgAFA5HGeQQ4z91XmNnlQGENeTKAne5e0IL1EkmIBpBF4vMGcJ6Z5QaPVDwnSO8EfBxMnXxpTP7dwTrcfRewwcy+AeHZNWOftyuSDBQMROIQPE7xKWAF4SdiLQ1W/YTwrJh/B2IHhGcB08zsXTM7inCguMLMVgDvkQaPUZTUogFkERFRy0BERBQMREQEBQMREUHBQEREUDAQEREUDEREBAUDERFBwUBERID/D9rcOgFMzep0AAAAAElFTkSuQmCC\n", "text/plain": [ "
" ] }, "metadata": { "needs_background": "light" }, "output_type": "display_data" } ], "source": [ "df1.plot(title=\"Kendrick P\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "with get_cursor() as cur:\n", " cur.execute('''select spyid from spotify_daily_streams''')\n", " all_stored = [r[0] for r in cur.fetchall()]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "left_to_do = all_artists_fr[~all_artists_fr.index.isin(all_stored)]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "excluded_countries = [\n", " 'MX', 'BR', 'CL', 'TR', 'SG', 'JP', 'ID', 'TH', 'TW', 'AR', 'PE', 'PH',\n", " 'IN', 'CO', 'MY', 'MA', 'UY', 'GT', 'DO', 'CR', 'VN', 'PA', 'PY', 'SA',\n", " 'EC', 'EG', 'TN', 'HK', 'DZ', 'BO', 'JO', 'MT'\n", "]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "included_countries = left_to_do[~left_to_do.city1_country.isin(excluded_countries)]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "len(all_artists_fr), len(left_to_do), len(included_countries)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "isomap = requests.get(\n", " ('https://gist.githubusercontent.com/ssskip/5a94bfcd2835bf1dea52/raw/'\n", " 'aeed5b0cb3a7eda19e614915c3d88ce113e4a914/ISO3166-1.alpha2.json')\n", ").json()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from datetime import datetime" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def store_batch(spyid_list):\n", " print(f\"Starting, {len(spyid_list)} items\")\n", " errors = 0\n", " for i, artist_id in enumerate(spyid_list):\n", " if (i % 30 == 0):\n", " print(\"refreshing headers...\")\n", " cg.refresh_request_headers()\n", " print(i, datetime.today().time().strftime('%H:%M:%S'), artist_id)\n", " try:\n", " fetch_and_store(artist_id, cg)\n", " errors = 0\n", " except Exception as e:\n", " errors += 1\n", " print(f\"Failed to get and store streams for {artist_id}, {e}\")\n", " traceback.print_exc()\n", "\n", " if errors > 5:\n", " print(\"Too many errors\")\n", " break\n", " import time; time.sleep(3 + random.randint(0,4))\n", " #import time; time.sleep(30 + random.randint(10,40))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def add_slope(dframe, days_back, roll=7):\n", " col = dframe.columns[0]\n", " rn = dframe.rolling(roll).mean().iloc[-days_back:]\n", " int_x = rn.index.astype(int)\n", " m_b = numpy.polyfit(y=rn[col], x=int_x, deg=1) \n", " fit_func = numpy.poly1d(m_b)\n", " return int_x, fit_func(int_x)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "spyids = ['1TqTJTMW8kkMX41lkYUmbA']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "store_batch(spyids)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "load_streams(spyids).iloc[-365:].plot(title='8D')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def sql_to_dataframe(sql_statement, columns=None, sql_args=[], index_column=None):\n", " with mysql.get_cursor(False) as cur:\n", " cur.execute(sql_statement, sql_args)\n", " rows = cur.fetchall()\n", " columns = [c[0] for c in cur.description] if columns is None else columns\n", " df = pd.DataFrame(data=rows, columns=columns)\n", " if index_column:\n", " df = df.set_index(index_column)\n", " return df" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import json" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from tracker.unicorn import daily_streams" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Copy listeners from one db to other" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": false }, "outputs": [], "source": [ "from tracker import db\n", "db.setup_session()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "result_proxy = db.Session.execute('''\n", " select\n", " sal.*\n", " from spy_artist_listeners sal \n", " where sal.as_of > current_date - 3\n", " and artist_spyid in (\n", " select\n", " distinct artist_spyid\n", " from spy_track_olap\n", " where artist_popularity < 85\n", " and not is_signed\n", " and release_date > current_date - 90\n", " and country_codes && array['US']\n", " )\n", " order by as_of desc\n", " ''')\n", "colnames = [a.name for a in result_proxy.cursor.description]\n", "rows = result_proxy.fetchall()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def insert_to_mysql(pg_rows, colnames):\n", " renamer = {'artist_spyid': 'spyid', 'as_of': 'last_updated'}\n", " final_names = [renamer.get(c, c) for c in colnames]\n", " seen_artists = set()\n", " id_col_ix = final_names.index('spyid')\n", " for i, chunk in enumerate(chunks(pg_rows, 500)):\n", " new_rows = [i for i in chunk if i[id_col_ix] not in seen_artists]\n", " print(f'{i} chunk, {len(new_rows)} (total {len(pg_rows)})')\n", " mysql.upsert(\n", " tablename='spotify_artist_listeners',\n", " datarows=new_rows,\n", " column_names=final_names,\n", " immut_columns=('spyid', 'first_seen')\n", " )\n", " seen_artists.update([r[id_col_ix] for r in new_rows])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "insert_to_mysql(rows, colnames)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# tracks for the artists (will rerun the signed filter on import.)\n", "artist_spyids = [r[0] for r in rows]\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "r = db.Session.execute('select spyid from spy_track_olap where not is_signed and release_date > current_date - 90 and artist_spyid = any(:arts)', params=dict(arts=artist_spyids))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "db.Session.rollback()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "track_spyids = [a[0] for a in r.fetchall()]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "inserted_tracks = mysql.query('select spyid, album_id from spotify_track where first_seen > date_sub(now(), interval 1 hour)')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "len(inserted_tracks)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "spy = spotify._authenticate()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "s = spy.albums([r[1] for r in inserted_tracks[0:3]])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "s" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "distinct_albums = [a[1] for a in inserted_tracks]\n", "print(f\"{len(distinct_albums)} total albums\")\n", "for chunk in chunks(distinct_albums, 200):\n", " print(\"Doing 200\")\n", " artist_ingestion.store_albums(chunk)\n", " import time; time.sleep(10)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# artst_ingestion.upsert_tracks(track_spyids[0:5])\n", "spy = spotify._authenticate()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "r1 = spy.tracks(track_spyids[0:5])" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "t = artist_ingestion._fetch_and_parse_tracks(track_spyids[0:5], datetime.utcnow(), spy)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "artist_ingestion._upsert_models(t)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "artist_ingestion.upsert_tracks(track_spyids[5:])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Ingest New Daily Streams" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "spyids = mysql.query_single_column(\n", "'''\n", "select\n", " sal.spyid\n", "from spotify_artist_listeners sal\n", "left join spotify_daily_streams sds on sds.spyid = sal.spyid\n", "where sal.monthly_listeners < 2000000\n", " and sal.monthly_listeners_delta > 1000\n", " and sds.spyid is null\n", " and (\n", " city0_country in (select iso_code from country_settings where is_source_for_releases)\n", " or city1_country in (select iso_code from country_settings where is_source_for_releases)\n", " or city2_country in (select iso_code from country_settings where is_source_for_releases)\n", " )\n", "order by monthly_listeners_delta desc\n", "'''\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "len(spyids)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from tracker.unicorn import artist_ingestion" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "reload(artist_ingestion)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "artist_ingestion.fetch_artists??" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "all_artist_models = artist_ingestion.fetch_artists(spyids)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "for chunk in chunks(all_artist_models, 300):\n", " mysql.std_upsert(chunk)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "artist_ingestion.store_batch_of_daily_streams(spyids)" ] }, { "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 }