ELT_Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dependencies\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "starbucks_path = \"Resources/us_starbucks_location.csv\"\n",
    "weather_path = \"Resources/weather_data_from_starbucks.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>store_number</th>\n",
       "      <th>store_name</th>\n",
       "      <th>ownership_type</th>\n",
       "      <th>street_address</th>\n",
       "      <th>city</th>\n",
       "      <th>state_province</th>\n",
       "      <th>postcode</th>\n",
       "      <th>phone_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3513-125945</td>\n",
       "      <td>Safeway-Anchorage #1809</td>\n",
       "      <td>Licensed</td>\n",
       "      <td>5600 Debarr Rd Ste 9</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995042300</td>\n",
       "      <td>907-339-0900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>74352-84449</td>\n",
       "      <td>Safeway-Anchorage #2628</td>\n",
       "      <td>Licensed</td>\n",
       "      <td>1725 Abbott Rd</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995073444</td>\n",
       "      <td>907-339-2800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12449-152385</td>\n",
       "      <td>Safeway - Anchorage #1813</td>\n",
       "      <td>Licensed</td>\n",
       "      <td>1501 Huffman Rd</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995153596</td>\n",
       "      <td>907-339-1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24936-233524</td>\n",
       "      <td>100th &amp; C St - Anchorage</td>\n",
       "      <td>Company Owned</td>\n",
       "      <td>320 W. 100th Ave, 100, Southgate Shopping Ctr ...</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>99515</td>\n",
       "      <td>(907) 227-9631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8973-85630</td>\n",
       "      <td>Old Seward &amp; Diamond</td>\n",
       "      <td>Company Owned</td>\n",
       "      <td>1005 E Dimond Blvd</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995152050</td>\n",
       "      <td>907-344-4160</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_number                 store_name ownership_type  \\\n",
       "0   3513-125945    Safeway-Anchorage #1809       Licensed   \n",
       "1   74352-84449    Safeway-Anchorage #2628       Licensed   \n",
       "2  12449-152385  Safeway - Anchorage #1813       Licensed   \n",
       "3  24936-233524   100th & C St - Anchorage  Company Owned   \n",
       "4    8973-85630       Old Seward & Diamond  Company Owned   \n",
       "\n",
       "                                      street_address       city  \\\n",
       "0                               5600 Debarr Rd Ste 9  Anchorage   \n",
       "1                                     1725 Abbott Rd  Anchorage   \n",
       "2                                    1501 Huffman Rd  Anchorage   \n",
       "3  320 W. 100th Ave, 100, Southgate Shopping Ctr ...  Anchorage   \n",
       "4                                 1005 E Dimond Blvd  Anchorage   \n",
       "\n",
       "  state_province   postcode    phone_number  \n",
       "0             AK  995042300    907-339-0900  \n",
       "1             AK  995073444    907-339-2800  \n",
       "2             AK  995153596    907-339-1300  \n",
       "3             AK      99515  (907) 227-9631  \n",
       "4             AK  995152050    907-344-4160  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read the starbucks csv and fixed the column name\n",
    "starbucks_df = pd.read_csv(starbucks_path)\n",
    "del starbucks_df[\"Unnamed: 0\"]\n",
    "starbucks_df = starbucks_df.rename(columns={\"Store Number\":\"store_number\",\"Store Name\":\"store_name\",\"Ownership Type\":\"ownership_type\",\n",
    "                                            \"Street Address\":\"street_address\",\"City\":\"city\", \"State/Province\": \"state_province\", \"Postcode\": \"postcode\", \"Phone Number\": \"phone_number\"})\n",
    "\n",
    "starbucks_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>City</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Max Temp</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Cloudiness</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Country</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>61.22</td>\n",
       "      <td>-149.90</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4.18</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>45.92</td>\n",
       "      <td>-89.24</td>\n",
       "      <td>5.0</td>\n",
       "      <td>56</td>\n",
       "      <td>1</td>\n",
       "      <td>2.10</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fairbanks</td>\n",
       "      <td>64.84</td>\n",
       "      <td>-147.72</td>\n",
       "      <td>-25.0</td>\n",
       "      <td>75</td>\n",
       "      <td>20</td>\n",
       "      <td>0.59</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Homer</td>\n",
       "      <td>59.64</td>\n",
       "      <td>-151.55</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>23</td>\n",
       "      <td>75</td>\n",
       "      <td>2.10</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Juneau</td>\n",
       "      <td>58.30</td>\n",
       "      <td>-134.42</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>92</td>\n",
       "      <td>90</td>\n",
       "      <td>0.92</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370647</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          City    Lat     Lng  Max Temp  Humidity  Cloudiness  Wind Speed  \\\n",
       "0    Anchorage  61.22 -149.90      -6.0        26           1        4.18   \n",
       "1  Eagle River  45.92  -89.24       5.0        56           1        2.10   \n",
       "2    Fairbanks  64.84 -147.72     -25.0        75          20        0.59   \n",
       "3        Homer  59.64 -151.55      -3.0        23          75        2.10   \n",
       "4       Juneau  58.30 -134.42      -2.0        92          90        0.92   \n",
       "\n",
       "  Country        Date  \n",
       "0      US  1604370751  \n",
       "1      US  1604370712  \n",
       "2      US  1604370756  \n",
       "3      US  1604370929  \n",
       "4      US  1604370647  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read the weather csv\n",
    "weather_df = pd.read_csv(weather_path)\n",
    "del weather_df[\"Unnamed: 0\"]\n",
    "\n",
    "weather_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>City</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lng</th>\n",
       "      <th>Max Temp</th>\n",
       "      <th>Humidity</th>\n",
       "      <th>Cloudiness</th>\n",
       "      <th>Wind Speed</th>\n",
       "      <th>Country</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>61.22</td>\n",
       "      <td>-149.90</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4.18</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>45.92</td>\n",
       "      <td>-89.24</td>\n",
       "      <td>5.0</td>\n",
       "      <td>56</td>\n",
       "      <td>1</td>\n",
       "      <td>2.10</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fairbanks</td>\n",
       "      <td>64.84</td>\n",
       "      <td>-147.72</td>\n",
       "      <td>-25.0</td>\n",
       "      <td>75</td>\n",
       "      <td>20</td>\n",
       "      <td>0.59</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Homer</td>\n",
       "      <td>59.64</td>\n",
       "      <td>-151.55</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>23</td>\n",
       "      <td>75</td>\n",
       "      <td>2.10</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Juneau</td>\n",
       "      <td>58.30</td>\n",
       "      <td>-134.42</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>92</td>\n",
       "      <td>90</td>\n",
       "      <td>0.92</td>\n",
       "      <td>US</td>\n",
       "      <td>1604370647</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          City    Lat     Lng  Max Temp  Humidity  Cloudiness  Wind Speed  \\\n",
       "0    Anchorage  61.22 -149.90      -6.0        26           1        4.18   \n",
       "1  Eagle River  45.92  -89.24       5.0        56           1        2.10   \n",
       "2    Fairbanks  64.84 -147.72     -25.0        75          20        0.59   \n",
       "3        Homer  59.64 -151.55      -3.0        23          75        2.10   \n",
       "4       Juneau  58.30 -134.42      -2.0        92          90        0.92   \n",
       "\n",
       "  Country        Date  \n",
       "0      US  1604370751  \n",
       "1      US  1604370712  \n",
       "2      US  1604370756  \n",
       "3      US  1604370929  \n",
       "4      US  1604370647  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_weather_df = weather_df[weather_df[\"Country\"].isin([\"US\"])]\n",
    "us_weather_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>city</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>cloudiness</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4.18</td>\n",
       "      <td>1604370751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>5.0</td>\n",
       "      <td>56</td>\n",
       "      <td>1</td>\n",
       "      <td>2.10</td>\n",
       "      <td>1604370712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fairbanks</td>\n",
       "      <td>-25.0</td>\n",
       "      <td>75</td>\n",
       "      <td>20</td>\n",
       "      <td>0.59</td>\n",
       "      <td>1604370756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Homer</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>23</td>\n",
       "      <td>75</td>\n",
       "      <td>2.10</td>\n",
       "      <td>1604370929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Juneau</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>92</td>\n",
       "      <td>90</td>\n",
       "      <td>0.92</td>\n",
       "      <td>1604370647</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          city  max_temp  humidity  cloudiness  wind_speed        date\n",
       "0    Anchorage      -6.0        26           1        4.18  1604370751\n",
       "1  Eagle River       5.0        56           1        2.10  1604370712\n",
       "2    Fairbanks     -25.0        75          20        0.59  1604370756\n",
       "3        Homer      -3.0        23          75        2.10  1604370929\n",
       "4       Juneau      -2.0        92          90        0.92  1604370647"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#renamed the column names and put into a new DF\n",
    "new_weather_df = us_weather_df[[\"City\", \"Max Temp\", \"Humidity\", \"Cloudiness\", \"Wind Speed\", \"Date\"]].copy()\n",
    "new_weather_df = new_weather_df.rename(columns ={\"City\": \"city\", \"Max Temp\": \"max_temp\", \"Humidity\": \"humidity\", \n",
    "                                                 \"Cloudiness\": \"cloudiness\", \"Wind Speed\": \"wind_speed\", \"Date\": \"date\"})\n",
    "new_weather_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>store_number</th>\n",
       "      <th>store_name</th>\n",
       "      <th>ownership_type</th>\n",
       "      <th>street_address</th>\n",
       "      <th>city</th>\n",
       "      <th>state_province</th>\n",
       "      <th>postcode</th>\n",
       "      <th>phone_number</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>cloudiness</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3513-125945</td>\n",
       "      <td>Safeway-Anchorage #1809</td>\n",
       "      <td>Licensed</td>\n",
       "      <td>5600 Debarr Rd Ste 9</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995042300</td>\n",
       "      <td>907-339-0900</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4.18</td>\n",
       "      <td>1604370751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>74352-84449</td>\n",
       "      <td>Safeway-Anchorage #2628</td>\n",
       "      <td>Licensed</td>\n",
       "      <td>1725 Abbott Rd</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995073444</td>\n",
       "      <td>907-339-2800</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4.18</td>\n",
       "      <td>1604370751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12449-152385</td>\n",
       "      <td>Safeway - Anchorage #1813</td>\n",
       "      <td>Licensed</td>\n",
       "      <td>1501 Huffman Rd</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995153596</td>\n",
       "      <td>907-339-1300</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4.18</td>\n",
       "      <td>1604370751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24936-233524</td>\n",
       "      <td>100th &amp; C St - Anchorage</td>\n",
       "      <td>Company Owned</td>\n",
       "      <td>320 W. 100th Ave, 100, Southgate Shopping Ctr ...</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>99515</td>\n",
       "      <td>(907) 227-9631</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4.18</td>\n",
       "      <td>1604370751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8973-85630</td>\n",
       "      <td>Old Seward &amp; Diamond</td>\n",
       "      <td>Company Owned</td>\n",
       "      <td>1005 E Dimond Blvd</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995152050</td>\n",
       "      <td>907-344-4160</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4.18</td>\n",
       "      <td>1604370751</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_number                 store_name ownership_type  \\\n",
       "0   3513-125945    Safeway-Anchorage #1809       Licensed   \n",
       "1   74352-84449    Safeway-Anchorage #2628       Licensed   \n",
       "2  12449-152385  Safeway - Anchorage #1813       Licensed   \n",
       "3  24936-233524   100th & C St - Anchorage  Company Owned   \n",
       "4    8973-85630       Old Seward & Diamond  Company Owned   \n",
       "\n",
       "                                      street_address       city  \\\n",
       "0                               5600 Debarr Rd Ste 9  Anchorage   \n",
       "1                                     1725 Abbott Rd  Anchorage   \n",
       "2                                    1501 Huffman Rd  Anchorage   \n",
       "3  320 W. 100th Ave, 100, Southgate Shopping Ctr ...  Anchorage   \n",
       "4                                 1005 E Dimond Blvd  Anchorage   \n",
       "\n",
       "  state_province   postcode    phone_number  max_temp  humidity  cloudiness  \\\n",
       "0             AK  995042300    907-339-0900      -6.0        26           1   \n",
       "1             AK  995073444    907-339-2800      -6.0        26           1   \n",
       "2             AK  995153596    907-339-1300      -6.0        26           1   \n",
       "3             AK      99515  (907) 227-9631      -6.0        26           1   \n",
       "4             AK  995152050    907-344-4160      -6.0        26           1   \n",
       "\n",
       "   wind_speed        date  \n",
       "0        4.18  1604370751  \n",
       "1        4.18  1604370751  \n",
       "2        4.18  1604370751  \n",
       "3        4.18  1604370751  \n",
       "4        4.18  1604370751  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merged 2 DFs\n",
    "merged_df = pd.merge(starbucks_df, new_weather_df,on=\"city\",how='inner')\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#link to Postgres and need to enter your own password\n",
    "rds_connection_string = \"postgres:<Enter your password>@localhost:5432/starbucks_weather_db\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#start the engine\n",
    "engine = create_engine(f'postgresql://{rds_connection_string}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['weather_data', 'starbucks_location']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#find the table name\n",
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#put weather df into the weather_data table\n",
    "new_weather_df.to_sql(name = 'weather_data',  con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#put starbucks df into starbucks_location table\n",
    "starbucks_df.to_sql(name = 'starbucks_location',  con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>city</th>\n",
       "      <th>max_temp</th>\n",
       "      <th>humidity</th>\n",
       "      <th>cloudiness</th>\n",
       "      <th>wind_speed</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anchorage</td>\n",
       "      <td>-6</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1604370751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>5</td>\n",
       "      <td>56</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1604370712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Fairbanks</td>\n",
       "      <td>-25</td>\n",
       "      <td>75</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1604370756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Homer</td>\n",
       "      <td>-3</td>\n",
       "      <td>23</td>\n",
       "      <td>75</td>\n",
       "      <td>2</td>\n",
       "      <td>1604370929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Juneau</td>\n",
       "      <td>-2</td>\n",
       "      <td>92</td>\n",
       "      <td>90</td>\n",
       "      <td>1</td>\n",
       "      <td>1604370647</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          city  max_temp  humidity  cloudiness  wind_speed        date\n",
       "0    Anchorage        -6        26           1           4  1604370751\n",
       "1  Eagle River         5        56           1           2  1604370712\n",
       "2    Fairbanks       -25        75          20           1  1604370756\n",
       "3        Homer        -3        23          75           2  1604370929\n",
       "4       Juneau        -2        92          90           1  1604370647"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#see if the data is in the weather_data table\n",
    "pd.read_sql_query('select * from weather_data', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>store_number</th>\n",
       "      <th>store_name</th>\n",
       "      <th>ownership_type</th>\n",
       "      <th>street_address</th>\n",
       "      <th>city</th>\n",
       "      <th>state_province</th>\n",
       "      <th>postcode</th>\n",
       "      <th>phone_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3513-125945</td>\n",
       "      <td>Safeway-Anchorage #1809</td>\n",
       "      <td>Licensed</td>\n",
       "      <td>5600 Debarr Rd Ste 9</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995042300</td>\n",
       "      <td>907-339-0900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>74352-84449</td>\n",
       "      <td>Safeway-Anchorage #2628</td>\n",
       "      <td>Licensed</td>\n",
       "      <td>1725 Abbott Rd</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995073444</td>\n",
       "      <td>907-339-2800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12449-152385</td>\n",
       "      <td>Safeway - Anchorage #1813</td>\n",
       "      <td>Licensed</td>\n",
       "      <td>1501 Huffman Rd</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995153596</td>\n",
       "      <td>907-339-1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24936-233524</td>\n",
       "      <td>100th &amp; C St - Anchorage</td>\n",
       "      <td>Company Owned</td>\n",
       "      <td>320 W. 100th Ave, 100, Southgate Shopping Ctr ...</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>99515</td>\n",
       "      <td>(907) 227-9631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8973-85630</td>\n",
       "      <td>Old Seward &amp; Diamond</td>\n",
       "      <td>Company Owned</td>\n",
       "      <td>1005 E Dimond Blvd</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>AK</td>\n",
       "      <td>995152050</td>\n",
       "      <td>907-344-4160</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_number                 store_name ownership_type  \\\n",
       "0   3513-125945    Safeway-Anchorage #1809       Licensed   \n",
       "1   74352-84449    Safeway-Anchorage #2628       Licensed   \n",
       "2  12449-152385  Safeway - Anchorage #1813       Licensed   \n",
       "3  24936-233524   100th & C St - Anchorage  Company Owned   \n",
       "4    8973-85630       Old Seward & Diamond  Company Owned   \n",
       "\n",
       "                                      street_address       city  \\\n",
       "0                               5600 Debarr Rd Ste 9  Anchorage   \n",
       "1                                     1725 Abbott Rd  Anchorage   \n",
       "2                                    1501 Huffman Rd  Anchorage   \n",
       "3  320 W. 100th Ave, 100, Southgate Shopping Ctr ...  Anchorage   \n",
       "4                                 1005 E Dimond Blvd  Anchorage   \n",
       "\n",
       "  state_province   postcode    phone_number  \n",
       "0             AK  995042300    907-339-0900  \n",
       "1             AK  995073444    907-339-2800  \n",
       "2             AK  995153596    907-339-1300  \n",
       "3             AK      99515  (907) 227-9631  \n",
       "4             AK  995152050    907-344-4160  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#see if the data is in the starbucks_location table\n",
    "pd.read_sql_query('select * from starbucks_location', con=engine).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}