notebooks/combine_record_rows.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combine records when information is split across rows\n",
    "\n",
    "Sometimes data for the same record is split across subsequent rows. This is particularly the case when you extract data from a PDF table using a tool like Tabula."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/ghing/.local/share/virtualenvs/python-data-cheatsheet-nEkgY1LY/lib/python3.6/site-packages/pandas/compat/__init__.py:84: UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError.\n",
      "  warnings.warn(msg)\n",
      "/Users/ghing/.local/share/virtualenvs/python-data-cheatsheet-nEkgY1LY/lib/python3.6/site-packages/pandas/compat/__init__.py:84: UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError.\n",
      "  warnings.warn(msg)\n"
     ]
    }
   ],
   "source": [
    "# Setup\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create some sample data\n",
    "\n",
    "In this data, it seems like the `message` value for the record with index 1 continues in the next row. However, there is no common identifier that can tie these rows together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>date</th>\n",
       "      <th>facility</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A log message.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A longer log message</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>that continues onto a second row.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1/3/2019</td>\n",
       "      <td>Facility 3</td>\n",
       "      <td>Another log message.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date    facility                            message\n",
       "0  1/2/2019  Facility 1                     A log message.\n",
       "1  1/2/2019  Facility 1              A longer log message \n",
       "2      None        None  that continues onto a second row.\n",
       "3  1/3/2019  Facility 3               Another log message."
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data = [\n",
    "    ('1/2/2019', 'Facility 1', \"A log message.\"),\n",
    "    ('1/2/2019', 'Facility 1', \"A longer log message \"),\n",
    "    (None, None, \"that continues onto a second row.\"),\n",
    "    ('1/3/2019', 'Facility 3', \"Another log message.\"),\n",
    "    \n",
    "]\n",
    "columns = ['date', 'facility', 'message']\n",
    "\n",
    "data = pd.DataFrame.from_records(raw_data, columns=columns)\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add an ID\n",
    "\n",
    "We'll start by adding an arbitrary ID to each row. We can just use the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>date</th>\n",
       "      <th>facility</th>\n",
       "      <th>message</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A log message.</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A longer log message</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>that continues onto a second row.</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1/3/2019</td>\n",
       "      <td>Facility 3</td>\n",
       "      <td>Another log message.</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date    facility                            message  id\n",
       "0  1/2/2019  Facility 1                     A log message.   0\n",
       "1  1/2/2019  Facility 1              A longer log message    1\n",
       "2      None        None  that continues onto a second row.   2\n",
       "3  1/3/2019  Facility 3               Another log message.   3"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['id'] = data.index\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove the ID for rows that continue previous records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>date</th>\n",
       "      <th>facility</th>\n",
       "      <th>message</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A log message.</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A longer log message</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>that continues onto a second row.</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1/3/2019</td>\n",
       "      <td>Facility 3</td>\n",
       "      <td>Another log message.</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date    facility                            message    id\n",
       "0  1/2/2019  Facility 1                     A log message.     0\n",
       "1  1/2/2019  Facility 1              A longer log message      1\n",
       "2      None        None  that continues onto a second row.  None\n",
       "3  1/3/2019  Facility 3               Another log message.     3"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['id'] = np.where(pd.isna(data['date']), None, data['id'])\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fill the now missing IDs with the ID of the previous record\n",
    "\n",
    "After doing this, the continued record will share an ID with the row that has most of the rest of the information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>date</th>\n",
       "      <th>facility</th>\n",
       "      <th>message</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A log message.</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A longer log message</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>that continues onto a second row.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1/3/2019</td>\n",
       "      <td>Facility 3</td>\n",
       "      <td>Another log message.</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date    facility                            message  id\n",
       "0  1/2/2019  Facility 1                     A log message.   0\n",
       "1  1/2/2019  Facility 1              A longer log message    1\n",
       "2      None        None  that continues onto a second row.   1\n",
       "3  1/3/2019  Facility 3               Another log message.   3"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['id'] = data['id'].fillna(method='ffill')\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use grouping and aggregation to merge the rows\n",
    "\n",
    "We'll group by the synthetic ID that we created and then use the [form of the agg method that lets us specify separate aggregation functions for each column](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html#applying-different-functions-to-dataframe-columns). I'm not entirely sure what the type of the data is that gets passed to the functions that are the values of the dictionary, but I think they're `Series`.\n",
    "\n",
    "For the columns that don't continue on multiple rows, we use a lambda function that returns the value from the first row in the group. For the `message` column that is continued, we use the expression `' '.join`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>date</th>\n",
       "      <th>facility</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A log message.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A longer log message  that continues onto a se...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1/3/2019</td>\n",
       "      <td>Facility 3</td>\n",
       "      <td>Another log message.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date    facility                                            message\n",
       "id                                                                         \n",
       "0   1/2/2019  Facility 1                                     A log message.\n",
       "1   1/2/2019  Facility 1  A longer log message  that continues onto a se...\n",
       "3   1/3/2019  Facility 3                               Another log message."
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = data.groupby('id')\\\n",
    "    .agg({\n",
    "        'date': lambda x: x.iloc[0],\n",
    "        'facility': lambda x: x.iloc[0],\n",
    "        'message': ' '.join,\n",
    "    })\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove the temporary ID we used for grouping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>date</th>\n",
       "      <th>facility</th>\n",
       "      <th>message</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A log message.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1/2/2019</td>\n",
       "      <td>Facility 1</td>\n",
       "      <td>A longer log message  that continues onto a se...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1/3/2019</td>\n",
       "      <td>Facility 3</td>\n",
       "      <td>Another log message.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date    facility                                            message\n",
       "0  1/2/2019  Facility 1                                     A log message.\n",
       "1  1/2/2019  Facility 1  A longer log message  that continues onto a se...\n",
       "2  1/3/2019  Facility 3                               Another log message."
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = data.reset_index(drop=True)\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "formats": "ipynb,Rmd"
  },
  "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}