data-collection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "738dbb66-7bfe-43ed-9d1f-967ac60a5075",
   "metadata": {},
   "source": [
    "# Collecting data\n",
    "\n",
    "This notebook detail the process of assembling the dataset written to `wordle.csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8f41dbac-7060-4d8d-97f7-16393d8b63d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests, re\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from scipy import stats\n",
    "from base64 import b64decode, b64encode\n",
    "from time import sleep\n",
    "from pathlib import Path\n",
    "from IPython.display import display, clear_output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86f913e2-d7e7-434c-bffa-bae0162c64a0",
   "metadata": {},
   "source": [
    "## Getting Wordle words\n",
    "\n",
    "First, I import Wordle's answers and word list from its source code as two lists: `answers` and `wordlist`. The source code is in JavaScript, but it can be parsed into a String then a List."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e07fba48-3a55-40d3-8d42-cf7cdf3e1972",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "wordlist:\t10657\n",
      "answers:\t2315\n",
      "total:\t\t12972\n"
     ]
    }
   ],
   "source": [
    "# Parse a JavaScript Array (as a String) into a Python List\n",
    "array2List = lambda arr : re.sub(r'\\\"|\\s', '', arr[1:-1]).split(\",\")\n",
    "\n",
    "answers = None\n",
    "wordlist = None\n",
    "with open(\"wordle.js\") as f:\n",
    "    txt = f.read().split(\"\\n\")\n",
    "    answers = array2List(txt[1117][13:])\n",
    "    wordlist = array2List(txt[1118][13:])\n",
    "\n",
    "sizes = {'wordlist': len(wordlist), 'answers': len(answers) }\n",
    "print(\"wordlist:\\t{}\\nanswers:\\t{}\\ntotal:\\t\\t{}\".format(sizes['wordlist'], sizes['answers'], sizes['wordlist'] + sizes['answers']))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e45a976b-def1-42cb-b24b-dd1cb893dd4f",
   "metadata": {},
   "source": [
    "Test for overlap between the set of words, `wordlist`, and the set of answers `answers`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "56db3489-dca5-4abf-9125-347330b97d4c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(wordlist).intersection(set(answers)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a5dbfa0-d281-4153-8d0f-94f84f73bca4",
   "metadata": {
    "tags": []
   },
   "source": [
    "Since the size of the intersection of both sets is zero, `wordlist` and `answers` are disjoint sets. \n",
    "\n",
    "### Fetching occurrence data\n",
    "Wordle puzzle solutions seem to be *normal* words, ones that you commonly hear in daily life. If you were to count the occurrence of every word in a book, you might consider the most frequently occurring ones to be normal. If you did that with every book, you'd have an idea of prevalence in the language. It just so happens that Google already did this with [more than 8 million books](https://aclanthology.org/P12-3029). It's called the [Google Books Ngram Corpus](https://books.google.com/ngrams).\n",
    "\n",
    "The function `getNgramUsage` downloads data from the JSON endpoint of Google Books Ngram viewer for a specific word. It defaults to corpus 26, which is English language books. Setting `smoothing` to zero fetches raw data. Finally, the range is between 1970 and 2019."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ce600947-507c-4ca6-bb26-5f0adf1a933c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fetch(url, params, wait=1):\n",
    "    res = requests.get(url, params)\n",
    "    tooManyReqsCode = 429;\n",
    "    if res.status_code == tooManyReqsCode:\n",
    "        while(res.status_code == tooManyReqsCode):\n",
    "            sleep(wait)\n",
    "            res = requests.get(url, params)\n",
    "    return res;\n",
    "    \n",
    "def getNgramUsage(ngram):\n",
    "    url = 'https://books.google.com/ngrams/json'\n",
    "    args = {\n",
    "        'content': ngram,\n",
    "        'year_start': 1970,\n",
    "        'year_end': 2019,\n",
    "        'corpus': 26,\n",
    "        'smoothing': 0\n",
    "    }\n",
    "    emptyValue = \"\"\n",
    "    res = fetch(url, args)\n",
    "    try:\n",
    "        occurrence = np.mean(res.json()[0]['timeseries'])\n",
    "        return occurrence\n",
    "    except:\n",
    "        return emptyValue"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "949d7882-1e82-4543-8c09-3afe0403b069",
   "metadata": {},
   "source": [
    "The function `fetch` is a wrapper for `requests.get` that crudely handles rate limiting. It will return a JSON response like this one for the [Webster 2021 Word of the Year](https://www.merriam-webster.com/words-at-play/word-of-the-year/vaccine)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "92b37433-29d1-42d5-b644-5ada45f18b50",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'ngram': 'vaccine',\n",
       "  'parent': '',\n",
       "  'type': 'NGRAM',\n",
       "  'timeseries': [7.729678145551588e-06,\n",
       "   7.463667134288698e-06,\n",
       "   7.373356993412017e-06,\n",
       "   7.262522688376651e-06,\n",
       "   7.1265085352933966e-06,\n",
       "   7.0330834855017855e-06,\n",
       "   6.615856169186632e-06,\n",
       "   6.586049342634699e-06,\n",
       "   6.623334593314212e-06,\n",
       "   6.5487166693856125e-06]}]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fetch(\"https://books.google.com/ngrams/json\",{'content': 'vaccine', 'corpus': 26, 'year_start': 2010, 'year_end': 2019}).json()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcbf3fc2-98bc-47ce-bc87-9393eae386e8",
   "metadata": {},
   "source": [
    "Likewise, `getNgramUsage` formats requests to the JSON endpoint of Google Book's Ngram viewer and calculates the mean of the values in the `timeseries` array."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6685171a-5137-4a41-b59d-3ae1e618abf0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.952722715141135e-06"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getNgramUsage(\"vaccine\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc992973-7530-4435-8cbd-5125ec6f798e",
   "metadata": {},
   "source": [
    "Some [words](https://en.wikipedia.org/wiki/Nardwuar) will not have a result, in this case we just use an empty string to denote null values at this point in the data pipeline."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3310e484-e616-46a1-837f-f14ead7e228e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "getNgramUsage(\"nardwuar\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3db18f65-c972-4df4-aacc-4fa1dc54533c",
   "metadata": {},
   "source": [
    "## Querying Ngram Viewer\n",
    "\n",
    "The process of querying this API is error prone. There's no rate limit response in the response headers. But, it does use the HTTP status code for [too many requests](https://developer.mozilla.org/en-US/docs/Web/HTTP/Status/429). So the cell below caches results to disc before it hits an error, and it is meant to be run repeatedly until all words have been queried. Removing `wordle.tsv` from the working directory will restart the process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d2169d68-6d8b-4986-aac3-030c6da126f7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Querying is complete 😎\n"
     ]
    }
   ],
   "source": [
    "# Get a list of only the words that we need to query\n",
    "\n",
    "fn = 'wordle.tsv'\n",
    "# If output file does not exist, then create it. \n",
    "# Likewise, delete output file to restart this data collection process\n",
    "if not Path(fn).is_file():\n",
    "    with open(fn, 'w') as f:\n",
    "        f.write('word\\toccurrence\\n')\n",
    "\n",
    "queriedWords = list(pd.read_csv(fn, delimiter='\\t').word)        \n",
    "words = wordlist + answers\n",
    "isQueried = lambda word : word not in queriedWords\n",
    "wordsToQuery = filter(isQueried, words)\n",
    "\n",
    "# Append new words to file\n",
    "with open(fn, 'a') as f:\n",
    "    for word in wordsToQuery:\n",
    "        clear_output(wait=True)\n",
    "        display(\"Querying: {}\".format(word))\n",
    "        # If word has not already been queried, then query it.\n",
    "        occurrence = getNgramUsage(word)\n",
    "        row ='{}\\t{}\\n'.format(word, occurrence)\n",
    "        f.write(row)\n",
    "\n",
    "print(\"Querying is complete 😎\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a2d8ead9-6d27-4b59-b3bb-f4c61c5f695f",
   "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>word</th>\n",
       "      <th>occurrence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>12972</td>\n",
       "      <td>1.290200e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>12972</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>aahed</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.879030e-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.370009e-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7.192649e-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.321310e-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.953624e-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.871247e-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.430560e-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         word    occurrence\n",
       "count   12972  1.290200e+04\n",
       "unique  12972           NaN\n",
       "top     aahed           NaN\n",
       "freq        1           NaN\n",
       "mean      NaN  4.879030e-06\n",
       "std       NaN  4.370009e-05\n",
       "min       NaN  7.192649e-12\n",
       "25%       NaN  2.321310e-09\n",
       "50%       NaN  1.953624e-08\n",
       "75%       NaN  2.871247e-07\n",
       "max       NaN  2.430560e-03"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv(fn, delimiter=\"\\t\", dtype={'word': str, 'occurrence': np.float64})\n",
    "data.describe(include=\"all\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "19474770-829f-477d-b5b0-896667af7847",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "wordle.tsv contains 12972 rows\n",
      "70 rows are NaN\n"
     ]
    }
   ],
   "source": [
    "print(\"{} contains {} rows\\n{} rows are NaN\".format(fn,data.shape[0], data[data['occurrence'].isnull()].shape[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ab60164-be4a-488c-b25b-56674395a07a",
   "metadata": {},
   "source": [
    "Finally export results to a CSV file."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bbee895-b25d-4bff-95b7-2da8a4ad11d8",
   "metadata": {},
   "source": [
    "This matches the total words calculated above, but 70 words did not have results on this corpus in Google Books Ngram Viewer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "baa9c676-0476-4030-ae86-f536ec38636e",
   "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>word</th>\n",
       "      <th>occurrence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>546</th>\n",
       "      <td>avyze</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>551</th>\n",
       "      <td>awdls</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>593</th>\n",
       "      <td>azygy</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1138</th>\n",
       "      <td>boygs</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1325</th>\n",
       "      <td>byked</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10482</th>\n",
       "      <td>ylkes</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10528</th>\n",
       "      <td>yrivd</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10579</th>\n",
       "      <td>zedas</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10587</th>\n",
       "      <td>zexes</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10598</th>\n",
       "      <td>zimbs</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        word  occurrence\n",
       "546    avyze         NaN\n",
       "551    awdls         NaN\n",
       "593    azygy         NaN\n",
       "1138   boygs         NaN\n",
       "1325   byked         NaN\n",
       "...      ...         ...\n",
       "10482  ylkes         NaN\n",
       "10528  yrivd         NaN\n",
       "10579  zedas         NaN\n",
       "10587  zexes         NaN\n",
       "10598  zimbs         NaN\n",
       "\n",
       "[70 rows x 2 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['occurrence'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94c411f8-6c52-47bb-b628-26a596a39167",
   "metadata": {},
   "source": [
    "I'll replace those NaN files with zero."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bbbd93cd-0c93-4bcd-8a66-941d5def6052",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['occurrence'] = data['occurrence'].replace(np.nan, 0)\n",
    "sum(data['occurrence'].isnull())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f48d0943-c418-4f8c-b707-bf9ffa4fe4bb",
   "metadata": {},
   "source": [
    "Since the sum of a Boolean series is zero, there are no truthy values and all NaN values have been replaced.\n",
    "\n",
    "Now I'll assign wordle days to the subset of solutions. This dataset can separate the solutions subset from the word list by filtering on `null` values in this field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e9ab38c5-32f7-445f-9b1a-72e435f58742",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['day'] = data['word'].apply(lambda w: answers.index(w) if w in answers else None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a234c186-c8cc-4570-a8a6-5560897fc0a7",
   "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>word</th>\n",
       "      <th>occurrence</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10657</th>\n",
       "      <td>cigar</td>\n",
       "      <td>2.605142e-06</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10658</th>\n",
       "      <td>rebut</td>\n",
       "      <td>1.067693e-06</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10659</th>\n",
       "      <td>sissy</td>\n",
       "      <td>2.105774e-07</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10660</th>\n",
       "      <td>humph</td>\n",
       "      <td>2.216358e-08</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10661</th>\n",
       "      <td>awake</td>\n",
       "      <td>7.097157e-06</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        word    occurrence  day\n",
       "10657  cigar  2.605142e-06  0.0\n",
       "10658  rebut  1.067693e-06  1.0\n",
       "10659  sissy  2.105774e-07  2.0\n",
       "10660  humph  2.216358e-08  3.0\n",
       "10661  awake  7.097157e-06  4.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[~data['day'].isnull()].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "40a3c130-8577-4f84-91fc-09f2a878fd52",
   "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>word</th>\n",
       "      <th>occurrence</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>12972</td>\n",
       "      <td>1.297200e+04</td>\n",
       "      <td>2315.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>12972</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>aahed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.852702e-06</td>\n",
       "      <td>1157.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.358348e-05</td>\n",
       "      <td>668.427259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.234261e-09</td>\n",
       "      <td>578.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.904988e-08</td>\n",
       "      <td>1157.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.829156e-07</td>\n",
       "      <td>1735.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.430560e-03</td>\n",
       "      <td>2314.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         word    occurrence          day\n",
       "count   12972  1.297200e+04  2315.000000\n",
       "unique  12972           NaN          NaN\n",
       "top     aahed           NaN          NaN\n",
       "freq        1           NaN          NaN\n",
       "mean      NaN  4.852702e-06  1157.000000\n",
       "std       NaN  4.358348e-05   668.427259\n",
       "min       NaN  0.000000e+00     0.000000\n",
       "25%       NaN  2.234261e-09   578.500000\n",
       "50%       NaN  1.904988e-08  1157.000000\n",
       "75%       NaN  2.829156e-07  1735.500000\n",
       "max       NaN  2.430560e-03  2314.000000"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.describe(include=\"all\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1ea9434-22e9-4587-bca2-dbb70621f5e4",
   "metadata": {},
   "source": [
    "### Export data\n",
    "Finally, sort and export the dataset to CSV format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "353cb918-6fa4-4530-9845-e157c4e4c7d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.sort_values('word').to_csv('wordle.csv', index=False)"
   ]
  }
 ],
 "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.9.7"
  },
  "toc-autonumbering": true
 },
 "nbformat": 4,
 "nbformat_minor": 5
}