{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: psycopg2-binary in ./anaconda3/lib/python3.7/site-packages (2.8.4)\r\n" ] } ], "source": [ "!pip install psycopg2-binary" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "import psycopg2\n", "import numpy as np\n", "import pandas as pd\n", "from scipy import stats\n", "con=psycopg2.connect(dbname= 'smaredshiftdb', host='reporting-db.smeanalyticsapps.com', port= '5439', user= 'mfridson', password='MbtW7DN9HVnT')\n", "cur = con.cursor()" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "#Query you would like to run\n", "import time\n", "\n", "t0 = time.time()\n", "cur.execute(\"\"\"\n", "SELECT f.report_date,\n", " pi.track_album_artist,\n", " pi.track_name,\n", " pi.track_isrc,\n", " a.min_report_date,\n", " p.fin_label_name,\n", " f.country_code,\n", " sum(f.num_streams) as streams,\n", " datediff(day,a.min_report_date,f.report_date)\n", "FROM spotify.fact_streams f\n", "JOIN common.dim_products p on f.product_key = p.product_key\n", "JOIN spotify.dim_partner_info pi ON f.partner_info_key = pi.partner_info_key\n", "JOIN (SELECT pi.track_album_artist,\n", " pi.track_isrc,\n", " min(f.report_date) as min_report_date\n", " FROM spotify.fact_streams f\n", " JOIN spotify.dim_partner_info pi ON f.partner_info_key = pi.partner_info_key\n", " WHERE f.country_code='US'\n", " AND pi.track_album_artist !=''\n", " AND pi.track_isrc !=''\n", " GROUP BY 1,2\n", " HAVING min(f.report_date)>'2018-10-01'::date) a ON a.track_isrc = pi.track_isrc\n", "WHERE f.report_date BETWEEN a.min_report_date AND DATEADD(day,90,a.min_report_date)\n", " AND f.country_code='US'\n", " AND (p.fin_label_name LIKE '%Epic%' OR p.fin_label_name LIKE '%Columbia%' OR p.fin_label_name LIKE '%RCA%')\n", " AND p.fin_label_name NOT LIKE '%Legacy%'\n", " AND p.fin_label_name NOT LIKE '%Cat%'\n", "GROUP BY f.report_date, 2,3,4,5,6,7\n", "ORDER BY 2,3,1\n", "\"\"\")\n", "#Save query result to a Dataframe\n", "col_names = []\n", "for elt in cur.description:\n", " col_names.append(elt[0])\n", "df=pd.DataFrame(cur.fetchall(),columns=col_names)\n", "cur.close() \n", "con.close()\n", "t1 = time.time()\n", "\n", "total = t1-t0" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "221.19654202461243\n" ] } ], "source": [ "print(total)" ] }, { "cell_type": "code", "execution_count": 5, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
report_datetrack_album_artisttrack_nametrack_isrcmin_report_datefin_label_namecountry_codestreamsdate_diff
02018-12-07$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS2400
12018-12-08$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS1701
22018-12-09$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS902
32018-12-10$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS1013
42018-12-11$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS824
52018-12-12$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS555
62018-12-13$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS876
72018-12-14$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS807
82018-12-15$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS768
92018-12-16$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS489
102018-12-17$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS4610
112018-12-18$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS5011
122018-12-19$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS6612
132018-12-20$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS3913
142018-12-21$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS4114
152018-12-22$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS2915
162018-12-23$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS3416
172018-12-24$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS3317
182018-12-25$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS1918
192018-12-26$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS1119
202018-12-27$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS3420
212018-12-28$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS2021
222018-12-29$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS1922
232018-12-30$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS2023
242018-12-31$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS2224
252019-01-01$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS1325
262019-01-02$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS2326
272019-01-03$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS1327
282019-01-04$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS2328
292019-01-05$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS2529
..............................
3192322019-07-10teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS430861
3192332019-07-11teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS393262
3192342019-07-12teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS385063
3192352019-07-13teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS334464
3192362019-07-14teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS303665
3192372019-07-15teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS350766
3192382019-07-16teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS330567
3192392019-07-17teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS318168
3192402019-07-18teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS318369
3192412019-07-19teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS304870
3192422019-07-20teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS259171
3192432019-07-21teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS239272
3192442019-07-22teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS293973
3192452019-07-23teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS298074
3192462019-07-24teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS290275
3192472019-07-25teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS287076
3192482019-07-26teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS263277
3192492019-07-27teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS244578
3192502019-07-28teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS286779
3192512019-07-29teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS345580
3192522019-07-30teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS321081
3192532019-07-31teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS297982
3192542019-08-01teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS302183
3192552019-08-02teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS305384
3192562019-08-03teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS256385
3192572019-08-04teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS230886
3192582019-08-05teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS250787
3192592019-08-06teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS250388
3192602019-08-07teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS265289
3192612019-08-08teamwork.AfterhoursDEQ3219000832019-05-10Epic/AJ MitchelllUS273990
\n", "

319262 rows × 9 columns

\n", "
" ], "text/plain": [ " report_date track_album_artist track_name track_isrc \\\n", "0 2018-12-07 $KEETA Back 2 Trappin USRC11803782 \n", "1 2018-12-08 $KEETA Back 2 Trappin USRC11803782 \n", "2 2018-12-09 $KEETA Back 2 Trappin USRC11803782 \n", "3 2018-12-10 $KEETA Back 2 Trappin USRC11803782 \n", "4 2018-12-11 $KEETA Back 2 Trappin USRC11803782 \n", "5 2018-12-12 $KEETA Back 2 Trappin USRC11803782 \n", "6 2018-12-13 $KEETA Back 2 Trappin USRC11803782 \n", "7 2018-12-14 $KEETA Back 2 Trappin USRC11803782 \n", "8 2018-12-15 $KEETA Back 2 Trappin USRC11803782 \n", "9 2018-12-16 $KEETA Back 2 Trappin USRC11803782 \n", "10 2018-12-17 $KEETA Back 2 Trappin USRC11803782 \n", "11 2018-12-18 $KEETA Back 2 Trappin USRC11803782 \n", "12 2018-12-19 $KEETA Back 2 Trappin USRC11803782 \n", "13 2018-12-20 $KEETA Back 2 Trappin USRC11803782 \n", "14 2018-12-21 $KEETA Back 2 Trappin USRC11803782 \n", "15 2018-12-22 $KEETA Back 2 Trappin USRC11803782 \n", "16 2018-12-23 $KEETA Back 2 Trappin USRC11803782 \n", "17 2018-12-24 $KEETA Back 2 Trappin USRC11803782 \n", "18 2018-12-25 $KEETA Back 2 Trappin USRC11803782 \n", "19 2018-12-26 $KEETA Back 2 Trappin USRC11803782 \n", "20 2018-12-27 $KEETA Back 2 Trappin USRC11803782 \n", "21 2018-12-28 $KEETA Back 2 Trappin USRC11803782 \n", "22 2018-12-29 $KEETA Back 2 Trappin USRC11803782 \n", "23 2018-12-30 $KEETA Back 2 Trappin USRC11803782 \n", "24 2018-12-31 $KEETA Back 2 Trappin USRC11803782 \n", "25 2019-01-01 $KEETA Back 2 Trappin USRC11803782 \n", "26 2019-01-02 $KEETA Back 2 Trappin USRC11803782 \n", "27 2019-01-03 $KEETA Back 2 Trappin USRC11803782 \n", "28 2019-01-04 $KEETA Back 2 Trappin USRC11803782 \n", "29 2019-01-05 $KEETA Back 2 Trappin USRC11803782 \n", "... ... ... ... ... \n", "319232 2019-07-10 teamwork. Afterhours DEQ321900083 \n", "319233 2019-07-11 teamwork. Afterhours DEQ321900083 \n", "319234 2019-07-12 teamwork. Afterhours DEQ321900083 \n", "319235 2019-07-13 teamwork. Afterhours DEQ321900083 \n", "319236 2019-07-14 teamwork. Afterhours DEQ321900083 \n", "319237 2019-07-15 teamwork. Afterhours DEQ321900083 \n", "319238 2019-07-16 teamwork. Afterhours DEQ321900083 \n", "319239 2019-07-17 teamwork. Afterhours DEQ321900083 \n", "319240 2019-07-18 teamwork. Afterhours DEQ321900083 \n", "319241 2019-07-19 teamwork. Afterhours DEQ321900083 \n", "319242 2019-07-20 teamwork. Afterhours DEQ321900083 \n", "319243 2019-07-21 teamwork. Afterhours DEQ321900083 \n", "319244 2019-07-22 teamwork. Afterhours DEQ321900083 \n", "319245 2019-07-23 teamwork. Afterhours DEQ321900083 \n", "319246 2019-07-24 teamwork. Afterhours DEQ321900083 \n", "319247 2019-07-25 teamwork. Afterhours DEQ321900083 \n", "319248 2019-07-26 teamwork. Afterhours DEQ321900083 \n", "319249 2019-07-27 teamwork. Afterhours DEQ321900083 \n", "319250 2019-07-28 teamwork. Afterhours DEQ321900083 \n", "319251 2019-07-29 teamwork. Afterhours DEQ321900083 \n", "319252 2019-07-30 teamwork. Afterhours DEQ321900083 \n", "319253 2019-07-31 teamwork. Afterhours DEQ321900083 \n", "319254 2019-08-01 teamwork. Afterhours DEQ321900083 \n", "319255 2019-08-02 teamwork. Afterhours DEQ321900083 \n", "319256 2019-08-03 teamwork. Afterhours DEQ321900083 \n", "319257 2019-08-04 teamwork. Afterhours DEQ321900083 \n", "319258 2019-08-05 teamwork. Afterhours DEQ321900083 \n", "319259 2019-08-06 teamwork. Afterhours DEQ321900083 \n", "319260 2019-08-07 teamwork. Afterhours DEQ321900083 \n", "319261 2019-08-08 teamwork. Afterhours DEQ321900083 \n", "\n", " min_report_date fin_label_name country_code streams date_diff \n", "0 2018-12-07 RCA Contemporary US 240 0 \n", "1 2018-12-07 RCA Contemporary US 170 1 \n", "2 2018-12-07 RCA Contemporary US 90 2 \n", "3 2018-12-07 RCA Contemporary US 101 3 \n", "4 2018-12-07 RCA Contemporary US 82 4 \n", "5 2018-12-07 RCA Contemporary US 55 5 \n", "6 2018-12-07 RCA Contemporary US 87 6 \n", "7 2018-12-07 RCA Contemporary US 80 7 \n", "8 2018-12-07 RCA Contemporary US 76 8 \n", "9 2018-12-07 RCA Contemporary US 48 9 \n", "10 2018-12-07 RCA Contemporary US 46 10 \n", "11 2018-12-07 RCA Contemporary US 50 11 \n", "12 2018-12-07 RCA Contemporary US 66 12 \n", "13 2018-12-07 RCA Contemporary US 39 13 \n", "14 2018-12-07 RCA Contemporary US 41 14 \n", "15 2018-12-07 RCA Contemporary US 29 15 \n", "16 2018-12-07 RCA Contemporary US 34 16 \n", "17 2018-12-07 RCA Contemporary US 33 17 \n", "18 2018-12-07 RCA Contemporary US 19 18 \n", "19 2018-12-07 RCA Contemporary US 11 19 \n", "20 2018-12-07 RCA Contemporary US 34 20 \n", "21 2018-12-07 RCA Contemporary US 20 21 \n", "22 2018-12-07 RCA Contemporary US 19 22 \n", "23 2018-12-07 RCA Contemporary US 20 23 \n", "24 2018-12-07 RCA Contemporary US 22 24 \n", "25 2018-12-07 RCA Contemporary US 13 25 \n", "26 2018-12-07 RCA Contemporary US 23 26 \n", "27 2018-12-07 RCA Contemporary US 13 27 \n", "28 2018-12-07 RCA Contemporary US 23 28 \n", "29 2018-12-07 RCA Contemporary US 25 29 \n", "... ... ... ... ... ... \n", "319232 2019-05-10 Epic/AJ Mitchelll US 4308 61 \n", "319233 2019-05-10 Epic/AJ Mitchelll US 3932 62 \n", "319234 2019-05-10 Epic/AJ Mitchelll US 3850 63 \n", "319235 2019-05-10 Epic/AJ Mitchelll US 3344 64 \n", "319236 2019-05-10 Epic/AJ Mitchelll US 3036 65 \n", "319237 2019-05-10 Epic/AJ Mitchelll US 3507 66 \n", "319238 2019-05-10 Epic/AJ Mitchelll US 3305 67 \n", "319239 2019-05-10 Epic/AJ Mitchelll US 3181 68 \n", "319240 2019-05-10 Epic/AJ Mitchelll US 3183 69 \n", "319241 2019-05-10 Epic/AJ Mitchelll US 3048 70 \n", "319242 2019-05-10 Epic/AJ Mitchelll US 2591 71 \n", "319243 2019-05-10 Epic/AJ Mitchelll US 2392 72 \n", "319244 2019-05-10 Epic/AJ Mitchelll US 2939 73 \n", "319245 2019-05-10 Epic/AJ Mitchelll US 2980 74 \n", "319246 2019-05-10 Epic/AJ Mitchelll US 2902 75 \n", "319247 2019-05-10 Epic/AJ Mitchelll US 2870 76 \n", "319248 2019-05-10 Epic/AJ Mitchelll US 2632 77 \n", "319249 2019-05-10 Epic/AJ Mitchelll US 2445 78 \n", "319250 2019-05-10 Epic/AJ Mitchelll US 2867 79 \n", "319251 2019-05-10 Epic/AJ Mitchelll US 3455 80 \n", "319252 2019-05-10 Epic/AJ Mitchelll US 3210 81 \n", "319253 2019-05-10 Epic/AJ Mitchelll US 2979 82 \n", "319254 2019-05-10 Epic/AJ Mitchelll US 3021 83 \n", "319255 2019-05-10 Epic/AJ Mitchelll US 3053 84 \n", "319256 2019-05-10 Epic/AJ Mitchelll US 2563 85 \n", "319257 2019-05-10 Epic/AJ Mitchelll US 2308 86 \n", "319258 2019-05-10 Epic/AJ Mitchelll US 2507 87 \n", "319259 2019-05-10 Epic/AJ Mitchelll US 2503 88 \n", "319260 2019-05-10 Epic/AJ Mitchelll US 2652 89 \n", "319261 2019-05-10 Epic/AJ Mitchelll US 2739 90 \n", "\n", "[319262 rows x 9 columns]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df" ] }, { "cell_type": "code", "execution_count": 6, "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", "
0.5streams
date_diff
0127.0
14580.0
23817.0
34200.5
43981.0
\n", "
" ], "text/plain": [ "0.5 streams\n", "date_diff \n", "0 127.0\n", "1 4580.0\n", "2 3817.0\n", "3 4200.5\n", "4 3981.0" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df1 = df.groupby('date_diff').quantile(.5)\n", "df2 = df.groupby('date_diff').quantile(.75)\n", "df3 = df.groupby('date_diff').quantile(1)\n", "df1.head()" ] }, { "cell_type": "code", "execution_count": 7, "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", "
50_pct75_pct100_pct
date_diff
0127.06921.002986620.0
14580.034768.253170763.0
23817.028836.503063053.0
34200.528429.003475151.0
43981.027645.503411678.0
\n", "
" ], "text/plain": [ " 50_pct 75_pct 100_pct\n", "date_diff \n", "0 127.0 6921.00 2986620.0\n", "1 4580.0 34768.25 3170763.0\n", "2 3817.0 28836.50 3063053.0\n", "3 4200.5 28429.00 3475151.0\n", "4 3981.0 27645.50 3411678.0" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "full = pd.merge(df1,df2,on='date_diff')\n", "full = pd.merge(full, df3, on='date_diff')\n", "full = full.rename(columns = {'streams_x':'50_pct', 'streams_y':'75_pct', 'streams':'100_pct'})\n", "full.head()" ] }, { "cell_type": "code", "execution_count": 8, "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", "
report_datetrack_album_artisttrack_nametrack_isrcmin_report_datefin_label_namecountry_codestreamsdate_diff50_pct75_pct100_pct
02018-12-07$KEETABack 2 TrappinUSRC118037822018-12-07RCA ContemporaryUS2400127.06921.02986620.0
12018-12-07$KEETAHip Hop Top 10USRC118037832018-12-07RCA ContemporaryUS2570127.06921.02986620.0
22018-12-07$KEETAHood & Slum NiggaUSRC118037852018-12-07RCA ContemporaryUS2180127.06921.02986620.0
32018-12-07$KEETAHot BoyUSRC118037872018-12-07RCA ContemporaryUS1960127.06921.02986620.0
42018-12-07$KEETAI Got A BagUSRC118037812018-12-07RCA ContemporaryUS2460127.06921.02986620.0
\n", "
" ], "text/plain": [ " report_date track_album_artist track_name track_isrc \\\n", "0 2018-12-07 $KEETA Back 2 Trappin USRC11803782 \n", "1 2018-12-07 $KEETA Hip Hop Top 10 USRC11803783 \n", "2 2018-12-07 $KEETA Hood & Slum Nigga USRC11803785 \n", "3 2018-12-07 $KEETA Hot Boy USRC11803787 \n", "4 2018-12-07 $KEETA I Got A Bag USRC11803781 \n", "\n", " min_report_date fin_label_name country_code streams date_diff 50_pct \\\n", "0 2018-12-07 RCA Contemporary US 240 0 127.0 \n", "1 2018-12-07 RCA Contemporary US 257 0 127.0 \n", "2 2018-12-07 RCA Contemporary US 218 0 127.0 \n", "3 2018-12-07 RCA Contemporary US 196 0 127.0 \n", "4 2018-12-07 RCA Contemporary US 246 0 127.0 \n", "\n", " 75_pct 100_pct \n", "0 6921.0 2986620.0 \n", "1 6921.0 2986620.0 \n", "2 6921.0 2986620.0 \n", "3 6921.0 2986620.0 \n", "4 6921.0 2986620.0 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "with_quantiles = pd.merge(df, full, on='date_diff')\n", "with_quantiles.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": [ "with_quantiles.to_csv('quantile_data_from_2018_10.csv')" ] } ], "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.3" } }, "nbformat": 4, "nbformat_minor": 2 }