src/tidy-faa.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tidy FAA survey\n",
    "\n",
    "In response to a public records request made by The Times, the FAA provided a decade of its annual estimates about the activity of America's most common helicopter models. This data is not published online. The records provided to The Times were manually input into a spreadsheet.\n",
    "\n",
    "This notebook filters the released data to population counts and flight-hour estimates for models that have appeared in all ten years of survey data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "%store -r"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in the raw survey data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "survey = pd.read_csv(os.path.join(input_dir, \"faa_general_aviation_survey.csv\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Identify all the models that have been surveyed by the FAA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_counts = survey.groupby([\n",
    "    'latimes_make',\n",
    "    'latimes_model'\n",
    "]).size().rename(\"survey_count\").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>latimes_make</th>\n",
       "      <th>latimes_model</th>\n",
       "      <th>survey_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AGUSTA</td>\n",
       "      <td>109</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AGUSTA</td>\n",
       "      <td>119</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AGUSTA</td>\n",
       "      <td>139</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AIRBUS</td>\n",
       "      <td>120</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AIRBUS</td>\n",
       "      <td>130</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AIRBUS</td>\n",
       "      <td>135</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AIRBUS</td>\n",
       "      <td>350</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>BELL</td>\n",
       "      <td>206</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>BELL</td>\n",
       "      <td>212</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>BELL</td>\n",
       "      <td>222</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>BELL</td>\n",
       "      <td>407</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>BELL</td>\n",
       "      <td>412</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>BELL</td>\n",
       "      <td>429</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>BELL</td>\n",
       "      <td>47</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>BELL</td>\n",
       "      <td>OH-58</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>BELL</td>\n",
       "      <td>TH-13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>BELL</td>\n",
       "      <td>UH-1H</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ENSTROM</td>\n",
       "      <td>280</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>ENSTROM</td>\n",
       "      <td>F28</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>HILLER</td>\n",
       "      <td>UH-12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>HUGHES</td>\n",
       "      <td>269</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>HUGHES</td>\n",
       "      <td>369</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>HUGHES</td>\n",
       "      <td>OH</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>HUGHES</td>\n",
       "      <td>TH</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>HUGHES</td>\n",
       "      <td>YO</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>MBB</td>\n",
       "      <td>105</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>MBB</td>\n",
       "      <td>117</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>MCDONNELL DOUGLAS</td>\n",
       "      <td>369</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>MITSUBISHI</td>\n",
       "      <td>MU2B</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ROBINSON</td>\n",
       "      <td>R22</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>ROBINSON</td>\n",
       "      <td>R44</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>ROBINSON</td>\n",
       "      <td>R66</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>ROCKWELL</td>\n",
       "      <td>690</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>ROTARY</td>\n",
       "      <td>RAF2000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>SCHLEICHER</td>\n",
       "      <td>ASW27</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>SCHWEIZER</td>\n",
       "      <td>269</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>SIKORSKY</td>\n",
       "      <td>61</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>SIKORSKY</td>\n",
       "      <td>76</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>SIKORSKY</td>\n",
       "      <td>92</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>SOCATA</td>\n",
       "      <td>TBM700</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>SWEARINGEN</td>\n",
       "      <td>SA227</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>TAYLORCRAFT</td>\n",
       "      <td>BC12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>VANS</td>\n",
       "      <td>RV10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>VANS</td>\n",
       "      <td>RV4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>VANS</td>\n",
       "      <td>RV6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>VANS</td>\n",
       "      <td>RV7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>VANS</td>\n",
       "      <td>RV8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>VANS</td>\n",
       "      <td>RV9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>YAKOVLEV</td>\n",
       "      <td>YAK52</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>ZENAIR</td>\n",
       "      <td>CH601</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>ZENAIR</td>\n",
       "      <td>CH701</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         latimes_make latimes_model  survey_count\n",
       "0              AGUSTA           109            10\n",
       "1              AGUSTA           119             2\n",
       "2              AGUSTA           139             4\n",
       "3              AIRBUS           120             3\n",
       "4              AIRBUS           130            10\n",
       "5              AIRBUS           135            10\n",
       "6              AIRBUS           350            10\n",
       "7                BELL           206            10\n",
       "8                BELL           212             5\n",
       "9                BELL           222             1\n",
       "10               BELL           407            10\n",
       "11               BELL           412             4\n",
       "12               BELL           429             1\n",
       "13               BELL            47             7\n",
       "14               BELL         OH-58            11\n",
       "15               BELL         TH-13             1\n",
       "16               BELL         UH-1H             9\n",
       "17            ENSTROM           280             1\n",
       "18            ENSTROM           F28             4\n",
       "19             HILLER         UH-12             2\n",
       "20             HUGHES           269             3\n",
       "21             HUGHES           369            10\n",
       "22             HUGHES            OH             2\n",
       "23             HUGHES            TH             1\n",
       "24             HUGHES            YO             2\n",
       "25                MBB           105             6\n",
       "26                MBB           117             9\n",
       "27  MCDONNELL DOUGLAS           369            10\n",
       "28         MITSUBISHI          MU2B             1\n",
       "29           ROBINSON           R22            10\n",
       "30           ROBINSON           R44            10\n",
       "31           ROBINSON           R66             1\n",
       "32           ROCKWELL           690             1\n",
       "33             ROTARY       RAF2000             1\n",
       "34         SCHLEICHER         ASW27             1\n",
       "35          SCHWEIZER           269            10\n",
       "36           SIKORSKY            61             2\n",
       "37           SIKORSKY            76            10\n",
       "38           SIKORSKY            92             2\n",
       "39             SOCATA        TBM700             1\n",
       "40         SWEARINGEN         SA227             1\n",
       "41        TAYLORCRAFT          BC12             1\n",
       "42               VANS          RV10             1\n",
       "43               VANS           RV4             1\n",
       "44               VANS           RV6             1\n",
       "45               VANS           RV7             1\n",
       "46               VANS           RV8             1\n",
       "47               VANS           RV9             1\n",
       "48           YAKOVLEV         YAK52             1\n",
       "49             ZENAIR         CH601             1\n",
       "50             ZENAIR         CH701             1"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model_counts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter down to models that appear in all ten years of survey data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "qualified_models = model_counts[model_counts.survey_count == 10][\n",
    "    ['latimes_make', 'latimes_model']\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>latimes_make</th>\n",
       "      <th>latimes_model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AGUSTA</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AIRBUS</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AIRBUS</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AIRBUS</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>BELL</td>\n",
       "      <td>206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>BELL</td>\n",
       "      <td>407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>HUGHES</td>\n",
       "      <td>369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>MCDONNELL DOUGLAS</td>\n",
       "      <td>369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ROBINSON</td>\n",
       "      <td>R22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>ROBINSON</td>\n",
       "      <td>R44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>SCHWEIZER</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>SIKORSKY</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         latimes_make latimes_model\n",
       "0              AGUSTA           109\n",
       "4              AIRBUS           130\n",
       "5              AIRBUS           135\n",
       "6              AIRBUS           350\n",
       "7                BELL           206\n",
       "10               BELL           407\n",
       "21             HUGHES           369\n",
       "27  MCDONNELL DOUGLAS           369\n",
       "29           ROBINSON           R22\n",
       "30           ROBINSON           R44\n",
       "35          SCHWEIZER           269\n",
       "37           SIKORSKY            76"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qualified_models"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter down the survey data to only include those models that have qualified for analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "qualified_survey = survey.merge(\n",
    "    qualified_models,\n",
    "    on=[\"latimes_make\", \"latimes_model\"],\n",
    "    how=\"inner\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trim down the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "trimmed_qualified_survey  = qualified_survey[[\n",
    "    'year',\n",
    "    'latimes_make',\n",
    "    'latimes_model',\n",
    "    'population_count',\n",
    "    'active_count',\n",
    "    'total_hours'\n",
    "]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_qualified_survey  = trimmed_qualified_survey.sort_values([\n",
    "    'latimes_make',\n",
    "    'latimes_model',\n",
    "    'year'\n",
    "])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a combined make and model column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_qualified_survey['latimes_make_and_model'] = cleaned_qualified_survey.latimes_make + \" \" + cleaned_qualified_survey.latimes_model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Output the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_qualified_survey.to_csv(os.path.join(output_dir, \"tidy-survey.csv\"), index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}