{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Geoguessr game analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from os import environ\n",
    "import atoti as tt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "config = \"mybinder-config.yml\" if \"JUPYTERHUB_SERVICE_PREFIX\" in environ else \"local-config.yml\"\n",
    "session = tt.create_session(\"Geoguessr\", config=config)\n",
    "print(session.url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "scores = session.read_csv(\"data/scores.csv\", keys=[\"GameId\", \"Round\", \"Player\"])\n",
    "games = session.read_csv(\"data/games.csv\",  keys=[\"GameId\"])\n",
    "rounds = session.read_csv(\"data/rounds.csv\", keys=[\"GameId\", \"Round\"])\n",
    "continents = session.read_csv(\"data/continents.csv\", keys=[\"Country\"], sep=\",\")\n",
    "scores.join(games, mapping={\"GameId\": \"GameId\"})\n",
    "scores.join(rounds, mapping={\"GameId\": \"GameId\", \"Round\": \"Round\"})\n",
    "rounds.join(continents, mapping={\"Country\": \"Country\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.stores.schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hierarchy structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cube = session.create_cube(scores)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m, lvl, h = cube.measures, cube.levels, cube.hierarchies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "h[\"Games\"] = [scores[\"GameId\"], scores[\"Round\"]]\n",
    "h[\"Round Number\"] = { \"Round Number\":  scores[\"Round\"]}\n",
    "del h[\"GameId\"]\n",
    "del h[\"Round\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "h[\"Round Number\"] = { \"Round Number\":  scores[\"Round\"]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "h[\"Geography\"] = [continents[\"Continent\"], rounds[\"Country\"]]\n",
    "del h[\"Continent\"]\n",
    "del h[\"Country\"]\n",
    "h[\"Geography\"].dimension = \"Geography\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "h"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## New measures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"score\"] = tt.agg.mean(\n",
    "    tt.agg.sum(scores[\"Score\"]),\n",
    "    scope=tt.scope.origin(lvl[\"Player\"], lvl[\"GameId\"])\n",
    ")\n",
    "m[\"score\"].formatter = \"DOUBLE[#,###]\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"avg score\"] = tt.agg.mean(scores[\"Score\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "atoti": {
     "widget": {
      "filters": [
       "{[Scores].[Player].[AllMember].[Benjamin], [Scores].[Player].[AllMember].[Fabien], [Scores].[Player].[AllMember].[Mehdi], [Scores].[Player].[AllMember].[Michael], [Scores].[Player].[AllMember].[Thibault], [Scores].[Player].[AllMember].[Daniel]}"
      ],
      "mapping": {
       "horizontalSubplots": [],
       "splitBy": [
        "ALL_MEASURES",
        "[Scores].[Player].[Player]"
       ],
       "values": [
        "[Measures].[score]"
       ],
       "verticalSubplots": [],
       "xAxis": [
        "[Games].[Date].[Date]"
       ]
      },
      "query": {
       "mdx": "SELECT NON EMPTY Hierarchize(Descendants({[Games].[Date].[AllMember]}, 1, SELF_AND_BEFORE)) ON ROWS, NON EMPTY Crossjoin({[Measures].[score]}, Hierarchize(Descendants({[Scores].[Player].[AllMember]}, 1, SELF_AND_BEFORE))) ON COLUMNS FROM [Scores]",
       "updateMode": "once"
      },
      "serverKey": "default",
      "widgetKey": "plotly-line-chart"
     }
    }
   },
   "outputs": [],
   "source": [
    "session.visualize()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "atoti": {
     "height": 818,
     "widget": {
      "mapping": {
       "horizontalSubplots": [],
       "stackBy": [
        "ALL_MEASURES"
       ],
       "values": [
        "[Measures].[avg score]"
       ],
       "verticalSubplots": [],
       "yAxis": [
        "[Geography].[Geography].[Country]"
       ]
      },
      "query": {
       "mdx": "SELECT NON EMPTY Order(Hierarchize(Descendants({[Geography].[Geography].[AllMember]}, 2, SELF_AND_BEFORE)), [Measures].[avg score], BASC) ON ROWS, NON EMPTY {[Measures].[avg score]} ON COLUMNS FROM [Scores]",
       "updateMode": "once"
      },
      "serverKey": "default",
      "widgetKey": "plotly-stacked-bar-chart"
     }
    }
   },
   "outputs": [],
   "source": [
    "session.visualize(\"Average Score per country\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "atoti": {
     "widget": {
      "filters": [
       "{[Geography].[Geography].[AllMember].[Africa], [Geography].[Geography].[AllMember].[Asia], [Geography].[Geography].[AllMember].[Europe], [Geography].[Geography].[AllMember].[North America], [Geography].[Geography].[AllMember].[Oceania], [Geography].[Geography].[AllMember].[South America]}"
      ],
      "mapping": {
       "horizontalSubplots": [],
       "stackBy": [
        "ALL_MEASURES"
       ],
       "values": [
        "[Measures].[avg score]"
       ],
       "verticalSubplots": [],
       "xAxis": [
        "[Geography].[Geography].[Continent]"
       ]
      },
      "query": {
       "mdx": "SELECT NON EMPTY {[Measures].[avg score]} ON COLUMNS, NON EMPTY Order(Hierarchize(Descendants({[Geography].[Geography].[AllMember]}, 1, SELF_AND_BEFORE)), [Measures].[avg score], BDESC) ON ROWS FROM [Scores]",
       "updateMode": "once"
      },
      "serverKey": "default",
      "widgetKey": "plotly-stacked-column-chart"
     }
    }
   },
   "outputs": [],
   "source": [
    "session.visualize(\"Average Score per continent\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"one\"] = 1\n",
    "m[\"Number of rounds\"] = tt.agg.sum(m[\"one\"], scope=tt.scope.origin(lvl[\"Round\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "atoti": {
     "height": 747,
     "widget": {
      "mapping": {
       "horizontalSubplots": [],
       "stackBy": [
        "ALL_MEASURES"
       ],
       "values": [
        "[Measures].[Number of rounds]"
       ],
       "verticalSubplots": [],
       "yAxis": [
        "[Geography].[Geography].[Country]"
       ]
      },
      "query": {
       "mdx": "SELECT NON EMPTY {[Measures].[Number of rounds]} ON COLUMNS, NON EMPTY Order(Hierarchize(Descendants({[Geography].[Geography].[AllMember]}, 2, SELF_AND_BEFORE)), [Measures].[Number of rounds], BASC) ON ROWS FROM [Scores]",
       "updateMode": "once"
      },
      "serverKey": "default",
      "widgetKey": "plotly-stacked-bar-chart"
     }
    }
   },
   "outputs": [],
   "source": [
    "session.visualize(\"Most seen countries\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "atoti": {
     "widget": {
      "filters": [
       "{[Geography].[Geography].[AllMember].[Africa], [Geography].[Geography].[AllMember].[Asia], [Geography].[Geography].[AllMember].[Europe], [Geography].[Geography].[AllMember].[North America], [Geography].[Geography].[AllMember].[Oceania], [Geography].[Geography].[AllMember].[South America]}"
      ],
      "mapping": {
       "horizontalSubplots": [],
       "sliceBy": [
        "[Geography].[Geography].[Continent]"
       ],
       "values": [
        "[Measures].[Number of rounds]"
       ],
       "verticalSubplots": []
      },
      "query": {
       "mdx": "SELECT NON EMPTY {[Measures].[Number of rounds]} ON COLUMNS, NON EMPTY Hierarchize(Descendants({[Geography].[Geography].[AllMember]}, 1, SELF_AND_BEFORE)) ON ROWS FROM [Scores]",
       "updateMode": "once"
      },
      "serverKey": "default",
      "widgetKey": "plotly-pie-chart"
     }
    }
   },
   "outputs": [],
   "source": [
    "session.visualize(\"Most seen continent\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"find country rate\"] = tt.filter(m[\"contributors.COUNT\"], lvl[\"HasCountry\"] == True) / m[\"contributors.COUNT\"]\n",
    "m[\"find country rate\"].formatter = \"DOUBLE[0.00%]\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "atoti": {
     "widget": {
      "mapping": {
       "columns": [
        "ALL_MEASURES"
       ],
       "measures": [
        "[Measures].[find country rate]"
       ],
       "rows": [
        "[Scores].[Player].[Player]"
       ]
      },
      "query": {
       "mdx": "SELECT NON EMPTY {[Measures].[find country rate]} ON COLUMNS, NON EMPTY Order(Hierarchize(Descendants({[Scores].[Player].[AllMember]}, 1, SELF_AND_BEFORE)), [Measures].[find country rate], DESC) ON ROWS FROM [Scores]",
       "updateMode": "once"
      },
      "serverKey": "default",
      "widgetKey": "pivot-table"
     }
    }
   },
   "outputs": [],
   "source": [
    "session.visualize(\"Find country rate per user\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"Best score\"] = tt.agg.max(m[\"score\"], scope=tt.scope.origin(lvl[\"GameId\"], lvl[\"Player\"]))\n",
    "m[\"Worst score\"] = tt.agg.min(m[\"score\"], scope=tt.scope.origin(lvl[\"GameId\"], lvl[\"Player\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cube.query(m[\"Best score\"], levels=lvl[\"Player\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m[\"Winner\"] = tt.where(\n",
    "    lvl[\"GameId\"] != None,\n",
    "    tt.agg.max_member(m[\"score\"], lvl[\"Player\"])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}