Destinations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The top 2018 International Destinations by Euromonitor\n",
    "\n",
    "I created this project to see what the top destinations are and any data I could find about that city.\n",
    "Such as: where they're located, population, city safety index, and country happiness index\n",
    "\n",
    "Main dataset source:\n",
    "https://go.euromonitor.com/white-paper-travel-2018-100-cities\n",
    "\n",
    "Python sources:\n",
    "https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html\n",
    "\n",
    "Project created around August, 2019 by Amelia V. Final set is shared in Tableau Public."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, re, pandas, requests, bs4, webbrowser, sys"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "STEP ONE- CREATE INITIAL SET\n",
    "I copied the table with the 100 cities from the euromonitor pdf into a text file.\n",
    "The rows are separated by new lines and columns are separated by spaces. However there are a few cities and countries that are more than one word (ie. New York City) so I manually added an underscore _ to those"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read in the text file and save the contents as a string\n",
    "file1 = open(\"initial_euromonitor_report.txt\",\"r\")\n",
    "initialset= file1.read()\n",
    "file1.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Make data into something readable as a csv\n",
    "initialset= initialset.replace(' ',';')  #make all the spaces into semicolons\n",
    "initialset= initialset.replace(',','')   #get rid of commas\n",
    "initialset= initialset.replace('_',' ')  #change _ back into a space\n",
    "#print(initialset)  #check to make sure the data looks how you want"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save the string as a csv\n",
    "file2 = open(\"formatted_euromonitor_report.csv\",\"w\")\n",
    "file2.write(initialset)\n",
    "file2.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read in the csv as a dataframe\n",
    "topcities = pandas.read_csv(\"formatted_euromonitor_report.csv\", sep=\";\")\n",
    "#topcities   #check to make sure the data looks how you want"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Check to see if it's a number data type. If not, get rid of the commas and make them floats\n",
    "topcities.dtypes\n",
    "#topcities['Arrivals 2016'] = topcities['Arrivals 2016'].str.replace(',', '').astype(float)\n",
    "#topcities['Arrivals 2017'] = topcities['Arrivals 2017'].str.replace(',', '').astype(float)\n",
    "#topcities['Arrivals 2018'] = topcities['Arrivals 2018'].str.replace(',', '').astype(float)\n",
    "#According to the report, 3 columns are not their actual numerical values and need to be multiplied by 1000. Only do this once\n",
    "topcities['Arrivals 2016']=topcities['Arrivals 2016']*1000\n",
    "topcities['Arrivals 2017']=topcities['Arrivals 2017']*1000\n",
    "topcities['Arrivals 2018']=topcities['Arrivals 2018']*1000\n",
    "#topcities   #check to make sure the data looks how you want\n",
    "#topcities.to_csv(r'topvisitedcities_transformed.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "STEP TWO- ADD COUNTRY CODES\n",
    "Country code dataset found here: https://www.kaggle.com/juanumusic/countries-iso-codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save it as a dataframe\n",
    "countrycode= pandas.read_csv(\"wikipedia-iso-country-codes.csv\")\n",
    "#remove unneeded columns and rename columns to Country and country code\n",
    "countrycode= countrycode.drop(['Alpha-3 code','Numeric code','ISO 3166-2'], axis=1)\n",
    "countrycode.rename(columns={'English short name lower case':'Country', 'Alpha-2 code':'country code'}, inplace=True) \n",
    "#countrycode   #check to make sure the data looks how you want "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Left outer join (called a merge in pandas). Adding c to dataframe name which stands for country code\n",
    "topcities_c=pandas.merge(topcities, countrycode, on=['Country'], how='left')\n",
    "#Find if any country codes are missing. This means the two countries' names didn't match up\n",
    "#topcities_c[topcities_c['country code'].isna()] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Rename the countries that are mismatched (between the two ddataframes) so that they will match\n",
    "topcities.Country = topcities.Country.replace({\"US\": \"United States Of America\",\"United States\": \"United States Of America\",\n",
    "                    \"UAE\": \"United Arab Emirates\", \"Hong Kong China\":\"Hong Kong\",\"UK\":\"United Kingdom\",\n",
    "                    \"Macau China\":\"Macao\", \"South Korea\": \"Korea, Republic of (South Korea)\"})\n",
    "#Do the merge again and then do another check to see if you missed any other countries\n",
    "topcities_c=pandas.merge(topcities, countrycode, on=['Country'], how='left')\n",
    "#topcities_c[topcities_c['country code'].isna()]\n",
    "#You can also re-rename country values if any are too long or another name is preferred\n",
    "topcities_c.Country = topcities_c.Country.replace({\"United States Of America\": \"United States\",\n",
    "                            \"Macao\":\"Macau\", \"Korea, Republic of (South Korea)\": \"South Korea\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "STEP 3- ADD CITY POPULATIONS\n",
    "Population dataset found here: https://www.kaggle.com/i2i2i2/cities-of-the-world"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save it as a dataframe\n",
    "population=pandas.read_csv('cities15000.csv',encoding = \"ISO-8859-1\")\n",
    "#Get rid of columns we don't need\n",
    "population= population.drop(['asciiname','geonameid','feature class','feature code','dem','elevation',\n",
    "        'cc2','admin1 code', 'admin2 code','admin3 code','admin4 code','modification date'], axis=1)\n",
    "#Rename \"name\" to \"City\" \n",
    "population.rename(columns={'name':'City'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Arrivals 2016</th>\n",
       "      <th>Arrivals 2017</th>\n",
       "      <th>Arrivals 2018</th>\n",
       "      <th>Growth 2017</th>\n",
       "      <th>Growth 2018</th>\n",
       "      <th>Rank 2012-17</th>\n",
       "      <th>Rank 2017-18</th>\n",
       "      <th>country code</th>\n",
       "      <th>alternatenames</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>population</th>\n",
       "      <th>timezone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>59</td>\n",
       "      <td>St. Petersburg</td>\n",
       "      <td>Russia</td>\n",
       "      <td>2840000.0</td>\n",
       "      <td>3600000.0</td>\n",
       "      <td>3996000.0</td>\n",
       "      <td>26.8%</td>\n",
       "      <td>11.0%</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>RU</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>82</th>\n",
       "      <td>83</td>\n",
       "      <td>Frankfurt</td>\n",
       "      <td>Germany</td>\n",
       "      <td>2406200.0</td>\n",
       "      <td>2496000.0</td>\n",
       "      <td>2582100.0</td>\n",
       "      <td>3.7%</td>\n",
       "      <td>3.4%</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>DE</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>96</th>\n",
       "      <td>97</td>\n",
       "      <td>Batam</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>1638600.0</td>\n",
       "      <td>2227500.0</td>\n",
       "      <td>2492600.0</td>\n",
       "      <td>35.9%</td>\n",
       "      <td>11.9%</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>ID</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Rank            City    Country  Arrivals 2016  Arrivals 2017  \\\n",
       "58    59  St. Petersburg     Russia      2840000.0      3600000.0   \n",
       "82    83       Frankfurt    Germany      2406200.0      2496000.0   \n",
       "96    97           Batam  Indonesia      1638600.0      2227500.0   \n",
       "\n",
       "    Arrivals 2018 Growth 2017 Growth 2018  Rank 2012-17  Rank 2017-18  \\\n",
       "58      3996000.0       26.8%       11.0%             9             1   \n",
       "82      2582100.0        3.7%        3.4%             3             5   \n",
       "96      2492600.0       35.9%       11.9%            31             6   \n",
       "\n",
       "   country code alternatenames latitude longitude population timezone  \n",
       "58           RU            NaN      NaN       NaN        NaN      NaN  \n",
       "82           DE            NaN      NaN       NaN        NaN      NaN  \n",
       "96           ID            NaN      NaN       NaN        NaN      NaN  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Merge dataframes\n",
    "topcities_cp=pandas.merge(topcities_c, population, how='left', left_on=['City', 'country code'], right_on=['City', 'country code'])\n",
    "#Check for null values in population column. This happens when the city and country names didn't match up\n",
    "topcities_cp[topcities_cp['population'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>alternatenames</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>country code</th>\n",
       "      <th>population</th>\n",
       "      <th>timezone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13121</th>\n",
       "      <td>Batam</td>\n",
       "      <td>BBM,Bat Dambang,Batambang,Batambangas,Batdamba...</td>\n",
       "      <td>13.10271</td>\n",
       "      <td>103.19822</td>\n",
       "      <td>KH</td>\n",
       "      <td>150444</td>\n",
       "      <td>Asia/Phnom_Penh</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        City                                     alternatenames  latitude  \\\n",
       "13121  Batam  BBM,Bat Dambang,Batambang,Batambangas,Batdamba...  13.10271   \n",
       "\n",
       "       longitude country code population         timezone  \n",
       "13121  103.19822           KH     150444  Asia/Phnom_Penh  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Find those cities in the population dataset by looking in the alternatenames column\n",
    "#See what the population dataset's main name for that city is and make note of it\n",
    "population[population.alternatenames.str.contains('Frankfurt',na=False)]   #Frankfurt am Main\n",
    "population[population.alternatenames.str.contains('St. Petersburg',na=False)]  #Saint Petersburg\n",
    "population[population.alternatenames.str.contains('Batam',na=False)]  \n",
    "#No city in Indonesia (only Cambodia) with that name so I will need to look for it manually"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Change the names for those cities that were found\n",
    "topcities_c.City = topcities_c.City.replace({\"Frankfurt\": \"Frankfurt am Main\",\n",
    "                                                         \"St. Petersburg\":\"Saint Petersburg\"})\n",
    "#Do the merge again and check for nulls again. Adding p to dataframe name for population\n",
    "topcities_cp=pandas.merge(topcities_c, population, how='left', left_on=['City', 'country code'], right_on=['City', 'country code'])\n",
    "topcities_cp[topcities_cp['population'].isna()]\n",
    "#change back any cities names that the name was preferred\n",
    "topcities_cp.City = topcities_cp.City.replace({\"Frankfurt am Main\":\"Frankfurt\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Rank</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Arrivals 2016</th>\n",
       "      <th>Arrivals 2017</th>\n",
       "      <th>Arrivals 2018</th>\n",
       "      <th>Growth 2017</th>\n",
       "      <th>Growth 2018</th>\n",
       "      <th>Rank 2012-17</th>\n",
       "      <th>Rank 2017-18</th>\n",
       "      <th>country code</th>\n",
       "      <th>alternatenames</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>population</th>\n",
       "      <th>timezone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>91</td>\n",
       "      <td>Lima</td>\n",
       "      <td>Peru</td>\n",
       "      <td>2151000.0</td>\n",
       "      <td>2348500.0</td>\n",
       "      <td>2583700.0</td>\n",
       "      <td>9.2%</td>\n",
       "      <td>10.0%</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>PE</td>\n",
       "      <td>Gorad Lima,LIM,Lim,Lima,Limae,Limaq,Lime,Limma...</td>\n",
       "      <td>-12.04318</td>\n",
       "      <td>-77.02824</td>\n",
       "      <td>7737002</td>\n",
       "      <td>America/Lima</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>92</td>\n",
       "      <td>Nice</td>\n",
       "      <td>France</td>\n",
       "      <td>2164800.0</td>\n",
       "      <td>2292500.0</td>\n",
       "      <td>2331500.0</td>\n",
       "      <td>5.9%</td>\n",
       "      <td>1.7%</td>\n",
       "      <td>16</td>\n",
       "      <td>7</td>\n",
       "      <td>FR</td>\n",
       "      <td>Gorad Nica,NCE,Nica,Nicaea,Nicc,Nicca,Niccae,N...</td>\n",
       "      <td>43.70313</td>\n",
       "      <td>7.26608</td>\n",
       "      <td>338620</td>\n",
       "      <td>Europe/Paris</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>93</td>\n",
       "      <td>Santiago</td>\n",
       "      <td>Chile</td>\n",
       "      <td>1933000.0</td>\n",
       "      <td>2275600.0</td>\n",
       "      <td>2648100.0</td>\n",
       "      <td>17.7%</td>\n",
       "      <td>16.4%</td>\n",
       "      <td>17</td>\n",
       "      <td>9</td>\n",
       "      <td>CL</td>\n",
       "      <td>Ciles Santjagas,CiudadSantiago,SCL,Sanctiacobi...</td>\n",
       "      <td>-33.45694</td>\n",
       "      <td>-70.64827</td>\n",
       "      <td>4837295</td>\n",
       "      <td>America/Santiago</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>94</td>\n",
       "      <td>Rio de Janeiro</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>2309000.0</td>\n",
       "      <td>2251300.0</td>\n",
       "      <td>2305300.0</td>\n",
       "      <td>-2.5%</td>\n",
       "      <td>2.4%</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>BR</td>\n",
       "      <td>Ciutat de Rio de Janeiro,Gorad Rya-deh-Zhanehj...</td>\n",
       "      <td>-22.90278</td>\n",
       "      <td>-43.2075</td>\n",
       "      <td>6023699</td>\n",
       "      <td>America/Sao_Paulo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>95</td>\n",
       "      <td>Abu Dhabi</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>2054900.0</td>\n",
       "      <td>2244500.0</td>\n",
       "      <td>2402800.0</td>\n",
       "      <td>9.2%</td>\n",
       "      <td>7.1%</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>AE</td>\n",
       "      <td>A-pu-that-pi,AEbu Saby,AUH,Aboe Dhabi,Abou Dab...</td>\n",
       "      <td>24.46667</td>\n",
       "      <td>54.36667</td>\n",
       "      <td>603492</td>\n",
       "      <td>Asia/Dubai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>Porto</td>\n",
       "      <td>Portugal</td>\n",
       "      <td>1969300.0</td>\n",
       "      <td>2232500.0</td>\n",
       "      <td>2391500.0</td>\n",
       "      <td>13.4%</td>\n",
       "      <td>7.1%</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>PT</td>\n",
       "      <td>OPO,Oporto,Porto,Portu,Portus Cale,bo tu,bwrtw...</td>\n",
       "      <td>41.14961</td>\n",
       "      <td>-8.61099</td>\n",
       "      <td>249633</td>\n",
       "      <td>Europe/Lisbon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>Batam</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>1638600.0</td>\n",
       "      <td>2227500.0</td>\n",
       "      <td>2492600.0</td>\n",
       "      <td>35.9%</td>\n",
       "      <td>11.9%</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>ID</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1301</td>\n",
       "      <td>104.0529</td>\n",
       "      <td>1236399</td>\n",
       "      <td>Asia/Jakarta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>Doha</td>\n",
       "      <td>Qatar</td>\n",
       "      <td>2849000.0</td>\n",
       "      <td>2200000.0</td>\n",
       "      <td>2210000.0</td>\n",
       "      <td>-22.8%</td>\n",
       "      <td>0.5%</td>\n",
       "      <td>31</td>\n",
       "      <td>10</td>\n",
       "      <td>QA</td>\n",
       "      <td>Ad Dawha,Ad Dawhah,Ad Dawü©a,Ad Dawü©ah,Ad D...</td>\n",
       "      <td>25.28545</td>\n",
       "      <td>51.53096</td>\n",
       "      <td>344939</td>\n",
       "      <td>Asia/Qatar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>Rhodes</td>\n",
       "      <td>Greece</td>\n",
       "      <td>2091100.0</td>\n",
       "      <td>2178700.0</td>\n",
       "      <td>2281100.0</td>\n",
       "      <td>4.2%</td>\n",
       "      <td>4.7%</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>GR</td>\n",
       "      <td>RHO,Rhodes,Rhodes Town,Rhodos,Rodas,Rodes,Rodh...</td>\n",
       "      <td>36.43556</td>\n",
       "      <td>28.22199</td>\n",
       "      <td>56128</td>\n",
       "      <td>Europe/Athens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>Krabi</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>2021700.0</td>\n",
       "      <td>2163200.0</td>\n",
       "      <td>2362100.0</td>\n",
       "      <td>7.0%</td>\n",
       "      <td>9.2%</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>TH</td>\n",
       "      <td>Amphoe Muang Krabi,Amphoe Mueang Krabi,Amphoe ...</td>\n",
       "      <td>8.07257</td>\n",
       "      <td>98.91052</td>\n",
       "      <td>31219</td>\n",
       "      <td>Asia/Bangkok</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Rank            City               Country  Arrivals 2016  Arrivals 2017  \\\n",
       "90    91            Lima                  Peru      2151000.0      2348500.0   \n",
       "91    92            Nice                France      2164800.0      2292500.0   \n",
       "92    93        Santiago                 Chile      1933000.0      2275600.0   \n",
       "93    94  Rio de Janeiro                Brazil      2309000.0      2251300.0   \n",
       "94    95       Abu Dhabi  United Arab Emirates      2054900.0      2244500.0   \n",
       "95    96           Porto              Portugal      1969300.0      2232500.0   \n",
       "96    97           Batam             Indonesia      1638600.0      2227500.0   \n",
       "97    98            Doha                 Qatar      2849000.0      2200000.0   \n",
       "98    99          Rhodes                Greece      2091100.0      2178700.0   \n",
       "99   100           Krabi              Thailand      2021700.0      2163200.0   \n",
       "\n",
       "    Arrivals 2018 Growth 2017 Growth 2018  Rank 2012-17  Rank 2017-18  \\\n",
       "90      2583700.0        9.2%       10.0%             2             4   \n",
       "91      2331500.0        5.9%        1.7%            16             7   \n",
       "92      2648100.0       17.7%       16.4%            17             9   \n",
       "93      2305300.0       -2.5%        2.4%             4             7   \n",
       "94      2402800.0        9.2%        7.1%            42             1   \n",
       "95      2391500.0       13.4%        7.1%            42             1   \n",
       "96      2492600.0       35.9%       11.9%            31             6   \n",
       "97      2210000.0      -22.8%        0.5%            31            10   \n",
       "98      2281100.0        4.2%        4.7%             1             3   \n",
       "99      2362100.0        7.0%        9.2%             9             2   \n",
       "\n",
       "   country code                                     alternatenames   latitude  \\\n",
       "90           PE  Gorad Lima,LIM,Lim,Lima,Limae,Limaq,Lime,Limma...  -12.04318   \n",
       "91           FR  Gorad Nica,NCE,Nica,Nicaea,Nicc,Nicca,Niccae,N...   43.70313   \n",
       "92           CL  Ciles Santjagas,CiudadSantiago,SCL,Sanctiacobi...  -33.45694   \n",
       "93           BR  Ciutat de Rio de Janeiro,Gorad Rya-deh-Zhanehj...  -22.90278   \n",
       "94           AE  A-pu-that-pi,AEbu Saby,AUH,Aboe Dhabi,Abou Dab...   24.46667   \n",
       "95           PT  OPO,Oporto,Porto,Portu,Portus Cale,bo tu,bwrtw...   41.14961   \n",
       "96           ID                                                NaN     1.1301   \n",
       "97           QA  Ad Dawha,Ad Dawhah,Ad Dawü©a,Ad Dawü©ah,Ad D...   25.28545   \n",
       "98           GR  RHO,Rhodes,Rhodes Town,Rhodos,Rodas,Rodes,Rodh...   36.43556   \n",
       "99           TH  Amphoe Muang Krabi,Amphoe Mueang Krabi,Amphoe ...    8.07257   \n",
       "\n",
       "    longitude population           timezone  \n",
       "90  -77.02824    7737002       America/Lima  \n",
       "91    7.26608     338620       Europe/Paris  \n",
       "92  -70.64827    4837295   America/Santiago  \n",
       "93   -43.2075    6023699  America/Sao_Paulo  \n",
       "94   54.36667     603492         Asia/Dubai  \n",
       "95   -8.61099     249633      Europe/Lisbon  \n",
       "96   104.0529    1236399       Asia/Jakarta  \n",
       "97   51.53096     344939         Asia/Qatar  \n",
       "98   28.22199      56128      Europe/Athens  \n",
       "99   98.91052      31219       Asia/Bangkok  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Look for the missing Indonesian city\n",
    "#If after looking it's still not there, look up the values and manually add them\n",
    "#indonesiancities=population[population['country code'] == 'ID'] #created to only look at Indonesian cities\n",
    "#indonesiancities.population= indonesiancities.population.astype(float) #change population to float\n",
    "#indonesiancities.sort_values(by=['population'], ascending=False)  #sort by largest to smallest population\n",
    "topcities_cp.at[96, 'population'] = 1236399\n",
    "topcities_cp.at[96, 'latitude'] = \"1.1301\"\n",
    "topcities_cp.at[96, 'longitude'] = \"104.0529\"\n",
    "topcities_cp.at[96, 'timezone'] = \"Asia/Jakarta\"\n",
    "topcities_cp.tail(10)   #check that it is there now"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "STEP 4- ADD CITY SAFETY INDEX\n",
    "https://ceoworld.biz/2019/08/01/the-worlds-safest-cities-ranking-2019/  \n",
    "I will add this list to ask the question of what cities people are willing to travel to despite being unsafe\n",
    "It was difficult to find a dataset with a lot of cities. This one has around 75% of the cities. Data is from 2019 (assumably from 2018)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#safety of cities\n",
    "safetyindex= pandas.read_csv(\"safetyofcities.csv\", sep=\";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#adding s to dataframe name for safety index\n",
    "topcities_cps=pandas.merge(topcities_cp, safetyindex, how='left', left_on=['City', 'Country'], right_on=['City', 'Country'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "topcities_cps[topcities_cps['Safety Index'].isna()]\n",
    "topcities_cp.City = topcities_cp.City.replace({\"Ha Noi\": \"Hanoi\",\"Penang City\":\"Penang\",\n",
    "                    \"Kolkatta\":\"Kolkata\",\"Buenos Aires City\": \"Buenos Aires\"})\n",
    "safetyindex.City=safetyindex.City.replace({\"Krakow (Cracow)\":\"Krakow\",\"Tel Aviv-Yafo\":\"Tel Aviv\",\n",
    "                                          \"Ad Dammam\":\"Dammam City\"})\n",
    "topcities_cps=pandas.merge(topcities_cp, safetyindex, how='left', left_on=['City', 'Country'], right_on=['City', 'Country'])\n",
    "#topcities_cps[topcities_cps['Safety Index'].isna()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "STEP 5- ADD COUNTRY HUMAN DEVELOPMENT INDEX\n",
    "http://hdr.undp.org/sites/default/files/2018_human_development_statistical_update.pdf\n",
    "https://en.wikipedia.org/wiki/List_of_countries_by_Human_Development_Index\n",
    "Above are figures for 2017\n",
    "https://www.ceicdata.com/en/indicator/macau/gdp-per-capita\n",
    "https://www.bti-project.org/fileadmin/files/BTI/Downloads/Reports/2018/pdf/BTI_2018_Taiwan.pdf\n",
    "https://www.bti-project.org/en/reports/country-reports/detail/itc/TWN/\n",
    "\n",
    "In this dataset there were citations so I searched for a letter in between spaces \" \\w \" and replaced with \"\"\n",
    "There was one\" \\w,\\w \" as well"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "HDI=pandas.read_csv('HDI_country_rank.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get rid of columns we don't need\n",
    "HDI.dtypes\n",
    "HDI= HDI.drop(['HDI rank','GNI per capita rank minus HDI rank','HDI rank.1'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Rank</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Arrivals 2016</th>\n",
       "      <th>Arrivals 2017</th>\n",
       "      <th>Arrivals 2018</th>\n",
       "      <th>Growth 2017</th>\n",
       "      <th>Growth 2018</th>\n",
       "      <th>Rank 2012-17</th>\n",
       "      <th>Rank 2017-18</th>\n",
       "      <th>...</th>\n",
       "      <th>longitude</th>\n",
       "      <th>population</th>\n",
       "      <th>timezone</th>\n",
       "      <th>Safety Rank</th>\n",
       "      <th>Safety Index</th>\n",
       "      <th>Human Development Index</th>\n",
       "      <th>Life expectancy</th>\n",
       "      <th>Expected years of schooling</th>\n",
       "      <th>Mean years of schooling</th>\n",
       "      <th>Gross national income per capita</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Rank, City, Country, Arrivals 2016, Arrivals 2017, Arrivals 2018, Growth 2017, Growth 2018, Rank 2012-17, Rank 2017-18, country code, alternatenames, latitude, longitude, population, timezone, Safety Rank, Safety Index, Human Development Index, Life expectancy, Expected years of schooling, Mean years of schooling, Gross national income per capita]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 23 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merge with the main dataframe. Adding h to dataframe name for HDI\n",
    "topcities_cpsh=pandas.merge(topcities_cps, HDI, how='left', on=['Country'])\n",
    "topcities_cpsh[topcities_cpsh['Human Development Index'].isna()]\n",
    "#Rename Country Czechia to Czech Republic\n",
    "HDI.Country = HDI.Country.replace({\"Czechia\": \"Czech Republic\"})\n",
    "#Merge and check again\n",
    "topcities_cpsh=pandas.merge(topcities_cps, HDI, how='left', on=['Country'])\n",
    "topcities_cpsh[topcities_cpsh['Human Development Index'].isna()]\n",
    "#topcities_cpsh #manual check"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "STEP 5- GET IMAGES\n",
    "images are from gettyimages.com"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get the list of cities in a list\n",
    "citylist= topcities_cpsh[\"City\"].tolist()\n",
    "#citylist  #check to make sure the variable saved all the cities to a list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "#loops thru all cities and add each to a getty images url to do a getty image search\n",
    "#on that page, you use the css selector to return the html of the first image from the search\n",
    "#you search for src from the html and save the urls into a new list\n",
    "\n",
    "#old cssSelector (new cssSelector was made 10/21/2020)\n",
    "#cssSelector= '''body > div.content_wrapper > section > div > main > section > div.search-content__gallery-pager-wrapper > div > div.search-content__gallery > div > div.search-content__gallery-assets > gix-asset:nth-child(1) > article > a > figure > img'''\n",
    "cssSelector='''body > div.content_wrapper > section > div > main > section > div.search-content__gallery-pager-wrapper > div > div.search-content__gallery > div > div.search-content__gallery-assets > gi-asset > article > div > meta'''\n",
    "imageurls= []\n",
    "\n",
    "for i in range(len(citylist)):\n",
    "    gettyurl='https://www.gettyimages.com/photos/'+citylist[i].replace(\" \", \"-\")\n",
    "    g= requests.get(gettyurl)\n",
    "    p= bs4.BeautifulSoup(g.text, 'html.parser')\n",
    "    n = p.select(cssSelector)\n",
    "    f=re.compile(r'''\"http(.+)\" ''')\n",
    "    a_string= \"http\"+f.findall(str(n))[0]\n",
    "    split_string = a_string.split(\" \", 1)\n",
    "    imageurls.append(split_string[0])\n",
    "    #f=re.compile(r'''src=\"(.+)\"''')\n",
    "    #imageurls.append(f.findall(str(n[0]))[0])\n",
    "\n",
    "#gettyurls\n",
    "#imageurls  #check that there is a list of image urls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "#adding i for image and then adding each value to the column \"City images\" \n",
    "topcities_cpshi = topcities_cpsh\n",
    "topcities_cpshi['City images']= imageurls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>Rank</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Arrivals 2016</th>\n",
       "      <th>Arrivals 2017</th>\n",
       "      <th>Arrivals 2018</th>\n",
       "      <th>Growth 2017</th>\n",
       "      <th>Growth 2018</th>\n",
       "      <th>Rank 2012-17</th>\n",
       "      <th>Rank 2017-18</th>\n",
       "      <th>...</th>\n",
       "      <th>population</th>\n",
       "      <th>timezone</th>\n",
       "      <th>Safety Rank</th>\n",
       "      <th>Safety Index</th>\n",
       "      <th>Human Development Index</th>\n",
       "      <th>Life expectancy</th>\n",
       "      <th>Expected years of schooling</th>\n",
       "      <th>Mean years of schooling</th>\n",
       "      <th>Gross national income per capita</th>\n",
       "      <th>City images</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>26552700.0</td>\n",
       "      <td>27880300.0</td>\n",
       "      <td>29827200.0</td>\n",
       "      <td>5.0%</td>\n",
       "      <td>7.0%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7012738</td>\n",
       "      <td>Asia/Hong_Kong</td>\n",
       "      <td>10.0</td>\n",
       "      <td>82.00</td>\n",
       "      <td>0.933</td>\n",
       "      <td>84.10</td>\n",
       "      <td>16.3</td>\n",
       "      <td>12.0</td>\n",
       "      <td>58420</td>\n",
       "      <td>https://media.gettyimages.com/photos/cityscape...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bangkok</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>20698300.0</td>\n",
       "      <td>22453900.0</td>\n",
       "      <td>23688800.0</td>\n",
       "      <td>8.5%</td>\n",
       "      <td>5.5%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5104476</td>\n",
       "      <td>Asia/Bangkok</td>\n",
       "      <td>155.0</td>\n",
       "      <td>58.61</td>\n",
       "      <td>0.755</td>\n",
       "      <td>75.50</td>\n",
       "      <td>14.7</td>\n",
       "      <td>7.6</td>\n",
       "      <td>15516</td>\n",
       "      <td>https://media.gettyimages.com/photos/wat-arun-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>19059500.0</td>\n",
       "      <td>19827800.0</td>\n",
       "      <td>20715900.0</td>\n",
       "      <td>4.0%</td>\n",
       "      <td>4.5%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>7556900</td>\n",
       "      <td>Europe/London</td>\n",
       "      <td>236.0</td>\n",
       "      <td>47.76</td>\n",
       "      <td>0.922</td>\n",
       "      <td>81.70</td>\n",
       "      <td>17.4</td>\n",
       "      <td>12.9</td>\n",
       "      <td>39116</td>\n",
       "      <td>https://media.gettyimages.com/photos/the-big-b...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>16604000.0</td>\n",
       "      <td>17618800.0</td>\n",
       "      <td>18551200.0</td>\n",
       "      <td>6.1%</td>\n",
       "      <td>5.3%</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>3547809</td>\n",
       "      <td>Asia/Singapore</td>\n",
       "      <td>62.0</td>\n",
       "      <td>71.64</td>\n",
       "      <td>0.932</td>\n",
       "      <td>83.20</td>\n",
       "      <td>16.2</td>\n",
       "      <td>11.5</td>\n",
       "      <td>82503</td>\n",
       "      <td>https://media.gettyimages.com/photos/gardens-b...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Macau</td>\n",
       "      <td>Macau</td>\n",
       "      <td>15704000.0</td>\n",
       "      <td>17337200.0</td>\n",
       "      <td>18931400.0</td>\n",
       "      <td>10.4%</td>\n",
       "      <td>9.2%</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>520400</td>\n",
       "      <td>Asia/Macau</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.909</td>\n",
       "      <td>83.99</td>\n",
       "      <td>12.4</td>\n",
       "      <td>12.0</td>\n",
       "      <td>77903</td>\n",
       "      <td>https://media.gettyimages.com/photos/macau-sky...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>Porto</td>\n",
       "      <td>Portugal</td>\n",
       "      <td>1969300.0</td>\n",
       "      <td>2232500.0</td>\n",
       "      <td>2391500.0</td>\n",
       "      <td>13.4%</td>\n",
       "      <td>7.1%</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>249633</td>\n",
       "      <td>Europe/Lisbon</td>\n",
       "      <td>135.0</td>\n",
       "      <td>60.75</td>\n",
       "      <td>0.847</td>\n",
       "      <td>81.40</td>\n",
       "      <td>16.3</td>\n",
       "      <td>9.2</td>\n",
       "      <td>27315</td>\n",
       "      <td>https://media.gettyimages.com/photos/porto-cit...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>Batam</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>1638600.0</td>\n",
       "      <td>2227500.0</td>\n",
       "      <td>2492600.0</td>\n",
       "      <td>35.9%</td>\n",
       "      <td>11.9%</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>1236399</td>\n",
       "      <td>Asia/Jakarta</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.694</td>\n",
       "      <td>69.40</td>\n",
       "      <td>12.8</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10846</td>\n",
       "      <td>https://media.gettyimages.com/photos/scenic-vi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>Doha</td>\n",
       "      <td>Qatar</td>\n",
       "      <td>2849000.0</td>\n",
       "      <td>2200000.0</td>\n",
       "      <td>2210000.0</td>\n",
       "      <td>-22.8%</td>\n",
       "      <td>0.5%</td>\n",
       "      <td>31</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>344939</td>\n",
       "      <td>Asia/Qatar</td>\n",
       "      <td>2.0</td>\n",
       "      <td>88.43</td>\n",
       "      <td>0.856</td>\n",
       "      <td>78.30</td>\n",
       "      <td>13.4</td>\n",
       "      <td>9.8</td>\n",
       "      <td>116818</td>\n",
       "      <td>https://media.gettyimages.com/photos/doha-skyl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>Rhodes</td>\n",
       "      <td>Greece</td>\n",
       "      <td>2091100.0</td>\n",
       "      <td>2178700.0</td>\n",
       "      <td>2281100.0</td>\n",
       "      <td>4.2%</td>\n",
       "      <td>4.7%</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>56128</td>\n",
       "      <td>Europe/Athens</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.870</td>\n",
       "      <td>81.40</td>\n",
       "      <td>17.3</td>\n",
       "      <td>10.8</td>\n",
       "      <td>24648</td>\n",
       "      <td>https://media.gettyimages.com/photos/rhodes-la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>Krabi</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>2021700.0</td>\n",
       "      <td>2163200.0</td>\n",
       "      <td>2362100.0</td>\n",
       "      <td>7.0%</td>\n",
       "      <td>9.2%</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>31219</td>\n",
       "      <td>Asia/Bangkok</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.755</td>\n",
       "      <td>75.50</td>\n",
       "      <td>14.7</td>\n",
       "      <td>7.6</td>\n",
       "      <td>15516</td>\n",
       "      <td>https://media.gettyimages.com/photos/beautiful...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Rank       City         Country  Arrivals 2016  Arrivals 2017  \\\n",
       "0      1  Hong Kong       Hong Kong     26552700.0     27880300.0   \n",
       "1      2    Bangkok        Thailand     20698300.0     22453900.0   \n",
       "2      3     London  United Kingdom     19059500.0     19827800.0   \n",
       "3      4  Singapore       Singapore     16604000.0     17618800.0   \n",
       "4      5      Macau           Macau     15704000.0     17337200.0   \n",
       "..   ...        ...             ...            ...            ...   \n",
       "95    96      Porto        Portugal      1969300.0      2232500.0   \n",
       "96    97      Batam       Indonesia      1638600.0      2227500.0   \n",
       "97    98       Doha           Qatar      2849000.0      2200000.0   \n",
       "98    99     Rhodes          Greece      2091100.0      2178700.0   \n",
       "99   100      Krabi        Thailand      2021700.0      2163200.0   \n",
       "\n",
       "    Arrivals 2018 Growth 2017 Growth 2018  Rank 2012-17  Rank 2017-18  ...  \\\n",
       "0      29827200.0        5.0%        7.0%             0             0  ...   \n",
       "1      23688800.0        8.5%        5.5%             0             0  ...   \n",
       "2      20715900.0        4.0%        4.5%             0             0  ...   \n",
       "3      18551200.0        6.1%        5.3%             0             1  ...   \n",
       "4      18931400.0       10.4%        9.2%             1             1  ...   \n",
       "..            ...         ...         ...           ...           ...  ...   \n",
       "95      2391500.0       13.4%        7.1%            42             1  ...   \n",
       "96      2492600.0       35.9%       11.9%            31             6  ...   \n",
       "97      2210000.0      -22.8%        0.5%            31            10  ...   \n",
       "98      2281100.0        4.2%        4.7%             1             3  ...   \n",
       "99      2362100.0        7.0%        9.2%             9             2  ...   \n",
       "\n",
       "   population        timezone Safety Rank Safety Index  \\\n",
       "0     7012738  Asia/Hong_Kong        10.0        82.00   \n",
       "1     5104476    Asia/Bangkok       155.0        58.61   \n",
       "2     7556900   Europe/London       236.0        47.76   \n",
       "3     3547809  Asia/Singapore        62.0        71.64   \n",
       "4      520400      Asia/Macau         NaN          NaN   \n",
       "..        ...             ...         ...          ...   \n",
       "95     249633   Europe/Lisbon       135.0        60.75   \n",
       "96    1236399    Asia/Jakarta         NaN          NaN   \n",
       "97     344939      Asia/Qatar         2.0        88.43   \n",
       "98      56128   Europe/Athens         NaN          NaN   \n",
       "99      31219    Asia/Bangkok         NaN          NaN   \n",
       "\n",
       "   Human Development Index Life expectancy  Expected years of schooling  \\\n",
       "0                    0.933           84.10                         16.3   \n",
       "1                    0.755           75.50                         14.7   \n",
       "2                    0.922           81.70                         17.4   \n",
       "3                    0.932           83.20                         16.2   \n",
       "4                    0.909           83.99                         12.4   \n",
       "..                     ...             ...                          ...   \n",
       "95                   0.847           81.40                         16.3   \n",
       "96                   0.694           69.40                         12.8   \n",
       "97                   0.856           78.30                         13.4   \n",
       "98                   0.870           81.40                         17.3   \n",
       "99                   0.755           75.50                         14.7   \n",
       "\n",
       "    Mean years of schooling  Gross national income per capita  \\\n",
       "0                      12.0                             58420   \n",
       "1                       7.6                             15516   \n",
       "2                      12.9                             39116   \n",
       "3                      11.5                             82503   \n",
       "4                      12.0                             77903   \n",
       "..                      ...                               ...   \n",
       "95                      9.2                             27315   \n",
       "96                      8.0                             10846   \n",
       "97                      9.8                            116818   \n",
       "98                     10.8                             24648   \n",
       "99                      7.6                             15516   \n",
       "\n",
       "                                          City images  \n",
       "0   https://media.gettyimages.com/photos/cityscape...  \n",
       "1   https://media.gettyimages.com/photos/wat-arun-...  \n",
       "2   https://media.gettyimages.com/photos/the-big-b...  \n",
       "3   https://media.gettyimages.com/photos/gardens-b...  \n",
       "4   https://media.gettyimages.com/photos/macau-sky...  \n",
       "..                                                ...  \n",
       "95  https://media.gettyimages.com/photos/porto-cit...  \n",
       "96  https://media.gettyimages.com/photos/scenic-vi...  \n",
       "97  https://media.gettyimages.com/photos/doha-skyl...  \n",
       "98  https://media.gettyimages.com/photos/rhodes-la...  \n",
       "99  https://media.gettyimages.com/photos/beautiful...  \n",
       "\n",
       "[100 rows x 24 columns]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "topcities_cpshi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Images of cities were retrieved except for Nice (France) due to name being not unique. \n",
    "#Next, create another image url column to get backup image URLS\n",
    "#Concatenate the city and country into one list then run it again\n",
    "countrylist= topcities_cpshi[\"Country\"].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2nd imageurl column- backup for more images\n",
    "imageurls2=[]\n",
    "#list(zip(citylist,countrylist))\n",
    "for i in range(len(citylist)):\n",
    "    #print((citylist[i]+'-'+countrylist[i]).replace(\" \", \"-\"))\n",
    "    gettyurl='https://www.gettyimages.com/photos/'+((citylist[i]+'-'+countrylist[i]).replace(\" \", \"-\"))\n",
    "    g= requests.get(gettyurl)\n",
    "    p= bs4.BeautifulSoup(g.text, 'html.parser')\n",
    "    n=p.select(cssSelector)\n",
    "    f=re.compile(r'''\"http(.+)\" ''')\n",
    "    a_string= \"http\"+f.findall(str(n))[0]\n",
    "    split_string = a_string.split(\" \", 1)\n",
    "    imageurls2.append(split_string[0])\n",
    "    #f=re.compile(r'''src=\"(.+)\"''')\n",
    "    #imageurls2.append(f.findall(str(n[0]))[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>Rank</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Arrivals 2016</th>\n",
       "      <th>Arrivals 2017</th>\n",
       "      <th>Arrivals 2018</th>\n",
       "      <th>Growth 2017</th>\n",
       "      <th>Growth 2018</th>\n",
       "      <th>Rank 2012-17</th>\n",
       "      <th>Rank 2017-18</th>\n",
       "      <th>...</th>\n",
       "      <th>timezone</th>\n",
       "      <th>Safety Rank</th>\n",
       "      <th>Safety Index</th>\n",
       "      <th>Human Development Index</th>\n",
       "      <th>Life expectancy</th>\n",
       "      <th>Expected years of schooling</th>\n",
       "      <th>Mean years of schooling</th>\n",
       "      <th>Gross national income per capita</th>\n",
       "      <th>City images</th>\n",
       "      <th>City images2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>26552700.0</td>\n",
       "      <td>27880300.0</td>\n",
       "      <td>29827200.0</td>\n",
       "      <td>5.0%</td>\n",
       "      <td>7.0%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Asia/Hong_Kong</td>\n",
       "      <td>10.0</td>\n",
       "      <td>82.00</td>\n",
       "      <td>0.933</td>\n",
       "      <td>84.10</td>\n",
       "      <td>16.3</td>\n",
       "      <td>12.0</td>\n",
       "      <td>58420</td>\n",
       "      <td>https://media.gettyimages.com/photos/cityscape...</td>\n",
       "      <td>https://media.gettyimages.com/photos/cityscape...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bangkok</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>20698300.0</td>\n",
       "      <td>22453900.0</td>\n",
       "      <td>23688800.0</td>\n",
       "      <td>8.5%</td>\n",
       "      <td>5.5%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Asia/Bangkok</td>\n",
       "      <td>155.0</td>\n",
       "      <td>58.61</td>\n",
       "      <td>0.755</td>\n",
       "      <td>75.50</td>\n",
       "      <td>14.7</td>\n",
       "      <td>7.6</td>\n",
       "      <td>15516</td>\n",
       "      <td>https://media.gettyimages.com/photos/wat-arun-...</td>\n",
       "      <td>https://media.gettyimages.com/photos/wat-arun-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>19059500.0</td>\n",
       "      <td>19827800.0</td>\n",
       "      <td>20715900.0</td>\n",
       "      <td>4.0%</td>\n",
       "      <td>4.5%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Europe/London</td>\n",
       "      <td>236.0</td>\n",
       "      <td>47.76</td>\n",
       "      <td>0.922</td>\n",
       "      <td>81.70</td>\n",
       "      <td>17.4</td>\n",
       "      <td>12.9</td>\n",
       "      <td>39116</td>\n",
       "      <td>https://media.gettyimages.com/photos/the-big-b...</td>\n",
       "      <td>https://media.gettyimages.com/photos/london-br...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>16604000.0</td>\n",
       "      <td>17618800.0</td>\n",
       "      <td>18551200.0</td>\n",
       "      <td>6.1%</td>\n",
       "      <td>5.3%</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Asia/Singapore</td>\n",
       "      <td>62.0</td>\n",
       "      <td>71.64</td>\n",
       "      <td>0.932</td>\n",
       "      <td>83.20</td>\n",
       "      <td>16.2</td>\n",
       "      <td>11.5</td>\n",
       "      <td>82503</td>\n",
       "      <td>https://media.gettyimages.com/photos/gardens-b...</td>\n",
       "      <td>https://media.gettyimages.com/photos/gardens-b...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Macau</td>\n",
       "      <td>Macau</td>\n",
       "      <td>15704000.0</td>\n",
       "      <td>17337200.0</td>\n",
       "      <td>18931400.0</td>\n",
       "      <td>10.4%</td>\n",
       "      <td>9.2%</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Asia/Macau</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.909</td>\n",
       "      <td>83.99</td>\n",
       "      <td>12.4</td>\n",
       "      <td>12.0</td>\n",
       "      <td>77903</td>\n",
       "      <td>https://media.gettyimages.com/photos/macau-sky...</td>\n",
       "      <td>https://media.gettyimages.com/photos/macau-sky...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>96</td>\n",
       "      <td>Porto</td>\n",
       "      <td>Portugal</td>\n",
       "      <td>1969300.0</td>\n",
       "      <td>2232500.0</td>\n",
       "      <td>2391500.0</td>\n",
       "      <td>13.4%</td>\n",
       "      <td>7.1%</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>Europe/Lisbon</td>\n",
       "      <td>135.0</td>\n",
       "      <td>60.75</td>\n",
       "      <td>0.847</td>\n",
       "      <td>81.40</td>\n",
       "      <td>16.3</td>\n",
       "      <td>9.2</td>\n",
       "      <td>27315</td>\n",
       "      <td>https://media.gettyimages.com/photos/porto-cit...</td>\n",
       "      <td>https://media.gettyimages.com/photos/porto-cit...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>97</td>\n",
       "      <td>Batam</td>\n",
       "      <td>Indonesia</td>\n",
       "      <td>1638600.0</td>\n",
       "      <td>2227500.0</td>\n",
       "      <td>2492600.0</td>\n",
       "      <td>35.9%</td>\n",
       "      <td>11.9%</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>Asia/Jakarta</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.694</td>\n",
       "      <td>69.40</td>\n",
       "      <td>12.8</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10846</td>\n",
       "      <td>https://media.gettyimages.com/photos/scenic-vi...</td>\n",
       "      <td>https://media.gettyimages.com/photos/scenic-vi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>98</td>\n",
       "      <td>Doha</td>\n",
       "      <td>Qatar</td>\n",
       "      <td>2849000.0</td>\n",
       "      <td>2200000.0</td>\n",
       "      <td>2210000.0</td>\n",
       "      <td>-22.8%</td>\n",
       "      <td>0.5%</td>\n",
       "      <td>31</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>Asia/Qatar</td>\n",
       "      <td>2.0</td>\n",
       "      <td>88.43</td>\n",
       "      <td>0.856</td>\n",
       "      <td>78.30</td>\n",
       "      <td>13.4</td>\n",
       "      <td>9.8</td>\n",
       "      <td>116818</td>\n",
       "      <td>https://media.gettyimages.com/photos/doha-skyl...</td>\n",
       "      <td>https://media.gettyimages.com/photos/doha-skyl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>99</td>\n",
       "      <td>Rhodes</td>\n",
       "      <td>Greece</td>\n",
       "      <td>2091100.0</td>\n",
       "      <td>2178700.0</td>\n",
       "      <td>2281100.0</td>\n",
       "      <td>4.2%</td>\n",
       "      <td>4.7%</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>Europe/Athens</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.870</td>\n",
       "      <td>81.40</td>\n",
       "      <td>17.3</td>\n",
       "      <td>10.8</td>\n",
       "      <td>24648</td>\n",
       "      <td>https://media.gettyimages.com/photos/rhodes-la...</td>\n",
       "      <td>https://media.gettyimages.com/photos/beautiful...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>100</td>\n",
       "      <td>Krabi</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>2021700.0</td>\n",
       "      <td>2163200.0</td>\n",
       "      <td>2362100.0</td>\n",
       "      <td>7.0%</td>\n",
       "      <td>9.2%</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>Asia/Bangkok</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.755</td>\n",
       "      <td>75.50</td>\n",
       "      <td>14.7</td>\n",
       "      <td>7.6</td>\n",
       "      <td>15516</td>\n",
       "      <td>https://media.gettyimages.com/photos/beautiful...</td>\n",
       "      <td>https://media.gettyimages.com/photos/beautiful...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Rank       City         Country  Arrivals 2016  Arrivals 2017  \\\n",
       "0      1  Hong Kong       Hong Kong     26552700.0     27880300.0   \n",
       "1      2    Bangkok        Thailand     20698300.0     22453900.0   \n",
       "2      3     London  United Kingdom     19059500.0     19827800.0   \n",
       "3      4  Singapore       Singapore     16604000.0     17618800.0   \n",
       "4      5      Macau           Macau     15704000.0     17337200.0   \n",
       "..   ...        ...             ...            ...            ...   \n",
       "95    96      Porto        Portugal      1969300.0      2232500.0   \n",
       "96    97      Batam       Indonesia      1638600.0      2227500.0   \n",
       "97    98       Doha           Qatar      2849000.0      2200000.0   \n",
       "98    99     Rhodes          Greece      2091100.0      2178700.0   \n",
       "99   100      Krabi        Thailand      2021700.0      2163200.0   \n",
       "\n",
       "    Arrivals 2018 Growth 2017 Growth 2018  Rank 2012-17  Rank 2017-18  ...  \\\n",
       "0      29827200.0        5.0%        7.0%             0             0  ...   \n",
       "1      23688800.0        8.5%        5.5%             0             0  ...   \n",
       "2      20715900.0        4.0%        4.5%             0             0  ...   \n",
       "3      18551200.0        6.1%        5.3%             0             1  ...   \n",
       "4      18931400.0       10.4%        9.2%             1             1  ...   \n",
       "..            ...         ...         ...           ...           ...  ...   \n",
       "95      2391500.0       13.4%        7.1%            42             1  ...   \n",
       "96      2492600.0       35.9%       11.9%            31             6  ...   \n",
       "97      2210000.0      -22.8%        0.5%            31            10  ...   \n",
       "98      2281100.0        4.2%        4.7%             1             3  ...   \n",
       "99      2362100.0        7.0%        9.2%             9             2  ...   \n",
       "\n",
       "          timezone Safety Rank Safety Index Human Development Index  \\\n",
       "0   Asia/Hong_Kong        10.0        82.00                   0.933   \n",
       "1     Asia/Bangkok       155.0        58.61                   0.755   \n",
       "2    Europe/London       236.0        47.76                   0.922   \n",
       "3   Asia/Singapore        62.0        71.64                   0.932   \n",
       "4       Asia/Macau         NaN          NaN                   0.909   \n",
       "..             ...         ...          ...                     ...   \n",
       "95   Europe/Lisbon       135.0        60.75                   0.847   \n",
       "96    Asia/Jakarta         NaN          NaN                   0.694   \n",
       "97      Asia/Qatar         2.0        88.43                   0.856   \n",
       "98   Europe/Athens         NaN          NaN                   0.870   \n",
       "99    Asia/Bangkok         NaN          NaN                   0.755   \n",
       "\n",
       "   Life expectancy Expected years of schooling  Mean years of schooling  \\\n",
       "0            84.10                        16.3                     12.0   \n",
       "1            75.50                        14.7                      7.6   \n",
       "2            81.70                        17.4                     12.9   \n",
       "3            83.20                        16.2                     11.5   \n",
       "4            83.99                        12.4                     12.0   \n",
       "..             ...                         ...                      ...   \n",
       "95           81.40                        16.3                      9.2   \n",
       "96           69.40                        12.8                      8.0   \n",
       "97           78.30                        13.4                      9.8   \n",
       "98           81.40                        17.3                     10.8   \n",
       "99           75.50                        14.7                      7.6   \n",
       "\n",
       "    Gross national income per capita  \\\n",
       "0                              58420   \n",
       "1                              15516   \n",
       "2                              39116   \n",
       "3                              82503   \n",
       "4                              77903   \n",
       "..                               ...   \n",
       "95                             27315   \n",
       "96                             10846   \n",
       "97                            116818   \n",
       "98                             24648   \n",
       "99                             15516   \n",
       "\n",
       "                                          City images  \\\n",
       "0   https://media.gettyimages.com/photos/cityscape...   \n",
       "1   https://media.gettyimages.com/photos/wat-arun-...   \n",
       "2   https://media.gettyimages.com/photos/the-big-b...   \n",
       "3   https://media.gettyimages.com/photos/gardens-b...   \n",
       "4   https://media.gettyimages.com/photos/macau-sky...   \n",
       "..                                                ...   \n",
       "95  https://media.gettyimages.com/photos/porto-cit...   \n",
       "96  https://media.gettyimages.com/photos/scenic-vi...   \n",
       "97  https://media.gettyimages.com/photos/doha-skyl...   \n",
       "98  https://media.gettyimages.com/photos/rhodes-la...   \n",
       "99  https://media.gettyimages.com/photos/beautiful...   \n",
       "\n",
       "                                         City images2  \n",
       "0   https://media.gettyimages.com/photos/cityscape...  \n",
       "1   https://media.gettyimages.com/photos/wat-arun-...  \n",
       "2   https://media.gettyimages.com/photos/london-br...  \n",
       "3   https://media.gettyimages.com/photos/gardens-b...  \n",
       "4   https://media.gettyimages.com/photos/macau-sky...  \n",
       "..                                                ...  \n",
       "95  https://media.gettyimages.com/photos/porto-cit...  \n",
       "96  https://media.gettyimages.com/photos/scenic-vi...  \n",
       "97  https://media.gettyimages.com/photos/doha-skyl...  \n",
       "98  https://media.gettyimages.com/photos/beautiful...  \n",
       "99  https://media.gettyimages.com/photos/beautiful...  \n",
       "\n",
       "[100 rows x 25 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#adding 2 for 2 images and then adding each value to the column \"City images2\" \n",
    "topcities_cpshi2 = topcities_cpshi\n",
    "topcities_cpshi2['City images2']= imageurls2\n",
    "topcities_cpshi2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "STEP 6- CLEAN AND SAVE TO A CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "#topcities_cpshi2.dtypes\n",
    "#Rename some columns and remove alternatenames column\n",
    "topcities_cpshi2.rename(columns={'country code':'Country Code','latitude':'Latitude','longitude':'Longitude',\n",
    "                               'population':'Population','timezone':'Timezone'}, inplace=True)\n",
    "#topcities_cpshi2= topcities_cpshi2.drop(['alternatenames','Safety Rank'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There's no dups\n"
     ]
    }
   ],
   "source": [
    "#check again for dups\n",
    "try:\n",
    "    pandas.concat(g for _, g in topcities_cpshi2.groupby(\"City\") if len(g) > 1) \n",
    "except:\n",
    "    print(\"There's no dups\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Finished set! Save to a csv\n",
    "topcities_cpshi2.to_csv(r'destinations.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}