infographic-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COVID-19 Infographic "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Getting data for [Strongly-related COVID articles][https://meta.wikimedia.org/wiki/User:Diego_(WMF)/COVID-19_Articles_(All_but_Q5)]: \n",
    "\n",
    "- Total number of strongly-related COVID articles globally, both by language and combined languages\n",
    "- Total number of languages that strongly-related COVID articles exist in \n",
    "- Total number of pageviews from Dec. 2019 - present on strongly-related COVID articles globally\n",
    "- Total number of edits to strongly-related COVID articles globally\n",
    "- Frequency of edits on strongly-related COVID articles globally \n",
    "- Total number of editors on all strongly-related COVID articles\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import json\n",
    "import matplotlib.pyplot as plt\n",
    "import gzip\n",
    "from wmfdata import hive,spark\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "## load COVID related page list into hive table\n",
    "filepath = \"COVID-article-list-03_30_2020.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "hive.load_csv(\n",
    "    filepath,\n",
    "    field_spec=\"Wikidata string, project string, page string, url string, wikilink string\",\n",
    "    db_name=\"cchen\",\n",
    "    table_name=\"covid_pages\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Language & Articles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "## join canonical wiki table to get language and language code\n",
    "related_pages = spark.run(\"\"\"\n",
    "    SELECT\n",
    "      w.database_code, \n",
    "      p.project,\n",
    "      w.language_code, \n",
    "      w.language_name,\n",
    "      p.page, \n",
    "      p.url\n",
    "    FROM cchen.covid_pages p \n",
    "      LEFT JOIN canonical_data.wikis w ON  CONCAT(p.project,'.org') =   w.domain_name \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>database_code</th>\n",
       "      <th>project</th>\n",
       "      <th>language_code</th>\n",
       "      <th>language_name</th>\n",
       "      <th>page</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>afwiki</td>\n",
       "      <td>af.wikipedia</td>\n",
       "      <td>af</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>Ernstige akute respiratoriese sindroom</td>\n",
       "      <td>https://af.wikipedia.org/wiki/Ernstige akute r...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>arwiki</td>\n",
       "      <td>ar.wikipedia</td>\n",
       "      <td>ar</td>\n",
       "      <td>Arabic</td>\n",
       "      <td>متلازمة تنفسية حادة وخيمة</td>\n",
       "      <td>https://ar.wikipedia.org/wiki/متلازمة تنفسية ح...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>astwiki</td>\n",
       "      <td>ast.wikipedia</td>\n",
       "      <td>ast</td>\n",
       "      <td>Asturian</td>\n",
       "      <td>Síndrome respiratoriu agudu grave</td>\n",
       "      <td>https://ast.wikipedia.org/wiki/Síndrome respir...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>azbwiki</td>\n",
       "      <td>azb.wikipedia</td>\n",
       "      <td>azb</td>\n",
       "      <td>South Azerbaijani</td>\n",
       "      <td>سارس</td>\n",
       "      <td>https://azb.wikipedia.org/wiki/سارس</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bgwiki</td>\n",
       "      <td>bg.wikipedia</td>\n",
       "      <td>bg</td>\n",
       "      <td>Bulgarian</td>\n",
       "      <td>Тежък остър респираторен синдром</td>\n",
       "      <td>https://bg.wikipedia.org/wiki/Тежък остър респ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  database_code        project language_code      language_name  \\\n",
       "0        afwiki   af.wikipedia            af          Afrikaans   \n",
       "1        arwiki   ar.wikipedia            ar             Arabic   \n",
       "2       astwiki  ast.wikipedia           ast           Asturian   \n",
       "3       azbwiki  azb.wikipedia           azb  South Azerbaijani   \n",
       "4        bgwiki   bg.wikipedia            bg          Bulgarian   \n",
       "\n",
       "                                     page  \\\n",
       "0  Ernstige akute respiratoriese sindroom   \n",
       "1               متلازمة تنفسية حادة وخيمة   \n",
       "2       Síndrome respiratoriu agudu grave   \n",
       "3                                    سارس   \n",
       "4        Тежък остър респираторен синдром   \n",
       "\n",
       "                                                 url  \n",
       "0  https://af.wikipedia.org/wiki/Ernstige akute r...  \n",
       "1  https://ar.wikipedia.org/wiki/متلازمة تنفسية ح...  \n",
       "2  https://ast.wikipedia.org/wiki/Síndrome respir...  \n",
       "3                https://azb.wikipedia.org/wiki/سارس  \n",
       "4  https://bg.wikipedia.org/wiki/Тежък остър респ...  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "related_pages[0:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of related COVID articles globally is 3372\n",
      "Total number of languages that strongly-related COVID articles exist is 143\n"
     ]
    }
   ],
   "source": [
    "print('Total number of related COVID articles globally is %s' %len(related_pages))\n",
    "print('Total number of languages that strongly-related COVID articles exist is %s' %related_pages[\"language_code\"].nunique(dropna = True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>language_code</th>\n",
       "      <th>language_name</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>af</td>\n",
       "      <td>Afrikaans</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>als</td>\n",
       "      <td>Alsatian</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>am</td>\n",
       "      <td>Amharic</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>an</td>\n",
       "      <td>Aragonese</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ang</td>\n",
       "      <td>Old English</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  language_code language_name  counts\n",
       "0            af     Afrikaans       4\n",
       "1           als      Alsatian       2\n",
       "2            am       Amharic       2\n",
       "3            an     Aragonese       4\n",
       "4           ang   Old English       1"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## count of articles by languages\n",
    "related_page_lan = related_pages.groupby(['language_code', 'language_name']).size().reset_index(name='article_counts')\n",
    "related_page_lan[0:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "related_page_lan.to_csv(\"articles_per_language.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Edits and Edit frequency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "related_dict = related_pages.groupby('project')['page'].apply(list).to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mwapi\n",
    "import pandas as pd\n",
    "\n",
    "def countRevisionsPerDay(page_name,project,startdate):\n",
    "    \"\"\"\n",
    "    page_name: str, article title, ex: 'COVID-19'\n",
    "    project: str, project id, ex: 'es.wikipedia'\n",
    "    startdate: timestamp, counting from given day example '2020-01-01T00:00:00Z'\n",
    "    \n",
    "    \"\"\"\n",
    "    counterPerDay = {}\n",
    "    \n",
    "    session = mwapi.Session(\"https://%s.org\" % project, user_agent=\"cchen@wikimedia.org - COVID-19 research\")\n",
    "    for response_doc in session.get(action='query', prop='revisions', titles=page_name, \n",
    "                                    rvprop=['ids', 'timestamp'], rvlimit=100, rvdir=\"newer\", \n",
    "                                    formatversion=2, rvstart=startdate, continuation=True):\n",
    "        for rev_doc in response_doc['query']['pages'][0]['revisions']:\n",
    "            rev_id = rev_doc['revid']\n",
    "            day = pd.to_datetime(rev_doc['timestamp']).strftime(\"%Y-%m-%d\")\n",
    "            counterPerDay[day] = counterPerDay.get(day,0)\n",
    "            counterPerDay[day]  += 1\n",
    "    output = pd.DataFrame.from_dict(counterPerDay,orient='index',columns=[page_name])\n",
    "    output.index = pd.to_datetime(output.index)\n",
    "    return output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "be_x_old.wikipedia\n",
      "ennews.wikipedia\n",
      "atj.wikipedia\n",
      "tl.wikipedia\n",
      "hak.wikipedia\n",
      "ast.wikipedia\n",
      "itvoyage.wikipedia\n",
      "wuu.wikipedia\n",
      "enquote.wikipedia\n",
      "itquote.wikipedia\n",
      "am.wikipedia\n",
      "hy.wikipedia\n",
      "el.wikipedia\n",
      "mwl.wikipedia\n",
      "oc.wikipedia\n",
      "sh.wikipedia\n",
      "esversity.wikipedia\n",
      "pap.wikipedia\n",
      "ko.wikipedia\n",
      "zh.wikipedia\n",
      "frquote.wikipedia\n",
      "sat.wikipedia\n",
      "frvoyage.wikipedia\n",
      "ukquote.wikipedia\n",
      "mn.wikipedia\n",
      "or.wikipedia\n",
      "frnews.wikipedia\n",
      "ptnews.wikipedia\n",
      "ceb.wikipedia\n",
      "se.wikipedia\n",
      "sd.wikipedia\n",
      "konews.wikipedia\n",
      "bcl.wikipedia\n",
      "zh_classical.wikipedia\n",
      "fi.wikipedia\n",
      "qu.wikipedia\n",
      "esvoyage.wikipedia\n",
      "mg.wikipedia\n",
      "gl.wikipedia\n",
      "tr.wikipedia\n",
      "war.wikipedia\n",
      "ro.wikipedia\n",
      "id.wikipedia\n",
      "esnews.wikipedia\n",
      "ta.wikipedia\n",
      "rue.wikipedia\n",
      "eu.wikipedia\n",
      "mnw.wikipedia\n",
      "finews.wikipedia\n",
      "pa.wikipedia\n",
      "azb.wikipedia\n",
      "uknews.wikipedia\n",
      "ne.wikipedia\n",
      "data.wikipedia\n",
      "wa.wikipedia\n",
      "lij.wikipedia\n",
      "et.wikipedia\n",
      "zh_yue.wikipedia\n",
      "ug.wikipedia\n",
      "zhnews.wikipedia\n",
      "sw.wikipedia\n",
      "frp.wikipedia\n",
      "ha.wikipedia\n",
      "eml.wikipedia\n",
      "km.wikipedia\n",
      "enversity.wikipedia\n",
      "species.wikipedia\n",
      "az.wikipedia\n",
      "kk.wikipedia\n",
      "min.wikipedia\n",
      "nrm.wikipedia\n",
      "ang.wikipedia\n",
      "envoyage.wikipedia\n",
      "cv.wikipedia\n",
      "fa.wikipedia\n",
      "bh.wikipedia\n",
      "sl.wikipedia\n",
      "frr.wikipedia\n",
      "ga.wikipedia\n",
      "dty.wikipedia\n",
      "zh_min_nan.wikipedia\n",
      "br.wikipedia\n",
      "deversity.wikipedia\n",
      "szl.wikipedia\n",
      "pt.wikipedia\n",
      "lmo.wikipedia\n",
      "nds.wikipedia\n",
      "jv.wikipedia\n",
      "ca.wikipedia\n",
      "jasource.wikipedia\n",
      "ht.wikipedia\n",
      "sq.wikipedia\n",
      "cdo.wikipedia\n",
      "ka.wikipedia\n",
      "xmf.wikipedia\n",
      "diq.wikipedia\n",
      "th.wikipedia\n",
      "as.wikipedia\n",
      "arz.wikipedia\n",
      "hequote.wikipedia\n",
      "ru.wikipedia\n",
      "als.wikipedia\n",
      "ms.wikipedia\n",
      "ckb.wikipedia\n",
      "bg.wikipedia\n",
      "zhvoyage.wikipedia\n",
      "sv.wikipedia\n",
      "ar.wikipedia\n",
      "cy.wikipedia\n",
      "my.wikipedia\n",
      "hesource.wikipedia\n",
      "vi.wikipedia\n",
      "kosource.wikipedia\n",
      "he.wikipedia\n",
      "sr.wikipedia\n",
      "mr.wikipedia\n",
      "ensource.wikipedia\n",
      "ml.wikipedia\n",
      "nlnews.wikipedia\n",
      "ku.wikipedia\n",
      "si.wikipedia\n",
      "mk.wikipedia\n",
      "simple.wikipedia\n",
      "plnews.wikipedia\n",
      "sco.wikipedia\n",
      "no.wikipedia\n",
      "fr.wikipedia\n",
      "ur.wikipedia\n",
      "gu.wikipedia\n",
      "zhsource.wikipedia\n",
      "pl.wikipedia\n",
      "bat_smg.wikipedia\n",
      "te.wikipedia\n",
      "la.wikipedia\n",
      "an.wikipedia\n",
      "su.wikipedia\n",
      "ti.wikipedia\n",
      "commons.wikipedia\n",
      "tg.wikipedia\n",
      "hu.wikipedia\n",
      "ja.wikipedia\n",
      "ky.wikipedia\n",
      "it.wikipedia\n",
      "lb.wikipedia\n",
      "bn.wikipedia\n",
      "hyw.wikipedia\n",
      "meta.wikipedia\n",
      "tk.wikipedia\n",
      "tet.wikipedia\n",
      "itversity.wikipedia\n",
      "dv.wikipedia\n",
      "hi.wikipedia\n",
      "is.wikipedia\n",
      "yo.wikipedia\n",
      "en.wikipedia\n",
      "ia.wikipedia\n",
      "eonews.wikipedia\n",
      "uz.wikipedia\n",
      "ban.wikipedia\n",
      "sah.wikipedia\n",
      "lt.wikipedia\n",
      "uk.wikipedia\n",
      "runews.wikipedia\n",
      "be.wikipedia\n",
      "sk.wikipedia\n",
      "nl.wikipedia\n",
      "kab.wikipedia\n",
      "lv.wikipedia\n",
      "cs.wikipedia\n",
      "es.wikipedia\n",
      "eo.wikipedia\n",
      "bo.wikipedia\n",
      "crh.wikipedia\n",
      "de.wikipedia\n",
      "af.wikipedia\n",
      "vec.wikipedia\n",
      "bs.wikipedia\n",
      "sc.wikipedia\n",
      "da.wikipedia\n",
      "hr.wikipedia\n",
      "hevoyage.wikipedia\n",
      "nn.wikipedia\n",
      "li.wikipedia\n",
      "din.wikipedia\n",
      "kn.wikipedia\n"
     ]
    }
   ],
   "source": [
    "#there are some errors/warnings. Don't worry, we are getting a lower-bound\n",
    "perDayResults = {}\n",
    "startDate = '2020-01-01T00:00:00Z'\n",
    "for project, pages in related_dict.items():\n",
    "    print(project)\n",
    "    for page in pages:\n",
    "        try:\n",
    "            perDay = countRevisionsPerDay(page,project,startDate)\n",
    "            perDayResults[project] = perDayResults.get(project,[])\n",
    "            perDayResults[project].append(perDay)\n",
    "        except:\n",
    "            #print('error in %s %s' % (page,project))\n",
    "            pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "resultsPerProjectperDay = {}\n",
    "for project, edits in perDayResults.items():\n",
    "    resultsPerProjectperDay[project] = pd.concat(edits,axis=1)\n",
    "    resultsPerProjectperDay[project].index = pd.to_datetime(resultsPerProjectperDay[project].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "allEditsPerDay = []\n",
    "for project, edits in resultsPerProjectperDay.items():\n",
    "    allEditsPerDay.append(pd.DataFrame(edits.sum(axis=1)))\n",
    "allEditsPerDay = pd.concat(allEditsPerDay,axis=1).sum(axis=1)\n",
    "\n",
    "edits_df = pd.DataFrame({'date':allEditsPerDay.index, 'edit_count':allEditsPerDay.values})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>edit_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-12-31</th>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-31</th>\n",
       "      <td>29085.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29</th>\n",
       "      <td>62225.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-31</th>\n",
       "      <td>248791.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            edit_count\n",
       "date                  \n",
       "2019-12-31        36.0\n",
       "2020-01-31     29085.0\n",
       "2020-02-29     62225.0\n",
       "2020-03-31    248791.0"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## monthly edits\n",
    "edits_df.groupby(pd.Grouper(key='date',freq='M')).sum()  \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of edits 340137.0\n",
      "Avg Edits per Day 3779.0\n",
      "Avg Edits per hour 157.0\n"
     ]
    }
   ],
   "source": [
    "print('Total number of edits %s' % edits_df.edit_count.sum())\n",
    "avgPerDay = round(edits_df.edit_count.sum()/(31+29+30))\n",
    "print('Avg Edits per Day %s' % avgPerDay )\n",
    "avgPerHour = round(edits_df.edit_count.sum()/(24*(31+29+30)))\n",
    "print('Avg Edits per hour %s' % avgPerHour )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Editors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "def countEditors(page_name,project,date):\n",
    "    \"\"\"\n",
    "    page_name: str, article title, ex: 'COVID-19'\n",
    "    project: str, project id, ex: 'es.wikipedia'\n",
    "    date: timestamp, counting from given day example '2020-01-01T00:00:00Z'\n",
    "    \n",
    "    \"\"\"\n",
    "    editors = {} #cross project user name should be the same.\n",
    "    ananoymous = {}\n",
    "    counter = 0\n",
    "    session = mwapi.Session(\"https://%s.org\" % project, user_agent=\"dsaez@wikimedia.org - COVID-19 research\")\n",
    "    for response_doc in session.get(action='query', prop='revisions', titles=page_name, \n",
    "                                    rvprop=['ids', 'timestamp','user','userid'], rvlimit=100, rvdir=\"newer\", \n",
    "                                    formatversion=2, rvstart=date, continuation=True):\n",
    "        for rev_doc in response_doc['query']['pages'][0]['revisions']:\n",
    "            rev_id = rev_doc['revid']\n",
    "            timestamp = rev_doc['timestamp']\n",
    "            user = rev_doc['user']\n",
    "            userid = str(rev_doc['userid']) #to avoid user id overlaps across projects\n",
    "            counter += 1\n",
    "            if userid != '0':  \n",
    "                editors[user] = editors.get(user,0) + 1\n",
    "            else:\n",
    "                ananoymous[user] = ananoymous.get(user,0) +1\n",
    "\n",
    "    return editors,ananoymous"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "be_x_old.wikipedia\n",
      "ennews.wikipedia\n",
      "atj.wikipedia\n",
      "tl.wikipedia\n",
      "hak.wikipedia\n",
      "ast.wikipedia\n",
      "itvoyage.wikipedia\n",
      "wuu.wikipedia\n",
      "enquote.wikipedia\n",
      "itquote.wikipedia\n",
      "am.wikipedia\n",
      "hy.wikipedia\n",
      "el.wikipedia\n",
      "mwl.wikipedia\n",
      "oc.wikipedia\n",
      "sh.wikipedia\n",
      "esversity.wikipedia\n",
      "pap.wikipedia\n",
      "ko.wikipedia\n",
      "zh.wikipedia\n",
      "frquote.wikipedia\n",
      "sat.wikipedia\n",
      "frvoyage.wikipedia\n",
      "ukquote.wikipedia\n",
      "mn.wikipedia\n",
      "or.wikipedia\n",
      "frnews.wikipedia\n",
      "ptnews.wikipedia\n",
      "ceb.wikipedia\n",
      "se.wikipedia\n",
      "sd.wikipedia\n",
      "konews.wikipedia\n",
      "bcl.wikipedia\n",
      "zh_classical.wikipedia\n",
      "fi.wikipedia\n",
      "qu.wikipedia\n",
      "esvoyage.wikipedia\n",
      "mg.wikipedia\n",
      "gl.wikipedia\n",
      "tr.wikipedia\n",
      "war.wikipedia\n",
      "ro.wikipedia\n",
      "id.wikipedia\n",
      "esnews.wikipedia\n",
      "ta.wikipedia\n",
      "rue.wikipedia\n",
      "eu.wikipedia\n",
      "mnw.wikipedia\n",
      "finews.wikipedia\n",
      "pa.wikipedia\n",
      "azb.wikipedia\n",
      "uknews.wikipedia\n",
      "ne.wikipedia\n",
      "data.wikipedia\n",
      "wa.wikipedia\n",
      "lij.wikipedia\n",
      "et.wikipedia\n",
      "zh_yue.wikipedia\n",
      "ug.wikipedia\n",
      "zhnews.wikipedia\n",
      "sw.wikipedia\n",
      "frp.wikipedia\n",
      "ha.wikipedia\n",
      "eml.wikipedia\n",
      "km.wikipedia\n",
      "enversity.wikipedia\n",
      "species.wikipedia\n",
      "az.wikipedia\n",
      "kk.wikipedia\n",
      "min.wikipedia\n",
      "nrm.wikipedia\n",
      "ang.wikipedia\n",
      "envoyage.wikipedia\n",
      "cv.wikipedia\n",
      "fa.wikipedia\n",
      "bh.wikipedia\n",
      "sl.wikipedia\n",
      "frr.wikipedia\n",
      "ga.wikipedia\n",
      "dty.wikipedia\n",
      "zh_min_nan.wikipedia\n",
      "br.wikipedia\n",
      "deversity.wikipedia\n",
      "szl.wikipedia\n",
      "pt.wikipedia\n",
      "lmo.wikipedia\n",
      "nds.wikipedia\n",
      "jv.wikipedia\n",
      "ca.wikipedia\n",
      "jasource.wikipedia\n",
      "ht.wikipedia\n",
      "sq.wikipedia\n",
      "cdo.wikipedia\n",
      "ka.wikipedia\n",
      "xmf.wikipedia\n",
      "diq.wikipedia\n",
      "th.wikipedia\n",
      "as.wikipedia\n",
      "arz.wikipedia\n",
      "hequote.wikipedia\n",
      "ru.wikipedia\n",
      "als.wikipedia\n",
      "ms.wikipedia\n",
      "ckb.wikipedia\n",
      "bg.wikipedia\n",
      "zhvoyage.wikipedia\n",
      "sv.wikipedia\n",
      "ar.wikipedia\n",
      "cy.wikipedia\n",
      "my.wikipedia\n",
      "hesource.wikipedia\n",
      "vi.wikipedia\n",
      "kosource.wikipedia\n",
      "he.wikipedia\n",
      "sr.wikipedia\n",
      "mr.wikipedia\n",
      "ensource.wikipedia\n",
      "ml.wikipedia\n",
      "nlnews.wikipedia\n",
      "ku.wikipedia\n",
      "si.wikipedia\n",
      "mk.wikipedia\n",
      "simple.wikipedia\n",
      "plnews.wikipedia\n",
      "sco.wikipedia\n",
      "no.wikipedia\n",
      "fr.wikipedia\n",
      "ur.wikipedia\n",
      "gu.wikipedia\n",
      "zhsource.wikipedia\n",
      "pl.wikipedia\n",
      "bat_smg.wikipedia\n",
      "te.wikipedia\n",
      "la.wikipedia\n",
      "an.wikipedia\n",
      "su.wikipedia\n",
      "ti.wikipedia\n",
      "commons.wikipedia\n",
      "tg.wikipedia\n",
      "hu.wikipedia\n",
      "ja.wikipedia\n",
      "ky.wikipedia\n",
      "it.wikipedia\n",
      "lb.wikipedia\n",
      "bn.wikipedia\n",
      "hyw.wikipedia\n",
      "meta.wikipedia\n",
      "tk.wikipedia\n",
      "tet.wikipedia\n",
      "itversity.wikipedia\n",
      "dv.wikipedia\n",
      "hi.wikipedia\n",
      "is.wikipedia\n",
      "yo.wikipedia\n",
      "en.wikipedia\n",
      "ia.wikipedia\n",
      "eonews.wikipedia\n",
      "uz.wikipedia\n",
      "ban.wikipedia\n",
      "sah.wikipedia\n",
      "lt.wikipedia\n",
      "uk.wikipedia\n",
      "runews.wikipedia\n",
      "be.wikipedia\n",
      "sk.wikipedia\n",
      "nl.wikipedia\n",
      "kab.wikipedia\n",
      "lv.wikipedia\n",
      "cs.wikipedia\n",
      "es.wikipedia\n",
      "eo.wikipedia\n",
      "bo.wikipedia\n",
      "crh.wikipedia\n",
      "de.wikipedia\n",
      "af.wikipedia\n",
      "vec.wikipedia\n",
      "bs.wikipedia\n",
      "sc.wikipedia\n",
      "da.wikipedia\n",
      "hr.wikipedia\n",
      "hevoyage.wikipedia\n",
      "nn.wikipedia\n",
      "li.wikipedia\n",
      "din.wikipedia\n",
      "kn.wikipedia\n"
     ]
    }
   ],
   "source": [
    "#there are some errors/warnings. Don't worry, we are getting a lower-bound\n",
    "totalEditors = set()\n",
    "anononymous = set()\n",
    "startDate = '2020-01-01T00:00:00Z'\n",
    "for project, pages in related_dict.items():\n",
    "    print(project)\n",
    "    for page in pages:\n",
    "        try:\n",
    "            result= countEditors(page,project,startDate)\n",
    "            totalEditors = totalEditors.union(result[0].keys())\n",
    "            anononymous = anononymous.union(result[1].keys())            \n",
    "        except:\n",
    "            pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number registered editors 16651\n",
      "Number IPs  (anonymous) 18166\n"
     ]
    }
   ],
   "source": [
    "print('Number registered editors %s' %len(totalEditors))\n",
    "print('Number IPs  (anonymous) %s' %len(anononymous))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pageviews"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext rpy2.ipython"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%R\n",
    "library(waxer)\n",
    "library(data.table)\n",
    "library(tidyverse)\n",
    "library(lubridate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%R -i related_pages\n",
    "\n",
    "related_pages = data.table(related_pages)\n",
    "\n",
    "pv_start = \"20191201\"\n",
    "pv_end = \"20200401\"\n",
    "\n",
    "pageviews = tibble(project = 'na', page_name = 'na', date = as.Date('2019-12-01'), views = 0)\n",
    "\n",
    "for (row in 1:nrow(related_pages))\n",
    "{\n",
    "\n",
    "    project_name =  related_pages[row,\"project\"]\n",
    "    page = related_pages[row,\"page\"]\n",
    "    \n",
    "    \n",
    "      try({  \n",
    "          \n",
    "          result = wx_page_views(\n",
    "            project = toString(project_name[[1]]),\n",
    "            page_name = toString(page[[1]]),\n",
    "            start_date = pv_start,\n",
    "            end_date = pv_end )\n",
    "           \n",
    "       pageviews = bind_rows(pageviews,result)}, silent = TRUE)\n",
    "\n",
    "} \n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "# A tibble: 4 x 2\n",
       "  month      total_views\n",
       "  <date>           <dbl>\n",
       "1 2019-12-01       92062\n",
       "2 2020-01-01     4821864\n",
       "3 2020-02-01     8454535\n",
       "4 2020-03-01   172509391\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%R \n",
    "\n",
    " pageviews %>% filter(date <= '2020-03-30')%>% group_by(month=floor_date(date, \"month\")) %>%\n",
    "   summarize(total_views =sum(views)) \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "language = spark.run(\"\"\"\n",
    "    SELECT\n",
    "      database_code, \n",
    "      domain_name as project,\n",
    "      w.language_code, \n",
    "      w.language_name\n",
    "    FROM canonical_data.wikis w \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%R -i language\n",
    "\n",
    "language = as.tibble(language)\n",
    "language = as.tibble(lapply (language,as.character))\n",
    "language$project = substr(language$project, 1,nchar(language$project)-4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%R \n",
    "    \n",
    "pv_language = merge(pv_project,language, by.x= \"value\", by.y = \"project\", all.x = TRUE)\n",
    "pv_language  = pv_language %>% select(month, total_views, language_name) %>% group_by (month, language_name) %>%\n",
    "   summarize(views =sum(total_views)) %>%\n",
    "    spread(month, views)\n",
    "\n",
    "write.csv(pv_language,\"pageviews by language.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}