laurenmarietta · March 5, 2021 23:50
diff --git a/db_face_rec.csv b/db_face_rec.csv
diff --git a/write-PHP-for-programmatic-WP-pages.ipynb b/write-PHP-for-programmatic-WP-pages.ipynb
 {
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Set-up\" data-toc-modified-id=\"Set-up-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Set up</a></span><ul class=\"toc-item\"><li><span><a href=\"#Import-necessary-packages\" data-toc-modified-id=\"Import-necessary-packages-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Import necessary packages</a></span></li><li><span><a href=\"#Use-pandas-to-load-spreadsheet-as-dataframe\" data-toc-modified-id=\"Use-pandas-to-load-spreadsheet-as-dataframe-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>Use <code>pandas</code> to load spreadsheet as dataframe</a></span></li><li><span><a href=\"#Define-the-root-URL-for-where-your-documents-are-on-the-server\" data-toc-modified-id=\"Define-the-root-URL-for-where-your-documents-are-on-the-server-1.3\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Define the root URL for where your documents are on the server</a></span></li></ul></li><li><span><a href=\"#Write-Wordpress-page-content-for-each-page\" data-toc-modified-id=\"Write-Wordpress-page-content-for-each-page-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Write Wordpress page content for each page</a></span><ul class=\"toc-item\"><li><span><a href=\"#Define-page-content-template,-using-string-formatting-with-replacement-fields\" data-toc-modified-id=\"Define-page-content-template,-using-string-formatting-with-replacement-fields-2.1\"><span class=\"toc-item-num\">2.1&nbsp;&nbsp;</span>Define page content template, using string formatting with replacement fields</a></span></li><li><span><a href=\"#Map-URLs-to-.zip-files-for-pages-with-a-ton-of-documents\" data-toc-modified-id=\"Map-URLs-to-.zip-files-for-pages-with-a-ton-of-documents-2.2\"><span class=\"toc-item-num\">2.2&nbsp;&nbsp;</span>Map URLs to .zip files for pages with a ton of documents</a></span></li><li><span><a href=\"#Function-to-turn-spreadsheet-row-into-page-content\" data-toc-modified-id=\"Function-to-turn-spreadsheet-row-into-page-content-2.3\"><span class=\"toc-item-num\">2.3&nbsp;&nbsp;</span>Function to turn spreadsheet row into page content</a></span></li><li><span><a href=\"#Use-.apply()-to-write-content-for-all-pages\" data-toc-modified-id=\"Use-.apply()-to-write-content-for-all-pages-2.4\"><span class=\"toc-item-num\">2.4&nbsp;&nbsp;</span>Use <code>.apply()</code> to write content for all pages</a></span></li></ul></li><li><span><a href=\"#Write-PHP-snippets-for-all-pages\" data-toc-modified-id=\"Write-PHP-snippets-for-all-pages-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Write PHP snippets for all pages</a></span><ul class=\"toc-item\"><li><span><a href=\"#Map-parent-page-IDs\" data-toc-modified-id=\"Map-parent-page-IDs-3.1\"><span class=\"toc-item-num\">3.1&nbsp;&nbsp;</span>Map parent page IDs</a></span></li><li><span><a href=\"#Function-to-turn-spreadsheet-row-into-PHP-command\" data-toc-modified-id=\"Function-to-turn-spreadsheet-row-into-PHP-command-3.2\"><span class=\"toc-item-num\">3.2&nbsp;&nbsp;</span>Function to turn spreadsheet row into PHP command</a></span></li><li><span><a href=\"#Use-.apply()-to-write-PHP-command-for-all-pages\" data-toc-modified-id=\"Use-.apply()-to-write-PHP-command-for-all-pages-3.3\"><span class=\"toc-item-num\">3.3&nbsp;&nbsp;</span>Use <code>.apply()</code> to write PHP command for all pages</a></span></li><li><span><a href=\"#Write-all-of-the-PHP-commands-out-to-a-txt-file\" data-toc-modified-id=\"Write-all-of-the-PHP-commands-out-to-a-txt-file-3.4\"><span class=\"toc-item-num\">3.4&nbsp;&nbsp;</span>Write all of the PHP commands out to a txt file</a></span></li></ul></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Programmatically create WP pages with Python\n",
    "\n",
    "How to programmatically write page content and PHP commands to create multiple new Wordpress pages, each linking to multiple documents.\n",
    "\n",
    "This notebook assumes you have:\n",
    "1. A directory structure containing all the documents to link to, organized such that there is one directory per page\n",
    "1. Those same documents uploaded to Wordpress, preserving directory structure\n",
    "1. A spreadsheet listing each desired page, the path to its corresponding folder in the directory structure, and any desired metadata to include\n",
    "\n",
    "---\n",
    "Feel free to contact [[email protected]](mailto:[email protected]) with any questions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set up\n",
    "### Import necessary packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Standard library\n",
    "import glob\n",
    "import os\n",
    "import re\n",
    "import urllib\n",
    "\n",
    "# Third party packages\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use `pandas` to load spreadsheet as dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "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>id</th>\n",
       "      <th>Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Department</th>\n",
       "      <th>Description</th>\n",
       "      <th>Files Transferred from Dropbox?</th>\n",
       "      <th>Numbered requests &amp; responses?</th>\n",
       "      <th>Directory</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A01-lantel-OEM</td>\n",
       "      <td>Lantel at the Boston Office of Emergency Manag...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Boston Office of Emergency Management</td>\n",
       "      <td>Since 2003, the City of Boston and surrounding...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A02-lantel-EOHHS</td>\n",
       "      <td>Lantel at the Executive Office of Health and H...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Executive Office of Health and Human Services</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A03-lantel-EOPSS</td>\n",
       "      <td>Lantel at the Executive Office of Public Safet...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Executive Office of Public Safety and Security</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A04-lantel-EOAF</td>\n",
       "      <td>Lantel at the Executive Office for Administrat...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Executive Office for Administration and Finance</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A05-lantel-MBTA</td>\n",
       "      <td>Lantel at the Massachusetts Bay Transit Author...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Massachusetts Bay Transit Authority (MBTA)</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>59</td>\n",
       "      <td>D11-peabody</td>\n",
       "      <td>Facial Recognition at the Peabody PD</td>\n",
       "      <td>2019</td>\n",
       "      <td>Peabody PD</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/D-town&amp;city/D...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>E01-camera-locs</td>\n",
       "      <td>Location of Surveillance Cameras Across Boston</td>\n",
       "      <td>2019</td>\n",
       "      <td>City of Boston</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Waiting for response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/E-boston/E01-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>E02-videofootage</td>\n",
       "      <td>Video Surveillance Data Sharing in Boston</td>\n",
       "      <td>2019</td>\n",
       "      <td>City of Boston</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/E-boston/E02-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>E03-camerastrategymeetings</td>\n",
       "      <td>Boston Security Camera Strategy Meetings</td>\n",
       "      <td>2019</td>\n",
       "      <td>City of Boston</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/E-boston/E03-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>63</td>\n",
       "      <td>F01-dea</td>\n",
       "      <td>Facial Recognition at the Drug Enforcement Adm...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Drug Enforcement Administration (DEA)</td>\n",
       "      <td>In 2019, the ACLU of Massachusetts and the Nat...</td>\n",
       "      <td>Waiting for response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/F-federal/F01...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>64 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            id  \\\n",
       "0               A01-lantel-OEM   \n",
       "1             A02-lantel-EOHHS   \n",
       "2             A03-lantel-EOPSS   \n",
       "3              A04-lantel-EOAF   \n",
       "4              A05-lantel-MBTA   \n",
       "..                         ...   \n",
       "59                 D11-peabody   \n",
       "60             E01-camera-locs   \n",
       "61            E02-videofootage   \n",
       "62  E03-camerastrategymeetings   \n",
       "63                     F01-dea   \n",
       "\n",
       "                                                Title  Year  \\\n",
       "0   Lantel at the Boston Office of Emergency Manag...  2019   \n",
       "1   Lantel at the Executive Office of Health and H...  2019   \n",
       "2   Lantel at the Executive Office of Public Safet...  2019   \n",
       "3   Lantel at the Executive Office for Administrat...  2019   \n",
       "4   Lantel at the Massachusetts Bay Transit Author...  2019   \n",
       "..                                                ...   ...   \n",
       "59               Facial Recognition at the Peabody PD  2019   \n",
       "60     Location of Surveillance Cameras Across Boston  2019   \n",
       "61          Video Surveillance Data Sharing in Boston  2019   \n",
       "62           Boston Security Camera Strategy Meetings  2019   \n",
       "63  Facial Recognition at the Drug Enforcement Adm...  2019   \n",
       "\n",
       "                                         Department  \\\n",
       "0             Boston Office of Emergency Management   \n",
       "1     Executive Office of Health and Human Services   \n",
       "2    Executive Office of Public Safety and Security   \n",
       "3   Executive Office for Administration and Finance   \n",
       "4        Massachusetts Bay Transit Authority (MBTA)   \n",
       "..                                              ...   \n",
       "59                                       Peabody PD   \n",
       "60                                   City of Boston   \n",
       "61                                   City of Boston   \n",
       "62                                   City of Boston   \n",
       "63            Drug Enforcement Administration (DEA)   \n",
       "\n",
       "                                          Description  \\\n",
       "0   Since 2003, the City of Boston and surrounding...   \n",
       "1   In 2019, we filed a public records request wit...   \n",
       "2   In 2019, we filed a public records request wit...   \n",
       "3   In 2019, we filed a public records request wit...   \n",
       "4   In 2019, we filed a public records request wit...   \n",
       "..                                                ...   \n",
       "59  In 2019, we filed a public records request wit...   \n",
       "60  In 2019, we filed a public records request wit...   \n",
       "61  In 2019, we filed a public records request wit...   \n",
       "62  In 2019, we filed a public records request wit...   \n",
       "63  In 2019, the ACLU of Massachusetts and the Nat...   \n",
       "\n",
       "   Files Transferred from Dropbox? Numbered requests & responses?  \\\n",
       "0                              Yes                            NaN   \n",
       "1                              Yes                            NaN   \n",
       "2                              Yes                            NaN   \n",
       "3                              Yes                            NaN   \n",
       "4                              Yes                            NaN   \n",
       "..                             ...                            ...   \n",
       "59                             Yes                            Yes   \n",
       "60            Waiting for response                            NaN   \n",
       "61                             Yes                            Yes   \n",
       "62                             Yes                            Yes   \n",
       "63            Waiting for response                            NaN   \n",
       "\n",
       "                                            Directory  \n",
       "0   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...  \n",
       "1   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...  \n",
       "2   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...  \n",
       "3   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...  \n",
       "4   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...  \n",
       "..                                                ...  \n",
       "59  /Users/lchambers/D4J/dir_for_d4j/D-town&city/D...  \n",
       "60  /Users/lchambers/D4J/dir_for_d4j/E-boston/E01-...  \n",
       "61  /Users/lchambers/D4J/dir_for_d4j/E-boston/E02-...  \n",
       "62  /Users/lchambers/D4J/dir_for_d4j/E-boston/E03-...  \n",
       "63  /Users/lchambers/D4J/dir_for_d4j/F-federal/F01...  \n",
       "\n",
       "[64 rows x 8 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_pages_df = pd.read_csv(\"db_face_rec.csv\")\n",
    "all_pages_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define the root URL for where your documents are on the server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "D4J_root_upload_url = \"https://data.aclum.org/wp-content/uploads/2021/02/\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write Wordpress page content for each page"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define page content template, using string formatting with replacement fields\n",
    "\n",
    "Here we mix raw HTML with the syntax for the Visual Composer editor in Wordpress to define a template that contains the common elements and structure of all pages.\n",
    "\n",
    "The replacement fields I include here include:\n",
    "- `description` - Text describing the current page\n",
    "- `agency` - Agency that received the public records request\n",
    "- `category` - Topic of request (e.g. surveillance, law enforcement)\n",
    "- `year` - Date of request\n",
    "- `n_docs` - Number of responsive documents returned\n",
    "- `request_li` - HTML with list of \"request\" documents\n",
    "- `response_li` - HTML with list of \"responses\" documents\n",
    "- `download_all_files` - button to download multiple files in a `.zip`\n",
    "- `docs_table` - HTML with table of all responsive documents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "page_content_template = '''\n",
    "[vc_row]\n",
    "    [vc_column column_width_percent=\"100\" gutter_size=\"3\" style=\"light\" overlay_alpha=\"50\" shift_x=\"0\" shift_y=\"0\" shift_y_down=\"0\" z_index=\"0\" medium_width=\"0\" mobile_width=\"0\" width=\"2/3\"][vc_column_text]\n",
    "        <h2><span style=\"color: #000000;\">Background</span></h2>\n",
    "        <span style=\"color: #777777;\">\n",
    "            {description}\n",
    "        </span>\n",
    "    [/vc_column_text][/vc_column]\n",
    "    \n",
    "    [vc_column column_width_percent=\"100\" position_vertical=\"middle\" gutter_size=\"3\" overlay_alpha=\"50\" shift_x=\"0\" shift_y=\"0\" shift_y_down=\"0\" z_index=\"0\" medium_width=\"0\" mobile_width=\"0\" width=\"1/3\"][vc_column_text border_color=\"transparent\"]\n",
    "        <table style=\"height: 72px; width: 99.8992%; border: none;\">\n",
    "            <tbody>\n",
    "                <tr style=\"height: 24px; background-color: #f2f2f2;\">\n",
    "                    <td style=\"width: 50%; height: 24px;\"><strong><span style=\"color: #808080; font-size: 16px;\"><span style=\"font-family: Poppins;\">Request Submitted To</span></span></strong></td>\n",
    "                    <td style=\"width: 50%; height: 24px; font-weight: 200 !important;\"><span style=\"color: #808080; font-size: 16px;\"><span style=\"font-family: Poppins;\">\n",
    "                        {agency}\n",
    "                    </span></span></td>\n",
    "                </tr>\n",
    "                <tr style=\"height: 24px;\">\n",
    "                    <td style=\"width: 50%; height: 24px;\"><strong><span style=\"color: #808080; font-size: 16px;\"><span style=\"font-family: Poppins;\">Category</span></span></strong></td>\n",
    "                    <td style=\"width: 50%; height: 24px; font-weight: 200 !important;\"><span style=\"color: #808080; font-size: 16px;\"><span style=\"font-family: Poppins;\">\n",
    "                        {category}\n",
    "                    </span></span></td>\n",
    "                </tr>\n",
    "                <tr style=\"height: 24px; background-color: #f2f2f2;\">\n",
    "                    <td style=\"width: 50%; height: 24px;\"><strong><span style=\"color: #808080; font-size: 16px;\"><span style=\"font-family: Poppins;\">Year Filed</span></span></strong></td>\n",
    "                    <td style=\"width: 50%; height: 24px; font-weight: 200 !important;\"><span style=\"color: #808080; font-size: 16px;\"><span style=\"font-family: Poppins;\">\n",
    "                        {year}\n",
    "                    </span></span></td>\n",
    "                </tr>\n",
    "                <tr>\n",
    "                    <td style=\"width: 50%;\"><strong><span style=\"color: #808080; font-size: 16px;\"><span style=\"font-family: Poppins;\">Number of Documents Received</span></span></strong></td>\n",
    "                    <td style=\"width: 50%; font-weight: 200 !important;\"><span style=\"color: #808080; font-size: 16px;\"><span style=\"font-family: Poppins;\">\n",
    "                        {n_docs}\n",
    "                    </span></span></td>\n",
    "                </tr>\n",
    "            </tbody>\n",
    "        </table>\n",
    "    [/vc_column_text][/vc_column]\n",
    "[/vc_row]\n",
    "\n",
    "[vc_row]\n",
    "    [vc_column width=\"1/2\"][vc_column_text]\n",
    "        <h2>We said...</h2>\n",
    "        <ul>\n",
    "            {request_li}\n",
    "        </ul>\n",
    "    [/vc_column_text][/vc_column]\n",
    "\n",
    "    [vc_column width=\"1/2\"][vc_column_text]\n",
    "        <h2>They said...</h2>\n",
    "        <ul>\n",
    "            {response_li}\n",
    "        </ul>\n",
    "    [/vc_column_text][/vc_column]\n",
    "[/vc_row]\n",
    "\n",
    "[vc_row][vc_column width=\"1/1\"][vc_column_text]\n",
    "    <h2>Responsive Documents</h2>\n",
    "    {download_all_files}\n",
    "    {docs_table}\n",
    "[/vc_column_text][/vc_column][/vc_row]\n",
    "\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Map URLs to .zip files for pages with a ton of documents\n",
    "For pages with many documents, I manually compressed all documents into a .zip file and uploaded to Google Drive. Here we map the URLs to the file downloads to different page IDs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "zipped_doc_pages = {\n",
    "    \"A01\": \"https://drive.google.com/u/1/uc?id=1Cd6sY5J3oyjL4ydj90tXL4gsTsA0LJUe&export=download\",\n",
    "    \"A06\": \"https://drive.google.com/u/1/uc?id=14G6MtdDB9sCH0ujYlCVbpj5EtvWf-zgr&export=download\", \n",
    "    \"A11a\": \"https://drive.google.com/u/1/uc?id=1c6DDgkfue-GCFHcZlIC6gob50uRKt9lg&export=download\", \n",
    "    \"A11b\": \"https://drive.google.com/u/1/uc?id=1j9jh6iJo-o2KJssiQbUkkajwxYcmOGTS&export=download\", \n",
    "    \"A11t\": \"https://drive.google.com/u/1/uc?id=1BPRgoVsJbgit35kaYhuW2xq14bCAxZkD&export=download\", \n",
    "    \"C11\": \"https://drive.google.com/u/1/uc?id=1uzHJ4WAfmnD5lsuRzT-9xTYr0BwDwsjM&export=download\", \n",
    "    \"D01\": \"https://drive.google.com/u/1/uc?id=19hj5afd9pWH7-zevXa52TA4Tpg4zAman&export=download\", \n",
    "    \"D05\": \"https://drive.google.com/u/1/uc?id=1Gn1BpII8Rm3BhYwGFu8gBVohxvRXgnMt&export=download\", \n",
    "    \"D10\": \"https://drive.google.com/u/1/uc?id=1XlWZh16DeCyZYj1eTjcAJCn9xSeMdTUp&export=download\", \n",
    "}\n",
    "\n",
    "regex_zipped_docs = '|'.join(zipped_doc_pages.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to turn spreadsheet row into page content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_wordpress_page(row):\n",
    "    page_metadata = row\n",
    "    \n",
    "    if page_metadata[\"id\"] in [\"A11a-solicitations\", \"A11b-unresponsive\", \"D01-unresponsive\"]:\n",
    "        # In reality, use custom functions to write tricky pages.\n",
    "        # In this example, just skip.\n",
    "        return \"\"\n",
    "    \n",
    "    # Basic metadata\n",
    "    description = page_metadata[\"Description\"]\n",
    "    agency = page_metadata[\"Department\"]\n",
    "    category = \"Surveillance\"\n",
    "    year = page_metadata[\"Year\"]\n",
    "    page_dir = page_metadata[\"Directory\"]\n",
    "    \n",
    "    # Create li tag for each request doc\n",
    "    requests = glob.glob(os.path.join(page_dir, \"request\", \"**\"), recursive=True)\n",
    "    requests = sorted([r for r in requests if os.path.isfile(r)])\n",
    "    \n",
    "    requests_li = \"\" \n",
    "    for r in requests:\n",
    "        url = D4J_root_upload_url + \\\n",
    "                r.split(\"dir_for_d4j/\")[-1].replace(\" \", \"%20\").replace(\"#\", \"\").replace(\"''\", \"\")\n",
    "        title = os.path.basename(r)\n",
    "        title = os.path.splitext(title)[0]\n",
    "        requests_li += '<li><a href=\"{url}\">{title}</a></li>'.format(url=url, title=title)\n",
    "        \n",
    "    # Create li tag for each response doc\n",
    "    responses = glob.glob(os.path.join(page_dir, \"response\", \"**\"), recursive=True)\n",
    "    responses = sorted([r for r in responses if os.path.isfile(r)])\n",
    "\n",
    "    if len(responses) == 0:\n",
    "        responses_li = \"<i>No formal written acknowledgement or response was received.</i>\"\n",
    "    else:\n",
    "        responses_li = \"\"\n",
    "        for r in responses:\n",
    "            url = D4J_root_upload_url + \\\n",
    "                    r.split(\"dir_for_d4j/\")[-1].replace(\" \", \"%20\").replace(\"#\", \"\").replace(\"''\", \"\")\n",
    "            title = os.path.basename(r)\n",
    "            title = os.path.splitext(title)[0]\n",
    "            responses_li += '<li><a href=\"{url}\">{title}</a></li>'.format(url=url, title=title)\n",
    "        \n",
    "    # Count total number of documents\n",
    "    docs = glob.glob(os.path.join(page_dir, \"documents\", \"**\"), recursive=True)\n",
    "    n_docs = len([d for d in docs if os.path.isfile(d)])\n",
    "    \n",
    "    # Add button to download ZIP of all docs, if applicable\n",
    "    zipped_id = re.findall(regex_zipped_docs, page_metadata[\"id\"])\n",
    "    if zipped_id:\n",
    "        url = zipped_doc_pages[zipped_id[0]]\n",
    "        url = urllib.parse.quote_plus(url)\n",
    "        download_all_files = '[vc_button border_width=\"0\" link=\"url:{}|||\"]Download all {} docs from Google Drive[/vc_button]'.format(url, n_docs)\n",
    "    else: \n",
    "        download_all_files = \"\"\n",
    "    \n",
    "    # Create tr for each document\n",
    "    if n_docs == 0:\n",
    "        docs_table = \"<i>No responsive documents returned.</i>\"\n",
    "    else:\n",
    "        docs_table = \"\"\n",
    "        for i, d in enumerate([d for d in docs if os.path.isfile(d)]):\n",
    "\n",
    "            row_color = \" background-color: #f2f2f2;\" if i%2 == 1 else \"\"\n",
    "\n",
    "            url = D4J_root_upload_url + \\\n",
    "                    d.split(\"dir_for_d4j/\")[-1].replace(\" \", \"%20\").replace(\"#\", \"\").replace(\"''\", \"\")\n",
    "\n",
    "            filename = os.path.basename(d)\n",
    "            title = os.path.splitext(filename)[0]\n",
    "            extension = os.path.splitext(filename)[1].upper()[1:]\n",
    "\n",
    "            size_bytes = os.path.getsize(d)\n",
    "            if size_bytes < 1e6:\n",
    "                size = \"{:.1f} KB\".format(size_bytes / 1e3)\n",
    "            elif size_bytes < 1e9:\n",
    "                size = \"{:.1f} MB\".format(size_bytes / 1e6)\n",
    "            elif size_bytes >= 1e9:\n",
    "                size = \"{:.1f} GB\".format(size_bytes / 1e9)\n",
    "\n",
    "            docs_table += '''\n",
    "            <tr style=\"height: 24px;{row_color}\">\n",
    "                <td style=\"width: 5.40787%; height: 24px;\">\n",
    "                    <span style=\"font-family: Poppins; font-size: 16px;\">\n",
    "                        <strong><span style=\"color: #808080;\">{i_doc}</span></strong>\n",
    "                    </span>\n",
    "                </td>\n",
    "                <td style=\"width: 40.8948%; height: 24px; font-weight: 200 !important;\">\n",
    "                    <span style=\"color: #006cff; font-family: Vollkorn; font-size: 16px;\">\n",
    "                        <a style=\"color: #006cff;\" href=\"{url}\">{title}</a>\n",
    "                    </span>\n",
    "                </td>\n",
    "                <!--<td style=\"width: 26.8486%; font-weight: 200 !important; height: 24px;\">\n",
    "                    <span style=\"color: #808080; font-size: 16px; font-family: Vollkorn;\">Description</span>\n",
    "                </td>-->\n",
    "                <td style=\"width: 13.4243%; font-weight: 200 !important; height: 24px;\">\n",
    "                    <span style=\"color: #808080; font-size: 16px; font-family: Vollkorn;\">{extension}</span>\n",
    "                </td>\n",
    "                <td style=\"width: 13.4243%; font-weight: 200 !important; height: 24px;\">\n",
    "                    <span style=\"color: #808080; font-size: 16px; font-family: Vollkorn;\">{size}</span>\n",
    "                </td>\n",
    "            </tr>\n",
    "            '''.format(row_color=row_color, i_doc=i+1, url=url, title=title,\n",
    "                      extension=extension, size=size)\n",
    "\n",
    "        docs_table = docs_table.replace(\"\\n\", \"\")\n",
    "        \n",
    "        docs_table = '''\n",
    "        <table style=\"height: 168px; width: 99.8992%; border: none;\">\n",
    "            <tbody>\n",
    "                <tr style=\"height: 24px;\">\n",
    "                    <td style=\"width: 5.40787%; height: 24px;\">\n",
    "                        <span style=\"font-size: 16px;\"><strong>\n",
    "                            <span style=\"color: #808080;\">\n",
    "                                <span style=\"font-family: Poppins;\">#</span>\n",
    "                            </span>\n",
    "                        </strong></span>\n",
    "                    </td>\n",
    "                    <td style=\"width: 40.8948%; height: 24px; font-weight: 200 !important;\">\n",
    "                        <span style=\"font-size: 16px;\"><strong>\n",
    "                            <span style=\"color: #808080;\">\n",
    "                                <span style=\"font-family: Poppins;\">File Name</span>\n",
    "                            </span>\n",
    "                        </strong></span>\n",
    "                    </td>\n",
    "                    <!--<td style=\"width: 26.8486%; font-weight: 200 !important; height: 24px;\">\n",
    "                        <span style=\"font-size: 16px;\"><strong>\n",
    "                            <span style=\"color: #808080;\">\n",
    "                                <span style=\"font-family: Poppins;\">Description</span>\n",
    "                            </span>\n",
    "                        </strong></span>    \n",
    "                    </td>-->\n",
    "                    <td style=\"width: 13.4243%; font-weight: 200 !important; height: 24px;\">\n",
    "                        <span style=\"font-size: 16px;\"><strong>\n",
    "                            <span style=\"color: #808080;\">\n",
    "                                <span style=\"font-family: Poppins;\">File Type</span>\n",
    "                            </span>\n",
    "                        </strong></span>\n",
    "                    </td>\n",
    "                    <td style=\"width: 13.4243%; font-weight: 200 !important; height: 24px;\">\n",
    "                        <span style=\"font-size: 16px;\"><strong>\n",
    "                            <span style=\"color: #808080;\">\n",
    "                                <span style=\"font-family: Poppins;\">File Size</span>\n",
    "                            </span>\n",
    "                        </strong></span>\n",
    "                    </td>\n",
    "                </tr>\n",
    "                {}\n",
    "            </tbody>\n",
    "        </table>\n",
    "        '''.format(docs_table)\n",
    "    \n",
    "    # Put it all together!\n",
    "    page_content = page_content_template.format(\n",
    "        description=description, agency=agency, category=category, \n",
    "        year=year, n_docs=n_docs, request_li=requests_li, \n",
    "        response_li=responses_li, download_all_files=download_all_files, \n",
    "        docs_table=docs_table)\n",
    "\n",
    "    return page_content.replace(\"\\n\", \"\").replace(\"\\t\", \"\").replace('\"', r'\\\"')\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use `.apply()` to write content for all pages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_page_content = all_pages_df.apply(lambda row: write_wordpress_page(row), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>id</th>\n",
       "      <th>Title</th>\n",
       "      <th>Year</th>\n",
       "      <th>Department</th>\n",
       "      <th>Description</th>\n",
       "      <th>Files Transferred from Dropbox?</th>\n",
       "      <th>Numbered requests &amp; responses?</th>\n",
       "      <th>Directory</th>\n",
       "      <th>page_content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A01-lantel-OEM</td>\n",
       "      <td>Lantel at the Boston Office of Emergency Manag...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Boston Office of Emergency Management</td>\n",
       "      <td>Since 2003, the City of Boston and surrounding...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A02-lantel-EOHHS</td>\n",
       "      <td>Lantel at the Executive Office of Health and H...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Executive Office of Health and Human Services</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A03-lantel-EOPSS</td>\n",
       "      <td>Lantel at the Executive Office of Public Safet...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Executive Office of Public Safety and Security</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A04-lantel-EOAF</td>\n",
       "      <td>Lantel at the Executive Office for Administrat...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Executive Office for Administration and Finance</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A05-lantel-MBTA</td>\n",
       "      <td>Lantel at the Massachusetts Bay Transit Author...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Massachusetts Bay Transit Authority (MBTA)</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/A-privatecomp...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>59</td>\n",
       "      <td>D11-peabody</td>\n",
       "      <td>Facial Recognition at the Peabody PD</td>\n",
       "      <td>2019</td>\n",
       "      <td>Peabody PD</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/D-town&amp;city/D...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>E01-camera-locs</td>\n",
       "      <td>Location of Surveillance Cameras Across Boston</td>\n",
       "      <td>2019</td>\n",
       "      <td>City of Boston</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Waiting for response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/E-boston/E01-...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>61</td>\n",
       "      <td>E02-videofootage</td>\n",
       "      <td>Video Surveillance Data Sharing in Boston</td>\n",
       "      <td>2019</td>\n",
       "      <td>City of Boston</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/E-boston/E02-...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>62</td>\n",
       "      <td>E03-camerastrategymeetings</td>\n",
       "      <td>Boston Security Camera Strategy Meetings</td>\n",
       "      <td>2019</td>\n",
       "      <td>City of Boston</td>\n",
       "      <td>In 2019, we filed a public records request wit...</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/E-boston/E03-...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>63</td>\n",
       "      <td>F01-dea</td>\n",
       "      <td>Facial Recognition at the Drug Enforcement Adm...</td>\n",
       "      <td>2019</td>\n",
       "      <td>Drug Enforcement Administration (DEA)</td>\n",
       "      <td>In 2019, the ACLU of Massachusetts and the Nat...</td>\n",
       "      <td>Waiting for response</td>\n",
       "      <td>NaN</td>\n",
       "      <td>/Users/lchambers/D4J/dir_for_d4j/F-federal/F01...</td>\n",
       "      <td>[vc_row][vc_column column_width_percent=\\\"100\\...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>64 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            id  \\\n",
       "0               A01-lantel-OEM   \n",
       "1             A02-lantel-EOHHS   \n",
       "2             A03-lantel-EOPSS   \n",
       "3              A04-lantel-EOAF   \n",
       "4              A05-lantel-MBTA   \n",
       "..                         ...   \n",
       "59                 D11-peabody   \n",
       "60             E01-camera-locs   \n",
       "61            E02-videofootage   \n",
       "62  E03-camerastrategymeetings   \n",
       "63                     F01-dea   \n",
       "\n",
       "                                                Title  Year  \\\n",
       "0   Lantel at the Boston Office of Emergency Manag...  2019   \n",
       "1   Lantel at the Executive Office of Health and H...  2019   \n",
       "2   Lantel at the Executive Office of Public Safet...  2019   \n",
       "3   Lantel at the Executive Office for Administrat...  2019   \n",
       "4   Lantel at the Massachusetts Bay Transit Author...  2019   \n",
       "..                                                ...   ...   \n",
       "59               Facial Recognition at the Peabody PD  2019   \n",
       "60     Location of Surveillance Cameras Across Boston  2019   \n",
       "61          Video Surveillance Data Sharing in Boston  2019   \n",
       "62           Boston Security Camera Strategy Meetings  2019   \n",
       "63  Facial Recognition at the Drug Enforcement Adm...  2019   \n",
       "\n",
       "                                         Department  \\\n",
       "0             Boston Office of Emergency Management   \n",
       "1     Executive Office of Health and Human Services   \n",
       "2    Executive Office of Public Safety and Security   \n",
       "3   Executive Office for Administration and Finance   \n",
       "4        Massachusetts Bay Transit Authority (MBTA)   \n",
       "..                                              ...   \n",
       "59                                       Peabody PD   \n",
       "60                                   City of Boston   \n",
       "61                                   City of Boston   \n",
       "62                                   City of Boston   \n",
       "63            Drug Enforcement Administration (DEA)   \n",
       "\n",
       "                                          Description  \\\n",
       "0   Since 2003, the City of Boston and surrounding...   \n",
       "1   In 2019, we filed a public records request wit...   \n",
       "2   In 2019, we filed a public records request wit...   \n",
       "3   In 2019, we filed a public records request wit...   \n",
       "4   In 2019, we filed a public records request wit...   \n",
       "..                                                ...   \n",
       "59  In 2019, we filed a public records request wit...   \n",
       "60  In 2019, we filed a public records request wit...   \n",
       "61  In 2019, we filed a public records request wit...   \n",
       "62  In 2019, we filed a public records request wit...   \n",
       "63  In 2019, the ACLU of Massachusetts and the Nat...   \n",
       "\n",
       "   Files Transferred from Dropbox? Numbered requests & responses?  \\\n",
       "0                              Yes                            NaN   \n",
       "1                              Yes                            NaN   \n",
       "2                              Yes                            NaN   \n",
       "3                              Yes                            NaN   \n",
       "4                              Yes                            NaN   \n",
       "..                             ...                            ...   \n",
       "59                             Yes                            Yes   \n",
       "60            Waiting for response                            NaN   \n",
       "61                             Yes                            Yes   \n",
       "62                             Yes                            Yes   \n",
       "63            Waiting for response                            NaN   \n",
       "\n",
       "                                            Directory  \\\n",
       "0   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...   \n",
       "1   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...   \n",
       "2   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...   \n",
       "3   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...   \n",
       "4   /Users/lchambers/D4J/dir_for_d4j/A-privatecomp...   \n",
       "..                                                ...   \n",
       "59  /Users/lchambers/D4J/dir_for_d4j/D-town&city/D...   \n",
       "60  /Users/lchambers/D4J/dir_for_d4j/E-boston/E01-...   \n",
       "61  /Users/lchambers/D4J/dir_for_d4j/E-boston/E02-...   \n",
       "62  /Users/lchambers/D4J/dir_for_d4j/E-boston/E03-...   \n",
       "63  /Users/lchambers/D4J/dir_for_d4j/F-federal/F01...   \n",
       "\n",
       "                                         page_content  \n",
       "0   [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "1   [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "2   [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "3   [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "4   [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "..                                                ...  \n",
       "59  [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "60  [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "61  [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "62  [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "63  [vc_row][vc_column column_width_percent=\\\"100\\...  \n",
       "\n",
       "[64 rows x 9 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add as new column to existing datarame\n",
    "all_pages_df[\"page_content\"] = all_page_content\n",
    "all_pages_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write PHP snippets for all pages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To publish the page content written above, we can use Wordpress' `wp_insert_post()` function. Similarly, we'll use Python to customize a template PHP function call for each page.\n",
    "\n",
    "We're going to write the following PHP command for each page:\n",
    "\n",
    "```PHP\n",
    "// Add page after wp_loaded\n",
    "add_action( 'wp_loaded', function () {{ \n",
    "\t\n",
    "\t$post_content = \"{content}\";\n",
    "    \n",
    "    $title = \"{title}\";\n",
    "\n",
    "\t$new_page = array(\n",
    "\t\t\t'comment_status' => 'close',\n",
    "\t\t\t'ping_status'    => 'close',\n",
    "\t\t\t'post_title'     => $title,\n",
    "\t\t\t'post_name'      => strtolower(str_replace(' ', '-', trim( $title ))),\n",
    "\t\t\t'post_status'    => 'publish',\n",
    "            'post_password'  => 'password', // Don't use this!\n",
    "\t\t\t'post_content'   => $post_content,\n",
    "\t\t\t'post_type'      => 'page',\n",
    "\t\t\t'post_parent'    => '{parent_id}',\n",
    "\t\t\t);\n",
    "\n",
    "    // Check if the page already exists\n",
    "\t$check_page_exist = get_page_by_title( $title, 'OBJECT', 'page');\n",
    "\n",
    "\tif(empty($check_page_exist)) {{\n",
    "\t\t$page_id = wp_insert_post( $new_page );\n",
    "\n",
    "\t\t// Add custom-auto category\n",
    "\t\twp_set_object_terms($page_id, array(\"custom-auto\"), \"page_category\");\n",
    "\t}} else {{\n",
    "\t\terror_log($title . \" page already exists; not inserting new post\");\n",
    "\t}}\n",
    "}});\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "php_snippet_template = '''\n",
    "// Add page after wp_loaded\n",
    "add_action( 'wp_loaded', function () {{ \n",
    "\t\n",
    "\t$post_content = \"{content}\";\n",
    "\n",
    "\t$title = \"{title}\";\n",
    "\n",
    "\t$new_page = array(\n",
    "\t\t\t'comment_status' => 'close',\n",
    "\t\t\t'ping_status'    => 'close',\n",
    "\t\t\t'post_title'     => $title,\n",
    "\t\t\t'post_name'      => strtolower(str_replace(' ', '-', trim( $title ))),\n",
    "\t\t\t'post_status'    => 'publish',\n",
    "            'post_password'  => 'password', // Don't use this!\n",
    "\t\t\t'post_content'   => $post_content,\n",
    "\t\t\t'post_type'      => 'page',\n",
    "\t\t\t'post_parent'    => '{parent_id}',\n",
    "\t\t\t);\n",
    "\n",
    "\t// Check if the page already exists\n",
    "\t$check_page_exist = get_page_by_title( $title, 'OBJECT', 'page');\n",
    "\n",
    "\tif(empty($check_page_exist)) {{\n",
    "\t\t$page_id = wp_insert_post( $new_page );\n",
    "\n",
    "\t\t// Add custom-auto category\n",
    "\t\twp_set_object_terms($page_id, array(\"custom-auto\"), \"page_category\");\n",
    "\t}} else {{\n",
    "\t\terror_log($title . \" page already exists; not inserting new post\");\n",
    "\t}}\n",
    "}});\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Map parent page IDs\n",
    "We wanted certain pages to be child pages of other pages, so we mapped the page ID to the alphabetic code (A-F) here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define IDs of parent pages (dummy IDs)\n",
    "parent_id_key = {\n",
    "    'A-privatecompanies': 1,\n",
    "    'A11-clearview': 2,\n",
    "    'B-schools': 3,\n",
    "    'C-stateagencies': 4,\n",
    "    'D-town&city': 5,\n",
    "    'E-boston': 6,\n",
    "    'F-federal': 7 \n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to turn spreadsheet row into PHP command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_php_snippet(row):\n",
    "    # Determine alphabetic code for parent page ID from directory path\n",
    "    parent_dir = os.path.dirname(os.path.dirname(row[\"Directory\"]))\n",
    "    parent_dir = os.path.basename(parent_dir)\n",
    "    parent_id = parent_id_key[parent_dir]\n",
    "    \n",
    "    # Fill in PHP command with  content, title, and parent ID for each page\n",
    "    php_snippet = php_snippet_template.format(\n",
    "        content=row[\"page_content\"],\n",
    "        title=row[\"Title\"],\n",
    "        parent_id=parent_id\n",
    "    )\n",
    "    \n",
    "    return php_snippet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Use `.apply()` to write PHP command for all pages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_snippets = all_pages_df.apply(lambda row: create_php_snippet(row), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write all of the PHP commands out to a txt file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write them!!!\n",
    "with open(\"insert_all_pages_php_snippet.txt\", \"w+\") as fo:\n",
    "    for s in all_snippets:\n",
    "        fo.write(s)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "272.391px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
 }