database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyzotero import zotero\n",
    "import os\n",
    "import tweepy as tw\n",
    "import pandas as pd\n",
    "import datetime\n",
    "import json, sys\n",
    "from datetime import date, timedelta  \n",
    "import datetime\n",
    "import plotly.express as px\n",
    "import pycountry\n",
    "import re\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Zotero library credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "library_id = '2514686'\n",
    "library_type = 'group'\n",
    "api_key = '' # api_key is only needed for private groups and libraries\n",
    "zot = zotero.Zotero(library_id, library_type)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retrieving data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "items = zot.everything(zot.top())\n",
    "\n",
    "data=[]\n",
    "columns=['Title','Publication type', 'Link to publication', 'Abstract', 'Zotero link', 'Date added', 'Date published', 'Authors', 'Publisher', 'Journal', 'Col_key']\n",
    "\n",
    "for item in items:\n",
    "    creators = item['data']['creators']\n",
    "    creators_str = \", \".join([creator.get('firstName', '') + ' ' + creator.get('lastName', '') for creator in creators])\n",
    "    data.append((\n",
    "        item['data']['title'], \n",
    "        item['data']['itemType'], \n",
    "        item['data']['url'], \n",
    "        item['data']['abstractNote'], \n",
    "        item['links']['alternate']['href'],\n",
    "        item['data']['dateAdded'],\n",
    "        item['data'].get('date'),\n",
    "        creators_str,\n",
    "        item['data'].get('publisher'),\n",
    "        item['data'].get('publicationTitle'),\n",
    "        item['data']['collections'],\n",
    "        )) \n",
    "\n",
    "df = pd.DataFrame(data, columns=columns)\n",
    "\n",
    "mapping_types = {\n",
    "    'thesis': 'Thesis',\n",
    "    'journalArticle': 'Journal article',\n",
    "    'book': 'Book',\n",
    "    'bookSection': 'Book chapter',\n",
    "    'blogPost': 'Blog post',\n",
    "    'videoRecording': 'Video',\n",
    "    'podcast': 'Podcast',\n",
    "    'magazineArticle': 'Magazine article',\n",
    "    'webpage': 'Webpage',\n",
    "    'newspaperArticle': 'Newspaper article',\n",
    "    'report': 'Report',\n",
    "    'forumPost': 'Forum post',\n",
    "    'manuscript': 'Manuscript',\n",
    "    'document': 'Document',\n",
    "    'conferencePaper': 'Conference paper',\n",
    "    'film': 'Film',\n",
    "    'presentation': 'Presentation'\n",
    "}\n",
    "df['Publication type'] = df['Publication type'].replace(mapping_types)\n",
    "\n",
    "mapping_publisher = {\n",
    "    'Taylor & Francis Group': 'Taylor and Francis',\n",
    "    'Taylor and Francis': 'Taylor and Francis',\n",
    "    'Taylor & Francis': 'Taylor and Francis',\n",
    "    'Routledge': 'Routledge',\n",
    "    'Routledge Handbooks Online': 'Routledge',\n",
    "    'Praeger Security International': 'Praeger',\n",
    "    'Praeger': 'Praeger'\n",
    "}\n",
    "df['Publisher'] = df['Publisher'].replace(mapping_publisher)\n",
    "\n",
    "# df['Publisher'] = df['Publisher'].replace(['Taylor & Francis Group', 'Taylor and Francis', 'Taylor & Francis'], 'Taylor and Francis')\n",
    "# df['Publisher'] = df['Publisher'].replace(['Routledge', 'Routledge Handbooks Online'], 'Routledge')\n",
    "# df['Publisher'] = df['Publisher'].replace(['Praeger Security International', 'Praeger'], 'Praeger')\n",
    "\n",
    "mapping_journal = {\n",
    "    'International Journal of Intelligence and Counterintelligence': 'Intl Journal of Intelligence and Counterintelligence',\n",
    "    'International Journal of Intelligence and CounterIntelligence': 'Intl Journal of Intelligence and Counterintelligence',\n",
    "    'Intelligence and national security': 'Intelligence and National Security',\n",
    "    'Intelligence and National Security': 'Intelligence and National Security',\n",
    "    'Intelligence & National Security': 'Intelligence and National Security'\n",
    "}\n",
    "\n",
    "df['Journal'] = df['Journal'].replace(mapping_journal)\n",
    "\n",
    "# df['Journal'] = df['Journal'].replace(['International Journal of Intelligence and Counterintelligence', 'International Journal of Intelligence and CounterIntelligence'], 'Intl Journal of Intelligence and Counterintelligence')\n",
    "# df['Journal'] = df['Journal'].replace(['Intelligence and national security', 'Intelligence and National Security', 'Intelligence & National Security'], 'Intelligence and National Security')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Title</th>\n",
       "      <th>Publication type</th>\n",
       "      <th>Link to publication</th>\n",
       "      <th>Abstract</th>\n",
       "      <th>Zotero link</th>\n",
       "      <th>Date added</th>\n",
       "      <th>Date published</th>\n",
       "      <th>Authors</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Journal</th>\n",
       "      <th>Col_key</th>\n",
       "      <th>Author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Spanish Intelligence in the Early Days of Late...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/08850607.2023.2261823</td>\n",
       "      <td>Conflicting factions within the late-Francoist...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-11-02T18:58:59Z</td>\n",
       "      <td>2023-31-10</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>None</td>\n",
       "      <td>Intl Journal of Intelligence and Counterintell...</td>\n",
       "      <td>MP7FJ9UA</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Spanish Intelligence in the Early Days of Late...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/08850607.2023.2261823</td>\n",
       "      <td>Conflicting factions within the late-Francoist...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-11-02T18:58:59Z</td>\n",
       "      <td>2023-31-10</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>None</td>\n",
       "      <td>Intl Journal of Intelligence and Counterintell...</td>\n",
       "      <td>AZ3BZ9BR</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>How Could Israeli Intelligence Miss the Hamas ...</td>\n",
       "      <td>Blog post</td>\n",
       "      <td>https://www.csis.org/analysis/how-could-israel...</td>\n",
       "      <td>Hamas pulled off a complex, multifront assault...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-30T21:27:03Z</td>\n",
       "      <td>2023-10-11</td>\n",
       "      <td>Emily Harding</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9YPHGMBS</td>\n",
       "      <td>Emily Harding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The pressures and pitfalls of attributing blam...</td>\n",
       "      <td>Newspaper article</td>\n",
       "      <td>https://www.ft.com/content/0d2b4ebc-aedc-44d6-...</td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-27T10:24:29Z</td>\n",
       "      <td>2023-10-26</td>\n",
       "      <td>Suzanne Raine</td>\n",
       "      <td>None</td>\n",
       "      <td>Financial Times</td>\n",
       "      <td>CZJ36V8L</td>\n",
       "      <td>Suzanne Raine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Intelligence warning in the corporate sector: ...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/18335330.2023.2274614</td>\n",
       "      <td>The 2013 terrorist attack at the In Amenas, Al...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-27T10:24:01Z</td>\n",
       "      <td>2023-10-26</td>\n",
       "      <td>Michael J. Ard</td>\n",
       "      <td>None</td>\n",
       "      <td>Journal of Policing, Intelligence and Counter ...</td>\n",
       "      <td>TLFN4NAL</td>\n",
       "      <td>Michael J. Ard</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4484</th>\n",
       "      <td>Silent Warfare: Understanding the World of Int...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2002</td>\n",
       "      <td>Abram N. Shulsky, Gary Schmitt</td>\n",
       "      <td>Potomac Books</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Gary Schmitt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4485</th>\n",
       "      <td>Silent Warfare: Understanding the World of Int...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2002</td>\n",
       "      <td>Abram N. Shulsky, Gary Schmitt</td>\n",
       "      <td>Potomac Books</td>\n",
       "      <td>None</td>\n",
       "      <td>DVEM4H4W</td>\n",
       "      <td>Gary Schmitt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4486</th>\n",
       "      <td>Intelligence and Government in Britain and the...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2012</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>Praeger</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4487</th>\n",
       "      <td>Intelligence and Government in Britain and the...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2012</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>Praeger</td>\n",
       "      <td>None</td>\n",
       "      <td>DVEM4H4W</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4488</th>\n",
       "      <td>Intelligence Power in Peace and War</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>1996</td>\n",
       "      <td>Michael Herman</td>\n",
       "      <td>Cambridge University Press</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Michael Herman</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4489 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  Title   Publication type  \\\n",
       "0     Spanish Intelligence in the Early Days of Late...    Journal article   \n",
       "1     Spanish Intelligence in the Early Days of Late...    Journal article   \n",
       "2     How Could Israeli Intelligence Miss the Hamas ...          Blog post   \n",
       "3     The pressures and pitfalls of attributing blam...  Newspaper article   \n",
       "4     Intelligence warning in the corporate sector: ...    Journal article   \n",
       "...                                                 ...                ...   \n",
       "4484  Silent Warfare: Understanding the World of Int...               Book   \n",
       "4485  Silent Warfare: Understanding the World of Int...               Book   \n",
       "4486  Intelligence and Government in Britain and the...               Book   \n",
       "4487  Intelligence and Government in Britain and the...               Book   \n",
       "4488                Intelligence Power in Peace and War               Book   \n",
       "\n",
       "                                    Link to publication  \\\n",
       "0         https://doi.org/10.1080/08850607.2023.2261823   \n",
       "1         https://doi.org/10.1080/08850607.2023.2261823   \n",
       "2     https://www.csis.org/analysis/how-could-israel...   \n",
       "3     https://www.ft.com/content/0d2b4ebc-aedc-44d6-...   \n",
       "4         https://doi.org/10.1080/18335330.2023.2274614   \n",
       "...                                                 ...   \n",
       "4484                                                      \n",
       "4485                                                      \n",
       "4486                                                      \n",
       "4487                                                      \n",
       "4488                                                      \n",
       "\n",
       "                                               Abstract  \\\n",
       "0     Conflicting factions within the late-Francoist...   \n",
       "1     Conflicting factions within the late-Francoist...   \n",
       "2     Hamas pulled off a complex, multifront assault...   \n",
       "3                                                         \n",
       "4     The 2013 terrorist attack at the In Amenas, Al...   \n",
       "...                                                 ...   \n",
       "4484                                                      \n",
       "4485                                                      \n",
       "4486                                                      \n",
       "4487                                                      \n",
       "4488                                                      \n",
       "\n",
       "                                            Zotero link            Date added  \\\n",
       "0     https://www.zotero.org/groups/intelligence_bib...  2023-11-02T18:58:59Z   \n",
       "1     https://www.zotero.org/groups/intelligence_bib...  2023-11-02T18:58:59Z   \n",
       "2     https://www.zotero.org/groups/intelligence_bib...  2023-10-30T21:27:03Z   \n",
       "3     https://www.zotero.org/groups/intelligence_bib...  2023-10-27T10:24:29Z   \n",
       "4     https://www.zotero.org/groups/intelligence_bib...  2023-10-27T10:24:01Z   \n",
       "...                                                 ...                   ...   \n",
       "4484  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4485  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4486  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4487  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4488  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "\n",
       "     Date published                         Authors  \\\n",
       "0        2023-31-10       Antonio M. Díaz-Fernández   \n",
       "1        2023-31-10       Antonio M. Díaz-Fernández   \n",
       "2        2023-10-11                   Emily Harding   \n",
       "3        2023-10-26                   Suzanne Raine   \n",
       "4        2023-10-26                  Michael J. Ard   \n",
       "...             ...                             ...   \n",
       "4484           2002  Abram N. Shulsky, Gary Schmitt   \n",
       "4485           2002  Abram N. Shulsky, Gary Schmitt   \n",
       "4486           2012              Philip H.J. Davies   \n",
       "4487           2012              Philip H.J. Davies   \n",
       "4488           1996                  Michael Herman   \n",
       "\n",
       "                       Publisher  \\\n",
       "0                           None   \n",
       "1                           None   \n",
       "2                           None   \n",
       "3                           None   \n",
       "4                           None   \n",
       "...                          ...   \n",
       "4484               Potomac Books   \n",
       "4485               Potomac Books   \n",
       "4486                     Praeger   \n",
       "4487                     Praeger   \n",
       "4488  Cambridge University Press   \n",
       "\n",
       "                                                Journal   Col_key  \\\n",
       "0     Intl Journal of Intelligence and Counterintell...  MP7FJ9UA   \n",
       "1     Intl Journal of Intelligence and Counterintell...  AZ3BZ9BR   \n",
       "2                                                  None  9YPHGMBS   \n",
       "3                                       Financial Times  CZJ36V8L   \n",
       "4     Journal of Policing, Intelligence and Counter ...  TLFN4NAL   \n",
       "...                                                 ...       ...   \n",
       "4484                                               None  HCN8YFI8   \n",
       "4485                                               None  DVEM4H4W   \n",
       "4486                                               None  HCN8YFI8   \n",
       "4487                                               None  DVEM4H4W   \n",
       "4488                                               None  HCN8YFI8   \n",
       "\n",
       "                         Author  \n",
       "0     Antonio M. Díaz-Fernández  \n",
       "1     Antonio M. Díaz-Fernández  \n",
       "2                 Emily Harding  \n",
       "3                 Suzanne Raine  \n",
       "4                Michael J. Ard  \n",
       "...                         ...  \n",
       "4484               Gary Schmitt  \n",
       "4485               Gary Schmitt  \n",
       "4486         Philip H.J. Davies  \n",
       "4487         Philip H.J. Davies  \n",
       "4488             Michael Herman  \n",
       "\n",
       "[4489 rows x 12 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Author'] = df['Authors'].str.split(', ')\n",
    "df = df.explode('Author', ignore_index=True)\n",
    "df['Col_key'] = df['Col_key'].astype(str).str.strip('[]').str.split(', ')\n",
    "df = df.explode('Col_key', ignore_index=True)\n",
    "df['Col_key'] = df['Col_key'].astype(str).str.strip(\"'\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def zotero_collections(library_id, library_type):\n",
    "    collections = zot.collections()\n",
    "    data = [(item['data']['key'], item['data']['name'], item['meta']['numItems'], item['links']['alternate']['href']) for item in collections]\n",
    "    df_collections = pd.DataFrame(data, columns=['Col_key', 'Collection_name', 'NumberOfItems', 'Collection_link'])\n",
    "    return df_collections\n",
    "df_collections = zotero_collections(library_id, library_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Title</th>\n",
       "      <th>Publication type</th>\n",
       "      <th>Link to publication</th>\n",
       "      <th>Abstract</th>\n",
       "      <th>Zotero link</th>\n",
       "      <th>Date added</th>\n",
       "      <th>Date published</th>\n",
       "      <th>Authors</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Journal</th>\n",
       "      <th>Col_key</th>\n",
       "      <th>Author</th>\n",
       "      <th>Collection_name</th>\n",
       "      <th>NumberOfItems</th>\n",
       "      <th>Collection_link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Spanish Intelligence in the Early Days of Late...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/08850607.2023.2261823</td>\n",
       "      <td>Conflicting factions within the late-Francoist...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-11-02T18:58:59Z</td>\n",
       "      <td>2023-31-10</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>None</td>\n",
       "      <td>Intl Journal of Intelligence and Counterintell...</td>\n",
       "      <td>MP7FJ9UA</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>01.5 Inter-war period</td>\n",
       "      <td>75.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Spanish Intelligence in the Early Days of Late...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/08850607.2023.2261823</td>\n",
       "      <td>Conflicting factions within the late-Francoist...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-11-02T18:58:59Z</td>\n",
       "      <td>2023-31-10</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>None</td>\n",
       "      <td>Intl Journal of Intelligence and Counterintell...</td>\n",
       "      <td>AZ3BZ9BR</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>14 Global intelligence</td>\n",
       "      <td>152.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>How Could Israeli Intelligence Miss the Hamas ...</td>\n",
       "      <td>Blog post</td>\n",
       "      <td>https://www.csis.org/analysis/how-could-israel...</td>\n",
       "      <td>Hamas pulled off a complex, multifront assault...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-30T21:27:03Z</td>\n",
       "      <td>2023-10-11</td>\n",
       "      <td>Emily Harding</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9YPHGMBS</td>\n",
       "      <td>Emily Harding</td>\n",
       "      <td>05.1 Intelligence, warning, and surprise attack</td>\n",
       "      <td>40.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The pressures and pitfalls of attributing blam...</td>\n",
       "      <td>Newspaper article</td>\n",
       "      <td>https://www.ft.com/content/0d2b4ebc-aedc-44d6-...</td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-27T10:24:29Z</td>\n",
       "      <td>2023-10-26</td>\n",
       "      <td>Suzanne Raine</td>\n",
       "      <td>None</td>\n",
       "      <td>Financial Times</td>\n",
       "      <td>CZJ36V8L</td>\n",
       "      <td>Suzanne Raine</td>\n",
       "      <td>03 Intelligence analysis</td>\n",
       "      <td>113.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Intelligence warning in the corporate sector: ...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/18335330.2023.2274614</td>\n",
       "      <td>The 2013 terrorist attack at the In Amenas, Al...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-27T10:24:01Z</td>\n",
       "      <td>2023-10-26</td>\n",
       "      <td>Michael J. Ard</td>\n",
       "      <td>None</td>\n",
       "      <td>Journal of Policing, Intelligence and Counter ...</td>\n",
       "      <td>TLFN4NAL</td>\n",
       "      <td>Michael J. Ard</td>\n",
       "      <td>01.9 Terrorism, insurgency, and counterinsurgency</td>\n",
       "      <td>85.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</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",
       "      <td>...</td>\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>4484</th>\n",
       "      <td>Silent Warfare: Understanding the World of Int...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2002</td>\n",
       "      <td>Abram N. Shulsky, Gary Schmitt</td>\n",
       "      <td>Potomac Books</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Gary Schmitt</td>\n",
       "      <td>02 Intelligence studies</td>\n",
       "      <td>153.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4485</th>\n",
       "      <td>Silent Warfare: Understanding the World of Int...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2002</td>\n",
       "      <td>Abram N. Shulsky, Gary Schmitt</td>\n",
       "      <td>Potomac Books</td>\n",
       "      <td>None</td>\n",
       "      <td>DVEM4H4W</td>\n",
       "      <td>Gary Schmitt</td>\n",
       "      <td>06 Intelligence oversight, and ethics</td>\n",
       "      <td>117.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4486</th>\n",
       "      <td>Intelligence and Government in Britain and the...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2012</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>Praeger</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>02 Intelligence studies</td>\n",
       "      <td>153.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4487</th>\n",
       "      <td>Intelligence and Government in Britain and the...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2012</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>Praeger</td>\n",
       "      <td>None</td>\n",
       "      <td>DVEM4H4W</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>06 Intelligence oversight, and ethics</td>\n",
       "      <td>117.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4488</th>\n",
       "      <td>Intelligence Power in Peace and War</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>1996</td>\n",
       "      <td>Michael Herman</td>\n",
       "      <td>Cambridge University Press</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Michael Herman</td>\n",
       "      <td>02 Intelligence studies</td>\n",
       "      <td>153.0</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4489 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  Title   Publication type  \\\n",
       "0     Spanish Intelligence in the Early Days of Late...    Journal article   \n",
       "1     Spanish Intelligence in the Early Days of Late...    Journal article   \n",
       "2     How Could Israeli Intelligence Miss the Hamas ...          Blog post   \n",
       "3     The pressures and pitfalls of attributing blam...  Newspaper article   \n",
       "4     Intelligence warning in the corporate sector: ...    Journal article   \n",
       "...                                                 ...                ...   \n",
       "4484  Silent Warfare: Understanding the World of Int...               Book   \n",
       "4485  Silent Warfare: Understanding the World of Int...               Book   \n",
       "4486  Intelligence and Government in Britain and the...               Book   \n",
       "4487  Intelligence and Government in Britain and the...               Book   \n",
       "4488                Intelligence Power in Peace and War               Book   \n",
       "\n",
       "                                    Link to publication  \\\n",
       "0         https://doi.org/10.1080/08850607.2023.2261823   \n",
       "1         https://doi.org/10.1080/08850607.2023.2261823   \n",
       "2     https://www.csis.org/analysis/how-could-israel...   \n",
       "3     https://www.ft.com/content/0d2b4ebc-aedc-44d6-...   \n",
       "4         https://doi.org/10.1080/18335330.2023.2274614   \n",
       "...                                                 ...   \n",
       "4484                                                      \n",
       "4485                                                      \n",
       "4486                                                      \n",
       "4487                                                      \n",
       "4488                                                      \n",
       "\n",
       "                                               Abstract  \\\n",
       "0     Conflicting factions within the late-Francoist...   \n",
       "1     Conflicting factions within the late-Francoist...   \n",
       "2     Hamas pulled off a complex, multifront assault...   \n",
       "3                                                         \n",
       "4     The 2013 terrorist attack at the In Amenas, Al...   \n",
       "...                                                 ...   \n",
       "4484                                                      \n",
       "4485                                                      \n",
       "4486                                                      \n",
       "4487                                                      \n",
       "4488                                                      \n",
       "\n",
       "                                            Zotero link            Date added  \\\n",
       "0     https://www.zotero.org/groups/intelligence_bib...  2023-11-02T18:58:59Z   \n",
       "1     https://www.zotero.org/groups/intelligence_bib...  2023-11-02T18:58:59Z   \n",
       "2     https://www.zotero.org/groups/intelligence_bib...  2023-10-30T21:27:03Z   \n",
       "3     https://www.zotero.org/groups/intelligence_bib...  2023-10-27T10:24:29Z   \n",
       "4     https://www.zotero.org/groups/intelligence_bib...  2023-10-27T10:24:01Z   \n",
       "...                                                 ...                   ...   \n",
       "4484  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4485  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4486  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4487  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4488  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "\n",
       "     Date published                         Authors  \\\n",
       "0        2023-31-10       Antonio M. Díaz-Fernández   \n",
       "1        2023-31-10       Antonio M. Díaz-Fernández   \n",
       "2        2023-10-11                   Emily Harding   \n",
       "3        2023-10-26                   Suzanne Raine   \n",
       "4        2023-10-26                  Michael J. Ard   \n",
       "...             ...                             ...   \n",
       "4484           2002  Abram N. Shulsky, Gary Schmitt   \n",
       "4485           2002  Abram N. Shulsky, Gary Schmitt   \n",
       "4486           2012              Philip H.J. Davies   \n",
       "4487           2012              Philip H.J. Davies   \n",
       "4488           1996                  Michael Herman   \n",
       "\n",
       "                       Publisher  \\\n",
       "0                           None   \n",
       "1                           None   \n",
       "2                           None   \n",
       "3                           None   \n",
       "4                           None   \n",
       "...                          ...   \n",
       "4484               Potomac Books   \n",
       "4485               Potomac Books   \n",
       "4486                     Praeger   \n",
       "4487                     Praeger   \n",
       "4488  Cambridge University Press   \n",
       "\n",
       "                                                Journal   Col_key  \\\n",
       "0     Intl Journal of Intelligence and Counterintell...  MP7FJ9UA   \n",
       "1     Intl Journal of Intelligence and Counterintell...  AZ3BZ9BR   \n",
       "2                                                  None  9YPHGMBS   \n",
       "3                                       Financial Times  CZJ36V8L   \n",
       "4     Journal of Policing, Intelligence and Counter ...  TLFN4NAL   \n",
       "...                                                 ...       ...   \n",
       "4484                                               None  HCN8YFI8   \n",
       "4485                                               None  DVEM4H4W   \n",
       "4486                                               None  HCN8YFI8   \n",
       "4487                                               None  DVEM4H4W   \n",
       "4488                                               None  HCN8YFI8   \n",
       "\n",
       "                         Author  \\\n",
       "0     Antonio M. Díaz-Fernández   \n",
       "1     Antonio M. Díaz-Fernández   \n",
       "2                 Emily Harding   \n",
       "3                 Suzanne Raine   \n",
       "4                Michael J. Ard   \n",
       "...                         ...   \n",
       "4484               Gary Schmitt   \n",
       "4485               Gary Schmitt   \n",
       "4486         Philip H.J. Davies   \n",
       "4487         Philip H.J. Davies   \n",
       "4488             Michael Herman   \n",
       "\n",
       "                                        Collection_name  NumberOfItems  \\\n",
       "0                                 01.5 Inter-war period           75.0   \n",
       "1                                14 Global intelligence          152.0   \n",
       "2       05.1 Intelligence, warning, and surprise attack           40.0   \n",
       "3                              03 Intelligence analysis          113.0   \n",
       "4     01.9 Terrorism, insurgency, and counterinsurgency           85.0   \n",
       "...                                                 ...            ...   \n",
       "4484                            02 Intelligence studies          153.0   \n",
       "4485              06 Intelligence oversight, and ethics          117.0   \n",
       "4486                            02 Intelligence studies          153.0   \n",
       "4487              06 Intelligence oversight, and ethics          117.0   \n",
       "4488                            02 Intelligence studies          153.0   \n",
       "\n",
       "                                        Collection_link  \n",
       "0     https://www.zotero.org/groups/intelligence_bib...  \n",
       "1     https://www.zotero.org/groups/intelligence_bib...  \n",
       "2     https://www.zotero.org/groups/intelligence_bib...  \n",
       "3     https://www.zotero.org/groups/intelligence_bib...  \n",
       "4     https://www.zotero.org/groups/intelligence_bib...  \n",
       "...                                                 ...  \n",
       "4484  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4485  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4486  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4487  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4488  https://www.zotero.org/groups/intelligence_bib...  \n",
       "\n",
       "[4489 rows x 15 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.merge(df, df_collections, on='Col_key', how='left')\n",
    "df"
   ]
  },
  {
   "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>Title</th>\n",
       "      <th>Publication type</th>\n",
       "      <th>Link to publication</th>\n",
       "      <th>Abstract</th>\n",
       "      <th>Zotero link</th>\n",
       "      <th>Date added</th>\n",
       "      <th>Date published</th>\n",
       "      <th>Authors</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Journal</th>\n",
       "      <th>Col_key</th>\n",
       "      <th>Author</th>\n",
       "      <th>Collection_name</th>\n",
       "      <th>NumberOfItems</th>\n",
       "      <th>Collection_link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>How Could Israeli Intelligence Miss the Hamas ...</td>\n",
       "      <td>Blog post</td>\n",
       "      <td>https://www.csis.org/analysis/how-could-israel...</td>\n",
       "      <td>Hamas pulled off a complex, multifront assault...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-30T21:27:03Z</td>\n",
       "      <td>2023-10-11</td>\n",
       "      <td>Emily Harding</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9YPHGMBS</td>\n",
       "      <td>Emily Harding</td>\n",
       "      <td>05.1 Intelligence, warning, and surprise attack</td>\n",
       "      <td>40</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The pressures and pitfalls of attributing blam...</td>\n",
       "      <td>Newspaper article</td>\n",
       "      <td>https://www.ft.com/content/0d2b4ebc-aedc-44d6-...</td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-27T10:24:29Z</td>\n",
       "      <td>2023-10-26</td>\n",
       "      <td>Suzanne Raine</td>\n",
       "      <td>None</td>\n",
       "      <td>Financial Times</td>\n",
       "      <td>CZJ36V8L</td>\n",
       "      <td>Suzanne Raine</td>\n",
       "      <td>03 Intelligence analysis</td>\n",
       "      <td>113</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Intelligence warning in the corporate sector: ...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/18335330.2023.2274614</td>\n",
       "      <td>The 2013 terrorist attack at the In Amenas, Al...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-27T10:24:01Z</td>\n",
       "      <td>2023-10-26</td>\n",
       "      <td>Michael J. Ard</td>\n",
       "      <td>None</td>\n",
       "      <td>Journal of Policing, Intelligence and Counter ...</td>\n",
       "      <td>TLFN4NAL</td>\n",
       "      <td>Michael J. Ard</td>\n",
       "      <td>01.9 Terrorism, insurgency, and counterinsurgency</td>\n",
       "      <td>85</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The moment Canada learned about the Cuban Miss...</td>\n",
       "      <td>Blog post</td>\n",
       "      <td>https://timsayle.substack.com/p/the-moment-can...</td>\n",
       "      <td>And: Just what is an “intelligence officer”?</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-24T09:34:59Z</td>\n",
       "      <td>2023-10-21</td>\n",
       "      <td>Tim Sayle</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>CZT6L9T7</td>\n",
       "      <td>Tim Sayle</td>\n",
       "      <td>01.7 Cold War</td>\n",
       "      <td>244</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Examining the Ethics of Spying: A Practitioner...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1007/s11572-023-09704-5</td>\n",
       "      <td>This paper examines from the point of view of ...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-24T07:39:55Z</td>\n",
       "      <td>2023-10-20</td>\n",
       "      <td>David Omand</td>\n",
       "      <td>None</td>\n",
       "      <td>Criminal Law and Philosophy</td>\n",
       "      <td>DVEM4H4W</td>\n",
       "      <td>David Omand</td>\n",
       "      <td>06 Intelligence oversight, and ethics</td>\n",
       "      <td>117</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</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",
       "      <td>...</td>\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>4482</th>\n",
       "      <td>Silent Warfare: Understanding the World of Int...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2002</td>\n",
       "      <td>Abram N. Shulsky, Gary Schmitt</td>\n",
       "      <td>Potomac Books</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Gary Schmitt</td>\n",
       "      <td>02 Intelligence studies</td>\n",
       "      <td>153</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4483</th>\n",
       "      <td>Silent Warfare: Understanding the World of Int...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2002</td>\n",
       "      <td>Abram N. Shulsky, Gary Schmitt</td>\n",
       "      <td>Potomac Books</td>\n",
       "      <td>None</td>\n",
       "      <td>DVEM4H4W</td>\n",
       "      <td>Gary Schmitt</td>\n",
       "      <td>06 Intelligence oversight, and ethics</td>\n",
       "      <td>117</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4484</th>\n",
       "      <td>Intelligence and Government in Britain and the...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2012</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>Praeger</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>02 Intelligence studies</td>\n",
       "      <td>153</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4485</th>\n",
       "      <td>Intelligence and Government in Britain and the...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2012</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>Praeger</td>\n",
       "      <td>None</td>\n",
       "      <td>DVEM4H4W</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>06 Intelligence oversight, and ethics</td>\n",
       "      <td>117</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4486</th>\n",
       "      <td>Intelligence Power in Peace and War</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>1996</td>\n",
       "      <td>Michael Herman</td>\n",
       "      <td>Cambridge University Press</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Michael Herman</td>\n",
       "      <td>02 Intelligence studies</td>\n",
       "      <td>153</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4487 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  Title   Publication type  \\\n",
       "0     How Could Israeli Intelligence Miss the Hamas ...          Blog post   \n",
       "1     The pressures and pitfalls of attributing blam...  Newspaper article   \n",
       "2     Intelligence warning in the corporate sector: ...    Journal article   \n",
       "3     The moment Canada learned about the Cuban Miss...          Blog post   \n",
       "4     Examining the Ethics of Spying: A Practitioner...    Journal article   \n",
       "...                                                 ...                ...   \n",
       "4482  Silent Warfare: Understanding the World of Int...               Book   \n",
       "4483  Silent Warfare: Understanding the World of Int...               Book   \n",
       "4484  Intelligence and Government in Britain and the...               Book   \n",
       "4485  Intelligence and Government in Britain and the...               Book   \n",
       "4486                Intelligence Power in Peace and War               Book   \n",
       "\n",
       "                                    Link to publication  \\\n",
       "0     https://www.csis.org/analysis/how-could-israel...   \n",
       "1     https://www.ft.com/content/0d2b4ebc-aedc-44d6-...   \n",
       "2         https://doi.org/10.1080/18335330.2023.2274614   \n",
       "3     https://timsayle.substack.com/p/the-moment-can...   \n",
       "4            https://doi.org/10.1007/s11572-023-09704-5   \n",
       "...                                                 ...   \n",
       "4482                                                      \n",
       "4483                                                      \n",
       "4484                                                      \n",
       "4485                                                      \n",
       "4486                                                      \n",
       "\n",
       "                                               Abstract  \\\n",
       "0     Hamas pulled off a complex, multifront assault...   \n",
       "1                                                         \n",
       "2     The 2013 terrorist attack at the In Amenas, Al...   \n",
       "3          And: Just what is an “intelligence officer”?   \n",
       "4     This paper examines from the point of view of ...   \n",
       "...                                                 ...   \n",
       "4482                                                      \n",
       "4483                                                      \n",
       "4484                                                      \n",
       "4485                                                      \n",
       "4486                                                      \n",
       "\n",
       "                                            Zotero link            Date added  \\\n",
       "0     https://www.zotero.org/groups/intelligence_bib...  2023-10-30T21:27:03Z   \n",
       "1     https://www.zotero.org/groups/intelligence_bib...  2023-10-27T10:24:29Z   \n",
       "2     https://www.zotero.org/groups/intelligence_bib...  2023-10-27T10:24:01Z   \n",
       "3     https://www.zotero.org/groups/intelligence_bib...  2023-10-24T09:34:59Z   \n",
       "4     https://www.zotero.org/groups/intelligence_bib...  2023-10-24T07:39:55Z   \n",
       "...                                                 ...                   ...   \n",
       "4482  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4483  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4484  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4485  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4486  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "\n",
       "     Date published                         Authors  \\\n",
       "0        2023-10-11                   Emily Harding   \n",
       "1        2023-10-26                   Suzanne Raine   \n",
       "2        2023-10-26                  Michael J. Ard   \n",
       "3        2023-10-21                       Tim Sayle   \n",
       "4        2023-10-20                     David Omand   \n",
       "...             ...                             ...   \n",
       "4482           2002  Abram N. Shulsky, Gary Schmitt   \n",
       "4483           2002  Abram N. Shulsky, Gary Schmitt   \n",
       "4484           2012              Philip H.J. Davies   \n",
       "4485           2012              Philip H.J. Davies   \n",
       "4486           1996                  Michael Herman   \n",
       "\n",
       "                       Publisher  \\\n",
       "0                           None   \n",
       "1                           None   \n",
       "2                           None   \n",
       "3                           None   \n",
       "4                           None   \n",
       "...                          ...   \n",
       "4482               Potomac Books   \n",
       "4483               Potomac Books   \n",
       "4484                     Praeger   \n",
       "4485                     Praeger   \n",
       "4486  Cambridge University Press   \n",
       "\n",
       "                                                Journal   Col_key  \\\n",
       "0                                                  None  9YPHGMBS   \n",
       "1                                       Financial Times  CZJ36V8L   \n",
       "2     Journal of Policing, Intelligence and Counter ...  TLFN4NAL   \n",
       "3                                                  None  CZT6L9T7   \n",
       "4                           Criminal Law and Philosophy  DVEM4H4W   \n",
       "...                                                 ...       ...   \n",
       "4482                                               None  HCN8YFI8   \n",
       "4483                                               None  DVEM4H4W   \n",
       "4484                                               None  HCN8YFI8   \n",
       "4485                                               None  DVEM4H4W   \n",
       "4486                                               None  HCN8YFI8   \n",
       "\n",
       "                  Author                                    Collection_name  \\\n",
       "0          Emily Harding    05.1 Intelligence, warning, and surprise attack   \n",
       "1          Suzanne Raine                           03 Intelligence analysis   \n",
       "2         Michael J. Ard  01.9 Terrorism, insurgency, and counterinsurgency   \n",
       "3              Tim Sayle                                      01.7 Cold War   \n",
       "4            David Omand              06 Intelligence oversight, and ethics   \n",
       "...                  ...                                                ...   \n",
       "4482        Gary Schmitt                            02 Intelligence studies   \n",
       "4483        Gary Schmitt              06 Intelligence oversight, and ethics   \n",
       "4484  Philip H.J. Davies                            02 Intelligence studies   \n",
       "4485  Philip H.J. Davies              06 Intelligence oversight, and ethics   \n",
       "4486      Michael Herman                            02 Intelligence studies   \n",
       "\n",
       "      NumberOfItems                                    Collection_link  \n",
       "0                40  https://www.zotero.org/groups/intelligence_bib...  \n",
       "1               113  https://www.zotero.org/groups/intelligence_bib...  \n",
       "2                85  https://www.zotero.org/groups/intelligence_bib...  \n",
       "3               244  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4               117  https://www.zotero.org/groups/intelligence_bib...  \n",
       "...             ...                                                ...  \n",
       "4482            153  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4483            117  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4484            153  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4485            117  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4486            153  https://www.zotero.org/groups/intelligence_bib...  \n",
       "\n",
       "[4487 rows x 15 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['NumberOfItems'] = df['NumberOfItems'].fillna(0).astype(int)\n",
    "df['NumberOfItems'] = df['NumberOfItems'].astype(int)\n",
    "df"
   ]
  },
  {
   "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>Col_key</th>\n",
       "      <th>Collection_name</th>\n",
       "      <th>NumberOfItems</th>\n",
       "      <th>Collection_link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>DS3WDJUS</td>\n",
       "      <td>01.1 Pre-Napoleonic Wars</td>\n",
       "      <td>26</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>8XA7D88D</td>\n",
       "      <td>01.2 Napoleonic Wars</td>\n",
       "      <td>10</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>9DTPTK46</td>\n",
       "      <td>01.3 1800-1914</td>\n",
       "      <td>73</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>BNPYHVD4</td>\n",
       "      <td>01.4 WW1 (First Wold War)</td>\n",
       "      <td>104</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>MP7FJ9UA</td>\n",
       "      <td>01.5 Inter-war period</td>\n",
       "      <td>74</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>SCCGXHMZ</td>\n",
       "      <td>01.6 WW2 (Second World War)</td>\n",
       "      <td>204</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>CZT6L9T7</td>\n",
       "      <td>01.7 Cold War</td>\n",
       "      <td>244</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>DHLN8GE4</td>\n",
       "      <td>01.7.1 Arab-Israeli Conflict</td>\n",
       "      <td>22</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9I86L884</td>\n",
       "      <td>01.7.2 Falklands War</td>\n",
       "      <td>4</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>6XBG92FJ</td>\n",
       "      <td>01.7.3 Suez Crisis</td>\n",
       "      <td>4</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>V7KUA58M</td>\n",
       "      <td>01.7.4 The Troubles</td>\n",
       "      <td>9</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>BHVIFBRH</td>\n",
       "      <td>01.7.5 Vietnam War</td>\n",
       "      <td>11</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>WHBCJ8GW</td>\n",
       "      <td>01.8 Post-Cold War</td>\n",
       "      <td>25</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>TLFN4NAL</td>\n",
       "      <td>01.9 Terrorism, insurgency, and counterinsurgency</td>\n",
       "      <td>85</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>PW26X9FU</td>\n",
       "      <td>01.98 Miscellaneous</td>\n",
       "      <td>8</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>KGU8VLSW</td>\n",
       "      <td>01.99 Intelligence archives and methodology</td>\n",
       "      <td>2</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Col_key                                    Collection_name  \\\n",
       "43  DS3WDJUS                           01.1 Pre-Napoleonic Wars   \n",
       "29  8XA7D88D                               01.2 Napoleonic Wars   \n",
       "42  9DTPTK46                                     01.3 1800-1914   \n",
       "41  BNPYHVD4                          01.4 WW1 (First Wold War)   \n",
       "40  MP7FJ9UA                              01.5 Inter-war period   \n",
       "39  SCCGXHMZ                        01.6 WW2 (Second World War)   \n",
       "38  CZT6L9T7                                      01.7 Cold War   \n",
       "24  DHLN8GE4                       01.7.1 Arab-Israeli Conflict   \n",
       "7   9I86L884                               01.7.2 Falklands War   \n",
       "10  6XBG92FJ                                 01.7.3 Suez Crisis   \n",
       "9   V7KUA58M                                01.7.4 The Troubles   \n",
       "8   BHVIFBRH                                 01.7.5 Vietnam War   \n",
       "27  WHBCJ8GW                                 01.8 Post-Cold War   \n",
       "37  TLFN4NAL  01.9 Terrorism, insurgency, and counterinsurgency   \n",
       "36  PW26X9FU                                01.98 Miscellaneous   \n",
       "19  KGU8VLSW        01.99 Intelligence archives and methodology   \n",
       "\n",
       "    NumberOfItems                                    Collection_link  \n",
       "43             26  https://www.zotero.org/groups/intelligence_bib...  \n",
       "29             10  https://www.zotero.org/groups/intelligence_bib...  \n",
       "42             73  https://www.zotero.org/groups/intelligence_bib...  \n",
       "41            104  https://www.zotero.org/groups/intelligence_bib...  \n",
       "40             74  https://www.zotero.org/groups/intelligence_bib...  \n",
       "39            204  https://www.zotero.org/groups/intelligence_bib...  \n",
       "38            244  https://www.zotero.org/groups/intelligence_bib...  \n",
       "24             22  https://www.zotero.org/groups/intelligence_bib...  \n",
       "7               4  https://www.zotero.org/groups/intelligence_bib...  \n",
       "10              4  https://www.zotero.org/groups/intelligence_bib...  \n",
       "9               9  https://www.zotero.org/groups/intelligence_bib...  \n",
       "8              11  https://www.zotero.org/groups/intelligence_bib...  \n",
       "27             25  https://www.zotero.org/groups/intelligence_bib...  \n",
       "37             85  https://www.zotero.org/groups/intelligence_bib...  \n",
       "36              8  https://www.zotero.org/groups/intelligence_bib...  \n",
       "19              2  https://www.zotero.org/groups/intelligence_bib...  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_collections = df_collections.sort_values(by='Collection_name')\n",
    "df_collections=df_collections[df_collections['Collection_name'].str.contains(\"01.\")]\n",
    "df_collections = df_collections.iloc[1: , :]\n",
    "df_collections"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Title</th>\n",
       "      <th>Publication type</th>\n",
       "      <th>Link to publication</th>\n",
       "      <th>Abstract</th>\n",
       "      <th>Zotero link</th>\n",
       "      <th>Date added</th>\n",
       "      <th>Date published</th>\n",
       "      <th>Authors</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>Journal</th>\n",
       "      <th>Col_key</th>\n",
       "      <th>Author</th>\n",
       "      <th>Collection_name</th>\n",
       "      <th>NumberOfItems</th>\n",
       "      <th>Collection_link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Spanish Intelligence in the Early Days of Late...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/08850607.2023.2261823</td>\n",
       "      <td>Conflicting factions within the late-Francoist...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-11-02T18:58:59Z</td>\n",
       "      <td>2023-31-10</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>None</td>\n",
       "      <td>Intl Journal of Intelligence and Counterintell...</td>\n",
       "      <td>MP7FJ9UA</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>01.5 Inter-war period</td>\n",
       "      <td>75</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Spanish Intelligence in the Early Days of Late...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/08850607.2023.2261823</td>\n",
       "      <td>Conflicting factions within the late-Francoist...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-11-02T18:58:59Z</td>\n",
       "      <td>2023-31-10</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>None</td>\n",
       "      <td>Intl Journal of Intelligence and Counterintell...</td>\n",
       "      <td>AZ3BZ9BR</td>\n",
       "      <td>Antonio M. Díaz-Fernández</td>\n",
       "      <td>14 Global intelligence</td>\n",
       "      <td>152</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>How Could Israeli Intelligence Miss the Hamas ...</td>\n",
       "      <td>Blog post</td>\n",
       "      <td>https://www.csis.org/analysis/how-could-israel...</td>\n",
       "      <td>Hamas pulled off a complex, multifront assault...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-30T21:27:03Z</td>\n",
       "      <td>2023-10-11</td>\n",
       "      <td>Emily Harding</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9YPHGMBS</td>\n",
       "      <td>Emily Harding</td>\n",
       "      <td>05.1 Intelligence, warning, and surprise attack</td>\n",
       "      <td>40</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>The pressures and pitfalls of attributing blam...</td>\n",
       "      <td>Newspaper article</td>\n",
       "      <td>https://www.ft.com/content/0d2b4ebc-aedc-44d6-...</td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-27T10:24:29Z</td>\n",
       "      <td>2023-10-26</td>\n",
       "      <td>Suzanne Raine</td>\n",
       "      <td>None</td>\n",
       "      <td>Financial Times</td>\n",
       "      <td>CZJ36V8L</td>\n",
       "      <td>Suzanne Raine</td>\n",
       "      <td>03 Intelligence analysis</td>\n",
       "      <td>113</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Intelligence warning in the corporate sector: ...</td>\n",
       "      <td>Journal article</td>\n",
       "      <td>https://doi.org/10.1080/18335330.2023.2274614</td>\n",
       "      <td>The 2013 terrorist attack at the In Amenas, Al...</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2023-10-27T10:24:01Z</td>\n",
       "      <td>2023-10-26</td>\n",
       "      <td>Michael J. Ard</td>\n",
       "      <td>None</td>\n",
       "      <td>Journal of Policing, Intelligence and Counter ...</td>\n",
       "      <td>TLFN4NAL</td>\n",
       "      <td>Michael J. Ard</td>\n",
       "      <td>01.9 Terrorism, insurgency, and counterinsurgency</td>\n",
       "      <td>85</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</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",
       "      <td>...</td>\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>4484</th>\n",
       "      <td>Silent Warfare: Understanding the World of Int...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2002</td>\n",
       "      <td>Abram N. Shulsky, Gary Schmitt</td>\n",
       "      <td>Potomac Books</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Gary Schmitt</td>\n",
       "      <td>02 Intelligence studies</td>\n",
       "      <td>153</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4485</th>\n",
       "      <td>Silent Warfare: Understanding the World of Int...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2002</td>\n",
       "      <td>Abram N. Shulsky, Gary Schmitt</td>\n",
       "      <td>Potomac Books</td>\n",
       "      <td>None</td>\n",
       "      <td>DVEM4H4W</td>\n",
       "      <td>Gary Schmitt</td>\n",
       "      <td>06 Intelligence oversight, and ethics</td>\n",
       "      <td>117</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4486</th>\n",
       "      <td>Intelligence and Government in Britain and the...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2012</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>Praeger</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>02 Intelligence studies</td>\n",
       "      <td>153</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4487</th>\n",
       "      <td>Intelligence and Government in Britain and the...</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>2012</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>Praeger</td>\n",
       "      <td>None</td>\n",
       "      <td>DVEM4H4W</td>\n",
       "      <td>Philip H.J. Davies</td>\n",
       "      <td>06 Intelligence oversight, and ethics</td>\n",
       "      <td>117</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4488</th>\n",
       "      <td>Intelligence Power in Peace and War</td>\n",
       "      <td>Book</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "      <td>2020-06-05T10:17:21Z</td>\n",
       "      <td>1996</td>\n",
       "      <td>Michael Herman</td>\n",
       "      <td>Cambridge University Press</td>\n",
       "      <td>None</td>\n",
       "      <td>HCN8YFI8</td>\n",
       "      <td>Michael Herman</td>\n",
       "      <td>02 Intelligence studies</td>\n",
       "      <td>153</td>\n",
       "      <td>https://www.zotero.org/groups/intelligence_bib...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4489 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  Title   Publication type  \\\n",
       "0     Spanish Intelligence in the Early Days of Late...    Journal article   \n",
       "1     Spanish Intelligence in the Early Days of Late...    Journal article   \n",
       "2     How Could Israeli Intelligence Miss the Hamas ...          Blog post   \n",
       "3     The pressures and pitfalls of attributing blam...  Newspaper article   \n",
       "4     Intelligence warning in the corporate sector: ...    Journal article   \n",
       "...                                                 ...                ...   \n",
       "4484  Silent Warfare: Understanding the World of Int...               Book   \n",
       "4485  Silent Warfare: Understanding the World of Int...               Book   \n",
       "4486  Intelligence and Government in Britain and the...               Book   \n",
       "4487  Intelligence and Government in Britain and the...               Book   \n",
       "4488                Intelligence Power in Peace and War               Book   \n",
       "\n",
       "                                    Link to publication  \\\n",
       "0         https://doi.org/10.1080/08850607.2023.2261823   \n",
       "1         https://doi.org/10.1080/08850607.2023.2261823   \n",
       "2     https://www.csis.org/analysis/how-could-israel...   \n",
       "3     https://www.ft.com/content/0d2b4ebc-aedc-44d6-...   \n",
       "4         https://doi.org/10.1080/18335330.2023.2274614   \n",
       "...                                                 ...   \n",
       "4484                                                      \n",
       "4485                                                      \n",
       "4486                                                      \n",
       "4487                                                      \n",
       "4488                                                      \n",
       "\n",
       "                                               Abstract  \\\n",
       "0     Conflicting factions within the late-Francoist...   \n",
       "1     Conflicting factions within the late-Francoist...   \n",
       "2     Hamas pulled off a complex, multifront assault...   \n",
       "3                                                         \n",
       "4     The 2013 terrorist attack at the In Amenas, Al...   \n",
       "...                                                 ...   \n",
       "4484                                                      \n",
       "4485                                                      \n",
       "4486                                                      \n",
       "4487                                                      \n",
       "4488                                                      \n",
       "\n",
       "                                            Zotero link            Date added  \\\n",
       "0     https://www.zotero.org/groups/intelligence_bib...  2023-11-02T18:58:59Z   \n",
       "1     https://www.zotero.org/groups/intelligence_bib...  2023-11-02T18:58:59Z   \n",
       "2     https://www.zotero.org/groups/intelligence_bib...  2023-10-30T21:27:03Z   \n",
       "3     https://www.zotero.org/groups/intelligence_bib...  2023-10-27T10:24:29Z   \n",
       "4     https://www.zotero.org/groups/intelligence_bib...  2023-10-27T10:24:01Z   \n",
       "...                                                 ...                   ...   \n",
       "4484  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4485  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4486  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4487  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "4488  https://www.zotero.org/groups/intelligence_bib...  2020-06-05T10:17:21Z   \n",
       "\n",
       "     Date published                         Authors  \\\n",
       "0        2023-31-10       Antonio M. Díaz-Fernández   \n",
       "1        2023-31-10       Antonio M. Díaz-Fernández   \n",
       "2        2023-10-11                   Emily Harding   \n",
       "3        2023-10-26                   Suzanne Raine   \n",
       "4        2023-10-26                  Michael J. Ard   \n",
       "...             ...                             ...   \n",
       "4484           2002  Abram N. Shulsky, Gary Schmitt   \n",
       "4485           2002  Abram N. Shulsky, Gary Schmitt   \n",
       "4486           2012              Philip H.J. Davies   \n",
       "4487           2012              Philip H.J. Davies   \n",
       "4488           1996                  Michael Herman   \n",
       "\n",
       "                       Publisher  \\\n",
       "0                           None   \n",
       "1                           None   \n",
       "2                           None   \n",
       "3                           None   \n",
       "4                           None   \n",
       "...                          ...   \n",
       "4484               Potomac Books   \n",
       "4485               Potomac Books   \n",
       "4486                     Praeger   \n",
       "4487                     Praeger   \n",
       "4488  Cambridge University Press   \n",
       "\n",
       "                                                Journal   Col_key  \\\n",
       "0     Intl Journal of Intelligence and Counterintell...  MP7FJ9UA   \n",
       "1     Intl Journal of Intelligence and Counterintell...  AZ3BZ9BR   \n",
       "2                                                  None  9YPHGMBS   \n",
       "3                                       Financial Times  CZJ36V8L   \n",
       "4     Journal of Policing, Intelligence and Counter ...  TLFN4NAL   \n",
       "...                                                 ...       ...   \n",
       "4484                                               None  HCN8YFI8   \n",
       "4485                                               None  DVEM4H4W   \n",
       "4486                                               None  HCN8YFI8   \n",
       "4487                                               None  DVEM4H4W   \n",
       "4488                                               None  HCN8YFI8   \n",
       "\n",
       "                         Author  \\\n",
       "0     Antonio M. Díaz-Fernández   \n",
       "1     Antonio M. Díaz-Fernández   \n",
       "2                 Emily Harding   \n",
       "3                 Suzanne Raine   \n",
       "4                Michael J. Ard   \n",
       "...                         ...   \n",
       "4484               Gary Schmitt   \n",
       "4485               Gary Schmitt   \n",
       "4486         Philip H.J. Davies   \n",
       "4487         Philip H.J. Davies   \n",
       "4488             Michael Herman   \n",
       "\n",
       "                                        Collection_name  NumberOfItems  \\\n",
       "0                                 01.5 Inter-war period             75   \n",
       "1                                14 Global intelligence            152   \n",
       "2       05.1 Intelligence, warning, and surprise attack             40   \n",
       "3                              03 Intelligence analysis            113   \n",
       "4     01.9 Terrorism, insurgency, and counterinsurgency             85   \n",
       "...                                                 ...            ...   \n",
       "4484                            02 Intelligence studies            153   \n",
       "4485              06 Intelligence oversight, and ethics            117   \n",
       "4486                            02 Intelligence studies            153   \n",
       "4487              06 Intelligence oversight, and ethics            117   \n",
       "4488                            02 Intelligence studies            153   \n",
       "\n",
       "                                        Collection_link  \n",
       "0     https://www.zotero.org/groups/intelligence_bib...  \n",
       "1     https://www.zotero.org/groups/intelligence_bib...  \n",
       "2     https://www.zotero.org/groups/intelligence_bib...  \n",
       "3     https://www.zotero.org/groups/intelligence_bib...  \n",
       "4     https://www.zotero.org/groups/intelligence_bib...  \n",
       "...                                                 ...  \n",
       "4484  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4485  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4486  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4487  https://www.zotero.org/groups/intelligence_bib...  \n",
       "4488  https://www.zotero.org/groups/intelligence_bib...  \n",
       "\n",
       "[4489 rows x 15 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['NumberOfItems'] = df['NumberOfItems'].fillna(0)\n",
    "df['NumberOfItems'] = df['NumberOfItems'].astype(int)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('database.csv')"
   ]
  }
 ],
 "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}