{ "cells": [ { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "import matplotlib.pyplot as plt\n", "%matplotlib inline\n", "import seaborn as sns\n", "import numpy as np\n", "import math\n", "import folium\n", "from folium import plugins\n", "from pandas.plotting import table\n", "from IPython.display import display\n", "from IPython.display import HTML\n", "import io\n", "from PIL import Image\n", "\n", "\n", "%run ./utils.ipynb\n", "\n", "\n", "# Better aesthetics - https://seaborn.pydata.org/tutorial/aesthetics.html\n", "\n", "# switch to seaborn defaults,\n", "# Note that in versions of seaborn prior to 0.8, \n", "# set() was called on import. On later versions, it must be explicitly invoked).\n", "sns.set()\n", "\n", "# There are five preset seaborn themes: darkgrid, whitegrid, dark, white, and ticks. \n", "sns.set_style(\"whitegrid\", {'axes.grid': False})\n", "sns.set_color_codes('muted')\n", "# The four preset contexts in order of relative size, are paper, notebook, talk, and poster. The notebook style is the default,\n", "sns.set_context(\"paper\")\n", "# Some plots benefit from offsetting the spines(lines arund the plot) away from the data\n", "sns.despine()\n", "pd.set_option('display.width', 1000)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "import selenium\n", "import io\n", "from PIL import Image" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: .\n", " \"\"\")\n" ] } ], "source": [ "engine = get_rds_engine()\n", "schema='bd345f915775993a4d3de1dae65b93b067ad69dde4286a1e1639e5cd2'" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "# function to convert dataframe into PNG\n", "# input dataframe, customer name and plot file name\n", "def df_to_png(df, customer,plot_title):\n", " plt.figure()\n", " # set fig size\n", " fig, ax = plt.subplots(figsize=(8, 3)) \n", " # no axes\n", " ax.xaxis.set_visible(False) \n", " ax.yaxis.set_visible(False) \n", " # no frame\n", " ax.set_frame_on(False) \n", " # plot table\n", " tab = table(ax, df, loc='upper right') \n", " # set font manually\n", " tab.auto_set_font_size(False)\n", " tab.set_fontsize(13) \n", " # save the result\n", " plot_location = f'images/{customer}_{plot_title}.png'\n", " plt.savefig(plot_location)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "# https://janakiev.com/blog/gps-points-distance-python/\n", "# function for calculating difference in km for two gps points\n", "def haversine(lat1, lon1, lat2, lon2):\n", " R = 6372800 # Earth radius in meters\n", " \n", " phi1, phi2 = math.radians(lat1), math.radians(lat2) \n", " dphi = math.radians(lat2 - lat1)\n", " dlambda = math.radians(lon2 - lon1)\n", " \n", " a = math.sin(dphi/2)**2 + \\\n", " math.cos(phi1)*math.cos(phi2)*math.sin(dlambda/2)**2\n", " # divide by 1000 to get distance in kilometers\n", " return 2*R*math.atan2(math.sqrt(a), math.sqrt(1 - a))/1000" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "# function for drawing world choropleth map\n", "def plot_world_choro(df,customer):\n", " \n", " bins = list(df['count'].quantile([0, 0.25, 0.5, 0.75, 1]))\n", " # bins = [1,2,6,83,22280,22289]\n", " country_geo = 'world_region.geojson'\n", " m = folium.Map(location=[50, 0], zoom_start=1,zoom_control=False,scrollWheelZoom=False,dragging=False)\n", " print(bins)\n", " \n", " folium.Choropleth(\n", " geo_data=country_geo,\n", " name='choropleth',\n", " data=df,\n", " columns=['country', 'count'],\n", " key_on='feature.properties.name',\n", " fill_color='YlGn',\n", " fill_opacity=0.7,\n", " line_opacity=0.2,\n", " nan_fill_color='gray',\n", " nan_fill_opacity=0.4,\n", " legend_name='Fan count in different countries',\n", " bins=bins).add_to(m)\n", " \n", " world_plot_location = f'images/{customer}_world_choro_plot.png'\n", " #img_data = m._to_png(5)\n", " #img = Image.open(io.BytesIO(img_data))\n", " #img.save('world_choro_plot.png')\n", " # html_string = m.get_root().render()\n", " # print(html_string)\n", " return m" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [], "source": [ "# function for drawing us states choropleth map\n", "def plot_us_states_choro(df,customer):\n", " \n", " inputDf = df[df['country']=='United States']\n", " bins = list(inputDf['count'].quantile([0, 0.25, 0.5, 0.75, 1]))\n", " \n", " # binsa = pd.qcut(inputDf['count'],10)\n", " # binsa = list(binsa.sort_index())\n", "\n", "\n", " print(bins)\n", " # print(binsa)\n", " \n", " country_geo = 'us_states.geojson'\n", " m = folium.Map(location=[38.025265, -101.284950], zoom_start=3.5,zoom_control=False,scrollWheelZoom=False,dragging=False)\n", "\n", " folium.Choropleth(\n", " geo_data=country_geo,\n", " name='choropleth',\n", " data=inputDf,\n", " columns=['administrative_area_level_1', 'count'],\n", " key_on='feature.properties.NAME',\n", " fill_color='YlGn',\n", " fill_opacity=0.7,\n", " line_opacity=0.2,\n", " nan_fill_color='gray',\n", " nan_fill_opacity=0.4,\n", " legend_name='Fan count in different states',\n", " bins=bins).add_to(m)\n", " \n", " return m" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [], "source": [ "# function for drawing us states choropleth map\n", "def plot_us_counties_choro(df,customer):\n", " \n", " inputDf = df[df['country']=='United States']\n", " \n", " inputDf['us_state'] = inputDf['administrative_area_level_2'].str.replace(' County', ', ')\n", " inputDf['us_state'] = inputDf['us_state']+inputDf['administrative_area_level_1']\n", " bins = list(inputDf['count'].quantile([0, 0.25, 0.5, 0.75, 1]))\n", " country_geo = 'us_county.geojson'\n", " m = folium.Map(location=[38.025265, -101.284950], zoom_start=3.5,zoom_control=False,scrollWheelZoom=False,dragging=False)\n", "\n", " folium.Choropleth(\n", " geo_data=country_geo,\n", " name='choropleth',\n", " data=inputDf,\n", " columns=['us_state', 'count'],\n", " key_on='feature.properties.COUNTY_STATE_NAME',\n", " fill_color='YlGn',\n", " fill_opacity=0.7,\n", " line_opacity=0.2,\n", " nan_fill_color='gray',\n", " nan_fill_opacity=0.4,\n", " legend_name='Fan count in different counties',\n", " bins=bins).add_to(m)\n", " \n", " return m" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# function for drawing us cities as points on map\n", "# https://medium.com/@madhuramiah/geographic-plotting-with-python-folium-2f235cc167b7\n", "# if input is RFM segment then rfm_segment table is used\n", "# if input is cluster nr then merch_clusters table is used- this contains clusters from clustering RFM input data\n", "# if input is 'x' then no segmenting or clustering is done- general city map is generated\n", "def plot_us_cities(schema,customer,rfm_segment):\n", " engine = get_rds_engine() \n", " print(rfm_segment)\n", " input_list = (0,1,2,3,4,5,6,7,8,9,10)\n", " \n", " if rfm_segment in input_list:\n", " query = f\"\"\"\n", " select count(s.fan_id) as size,\n", " (count(fag.fan_id)*100 / (select count(*) From {schema}.fan_address_geocoded fag\n", " inner join {schema}.top1000_us_cities t on t.\"City\"= fag.locality\n", " and t.\"State\"=fag.administrative_area_level_1)) as percentage,\n", " t.\"City\", t.\"Latitude\", t.\"Longitude\" from {schema}.merch_clusters s \n", " inner join {schema}.fan_address_geocoded fag on s.fan_id=fag.fan_id\n", " inner join {schema}.top1000_us_cities t on t.\"City\"= fag.locality\n", " and t.\"State\"=fag.administrative_area_level_1 where s.cluster='{rfm_segment}'\n", " group by t.\"City\", t.\"Latitude\", t.\"Longitude\" order by 1 desc\n", " \"\"\"\n", " elif rfm_segment=='x':\n", " query = f\"\"\"\n", " select count(fag.fan_id) as size,\n", " (count(fag.fan_id)*100 / (select count(*) From {schema}.fan_address_geocoded fag\n", " inner join {schema}.top1000_us_cities t on t.\"City\"= fag.locality\n", " and t.\"State\"=fag.administrative_area_level_1)) as percentage,\n", " t.\"City\", t.\"Latitude\", t.\"Longitude\" from \n", " {schema}.fan_address_geocoded fag\n", " inner join {schema}.top1000_us_cities t on t.\"City\"= fag.locality\n", " and t.\"State\"=fag.administrative_area_level_1 group by t.\"City\", t.\"Latitude\", t.\"Longitude\" order by 1 desc\n", " \"\"\"\n", " else:\n", " query = f\"\"\"\n", " select count(s.fan_id) as size,\n", " (count(fag.fan_id)*100 / (select count(*) From {schema}.fan_address_geocoded fag\n", " inner join {schema}.top1000_us_cities t on t.\"City\"= fag.locality\n", " and t.\"State\"=fag.administrative_area_level_1)) as percentage,\n", " t.\"City\", t.\"Latitude\", t.\"Longitude\" from {schema}.rfm_segments s \n", " inner join {schema}.fan_address_geocoded fag on s.fan_id=fag.fan_id\n", " inner join {schema}.top1000_us_cities t on t.\"City\"= fag.locality\n", " and t.\"State\"=fag.administrative_area_level_1 where s.rfm_segment='{rfm_segment}'\n", " group by t.\"City\", t.\"Latitude\", t.\"Longitude\" order by 1 desc\n", " \"\"\"\n", " loc = pd.read_sql(query, engine)\n", " print(loc[['City', 'size','percentage']] .head(10))\n", " m=folium.Map([38.025265, -101.284950],zoom_start=3.5)\n", " \n", " for lat,lon,area,size in zip(loc['Latitude'],loc['Longitude'],loc['City'],loc['size']):\n", " folium.CircleMarker([lat, lon],\n", " popup=area,\n", " radius=size,\n", " color='#eb7a34',\n", " fill=True,\n", " fill_opacity=0.8\n", " ).add_to(m)\n", " return m" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "# function for drawing us states choropleth map\n", "def plot_australia_choro(df,customer):\n", " \n", " inputDf = df[df['country']=='Australia']\n", " bins = list(inputDf['count'].quantile([0, 0.25, 0.5, 0.75, 1]))\n", " country_geo = 'aus_states.geojson'\n", " m = folium.Map(location=[-26.206337, 133.757599], zoom_start=3.5,zoom_control=False,scrollWheelZoom=False,dragging=False)\n", "\n", " folium.Choropleth(\n", " geo_data=country_geo,\n", " name='choropleth',\n", " data=inputDf,\n", " columns=['administrative_area_level_1', 'count'],\n", " key_on='feature.properties.STATE_NAME',\n", " fill_color='YlGn',\n", " fill_opacity=0.7,\n", " line_opacity=0.2,\n", " nan_fill_color='gray',\n", " nan_fill_opacity=0.4,\n", " legend_name='Fan count in different regions',\n", " bins=bins).add_to(m)\n", " \n", " return m" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "# function for drawing New Zealand choropleth map\n", "def plot_nz_choro(df,customer):\n", "\n", " inputDf = df[df['country']=='New Zealand']\n", " replacements = {\n", " 'administrative_area_level_1': {\n", " r'Auckland': 'Auckland Region',\n", " r'Waikato': 'Waikato Region',\n", " r'Bay Of Plenty': 'Bay of Plenty Region',\n", " r'Otago': 'Otago Region',\n", " r\"Hawke's Bay\": \"Hawke's Bay Region\",\n", " r\"West Coast\": \"West Coast Region\",\n", " r'Taranaki': 'Taranaki Region',\n", " r'Manawatu-Wanganui': 'Manawatu-Wanganui Region',\n", " r'Wellington': 'Wellington Region',\n", " r'Canterbury': 'Canterbury Region',\n", " r'Southland': 'Southland Region',\n", " r'Nelson': 'Nelson Region',\n", " r'Marlborough': 'Marlborough Region',\n", " r'Gisborne': 'Gisborne Region',\n", " r'Tasman': 'Tasman Region',\n", " r'Northland': 'Northland Region'}\n", " }\n", " inputDf.replace(replacements, regex=True, inplace=True)\n", "\n", " bins = list(inputDf['count'].quantile([0, 0.25, 0.5, 0.75, 1]))\n", "\n", " nz_geo = 'nz_region.geojson'\n", " m= folium.Map(location=[-42.161632, 173.003611], zoom_start=5,zoom_control=False, scrollWheelZoom=False, dragging=False)\n", "\n", " folium.Choropleth(\n", " geo_data=nz_geo,\n", " name='choropleth',\n", " data=inputDf,\n", " columns=['administrative_area_level_1', 'count'],\n", " key_on='feature.properties.REGC2016_N',\n", " fill_color='YlGn',\n", " fill_opacity=0.7,\n", " line_opacity=0.2,\n", " legend_name='Nbr of fans in each region',\n", " bins=bins\n", " ).add_to(m)\n", " \n", " #folium.LayerControl().add_to(m)\n", " \n", " return m" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "\"\"\" draw general fan overview plots like gender, age \"\"\"\n", "def plot_age_gender(schema, customer):\n", " engine = get_rds_engine()\n", "\n", " query = f\"\"\"\n", " select age, gender from {schema}.fan_table\n", " \"\"\" \n", " print(\"getting fan gender and age ....\")\n", " ageAndGender = pd.read_sql(query, engine)\n", " print(\"standardizing gender ....\")\n", " ageAndGender.replace(['F','female'], 'female', inplace=True)\n", " ageAndGender.replace(['M','male'], 'male', inplace=True)\n", " ageAndGender.replace(['other','none','unknown'], np.nan, inplace=True)\n", " ageAndGender['gender'] = pd.Categorical(ageAndGender['gender'], categories=['female', 'male'], ordered=True)\n", " \n", " print(\"outputting general overview ....\")\n", " print(\"Total nr of unique fans: \" + str(len(ageAndGender.index)) + \" out of which age is present for: \" + str(ageAndGender['age'].notnull().sum()))\n", " print(\"Total nr of unique fans: \" + str(len(ageAndGender.index)) + \" out of which gender is present for: \" + str(ageAndGender['gender'].notnull().sum()))\n", " \n", " print(\"outputting gender distro plot ....\")\n", " plt.figure()\n", " sns.countplot(x = 'gender', data = ageAndGender, palette=[\"#e66e8e\", \"#3974cc\"])\n", " plt.title('Gender distribution: missing values: ' + str(ageAndGender['gender'].isnull().sum()))\n", " gender_distro_plot_location = f'images/{customer}_gender_distro.png'\n", " plt.savefig(gender_distro_plot_location, bbox_inches='tight')\n", "\n", " print(\"outputting fan age distribution plot....\")\n", " plt.figure()\n", " sns.distplot(ageAndGender['age'], kde=False, color='red', bins=10)\n", " plt.title('Fanbase age distribution', fontsize=18)\n", " plt.ylabel('Frequency', fontsize=14)\n", " # plt.xticks((0,10,20, 30, 40,50,60,70,80,90,100,110,120))\n", " age_distro_plot_location = f'images/{customer}_age_distro.png'\n", " plt.savefig(age_distro_plot_location, bbox_inches='tight')\n", "\n", " print(\"outputting age gender combination....\")\n", " plt.figure()\n", " def multihist(x, hue, n_bins=10, color=None, **kws):\n", " bins = np.linspace(x.min(), x.max(), n_bins)\n", " for _, x_i in x.groupby(hue):\n", " plt.hist(x_i, bins, **kws,color='red')\n", " \n", " g=sns.FacetGrid(ageAndGender[ageAndGender['gender'].isin(['male','female'])], col=\"gender\", sharex=False)\n", " g.map(multihist, \"age\", \"gender\", alpha=.5, edgecolor=\"w\")\n", " g.set(ylabel='frequency')\n", " p=gender_age_distro_plot_location = f'images/{customer}_gender_age_distro.png'\n", " p=plt.savefig(gender_age_distro_plot_location, bbox_inches='tight')\n", " \n", " # print(df.dtypes)\n", " text_output = {'unique_fans': len(ageAndGender.index),\n", " 'age_exists_for': ageAndGender['age'].notnull().sum(),\n", " 'gender_exists_for': ageAndGender['gender'].notnull().sum()}\n", " return age_distro_plot_location, gender_age_distro_plot_location, gender_distro_plot_location, text_output" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "\"\"\" plot fan distance from venue \"\"\"\n", "def plot_distance_from_venue(schema, customer):\n", " engine = get_rds_engine()\n", "\n", " query = f\"\"\"\n", " select fan_id,latitude, longitude from\n", " {schema}.fan_address_geocoded\n", " where locality is not null\n", " \"\"\"\n", " fanAddressCoordinates = pd.read_sql(query, engine)\n", " \n", " query2 = f\"\"\"\n", " select distinct fad.fan_id, fad.event_name, fad.event_location, ed.venue_name, fad.event_data,\n", " cast(fad.event_lon as double precision), cast(fad.event_lat as double precision) from\n", " {schema}.fan_event_data fad inner join\n", " {schema}.event_data ed on fad.event_location=ed.venue_address\n", " where event_location is not null and event_lon is not null\n", " \"\"\"\n", " venueAddressCoordinates = pd.read_sql(query2, engine)\n", " joinedCoordinates = pd.merge(fanAddressCoordinates,venueAddressCoordinates,on='fan_id')\n", " joinedCoordinates['distance'] = joinedCoordinates.apply(lambda row : haversine(\n", " row['latitude'],\n", " row['longitude'], \n", " row['event_lat'],\n", " row['event_lon']), axis = 1)\n", " # remove outliers above 0,95%\n", " q=joinedCoordinates[\"distance\"].quantile(0.95)\n", " joinedCoordinates=joinedCoordinates[joinedCoordinates[\"distance\"] 0\n", " and merch_purchase_date is not null group by fan_id\n", " \"\"\"\n", " else:\n", " query1 = f\"\"\"\n", " select fan_id, sum(CAST(merch_purchase_monetary AS DOUBLE precision)) as total_merch_value_per_fan from\n", " {schema}.fan_merch_data where CAST(merch_purchase_monetary AS DOUBLE precision)>0\n", " and merch_purchase_date is not null group by fan_id\n", " \"\"\"\n", " \n", " clusterMerchValue = pd.read_sql(query1, engine)\n", " \n", " # query cluster and total merch items\n", " if customer=='Pixies': \n", " query2 = f\"\"\"\n", " select fan_id, sum(CAST (merch_purchase_quantity AS INTEGER)) as total_items_per_fan\n", " from {schema}.fan_merch_data_temp where CAST (merch_purchase_quantity AS INTEGER)>0\n", " and merch_purchase_date is not null and CAST(merch_item_price AS DOUBLE precision)>0 group by fan_id\n", " \"\"\"\n", " else:\n", " query2 = f\"\"\"\n", " select fan_id, sum(CAST (merch_purchase_quantity AS INTEGER)) as total_items_per_fan\n", " from {schema}.fan_merch_data where CAST (merch_purchase_quantity AS INTEGER)>0\n", " and merch_purchase_date is not null and CAST(merch_purchase_monetary AS DOUBLE precision)>0 group by fan_id\n", " \"\"\"\n", " clusterMerchCount = pd.read_sql(query2, engine)\n", " \n", " clustersEventsMerchs = pd.merge(clusteredFans, clusterMerchValue, how='left', on=['fan_id'])\n", " clustersEventsMerchs = pd.merge(clustersEventsMerchs, clusterMerchCount, how='left', on=['fan_id'])\n", " clustersEventsMerchs['average_item_value'] =clustersEventsMerchs['total_merch_value_per_fan']/clustersEventsMerchs['total_items_per_fan']\n", " input_columns = [\"total_merch_value_per_fan\",\"total_items_per_fan\",\"average_item_value\"]\n", " clEvMeGr = clustersEventsMerchs.groupby(\"cluster\",as_index=False)[input_columns].mean().round(decimals=2)\n", " print(clEvMeGr)\n", " df_to_png(clEvMeGr, customer,'merch_cluster_means_table')\n", " \n", " plt.figure()\n", " ax =sns.boxplot(x=\"cluster\", y=\"total_merch_value_per_fan\", data=clustersEventsMerchs, showfliers = False)\n", " ax.set_xticklabels(ax.get_xticklabels(),rotation=90)\n", " ax.set_title('Fan merch value distribution by cluster')\n", " t=ax.set(xlabel='cluster', ylabel='total merch value per fan')\n", " merch_cluster_total_value = f'images/{customer}_merch_cluster_total_value.png'\n", " p=plt.savefig(merch_cluster_total_value, bbox_inches='tight')\n", "\n", " plt.figure()\n", " ax =sns.boxplot(x=\"cluster\", y=\"total_items_per_fan\", data=clustersEventsMerchs, showfliers = False)\n", " ax.set_xticklabels(ax.get_xticklabels(),rotation=90)\n", " ax.set_title('Fan merch count distribution by cluster')\n", " t=ax.set(xlabel='cluster', ylabel='total merch item count per fan')\n", " merch_cluster_item_count = f'images/{customer}_merch_cluster_item_count.png'\n", " p=plt.savefig(merch_cluster_item_count, bbox_inches='tight')\n", " \n", " return 1" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [], "source": [ "\"\"\" plot fan merch cluster data \"\"\"\n", "def plot_merch_cluster_top_items(schema, customer):\n", " engine = get_rds_engine()\n", " # pixies and IHW queries currently supported\n", " if customer=='Pixies':\n", " print(\"Pixies\")\n", " query = f\"\"\"\n", " select me.fan_id, cl.cluster, substring(merch_name from '(^.+)(( -)|(,))') as merch_type, me.merch_name,\n", " CAST (me.merch_purchase_quantity AS INTEGER) as merch_purchase_quantity,\n", " CAST(me.merch_item_price AS DOUBLE precision) as merch_purchase_monetary from\n", " {schema}.fan_merch_data_temp me inner join\n", " {schema}.merch_clusters cl\n", " on me.fan_id=cl.fan_id\n", " where CAST(me.merch_item_price AS DOUBLE precision)>0 and\n", " CAST (merch_purchase_quantity AS INTEGER)>0 and merch_purchase_date is not null\n", " \"\"\"\n", " else:\n", " query = f\"\"\"\n", " select me.fan_id, cl.cluster, me.merch_type, me.merch_name,\n", " CAST (me.merch_purchase_quantity AS INTEGER) as merch_purchase_quantity,\n", " CAST(me.merch_item_price AS DOUBLE precision) as merch_purchase_monetary from\n", " {schema}.fan_merch_data me inner join\n", " {schema}.merch_clusters cl\n", " on me.fan_id=cl.fan_id\n", " where CAST(me.merch_item_price AS DOUBLE precision)>0 and\n", " CAST (merch_purchase_quantity AS INTEGER)>0\n", " \"\"\"\n", " \n", " clusterItems = pd.read_sql(query, engine)\n", " uniqueClusters= sorted(clusterItems['cluster'].unique())\n", " # for each cluste print top items and price distribution\n", " for i in uniqueClusters:\n", " # print(\"top item types for each cluster\")\n", " # print(\"cluster: \" + str(i))\n", " inputItems = clusterItems[clusterItems['cluster']==i]\n", " # print(inputItems.shape)\n", " groupedItems= inputItems.groupby(['cluster', 'merch_type'],as_index=False\n", " ).agg(\n", " {\n", " 'fan_id':\"count\",\n", " 'merch_purchase_monetary': [min, 'median', max]\n", " }\n", " ) .round(decimals=2)\n", " \n", " groupedItems.columns = groupedItems.columns.droplevel(level=0)\n", " groupedItemsTotal = groupedItems['count'].sum()\n", " groupedItems['proportion'] = (groupedItems['count']/groupedItemsTotal).round(decimals=2)\n", " # print(groupedItemsTotal)\n", " groupedItems=groupedItems.sort_values(by=['count'], ascending=False).head(5)\n", " groupedItems.columns=(\"cluster\",\"item group\",\"count\",\"min value\",\"median value\",\"max value\",\"proportion\")\n", " groupedItems = groupedItems[[\"cluster\",\"item group\",\"count\",\"proportion\",\"min value\",\"median value\",\"max value\"]]\n", " print(groupedItems.to_string(index=False))\n", " df_to_png(groupedItems, customer,f'merch_cluster_{i}_topitems')\n", " \n", " return 1" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "\"\"\" plot RFM data \"\"\"\n", "def plot_rfm_data(schema, df, customer):\n", " engine = get_rds_engine()\n", "\n", " \n", " query0 = f\"\"\"\n", " select fan_id, administrative_area_level_1 from\n", " {schema}.fan_address_geocoded\n", " where locality is not null and country='United States'\n", " \"\"\"\n", " locations = pd.read_sql(query0, engine)\n", " \n", " query = f\"\"\"\n", " select fan_id, gender from {schema}.fan_table where gender is not null\n", " \"\"\"\n", " gender = pd.read_sql(query, engine)\n", " gender.replace(['F','female'], 'female', inplace=True)\n", " gender.replace(['M','male'], 'male', inplace=True)\n", " gender.replace(['other','none','unknown'], np.nan, inplace=True)\n", " gender['gender'] = pd.Categorical(gender['gender'], categories=['female', 'male'], ordered=True)\n", " fanGender = pd.merge(df,gender,on='fan_id')\n", " # fanGenderPlotData = fanGender.groupby([\"rfm_segment\",\"gender\"],as_index=False)[\"fan_id\"].count().rename(columns={'fan_id' : 'count'})\n", " # fanGenderPlotData = fanGenderPlotData.pivot(index='cluster', columns='gender', values='count')\n", " # print(fanGenderPlotData)\n", " # clusterGender.fillna(0,inplace=True)\n", " # clusterGender = clusterGender.apply(lambda x: x*100/sum(x), axis=1)\n", " \n", " \n", " \n", " segmentSize = df.groupby(\"rfm_segment\",as_index=False)[\"fan_id\"].count().rename(columns={'fan_id' : 'size'})\n", " print(segmentSize)\n", " \n", " plt.figure()\n", " sns.barplot(data = segmentSize,x = 'rfm_segment', y='size')\n", " plt.xticks(rotation=45)\n", " plt.title('size of each RFM segment')\n", " plt.xlabel('')\n", " rfm_segment_size_plot_location = f'images/{customer}_rfm_segment_sizes.png'\n", " p=plt.savefig(rfm_segment_size_plot_location, bbox_inches='tight')\n", " \n", " plt.figure()\n", " ax =sns.boxplot(x=\"rfm_segment\", y=\"frequency_value\", data=df, showfliers = False)\n", " ax.set_xticklabels(ax.get_xticklabels(),rotation=90)\n", " ax.set_title('Item count per RFM segment')\n", " t=ax.set(xlabel='RFM segment', ylabel='count')\n", " rfm_item_count_plot_location = f'images/{customer}_rfm_item_count.png'\n", " p=plt.savefig(rfm_item_count_plot_location, bbox_inches='tight') \n", " \n", " plt.figure()\n", " ax =sns.boxplot(x=\"rfm_segment\", y=\"monetary_value\", data=df, showfliers = False)\n", " ax.set_xticklabels(ax.get_xticklabels(),rotation=90)\n", " ax.set_title('Monetary value per RFM segment')\n", " t=ax.set(xlabel='RFM segment', ylabel='monetary value')\n", " rfm_monetary_value_plot_location = f'images/{customer}_rfm_monetary_value.png'\n", " p=plt.savefig(rfm_monetary_value_plot_location, bbox_inches='tight') \n", " \n", " \n", " # calculate additional feature for date plot\n", " df['date_diff'] = (calculate_diff(df['recency_value'], pd.to_datetime('today'))) / pd.Timedelta(1, unit='d')\n", " plt.figure()\n", " ax =sns.boxplot(x=\"rfm_segment\", y=\"date_diff\", data=df, showfliers = False)\n", " ax.set_xticklabels(ax.get_xticklabels(),rotation=90)\n", " ax.set_title('Days from today per RFM segment')\n", " t=ax.set(xlabel='RFM segment', ylabel='nr of days')\n", " rfm_recency_value_plot_location = f'images/{customer}_rfm_recency_value.png'\n", " p=plt.savefig(rfm_recency_value_plot_location, bbox_inches='tight')\n", " \n", " # plot recency and frequency combination heatmap\n", " dfPivotInput = df.groupby(['rfm_recency','rfm_frequency'],as_index=False)[\"monetary_value\"].mean().round(decimals=0)\n", " dfPivot=dfPivotInput.pivot(index=\"rfm_recency\",columns=\"rfm_frequency\",values=\"monetary_value\")\n", " dfPivot.fillna(0,inplace=True)\n", " plt.figure()\n", " plt.title(\"Most valuable RFM segments\",fontsize=18)\n", " plt.xlabel(\"RFM Frequency groups\")\n", " plt.ylabel('RFM recency groups')\n", " redpink = [\"#fac1c0\", \"#fdb2b1\", \"#ffa3a3\", \"#ff9494\", \"#ff8486\", \"#ff7377\",\"#ff6169\",\"#ff4d5b\"]\n", " # sns.heatmap(dfPivot,fmt=\"\",cmap='YlGn', annot=True)\n", " sns.heatmap(dfPivot,fmt=\"\",cmap=sns.color_palette(redpink), annot=True)\n", " rfm_recency_frequency_heatmap_location = f'images/{customer}_rfm_recency_frequency_heatmap.png'\n", " p=plt.savefig(rfm_recency_frequency_heatmap_location, bbox_inches='tight')\n", " \n", " # plot champion segment map\n", " rfmMapInput= pd.merge(df,locations,on='fan_id')\n", " rfmMapInputGrouped = rfmMapInput.groupby(['rfm_segment','administrative_area_level_1'],as_index=False)[\"fan_id\"].count().rename(columns={'fan_id' : 'count'})\n", " rfmSegmentList= rfmMapInputGrouped['rfm_segment'].unique().tolist()\n", " for i in rfmSegmentList:\n", " print(i)\n", " mapInput = rfmMapInputGrouped[rfmMapInputGrouped['rfm_segment']==i]\n", " mapInput['country']='United States'\n", " state_map =plot_us_states_choro(mapInput,customer)\n", " display(state_map)\n", " \n", " # plot top items per segment\n", " \n", " # plot gender distribution by rfm segment\n", " # exclude this plot for time being as no gender for merch data\n", " # plt.figure()\n", " # fanGender.plot(kind=\"bar\", stacked=True,color=['pink','lightblue','yellow'])\n", " # plt.title(\"RFM segment gender breakdown\")\n", " # plt.xlabel(\"RFM segment\")\n", " # plt.ylabel(\"Percentage inside segment (%)\")\n", " # event_cluster_gender_plot_location = f'images/{customer}_event_cluster_gender.png'\n", " # p=plt.savefig(event_cluster_gender_plot_location, bbox_inches='tight')\n", " \n", " return 1" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "\"\"\" plot superfan locations from Google API results \"\"\"\n", "def plot_superfan_maps(schema, customer,treshold):\n", " query = f\"\"\"\n", " select total_score,\n", " count(fan_id) as count,\n", " (count(fan_id)*100 / (select count(*) From {schema}.superfans)) as percentage from\n", " {schema}.superfans group by total_score order by 2 desc\n", " \"\"\"\n", " superfanLocations = pd.read_sql(query, engine)\n", " display(superfanLocations.head(10))\n", " \n", " query2 = f\"\"\"\n", " select country, administrative_area_level_1, administrative_area_level_2,\n", " count(sf.fan_id) as count from\n", " {schema}.fan_address_geocoded fag\n", " inner join {schema}.superfans sf on fag.fan_id=sf.fan_id\n", " where locality is not null and sf.total_score >={treshold}\n", " group by country,administrative_area_level_1,administrative_area_level_2 order by 1,4 desc\n", " \"\"\"\n", " superfanLocations = pd.read_sql(query2, engine)\n", " superfanLocationsGrouped = superfanLocations.groupby(['country'],as_index=False)[\"count\"].count()\n", " superfanLocationsGrouped=superfanLocationsGrouped.sort_values(by=['count'], ascending=False).head(5)\n", "\n", " plt.figure()\n", " sns.barplot(data = superfanLocationsGrouped,x = 'country', y='count')\n", " plt.xticks(rotation=45)\n", " plt.title('Top 5 countries for superfans')\n", " plt.xlabel('')\n", " superfan_top5_countries_plot_location = f'images/{customer}_suprefan_top5_plot.png'\n", " p=plt.savefig(superfan_top5_countries_plot_location, bbox_inches='tight')\n", " \n", " query3 = f\"\"\"\n", " select count(s.fan_id) as size, t.\"City\", t.\"Latitude\", t.\"Longitude\" from {schema}.superfans s \n", " inner join {schema}.fan_address_geocoded fag on s.fan_id=fag.fan_id\n", " inner join {schema}.top1000_us_cities t on t.\"City\"= fag.locality\n", " and t.\"State\"=fag.administrative_area_level_1 where s.total_score>={treshold} group by t.\"City\", t.\"Latitude\", t.\"Longitude\"\n", " \"\"\"\n", " sfCities = pd.read_sql(query3, engine)\n", " m=folium.Map([38.025265, -101.284950],zoom_start=3.5)\n", " \n", " for lat,lon,area,size in zip(sfCities['Latitude'],sfCities['Longitude'],sfCities['City'],sfCities['size']):\n", " folium.CircleMarker([lat, lon],\n", " popup=area,\n", " radius=size,\n", " color='#eb7a34',\n", " fill=True,\n", " fill_opacity=0.8\n", " ).add_to(m)\n", " return m" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "\"\"\" plot merch timeseries \"\"\"\n", "def plot_merch_timeseries(schema, customer):\n", " engine = get_rds_engine()\n", " import matplotlib.pyplot as plt\n", " import matplotlib.ticker as ticker\n", " # plot purchase day of week\n", " query = f\"\"\"\n", " select fan_id, extract(isodow from merch_purchase_date::timestamp)::INTEGER as dow,\n", " extract(hour from merch_purchase_date::timestamp)::INTEGER as hour,\n", " merch_purchase_date::timestamp::date as date,\n", " merch_purchase_date as date_txt from {schema}.fan_merch_data fmd\n", " where merch_purchase_date is not null and CAST(merch_item_price AS DOUBLE precision)>0\n", " ;\n", " \"\"\"\n", " purchase_moment = pd.read_sql(query, engine)\n", " \n", " plt.figure()\n", " sns.distplot(purchase_moment['dow'], kde=False, color='red', bins=7)\n", " plt.title('Day of week for purchases', fontsize=14)\n", " plt.ylabel('Frequency', fontsize=14)\n", " merch_dow_plot_location = f'images/{customer}_merch_dow_distro.png'\n", " plt.savefig(merch_dow_plot_location, bbox_inches='tight')\n", " \n", " plt.figure()\n", " sns.distplot(purchase_moment['hour'], kde=False, color='red', bins=24)\n", " plt.title('Hour of day for purchases', fontsize=14)\n", " plt.ylabel('Frequency', fontsize=14)\n", " merch_hour_plot_location = f'images/{customer}_merch_hour_distro.png'\n", " plt.savefig(merch_hour_plot_location, bbox_inches='tight')\n", " \n", " # plot recency and frequency combination heatmap\n", " dfPivotInput = purchase_moment.groupby(['hour','dow'],as_index=False)[\"fan_id\"].count().round(decimals=0)\n", " print(dfPivotInput.sort_values(by=['fan_id'], ascending=False).head(5))\n", " dfPivot=dfPivotInput.pivot(index=\"hour\",columns=\"dow\",values=\"fan_id\")\n", " dfPivot.fillna(0,inplace=True)\n", " plt.figure()\n", " plt.title(\"Most active day of week and hour combinations\",fontsize=14)\n", " plt.xlabel(\"RFM Frequency groups\")\n", " plt.ylabel('RFM recency groups')\n", " redpink = [\"#fac1c0\", \"#fdb2b1\", \"#ffa3a3\", \"#ff9494\", \"#ff8486\", \"#ff7377\",\"#ff6169\",\"#ff4d5b\"]\n", " sns.heatmap(dfPivot,fmt=\"\",cmap=sns.color_palette(redpink), annot=False)\n", " merch_dow_hour_heatmap_location = f'images/{customer}_dow_hour_heatmap.png'\n", " p=plt.savefig(merch_dow_hour_heatmap_location, bbox_inches='tight')\n", " \n", " dfTimeSeries = purchase_moment.groupby(['date'],as_index=False)[\"fan_id\"].count().round(decimals=0).rename(columns={'fan_id' : 'count'})\n", " \n", " plt.figure(figsize=(16, 6))\n", " ax=sns.lineplot(x=\"date\", y=\"count\", data=dfTimeSeries,color='red')\n", " ax.xaxis.set_major_locator(ticker.MultipleLocator(90))\n", " plt.xticks(rotation=45)\n", " plt.rcParams['xtick.bottom'] = True\n", " plt.title('Nr of purchases through time',fontsize=14)\n", " merch_timeseries_location = f'images/{customer}_merch_timeseries.png'\n", " p=plt.savefig(merch_timeseries_location, bbox_inches='tight')\n", " \n", " return 1" ] } ], "metadata": { "kernelspec": { "display_name": "conda_python3", "language": "python", "name": "conda_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.6.10" } }, "nbformat": 4, "nbformat_minor": 4 }