AnalyseSearchResults_DetectVersionVsUniqueResources.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7c89fb77",
   "metadata": {},
   "source": [
    "# Analyse Query Results: Detect Versions vs Unique Resources"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e047bf54",
   "metadata": {},
   "source": [
    "When you get query results in SolrWayback, it will provide archival resources that matches your query.\n",
    "\n",
    "However, you might want to know how many of them that are unique, and how many that are different versions of the same resource.\n",
    "\n",
    "Different versions are not necessarily duplicates. For a front page of a web news site, there will be great dissimilarities from one day to the next. However, when you use SolrWayback to define a corpus of resources for examination, it might be valuable to know if some of resources in your corpus are \"overrepresented\" due to a high number of versions.\n",
    "\n",
    "This notebook let you analyse the number of unique urls in the corpus, and detect which archived urls that appears several times.\n",
    "\n",
    "#### JSONL data from SolrWayback\n",
    "You will need a file in JSONL format, exported from your SolrWayback query result. The notebook uses an example from a query on \"Jonas Gahr Støre\", but you can replace this data from your own queries.\n",
    "\n",
    "For the operations in this notebook to work, the fields \"warc_key_id\", \"url_norm\" and \"crawl_date\" must be included in the export."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3c0dfe5",
   "metadata": {},
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f86650a4",
   "metadata": {},
   "source": [
    "First, we need to import the necessary libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "66919e68",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandas in /Users/joncto/opt/anaconda3/lib/python3.9/site-packages (2.0.0)\n",
      "Requirement already satisfied: tzdata>=2022.1 in /Users/joncto/opt/anaconda3/lib/python3.9/site-packages (from pandas) (2023.3)\n",
      "Requirement already satisfied: pytz>=2020.1 in /Users/joncto/opt/anaconda3/lib/python3.9/site-packages (from pandas) (2022.7)\n",
      "Requirement already satisfied: python-dateutil>=2.8.2 in /Users/joncto/opt/anaconda3/lib/python3.9/site-packages (from pandas) (2.8.2)\n",
      "Requirement already satisfied: numpy>=1.20.3 in /Users/joncto/opt/anaconda3/lib/python3.9/site-packages (from pandas) (1.20.3)\n",
      "Requirement already satisfied: six>=1.5 in /Users/joncto/opt/anaconda3/lib/python3.9/site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5c2f209",
   "metadata": {},
   "source": [
    "### Load data from SolrWayback"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8189132d",
   "metadata": {},
   "source": [
    "Then, we load the JSONL data set you exported from SolrWayback."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0a5e895b",
   "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>warc_key_id</th>\n",
       "      <th>title</th>\n",
       "      <th>domain</th>\n",
       "      <th>content_type</th>\n",
       "      <th>crawl_date</th>\n",
       "      <th>crawl_year</th>\n",
       "      <th>url_norm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;urn:uuid:06add8d3-380a-4b3a-9cf5-2578f77d7f62&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>1645812529000</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;urn:uuid:8de22051-ee5a-4d25-b7eb-0c1d48f1e007&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>1646070193000</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;urn:uuid:db2fb7a5-0eb1-460e-b414-e77fa2d2b410&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>1646330192000</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;urn:uuid:498cd143-f833-4c6c-aec6-fc721e25bc88&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>1646765196000</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;urn:uuid:d65a4fb6-b316-480e-842c-d644d6c1ad49&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>1646919904000</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6408</th>\n",
       "      <td>&lt;urn:uuid:0647b30d-bcbe-4537-a95f-a6a0ce5aaffa&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>tv2.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>1646061800000</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://tv2.no:443/spesialer/api/open/articles/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6409</th>\n",
       "      <td>&lt;urn:uuid:fc1b7067-c8ba-434d-bb85-5184ac83914f&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>tv2.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>1646323954000</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://tv2.no:443/spesialer/api/open/articles/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6410</th>\n",
       "      <td>&lt;urn:uuid:f916850c-9474-4da1-8229-7bc507112d27&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>tv2.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>1648824785000</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://tv2.no:443/spesialer/api/open/articles/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6411</th>\n",
       "      <td>&lt;urn:uuid:ba5c7751-2470-4d77-acb9-c955b3da8f0d&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>vl.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>1685060661000</td>\n",
       "      <td>2023</td>\n",
       "      <td>http://vl.no:443/pf/api/v3/content/fetch/conte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6412</th>\n",
       "      <td>&lt;urn:uuid:2ca44f98-06cd-4115-8b32-9885af6358e6&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>dagsavisen.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>1685091944000</td>\n",
       "      <td>2023</td>\n",
       "      <td>http://dagsavisen.no:443/pf/api/v3/content/fet...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6413 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          warc_key_id  \\\n",
       "0     <urn:uuid:06add8d3-380a-4b3a-9cf5-2578f77d7f62>   \n",
       "1     <urn:uuid:8de22051-ee5a-4d25-b7eb-0c1d48f1e007>   \n",
       "2     <urn:uuid:db2fb7a5-0eb1-460e-b414-e77fa2d2b410>   \n",
       "3     <urn:uuid:498cd143-f833-4c6c-aec6-fc721e25bc88>   \n",
       "4     <urn:uuid:d65a4fb6-b316-480e-842c-d644d6c1ad49>   \n",
       "...                                               ...   \n",
       "6408  <urn:uuid:0647b30d-bcbe-4537-a95f-a6a0ce5aaffa>   \n",
       "6409  <urn:uuid:fc1b7067-c8ba-434d-bb85-5184ac83914f>   \n",
       "6410  <urn:uuid:f916850c-9474-4da1-8229-7bc507112d27>   \n",
       "6411  <urn:uuid:ba5c7751-2470-4d77-acb9-c955b3da8f0d>   \n",
       "6412  <urn:uuid:2ca44f98-06cd-4115-8b32-9885af6358e6>   \n",
       "\n",
       "                                    title            domain  \\\n",
       "0     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "1     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "2     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "3     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "4     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "...                                   ...               ...   \n",
       "6408                                  NaN            tv2.no   \n",
       "6409                                  NaN            tv2.no   \n",
       "6410                                  NaN            tv2.no   \n",
       "6411                                  NaN             vl.no   \n",
       "6412                                  NaN     dagsavisen.no   \n",
       "\n",
       "               content_type     crawl_date  crawl_year  \\\n",
       "0     application/xhtml+xml  1645812529000        2022   \n",
       "1     application/xhtml+xml  1646070193000        2022   \n",
       "2     application/xhtml+xml  1646330192000        2022   \n",
       "3     application/xhtml+xml  1646765196000        2022   \n",
       "4     application/xhtml+xml  1646919904000        2022   \n",
       "...                     ...            ...         ...   \n",
       "6408             text/plain  1646061800000        2022   \n",
       "6409             text/plain  1646323954000        2022   \n",
       "6410             text/plain  1648824785000        2022   \n",
       "6411             text/plain  1685060661000        2023   \n",
       "6412             text/plain  1685091944000        2023   \n",
       "\n",
       "                                               url_norm  \n",
       "0          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "1          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "2          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "3          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "4          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "...                                                 ...  \n",
       "6408  http://tv2.no:443/spesialer/api/open/articles/...  \n",
       "6409  http://tv2.no:443/spesialer/api/open/articles/...  \n",
       "6410  http://tv2.no:443/spesialer/api/open/articles/...  \n",
       "6411  http://vl.no:443/pf/api/v3/content/fetch/conte...  \n",
       "6412  http://dagsavisen.no:443/pf/api/v3/content/fet...  \n",
       "\n",
       "[6413 rows x 7 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_json('data/solrwayback_JonasGahrStore.jsonl', lines=True)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "250f8e9b",
   "metadata": {},
   "source": [
    "## 1. Convert timestamp"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "074811ba",
   "metadata": {},
   "source": [
    "The timestamps in our JSONL file is in the machine-readable Unix format. Before computing this, we want to convert it into python's Datetime format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e8e48d05",
   "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>warc_key_id</th>\n",
       "      <th>title</th>\n",
       "      <th>domain</th>\n",
       "      <th>content_type</th>\n",
       "      <th>crawl_date</th>\n",
       "      <th>crawl_year</th>\n",
       "      <th>url_norm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;urn:uuid:06add8d3-380a-4b3a-9cf5-2578f77d7f62&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>2022-02-25 18:08:49</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;urn:uuid:8de22051-ee5a-4d25-b7eb-0c1d48f1e007&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>2022-02-28 17:43:13</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;urn:uuid:db2fb7a5-0eb1-460e-b414-e77fa2d2b410&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>2022-03-03 17:56:32</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;urn:uuid:498cd143-f833-4c6c-aec6-fc721e25bc88&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>2022-03-08 18:46:36</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;urn:uuid:d65a4fb6-b316-480e-842c-d644d6c1ad49&gt;</td>\n",
       "      <td>Europabloggen.no » Jonas Gahr Støre</td>\n",
       "      <td>europabloggen.no</td>\n",
       "      <td>application/xhtml+xml</td>\n",
       "      <td>2022-03-10 13:45:04</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://europabloggen.no/tag/jonas-gahr-støre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6408</th>\n",
       "      <td>&lt;urn:uuid:0647b30d-bcbe-4537-a95f-a6a0ce5aaffa&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>tv2.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>2022-02-28 15:23:20</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://tv2.no:443/spesialer/api/open/articles/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6409</th>\n",
       "      <td>&lt;urn:uuid:fc1b7067-c8ba-434d-bb85-5184ac83914f&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>tv2.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>2022-03-03 16:12:34</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://tv2.no:443/spesialer/api/open/articles/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6410</th>\n",
       "      <td>&lt;urn:uuid:f916850c-9474-4da1-8229-7bc507112d27&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>tv2.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>2022-04-01 14:53:05</td>\n",
       "      <td>2022</td>\n",
       "      <td>http://tv2.no:443/spesialer/api/open/articles/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6411</th>\n",
       "      <td>&lt;urn:uuid:ba5c7751-2470-4d77-acb9-c955b3da8f0d&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>vl.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>2023-05-26 00:24:21</td>\n",
       "      <td>2023</td>\n",
       "      <td>http://vl.no:443/pf/api/v3/content/fetch/conte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6412</th>\n",
       "      <td>&lt;urn:uuid:2ca44f98-06cd-4115-8b32-9885af6358e6&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>dagsavisen.no</td>\n",
       "      <td>text/plain</td>\n",
       "      <td>2023-05-26 09:05:44</td>\n",
       "      <td>2023</td>\n",
       "      <td>http://dagsavisen.no:443/pf/api/v3/content/fet...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6413 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          warc_key_id  \\\n",
       "0     <urn:uuid:06add8d3-380a-4b3a-9cf5-2578f77d7f62>   \n",
       "1     <urn:uuid:8de22051-ee5a-4d25-b7eb-0c1d48f1e007>   \n",
       "2     <urn:uuid:db2fb7a5-0eb1-460e-b414-e77fa2d2b410>   \n",
       "3     <urn:uuid:498cd143-f833-4c6c-aec6-fc721e25bc88>   \n",
       "4     <urn:uuid:d65a4fb6-b316-480e-842c-d644d6c1ad49>   \n",
       "...                                               ...   \n",
       "6408  <urn:uuid:0647b30d-bcbe-4537-a95f-a6a0ce5aaffa>   \n",
       "6409  <urn:uuid:fc1b7067-c8ba-434d-bb85-5184ac83914f>   \n",
       "6410  <urn:uuid:f916850c-9474-4da1-8229-7bc507112d27>   \n",
       "6411  <urn:uuid:ba5c7751-2470-4d77-acb9-c955b3da8f0d>   \n",
       "6412  <urn:uuid:2ca44f98-06cd-4115-8b32-9885af6358e6>   \n",
       "\n",
       "                                    title            domain  \\\n",
       "0     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "1     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "2     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "3     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "4     Europabloggen.no » Jonas Gahr Støre  europabloggen.no   \n",
       "...                                   ...               ...   \n",
       "6408                                  NaN            tv2.no   \n",
       "6409                                  NaN            tv2.no   \n",
       "6410                                  NaN            tv2.no   \n",
       "6411                                  NaN             vl.no   \n",
       "6412                                  NaN     dagsavisen.no   \n",
       "\n",
       "               content_type          crawl_date  crawl_year  \\\n",
       "0     application/xhtml+xml 2022-02-25 18:08:49        2022   \n",
       "1     application/xhtml+xml 2022-02-28 17:43:13        2022   \n",
       "2     application/xhtml+xml 2022-03-03 17:56:32        2022   \n",
       "3     application/xhtml+xml 2022-03-08 18:46:36        2022   \n",
       "4     application/xhtml+xml 2022-03-10 13:45:04        2022   \n",
       "...                     ...                 ...         ...   \n",
       "6408             text/plain 2022-02-28 15:23:20        2022   \n",
       "6409             text/plain 2022-03-03 16:12:34        2022   \n",
       "6410             text/plain 2022-04-01 14:53:05        2022   \n",
       "6411             text/plain 2023-05-26 00:24:21        2023   \n",
       "6412             text/plain 2023-05-26 09:05:44        2023   \n",
       "\n",
       "                                               url_norm  \n",
       "0          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "1          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "2          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "3          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "4          http://europabloggen.no/tag/jonas-gahr-støre  \n",
       "...                                                 ...  \n",
       "6408  http://tv2.no:443/spesialer/api/open/articles/...  \n",
       "6409  http://tv2.no:443/spesialer/api/open/articles/...  \n",
       "6410  http://tv2.no:443/spesialer/api/open/articles/...  \n",
       "6411  http://vl.no:443/pf/api/v3/content/fetch/conte...  \n",
       "6412  http://dagsavisen.no:443/pf/api/v3/content/fet...  \n",
       "\n",
       "[6413 rows x 7 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df['crawl_date'] = pd.to_datetime(df['crawl_date'], unit='ms')\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd2f936d",
   "metadata": {},
   "source": [
    "### 1. Count resources (unique urls and urls with several versions)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7c371f4",
   "metadata": {},
   "source": [
    "Let us count the number each url appear."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0539a23c",
   "metadata": {},
   "outputs": [],
   "source": [
    "url_counts = df['url_norm'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6eda6f7c",
   "metadata": {},
   "source": [
    "Then, we want to filter urls that appears more than one time in the data set.\n",
    "To make it easier to read, we limit the dataframe to the url, title and count columns.\n",
    "Then, we display a table with "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1360f017",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of lines in the dataset: \u001b[1m6413\u001b[0m \n",
      "Number of unique resources: \u001b[1m3482\u001b[0m \n",
      "Number of resources archived in several versions: \u001b[1m979\u001b[0m.\n"
     ]
    },
    {
     "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>url_norm</th>\n",
       "      <th>title</th>\n",
       "      <th>versions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>898</th>\n",
       "      <td>http://sapmi.arbeiderpartiet.no:443/</td>\n",
       "      <td>Forsiden</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>http://friheten.no:443/</td>\n",
       "      <td>artikkel</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>901</th>\n",
       "      <td>http://sapmi.arbeiderpartiet.no:443/l/se/polit...</td>\n",
       "      <td>Válggaprogramma 2021-2025</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>907</th>\n",
       "      <td>http://sapmi.arbeiderpartiet.no:443/nyhetsbrev</td>\n",
       "      <td>Få nyhetsbrev fra Arbeiderpartiet!</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://abcnyheter.no:443/hvordan</td>\n",
       "      <td>Hvordan | ABC Nyheter</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>743</th>\n",
       "      <td>http://regjeringen.no:443/no/aktuelt/taler_art...</td>\n",
       "      <td>Taler og innlegg - regjeringen.no</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>http://nettavisen.no:443/norsk-debatt/tenk-om-...</td>\n",
       "      <td>Norsk politikk, Ukraina | Tenk om Moxnes og Ly...</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>http://nettavisen.no:443/nyheter/blir-det-krig...</td>\n",
       "      <td>Ukraina, Russland | Blir det krig i Norge? Det...</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>http://dagbladet.no:443/nyheter/tidslinje-russ...</td>\n",
       "      <td>Tidslinje: Russlands krig mot Ukraina</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>http://folkogforsvar.no:443/ukraina-vestens-re...</td>\n",
       "      <td>Ukraina: Vestens respons</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>979 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              url_norm  \\\n",
       "898               http://sapmi.arbeiderpartiet.no:443/   \n",
       "189                            http://friheten.no:443/   \n",
       "901  http://sapmi.arbeiderpartiet.no:443/l/se/polit...   \n",
       "907     http://sapmi.arbeiderpartiet.no:443/nyhetsbrev   \n",
       "0                     http://abcnyheter.no:443/hvordan   \n",
       "..                                                 ...   \n",
       "743  http://regjeringen.no:443/no/aktuelt/taler_art...   \n",
       "237  http://nettavisen.no:443/norsk-debatt/tenk-om-...   \n",
       "238  http://nettavisen.no:443/nyheter/blir-det-krig...   \n",
       "88   http://dagbladet.no:443/nyheter/tidslinje-russ...   \n",
       "159  http://folkogforsvar.no:443/ukraina-vestens-re...   \n",
       "\n",
       "                                                 title  versions  \n",
       "898                                           Forsiden        25  \n",
       "189                                           artikkel        10  \n",
       "901                          Válggaprogramma 2021-2025         9  \n",
       "907                 Få nyhetsbrev fra Arbeiderpartiet!         8  \n",
       "0                                Hvordan | ABC Nyheter         7  \n",
       "..                                                 ...       ...  \n",
       "743                  Taler og innlegg - regjeringen.no         2  \n",
       "237  Norsk politikk, Ukraina | Tenk om Moxnes og Ly...         2  \n",
       "238  Ukraina, Russland | Blir det krig i Norge? Det...         2  \n",
       "88               Tidslinje: Russlands krig mot Ukraina         1  \n",
       "159                           Ukraina: Vestens respons         1  \n",
       "\n",
       "[979 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "urls_with_multiple_timestamps = df.groupby('url_norm').filter(lambda x: x['crawl_date'].nunique() > 1)\n",
    "urls_with_counts = urls_with_multiple_timestamps.groupby(['url_norm', 'title'])['crawl_date'].nunique().reset_index(name='versions')\n",
    "urls_with_counts = urls_with_counts.sort_values(by='versions', ascending=False)\n",
    "\n",
    "# Print summary message\n",
    "total_rows = df.shape[0]\n",
    "unique_urls = len(df['url_norm'].unique())\n",
    "multiple_versions = len(urls_with_counts)\n",
    "print(f\"Total number of lines in the dataset: \\033[1m{total_rows}\\033[0m \\nNumber of unique resources: \\033[1m{unique_urls}\\033[0m \\nNumber of resources archived in several versions: \\033[1m{multiple_versions}\\033[0m.\")\n",
    "\n",
    "display(urls_with_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "778532c6",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}