Working_with_excel_spreadsheets.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with Excel Spreadsheets"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Source: https://automatetheboringstuff.com/chapter12/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext nb_black"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Working with Excel Spreadsheets\n",
    "\n",
    "**Excel** is a popular and powerful spreadsheet application for Windows. The `openpyxl` module allows your Python programs to read and modify Excel spreadsheet files. For example, you might have the boring task of copying certain data from one spreadsheet and pasting it into another one. Or you might have to go through thousands of rows and pick out just a handful of them to make small edits based on some criteria. Or you might have to look through hundreds of spreadsheets of department budgets, searching for any that are in the red. These are exactly the sort of boring, mindless spreadsheet tasks that Python can do for you.\n",
    "\n",
    "Although Excel is proprietary software from Microsoft, there are free alternatives that run on Windows, OS X, and Linux. Both LibreOffice Calc and OpenOffice Calc work with Excel’s `.xlsx` file format for spreadsheets, which means the openpyxl module can work on spreadsheets from these applications as well. You can download the software from https://www.libreoffice.org/ and http://www.openoffice.org/, respectively. Even if you already have Excel installed on your computer, you may find these programs easier to use. The screenshots in this chapter, however, are all from Excel 2010 on Windows 7."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### About Excel\n",
    "\n",
    "First, let’s go over some basic definitions: An Excel spreadsheet document is called a **workbook**. A single workbook is saved in a file with the `.xlsx` extension. Each workbook can contain multiple sheets (also called **worksheets**). The sheet the user is currently viewing (or last viewed before closing Excel) is called the **active sheet**.\n",
    "\n",
    "Each sheet has **columns** (addressed by letters starting at A) and **rows** (addressed by numbers starting at 1). A box at a particular column and row is called a **cell**. Each cell can contain a number or text value. The grid of cells with data makes up a sheet."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Installing the openpyxl Module\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Python does not come with OpenPyXL, so you’ll have to install it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install openpyxl"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " To test whether it is installed correctly, enter the following into a code block:\n",
    "\n",
    "`import openpyxl`\n",
    "\n",
    "If the module was correctly installed, this should produce no error messages. Remember to import the `openpyxl` module before running the examples in this notebook, or you’ll get a *NameError: name 'openpyxl' is not defined* error.\n",
    "\n",
    "This book covers version 2.3.3 of OpenPyXL, but new versions are regularly released by the OpenPyXL team. Don’t worry, though: New versions should stay backward compatible with the instructions in this book for quite some time. If you have a newer version and want to see what additional features may be available to you, you can check out the full documentation for OpenPyXL at http://openpyxl.readthedocs.org/."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading Excel Documents\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The examples in this chapter will use a spreadsheet named **example.xlsx** stored in the root folder. You can either create the spreadsheet yourself or download it from http://nostarch.com/automatestuff/. "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Opening Excel Documents with OpenPyXL\n",
    "\n",
    "Once you’ve imported the openpyxl module, you’ll be able to use the `openpyxl.load_workbook()` function. Enter the following into a code block:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "wb = openpyxl.load_workbook(\"example.xlsx\")\n",
    "type(wb)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    \n",
    "The `openpyxl.load_workbook()` function takes in the filename and returns a value of the workbook data type. This Workbook object represents the Excel file, a bit like how a File object represents an opened text file.\n",
    "\n",
    "Remember that *example.xlsx* needs to be in the current working directory in order for you to work with it. You can find out what the current working directory is by importing `os` and using `os.getcwd()`, and you can change the current working directory using `os.chdir()`."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting Sheets from the Workbook\n",
    "\n",
    "You can get a list of all the sheet names in the workbook by calling the `get_sheet_names()` method. Enter the following into a codeblock:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "wb = openpyxl.load_workbook(\"example.xlsx\")\n",
    "wb.get_sheet_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheet = wb.get_sheet_by_name(\"Sheet3\")\n",
    "print(sheet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(type(sheet))\n",
    "print(sheet.title)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "anotherSheet = wb.active\n",
    "print(anotherSheet)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each sheet is represented by a Worksheet object, which you can obtain by passing the sheet name string to the `get_sheet_by_name()` workbook method.\\\n",
    "Finally, you can read the active member variable of a Workbook object to get the workbook’s active sheet. The active sheet is the sheet that’s on top when the workbook is opened in Excel. Once you have the Worksheet object, you can get its name from the title attribute."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting Cells from the Sheets\n",
    "\n",
    "Once you have a Worksheet object, you can access a Cell object by its name. Enter the following into the interactive shell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "wb = openpyxl.load_workbook(\"example.xlsx\")\n",
    "sheet = wb.get_sheet_by_name(\"Sheet1\")\n",
    "print(sheet[\"A1\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(sheet[\"A1\"].value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c = sheet[\"B1\"]\n",
    "print(c.value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Row \" + str(c.row) + \", Column \" + c.column + \" is \" + c.value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Cell \" + c.coordinate + \" is \" + c.value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(sheet[\"C1\"].value)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Cell object has a value attribute that contains, unsurprisingly, the value stored in that cell. Cell objects also have row, column, and coordinate attributes that provide location information for the cell.\n",
    "\n",
    "Here, accessing the value attribute of our Cell object for cell *B1* gives us the string *'Apples'*. The row attribute gives us the integer *1*, the column attribute gives us *'B'*, and the coordinate attribute gives us *'B1'*.\n",
    "\n",
    "*OpenPyXL* will automatically interpret the dates in column *A* and return them as datetime values rather than strings.\n",
    "\n",
    "Specifying a column by letter can be tricky to program, especially because after column *Z*, the columns start by using two letters: *AA, AB, AC*, and so on. As an alternative, you can also get a cell using the sheet’s *cell()* method and passing integers for its row and column keyword arguments. The first row or column integer is *1*, not *0*. Continue the interactive shell example by entering the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheet.cell(row=1, column=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheet.cell(row=1, column=2).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(1, 8, 2):\n",
    "    print(i, sheet.cell(row=i, column=2).value)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, using the sheet’s cell() method and passing it `row=1` and `column=2` gets you a Cell object for cell *B1*, just like specifying `sheet['B1']` did. Then, using the `cell()` method and its keyword arguments, you can write a `for` loop to print the values of a series of cells.\n",
    "\n",
    "Say you want to go down column *B* and print the value in every cell with an odd row number. By passing *2* for the `range()` function’s “step” parameter, you can get cells from every second row (in this case, all the odd-numbered rows). The for loop’s `i` variable is passed for the row keyword argument to the `cell()` method, while *2* is always passed for the column keyword argument. Note that the integer *2*, not the string *'B'*, is passed.\n",
    "\n",
    "You can determine the size of the sheet with the Worksheet object’s `max_row` and `max_column` member variables. Enter the following into the interactive shell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "wb = openpyxl.load_workbook(\"example.xlsx\")\n",
    "sheet = wb.get_sheet_by_name(\"Sheet1\")\n",
    "print(sheet.max_row)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the *max_column* method returns an integer rather than the letter that appears in Excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(sheet.max_column)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Converting Between Column Letters and Numbers\n",
    "\n",
    "To convert from letters to numbers, call the `openpyxl.cell.column_index_from_string()` function. To convert from numbers to letters, call the `openpyxl.cell.get_column_letter()` function. Enter the following into the interactive shell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "from openpyxl.utils import get_column_letter, column_index_from_string\n",
    "\n",
    "get_column_letter(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_column_letter(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_column_letter(27)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_column_letter(900)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb = openpyxl.load_workbook(\"example.xlsx\")\n",
    "sheet = wb.get_sheet_by_name(\"Sheet1\")\n",
    "get_column_letter(sheet.max_column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_index_from_string(\"A\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_index_from_string(\"AA\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After you import these two functions from the `openpyxl.utils` module, you can call `get_column_letter()` and pass it an integer like 27 to figure out what the letter name of the 27th column is. The function `column_index_string()` does the reverse: You pass it the letter name of a column, and it tells you what number that column is. You don’t need to have a workbook loaded to use these functions. If you want, you can load a workbook, get a Worksheet object, and call a Worksheet object method like `max_column` to get an integer. Then, you can pass that integer to `get_column_letter()`."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting Rows and Columns from the Sheets\n",
    "\n",
    "You can slice Worksheet objects to get all the Cell objects in a row, column, or rectangular area of the spreadsheet. Then you can loop over all the cells in the slice. Enter the following into the interactive shell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "wb = openpyxl.load_workbook(\"example.xlsx\")\n",
    "sheet = wb.get_sheet_by_name(\"Sheet1\")\n",
    "tuple(sheet[\"A1\":\"C3\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# loop 1\n",
    "for rowOfCellObjects in sheet[\"A1\":\"C3\"]:\n",
    "    # loop 2\n",
    "    for cellObj in rowOfCellObjects:\n",
    "        print(cellObj.coordinate, cellObj.value)\n",
    "    print(\"--- END OF ROW ---\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we specify that we want the Cell objects in the rectangular area from *A1* to *C3*, and we get a Generator object containing the Cell objects in that area. To help us visualize this Generator object, we can use `tuple()` on it to display its Cell objects in a tuple.\n",
    "\n",
    "This tuple contains three tuples: one for each row, from the top of the desired area to the bottom. Each of these three inner tuples contains the Cell objects in one row of our desired area, from the leftmost cell to the right. So overall, our slice of the sheet contains all the Cell objects in the area from A1 to C3, starting from the top-left cell and ending with the bottom-right cell.\n",
    "\n",
    "To print the values of each cell in the area, we use two for loops. The outer for loop goes over each row in the slice (see #loop 1). Then, for each row, the nested for loop goes through each cell in that row (see #loop 2).\n",
    "\n",
    "To access the values of cells in a particular row or column, you can also use a Worksheet object’s rows and columns attribute. Enter the following into the interactive shell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "wb = openpyxl.load_workbook(\"example.xlsx\")\n",
    "sheet = wb.active\n",
    "print(list(sheet.columns)[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for cellObj in list(sheet.columns)[1]:\n",
    "    print(cellObj.value)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the rows attribute on a Worksheet object will give you a tuple of tuples. Each of these inner tuples represents a row, and contains the Cell objects in that row. The columns attribute also gives you a tuple of tuples, with each of the inner tuples containing the Cell objects in a particular column. For *example.xlsx*, since there are 7 rows and 3 columns, rows gives us a tuple of 7 tuples (each containing 3 Cell objects), and columns gives us a tuple of 3 tuples (each containing 7 Cell objects).\n",
    "\n",
    "To access one particular tuple, you can refer to it by its index in the larger tuple. For example, to get the tuple that represents column *B*, you use `sheet.columns[1]`. To get the tuple containing the Cell objects in column *A*, you’d use `sheet.columns[0]`. Once you have a tuple representing one row or column, you can loop through its Cell objects and print their values."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Workbooks, Sheets, Cells\n",
    "\n",
    "As a quick review, here’s a rundown of all the functions, methods, and data types involved in reading a cell out of a spreadsheet file:\n",
    "\n",
    "1. Import the`openpyxl` module.\n",
    "\n",
    "2. Call the `openpyxl.load_workbook()` function.\n",
    "\n",
    "3. Get a Workbook object.\n",
    "\n",
    "4. Read the active member variable or call the `get_sheet_by_name()` workbook method.\n",
    "\n",
    "5. Get a Worksheet object.\n",
    "\n",
    "6. Use indexing or the `cell()` sheet method with row and column keyword arguments.\n",
    "\n",
    "7. Get a Cell object.\n",
    "\n",
    "8. Read the Cell object’s value attribute.\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Project: Reading Data from a Spreadsheet\n",
    "\n",
    "Say you have a spreadsheet of data from the 2010 US Census and you have the boring task of going through its thousands of rows to count both the total population and the number of census tracts for each county. (A census tract is simply a geographic area defined for the purposes of the census.) Each row represents a single census tract. We’ll name the spreadsheet file **censuspopdata.xlsx**, and you can download it from http://nostarch.com/automatestuff/."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even though Excel can calculate the sum of multiple selected cells, you’d still have to select the cells for each of the 3,000-plus counties. Even if it takes just a few seconds to calculate a county’s population by hand, this would take hours to do for the whole spreadsheet.\n",
    "\n",
    "In this project, you’ll write a script that can read from the census spreadsheet file and calculate statistics for each county in a matter of seconds.\n",
    "\n",
    "This is what your program does:\n",
    "\n",
    "1. Reads the data from the Excel spreadsheet.\n",
    "\n",
    "2. Counts the number of census tracts in each county.\n",
    "\n",
    "3. Counts the total population of each county.\n",
    "\n",
    "4. Prints the results.\n",
    "\n",
    "This means your code will need to do the following:\n",
    "\n",
    "1. Open and read the cells of an Excel document with the `openpyxl` module.\n",
    "\n",
    "2. Calculate all the tract and population data and store it in a data structure.\n",
    "\n",
    "3. Write the data structure to a text file with the `.py` extension using the `pprint` module.\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1: Read the Spreadsheet Data\n",
    "\n",
    "There is just one sheet in the **censuspopdata.xlsx** spreadsheet, named 'Population by Census Tract', and each row holds the data for a single census tract. The columns are the tract number (A), the state abbreviation (B), the county name (C), and the population of the tract (D).\n",
    "\n",
    "Use the following code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl, pprint\n",
    "\n",
    "print(\"Opening workbook...\")\n",
    "wb = openpyxl.load_workbook(\"censuspopdata.xlsx\")\n",
    "sheet = wb.get_sheet_by_name(\"Population by Census Tract\")\n",
    "countyData = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: Fill in countyData with each county's population and tracts.\n",
    "print(\"Reading rows...\")\n",
    "for row in range(2, sheet.max_row + 1):\n",
    "    # Each row in the spreadsheet has data for one census tract.\n",
    "    state = sheet[\"B\" + str(row)].value\n",
    "    county = sheet[\"C\" + str(row)].value\n",
    "    pop = sheet[\"D\" + str(row)].value\n",
    "# TODO: Open a new text file and write the contents of countyData to it."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This code imports the openpyxl module, as well as the pprint module that you’ll use to print the final county data. Then it opens the **censuspopdata.xlsx** file, gets the sheet with the census data, and begins iterating over its rows.\n",
    "\n",
    "Note that you’ve also created a variable named `countyData`, which will contain the populations and number of tracts you calculate for each county. Before you can store anything in it, though, you should determine exactly how you’ll structure the data inside it."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Populate the Data Structure"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data structure stored in *countyData* will be a dictionary with state abbreviations as its keys. Each state abbreviation will map to another dictionary, whose keys are strings of the county names in that state. Each county name will in turn map to a dictionary with just two keys, 'tracts' and 'pop'. These keys map to the number of census tracts and population for the county. For example, the dictionary will look similar to this:\n",
    "\n",
    "`{'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1},\n",
    "        'Aleutians West': {'pop': 5561, 'tracts': 2},\n",
    "        'Anchorage': {'pop': 291826, 'tracts': 55},\n",
    "        'Bethel': {'pop': 17013, 'tracts': 3},\n",
    "        'Bristol Bay': {'pop': 997, 'tracts': 1},\n",
    "        --snip--`"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the previous dictionary were stored in *countyData*, the following expressions would evaluate like this:\n",
    "\n",
    "`countyData['AK']['Anchorage']['pop']`\n",
    "\n",
    "291826\n",
    "\n",
    "`countyData['AK']['Anchorage']['tracts']`\n",
    "\n",
    "55\n",
    "\n",
    "More generally, the *countyData* dictionary’s keys will look like this:\n",
    "\n",
    "`countyData[state abbrev][county]['tracts']`\n",
    "\n",
    "`countyData[state abbrev][county]['pop']`\n",
    "\n",
    "Now that you know how *countyData* will be structured, you can write the code that will fill it with the county data. Add the following code to the bottom of your program:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#! python 3\n",
    "# readCensusExcel.py - Tabulates population and number of census tracts for\n",
    "# each county.\n",
    "# --snip--\n",
    "for row in range(2, sheet.max_row + 1):\n",
    "    # Each row in the spreadsheet has data for one census tract.\n",
    "    state = sheet[\"B\" + str(row)].value\n",
    "    county = sheet[\"C\" + str(row)].value\n",
    "    pop = sheet[\"D\" + str(row)].value"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make sure the key for this state exists.\n",
    "1. `countyData.setdefault(state, {})`\n",
    "\n",
    "Make sure the key for this county in this state exists.\n",
    "\n",
    "2. `countyData[state].setdefault(county, {'tracts': 0, 'pop': 0})`\n",
    "\n",
    "Each row represents one census tract, so increment by one.\n",
    "\n",
    "3. `countyData[state][county]['tracts'] += 1`\n",
    "\n",
    "Increase the county pop by the pop in this census tract.\n",
    "\n",
    "4. `countyData[state][county]['pop'] += int(pop)`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: Open a new text file and write the contents of countyData to it."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "\n",
    "The last two lines of code perform the actual calculation work, incrementing the value for tracts and increasing the value for pop for the current county on each iteration of the for loop.\n",
    "\n",
    "The other code is there because you cannot add a county dictionary as the value for a state abbreviation key until the key itself exists in countyData. (That is, `countyData['AK']['Anchorage']['tracts'] += 1` will cause an error if the 'AK' key doesn’t exist yet.) To make sure the state abbreviation key exists in your data structure, you need to call the `setdefault()` method to set a value if one does not already exist for state.\n",
    "\n",
    "Just as the *countyData* dictionary needs a dictionary as the value for each state abbreviation key, each of those dictionaries will need its own dictionary as the value for each county key. And each of those dictionaries in turn will need keys 'tracts' and 'pop' that start with the integer value 0. (If you ever lose track of the dictionary structure, look back at the example dictionary at the start of this section.)\n",
    "\n",
    "Since `setdefault()` will do nothing if the key already exists, you can call it on every iteration of the for loop without a problem."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Step 3: Write the Results to a File\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After the for loop has finished, the countyData dictionary will contain all of the population and tract information keyed by county and state. At this point, you could program more code to write this to a text file or another Excel spreadsheet. For now, let’s just use the pprint.pformat() function to write the countyData dictionary value as a massive string to a file named census2010.py. Add the following code to the bottom of your program (making sure to keep it unindented so that it stays outside the for loop):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#! python 3\n",
    "# readCensusExcel.py - Tabulates population and number of census tracts for\n",
    "# each county.get_active_sheet\n",
    "\n",
    "for row in range(2, sheet.max_row + 1):\n",
    "    --snip--"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Open a new text file and write the contents of countyData to it.\n",
    "print(\"Writing results...\")\n",
    "resultFile = open(\"census2010.py\", \"w\")\n",
    "resultFile.write(\"allData = \" + pprint.pformat(countyData))\n",
    "resultFile.close()\n",
    "print(\"Done.\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `pprint.pformat()` function produces a string that itself is formatted as valid Python code. By outputting it to a text file named `census2010.py`, you’ve generated a Python program from your Python program! This may seem complicated, but the advantage is that you can now import `census2010.py` just like any other Python module. In the interactive shell, change the current working directory to the folder with your newly created `census2010.py` file (on my laptop, this is `C:\\Python34`), and then import it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "os.chdir(\"C:\\\\Python34\")\n",
    "import census2010\n",
    "\n",
    "census2010.allData[\"AK\"][\"Anchorage\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "anchoragePop = census2010.allData[\"AK\"][\"Anchorage\"][\"pop\"]\n",
    "print(\"The 2010 population of Anchorage was \" + str(anchoragePop))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `readCensusExcel.py` program was throwaway code: Once you have its results saved to `census2010.py`, you won’t need to run the program again. Whenever you need the county data, you can just run \n",
    "\n",
    "`import census2010`.\n",
    "\n",
    "Calculating this data by hand would have taken hours; this program did it in a few seconds. Using *OpenPyXL*, you will have no trouble extracting information that is saved to an Excel spreadsheet and performing calculations on it. You can download the complete program from http://nostarch.com/automatestuff/."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ideas for Similar Programs\n",
    "\n",
    "Many businesses and offices use Excel to store various types of data, and it’s not uncommon for spreadsheets to become large and unwieldy. Any program that parses an Excel spreadsheet has a similar structure: It loads the spreadsheet file, preps some variables or data structures, and then loops through each of the rows in the spreadsheet. Such a program could do the following:\n",
    "\n",
    "1. Compare data across multiple rows in a spreadsheet.\n",
    "\n",
    "2. Open multiple Excel files and compare data between spreadsheets.\n",
    "\n",
    "3. Check whether a spreadsheet has blank rows or invalid data in any cells and alert the user if it does.\n",
    "\n",
    "4. Read data from a spreadsheet and use it as the input for your Python programs.\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing Excel Documents\n",
    "\n",
    "OpenPyXL also provides ways of writing data, meaning that your programs can create and edit spreadsheet files. With Python, it’s simple to create spreadsheets with thousands of rows of data."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating and Saving Excel Documents\n",
    "\n",
    "Call the `openpyxl.Workbook()` function to create a new, blank Workbook object. Enter the following into the interactive shell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "wb = openpyxl.Workbook()\n",
    "wb.get_sheet_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheet = wb.active\n",
    "sheet.title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheet.title = \"Spam Bacon Eggs Sheet\"\n",
    "wb.get_sheet_names()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The workbook will start off with a single sheet named Sheet. You can change the name of the sheet by storing a new string in its title attribute.\n",
    "\n",
    "Any time you modify the Workbook object or its sheets and cells, the spreadsheet file will not be saved until you call the `save()` workbook method. Enter the following into the interactive shell (with **example.xlsx** in the current working directory):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "wb = openpyxl.load_workbook(\"example.xlsx\")\n",
    "sheet = wb.active\n",
    "sheet.title = \"Spam Spam Spam\"\n",
    "wb.save(\"example_copy.xlsx\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we change the name of our sheet. To save our changes, we pass a filename as a string to the`save()` method. Passing a different filename than the original, such as **example_copy.xlsx**, saves the changes to a copy of the spreadsheet.\n",
    "\n",
    "Whenever you edit a spreadsheet you’ve loaded from a file, you should always save the new, edited spreadsheet to a different filename than the original. That way, you’ll still have the original spreadsheet file to work with in case a bug in your code caused the new, saved file to have incorrect or corrupt data."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating and Removing Sheets\n",
    "\n",
    "Sheets can be added to and removed from a workbook with the `create_sheet()` and `remove_sheet()` methods. Enter the following into the interactive shell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "wb = openpyxl.Workbook()\n",
    "wb.get_sheet_names()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb.create_sheet()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb.get_sheet_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb.create_sheet(index=0, title=\"First Sheet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},