format-column-percentages-openpyxl2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "44292016",
   "metadata": {},
   "source": [
    "The objective of this notebook is to use Python and openpyxl to format an Excel column as a percentage. Let's bring in the relevant modules and dataset to get started: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fbd11c63",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { font-size: 16px !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Increase font sizes\n",
    "\n",
    "from IPython.display import display, HTML\n",
    "\n",
    "# Increase font size\n",
    "display(HTML(\"<style>.container { font-size: 16px !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d3955c2c",
   "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>email</th>\n",
       "      <th>cohort</th>\n",
       "      <th>pre</th>\n",
       "      <th>post</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>education</th>\n",
       "      <th>satisfaction</th>\n",
       "      <th>study_hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>smehaffey0@creativecommons.org</td>\n",
       "      <td>4</td>\n",
       "      <td>485</td>\n",
       "      <td>494</td>\n",
       "      <td>32</td>\n",
       "      <td>Male</td>\n",
       "      <td>Bachelor's</td>\n",
       "      <td>2</td>\n",
       "      <td>36.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bbenham2@xrea.com</td>\n",
       "      <td>3</td>\n",
       "      <td>477</td>\n",
       "      <td>483</td>\n",
       "      <td>33</td>\n",
       "      <td>Female</td>\n",
       "      <td>Bachelor's</td>\n",
       "      <td>1</td>\n",
       "      <td>19.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>jagostini4@wordpress.org</td>\n",
       "      <td>1</td>\n",
       "      <td>495</td>\n",
       "      <td>494</td>\n",
       "      <td>38</td>\n",
       "      <td>Female</td>\n",
       "      <td>Bachelor's</td>\n",
       "      <td>9</td>\n",
       "      <td>32.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>kbridgnell5@webnode.com</td>\n",
       "      <td>1</td>\n",
       "      <td>484</td>\n",
       "      <td>503</td>\n",
       "      <td>32</td>\n",
       "      <td>Female</td>\n",
       "      <td>Bachelor's</td>\n",
       "      <td>6</td>\n",
       "      <td>32.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>abotham6@usda.gov</td>\n",
       "      <td>1</td>\n",
       "      <td>497</td>\n",
       "      <td>501</td>\n",
       "      <td>30</td>\n",
       "      <td>Female</td>\n",
       "      <td>Bachelor's</td>\n",
       "      <td>10</td>\n",
       "      <td>32.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            email  cohort  pre  post  age     sex   education  \\\n",
       "0  smehaffey0@creativecommons.org       4  485   494   32    Male  Bachelor's   \n",
       "1               bbenham2@xrea.com       3  477   483   33  Female  Bachelor's   \n",
       "2        jagostini4@wordpress.org       1  495   494   38  Female  Bachelor's   \n",
       "3         kbridgnell5@webnode.com       1  484   503   32  Female  Bachelor's   \n",
       "4               abotham6@usda.gov       1  497   501   30  Female  Bachelor's   \n",
       "\n",
       "   satisfaction  study_hours  \n",
       "0             2         36.6  \n",
       "1             1         19.8  \n",
       "2             9         32.5  \n",
       "3             6         32.2  \n",
       "4            10         32.3  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.utils.dataframe import dataframe_to_rows\n",
    "from openpyxl.utils import get_column_letter\n",
    "\n",
    "df = pd.read_csv('https://raw.githubusercontent.com/summerofgeorge/blog-files/master/contestants_cleaned.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78e3059f",
   "metadata": {},
   "source": [
    "Using `pandas`, we'll create a percentile rank for the post column and confirm its validity by running descriptive statistics with `describe()`: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0590654f",
   "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>post_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>82.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.506098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.290324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.012195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.262195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.757622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        post_pct\n",
       "count  82.000000\n",
       "mean    0.506098\n",
       "std     0.290324\n",
       "min     0.012195\n",
       "25%     0.262195\n",
       "50%     0.500000\n",
       "75%     0.757622\n",
       "max     1.000000"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate the percentile rank\n",
    "df['post_pct'] = df['post'].rank(pct=True)\n",
    "\n",
    "df[['post_pct']].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc32db00",
   "metadata": {},
   "source": [
    "Next, I'll write the DataFrame to Excel using `openpyxl`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1863d36a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write to Excel\n",
    "\n",
    "# Create a workbook and worksheet\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "# Write the DataFrame to the worksheet\n",
    "for row in dataframe_to_rows(df, index=False, header=True):\n",
    "    ws.append(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b66865c7",
   "metadata": {},
   "source": [
    "If we were to save and open the file in Excel now, it would look like this:\n",
    "\n",
    "<img src=\"https://raw.githubusercontent.com/summerofgeorge/blog-files/master/post_pct_not_unformatted.png\" alt=\"image description\" width=\"500px\" />\n",
    "\n",
    "\n",
    "Python and `pandas` primarily focus on data processing and analysis, with limited formatting capabilities. On the other hand, Excel offers a wide range of formatting features that can be easily accessed in Python using `openpyxl`. \n",
    "\n",
    "To learn how to adjust the column widths of this dataset, [check out this post](https://stringfestanalytics.com/how-to-automatically-adjust-excel-column-widths-in-openpyxl/). Our focus will be converting the format of the post_pct column to a percentage.\n",
    "\n",
    "To find the index position of the post_pct column in the DataFrame, we'll use get_loc() from pandas. Then, we'll add one to the result to account for Excel's [one-based indexing](https://stringfestanalytics.com/seen-zero-based-indexing/). Additionally, we'll convert the result to Excel's column name-based schema."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "53643bc4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'J'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get index position of percentile column\n",
    "\n",
    "post_pct_loc = df.columns.get_loc('post_pct')\n",
    "post_pct_col = get_column_letter(post_pct_loc + 1) \n",
    "post_pct_col"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c55b317",
   "metadata": {},
   "source": [
    "Next, we'll specify a custom number format and apply it to every row in the relevant column of the dataset: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c462870b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format the column cells as percentage\n",
    "\n",
    "number_format = '0.0%'\n",
    "\n",
    "for cell in ws[post_pct_col]:\n",
    "    cell.number_format = number_format"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3551021",
   "metadata": {},
   "source": [
    "Finally, it's time to close and save the workbook. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "be9ac2c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the workbook\n",
    "wb.save('format_pct.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c14743b8",
   "metadata": {},
   "source": [
    "You should now see `post_pct` formatted as a percentage with one decimal place in your workbook. "
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}