01-deletion_stats_q4_fy23.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6d2d64ec-1cac-4d60-b961-fb9cd219241a",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Content Translation Article Deletion Ratios, across all wikis\n",
    "\n",
    "**Last updated on 23 October 2024**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed1bf803-1965-4432-81a5-43cca8ff7fdf",
   "metadata": {},
   "source": [
    "# Contents\n",
    "\n",
    "1. [Overview](#Overview)\n",
    "2. [Data Gathering](#Data-Gathering)\n",
    "3. [Analysis](#Analysis)\n",
    "    * [Current Quarter - FY23 Q4](#Current-Quarter)\n",
    "    * [Previous Quarter - FY23 Q3](#Previous-Quarter)\n",
    "4. [Formatting](#Formatting)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cf202e3-3b0e-4b80-994b-065dc52b5910",
   "metadata": {},
   "source": [
    "# Overview\n",
    "\n",
    "## Purpose\n",
    "The purpose of this analysis is to identify and list the number of wikis where the deletion rate of articles created with content translation is higher than the deletion rate for articles created with other tools. Specifically, we want to answer the following questions:\n",
    "* How many wikis have translations deleted more often than regular articles?\n",
    "* Which are these wikis?\n",
    "* Has the number of those wikis reduced compared to the previous period?\n",
    "* How high is the highest deletion ratio a wiki has for translations?\n",
    "* This analysis will be used as a baseline to assess the evolution of deletion rates as improvements are made.\n",
    "\n",
    "Results are posted at: https://www.mediawiki.org/wiki/Content_translation/Deletion_statistics_comparison#April_to_June_2024_(Q4_2023-24)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "022fee1d-149a-4abc-97c1-60d8adf712c8",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Data-Gathering"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aaef8a03-4f67-4e09-95fa-285fb15b9604",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f2e8e3f7-c9b8-4bc3-a379-7b0259443248",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import wmfdata as wmf\n",
    "\n",
    "pd.options.display.max_columns = None\n",
    "from IPython.display import clear_output\n",
    "\n",
    "import importlib\n",
    "import warnings\n",
    "\n",
    "import data_functions as dtf\n",
    "import formatting_functions as ftf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3139d8ec-d87f-48a0-a203-74b0a19b1c3d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'formatting_functions' from '/srv/home/kcvelaga/git/content-translation-deletion-stats/formatting_functions.py'>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "importlib.reload(dtf)\n",
    "importlib.reload(ftf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cd65304b-5d5f-40ca-8f2b-41c5b15c6195",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - hive</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://stat1011.eqiad.wmnet:4046\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.1.2</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>yarn</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>cx-del-stats-jun24</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7f8fa1fce620>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark_session = wmf.spark.get_active_session()\n",
    "\n",
    "if type(spark_session) == type(None):\n",
    "    spark_session = wmf.spark.create_custom_session(\n",
    "        master=\"yarn\",\n",
    "        app_name='cx-del-stats-jun24',\n",
    "        spark_config={\n",
    "            \"spark.driver.memory\": \"4g\",\n",
    "            \"spark.dynamicAllocation.maxExecutors\": 64,\n",
    "            \"spark.executor.memory\": \"16g\",\n",
    "            \"spark.executor.cores\": 4,\n",
    "            \"spark.sql.shuffle.partitions\": 256,\n",
    "            \"spark.driver.maxResultSize\": \"2g\"\n",
    "        }\n",
    "    )\n",
    "\n",
    "spark_session.sparkContext.setLogLevel(\"ERROR\")\n",
    "\n",
    "clear_output()\n",
    "\n",
    "spark_session"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74e6b40e-a6ad-40e5-9c0d-1873e7b76fd5",
   "metadata": {},
   "source": [
    "## run query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "718001d4-987e-4cc7-8e30-c704eedc8cf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "currq_dates = dtf.generate_quarters(2023)['Q4']\n",
    "prevq_dates = dtf.generate_quarters(2023)['Q3']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "819bdab7-121c-48bb-8730-e288e6b1f2a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 5:======================================================>(255 + 1) / 256]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 731 ms, sys: 137 ms, total: 868 ms\n",
      "Wall time: 4min 10s\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "deletion_stats_currq_all = dtf.query_deletion_stats(currq_dates)\n",
    "deletion_stats_prevq_all = dtf.query_deletion_stats(prevq_dates)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fc4e090-4c76-4971-b79b-6d4d807f2aec",
   "metadata": {},
   "source": [
    "# Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9833d970-ba25-4b62-be25-e9ee7f70680d",
   "metadata": {},
   "source": [
    "## Current-Quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e3fc0f9a-a49c-427d-a77e-6207a6533d01",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "During FY23-Q4, overall percentage of articles that were deleted,\n",
      "\t- created using the Content Translation tool: 4.73%\n",
      "\t- created without using the Content Translation Tool: 11.46%\n"
     ]
    }
   ],
   "source": [
    "# overal deletion percent\n",
    "dtf.overall_deletion_pct(deletion_stats_currq_all, period='FY23-Q4', pr=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "66a25161-0cfb-49f0-aba1-f2f00d10c822",
   "metadata": {},
   "outputs": [],
   "source": [
    "# deletion ratio by wiki\n",
    "deletion_stats_currq = dtf.generate_ratios_by_wiki(deletion_stats_currq_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "60d661f6-5b18-4d88-80ba-6a5987dbbd2b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "During FY23-Q4, across all wikis where more than 15 articles have been created with the Content Translation tool,\n",
      "there were 16 wikis where the deletion rate of articles created using CX was atleast 2% higher than articles created without using CX.\n"
     ]
    }
   ],
   "source": [
    "print(f'During FY23-Q4, across all wikis where more than 15 articles have been created with the Content Translation tool,\\n\\\n",
    "there were {deletion_stats_currq.query(\"\"\"deletion_pct_diff <= -2\"\"\").shape[0]} wikis where the deletion rate of articles created \\\n",
    "using CX was atleast 2% higher than articles created without using CX.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8430b398-c6df-4879-a505-33c8f6d46b70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>created_cx</th>\n",
       "      <th>created_non_cx</th>\n",
       "      <th>deleted_cx</th>\n",
       "      <th>deleted_non_cx</th>\n",
       "      <th>deleted_cx_pct</th>\n",
       "      <th>deleted_non_cx_pct</th>\n",
       "      <th>deletion_pct_diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wiki_db</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>yiwiki</th>\n",
       "      <td>41</td>\n",
       "      <td>21</td>\n",
       "      <td>32</td>\n",
       "      <td>8</td>\n",
       "      <td>78.05</td>\n",
       "      <td>38.10</td>\n",
       "      <td>-39.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kuwiki</th>\n",
       "      <td>30</td>\n",
       "      <td>846</td>\n",
       "      <td>10</td>\n",
       "      <td>45</td>\n",
       "      <td>33.33</td>\n",
       "      <td>5.32</td>\n",
       "      <td>-28.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>swwiki</th>\n",
       "      <td>21</td>\n",
       "      <td>2591</td>\n",
       "      <td>6</td>\n",
       "      <td>47</td>\n",
       "      <td>28.57</td>\n",
       "      <td>1.81</td>\n",
       "      <td>-26.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lvwiki</th>\n",
       "      <td>38</td>\n",
       "      <td>3397</td>\n",
       "      <td>9</td>\n",
       "      <td>252</td>\n",
       "      <td>23.68</td>\n",
       "      <td>7.42</td>\n",
       "      <td>-16.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ocwiki</th>\n",
       "      <td>16</td>\n",
       "      <td>541</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>18.75</td>\n",
       "      <td>3.70</td>\n",
       "      <td>-15.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uzwiki</th>\n",
       "      <td>15340</td>\n",
       "      <td>31189</td>\n",
       "      <td>2036</td>\n",
       "      <td>861</td>\n",
       "      <td>13.27</td>\n",
       "      <td>2.76</td>\n",
       "      <td>-10.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>roa_rupwiki</th>\n",
       "      <td>17</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>11.76</td>\n",
       "      <td>3.03</td>\n",
       "      <td>-8.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mnwiki</th>\n",
       "      <td>19</td>\n",
       "      <td>759</td>\n",
       "      <td>4</td>\n",
       "      <td>101</td>\n",
       "      <td>21.05</td>\n",
       "      <td>13.31</td>\n",
       "      <td>-7.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hrwiki</th>\n",
       "      <td>254</td>\n",
       "      <td>2147</td>\n",
       "      <td>59</td>\n",
       "      <td>372</td>\n",
       "      <td>23.23</td>\n",
       "      <td>17.33</td>\n",
       "      <td>-5.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tnwiki</th>\n",
       "      <td>22</td>\n",
       "      <td>579</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4.55</td>\n",
       "      <td>0.35</td>\n",
       "      <td>-4.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>brwiki</th>\n",
       "      <td>27</td>\n",
       "      <td>673</td>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>7.41</td>\n",
       "      <td>3.71</td>\n",
       "      <td>-3.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>astwiki</th>\n",
       "      <td>19</td>\n",
       "      <td>2883</td>\n",
       "      <td>1</td>\n",
       "      <td>47</td>\n",
       "      <td>5.26</td>\n",
       "      <td>1.63</td>\n",
       "      <td>-3.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fiwiki</th>\n",
       "      <td>16</td>\n",
       "      <td>8464</td>\n",
       "      <td>2</td>\n",
       "      <td>757</td>\n",
       "      <td>12.50</td>\n",
       "      <td>8.94</td>\n",
       "      <td>-3.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dewiki</th>\n",
       "      <td>479</td>\n",
       "      <td>47302</td>\n",
       "      <td>77</td>\n",
       "      <td>6178</td>\n",
       "      <td>16.08</td>\n",
       "      <td>13.06</td>\n",
       "      <td>-3.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gawiki</th>\n",
       "      <td>153</td>\n",
       "      <td>1268</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>3.92</td>\n",
       "      <td>1.74</td>\n",
       "      <td>-2.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>htwiki</th>\n",
       "      <td>63</td>\n",
       "      <td>179</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3.17</td>\n",
       "      <td>1.12</td>\n",
       "      <td>-2.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             created_cx  created_non_cx  deleted_cx  deleted_non_cx  \\\n",
       "wiki_db                                                               \n",
       "yiwiki               41              21          32               8   \n",
       "kuwiki               30             846          10              45   \n",
       "swwiki               21            2591           6              47   \n",
       "lvwiki               38            3397           9             252   \n",
       "ocwiki               16             541           3              20   \n",
       "uzwiki            15340           31189        2036             861   \n",
       "roa_rupwiki          17              33           2               1   \n",
       "mnwiki               19             759           4             101   \n",
       "hrwiki              254            2147          59             372   \n",
       "tnwiki               22             579           1               2   \n",
       "brwiki               27             673           2              25   \n",
       "astwiki              19            2883           1              47   \n",
       "fiwiki               16            8464           2             757   \n",
       "dewiki              479           47302          77            6178   \n",
       "gawiki              153            1268           6              22   \n",
       "htwiki               63             179           2               2   \n",
       "\n",
       "             deleted_cx_pct  deleted_non_cx_pct  deletion_pct_diff  \n",
       "wiki_db                                                             \n",
       "yiwiki                78.05               38.10             -39.95  \n",
       "kuwiki                33.33                5.32             -28.01  \n",
       "swwiki                28.57                1.81             -26.76  \n",
       "lvwiki                23.68                7.42             -16.26  \n",
       "ocwiki                18.75                3.70             -15.05  \n",
       "uzwiki                13.27                2.76             -10.51  \n",
       "roa_rupwiki           11.76                3.03              -8.73  \n",
       "mnwiki                21.05               13.31              -7.74  \n",
       "hrwiki                23.23               17.33              -5.90  \n",
       "tnwiki                 4.55                0.35              -4.20  \n",
       "brwiki                 7.41                3.71              -3.70  \n",
       "astwiki                5.26                1.63              -3.63  \n",
       "fiwiki                12.50                8.94              -3.56  \n",
       "dewiki                16.08               13.06              -3.02  \n",
       "gawiki                 3.92                1.74              -2.18  \n",
       "htwiki                 3.17                1.12              -2.05  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# wikis with high deletion ratio\n",
    "currq_high_deletion_ratio = deletion_stats_currq.query(\"\"\"deletion_pct_diff <= -2\"\"\").sort_values('deletion_pct_diff')\n",
    "currq_high_deletion_ratio"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1ca0ef0-0058-43d4-af22-132eb75792c2",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Previous-Quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "22c0cf81-656c-4311-bc58-120555588139",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "During FY23-Q3, overall percentage of articles that were deleted,\n",
      "\t- created using the Content Translation tool: 2.32%\n",
      "\t- created without using the Content Translation Tool: 11.65%\n"
     ]
    }
   ],
   "source": [
    "# overal deletion percent\n",
    "dtf.overall_deletion_pct(deletion_stats_prevq_all, period='FY23-Q3', pr=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e2c285cb-81e7-4f5e-8281-8d00f2a0679e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# deletion ratio by wiki\n",
    "deletion_stats_prevq = dtf.generate_ratios_by_wiki(deletion_stats_prevq_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8710184d-07cd-4e75-ac83-4cf34c53738b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "During FY23-Q3, across all wikis where more than 15 articles have been created with the Content Translation tool,\n",
      "there were 10 wikis where the deletion rate of articles created using CX was atleast 2% higher than articles created without using CX.\n"
     ]
    }
   ],
   "source": [
    "print(f'During FY23-Q3, across all wikis where more than 15 articles have been created with the Content Translation tool,\\n\\\n",
    "there were {deletion_stats_prevq.query(\"\"\"deletion_pct_diff < -2\"\"\").shape[0]} wikis where the deletion rate of articles created \\\n",
    "using CX was atleast 2% higher than articles created without using CX.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c46498bf-7564-4bcb-aee8-d68016a2ed55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>created_cx</th>\n",
       "      <th>created_non_cx</th>\n",
       "      <th>deleted_cx</th>\n",
       "      <th>deleted_non_cx</th>\n",
       "      <th>deleted_cx_pct</th>\n",
       "      <th>deleted_non_cx_pct</th>\n",
       "      <th>deletion_pct_diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wiki_db</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ltwiki</th>\n",
       "      <td>50</td>\n",
       "      <td>4039</td>\n",
       "      <td>23</td>\n",
       "      <td>504</td>\n",
       "      <td>46.00</td>\n",
       "      <td>12.48</td>\n",
       "      <td>-33.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>banwiki</th>\n",
       "      <td>18</td>\n",
       "      <td>1416</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>27.78</td>\n",
       "      <td>1.06</td>\n",
       "      <td>-26.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cebwiki</th>\n",
       "      <td>34</td>\n",
       "      <td>741</td>\n",
       "      <td>8</td>\n",
       "      <td>37</td>\n",
       "      <td>23.53</td>\n",
       "      <td>4.99</td>\n",
       "      <td>-18.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mnwiki</th>\n",
       "      <td>22</td>\n",
       "      <td>903</td>\n",
       "      <td>7</td>\n",
       "      <td>211</td>\n",
       "      <td>31.82</td>\n",
       "      <td>23.37</td>\n",
       "      <td>-8.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>iswiki</th>\n",
       "      <td>45</td>\n",
       "      <td>966</td>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "      <td>13.33</td>\n",
       "      <td>6.21</td>\n",
       "      <td>-7.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gnwiki</th>\n",
       "      <td>24</td>\n",
       "      <td>115</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>8.33</td>\n",
       "      <td>1.74</td>\n",
       "      <td>-6.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tgwiki</th>\n",
       "      <td>142</td>\n",
       "      <td>1979</td>\n",
       "      <td>15</td>\n",
       "      <td>115</td>\n",
       "      <td>10.56</td>\n",
       "      <td>5.81</td>\n",
       "      <td>-4.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>svwiki</th>\n",
       "      <td>108</td>\n",
       "      <td>14912</td>\n",
       "      <td>14</td>\n",
       "      <td>1321</td>\n",
       "      <td>12.96</td>\n",
       "      <td>8.86</td>\n",
       "      <td>-4.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kuwiki</th>\n",
       "      <td>23</td>\n",
       "      <td>629</td>\n",
       "      <td>3</td>\n",
       "      <td>60</td>\n",
       "      <td>13.04</td>\n",
       "      <td>9.54</td>\n",
       "      <td>-3.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ttwiki</th>\n",
       "      <td>55</td>\n",
       "      <td>388</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>5.45</td>\n",
       "      <td>2.06</td>\n",
       "      <td>-3.39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         created_cx  created_non_cx  deleted_cx  deleted_non_cx  \\\n",
       "wiki_db                                                           \n",
       "ltwiki           50            4039          23             504   \n",
       "banwiki          18            1416           5              15   \n",
       "cebwiki          34             741           8              37   \n",
       "mnwiki           22             903           7             211   \n",
       "iswiki           45             966           6              60   \n",
       "gnwiki           24             115           2               2   \n",
       "tgwiki          142            1979          15             115   \n",
       "svwiki          108           14912          14            1321   \n",
       "kuwiki           23             629           3              60   \n",
       "ttwiki           55             388           3               8   \n",
       "\n",
       "         deleted_cx_pct  deleted_non_cx_pct  deletion_pct_diff  \n",
       "wiki_db                                                         \n",
       "ltwiki            46.00               12.48             -33.52  \n",
       "banwiki           27.78                1.06             -26.72  \n",
       "cebwiki           23.53                4.99             -18.54  \n",
       "mnwiki            31.82               23.37              -8.45  \n",
       "iswiki            13.33                6.21              -7.12  \n",
       "gnwiki             8.33                1.74              -6.59  \n",
       "tgwiki            10.56                5.81              -4.75  \n",
       "svwiki            12.96                8.86              -4.10  \n",
       "kuwiki            13.04                9.54              -3.50  \n",
       "ttwiki             5.45                2.06              -3.39  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# wikis with high deletion ratio\n",
    "\n",
    "prevq_high_deletion_ratio = deletion_stats_prevq.query(\"\"\"deletion_pct_diff < -2\"\"\").sort_values('deletion_pct_diff')\n",
    "prevq_high_deletion_ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a4cbbdc1-b5f3-4f2d-926c-43ef72a665c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['kuwiki', 'mnwiki'], dtype=object)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# wikis that had high deletion rates for articles that have been created with CX compared articles that have not been created using CX\n",
    "wikis_high_deletion_ratio = np.intersect1d(currq_high_deletion_ratio.index.values, prevq_high_deletion_ratio.index.values)\n",
    "wikis_high_deletion_ratio"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2de896a-deb0-4d15-a105-46f47a32673c",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Formatting\n",
    "for publication on Meta-Wiki at [Content translation/Deletion statistics comparison](https://www.mediawiki.org/wiki/Content_translation/Deletion_statistics_comparison)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "3d418254-a68c-4a70-9201-9a447a824498",
   "metadata": {},
   "outputs": [],
   "source": [
    "currq_wtable = currq_high_deletion_ratio.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9bee1792-17f2-40b8-b797-c6b6d87bbe98",
   "metadata": {},
   "outputs": [],
   "source": [
    "# format the percentage columns\n",
    "\n",
    "percentage_columns = ['deleted_cx_pct', 'deleted_non_cx_pct', 'deletion_pct_diff']\n",
    "currq_wtable[percentage_columns] = currq_wtable[percentage_columns]/100\n",
    "\n",
    "currq_wtable = (\n",
    "    currq_wtable\n",
    "    .assign(\n",
    "        deleted_cx_pct = ftf.format_percent('deleted_cx_pct', currq_wtable),\n",
    "        deleted_non_cx_pct = ftf.format_percent('deleted_non_cx_pct', currq_wtable),\n",
    "        deletion_pct_diff = ftf.format_percent('deletion_pct_diff', currq_wtable)\n",
    "    )\n",
    "    .reset_index()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8d5e2434-3a21-4e9d-8ba2-e164e6583e0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename columns\n",
    "columns_rename_map = {\n",
    "    'wiki_db': 'Wikipedia',\n",
    "    'created_cx': 'Created CX Articles', \n",
    "    'created_non_cx': 'Created non-CX Articles', \n",
    "    'deleted_cx': 'Deleted CX Articles', \n",
    "    'deleted_non_cx': 'Deleted non-CX Articles',\n",
    "    'deleted_cx_pct': 'CX Articles Deletion Ratio', \n",
    "    'deleted_non_cx_pct': 'Non-CX Articles Deletion Ratio', \n",
    "    'deletion_pct_diff': 'Deletion Ratio Difference'\n",
    "}\n",
    "\n",
    "currq_wtable.rename(columns_rename_map, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "baea4141-cf5a-4399-9aa9-b55e9d4958c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Wikipedia</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Created Articles</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Deleted Articles</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Deletion Ratios</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Wikipedia</th>\n",
       "      <th>Created CX Articles</th>\n",
       "      <th>Created non-CX Articles</th>\n",
       "      <th>Deleted CX Articles</th>\n",
       "      <th>Deleted non-CX Articles</th>\n",
       "      <th>CX Articles Deletion Ratio</th>\n",
       "      <th>Non-CX Articles Deletion Ratio</th>\n",
       "      <th>Deletion Ratio Difference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>yiwiki</td>\n",
       "      <td>41</td>\n",
       "      <td>21</td>\n",
       "      <td>32</td>\n",
       "      <td>8</td>\n",
       "      <td>78.05%</td>\n",
       "      <td>38.10%</td>\n",
       "      <td>-39.95%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>kuwiki</td>\n",
       "      <td>30</td>\n",
       "      <td>846</td>\n",
       "      <td>10</td>\n",
       "      <td>45</td>\n",
       "      <td>33.33%</td>\n",
       "      <td>5.32%</td>\n",
       "      <td>-28.01%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>swwiki</td>\n",
       "      <td>21</td>\n",
       "      <td>2591</td>\n",
       "      <td>6</td>\n",
       "      <td>47</td>\n",
       "      <td>28.57%</td>\n",
       "      <td>1.81%</td>\n",
       "      <td>-26.76%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lvwiki</td>\n",
       "      <td>38</td>\n",
       "      <td>3397</td>\n",
       "      <td>9</td>\n",
       "      <td>252</td>\n",
       "      <td>23.68%</td>\n",
       "      <td>7.42%</td>\n",
       "      <td>-16.26%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ocwiki</td>\n",
       "      <td>16</td>\n",
       "      <td>541</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>18.75%</td>\n",
       "      <td>3.70%</td>\n",
       "      <td>-15.05%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Wikipedia    Created Articles                            Deleted Articles  \\\n",
       "  Wikipedia Created CX Articles Created non-CX Articles Deleted CX Articles   \n",
       "0    yiwiki                  41                      21                  32   \n",
       "1    kuwiki                  30                     846                  10   \n",
       "2    swwiki                  21                    2591                   6   \n",
       "3    lvwiki                  38                    3397                   9   \n",
       "4    ocwiki                  16                     541                   3   \n",
       "\n",
       "                                     Deletion Ratios  \\\n",
       "  Deleted non-CX Articles CX Articles Deletion Ratio   \n",
       "0                       8                     78.05%   \n",
       "1                      45                     33.33%   \n",
       "2                      47                     28.57%   \n",
       "3                     252                     23.68%   \n",
       "4                      20                     18.75%   \n",
       "\n",
       "                                                            \n",
       "  Non-CX Articles Deletion Ratio Deletion Ratio Difference  \n",
       "0                         38.10%                   -39.95%  \n",
       "1                          5.32%                   -28.01%  \n",
       "2                          1.81%                   -26.76%  \n",
       "3                          7.42%                   -16.26%  \n",
       "4                          3.70%                   -15.05%  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a multi-level column\n",
    "column_arrays = [\n",
    "    np.array(['Wikipedia'] + ['Created Articles'] * 2 + ['Deleted Articles'] * 2 + ['Deletion Ratios'] * 3),\n",
    "    currq_wtable.columns.to_numpy()\n",
    "]\n",
    "\n",
    "currq_wtable.columns = pd.MultiIndex.from_arrays(column_arrays)\n",
    "\n",
    "currq_wtable.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "b0c63d0a-790e-4e7b-bde5-735685f07c7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add footnote (as superscript) for wikis that had high deletion ratio for article created using CX during the last quarter as well\n",
    "currq_wtable[('Wikipedia', 'Wikipedia')] = currq_wtable[('Wikipedia', 'Wikipedia')].apply(lambda x:ftf.add_footnote(x, wikis_high_deletion_ratio))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9928fe0c-4a16-4309-ae99-e87510c65e7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "table_headers = [\n",
    "    'Wikipedias with higher deletion ratios for articles created with Content Translation',\n",
    "    'Reviewed Time Period: April to June 2024 (Q4 2023-24)'\n",
    "]\n",
    "\n",
    "table_footers = [\n",
    "    '<sup>1</sup> Excludes Wikipedias with 15 or fewer articles created with Content Translation during the reviewed time period.',\n",
    "    '<sup>2</sup> Also identified in the prior quarter as a wiki with a higher deletion ratio for articles created with Content Translation.'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "97f0078e-4d8c-4cce-a09e-381b4e88d3cc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{| class='wikitable'\n",
      "! colspan='8' | Wikipedias with higher deletion ratios for articles created with Content Translation\n",
      "! colspan='8' | Reviewed Time Period: April to June 2024 (Q4 2023-24)\n",
      "|-\n",
      "colspan='1' | Wikipedia !! colspan='2' | Created Articles !! colspan='2' | Deleted Articles !! colspan='3' | Deletion Ratios\n",
      "colspan='1' | Wikipedia !! colspan='1' | Created CX Articles !! colspan='1' | Created non-CX Articles !! colspan='1' | Deleted CX Articles !! colspan='1' | Deleted non-CX Articles !! colspan='1' | CX Articles Deletion Ratio !! colspan='1' | Non-CX Articles Deletion Ratio !! colspan='1' | Deletion Ratio Difference\n",
      "|-\n",
      "| yiwiki || 41 || 21 || 32 || 8 || 78.05% || 38.10% || -39.95%\n",
      "|-\n",
      "| kuwiki<sup>2</sup> || 30 || 846 || 10 || 45 || 33.33% || 5.32% || -28.01%\n",
      "|-\n",
      "| swwiki || 21 || 2591 || 6 || 47 || 28.57% || 1.81% || -26.76%\n",
      "|-\n",
      "| lvwiki || 38 || 3397 || 9 || 252 || 23.68% || 7.42% || -16.26%\n",
      "|-\n",
      "| ocwiki || 16 || 541 || 3 || 20 || 18.75% || 3.70% || -15.05%\n",
      "|-\n",
      "| uzwiki || 15340 || 31189 || 2036 || 861 || 13.27% || 2.76% || -10.51%\n",
      "|-\n",
      "| roa_rupwiki || 17 || 33 || 2 || 1 || 11.76% || 3.03% || -8.73%\n",
      "|-\n",
      "| mnwiki<sup>2</sup> || 19 || 759 || 4 || 101 || 21.05% || 13.31% || -7.74%\n",
      "|-\n",
      "| hrwiki || 254 || 2147 || 59 || 372 || 23.23% || 17.33% || -5.90%\n",
      "|-\n",
      "| tnwiki || 22 || 579 || 1 || 2 || 4.55% || 0.35% || -4.20%\n",
      "|-\n",
      "| brwiki || 27 || 673 || 2 || 25 || 7.41% || 3.71% || -3.70%\n",
      "|-\n",
      "| astwiki || 19 || 2883 || 1 || 47 || 5.26% || 1.63% || -3.63%\n",
      "|-\n",
      "| fiwiki || 16 || 8464 || 2 || 757 || 12.50% || 8.94% || -3.56%\n",
      "|-\n",
      "| dewiki || 479 || 47302 || 77 || 6178 || 16.08% || 13.06% || -3.02%\n",
      "|-\n",
      "| gawiki || 153 || 1268 || 6 || 22 || 3.92% || 1.74% || -2.18%\n",
      "|-\n",
      "| htwiki || 63 || 179 || 2 || 2 || 3.17% || 1.12% || -2.05%\n",
      "|-\n",
      "| style=\"text-align:left;\" colspan='8' | <sup>1</sup> Excludes Wikipedias with 15 or fewer articles created with Content Translation during the reviewed time period.\n",
      "| style=\"text-align:left;\" colspan='8' | <sup>2</sup> Also identified in the prior quarter as a wiki with a higher deletion ratio for articles created with Content Translation.\n",
      "|-\n",
      "|}\n"
     ]
    }
   ],
   "source": [
    "# to be published at https://www.mediawiki.org/wiki/Content_translation/Deletion_statistics_comparison\n",
    "print(ftf.dataframe_to_mediawiki(currq_wtable, table_headers, table_footers))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4d6d316e-e5b3-40ff-ab5c-14dc6d484f9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark_session.stop()"
   ]
  }
 ],
 "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.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}