<jupyter_start><jupyter_text>Exploratory Data AnalysisThis notebook explores the Zommato dataset we will work for this project.First, we did a quick inspection for null and duplicates values, clear all NaN entries (9),and reshaped some columns that contained nested lists. Next, we created some visualizations,and defined our goals that we will explore using MySQL.<jupyter_code># Geopandas packages help us to create visualizations using coordinates.
!pip install geopandas
# Import libraries to Explore data
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
import random
from itertools import chain
# We installed GeoPandas to create a visualization of restaurant's coordinates
from shapely.geometry import Point
import geopandas as gpd 
from geopandas import GeoDataFrame
data = pd.read_csv('zomato.csv', encoding = 'latin1')
data.head(3)
#This dataset has a dimension of 21 columns x 9551 rows,
# however we will have to check for duplicates and NaN
data.info()
# Apparently this dataset does not contain null values,
# however we will apply dropna() and see how its shape changes
print('Shape before dropna:', data.shape)
data.dropna(inplace=True)
print('Shape after dropna:', data.shape)
## Find duplicates in data, and explore the Restaurant Name, Restaurant ID, and Cuisine columns:
cuisines = data.Cuisines.value_counts().to_dict()
restaurants = data['Restaurant Name'].value_counts().to_dict().keys()
restaurants_id= data['Restaurant ID'].value_counts().to_dict().keys()

# We can see that we have some restaurants that belongs to the category of 'Chains'.
#for instance 'Dunikin Donuts'. So, we will have duplicates in the Restaurant Name column. 
print('The number of different restaurants:', len(list(restaurants)))
print('The number of different restaurant ids:', len(list(restaurants_id)))

# We also see that the Cuisine column contains lists of the different types of cuisines 
# that each restaurant prepares. For example, 
#['indian', 'chinese', 'filipino']. This information is will be decomposed in new rows
print('Example of one cell in the Cuisines column: ',random.choice(list(cuisines.keys())))
# We couldn't find any duplicate in the Restuarant ID column, and that is good news.
# Eventhough some resturants like 'chain restaurants' share the same name, their IDs are
# different. Therefore, we can use this column as a Primary Key in MySQL table creation notebook
duplicate = data[data.duplicated('Restaurant ID')] 
print("Duplicate Rows based on Restaurant ID :") 
# Print the resultant Dataframe
duplicate.shape
# Let's explore the currency column. To our understanding we need to map this currencies
# to US dollars. We will add a new column in our dataset to convert all these values to Dollars.
data['Currency'].value_counts()
# To convert to US Dollars, we created a the next function:
def currency (row):
    """ This function takes a row of the dataframe as input
    and returns the a new value of 'Average Cost for two'
    in Dollars when 'Currency' column is not dollars
    """
    if row['Currency'] == 'Indian Rupees(Rs.)':
        return round(row['Average Cost for two']*0.013, 2)
    if row['Currency'] == 'Dollar($)':
        return row['Average Cost for two']
    if row['Currency'] == ('Pounds(£)'):
        return round(row['Average Cost for two']*1.37, 2)
    if row['Currency'] == 'Rand(R)':
        return round(row['Average Cost for two']*0.068, 2)
    if row['Currency'] == 'Emirati Diram(AED)':
        return round(row['Average Cost for two']*0.27, 2)
    if row['Currency'] == 'Brazilian Real(R$)':
        return round(row['Average Cost for two']*0.18, 2)
    if row['Currency'] == 'NewZealand($)':
        return round(row['Average Cost for two']*0.70, 2)
    if row['Currency'] == 'Turkish Lira(TL)':
        return round(row['Average Cost for two']*0.12, 2)
    if row['Currency'] == 'Botswana Pula(P)':
        return round(row['Average Cost for two']*0.091, 2)
    if row['Currency'] == 'Indonesian Rupiah(IDR)':
        return round(row['Average Cost for two']*0.000069, 2)
    if row['Currency'] == 'Qatari Rial(QR)':
        return round(row['Average Cost for two']*0.27, 2)
    if row['Currency'] == 'Sri Lankan Rupee(LKR)':
        return round(row['Average Cost for two']*0.0050, 2)

# Then, we applied this function for every row in the dataset
data['Average cost USD'] = data.apply (lambda row: currency(row), axis=1)
# To have some idea of the Average cost for two column we see that
# we have values equal to zero
data['Average cost USD'].value_counts(sort= False, ascending=True)
# We explored those zero values and find that 15 entries have 0 values:
print('Number of rowsxcolumns with zero values in Average cost USD:', data[data['Average cost USD']==0].shape)
# Locality verbose, locality and adress columns contain the same information
# about a restaurant:
#print(data['Locality Verbose'][0].values)
#print(data['Locality'][0].values)
#print(data['Address'][0].values)

## Therefore, for convenience we kept only Address:
data.drop(['Locality Verbose', 'Locality'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Data visualization:<jupyter_code># We explored the columns Average cost for two USD:
sns.set(font_scale=1.1)
data['Average cost USD'].value_counts().plot(kind='bar', figsize=(37, 16), rot=40)
plt.xlabel("Average Cost for two USD", labelpad=14)
plt.ylabel("Average Cost USD", labelpad=14)
plt.title("Average Cost for two USD", y=1.02)
# Visualization of the currencies in our dataset:
data['Currency'].value_counts().plot(kind='bar')
plt.xticks(rotation=40, ha='right')
# Next we explored Switch to order menu, Has table booking, and Online Delivery columns:
fig, ax = plt.subplots(1, 4, figsize = (30, 10))
sns.countplot(x = 'Has Table booking', data=data, ax = ax[0]);
print(data['Has Table booking'].value_counts())
ax[0].set_title('Has Table booking Counts');

sns.countplot(x = 'Has Online delivery', data=data, ax = ax[1]);
print(data['Has Online delivery'].value_counts())
ax[1].set_title('Has Online delivery Counts');

sns.countplot(x = 'Is delivering now', data=data, ax = ax[2]);
print(data['Is delivering now'].value_counts())
ax[2].set_title('Is delivering now Counts');

sns.countplot(x = 'Switch to order menu', data=data, ax = ax[3]);
print(data['Switch to order menu'].value_counts())
ax[3].set_title('Switch to order menu Counts');
# Price range column:
print(data['Price range'].value_counts())
data['Price range'].value_counts().plot(kind='bar', rot=0)
# Aggregate rating:
print(data['Aggregate rating'].value_counts())
data['Aggregate rating'].value_counts().plot(kind='bar', figsize=(17, 6))
plt.xticks(rotation=30, ha='right')
# We are getting to the end, I promise!!
print(data['Rating color'].value_counts())
data['Rating color'].value_counts().plot(kind='bar')
plt.xticks(rotation=30, ha='right')
# Finally we saw the locations of the restaurants using its coordinates:
# We can visualize where these restaurants are located around the globe using
# its coordinates values

# To do so, we first zip longitude and latitud and saved as a new list, and 
# these values will be added to our dataframe as coordinates Points:
geometry = [Point(xy) for xy in zip(data['Longitude'], data['Latitude'])]

# Then we create this GeoDataFrame that will allow us to plot coordinates in a map
gdf = GeoDataFrame(data, geometry=geometry)   
# We import a simple map from Geopandas, and plot together with the coordinates on top.
world = gpd.read_file(gpd.datasets.get_path('naturalearth_lowres'))
gdf.plot(ax=world.plot(figsize=(10, 6)), marker='o', color='red', markersize=8)
# Latitude and Longitude are already zipped, and for convenience
# we dropped these columns:
data.drop(['Latitude', 'Longitude'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Observations:- Our analysis indicates these restaurants are all over the world.- To our understanding we translated the 'Average Price for two' column to USD dollars- We have to consider prices that are equal to zero, and decide if we get rid of them.- The Cuisine column has nested lists, and we should disaggregate those lists so we can query restaurants based on the type of cuisines for later analysis. Further analysis:- Restructure this flattened dataset so it can be loaded into a SQL database.- Run an analysis of what are the best restaurants given a location.<jupyter_code>data.columns
## Moving forward, we disaggregated the Cuisine column, and moved 
# return list from series of comma-separated strings
def chainer(s):
  return list(chain.from_iterable(s.str.split(',')))

# calculate lengths of splits
lens = data['Cuisines'].str.split(',').map(len)

# create new dataframe, reapeting or chaining as appropiate
zomato = pd.DataFrame({
    'Restaurant ID': np.repeat(data['Restaurant ID'], lens),
    'Restaurant Name': np.repeat(data['Restaurant Name'], lens),
    'Country Code': np.repeat(data['Country Code'], lens),
    'City':np.repeat(data['City'], lens),
    'Address':np.repeat(data['Address'], lens),
    'Cuisines':chainer(data['Cuisines']),
    'Average Cost for two':np.repeat(data['Average Cost for two'], lens), 
    'Currency':np.repeat(data['Currency'], lens), 
    'Has Table booking':np.repeat(data['Has Table booking'], lens),
    'Has Online delivery':np.repeat(data['Has Online delivery'], lens), 
    'Is delivering now':np.repeat(data['Is delivering now'], lens), 
    'Switch to order menu':np.repeat(data['Switch to order menu'], lens),
    'Price range':np.repeat(data['Price range'], lens), 
    'Aggregate rating':np.repeat(data['Aggregate rating'], lens), 
    'Rating color':np.repeat(data['Rating color'], lens), 
    'Rating text':np.repeat(data['Rating text'], lens),
    'Votes': np.repeat(data['Votes'], lens),
    'Average cost USD': np.repeat(data['Average cost USD'], lens),
    'geometry': np.repeat(data['geometry'], lens)
})

# This should be exported to github.
zomato.to_csv('/Users/josegarcia/Desktop/SCHOOL/DATA_BASE_MNGT/FINAL PROJECT/cleaned_zomato.csv')
zomato.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12649826646854878, 'fraction_numerical': 0.011391778107974244, 'mean_word_length': 5.25914445133292, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '333845', 'license': 'CC-BY-3.0', 'n_tokens_mistral': 3153, 'n_tokens_neox': 2991, 'n_words': 1119, 'path': 'assets/EMSE6586/Exploratory_Data_Analysis.ipynb', 'repo_name': 'ngau9567/ngau9567.github.io', 'size': '10095'}