{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "This is for making the power function" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "%matplotlib inline" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "import os\n", "os.environ['UC_SECRETS_MANAGER_KEY_NAMES'] = \"WhitelistKeys,unicronSecretsProd\"" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "import sys; sys.path.insert(0, '/Users/joel/src/thundr/unicapi')" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "import logging\n", "logger = logging.getLogger('notebook')\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Loading secrets from 0: WhitelistKeys\n", "Boto session created in eu-west-1\n", "Loading secrets from 1: unicronSecretsProd\n", "Boto session created in eu-west-1\n" ] } ], "source": [ "from api.data import db" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Engine(postgresql://bot:***@whitelist2.cbn1zk7uet6r.eu-west-1.rds.amazonaws.com:5432/randh)" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "db.setup_randh_session()\n", "db.RandHSession.get_bind()" ] }, { "cell_type": "code", "execution_count": 47, "metadata": {}, "outputs": [], "source": [ "db.RandHSession.rollback()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "columns = {\n", " 'video_count': 'total_video_count',\n", " 'comments': 'comment_count',\n", " 'diggs': 'digg_count',\n", " 'plays': 'play_count',\n", " 'shares': 'share_count',\n", " 'author_following': 'author_following_count',\n", " 'author_follower': 'author_follower_count',\n", " 'author_heart': 'author_heart_count',\n", " 'author_video': 'author_video_count',\n", " 'author_digg': 'author_digg_count',\n", "}\n" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "def _pg_to_dataframe(sql_statement, columns=None, params={}, index_column=None):\n", "\n", " rp = db.RandHSession.execute(sql_statement, params=params)\n", " columns = columns or [a.name for a in rp.cursor.description]\n", " rows = rp.fetchall()\n", " if not rows:\n", " logger.info(f\"Empty dataset for {sql_statement[:30]}\")\n", " df = pd.DataFrame(data=[], columns=columns)\n", " else:\n", " logger.info(f\"Rows: {len(rows)}, {rows[0]}...{rows[-1]}\")\n", " df = pd.DataFrame(data=rows, columns=columns)\n", "\n", " if index_column:\n", " df = df.set_index(index_column)\n", " return df" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [], "source": [ "df = _pg_to_dataframe(\n", " f'''\n", " select \n", " video_ttid, \n", " as_of as ts, \n", " {columns['plays']} as value\n", " from tiktok_music_video_stats\n", " where music_ttid=:ttid\n", " ''',\n", " params=dict(ttid='6942499815868385282'),\n", ")\n", "\n" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": [ "by_video = df.pivot(index='ts', columns='video_ttid', values='value')" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [], "source": [ "by_video2 = by_video.resample('6H').max().interpolate()" ] }, { "cell_type": "code", "execution_count": 64, "metadata": {}, "outputs": [ { "ename": "TypeError", "evalue": "sort_values() missing 1 required positional argument: 'by'", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mTypeError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mby_video2\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;31mTypeError\u001b[0m: sort_values() missing 1 required positional argument: 'by'" ] } ], "source": [ "by_video2.sort_values(axis=1)" ] }, { "cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [], "source": [ "meta_dict = by_video2.describe().to_dict('split')" ] }, { "cell_type": "code", "execution_count": 53, "metadata": {}, "outputs": [], "source": [ "video_info = {ttid: obj for ttid,obj in db.RandHSession.execute(\n", " '''select ttid, to_jsonb(v.*) from tiktok_music_top_videos v where ttid = any (:ttids)''',\n", " params=dict(ttids=meta_dict['columns']),\n", ")}" ] }, { "cell_type": "code", "execution_count": 65, "metadata": {}, "outputs": [ { "ename": "AttributeError", "evalue": "'dict' object has no attribute 'T'", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mAttributeError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mvideo_info\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mT\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[0;31mAttributeError\u001b[0m: 'dict' object has no attribute 'T'" ] } ], "source": [ "video_info.T.sort_values" ] }, { "cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [], "source": [ "meta_dict['video_info'] = video_info" ] }, { "cell_type": "code", "execution_count": 76, "metadata": {}, "outputs": [], "source": [ "maxes = by_video2.max()" ] }, { "cell_type": "code", "execution_count": 82, "metadata": {}, "outputs": [], "source": [ "by_video3 = by_video2[sorted(by_video2.columns, key=lambda col: maxes[col], reverse=True)]" ] }, { "cell_type": "code", "execution_count": 83, "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", " \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", " \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", " \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", " \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", " \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", " \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", "
video_ttid694586075207500109369451485873072570936944797813163232517694848980513517082169470866504428290616945954563924987141694441682007820211769477454368913850936945084919005596933
ts
2021-04-01 12:00:002800000.03500000.01500000.0NaNNaN432300.0742200.000000NaN453400.0
2021-04-01 18:00:003300000.03600000.01600000.0NaNNaN566900.0750300.000000NaN457200.0
2021-04-02 00:00:003700000.03600000.01600000.0NaNNaN633900.0753900.000000NaN458800.0
2021-04-02 06:00:003900000.03600000.01600000.0NaNNaN657100.0759000.000000NaN460800.0
2021-04-02 12:00:004500000.03600000.01600000.0NaNNaN678800.0764700.000000NaN465000.0
2021-04-02 18:00:004700000.03700000.01600000.0NaNNaN713000.0771800.000000NaN470800.0
2021-04-03 00:00:004900000.03700000.01600000.0NaNNaN745100.0774300.000000NaN472000.0
2021-04-03 06:00:005100000.03700000.01600000.0NaNNaN766600.0777600.000000NaN473500.0
2021-04-03 12:00:005300000.03700000.01600000.0NaNNaN782300.0781700.000000NaN475900.0
2021-04-03 18:00:005400000.03800000.01600000.0NaNNaN805200.0785000.000000NaN478300.0
2021-04-04 00:00:005500000.03800000.01600000.0NaNNaN828500.0786400.000000NaN479200.0
2021-04-04 06:00:005600000.03800000.01600000.0NaN645000.0845500.0788900.000000NaN479200.0
2021-04-04 12:00:005900000.03800000.01600000.0NaN890000.0857000.0791100.000000NaN479200.0
2021-04-04 18:00:006000000.03800000.01600000.0NaN1100000.0871600.0793900.000000NaN479200.0
2021-04-05 00:00:006200000.03800000.01600000.0NaN1100000.0884500.0795500.000000NaN479200.0
2021-04-05 06:00:006600000.03900000.01600000.0NaN1200000.0893500.0800000.000000NaN479200.0
2021-04-05 12:00:006800000.03900000.01600000.0NaN1200000.0902500.0806200.000000NaN479200.0
2021-04-05 18:00:006900000.04000000.01600000.0NaN1300000.0930400.0813400.000000NaN479200.0
2021-04-06 00:00:007000000.04000000.01600000.0NaN1300000.0962100.0816700.000000NaN479200.0
2021-04-06 06:00:007800000.04000000.01600000.0NaN1300000.0978900.0824200.000000NaN479200.0
2021-04-06 12:00:009400000.04000000.01600000.0NaN1400000.0996800.0833200.000000NaN479200.0
2021-04-06 18:00:0010300000.04100000.01600000.0NaN1400000.01000000.0842200.000000NaN479200.0
2021-04-07 00:00:0010600000.04100000.01600000.0NaN1400000.01000000.0843500.000000NaN479200.0
2021-04-07 06:00:0010900000.04100000.01600000.0NaN1400000.01000000.0848000.000000646300.0479200.0
2021-04-07 12:00:0011000000.04100000.01600000.0NaN1400000.01100000.0856033.333333676900.0479200.0
2021-04-07 18:00:0011100000.04100000.01700000.0NaN1400000.01100000.0864066.666667699600.0479200.0
2021-04-08 00:00:0011200000.04100000.01700000.0NaN1400000.01100000.0872100.000000707200.0479200.0
2021-04-08 06:00:0011400000.04100000.01700000.0NaN1500000.01100000.0872100.000000719500.0479200.0
2021-04-08 12:00:0011600000.04200000.01700000.01700000.01500000.01100000.0872100.000000719500.0479200.0
\n", "
" ], "text/plain": [ "video_ttid 6945860752075001093 6945148587307257093 \\\n", "ts \n", "2021-04-01 12:00:00 2800000.0 3500000.0 \n", "2021-04-01 18:00:00 3300000.0 3600000.0 \n", "2021-04-02 00:00:00 3700000.0 3600000.0 \n", "2021-04-02 06:00:00 3900000.0 3600000.0 \n", "2021-04-02 12:00:00 4500000.0 3600000.0 \n", "2021-04-02 18:00:00 4700000.0 3700000.0 \n", "2021-04-03 00:00:00 4900000.0 3700000.0 \n", "2021-04-03 06:00:00 5100000.0 3700000.0 \n", "2021-04-03 12:00:00 5300000.0 3700000.0 \n", "2021-04-03 18:00:00 5400000.0 3800000.0 \n", "2021-04-04 00:00:00 5500000.0 3800000.0 \n", "2021-04-04 06:00:00 5600000.0 3800000.0 \n", "2021-04-04 12:00:00 5900000.0 3800000.0 \n", "2021-04-04 18:00:00 6000000.0 3800000.0 \n", "2021-04-05 00:00:00 6200000.0 3800000.0 \n", "2021-04-05 06:00:00 6600000.0 3900000.0 \n", "2021-04-05 12:00:00 6800000.0 3900000.0 \n", "2021-04-05 18:00:00 6900000.0 4000000.0 \n", "2021-04-06 00:00:00 7000000.0 4000000.0 \n", "2021-04-06 06:00:00 7800000.0 4000000.0 \n", "2021-04-06 12:00:00 9400000.0 4000000.0 \n", "2021-04-06 18:00:00 10300000.0 4100000.0 \n", "2021-04-07 00:00:00 10600000.0 4100000.0 \n", "2021-04-07 06:00:00 10900000.0 4100000.0 \n", "2021-04-07 12:00:00 11000000.0 4100000.0 \n", "2021-04-07 18:00:00 11100000.0 4100000.0 \n", "2021-04-08 00:00:00 11200000.0 4100000.0 \n", "2021-04-08 06:00:00 11400000.0 4100000.0 \n", "2021-04-08 12:00:00 11600000.0 4200000.0 \n", "\n", "video_ttid 6944797813163232517 6948489805135170821 \\\n", "ts \n", "2021-04-01 12:00:00 1500000.0 NaN \n", "2021-04-01 18:00:00 1600000.0 NaN \n", "2021-04-02 00:00:00 1600000.0 NaN \n", "2021-04-02 06:00:00 1600000.0 NaN \n", "2021-04-02 12:00:00 1600000.0 NaN \n", "2021-04-02 18:00:00 1600000.0 NaN \n", "2021-04-03 00:00:00 1600000.0 NaN \n", "2021-04-03 06:00:00 1600000.0 NaN \n", "2021-04-03 12:00:00 1600000.0 NaN \n", "2021-04-03 18:00:00 1600000.0 NaN \n", "2021-04-04 00:00:00 1600000.0 NaN \n", "2021-04-04 06:00:00 1600000.0 NaN \n", "2021-04-04 12:00:00 1600000.0 NaN \n", "2021-04-04 18:00:00 1600000.0 NaN \n", "2021-04-05 00:00:00 1600000.0 NaN \n", "2021-04-05 06:00:00 1600000.0 NaN \n", "2021-04-05 12:00:00 1600000.0 NaN \n", "2021-04-05 18:00:00 1600000.0 NaN \n", "2021-04-06 00:00:00 1600000.0 NaN \n", "2021-04-06 06:00:00 1600000.0 NaN \n", "2021-04-06 12:00:00 1600000.0 NaN \n", "2021-04-06 18:00:00 1600000.0 NaN \n", "2021-04-07 00:00:00 1600000.0 NaN \n", "2021-04-07 06:00:00 1600000.0 NaN \n", "2021-04-07 12:00:00 1600000.0 NaN \n", "2021-04-07 18:00:00 1700000.0 NaN \n", "2021-04-08 00:00:00 1700000.0 NaN \n", "2021-04-08 06:00:00 1700000.0 NaN \n", "2021-04-08 12:00:00 1700000.0 1700000.0 \n", "\n", "video_ttid 6947086650442829061 6945954563924987141 \\\n", "ts \n", "2021-04-01 12:00:00 NaN 432300.0 \n", "2021-04-01 18:00:00 NaN 566900.0 \n", "2021-04-02 00:00:00 NaN 633900.0 \n", "2021-04-02 06:00:00 NaN 657100.0 \n", "2021-04-02 12:00:00 NaN 678800.0 \n", "2021-04-02 18:00:00 NaN 713000.0 \n", "2021-04-03 00:00:00 NaN 745100.0 \n", "2021-04-03 06:00:00 NaN 766600.0 \n", "2021-04-03 12:00:00 NaN 782300.0 \n", "2021-04-03 18:00:00 NaN 805200.0 \n", "2021-04-04 00:00:00 NaN 828500.0 \n", "2021-04-04 06:00:00 645000.0 845500.0 \n", "2021-04-04 12:00:00 890000.0 857000.0 \n", "2021-04-04 18:00:00 1100000.0 871600.0 \n", "2021-04-05 00:00:00 1100000.0 884500.0 \n", "2021-04-05 06:00:00 1200000.0 893500.0 \n", "2021-04-05 12:00:00 1200000.0 902500.0 \n", "2021-04-05 18:00:00 1300000.0 930400.0 \n", "2021-04-06 00:00:00 1300000.0 962100.0 \n", "2021-04-06 06:00:00 1300000.0 978900.0 \n", "2021-04-06 12:00:00 1400000.0 996800.0 \n", "2021-04-06 18:00:00 1400000.0 1000000.0 \n", "2021-04-07 00:00:00 1400000.0 1000000.0 \n", "2021-04-07 06:00:00 1400000.0 1000000.0 \n", "2021-04-07 12:00:00 1400000.0 1100000.0 \n", "2021-04-07 18:00:00 1400000.0 1100000.0 \n", "2021-04-08 00:00:00 1400000.0 1100000.0 \n", "2021-04-08 06:00:00 1500000.0 1100000.0 \n", "2021-04-08 12:00:00 1500000.0 1100000.0 \n", "\n", "video_ttid 6944416820078202117 6947745436891385093 \\\n", "ts \n", "2021-04-01 12:00:00 742200.000000 NaN \n", "2021-04-01 18:00:00 750300.000000 NaN \n", "2021-04-02 00:00:00 753900.000000 NaN \n", "2021-04-02 06:00:00 759000.000000 NaN \n", "2021-04-02 12:00:00 764700.000000 NaN \n", "2021-04-02 18:00:00 771800.000000 NaN \n", "2021-04-03 00:00:00 774300.000000 NaN \n", "2021-04-03 06:00:00 777600.000000 NaN \n", "2021-04-03 12:00:00 781700.000000 NaN \n", "2021-04-03 18:00:00 785000.000000 NaN \n", "2021-04-04 00:00:00 786400.000000 NaN \n", "2021-04-04 06:00:00 788900.000000 NaN \n", "2021-04-04 12:00:00 791100.000000 NaN \n", "2021-04-04 18:00:00 793900.000000 NaN \n", "2021-04-05 00:00:00 795500.000000 NaN \n", "2021-04-05 06:00:00 800000.000000 NaN \n", "2021-04-05 12:00:00 806200.000000 NaN \n", "2021-04-05 18:00:00 813400.000000 NaN \n", "2021-04-06 00:00:00 816700.000000 NaN \n", "2021-04-06 06:00:00 824200.000000 NaN \n", "2021-04-06 12:00:00 833200.000000 NaN \n", "2021-04-06 18:00:00 842200.000000 NaN \n", "2021-04-07 00:00:00 843500.000000 NaN \n", "2021-04-07 06:00:00 848000.000000 646300.0 \n", "2021-04-07 12:00:00 856033.333333 676900.0 \n", "2021-04-07 18:00:00 864066.666667 699600.0 \n", "2021-04-08 00:00:00 872100.000000 707200.0 \n", "2021-04-08 06:00:00 872100.000000 719500.0 \n", "2021-04-08 12:00:00 872100.000000 719500.0 \n", "\n", "video_ttid 6945084919005596933 \n", "ts \n", "2021-04-01 12:00:00 453400.0 \n", "2021-04-01 18:00:00 457200.0 \n", "2021-04-02 00:00:00 458800.0 \n", "2021-04-02 06:00:00 460800.0 \n", "2021-04-02 12:00:00 465000.0 \n", "2021-04-02 18:00:00 470800.0 \n", "2021-04-03 00:00:00 472000.0 \n", "2021-04-03 06:00:00 473500.0 \n", "2021-04-03 12:00:00 475900.0 \n", "2021-04-03 18:00:00 478300.0 \n", "2021-04-04 00:00:00 479200.0 \n", "2021-04-04 06:00:00 479200.0 \n", "2021-04-04 12:00:00 479200.0 \n", "2021-04-04 18:00:00 479200.0 \n", "2021-04-05 00:00:00 479200.0 \n", "2021-04-05 06:00:00 479200.0 \n", "2021-04-05 12:00:00 479200.0 \n", "2021-04-05 18:00:00 479200.0 \n", "2021-04-06 00:00:00 479200.0 \n", "2021-04-06 06:00:00 479200.0 \n", "2021-04-06 12:00:00 479200.0 \n", "2021-04-06 18:00:00 479200.0 \n", "2021-04-07 00:00:00 479200.0 \n", "2021-04-07 06:00:00 479200.0 \n", "2021-04-07 12:00:00 479200.0 \n", "2021-04-07 18:00:00 479200.0 \n", "2021-04-08 00:00:00 479200.0 \n", "2021-04-08 06:00:00 479200.0 \n", "2021-04-08 12:00:00 479200.0 " ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "by_video3" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = df.set_index('ts').resample('8hour').mean()\n", "df.insert(0, 'ts', (df.index.astype(int) / 1e6).astype(int))\n", "fiveMin = df\n", "\n", "# import pdb; pdb.set_trace()\n", "# _cache_df(fiveMin, track_id)\n", "\n", "timeseries = fiveMin.to_numpy().tolist()\n", "\n", "fiveMin['dday'] = fiveMin.index.map(to_day)\n", "# fiveMin = fiveMin[fiveMin.dday > fiveMin.dday.max()- timedelta(days=4)]\n", "fiveMin['tday'] = fiveMin.index.map(to_minutes).astype(int)\n", "fmpv = fiveMin.pivot(index='tday', columns='dday', values=['listeners'])\n", "fmpv.insert(0, 'ts', fmpv.index * 60000)\n", "\n", "byday = fmpv.to_numpy().tolist()\n", "\n", "metaDict = fmpv.listeners.describe().to_dict('split')\n", "metaDict['columns'] = [c.isoformat() for c in metaDict['columns']]\n", "\n", "fmtoday = fmpv[fmpv[fmpv.columns[-1]] > 0]\n", "numDays = len(fmpv.columns)\n", "\n", "delta3mean = (fmtoday[fmtoday.columns[-1]] - fmtoday[fmtoday.columns[-4:-1]].mean(axis=1)) if numDays >= 5 else None\n", "delta1 = (fmtoday[fmtoday.columns[-1]] - fmtoday[fmtoday.columns[-2]]) if numDays >= 2 else None\n", "delta7 = (fmtoday[fmtoday.columns[-1]] - fmtoday[fmtoday.columns[-8]]) if numDays >= 9 else None\n", "\n", "return output_json({\n", " 'timeseries': timeseries,\n", " 'byDay': byday,\n", " 'byDayMeta': metaDict,\n", " 'deltas': {\n", " 'd1': list(map(float, [delta1.sum() / (delta1.count()*5), delta1.sum(), delta1.count()])) if delta1 is not None and delta1.count() > 0 else None,\n", " 'd3mean': list(map(float, [delta3mean.sum() / (delta3mean.count()*5), delta3mean.sum(), delta3mean.count()])) if delta3mean is not None and delta3mean.count() > 0 else None,\n", " 'd7': list(map(float, [delta7.sum() / (delta7.count()*5), delta7.sum(), delta7.count()])) if delta7 is not None and delta7.count() > 0 else None,\n", " }\n", "})" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "db.RandHSession.rollback()" ] }, { "cell_type": "code", "execution_count": 86, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
video_ttidvalue
ts
2021-04-01 14:02:5469459545639249871413500000
2021-04-01 16:03:4169459545639249871413500000
2021-04-01 18:04:2769459545639249871413500000
2021-04-01 20:05:1369459545639249871413500000
2021-04-01 22:05:5869459545639249871413600000
.........
2021-04-08 09:05:30694774543689138509311300000
2021-04-08 11:06:18694774543689138509311400000
2021-04-08 13:07:06694848980513517082111500000
2021-04-08 15:07:53694848980513517082111500000
2021-04-08 17:08:40694848980513517082111600000
\n", "

85 rows × 2 columns

\n", "
" ], "text/plain": [ " video_ttid value\n", "ts \n", "2021-04-01 14:02:54 6945954563924987141 3500000\n", "2021-04-01 16:03:41 6945954563924987141 3500000\n", "2021-04-01 18:04:27 6945954563924987141 3500000\n", "2021-04-01 20:05:13 6945954563924987141 3500000\n", "2021-04-01 22:05:58 6945954563924987141 3600000\n", "... ... ...\n", "2021-04-08 09:05:30 6947745436891385093 11300000\n", "2021-04-08 11:06:18 6947745436891385093 11400000\n", "2021-04-08 13:07:06 6948489805135170821 11500000\n", "2021-04-08 15:07:53 6948489805135170821 11500000\n", "2021-04-08 17:08:40 6948489805135170821 11600000\n", "\n", "[85 rows x 2 columns]" ] }, "execution_count": 86, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.groupby('ts').max()" ] }, { "cell_type": "code", "execution_count": 92, "metadata": {}, "outputs": [], "source": [ "sound1 = by_video2.copy()\n", "sound1['6942499815868385282'] = by_video2.max(axis=1)\n", "sound1[['6942499815868385282']]" ] }, { "cell_type": "code", "execution_count": 94, "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", " \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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
video_ttid6942499815868385282
ts
2021-04-01 12:00:003500000.0
2021-04-01 18:00:003600000.0
2021-04-02 00:00:003700000.0
2021-04-02 06:00:003900000.0
2021-04-02 12:00:004500000.0
2021-04-02 18:00:004700000.0
2021-04-03 00:00:004900000.0
2021-04-03 06:00:005100000.0
2021-04-03 12:00:005300000.0
2021-04-03 18:00:005400000.0
2021-04-04 00:00:005500000.0
2021-04-04 06:00:005600000.0
2021-04-04 12:00:005900000.0
2021-04-04 18:00:006000000.0
2021-04-05 00:00:006200000.0
2021-04-05 06:00:006600000.0
2021-04-05 12:00:006800000.0
2021-04-05 18:00:006900000.0
2021-04-06 00:00:007000000.0
2021-04-06 06:00:007800000.0
2021-04-06 12:00:009400000.0
2021-04-06 18:00:0010300000.0
2021-04-07 00:00:0010600000.0
2021-04-07 06:00:0010900000.0
2021-04-07 12:00:0011000000.0
2021-04-07 18:00:0011100000.0
2021-04-08 00:00:0011200000.0
2021-04-08 06:00:0011400000.0
2021-04-08 12:00:0011600000.0
\n", "
" ], "text/plain": [ "video_ttid 6942499815868385282\n", "ts \n", "2021-04-01 12:00:00 3500000.0\n", "2021-04-01 18:00:00 3600000.0\n", "2021-04-02 00:00:00 3700000.0\n", "2021-04-02 06:00:00 3900000.0\n", "2021-04-02 12:00:00 4500000.0\n", "2021-04-02 18:00:00 4700000.0\n", "2021-04-03 00:00:00 4900000.0\n", "2021-04-03 06:00:00 5100000.0\n", "2021-04-03 12:00:00 5300000.0\n", "2021-04-03 18:00:00 5400000.0\n", "2021-04-04 00:00:00 5500000.0\n", "2021-04-04 06:00:00 5600000.0\n", "2021-04-04 12:00:00 5900000.0\n", "2021-04-04 18:00:00 6000000.0\n", "2021-04-05 00:00:00 6200000.0\n", "2021-04-05 06:00:00 6600000.0\n", "2021-04-05 12:00:00 6800000.0\n", "2021-04-05 18:00:00 6900000.0\n", "2021-04-06 00:00:00 7000000.0\n", "2021-04-06 06:00:00 7800000.0\n", "2021-04-06 12:00:00 9400000.0\n", "2021-04-06 18:00:00 10300000.0\n", "2021-04-07 00:00:00 10600000.0\n", "2021-04-07 06:00:00 10900000.0\n", "2021-04-07 12:00:00 11000000.0\n", "2021-04-07 18:00:00 11100000.0\n", "2021-04-08 00:00:00 11200000.0\n", "2021-04-08 06:00:00 11400000.0\n", "2021-04-08 12:00:00 11600000.0" ] }, "execution_count": 94, "metadata": {}, "output_type": "execute_result" } ], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "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 }