convert-front-pages-harvest.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "227551ef-abef-4a2f-b748-df37c8272b75",
   "metadata": {},
   "source": [
    "# Process the harvested data\n",
    "\n",
    "Using the Trove Newspaper Harvester, I've [downloaded the metadata from 19 million newspaper articles](large-harvest-example.ipynb) that were published on page one. The Harvester has saved all the metadata into a big (14.6gb) newline-delimited JSON file. In this notebook we'll process the JSON, extracting the fields we want into a CSV file. Then we'll use DuckDB to save the CSV as a (much smaller) parquet file, and create another parquet file that sums the word counts per page and category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "faf4d977-d888-45f5-86b8-59b0146aa851",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import json\n",
    "import re\n",
    "from pathlib import Path\n",
    "\n",
    "import duckdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "897e4fb4-1334-454f-8462-e20054f22dc9",
   "metadata": {},
   "source": [
    "## Save required columns to a CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf91595f-854d-4dc1-9102-1e2689ddd317",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Open up the ndjson file created by the Newspaper Harvester\n",
    "with Path(\"data\", \"20230722015049\", \"results.ndjson\").open() as json_file:\n",
    "\n",
    "    # Open a CSV file for writing the filtered results\n",
    "    with Path(\"front_pages.csv\").open(\"w\") as csv_file:\n",
    "        csv_writer = csv.writer(csv_file)\n",
    "        # Write the CSV column headers\n",
    "        csv_writer.writerow(\n",
    "            [\n",
    "                \"article_id\",\n",
    "                \"title\",\n",
    "                \"newspaper_id\",\n",
    "                \"date\",\n",
    "                \"category\",\n",
    "                \"word_count\",\n",
    "                \"page_id\",\n",
    "            ]\n",
    "        )\n",
    "        # Process the ndjson file line by line to avoid memory problems\n",
    "        for line in json_file:\n",
    "            # print(line)\n",
    "            data = json.loads(line)\n",
    "            try:\n",
    "                # Articles in supplements can also have a page number of 1\n",
    "                # These can be excluded by looking for an 'S' in the `pageSequence` field\n",
    "                if str(data[\"pageSequence\"]).endswith(\"S\"):\n",
    "                    continue\n",
    "                # Select the fields we want to add tto the CSV\n",
    "                row = [\n",
    "                    data[\"id\"],\n",
    "                    data.get(\"heading\", \"\"),\n",
    "                    data[\"title\"][\"id\"],\n",
    "                    data[\"date\"],\n",
    "                    data[\"category\"],\n",
    "                    data[\"wordCount\"],\n",
    "                    re.search(r\"\\/page\\/(\\d+)$\", data[\"trovePageUrl\"]).group(1),\n",
    "                ]\n",
    "            except (TypeError, KeyError):\n",
    "                # These are likely to be articles that are 'coming soon' without page links. Uncomment the next line to check\n",
    "                # print(data)\n",
    "                pass\n",
    "            else:\n",
    "                # Write the row of data\n",
    "                csv_writer.writerow(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6632f33c-8ae0-48ad-933e-3e13bb7b01f6",
   "metadata": {},
   "source": [
    "## Use DuckDB to create parquet files\n",
    "\n",
    "The CSV file created by the step above is about 1.3gb. That's too big to easily share or use, so we'll convert it to a parquet file which will be much more compact. One way of doing this is to use a `COPY` statement with DuckDB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1a5d496-a4ad-4a70-a46b-111e7f7d65ee",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# This just loads all the data from the CSV into DuckDB then saves it as a parquet file.\n",
    "duckdb.sql(\n",
    "    \"COPY (SELECT * FROM read_csv('front_pages.csv', AUTO_DETECT=TRUE)) TO 'front_pages.parquet' (FORMAT 'PARQUET', CODEC 'ZSTD');\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0180d68c-0932-45a7-bec0-9c89c6ba0157",
   "metadata": {},
   "source": [
    "What I'm really interested in is how the number of words in each of Trove's article categories varies over time – in particular how do the 'Advertising' and 'Article' categories compare? To make this easier to explore, we'll use DuckDB to run an SQL query over the CSV data, grouping the results by page, newspaper, and category, and then adding together the word counts. What we'll end up with is a breakdown for each page, giving the total number of words in each category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45d4b25a-e9df-4345-ba41-a238af66f7e1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "duckdb.sql(\n",
    "    \"COPY (SELECT date, page_id, newspaper_id, category, sum(word_count) AS total FROM read_csv('front_pages.csv', AUTO_DETECT=TRUE) GROUP BY date, page_id, newspaper_id, category) TO 'front_pages_totals.parquet' (FORMAT 'PARQUET', CODEC 'ZSTD');\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c70800c-0101-4440-821b-b35e4002d1f0",
   "metadata": {},
   "source": [
    "----\n",
    "\n",
    "Created by [Tim Sherratt](https://timsherratt.org), August 2023"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}