notebooks/pivot_rows_into_columns.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a3b2aa8c-2b1d-4a02-9e22-ee4bbf13fe39",
   "metadata": {},
   "source": [
    "# Pivot values in rows into separate columns\n",
    "\n",
    "It's often best to have [tidy data](https://r4ds.had.co.nz/tidy-data.html). However, sometimes when standardizing data that reflects similar events across different sources, I find it preferable to have one row represent the same thing. For example, if standardizing arrest records across jurisdictions, some jurisdictions might have one row per person arrested, while another jurisdiction might have one row per charge. In this case, I might want to pivot the charges into multiple columns like `charge`, `charge_2`, `charge_3`, etc. This isn't ideal, but sometimes this is the best option.\n",
    "\n",
    "Here's how to do it. It's worth checking out the Pandas documentation on [reshaping and pivot tables](https://pandas.pydata.org/docs/user_guide/reshaping.html) first to get familiar with some of the methods that take data from wide to long and back again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "df072eb9-06f0-4772-96dd-de9642fa82a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup\n",
    "\n",
    "from io import StringIO\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "798a0fd8-c9ee-4a4e-ae58-6fb4e8f3343a",
   "metadata": {},
   "source": [
    "Consider this extract from a city police department's arrest data. I've removed personally identifying details. As a result, there are some rows that end up being duplicates. In this example, `unique_id` identifies the person arrested."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e66d0c52-41f2-4d80-b9de-416e69548983",
   "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>unique_id</th>\n",
       "      <th>penal_law_code</th>\n",
       "      <th>penal_law_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.12</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.12</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.12</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.12</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.12</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.12</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    unique_id  penal_law_code                  penal_law_description\n",
       "0      776187         2923.12             Carrying Concealed Weapons\n",
       "1      776187         2923.12             Carrying Concealed Weapons\n",
       "2      776187         2923.12             Carrying Concealed Weapons\n",
       "3      776187         2923.12             Carrying Concealed Weapons\n",
       "4      776187         2923.12             Carrying Concealed Weapons\n",
       "5      776187         2923.12             Carrying Concealed Weapons\n",
       "6      776187         2923.13  Having Weapons While Under Disability\n",
       "7      776187         2923.13  Having Weapons While Under Disability\n",
       "8      776187         2923.13  Having Weapons While Under Disability\n",
       "9      776187         2923.13  Having Weapons While Under Disability\n",
       "10     776187         2923.13  Having Weapons While Under Disability\n",
       "11     776187         2923.13  Having Weapons While Under Disability"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Set up some example data\n",
    "example_csv = \"\"\"unique_id,penal_law_code,penal_law_description\n",
    "776187,2923.12,Carrying Concealed Weapons\n",
    "776187,2923.12,Carrying Concealed Weapons\n",
    "776187,2923.12,Carrying Concealed Weapons\n",
    "776187,2923.12,Carrying Concealed Weapons\n",
    "776187,2923.12,Carrying Concealed Weapons\n",
    "776187,2923.12,Carrying Concealed Weapons\n",
    "776187,2923.13,Having Weapons While Under Disability\n",
    "776187,2923.13,Having Weapons While Under Disability\n",
    "776187,2923.13,Having Weapons While Under Disability\n",
    "776187,2923.13,Having Weapons While Under Disability\n",
    "776187,2923.13,Having Weapons While Under Disability\n",
    "776187,2923.13,Having Weapons While Under Disability\n",
    "\"\"\"\n",
    "\n",
    "df = pd.read_csv(StringIO(example_csv))\n",
    "\n",
    "df "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afafd4ad-697e-4fc9-9ad5-8d9444e6853f",
   "metadata": {},
   "source": [
    "Start out by removing the duplicates and adding a column that identifies each charge."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "21f7dcdc-5b20-4f50-aac6-e6b6a4761344",
   "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>unique_id</th>\n",
       "      <th>penal_law_code</th>\n",
       "      <th>penal_law_description</th>\n",
       "      <th>charge_no</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.12</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>776187</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_id  penal_law_code                  penal_law_description  charge_no\n",
       "0     776187         2923.12             Carrying Concealed Weapons          0\n",
       "6     776187         2923.13  Having Weapons While Under Disability          1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Remove duplicates and add a column to count each charge number\n",
    "df_with_nos = df.drop_duplicates().assign(charge_no=lambda df: np.arange(df.shape[0]))\n",
    "\n",
    "df_with_nos"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70871335-dc6f-4072-9813-6586b85860d6",
   "metadata": {},
   "source": [
    "Then, melt the data so there's one row per observation, that is one row per charge, per column we want to eventually widen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ea19be66-cc02-462b-946f-efbe0596eeab",
   "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>unique_id</th>\n",
       "      <th>charge_no</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>776187</td>\n",
       "      <td>0</td>\n",
       "      <td>penal_law_code</td>\n",
       "      <td>2923.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>776187</td>\n",
       "      <td>1</td>\n",
       "      <td>penal_law_code</td>\n",
       "      <td>2923.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>776187</td>\n",
       "      <td>0</td>\n",
       "      <td>penal_law_description</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>776187</td>\n",
       "      <td>1</td>\n",
       "      <td>penal_law_description</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_id  charge_no               variable  \\\n",
       "0     776187          0         penal_law_code   \n",
       "1     776187          1         penal_law_code   \n",
       "2     776187          0  penal_law_description   \n",
       "3     776187          1  penal_law_description   \n",
       "\n",
       "                                   value  \n",
       "0                                2923.12  \n",
       "1                                2923.13  \n",
       "2             Carrying Concealed Weapons  \n",
       "3  Having Weapons While Under Disability  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get one row per observation\n",
    "\n",
    "df_tidy = df_with_nos.melt(id_vars=[\"unique_id\", \"charge_no\"])\n",
    "\n",
    "df_tidy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e989f46-e26a-4ae1-8117-d0a71090bb01",
   "metadata": {},
   "source": [
    "We could get away without doing the next step, but let's make more human friendly labels so that the first charge statute is named `penal_law_code` and the second `penal_law_code_2` instead of `penal_law_code_0` and `penal_law_code_1` respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "60af5e89-bfc2-4343-816c-c639a6e377f1",
   "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>unique_id</th>\n",
       "      <th>charge_no</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>charge_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>776187</td>\n",
       "      <td>0</td>\n",
       "      <td>penal_law_code</td>\n",
       "      <td>2923.12</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>776187</td>\n",
       "      <td>1</td>\n",
       "      <td>penal_law_code</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>776187</td>\n",
       "      <td>0</td>\n",
       "      <td>penal_law_description</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>776187</td>\n",
       "      <td>1</td>\n",
       "      <td>penal_law_description</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_id  charge_no               variable  \\\n",
       "0     776187          0         penal_law_code   \n",
       "1     776187          1         penal_law_code   \n",
       "2     776187          0  penal_law_description   \n",
       "3     776187          1  penal_law_description   \n",
       "\n",
       "                                   value charge_label  \n",
       "0                                2923.12               \n",
       "1                                2923.13            2  \n",
       "2             Carrying Concealed Weapons               \n",
       "3  Having Weapons While Under Disability            2  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Translate the charge numbers into more human-friendly labels\n",
    "\n",
    "df_tidy[\"charge_label\"] = np.where(\n",
    "    df_tidy[\"charge_no\"] == 0,\n",
    "    # If the charge number is 0, make the label an empty string\n",
    "    \"\",\n",
    "    # Otherwise, increment the index by one\n",
    "    (df_tidy[\"charge_no\"] + 1).astype(\"str\")\n",
    ")\n",
    "\n",
    "df_tidy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cec0260-22b8-4222-a026-9eabccb3c141",
   "metadata": {},
   "source": [
    "Then, combine the original column name (in `variable`) with the numeric labels we created in the previous step. This will be the column name when we eventually make the data wide."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "62491cc1-554c-444f-afdd-e7ca69e5a69a",
   "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>unique_id</th>\n",
       "      <th>charge_no</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>charge_label</th>\n",
       "      <th>new_col_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>776187</td>\n",
       "      <td>0</td>\n",
       "      <td>penal_law_code</td>\n",
       "      <td>2923.12</td>\n",
       "      <td></td>\n",
       "      <td>penal_law_code</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>776187</td>\n",
       "      <td>1</td>\n",
       "      <td>penal_law_code</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>2</td>\n",
       "      <td>penal_law_code_2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>776187</td>\n",
       "      <td>0</td>\n",
       "      <td>penal_law_description</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "      <td></td>\n",
       "      <td>penal_law_description</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>776187</td>\n",
       "      <td>1</td>\n",
       "      <td>penal_law_description</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "      <td>2</td>\n",
       "      <td>penal_law_description_2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   unique_id  charge_no               variable  \\\n",
       "0     776187          0         penal_law_code   \n",
       "1     776187          1         penal_law_code   \n",
       "2     776187          0  penal_law_description   \n",
       "3     776187          1  penal_law_description   \n",
       "\n",
       "                                   value charge_label             new_col_name  \n",
       "0                                2923.12                        penal_law_code  \n",
       "1                                2923.13            2         penal_law_code_2  \n",
       "2             Carrying Concealed Weapons                 penal_law_description  \n",
       "3  Having Weapons While Under Disability            2  penal_law_description_2  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Generate new column names\n",
    "\n",
    "df_tidy[\"new_col_name\"] = np.where(\n",
    "    df_tidy[\"charge_label\"] == \"\",\n",
    "    # If there's not a number in the label, just use the original column name\n",
    "    df_tidy[\"variable\"],\n",
    "    # If there is a numeric label, append it to the original column name\n",
    "    df_tidy[\"variable\"] + \"_\" + df_tidy[\"charge_label\"]\n",
    ")\n",
    "\n",
    "df_tidy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e086ec6-82f4-4a08-9cd2-8058ec21a101",
   "metadata": {},
   "source": [
    "Now use [`DataFrame.pivot()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot.html) to go from long to wide, putting the values in columns with the new column names we just created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9aa2a7b6-4ff7-4dc9-8b0b-9109052ec9dd",
   "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>penal_law_code</th>\n",
       "      <th>penal_law_code_2</th>\n",
       "      <th>penal_law_description</th>\n",
       "      <th>penal_law_description_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>776187</th>\n",
       "      <td>2923.12</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          penal_law_code penal_law_code_2       penal_law_description  \\\n",
       "unique_id                                                               \n",
       "776187           2923.12          2923.13  Carrying Concealed Weapons   \n",
       "\n",
       "                         penal_law_description_2  \n",
       "unique_id                                         \n",
       "776187     Having Weapons While Under Disability  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pivot the data from long to wide\n",
    "\n",
    "df_wide = df_tidy.pivot(index=\"unique_id\", columns=\"new_col_name\", values=\"value\")\n",
    "\n",
    "# `.pivot()` will make the name of the column index `new_col_name`.\n",
    "# I think this is ugly. Get rid of it.\n",
    "df_wide.columns.name = None\n",
    "\n",
    "df_wide"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d80877c-b65d-4e40-a2cc-4163491e5104",
   "metadata": {},
   "source": [
    "We're almost done, but I want to keep the original column order isntead of all the statutes coming before all of the descriptions. To do this, I'll make use of Python's [`sorted()`](https://docs.python.org/3/howto/sorting.html) function with a custom `key` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c0692f17-b47b-4c7d-8c71-b40614e5b0de",
   "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>penal_law_code</th>\n",
       "      <th>penal_law_description</th>\n",
       "      <th>penal_law_code_2</th>\n",
       "      <th>penal_law_description_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>776187</th>\n",
       "      <td>2923.12</td>\n",
       "      <td>Carrying Concealed Weapons</td>\n",
       "      <td>2923.13</td>\n",
       "      <td>Having Weapons While Under Disability</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          penal_law_code       penal_law_description penal_law_code_2  \\\n",
       "unique_id                                                               \n",
       "776187           2923.12  Carrying Concealed Weapons          2923.13   \n",
       "\n",
       "                         penal_law_description_2  \n",
       "unique_id                                         \n",
       "776187     Having Weapons While Under Disability  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reorder the columns\n",
    "\n",
    "# Start by defining a custom key function we'll use with sorted()\n",
    "\n",
    "def get_number_from_colname(colname):\n",
    "    \"\"\"\n",
    "    Returns the numeric order from a column name\n",
    "    \"\"\"\n",
    "    bits = colname.split(\"_\")\n",
    "    try:\n",
    "        return int(bits[-1])\n",
    "        \n",
    "    except ValueError:\n",
    "        return 0\n",
    "\n",
    "# Re-order the columns\n",
    "cols_ordered = sorted(df_wide.columns, key=get_number_from_colname)\n",
    "df_wide = df_wide[cols_ordered]\n",
    "\n",
    "df_wide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "456b4fe3-6815-4a32-8b4b-14069cfafd1b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python-data-cheatsheet",
   "language": "python",
   "name": "python-data-cheatsheet"
  },
  "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}