micro/CPS_Example_Notebook_UPDATED.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Current Population Survey Microdata with Python\n",
    "\n",
    "\n",
    "*Brian Dew, @bd_econ, July 7, 2019*\n",
    "\n",
    "The following is a minimal working example of using python and the struct method to read CPS microdata. It requires two files to be saved locally: the data dictionary, `January_2017_Record_Layout.txt`, and the large (13MB compressed, 146MB uncompressed) CPS public use microdata file covering April 2017, `apr17pub.dat`. Both are downloaded from the [Census CPS page](https://www.census.gov/data/datasets/time-series/demo/cps/cps-basic.html).\n",
    "\n",
    "First, the Census data dictionary file identifies where the relevant variables are located in the microdata file. Because the microdata file is fixed-width format with no headers, there is nothing inside of the file that tells what each variable is or where it is stored--hence the need for a separate dictionary.\n",
    "\n",
    "Once the data structure is identified, the relevant parts of each row of microdata are extracted into a big list. Pandas, which makes further data cleaning and calculations easier, is able to quickly read this list. To confirm that the data are loaded properly, the example successfully benchmarks the results of a local calculation of what share of women age 25-54 are employed in April 2017 against the published BLS estimate. \n",
    "\n",
    "The code benefits greatly from the excellent four-part series ([1](https://tomaugspurger.github.io/tackling%20the%20cps.html), [2](https://tomaugspurger.github.io/tackling%20the%20cps%20%28part%202%29.html), [3](https://tomaugspurger.github.io/tackling%20the%20cps%20%28part%203%29.html), [4](https://tomaugspurger.github.io/tackling%20the%20cps%20%28part%204%29.html)) by Tom Augspurger and from very patient guidance with the CPS from John Schmitt.\n",
    "\n",
    "Grateful for any advice on making the code better!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Requirements:\n",
    "\n",
    "Python (3.6 used, probably works on 2.7+), pandas, and numpy\n",
    "\n",
    "Two external files: \n",
    "\n",
    " 1) January_2017_Record_Layout.txt (a data dictionary text file: https://www2.census.gov/programs-surveys/cps/datasets/2017/basic/January_2017_Record_Layout.txt); and \n",
    " 2) apr17pub.dat (*CAUTION*: large public use microdata file [13MB compressed, 146MB uncompressed]: https://www2.census.gov/programs-surveys/cps/datasets/2017/basic/apr17pub.zip)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-07T14:46:53.160437Z",
     "start_time": "2019-07-07T14:46:52.913529Z"
    },
    "code_folding": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['January_2017_Record_Layout.txt', 'apr17pub.dat']\n"
     ]
    }
   ],
   "source": [
    "# Import relevant libraries (python 3.7)\n",
    "import os, re, struct\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# check that two required files are saved locally\n",
    "os.chdir('cps_example/')\n",
    "print(os.listdir(os.curdir))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CPS data dictionary\n",
    "\n",
    "Census posts a [file](https://thedataweb.rm.census.gov/pub/cps/basic/201701-/January_2017_Record_Layout.txt) that describes how to read the fixed-width format CPS microdata files. This \"data dictionary\" file can be machine read. In this example, the January 2017 data dictionary text file is saved locally then used to manually find and type out the \"names\" of four variables: `PRTAGE` for age, `PESEX` for gender, `PREMPNOT` for employment status, and `PWCMPWGT` for the sample weight. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-07T14:46:53.166088Z",
     "start_time": "2019-07-07T14:46:53.162074Z"
    }
   },
   "outputs": [],
   "source": [
    "# read data dictionary text file \n",
    "data_dict = open('January_2017_Record_Layout.txt', 'r', encoding='iso-8859-1').read()\n",
    "\n",
    "# manually list out the IDs for series of interest \n",
    "var_names = ['PRTAGE', 'PESEX', 'PREMPNOT', 'PWCMPWGT']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Regular expressions\n",
    "\n",
    "The data dictionary text file follows a pattern that makes it machine readable. In this case, specific rows of interest follow the pattern of: VariableName [space] VariableLength [space] VariableDescription [space] VariableStart - VariableEnd. The regex pattern that captures the data of interest is identified by `p`.\n",
    "\n",
    "The python numbering system starts at zero and does not include the last number from a range. As a result, the start location of a variable, as identified in the data dictionary, is adjusted by -1. Additionally, the length of the variable value, its \"width\" in the dataset, is stored as a string ending in `s`, the struct format code for a byte containing a single character.  This will be used later in reading the microdata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-07T14:46:53.175482Z",
     "start_time": "2019-07-07T14:46:53.167593Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'PRTAGE': [121, 123, '2s'], 'PESEX': [128, 130, '2s'], 'PREMPNOT': [392, 394, '2s'], 'PWCMPWGT': [845, 855, '10s']}\n"
     ]
    }
   ],
   "source": [
    "# regular expression matching series name and data dict pattern\n",
    "p = f'\\n({\"|\".join(var_names)})\\s+(\\d+)\\s+.*?\\t+.*?(\\d\\d*).*?(\\d\\d+)'\n",
    "\n",
    "# dictionary of variable name: [start, end, and length + 's']\n",
    "d = {s[0]: [int(s[2])-1, int(s[3]), f'{s[1]}s']\n",
    "     for s in re.findall(p, data_dict)}\n",
    "\n",
    "print(d)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Struct method\n",
    "\n",
    "There are at least three ways to read a fixed-width format file in python: 1) string slicing on each row for each variable; 2) pandas.read_fwf(file, colspecs); or 3) use struct and a format string to identify what part of each CPS row to keep as a variable and what part to ignore. The pandas method is the easiest to use, but the slowest. The struct method is the most difficult to use but the fastest. If you need to read 40 variables for all months going back to 1994, you might want to consider writing out a script that applies the struct method.\n",
    "\n",
    "Here is a minimal example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-07T14:46:53.182220Z",
     "start_time": "2019-07-07T14:46:53.176793Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "121x2s5x2s262x2s451x10s\n"
     ]
    }
   ],
   "source": [
    "# lists of variable starts, ends, and lengths\n",
    "start, end, width = zip(*d.values())\n",
    "\n",
    "# create list of which characters to skip in each row\n",
    "skip = ([f'{s - e}x' for s, e in zip(start, [0] + list(end[:-1]))])\n",
    "\n",
    "# create format string by joining skip and variable segments\n",
    "unpack_fmt = ''.join([j for i in zip(skip, width) for j in i])\n",
    "print(unpack_fmt)\n",
    "\n",
    "# struct can interpret row bytes with the format string\n",
    "unpacker = struct.Struct(unpack_fmt).unpack_from"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fixed width format\n",
    "\n",
    "The string format above, called `unpack_fmt`, can be read as: ignore the first 121 characters (`121x`), store the next two (`2s`), ignore the next five (`5x`), store the next two (`2s`), and so on. This format matches the structure of each line of the fwf data. Here's the first row of the raw CPS microdata, as an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-07T14:46:53.187929Z",
     "start_time": "2019-07-07T14:46:53.183598Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "000110116792163 42017 120100-1 1 1-1 115-1-1-1  15049796 1 2 1 7 2 0 205011 2  1 1-1-1-1 36 01 338600001103000   -1-1 1-1420 1 2 1 2-1 243 1-1 9-1 1-1 1 1 1 2 1 2 57 57 57 1 0 0 1 1 1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1 2-150-1-1 50-1-1-1-1 2-1 2-150-1 50-1-1    2 5 5-1 2 3 5 2-1-1-1-1-1 -1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1 -1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1 1-121 1 1 1 6-1-1-1 -1-1-1 1 2-1-1-1-1 1 2 1 6 4      -1-1       4 3 3 1 2 4-1-1 6-138-114-1 1 9-1 3-1 2 1 1 1 0-1-1-1-1  -1  -1  -1  -10-1      -10-1-1      -1      -10-1-1-1-1-1-1-1-1-1 2-1-1 2  15049796  22986106         0  16044411  15280235 0 0 1-1-1-1 0 0 1 0-1 050 0 0 0 0 1 0 0 0-1-1-1 1 0 0-1 1 1 0 1 0 1 1 0 1 1 1 0 1 0 1 1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1 0 0 0-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1 0 1 1 3865 1-1-1-1-1-1-1 1 1 1-1-1-1  1573071277704210  -1  -114-1-1-1-1-1 0-1-1-1-1-15050 1 1 1 2 2 2 2 2 2 2 0 0 0 0 0 0 0-1-1-1-1-1 1 1 1202020                                            A\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(open('apr17pub.dat').readline())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we ignore the first 121 characters and then keep the next two, we find `42` which is the age of the person in the first row of the microdata. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read the raw microdata \n",
    "\n",
    "First, open the raw CPS microdata file with the readlines method. The raw data is filtered to only include observations with a positive sample weight. The unpacker identified above is applied to each row of microdata, while the extracted variable values are converted to integers from strings.\n",
    "\n",
    "The first five observations are printed. They are much more readable than the raw data, but still not identified by name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-07T14:46:53.528130Z",
     "start_time": "2019-07-07T14:46:53.189154Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[42, 1, 1, 15730712], [26, 2, 1, 14582612], [25, 2, 1, 20672047], [42, 2, 4, 15492377], [47, 1, 1, 18155638]]\n"
     ]
    }
   ],
   "source": [
    "# open file (read as binary) and read lines into \"raw_data\"\n",
    "raw_data = open('apr17pub.dat', 'rb').readlines()\n",
    "\n",
    "wgt = d['PWCMPWGT']  # Location of sample weight variable\n",
    "\n",
    "# unpack and store data of interest if sample weight > 0\n",
    "data = [[*map(int, unpacker(row))] for row in raw_data\n",
    "        if int(row[wgt[0]:wgt[1]]) > 0]\n",
    "\n",
    "print(data[:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create pandas dataframe from CPS data\n",
    "\n",
    "Pandas is a useful tool for working with the data--here is it used to filter out the observations by age and gender. I also convert the sample weights to the values they are intended to represent, as the data dictionary indicates that they have four implied decimal places. That is, the first person in the sample below represents \"1,458.2612\" people in the US population. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-07T14:46:53.606959Z",
     "start_time": "2019-07-07T14:46:53.529470Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " PRTAGE  PESEX  PREMPNOT   PWCMPWGT\n",
      "     26      2         1  1458.2612\n",
      "     25      2         1  2067.2047\n",
      "     42      2         4  1549.2377\n",
      "     49      2         1  1633.0038\n",
      "     26      2         1  1611.2316\n"
     ]
    }
   ],
   "source": [
    "# Pandas dataframe of women age 25 to 54\n",
    "df = (pd.DataFrame(data, columns=d.keys())\n",
    "      .query('PESEX == 2 and 25 <= PRTAGE <= 54')\n",
    "      .assign(PWCMPWGT = lambda x: x['PWCMPWGT'] / 10000))\n",
    "\n",
    "print(df.head().to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Benchmarking against BLS published data\n",
    "\n",
    "The [published value](https://data.bls.gov/timeseries/LNU02300062) for April 2017 is 72.3%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-07T14:46:53.612687Z",
     "start_time": "2019-07-07T14:46:53.608522Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "April 2017: 72.3%\n"
     ]
    }
   ],
   "source": [
    "# identify employed portion of group as 1 & the rest as 0\n",
    "empl = np.where(df['PREMPNOT'] == 1, 1, 0)\n",
    "\n",
    "# take sample weighted average of employed portion of group\n",
    "epop = np.average(empl, weights=df['PWCMPWGT'])\n",
    "\n",
    "# print out the result to check against LNU02300062\n",
    "print(f'April 2017: {epop*100:.1f}%')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}