01-deletion_stats_q3_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 26 June 2024**\n",
    "\n",
    "[TASK: T368496](https://phabricator.wikimedia.org/T368496)"
   ]
  },
  {
   "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 Q2](#Current-Quarter)\n",
    "    * [Previous Quarter - FY23 Q1](#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",
    "## Summary\n",
    "* The deletion rate for CX created articles (2.88%) is significantly less than that of non-CX created articles (13.21%).\n",
    "    * This was the lowest deletion rate for CX created articles after December 2021.\n",
    "* There were [4 wikis](https://www.mediawiki.org/wiki/Content_translation/Deletion_statistics_comparison#October_through_December_2023_(Q2_2023)) had at least 2% higher deletion rate of CX created articles than the ones that were not. \n",
    "    * Two of these wikis had higher deletion rates for CX created articles during the last quarter as well.\n",
    "    * Kurdish (kuwiki) has been on the list for the last four quarters, and Occitan (ocwiki) for the last three quarters."
   ]
  },
  {
   "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": 4,
   "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://stat1008.eqiad.wmnet:4044\">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 0x7fd134aabd30>"
      ]
     },
     "execution_count": 4,
     "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": 5,
   "id": "718001d4-987e-4cc7-8e30-c704eedc8cf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "currq_dates = dtf.generate_quarters(2023)['Q3']\n",
    "prevq_dates = dtf.generate_quarters(2023)['Q2']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "819bdab7-121c-48bb-8730-e288e6b1f2a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.35 s, sys: 262 ms, total: 1.61 s\n",
      "Wall time: 4min 33s\n"
     ]
    }
   ],
   "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": 7,
   "id": "e3fc0f9a-a49c-427d-a77e-6207a6533d01",
   "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_currq_all, period='FY23-Q3', pr=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": 9,
   "id": "60d661f6-5b18-4d88-80ba-6a5987dbbd2b",
   "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_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>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": 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": 11,
   "id": "22c0cf81-656c-4311-bc58-120555588139",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "During FY23-Q2, overall percentage of articles that were deleted,\n",
      "\t- created using the Content Translation tool: 2.88%\n",
      "\t- created without using the Content Translation Tool: 13.2%\n"
     ]
    }
   ],
   "source": [
    "# overal deletion percent\n",
    "dtf.overall_deletion_pct(deletion_stats_prevq_all, period='FY23-Q2', pr=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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": 13,
   "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 4 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": 14,
   "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>kuwiki</th>\n",
       "      <td>22</td>\n",
       "      <td>15023</td>\n",
       "      <td>3</td>\n",
       "      <td>24</td>\n",
       "      <td>13.64</td>\n",
       "      <td>0.16</td>\n",
       "      <td>-13.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>svwiki</th>\n",
       "      <td>104</td>\n",
       "      <td>12911</td>\n",
       "      <td>19</td>\n",
       "      <td>1618</td>\n",
       "      <td>18.27</td>\n",
       "      <td>12.53</td>\n",
       "      <td>-5.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ocwiki</th>\n",
       "      <td>45</td>\n",
       "      <td>1053</td>\n",
       "      <td>3</td>\n",
       "      <td>22</td>\n",
       "      <td>6.67</td>\n",
       "      <td>2.09</td>\n",
       "      <td>-4.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dewiki</th>\n",
       "      <td>536</td>\n",
       "      <td>53768</td>\n",
       "      <td>89</td>\n",
       "      <td>6865</td>\n",
       "      <td>16.60</td>\n",
       "      <td>12.77</td>\n",
       "      <td>-3.83</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",
       "kuwiki           22           15023           3              24   \n",
       "svwiki          104           12911          19            1618   \n",
       "ocwiki           45            1053           3              22   \n",
       "dewiki          536           53768          89            6865   \n",
       "\n",
       "         deleted_cx_pct  deleted_non_cx_pct  deletion_pct_diff  \n",
       "wiki_db                                                         \n",
       "kuwiki            13.64                0.16             -13.48  \n",
       "svwiki            18.27               12.53              -5.74  \n",
       "ocwiki             6.67                2.09              -4.58  \n",
       "dewiki            16.60               12.77              -3.83  "
      ]
     },
     "execution_count": 14,
     "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": 15,
   "id": "a4cbbdc1-b5f3-4f2d-926c-43ef72a665c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['kuwiki', 'svwiki'], dtype=object)"
      ]
     },
     "execution_count": 15,
     "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": 16,
   "id": "3d418254-a68c-4a70-9201-9a447a824498",
   "metadata": {},
   "outputs": [],
   "source": [
    "currq_wtable = currq_high_deletion_ratio.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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": 18,
   "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": 19,
   "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>ltwiki</td>\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>1</th>\n",
       "      <td>banwiki</td>\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>2</th>\n",
       "      <td>cebwiki</td>\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>3</th>\n",
       "      <td>mnwiki</td>\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>4</th>\n",
       "      <td>iswiki</td>\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",
       "  </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    ltwiki                  50                    4039                  23   \n",
       "1   banwiki                  18                    1416                   5   \n",
       "2   cebwiki                  34                     741                   8   \n",
       "3    mnwiki                  22                     903                   7   \n",
       "4    iswiki                  45                     966                   6   \n",
       "\n",
       "                                     Deletion Ratios  \\\n",
       "  Deleted non-CX Articles CX Articles Deletion Ratio   \n",
       "0                     504                     46.00%   \n",
       "1                      15                     27.78%   \n",
       "2                      37                     23.53%   \n",
       "3                     211                     31.82%   \n",
       "4                      60                     13.33%   \n",
       "\n",
       "                                                            \n",
       "  Non-CX Articles Deletion Ratio Deletion Ratio Difference  \n",
       "0                         12.48%                   -33.52%  \n",
       "1                          1.06%                   -26.72%  \n",
       "2                          4.99%                   -18.54%  \n",
       "3                         23.37%                    -8.45%  \n",
       "4                          6.21%                    -7.12%  "
      ]
     },
     "execution_count": 19,
     "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": 23,
   "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: January through March 2024 (FY 23 Q3)'\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": 24,
   "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: January through March 2024 (FY 23 Q3)\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",
      "| ltwiki || 50 || 4039 || 23 || 504 || 46.00% || 12.48% || -33.52%\n",
      "|-\n",
      "| banwiki || 18 || 1416 || 5 || 15 || 27.78% || 1.06% || -26.72%\n",
      "|-\n",
      "| cebwiki || 34 || 741 || 8 || 37 || 23.53% || 4.99% || -18.54%\n",
      "|-\n",
      "| mnwiki || 22 || 903 || 7 || 211 || 31.82% || 23.37% || -8.45%\n",
      "|-\n",
      "| iswiki || 45 || 966 || 6 || 60 || 13.33% || 6.21% || -7.12%\n",
      "|-\n",
      "| gnwiki || 24 || 115 || 2 || 2 || 8.33% || 1.74% || -6.59%\n",
      "|-\n",
      "| tgwiki || 142 || 1979 || 15 || 115 || 10.56% || 5.81% || -4.75%\n",
      "|-\n",
      "| svwiki<sup>2</sup> || 108 || 14912 || 14 || 1321 || 12.96% || 8.86% || -4.10%\n",
      "|-\n",
      "| kuwiki<sup>2</sup> || 23 || 629 || 3 || 60 || 13.04% || 9.54% || -3.50%\n",
      "|-\n",
      "| ttwiki || 55 || 388 || 3 || 8 || 5.45% || 2.06% || -3.39%\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": 25,
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}