magazine_authors_merge_cleaned.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "date_today = datetime.today().strftime('%Y-%m-%d')\n",
    "\n",
    "cleaned_deduped_authors_filename = \"data/MCK-CX_2022-10-09magazine_authors_newly_merged.csv\"\n",
    "newly_parsed_authors_filename = \"data/magazine_authors_parsed.csv\"\n",
    "new_authors_batch_filename = f\"data/magazine_authors_new_batch-{date_today}.xlsx\"\n",
    "newly_merged_authors_filename = f\"data/magazine_authors-{date_today}-merged.xlsx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "cleaned_deduped_authors = pd.read_csv(cleaned_deduped_authors_filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "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>drupal_full_name</th>\n",
       "      <th>drupal_author_id</th>\n",
       "      <th>duplicate of ID</th>\n",
       "      <th>given_name</th>\n",
       "      <th>family_name</th>\n",
       "      <th>organization_name</th>\n",
       "      <th>meeting_name</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Carl Abbott</td>\n",
       "      <td>1352</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Carl</td>\n",
       "      <td>Abbott</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Marge Abbott</td>\n",
       "      <td>2931</td>\n",
       "      <td>343.0</td>\n",
       "      <td>Margery Post</td>\n",
       "      <td>Abbott</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Margery  Post Abbott</td>\n",
       "      <td>3430</td>\n",
       "      <td>343.0</td>\n",
       "      <td>Margery Post</td>\n",
       "      <td>Abbott</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Margery Abbott</td>\n",
       "      <td>3267</td>\n",
       "      <td>343.0</td>\n",
       "      <td>Margery Post</td>\n",
       "      <td>Abbott</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Margery Post Abbott</td>\n",
       "      <td>343</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Margery Post</td>\n",
       "      <td>Abbott</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       drupal_full_name  drupal_author_id  duplicate of ID    given_name  \\\n",
       "0           Carl Abbott              1352              NaN          Carl   \n",
       "1          Marge Abbott              2931            343.0  Margery Post   \n",
       "2  Margery  Post Abbott              3430            343.0  Margery Post   \n",
       "3        Margery Abbott              3267            343.0  Margery Post   \n",
       "4   Margery Post Abbott               343              NaN  Margery Post   \n",
       "\n",
       "  family_name organization_name meeting_name Notes  \n",
       "0      Abbott               NaN          NaN   NaN  \n",
       "1      Abbott               NaN          NaN   NaN  \n",
       "2      Abbott               NaN          NaN   NaN  \n",
       "3      Abbott               NaN          NaN   NaN  \n",
       "4      Abbott               NaN          NaN   NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_deduped_authors.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2774, 8)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_deduped_authors.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "newly_parsed_authors = pd.read_csv(newly_parsed_authors_filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "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>given_name</th>\n",
       "      <th>family_name</th>\n",
       "      <th>drupal_full_name</th>\n",
       "      <th>drupal_author_id</th>\n",
       "      <th>civicrm_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A. N.</td>\n",
       "      <td>Whitehead</td>\n",
       "      <td>A. N. Whitehead</td>\n",
       "      <td>1832</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A.</td>\n",
       "      <td>S.</td>\n",
       "      <td>A. S.</td>\n",
       "      <td>1597</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A. Sidney</td>\n",
       "      <td>Wright</td>\n",
       "      <td>A. Sidney Wright</td>\n",
       "      <td>1789</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A. Stanley</td>\n",
       "      <td>Thompson</td>\n",
       "      <td>A. Stanley Thompson</td>\n",
       "      <td>2801</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A.F.</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>A.F. Anderson</td>\n",
       "      <td>2615</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   given_name family_name     drupal_full_name  drupal_author_id  civicrm_id\n",
       "0       A. N.   Whitehead      A. N. Whitehead              1832         NaN\n",
       "1          A.          S.                A. S.              1597         NaN\n",
       "2   A. Sidney      Wright     A. Sidney Wright              1789         NaN\n",
       "3  A. Stanley    Thompson  A. Stanley Thompson              2801         NaN\n",
       "4        A.F.    Anderson        A.F. Anderson              2615         NaN"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newly_parsed_authors.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3106, 5)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newly_parsed_authors.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select new authors (and export for review)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# select newly parsed authors that are NOT in the cleaned deduped authors\n",
    "new_authors = newly_parsed_authors[~newly_parsed_authors[\"drupal_author_id\"].isin(cleaned_deduped_authors[\"drupal_author_id\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_authors.to_excel(new_authors_batch_filename, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge new authors into existing spreadsheet\n",
    "\n",
    "We will use the Drupal ID to skip existing rows and add only new authors to the merged spreadsheet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "newly_merged_authors_for_export = pd.concat([cleaned_deduped_authors, new_authors])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "newly_merged_authors_for_export.sort_values(by=\"drupal_full_name\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# make sure the row counts match\n",
    "assert new_authors.shape[0] + cleaned_deduped_authors.shape[0] == newly_merged_authors_for_export.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "newly_merged_authors_for_export.to_excel(newly_merged_authors_filename, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.1"
  },
  "vscode": {
   "interpreter": {
    "hash": "7ef637ac01b287f33c3e5e1411200011e098537af1286f40ad77c2a7db109a89"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}