cc-index-table/correlation-language-charset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Correlation Metrics Between Charset and Content Language of Web Pages\n",
    "\n",
    "The correlation metrics are based on data from the [Common Crawl September 2019 data set](https://commoncrawl.org/2019/09/september-2019-crawl-archive-now-available/). The metrics are mined on the [columnar index](https://commoncrawl.org/2018/03/index-to-warc-files-and-urls-in-columnar-format/) by the SQL query [correlation-language-charset.sql](https://github.com/commoncrawl/cc-index-table/blob/master/src/sql/examples/cc-index/correlation-language-charset.sql) executed on [AWS Athena](https://aws.amazon.com/athena/). The Athena results are kept in a CSV file ([cc-index-table/data/cc-main-2020-05-language-charset-correlation.csv](./data/cc-main-2020-05-language-charset-correlation.csv)) from where they are further analyzed.\n",
    "\n",
    "Some background information: content language and charset are detected since August 2018 ([CC-MAIN-2018-34](https://commoncrawl.org/2018/08/august-2018-crawl-archive-now-available/)). The detection is performed by [CLD2](https://github.com/CLD2Owners/cld2) resp. [Apache Tika](https://tika.apache.org/). Both use heuristics based on character and n-gram frequencies supported by metadata from the HTML and HTTP headers. Since September 2018 ([CC-MAIN-2018-39](https://commoncrawl.org/2018/10/september-2018-crawl-archive-now-available/)) the detected information is available as columns `content_languages` and `content_charset`. The former may include multiple values separated by a comma, simply because a web page can be written in multiple languages, eg. one part in Japanese other(s) in English. CLD2 is able to recognize up to three languages per web page. Also important to know: language and charset detection may be wrong - there is a (low) error rate in the statistical methods and also the input data can be misleading (erroneous metadata or garbled content).\n",
    "\n",
    "But let's now put the SQL query together... You'll get the complete query from here: [correlation-language-charset.sql](https://github.com/commoncrawl/cc-index-table/blob/master/src/sql/examples/cc-index/correlation-language-charset.sql), so I'll explain the steps here in more detail. For better readability the query is split into one part creating a SQL view which is again subdivided by defining a subquery \"tmp\". The three essential steps are:\n",
    "\n",
    "1. get the page counts for pairs of language and charset, as well as the overal number of pages per language, charset and in total:\n",
    "```sql\n",
    "SELECT COUNT(*) as n_pages,\n",
    "       content_languages AS languages,\n",
    "       content_charset AS charset,\n",
    "       SUM(COUNT(*)) OVER() AS total_pages,\n",
    "       SUM(COUNT(*)) OVER(PARTITION BY content_charset) AS n_pages_charset,\n",
    "       SUM(COUNT(*)) OVER(PARTITION BY content_languages) as n_pages_languages\n",
    "FROM \"ccindex\".\"ccindex\"\n",
    "WHERE crawl = 'CC-MAIN-2020-05'\n",
    "  AND subset = 'warc'\n",
    "GROUP BY content_charset,\n",
    "         content_languages\n",
    "```\n",
    "   The [SQL window functions](https://prestodb.io/docs/current/functions/window.html) make it easy to get the overall counts.  We filter by crawl and use only the \"warc\" subset to count only successfully fetched pages.\n",
    "\n",
    "2. next, based on the counts from step 1, we calculate the correlation metrics the log-likelihood and the conditional probabilities:\n",
    "```sql\n",
    "SELECT ...,\n",
    "       ... AS loglikelihood,\n",
    "       (n_pages/CAST(n_pages_languages AS DOUBLE)) AS prob_cs_given_lang,\n",
    "       (n_pages/CAST(n_pages_charset AS DOUBLE)) AS prob_lang_given_cs\n",
    "```\n",
    "   The calculation of the log-likelihood is left away for now, we'll discuss it later.\n",
    "\n",
    "3. finally, to reduce the noise we filter away pages with multiple languages and rare (and maybe accidential) combinations of language and charset showing up less than 100 times:\n",
    "```sql\n",
    "WHERE\n",
    "  -- skip low-frequency pairs of language and charset:\n",
    "      n_pages >= 100\n",
    "  -- skip pages with more than one language:\n",
    "      AND NOT languages LIKE '%,%'\n",
    "```\n",
    "\n",
    "The query is executed by Athena for less than \\\\$.01 (given a price of \\\\$5.00 for 1 TB scanned):\n",
    "```\n",
    "(Run time: 19.12 seconds, Data scanned: 613.44 MB)\n",
    "```\n",
    "The resulting CSV file is also provided here as [cc-index-table/data/cc-main-2020-05-language-charset-correlation.csv](./data/cc-main-2020-05-language-charset-correlation.csv). We'll now take a look the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>n_pages_charset</th>\n",
       "      <th>%</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>charset</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>UTF-8</th>\n",
       "      <td>437142931590</td>\n",
       "      <td>96.89321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO-8859-1</th>\n",
       "      <td>10083073661</td>\n",
       "      <td>2.23492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>windows-1251</th>\n",
       "      <td>1344152824</td>\n",
       "      <td>0.29793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>windows-1252</th>\n",
       "      <td>1147056008</td>\n",
       "      <td>0.25425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB2312</th>\n",
       "      <td>449164209</td>\n",
       "      <td>0.09956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUC-JP</th>\n",
       "      <td>173648376</td>\n",
       "      <td>0.03849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shift_JIS</th>\n",
       "      <td>144895231</td>\n",
       "      <td>0.03212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO-8859-2</th>\n",
       "      <td>139068840</td>\n",
       "      <td>0.03082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBK</th>\n",
       "      <td>105909020</td>\n",
       "      <td>0.02347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO-8859-15</th>\n",
       "      <td>102893508</td>\n",
       "      <td>0.02281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUC-KR</th>\n",
       "      <td>90090000</td>\n",
       "      <td>0.01997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>windows-1250</th>\n",
       "      <td>87456201</td>\n",
       "      <td>0.01938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GB18030</th>\n",
       "      <td>52079296</td>\n",
       "      <td>0.01154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Big5</th>\n",
       "      <td>21677293</td>\n",
       "      <td>0.00480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>windows-1256</th>\n",
       "      <td>19274311</td>\n",
       "      <td>0.00427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US-ASCII</th>\n",
       "      <td>18349760</td>\n",
       "      <td>0.00407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO-8859-9</th>\n",
       "      <td>15470741</td>\n",
       "      <td>0.00343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>windows-1254</th>\n",
       "      <td>8384430</td>\n",
       "      <td>0.00186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>windows-1255</th>\n",
       "      <td>4441722</td>\n",
       "      <td>0.00098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>windows-1257</th>\n",
       "      <td>1751412</td>\n",
       "      <td>0.00039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO-8859-7</th>\n",
       "      <td>1272867</td>\n",
       "      <td>0.00028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>x-windows-874</th>\n",
       "      <td>1039598</td>\n",
       "      <td>0.00023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TIS-620</th>\n",
       "      <td>956980</td>\n",
       "      <td>0.00021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KOI8-R</th>\n",
       "      <td>916131</td>\n",
       "      <td>0.00020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>windows-1253</th>\n",
       "      <td>772184</td>\n",
       "      <td>0.00017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               n_pages_charset        %\n",
       "charset                                \n",
       "UTF-8             437142931590 96.89321\n",
       "ISO-8859-1         10083073661  2.23492\n",
       "windows-1251        1344152824  0.29793\n",
       "windows-1252        1147056008  0.25425\n",
       "GB2312               449164209  0.09956\n",
       "EUC-JP               173648376  0.03849\n",
       "Shift_JIS            144895231  0.03212\n",
       "ISO-8859-2           139068840  0.03082\n",
       "GBK                  105909020  0.02347\n",
       "ISO-8859-15          102893508  0.02281\n",
       "EUC-KR                90090000  0.01997\n",
       "windows-1250          87456201  0.01938\n",
       "GB18030               52079296  0.01154\n",
       "Big5                  21677293  0.00480\n",
       "windows-1256          19274311  0.00427\n",
       "US-ASCII              18349760  0.00407\n",
       "ISO-8859-9            15470741  0.00343\n",
       "windows-1254           8384430  0.00186\n",
       "windows-1255           4441722  0.00098\n",
       "windows-1257           1751412  0.00039\n",
       "ISO-8859-7             1272867  0.00028\n",
       "x-windows-874          1039598  0.00023\n",
       "TIS-620                 956980  0.00021\n",
       "KOI8-R                  916131  0.00020\n",
       "windows-1253            772184  0.00017"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.options.display.float_format = '{:,.5f}'.format\n",
    "\n",
    "df = pd.read_csv('data/cc-main-2020-05-language-charset-correlation.csv')\n",
    "df_ = df.loc[:, ['charset', 'n_pages_charset']]\n",
    "df_['%'] = 100.0*df_['n_pages_charset']/df_['n_pages_charset'].sum()\n",
    "df_.groupby(['charset']).aggregate(sum).sort_values(['n_pages_charset'], ascending=0).head(25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "UTF-8 is clearly the most frequently used encoding used by almost 97% of the web pages.\n",
    "\n",
    "But now let's look how charsets are used to write a single language. We take Japanese because it cannot (or hardly) be written using the Latin alphabet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>languages</th>\n",
       "      <th>charset</th>\n",
       "      <th>n_pages_languages</th>\n",
       "      <th>n_pages_charset</th>\n",
       "      <th>n_pages</th>\n",
       "      <th>loglikelihood</th>\n",
       "      <th>prob_cs_given_lang</th>\n",
       "      <th>prob_lang_given_cs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>jpn</td>\n",
       "      <td>UTF-8</td>\n",
       "      <td>64194459</td>\n",
       "      <td>2820276978</td>\n",
       "      <td>54506179</td>\n",
       "      <td>0.85865</td>\n",
       "      <td>0.84908</td>\n",
       "      <td>0.01933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>jpn</td>\n",
       "      <td>Shift_JIS</td>\n",
       "      <td>64194459</td>\n",
       "      <td>11145787</td>\n",
       "      <td>6517226</td>\n",
       "      <td>4.76341</td>\n",
       "      <td>0.10152</td>\n",
       "      <td>0.58473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>jpn</td>\n",
       "      <td>EUC-JP</td>\n",
       "      <td>64194459</td>\n",
       "      <td>7893108</td>\n",
       "      <td>3010223</td>\n",
       "      <td>4.81949</td>\n",
       "      <td>0.04689</td>\n",
       "      <td>0.38137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>jpn</td>\n",
       "      <td>GBK</td>\n",
       "      <td>64194459</td>\n",
       "      <td>15129860</td>\n",
       "      <td>100009</td>\n",
       "      <td>-2.24805</td>\n",
       "      <td>0.00156</td>\n",
       "      <td>0.00661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>jpn</td>\n",
       "      <td>windows-31j</td>\n",
       "      <td>64194459</td>\n",
       "      <td>57207</td>\n",
       "      <td>32458</td>\n",
       "      <td>4.98683</td>\n",
       "      <td>0.00051</td>\n",
       "      <td>0.56738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>jpn</td>\n",
       "      <td>GB18030</td>\n",
       "      <td>64194459</td>\n",
       "      <td>3063488</td>\n",
       "      <td>12873</td>\n",
       "      <td>-3.15421</td>\n",
       "      <td>0.00020</td>\n",
       "      <td>0.00420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>jpn</td>\n",
       "      <td>ISO-2022-JP</td>\n",
       "      <td>64194459</td>\n",
       "      <td>23204</td>\n",
       "      <td>6222</td>\n",
       "      <td>4.54004</td>\n",
       "      <td>0.00010</td>\n",
       "      <td>0.26814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>jpn</td>\n",
       "      <td>UTF-16BE</td>\n",
       "      <td>64194459</td>\n",
       "      <td>4458</td>\n",
       "      <td>2860</td>\n",
       "      <td>4.85729</td>\n",
       "      <td>0.00004</td>\n",
       "      <td>0.64154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>jpn</td>\n",
       "      <td>ISO-8859-1</td>\n",
       "      <td>64194459</td>\n",
       "      <td>103949213</td>\n",
       "      <td>2354</td>\n",
       "      <td>-13.52745</td>\n",
       "      <td>0.00004</td>\n",
       "      <td>0.00002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>332</th>\n",
       "      <td>jpn</td>\n",
       "      <td>UTF-16LE</td>\n",
       "      <td>64194459</td>\n",
       "      <td>29580</td>\n",
       "      <td>1704</td>\n",
       "      <td>1.97009</td>\n",
       "      <td>0.00003</td>\n",
       "      <td>0.05761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>333</th>\n",
       "      <td>jpn</td>\n",
       "      <td>GB2312</td>\n",
       "      <td>64194459</td>\n",
       "      <td>34551093</td>\n",
       "      <td>1337</td>\n",
       "      <td>-12.50069</td>\n",
       "      <td>0.00002</td>\n",
       "      <td>0.00004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>334</th>\n",
       "      <td>jpn</td>\n",
       "      <td>windows-1252</td>\n",
       "      <td>64194459</td>\n",
       "      <td>15713096</td>\n",
       "      <td>519</td>\n",
       "      <td>-12.82932</td>\n",
       "      <td>0.00001</td>\n",
       "      <td>0.00003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>jpn</td>\n",
       "      <td>EUC-KR</td>\n",
       "      <td>64194459</td>\n",
       "      <td>4504500</td>\n",
       "      <td>156</td>\n",
       "      <td>-12.74167</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>jpn</td>\n",
       "      <td>Big5</td>\n",
       "      <td>64194459</td>\n",
       "      <td>1970663</td>\n",
       "      <td>152</td>\n",
       "      <td>-11.14189</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    languages       charset  n_pages_languages  n_pages_charset   n_pages  \\\n",
       "323       jpn         UTF-8           64194459       2820276978  54506179   \n",
       "324       jpn     Shift_JIS           64194459         11145787   6517226   \n",
       "325       jpn        EUC-JP           64194459          7893108   3010223   \n",
       "326       jpn           GBK           64194459         15129860    100009   \n",
       "327       jpn   windows-31j           64194459            57207     32458   \n",
       "328       jpn       GB18030           64194459          3063488     12873   \n",
       "329       jpn   ISO-2022-JP           64194459            23204      6222   \n",
       "330       jpn      UTF-16BE           64194459             4458      2860   \n",
       "331       jpn    ISO-8859-1           64194459        103949213      2354   \n",
       "332       jpn      UTF-16LE           64194459            29580      1704   \n",
       "333       jpn        GB2312           64194459         34551093      1337   \n",
       "334       jpn  windows-1252           64194459         15713096       519   \n",
       "335       jpn        EUC-KR           64194459          4504500       156   \n",
       "336       jpn          Big5           64194459          1970663       152   \n",
       "\n",
       "     loglikelihood  prob_cs_given_lang  prob_lang_given_cs  \n",
       "323        0.85865             0.84908             0.01933  \n",
       "324        4.76341             0.10152             0.58473  \n",
       "325        4.81949             0.04689             0.38137  \n",
       "326       -2.24805             0.00156             0.00661  \n",
       "327        4.98683             0.00051             0.56738  \n",
       "328       -3.15421             0.00020             0.00420  \n",
       "329        4.54004             0.00010             0.26814  \n",
       "330        4.85729             0.00004             0.64154  \n",
       "331      -13.52745             0.00004             0.00002  \n",
       "332        1.97009             0.00003             0.05761  \n",
       "333      -12.50069             0.00002             0.00004  \n",
       "334      -12.82932             0.00001             0.00003  \n",
       "335      -12.74167             0.00000             0.00003  \n",
       "336      -11.14189             0.00000             0.00008  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['languages'] == 'jpn'].sort_values(['prob_cs_given_lang'], ascending=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Still, UTF-8 is used most frequently, but for significant lower proportion of web pages (85%). However, there are a couple of other character sets, likely you never heard about. We now re-sort the table by log-likelihood:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>languages</th>\n",
       "      <th>charset</th>\n",
       "      <th>n_pages_languages</th>\n",
       "      <th>n_pages_charset</th>\n",
       "      <th>n_pages</th>\n",
       "      <th>loglikelihood</th>\n",
       "      <th>prob_cs_given_lang</th>\n",
       "      <th>prob_lang_given_cs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>jpn</td>\n",
       "      <td>windows-31j</td>\n",
       "      <td>64194459</td>\n",
       "      <td>57207</td>\n",
       "      <td>32458</td>\n",
       "      <td>4.98683</td>\n",
       "      <td>0.00051</td>\n",
       "      <td>0.56738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>jpn</td>\n",
       "      <td>UTF-16BE</td>\n",
       "      <td>64194459</td>\n",
       "      <td>4458</td>\n",
       "      <td>2860</td>\n",
       "      <td>4.85729</td>\n",
       "      <td>0.00004</td>\n",
       "      <td>0.64154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>jpn</td>\n",
       "      <td>EUC-JP</td>\n",
       "      <td>64194459</td>\n",
       "      <td>7893108</td>\n",
       "      <td>3010223</td>\n",
       "      <td>4.81949</td>\n",
       "      <td>0.04689</td>\n",
       "      <td>0.38137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>jpn</td>\n",
       "      <td>Shift_JIS</td>\n",
       "      <td>64194459</td>\n",
       "      <td>11145787</td>\n",
       "      <td>6517226</td>\n",
       "      <td>4.76341</td>\n",
       "      <td>0.10152</td>\n",
       "      <td>0.58473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>jpn</td>\n",
       "      <td>ISO-2022-JP</td>\n",
       "      <td>64194459</td>\n",
       "      <td>23204</td>\n",
       "      <td>6222</td>\n",
       "      <td>4.54004</td>\n",
       "      <td>0.00010</td>\n",
       "      <td>0.26814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>332</th>\n",
       "      <td>jpn</td>\n",
       "      <td>UTF-16LE</td>\n",
       "      <td>64194459</td>\n",
       "      <td>29580</td>\n",
       "      <td>1704</td>\n",
       "      <td>1.97009</td>\n",
       "      <td>0.00003</td>\n",
       "      <td>0.05761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>jpn</td>\n",
       "      <td>UTF-8</td>\n",
       "      <td>64194459</td>\n",
       "      <td>2820276978</td>\n",
       "      <td>54506179</td>\n",
       "      <td>0.85865</td>\n",
       "      <td>0.84908</td>\n",
       "      <td>0.01933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>326</th>\n",
       "      <td>jpn</td>\n",
       "      <td>GBK</td>\n",
       "      <td>64194459</td>\n",
       "      <td>15129860</td>\n",
       "      <td>100009</td>\n",
       "      <td>-2.24805</td>\n",
       "      <td>0.00156</td>\n",
       "      <td>0.00661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>jpn</td>\n",
       "      <td>GB18030</td>\n",
       "      <td>64194459</td>\n",
       "      <td>3063488</td>\n",
       "      <td>12873</td>\n",
       "      <td>-3.15421</td>\n",
       "      <td>0.00020</td>\n",
       "      <td>0.00420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>jpn</td>\n",
       "      <td>Big5</td>\n",
       "      <td>64194459</td>\n",
       "      <td>1970663</td>\n",
       "      <td>152</td>\n",
       "      <td>-11.14189</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>333</th>\n",
       "      <td>jpn</td>\n",
       "      <td>GB2312</td>\n",
       "      <td>64194459</td>\n",
       "      <td>34551093</td>\n",
       "      <td>1337</td>\n",
       "      <td>-12.50069</td>\n",
       "      <td>0.00002</td>\n",
       "      <td>0.00004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>jpn</td>\n",
       "      <td>EUC-KR</td>\n",
       "      <td>64194459</td>\n",
       "      <td>4504500</td>\n",
       "      <td>156</td>\n",
       "      <td>-12.74167</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>334</th>\n",
       "      <td>jpn</td>\n",
       "      <td>windows-1252</td>\n",
       "      <td>64194459</td>\n",
       "      <td>15713096</td>\n",
       "      <td>519</td>\n",
       "      <td>-12.82932</td>\n",
       "      <td>0.00001</td>\n",
       "      <td>0.00003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>jpn</td>\n",
       "      <td>ISO-8859-1</td>\n",
       "      <td>64194459</td>\n",
       "      <td>103949213</td>\n",
       "      <td>2354</td>\n",
       "      <td>-13.52745</td>\n",
       "      <td>0.00004</td>\n",
       "      <td>0.00002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    languages       charset  n_pages_languages  n_pages_charset   n_pages  \\\n",
       "327       jpn   windows-31j           64194459            57207     32458   \n",
       "330       jpn      UTF-16BE           64194459             4458      2860   \n",
       "325       jpn        EUC-JP           64194459          7893108   3010223   \n",
       "324       jpn     Shift_JIS           64194459         11145787   6517226   \n",
       "329       jpn   ISO-2022-JP           64194459            23204      6222   \n",
       "332       jpn      UTF-16LE           64194459            29580      1704   \n",
       "323       jpn         UTF-8           64194459       2820276978  54506179   \n",
       "326       jpn           GBK           64194459         15129860    100009   \n",
       "328       jpn       GB18030           64194459          3063488     12873   \n",
       "336       jpn          Big5           64194459          1970663       152   \n",
       "333       jpn        GB2312           64194459         34551093      1337   \n",
       "335       jpn        EUC-KR           64194459          4504500       156   \n",
       "334       jpn  windows-1252           64194459         15713096       519   \n",
       "331       jpn    ISO-8859-1           64194459        103949213      2354   \n",
       "\n",
       "     loglikelihood  prob_cs_given_lang  prob_lang_given_cs  \n",
       "327        4.98683             0.00051             0.56738  \n",
       "330        4.85729             0.00004             0.64154  \n",
       "325        4.81949             0.04689             0.38137  \n",
       "324        4.76341             0.10152             0.58473  \n",
       "329        4.54004             0.00010             0.26814  \n",
       "332        1.97009             0.00003             0.05761  \n",
       "323        0.85865             0.84908             0.01933  \n",
       "326       -2.24805             0.00156             0.00661  \n",
       "328       -3.15421             0.00020             0.00420  \n",
       "336      -11.14189             0.00000             0.00008  \n",
       "333      -12.50069             0.00002             0.00004  \n",
       "335      -12.74167             0.00000             0.00003  \n",
       "334      -12.82932             0.00001             0.00003  \n",
       "331      -13.52745             0.00004             0.00002  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['languages'] == 'jpn'].sort_values(['loglikelihood'], ascending=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The log-likelihood ratio test is a metric for the correlation of two variables, surely more robust than the conditional probability. That becomes immediately evident when comparing the column \"loglikelihood\" and \"prob_lang_given_cs\": the top charset by log-likelihood is [windows-31j](https://en.wikipedia.org/wiki/Code_page_932_(Microsoft_Windows)) and [UTF-16BE](https://en.wikipedia.org/wiki/UTF-16#UTF-16BE) has the highest conditional probability. While the former is indeed a character set designed to encode Japanese content, the latter is a universal charset and it might be that its coocurrence with Japanese is purely accidental.\n",
    "\n",
    "The calculation of the log-likelihood a little bit more complex, esp. if you need to avoid query failures due to integer overflows or taking $log(0)$, see [correlation-language-charset.sql](https://github.com/commoncrawl/cc-index-table/blob/master/src/sql/examples/cc-index/correlation-language-charset.sql). I recommend to read [Ted Dunning's article about association metrics of collocations](https://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.14.5962) to understand calculation and usage of the log-likelihood ratio test."
   ]
  },
  {
   "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.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}