{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "Add a new field - days in quarantine? Something to indicate the " ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "from datetime import datetime, timedelta \n", "import os\n", "import seaborn as sns\n", "import matplotlib.pyplot as plt\n", "import glob\n", "from pandas_profiling import ProfileReport\n", "import statsmodels.api as sm" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "def read_multi_files(filepath): #filepath should be in quotes and have an * \n", " source_files = glob.glob(filepath)\n", " li = []\n", " for filename in source_files:\n", " df = pd.read_csv(filename, index_col=None, header=0)\n", " li.append(df)\n", " source = pd.concat(li, axis=0, ignore_index=True)\n", " return source" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "#Bring in and concat files for the different breakdowns (free/paid, source, gender, age)\n", "\n", "#Overall\n", "data = read_multi_files('/users/BARR001/Documents/Apple Finance Corona/finance_apple_total_streams_all.csv')\n", "\n", "#Free/Paid\n", "free_paid = read_multi_files('/users/BARR001/Documents/Apple Finance Corona/finance_apple_free_paid.csv')\n", "\n", "#Source\n", "source = read_multi_files('/users/BARR001/Documents/Apple Finance Corona/finance_apple_srcon_playlist_*.csv')\n", "\n", "#Gender\n", "gender = read_multi_files('/users/BARR001/Documents/Apple Finance Corona/finance_apple_gender.csv')\n", "\n", "#Age\n", "age = read_multi_files('/users/BARR001/Documents/Apple Finance Corona/finance_apple_age.csv')" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['report_date', 'week_end_date_thursday', 'month_end_date',\n", " 'product_family_no', 'product_family_name', 'primary_artist_key',\n", " 'primary_artist_name', 'fin_rollup_name', 'parent_rep_owner_name',\n", " 'project_id', 'track_duration', 'min_date', 'peak_days_after_release',\n", " 'product_count', 'max_streams', 'streams'],\n", " dtype='object')" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data.columns" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "source.loc[source['source_name'] =='Voice', 'source_name'] = 'External'\n", "source.loc[source['source_name'] =='Now Playing', 'source_name'] = 'Other'" ] }, { "cell_type": "code", "execution_count": 9, "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", "
Container TypeLean Forward/Lean BackPlaylist TypeSource of Stream
0PlaylistLean BackEditorialDiscovery
1RadioLean BackEditorial StationDiscovery
2AlbumLean ForwardNot applicableDiscovery
3NoneLean BackNot applicableDiscovery
4RadioLean BackSeeded by Artist/SongDiscovery
\n", "
" ], "text/plain": [ " Container Type Lean Forward/Lean Back Playlist Type \\\n", "0 Playlist Lean Back Editorial \n", "1 Radio Lean Back Editorial Station \n", "2 Album Lean Forward Not applicable \n", "3 None Lean Back Not applicable \n", "4 Radio Lean Back Seeded by Artist/Song \n", "\n", " Source of Stream \n", "0 Discovery \n", "1 Discovery \n", "2 Discovery \n", "3 Discovery \n", "4 Discovery " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Bring in Lean Forward/Back mapping\n", "\n", "lflb = pd.read_excel('/users/BARR001/Documents/Apple Finance Nashville/apple_music_lflb.xlsx')\n", "del lflb['Stream Count']\n", "lflb.head()" ] }, { "cell_type": "code", "execution_count": 10, "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", "
report_dateweek_end_date_thursdaymonth_end_dateproduct_family_noproduct_family_nameprimary_artist_keyprimary_artist_namefin_rollup_nameparent_rep_owner_nameproject_idtotal_streamssrcon_streamssrcon_pctLean Forward/Lean Back
02020-01-262020-01-302020-01-314470205Down With the Clique262827SolangeColumbia Records GroupColumbia Records Group130675031316670.213031Lean Forward
12020-01-312020-02-062020-01-314459320Red Dirt Road1126076Brooks & Dunn with Cody JohnsonSONY MUSIC NASHVILLESony Music Nashville1342483111184610.041464Lean Forward
22020-01-292020-01-302020-01-314535723Massage Seats1126798Freddie Gibbs & MadlibRCA RecordsRCA Records134885625455340.209823Lean Forward
32020-02-032020-02-062020-02-294513260Shhh (Calla')795813MalumaUS LATINSME US LATIN LLC134271533434070.121747Lean Forward
42020-02-032020-02-062020-02-294431931To Noise Making (Sing)882313HozierColumbia Records GroupColumbia Records Group123214237375370.143698Lean Forward
\n", "
" ], "text/plain": [ " report_date week_end_date_thursday month_end_date product_family_no \\\n", "0 2020-01-26 2020-01-30 2020-01-31 4470205 \n", "1 2020-01-31 2020-02-06 2020-01-31 4459320 \n", "2 2020-01-29 2020-01-30 2020-01-31 4535723 \n", "3 2020-02-03 2020-02-06 2020-02-29 4513260 \n", "4 2020-02-03 2020-02-06 2020-02-29 4431931 \n", "\n", " product_family_name primary_artist_key \\\n", "0 Down With the Clique 262827 \n", "1 Red Dirt Road 1126076 \n", "2 Massage Seats 1126798 \n", "3 Shhh (Calla') 795813 \n", "4 To Noise Making (Sing) 882313 \n", "\n", " primary_artist_name fin_rollup_name \\\n", "0 Solange Columbia Records Group \n", "1 Brooks & Dunn with Cody Johnson SONY MUSIC NASHVILLE \n", "2 Freddie Gibbs & Madlib RCA Records \n", "3 Maluma US LATIN \n", "4 Hozier Columbia Records Group \n", "\n", " parent_rep_owner_name project_id total_streams srcon_streams \\\n", "0 Columbia Records Group 1306750 3131 667 \n", "1 Sony Music Nashville 1342483 11118 461 \n", "2 RCA Records 1348856 2545 534 \n", "3 SME US LATIN LLC 1342715 3343 407 \n", "4 Columbia Records Group 1232142 3737 537 \n", "\n", " srcon_pct Lean Forward/Lean Back \n", "0 0.213031 Lean Forward \n", "1 0.041464 Lean Forward \n", "2 0.209823 Lean Forward \n", "3 0.121747 Lean Forward \n", "4 0.143698 Lean Forward " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "lean = source.merge(lflb, left_on=['source_name','container_name','playlist_type_name'], right_on=['Source of Stream','Container Type','Playlist Type'])\n", "del lean['Container Type']\n", "del lean['Playlist Type']\n", "del lean['Source of Stream']\n", "del lean['source_name']\n", "del lean['container_name']\n", "del lean['playlist_type_name']\n", "lean.head()" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "data = data.fillna(value={'fin_rollup_name':'Unknown'})\n", "free_paid = free_paid.fillna(value={'fin_rollup_name':'Unknown'})\n", "source = source.fillna(value={'fin_rollup_name':'Unknown'})\n", "gender = gender.fillna(value={'fin_rollup_name':'Unknown'})\n", "age = age.fillna(value={'fin_rollup_name':'Unknown'})\n", "lean = lean.fillna(value={'fin_rollup_name':'Unknown'})" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [], "source": [ "def etl_to_pivot(df,fields_list,vals):\n", " df_pivot = pd.pivot_table(df, values=vals, index=['report_date', 'week_end_date_thursday', \n", " 'month_end_date','product_family_no', 'product_family_name','primary_artist_key',\n", " 'primary_artist_name','fin_rollup_name','parent_rep_owner_name','project_id'],\n", " columns=fields_list, aggfunc=np.sum, fill_value=0)\n", " df_pivot = df_pivot.reset_index()\n", " if len(fields_list)>=2:\n", " df_pivot.columns = df_pivot.columns.to_series().str.join('_')\n", " df_pivot = df_pivot.rename(columns={'report_date_':'report_date', \n", " 'week_end_date_thursday_':'week_end_date_thursday', \n", " 'month_end_date_':'month_end_date',\n", " 'product_family_no_':'product_family_no', \n", " 'product_family_name_':'product_family_name',\n", " 'primary_artist_key_':'primary_artist_key',\n", " 'primary_artist_name_':'primary_artist_name',\n", " 'fin_rollup_name_':'fin_rollup_name',\n", " 'parent_rep_owner_name_':'parent_rep_owner_name',\n", " 'project_id_':'project_id'})\n", " else:\n", " df_pivot\n", " return df_pivot" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "#Pivot data for all groups\n", "\n", "#Free/Paid\n", "free_paid_pivot = etl_to_pivot(free_paid, ['membership_code'], 'free_paid_pct')\n", "\n", "#Source\n", "source_pivot = etl_to_pivot(source, ['source_name','container_name'], 'srcon_pct')\n", "\n", "#Gender\n", "gender_pivot = etl_to_pivot(gender, ['gender_name'], 'gender_pct')\n", "\n", "#Age\n", "age_pivot = etl_to_pivot(age, ['age_group_name'], 'age_pct')\n", "\n", "#Lean Type\n", "lean_pivot = etl_to_pivot(lean, ['Lean Forward/Lean Back'], 'srcon_pct')" ] }, { "cell_type": "code", "execution_count": 14, "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", "
report_dateweek_end_date_thursdaymonth_end_dateproduct_family_noproduct_family_nameprimary_artist_keyprimary_artist_namefin_rollup_nameparent_rep_owner_nameproject_id...Search_NoneSearch_PlaylistSearch_RadioFemaleMaleunknown_genderLean BackLean ForwardFreePaid
02019-04-132019-04-182019-04-304376434Una Vez Más1048416Manuel TurizoUS LATINSME US LATIN LLC1323151...0.0831520.0021770.0008710.4806270.3635180.0740100.1188510.8480630.0979540.902046
12019-05-162019-05-162019-05-314422229Rain and Thunder Sounds for Sleeping1119172Sleepy JohnCOMMERCIAL MUSIC GROUPSME Sweden AB1349979...0.0000000.3076920.0000000.3223440.2344320.0000000.5421250.4578750.0952380.904762
22019-06-122019-06-132019-06-304422106A Light Storm1119172Sleepy JohnCOMMERCIAL MUSIC GROUPSME Sweden AB1349979...0.0000000.3126680.0000000.4043130.2587600.0000000.5714290.4285710.0970350.902965
32019-07-052019-07-112019-07-314454597Casadas1027352El PolacoUS LATINSME Argentina S.A.1351402...0.0060980.3475610.0000000.1341460.2195120.0000000.6890240.3109760.1036590.896341
42019-07-302019-08-012019-07-314404063Thunder & Rain Sounds, Pt. 041119172Sleepy JohnCOMMERCIAL MUSIC GROUPSME Sweden AB1349092...0.0000000.3953900.0000000.4503550.2588650.0106380.7163120.2836880.0868790.913121
\n", "

5 rows × 54 columns

\n", "
" ], "text/plain": [ " report_date week_end_date_thursday month_end_date product_family_no \\\n", "0 2019-04-13 2019-04-18 2019-04-30 4376434 \n", "1 2019-05-16 2019-05-16 2019-05-31 4422229 \n", "2 2019-06-12 2019-06-13 2019-06-30 4422106 \n", "3 2019-07-05 2019-07-11 2019-07-31 4454597 \n", "4 2019-07-30 2019-08-01 2019-07-31 4404063 \n", "\n", " product_family_name primary_artist_key \\\n", "0 Una Vez Más 1048416 \n", "1 Rain and Thunder Sounds for Sleeping 1119172 \n", "2 A Light Storm 1119172 \n", "3 Casadas 1027352 \n", "4 Thunder & Rain Sounds, Pt. 04 1119172 \n", "\n", " primary_artist_name fin_rollup_name parent_rep_owner_name \\\n", "0 Manuel Turizo US LATIN SME US LATIN LLC \n", "1 Sleepy John COMMERCIAL MUSIC GROUP SME Sweden AB \n", "2 Sleepy John COMMERCIAL MUSIC GROUP SME Sweden AB \n", "3 El Polaco US LATIN SME Argentina S.A. \n", "4 Sleepy John COMMERCIAL MUSIC GROUP SME Sweden AB \n", "\n", " project_id ... Search_None Search_Playlist Search_Radio Female \\\n", "0 1323151 ... 0.083152 0.002177 0.000871 0.480627 \n", "1 1349979 ... 0.000000 0.307692 0.000000 0.322344 \n", "2 1349979 ... 0.000000 0.312668 0.000000 0.404313 \n", "3 1351402 ... 0.006098 0.347561 0.000000 0.134146 \n", "4 1349092 ... 0.000000 0.395390 0.000000 0.450355 \n", "\n", " Male unknown_gender Lean Back Lean Forward Free Paid \n", "0 0.363518 0.074010 0.118851 0.848063 0.097954 0.902046 \n", "1 0.234432 0.000000 0.542125 0.457875 0.095238 0.904762 \n", "2 0.258760 0.000000 0.571429 0.428571 0.097035 0.902965 \n", "3 0.219512 0.000000 0.689024 0.310976 0.103659 0.896341 \n", "4 0.258865 0.010638 0.716312 0.283688 0.086879 0.913121 \n", "\n", "[5 rows x 54 columns]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Merge all pivot tables\n", "\n", "temp = data.merge(age_pivot, on=['report_date', 'week_end_date_thursday', 'month_end_date','product_family_no', 'product_family_name','primary_artist_key','primary_artist_name','fin_rollup_name','parent_rep_owner_name','project_id'])\n", "temp = temp.merge(source_pivot, on=['report_date', 'week_end_date_thursday', 'month_end_date','product_family_no', 'product_family_name','primary_artist_key','primary_artist_name','fin_rollup_name','parent_rep_owner_name','project_id'])\n", "temp = temp.merge(gender_pivot, on=['report_date', 'week_end_date_thursday', 'month_end_date','product_family_no', 'product_family_name','primary_artist_key','primary_artist_name','fin_rollup_name','parent_rep_owner_name','project_id'])\n", "temp = temp.merge(lean_pivot, on=['report_date', 'week_end_date_thursday', 'month_end_date','product_family_no', 'product_family_name','primary_artist_key','primary_artist_name','fin_rollup_name','parent_rep_owner_name','project_id'])\n", "full = temp.merge(free_paid_pivot, on=['report_date', 'week_end_date_thursday', 'month_end_date','product_family_no', 'product_family_name','primary_artist_key','primary_artist_name','fin_rollup_name','parent_rep_owner_name','project_id'])\n", "full = full.rename(columns={'Unknown_x': 'unknown_age','Unknown_y': 'unknown_gender'})\n", "full.head()" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": [ "#Fix weird data for Matt Stell (may want advice on how best to deal with him)\n", "\n", "full.loc[(full.primary_artist_name=='Matt Stell'),'fin_rollup_name']='SONY MUSIC NASHVILLE'\n", "full.loc[(full.primary_artist_name=='Matt Stell feat. Jimmie Allen'),'fin_rollup_name']='SONY MUSIC NASHVILLE'" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['report_date', 'week_end_date_thursday', 'month_end_date',\n", " 'product_family_no', 'product_family_name', 'primary_artist_key',\n", " 'primary_artist_name', 'fin_rollup_name', 'parent_rep_owner_name',\n", " 'project_id', 'track_duration', 'min_date', 'peak_days_after_release',\n", " 'product_count', 'max_streams', 'streams', '18-24', '25-34', '35-44',\n", " '45-54', '55-64', '65+', 'unknown_age', 'Discovery_Album',\n", " 'Discovery_None', 'Discovery_Playlist', 'Discovery_Radio',\n", " 'External_Album', 'External_None', 'External_Playlist',\n", " 'External_Radio', 'MusicKit_Album', 'MusicKit_None',\n", " 'MusicKit_Playlist', 'MusicKit_Radio', 'My Music_Album',\n", " 'My Music_None', 'My Music_Playlist', 'My Music_Radio', 'Other_Album',\n", " 'Other_None', 'Other_Playlist', 'Other_Radio', 'Search_Album',\n", " 'Search_None', 'Search_Playlist', 'Search_Radio', 'Female', 'Male',\n", " 'unknown_gender', 'Lean Back', 'Lean Forward', 'Free', 'Paid'],\n", " dtype='object')" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "full.columns" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "report_date datetime64[ns]\n", "week_end_date_thursday datetime64[ns]\n", "month_end_date datetime64[ns]\n", "product_family_no int64\n", "product_family_name object\n", "primary_artist_key int64\n", "primary_artist_name object\n", "fin_rollup_name object\n", "parent_rep_owner_name object\n", "project_id int64\n", "track_duration int64\n", "min_date datetime64[ns]\n", "peak_days_after_release int64\n", "product_count int64\n", "max_streams int64\n", "streams int64\n", "18-24 float64\n", "25-34 float64\n", "35-44 float64\n", "45-54 float64\n", "55-64 float64\n", "65+ float64\n", "unknown_age float64\n", "Discovery_Album float64\n", "Discovery_None float64\n", "Discovery_Playlist float64\n", "Discovery_Radio float64\n", "External_Album float64\n", "External_None float64\n", "External_Playlist float64\n", "External_Radio float64\n", "MusicKit_Album float64\n", "MusicKit_None float64\n", "MusicKit_Playlist float64\n", "MusicKit_Radio float64\n", "My Music_Album float64\n", "My Music_None float64\n", "My Music_Playlist float64\n", "My Music_Radio float64\n", "Other_Album float64\n", "Other_None float64\n", "Other_Playlist float64\n", "Other_Radio float64\n", "Search_Album float64\n", "Search_None float64\n", "Search_Playlist float64\n", "Search_Radio float64\n", "Female float64\n", "Male float64\n", "unknown_gender float64\n", "Lean Back float64\n", "Lean Forward float64\n", "Free float64\n", "Paid float64\n", "days_after_release timedelta64[ns]\n", "dtype: object" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "full['report_date'] = pd.to_datetime(full['report_date'])\n", "full['week_end_date_thursday'] = pd.to_datetime(full['week_end_date_thursday'])\n", "full['month_end_date'] = pd.to_datetime(full['month_end_date'])\n", "full['min_date'] = pd.to_datetime(full['min_date'])\n", "full['days_after_release']=full['report_date']-full['min_date']\n", "full.dtypes" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "full['days_after_release'] = full['days_after_release'].dt.days.astype('int16')\n", "full['day_of_week'] = full['report_date'].dt.dayofweek" ] }, { "cell_type": "code", "execution_count": 19, "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", "
report_dateweek_end_date_thursdaymonth_end_dateproduct_family_noproduct_family_nameprimary_artist_keyprimary_artist_namefin_rollup_nameparent_rep_owner_nameproject_id...Search_RadioFemaleMaleunknown_genderLean BackLean ForwardFreePaiddays_after_releaseday_of_week
02019-04-132019-04-182019-04-304376434Una Vez Más1048416Manuel TurizoUS LATINSME US LATIN LLC1323151...0.0008710.4806270.3635180.0740100.1188510.8480630.0979540.9020461855
12019-05-162019-05-162019-05-314422229Rain and Thunder Sounds for Sleeping1119172Sleepy JohnCOMMERCIAL MUSIC GROUPSME Sweden AB1349979...0.0000000.3223440.2344320.0000000.5421250.4578750.0952380.9047621503
22019-06-122019-06-132019-06-304422106A Light Storm1119172Sleepy JohnCOMMERCIAL MUSIC GROUPSME Sweden AB1349979...0.0000000.4043130.2587600.0000000.5714290.4285710.0970350.9029651792
32019-07-052019-07-112019-07-314454597Casadas1027352El PolacoUS LATINSME Argentina S.A.1351402...0.0000000.1341460.2195120.0000000.6890240.3109760.1036590.8963411414
42019-07-302019-08-012019-07-314404063Thunder & Rain Sounds, Pt. 041119172Sleepy JohnCOMMERCIAL MUSIC GROUPSME Sweden AB1349092...0.0000000.4503550.2588650.0106380.7163120.2836880.0868790.9131211871
\n", "

5 rows × 56 columns

\n", "
" ], "text/plain": [ " report_date week_end_date_thursday month_end_date product_family_no \\\n", "0 2019-04-13 2019-04-18 2019-04-30 4376434 \n", "1 2019-05-16 2019-05-16 2019-05-31 4422229 \n", "2 2019-06-12 2019-06-13 2019-06-30 4422106 \n", "3 2019-07-05 2019-07-11 2019-07-31 4454597 \n", "4 2019-07-30 2019-08-01 2019-07-31 4404063 \n", "\n", " product_family_name primary_artist_key \\\n", "0 Una Vez Más 1048416 \n", "1 Rain and Thunder Sounds for Sleeping 1119172 \n", "2 A Light Storm 1119172 \n", "3 Casadas 1027352 \n", "4 Thunder & Rain Sounds, Pt. 04 1119172 \n", "\n", " primary_artist_name fin_rollup_name parent_rep_owner_name \\\n", "0 Manuel Turizo US LATIN SME US LATIN LLC \n", "1 Sleepy John COMMERCIAL MUSIC GROUP SME Sweden AB \n", "2 Sleepy John COMMERCIAL MUSIC GROUP SME Sweden AB \n", "3 El Polaco US LATIN SME Argentina S.A. \n", "4 Sleepy John COMMERCIAL MUSIC GROUP SME Sweden AB \n", "\n", " project_id ... Search_Radio Female Male unknown_gender \\\n", "0 1323151 ... 0.000871 0.480627 0.363518 0.074010 \n", "1 1349979 ... 0.000000 0.322344 0.234432 0.000000 \n", "2 1349979 ... 0.000000 0.404313 0.258760 0.000000 \n", "3 1351402 ... 0.000000 0.134146 0.219512 0.000000 \n", "4 1349092 ... 0.000000 0.450355 0.258865 0.010638 \n", "\n", " Lean Back Lean Forward Free Paid days_after_release \\\n", "0 0.118851 0.848063 0.097954 0.902046 185 \n", "1 0.542125 0.457875 0.095238 0.904762 150 \n", "2 0.571429 0.428571 0.097035 0.902965 179 \n", "3 0.689024 0.310976 0.103659 0.896341 141 \n", "4 0.716312 0.283688 0.086879 0.913121 187 \n", "\n", " day_of_week \n", "0 5 \n", "1 3 \n", "2 2 \n", "3 4 \n", "4 1 \n", "\n", "[5 rows x 56 columns]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "full.head()" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [], "source": [ "del full['report_date']\n", "del full['week_end_date_thursday']\n", "del full['month_end_date']\n", "del full['product_family_no']\n", "del full['product_family_name']\n", "del full['primary_artist_key']\n", "del full['primary_artist_name']\n", "del full['fin_rollup_name']\n", "del full['parent_rep_owner_name']\n", "del full['project_id']\n", "del full['min_date']\n", "del full['max_streams']\n", "del full['peak_days_after_release']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "###### BRING IN PENNY RATE" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['track_duration', 'product_count', 'streams', '18-24', '25-34', '35-44',\n", " '45-54', '55-64', '65+', 'unknown_age', 'Discovery_Album',\n", " 'Discovery_None', 'Discovery_Playlist', 'Discovery_Radio',\n", " 'External_Album', 'External_None', 'External_Playlist',\n", " 'External_Radio', 'MusicKit_Album', 'MusicKit_None',\n", " 'MusicKit_Playlist', 'MusicKit_Radio', 'My Music_Album',\n", " 'My Music_None', 'My Music_Playlist', 'My Music_Radio', 'Other_Album',\n", " 'Other_None', 'Other_Playlist', 'Other_Radio', 'Search_Album',\n", " 'Search_None', 'Search_Playlist', 'Search_Radio', 'Female', 'Male',\n", " 'unknown_gender', 'Lean Back', 'Lean Forward', 'Free', 'Paid',\n", " 'days_after_release', 'day_of_week'],\n", " dtype='object')" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "full.columns\n", "# full.shape" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(1177756, 43)" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "train, validate, test = np.split(full.sample(frac=1), [int(.6*len(full)), int(.8*len(full))])\n", "train.shape" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [], "source": [ "full_predictors = train.drop(\"streams\", axis=1)\n", "full_labels = train[\"streams\"].copy()" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "track_duration int64\n", "product_count int64\n", "18-24 float64\n", "25-34 float64\n", "35-44 float64\n", "45-54 float64\n", "55-64 float64\n", "65+ float64\n", "unknown_age float64\n", "Discovery_Album float64\n", "Discovery_None float64\n", "Discovery_Playlist float64\n", "Discovery_Radio float64\n", "External_Album float64\n", "External_None float64\n", "External_Playlist float64\n", "External_Radio float64\n", "MusicKit_Album float64\n", "MusicKit_None float64\n", "MusicKit_Playlist float64\n", "MusicKit_Radio float64\n", "My Music_Album float64\n", "My Music_None float64\n", "My Music_Playlist float64\n", "My Music_Radio float64\n", "Other_Album float64\n", "Other_None float64\n", "Other_Playlist float64\n", "Other_Radio float64\n", "Search_Album float64\n", "Search_None float64\n", "Search_Playlist float64\n", "Search_Radio float64\n", "Female float64\n", "Male float64\n", "unknown_gender float64\n", "Lean Back float64\n", "Lean Forward float64\n", "Free float64\n", "Paid float64\n", "days_after_release int16\n", "day_of_week int64\n", "dtype: object" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# full_predictors['days_after_release'] = full_predictors['days_after_release'].dt.days.astype('int16')\n", "full_predictors.dtypes" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "LinearRegression(copy_X=True, fit_intercept=True, n_jobs=None, normalize=False)" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from sklearn.linear_model import LinearRegression\n", "\n", "lin_reg = LinearRegression()\n", "lin_reg.fit(full_predictors, full_labels)" ] }, { "cell_type": "code", "execution_count": 34, "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", "
track_durationproduct_count18-2425-3435-4445-5455-6465+unknown_ageDiscovery_Album...Search_RadioFemaleMaleunknown_genderLean BackLean ForwardFreePaiddays_after_releaseday_of_week
3276787810.0262580.4354490.1991250.0218820.00.00.0000000.032823...0.00.0000000.6827130.0000000.0153170.9846830.2450770.754923232
10596226710.0205760.2469140.2386830.0823050.00.00.0000000.024691...0.00.2510290.3374490.0000000.4732510.5267490.0864200.9135803485
60668223110.0000000.0462960.1388890.0555560.00.00.0000000.000000...0.00.0000000.2407410.0000000.5277780.4722220.0740740.925926681
72786119920.0304050.2128380.0439190.0337840.00.00.0000000.013514...0.00.0777030.2432430.0000000.2939190.7027030.0743240.925676680
148745722110.2563510.3406470.0762120.0496540.00.00.0519630.000000...0.00.2043880.5103930.0600460.3741340.6154730.0912240.908776703
\n", "

5 rows × 42 columns

\n", "
" ], "text/plain": [ " track_duration product_count 18-24 25-34 35-44 \\\n", "327678 78 1 0.026258 0.435449 0.199125 \n", "1059622 67 1 0.020576 0.246914 0.238683 \n", "606682 231 1 0.000000 0.046296 0.138889 \n", "727861 199 2 0.030405 0.212838 0.043919 \n", "1487457 221 1 0.256351 0.340647 0.076212 \n", "\n", " 45-54 55-64 65+ unknown_age Discovery_Album ... \\\n", "327678 0.021882 0.0 0.0 0.000000 0.032823 ... \n", "1059622 0.082305 0.0 0.0 0.000000 0.024691 ... \n", "606682 0.055556 0.0 0.0 0.000000 0.000000 ... \n", "727861 0.033784 0.0 0.0 0.000000 0.013514 ... \n", "1487457 0.049654 0.0 0.0 0.051963 0.000000 ... \n", "\n", " Search_Radio Female Male unknown_gender Lean Back \\\n", "327678 0.0 0.000000 0.682713 0.000000 0.015317 \n", "1059622 0.0 0.251029 0.337449 0.000000 0.473251 \n", "606682 0.0 0.000000 0.240741 0.000000 0.527778 \n", "727861 0.0 0.077703 0.243243 0.000000 0.293919 \n", "1487457 0.0 0.204388 0.510393 0.060046 0.374134 \n", "\n", " Lean Forward Free Paid days_after_release day_of_week \n", "327678 0.984683 0.245077 0.754923 23 2 \n", "1059622 0.526749 0.086420 0.913580 348 5 \n", "606682 0.472222 0.074074 0.925926 68 1 \n", "727861 0.702703 0.074324 0.925676 68 0 \n", "1487457 0.615473 0.091224 0.908776 70 3 \n", "\n", "[5 rows x 42 columns]" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "some_data" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Predictions: [ 7189.9418692 -4837.55602879 3991.12639798 1155.53616009\n", " 15698.3389665 ]\n", "Labels: [457, 243, 108, 296, 866]\n" ] } ], "source": [ "some_data = full_predictors.iloc[:5]\n", "some_labels = full_labels.iloc[:5]\n", "# some_data_prepared = full_pipeline.transform(some_data)\n", "print(\"Predictions:\", lin_reg.predict(some_data))\n", "print(\"Labels:\", list(some_labels))" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "44855.029840166" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from sklearn.metrics import mean_squared_error\n", "full_predictions = lin_reg.predict(full_predictors)\n", "lin_mse = mean_squared_error(full_labels, full_predictions)\n", "lin_rmse = np.sqrt(lin_mse)\n", "lin_rmse" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "DecisionTreeRegressor(criterion='mse', max_depth=None, max_features=None,\n", " max_leaf_nodes=None, min_impurity_decrease=0.0,\n", " min_impurity_split=None, min_samples_leaf=1,\n", " min_samples_split=2, min_weight_fraction_leaf=0.0,\n", " presort=False, random_state=None, splitter='best')" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from sklearn.tree import DecisionTreeRegressor\n", "\n", "tree_reg = DecisionTreeRegressor()\n", "tree_reg.fit(full_predictors, full_labels)" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0.0" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tree_predictions = tree_reg.predict(full_predictors)\n", "tree_mse = mean_squared_error(full_labels, tree_predictions)\n", "tree_rmse = np.sqrt(tree_mse)\n", "tree_rmse" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "array([26841.19075867, 21404.38404744, 21831.39851661, 23216.4157723 ,\n", " 21359.89049817, 31031.83772656, 22945.59478838, 28478.79366929,\n", " 36322.43311206, 18890.1324316 ])" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from sklearn.model_selection import cross_val_score\n", "scores = cross_val_score(tree_reg, full_predictors, full_labels,\n", " scoring=\"neg_mean_squared_error\", cv=10)\n", "tree_rmse_scores = np.sqrt(-scores)\n", "tree_rmse_scores" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Scores: [26841.19075867 21404.38404744 21831.39851661 23216.4157723\n", " 21359.89049817 31031.83772656 22945.59478838 28478.79366929\n", " 36322.43311206 18890.1324316 ]\n", "Mean: 25232.207132107258\n", "Standard deviation: 5103.767892608817\n" ] } ], "source": [ "def display_scores(scores):\n", " print(\"Scores:\", scores)\n", " print(\"Mean:\", scores.mean())\n", " print(\"Standard deviation:\", scores.std())\n", "display_scores(tree_rmse_scores)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [], "source": [ "# Will use this when needing to split data, but first need to figure out how we're rolling up the data\n", "def split_train_test(data, test_ratio):\n", " shuffled_indices = np.random.permutation(len(data))\n", " test_set_size = int(len(data) * test_ratio)\n", " test_indices = shuffled_indices[:test_set_size]\n", " train_indices = shuffled_indices[test_set_size:]\n", " return data.iloc[train_indices], data.iloc[test_indices]\n", "\n", "train_set, test_set = split_train_test(full, .2)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "\n", "You could just use sklearn.model_selection.train_test_split twice. First to split to train, test and then split train again into validation and train. Something like this:\n", "\n", " X_train, X_test, y_train, y_test \n", " = train_test_split(X, y, test_size=0.2, random_state=1)\n", "\n", " X_train, X_val, y_train, y_val \n", " = train_test_split(X_train, y_train, test_size=0.2, random_state=1)" ] } ], "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.5" } }, "nbformat": 4, "nbformat_minor": 2 }