{ "cells": [ { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%run ./utils.ipynb\n", "%run ./viz_utils.ipynb\n", "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "engine = get_rds_engine()\n", "schema = 'af65a0ef98e7420800afdea2beb81059bde2bc59d797cc0851412ac41'\n", "customer = \"Janis\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_age_gender(schema,customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_fan_maps(schema, customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_us_cities(schema,customer,'x')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# ----------------------------------------Superfans -------------------------- #\n", "# get unique fans from fans table as a starting point\n", "query1 = f\"\"\"select id as fan_id from {schema}.fan\"\"\"\n", "uniqueFans = pd.read_sql(query1, engine)\n", "# get item count\n", "query2 = f\"\"\"select fan_id, sum(CAST (merch_purchase_quantity AS INTEGER)) as count,\n", " sum(CAST (merch_item_price AS DOUBLE precision)) as value,\n", " max(merch_purchase_date::timestamp) as last_date from\n", " {schema}.fan_merch_data fmd\n", " where merch_purchase_date is not null and CAST(merch_purchase_monetary AS DOUBLE precision)>0 group by fan_id\"\"\"\n", "itemCountPerFan = pd.read_sql(query2, engine)\n", "\n", "query3 = f\"\"\"\n", "SELECT \n", "fan_id, root_email\n", "FROM {schema}.fan_table\n", ";\n", "\"\"\"\n", "fan_emails = pd.read_sql(query3, engine)\n", "\n", "# query optin count\n", "query4 = f\"\"\"\n", "SELECT \n", "fan_id, sum(number_of_opt_ins) as optin_count\n", "FROM {schema}.fan_optin group by fan_id\n", ";\n", "\"\"\"\n", "fan_optins = pd.read_sql(query4, engine)\n", "# print (itemCountPerFan.describe())\n", "print(itemCountPerFan[\"value\"].quantile(0.90))\n", "\n", "itemCountPerFan['high_value_purchases'] =np.where(itemCountPerFan['value']>75,1, np.nan)\n", "itemCountPerFan['single_item'] =np.where(itemCountPerFan['count']>0,1, np.nan)\n", "itemCountPerFan['multiple_item'] =np.where(itemCountPerFan['count']>1,1, np.nan)\n", "itemCountPerFan['purchase_last_30d'] =np.where(\n", " (calculate_diff(itemCountPerFan['last_date'], pd.to_datetime('today'))) / pd.Timedelta(1, unit='d')<90,1, np.nan\n", " )\n", "itemCountPerFan.drop(['count','last_date','value'], axis=1,inplace=True)\n", "\n", "fan_optins['multi_optin']=np.where(fan_optins['optin_count']>1,1, np.nan)\n", "fan_optins.drop(['optin_count'], axis=1,inplace=True)\n", "\n", "# merge dataframes\n", "rawSuperFanDf = pd.merge(uniqueFans, itemCountPerFan, how='left', on=['fan_id'])\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, fan_optins, how='left', on=['fan_id'])\n", "rawSuperFanDf['total_score'] = rawSuperFanDf.sum(axis=1)\n", "# write superfandata into RDS\n", "rawSuperFanDf.to_sql(f'superfans',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " #index_label='fan_id',\n", " index=False\n", " )\n", "\n", "# print(rawSuperFanDf.head(5))\n", "groupedSuperFanData = rawSuperFanDf.groupby(['total_score'],as_index=False).count()\n", "print(groupedSuperFanData)\n", "\n", "# Join emails\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, fan_emails, how='left', on=['fan_id'])\n", "\n", "#rawSuperFanDf.loc[rawSuperFanDf['total_score']==2]\n", "\n", "# Select some good segments (4,5,6)\n", "superfans = rawSuperFanDf.loc[rawSuperFanDf['total_score'].isin([3,4,5,6])]\n", "# print(superfans.head(10))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "topc = pd.read_csv(\"top1000_us_cities_lat_long.csv\",delimiter=\";\")\n", "print(topc.shape)\n", "topc.to_sql(f'top1000_us_cities',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " #index_label='fan_id',\n", " index=False\n", " )" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# schema, customer, treshold= lowest score group to include\n", "plot_superfan_maps(schema,customer,3)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = prepare_rfm_dataset_jj(schema)\n", "print(df.dtypes)\n", "print(df.shape)\n", "df_=df.filter(items=['fan_id','rfm_segment'])\n", "print(df_.dtypes)\n", "print(df_.shape)\n", "df_.to_sql(f'rfm_segments',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " #index_label='fan_id',\n", " index=False\n", " )" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df2 = prepare_fan_demographics(schema)\n", "print(df2.dtypes)\n", "print(df2.shape)\n", "print(df2.tail(5))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df['Sales_Bins'] = pd.qcut(df.monetary_value,4)\n", "df.Sales_Bins.value_counts().sort_index()\n", "bins = list(df.Sales_Bins.value_counts().sort_index())\n", "print(bins)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# print(df.groupby('rfm_segment')[\"fan_id\"].count())\n", "# clusterGender = clusteredFans.groupby([\"cluster\",\"gender\"],as_index=False)[\"fan_id\"].count().rename(columns={'fan_id' : 'count'})\n", "plot_rfm_data(schema,df,customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "rfm_segment='At Risk'\n", "# rfm_segment=\"Can't Lose Them\"\n", "# rfm_segment='Loyal Customers'\n", "# rfm_segment='Champions'\n", "rfm_segment=0\n", "plot_us_cities(schema,customer,rfm_segment)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_merch_cluster_results(schema, customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_merch_cluster_top_items(schema, customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_merch_timeseries(schema,customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# plot RFM CLUSTER RESULTS\n", "rfm_segment=5\n", "plot_us_cities(schema,customer,rfm_segment)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# let's plot most popular items by for hour 12-13 and dow 1\n", "engine = get_rds_engine() \n", "\n", "query7 = f\"\"\"\n", "select extract(year from merch_purchase_date::timestamp)as year,\n", "substring(merch_name from '(^.+)(( -)|(,))') as item,\n", "count(fan_id) as count from {schema}.fan_merch_data_temp\n", "where merch_purchase_date is not null and CAST(merch_purchase_quantity AS INTEGER)>0 and\n", "extract(isodow from merch_purchase_date::timestamp)=3 and \n", "extract(hour from merch_purchase_date::timestamp) in(6,7,8,9) and\n", "extract(year from merch_purchase_date::timestamp)=2018\n", "group by 1,2\n", "order by 3 desc\n", "\"\"\"\n", "\n", "query7 = F\"\"\"\n", "select merch_name,\n", "count(fan_id) as count from {schema}.fan_merch_data\n", "where merch_purchase_date is not null and CAST(merch_purchase_quantity AS INTEGER)>0 group by merch_name\n", "order by 2 desc\n", "\"\"\"\n", "dowitems = pd.read_sql(query7, engine)\n", "print(dowitems.head(20))\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "engine = get_rds_engine() \n", "\n", "queryX = f\"\"\"\n", "select count(s.fan_id), s.rfm_segment,t.\"City\" 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 in('At Risk','Champions','Loyal Customers')\n", "or s.rfm_segment like '%%Lose%%'\n", "group by s.rfm_segment, t.\"City\" order by 2, 1 desc\n", "\"\"\"\n", "rfm_by_city= pd.read_sql(queryX, engine)\n", "print(rfm_by_city.head(5))\n", "print(rfm_by_city.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "aa=rfm_by_city[rfm_by_city['rfm_segment']=='Champions']\n", "aa['perc']= aa['count']/aa['count'].sum()\n", "aa=aa.head(5)\n", "print(aa)\n", "\n", "bb=rfm_by_city[rfm_by_city['rfm_segment']=='At Risk']\n", "bb['perc']= bb['count']/bb['count'].sum()\n", "bb=bb.head(5)\n", "print(bb)\n", "\n", "\n", "cc=rfm_by_city[rfm_by_city['rfm_segment']=='Loyal Customers']\n", "cc['perc']= cc['count']/cc['count'].sum()\n", "cc=cc.head(5)\n", "print(cc)\n", "\n", "dd=rfm_by_city[rfm_by_city['rfm_segment']=='Can\\'t Lose Them']\n", "dd['perc']= dd['count']/dd['count'].sum()\n", "dd=dd.head(5)\n", "print(dd)\n", "\n", "total=aa.append(bb) \n", "total=total.append(cc)\n", "total=total.append(dd)\n", "total['perc']=total['perc'].round(decimals=2)\n", "print(total.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfPivot=total.pivot(index=\"rfm_segment\",columns=\"City\",values=\"perc\")\n", "dfPivot.fillna(0,inplace=True)\n", "dfPivot\n", "plt.figure(figsize=(16, 6))\n", "\n", "plt.title(\"RFM segment and City heatmap(top 5 cities in percentage)\",fontsize=18)\n", "plt.xlabel(\"\")\n", "plt.ylabel('')\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,linewidths=0.1, linecolor='gray')" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(500, 4)\n", " email gender age address\n", "0 MichaelCaputo718@aol.com male 63 1940 77th Street, East Elmhurst, New York\n", "1 amykuester@yahoo.com female NaN La Crosse, Wisconsin\n", "2 lucyr169@gmail.com NaN NaN 169-3 Monitor Street, Brooklyn, New York\n", "3 willcarol@comcast.net female NaN NaN\n" ] } ], "source": [ "# load enriched data to DB\n", "enriched_input = pd.read_csv(\"enriched_input.csv\",delimiter=\";\")\n", "print(enriched_input.shape)\n", "print(enriched_input.head(4))\n", "enriched_input.to_sql(f'enriched_input',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " #index_label='fan_id',\n", " index=False\n", " )" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Text(0, 0.5, 'Frequency')" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAEbCAYAAADXk4MCAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy86wFpkAAAACXBIWXMAAAsTAAALEwEAmpwYAAAnMUlEQVR4nO3deVhUZf8G8JvFYZFAQbEiJWMzY1NcyBZERXErBUmCMEVRyy01iIxeszClV3N9LYUuUFHIeLWE/FlKKZUrmWEGGLgkRqnsmwPDnN8fxryOg8Ygc0Y49+e6vC7mmXPO8z0cnHvOczYDQRAEEBGRZBnquwAiItIvBgERkcQxCIiIJI5BQEQkcQwCIiKJYxAQEUkcg4A0HD9+HC4uLti9e7e+S5GcDRs2wMXFBUVFRXdtk0od+uxXSoz1XQA17/jx45gyZcod3//000/h6ekpXkHU7lRWVmLr1q0YNGgQBg8erO9y7urgwYPIzc3FvHnz9F2KJDEI7nPjxo3Ds88+q9Heq1cvPVRD+vDKK69g5syZkMlkWs1XWVmJjRs3Yu7cuVoHQWv7bK2DBw9iz549zQaB2LVIEYPgPte3b188//zz+i6D9MjY2BjGxuL8V62uroaFhYWoff6T+6mWjorHCNqxnJwcREdHY9SoUfDw8EC/fv0QHByMAwcOaEwbHR0NFxcXVFVVYenSpXjyySfh5uaG4OBg/Pzzz3fsY/v27Rg1ahTc3NwwatQobN++/Z7qKC4uxptvvglfX1+4urriySefRHBwMPbs2aM2nSAI2LlzJwICAlTLDAsLw7Fjx1r0u6mursaaNWsQFBSEwYMHw9XVFX5+fli1ahXq6uo0pi8rK8Obb76JwYMHo1+/fpgyZQp+/fVXhIWFYdiwYRrTnzlzBnPmzFEte9SoUfjoo4+gUChaVJ9SqcTmzZsxbNgwuLm5Ydy4cdi7d2+z0zY3Rl5eXo73338fI0aMgJubGwYPHoyAgAAkJCQAuDm0OHz4cADAxo0b4eLiAhcXF9W6FBUVwcXFBRs2bMC+ffsQEBAAd3d3xMbG3rHPJnV1dYiNjcVTTz0Fd3d3BAUF4ejRo2rT3Lr8f1qfsLAw1fZvqvPWY1R3qqWoqAiRkZEYMmQIXF1dMWLECHz44Yca27dp/vPnz+PDDz/Es88+C1dXVzz33HM4fPhws79zqWHM3ufq6upQWlqq1iaTyWBhYYEDBw7g/Pnz8Pf3h52dHcrLy7Fnzx7MnTsXq1atwvjx4zWWN336dFhbW2POnDkoLy9HYmIiZs6ciczMTFhYWKhNm5ycjGvXrmHy5MmwsLBARkYGYmNjUVFRgblz56qma2kdCoUC06ZNw19//YWQkBA8+uijqK6uRn5+PrKzszFx4kTVMiMjI/Hll19i1KhRCAgIQH19PdLT0xEeHo4NGzaoPuTu5K+//kJaWhpGjhyJcePGwdjYGCdOnEBCQgJyc3PxySefqKatr6/HtGnTkJubi4CAALi5uSE/Px/Tpk2DlZWVxrIPHTqEuXPnwt7eHuHh4bCyssLp06exfv165ObmYv369XetDQBWrFiBbdu2YeDAgZg6dSpKSkrw7rvvomfPnv84LwAsWLAA2dnZCA4OhouLC27cuIHCwkKcOHECM2bMgIODA958802sWLECfn5+8PPzAwB07txZbTkHDx7E9u3b8eKLLyI4OFjjb6A5b7zxBgwNDREREYHq6mp8+umnmDFjBuLj4zFkyJAW1X+r2bNnQ6lUIjs7Gx988IGqvX///nec58qVKwgKCkJVVRVCQkJgb2+PEydOYPPmzTh16hSSkpI09iKio6NhbGyM8PBwNDQ0YOvWrZgzZw7279+PRx55ROu6OxSB7kvHjh0TnJ2dm/332muvCYIgCDU1NRrz1dbWCiNHjhRGjx6t1v7GG28Izs7OwtKlS9Xa9+3bJzg7OwspKSkafXt6egrFxcWqdrlcLgQGBgp9+/ZVa29pHbm5uYKzs7OwZcuWu677119/LTg7Owupqalq7Q0NDcLEiRMFX19fQalU3nUZcrlcqK+v12hfs2aN4OzsLPz888+qtuTkZMHZ2VnYtGmT2rRN7b6+vqq2GzduCEOGDBFCQkKEhoYGtekTExMFZ2dn4dixY3etrbCwUHBxcRGmTJkiKBQKVfsvv/wiuLi4CM7OzsLly5dV7evXr1drq6ysbHZb3u7y5cuCs7OzsH79+ju+17dvX6GgoEDj/dv7vLVt0qRJglwuV7UXFxcLnp6egr+/f4v6bm7ZTX+fzWlu+kWLFgnOzs7CoUOH1KZduXKl4OzsLOzatUtj/pkzZ6r93fz888+Cs7OzsGrVqmb7lRIODd3nJk+ejMTERLV/r7zyCgDA3NxcNV1dXR3KyspQV1cHb29vFBYWorq6WmN5U6dOVXvt7e0NALh06ZLGtOPHj8eDDz6oei2TyTB16lQoFAp88803qvaW1vHAAw8AuDlsUVJScsd13rt3Lzp37owRI0agtLRU9a+yshLDhg3DlStXcPHixTvO31Rrp06dANzcE6moqEBpaanqG+utw2HffvstjIyMNM7SCgoKUtXc5IcffsD169cREBCAyspKtfqaDur/8MMPd60tMzMTgiBg2rRpMDIyUrU/8cQTeOqpp+46LwCYmJhAJpMhJyfnnk+p9PHxgYODg1bzTJ06Ve3A7YMPPojx48fj/PnzKCwsvKd6WkKpVOKbb75B37594ePjo/berFmzYGhoiIMHD2rMN2XKFBgYGKheu7u7w9zcvNm/fanh0NB9zt7e/o672yUlJVi7di0yMzOb/WCtrKzU2NW/feiha9euAG6OOd+uuQ8IR0dHAMDly5e1rsPOzg6zZ8/Gli1b8PTTT+Pxxx+Ht7c3/P394e7urpq+sLAQNTU1dx1mKCkpQe/eve/4PgDs2LEDqampKCgogFKpVHuvoqJC9XNRURFsbW01hk1kMhkeeeQRVFZWqtUGAEuWLLljv9evX79rXU2/u8cee0zjPQcHB3z//fd3nV8mk2HJkiVYvnw5hg8fDkdHR3h7e2PEiBF48skn7zrv7R599FGtpm+q8U5tly9f1jpYtFVaWora2lrV3+KtunTpgu7du6v9fTZpbtita9euKCsr00md7QmDoJ0SBAHh4eEoLCzElClT4OrqigceeABGRkb473//i4yMDI0PPwBq30BvX54YdSxcuBCTJk3CoUOHkJ2djbS0NHzyySeYMWMGIiMjVcu0trbG6tWr79ivk5PTXetKTEzEypUr8fTTT2PKlCmwtbVFp06d8NdffyE6Ovqe1hcAoqKi8Pjjjzc7ja2tbauWrY0XX3wRw4cPx+HDh3HixAl89dVXSE5OxpgxY7BmzZoWL8fMzEwn9d36zft2LT2g3tYMDTkAcicMgnYqPz8feXl5mDNnDubPn6/23meffdYmfTS3m19QUADgf9+uWlNHz549ERYWhrCwMMjlckyfPh0JCQkIDw+HjY0N7O3tcfHiRXh4eGh8S2+pL774AnZ2doiPj1f7AMjKytKY1s7ODkePHkVNTY1afw0NDSgqKoKlpaWqrekbtJmZWasOjAL/+92dP39e43oQbYZWbG1tERQUhKCgIDQ2NiIqKgoZGRmYNm0a3N3d7/phfC8KCwvRp08fjTbgf+vWdJD91j2vJs0NZ2lTq7W1NTp37qz6W7xVRUUFrl27dseQpuYxItuppg+327/Znjt3rtnTNlsjPT0df/75p+p1fX09kpKSYGRkBF9fX63rqKqqQkNDg1qbiYmJaoik6UNjwoQJUCqV+PDDD5ut65+GXprqMjAwUKtLoVAgPj5eY9phw4ahsbER27ZtU2vftWsXqqqq1Nqefvpp2NjYID4+vtnhtBs3bjR7bOb2/gwMDJCYmIjGxkZV+9mzZ3HkyJF/XLe6ujqNUySNjIzg4uIC4H+/x6ZjN819GN+LpKQk1NfXq17/+eefSE9PR+/evVXDQhYWFujevTuOHTumtg0uX77c7Ph9U63N/U5vZ2hoCF9fX/z6668awb5lyxYolUqMGDGiNasmWdwjaKccHBzg5OSEhIQE3LhxA71798aFCxfw6aefwtnZGWfPnr3nPnr37o2goCAEBwejc+fOyMjIwJkzZ/Dqq6/ioYce0rqO48eP4+2338bIkSPRu3dvdO7cGb/88gvS0tLg4eGhCgR/f38EBAQgOTkZZ8+eha+vL7p27Yo///wTp0+fxqVLl5CZmXnX2v39/bF69WpERETAz88P1dXVyMjIaPbCpKCgIKSmpmLt2rX4/fffVaeP7t+/H/b29mpDGebm5oiLi8OcOXPg7++PwMBA2Nvbo7KyEufPn8eBAwewcePGu17J6+DggNDQUCQnJ+Pll1/GyJEjUVJSgh07dqBPnz749ddf77puFy9exEsvvQQ/Pz84OTnB0tIS58+fR0pKCh555BEMGDAAwM3xb3t7e3z55Zfo2bMnunXrBjMzs2avi9BGY2MjQkNDMXbsWNTU1CA1NRVyuRwxMTFq04WGhmLt2rWYMWMGRowYgatXryI1NRVOTk44c+aM2rQeHh5ITk7GsmXL4OPjg06dOsHd3f2Op9MuWrQIR44cwZw5cxASEoJevXohOzsb+/btw8CBA9VORaZ/xiBop4yMjLB582bExcVhz549qKurg5OTE+Li4pCXl9cmQfDSSy+huroaycnJ+OOPP/Dwww9jyZIlePnll1tVh4uLC/z8/HDixAmkp6dDqVTioYcewqxZsxAeHq7W94oVKzB48GDs2rULmzdvRkNDA7p3746+ffti8eLF/1j79OnTIQgC0tLSsHz5cnTv3h2jR49GYGAgxowZozatTCbD1q1b8cEHHyAzMxP/93//B3d3dyQlJeGtt97CjRs31KZ/5plnkJaWhi1btmDv3r0oKyuDpaUlevXqhalTp6q+md/NW2+9hW7dumHXrl344IMP8Oijj+Jf//oXLl269I9B8OCDDyIwMBDHjx/HwYMHUV9fjx49eiAoKAgRERFq4/6rVq3C+++/jzVr1qCurg52dnb3HARxcXFITU1FfHw8Kisr4eLigpUrV2qc8RQREYGqqirs3bsXJ06cgKOjI5YvX46zZ89qBMG4ceOQm5uLL7/8Evv374dSqcSKFSvuGAR2dnbYtWsX1q9fj71796Kqqgo9evTArFmz8Morr/BKZC0ZCK09akbUwTU2NsLb2xvu7u5qF6ARdTQ8RkAEaHzrB4DU1FRUVla26Nx+ovaM+09EAGJiYlBfX49+/fpBJpPhp59+QkZGBuzt7fHCCy/ouzwineLQEBGAzz//HDt27MDFixdRW1sLGxsb+Pj4YMGCBejWrZu+yyPSKQYBEZHE8RgBEZHEtctjBD/++KO+SyAiape8vLw02tplEADNrwwREd3Znb5Ec2iIiEjiGARERBLHICAikjgGARGRxDEIiIgkjkFARCRxDAIiIoljEBARSZyoF5Tl5ORg7dq1aGhogI+PDwICAhAVFYWamhoMGTIE8+bNE7Mcoo6lpARowaMe25yREXDLIzdF06ULYGMjfr8dkGhBUF9fj40bN+I///mP6glKcXFxCAwMxOjRozFz5kwUFBTA0dFRrJKIOpbycuAfHuGpE56ewOnT4vc7fDiDoI2INjR0+vRpmJqaYv78+QgPD0deXh5OnTqlegj60KFDcfLkSbHKISKiv4m2R3D16lUUFBQgLS0NxcXFiImJQW1tLUxNTQEAlpaWKCoqEqscIiL6m2h7BJaWlujfvz/Mzc3h4OCA6upqmJmZQS6XAwCqqqpgZWUlVjlERPQ30YLAw8MDFy5cgFKpxLVr1yCTyeDl5YXDhw8DALKysjBgwACxyiEior+JNjRkZWWFiRMn4qWXXoJCoUB0dDQcHBwQFRWFxMREeHt7w8nJSaxyiIjob6KePjpp0iRMmjRJrS0hIUHMEoiI6Da8oIyISOIYBEREEscgICKSOAYBEZHEMQiIiCSOQUBEJHEMAiIiiWMQEBFJHIOAiEjiGARERBLHICAikjgGARGRxDEIiIgkjkFARCRxDAIiIoljEBARSRyDgIhI4hgEREQSxyAgIpI4BgERkcQxCIiIJI5BQEQkcQwCIiKJYxAQEUkcg4CISOIYBEREEmcsZmeenp5wc3MDAERERGDQoEGIjo7G1atX4eTkhKVLl8LQkNlE7VhJCVBerp++a2r00y+1e6IGwSOPPILt27erXu/YsQOurq6YMWMGli1bhu+++w4+Pj5ilkTUtsrLgcxM/fTt6amffqndE/Xrd3FxMUJDQ7F48WKUlZUhOzsbvr6+AIChQ4fi5MmTYpZDREQQeY/gwIEDsLa2RlpaGtasWYOKigpYWloCACwtLVFRUSFmOUREBJH3CKytrQEAY8eORW5uLiwtLVFZWQkAqKqqgpWVlZjlEBERRAyC2tpaNDY2AgBOnDgBe3t7DBw4EFlZWQCArKwsDBgwQKxyiIjob6INDZ0/fx4xMTGwsLCATCZDbGwsunbtiujoaISGhsLBwQHPPvusWOUQEdHfRAsCV1dXfP755xrt69atE6sEIiJqBk/aJyKSOAYBEZHEMQiIiCSOQUBEJHEMAiIiiWMQEBFJHIOAiEjiGARERBLHICAikjgGARGRxDEIiIgkjkFARCRxDAIiIokT9QllRERtRi4HCgv103eXLoCNjX761gEGARG1T9XVwPff66fv4cM7VBBwaIiISOIYBEREEscgICKSOAYBEZHEMQiIiCSOQUBEJHEMAiIiiWMQEBFJHIOAiEjitAqC559/HsnJyaioqNBVPUREJDKtgmDo0KFISEjAM888g0WLFuHo0aO6qouIiESiVRAsXLgQ3377LTZs2IDGxkbMnDkTw4YNw8aNG/HHH3+0aBnZ2dlwcXFBaWkpSktLMWPGDLz44ovYsGFDq1aAiIjujdbHCAwMDODj44N169bhu+++w+TJk7F582aMGDEC06dPR1ZW1l3n37p1K1xdXQEA8fHxCAwMREpKCs6cOYOCgoLWrQUREbVaqw8Wnz59GqtXr8aWLVtga2uLOXPmoGfPnliwYAGWL1/e7DzffvstvLy8YG5uDgA4deoUfH19Adwcdjp58mRryyEiolbS6jbUJSUl+Pzzz7F79278/vvvGDZsGNavX4+nnnpKNc3zzz+P8PBwvPXWW2rzKpVK7Ny5Exs3bkRmZiYAoLa2FqampgAAS0tLFBUV3ev6EBGRlrQKAh8fH/Tq1QuTJk3ChAkTYG1trTGNk5OTaujnVunp6Rg2bBhMTExUbWZmZpDL5TAxMUFVVRWsrKxasQpERHQvtAqCpKQkDBgw4K7TWFhYYPv27Rrt586dw9mzZ3Hw4EHk5+fj9ddfh5eXFw4fPoyRI0ciKysLixYt0q56IiK6Z1oFgZWVFfLy8tCnTx+19ry8PBgbG8PR0fGO80ZGRqp+DgsLw6pVqwAAUVFRSExMhLe3N5ycnLQph4iI2oBWQfD2228jNDRUIwgKCwuRnJyMlJSUFi3n1j2GhIQEbUogIqI2ptVZQ/n5+XB3d9dod3Nzw7lz59qsKCIiEo9WQWBkZISqqiqN9oqKCgiC0GZFERGReLQKgoEDB+Ljjz9GY2Ojqk2hUODjjz/GwIED27w4IiLSPa2OEURGRiIkJAR+fn7w8vICAPz444+ora3Fjh07dFIgERHpllZ7BI899hj27t2L8ePHo6KiAhUVFRg/fjy++OILODg46KpGIiLSIa32CADA1tYWCxcu1EUtRESkB1oHQV1dHXJzc1FaWgqlUqn23siRI9usMCIiEodWQXDkyBEsWrQI5eXlGu8ZGBggNze3reoiIiKRaBUEy5cvx9ChQ7Fw4UL06NFDVzUREZGItAqCK1eu4KOPPmIIEBF1IFqdNdS/f39cuHBBV7UQEZEeaLVHEBwcjLi4OFy9ehXOzs4wNlaf/YknnmjT4oiISPe0CoL58+cDuHnzudvxYDERUfukVRA0PVmMiIg6Dq2CwM7OTld1EBGRnmj98PrDhw9j1qxZGDNmDIqLiwEAn332GY4ePdrmxRERke5pFQR79+7Fa6+9Bnt7exQVFUGhUAAAGhsb+YAZIqJ2SqsgSEhIQGxsLJYsWQIjIyNVu6enJw8UExG1U1oFwaVLl+Dp6anRbm5ujurq6raqiYiIRKTVwWJbW1tcvHhR46DxyZMn0atXrzYtjDqAkhKgmftSiaJLF8DGRj99E7UzWgXBCy+8gNjYWMTGxgIAiouLkZ2djX//+9+YN2+eTgqkdqy8HNDXKcfDhzMIiFpIqyCIiIhAdXU1wsPDIZfLMWXKFMhkMoSHhyM0NFRXNRIRkQ5p/TyChQsXYvbs2SgoKIAgCHBwcEDnzp11URsREYlA6yAAADMzM7i5ubV1LUREpAdaBcHs2bPv+v7HH398T8UQEZH4tAqCrl27qr1uaGhAfn4+iouL4efn16aFERGROLQKghUrVjTbvnLlSlhYWNx13uvXr2Pu3LkwNjZGY2Mjli1bhl69eiE6OhpXr16Fk5MTli5dCkNDre96QaRJLgcKC8Xvt6ZG/D6J7lGrjhHcbvLkyQgJCcHcuXPvOE3Xrl2xc+dOGBoa4vjx49iyZQv69esHV1dXzJgxA8uWLcN3330HHx+ftiiJpK66Gvj+e/H7beaCS6L7XZt8/W7JU8uMjIxU3/arqqrQp08fZGdnw9fXFwAwdOhQnDx5si3KISIiLWi1R9B0IVkTQRBw7do1ZGVlITAw8B/nLygoQExMDIqLi7FhwwYcOXIElpaWAABLS0tUVFRoUw4REbUBrYIgPz9f7bWhoSGsra3x5ptvtigIHB0dkZqairy8PLz99tuws7NDZWUlunfvjqqqKlhZWWlXPRER3TOtgmD79u2t7qi+vh4ymQwA8MADD8DU1BQDBw5EVlYWHBwckJWVhaeffrrVyyciotZpk4PFLXH27FmsXr0aBgYGAIDo6Gg89thjiI6ORmhoKBwcHPDss8+KVQ4REf1NqyAICwtTfZD/k23btqm97tevH5KTkzWmW7dunTYlEBFRG9MqCBwcHJCeno5u3brBw8MDAJCTk4Pr169j3Lhxag+rISKi9kGrIJDJZJg4cSLeeusttT2D5cuXQxAExMTEtHmBRESkW1pdR/DFF18gNDRUY3goJCQEe/fubdPCiIhIHFoFgSAIOHfunEZ7c21ERNQ+aDU0FBgYiJiYGFy6dEl1jODnn39GQkICAgICdFIgERHpllZBEBkZCWtra2zbtg0ffvghAKB79+6IiIhAeHi4TgokIiLd0ioIDA0NERERoXpkJYB/vOsoERHd31p107kzZ84gKytLdRO52tpaKBSKNi2MiIjEodUewfXr1/Hqq68iJycHBgYG+Prrr2Fubo6VK1dCJpPx9FEionZIqz2CFStWwMbGBsePH4epqamq3d/fHz/88EObF0dERLqn1R7B0aNHkZSUpHGX0J49e6K4uLhNCyMiInFotUdw48YNdOrUSaO9rKwMJiYmbVYUERGJR6sgGDhwIPbs2aPW1tjYiPj4eHh7e7dpYUREJA6tryN46aWXcObMGTQ0NCAuLg6//fYbqqurkZKSoqsaiYhIh7QKAkdHR6SnpyMlJQUymQxyuRz+/v4IDQ2Fra2trmokIiIdanEQNDQ0ICQkBHFxcZg/f74uayIiIhG1+BhBp06dUFRU1OIH0xARUfug1cHiCRMmYNeuXbqqhYiI9ECrYwR1dXVIT0/HkSNH8MQTT8Dc3FztfV5ZTETU/rQoCPLy8uDk5ITCwkL07dsXAHD58mW1aThkRETUPrUoCCZOnIjvv/8e27dvBwDMnDkTsbGxPFOIiKgDaNExAkEQ1F5nZ2dDLpfrpCAiIhJXq25DfXswEBFR+9WiIDAwMOAxACKiDqpFxwgEQUBkZKTqhnP19fV4++231W5FDQAff/xx21dIREQ61eKDxbd67rnndFIMERGJr0VBsGLFinvu6KeffsLKlSvRqVMnmJubY9WqVVAoFIiKikJNTQ2GDBmCefPm3XM/RESkHa0uKLsXDz/8MJKSkmBmZoaUlBTs2LEDlZWVCAwMxOjRozFz5kwUFBTA0dFRrJKIiAitPGuoNXr06AEzMzMAN+9bZGRkhFOnTsHX1xcAMHToUJw8eVKscoiI6G+iBUGTsrIy7Ny5E5MmTUJtba3qgLOlpSUqKirELoeISPJEGxoCbt6raMGCBYiJiYG1tTXMzMwgl8thYmKCqqoqjWchUxspKQHKy8Xvt6ZG/D6JSGuiBYFCocDChQsRFhaG/v37AwC8vLxw+PBhjBw5EllZWVi0aJFY5UhLeTmQmSl+v56e4vdJRFoTLQgyMjKQnZ2NmpoabNu2DT4+PoiIiEBUVBQSExPh7e0NJycnscohIqK/iRYEEyZMwIQJEzTaExISxCqBiIiaIfrBYiIiur8wCIiIJI5BQEQkcQwCIiKJYxAQEUkcg4CISOIYBEREEscgICKSOAYBEZHEiXrTOUnT143fAN78jYjuikEgFn3d+A3gzd+I6K44NEREJHEMAiIiiWMQEBFJHIOAiEjiGARERBLHICAikjgGARGRxDEIiIgkjkFARCRxDAIiIoljEBARSRyDgIhI4hgEREQSxyAgIpI4BgERkcSJFgQNDQ0IDg7GgAEDsH//fgBAaWkpZsyYgRdffBEbNmwQqxQiIrqFaEFgbGyM9evX4+WXX1a1xcfHIzAwECkpKThz5gwKCgrEKoeIiP4mWhAYGBjA1tZWre3UqVPw9fUFAAwdOhQnT54UqxwiIvqbXo8R1NbWwtTUFABgaWmJiooKfZZDRCRJeg0CMzMzyOVyAEBVVRWsrKz0WQ4RkSTpNQi8vLxw+PBhAEBWVhYGDBigz3KIiCTJWMzOFixYgF9++QXm5ubIyclBREQEoqKikJiYCG9vbzg5OYlZDhERQeQgWLdunUZbQkKCmCUQEd07uRwoLBS/3y5dABubNl+sqEFARNQhVFcD338vfr/Dh+skCHhlMRGRxDEIiIgkjkFARCRxDAIiIoljEBARSZz0zhoqKQHKy8Xvt6ZG/D6JiFpAekFQXg5kZorfr6en+H0SEbUAh4aIiCSOQUBEJHEMAiIiiWMQEBFJHIOAiEjiGARERBLHICAikjgGARGRxDEIiIgkjkFARCRxDAIiIoljEBARSRyDgIhI4hgEREQSxyAgIpI4BgERkcQxCIiIJI5BQEQkcXoPgl27diE4OBhhYWG4fPmyvsshIpIcvQZBeXk5PvvsMyQnJyMyMhKrVq3SZzlERJKk1yDIycnBoEGDYGxsDHd3d1y4cEGf5RARSZJeg6CiogJWVlaq14Ig6LEaIiJpMtZn55aWlsjPz1e9NjRseS79+OOPre/Yy6v1894LffWrz765ztLoW2r96qvv8nLgXj777kCvQeDh4YFNmzahsbEReXl5sLe3b9F8Xvrc+EREHYxeg6BLly6YMGECQkNDYWxsjOXLl+uzHCIiSTIQODBPRCRper+OgIiI9ItBQEQkcQwCIiKJYxAQEUmcXs8akoLr169j7ty5MDY2RmNjI5YtW4ZevXohOjoaV69ehZOTE5YuXarVNRT3k+zsbISGhuLo0aMAgKioKNTU1GDIkCGYN2+enqtrHU9PT7i5uQEAIiIiMGjQoA6zvXJycrB27Vo0NDTAx8cHAQEB7X6bFRQUYNmyZQCAmpoaCIKAlJSUDrHN3n33Xfz6669QKpVYvHgxPDw8dLNeAumUQqEQGhsbBUEQhGPHjgmLFy8WkpOThfj4eEEQBOGdd94RDh06pM8S78ncuXOFgIAAoaSkRFi5cqWwb98+QRAEISIiQvjtt9/0XF3rjB07Vu11R9lecrlciIiIEGpra1VtHWWbNUlOThY2bdrUIbbZhQsXhClTpgiCIAh//PGHEBISorP1an8R2c4YGRmpEruqqgp9+vRBdnY2fH19AQBDhw7FyZMn9Vliq3377bfw8vKCubk5AODUqVMdYr2Ki4sRGhqKxYsXo6ysrMNsr9OnT8PU1BTz589HeHg48vLyOsw2a5KRkYFx48Z1iG3WrVs3mJqaQqFQoLKyEtbW1jpbLw4NiaCgoAAxMTEoLi7Ghg0bcOTIEVhaWgK4eZuNiooKPVeoPaVSiZ07d2Ljxo3IzMwEANTW1sLU1BTAzfUqKirSZ4mtduDAAVhbWyMtLQ1r1qxBRUVFu99eAHD16lUUFBQgLS0NxcXFiImJ6TDbDACKioqgVCrRs2fPDrHNOnfujIcffhj+/v64ceMGNm7ciPXr1+tkvbhHIAJHR0ekpqZi8+bNeO+992BpaYnKykoAN/cSbr3xXnuRnp6OYcOGwcTERNVmZmYGuVwOoP2uFwBYW1sDAMaOHYvc3NwOsb2Amx8c/fv3h7m5ORwcHFBdXd1hthkA7Nu3D2PGjAGADrHNfvjhB5SXl+Prr7/G7t278e677+psvRgEOlZfX6/6+YEHHoCpqSkGDhyIrKwsAEBWVhYGDBigr/Ja7dy5c/jqq68wffp05Ofn4/XXX4eXlxcOHz4MoP2uV21tLRobGwEAJ06cgL29fYfYXsDNe3tduHABSqUS165dg0wm6xDbrMmtQdARtplSqYSVlRUMDQ1hYWGB2tpana0XbzGhYz/99BNWr14NAwMDAEB0dDQee+wxREdH4/r163BwcMA777zTLs9oaBIWFoZ169YB+N9ZQ97e3liwYIGeK9PeL7/8gpiYGFhYWEAmkyE2NhZdu3btMNsrLS0Nu3fvhkKhQGRkJBwcHNr9NgOA3377DcuXL0dSUhIAoK6urt1vs8bGRkRHR+PKlSuQy+V4+eWX4efnp5P1YhAQEUlc+4pIIiJqcwwCIiKJYxAQEUkcg4CISOIYBEREEscgICKSOAYBEZHE8V5DRFqaNWsWrl27hoaGBsybNw8jR47EmjVrsH//fjz88MMAgNmzZ2Pw4ME4dOgQNm3aBLlcDg8Pj3Z5YRN1fAwCIi3FxcWhS5cuqK6uxuTJk9GjRw8cP34cGRkZKC0txejRowEApaWl2Lp1K7Zv3w4TExMsW7YMX3/9Nfz9/fW8BkTqGAREWkpKSsI333wDALhy5QqOHTuGESNGoFOnTujRo4fq/i+nT59Gfn4+XnjhBQDAjRs3VHsMRPcTBgGRFo4dO4YzZ84gLS0NMpkM48aNg4mJCRQKhca0giBg+PDheO+99/RQKVHLcbCSSAvV1dWwsrKCTCZDTk4OCgsL0a9fPxw8eBAKhQJXr17Fjz/+CODmIy+PHj2KP//8EwBQVlam+pnofsI9AiItPPPMM9i5cyfGjh0LFxcX9OnTBzY2Nhg0aBDGjh0LOzs79OnTBxYWFrCxscG//vUvvPrqq2hoaECnTp3w3nvv4cEHH9T3ahCp4d1HidpAbW0tzM3NUVpaiuDgYOzevRsWFhb6LouoRbhHQNQGlixZggsXLkChUGDBggUMAWpXuEdARCRxPFhMRCRxDAIiIoljEBARSRyDgIhI4hgEREQSxyAgIpK4/welmeDPFZB/ygAAAABJRU5ErkJggg==\n", "text/plain": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# age distribution\n", "import seaborn as sns\n", "engine = get_rds_engine() \n", "\n", "queryAge = f\"\"\"\n", "select m.fan_id, cast(ft.age as INTEGER) as age from af65a0ef98e7420800afdea2beb81059bde2bc59d797cc0851412ac41.fan_table b\n", "inner join af65a0ef98e7420800afdea2beb81059bde2bc59d797cc0851412ac41.merch_clusters m on m.fan_id=b.fan_id\n", "inner join af65a0ef98e7420800afdea2beb81059bde2bc59d797cc0851412ac41.enriched_input ft\n", "on b.root_email = ft.email where ft.age!='Born'\n", "\"\"\"\n", "agedf= pd.read_sql(queryAge, engine)\n", "\n", "plt.figure()\n", "sns.distplot(agedf['age'], kde=False, color='red', bins=10)\n", "plt.title('Fanbase age distribution', fontsize=18)\n", "plt.ylabel('Frequency', fontsize=14)" ] } ], "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 }