data_collection_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## COVID-19 Infographic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we focus in articles related to COVID-19 (Q84263196) and 2019–20 COVID-19 pandemic (Q81068910). Finding relevant connections such as \"Main Subject\", \"Part Of\" or \"Has caused\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from SPARQLWrapper import SPARQLWrapper, JSON\n",
    "import pandas as pd\n",
    "\n",
    "sparql = SPARQLWrapper(\"https://query.wikidata.org/sparql\")\n",
    "#https://w.wiki/KvX (Thanks User:Dipsacus_fullonum)\n",
    "# All statements with item, property, value and rank with COVID-19 (Q84263196) as value for qualifier.\n",
    "\n",
    "sparql.setQuery(\"\"\"\n",
    "SELECT ?item ?itemLabel ?property ?propertyLabel ?value ?valueLabel ?rank ?qualifier ?qualifierLabel\n",
    "WHERE\n",
    "{\n",
    "  ?item ?claim ?statement.\n",
    "  ?property wikibase:claim ?claim.\n",
    "  ?property wikibase:statementProperty ?sprop.\n",
    "  ?statement ?sprop ?value.\n",
    "  ?statement wikibase:rank ?rank. \n",
    "  ?statement ?qprop wd:Q84263196. # COVID-19\n",
    "\n",
    "  \n",
    "  ?qualifier wikibase:qualifier ?qprop.\n",
    "  SERVICE wikibase:label { bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],en\". }\n",
    "}\n",
    "\"\"\")\n",
    "sparql.setReturnFormat(JSON)\n",
    "results = sparql.query().convert()\n",
    "\n",
    "allStatements = pd.io.json.json_normalize(results['results']['bindings'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "disease outbreak                 265\n",
       "human                              6\n",
       "treatment                          2\n",
       "drug development                   1\n",
       "mascot character                   1\n",
       "moe anthropomorphic character      1\n",
       "drug repositioning                 1\n",
       "medical diagnosis                  1\n",
       "diagnostic test                    1\n",
       "vaccine                            1\n",
       "pandemic                           1\n",
       "Name: valueLabel.value, dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allStatements['valueLabel.value'].value_counts()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>item.value</th>\n",
       "      <th>itemLabel.value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.wikidata.org/entity/Q83873593</td>\n",
       "      <td>2020 coronavirus pandemic in France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.wikidata.org/entity/Q83889294</td>\n",
       "      <td>2020 coronavirus pandemic in Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.wikidata.org/entity/Q84166704</td>\n",
       "      <td>2020 coronavirus pandemic in Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.wikidata.org/entity/Q84055415</td>\n",
       "      <td>2019–20 coronavirus outbreak in Finland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.wikidata.org/entity/Q84081576</td>\n",
       "      <td>2020 coronavirus pandemic in Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>http://www.wikidata.org/entity/Q84098939</td>\n",
       "      <td>2020 coronavirus pandemic in Russia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>http://www.wikidata.org/entity/Q84446340</td>\n",
       "      <td>2020 coronavirus pandemic in Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>http://www.wikidata.org/entity/Q86521237</td>\n",
       "      <td>2020 coronavirus outbreak in Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>http://www.wikidata.org/entity/Q83872291</td>\n",
       "      <td>2019–20 coronavirus outbreak in Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>http://www.wikidata.org/entity/Q83873057</td>\n",
       "      <td>2019–20 coronavirus outbreak in Vietnam</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 item.value  \\\n",
       "0  http://www.wikidata.org/entity/Q83873593   \n",
       "1  http://www.wikidata.org/entity/Q83889294   \n",
       "2  http://www.wikidata.org/entity/Q84166704   \n",
       "3  http://www.wikidata.org/entity/Q84055415   \n",
       "4  http://www.wikidata.org/entity/Q84081576   \n",
       "5  http://www.wikidata.org/entity/Q84098939   \n",
       "6  http://www.wikidata.org/entity/Q84446340   \n",
       "7  http://www.wikidata.org/entity/Q86521237   \n",
       "8  http://www.wikidata.org/entity/Q83872291   \n",
       "9  http://www.wikidata.org/entity/Q83873057   \n",
       "\n",
       "                           itemLabel.value  \n",
       "0      2020 coronavirus pandemic in France  \n",
       "1     2020 coronavirus pandemic in Germany  \n",
       "2       2020 coronavirus pandemic in Spain  \n",
       "3  2019–20 coronavirus outbreak in Finland  \n",
       "4      2020 coronavirus pandemic in Sweden  \n",
       "5      2020 coronavirus pandemic in Russia  \n",
       "6     2020 coronavirus pandemic in Belgium  \n",
       "7        2020 coronavirus outbreak in Asia  \n",
       "8    2019–20 coronavirus outbreak in Japan  \n",
       "9  2019–20 coronavirus outbreak in Vietnam  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allStatements[['item.value','itemLabel.value']].head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# All truthy statements with COVID-19 (Q84263196) as value.\n",
    "#https://w.wiki/KvZ (Thanks User:Dipsacus_fullonum)\n",
    "\n",
    "sparql.setQuery(\"\"\"\n",
    "SELECT ?item ?itemLabel ?property ?propertyLabel\n",
    "WHERE\n",
    "{\n",
    "  ?item ?claim wd:Q84263196.\n",
    "  ?property wikibase:directClaim ?claim.\n",
    "   SERVICE wikibase:label { bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],en\". }\n",
    "}\"\"\")\n",
    "sparql.setReturnFormat(JSON)\n",
    "results = sparql.query().convert()\n",
    "\n",
    "truthy = pd.io.json.json_normalize(results['results']['bindings'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>item.value</th>\n",
       "      <th>itemLabel.value</th>\n",
       "      <th>propertyLabel.value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>http://www.wikidata.org/entity/Q88627924</td>\n",
       "      <td>Jorge Alcibíades García Lara</td>\n",
       "      <td>medical condition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>894</th>\n",
       "      <td>http://www.wikidata.org/entity/Q88503664</td>\n",
       "      <td>Harouna Kaboré</td>\n",
       "      <td>medical condition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>640</th>\n",
       "      <td>http://www.wikidata.org/entity/Q87369505</td>\n",
       "      <td>The COVID‐19 epidemic</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>719</th>\n",
       "      <td>http://www.wikidata.org/entity/Q87648634</td>\n",
       "      <td>2020 coronavirus pandemic in Armenia</td>\n",
       "      <td>has cause</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>870</th>\n",
       "      <td>http://www.wikidata.org/entity/Q88090268</td>\n",
       "      <td>Jean-Paul Hamon</td>\n",
       "      <td>medical condition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>http://www.wikidata.org/entity/Q13742419</td>\n",
       "      <td>Kees Bakker</td>\n",
       "      <td>cause of death</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518</th>\n",
       "      <td>http://www.wikidata.org/entity/Q87070975</td>\n",
       "      <td>2020 coronavirus pandemic in Israel</td>\n",
       "      <td>has cause</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1012</th>\n",
       "      <td>http://www.wikidata.org/entity/Q87865656</td>\n",
       "      <td>Detection of Covid-19 in Children in Early Jan...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>http://www.wikidata.org/entity/Q19599015</td>\n",
       "      <td>Javier Jiménez</td>\n",
       "      <td>medical condition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>780</th>\n",
       "      <td>http://www.wikidata.org/entity/Q87647368</td>\n",
       "      <td>The Incubation Period of Coronavirus Disease 2...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    item.value  \\\n",
       "897   http://www.wikidata.org/entity/Q88627924   \n",
       "894   http://www.wikidata.org/entity/Q88503664   \n",
       "640   http://www.wikidata.org/entity/Q87369505   \n",
       "719   http://www.wikidata.org/entity/Q87648634   \n",
       "870   http://www.wikidata.org/entity/Q88090268   \n",
       "203   http://www.wikidata.org/entity/Q13742419   \n",
       "518   http://www.wikidata.org/entity/Q87070975   \n",
       "1012  http://www.wikidata.org/entity/Q87865656   \n",
       "265   http://www.wikidata.org/entity/Q19599015   \n",
       "780   http://www.wikidata.org/entity/Q87647368   \n",
       "\n",
       "                                        itemLabel.value propertyLabel.value  \n",
       "897                        Jorge Alcibíades García Lara   medical condition  \n",
       "894                                      Harouna Kaboré   medical condition  \n",
       "640                               The COVID‐19 epidemic        main subject  \n",
       "719                2020 coronavirus pandemic in Armenia           has cause  \n",
       "870                                     Jean-Paul Hamon   medical condition  \n",
       "203                                         Kees Bakker      cause of death  \n",
       "518                 2020 coronavirus pandemic in Israel           has cause  \n",
       "1012  Detection of Covid-19 in Children in Early Jan...        main subject  \n",
       "265                                      Javier Jiménez   medical condition  \n",
       "780   The Incubation Period of Coronavirus Disease 2...        main subject  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "truthy[['item.value','itemLabel.value','propertyLabel.value']].sample(10).head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "medical condition                454\n",
       "main subject                     234\n",
       "has cause                        197\n",
       "cause of death                   123\n",
       "research intervention             13\n",
       "category combines topics           3\n",
       "different from                     3\n",
       "facet of                           3\n",
       "has effect                         3\n",
       "has immediate cause                2\n",
       "named after                        2\n",
       "category's main topic              1\n",
       "vaccine for                        1\n",
       "Wikimedia portal's main topic      1\n",
       "interested in                      1\n",
       "represents                         1\n",
       "academic degree                    1\n",
       "said to be the same as             1\n",
       "medical condition treated          1\n",
       "item for this sense                1\n",
       "Name: propertyLabel.value, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "truthy['propertyLabel.value'].value_counts()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>itemLabel.value</th>\n",
       "      <th>propertyLabel.value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>444</th>\n",
       "      <td>Recent advances in the detection of respirator...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>The continuing 2019-nCoV epidemic threat of no...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>446</th>\n",
       "      <td>Clinical features of patients infected with 20...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>447</th>\n",
       "      <td>Early Transmission Dynamics in Wuhan, China, o...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>2019-nCoV, first death outside China</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       itemLabel.value propertyLabel.value\n",
       "444  Recent advances in the detection of respirator...        main subject\n",
       "445  The continuing 2019-nCoV epidemic threat of no...        main subject\n",
       "446  Clinical features of patients infected with 20...        main subject\n",
       "447  Early Transmission Dynamics in Wuhan, China, o...        main subject\n",
       "448               2019-nCoV, first death outside China        main subject"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "truthy[truthy['propertyLabel.value'] == 'main subject'][['itemLabel.value','propertyLabel.value']].head(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>itemLabel.value</th>\n",
       "      <th>propertyLabel.value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>2019–20 COVID-19 pandemic</td>\n",
       "      <td>has cause</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>416</th>\n",
       "      <td>2019–20 coronavirus pandemic in mainland China</td>\n",
       "      <td>has cause</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>417</th>\n",
       "      <td>2019–20 coronavirus outbreak in Japan</td>\n",
       "      <td>has cause</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>418</th>\n",
       "      <td>2019–20 COVID-19 outbreak in South Korea</td>\n",
       "      <td>has cause</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>419</th>\n",
       "      <td>2019–20 coronavirus outbreak in Vietnam</td>\n",
       "      <td>has cause</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    itemLabel.value propertyLabel.value\n",
       "306                       2019–20 COVID-19 pandemic           has cause\n",
       "416  2019–20 coronavirus pandemic in mainland China           has cause\n",
       "417           2019–20 coronavirus outbreak in Japan           has cause\n",
       "418        2019–20 COVID-19 outbreak in South Korea           has cause\n",
       "419         2019–20 coronavirus outbreak in Vietnam           has cause"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "truthy[truthy['propertyLabel.value'] == 'has cause'][['itemLabel.value','propertyLabel.value']].head(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>itemLabel.value</th>\n",
       "      <th>propertyLabel.value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>timeline of the 2019–20 coronavirus outbreak</td>\n",
       "      <td>facet of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>SARS-CoV-2 transmission</td>\n",
       "      <td>facet of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1043</th>\n",
       "      <td>Ordinance of January 30, 2020</td>\n",
       "      <td>facet of</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   itemLabel.value propertyLabel.value\n",
       "490   timeline of the 2019–20 coronavirus outbreak            facet of\n",
       "491                        SARS-CoV-2 transmission            facet of\n",
       "1043                 Ordinance of January 30, 2020            facet of"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "truthy[truthy['propertyLabel.value'] == ('facet of' or 'main subject')][['itemLabel.value','propertyLabel.value']].head(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>itemLabel.value</th>\n",
       "      <th>propertyLabel.value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Jackson Browne</td>\n",
       "      <td>medical condition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Greg Rikaart</td>\n",
       "      <td>medical condition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Albin Ekdal</td>\n",
       "      <td>medical condition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Brett Dean</td>\n",
       "      <td>medical condition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Shintaro Fujinami</td>\n",
       "      <td>medical condition</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      itemLabel.value propertyLabel.value\n",
       "16     Jackson Browne   medical condition\n",
       "17       Greg Rikaart   medical condition\n",
       "18        Albin Ekdal   medical condition\n",
       "19         Brett Dean   medical condition\n",
       "20  Shintaro Fujinami   medical condition"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "truthy[truthy['propertyLabel.value'] == 'medical condition'][['itemLabel.value','propertyLabel.value']].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove medical condition and cause of death\n",
    "mainSubject = truthy[truthy['propertyLabel.value'] != 'medical condition']\n",
    "mainSubject = mainSubject[mainSubject['propertyLabel.value'] != 'cause of death']\n",
    "mainSubject = mainSubject[mainSubject['propertyLabel.value'] != 'medical condition treated']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>itemLabel.value</th>\n",
       "      <th>propertyLabel.value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>859</th>\n",
       "      <td>The Cholera Epidemics in Hamburg and What to L...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>Molecular Modeling Evaluation of the Binding A...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>662</th>\n",
       "      <td>Coronavirus Disease 2019 (COVID-19): A critica...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <td>Vitamin C Infusion for the Treatment of Severe...</td>\n",
       "      <td>main subject</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>962</th>\n",
       "      <td>2020 coronavirus pandemic in Benin</td>\n",
       "      <td>has cause</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       itemLabel.value propertyLabel.value\n",
       "859  The Cholera Epidemics in Hamburg and What to L...        main subject\n",
       "450  Molecular Modeling Evaluation of the Binding A...        main subject\n",
       "662  Coronavirus Disease 2019 (COVID-19): A critica...        main subject\n",
       "466  Vitamin C Infusion for the Treatment of Severe...        main subject\n",
       "962                 2020 coronavirus pandemic in Benin           has cause"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mainSubject[['itemLabel.value','propertyLabel.value']].sample(5).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#All truthy statements with 2019–20 COVID-19 pandemic (Q81068910) as value.\n",
    "#https://w.wiki/Kvd (Thanks User:Dipsacus_fullonum)\n",
    "\n",
    "sparql.setQuery(\"\"\"\n",
    "# \n",
    "SELECT ?item ?itemLabel ?property ?propertyLabel WHERE {\n",
    "  ?item ?claim wd:Q81068910. #2019–20 COVID-19 pandemic\n",
    "  ?property wikibase:directClaim ?claim.\n",
    "  SERVICE wikibase:label { bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],en\". }\n",
    "}\n",
    "\"\"\")\n",
    "sparql.setReturnFormat(JSON)\n",
    "results = sparql.query().convert()\n",
    "\n",
    "Q81068910 = pd.io.json.json_normalize(results['results']['bindings'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "part of                          427\n",
       "main subject                     167\n",
       "facet of                          31\n",
       "category combines topics          20\n",
       "has cause                         10\n",
       "significant event                  5\n",
       "has contributing factor            3\n",
       "has effect                         3\n",
       "field of work                      2\n",
       "category contains                  2\n",
       "different from                     2\n",
       "notable works                      1\n",
       "cause of death                     1\n",
       "Wikimedia portal's main topic      1\n",
       "interested in                      1\n",
       "template's main topic              1\n",
       "said to be the same as             1\n",
       "category's main topic              1\n",
       "Name: propertyLabel.value, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q81068910['propertyLabel.value'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>itemLabel.value</th>\n",
       "      <th>propertyLabel.value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019–20 coronavirus pandemic in mainland China</td>\n",
       "      <td>part of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019–20 coronavirus outbreak in Japan</td>\n",
       "      <td>part of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019–20 COVID-19 outbreak in South Korea</td>\n",
       "      <td>part of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019–20 coronavirus outbreak in Vietnam</td>\n",
       "      <td>part of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019–20 coronavirus outbreak in Singapore</td>\n",
       "      <td>part of</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  itemLabel.value propertyLabel.value\n",
       "1  2019–20 coronavirus pandemic in mainland China             part of\n",
       "2           2019–20 coronavirus outbreak in Japan             part of\n",
       "3        2019–20 COVID-19 outbreak in South Korea             part of\n",
       "4         2019–20 coronavirus outbreak in Vietnam             part of\n",
       "5       2019–20 coronavirus outbreak in Singapore             part of"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Q81068910[Q81068910['propertyLabel.value'] == 'part of'][['itemLabel.value','propertyLabel.value']].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#removing not strong connections such.\n",
    "Q81068910Strong = Q81068910[Q81068910['propertyLabel.value'] != 'field of work']\n",
    "Q81068910Strong = Q81068910Strong[Q81068910Strong['propertyLabel.value'] != 'interested in']\n",
    "Q81068910Strong = Q81068910Strong[Q81068910Strong['propertyLabel.value'] != 'notable works']\n",
    "Q81068910Strong = Q81068910Strong[Q81068910Strong['propertyLabel.value'] != 'Wikimedia portal\\'s main topic']\n",
    "Q81068910Strong = Q81068910Strong[Q81068910Strong['propertyLabel.value'] != 'category combines topics']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Getting Qs ids\n",
    "mainSubjectQ = [ link.split('/')[-1] for link in mainSubject['item.value'].tolist()]\n",
    "allStatementsQ = [ link.split('/')[-1] for link in allStatements['item.value'].tolist()]\n",
    "Q81068910StrongQ = [ link.split('/')[-1] for link in Q81068910Strong['item.value'].tolist()]\n",
    "## merging both sets\n",
    "#adding both sets & seeds\n",
    "strongQs = set(mainSubjectQ).union(allStatementsQ).union({'Q84263196','Q83741704'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "with open('strongQsCovid-19_20200325.pickle','wb') as f:\n",
    "    pickle.dump(strongQs,f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#On the strong set\n",
    "import requests\n",
    "sitelinks_base = 'https://www.wikidata.org/w/api.php?action=wbgetentities&format=json&props=sitelinks&ids=' \n",
    "sitelinks = []\n",
    "\n",
    "for Q in strongQs:\n",
    "    url = sitelinks_base + Q\n",
    "    sitelinks.append(requests.get(url=url).json())\n",
    "\n",
    "pagesPerProject = {}\n",
    "for s in sitelinks:\n",
    "    if 'entities' in s:\n",
    "        for k,v in s['entities'].items():\n",
    "            if 'sitelinks' in v:\n",
    "                for wiki,data in v['sitelinks'].items():\n",
    "                    page = data['title']\n",
    "                    project ='%s.wikipedia' % wiki.replace('wiki','')\n",
    "                    pagesPerProject[project] = pagesPerProject.get(project,[])\n",
    "                    pagesPerProject[project].append(page)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('pagesPerProjectStronglyRelated20200325.wikitext','w') as f:\n",
    "    for project, pages in pagesPerProject.items():\n",
    "        projectcode = project.split('.')[0]\n",
    "        f.write('\\n== %s == \\n \\n' % project )\n",
    "        for page in pages:\n",
    "            f.write('* [[%s:%s|%s]]\\n' % (projectcode,page,page)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "with open('pagesPerProjectStronglyRelated20200325.pickle','wb') as f:\n",
    "    pickle.dump(pagesPerProject,f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle \n",
    "from random import randint\n",
    "with open('pagesPerProjectStronglyRelated20200325.pickle','rb') as f:\n",
    "    pagesPerProject = pickle.load(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total number of edits to strongly-related COVID articles globally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Function to count revisions\n",
    "\n",
    "import mwapi\n",
    "\n",
    "def countRevisions(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",
    "    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'], 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",
    "            counter += 1\n",
    "    return counter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "hequote.wikipedia\n",
      "itvoyage.wikipedia\n",
      "ta.wikipedia\n",
      "bn.wikipedia\n",
      "cdo.wikipedia\n",
      "uknews.wikipedia\n",
      "species.wikipedia\n",
      "als.wikipedia\n",
      "nlnews.wikipedia\n",
      "frp.wikipedia\n",
      "jv.wikipedia\n",
      "az.wikipedia\n",
      "as.wikipedia\n",
      "bg.wikipedia\n",
      "fa.wikipedia\n",
      "id.wikipedia\n",
      "bat_smg.wikipedia\n",
      "ti.wikipedia\n",
      "tk.wikipedia\n",
      "ja.wikipedia\n",
      "yo.wikipedia\n",
      "zh_classical.wikipedia\n",
      "tg.wikipedia\n",
      "simple.wikipedia\n",
      "si.wikipedia\n",
      "hi.wikipedia\n",
      "mn.wikipedia\n",
      "ug.wikipedia\n",
      "eo.wikipedia\n",
      "te.wikipedia\n",
      "my.wikipedia\n",
      "bh.wikipedia\n",
      "en.wikipedia\n",
      "mg.wikipedia\n",
      "frnews.wikipedia\n",
      "hesource.wikipedia\n",
      "sq.wikipedia\n",
      "vec.wikipedia\n",
      "de.wikipedia\n",
      "ang.wikipedia\n",
      "mwl.wikipedia\n",
      "af.wikipedia\n",
      "su.wikipedia\n",
      "ensource.wikipedia\n",
      "itquote.wikipedia\n",
      "bs.wikipedia\n",
      "ga.wikipedia\n",
      "ukquote.wikipedia\n",
      "esvoyage.wikipedia\n",
      "mk.wikipedia\n",
      "sl.wikipedia\n",
      "hu.wikipedia\n",
      "sc.wikipedia\n",
      "azb.wikipedia\n",
      "ro.wikipedia\n",
      "nrm.wikipedia\n",
      "crh.wikipedia\n",
      "hyw.wikipedia\n",
      "th.wikipedia\n",
      "enversity.wikipedia\n",
      "et.wikipedia\n",
      "finews.wikipedia\n",
      "ban.wikipedia\n",
      "am.wikipedia\n",
      "ml.wikipedia\n",
      "cv.wikipedia\n",
      "commons.wikipedia\n",
      "enquote.wikipedia\n",
      "konews.wikipedia\n",
      "he.wikipedia\n",
      "ne.wikipedia\n",
      "pa.wikipedia\n",
      "zhsource.wikipedia\n",
      "envoyage.wikipedia\n",
      "frvoyage.wikipedia\n",
      "atj.wikipedia\n",
      "frr.wikipedia\n",
      "nds.wikipedia\n",
      "tet.wikipedia\n",
      "lij.wikipedia\n",
      "zh_min_nan.wikipedia\n",
      "csquote.wikipedia\n",
      "pl.wikipedia\n",
      "ha.wikipedia\n",
      "meta.wikipedia\n",
      "runews.wikipedia\n",
      "be_x_old.wikipedia\n",
      "gl.wikipedia\n",
      "be.wikipedia\n",
      "it.wikipedia\n",
      "tl.wikipedia\n",
      "an.wikipedia\n",
      "sr.wikipedia\n",
      "eonews.wikipedia\n",
      "zh.wikipedia\n",
      "fi.wikipedia\n",
      "no.wikipedia\n",
      "dty.wikipedia\n",
      "esnews.wikipedia\n",
      "wuu.wikipedia\n",
      "tr.wikipedia\n",
      "or.wikipedia\n",
      "km.wikipedia\n",
      "ky.wikipedia\n",
      "da.wikipedia\n",
      "oc.wikipedia\n",
      "ht.wikipedia\n",
      "ka.wikipedia\n",
      "ennews.wikipedia\n",
      "vi.wikipedia\n",
      "zh_yue.wikipedia\n",
      "kk.wikipedia\n",
      "fr.wikipedia\n",
      "ms.wikipedia\n",
      "eml.wikipedia\n",
      "is.wikipedia\n",
      "el.wikipedia\n",
      "esversity.wikipedia\n",
      "hy.wikipedia\n",
      "frquote.wikipedia\n",
      "sco.wikipedia\n",
      "diq.wikipedia\n",
      "zhnews.wikipedia\n",
      "nn.wikipedia\n",
      "uk.wikipedia\n",
      "sw.wikipedia\n",
      "br.wikipedia\n",
      "es.wikipedia\n",
      "data.wikipedia\n",
      "pt.wikipedia\n",
      "hak.wikipedia\n",
      "ca.wikipedia\n",
      "qu.wikipedia\n",
      "kn.wikipedia\n",
      "sd.wikipedia\n",
      "lmo.wikipedia\n",
      "arz.wikipedia\n",
      "dv.wikipedia\n",
      "plnews.wikipedia\n",
      "hevoyage.wikipedia\n",
      "lv.wikipedia\n",
      "cy.wikipedia\n",
      "bcl.wikipedia\n",
      "sk.wikipedia\n",
      "bo.wikipedia\n",
      "se.wikipedia\n",
      "kab.wikipedia\n",
      "pap.wikipedia\n",
      "min.wikipedia\n",
      "ko.wikipedia\n",
      "sv.wikipedia\n",
      "ckb.wikipedia\n",
      "sat.wikipedia\n",
      "lt.wikipedia\n",
      "nl.wikipedia\n",
      "li.wikipedia\n",
      "ur.wikipedia\n",
      "zhvoyage.wikipedia\n",
      "lb.wikipedia\n",
      "ptnews.wikipedia\n",
      "deversity.wikipedia\n",
      "sah.wikipedia\n",
      "uz.wikipedia\n",
      "gu.wikipedia\n",
      "la.wikipedia\n",
      "din.wikipedia\n",
      "ast.wikipedia\n",
      "ku.wikipedia\n",
      "eu.wikipedia\n",
      "mr.wikipedia\n",
      "itversity.wikipedia\n",
      "ceb.wikipedia\n",
      "hr.wikipedia\n",
      "wa.wikipedia\n",
      "mnw.wikipedia\n",
      "war.wikipedia\n",
      "ru.wikipedia\n",
      "ia.wikipedia\n",
      "szl.wikipedia\n",
      "ar.wikipedia\n",
      "sh.wikipedia\n",
      "rue.wikipedia\n",
      "xmf.wikipedia\n",
      "cs.wikipedia\n"
     ]
    }
   ],
   "source": [
    "results = []\n",
    "startDate = '2020-01-01T00:00:00Z'\n",
    "for project, pages in pagesPerProject.items():\n",
    "    print(project)\n",
    "    for page in pages:\n",
    "        try:\n",
    "            c = countRevisions(page,project,startDate)\n",
    "            results.append([project,page,c])\n",
    "        except:\n",
    "            #print('error in %s %s' % (page,project))\n",
    "            pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.DataFrame(results)\n",
    "df.rename(columns={0:'project',1:'article',2:'edits'},inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Statistics from Jan 1st to March 30th, 2020\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of projects 146\n",
      "Total number of Articles 2558\n",
      "Total number of edits 299342\n",
      "Avg Edits per Day 3838.0\n",
      "Avg Edits per hour 160.0\n"
     ]
    }
   ],
   "source": [
    "print('Total number of projects %s' % len(df.project.unique()))\n",
    "print('Total number of Articles %s' % df.shape[0])\n",
    "print('Total number of edits %s' % df.edits.sum())\n",
    "avgPerDay = round(df.edits.sum()/(31+28+19))\n",
    "print('Avg Edits per Day %s' % avgPerDay )\n",
    "avgPerHour = round(df.edits.sum()/(24*(31+28+19)))\n",
    "print('Avg Edits per hour %s' % avgPerHour )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#there are some errors/warnings. Don't worry, we are getting a lower-bound\n",
    "results = []\n",
    "startDate = '2020-03-01T00:00:00Z'\n",
    "for project, pages in pagesPerProject.items():\n",
    "    print(project)\n",
    "    for page in pages:\n",
    "        try:\n",
    "            c = countRevisions(page,project,startDate)\n",
    "            results.append([project,page,c])\n",
    "        except:\n",
    "            #print('error in %s %s' % (page,project))\n",
    "            pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>translation_source_language</th>\n",
       "      <th>translation_target_language</th>\n",
       "      <th>translation_source_title</th>\n",
       "      <th>frequency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>en</td>\n",
       "      <td>am</td>\n",
       "      <td>Coronavirus</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>en</td>\n",
       "      <td>bg</td>\n",
       "      <td>Novel coronavirus (2019-nCoV)</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>en</td>\n",
       "      <td>gu</td>\n",
       "      <td>Coronavirus disease 2019</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>en</td>\n",
       "      <td>fr</td>\n",
       "      <td>COVID-19 vaccine</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>en</td>\n",
       "      <td>ca</td>\n",
       "      <td>Betacoronavirus</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>444</th>\n",
       "      <td>en</td>\n",
       "      <td>es</td>\n",
       "      <td>2020 coronavirus pandemic in Japan</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>en</td>\n",
       "      <td>fa</td>\n",
       "      <td>2020 coronavirus outbreak in France</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>446</th>\n",
       "      <td>en</td>\n",
       "      <td>ar</td>\n",
       "      <td>2020 coronavirus pandemic in Nevada</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>447</th>\n",
       "      <td>en</td>\n",
       "      <td>ro</td>\n",
       "      <td>Murine coronavirus</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>es</td>\n",
       "      <td>ca</td>\n",
       "      <td>Corona egipcia</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>449 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    translation_source_language translation_target_language  \\\n",
       "0                            en                          am   \n",
       "1                            en                          bg   \n",
       "2                            en                          gu   \n",
       "3                            en                          fr   \n",
       "4                            en                          ca   \n",
       "..                          ...                         ...   \n",
       "444                          en                          es   \n",
       "445                          en                          fa   \n",
       "446                          en                          ar   \n",
       "447                          en                          ro   \n",
       "448                          es                          ca   \n",
       "\n",
       "                translation_source_title  frequency  \n",
       "0                            Coronavirus         17  \n",
       "1          Novel coronavirus (2019-nCoV)         13  \n",
       "2               Coronavirus disease 2019         10  \n",
       "3                       COVID-19 vaccine         10  \n",
       "4                        Betacoronavirus          6  \n",
       "..                                   ...        ...  \n",
       "444   2020 coronavirus pandemic in Japan          1  \n",
       "445  2020 coronavirus outbreak in France          1  \n",
       "446  2020 coronavirus pandemic in Nevada          1  \n",
       "447                   Murine coronavirus          1  \n",
       "448                       Corona egipcia          1  \n",
       "\n",
       "[449 rows x 4 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import wmfdata as wmf\n",
    "wmf.mariadb.run(\"\"\"\n",
    "select distinct translation_source_language,translation_target_language, \n",
    "translation_source_title, count(*) as frequency from cx_translations \n",
    "where ( translation_source_title LIKE '%orona%' \n",
    "OR translation_source_title LIKE '%COVID%' ) AND translation_target_url is not NULL   \n",
    "group by translation_source_title order by frequency desc\n",
    "\"\"\", \"wikishared\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>Field</th>\n",
       "      <th>Type</th>\n",
       "      <th>Null</th>\n",
       "      <th>Key</th>\n",
       "      <th>Default</th>\n",
       "      <th>Extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>translation_id</td>\n",
       "      <td>int(11)</td>\n",
       "      <td>NO</td>\n",
       "      <td>PRI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>auto_increment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>translation_source_title</td>\n",
       "      <td>varbinary(512)</td>\n",
       "      <td>NO</td>\n",
       "      <td>MUL</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>translation_target_title</td>\n",
       "      <td>varbinary(512)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>translation_source_language</td>\n",
       "      <td>varbinary(36)</td>\n",
       "      <td>NO</td>\n",
       "      <td>MUL</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>translation_target_language</td>\n",
       "      <td>varbinary(36)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>translation_source_url</td>\n",
       "      <td>blob</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>translation_target_url</td>\n",
       "      <td>blob</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>translation_status</td>\n",
       "      <td>enum('draft','published','deleted')</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>translation_start_timestamp</td>\n",
       "      <td>varbinary(14)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>translation_last_updated_timestamp</td>\n",
       "      <td>varbinary(14)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>translation_progress</td>\n",
       "      <td>tinyblob</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>translation_started_by</td>\n",
       "      <td>int(11)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>translation_last_update_by</td>\n",
       "      <td>int(11)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>translation_source_revision_id</td>\n",
       "      <td>int(10) unsigned</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>translation_target_revision_id</td>\n",
       "      <td>int(10) unsigned</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>translation_cx_version</td>\n",
       "      <td>tinyint(3) unsigned</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Field                                 Type  \\\n",
       "0                       translation_id                              int(11)   \n",
       "1             translation_source_title                       varbinary(512)   \n",
       "2             translation_target_title                       varbinary(512)   \n",
       "3          translation_source_language                        varbinary(36)   \n",
       "4          translation_target_language                        varbinary(36)   \n",
       "5               translation_source_url                                 blob   \n",
       "6               translation_target_url                                 blob   \n",
       "7                   translation_status  enum('draft','published','deleted')   \n",
       "8          translation_start_timestamp                        varbinary(14)   \n",
       "9   translation_last_updated_timestamp                        varbinary(14)   \n",
       "10                translation_progress                             tinyblob   \n",
       "11              translation_started_by                              int(11)   \n",
       "12          translation_last_update_by                              int(11)   \n",
       "13      translation_source_revision_id                     int(10) unsigned   \n",
       "14      translation_target_revision_id                     int(10) unsigned   \n",
       "15              translation_cx_version                  tinyint(3) unsigned   \n",
       "\n",
       "   Null  Key  Default           Extra  \n",
       "0    NO  PRI      NaN  auto_increment  \n",
       "1    NO  MUL      NaN                  \n",
       "2    NO           NaN                  \n",
       "3    NO  MUL      NaN                  \n",
       "4    NO           NaN                  \n",
       "5    NO           NaN                  \n",
       "6   YES           NaN                  \n",
       "7   YES           NaN                  \n",
       "8    NO           NaN                  \n",
       "9    NO           NaN                  \n",
       "10   NO           NaN                  \n",
       "11  YES           NaN                  \n",
       "12  YES           NaN                  \n",
       "13  YES           NaN                  \n",
       "14  YES           NaN                  \n",
       "15  YES           1.0                  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wmf.mariadb.run(\"\"\"\n",
    "describe cx_translations\n",
    "\"\"\", \"wikishared\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>translation_source_language</th>\n",
       "      <th>translation_target_language</th>\n",
       "      <th>translation_source_title</th>\n",
       "      <th>frequency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>es</td>\n",
       "      <td>pt</td>\n",
       "      <td>Palak paneer</td>\n",
       "      <td>560643</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  translation_source_language translation_target_language  \\\n",
       "0                          es                          pt   \n",
       "\n",
       "  translation_source_title  frequency  \n",
       "0             Palak paneer     560643  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wmf.mariadb.run(\"\"\"\n",
    "select distinct translation_source_language,translation_target_language, \n",
    "translation_source_title, count(*) as frequency \n",
    "from cx_translations \n",
    "where translation_status='published'\n",
    "order by frequency desc\n",
    "limit 100\"\"\",\"wikishared\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}