trip_advisor_resturants.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore restaurants data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### This notebook shows on the nuisances of data processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  18802  316159 6912444 data/restaurants/tripadvisor_in-restaurant_sample.csv\n"
     ]
    }
   ],
   "source": [
    "# Check if the counts in the input file and data frame are matching\n",
    "!wc data/restaurants/tripadvisor_in-restaurant_sample.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/restaurants/restaurants-on-tripadvisor.zip\",compression='zip',encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Restaurant ID          object\n",
       "Restaurant URL         object\n",
       "Name                   object\n",
       "Address                object\n",
       "Phone                  object\n",
       "City                   object\n",
       "State                  object\n",
       "Country                object\n",
       "Neighbourhood          object\n",
       "Email ID               object\n",
       "Menu                  float64\n",
       "Website                object\n",
       "Latitude              float64\n",
       "Longitude             float64\n",
       "About Restaurant       object\n",
       "Cuisine                object\n",
       "Good for(suitable)     object\n",
       "Price                  object\n",
       "Currency               object\n",
       "Rating                 object\n",
       "Ranking                object\n",
       "Deal(Promotion)       float64\n",
       "Total Review          float64\n",
       "Last Reviewed          object\n",
       "Recommended           float64\n",
       "Dining Option          object\n",
       "Award                  object\n",
       "Uniq Id                object\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the data types of the columns\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 18000 entries, 0 to 17999\n",
      "Data columns (total 28 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   Restaurant ID       18000 non-null  object \n",
      " 1   Restaurant URL      17994 non-null  object \n",
      " 2   Name                17991 non-null  object \n",
      " 3   Address             17978 non-null  object \n",
      " 4   Phone               15566 non-null  object \n",
      " 5   City                17004 non-null  object \n",
      " 6   State               17993 non-null  object \n",
      " 7   Country             17996 non-null  object \n",
      " 8   Neighbourhood       650 non-null    object \n",
      " 9   Email ID            858 non-null    object \n",
      " 10  Menu                0 non-null      float64\n",
      " 11  Website             8576 non-null   object \n",
      " 12  Latitude            17442 non-null  float64\n",
      " 13  Longitude           17442 non-null  float64\n",
      " 14  About Restaurant    937 non-null    object \n",
      " 15  Cuisine             7553 non-null   object \n",
      " 16  Good for(suitable)  1199 non-null   object \n",
      " 17  Price               1704 non-null   object \n",
      " 18  Currency            1701 non-null   object \n",
      " 19  Rating              8 non-null      object \n",
      " 20  Ranking             10133 non-null  object \n",
      " 21  Deal(Promotion)     0 non-null      float64\n",
      " 22  Total Review        10661 non-null  float64\n",
      " 23  Last Reviewed       10494 non-null  object \n",
      " 24  Recommended         0 non-null      float64\n",
      " 25  Dining Option       2199 non-null   object \n",
      " 26  Award               718 non-null    object \n",
      " 27  Uniq Id             18000 non-null  object \n",
      "dtypes: float64(6), object(22)\n",
      "memory usage: 3.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Restaurant ID         100.000000\n",
       "Restaurant URL         99.966667\n",
       "Name                   99.950000\n",
       "Address                99.877778\n",
       "Phone                  86.477778\n",
       "City                   94.466667\n",
       "State                  99.961111\n",
       "Country                99.977778\n",
       "Neighbourhood           3.611111\n",
       "Email ID                4.766667\n",
       "Menu                    0.000000\n",
       "Website                47.644444\n",
       "Latitude               96.900000\n",
       "Longitude              96.900000\n",
       "About Restaurant        5.205556\n",
       "Cuisine                41.961111\n",
       "Good for(suitable)      6.661111\n",
       "Price                   9.466667\n",
       "Currency                9.450000\n",
       "Rating                  0.044444\n",
       "Ranking                56.294444\n",
       "Deal(Promotion)         0.000000\n",
       "Total Review           59.227778\n",
       "Last Reviewed          58.300000\n",
       "Recommended             0.000000\n",
       "Dining Option          12.216667\n",
       "Award                   3.988889\n",
       "Uniq Id               100.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(len(df)-df.isnull().sum())*100/len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Restaurant ID</th>\n",
       "      <th>Restaurant URL</th>\n",
       "      <th>Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>Phone</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Neighbourhood</th>\n",
       "      <th>Email ID</th>\n",
       "      <th>...</th>\n",
       "      <th>Currency</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Ranking</th>\n",
       "      <th>Deal(Promotion)</th>\n",
       "      <th>Total Review</th>\n",
       "      <th>Last Reviewed</th>\n",
       "      <th>Recommended</th>\n",
       "      <th>Dining Option</th>\n",
       "      <th>Award</th>\n",
       "      <th>Uniq Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1547</th>\n",
       "      <td>4\\r159832</td>\n",
       "      <td>http://www.tripadvisor.in/Restaurant_Review-g6...</td>\n",
       "      <td>Europe</td>\n",
       "      <td>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>...</td>\n",
       "      <td>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>bfc127bb137c9aa4628c0659e0dce2d5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Restaurant ID                                     Restaurant URL    Name  \\\n",
       "1547     4\\r159832  http://www.tripadvisor.in/Restaurant_Review-g6...  Europe   \n",
       "\n",
       "     Address Phone City State Country Neighbourhood Email ID  ...  Currency  \\\n",
       "1547     NaN   NaN  NaN   NaN     NaN           NaN      NaN  ...       NaN   \n",
       "\n",
       "     Rating  Ranking  Deal(Promotion) Total Review Last Reviewed Recommended  \\\n",
       "1547    NaN      NaN              NaN          NaN           NaN         NaN   \n",
       "\n",
       "     Dining Option Award                           Uniq Id  \n",
       "1547           NaN   NaN  bfc127bb137c9aa4628c0659e0dce2d5  \n",
       "\n",
       "[1 rows x 28 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df[\"Uniq Id\"] == \"bfc127bb137c9aa4628c0659e0dce2d5\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select sub set of columns that can be checked for null and remove any such records\n",
    "major_columns = ['Restaurant ID','Restaurant URL','Name','Uniq Id']\n",
    "col_na = [col_name for col_name in list(df.columns) if col_name not in major_columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Address', 'Phone', 'City', 'State', 'Country', 'Neighbourhood', 'Email ID', 'Menu', 'Website', 'Latitude', 'Longitude', 'About Restaurant', 'Cuisine', 'Good for(suitable)', 'Price', 'Currency', 'Rating', 'Ranking', 'Deal(Promotion)', 'Total Review', 'Last Reviewed', 'Recommended', 'Dining Option', 'Award']\n"
     ]
    }
   ],
   "source": [
    "print(col_na)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all of the rows where subset of columns are completely null\n",
    "df1 = df.dropna(axis=0,subset=col_na,how='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17996"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df1.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop those columns that are completely null\n",
    "df2 = df1.dropna(axis=1,how='all').copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17996"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df2.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Restaurant ID</th>\n",
       "      <th>Restaurant URL</th>\n",
       "      <th>Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>Phone</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Neighbourhood</th>\n",
       "      <th>Email ID</th>\n",
       "      <th>...</th>\n",
       "      <th>Good for(suitable)</th>\n",
       "      <th>Price</th>\n",
       "      <th>Currency</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Ranking</th>\n",
       "      <th>Total Review</th>\n",
       "      <th>Last Reviewed</th>\n",
       "      <th>Dining Option</th>\n",
       "      <th>Award</th>\n",
       "      <th>Uniq Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1345154</td>\n",
       "      <td>http://www.tripadvisor.in/Restaurant_Review-g1...</td>\n",
       "      <td>Gaststatte Gunter Jung</td>\n",
       "      <td>Hintergasse 38, 61231 Bad Nauheim, Hesse, Germany</td>\n",
       "      <td>+49 6032 81796</td>\n",
       "      <td>Bad Nauheim</td>\n",
       "      <td>Hesse</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40 of 61</td>\n",
       "      <td>1.0</td>\n",
       "      <td>- 28 May 2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>030c4d068d2d59518db5de6ebb9ecdd6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5818482</td>\n",
       "      <td>http://www.tripadvisor.in/Restaurant_Review-g1...</td>\n",
       "      <td>TurQuaz Shisha Lounge &amp; Thorizzio da Bambi Lie...</td>\n",
       "      <td>Frankfurter Strasse 49, 63571 Gelnhausen, Hess...</td>\n",
       "      <td>06051 8877462</td>\n",
       "      <td>Gelnhausen</td>\n",
       "      <td>Hesse</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>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>6e160a28f7d9beeee848a35bd947477c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>959812</td>\n",
       "      <td>http://www.tripadvisor.in/Restaurant_Review-g2...</td>\n",
       "      <td>Zum Schrebergarten</td>\n",
       "      <td>Lampertheimer Str. 90, Viernheim, Hesse, Germany</td>\n",
       "      <td>06204-72892</td>\n",
       "      <td>Viernheim</td>\n",
       "      <td>Hesse</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24 of 53</td>\n",
       "      <td>2.0</td>\n",
       "      <td>- 6 Feb 2008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>cf8fedd0a87f95cc1bd9c5f7a3561e82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5826288</td>\n",
       "      <td>http://www.tripadvisor.in/Restaurant_Review-g1...</td>\n",
       "      <td>Zum Taubenschlag - \"Magda\" Fruher \"Poldi\"</td>\n",
       "      <td>Wilhelm-Leuschner-Str. 19, 64653 Lorsch, Hesse...</td>\n",
       "      <td>06251/57234</td>\n",
       "      <td>Lorsch</td>\n",
       "      <td>Hesse</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>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>d7e080df070caeeeb0615b436a46802a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1848931</td>\n",
       "      <td>http://www.tripadvisor.in/Restaurant_Review-g1...</td>\n",
       "      <td>Zum Gruenen Wald</td>\n",
       "      <td>Friedrich-Ebert-Str. 19, 61476  Kronberg im Ta...</td>\n",
       "      <td>061732011</td>\n",
       "      <td>Kronberg im Taunus</td>\n",
       "      <td>Hesse</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7  - 27</td>\n",
       "      <td>€</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1 of 24</td>\n",
       "      <td>28.0</td>\n",
       "      <td>11 March 2014</td>\n",
       "      <td>Lunch Spot, Dinner, Reservations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>e9fcebcaa1e77ed0f5f55f0ebddd2f1a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Restaurant ID                                     Restaurant URL  \\\n",
       "0       1345154  http://www.tripadvisor.in/Restaurant_Review-g1...   \n",
       "1       5818482  http://www.tripadvisor.in/Restaurant_Review-g1...   \n",
       "2        959812  http://www.tripadvisor.in/Restaurant_Review-g2...   \n",
       "3       5826288  http://www.tripadvisor.in/Restaurant_Review-g1...   \n",
       "4       1848931  http://www.tripadvisor.in/Restaurant_Review-g1...   \n",
       "\n",
       "                                                Name  \\\n",
       "0                             Gaststatte Gunter Jung   \n",
       "1  TurQuaz Shisha Lounge & Thorizzio da Bambi Lie...   \n",
       "2                                 Zum Schrebergarten   \n",
       "3          Zum Taubenschlag - \"Magda\" Fruher \"Poldi\"   \n",
       "4                                   Zum Gruenen Wald   \n",
       "\n",
       "                                             Address           Phone  \\\n",
       "0  Hintergasse 38, 61231 Bad Nauheim, Hesse, Germany  +49 6032 81796   \n",
       "1  Frankfurter Strasse 49, 63571 Gelnhausen, Hess...   06051 8877462   \n",
       "2   Lampertheimer Str. 90, Viernheim, Hesse, Germany     06204-72892   \n",
       "3  Wilhelm-Leuschner-Str. 19, 64653 Lorsch, Hesse...     06251/57234   \n",
       "4  Friedrich-Ebert-Str. 19, 61476  Kronberg im Ta...       061732011   \n",
       "\n",
       "                 City  State  Country Neighbourhood Email ID  ...  \\\n",
       "0         Bad Nauheim  Hesse  Germany           NaN      NaN  ...   \n",
       "1          Gelnhausen  Hesse  Germany           NaN      NaN  ...   \n",
       "2           Viernheim  Hesse  Germany           NaN      NaN  ...   \n",
       "3              Lorsch  Hesse  Germany           NaN      NaN  ...   \n",
       "4  Kronberg im Taunus  Hesse  Germany           NaN      NaN  ...   \n",
       "\n",
       "  Good for(suitable)     Price  Currency Rating   Ranking Total Review  \\\n",
       "0                NaN       NaN       NaN    NaN  40 of 61          1.0   \n",
       "1                NaN       NaN       NaN    NaN       NaN          NaN   \n",
       "2                NaN       NaN       NaN    NaN  24 of 53          2.0   \n",
       "3                NaN       NaN       NaN    NaN       NaN          NaN   \n",
       "4                NaN  7  - 27          €    NaN   1 of 24         28.0   \n",
       "\n",
       "   Last Reviewed                     Dining Option Award  \\\n",
       "0  - 28 May 2012                               NaN   NaN   \n",
       "1            NaN                               NaN   NaN   \n",
       "2   - 6 Feb 2008                               NaN   NaN   \n",
       "3            NaN                               NaN   NaN   \n",
       "4  11 March 2014  Lunch Spot, Dinner, Reservations   NaN   \n",
       "\n",
       "                            Uniq Id  \n",
       "0  030c4d068d2d59518db5de6ebb9ecdd6  \n",
       "1  6e160a28f7d9beeee848a35bd947477c  \n",
       "2  cf8fedd0a87f95cc1bd9c5f7a3561e82  \n",
       "3  d7e080df070caeeeb0615b436a46802a  \n",
       "4  e9fcebcaa1e77ed0f5f55f0ebddd2f1a  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 17996 entries, 0 to 17999\n",
      "Data columns (total 25 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   Restaurant ID       17996 non-null  object \n",
      " 1   Restaurant URL      17990 non-null  object \n",
      " 2   Name                17990 non-null  object \n",
      " 3   Address             17978 non-null  object \n",
      " 4   Phone               15566 non-null  object \n",
      " 5   City                17004 non-null  object \n",
      " 6   State               17993 non-null  object \n",
      " 7   Country             17996 non-null  object \n",
      " 8   Neighbourhood       650 non-null    object \n",
      " 9   Email ID            858 non-null    object \n",
      " 10  Website             8576 non-null   object \n",
      " 11  Latitude            17442 non-null  float64\n",
      " 12  Longitude           17442 non-null  float64\n",
      " 13  About Restaurant    937 non-null    object \n",
      " 14  Cuisine             7553 non-null   object \n",
      " 15  Good for(suitable)  1199 non-null   object \n",
      " 16  Price               1704 non-null   object \n",
      " 17  Currency            1701 non-null   object \n",
      " 18  Rating              8 non-null      object \n",
      " 19  Ranking             10133 non-null  object \n",
      " 20  Total Review        10661 non-null  float64\n",
      " 21  Last Reviewed       10494 non-null  object \n",
      " 22  Dining Option       2199 non-null   object \n",
      " 23  Award               718 non-null    object \n",
      " 24  Uniq Id             17996 non-null  object \n",
      "dtypes: float64(3), object(22)\n",
      "memory usage: 3.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0     2469\n",
       "2.0     1287\n",
       "3.0      867\n",
       "4.0      558\n",
       "5.0      462\n",
       "6.0      378\n",
       "7.0      316\n",
       "8.0      269\n",
       "9.0      247\n",
       "10.0     216\n",
       "11.0     201\n",
       "12.0     180\n",
       "13.0     143\n",
       "15.0     142\n",
       "14.0     130\n",
       "16.0     129\n",
       "17.0     125\n",
       "18.0     100\n",
       "19.0      98\n",
       "20.0      90\n",
       "22.0      83\n",
       "Name: Total Review, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['Total Review'].value_counts().iloc[0:21]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8490"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['Total Review'].value_counts().iloc[0:21].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0     2469\n",
       "2.0     1287\n",
       "3.0      867\n",
       "4.0      558\n",
       "5.0      462\n",
       "6.0      378\n",
       "7.0      316\n",
       "8.0      269\n",
       "9.0      247\n",
       "10.0     216\n",
       "11.0     201\n",
       "12.0     180\n",
       "13.0     143\n",
       "15.0     142\n",
       "14.0     130\n",
       "16.0     129\n",
       "17.0     125\n",
       "18.0     100\n",
       "19.0      98\n",
       "20.0      90\n",
       "22.0      83\n",
       "Name: Total Review, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['Total Review'].value_counts().iloc[0:21]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Restaurant ID', 'Restaurant URL', 'Name', 'Address', 'Phone', 'City',\n",
       "       'State', 'Country', 'Neighbourhood', 'Email ID', 'Website', 'Latitude',\n",
       "       'Longitude', 'About Restaurant', 'Cuisine', 'Good for(suitable)',\n",
       "       'Price', 'Currency', 'Rating', 'Ranking', 'Total Review',\n",
       "       'Last Reviewed', 'Dining Option', 'Award', 'Uniq Id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7f4ab845a150>"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAygAAAGYCAYAAACpocatAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8li6FKAAASmklEQVR4nO3de6ykB1nH8d9Dt4hIBWyPRanLqiFEo0HI2j+AqKASpIrRyMUoGqOuJmowErHGP4z/mJp4S9REKxDxAgSjJCqo4KU1JNxaLgZsSQyW0Ch2oSoXDdry+MeeTca1cGZ2Z877dOfzSU46887k5Plj82S+fd95T3V3AAAAJnjI0gMAAACcJ1AAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGOPELn7pNddc06dOndrFrwYAAC4Dt99++4e7++DC4zsJlFOnTuW2227bxa8GAAAuA1X1gQc67hIvAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMcWKdN1XVXUk+luT+JPd19+ldDgUAAOyntQLl0NO7+8M7mwQAANh7LvECAADGWPcMSid5Q1V1kt/q7psvfENVnUlyJklOnjy5vQkfpE7d+LqlR2CAu266YekRAAAeVNY9g/LU7n5ykm9K8iNV9TUXvqG7b+7u0919+uDgYKtDAgAA+2GtQOnufz787z1JXpvk+l0OBQAA7KcjA6WqPqeqrjr/OMkzk7xn14MBAAD7Z53voFyb5LVVdf79r+zuv9jpVAAAwF46MlC6+/1JnngMswAAAHvObYYBAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGGPtQKmqK6rqnVX1Z7scCAAA2F+bnEF5UZI7djUIAADAWoFSVdcluSHJS3c7DgAAsM/WPYPyq0lekuRTO5wFAADYc0cGSlV9c5J7uvv2I953pqpuq6rbzp49u7UBAQCA/bHOGZSnJnlOVd2V5NVJnlFVv3/hm7r75u4+3d2nDw4OtjwmAACwD44MlO7+6e6+rrtPJXlBkr/p7u/e+WQAAMDe8XdQAACAMU5s8ubuviXJLTuZBAAA2HvOoAAAAGMIFAAAYAyBAgAAjCFQAACAMQQKAAAwhkABAADGECgAAMAYAgUAABhDoAAAAGMIFAAAYAyBAgAAjCFQAACAMQQKAAAwhkABAADGECgAAMAYAgUAABhDoAAAAGMIFAAAYAyBAgAAjCFQAACAMQQKAAAwhkABAADGECgAAMAYAgUAABhDoAAAAGMIFAAAYAyBAgAAjCFQAACAMQQKAAAwhkABAADGECgAAMAYAgUAABhDoAAAAGMIFAAAYAyBAgAAjCFQAACAMQQKAAAwhkABAADGECgAAMAYAgUAABhDoAAAAGMIFAAAYAyBAgAAjCFQAACAMQQKAAAwhkABAADGECgAAMAYAgUAABhDoAAAAGMIFAAAYAyBAgAAjCFQAACAMQQKAAAwxpGBUlUPq6q3VdW7q+q9VfVzxzEYAACwf06s8Z5PJnlGd3+8qq5M8qaq+vPufsuOZwMAAPbMkYHS3Z3k44dPrzz86V0OBQAA7Ke1voNSVVdU1buS3JPkjd391t2OBQAA7KO1AqW77+/ur0pyXZLrq+orLnxPVZ2pqtuq6razZ89ue04AAGAPbHQXr+7+9yS3JHnWA7x2c3ef7u7TBwcHWxoPAADYJ+vcxeugqh51+Pizk3xDkjt3PRgAALB/1rmL1xckeUVVXZFzQfOa7v6z3Y4FAADso3Xu4vX3SZ50DLMAAAB7zl+SBwAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGCMIwOlqr6oqv62qu6oqvdW1YuOYzAAAGD/nFjjPfcleXF3v6Oqrkpye1W9sbv/YcezAQAAe+bIMyjd/S/d/Y7Dxx9LckeSx+56MAAAYP9s9B2UqjqV5ElJ3rqLYQAAgP22ziVeSZKqekSSP0ry49390Qd4/UySM0ly8uTJrQ0I8GB36sbXLT0CA9x10w1LjwDwoLDWGZSqujLn4uQPuvuPH+g93X1zd5/u7tMHBwfbnBEAANgT69zFq5K8LMkd3f3Lux8JAADYV+ucQXlqkhcmeUZVvevw59k7ngsAANhDR34HpbvflKSOYRYAAGDP+UvyAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIwhUAAAgDEECgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABjCBQAAGAMgQIAAIxxZKBU1cur6p6qes9xDAQAAOyvdc6g/E6SZ+14DgAAgKMDpbv/Lsm9xzALAACw53wHBQAAGOPEtn5RVZ1JciZJTp48ua1fCwBw2Th14+uWHoEh7rrphqVHGGtrZ1C6++buPt3dpw8ODrb1awEAgD3iEi8AAGCMdW4z/Kokb07yhKq6u6q+f/djAQAA++jI76B093cexyAAAAAu8QIAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgDIECAACMIVAAAIAxBAoAADCGQAEAAMYQKAAAwBgCBQAAGEOgAAAAYwgUAABgjLUCpaqeVVXvq6p/rKobdz0UAACwn44MlKq6IslvJPmmJF+e5Dur6st3PRgAALB/1jmDcn2Sf+zu93f3fyd5dZJv3e1YAADAPlonUB6b5IMrz+8+PAYAALBVJ9Z4Tz3Asf5/b6o6k+TM4dOPV9X7LmUwLgvXJPnw0kMsqX5h6QlgDPvAPoBVdoKdkCSPe6CD6wTK3Um+aOX5dUn++cI3dffNSW6+qNG4LFXVbd19euk5gOXZB8AqO4HPZJ1LvN6e5PFV9cVV9dAkL0jyJ7sdCwAA2EdHnkHp7vuq6keT/GWSK5K8vLvfu/PJAACAvbPOJV7p7tcnef2OZ+Hy45I/4Dz7AFhlJ/BpVff/+747AADAItb6S/IAAADHQaAAAABjCBQAAGAMgcIlq6pHVtVNVXVnVX3k8OeOw2OPWno+4PjYB8AqO4GLIVDYhtck+bckX9fdV3f31UmefnjsDxedDDhu9gGwyk5gY+7ixSWrqvd19xM2fQ24/NgHwCo7gYvhDArb8IGqeklVXXv+QFVdW1U/leSDC84FHD/7AFhlJ7AxgcI2PD/J1Ulurap7q+reJLck+bwkz1tyMODY2QfAKjuBjbnECwAAGOPE0gNw+amqpyW5Psl7uvsNS88DLMc+AFbZCazDJV5csqp628rjH0zy60muSvKzVXXjYoMBx84+AFbZCVwMl3hxyarqnd39pMPHb0/y7O4+W1Wfk+Qt3f2Vy04IHBf7AFhlJ3AxXOLFNjykqh6dc2fkqrvPJkl3f6Kq7lt2NOCY2QfAKjuBjQkUtuGRSW5PUkm6qh7T3R+qqkccHgP2h30ArLIT2JhLvNiZqnp4kmu7+5+WngVYln0ArLIT+EwECgAAMIa7eAEAAGMIFC5ZVX3W0jMAM9gHwCo7gYshUNiGNydJVf3e0oMAi7MPgFV2AhtzFy+24aFV9b1JnlJV337hi939xwvMBCzDPgBW2QlsTKCwDT+c5LuSPCrJt1zwWiexfGB/2AfAKjuBjbmLF1tTVd/f3S9beg5gefYBsMpOYBMCha2pqofm3P8p+ZrDQ7cm+c3u/p/lpgKWYB8Aq+wENiFQ2JqqemmSK5O84vDQC5Pc390/sNxUwBLsA2CVncAmBApbU1Xv7u4nHnUMuPzZB8AqO4FNuM0w23R/VX3p+SdV9SVJ7l9wHmA59gGwyk5gbe7ixTb9ZJK/rar3J6kkj0vyfcuOBCzEPgBW2QmszSVebNXhX4x9Qs4tnzu7+5MLjwQsxD4AVtkJrEugAAAAY/gOCgAAMIZAAQAAxhAobE1V/fU6x4DLn30ArLIT2IS7eHHJquphSR6e5JqqenTOffktST43yRcuNhhw7OwDYJWdwMUQKGzDDyX58ZxbNO9YOf7RJL+xyETAUuwDYJWdwMbcxYutqaof6+5fW3oOYHn2AbDKTmATAoWtqarveaDj3f27xz0LsCz7AFhlJ7AJl3ixTV+98vhhSb4+507nWj6wf+wDYJWdwNqcQWFnquqRSX6vu5+z9CzAsuwDYJWdwGfiNsPs0n8mefzSQwAj2AfAKjuBT8slXmxNVf1pkvOn5K5I8mVJXrPcRMBS7ANglZ3AJlzixdZU1deuPL0vyQe6++6l5gGWYx8Aq+wENuESL7amu29NcmeSq5I8Osl/LzsRsBT7AFhlJ7AJgcLWVNXzkrwtyXOTPC/JW6vqO5adCliCfQCsshPYhEu82JqqeneSb+zuew6fHyT5q+5+4rKTAcfNPgBW2QlswhkUtukh5xfPoY/EvzHYV/YBsMpOYG3u4sU2/UVV/WWSVx0+f36S1y84D7Ac+wBYZSewNpd4sVVV9e1Jnpakkvxdd7924ZGAhdgHwCo7gXUJFAAAYAzX/gEAAGMIFAAAYAyBAgAAjCFQuGRV9ayVx4+sqpdV1d9X1Sur6tolZwOO1+EOuKmq7qyqjxz+3HF47FFLzwccLzuBiyFQ2IafX3n8S0n+Jcm3JHl7kt9aZCJgKa9J8m9Jvq67r+7uq5M8/fDYHy46GbAEO4GNuYsXl6yq3tHdTz58/K7u/qqV1/7Pc+DyVlXv6+4nbPoacHmyE7gYzqCwDZ9fVT9RVS9O8rlVVSuv+TcG++UDVfWS1cs7q+raqvqpJB9ccC5gGXYCG/PhkW347SRXJXlEklckuSZJquoxSd614FzA8Xt+kquT3FpV91bVvUluSfJ5SZ635GDAIuwENuYSLwAAYAxnUNiJqvrdpWcAlldVTzu8BPSZS88CzOAzAkc5sfQAPPhV1Z9ceCjJ08/fPrC7n3P8UwFLqKq3dff1h49/MMmPJHltkp+tqid3902LDggcK58RuBgChW24Lsk/JHlpks655XM65245DOyXK1cen0nyjd19tqp+MclbkggU2C8+I7Axl3ixDaeT3J7kZ5L8R3ffkuS/uvvW7r510cmA4/aQqnp0VV2dc99zPJsk3f2JJPctOxqwAJ8R2JgvybM1VXVdkl9J8q9JntPdJxceCThmVXVXkk/l3P8l7SRP6e4PVdUjkrzJ30WC/eQzAptwiRdb0913J3luVd2Q5KNLzwMcv+4+9Wle+lSSbzvGUYBBfEZgE86gAAAAY/gOCgAAMIZAAQAAxhAoAADAGAIFAAAYQ6AAAABj/C/Rhlbl0rFadgAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 1008x432 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Top 20 reviews\n",
    "df2['Rating'].value_counts().iloc[0:21].plot(kind='bar',figsize=(14,6))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# incase if you want to consider the columns having data populated > 60%\n",
    "#cols = [col for col in list(df.columns) if col not in list(df_col[df_col > 60].index)]"
   ]
  },
  {
   "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>Restaurant ID</th>\n",
       "      <th>Restaurant URL</th>\n",
       "      <th>Name</th>\n",
       "      <th>Address</th>\n",
       "      <th>Phone</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Neighbourhood</th>\n",
       "      <th>Email ID</th>\n",
       "      <th>...</th>\n",
       "      <th>Currency</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Ranking</th>\n",
       "      <th>Deal(Promotion)</th>\n",
       "      <th>Total Review</th>\n",
       "      <th>Last Reviewed</th>\n",
       "      <th>Recommended</th>\n",
       "      <th>Dining Option</th>\n",
       "      <th>Award</th>\n",
       "      <th>Uniq Id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1345154</td>\n",
       "      <td>http://www.tripadvisor.in/Restaurant_Review-g1...</td>\n",
       "      <td>Gaststatte Gunter Jung</td>\n",
       "      <td>Hintergasse 38, 61231 Bad Nauheim, Hesse, Germany</td>\n",
       "      <td>+49 6032 81796</td>\n",
       "      <td>Bad Nauheim</td>\n",
       "      <td>Hesse</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40 of 61</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>- 28 May 2012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>030c4d068d2d59518db5de6ebb9ecdd6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5818482</td>\n",
       "      <td>http://www.tripadvisor.in/Restaurant_Review-g1...</td>\n",
       "      <td>TurQuaz Shisha Lounge &amp; Thorizzio da Bambi Lie...</td>\n",
       "      <td>Frankfurter Strasse 49, 63571 Gelnhausen, Hess...</td>\n",
       "      <td>06051 8877462</td>\n",
       "      <td>Gelnhausen</td>\n",
       "      <td>Hesse</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>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>6e160a28f7d9beeee848a35bd947477c</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Restaurant ID                                     Restaurant URL  \\\n",
       "0       1345154  http://www.tripadvisor.in/Restaurant_Review-g1...   \n",
       "1       5818482  http://www.tripadvisor.in/Restaurant_Review-g1...   \n",
       "\n",
       "                                                Name  \\\n",
       "0                             Gaststatte Gunter Jung   \n",
       "1  TurQuaz Shisha Lounge & Thorizzio da Bambi Lie...   \n",
       "\n",
       "                                             Address           Phone  \\\n",
       "0  Hintergasse 38, 61231 Bad Nauheim, Hesse, Germany  +49 6032 81796   \n",
       "1  Frankfurter Strasse 49, 63571 Gelnhausen, Hess...   06051 8877462   \n",
       "\n",
       "          City  State  Country Neighbourhood Email ID  ...  Currency Rating  \\\n",
       "0  Bad Nauheim  Hesse  Germany           NaN      NaN  ...       NaN    NaN   \n",
       "1   Gelnhausen  Hesse  Germany           NaN      NaN  ...       NaN    NaN   \n",
       "\n",
       "    Ranking  Deal(Promotion) Total Review  Last Reviewed Recommended  \\\n",
       "0  40 of 61              NaN          1.0  - 28 May 2012         NaN   \n",
       "1       NaN              NaN          NaN            NaN         NaN   \n",
       "\n",
       "  Dining Option Award                           Uniq Id  \n",
       "0           NaN   NaN  030c4d068d2d59518db5de6ebb9ecdd6  \n",
       "1           NaN   NaN  6e160a28f7d9beeee848a35bd947477c  \n",
       "\n",
       "[2 rows x 28 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    40 of 61\n",
       "1         NaN\n",
       "2    24 of 53\n",
       "3         NaN\n",
       "4     1 of 24\n",
       "Name: Ranking, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Ranking'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "United States    12851\n",
       "Germany           2549\n",
       "Brazil            1661\n",
       "England            431\n",
       "Scotland           356\n",
       "Canada              68\n",
       "France              44\n",
       "Switzerland         29\n",
       "Wales                2\n",
       "South America        1\n",
       "Europe               1\n",
       "Austria              1\n",
       "Mexico               1\n",
       "Africa               1\n",
       "Name: Country, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Country.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FL              3132\n",
       "CA              2985\n",
       "AL              1509\n",
       "WA              1295\n",
       "MA              1291\n",
       "MI               936\n",
       "MN               429\n",
       "AR               282\n",
       "RI               248\n",
       "SD               235\n",
       "CO               224\n",
       "ME                74\n",
       "MS                36\n",
       "IA                27\n",
       "OR                25\n",
       "GA                22\n",
       "ND                15\n",
       "ID                13\n",
       "NH                10\n",
       "TN                 9\n",
       "WI                 8\n",
       "NV                 7\n",
       "NE                 7\n",
       "MO                 6\n",
       "OH                 5\n",
       "CT                 4\n",
       "TX                 3\n",
       "OK                 2\n",
       "Harrison           1\n",
       "Gainesville        1\n",
       "Lutsen             1\n",
       "White Salmon       1\n",
       "LA                 1\n",
       "Platte             1\n",
       "Woburn             1\n",
       "Birmingham         1\n",
       "Sacramento         1\n",
       "Mitchell           1\n",
       "Name: State, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.Country==\"United States\"][\"State\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_usa = df[df.Country==\"United States\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select only those records with valid state codes, STATE code that has lenght of 2\n",
    "df_usa_state = df_usa[df_usa.State.str.len() == 2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FL    3132\n",
       "CA    2985\n",
       "AL    1509\n",
       "WA    1295\n",
       "MA    1291\n",
       "MI     936\n",
       "MN     429\n",
       "AR     282\n",
       "RI     248\n",
       "SD     235\n",
       "CO     224\n",
       "ME      74\n",
       "MS      36\n",
       "IA      27\n",
       "OR      25\n",
       "GA      22\n",
       "ND      15\n",
       "ID      13\n",
       "NH      10\n",
       "TN       9\n",
       "WI       8\n",
       "NE       7\n",
       "NV       7\n",
       "MO       6\n",
       "OH       5\n",
       "CT       4\n",
       "TX       3\n",
       "OK       2\n",
       "LA       1\n",
       "Name: State, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_usa_state.State.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FL    3132\n",
       "CA    2985\n",
       "AL    1509\n",
       "WA    1295\n",
       "MA    1291\n",
       "MI     936\n",
       "MN     429\n",
       "AR     282\n",
       "RI     248\n",
       "SD     235\n",
       "Name: State, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_usa_state.State.value_counts()[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2012-05-28\n",
       "1          NaT\n",
       "2   2008-02-06\n",
       "3          NaT\n",
       "4   2014-03-11\n",
       "Name: Last Reviewed, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime(df2['Last Reviewed']).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    - 28 May 2012\n",
       "1              NaN\n",
       "2     - 6 Feb 2008\n",
       "3              NaN\n",
       "4    11 March 2014\n",
       "5              NaN\n",
       "6    - 20 Apr 2014\n",
       "7      9 June 2014\n",
       "8     - 1 Jun 2014\n",
       "9              NaN\n",
       "Name: Last Reviewed, dtype: object"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Last Reviewed column contains data values in the format dd Mon Year\n",
    "df2[\"Last Reviewed\"].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This function extract the day, month, year part of the date column\n",
    "def get_split_value(value,index):\n",
    "    value = str(value)\n",
    "    value = value.replace(\"- \",\"\")\n",
    "    fields = value.split()\n",
    "    month = \"\"\n",
    "    try:\n",
    "        month = fields[index]\n",
    "    except TypeError:\n",
    "        month = \"\"\n",
    "    except IndexError:\n",
    "        month = \"\"\n",
    "    return month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[67, 68, 69, 75, 92, 113, 123, 210, 218, 267, 315, 360, 397, 412, 420, 450, 494, 536, 565, 586, 902, 1065, 2690, 7502]\n"
     ]
    }
   ],
   "source": [
    "# Let's check if the date values in Last Reviewed column are having valid values\n",
    "# check Day\n",
    "days = df2[\"Last Reviewed\"].apply(lambda x:get_split_value(x,1)).value_counts().sort_index(ascending=True)\n",
    "print(sorted([ int(x) for x in list(days.values) if x != 'nan']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "             7502\n",
       "Apr          1065\n",
       "April         397\n",
       "Aug           315\n",
       "August         68\n",
       "Dec           420\n",
       "December       92\n",
       "Feb           494\n",
       "February      123\n",
       "Jan           536\n",
       "January       113\n",
       "Jul           586\n",
       "July          267\n",
       "Jun           565\n",
       "June          210\n",
       "Mar           902\n",
       "March         218\n",
       "May          2690\n",
       "Nov           412\n",
       "November       67\n",
       "Oct           450\n",
       "October        75\n",
       "Sep           360\n",
       "September      69\n",
       "Name: Last Reviewed, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['', 'Apr', 'April', 'Aug', 'August', 'Dec', 'December', 'Feb', 'February', 'Jan', 'January', 'Jul', 'July', 'Jun', 'June', 'Mar', 'March', 'May', 'Nov', 'November', 'Oct', 'October', 'Sep', 'September']\n"
     ]
    }
   ],
   "source": [
    "# check Month\n",
    "months = df2[\"Last Reviewed\"].apply(lambda x:get_split_value(x,1)).value_counts().sort_index(ascending=True)\n",
    "print(sorted(list(months.index)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['', '2004', '2005', '2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014']\n"
     ]
    }
   ],
   "source": [
    "# check Year\n",
    "years = df2[\"Last Reviewed\"].apply(lambda x:get_split_value(x,2)).value_counts().sort_index(ascending=True)\n",
    "print(list(years.index))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        7502\n",
       "2014    6738\n",
       "2013    2712\n",
       "2012     666\n",
       "2011     175\n",
       "2008      70\n",
       "2010      64\n",
       "2009      39\n",
       "2007      21\n",
       "2005       4\n",
       "2004       3\n",
       "2006       2\n",
       "Name: Last Reviewed, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2[\"Last Reviewed\"].apply(lambda x:get_split_value(x,2)).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['last_reviewed_dt'] = pd.to_datetime(df2[\"Last Reviewed\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2014.0    4890\n",
       "2013.0    2126\n",
       "2012.0     462\n",
       "2011.0      78\n",
       "2008.0      64\n",
       "2009.0      34\n",
       "2010.0      29\n",
       "2007.0      20\n",
       "2005.0       4\n",
       "2004.0       3\n",
       "2006.0       2\n",
       "Name: last_reviewed_dt, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2[df2.Country==\"United States\"].last_reviewed_dt.apply(lambda x:x.year).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2012-05-28\n",
       "1          NaT\n",
       "2   2008-02-06\n",
       "3          NaT\n",
       "4   2014-03-11\n",
       "Name: last_reviewed_dt, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.last_reviewed_dt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                   10494\n",
       "unique                   1157\n",
       "top       2014-05-28 00:00:00\n",
       "freq                      290\n",
       "first     2004-07-05 00:00:00\n",
       "last      2014-07-23 00:00:00\n",
       "Name: last_reviewed_dt, dtype: object"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.last_reviewed_dt.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}