2.4 - Cleaning - Merge Data into Final Models.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "53a356f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "941025b1",
   "metadata": {},
   "source": [
    "# Merge County level data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f5ff80aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "poverty_df = pd.read_csv('CLEANED county_poverty BACKUP.csv',index_col=False)\n",
    "education_df = pd.read_csv('CLEANED county_education BACKUP.csv',index_col=False)\n",
    "unemployment_df = pd.read_csv('CLEANED county_unemployment BACKUP.csv',index_col=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d7ac67f0",
   "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>FIPS</th>\n",
       "      <th>county_name</th>\n",
       "      <th>rural-urban-continuum-code</th>\n",
       "      <th>perc_people_poverty_2020</th>\n",
       "      <th>perc_children_poverty_2020</th>\n",
       "      <th>state</th>\n",
       "      <th>joincol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>53001</td>\n",
       "      <td>Adams</td>\n",
       "      <td>6</td>\n",
       "      <td>0.127</td>\n",
       "      <td>0.170</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Adams, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53003</td>\n",
       "      <td>Asotin</td>\n",
       "      <td>3</td>\n",
       "      <td>0.141</td>\n",
       "      <td>0.185</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Asotin, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>53005</td>\n",
       "      <td>Benton</td>\n",
       "      <td>2</td>\n",
       "      <td>0.091</td>\n",
       "      <td>0.115</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Benton, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>53007</td>\n",
       "      <td>Chelan</td>\n",
       "      <td>3</td>\n",
       "      <td>0.083</td>\n",
       "      <td>0.116</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Chelan, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53009</td>\n",
       "      <td>Clallam</td>\n",
       "      <td>5</td>\n",
       "      <td>0.133</td>\n",
       "      <td>0.189</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Clallam, Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS county_name  rural-urban-continuum-code  perc_people_poverty_2020  \\\n",
       "0  53001       Adams                           6                     0.127   \n",
       "1  53003      Asotin                           3                     0.141   \n",
       "2  53005      Benton                           2                     0.091   \n",
       "3  53007      Chelan                           3                     0.083   \n",
       "4  53009     Clallam                           5                     0.133   \n",
       "\n",
       "   perc_children_poverty_2020       state              joincol  \n",
       "0                       0.170  Washington    Adams, Washington  \n",
       "1                       0.185  Washington   Asotin, Washington  \n",
       "2                       0.115  Washington   Benton, Washington  \n",
       "3                       0.116  Washington   Chelan, Washington  \n",
       "4                       0.189  Washington  Clallam, Washington  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poverty_df.drop(columns=['Unnamed: 0'],inplace=True)\n",
    "poverty_df['joincol'] = poverty_df['county_name'] + ', ' + poverty_df['state']\n",
    "poverty_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a5c74446",
   "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>FIPS</th>\n",
       "      <th>county_name</th>\n",
       "      <th>rural-urban-continuum-code</th>\n",
       "      <th>perc-college-educated-2016-2020</th>\n",
       "      <th>state</th>\n",
       "      <th>joincol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>53001</td>\n",
       "      <td>Adams</td>\n",
       "      <td>6</td>\n",
       "      <td>0.138165</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Adams, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53003</td>\n",
       "      <td>Asotin</td>\n",
       "      <td>3</td>\n",
       "      <td>0.233691</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Asotin, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>53005</td>\n",
       "      <td>Benton</td>\n",
       "      <td>2</td>\n",
       "      <td>0.315854</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Benton, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>53007</td>\n",
       "      <td>Chelan</td>\n",
       "      <td>3</td>\n",
       "      <td>0.263702</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Chelan, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53009</td>\n",
       "      <td>Clallam</td>\n",
       "      <td>5</td>\n",
       "      <td>0.288448</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Clallam, Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS county_name  rural-urban-continuum-code  \\\n",
       "0  53001       Adams                           6   \n",
       "1  53003      Asotin                           3   \n",
       "2  53005      Benton                           2   \n",
       "3  53007      Chelan                           3   \n",
       "4  53009     Clallam                           5   \n",
       "\n",
       "   perc-college-educated-2016-2020       state              joincol  \n",
       "0                         0.138165  Washington    Adams, Washington  \n",
       "1                         0.233691  Washington   Asotin, Washington  \n",
       "2                         0.315854  Washington   Benton, Washington  \n",
       "3                         0.263702  Washington   Chelan, Washington  \n",
       "4                         0.288448  Washington  Clallam, Washington  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "education_df.drop(columns=['Unnamed: 0'],inplace=True)\n",
    "education_df['joincol'] = education_df['county_name'] + ', ' + education_df['state']\n",
    "education_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c212d63b",
   "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>FIPS</th>\n",
       "      <th>county_name</th>\n",
       "      <th>perc_unemployed_2021</th>\n",
       "      <th>median_hh_income</th>\n",
       "      <th>perc_state_median_hh_income</th>\n",
       "      <th>state</th>\n",
       "      <th>joincol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>53001</td>\n",
       "      <td>Adams</td>\n",
       "      <td>0.055</td>\n",
       "      <td>56421</td>\n",
       "      <td>0.702461</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Adams, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53003</td>\n",
       "      <td>Asotin</td>\n",
       "      <td>0.037</td>\n",
       "      <td>53377</td>\n",
       "      <td>0.664563</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Asotin, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>53005</td>\n",
       "      <td>Benton</td>\n",
       "      <td>0.056</td>\n",
       "      <td>75882</td>\n",
       "      <td>0.944758</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Benton, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>53007</td>\n",
       "      <td>Chelan</td>\n",
       "      <td>0.053</td>\n",
       "      <td>61546</td>\n",
       "      <td>0.766270</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Chelan, Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53009</td>\n",
       "      <td>Clallam</td>\n",
       "      <td>0.065</td>\n",
       "      <td>54712</td>\n",
       "      <td>0.681184</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Clallam, Washington</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    FIPS county_name  perc_unemployed_2021  median_hh_income  \\\n",
       "0  53001       Adams                 0.055             56421   \n",
       "1  53003      Asotin                 0.037             53377   \n",
       "2  53005      Benton                 0.056             75882   \n",
       "3  53007      Chelan                 0.053             61546   \n",
       "4  53009     Clallam                 0.065             54712   \n",
       "\n",
       "   perc_state_median_hh_income       state              joincol  \n",
       "0                     0.702461  Washington    Adams, Washington  \n",
       "1                     0.664563  Washington   Asotin, Washington  \n",
       "2                     0.944758  Washington   Benton, Washington  \n",
       "3                     0.766270  Washington   Chelan, Washington  \n",
       "4                     0.681184  Washington  Clallam, Washington  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment_df.drop(columns=['Unnamed: 0'],inplace=True)\n",
    "unemployment_df['joincol'] = unemployment_df['county_name'] + ', ' + unemployment_df['state']\n",
    "unemployment_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "60b05e90",
   "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>county_name</th>\n",
       "      <th>state</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>rural-urban-continuum-code</th>\n",
       "      <th>perc-college-educated-2016-2020</th>\n",
       "      <th>perc_unemployed_2021</th>\n",
       "      <th>median_hh_income</th>\n",
       "      <th>perc_state_median_hh_income</th>\n",
       "      <th>perc_people_poverty_2020</th>\n",
       "      <th>perc_children_poverty_2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adams</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53001</td>\n",
       "      <td>6</td>\n",
       "      <td>0.138165</td>\n",
       "      <td>0.055</td>\n",
       "      <td>56421</td>\n",
       "      <td>0.702461</td>\n",
       "      <td>0.127</td>\n",
       "      <td>0.170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Asotin</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53003</td>\n",
       "      <td>3</td>\n",
       "      <td>0.233691</td>\n",
       "      <td>0.037</td>\n",
       "      <td>53377</td>\n",
       "      <td>0.664563</td>\n",
       "      <td>0.141</td>\n",
       "      <td>0.185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Benton</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53005</td>\n",
       "      <td>2</td>\n",
       "      <td>0.315854</td>\n",
       "      <td>0.056</td>\n",
       "      <td>75882</td>\n",
       "      <td>0.944758</td>\n",
       "      <td>0.091</td>\n",
       "      <td>0.115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chelan</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53007</td>\n",
       "      <td>3</td>\n",
       "      <td>0.263702</td>\n",
       "      <td>0.053</td>\n",
       "      <td>61546</td>\n",
       "      <td>0.766270</td>\n",
       "      <td>0.083</td>\n",
       "      <td>0.116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Clallam</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53009</td>\n",
       "      <td>5</td>\n",
       "      <td>0.288448</td>\n",
       "      <td>0.065</td>\n",
       "      <td>54712</td>\n",
       "      <td>0.681184</td>\n",
       "      <td>0.133</td>\n",
       "      <td>0.189</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  county_name       state   FIPS  rural-urban-continuum-code  \\\n",
       "0       Adams  Washington  53001                           6   \n",
       "1      Asotin  Washington  53003                           3   \n",
       "2      Benton  Washington  53005                           2   \n",
       "3      Chelan  Washington  53007                           3   \n",
       "4     Clallam  Washington  53009                           5   \n",
       "\n",
       "   perc-college-educated-2016-2020  perc_unemployed_2021  median_hh_income  \\\n",
       "0                         0.138165                 0.055             56421   \n",
       "1                         0.233691                 0.037             53377   \n",
       "2                         0.315854                 0.056             75882   \n",
       "3                         0.263702                 0.053             61546   \n",
       "4                         0.288448                 0.065             54712   \n",
       "\n",
       "   perc_state_median_hh_income  perc_people_poverty_2020  \\\n",
       "0                     0.702461                     0.127   \n",
       "1                     0.664563                     0.141   \n",
       "2                     0.944758                     0.091   \n",
       "3                     0.766270                     0.083   \n",
       "4                     0.681184                     0.133   \n",
       "\n",
       "   perc_children_poverty_2020  \n",
       "0                       0.170  \n",
       "1                       0.185  \n",
       "2                       0.115  \n",
       "3                       0.116  \n",
       "4                       0.189  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_df = poverty_df.merge(\n",
    "    education_df[['joincol','perc-college-educated-2016-2020']],\n",
    "    on='joincol',\n",
    "    how='inner'\n",
    ")\n",
    "county_df = county_df.merge(\n",
    "    unemployment_df[['joincol','perc_unemployed_2021','median_hh_income','perc_state_median_hh_income']],\n",
    "    on='joincol',\n",
    "    how='inner'\n",
    ")\n",
    "\n",
    "county_df = county_df[['county_name','state','FIPS','rural-urban-continuum-code',\n",
    "                       'perc-college-educated-2016-2020','perc_unemployed_2021','median_hh_income',\n",
    "                       'perc_state_median_hh_income','perc_people_poverty_2020','perc_children_poverty_2020']]\n",
    "\n",
    "county_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5221b382",
   "metadata": {},
   "outputs": [],
   "source": [
    "county_df.to_csv('CLEANED county_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f5d0f052",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-8-014fb671d891>:2: DtypeWarning: Columns (4) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  scraped_pages = pd.read_csv('CLEANED scraped_pages BACKUP.csv')\n"
     ]
    }
   ],
   "source": [
    "scraped_listings = pd.read_csv('CLEANED scraped_listings BACKUP.csv')\n",
    "scraped_pages = pd.read_csv('CLEANED scraped_pages BACKUP.csv')\n",
    "geographic_data = pd.read_csv('CLEANED geolocation_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1f1e9d1c",
   "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>listing_id</th>\n",
       "      <th>listing_url</th>\n",
       "      <th>is_superhost</th>\n",
       "      <th>is_premium</th>\n",
       "      <th>rating</th>\n",
       "      <th>n_reviews</th>\n",
       "      <th>listing_city</th>\n",
       "      <th>listing_title</th>\n",
       "      <th>n_pictures</th>\n",
       "      <th>entire_home</th>\n",
       "      <th>hotel_room</th>\n",
       "      <th>private_room</th>\n",
       "      <th>shared_room</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>beds</th>\n",
       "      <th>price</th>\n",
       "      <th>perc_discount</th>\n",
       "      <th>image_1</th>\n",
       "      <th>image_2</th>\n",
       "      <th>image_3</th>\n",
       "      <th>image_4</th>\n",
       "      <th>image_5</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>47924385</td>\n",
       "      <td>https://www.airbnb.com/rooms/47924385</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.86</td>\n",
       "      <td>207.0</td>\n",
       "      <td>Flagler</td>\n",
       "      <td>Home in Flagler</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.292940</td>\n",
       "      <td>-103.064290</td>\n",
       "      <td>1 king bed</td>\n",
       "      <td>77</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/40a53028-5...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/66d3a0d5-b...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/a7339783-c...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/f8ea448c-d...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/1a2a5d6b-f...</td>\n",
       "      <td>Flagler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12964075</td>\n",
       "      <td>https://www.airbnb.com/rooms/12964075</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.87</td>\n",
       "      <td>419.0</td>\n",
       "      <td>Flagler</td>\n",
       "      <td>Private room in Flagler</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>39.294920</td>\n",
       "      <td>-103.063720</td>\n",
       "      <td>1 queen bed</td>\n",
       "      <td>65</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/b6b6703f-b...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/49b235b7-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/478c7fd6-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/1b6947f8-2...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/ce671a50-b...</td>\n",
       "      <td>Flagler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50379619</td>\n",
       "      <td>https://www.airbnb.com/rooms/50379619</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.93</td>\n",
       "      <td>42.0</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>Home in Bridgeport</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41.665535</td>\n",
       "      <td>-103.095772</td>\n",
       "      <td>3 beds</td>\n",
       "      <td>75</td>\n",
       "      <td>0.882353</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/9ee93475-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/b18ca7d5-0...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/ecc78c28-b...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/05172838-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/f30c7acb-2...</td>\n",
       "      <td>Bridgeport</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>710231964358460529</td>\n",
       "      <td>https://www.airbnb.com/rooms/710231964358460529</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>Private room in Bridgeport</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>41.668770</td>\n",
       "      <td>-103.101660</td>\n",
       "      <td>2 beds</td>\n",
       "      <td>40</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/7a8d2d04-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/42c9020c-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/52bd72d4-d...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/35c9f58a-d...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/d39ad521-3...</td>\n",
       "      <td>Bridgeport</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>723852070242986749</td>\n",
       "      <td>https://www.airbnb.com/rooms/723852070242986749</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>Private room in Bridgeport</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>41.670050</td>\n",
       "      <td>-103.101620</td>\n",
       "      <td>2 beds</td>\n",
       "      <td>43</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/58f6e95a-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>Bridgeport</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           listing_id                                      listing_url  \\\n",
       "0            47924385            https://www.airbnb.com/rooms/47924385   \n",
       "1            12964075            https://www.airbnb.com/rooms/12964075   \n",
       "2            50379619            https://www.airbnb.com/rooms/50379619   \n",
       "3  710231964358460529  https://www.airbnb.com/rooms/710231964358460529   \n",
       "4  723852070242986749  https://www.airbnb.com/rooms/723852070242986749   \n",
       "\n",
       "   is_superhost  is_premium rating  n_reviews listing_city  \\\n",
       "0             1           0   4.86      207.0      Flagler   \n",
       "1             1           0   4.87      419.0      Flagler   \n",
       "2             1           0   4.93       42.0   Bridgeport   \n",
       "3             0           0    NaN        NaN   Bridgeport   \n",
       "4             0           0    NaN        NaN   Bridgeport   \n",
       "\n",
       "                listing_title  n_pictures  entire_home  hotel_room  \\\n",
       "0             Home in Flagler         9.0            1           0   \n",
       "1     Private room in Flagler        11.0            0           0   \n",
       "2          Home in Bridgeport        15.0            1           0   \n",
       "3  Private room in Bridgeport         5.0            0           0   \n",
       "4  Private room in Bridgeport         7.0            0           0   \n",
       "\n",
       "   private_room  shared_room   latitude   longitude         beds  price  \\\n",
       "0             0            0  39.292940 -103.064290   1 king bed     77   \n",
       "1             1            0  39.294920 -103.063720  1 queen bed     65   \n",
       "2             0            0  41.665535 -103.095772       3 beds     75   \n",
       "3             1            0  41.668770 -103.101660       2 beds     40   \n",
       "4             1            0  41.670050 -103.101620       2 beds     43   \n",
       "\n",
       "   perc_discount                                            image_1  \\\n",
       "0       0.000000  https://a0.muscache.com/im/pictures/40a53028-5...   \n",
       "1       0.000000  https://a0.muscache.com/im/pictures/b6b6703f-b...   \n",
       "2       0.882353  https://a0.muscache.com/im/pictures/9ee93475-6...   \n",
       "3       0.000000  https://a0.muscache.com/im/pictures/7a8d2d04-6...   \n",
       "4       0.000000  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "\n",
       "                                             image_2  \\\n",
       "0  https://a0.muscache.com/im/pictures/66d3a0d5-b...   \n",
       "1  https://a0.muscache.com/im/pictures/49b235b7-7...   \n",
       "2  https://a0.muscache.com/im/pictures/b18ca7d5-0...   \n",
       "3  https://a0.muscache.com/im/pictures/42c9020c-7...   \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "\n",
       "                                             image_3  \\\n",
       "0  https://a0.muscache.com/im/pictures/a7339783-c...   \n",
       "1  https://a0.muscache.com/im/pictures/478c7fd6-7...   \n",
       "2  https://a0.muscache.com/im/pictures/ecc78c28-b...   \n",
       "3  https://a0.muscache.com/im/pictures/52bd72d4-d...   \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "\n",
       "                                             image_4  \\\n",
       "0  https://a0.muscache.com/im/pictures/f8ea448c-d...   \n",
       "1  https://a0.muscache.com/im/pictures/1b6947f8-2...   \n",
       "2  https://a0.muscache.com/im/pictures/05172838-6...   \n",
       "3  https://a0.muscache.com/im/pictures/35c9f58a-d...   \n",
       "4  https://a0.muscache.com/im/pictures/58f6e95a-6...   \n",
       "\n",
       "                                             image_5        city  \n",
       "0  https://a0.muscache.com/im/pictures/1a2a5d6b-f...     Flagler  \n",
       "1  https://a0.muscache.com/im/pictures/ce671a50-b...     Flagler  \n",
       "2  https://a0.muscache.com/im/pictures/f30c7acb-2...  Bridgeport  \n",
       "3  https://a0.muscache.com/im/pictures/d39ad521-3...  Bridgeport  \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...  Bridgeport  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scraped_listings['city'] = scraped_listings['listing_city']\n",
    "scraped_listings.drop(columns=['Unnamed: 0'],inplace=True)\n",
    "scraped_listings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a7df2336",
   "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>listing_id</th>\n",
       "      <th>n_guests</th>\n",
       "      <th>n_bedrooms</th>\n",
       "      <th>n_beds</th>\n",
       "      <th>n_baths</th>\n",
       "      <th>n_amenities</th>\n",
       "      <th>rating_cleanliness</th>\n",
       "      <th>rating_communication</th>\n",
       "      <th>rating_checkin</th>\n",
       "      <th>rating_accuracy</th>\n",
       "      <th>rating_location</th>\n",
       "      <th>rating_value</th>\n",
       "      <th>description</th>\n",
       "      <th>amenities_services</th>\n",
       "      <th>amenities_heat_cool</th>\n",
       "      <th>amenities_bedroom_laundry</th>\n",
       "      <th>amenities_kitchen_dining</th>\n",
       "      <th>amenities_safety</th>\n",
       "      <th>amenities_parking_facilities</th>\n",
       "      <th>amenities_internet_office</th>\n",
       "      <th>amenities_not_included</th>\n",
       "      <th>amenities_entertainment</th>\n",
       "      <th>amenities_bathroom</th>\n",
       "      <th>amenities_outdoor</th>\n",
       "      <th>amenities_location_features</th>\n",
       "      <th>amenities_family</th>\n",
       "      <th>amenities_scenic_views</th>\n",
       "      <th>bed_king</th>\n",
       "      <th>bed_queen</th>\n",
       "      <th>bed_double</th>\n",
       "      <th>bed_single</th>\n",
       "      <th>bed_sofa</th>\n",
       "      <th>bed_air</th>\n",
       "      <th>bed_floor</th>\n",
       "      <th>bed_bunkbed</th>\n",
       "      <th>bed_hammock</th>\n",
       "      <th>bed_baby</th>\n",
       "      <th>description_sentiment_score</th>\n",
       "      <th>description_length</th>\n",
       "      <th>description_reading_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39368048</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>24.0</td>\n",
       "      <td>3.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.2</td>\n",
       "      <td>Relax and Comfortable Room</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.7351</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>553577526302640000</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2.5</td>\n",
       "      <td>60.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.7</td>\n",
       "      <td>Your family will be close to everything when y...</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.7269</td>\n",
       "      <td>83.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>671161306694513920</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>54.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Nestled on the Kiley Ranch trail, relax in a b...</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.8959</td>\n",
       "      <td>141.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>583410599257628928</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>4</th>\n",
       "      <td>582654007380509568</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.7</td>\n",
       "      <td>Enjoy your stay and explore the wonderful city...</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.9819</td>\n",
       "      <td>74.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           listing_id n_guests n_bedrooms n_beds n_baths  n_amenities  \\\n",
       "0            39368048        2          1      1       1         24.0   \n",
       "1  553577526302640000        8          4      4     2.5         60.0   \n",
       "2  671161306694513920        4          1      2       1         54.0   \n",
       "3  583410599257628928      NaN        NaN    NaN     NaN          NaN   \n",
       "4  582654007380509568        5          2      2       1         45.0   \n",
       "\n",
       "   rating_cleanliness  rating_communication  rating_checkin  rating_accuracy  \\\n",
       "0                 3.8                   5.0             5.0              4.2   \n",
       "1                 4.8                   5.0             5.0              4.9   \n",
       "2                 5.0                   5.0             5.0              5.0   \n",
       "3                 NaN                   NaN             NaN              NaN   \n",
       "4                 4.5                   4.8             4.9              4.8   \n",
       "\n",
       "   rating_location  rating_value  \\\n",
       "0              5.0           4.2   \n",
       "1              4.9           4.7   \n",
       "2              5.0           5.0   \n",
       "3              NaN           NaN   \n",
       "4              4.8           4.7   \n",
       "\n",
       "                                         description  amenities_services  \\\n",
       "0                         Relax and Comfortable Room                   2   \n",
       "1  Your family will be close to everything when y...                   4   \n",
       "2  Nestled on the Kiley Ranch trail, relax in a b...                   3   \n",
       "3                                                NaN                   0   \n",
       "4  Enjoy your stay and explore the wonderful city...                   2   \n",
       "\n",
       "   amenities_heat_cool  amenities_bedroom_laundry  amenities_kitchen_dining  \\\n",
       "0                    2                          5                         4   \n",
       "1                    4                          9                        15   \n",
       "2                    2                          8                        15   \n",
       "3                    0                          0                         0   \n",
       "4                    2                          6                        16   \n",
       "\n",
       "   amenities_safety  amenities_parking_facilities  amenities_internet_office  \\\n",
       "0                 4                             1                          2   \n",
       "1                 5                             3                          2   \n",
       "2                 5                             3                          2   \n",
       "3                 0                             0                          0   \n",
       "4                 2                             3                          2   \n",
       "\n",
       "   amenities_not_included  amenities_entertainment  amenities_bathroom  \\\n",
       "0                       1                        1                   2   \n",
       "1                       2                        1                   7   \n",
       "2                       0                        2                   7   \n",
       "3                       0                        0                   0   \n",
       "4                       1                        1                   6   \n",
       "\n",
       "   amenities_outdoor  amenities_location_features  amenities_family  \\\n",
       "0                  1                            0                 0   \n",
       "1                  4                            0                 6   \n",
       "2                  2                            2                 1   \n",
       "3                  0                            0                 0   \n",
       "4                  4                            1                 0   \n",
       "\n",
       "   amenities_scenic_views  bed_king  bed_queen  bed_double  bed_single  \\\n",
       "0                       0       0.0        1.0         0.0         0.0   \n",
       "1                       0       NaN        NaN         NaN         NaN   \n",
       "2                       2       1.0        0.0         0.0         0.0   \n",
       "3                       0       NaN        NaN         NaN         NaN   \n",
       "4                       0       NaN        NaN         NaN         NaN   \n",
       "\n",
       "   bed_sofa  bed_air  bed_floor  bed_bunkbed  bed_hammock  bed_baby  \\\n",
       "0       0.0      0.0        0.0          0.0          0.0       0.0   \n",
       "1       NaN      NaN        NaN          NaN          NaN       NaN   \n",
       "2       1.0      0.0        0.0          0.0          0.0       0.0   \n",
       "3       NaN      NaN        NaN          NaN          NaN       NaN   \n",
       "4       NaN      NaN        NaN          NaN          NaN       NaN   \n",
       "\n",
       "   description_sentiment_score  description_length  description_reading_level  \n",
       "0                       0.7351                 4.0                        NaN  \n",
       "1                       0.7269                83.0                        NaN  \n",
       "2                       0.8959               141.0                        9.0  \n",
       "3                          NaN                 NaN                        NaN  \n",
       "4                       0.9819                74.0                        NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scraped_pages.drop(columns=['Unnamed: 0'],inplace=True)\n",
    "scraped_pages['listing_id'] = scraped_pages['listing_id'].astype('int',errors = 'ignore')\n",
    "scraped_pages.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0a17181a",
   "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>Unnamed: 0.1</th>\n",
       "      <th>listing_id</th>\n",
       "      <th>country</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>postcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>47924385</td>\n",
       "      <td>United States</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Kit Carson County</td>\n",
       "      <td>80815.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>12964075</td>\n",
       "      <td>United States</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Kit Carson County</td>\n",
       "      <td>80815.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>50379619</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Morrill County</td>\n",
       "      <td>69336.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>710231964358460529</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Morrill County</td>\n",
       "      <td>69336.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>723852070242986749</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Morrill County</td>\n",
       "      <td>69336.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0.1          listing_id        country     state  \\\n",
       "0             0            47924385  United States  Colorado   \n",
       "1             1            12964075  United States  Colorado   \n",
       "2             2            50379619  United States  Nebraska   \n",
       "3             3  710231964358460529  United States  Nebraska   \n",
       "4             4  723852070242986749  United States  Nebraska   \n",
       "\n",
       "              county postcode  \n",
       "0  Kit Carson County  80815.0  \n",
       "1  Kit Carson County  80815.0  \n",
       "2     Morrill County  69336.0  \n",
       "3     Morrill County  69336.0  \n",
       "4     Morrill County  69336.0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geographic_data.drop(columns=['Unnamed: 0','latitude','longitude'],inplace=True)\n",
    "geographic_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5da2f2fe",
   "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>listing_id</th>\n",
       "      <th>listing_url</th>\n",
       "      <th>is_superhost</th>\n",
       "      <th>is_premium</th>\n",
       "      <th>rating</th>\n",
       "      <th>n_reviews</th>\n",
       "      <th>listing_city</th>\n",
       "      <th>listing_title</th>\n",
       "      <th>n_pictures</th>\n",
       "      <th>entire_home</th>\n",
       "      <th>hotel_room</th>\n",
       "      <th>private_room</th>\n",
       "      <th>shared_room</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>beds</th>\n",
       "      <th>price</th>\n",
       "      <th>perc_discount</th>\n",
       "      <th>image_1</th>\n",
       "      <th>image_2</th>\n",
       "      <th>image_3</th>\n",
       "      <th>image_4</th>\n",
       "      <th>image_5</th>\n",
       "      <th>city</th>\n",
       "      <th>n_guests</th>\n",
       "      <th>n_bedrooms</th>\n",
       "      <th>n_beds</th>\n",
       "      <th>n_baths</th>\n",
       "      <th>n_amenities</th>\n",
       "      <th>rating_cleanliness</th>\n",
       "      <th>rating_communication</th>\n",
       "      <th>rating_checkin</th>\n",
       "      <th>rating_accuracy</th>\n",
       "      <th>rating_location</th>\n",
       "      <th>rating_value</th>\n",
       "      <th>description</th>\n",
       "      <th>amenities_services</th>\n",
       "      <th>amenities_heat_cool</th>\n",
       "      <th>amenities_bedroom_laundry</th>\n",
       "      <th>amenities_kitchen_dining</th>\n",
       "      <th>amenities_safety</th>\n",
       "      <th>amenities_parking_facilities</th>\n",
       "      <th>amenities_internet_office</th>\n",
       "      <th>amenities_not_included</th>\n",
       "      <th>amenities_entertainment</th>\n",
       "      <th>amenities_bathroom</th>\n",
       "      <th>amenities_outdoor</th>\n",
       "      <th>amenities_location_features</th>\n",
       "      <th>amenities_family</th>\n",
       "      <th>amenities_scenic_views</th>\n",
       "      <th>bed_king</th>\n",
       "      <th>bed_queen</th>\n",
       "      <th>bed_double</th>\n",
       "      <th>bed_single</th>\n",
       "      <th>bed_sofa</th>\n",
       "      <th>bed_air</th>\n",
       "      <th>bed_floor</th>\n",
       "      <th>bed_bunkbed</th>\n",
       "      <th>bed_hammock</th>\n",
       "      <th>bed_baby</th>\n",
       "      <th>description_sentiment_score</th>\n",
       "      <th>description_length</th>\n",
       "      <th>description_reading_level</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>country</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>postcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>47924385</td>\n",
       "      <td>https://www.airbnb.com/rooms/47924385</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.86</td>\n",
       "      <td>207.0</td>\n",
       "      <td>Flagler</td>\n",
       "      <td>Home in Flagler</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.292940</td>\n",
       "      <td>-103.064290</td>\n",
       "      <td>1 king bed</td>\n",
       "      <td>77</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/40a53028-5...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/66d3a0d5-b...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/a7339783-c...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/f8ea448c-d...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/1a2a5d6b-f...</td>\n",
       "      <td>Flagler</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>35.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>Cozy little house in the town of Flagler just ...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.9436</td>\n",
       "      <td>117.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Kit Carson County</td>\n",
       "      <td>80815.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12964075</td>\n",
       "      <td>https://www.airbnb.com/rooms/12964075</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.87</td>\n",
       "      <td>419.0</td>\n",
       "      <td>Flagler</td>\n",
       "      <td>Private room in Flagler</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>39.294920</td>\n",
       "      <td>-103.063720</td>\n",
       "      <td>1 queen bed</td>\n",
       "      <td>65</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/b6b6703f-b...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/49b235b7-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/478c7fd6-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/1b6947f8-2...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/ce671a50-b...</td>\n",
       "      <td>Flagler</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.9</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>You’ll love my place because of the quiet loca...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.9402</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Kit Carson County</td>\n",
       "      <td>80815.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50379619</td>\n",
       "      <td>https://www.airbnb.com/rooms/50379619</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.93</td>\n",
       "      <td>42.0</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>Home in Bridgeport</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41.665535</td>\n",
       "      <td>-103.095772</td>\n",
       "      <td>3 beds</td>\n",
       "      <td>75</td>\n",
       "      <td>0.882353</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/9ee93475-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/b18ca7d5-0...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/ecc78c28-b...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/05172838-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/f30c7acb-2...</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>50.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Older home in a quite neighborhood. Free parki...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.7490</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Morrill County</td>\n",
       "      <td>69336.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>710231964358460529</td>\n",
       "      <td>https://www.airbnb.com/rooms/710231964358460529</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>Private room in Bridgeport</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>41.668770</td>\n",
       "      <td>-103.101660</td>\n",
       "      <td>2 beds</td>\n",
       "      <td>40</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/7a8d2d04-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/42c9020c-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/52bd72d4-d...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/35c9f58a-d...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/d39ad521-3...</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Morrill County</td>\n",
       "      <td>69336.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>723852070242986749</td>\n",
       "      <td>https://www.airbnb.com/rooms/723852070242986749</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>Private room in Bridgeport</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>41.670050</td>\n",
       "      <td>-103.101620</td>\n",
       "      <td>2 beds</td>\n",
       "      <td>43</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/58f6e95a-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Morrill County</td>\n",
       "      <td>69336.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           listing_id                                      listing_url  \\\n",
       "0            47924385            https://www.airbnb.com/rooms/47924385   \n",
       "1            12964075            https://www.airbnb.com/rooms/12964075   \n",
       "2            50379619            https://www.airbnb.com/rooms/50379619   \n",
       "3  710231964358460529  https://www.airbnb.com/rooms/710231964358460529   \n",
       "4  723852070242986749  https://www.airbnb.com/rooms/723852070242986749   \n",
       "\n",
       "   is_superhost  is_premium rating  n_reviews listing_city  \\\n",
       "0             1           0   4.86      207.0      Flagler   \n",
       "1             1           0   4.87      419.0      Flagler   \n",
       "2             1           0   4.93       42.0   Bridgeport   \n",
       "3             0           0    NaN        NaN   Bridgeport   \n",
       "4             0           0    NaN        NaN   Bridgeport   \n",
       "\n",
       "                listing_title  n_pictures  entire_home  hotel_room  \\\n",
       "0             Home in Flagler         9.0            1           0   \n",
       "1     Private room in Flagler        11.0            0           0   \n",
       "2          Home in Bridgeport        15.0            1           0   \n",
       "3  Private room in Bridgeport         5.0            0           0   \n",
       "4  Private room in Bridgeport         7.0            0           0   \n",
       "\n",
       "   private_room  shared_room   latitude   longitude         beds  price  \\\n",
       "0             0            0  39.292940 -103.064290   1 king bed     77   \n",
       "1             1            0  39.294920 -103.063720  1 queen bed     65   \n",
       "2             0            0  41.665535 -103.095772       3 beds     75   \n",
       "3             1            0  41.668770 -103.101660       2 beds     40   \n",
       "4             1            0  41.670050 -103.101620       2 beds     43   \n",
       "\n",
       "   perc_discount                                            image_1  \\\n",
       "0       0.000000  https://a0.muscache.com/im/pictures/40a53028-5...   \n",
       "1       0.000000  https://a0.muscache.com/im/pictures/b6b6703f-b...   \n",
       "2       0.882353  https://a0.muscache.com/im/pictures/9ee93475-6...   \n",
       "3       0.000000  https://a0.muscache.com/im/pictures/7a8d2d04-6...   \n",
       "4       0.000000  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "\n",
       "                                             image_2  \\\n",
       "0  https://a0.muscache.com/im/pictures/66d3a0d5-b...   \n",
       "1  https://a0.muscache.com/im/pictures/49b235b7-7...   \n",
       "2  https://a0.muscache.com/im/pictures/b18ca7d5-0...   \n",
       "3  https://a0.muscache.com/im/pictures/42c9020c-7...   \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "\n",
       "                                             image_3  \\\n",
       "0  https://a0.muscache.com/im/pictures/a7339783-c...   \n",
       "1  https://a0.muscache.com/im/pictures/478c7fd6-7...   \n",
       "2  https://a0.muscache.com/im/pictures/ecc78c28-b...   \n",
       "3  https://a0.muscache.com/im/pictures/52bd72d4-d...   \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "\n",
       "                                             image_4  \\\n",
       "0  https://a0.muscache.com/im/pictures/f8ea448c-d...   \n",
       "1  https://a0.muscache.com/im/pictures/1b6947f8-2...   \n",
       "2  https://a0.muscache.com/im/pictures/05172838-6...   \n",
       "3  https://a0.muscache.com/im/pictures/35c9f58a-d...   \n",
       "4  https://a0.muscache.com/im/pictures/58f6e95a-6...   \n",
       "\n",
       "                                             image_5        city n_guests  \\\n",
       "0  https://a0.muscache.com/im/pictures/1a2a5d6b-f...     Flagler        3   \n",
       "1  https://a0.muscache.com/im/pictures/ce671a50-b...     Flagler        3   \n",
       "2  https://a0.muscache.com/im/pictures/f30c7acb-2...  Bridgeport        4   \n",
       "3  https://a0.muscache.com/im/pictures/d39ad521-3...  Bridgeport      NaN   \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...  Bridgeport      NaN   \n",
       "\n",
       "  n_bedrooms n_beds n_baths  n_amenities  rating_cleanliness  \\\n",
       "0          1      1       1         35.0                 4.8   \n",
       "1          1      1       1         31.0                 4.8   \n",
       "2          1      3       2         50.0                 4.9   \n",
       "3        NaN    NaN     NaN          NaN                 NaN   \n",
       "4        NaN    NaN     NaN          NaN                 NaN   \n",
       "\n",
       "   rating_communication  rating_checkin  rating_accuracy  rating_location  \\\n",
       "0                   5.0             4.9              4.9              4.9   \n",
       "1                   4.9             5.0              5.0              4.9   \n",
       "2                   5.0             5.0              5.0              4.8   \n",
       "3                   NaN             NaN              NaN              NaN   \n",
       "4                   NaN             NaN              NaN              NaN   \n",
       "\n",
       "   rating_value                                        description  \\\n",
       "0           4.9  Cozy little house in the town of Flagler just ...   \n",
       "1           4.9  You’ll love my place because of the quiet loca...   \n",
       "2           5.0  Older home in a quite neighborhood. Free parki...   \n",
       "3           NaN                                                NaN   \n",
       "4           NaN                                                NaN   \n",
       "\n",
       "   amenities_services  amenities_heat_cool  amenities_bedroom_laundry  \\\n",
       "0                 3.0                  2.0                       10.0   \n",
       "1                 3.0                  3.0                        7.0   \n",
       "2                 5.0                  3.0                        8.0   \n",
       "3                 NaN                  NaN                        NaN   \n",
       "4                 NaN                  NaN                        NaN   \n",
       "\n",
       "   amenities_kitchen_dining  amenities_safety  amenities_parking_facilities  \\\n",
       "0                       5.0               4.0                           2.0   \n",
       "1                       4.0               2.0                           2.0   \n",
       "2                      15.0               3.0                           3.0   \n",
       "3                       NaN               NaN                           NaN   \n",
       "4                       NaN               NaN                           NaN   \n",
       "\n",
       "   amenities_internet_office  amenities_not_included  amenities_entertainment  \\\n",
       "0                        2.0                     3.0                      1.0   \n",
       "1                        2.0                     1.0                      1.0   \n",
       "2                        2.0                     4.0                      1.0   \n",
       "3                        NaN                     NaN                      NaN   \n",
       "4                        NaN                     NaN                      NaN   \n",
       "\n",
       "   amenities_bathroom  amenities_outdoor  amenities_location_features  \\\n",
       "0                 5.0                0.0                          1.0   \n",
       "1                 6.0                0.0                          1.0   \n",
       "2                 7.0                1.0                          1.0   \n",
       "3                 NaN                NaN                          NaN   \n",
       "4                 NaN                NaN                          NaN   \n",
       "\n",
       "   amenities_family  amenities_scenic_views  bed_king  bed_queen  bed_double  \\\n",
       "0               0.0                     0.0       1.0        0.0         0.0   \n",
       "1               0.0                     0.0       0.0        1.0         0.0   \n",
       "2               1.0                     0.0       0.0        1.0         0.0   \n",
       "3               NaN                     NaN       NaN        NaN         NaN   \n",
       "4               NaN                     NaN       NaN        NaN         NaN   \n",
       "\n",
       "   bed_single  bed_sofa  bed_air  bed_floor  bed_bunkbed  bed_hammock  \\\n",
       "0         0.0       1.0      0.0        0.0          0.0          0.0   \n",
       "1         0.0       0.0      0.0        0.0          0.0          0.0   \n",
       "2         0.0       0.0      0.0        0.0          1.0          0.0   \n",
       "3         NaN       NaN      NaN        NaN          NaN          NaN   \n",
       "4         NaN       NaN      NaN        NaN          NaN          NaN   \n",
       "\n",
       "   bed_baby  description_sentiment_score  description_length  \\\n",
       "0       0.0                       0.9436               117.0   \n",
       "1       0.0                       0.9402                30.0   \n",
       "2       0.0                       0.7490                14.0   \n",
       "3       NaN                          NaN                 NaN   \n",
       "4       NaN                          NaN                 NaN   \n",
       "\n",
       "   description_reading_level  Unnamed: 0.1        country     state  \\\n",
       "0                        7.0           0.0  United States  Colorado   \n",
       "1                        NaN           1.0  United States  Colorado   \n",
       "2                        NaN           2.0  United States  Nebraska   \n",
       "3                        NaN           3.0  United States  Nebraska   \n",
       "4                        NaN           4.0  United States  Nebraska   \n",
       "\n",
       "              county postcode  \n",
       "0  Kit Carson County  80815.0  \n",
       "1  Kit Carson County  80815.0  \n",
       "2     Morrill County  69336.0  \n",
       "3     Morrill County  69336.0  \n",
       "4     Morrill County  69336.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb_data = scraped_listings.merge(\n",
    "    scraped_pages, \n",
    "    how='left',\n",
    "    on='listing_id'\n",
    ")\n",
    "\n",
    "airbnb_data = airbnb_data.merge(\n",
    "    geographic_data,\n",
    "    how='left',\n",
    "    on='listing_id'\n",
    ")\n",
    "\n",
    "airbnb_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4351e6c8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b7569961",
   "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>listing_id</th>\n",
       "      <th>listing_url</th>\n",
       "      <th>is_superhost</th>\n",
       "      <th>is_premium</th>\n",
       "      <th>rating</th>\n",
       "      <th>n_reviews</th>\n",
       "      <th>country</th>\n",
       "      <th>state</th>\n",
       "      <th>city</th>\n",
       "      <th>county</th>\n",
       "      <th>postcode</th>\n",
       "      <th>listing_title</th>\n",
       "      <th>n_pictures</th>\n",
       "      <th>entire_home</th>\n",
       "      <th>hotel_room</th>\n",
       "      <th>private_room</th>\n",
       "      <th>shared_room</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>price</th>\n",
       "      <th>perc_discount</th>\n",
       "      <th>n_guests</th>\n",
       "      <th>n_bedrooms</th>\n",
       "      <th>n_beds</th>\n",
       "      <th>n_baths</th>\n",
       "      <th>rating_cleanliness</th>\n",
       "      <th>rating_communication</th>\n",
       "      <th>rating_checkin</th>\n",
       "      <th>rating_accuracy</th>\n",
       "      <th>rating_location</th>\n",
       "      <th>rating_value</th>\n",
       "      <th>n_amenities</th>\n",
       "      <th>amenities_services</th>\n",
       "      <th>amenities_heat_cool</th>\n",
       "      <th>amenities_bedroom_laundry</th>\n",
       "      <th>amenities_kitchen_dining</th>\n",
       "      <th>amenities_safety</th>\n",
       "      <th>amenities_parking_facilities</th>\n",
       "      <th>amenities_internet_office</th>\n",
       "      <th>amenities_not_included</th>\n",
       "      <th>amenities_entertainment</th>\n",
       "      <th>amenities_bathroom</th>\n",
       "      <th>amenities_outdoor</th>\n",
       "      <th>amenities_location_features</th>\n",
       "      <th>amenities_family</th>\n",
       "      <th>amenities_scenic_views</th>\n",
       "      <th>bed_king</th>\n",
       "      <th>bed_queen</th>\n",
       "      <th>bed_double</th>\n",
       "      <th>bed_single</th>\n",
       "      <th>bed_sofa</th>\n",
       "      <th>bed_air</th>\n",
       "      <th>bed_floor</th>\n",
       "      <th>bed_bunkbed</th>\n",
       "      <th>bed_hammock</th>\n",
       "      <th>bed_baby</th>\n",
       "      <th>description</th>\n",
       "      <th>description_sentiment_score</th>\n",
       "      <th>description_length</th>\n",
       "      <th>description_reading_level</th>\n",
       "      <th>image_1</th>\n",
       "      <th>image_2</th>\n",
       "      <th>image_3</th>\n",
       "      <th>image_4</th>\n",
       "      <th>image_5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>47924385</td>\n",
       "      <td>https://www.airbnb.com/rooms/47924385</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.86</td>\n",
       "      <td>207.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Flagler</td>\n",
       "      <td>Kit Carson County</td>\n",
       "      <td>80815.0</td>\n",
       "      <td>Home in Flagler</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.292940</td>\n",
       "      <td>-103.064290</td>\n",
       "      <td>77</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>35.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Cozy little house in the town of Flagler just ...</td>\n",
       "      <td>0.9436</td>\n",
       "      <td>117.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/40a53028-5...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/66d3a0d5-b...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/a7339783-c...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/f8ea448c-d...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/1a2a5d6b-f...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12964075</td>\n",
       "      <td>https://www.airbnb.com/rooms/12964075</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.87</td>\n",
       "      <td>419.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Flagler</td>\n",
       "      <td>Kit Carson County</td>\n",
       "      <td>80815.0</td>\n",
       "      <td>Private room in Flagler</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>39.294920</td>\n",
       "      <td>-103.063720</td>\n",
       "      <td>65</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.9</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>31.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>You’ll love my place because of the quiet loca...</td>\n",
       "      <td>0.9402</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/b6b6703f-b...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/49b235b7-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/478c7fd6-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/1b6947f8-2...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/ce671a50-b...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50379619</td>\n",
       "      <td>https://www.airbnb.com/rooms/50379619</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.93</td>\n",
       "      <td>42.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>Morrill County</td>\n",
       "      <td>69336.0</td>\n",
       "      <td>Home in Bridgeport</td>\n",
       "      <td>15.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41.665535</td>\n",
       "      <td>-103.095772</td>\n",
       "      <td>75</td>\n",
       "      <td>0.882353</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4.9</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Older home in a quite neighborhood. Free parki...</td>\n",
       "      <td>0.7490</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/9ee93475-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/b18ca7d5-0...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/ecc78c28-b...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/05172838-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/f30c7acb-2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>710231964358460529</td>\n",
       "      <td>https://www.airbnb.com/rooms/710231964358460529</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>Morrill County</td>\n",
       "      <td>69336.0</td>\n",
       "      <td>Private room in Bridgeport</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>41.668770</td>\n",
       "      <td>-103.101660</td>\n",
       "      <td>40</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/7a8d2d04-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/42c9020c-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/52bd72d4-d...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/35c9f58a-d...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/d39ad521-3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>723852070242986749</td>\n",
       "      <td>https://www.airbnb.com/rooms/723852070242986749</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>Morrill County</td>\n",
       "      <td>69336.0</td>\n",
       "      <td>Private room in Bridgeport</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>41.670050</td>\n",
       "      <td>-103.101620</td>\n",
       "      <td>43</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/58f6e95a-6...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20255742</td>\n",
       "      <td>https://www.airbnb.com/rooms/20255742</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Hemingford</td>\n",
       "      <td>Box Butte County</td>\n",
       "      <td>69348.0</td>\n",
       "      <td>Camper/RV in Hemingford</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>42.315700</td>\n",
       "      <td>-103.080900</td>\n",
       "      <td>400</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38 ft 5th wheel camper.  Functional shower, si...</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/e00d199e-2...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/4202cb53-f...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/f1c33d3f-5...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/e44f5b77-f...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/017ffee5-5...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>595526406823737723</td>\n",
       "      <td>https://www.airbnb.com/rooms/595526406823737723</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.96</td>\n",
       "      <td>23.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Hemingford</td>\n",
       "      <td>Box Butte County</td>\n",
       "      <td>69348.0</td>\n",
       "      <td>Cottage in Hemingford</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>42.303790</td>\n",
       "      <td>-103.073150</td>\n",
       "      <td>150</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>53049583</td>\n",
       "      <td>https://www.airbnb.com/rooms/53049583</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Crawford</td>\n",
       "      <td>Dawes County</td>\n",
       "      <td>69354.0</td>\n",
       "      <td>Cabin in Crawford</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>42.465530</td>\n",
       "      <td>-103.146400</td>\n",
       "      <td>180</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>4.9</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.8</td>\n",
       "      <td>4.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>34.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Come visit Western Nebraska at this cabin righ...</td>\n",
       "      <td>0.9255</td>\n",
       "      <td>87.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/6461bcfa-1...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/16bba3c1-e...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/bb5ed55c-e...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/d440f389-5...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/b22d75ad-a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>687854558225404199</td>\n",
       "      <td>https://www.airbnb.com/rooms/687854558225404199</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Dawes County</td>\n",
       "      <td>Dawes County</td>\n",
       "      <td>69354.0</td>\n",
       "      <td>Tiny home in Dawes County</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>42.524706</td>\n",
       "      <td>-103.067292</td>\n",
       "      <td>100</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/miso/Hosti...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>545830880303981576</td>\n",
       "      <td>https://www.airbnb.com/rooms/545830880303981576</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>Nebraska</td>\n",
       "      <td>Dawes County</td>\n",
       "      <td>Dawes County</td>\n",
       "      <td>69354.0</td>\n",
       "      <td>Cottage in Dawes County</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>42.467040</td>\n",
       "      <td>-103.096060</td>\n",
       "      <td>225</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/6a437a93-e...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/8455e2b5-1...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/765ff0af-8...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/e6d9f951-7...</td>\n",
       "      <td>https://a0.muscache.com/im/pictures/600654f8-a...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           listing_id                                      listing_url  \\\n",
       "0            47924385            https://www.airbnb.com/rooms/47924385   \n",
       "1            12964075            https://www.airbnb.com/rooms/12964075   \n",
       "2            50379619            https://www.airbnb.com/rooms/50379619   \n",
       "3  710231964358460529  https://www.airbnb.com/rooms/710231964358460529   \n",
       "4  723852070242986749  https://www.airbnb.com/rooms/723852070242986749   \n",
       "5            20255742            https://www.airbnb.com/rooms/20255742   \n",
       "6  595526406823737723  https://www.airbnb.com/rooms/595526406823737723   \n",
       "7            53049583            https://www.airbnb.com/rooms/53049583   \n",
       "8  687854558225404199  https://www.airbnb.com/rooms/687854558225404199   \n",
       "9  545830880303981576  https://www.airbnb.com/rooms/545830880303981576   \n",
       "\n",
       "   is_superhost  is_premium rating  n_reviews        country     state  \\\n",
       "0             1           0   4.86      207.0  United States  Colorado   \n",
       "1             1           0   4.87      419.0  United States  Colorado   \n",
       "2             1           0   4.93       42.0  United States  Nebraska   \n",
       "3             0           0    NaN        NaN  United States  Nebraska   \n",
       "4             0           0    NaN        NaN  United States  Nebraska   \n",
       "5             0           0    NaN        NaN  United States  Nebraska   \n",
       "6             1           0   4.96       23.0  United States  Nebraska   \n",
       "7             0           0    5.0       12.0  United States  Nebraska   \n",
       "8             1           0    NaN        NaN  United States  Nebraska   \n",
       "9             0           0    5.0        6.0  United States  Nebraska   \n",
       "\n",
       "           city             county postcode               listing_title  \\\n",
       "0       Flagler  Kit Carson County  80815.0             Home in Flagler   \n",
       "1       Flagler  Kit Carson County  80815.0     Private room in Flagler   \n",
       "2    Bridgeport     Morrill County  69336.0          Home in Bridgeport   \n",
       "3    Bridgeport     Morrill County  69336.0  Private room in Bridgeport   \n",
       "4    Bridgeport     Morrill County  69336.0  Private room in Bridgeport   \n",
       "5    Hemingford   Box Butte County  69348.0     Camper/RV in Hemingford   \n",
       "6    Hemingford   Box Butte County  69348.0       Cottage in Hemingford   \n",
       "7      Crawford       Dawes County  69354.0           Cabin in Crawford   \n",
       "8  Dawes County       Dawes County  69354.0   Tiny home in Dawes County   \n",
       "9  Dawes County       Dawes County  69354.0     Cottage in Dawes County   \n",
       "\n",
       "   n_pictures  entire_home  hotel_room  private_room  shared_room   latitude  \\\n",
       "0         9.0            1           0             0            0  39.292940   \n",
       "1        11.0            0           0             1            0  39.294920   \n",
       "2        15.0            1           0             0            0  41.665535   \n",
       "3         5.0            0           0             1            0  41.668770   \n",
       "4         7.0            0           0             1            0  41.670050   \n",
       "5         8.0            1           0             0            0  42.315700   \n",
       "6        11.0            1           0             0            0  42.303790   \n",
       "7        22.0            1           0             0            0  42.465530   \n",
       "8         9.0            1           0             0            0  42.524706   \n",
       "9        18.0            1           0             0            0  42.467040   \n",
       "\n",
       "    longitude  price  perc_discount n_guests n_bedrooms n_beds n_baths  \\\n",
       "0 -103.064290     77       0.000000        3          1      1       1   \n",
       "1 -103.063720     65       0.000000        3          1      1       1   \n",
       "2 -103.095772     75       0.882353        4          1      3       2   \n",
       "3 -103.101660     40       0.000000      NaN        NaN    NaN     NaN   \n",
       "4 -103.101620     43       0.000000      NaN        NaN    NaN     NaN   \n",
       "5 -103.080900    400       0.000000        4          1      2       1   \n",
       "6 -103.073150    150       0.000000      NaN        NaN    NaN     NaN   \n",
       "7 -103.146400    180       0.800000       10          4      7       3   \n",
       "8 -103.067292    100       0.000000      NaN        NaN    NaN     NaN   \n",
       "9 -103.096060    225       0.000000      NaN        NaN    NaN     NaN   \n",
       "\n",
       "   rating_cleanliness  rating_communication  rating_checkin  rating_accuracy  \\\n",
       "0                 4.8                   5.0             4.9              4.9   \n",
       "1                 4.8                   4.9             5.0              5.0   \n",
       "2                 4.9                   5.0             5.0              5.0   \n",
       "3                 NaN                   NaN             NaN              NaN   \n",
       "4                 NaN                   NaN             NaN              NaN   \n",
       "5                 NaN                   NaN             NaN              NaN   \n",
       "6                 NaN                   NaN             NaN              NaN   \n",
       "7                 4.9                   5.0             4.9              4.8   \n",
       "8                 NaN                   NaN             NaN              NaN   \n",
       "9                 NaN                   NaN             NaN              NaN   \n",
       "\n",
       "   rating_location  rating_value  n_amenities  amenities_services  \\\n",
       "0              4.9           4.9         35.0                 3.0   \n",
       "1              4.9           4.9         31.0                 3.0   \n",
       "2              4.8           5.0         50.0                 5.0   \n",
       "3              NaN           NaN          NaN                 NaN   \n",
       "4              NaN           NaN          NaN                 NaN   \n",
       "5              NaN           NaN          4.0                 1.0   \n",
       "6              NaN           NaN          NaN                 NaN   \n",
       "7              4.9           4.9         34.0                 2.0   \n",
       "8              NaN           NaN          NaN                 NaN   \n",
       "9              NaN           NaN          NaN                 NaN   \n",
       "\n",
       "   amenities_heat_cool  amenities_bedroom_laundry  amenities_kitchen_dining  \\\n",
       "0                  2.0                       10.0                       5.0   \n",
       "1                  3.0                        7.0                       4.0   \n",
       "2                  3.0                        8.0                      15.0   \n",
       "3                  NaN                        NaN                       NaN   \n",
       "4                  NaN                        NaN                       NaN   \n",
       "5                  0.0                        2.0                       0.0   \n",
       "6                  NaN                        NaN                       NaN   \n",
       "7                  3.0                        5.0                      11.0   \n",
       "8                  NaN                        NaN                       NaN   \n",
       "9                  NaN                        NaN                       NaN   \n",
       "\n",
       "   amenities_safety  amenities_parking_facilities  amenities_internet_office  \\\n",
       "0               4.0                           2.0                        2.0   \n",
       "1               2.0                           2.0                        2.0   \n",
       "2               3.0                           3.0                        2.0   \n",
       "3               NaN                           NaN                        NaN   \n",
       "4               NaN                           NaN                        NaN   \n",
       "5               0.0                           0.0                        0.0   \n",
       "6               NaN                           NaN                        NaN   \n",
       "7               3.0                           1.0                        2.0   \n",
       "8               NaN                           NaN                        NaN   \n",
       "9               NaN                           NaN                        NaN   \n",
       "\n",
       "   amenities_not_included  amenities_entertainment  amenities_bathroom  \\\n",
       "0                     3.0                      1.0                 5.0   \n",
       "1                     1.0                      1.0                 6.0   \n",
       "2                     4.0                      1.0                 7.0   \n",
       "3                     NaN                      NaN                 NaN   \n",
       "4                     NaN                      NaN                 NaN   \n",
       "5                    10.0                      0.0                 1.0   \n",
       "6                     NaN                      NaN                 NaN   \n",
       "7                     4.0                      1.0                 2.0   \n",
       "8                     NaN                      NaN                 NaN   \n",
       "9                     NaN                      NaN                 NaN   \n",
       "\n",
       "   amenities_outdoor  amenities_location_features  amenities_family  \\\n",
       "0                0.0                          1.0               0.0   \n",
       "1                0.0                          1.0               0.0   \n",
       "2                1.0                          1.0               1.0   \n",
       "3                NaN                          NaN               NaN   \n",
       "4                NaN                          NaN               NaN   \n",
       "5                0.0                          0.0               0.0   \n",
       "6                NaN                          NaN               NaN   \n",
       "7                2.0                          0.0               1.0   \n",
       "8                NaN                          NaN               NaN   \n",
       "9                NaN                          NaN               NaN   \n",
       "\n",
       "   amenities_scenic_views  bed_king  bed_queen  bed_double  bed_single  \\\n",
       "0                     0.0       1.0        0.0         0.0         0.0   \n",
       "1                     0.0       0.0        1.0         0.0         0.0   \n",
       "2                     0.0       0.0        1.0         0.0         0.0   \n",
       "3                     NaN       NaN        NaN         NaN         NaN   \n",
       "4                     NaN       NaN        NaN         NaN         NaN   \n",
       "5                     0.0       0.0        1.0         0.0         0.0   \n",
       "6                     NaN       NaN        NaN         NaN         NaN   \n",
       "7                     1.0       1.0        0.0         2.0         4.0   \n",
       "8                     NaN       NaN        NaN         NaN         NaN   \n",
       "9                     NaN       NaN        NaN         NaN         NaN   \n",
       "\n",
       "   bed_sofa  bed_air  bed_floor  bed_bunkbed  bed_hammock  bed_baby  \\\n",
       "0       1.0      0.0        0.0          0.0          0.0       0.0   \n",
       "1       0.0      0.0        0.0          0.0          0.0       0.0   \n",
       "2       0.0      0.0        0.0          1.0          0.0       0.0   \n",
       "3       NaN      NaN        NaN          NaN          NaN       NaN   \n",
       "4       NaN      NaN        NaN          NaN          NaN       NaN   \n",
       "5       1.0      0.0        1.0          0.0          0.0       0.0   \n",
       "6       NaN      NaN        NaN          NaN          NaN       NaN   \n",
       "7       0.0      0.0        0.0          0.0          0.0       0.0   \n",
       "8       NaN      NaN        NaN          NaN          NaN       NaN   \n",
       "9       NaN      NaN        NaN          NaN          NaN       NaN   \n",
       "\n",
       "                                         description  \\\n",
       "0  Cozy little house in the town of Flagler just ...   \n",
       "1  You’ll love my place because of the quiet loca...   \n",
       "2  Older home in a quite neighborhood. Free parki...   \n",
       "3                                                NaN   \n",
       "4                                                NaN   \n",
       "5  38 ft 5th wheel camper.  Functional shower, si...   \n",
       "6                                                NaN   \n",
       "7  Come visit Western Nebraska at this cabin righ...   \n",
       "8                                                NaN   \n",
       "9                                                NaN   \n",
       "\n",
       "   description_sentiment_score  description_length  description_reading_level  \\\n",
       "0                       0.9436               117.0                        7.0   \n",
       "1                       0.9402                30.0                        NaN   \n",
       "2                       0.7490                14.0                        NaN   \n",
       "3                          NaN                 NaN                        NaN   \n",
       "4                          NaN                 NaN                        NaN   \n",
       "5                       0.0000                11.0                        NaN   \n",
       "6                          NaN                 NaN                        NaN   \n",
       "7                       0.9255                87.0                        NaN   \n",
       "8                          NaN                 NaN                        NaN   \n",
       "9                          NaN                 NaN                        NaN   \n",
       "\n",
       "                                             image_1  \\\n",
       "0  https://a0.muscache.com/im/pictures/40a53028-5...   \n",
       "1  https://a0.muscache.com/im/pictures/b6b6703f-b...   \n",
       "2  https://a0.muscache.com/im/pictures/9ee93475-6...   \n",
       "3  https://a0.muscache.com/im/pictures/7a8d2d04-6...   \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "5  https://a0.muscache.com/im/pictures/e00d199e-2...   \n",
       "6  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "7  https://a0.muscache.com/im/pictures/6461bcfa-1...   \n",
       "8  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "9  https://a0.muscache.com/im/pictures/6a437a93-e...   \n",
       "\n",
       "                                             image_2  \\\n",
       "0  https://a0.muscache.com/im/pictures/66d3a0d5-b...   \n",
       "1  https://a0.muscache.com/im/pictures/49b235b7-7...   \n",
       "2  https://a0.muscache.com/im/pictures/b18ca7d5-0...   \n",
       "3  https://a0.muscache.com/im/pictures/42c9020c-7...   \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "5  https://a0.muscache.com/im/pictures/4202cb53-f...   \n",
       "6  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "7  https://a0.muscache.com/im/pictures/16bba3c1-e...   \n",
       "8  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "9  https://a0.muscache.com/im/pictures/8455e2b5-1...   \n",
       "\n",
       "                                             image_3  \\\n",
       "0  https://a0.muscache.com/im/pictures/a7339783-c...   \n",
       "1  https://a0.muscache.com/im/pictures/478c7fd6-7...   \n",
       "2  https://a0.muscache.com/im/pictures/ecc78c28-b...   \n",
       "3  https://a0.muscache.com/im/pictures/52bd72d4-d...   \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "5  https://a0.muscache.com/im/pictures/f1c33d3f-5...   \n",
       "6  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "7  https://a0.muscache.com/im/pictures/bb5ed55c-e...   \n",
       "8  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "9  https://a0.muscache.com/im/pictures/765ff0af-8...   \n",
       "\n",
       "                                             image_4  \\\n",
       "0  https://a0.muscache.com/im/pictures/f8ea448c-d...   \n",
       "1  https://a0.muscache.com/im/pictures/1b6947f8-2...   \n",
       "2  https://a0.muscache.com/im/pictures/05172838-6...   \n",
       "3  https://a0.muscache.com/im/pictures/35c9f58a-d...   \n",
       "4  https://a0.muscache.com/im/pictures/58f6e95a-6...   \n",
       "5  https://a0.muscache.com/im/pictures/e44f5b77-f...   \n",
       "6  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "7  https://a0.muscache.com/im/pictures/d440f389-5...   \n",
       "8  https://a0.muscache.com/im/pictures/miso/Hosti...   \n",
       "9  https://a0.muscache.com/im/pictures/e6d9f951-7...   \n",
       "\n",
       "                                             image_5  \n",
       "0  https://a0.muscache.com/im/pictures/1a2a5d6b-f...  \n",
       "1  https://a0.muscache.com/im/pictures/ce671a50-b...  \n",
       "2  https://a0.muscache.com/im/pictures/f30c7acb-2...  \n",
       "3  https://a0.muscache.com/im/pictures/d39ad521-3...  \n",
       "4  https://a0.muscache.com/im/pictures/miso/Hosti...  \n",
       "5  https://a0.muscache.com/im/pictures/017ffee5-5...  \n",
       "6  https://a0.muscache.com/im/pictures/miso/Hosti...  \n",
       "7  https://a0.muscache.com/im/pictures/b22d75ad-a...  \n",
       "8  https://a0.muscache.com/im/pictures/miso/Hosti...  \n",
       "9  https://a0.muscache.com/im/pictures/600654f8-a...  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airbnb_data = airbnb_data[['listing_id','listing_url','is_superhost','is_premium','rating',\n",
    "                          'n_reviews','country','state','city','county','postcode','listing_title','n_pictures','entire_home',\n",
    "                         'hotel_room','private_room','shared_room','latitude','longitude','price',\n",
    "                         'perc_discount','n_guests','n_bedrooms','n_beds','n_baths',\n",
    "                         'rating_cleanliness','rating_communication','rating_checkin','rating_accuracy',\n",
    "                         'rating_location','rating_value','n_amenities','amenities_services','amenities_heat_cool',\n",
    "                         'amenities_bedroom_laundry','amenities_kitchen_dining','amenities_safety',\n",
    "                          'amenities_parking_facilities','amenities_internet_office','amenities_not_included',\n",
    "                          'amenities_entertainment','amenities_bathroom','amenities_outdoor',\n",
    "                          'amenities_location_features','amenities_family','amenities_scenic_views','bed_king',\n",
    "                          'bed_queen','bed_double','bed_single','bed_sofa','bed_air','bed_floor','bed_bunkbed',\n",
    "                          'bed_hammock','bed_baby','description','description_sentiment_score','description_length',\n",
    "                          'description_reading_level','image_1','image_2','image_3','image_4',\n",
    "                          'image_5'\n",
    "                         ]]\n",
    "\n",
    "\n",
    "airbnb_data.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d3e11ca6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_rating(x):\n",
    "    try:\n",
    "        if x == 'New':\n",
    "            return None\n",
    "        else:\n",
    "            return x\n",
    "    except:\n",
    "        return x\n",
    "    \n",
    "airbnb_data['rating'] = airbnb_data['rating'].apply(clean_rating)\n",
    "airbnb_data['n_reviews'] = airbnb_data['n_reviews'].apply(clean_rating)\n",
    "\n",
    "\n",
    "def clean_stats(x):\n",
    "    try:\n",
    "        if '+' in x:\n",
    "            return int(x.replace('+',''))\n",
    "        else:\n",
    "            return x\n",
    "    except:\n",
    "        return x\n",
    "    \n",
    "    \n",
    "airbnb_data['n_guests'] = airbnb_data['n_guests'].apply(clean_stats)\n",
    "airbnb_data['n_bedrooms'] = airbnb_data['n_bedrooms'].apply(clean_stats)\n",
    "airbnb_data['n_beds'] = airbnb_data['n_beds'].apply(clean_stats)\n",
    "airbnb_data['n_baths'] = airbnb_data['n_baths'].apply(clean_stats)\n",
    "    \n",
    "def studio(x):\n",
    "    try:\n",
    "        if x=='Studio':\n",
    "            return 0\n",
    "        else:\n",
    "            return x\n",
    "    except:\n",
    "        return x\n",
    "    \n",
    "airbnb_data['n_bedrooms'] = airbnb_data['n_bedrooms'].apply(studio)\n",
    "\n",
    "\n",
    "def halfbath(x):\n",
    "    try:\n",
    "        if x=='Half-bath':\n",
    "            return 0\n",
    "        else:\n",
    "            return x\n",
    "    except:\n",
    "        return x\n",
    "    \n",
    "airbnb_data['n_guests'] = airbnb_data['n_guests'].apply(halfbath)\n",
    "airbnb_data['n_bedrooms'] = airbnb_data['n_bedrooms'].apply(halfbath)\n",
    "airbnb_data['n_beds'] = airbnb_data['n_beds'].apply(halfbath)\n",
    "airbnb_data['n_baths'] = airbnb_data['n_baths'].apply(halfbath)\n",
    "\n",
    "def shared(x):\n",
    "    try:\n",
    "        if x=='Shared':\n",
    "            return 0\n",
    "        elif x=='Private':\n",
    "            return 1\n",
    "        else:\n",
    "            return x\n",
    "    except:\n",
    "        return x\n",
    "    \n",
    "airbnb_data['n_beds'] = airbnb_data['n_beds'].apply(shared)\n",
    "airbnb_data['n_baths'] = airbnb_data['n_baths'].apply(shared)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d50d8984",
   "metadata": {},
   "source": [
    "# Remove listings in areas we won't analyze.\n",
    "\n",
    "Due to the nature of how the scraping worked, there were some listings at the edges of the geographic areas we want to assess that were scrapped. This brought into data from border states (Nebraska), and border countries (Canada), that share a border with a state we scrapped.\n",
    "\n",
    "We'll drop these listings, as we don't have the relevant demographic data about the area to help assess those listings in the same way we will assess the others."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "224f9439",
   "metadata": {},
   "outputs": [],
   "source": [
    "airbnb_data = airbnb_data[~airbnb_data['country'].isin(['Canada','México'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "7619c531",
   "metadata": {},
   "outputs": [],
   "source": [
    "airbnb_data = airbnb_data[~airbnb_data['state'].isin(['Nebraska','North Dakota','Texas','South Dakota','Oklahoma'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a699943e",
   "metadata": {},
   "outputs": [],
   "source": [
    "airbnb_data.to_csv('CLEANED airbnb_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a4b15f05",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 175152 entries, 0 to 186807\n",
      "Data columns (total 65 columns):\n",
      " #   Column                        Non-Null Count   Dtype  \n",
      "---  ------                        --------------   -----  \n",
      " 0   listing_id                    175152 non-null  int64  \n",
      " 1   listing_url                   175152 non-null  object \n",
      " 2   is_superhost                  175152 non-null  int64  \n",
      " 3   is_premium                    175152 non-null  int64  \n",
      " 4   rating                        141442 non-null  object \n",
      " 5   n_reviews                     141442 non-null  float64\n",
      " 6   country                       175031 non-null  object \n",
      " 7   state                         175031 non-null  object \n",
      " 8   city                          175063 non-null  object \n",
      " 9   county                        175031 non-null  object \n",
      " 10  postcode                      175031 non-null  object \n",
      " 11  listing_title                 175152 non-null  object \n",
      " 12  n_pictures                    175152 non-null  float64\n",
      " 13  entire_home                   175152 non-null  int64  \n",
      " 14  hotel_room                    175152 non-null  int64  \n",
      " 15  private_room                  175152 non-null  int64  \n",
      " 16  shared_room                   175152 non-null  int64  \n",
      " 17  latitude                      175152 non-null  float64\n",
      " 18  longitude                     175152 non-null  float64\n",
      " 19  price                         175152 non-null  int64  \n",
      " 20  perc_discount                 175152 non-null  float64\n",
      " 21  n_guests                      100115 non-null  object \n",
      " 22  n_bedrooms                    100110 non-null  object \n",
      " 23  n_beds                        99865 non-null   object \n",
      " 24  n_baths                       97789 non-null   object \n",
      " 25  rating_cleanliness            93025 non-null   float64\n",
      " 26  rating_communication          93024 non-null   float64\n",
      " 27  rating_checkin                93024 non-null   float64\n",
      " 28  rating_accuracy               93024 non-null   float64\n",
      " 29  rating_location               93024 non-null   float64\n",
      " 30  rating_value                  93024 non-null   float64\n",
      " 31  n_amenities                   99538 non-null   float64\n",
      " 32  amenities_services            100255 non-null  float64\n",
      " 33  amenities_heat_cool           100255 non-null  float64\n",
      " 34  amenities_bedroom_laundry     100255 non-null  float64\n",
      " 35  amenities_kitchen_dining      100255 non-null  float64\n",
      " 36  amenities_safety              100255 non-null  float64\n",
      " 37  amenities_parking_facilities  100255 non-null  float64\n",
      " 38  amenities_internet_office     100255 non-null  float64\n",
      " 39  amenities_not_included        100255 non-null  float64\n",
      " 40  amenities_entertainment       100255 non-null  float64\n",
      " 41  amenities_bathroom            100255 non-null  float64\n",
      " 42  amenities_outdoor             100255 non-null  float64\n",
      " 43  amenities_location_features   100255 non-null  float64\n",
      " 44  amenities_family              100255 non-null  float64\n",
      " 45  amenities_scenic_views        100255 non-null  float64\n",
      " 46  bed_king                      89325 non-null   float64\n",
      " 47  bed_queen                     89325 non-null   float64\n",
      " 48  bed_double                    89325 non-null   float64\n",
      " 49  bed_single                    89325 non-null   float64\n",
      " 50  bed_sofa                      89325 non-null   float64\n",
      " 51  bed_air                       89325 non-null   float64\n",
      " 52  bed_floor                     89325 non-null   float64\n",
      " 53  bed_bunkbed                   89325 non-null   float64\n",
      " 54  bed_hammock                   89325 non-null   float64\n",
      " 55  bed_baby                      89325 non-null   float64\n",
      " 56  description                   99535 non-null   object \n",
      " 57  description_sentiment_score   99535 non-null   float64\n",
      " 58  description_length            99535 non-null   float64\n",
      " 59  description_reading_level     83831 non-null   float64\n",
      " 60  image_1                       175152 non-null  object \n",
      " 61  image_2                       174985 non-null  object \n",
      " 62  image_3                       174834 non-null  object \n",
      " 63  image_4                       174565 non-null  object \n",
      " 64  image_5                       174138 non-null  object \n",
      "dtypes: float64(39), int64(8), object(18)\n",
      "memory usage: 88.2+ MB\n"
     ]
    }
   ],
   "source": [
    "airbnb_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "acb52c7f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 411 entries, 0 to 410\n",
      "Data columns (total 10 columns):\n",
      " #   Column                           Non-Null Count  Dtype  \n",
      "---  ------                           --------------  -----  \n",
      " 0   county_name                      411 non-null    object \n",
      " 1   state                            411 non-null    object \n",
      " 2   FIPS                             411 non-null    int64  \n",
      " 3   rural-urban-continuum-code       411 non-null    int64  \n",
      " 4   perc-college-educated-2016-2020  411 non-null    float64\n",
      " 5   perc_unemployed_2021             411 non-null    float64\n",
      " 6   median_hh_income                 411 non-null    int64  \n",
      " 7   perc_state_median_hh_income      411 non-null    float64\n",
      " 8   perc_people_poverty_2020         411 non-null    float64\n",
      " 9   perc_children_poverty_2020       411 non-null    float64\n",
      "dtypes: float64(5), int64(3), object(2)\n",
      "memory usage: 35.3+ KB\n"
     ]
    }
   ],
   "source": [
    "county_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01954a22",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}