notebooks/pandas_excel.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f9ebdd17-8e06-4564-9904-291a1a76c119",
   "metadata": {},
   "source": [
    "# Writing Pandas DataFrames to Excel workbooks\n",
    "\n",
    "I used to never really consider Excel as a file format for writing the output of analysis. I think this was mainly because it's hard to preview the files without proprietary software. However, for explicit uses, Excel makes a lot of sense. It has some support for typing and formats and, most importantly, it lets you have multiple tables of data in a single file. When sharing data with reporters who primarily work with data in Excel, I find it really helpful to be able to send a README and a data dictionary along with the data.\n",
    "\n",
    "I had writen data using [`to_excel()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html), but this notebook includes some things I learned to support writing Excel files from Pandas in a way that made them seem like ones I meticulously formatted by hand. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0a8e3775-8891-4e02-9816-629917db7a03",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup\n",
    "\n",
    "import copy\n",
    "\n",
    "from openpyxl.utils import get_column_letter\n",
    "import pandas as pd\n",
    "\n",
    "import settings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84cb83f9-28aa-4450-995b-c769a5facf9d",
   "metadata": {},
   "source": [
    "## To write multiple worksheets, you need to used a `pd.ExcelWriter` object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "905d358f-61a0-4b9e-89e2-0efb4d11b08b",
   "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>col 1</th>\n",
       "      <th>col 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>row 1</th>\n",
       "      <td>a</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row 2</th>\n",
       "      <td>c</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      col 1 col 2\n",
       "row 1     a     b\n",
       "row 2     c     d"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make some toy data\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    [\n",
    "        ['a', 'b'],\n",
    "        ['c', 'd'],\n",
    "    ],\n",
    "    index=['row 1', 'row 2'],\n",
    "    columns=['col 1', 'col 2'],\n",
    ")\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26d2689f-cfcc-4fbe-afdb-cdb27690d1d3",
   "metadata": {},
   "source": [
    "One thing I like to do when writing an Excel workbook from a Pandas `DataFrame` is to also add a `README` worksheet to describe the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "848a6576-6114-437f-b905-4db32a16466c",
   "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>heading1</th>\n",
       "      <th>heading2</th>\n",
       "      <th>content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>What is this?</td>\n",
       "      <td></td>\n",
       "      <td>This is a sample dataset to illustrate writing...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        heading1 heading2                                            content\n",
       "0  What is this?           This is a sample dataset to illustrate writing..."
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a DataFrame suitable for writing a README worksheet\n",
    "\n",
    "readme = pd.DataFrame([\n",
    "    {\n",
    "        \"heading1\": \"What is this?\",\n",
    "        \"heading2\": \"\",\n",
    "        \"content\": \"This is a sample dataset to illustrate writing to Excel files from Pandas\",\n",
    "    }\n",
    "])\n",
    "\n",
    "readme"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "580152e0-1a76-4696-a9f2-79df4a6f188b",
   "metadata": {},
   "source": [
    "In order to write multiple worksheets to one Excel workbook, you have to used an [`ExcelWriter`](https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html#pandas.ExcelWriter) object.\n",
    "\n",
    "It works as a context manager."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4fea72a2-4455-487c-84a2-8577214e1ab9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write data and a README to one Excel workbook\n",
    "\n",
    "os.makedirs(settings.DATA_DIR_PROCESSED, exist_ok=True)\n",
    "\n",
    "excel_out_path = settings.DATA_DIR_PROCESSED / \"sample_excel_data.xlsx\"\n",
    "\n",
    "with pd.ExcelWriter(excel_out_path) as writer:\n",
    "    df.to_excel(writer, index=False, sheet_name=\"data\")\n",
    "    readme.to_excel(writer, index=False, sheet_name=\"README\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a09c8777-8fdd-4310-967d-eab21011a5d2",
   "metadata": {},
   "source": [
    "## Sizing columns\n",
    "\n",
    "Many times I want to resize the columns to fit the contents. The key to doing is is accessing the underlying [`Worksheet`](https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet) object and configuring various properties of the columns and cells. The worksheets can be accessed from the `.sheets` attribute of the `ExcelWriter` object.\n",
    "\n",
    "Rather than getting into the full API, here are a couple of functions I wrote in a project to help format the worksheets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0f142bcc-e4e8-45f9-b715-8a89d9593d4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define some functions\n",
    "\n",
    "def size_excel_columns(df, worksheet, wrap_columns=None):\n",
    "    \"\"\"Size the columns to fit the values in an Excel worksheet corresponding to a DataFrame\"\"\"\n",
    "    # h/t https://stackoverflow.com/a/40535454/386210\n",
    "    if wrap_columns is None:\n",
    "        wrap_columns = []\n",
    "\n",
    "    for idx, col in enumerate(df):  # loop through all columns\n",
    "        col_data = df[col]\n",
    "        if col in wrap_columns:\n",
    "            mean_len = (\n",
    "                max(\n",
    "                    col_data.astype(str).map(len).mean(),\n",
    "                    len(str(col_data.name)),  # len of column name/header\n",
    "                )\n",
    "                + 1  # adding a little extra space\n",
    "            )\n",
    "            col_letter = get_column_letter(idx + 1)\n",
    "            worksheet.column_dimensions[col_letter].width = mean_len\n",
    "            for row_idx in range(len(col_data)):\n",
    "                cell_label = f\"{col_letter}{row_idx + 2}\"\n",
    "                alignment = copy.copy(worksheet[cell_label].alignment)\n",
    "                alignment.wrapText = True\n",
    "                worksheet[cell_label].alignment = alignment\n",
    "\n",
    "        else:\n",
    "            max_len = (\n",
    "                max(\n",
    "                    (\n",
    "                        col_data.astype(str).map(len).max(),  # len of largest item\n",
    "                        len(str(col_data.name)),  # len of column name/header\n",
    "                    )\n",
    "                )\n",
    "                + 1  # adding a little extra space\n",
    "            )\n",
    "            worksheet.column_dimensions[get_column_letter(idx + 1)].width = max_len\n",
    "\n",
    "\n",
    "def set_excel_column_width(df, worksheet, column_name, width, wrap=False):\n",
    "    \"\"\"\n",
    "    Explicitly set the column width of an Excel sheet\n",
    "\n",
    "    \"\"\"\n",
    "    col_letter = get_column_letter(df.columns.get_loc(column_name) + 1)\n",
    "    worksheet.column_dimensions[col_letter].width = width\n",
    "    for row_idx in range(df.shape[0]):\n",
    "        cell_label = f\"{col_letter}{row_idx + 2}\"\n",
    "        alignment = copy.copy(worksheet[cell_label].alignment)\n",
    "        alignment.wrapText = wrap\n",
    "        worksheet[cell_label].alignment = alignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1d3af238-5064-45fa-b698-97dc51724b9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use worksheet column width/wrapping setting functions\n",
    "\n",
    "with pd.ExcelWriter(excel_out_path) as writer:\n",
    "    df.to_excel(writer, index=False, sheet_name=\"data\")\n",
    "    size_excel_columns(df, writer.sheets[\"data\"])\n",
    "    readme.to_excel(writer, index=False, sheet_name=\"README\")\n",
    "    set_excel_column_width(readme, writer.sheets[\"README\"], \"content\", 200, wrap=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "873dd7d3-08ac-4e0f-91a9-eddfffbd5f44",
   "metadata": {},
   "source": [
    "## Resources\n",
    "\n",
    "- [Working with Pandas and NumPy](https://openpyxl.readthedocs.io/en/stable/pandas.html) (OpenPyXL)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c32d6905-1fd6-4b7f-90fc-7e316e5da1a7",
   "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.9.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}