(d)ata_analyysi.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Kiltisbotin kulutusdata\n",
    "\n",
    "Kiltisbot on Fyysikkokillan kiltistoimikunnan ylläpitämä Telegramin bottirajapintaan pohjautuva multimedia ratkaisu, jonka tarkoitus on helpottaa kiltalaisten elämää. Kiltibotissa on tällä hetkellä integraatiot niin Fiirumille kuin Fyysikkokillan Googlekalenteriinkin. Kiltisbotin alkuperäinen ja tärkein käyttötarkoitus on kiltiksen herkkukaapin piikkimyynnin siirtäminen 2000-luvulle eli paperisesta piikkilistasta pilveen."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Kiltisbot otettiin käyttöön 13.4.2019 ja tätä juttua varten on käytetty dataa yhden vuoden ajalta 13.4.2019 - 12.4.2020. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assumes that your ssh key is in the guild's server. Contact tech support if needed.\n",
    "!scp root@104.248.244.199:/root/fk-kiltisbot/kiltis_PROD.db ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import statistics as stats\n",
    "\n",
    "from scipy.stats import norm, normaltest\n",
    "from sklearn.decomposition import PCA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(\"kiltis_PROD.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "SELECT *\n",
    "FROM transactions\n",
    "\"\"\"\n",
    "data = pd.read_sql(sql, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_bad_names(x):\n",
    "    if x == \"X-tra suklaa\":\n",
    "        return \"X-tra tummasuklaa\"\n",
    "    elif x in [\"Fazer Mint\", \"Fazerina\", \"Geisha \"]:\n",
    "        return \"Fazer patukat\"\n",
    "    else:\n",
    "        return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[\"datetime\"] = data.aika.astype(\"datetime64\")\n",
    "data[\"date\"] = data.aika.apply(lambda x: x[:10]).astype(\"datetime64\")\n",
    "data[\"weekday\"] = data.date.apply(lambda x: x.isocalendar()[2])\n",
    "data[\"week\"] = data.date.apply(lambda x: x.isocalendar()[1])\n",
    "data[\"minute\"] = data.aika.apply(lambda x: int(x.split(\":\")[1]))\n",
    "data[\"hour\"] = data.datetime.apply(lambda x: x.hour)\n",
    "data[\"tuote\"] = data[\"tuote\"].apply(fix_bad_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nostot = data[data.tuote == \"NOSTO\"]\n",
    "panot = data[data.tuote == \"PANO\"]\n",
    "ostot = data[(data.tuote != \"NOSTO\") & (data.tuote != \"PANO\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Tapahtumia yhteensä: {}\".format(len(data)))\n",
    "print(\"Panoja {} kappaletta.  Yhteensä {} euroa.\".format(len(panot), panot.hinta.sum() / 100))\n",
    "print(\"Nostoja {} kappaletta.   Yhteensä {} euroa.\".format(len(nostot), nostot.hinta.sum() / 100))\n",
    "print(\"Ostoja {} kappaletta. Yhteensä {:.2f} euroa.\".format(len(ostot), ostot.hinta.sum() / 100))\n",
    "print(\"Sähköisen kiltispiikin tase: {} euroa\".format((panot.hinta.sum() - nostot.hinta.sum() - ostot.hinta.sum()) / 100))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tuotteiden kokonaiskysyntä"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tuotteittainen = ostot.groupby(\"tuote\").count().sort_values(\"id\", ascending = True).reset_index()\n",
    "plt.figure(1, (5, 14))\n",
    "plt.barh(tuotteittainen.tuote, tuotteittainen.id)\n",
    "plt.grid()\n",
    "#plt.tight_layout()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Päivittäiset kulutuksen vaihtelu vuoden sisällä"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "paivat = pd.DataFrame(columns=[\"date\"], data=pd.date_range(start=\"2019-04-13\", end=\"2020-04-12\"))\n",
    "paivittainen = paivat.merge(ostot, how=\"left\", on=\"date\").groupby(\"date\").count().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Keskiarvo: {:.2f}\".format(paivittainen.tuote.mean()))\n",
    "print(\"Keskihajonta: {:.2f}\".format(paivittainen.tuote.std()))\n",
    "print(\"Maksimi: {}\".format(paivittainen.tuote.max()))\n",
    "print(\"Minimi: {}\".format(paivittainen.tuote.min()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(1, (14, 8))\n",
    "plt.plot(paivittainen.date, paivittainen.tuote)\n",
    "plt.title(\"Kiltiksen päivittäinen kulutus vuoden aikana\", size=16)\n",
    "plt.ylabel(\"Ostettuja tuotteita\", size=14)\n",
    "#plt.xticks(15 + np.arange(12) * 30, kuukaudet)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig=plt.figure(1, (14, 5))\n",
    "ax = fig.add_axes([0,0,1,1])\n",
    "ax.plot(paivittainen.date, paivittainen.tuote.rolling(14).mean(),linewidth=4)\n",
    "ax.spines['top'].set_visible(False)\n",
    "ax.spines['right'].set_visible(False)\n",
    "ax.tick_params(axis='both', labelsize=22)\n",
    "ax.spines['bottom'].set_visible(False)\n",
    "ax.spines['left'].set_visible(False)\n",
    "plt.title(\"Kahden viikon liukuva keskiarvo\", size=22)\n",
    "plt.ylabel(\"Tuotetta\", size=22)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Maksimi 32 ostosta päivässä kirjattiin päivälle, jolloin järjestettiin myös killan vuoden 2020 vuosikokous."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Kulutuksen vaihtelu viikon sisällä "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "viikoittainen = ostot.groupby(\"weekday\").count().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#plt.figure(1, (4, 6))\n",
    "fig, ax = plt.subplots()\n",
    "ax.stem(np.arange(7), viikoittainen.tuote / 52)\n",
    "#plt.barh(np.arange(7), viikoittainen.tuote / 52)\n",
    "ax.set_xticklabels([\"\", \"ma\", \"ti\", \"ke\", \"to\", \"pe\", \"la\", \"su\"])\n",
    "#ax.set_xlim(8,0)\n",
    "ax.tick_params(axis='both', labelsize=12)\n",
    "#ax.invert_yaxis() \n",
    "#ax.invert_xaxis()\n",
    "#ax.xaxis.tick_top()\n",
    "#ax.yaxis.tick_right()\n",
    "ax.spines['top'].set_visible(False)\n",
    "ax.spines['right'].set_visible(False)\n",
    "ax.spines['bottom'].set_visible(False)\n",
    "ax.spines['left'].set_visible(False)\n",
    "ax.tick_params(length = 0)\n",
    "#ax.get_yaxis().yticks(np.arange(7), [\"ma\", \"ti\", \"ke\", \"to\", \"pe\", \"la\", \"su\"], size=17)\n",
    "plt.title(\"Keskimääräinen kulutus viikonpäivinä\", size=18)\n",
    "#plt.ylabel(\"Tuotetta\", size=14)\n",
    "#plt.grid(color=\"white\", axis=\"x\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Viikoittaisen kulutuksen vaihtelu vuoden sisällä"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "weeks = pd.DataFrame(columns=[\"week\"], data = 1 + (np.arange(0, 52) + 14) % 52)\n",
    "viikot = weeks.merge(ostot, how=\"left\", on=\"week\").groupby(\"week\").count().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(1, (14, 8))\n",
    "plt.bar(viikot.week, viikot.tuote)\n",
    "plt.title(\"Kiltiksen viikoittainen kulutus\", size=16)\n",
    "plt.ylabel(\"Tuotteita\", size=16)\n",
    "plt.xlabel(\"Viikko\", size=16)\n",
    "plt.xlim(0, 53)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Minuutittainen tarkastelu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "minuutittaiset = ostot.groupby(\"minute\").count().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(1, (14, 8))\n",
    "plt.bar(minuutittaiset.minute, minuutittaiset.tuote)\n",
    "plt.xticks(5 + np.arange(12) * 5, size=14)\n",
    "plt.ylabel(\"Tuotetta\", size=14)\n",
    "plt.xlabel(\"Minuutti\", size=14)\n",
    "plt.title(\"Myynti tunnin aikana\", size=16)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "daily = ostot.hour * 60 + ostot.minute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = np.linspace(0, 1440, 1000)\n",
    "mu, std = norm.fit(daily)\n",
    "p = norm.pdf(x, mu, std)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(1, (14, 8))\n",
    "ax = fig.add_axes([0,0,1,1])\n",
    "\n",
    "plt.hist(daily, density=True, bins=30, alpha=0.6)\n",
    "plt.plot(x, p, linewidth=2, color=\"k\")\n",
    "plt.title(\"Nomaalijakauma sovitettuna päivittäiseen kulutusdataan\", size=20)\n",
    "plt.ylabel(\"Tiheys\", size=20)\n",
    "plt.xlabel(\"Minuutti\", size=20)\n",
    "\n",
    "ax.tick_params(axis='both', labelsize=20)\n",
    "ax.tick_params(length = 0)\n",
    "plt.axhline(0, color=\"red\")\n",
    "#plt.grid(axis='y')\n",
    "ax.spines['top'].set_visible(False)\n",
    "ax.spines['right'].set_visible(False)\n",
    "ax.spines['bottom'].set_visible(False)\n",
    "ax.spines['left'].set_visible(False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mu"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Kiltispiikin viikonloppukäyttö\n",
    "\n",
    "Kiltalaiset jakautuvat kahteen kastiin: niihin joilla on avain kiltahuoneelle ja niihin joilta tämä saavutettu etuus puuttuu. Tutkittaessa kuinka piikin viikonloppukäyttö suhtautuu sen kokonaiskäyttöön, tämä joukko käy erottuu."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO legend kilta eliitti vs kilta plebeijit\n",
    "vkl_ostot = ostot[(ostot[\"weekday\"] == 7) | (ostot[\"weekday\"] == 6)]\n",
    "vkl = vkl_ostot[\"user\"].value_counts()\n",
    "tot = ostot[\"user\"].value_counts()\n",
    "\n",
    "x = tot.values\n",
    "y = [vkl[uid] if uid in vkl.keys() else 0 for uid in tot.keys()]\n",
    "colors = ['r' if point >= 10 else 'b' for point in y]\n",
    "\n",
    "plt.figure(1, (12, 8))\n",
    "plt.scatter(x,y,c=colors,marker='o')\n",
    "plt.grid()\n",
    "plt.xlabel('Kokonaiskäyttö')\n",
    "plt.ylabel('Viikonloppukäyttö')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Eri tuotteiden mediaaniajat\n",
    "\n",
    "Mediaanikiltalainen popsii muumilakun klo 11, juo Power Kingin klo 14, hörpää VitaminWaterin naamariin klo 15 ja skippaa päivällisen ja syö kiltahuoneella nuudelit ja fitness patukan hieman klo 17 jälkeen -- tämä kaikki tapahtuu tietenkin herkeämättömän kahvin ja teen ryystämisen ohella. Ohessa on esitetty mediaaniajat, jolloin osto on kirjattu bottiin."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 17.1.2020 server was changed which causes an offset of 2 hours\n",
    "aikakorjatut_ostot = ostot.copy()\n",
    "aikakorjatut_ostot.loc[ostot[\"date\"] > np.datetime64('2020-01-17'), \"hour\"] += 2\n",
    "aikakorjatut_ostot.loc[ostot[\"date\"] > np.datetime64('2020-01-17'), \"hour\"] %= 24\n",
    "aikakorjatut_ostot[\"quarter\"] = 4 * aikakorjatut_ostot[\"hour\"] + aikakorjatut_ostot[\"minute\"] // 15\n",
    "\n",
    "res = {}\n",
    "products = ostot[\"tuote\"].unique()\n",
    "for p in products:\n",
    "    d = aikakorjatut_ostot[aikakorjatut_ostot[\"tuote\"] == p]\n",
    "    res[p] = stats.median(d[\"quarter\"])\n",
    "res = {k: v for k, v in sorted(res.items(), key=lambda item: item[1])} # Sort by value\n",
    "\n",
    "x = list(res.values())\n",
    "y = list(res.keys())\n",
    "\n",
    "x = x[2:]\n",
    "y = y[2:]\n",
    "\n",
    "# Plot timeline\n",
    "levels = np.array([-9,9,-7,7,-5, 5, -3, 3, -1, 1])\n",
    "fig, ax = plt.subplots(figsize=(25, 12))\n",
    "\n",
    "# Create the base line\n",
    "start = min(x)\n",
    "stop = max(x)\n",
    "ax.plot((x, x), (0, 0), 'k', alpha=.5)\n",
    "\n",
    "# Iterate through median values annotating each one\n",
    "for ii, (yy, xx) in enumerate(zip(y, x)):\n",
    "    level = levels[ii % 10]\n",
    "    vert = 'top' if level < 0 else 'bottom'\n",
    "\n",
    "    ax.scatter(xx, 0, s=100, facecolor='w', edgecolor='k', zorder=9999)\n",
    "    # Plot a line up to the text\n",
    "    ax.plot((xx, xx), (0, level), c='r', alpha=.7)\n",
    "    # Give the text a faint background and align it properly\n",
    "    ax.text(xx, level, yy,\n",
    "            horizontalalignment='right', verticalalignment=vert, fontsize=24,\n",
    "            backgroundcolor=(1., 1., 1., .3))\n",
    "#ax.set(title=\"Ostojen mediaaniajat\")\n",
    "\n",
    "# Remove components for a cleaner look\n",
    "plt.setp((ax.get_yticklabels() + ax.get_yticklines() +\n",
    "          list(ax.spines.values())), visible=False)\n",
    "\n",
    "xtick_labels = [int(y // 4) for y in x]\n",
    "xtick_pos = [4*y for y in xtick_labels]\n",
    "plt.xticks(ticks=xtick_pos, labels=xtick_labels, fontsize=25,)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Kiltispiikin käytön jakautuminen\n",
    "\n",
    "Pieni joukko kiltalaisia käyttää bottia erityisen ahkerasti."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = ostot[\"user\"].value_counts()\n",
    "x = range(len(res))\n",
    "\n",
    "plt.figure(1, (14, 8))\n",
    "plt.bar(x,res.values)\n",
    "plt.ylabel(\"Rappion määrä\");\n",
    "plt.xlabel(\"Anonymisoitu kiltalainen\");\n",
    "plt.tick_params(\n",
    "    axis='x',          # changes apply to the x-axis\n",
    "    which='both',      # both major and minor ticks are affected\n",
    "    bottom=False,      # ticks along the bottom edge are off\n",
    "    top=False,         # ticks along the top edge are off\n",
    "    labelbottom=False) # labels along the bottom edge are off\n",
    "plt.grid()\n",
    "#plt.axis('off') # remove all labels, add later with other software \n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Kiltahuoneen huoltovarmuuden turvaaminen\n",
    "\n",
    "Ei tarvitse olla Supply Chain Analyst RELEXillä nähdäkseen seuraavista kuvaajista, että jos tavara on loppu, vaikuttaa se negatiivisesti tuotteen myyntiin."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#tuotteet = [\"Pätkis \", \"Daim\", \"Twix\", \"Tupla Max\", \"Fitness-patukka\"]\n",
    "#ymax = 5\n",
    "\n",
    "tuotteet = np.unique(ostot.tuote.values)\n",
    "ymax = 12\n",
    "\n",
    "fig, axs = plt.subplots(len(tuotteet), figsize=(15,len(tuotteet)))\n",
    "paivat = pd.DataFrame(columns=[\"date\"], data=pd.date_range(start=\"2019-04-13\", end=\"2020-04-12\"))\n",
    "\n",
    "for i, tuote in enumerate(tuotteet):\n",
    "    df = ostot[ostot.tuote == tuote]\n",
    "    paivittainen = paivat.merge(df, how=\"left\", on=\"date\").groupby(\"date\").count().reset_index()\n",
    "\n",
    "    axs[i].plot(paivittainen.date, paivittainen.tuote, linewidth=4, color=\"white\")\n",
    "    axs[i].spines['top'].set_visible(False)\n",
    "    axs[i].spines['right'].set_visible(False)\n",
    "    axs[i].spines['bottom'].set_visible(False)\n",
    "    axs[i].spines['left'].set_visible(False)\n",
    "    axs[i].get_xaxis().set_ticks([])\n",
    "    axs[i].get_yaxis().set_ticks([])\n",
    "    axs[i].set_ylim((0,ymax))\n",
    "    axs[i].set_ylabel(tuote, rotation=0, size=25,  horizontalalignment='right', )\n",
    "    axs[i].yaxis.set_label_coords(-0.03,0)\n",
    "\n",
    "plt.gcf().subplots_adjust(left=-0.15)\n",
    "plt.show()\n",
    "fig.savefig('temp.png', transparent=True, dpi=300, pad_inches=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## All in one master plot\n",
    "\n",
    "Seuraava heat map antaa karkean kuvan mitä tuotteita menee erityisesti. Mitä enemmän punaista, sitä suurempi kulutus. Tulkinnassa kannattaa muistaa että osa tuotteista on saattanut olla vain lyhyen aikaa saatavilla. Marginaalijakaumat näyttävät käyttäjien kokonaiskulutuksen ja tuotteiden kulutuksen jakaumat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = ostot.user.value_counts().keys()\n",
    "tuotteet = ostot.tuote.value_counts().keys()\n",
    "\n",
    "matrix = np.zeros(shape=(len(tuotteet), len(users)))\n",
    "\n",
    "for i, tuote in enumerate(tuotteet):\n",
    "    for j, user in enumerate(users):\n",
    "        matrix[i,j] = len(ostot[(ostot.tuote == tuote) & (ostot.user == user)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from matplotlib import colors\n",
    "from matplotlib.ticker import NullFormatter\n",
    "nullfmt = NullFormatter() \n",
    "from mpl_toolkits.axes_grid1.inset_locator import inset_axes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(1, figsize=(10, 10))\n",
    "\n",
    "left, width = 0.1, 0.65\n",
    "bottom, height = 0.1, 0.65\n",
    "bottom_h = left_h = left + width + 0.02\n",
    "\n",
    "rect_scatter = [left, bottom, width, height]\n",
    "rect_histx = [left, bottom_h, width, 0.5]\n",
    "rect_histy = [left_h, bottom, 0.5, height]\n",
    "\n",
    "axHeatmap = plt.axes(rect_scatter)\n",
    "axBarvert = plt.axes(rect_histx, frameon=False)\n",
    "axBarhor = plt.axes(rect_histy, frameon=False)\n",
    "\n",
    "axBarvert.xaxis.set_major_formatter(nullfmt)\n",
    "axBarhor.yaxis.set_major_formatter(nullfmt)\n",
    "\n",
    "# First plot heatmap of all sales\n",
    "colmap = colors.ListedColormap([\"white\", \"#ffeeee\", \"#ffdddd\", \"#ffbbbb\", \"#ff7777\",\"#ff0000\"])\n",
    "bounds=[0,1,5,10,20,40,80]\n",
    "norm = colors.BoundaryNorm(bounds, 6)\n",
    "im = axHeatmap.pcolor(matrix, norm=norm, cmap=colmap)\n",
    "axins = inset_axes(axHeatmap,\n",
    "                   width=\"1%\",  \n",
    "                   height=\"50%\", \n",
    "                   loc='lower left',\n",
    "                   bbox_to_anchor=(-0.24, 0., 1, 1),\n",
    "                   bbox_transform=ax.transAxes,\n",
    "                   borderpad=0,\n",
    "                   )\n",
    "fig.colorbar(im, cax=axins)\n",
    "\n",
    "axHeatmap.set_xticks(np.arange(start=0.5, stop=len(users), step=10))\n",
    "axHeatmap.set_yticks(np.arange(start=0.5, stop=len(tuotteet)))\n",
    "\n",
    "axHeatmap.set_xticklabels(np.arange(start=0, stop=len(users), step=10))\n",
    "axHeatmap.set_yticklabels(tuotteet)\n",
    "\n",
    "axHeatmap.set_xlabel(\"Anonymisoitu käyttäjä\")\n",
    "\n",
    "users_total = ostot.user.value_counts()\n",
    "tuotteet_total = ostot.tuote.value_counts()\n",
    "\n",
    "x1 = range(len(users_total))\n",
    "x2 = range(len(tuotteet_total))\n",
    "\n",
    "x1 = [x+0.5 for x in x1] # Correction so all bars show completely\n",
    "x2 = [x+0.5 for x in x2]\n",
    "\n",
    "# Bar charts for total consumtion per user and per product\n",
    "axBarvert.bar(x1,users_total.values)\n",
    "axBarhor.barh(x2,tuotteet_total.values)\n",
    "\n",
    "axBarhor.tick_params(length = 0)\n",
    "axBarvert.tick_params(length = 0)\n",
    "\n",
    "axBarvert.grid(color=\"white\")\n",
    "axBarhor.grid(color=\"white\")\n",
    "\n",
    "axBarvert.set_xlim(axHeatmap.get_xlim())\n",
    "axBarhor.set_ylim(axHeatmap.get_ylim())\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Kiltalaisten piikin kehitys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def kayttajan_kulutus(kayttaja):\n",
    "    kayttajan_data = data[data.user == kayttaja].copy()\n",
    "    hinta_tuote = zip(kayttajan_data.tuote, kayttajan_data.hinta / 100)\n",
    "    kayttajan_data[\"oikea_hinta\"] = list(map(lambda x: x[1] if x[0] == \"PANO\" else -x[1], hinta_tuote))\n",
    "    paivat = pd.date_range(start=\"2019-04-12\", end=\"2020-03-20\").to_series()\n",
    "    kulutukset = paivat.apply(lambda x: kayttajan_data[kayttajan_data.date < x].oikea_hinta.sum())\n",
    "    return (paivat, kulutukset)\n",
    "\n",
    "# list of telegram user ids\n",
    "#jabat =  \n",
    "\n",
    "texts = [\"Ugly\", \"Good\", \"Bad\"]\n",
    "saldot = [kayttajan_kulutus(jaba) for jaba in jabat]\n",
    "\n",
    "fig = plt.figure(1, (14, 8))\n",
    "ax = fig.add_axes([0,0,1,1])\n",
    "\n",
    "for i, jaba in enumerate(saldot):\n",
    "    x,y = jaba[0], jaba[1]\n",
    "    plt.plot(x, y, linewidth=3)\n",
    "    plt.text(x[-1], y[-1], \" \"+texts[i], size=22)\n",
    "\n",
    "plt.title(\"The Good, the Bad and the Ugly\", size=22)\n",
    "plt.ylabel('Saldo euroissa', fontsize=22)\n",
    "ax.tick_params(axis='both', labelsize=22)\n",
    "ax.tick_params(length = 0)\n",
    "plt.axhline(0, color=\"red\")\n",
    "#plt.grid(axis='y')\n",
    "ax.spines['top'].set_visible(False)\n",
    "ax.spines['right'].set_visible(False)\n",
    "ax.spines['bottom'].set_visible(False)\n",
    "ax.spines['left'].set_visible(False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Koko piikin saldo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def piikin_saldo():\n",
    "    temp = data.copy()\n",
    "    hinta_tuote = zip(data.tuote, data.hinta / 100)\n",
    "    temp[\"oikea_hinta\"] = list(map(lambda x: x[1] if x[0] == \"PANO\" else -x[1], hinta_tuote))\n",
    "    paivat = pd.date_range(start=\"2019-04-12\", end=\"2020-04-13\").to_series()\n",
    "    kulutukset = paivat.apply(lambda x: temp[temp.date < x].oikea_hinta.sum())\n",
    "    #return temp.datetime, temp.oikea_hinta.cumsum() \n",
    "    return (paivat, kulutukset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a, b = piikin_saldo()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(1, (14, 8))\n",
    "plt.plot(a, b, linewidth=2)\n",
    "plt.axhline(0, color=\"red\", linestyle = \"--\")\n",
    "plt.ylabel(\"Tase (€)\", size = 14)\n",
    "plt.title(\"Kiltispiikin päivittäinen tase vuoden aikana\", size=16)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Korrelaatioanalyysi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tuotteet = list(set(data[\"tuote\"]))\n",
    "kayttajat = list(set(data[\"user\"]))\n",
    "m = np.zeros((len(tuotteet), len(kayttajat)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_count(user, tuote):\n",
    "    sql = \"\"\"\n",
    "    SELECT \n",
    "    COUNT(*)\n",
    "    FROM transactions\n",
    "    WHERE user = '{}'\n",
    "    AND tuote = '{}'\n",
    "    \"\"\".format(user, tuote)\n",
    "    d = pd.read_sql(sql, conn)\n",
    "    return d.iloc[0,0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, t in enumerate(tuotteet):\n",
    "    for j, k in enumerate(kayttajat):\n",
    "        m[i, j] = get_count(k, t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "corr = np.corrcoef(m)\n",
    "\n",
    "f, ax = plt.subplots(figsize=(11, 9))\n",
    "cmap = sns.diverging_palette(220, 10, as_cmap=True)\n",
    "sns.heatmap(corr, cmap=cmap, center=0, square=True, linewidths=.5, cbar_kws={\"shrink\": .5}, xticklabels=tuotteet, yticklabels=tuotteet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "juomat = [\"Pepsi Max\", \"Power King \", \"Laitilan limu\", \"Jaffa \", \"Pepsi \", \"Ginger Ale\", \"7 Up\", \"VitaminWater\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patukat = [\"Kismet\", \"Suffeli\", \"Fazer patukat\", \"Tupla Max\", \"Twix\", \"ISOPätkis \", \"Snickers\", \"Daim\", \"Mars \",\n",
    "           \"Elovena \", \"DaCapo\", \"Mentos\", \"Kinder maxi\", \"Pepelaku\", \"Sukulaku\", \"Fitness-patukka\", \"Nuudelit \"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "suositut = list(data.groupby(\"tuote\").count().sort_values(\"id\", ascending=False).index[:21])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tuote_korrelaatio(tuotteet):\n",
    "    kayttajat = list(set(data[\"user\"]))\n",
    "    m = np.zeros((len(tuotteet), len(kayttajat)))\n",
    "    \n",
    "    for i, t in enumerate(tuotteet):\n",
    "        for j, k in enumerate(kayttajat):\n",
    "            m[i, j] = get_count(k, t)\n",
    "            \n",
    "    corr = np.clip(np.corrcoef(m), 0, 1)\n",
    "\n",
    "    f, ax = plt.subplots(figsize=(11, 9))\n",
    "    cmap = sns.diverging_palette(220, 10, as_cmap=True)\n",
    "    sns.heatmap(corr, cmap=cmap, center=0, square=True, linewidths=.5, cbar_kws={\"shrink\": .5}, xticklabels=tuotteet, yticklabels=tuotteet)\n",
    "    \n",
    "    return m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kaikki = tuote_korrelaatio(tuotteet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patukka_corr = tuote_korrelaatio(patukat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "juoma_corr = tuote_korrelaatio(juomat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "suositut_corr = tuote_korrelaatio(suositut)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Principal Componend Analysis "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pca = PCA(n_components=2)\n",
    "pca.fit(suositut_corr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pca.explained_variance_ratio_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fit = pca.fit_transform(suositut_corr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(1, (14, 8))\n",
    "plt.scatter(fit[:,0], fit[:,1], marker=\"x\")\n",
    "for i, j in enumerate(suositut):\n",
    "    plt.annotate(j, fit[i] + 0.2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deep Dive: Power King\n",
    "Miksi Power King ei korreloi minkään kanssa?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pk = data[data.is_powerking].groupby(\"user\").count().sort_values(\"user\", ascending=False).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "not_pk = data[~data.is_powerking].groupby(\"user\").count().sort_values(\"user\", ascending=False).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(1, (14,7))\n",
    "for i, k in enumerate(kayttajat):\n",
    "    not_pk = len(data[(data.user == k) & ~data.is_powerking])\n",
    "    yes_pk = len(data[(data.user == k) & data.is_powerking])\n",
    "    plt.bar(i, not_pk + yes_pk, color=\"b\")\n",
    "    plt.bar(i, yes_pk, color=\"orange\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Vastaus: Suurin osa käyttäjistä ei osta sitä ollenkaan, etenkään eniten kuluttavat. Ne jotka ostavat Power Kingiä ostavat sitä suhteessa paljon."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Käyttäjien tuoteryhmäkohtaiset kulutustottumukset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pepsimax = [\"Pepsi Max\"]\n",
    "powerking = [\"Power King \"]\n",
    "muut_limut = [\"Laitilan limu\", \"Jaffa \", \"Pepsi \", \"Ginger Ale\", \"7 Up\", \"VitaminWater\"]\n",
    "nuudelit = [\"Nuudelit \"]\n",
    "elovenafitness = [\"Elovena \", \"Fitness-patukka\"]\n",
    "transaktiot = [\"PANO\", \"NOSTO\"]\n",
    "fazer_patukat = [\"Fazer patukat\"]\n",
    "muut_karkit = [t for t in tuotteet if t not in pepsimax + powerking + muut_limut + nuudelit + elovenafitness + transaktiot + fazer_patukat]\n",
    "karkit = fazer_patukat + muut_karkit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "juomat = pepsimax + powerking\n",
    "muut = nuudelit + elovenafitness"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "kayttajat = list(data.groupby(\"user\").count().sort_values(\"id\", ascending=False).index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = []\n",
    "p = []\n",
    "m = []\n",
    "n = []\n",
    "e = []\n",
    "t = []\n",
    "f = []\n",
    "r = []\n",
    "for i, k in enumerate(kayttajat):\n",
    "    total = len(data[data.user == k])\n",
    "    x.append(len(data[data[\"tuote\"].apply(lambda x: x in pepsimax)       & (data.user == k)]))\n",
    "    p.append(len(data[data[\"tuote\"].apply(lambda x: x in powerking)      & (data.user == k)]))\n",
    "    m.append(len(data[data[\"tuote\"].apply(lambda x: x in muut_limut)     & (data.user == k)]))\n",
    "    n.append(len(data[data[\"tuote\"].apply(lambda x: x in nuudelit)       & (data.user == k)]))\n",
    "    e.append(len(data[data[\"tuote\"].apply(lambda x: x in elovenafitness) & (data.user == k)]))\n",
    "    t.append(len(data[data[\"tuote\"].apply(lambda x: x in transaktiot)    & (data.user == k)]))\n",
    "    f.append(len(data[data[\"tuote\"].apply(lambda x: x in fazer_patukat)  & (data.user == k)]))\n",
    "    r.append(len(data[data[\"tuote\"].apply(lambda x: x in muut_karkit)    & (data.user == k)]))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = np.array(x)\n",
    "p = np.array(p)\n",
    "m = np.array(m)\n",
    "n = np.array(n)\n",
    "e = np.array(e)\n",
    "t = np.array(t)\n",
    "r = np.array(r)\n",
    "f = np.array(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(1, (14, 7))\n",
    "i = np.arange(len(kayttajat))\n",
    "plt.bar(i, x, bottom = 0, label=\"Pepsi Max\")\n",
    "plt.bar(i, p, bottom = x, label=\"Power King\")\n",
    "plt.bar(i, m, bottom = x + p, label=\"Muut limut\")\n",
    "plt.bar(i, n, bottom = x + p + m, label=\"Nuudelit\")\n",
    "plt.bar(i, e, bottom = x + p + m + n, label=\"Elovena ja Fitness\")\n",
    "plt.bar(i, t, bottom = x + p + m + n + e, label=\"Transaktiot\")\n",
    "plt.bar(i, f, bottom = x + p + m + n + e + t, label=\"Fazer patukat\")\n",
    "plt.bar(i, r, bottom = x + p + m + n + e + t + f, label=\"Muut karkit\")\n",
    "plt.legend()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(1, (14, 7))\n",
    "i = np.arange(len(kayttajat))\n",
    "juomat = x + p + m\n",
    "muut = n + e\n",
    "plt.bar(i, f, bottom = 0, label=\"Fazer patukat\")\n",
    "plt.bar(i, r, bottom = f, label=\"Muut karkit\")\n",
    "plt.bar(i, juomat, bottom = f + r, label=\"Juomat\")\n",
    "plt.bar(i, muut, bottom = f + r + juomat, label=\"Muut\")\n",
    "plt.legend()"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}