{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d11116ad-27cb-4d0f-b9e6-148516e0419f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pandas as ps\n",
    "import pandas as pt\n",
    "import pandas as pu\n",
    "import pandas as pV\n",
    "import os\n",
    "from wmfdata import hive, mariadb\n",
    "from wmfdata.spark import get_session\n",
    "from collections import defaultdict\n",
    "\n",
    "pd.set_option('display.max_rows', 600)\n",
    "pd.set_option('display.max_colwidth',70)\n",
    "\n",
    "def query_1(start, end, wiki):\n",
    "    query = \"\"\"\n",
    "            SELECT\n",
    "                COUNT(*) as '\"\"\"+wiki+\"\"\"_notifications'\n",
    "                FROM echo_notification\n",
    "                WHERE\n",
    "                    notification_timestamp > '\"\"\" + start + \"\"\"' AND\n",
    "                    notification_timestamp < '\"\"\" + end + \"\"\"'\n",
    "                GROUP BY notification_user\n",
    "                ORDER BY COUNT(*) DESC\"\"\"\n",
    "    pd = mariadb.run(query, wiki, \"wikishared\",\"pandas\")\n",
    "    \n",
    "    return pd\n",
    "\n",
    "wikidb = [\"ptwiki\", \"ruwiki\"]\n",
    "measures = [\"daily_means\", \"thirty_day_mean\", \"std\",\"max\",\"daily_medians\", \"thirty_day_medians\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d29b9fd4-661b-4254-934b-4c31fa910117",
   "metadata": {},
   "outputs": [],
   "source": [
    "# We use wmfdata boilerplate to init a spark session.\n",
    "# Under the hood the library uses findspark to initialise\n",
    "# Spark's environment. pyspark imports will be available \n",
    "# after initialisation\n",
    "spark = get_session(type='regular', app_name=\"ImageRec Training\")\n",
    "import pyspark\n",
    "import pyspark.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76df426f-052b-481e-99d4-662da7eb1da3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def query_1(start, end, wiki):\n",
    "    query = \"\"\"\n",
    "            SELECT\n",
    "                COUNT(*) as '\"\"\"+wiki+\"\"\"_notifications'\n",
    "                FROM echo_notification\n",
    "                WHERE\n",
    "                    notification_timestamp > '\"\"\" + start + \"\"\"' AND\n",
    "                    notification_timestamp < '\"\"\" + end + \"\"\"'\n",
    "                GROUP BY notification_user\n",
    "                ORDER BY COUNT(*) DESC\"\"\"\n",
    "    pd = mariadb.run(query, wiki, \"wikishared\",\"pandas\")\n",
    "    \n",
    "    return pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa24c154-3d63-4e16-a645-f956f9f2ff67",
   "metadata": {},
   "outputs": [],
   "source": [
    "#This is just provided for context in detailing about "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7dcb5566-8b62-42bf-a0fc-942dceb64d0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# number of articles users are watching for all relevant wikis - maximum, average, median\n",
    "# (based on mariadb queries)\n",
    "\n",
    "pd = mariadb.run(\"\"\" SELECT\n",
    "                COUNT(*) as 'articles_watching'\n",
    "                FROM watchlist\n",
    "                WHERE\n",
    "                    wl_namespace = 0\n",
    "                GROUP BY wl_user\n",
    "                ORDER BY COUNT(*)\"\"\", \"ptwiki\")\n",
    "\n",
    "print(pd[\"articles_watching\"].mean())\n",
    "print(pd[\"articles_watching\"].median())\n",
    "print(pd[\"articles_watching\"].max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "282aa5cd-0b34-490b-a011-354b9f3ea0b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "sdf = spark.read.csv('ru.unillustrated.csv').toDF(\"page_id\", \"page_title\")\n",
    "sdf.createOrReplaceTempView('unillustrated')\n",
    "\n",
    "query = \"\"\"\n",
    "    SELECT page_id, page_title\n",
    "    FROM unillustrated\n",
    "    \"\"\"\n",
    "pd = spark.sql(query).toPandas()\n",
    "pd [\"is_unillustrated\"]=\"y\"\n",
    "print(pd)\n",
    "print(\"------------------------------------------\")\n",
    "\n",
    "pd['page_id'] = pd['page_id'].astype(str).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa9b77ca-206c-4a02-a0fc-432053e0f4bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "    SELECT wl_user, wl_title, page_id, page_namespace\n",
    "    FROM watchlist\n",
    "    JOIN page on page_title = wl_title\n",
    "    WHERE\n",
    "        wl_namespace = 0\n",
    "    \"\"\"\n",
    "ps = mariadb.run(query, \"ruwiki\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a3ae84f-8caa-460d-8b3d-ca9f77825cd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt = ps.merge(pd, on=\"page_id\", how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e28bf0c1-5adb-4d28-b442-a83844059c5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt['wl_user'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d85974a-a36a-4823-a5fe-7d02f30b6b56",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt[\"page_actor_hash\"] = pt[\"page_id\"].astype(str) + \"_\" + pt[\"wl_user\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d17ed005-1f2e-490b-9d5a-49984043f30e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pt = pt[ pt[\"is_unillustrated\"] == \"y\" ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57b31fc9-1557-4feb-a63a-2cd1ec6b072e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# a count of users that will be notified of suggested images for unillustrated articles they are watching, for all relevant wikis:\n",
    "# (based on mariadb queries and prior unillustrated article list dump )\n",
    "\n",
    "pt['wl_user'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c2ca9d6-b0e9-4e93-a62f-2f324f385df1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# a count of unillustrated articles that users are watching, for which there is a suggestion of an image, for all relevant wikis\n",
    "# (based on mariadb queries and prior unillustrated article list dump )\n",
    "\n",
    "pt['wl_title'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f75a1cc9-e386-47de-b015-1c7287fdbc2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac3e9396-7b4b-4f5a-ac5f-5e4dbac62ad0",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "    SELECT rev_page, rev_actor\n",
    "    FROM revision\n",
    "    WHERE\n",
    "        rev_timestamp > 20220221\n",
    "    GROUP BY rev_page, rev_actor\n",
    "    \"\"\"\n",
    "ps = mariadb.run(query, \"ruwiki\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d31459e5-451e-4007-b120-b087a04eeb22",
   "metadata": {},
   "outputs": [],
   "source": [
    "ps[\"page_actor_hash\"] = ps[\"rev_page\"].astype(str) + \"_\" + ps[\"rev_actor\"].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b998a89-c99a-409d-a6ed-f61c761cebea",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "print(ps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f4f6938-6a97-46d1-8484-3fab5da8b4b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "pu = pt.merge(ps, on=\"page_actor_hash\", how=\"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6bddab7-3f9b-4986-8d6a-56c8a359e81f",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf31a776-acee-481f-be4c-f34c459dc980",
   "metadata": {},
   "outputs": [],
   "source": [
    "# a count of users that will be notified of suggested images for unillustrated articles they have edited in the last 30 days, for all relevant wikis\n",
    "# (based on mariadb queries and prior unillustrated article list dump )\n",
    "\n",
    "pu['wl_user'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e4725ea-d57e-4573-82b3-a51f6dca99b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "mariadb.run(\"\"\"\n",
    "DESCRIBE echo_notification\"\"\", \"viwiki\", \"wikishared\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "457ea29d-40dd-4304-a607-eda3cf9deec9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This provides the basic statistics consisting of:\n",
    "#      * 1 day means for a period of 30 days\n",
    "#      * 1 day medians for a period of 30 days\n",
    "#      * 30 day mean\n",
    "#      * 30 day median"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02b9691b-686d-425d-85fe-3729025c1158",
   "metadata": {},
   "outputs": [],
   "source": [
    "a = 20220101\n",
    "\n",
    "wikistats = {}\n",
    "\n",
    "for w in wikidb:\n",
    "    wikistats[w]= {}\n",
    "\n",
    "for w in wikidb:\n",
    "    mean = []\n",
    "    median = []\n",
    "    maximum = []\n",
    "    time_a = a\n",
    "    time_b = a + 1\n",
    "    time_c = a + 29\n",
    "    for x in range(29):\n",
    "        time_a += 1\n",
    "        time_b += 1\n",
    "        pd = query_1(str(time_a),str(time_b),w)\n",
    "        mean.append( round(pd[w + \"_notifications\"].mean(), 2) )\n",
    "        median.append( round(pd[w + \"_notifications\"].median(), 2) )\n",
    "        maximum.append( pd[w + \"_notifications\"].max() )\n",
    "    \n",
    "        wikistats[w][\"onedaymean\"] = mean\n",
    "        wikistats[w][\"onedaymedian\"] = median\n",
    "        wikistats[w][\"onedaymaximum\"] = maximum\n",
    "    pd = query_1(str(time_a),str(time_c),w)\n",
    "    wikistats[w][\"thirtydaymean\"] = pd[w + \"_notifications\"].mean()\n",
    "    wikistats[w][\"thirtydaymedian\"] = pd[w + \"_notifications\"].median()\n",
    "\n",
    "\n",
    "print(\"Basic stats generated\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8ab4061-b99f-4ee8-89c4-fc59c2741300",
   "metadata": {},
   "outputs": [],
   "source": [
    " pd = query_1(str(20220101),str(20220130),\"ptwiki\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb70fd73-6e3a-49dd-a1d0-3d08bc4ae4ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd[\"ptwiki_notifications\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c1e6315-3361-4940-a29c-dd29d9fe996a",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd[\"ptwiki_notifications\"].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13a74659-076a-4950-9eb9-ee48eee55490",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd[\"ptwiki_notifications\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57f8ef38-7a8a-42d4-8404-7babf20d618c",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(pd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13d7e421-8e69-44cc-a5e7-237b57ce0c7b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ed4ed29-6d35-4cfe-8ad1-277971ebe401",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(wikistats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6384103-55a0-48d0-89d1-142f89711908",
   "metadata": {},
   "outputs": [],
   "source": [
    "for w in wikidb:\n",
    "    thirtydaymeantop10 = []\n",
    "    thirtydaymediantop10 = []\n",
    "    thirtydaymeantop25 = []\n",
    "    thirtydaymediantop25 = []\n",
    "    time_a = a\n",
    "    time_b = a + 1\n",
    "    time_c = a + 29\n",
    "    pd = query_1(str(time_a),str(time_c),w)\n",
    "    n_top_25 = len(pd[w + \"_notifications\"])/4\n",
    "    pd = pd.head(int(n_top_25))\n",
    "    thirtydaymeantop25.append( pd[w +\"_notifications\"].mean() )\n",
    "    thirtydaymediantop25.append( pd[w +\"_notifications\"].median() )\n",
    "    \n",
    "    pd = query_1(str(time_a),str(time_c),w)\n",
    "    n_top_10 = len(pd[w + \"_notifications\"])/10\n",
    "    pd = pd.head(int(n_top_10))\n",
    "    thirtydaymeantop10.append( pd[w +\"_notifications\"].mean() )\n",
    "    thirtydaymediantop10.append( pd[w +\"_notifications\"].median() )\n",
    "\n",
    "    wikistats[w][\"thirtydaymeantop25\"] = thirtydaymeantop25\n",
    "    wikistats[w][\"thirtydaymediantop25\"] = thirtydaymediantop25\n",
    "    wikistats[w][\"thirtydaymeantop10\"] = thirtydaymeantop10\n",
    "    wikistats[w][\"thirtydaymediantop10\"] = thirtydaymediantop10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e7e14e6-576a-4041-b7bd-480f7a485f92",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(wikistats['ruwiki'][\"thirtydaymeantop25\"])\n",
    "print(wikistats['ruwiki'][\"thirtydaymediantop25\"])\n",
    "print(wikistats['ruwiki'][\"thirtydaymeantop10\"])\n",
    "print(wikistats['ruwiki'][\"thirtydaymediantop10\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afe92005-88e5-40a2-a28b-9e7bf2733f7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(wikistats['ptwiki'][\"thirtydaymeantop25\"])\n",
    "print(wikistats['ptwiki'][\"thirtydaymediantop25\"])\n",
    "print(wikistats['ptwiki'][\"thirtydaymeantop10\"])\n",
    "print(wikistats['ptwiki'][\"thirtydaymediantop10\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5365fcda-86ce-435a-a850-a95ee92d6e4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "for w in wikidb:\n",
    "    onedaymeantop10 = []\n",
    "    onedaymediantop10 = []\n",
    "    time_a = a\n",
    "    time_b = a + 1\n",
    "    time_c = a + 29\n",
    "    for x in range(29):\n",
    "        time_a += 1\n",
    "        time_b += 1\n",
    "        pd = query_1(str(time_a),str(time_b),w)\n",
    "        n_top_10 = len(pd[w + \"_notifications\"])/10\n",
    "        pd = pd.head(int(n_top_10))\n",
    "        onedaymeantop10.append( round(pd[w + \"_notifications\"].mean(), 2) )\n",
    "        onedaymediantop10.append( round(pd[w + \"_notifications\"].median(), 2) )\n",
    "    \n",
    "        wikistats[w][\"onedaymeantop10\"] = onedaymeantop10\n",
    "        wikistats[w][\"onedaymediantop10\"] = onedaymediantop10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7efba46-64ff-4221-b45a-01b0bcbfd39c",
   "metadata": {},
   "outputs": [],
   "source": [
    "for w in wikidb:\n",
    "    onedaymeantop25 = []\n",
    "    onedaymediantop25 = []\n",
    "    time_a = a\n",
    "    time_b = a + 1\n",
    "    time_c = a + 29\n",
    "    for x in range(29):\n",
    "        time_a += 1\n",
    "        time_b += 1\n",
    "        pd = query_1(str(time_a),str(time_b),w)\n",
    "        n_top_25 = len(pd[w + \"_notifications\"])/4\n",
    "        pd = pd.head(int(n_top_25))\n",
    "        onedaymeantop25.append( round(pd[w + \"_notifications\"].mean(), 2) )\n",
    "        onedaymediantop25.append( round(pd[w + \"_notifications\"].median(), 2) )\n",
    "    \n",
    "        wikistats[w][\"onedaymeantop25\"] = onedaymeantop25\n",
    "        wikistats[w][\"onedaymediantop25\"] = onedaymediantop25"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b157d14-4a25-4c0f-856d-4e5d6c706659",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(wikistats['ruwiki'][\"onedaymeantop10\"])\n",
    "print(wikistats['ruwiki'][\"onedaymediantop10\"])\n",
    "print(wikistats['ruwiki'][\"onedaymeantop25\"])\n",
    "print(wikistats['ruwiki'][\"onedaymediantop25\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba0949c1-57b1-4417-956f-1c51f3fba218",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(wikistats['ptwiki'][\"onedaymeantop10\"])\n",
    "print(wikistats['ptwiki'][\"onedaymediantop10\"])\n",
    "print(wikistats['ptwiki'][\"onedaymeantop25\"])\n",
    "print(wikistats['ptwiki'][\"onedaymediantop25\"])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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": 5
}