QA2_event.mediawiki_edit_attempt.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d558e8eb-8819-4e83-ac10-034107c74ca2",
   "metadata": {},
   "outputs": [],
   "source": [
    "from wmfdata import hive, spark"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1f5c531-07ec-426f-9676-54c1c75cfd2c",
   "metadata": {},
   "source": [
    "### Issues\n",
    "- The number of events on editattemptstep is higher than mediawiki_edit_attempt. This is because metrics platform is not capturing events on wikitext editor interface. This was discovered in Data QA testing Round 3 (see notebook in this folder)\n",
    "\n",
    "##### Notes\n",
    "- editattemptstep: event.session_token = mediawiki_edit_attempt: performer.session_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9a756ce-d8d4-4991-8d73-0fffb88bccb5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "01a23f24-68d1-44a5-8271-139bb429c220",
   "metadata": {},
   "source": [
    "## Compare metrics between editattemptstep and event.mediawiki_edit_attempt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77aee198-699e-4ffe-9214-d6be502a7c79",
   "metadata": {},
   "source": [
    "First, we check a few events at the hour level. Here I am looking at visualeditor events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e054fecb-e99a-43c4-9922-6c67e545be51",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "name_query1=spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, count(1) AS ve_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE name like 'eas.ve%'\n",
    "  AND year = 2022\n",
    "  AND month= 11\n",
    "  AND day = 3\n",
    "  AND hour = 4\n",
    "  AND normalized_host.project = 'test'\n",
    "  GROUP BY name \n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "007e3a56-32d1-4562-993a-a3c9fe25f7c3",
   "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>name</th>\n",
       "      <th>ve_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.ve.ready</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>eas.ve.loaded</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.ve.init</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.ve.save_success</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.ve.save_intent</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.ve.save_attempt</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.ve.first_change</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.ve.abort</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  ve_events\n",
       "5         eas.ve.ready         17\n",
       "7        eas.ve.loaded         17\n",
       "3          eas.ve.init         17\n",
       "2  eas.ve.save_success         15\n",
       "4   eas.ve.save_intent         15\n",
       "6  eas.ve.save_attempt         15\n",
       "0  eas.ve.first_change         15\n",
       "1         eas.ve.abort          2"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name_query1.sort_values(by=['ve_events','name'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "de17daf2-ee81-4a36-a16b-f4538b86d195",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "action_query1=spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, count(1) AS ve_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.editor_interface= 'visualeditor'\n",
    "  AND year = 2022\n",
    "  AND month= 11\n",
    "  AND day = 3\n",
    "  AND hour = 4\n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action \n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67bad63e-f666-4575-b097-15e83810cdf3",
   "metadata": {},
   "source": [
    "This matches exactly with the events from mediawiki_edit_attempt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d5d9353b-7e0c-478e-aebb-ad8931710a2c",
   "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>action</th>\n",
       "      <th>ve_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ready</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>loaded</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>init</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>saveSuccess</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>saveIntent</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>saveAttempt</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>firstChange</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>abort</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        action  ve_events\n",
       "2        ready         17\n",
       "1       loaded         17\n",
       "3         init         17\n",
       "5  saveSuccess         15\n",
       "6   saveIntent         15\n",
       "0  saveAttempt         15\n",
       "7  firstChange         15\n",
       "4        abort          2"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "action_query1.sort_values(by=['ve_events','action'],ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06081af7-7398-4caf-b9cd-3759420f6e93",
   "metadata": {},
   "source": [
    "Next I compared events from 2022-10-09 hour \"8\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "acd90077-73a8-4c16-9bc8-c66a89eb2dc8",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "name_query2=spark.run('''\n",
    "SELECT \n",
    "  DISTINCT name, count(1) AS wikitext_events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE name like 'eas.mf%'\n",
    "  AND year = 2022\n",
    "  AND month= 10\n",
    "  AND day = 9\n",
    "  AND hour = 8\n",
    "  AND normalized_host.project = 'test'\n",
    "  GROUP BY name \n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "ddd41f81-3865-45f5-9679-97a9b2685f2d",
   "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>name</th>\n",
       "      <th>wikitext_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eas.mf.first_change</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name  wikitext_events\n",
       "0         eas.mf.ready                5\n",
       "6        eas.mf.loaded                5\n",
       "1          eas.mf.init                5\n",
       "3  eas.mf.save_success                2\n",
       "2   eas.mf.save_intent                2\n",
       "5  eas.mf.save_attempt                2\n",
       "4  eas.mf.first_change                2"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name_query2.sort_values(by=['wikitext_events','name'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "a60cff9a-215b-4778-80c4-95c14e9697f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n"
     ]
    }
   ],
   "source": [
    "action_query2=spark.run('''\n",
    "SELECT \n",
    "  DISTINCT event.action AS action, count(1) AS wikitext_events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE event.editor_interface = 'wikitext'\n",
    "  AND year = 2022\n",
    "  AND month= 10\n",
    "  AND day = 9\n",
    "  AND hour = 8 \n",
    "  AND wiki = 'testwiki' \n",
    "  GROUP BY action \n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88206b8e-275e-4e49-ab37-855e8f290fa6",
   "metadata": {},
   "source": [
    "Note that the number of events captured in the same \"hour\" is different in both tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "8571c201-a048-45a4-a182-1dd0f003929f",
   "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>action</th>\n",
       "      <th>wikitext_events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ready</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>loaded</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>init</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   action  wikitext_events\n",
       "2   ready                1\n",
       "1  loaded                1\n",
       "0    init                1"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "action_query2.sort_values(by=['wikitext_events','action'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "dd438299-7fb0-4c5e-99d9-a559aa56ea42",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "eas_query1=spark.run('''\n",
    "SELECT \n",
    "  *\n",
    "  FROM event.editattemptstep\n",
    "  WHERE year = 2022\n",
    "  AND month= 10\n",
    "  AND day = 9\n",
    "  AND hour = 8 \n",
    "  AND wiki = 'testwiki'  \n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "7abaec7c-1476-4b4c-9474-1cb2ebbf8391",
   "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>dt</th>\n",
       "      <th>event</th>\n",
       "      <th>ip</th>\n",
       "      <th>recvfrom</th>\n",
       "      <th>revision</th>\n",
       "      <th>schema</th>\n",
       "      <th>seqid</th>\n",
       "      <th>useragent</th>\n",
       "      <th>uuid</th>\n",
       "      <th>webhost</th>\n",
       "      <th>...</th>\n",
       "      <th>_schema</th>\n",
       "      <th>client_dt</th>\n",
       "      <th>http</th>\n",
       "      <th>meta</th>\n",
       "      <th>user_agent_map</th>\n",
       "      <th>normalized_host</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-10-09T08:45:38.473Z</td>\n",
       "      <td>(init, ecc907e1cd3cc9b88ac4, wikitext, click, ...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>EditAttemptStep</td>\n",
       "      <td>None</td>\n",
       "      <td>(Chrome, 106, None, Lenovo TB-7104F, False, Fa...</td>\n",
       "      <td>None</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-09T08:45:44.524Z</td>\n",
       "      <td>(None, None, None, None, {'user-agent': 'Mozil...</td>\n",
       "      <td>(None, 195de194-b0e5-4239-81e2-d9e99ac6f282, 2...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-09T08:45:38.483Z</td>\n",
       "      <td>(ready, ecc907e1cd3cc9b88ac4, wikitext, None, ...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>EditAttemptStep</td>\n",
       "      <td>None</td>\n",
       "      <td>(Chrome, 106, None, Lenovo TB-7104F, False, Fa...</td>\n",
       "      <td>None</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-09T08:45:45.137Z</td>\n",
       "      <td>(None, None, None, None, {'user-agent': 'Mozil...</td>\n",
       "      <td>(None, 93c276f5-6e37-4c5d-a693-7d1820f6c8f7, e...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10-09T08:45:38.823Z</td>\n",
       "      <td>(loaded, ecc907e1cd3cc9b88ac4, wikitext, None,...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>EditAttemptStep</td>\n",
       "      <td>None</td>\n",
       "      <td>(Chrome, 106, None, Lenovo TB-7104F, False, Fa...</td>\n",
       "      <td>None</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-09T08:45:45.144Z</td>\n",
       "      <td>(None, None, None, None, {'user-agent': 'Mozil...</td>\n",
       "      <td>(None, deec3806-b63d-42c9-9bb8-ef56c4935a5c, c...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         dt  \\\n",
       "0  2022-10-09T08:45:38.473Z   \n",
       "1  2022-10-09T08:45:38.483Z   \n",
       "2  2022-10-09T08:45:38.823Z   \n",
       "\n",
       "                                               event    ip recvfrom revision  \\\n",
       "0  (init, ecc907e1cd3cc9b88ac4, wikitext, click, ...  None     None     None   \n",
       "1  (ready, ecc907e1cd3cc9b88ac4, wikitext, None, ...  None     None     None   \n",
       "2  (loaded, ecc907e1cd3cc9b88ac4, wikitext, None,...  None     None     None   \n",
       "\n",
       "            schema seqid                                          useragent  \\\n",
       "0  EditAttemptStep  None  (Chrome, 106, None, Lenovo TB-7104F, False, Fa...   \n",
       "1  EditAttemptStep  None  (Chrome, 106, None, Lenovo TB-7104F, False, Fa...   \n",
       "2  EditAttemptStep  None  (Chrome, 106, None, Lenovo TB-7104F, False, Fa...   \n",
       "\n",
       "   uuid             webhost  ... _schema                 client_dt  \\\n",
       "0  None  test.wikipedia.org  ...    None  2022-10-09T08:45:44.524Z   \n",
       "1  None  test.wikipedia.org  ...    None  2022-10-09T08:45:45.137Z   \n",
       "2  None  test.wikipedia.org  ...    None  2022-10-09T08:45:45.144Z   \n",
       "\n",
       "                                                http  \\\n",
       "0  (None, None, None, None, {'user-agent': 'Mozil...   \n",
       "1  (None, None, None, None, {'user-agent': 'Mozil...   \n",
       "2  (None, None, None, None, {'user-agent': 'Mozil...   \n",
       "\n",
       "                                                meta  \\\n",
       "0  (None, 195de194-b0e5-4239-81e2-d9e99ac6f282, 2...   \n",
       "1  (None, 93c276f5-6e37-4c5d-a693-7d1820f6c8f7, e...   \n",
       "2  (None, deec3806-b63d-42c9-9bb8-ef56c4935a5c, c...   \n",
       "\n",
       "                                      user_agent_map  \\\n",
       "0  {'os_family': 'Android', 'browser_major': '106...   \n",
       "1  {'os_family': 'Android', 'browser_major': '106...   \n",
       "2  {'os_family': 'Android', 'browser_major': '106...   \n",
       "\n",
       "                         normalized_host  year month day hour  \n",
       "0  (wikipedia, test, [], org, wikipedia)  2022    10   9    8  \n",
       "1  (wikipedia, test, [], org, wikipedia)  2022    10   9    8  \n",
       "2  (wikipedia, test, [], org, wikipedia)  2022    10   9    8  \n",
       "\n",
       "[3 rows x 24 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eas_query1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "831ee74a-c684-4091-bf9c-45f1839acf45",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mea_query1=spark.run('''\n",
    "SELECT \n",
    "  *\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE year = 2022\n",
    "  AND month= 10\n",
    "  AND day = 9\n",
    "  AND hour = 8\n",
    "  AND normalized_host.project = 'test'\n",
    "  AND dt like '2022-10-09T08:45%'\n",
    "   \n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "5b7d53ff-a501-433f-acf9-ab679b652a83",
   "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>_schema</th>\n",
       "      <th>agent</th>\n",
       "      <th>custom_data</th>\n",
       "      <th>dt</th>\n",
       "      <th>http</th>\n",
       "      <th>mediawiki</th>\n",
       "      <th>meta</th>\n",
       "      <th>name</th>\n",
       "      <th>page</th>\n",
       "      <th>performer</th>\n",
       "      <th>user_agent_map</th>\n",
       "      <th>is_wmf_domain</th>\n",
       "      <th>normalized_host</th>\n",
       "      <th>datacenter</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, mobile_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-10-09T08:45:14.517Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.4, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-10-09T08:45:11.643Z,...</td>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>(Requests/Permissions/SK fanella, None, 0, Non...</td>\n",
       "      <td>(None, 0, None, None, 55333, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>eqiad</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, mobile_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-10-09T08:45:45.133Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.4, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-10-09T08:45:38.483Z,...</td>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>(Requests/Permissions/SK fanella, None, 146702...</td>\n",
       "      <td>(None, 1, None, None, 55333, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>eqiad</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, mobile_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-10-09T08:45:17.384Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.4, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-10-09T08:45:11.645Z,...</td>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>(Requests/Permissions/SK fanella, None, 0, Non...</td>\n",
       "      <td>(None, 0, None, None, 55333, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>eqiad</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, mobile_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-10-09T08:45:18.690Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.4, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-10-09T08:45:11.656Z,...</td>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>(Requests/Permissions/SK fanella, None, 0, Non...</td>\n",
       "      <td>(None, 0, None, None, 55333, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>eqiad</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, mobile_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-10-09T08:45:44.516Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.4, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-10-09T08:45:38.458Z,...</td>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>(Requests/Permissions/SK fanella, None, 146702...</td>\n",
       "      <td>(None, 1, None, None, 55333, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>eqiad</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>None</td>\n",
       "      <td>(None, mediawiki_js, mobile_browser)</td>\n",
       "      <td>{'integration': ('string', 'page'), 'editing_s...</td>\n",
       "      <td>2022-10-09T08:45:45.139Z</td>\n",
       "      <td>(None, None, None, {'user-agent': 'Mozilla/5.0...</td>\n",
       "      <td>(None, None, None, None, None, 1.40.0-wmf.4, N...</td>\n",
       "      <td>(test.wikipedia.org, 2022-10-09T08:45:38.813Z,...</td>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>(Requests/Permissions/SK fanella, None, 146702...</td>\n",
       "      <td>(None, 1, None, None, 55333, None, True, None,...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>True</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>eqiad</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  _schema                                 agent  \\\n",
       "0    None  (None, mediawiki_js, mobile_browser)   \n",
       "1    None  (None, mediawiki_js, mobile_browser)   \n",
       "2    None  (None, mediawiki_js, mobile_browser)   \n",
       "3    None  (None, mediawiki_js, mobile_browser)   \n",
       "4    None  (None, mediawiki_js, mobile_browser)   \n",
       "5    None  (None, mediawiki_js, mobile_browser)   \n",
       "\n",
       "                                         custom_data  \\\n",
       "0  {'integration': ('string', 'page'), 'editing_s...   \n",
       "1  {'integration': ('string', 'page'), 'editing_s...   \n",
       "2  {'integration': ('string', 'page'), 'editing_s...   \n",
       "3  {'integration': ('string', 'page'), 'editing_s...   \n",
       "4  {'integration': ('string', 'page'), 'editing_s...   \n",
       "5  {'integration': ('string', 'page'), 'editing_s...   \n",
       "\n",
       "                         dt  \\\n",
       "0  2022-10-09T08:45:14.517Z   \n",
       "1  2022-10-09T08:45:45.133Z   \n",
       "2  2022-10-09T08:45:17.384Z   \n",
       "3  2022-10-09T08:45:18.690Z   \n",
       "4  2022-10-09T08:45:44.516Z   \n",
       "5  2022-10-09T08:45:45.139Z   \n",
       "\n",
       "                                                http  \\\n",
       "0  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "1  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "2  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "3  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "4  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "5  (None, None, None, {'user-agent': 'Mozilla/5.0...   \n",
       "\n",
       "                                           mediawiki  \\\n",
       "0  (None, None, None, None, None, 1.40.0-wmf.4, N...   \n",
       "1  (None, None, None, None, None, 1.40.0-wmf.4, N...   \n",
       "2  (None, None, None, None, None, 1.40.0-wmf.4, N...   \n",
       "3  (None, None, None, None, None, 1.40.0-wmf.4, N...   \n",
       "4  (None, None, None, None, None, 1.40.0-wmf.4, N...   \n",
       "5  (None, None, None, None, None, 1.40.0-wmf.4, N...   \n",
       "\n",
       "                                                meta                 name  \\\n",
       "0  (test.wikipedia.org, 2022-10-09T08:45:11.643Z,...   eas.mf.save_intent   \n",
       "1  (test.wikipedia.org, 2022-10-09T08:45:38.483Z,...         eas.mf.ready   \n",
       "2  (test.wikipedia.org, 2022-10-09T08:45:11.645Z,...  eas.mf.save_attempt   \n",
       "3  (test.wikipedia.org, 2022-10-09T08:45:11.656Z,...  eas.mf.save_success   \n",
       "4  (test.wikipedia.org, 2022-10-09T08:45:38.458Z,...          eas.mf.init   \n",
       "5  (test.wikipedia.org, 2022-10-09T08:45:38.813Z,...        eas.mf.loaded   \n",
       "\n",
       "                                                page  \\\n",
       "0  (Requests/Permissions/SK fanella, None, 0, Non...   \n",
       "1  (Requests/Permissions/SK fanella, None, 146702...   \n",
       "2  (Requests/Permissions/SK fanella, None, 0, Non...   \n",
       "3  (Requests/Permissions/SK fanella, None, 0, Non...   \n",
       "4  (Requests/Permissions/SK fanella, None, 146702...   \n",
       "5  (Requests/Permissions/SK fanella, None, 146702...   \n",
       "\n",
       "                                           performer  \\\n",
       "0  (None, 0, None, None, 55333, None, True, None,...   \n",
       "1  (None, 1, None, None, 55333, None, True, None,...   \n",
       "2  (None, 0, None, None, 55333, None, True, None,...   \n",
       "3  (None, 0, None, None, 55333, None, True, None,...   \n",
       "4  (None, 1, None, None, 55333, None, True, None,...   \n",
       "5  (None, 1, None, None, 55333, None, True, None,...   \n",
       "\n",
       "                                      user_agent_map  is_wmf_domain  \\\n",
       "0  {'os_family': 'Android', 'browser_major': '106...           True   \n",
       "1  {'os_family': 'Android', 'browser_major': '106...           True   \n",
       "2  {'os_family': 'Android', 'browser_major': '106...           True   \n",
       "3  {'os_family': 'Android', 'browser_major': '106...           True   \n",
       "4  {'os_family': 'Android', 'browser_major': '106...           True   \n",
       "5  {'os_family': 'Android', 'browser_major': '106...           True   \n",
       "\n",
       "                         normalized_host datacenter  year  month  day  hour  \n",
       "0  (wikipedia, test, [], org, wikipedia)      eqiad  2022     10    9     8  \n",
       "1  (wikipedia, test, [], org, wikipedia)      eqiad  2022     10    9     8  \n",
       "2  (wikipedia, test, [], org, wikipedia)      eqiad  2022     10    9     8  \n",
       "3  (wikipedia, test, [], org, wikipedia)      eqiad  2022     10    9     8  \n",
       "4  (wikipedia, test, [], org, wikipedia)      eqiad  2022     10    9     8  \n",
       "5  (wikipedia, test, [], org, wikipedia)      eqiad  2022     10    9     8  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mea_query1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "176a2006-4401-4d13-b971-2eddebf42a13",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "eas_query2=spark.run('''\n",
    "SELECT \n",
    "  *\n",
    "  FROM event.editattemptstep\n",
    "  WHERE \n",
    "  event.session_token='0ace36c3e6f09d3668b0'\n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "99261828-0e0c-4594-8933-1978a17fb2a1",
   "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>dt</th>\n",
       "      <th>event</th>\n",
       "      <th>ip</th>\n",
       "      <th>recvfrom</th>\n",
       "      <th>revision</th>\n",
       "      <th>schema</th>\n",
       "      <th>seqid</th>\n",
       "      <th>useragent</th>\n",
       "      <th>uuid</th>\n",
       "      <th>webhost</th>\n",
       "      <th>...</th>\n",
       "      <th>_schema</th>\n",
       "      <th>client_dt</th>\n",
       "      <th>http</th>\n",
       "      <th>meta</th>\n",
       "      <th>user_agent_map</th>\n",
       "      <th>normalized_host</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-10-09T08:45:38.473Z</td>\n",
       "      <td>(init, ecc907e1cd3cc9b88ac4, wikitext, click, ...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>EditAttemptStep</td>\n",
       "      <td>None</td>\n",
       "      <td>(Chrome, 106, None, Lenovo TB-7104F, False, Fa...</td>\n",
       "      <td>None</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-09T08:45:44.524Z</td>\n",
       "      <td>(None, None, None, None, {'user-agent': 'Mozil...</td>\n",
       "      <td>(None, 195de194-b0e5-4239-81e2-d9e99ac6f282, 2...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-09T08:45:38.483Z</td>\n",
       "      <td>(ready, ecc907e1cd3cc9b88ac4, wikitext, None, ...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>EditAttemptStep</td>\n",
       "      <td>None</td>\n",
       "      <td>(Chrome, 106, None, Lenovo TB-7104F, False, Fa...</td>\n",
       "      <td>None</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-09T08:45:45.137Z</td>\n",
       "      <td>(None, None, None, None, {'user-agent': 'Mozil...</td>\n",
       "      <td>(None, 93c276f5-6e37-4c5d-a693-7d1820f6c8f7, e...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10-09T08:45:38.823Z</td>\n",
       "      <td>(loaded, ecc907e1cd3cc9b88ac4, wikitext, None,...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>EditAttemptStep</td>\n",
       "      <td>None</td>\n",
       "      <td>(Chrome, 106, None, Lenovo TB-7104F, False, Fa...</td>\n",
       "      <td>None</td>\n",
       "      <td>test.wikipedia.org</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-09T08:45:45.144Z</td>\n",
       "      <td>(None, None, None, None, {'user-agent': 'Mozil...</td>\n",
       "      <td>(None, deec3806-b63d-42c9-9bb8-ef56c4935a5c, c...</td>\n",
       "      <td>{'os_family': 'Android', 'browser_major': '106...</td>\n",
       "      <td>(wikipedia, test, [], org, wikipedia)</td>\n",
       "      <td>2022</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         dt  \\\n",
       "0  2022-10-09T08:45:38.473Z   \n",
       "1  2022-10-09T08:45:38.483Z   \n",
       "2  2022-10-09T08:45:38.823Z   \n",
       "\n",
       "                                               event    ip recvfrom revision  \\\n",
       "0  (init, ecc907e1cd3cc9b88ac4, wikitext, click, ...  None     None     None   \n",
       "1  (ready, ecc907e1cd3cc9b88ac4, wikitext, None, ...  None     None     None   \n",
       "2  (loaded, ecc907e1cd3cc9b88ac4, wikitext, None,...  None     None     None   \n",
       "\n",
       "            schema seqid                                          useragent  \\\n",
       "0  EditAttemptStep  None  (Chrome, 106, None, Lenovo TB-7104F, False, Fa...   \n",
       "1  EditAttemptStep  None  (Chrome, 106, None, Lenovo TB-7104F, False, Fa...   \n",
       "2  EditAttemptStep  None  (Chrome, 106, None, Lenovo TB-7104F, False, Fa...   \n",
       "\n",
       "   uuid             webhost  ... _schema                 client_dt  \\\n",
       "0  None  test.wikipedia.org  ...    None  2022-10-09T08:45:44.524Z   \n",
       "1  None  test.wikipedia.org  ...    None  2022-10-09T08:45:45.137Z   \n",
       "2  None  test.wikipedia.org  ...    None  2022-10-09T08:45:45.144Z   \n",
       "\n",
       "                                                http  \\\n",
       "0  (None, None, None, None, {'user-agent': 'Mozil...   \n",
       "1  (None, None, None, None, {'user-agent': 'Mozil...   \n",
       "2  (None, None, None, None, {'user-agent': 'Mozil...   \n",
       "\n",
       "                                                meta  \\\n",
       "0  (None, 195de194-b0e5-4239-81e2-d9e99ac6f282, 2...   \n",
       "1  (None, 93c276f5-6e37-4c5d-a693-7d1820f6c8f7, e...   \n",
       "2  (None, deec3806-b63d-42c9-9bb8-ef56c4935a5c, c...   \n",
       "\n",
       "                                      user_agent_map  \\\n",
       "0  {'os_family': 'Android', 'browser_major': '106...   \n",
       "1  {'os_family': 'Android', 'browser_major': '106...   \n",
       "2  {'os_family': 'Android', 'browser_major': '106...   \n",
       "\n",
       "                         normalized_host  year month day hour  \n",
       "0  (wikipedia, test, [], org, wikipedia)  2022    10   9    8  \n",
       "1  (wikipedia, test, [], org, wikipedia)  2022    10   9    8  \n",
       "2  (wikipedia, test, [], org, wikipedia)  2022    10   9    8  \n",
       "\n",
       "[3 rows x 24 columns]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eas_query2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5fb1d42-7087-4e25-a265-70b978fb78e1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "483b7081-d3c0-4346-9a01-69fe5647b6e8",
   "metadata": {},
   "source": [
    "#### Checking overall data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "06052823-06ba-4a69-b7f1-627fa3eb1add",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "eas_query1=spark.run('''\n",
    "SELECT \n",
    "  --date_format(dt, 'yyyy-MM-dd') AS date, \n",
    "  date_format(dt, 'yyyy-MM') AS date, \n",
    "  count(1) AS events\n",
    "  FROM event.editattemptstep\n",
    "  WHERE \n",
    "  year = 2022 \n",
    "  AND wiki = 'testwiki' \n",
    "  AND month=11\n",
    "  GROUP BY date \n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e63b2671-715e-46fd-8d4e-9603ad6b2db9",
   "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>date</th>\n",
       "      <th>events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>3619</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      date  events\n",
       "0  2022-11    3619"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eas_query1.sort_values(by=['date'],ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a094692c-216a-4ad3-a886-19d1aedee409",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mea_query1=spark.run('''\n",
    "SELECT \n",
    "  date_format(dt, 'yyyy-MM') AS date,\n",
    "  count(1) AS events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE \n",
    "  year = 2022\n",
    "  AND normalized_host.project = 'test'\n",
    "  AND month=11\n",
    "  GROUP BY date \n",
    "''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "78a52227-5088-40d1-8df7-25aee8110fa2",
   "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>date</th>\n",
       "      <th>events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>751</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      date  events\n",
       "0  2022-11     751"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mea_query1.sort_values(by=['date'],ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60ae9c70-ab17-420c-9f6c-e532b3815652",
   "metadata": {},
   "source": [
    "MEA and EAS group 0 wikis are sampled at 100% however, \n",
    "### The number of events on MEA is far lesser, almost 5 times lesser than EAS. This is a huge data QA issue and needs to be looked into. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a49f5c8-cfd8-499f-91b6-af0d8461d800",
   "metadata": {},
   "source": [
    "One potential reason this could be happening is because Metrics Platform is not firing wikitext editor events which I discovered in round 3. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9f92a584-f9f4-4d63-8bc1-489afd7bb8a7",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "mea_query2=spark.run('''\n",
    "SELECT \n",
    "  date_format(dt, 'yyyy-MM') AS date,\n",
    "  name,\n",
    "  count(1) AS events\n",
    "  FROM event.mediawiki_edit_attempt\n",
    "  WHERE \n",
    "  year = 2022\n",
    "  AND normalized_host.project = 'test'\n",
    "  AND name like 'eas.mf%'\n",
    "  AND month=11\n",
    "  GROUP BY date, name \n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1dca5338-19d1-42dd-a3e8-ebdf8d5fb3dd",
   "metadata": {},
   "source": [
    "All wikitext events in 2022-11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3ec1dea2-a2a9-41d2-8895-56e038f957ae",
   "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>date</th>\n",
       "      <th>name</th>\n",
       "      <th>events</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>eas.mf.init</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>eas.mf.ready</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>eas.mf.loaded</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>eas.mf.abort</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>eas.mf.save_intent</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>eas.mf.first_change</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>eas.mf.save_attempt</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-11</td>\n",
       "      <td>eas.mf.save_success</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      date                 name  events\n",
       "4  2022-11          eas.mf.init      21\n",
       "0  2022-11         eas.mf.ready      20\n",
       "3  2022-11        eas.mf.loaded      20\n",
       "5  2022-11         eas.mf.abort       7\n",
       "1  2022-11   eas.mf.save_intent       5\n",
       "2  2022-11  eas.mf.first_change       5\n",
       "6  2022-11  eas.mf.save_attempt       4\n",
       "7  2022-11  eas.mf.save_success       4"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mea_query2.sort_values(by=['events'],ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "615fd5f4-aba7-451c-8ad2-e9839a0fc394",
   "metadata": {},
   "source": [
    "MEA has 21 wikitext init events as of 11-09-2022, which is very low. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad9c5f67-1ee4-4293-94fe-59c23d233ed4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}