ufo_census_merge&clean.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b648b9d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1310bc15",
   "metadata": {},
   "source": [
    "# UFO Data \n",
    "Importing UFO data, standardizing datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d15ebfdd",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\hfatt\\anaconda3\\envs\\Pythondata\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3186: DtypeWarning: Columns (5,9) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "ufo_raw = pd.read_csv(\"data/ufo_scrubbed.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "cec6306d",
   "metadata": {},
   "outputs": [],
   "source": [
    "ufo_raw[\"datetime\"] = pd.to_datetime(ufo_raw[\"datetime\"], format=\"%m/%d/%Y %H:%M\", errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a4ff08ca",
   "metadata": {
    "scrolled": true
   },
   "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>datetime</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>shape</th>\n",
       "      <th>duration (seconds)</th>\n",
       "      <th>duration (hours/min)</th>\n",
       "      <th>comments</th>\n",
       "      <th>date posted</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>NaT</td>\n",
       "      <td>rome</td>\n",
       "      <td>ny</td>\n",
       "      <td>us</td>\n",
       "      <td>oval</td>\n",
       "      <td>120</td>\n",
       "      <td>a min or two</td>\n",
       "      <td>I was walking from the garage to the house&amp;#44...</td>\n",
       "      <td>2/1/2007</td>\n",
       "      <td>43.2127778</td>\n",
       "      <td>-75.456111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>693</th>\n",
       "      <td>NaT</td>\n",
       "      <td>chulucanas-piura la vieja (peru)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>other</td>\n",
       "      <td>6312000</td>\n",
       "      <td>2 years</td>\n",
       "      <td>go to:  http://www.24horas.com.pe/data/videos/...</td>\n",
       "      <td>3/4/2003</td>\n",
       "      <td>-5.129547</td>\n",
       "      <td>-80.120569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>962</th>\n",
       "      <td>NaT</td>\n",
       "      <td>novi</td>\n",
       "      <td>mi</td>\n",
       "      <td>us</td>\n",
       "      <td>triangle</td>\n",
       "      <td>300</td>\n",
       "      <td>5 minutes</td>\n",
       "      <td>V shaped and 8 big and very brite lights&amp;#44mo...</td>\n",
       "      <td>10/30/2012</td>\n",
       "      <td>42.4805556</td>\n",
       "      <td>-83.475556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067</th>\n",
       "      <td>NaT</td>\n",
       "      <td>salatiga (indonesia)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>disk</td>\n",
       "      <td>22</td>\n",
       "      <td>22 seconds</td>\n",
       "      <td>UFO in Salatiga&amp;#44Indonesia</td>\n",
       "      <td>10/31/2003</td>\n",
       "      <td>-7.33683</td>\n",
       "      <td>110.498817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1221</th>\n",
       "      <td>NaT</td>\n",
       "      <td>cincinnati</td>\n",
       "      <td>oh</td>\n",
       "      <td>us</td>\n",
       "      <td>fireball</td>\n",
       "      <td>300</td>\n",
       "      <td>3-5 minutes</td>\n",
       "      <td>A bright orange light split into four&amp;#44 did ...</td>\n",
       "      <td>10/14/2013</td>\n",
       "      <td>39.1619444</td>\n",
       "      <td>-84.456944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79136</th>\n",
       "      <td>NaT</td>\n",
       "      <td>boonville</td>\n",
       "      <td>nc</td>\n",
       "      <td>us</td>\n",
       "      <td>cigar</td>\n",
       "      <td>10800</td>\n",
       "      <td>3+ hours</td>\n",
       "      <td>Unusual activity in our small community&amp;#44 cr...</td>\n",
       "      <td>9/9/2013</td>\n",
       "      <td>36.2325</td>\n",
       "      <td>-80.708333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79137</th>\n",
       "      <td>NaT</td>\n",
       "      <td>redondo beach</td>\n",
       "      <td>ca</td>\n",
       "      <td>us</td>\n",
       "      <td>triangle</td>\n",
       "      <td>300</td>\n",
       "      <td>5 minutes</td>\n",
       "      <td>Triangulat UFO seen over Redondo Beach&amp;#44 Cal...</td>\n",
       "      <td>12/16/2005</td>\n",
       "      <td>33.8492</td>\n",
       "      <td>-118.387500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79740</th>\n",
       "      <td>NaT</td>\n",
       "      <td>portsmouth</td>\n",
       "      <td>oh</td>\n",
       "      <td>us</td>\n",
       "      <td>changing</td>\n",
       "      <td>900</td>\n",
       "      <td>15 minutes</td>\n",
       "      <td>A bright red light hoovering in the sky.</td>\n",
       "      <td>9/13/2002</td>\n",
       "      <td>38.7317</td>\n",
       "      <td>-82.997778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79759</th>\n",
       "      <td>NaT</td>\n",
       "      <td>montrose</td>\n",
       "      <td>co</td>\n",
       "      <td>us</td>\n",
       "      <td>unknown</td>\n",
       "      <td>1200</td>\n",
       "      <td>20 minutes</td>\n",
       "      <td>Stationary object with three pulsating lights ...</td>\n",
       "      <td>9/9/2004</td>\n",
       "      <td>38.4783</td>\n",
       "      <td>-107.875556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79942</th>\n",
       "      <td>NaT</td>\n",
       "      <td>cedar rapids</td>\n",
       "      <td>ia</td>\n",
       "      <td>us</td>\n",
       "      <td>changing</td>\n",
       "      <td>900</td>\n",
       "      <td>15 min</td>\n",
       "      <td>WE SEEN IT</td>\n",
       "      <td>9/17/2003</td>\n",
       "      <td>42.0083</td>\n",
       "      <td>-91.643889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>694 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      datetime                              city state country     shape  \\\n",
       "388        NaT                              rome    ny      us      oval   \n",
       "693        NaT  chulucanas-piura la vieja (peru)   NaN     NaN     other   \n",
       "962        NaT                              novi    mi      us  triangle   \n",
       "1067       NaT              salatiga (indonesia)   NaN     NaN      disk   \n",
       "1221       NaT                        cincinnati    oh      us  fireball   \n",
       "...        ...                               ...   ...     ...       ...   \n",
       "79136      NaT                         boonville    nc      us     cigar   \n",
       "79137      NaT                     redondo beach    ca      us  triangle   \n",
       "79740      NaT                        portsmouth    oh      us  changing   \n",
       "79759      NaT                          montrose    co      us   unknown   \n",
       "79942      NaT                      cedar rapids    ia      us  changing   \n",
       "\n",
       "      duration (seconds) duration (hours/min)  \\\n",
       "388                  120         a min or two   \n",
       "693              6312000              2 years   \n",
       "962                  300            5 minutes   \n",
       "1067                  22           22 seconds   \n",
       "1221                 300          3-5 minutes   \n",
       "...                  ...                  ...   \n",
       "79136              10800             3+ hours   \n",
       "79137                300            5 minutes   \n",
       "79740                900           15 minutes   \n",
       "79759               1200           20 minutes   \n",
       "79942                900               15 min   \n",
       "\n",
       "                                                comments date posted  \\\n",
       "388    I was walking from the garage to the house&#44...    2/1/2007   \n",
       "693    go to:  http://www.24horas.com.pe/data/videos/...    3/4/2003   \n",
       "962    V shaped and 8 big and very brite lights&#44mo...  10/30/2012   \n",
       "1067                        UFO in Salatiga&#44Indonesia  10/31/2003   \n",
       "1221   A bright orange light split into four&#44 did ...  10/14/2013   \n",
       "...                                                  ...         ...   \n",
       "79136  Unusual activity in our small community&#44 cr...    9/9/2013   \n",
       "79137  Triangulat UFO seen over Redondo Beach&#44 Cal...  12/16/2005   \n",
       "79740           A bright red light hoovering in the sky.   9/13/2002   \n",
       "79759  Stationary object with three pulsating lights ...    9/9/2004   \n",
       "79942                                         WE SEEN IT   9/17/2003   \n",
       "\n",
       "         latitude  longitude   \n",
       "388    43.2127778  -75.456111  \n",
       "693     -5.129547  -80.120569  \n",
       "962    42.4805556  -83.475556  \n",
       "1067     -7.33683  110.498817  \n",
       "1221   39.1619444  -84.456944  \n",
       "...           ...         ...  \n",
       "79136     36.2325  -80.708333  \n",
       "79137     33.8492 -118.387500  \n",
       "79740     38.7317  -82.997778  \n",
       "79759     38.4783 -107.875556  \n",
       "79942     42.0083  -91.643889  \n",
       "\n",
       "[694 rows x 11 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo_raw[ufo_raw[\"datetime\"].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7b6edb6a",
   "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>datetime</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>shape</th>\n",
       "      <th>duration (seconds)</th>\n",
       "      <th>duration (hours/min)</th>\n",
       "      <th>comments</th>\n",
       "      <th>date posted</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ca</th>\n",
       "      <td>8848</td>\n",
       "      <td>8912</td>\n",
       "      <td>8912</td>\n",
       "      <td>8912</td>\n",
       "      <td>8684</td>\n",
       "      <td>8912</td>\n",
       "      <td>8912</td>\n",
       "      <td>8911</td>\n",
       "      <td>8912</td>\n",
       "      <td>8912</td>\n",
       "      <td>8912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wa</th>\n",
       "      <td>3939</td>\n",
       "      <td>3966</td>\n",
       "      <td>3966</td>\n",
       "      <td>3966</td>\n",
       "      <td>3708</td>\n",
       "      <td>3966</td>\n",
       "      <td>3966</td>\n",
       "      <td>3965</td>\n",
       "      <td>3966</td>\n",
       "      <td>3966</td>\n",
       "      <td>3966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fl</th>\n",
       "      <td>3812</td>\n",
       "      <td>3835</td>\n",
       "      <td>3835</td>\n",
       "      <td>3835</td>\n",
       "      <td>3754</td>\n",
       "      <td>3835</td>\n",
       "      <td>3835</td>\n",
       "      <td>3835</td>\n",
       "      <td>3835</td>\n",
       "      <td>3835</td>\n",
       "      <td>3835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tx</th>\n",
       "      <td>3413</td>\n",
       "      <td>3447</td>\n",
       "      <td>3447</td>\n",
       "      <td>3447</td>\n",
       "      <td>3399</td>\n",
       "      <td>3447</td>\n",
       "      <td>3447</td>\n",
       "      <td>3446</td>\n",
       "      <td>3447</td>\n",
       "      <td>3447</td>\n",
       "      <td>3447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ny</th>\n",
       "      <td>2958</td>\n",
       "      <td>2980</td>\n",
       "      <td>2980</td>\n",
       "      <td>2980</td>\n",
       "      <td>2915</td>\n",
       "      <td>2980</td>\n",
       "      <td>2980</td>\n",
       "      <td>2980</td>\n",
       "      <td>2980</td>\n",
       "      <td>2980</td>\n",
       "      <td>2980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>il</th>\n",
       "      <td>2481</td>\n",
       "      <td>2499</td>\n",
       "      <td>2499</td>\n",
       "      <td>2499</td>\n",
       "      <td>2447</td>\n",
       "      <td>2499</td>\n",
       "      <td>2499</td>\n",
       "      <td>2499</td>\n",
       "      <td>2499</td>\n",
       "      <td>2499</td>\n",
       "      <td>2499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>az</th>\n",
       "      <td>2398</td>\n",
       "      <td>2414</td>\n",
       "      <td>2414</td>\n",
       "      <td>2414</td>\n",
       "      <td>2362</td>\n",
       "      <td>2414</td>\n",
       "      <td>2414</td>\n",
       "      <td>2414</td>\n",
       "      <td>2414</td>\n",
       "      <td>2414</td>\n",
       "      <td>2414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pa</th>\n",
       "      <td>2354</td>\n",
       "      <td>2366</td>\n",
       "      <td>2366</td>\n",
       "      <td>2366</td>\n",
       "      <td>2319</td>\n",
       "      <td>2366</td>\n",
       "      <td>2366</td>\n",
       "      <td>2366</td>\n",
       "      <td>2366</td>\n",
       "      <td>2366</td>\n",
       "      <td>2366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>oh</th>\n",
       "      <td>2252</td>\n",
       "      <td>2275</td>\n",
       "      <td>2275</td>\n",
       "      <td>2275</td>\n",
       "      <td>2252</td>\n",
       "      <td>2275</td>\n",
       "      <td>2275</td>\n",
       "      <td>2274</td>\n",
       "      <td>2275</td>\n",
       "      <td>2275</td>\n",
       "      <td>2275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mi</th>\n",
       "      <td>1826</td>\n",
       "      <td>1836</td>\n",
       "      <td>1836</td>\n",
       "      <td>1836</td>\n",
       "      <td>1781</td>\n",
       "      <td>1836</td>\n",
       "      <td>1836</td>\n",
       "      <td>1835</td>\n",
       "      <td>1836</td>\n",
       "      <td>1836</td>\n",
       "      <td>1836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>or</th>\n",
       "      <td>1728</td>\n",
       "      <td>1747</td>\n",
       "      <td>1747</td>\n",
       "      <td>1747</td>\n",
       "      <td>1667</td>\n",
       "      <td>1747</td>\n",
       "      <td>1747</td>\n",
       "      <td>1747</td>\n",
       "      <td>1747</td>\n",
       "      <td>1747</td>\n",
       "      <td>1747</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       datetime  city  state  country  shape  duration (seconds)  \\\n",
       "state                                                              \n",
       "ca         8848  8912   8912     8912   8684                8912   \n",
       "wa         3939  3966   3966     3966   3708                3966   \n",
       "fl         3812  3835   3835     3835   3754                3835   \n",
       "tx         3413  3447   3447     3447   3399                3447   \n",
       "ny         2958  2980   2980     2980   2915                2980   \n",
       "il         2481  2499   2499     2499   2447                2499   \n",
       "az         2398  2414   2414     2414   2362                2414   \n",
       "pa         2354  2366   2366     2366   2319                2366   \n",
       "oh         2252  2275   2275     2275   2252                2275   \n",
       "mi         1826  1836   1836     1836   1781                1836   \n",
       "or         1728  1747   1747     1747   1667                1747   \n",
       "\n",
       "       duration (hours/min)  comments  date posted  latitude  longitude   \n",
       "state                                                                     \n",
       "ca                     8912      8911         8912      8912        8912  \n",
       "wa                     3966      3965         3966      3966        3966  \n",
       "fl                     3835      3835         3835      3835        3835  \n",
       "tx                     3447      3446         3447      3447        3447  \n",
       "ny                     2980      2980         2980      2980        2980  \n",
       "il                     2499      2499         2499      2499        2499  \n",
       "az                     2414      2414         2414      2414        2414  \n",
       "pa                     2366      2366         2366      2366        2366  \n",
       "oh                     2275      2274         2275      2275        2275  \n",
       "mi                     1836      1835         1836      1836        1836  \n",
       "or                     1747      1747         1747      1747        1747  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#find U.S. states with the highest number of UFO sightings reported\n",
    "ufo_sort=ufo_raw[(ufo_raw[\"country\"] == \"us\")].groupby(ufo_raw[\"state\"]).count()\n",
    "ufo_sort.sort_values(by=\"city\", ascending=False).head(11)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "604307a4",
   "metadata": {},
   "source": [
    "# U.S. Census Data\n",
    "\n",
    "Importing census data, removing prior to 1950 and after 2010, making dataframes for 11 UFO focus states that have state population data for 1950-2020."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "73f5a45d",
   "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>Name</th>\n",
       "      <th>Geography Type</th>\n",
       "      <th>Year</th>\n",
       "      <th>Resident Population</th>\n",
       "      <th>Percent Change in Resident Population</th>\n",
       "      <th>Resident Population Density</th>\n",
       "      <th>Resident Population Density Rank</th>\n",
       "      <th>Number of Representatives</th>\n",
       "      <th>Change in Number of Representatives</th>\n",
       "      <th>Average Apportionment Population Per Representative</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>State</td>\n",
       "      <td>1910</td>\n",
       "      <td>2,138,093</td>\n",
       "      <td>16.9</td>\n",
       "      <td>42.2</td>\n",
       "      <td>25.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>213,809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>State</td>\n",
       "      <td>1910</td>\n",
       "      <td>64,356</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.1</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>State</td>\n",
       "      <td>1910</td>\n",
       "      <td>204,354</td>\n",
       "      <td>66.2</td>\n",
       "      <td>1.8</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>State</td>\n",
       "      <td>1910</td>\n",
       "      <td>1,574,449</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.3</td>\n",
       "      <td>30.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>224,921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>State</td>\n",
       "      <td>1910</td>\n",
       "      <td>2,377,549</td>\n",
       "      <td>60.1</td>\n",
       "      <td>15.3</td>\n",
       "      <td>38.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>216,051</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Name Geography Type  Year Resident Population  \\\n",
       "0     Alabama          State  1910           2,138,093   \n",
       "1      Alaska          State  1910              64,356   \n",
       "2     Arizona          State  1910             204,354   \n",
       "3    Arkansas          State  1910           1,574,449   \n",
       "4  California          State  1910           2,377,549   \n",
       "\n",
       "   Percent Change in Resident Population Resident Population Density  \\\n",
       "0                                   16.9                        42.2   \n",
       "1                                    1.2                         0.1   \n",
       "2                                   66.2                         1.8   \n",
       "3                                   20.0                        30.3   \n",
       "4                                   60.1                        15.3   \n",
       "\n",
       "   Resident Population Density Rank  Number of Representatives  \\\n",
       "0                              25.0                       10.0   \n",
       "1                              52.0                        NaN   \n",
       "2                              49.0                        NaN   \n",
       "3                              30.0                        7.0   \n",
       "4                              38.0                       11.0   \n",
       "\n",
       "   Change in Number of Representatives  \\\n",
       "0                                  1.0   \n",
       "1                                  NaN   \n",
       "2                                  NaN   \n",
       "3                                  0.0   \n",
       "4                                  3.0   \n",
       "\n",
       "  Average Apportionment Population Per Representative  \n",
       "0                                            213,809   \n",
       "1                                                NaN   \n",
       "2                                                NaN   \n",
       "3                                            224,921   \n",
       "4                                            216,051   "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_census = pd.read_csv(\"data/US_census.csv\")\n",
    "us_census.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "cf773390",
   "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>Name</th>\n",
       "      <th>Geography Type</th>\n",
       "      <th>Year</th>\n",
       "      <th>Resident Population</th>\n",
       "      <th>Percent Change in Resident Population</th>\n",
       "      <th>Resident Population Density</th>\n",
       "      <th>Resident Population Density Rank</th>\n",
       "      <th>Number of Representatives</th>\n",
       "      <th>Change in Number of Representatives</th>\n",
       "      <th>Average Apportionment Population Per Representative</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>State</td>\n",
       "      <td>1950</td>\n",
       "      <td>3,061,743</td>\n",
       "      <td>8.1</td>\n",
       "      <td>60.5</td>\n",
       "      <td>24.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>340,194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>State</td>\n",
       "      <td>1950</td>\n",
       "      <td>128,643</td>\n",
       "      <td>77.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>State</td>\n",
       "      <td>1950</td>\n",
       "      <td>749,587</td>\n",
       "      <td>50.1</td>\n",
       "      <td>6.6</td>\n",
       "      <td>47.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>374,794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>State</td>\n",
       "      <td>1950</td>\n",
       "      <td>1,909,511</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>36.7</td>\n",
       "      <td>34.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>318,252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>California</td>\n",
       "      <td>State</td>\n",
       "      <td>1950</td>\n",
       "      <td>10,586,223</td>\n",
       "      <td>53.3</td>\n",
       "      <td>68</td>\n",
       "      <td>22.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>352,874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>622</th>\n",
       "      <td>Midwest Region</td>\n",
       "      <td>Region</td>\n",
       "      <td>2010</td>\n",
       "      <td>66,927,001</td>\n",
       "      <td>3.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>623</th>\n",
       "      <td>Northeast Region</td>\n",
       "      <td>Region</td>\n",
       "      <td>2010</td>\n",
       "      <td>55,317,240</td>\n",
       "      <td>3.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>624</th>\n",
       "      <td>South Region</td>\n",
       "      <td>Region</td>\n",
       "      <td>2010</td>\n",
       "      <td>114,555,744</td>\n",
       "      <td>14.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>625</th>\n",
       "      <td>West Region</td>\n",
       "      <td>Region</td>\n",
       "      <td>2010</td>\n",
       "      <td>71,945,553</td>\n",
       "      <td>13.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626</th>\n",
       "      <td>United States</td>\n",
       "      <td>Nation</td>\n",
       "      <td>2010</td>\n",
       "      <td>308,745,538</td>\n",
       "      <td>9.7</td>\n",
       "      <td>87.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>435.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>710,767</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>399 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Name Geography Type  Year Resident Population  \\\n",
       "228           Alabama          State  1950           3,061,743   \n",
       "229            Alaska          State  1950             128,643   \n",
       "230           Arizona          State  1950             749,587   \n",
       "231          Arkansas          State  1950           1,909,511   \n",
       "232        California          State  1950          10,586,223   \n",
       "..                ...            ...   ...                 ...   \n",
       "622    Midwest Region         Region  2010          66,927,001   \n",
       "623  Northeast Region         Region  2010          55,317,240   \n",
       "624      South Region         Region  2010         114,555,744   \n",
       "625       West Region         Region  2010          71,945,553   \n",
       "626     United States         Nation  2010         308,745,538   \n",
       "\n",
       "     Percent Change in Resident Population Resident Population Density  \\\n",
       "228                                    8.1                        60.5   \n",
       "229                                   77.4                         0.2   \n",
       "230                                   50.1                         6.6   \n",
       "231                                   -2.0                        36.7   \n",
       "232                                   53.3                          68   \n",
       "..                                     ...                         ...   \n",
       "622                                    3.9                         NaN   \n",
       "623                                    3.2                         NaN   \n",
       "624                                   14.3                         NaN   \n",
       "625                                   13.8                         NaN   \n",
       "626                                    9.7                        87.4   \n",
       "\n",
       "     Resident Population Density Rank  Number of Representatives  \\\n",
       "228                              24.0                        9.0   \n",
       "229                              52.0                        NaN   \n",
       "230                              47.0                        2.0   \n",
       "231                              34.0                        6.0   \n",
       "232                              22.0                       30.0   \n",
       "..                                ...                        ...   \n",
       "622                               NaN                        NaN   \n",
       "623                               NaN                        NaN   \n",
       "624                               NaN                        NaN   \n",
       "625                               NaN                        NaN   \n",
       "626                               NaN                      435.0   \n",
       "\n",
       "     Change in Number of Representatives  \\\n",
       "228                                  0.0   \n",
       "229                                  NaN   \n",
       "230                                  0.0   \n",
       "231                                 -1.0   \n",
       "232                                  7.0   \n",
       "..                                   ...   \n",
       "622                                  NaN   \n",
       "623                                  NaN   \n",
       "624                                  NaN   \n",
       "625                                  NaN   \n",
       "626                                 12.0   \n",
       "\n",
       "    Average Apportionment Population Per Representative  \n",
       "228                                            340,194   \n",
       "229                                                NaN   \n",
       "230                                            374,794   \n",
       "231                                            318,252   \n",
       "232                                            352,874   \n",
       "..                                                 ...   \n",
       "622                                                NaN   \n",
       "623                                                NaN   \n",
       "624                                                NaN   \n",
       "625                                                NaN   \n",
       "626                                            710,767   \n",
       "\n",
       "[399 rows x 10 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_census = us_census[(us_census[\"Year\"] >= 1950) & (us_census[\"Year\"] < 2020)]\n",
    "us_census"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4561f95c",
   "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>state</th>\n",
       "      <th>decade</th>\n",
       "      <th>Resident Population</th>\n",
       "      <th>Percent Change in Resident Population</th>\n",
       "      <th>Resident Population Density</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>1950</td>\n",
       "      <td>749,587</td>\n",
       "      <td>50.1</td>\n",
       "      <td>6.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>California</td>\n",
       "      <td>1950</td>\n",
       "      <td>10,586,223</td>\n",
       "      <td>53.3</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>Florida</td>\n",
       "      <td>1950</td>\n",
       "      <td>2,771,305</td>\n",
       "      <td>46.1</td>\n",
       "      <td>51.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>1950</td>\n",
       "      <td>8,712,176</td>\n",
       "      <td>10.3</td>\n",
       "      <td>156.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>Michigan</td>\n",
       "      <td>1950</td>\n",
       "      <td>6,371,766</td>\n",
       "      <td>21.2</td>\n",
       "      <td>112.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>605</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>2010</td>\n",
       "      <td>11,536,504</td>\n",
       "      <td>1.6</td>\n",
       "      <td>282.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>2010</td>\n",
       "      <td>3,831,074</td>\n",
       "      <td>12.0</td>\n",
       "      <td>39.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>608</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>2010</td>\n",
       "      <td>12,702,379</td>\n",
       "      <td>3.4</td>\n",
       "      <td>283.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>Texas</td>\n",
       "      <td>2010</td>\n",
       "      <td>25,145,561</td>\n",
       "      <td>20.6</td>\n",
       "      <td>96.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>618</th>\n",
       "      <td>Washington</td>\n",
       "      <td>2010</td>\n",
       "      <td>6,724,540</td>\n",
       "      <td>14.1</td>\n",
       "      <td>101.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>77 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            state  decade Resident Population  \\\n",
       "230       Arizona    1950             749,587   \n",
       "232    California    1950          10,586,223   \n",
       "237       Florida    1950           2,771,305   \n",
       "241      Illinois    1950           8,712,176   \n",
       "250      Michigan    1950           6,371,766   \n",
       "..            ...     ...                 ...   \n",
       "605          Ohio    2010          11,536,504   \n",
       "607        Oregon    2010           3,831,074   \n",
       "608  Pennsylvania    2010          12,702,379   \n",
       "614         Texas    2010          25,145,561   \n",
       "618    Washington    2010           6,724,540   \n",
       "\n",
       "     Percent Change in Resident Population Resident Population Density  \n",
       "230                                   50.1                         6.6  \n",
       "232                                   53.3                          68  \n",
       "237                                   46.1                        51.7  \n",
       "241                                   10.3                       156.9  \n",
       "250                                   21.2                       112.7  \n",
       "..                                     ...                         ...  \n",
       "605                                    1.6                       282.3  \n",
       "607                                   12.0                        39.9  \n",
       "608                                    3.4                       283.9  \n",
       "614                                   20.6                        96.3  \n",
       "618                                   14.1                       101.2  \n",
       "\n",
       "[77 rows x 5 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "census_columns = ['Name', 'Year', 'Resident Population', 'Percent Change in Resident Population',\n",
    "       'Resident Population Density']\n",
    "state_pop_df = us_census.loc[(us_census[\"Name\"] == \"Arizona\") | (us_census[\"Name\"] == \"California\")\n",
    "                                   | (us_census[\"Name\"] == \"Florida\") \n",
    "                                   | (us_census[\"Name\"] == \"Illinois\") | (us_census[\"Name\"] == \"Michigan\") \n",
    "                                   | (us_census[\"Name\"] == \"New York\") | (us_census[\"Name\"] == \"Ohio\") \n",
    "                                   | (us_census[\"Name\"] == \"Oregon\")\n",
    "                                   | (us_census[\"Name\"] == \"Pennsylvania\") | (us_census[\"Name\"] == \"Texas\")\n",
    "                                   | (us_census[\"Name\"] == \"Washington\"), census_columns]\n",
    "\n",
    "state_pop_df.rename(columns = {'Name':'state', 'Year': 'decade'}, inplace = True)\n",
    "state_pop_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d35ab76",
   "metadata": {},
   "source": [
    "az_census = us_census[us_census[\"Name\"] == \"Arizona\"]\n",
    "ca_census = us_census[us_census[\"Name\"] == \"Calfornia\"]\n",
    "fl_census = us_census[us_census[\"Name\"] == \"Florida\"]\n",
    "il_census = us_census[us_census[\"Name\"] == \"Illinois\"]\n",
    "mi_census = us_census[us_census[\"Name\"] == \"Michigan\"]\n",
    "ny_census = us_census[us_census[\"Name\"] == \"New York\"]\n",
    "oh_census = us_census[us_census[\"Name\"] == \"Ohio\"]\n",
    "or_census = us_census[us_census[\"Name\"] == \"Oregon\"]\n",
    "pa_census = us_census[us_census[\"Name\"] == \"Pennsylvania\"]\n",
    "tx_census = us_census[us_census[\"Name\"] == \"Texas\"]\n",
    "wa_census = us_census[us_census[\"Name\"] == \"Washington\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4245975d",
   "metadata": {},
   "outputs": [],
   "source": [
    "focus_states = [\"Arizona\", \"California\", \"Florida\", \"Illinois\", \"Michigan\", \"New York\", \"Ohio\", \"Oregon\", \n",
    "          \"Pennsylvania\", \"Texas\", \"Washington\"]\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe55be98",
   "metadata": {},
   "source": [
    "# UFO Data\n",
    "Binning UFO data into decades, pulling data for 11 focus states."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "307530b9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\hfatt\\anaconda3\\envs\\Pythondata\\lib\\site-packages\\ipykernel_launcher.py:6: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n",
      "C:\\Users\\hfatt\\anaconda3\\envs\\Pythondata\\lib\\site-packages\\ipykernel_launcher.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n",
      "C:\\Users\\hfatt\\anaconda3\\envs\\Pythondata\\lib\\site-packages\\ipykernel_launcher.py:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "ufo_dropped = ufo_raw.dropna()\n",
    "\n",
    "#datetime\n",
    "ufo_us = ufo_dropped[ufo_dropped[\"country\"] == \"us\"]\n",
    "ufo_by_year = ufo_us\n",
    "ufo_by_year[\"datetime\"] = pd.to_datetime(ufo_by_year[\"datetime\"], format=\"%m/%d/%Y %H:%M\", errors='coerce')\n",
    "ufo_by_year[ufo_by_year[\"datetime\"].isna()].dropna()\n",
    "ufo_by_year[\"year\"] = pd.DatetimeIndex(ufo_by_year['datetime']).year\n",
    "ufo_by_year.head()\n",
    "\n",
    "#grouping by decade\n",
    "groupnames1 = [1950, 1960, 1970, 1980, 1990, 2000, 2010, 2020]\n",
    "bins = [1950.0, 1959.0, 1969.0, 1979.0,1989.0,1999.0,2009.0,2019,2024.0]\n",
    "ufo_by_year[\"decade\"] = pd.cut(ufo_by_year[\"year\"],bins,labels=groupnames1,include_lowest=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "884bec7b",
   "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>datetime</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>shape</th>\n",
       "      <th>duration (seconds)</th>\n",
       "      <th>duration (hours/min)</th>\n",
       "      <th>comments</th>\n",
       "      <th>date posted</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>year</th>\n",
       "      <th>decade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1956-10-10 21:00:00</td>\n",
       "      <td>edna</td>\n",
       "      <td>tx</td>\n",
       "      <td>us</td>\n",
       "      <td>circle</td>\n",
       "      <td>20</td>\n",
       "      <td>1/2 hour</td>\n",
       "      <td>My older brother and twin sister were leaving ...</td>\n",
       "      <td>1/17/2004</td>\n",
       "      <td>28.9783333</td>\n",
       "      <td>-96.645833</td>\n",
       "      <td>1956</td>\n",
       "      <td>1950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1960-10-10 20:00:00</td>\n",
       "      <td>kaneohe</td>\n",
       "      <td>hi</td>\n",
       "      <td>us</td>\n",
       "      <td>light</td>\n",
       "      <td>900</td>\n",
       "      <td>15 minutes</td>\n",
       "      <td>AS a Marine 1st Lt. flying an FJ4B fighter/att...</td>\n",
       "      <td>1/22/2004</td>\n",
       "      <td>21.4180556</td>\n",
       "      <td>-157.803611</td>\n",
       "      <td>1960</td>\n",
       "      <td>1960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1961-10-10 19:00:00</td>\n",
       "      <td>bristol</td>\n",
       "      <td>tn</td>\n",
       "      <td>us</td>\n",
       "      <td>sphere</td>\n",
       "      <td>300</td>\n",
       "      <td>5 minutes</td>\n",
       "      <td>My father is now 89 my brother 52 the girl wit...</td>\n",
       "      <td>4/27/2007</td>\n",
       "      <td>36.5950000</td>\n",
       "      <td>-82.188889</td>\n",
       "      <td>1961</td>\n",
       "      <td>1960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1965-10-10 23:45:00</td>\n",
       "      <td>norwalk</td>\n",
       "      <td>ct</td>\n",
       "      <td>us</td>\n",
       "      <td>disk</td>\n",
       "      <td>1200</td>\n",
       "      <td>20 minutes</td>\n",
       "      <td>A bright orange color changing to reddish colo...</td>\n",
       "      <td>10/2/1999</td>\n",
       "      <td>41.1175000</td>\n",
       "      <td>-73.408333</td>\n",
       "      <td>1965</td>\n",
       "      <td>1960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1966-10-10 20:00:00</td>\n",
       "      <td>pell city</td>\n",
       "      <td>al</td>\n",
       "      <td>us</td>\n",
       "      <td>disk</td>\n",
       "      <td>180</td>\n",
       "      <td>3  minutes</td>\n",
       "      <td>Strobe Lighted disk shape object observed clos...</td>\n",
       "      <td>3/19/2009</td>\n",
       "      <td>33.5861111</td>\n",
       "      <td>-86.286111</td>\n",
       "      <td>1966</td>\n",
       "      <td>1960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80327</th>\n",
       "      <td>2013-09-09 21:15:00</td>\n",
       "      <td>nashville</td>\n",
       "      <td>tn</td>\n",
       "      <td>us</td>\n",
       "      <td>light</td>\n",
       "      <td>600</td>\n",
       "      <td>10 minutes</td>\n",
       "      <td>Round from the distance/slowly changing colors...</td>\n",
       "      <td>9/30/2013</td>\n",
       "      <td>36.1658</td>\n",
       "      <td>-86.784444</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80328</th>\n",
       "      <td>2013-09-09 22:00:00</td>\n",
       "      <td>boise</td>\n",
       "      <td>id</td>\n",
       "      <td>us</td>\n",
       "      <td>circle</td>\n",
       "      <td>1200</td>\n",
       "      <td>20 minutes</td>\n",
       "      <td>Boise&amp;#44 ID&amp;#44 spherical&amp;#44 20 min&amp;#44 10 r...</td>\n",
       "      <td>9/30/2013</td>\n",
       "      <td>43.6136</td>\n",
       "      <td>-116.202500</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80329</th>\n",
       "      <td>2013-09-09 22:00:00</td>\n",
       "      <td>napa</td>\n",
       "      <td>ca</td>\n",
       "      <td>us</td>\n",
       "      <td>other</td>\n",
       "      <td>1200</td>\n",
       "      <td>hour</td>\n",
       "      <td>Napa UFO&amp;#44</td>\n",
       "      <td>9/30/2013</td>\n",
       "      <td>38.2972</td>\n",
       "      <td>-122.284444</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80330</th>\n",
       "      <td>2013-09-09 22:20:00</td>\n",
       "      <td>vienna</td>\n",
       "      <td>va</td>\n",
       "      <td>us</td>\n",
       "      <td>circle</td>\n",
       "      <td>5</td>\n",
       "      <td>5 seconds</td>\n",
       "      <td>Saw a five gold lit cicular craft moving fastl...</td>\n",
       "      <td>9/30/2013</td>\n",
       "      <td>38.9011</td>\n",
       "      <td>-77.265556</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80331</th>\n",
       "      <td>2013-09-09 23:00:00</td>\n",
       "      <td>edmond</td>\n",
       "      <td>ok</td>\n",
       "      <td>us</td>\n",
       "      <td>cigar</td>\n",
       "      <td>1020</td>\n",
       "      <td>17 minutes</td>\n",
       "      <td>2 witnesses 2  miles apart&amp;#44 Red &amp;amp; White...</td>\n",
       "      <td>9/30/2013</td>\n",
       "      <td>35.6528</td>\n",
       "      <td>-97.477778</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62994 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 datetime       city state country   shape duration (seconds)  \\\n",
       "3     1956-10-10 21:00:00       edna    tx      us  circle                 20   \n",
       "4     1960-10-10 20:00:00    kaneohe    hi      us   light                900   \n",
       "5     1961-10-10 19:00:00    bristol    tn      us  sphere                300   \n",
       "7     1965-10-10 23:45:00    norwalk    ct      us    disk               1200   \n",
       "8     1966-10-10 20:00:00  pell city    al      us    disk                180   \n",
       "...                   ...        ...   ...     ...     ...                ...   \n",
       "80327 2013-09-09 21:15:00  nashville    tn      us   light                600   \n",
       "80328 2013-09-09 22:00:00      boise    id      us  circle               1200   \n",
       "80329 2013-09-09 22:00:00       napa    ca      us   other               1200   \n",
       "80330 2013-09-09 22:20:00     vienna    va      us  circle                  5   \n",
       "80331 2013-09-09 23:00:00     edmond    ok      us   cigar               1020   \n",
       "\n",
       "      duration (hours/min)                                           comments  \\\n",
       "3                 1/2 hour  My older brother and twin sister were leaving ...   \n",
       "4               15 minutes  AS a Marine 1st Lt. flying an FJ4B fighter/att...   \n",
       "5                5 minutes  My father is now 89 my brother 52 the girl wit...   \n",
       "7               20 minutes  A bright orange color changing to reddish colo...   \n",
       "8               3  minutes  Strobe Lighted disk shape object observed clos...   \n",
       "...                    ...                                                ...   \n",
       "80327           10 minutes  Round from the distance/slowly changing colors...   \n",
       "80328           20 minutes  Boise&#44 ID&#44 spherical&#44 20 min&#44 10 r...   \n",
       "80329                 hour                                       Napa UFO&#44   \n",
       "80330            5 seconds  Saw a five gold lit cicular craft moving fastl...   \n",
       "80331           17 minutes  2 witnesses 2  miles apart&#44 Red &amp; White...   \n",
       "\n",
       "      date posted    latitude  longitude   year decade  \n",
       "3       1/17/2004  28.9783333  -96.645833  1956   1950  \n",
       "4       1/22/2004  21.4180556 -157.803611  1960   1960  \n",
       "5       4/27/2007  36.5950000  -82.188889  1961   1960  \n",
       "7       10/2/1999  41.1175000  -73.408333  1965   1960  \n",
       "8       3/19/2009  33.5861111  -86.286111  1966   1960  \n",
       "...           ...         ...         ...   ...    ...  \n",
       "80327   9/30/2013     36.1658  -86.784444  2013   2010  \n",
       "80328   9/30/2013     43.6136 -116.202500  2013   2010  \n",
       "80329   9/30/2013     38.2972 -122.284444  2013   2010  \n",
       "80330   9/30/2013     38.9011  -77.265556  2013   2010  \n",
       "80331   9/30/2013     35.6528  -97.477778  2013   2010  \n",
       "\n",
       "[62994 rows x 13 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo_by_year = ufo_by_year.dropna()\n",
    "ufo_by_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d828ae79",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['datetime', 'city', 'state', 'country', 'shape', 'duration (seconds)',\n",
    "       'duration (hours/min)', 'latitude', 'longitude ', 'year', 'decade']\n",
    "\n",
    "spotted_in_us_df = ufo_by_year.loc[(ufo_by_year[\"state\"] == \"az\") | (ufo_by_year[\"state\"] == \"ca\")| (ufo_by_year[\"state\"] == \"fl\") \n",
    "                                   | (ufo_by_year[\"state\"] == \"il\") | (ufo_by_year[\"state\"] == \"mi\") \n",
    "                                   | (ufo_by_year[\"state\"] == \"ny\") | (ufo_by_year[\"state\"] == \"oh\") | (ufo_by_year[\"state\"] == \"or\")\n",
    "                                   | (ufo_by_year[\"state\"] == \"pa\") | (ufo_by_year[\"state\"] == \"tx\")\n",
    "                                   | (ufo_by_year[\"state\"] == \"wa\"), columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3bb51f62",
   "metadata": {
    "scrolled": true
   },
   "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>datetime</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>shape</th>\n",
       "      <th>duration (seconds)</th>\n",
       "      <th>duration (hours/min)</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>year</th>\n",
       "      <th>decade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1956-10-10 21:00:00</td>\n",
       "      <td>edna</td>\n",
       "      <td>Texas</td>\n",
       "      <td>us</td>\n",
       "      <td>circle</td>\n",
       "      <td>20</td>\n",
       "      <td>1/2 hour</td>\n",
       "      <td>28.9783333</td>\n",
       "      <td>-96.645833</td>\n",
       "      <td>1956</td>\n",
       "      <td>1950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1966-10-10 21:00:00</td>\n",
       "      <td>live oak</td>\n",
       "      <td>Florida</td>\n",
       "      <td>us</td>\n",
       "      <td>disk</td>\n",
       "      <td>120</td>\n",
       "      <td>several minutes</td>\n",
       "      <td>30.2947222</td>\n",
       "      <td>-82.984167</td>\n",
       "      <td>1966</td>\n",
       "      <td>1960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1968-10-10 13:00:00</td>\n",
       "      <td>hawthorne</td>\n",
       "      <td>California</td>\n",
       "      <td>us</td>\n",
       "      <td>circle</td>\n",
       "      <td>300</td>\n",
       "      <td>5 min.</td>\n",
       "      <td>33.9163889</td>\n",
       "      <td>-118.351667</td>\n",
       "      <td>1968</td>\n",
       "      <td>1960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1970-10-10 16:00:00</td>\n",
       "      <td>bellmore</td>\n",
       "      <td>New York</td>\n",
       "      <td>us</td>\n",
       "      <td>disk</td>\n",
       "      <td>1800</td>\n",
       "      <td>30 min.</td>\n",
       "      <td>40.6686111</td>\n",
       "      <td>-73.527500</td>\n",
       "      <td>1970</td>\n",
       "      <td>1970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1972-10-10 22:30:00</td>\n",
       "      <td>west bloomfield</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>us</td>\n",
       "      <td>disk</td>\n",
       "      <td>120</td>\n",
       "      <td>2 minutes</td>\n",
       "      <td>42.5377778</td>\n",
       "      <td>-83.233056</td>\n",
       "      <td>1972</td>\n",
       "      <td>1970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80315</th>\n",
       "      <td>2013-09-09 03:00:00</td>\n",
       "      <td>struthers</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>us</td>\n",
       "      <td>unknown</td>\n",
       "      <td>120</td>\n",
       "      <td>2 minutes</td>\n",
       "      <td>41.0525</td>\n",
       "      <td>-80.608056</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80316</th>\n",
       "      <td>2013-09-09 09:51:00</td>\n",
       "      <td>san diego</td>\n",
       "      <td>California</td>\n",
       "      <td>us</td>\n",
       "      <td>light</td>\n",
       "      <td>4</td>\n",
       "      <td>~4 seconds</td>\n",
       "      <td>32.7153</td>\n",
       "      <td>-117.156389</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80317</th>\n",
       "      <td>2013-09-09 12:34:00</td>\n",
       "      <td>cedar park</td>\n",
       "      <td>Texas</td>\n",
       "      <td>us</td>\n",
       "      <td>cigar</td>\n",
       "      <td>8</td>\n",
       "      <td>5-8 seconds</td>\n",
       "      <td>30.505</td>\n",
       "      <td>-97.820000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80323</th>\n",
       "      <td>2013-09-09 21:00:00</td>\n",
       "      <td>gainesville</td>\n",
       "      <td>Florida</td>\n",
       "      <td>us</td>\n",
       "      <td>triangle</td>\n",
       "      <td>60</td>\n",
       "      <td>1 minute</td>\n",
       "      <td>29.6514</td>\n",
       "      <td>-82.325000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80329</th>\n",
       "      <td>2013-09-09 22:00:00</td>\n",
       "      <td>napa</td>\n",
       "      <td>California</td>\n",
       "      <td>us</td>\n",
       "      <td>other</td>\n",
       "      <td>1200</td>\n",
       "      <td>hour</td>\n",
       "      <td>38.2972</td>\n",
       "      <td>-122.284444</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34988 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 datetime             city       state country     shape  \\\n",
       "3     1956-10-10 21:00:00             edna       Texas      us    circle   \n",
       "9     1966-10-10 21:00:00         live oak     Florida      us      disk   \n",
       "10    1968-10-10 13:00:00        hawthorne  California      us    circle   \n",
       "12    1970-10-10 16:00:00         bellmore    New York      us      disk   \n",
       "16    1972-10-10 22:30:00  west bloomfield    Michigan      us      disk   \n",
       "...                   ...              ...         ...     ...       ...   \n",
       "80315 2013-09-09 03:00:00        struthers        Ohio      us   unknown   \n",
       "80316 2013-09-09 09:51:00        san diego  California      us     light   \n",
       "80317 2013-09-09 12:34:00       cedar park       Texas      us     cigar   \n",
       "80323 2013-09-09 21:00:00      gainesville     Florida      us  triangle   \n",
       "80329 2013-09-09 22:00:00             napa  California      us     other   \n",
       "\n",
       "      duration (seconds) duration (hours/min)    latitude  longitude   year  \\\n",
       "3                     20             1/2 hour  28.9783333  -96.645833  1956   \n",
       "9                    120      several minutes  30.2947222  -82.984167  1966   \n",
       "10                   300               5 min.  33.9163889 -118.351667  1968   \n",
       "12                  1800              30 min.  40.6686111  -73.527500  1970   \n",
       "16                   120            2 minutes  42.5377778  -83.233056  1972   \n",
       "...                  ...                  ...         ...         ...   ...   \n",
       "80315                120            2 minutes     41.0525  -80.608056  2013   \n",
       "80316                  4           ~4 seconds     32.7153 -117.156389  2013   \n",
       "80317                  8          5-8 seconds      30.505  -97.820000  2013   \n",
       "80323                 60             1 minute     29.6514  -82.325000  2013   \n",
       "80329               1200                 hour     38.2972 -122.284444  2013   \n",
       "\n",
       "      decade  \n",
       "3       1950  \n",
       "9       1960  \n",
       "10      1960  \n",
       "12      1970  \n",
       "16      1970  \n",
       "...      ...  \n",
       "80315   2010  \n",
       "80316   2010  \n",
       "80317   2010  \n",
       "80323   2010  \n",
       "80329   2010  \n",
       "\n",
       "[34988 rows x 11 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states = {'az': 'Arizona', 'ca': 'California', 'fl': \"Florida\", 'il': \"Illinois\", 'mi': \"Michigan\", 'ny': \"New York\", \n",
    "          'oh': \"Ohio\", 'or': \"Oregon\", 'pa': \"Pennsylvania\", 'tx': \"Texas\", 'wa': \"Washington\"}\n",
    "states = {state: abbrev for state, abbrev in states.items()}\n",
    "spotted_in_us_df['state'] = spotted_in_us_df['state'].map(states)\n",
    "\n",
    "spotted_in_us_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d899883",
   "metadata": {},
   "source": [
    "# Merging UFO and State Census Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "283e1567",
   "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>datetime</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>shape</th>\n",
       "      <th>duration (seconds)</th>\n",
       "      <th>duration (hours/min)</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>year</th>\n",
       "      <th>decade</th>\n",
       "      <th>Resident Population</th>\n",
       "      <th>Percent Change in Resident Population</th>\n",
       "      <th>Resident Population Density</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1956-10-10 21:00:00</td>\n",
       "      <td>edna</td>\n",
       "      <td>Texas</td>\n",
       "      <td>us</td>\n",
       "      <td>circle</td>\n",
       "      <td>20</td>\n",
       "      <td>1/2 hour</td>\n",
       "      <td>28.9783333</td>\n",
       "      <td>-96.645833</td>\n",
       "      <td>1956</td>\n",
       "      <td>1950</td>\n",
       "      <td>7,711,194</td>\n",
       "      <td>20.2</td>\n",
       "      <td>29.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1966-10-10 21:00:00</td>\n",
       "      <td>live oak</td>\n",
       "      <td>Florida</td>\n",
       "      <td>us</td>\n",
       "      <td>disk</td>\n",
       "      <td>120</td>\n",
       "      <td>several minutes</td>\n",
       "      <td>30.2947222</td>\n",
       "      <td>-82.984167</td>\n",
       "      <td>1966</td>\n",
       "      <td>1960</td>\n",
       "      <td>4,951,560</td>\n",
       "      <td>78.7</td>\n",
       "      <td>92.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1968-10-10 13:00:00</td>\n",
       "      <td>hawthorne</td>\n",
       "      <td>California</td>\n",
       "      <td>us</td>\n",
       "      <td>circle</td>\n",
       "      <td>300</td>\n",
       "      <td>5 min.</td>\n",
       "      <td>33.9163889</td>\n",
       "      <td>-118.351667</td>\n",
       "      <td>1968</td>\n",
       "      <td>1960</td>\n",
       "      <td>15,717,204</td>\n",
       "      <td>48.5</td>\n",
       "      <td>100.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1970-10-10 16:00:00</td>\n",
       "      <td>bellmore</td>\n",
       "      <td>New York</td>\n",
       "      <td>us</td>\n",
       "      <td>disk</td>\n",
       "      <td>1800</td>\n",
       "      <td>30 min.</td>\n",
       "      <td>40.6686111</td>\n",
       "      <td>-73.527500</td>\n",
       "      <td>1970</td>\n",
       "      <td>1970</td>\n",
       "      <td>18,236,967</td>\n",
       "      <td>8.7</td>\n",
       "      <td>387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1972-10-10 22:30:00</td>\n",
       "      <td>west bloomfield</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>us</td>\n",
       "      <td>disk</td>\n",
       "      <td>120</td>\n",
       "      <td>2 minutes</td>\n",
       "      <td>42.5377778</td>\n",
       "      <td>-83.233056</td>\n",
       "      <td>1972</td>\n",
       "      <td>1970</td>\n",
       "      <td>8,875,083</td>\n",
       "      <td>13.4</td>\n",
       "      <td>157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34983</th>\n",
       "      <td>2013-09-09 03:00:00</td>\n",
       "      <td>struthers</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>us</td>\n",
       "      <td>unknown</td>\n",
       "      <td>120</td>\n",
       "      <td>2 minutes</td>\n",
       "      <td>41.0525</td>\n",
       "      <td>-80.608056</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "      <td>11,536,504</td>\n",
       "      <td>1.6</td>\n",
       "      <td>282.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34984</th>\n",
       "      <td>2013-09-09 09:51:00</td>\n",
       "      <td>san diego</td>\n",
       "      <td>California</td>\n",
       "      <td>us</td>\n",
       "      <td>light</td>\n",
       "      <td>4</td>\n",
       "      <td>~4 seconds</td>\n",
       "      <td>32.7153</td>\n",
       "      <td>-117.156389</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "      <td>37,253,956</td>\n",
       "      <td>10.0</td>\n",
       "      <td>239.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34985</th>\n",
       "      <td>2013-09-09 12:34:00</td>\n",
       "      <td>cedar park</td>\n",
       "      <td>Texas</td>\n",
       "      <td>us</td>\n",
       "      <td>cigar</td>\n",
       "      <td>8</td>\n",
       "      <td>5-8 seconds</td>\n",
       "      <td>30.505</td>\n",
       "      <td>-97.820000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "      <td>25,145,561</td>\n",
       "      <td>20.6</td>\n",
       "      <td>96.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34986</th>\n",
       "      <td>2013-09-09 21:00:00</td>\n",
       "      <td>gainesville</td>\n",
       "      <td>Florida</td>\n",
       "      <td>us</td>\n",
       "      <td>triangle</td>\n",
       "      <td>60</td>\n",
       "      <td>1 minute</td>\n",
       "      <td>29.6514</td>\n",
       "      <td>-82.325000</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "      <td>18,801,310</td>\n",
       "      <td>17.6</td>\n",
       "      <td>350.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34987</th>\n",
       "      <td>2013-09-09 22:00:00</td>\n",
       "      <td>napa</td>\n",
       "      <td>California</td>\n",
       "      <td>us</td>\n",
       "      <td>other</td>\n",
       "      <td>1200</td>\n",
       "      <td>hour</td>\n",
       "      <td>38.2972</td>\n",
       "      <td>-122.284444</td>\n",
       "      <td>2013</td>\n",
       "      <td>2010</td>\n",
       "      <td>37,253,956</td>\n",
       "      <td>10.0</td>\n",
       "      <td>239.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34988 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 datetime             city       state country     shape  \\\n",
       "0     1956-10-10 21:00:00             edna       Texas      us    circle   \n",
       "1     1966-10-10 21:00:00         live oak     Florida      us      disk   \n",
       "2     1968-10-10 13:00:00        hawthorne  California      us    circle   \n",
       "3     1970-10-10 16:00:00         bellmore    New York      us      disk   \n",
       "4     1972-10-10 22:30:00  west bloomfield    Michigan      us      disk   \n",
       "...                   ...              ...         ...     ...       ...   \n",
       "34983 2013-09-09 03:00:00        struthers        Ohio      us   unknown   \n",
       "34984 2013-09-09 09:51:00        san diego  California      us     light   \n",
       "34985 2013-09-09 12:34:00       cedar park       Texas      us     cigar   \n",
       "34986 2013-09-09 21:00:00      gainesville     Florida      us  triangle   \n",
       "34987 2013-09-09 22:00:00             napa  California      us     other   \n",
       "\n",
       "      duration (seconds) duration (hours/min)    latitude  longitude   year  \\\n",
       "0                     20             1/2 hour  28.9783333  -96.645833  1956   \n",
       "1                    120      several minutes  30.2947222  -82.984167  1966   \n",
       "2                    300               5 min.  33.9163889 -118.351667  1968   \n",
       "3                   1800              30 min.  40.6686111  -73.527500  1970   \n",
       "4                    120            2 minutes  42.5377778  -83.233056  1972   \n",
       "...                  ...                  ...         ...         ...   ...   \n",
       "34983                120            2 minutes     41.0525  -80.608056  2013   \n",
       "34984                  4           ~4 seconds     32.7153 -117.156389  2013   \n",
       "34985                  8          5-8 seconds      30.505  -97.820000  2013   \n",
       "34986                 60             1 minute     29.6514  -82.325000  2013   \n",
       "34987               1200                 hour     38.2972 -122.284444  2013   \n",
       "\n",
       "       decade Resident Population  Percent Change in Resident Population  \\\n",
       "0        1950           7,711,194                                   20.2   \n",
       "1        1960           4,951,560                                   78.7   \n",
       "2        1960          15,717,204                                   48.5   \n",
       "3        1970          18,236,967                                    8.7   \n",
       "4        1970           8,875,083                                   13.4   \n",
       "...       ...                 ...                                    ...   \n",
       "34983    2010          11,536,504                                    1.6   \n",
       "34984    2010          37,253,956                                   10.0   \n",
       "34985    2010          25,145,561                                   20.6   \n",
       "34986    2010          18,801,310                                   17.6   \n",
       "34987    2010          37,253,956                                   10.0   \n",
       "\n",
       "      Resident Population Density  \n",
       "0                            29.5  \n",
       "1                            92.3  \n",
       "2                           100.9  \n",
       "3                             387  \n",
       "4                             157  \n",
       "...                           ...  \n",
       "34983                       282.3  \n",
       "34984                       239.1  \n",
       "34985                        96.3  \n",
       "34986                       350.6  \n",
       "34987                       239.1  \n",
       "\n",
       "[34988 rows x 14 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo_statepop_df = pd.merge(spotted_in_us_df, state_pop_df, \n",
    "                                 how='left', on=['decade', 'state'], sort=False)\n",
    "ufo_statepop_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "5aeaffd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "ufo_statepop_df.to_csv(\"data/ufopop.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf5f0487",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PythonData",
   "language": "python",
   "name": "pythondata"
  },
  "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}