<jupyter_start><jupyter_text>RestaurantsR1 = 1 chefmozaccepts.csvR2 = 2 chefmozcuisine.csvR3 = 3 chefmozhours4.csvR4 = 4 chefmozparking.csvR5 = 5 geoplaces2.csv UserU6 = 6 usercuisine.csvU7 = 7 userpayment.csvU8 = 8 userprofile.csv User-Item-RatingU9 = 9 rating_final.csv<jupyter_code>#1 chefmozaccepts.csv 2 chefmozcuisine.csv 3 chefmozhours4.csv 4 chefmozparking.csv 5 geoplaces2.csv
payment_methods_url = 'https://raw.githubusercontent.com/JeanFraga/DS-Unit-1-Sprint-4-Linear-Algebra/master/Story%20Telling%20project/RCdata/chefmozaccepts.csv'
cuisine_url = 'https://raw.githubusercontent.com/JeanFraga/DS-Unit-1-Sprint-4-Linear-Algebra/master/Story%20Telling%20project/RCdata/chefmozcuisine.csv'
hours_operating_url = 'https://raw.githubusercontent.com/JeanFraga/DS-Unit-1-Sprint-4-Linear-Algebra/master/Story%20Telling%20project/RCdata/chefmozhours4.csv'
parking_url = 'https://raw.githubusercontent.com/JeanFraga/DS-Unit-1-Sprint-4-Linear-Algebra/master/Story%20Telling%20project/RCdata/chefmozparking.csv'
geo_places_url = 'https://raw.githubusercontent.com/JeanFraga/DS-Unit-1-Sprint-4-Linear-Algebra/master/Story%20Telling%20project/RCdata/geoplaces2.csv'
#6 usercuisine.csv 7 userpayment.csv 8 userprofile.csv
user_cuisine_url = 'https://raw.githubusercontent.com/JeanFraga/DS-Unit-1-Sprint-4-Linear-Algebra/master/Story%20Telling%20project/RCdata/usercuisine.csv'
user_payment_url = 'https://raw.githubusercontent.com/JeanFraga/DS-Unit-1-Sprint-4-Linear-Algebra/master/Story%20Telling%20project/RCdata/userpayment.csv'
user_profile_url = 'https://raw.githubusercontent.com/JeanFraga/DS-Unit-1-Sprint-4-Linear-Algebra/master/Story%20Telling%20project/RCdata/userprofile.csv'
#9 rating_final.csv
rating_final_url = 'https://raw.githubusercontent.com/JeanFraga/DS-Unit-1-Sprint-4-Linear-Algebra/master/Story%20Telling%20project/RCdata/rating_final.csv'
import pandas as pd
payment_methods = pd.read_csv(payment_methods_url, na_values= '?')
print(payment_methods.isnull().sum())
print(payment_methods.shape)
print(payment_methods.info())
payment_methods.head(10)
payment_methods.rename(str.lower, axis='columns', inplace=True)
payment_methods['rpayment'] = payment_methods['rpayment'].str.lower()
payment_methods.rpayment.value_counts()
#because no one payed with these types of payment I will go ahead and take them out of the final df
not_useful_payments = payment_methods.rpayment.value_counts()
not_useful_payments = not_useful_payments[not_useful_payments<130].index.tolist()

for i in range(len(not_useful_payments)):
    payment_methods=payment_methods[~payment_methods.rpayment.str.contains(not_useful_payments[i])]
    

# With this dataset I managed to reduce from 12 columns to 6 by removing unused types of payment
# Please reference to user_payment(U7) below for more information.

renamed_payments = {'rpayment_american_express': 'accepts_amex', 
                    'rpayment_bank_debit_cards': 'accepts_debit',
                   'rpayment_cash':'accepts_cash',
                   'rpayment_mastercard-eurocard': 'accepts_mastercard',
                   'rpayment_visa': 'accepts_visa'}
R1 = pd.get_dummies(payment_methods,columns=['rpayment']).groupby('placeid',as_index=False).sum()
R1.rename(columns=renamed_payments, inplace=True)
R1
cuisine = pd.read_csv(cuisine_url, na_values= '?')
print(cuisine.isnull().sum())
print(cuisine.shape)
cuisine.head(10)
cuisine.rename(str.lower, axis='columns', inplace=True)
cuisine['rcuisine'] = cuisine['rcuisine'].str.lower()
cuisine.rcuisine.value_counts()[:30]
# I keep this variable so I can engineer a column with the total number of cuisines a restaurant has.
total_cuisine = (pd.get_dummies(cuisine,columns=['rcuisine']).groupby('placeid',as_index=False).sum()
                  .loc[:, 'rcuisine_afghan':].sum(axis=1))
# In order to reduce dimensionality I will go ahead and only keep the top 30
# then make an additional column that holds the sum of the cuisines to retain some of the use.
not_useful_cuisine = cuisine.rcuisine.value_counts()[30:].index.tolist()
#not_useful_cuisine = not_useful_cuisine[not_useful_cuisine<13].index.tolist()

for i in range(len(not_useful_cuisine)):
    cuisine=cuisine[~cuisine.rcuisine.str.contains(not_useful_cuisine[i])]
    

# With this dataset I managed to reduce dimensionality from 60 to 32
R2 = pd.get_dummies(cuisine,columns=['rcuisine']).groupby('placeid',as_index=False).sum()
R2['rtotal_cuisine'] = total_cuisine
R2
# for the time being I will go ahead and forgoe the times becuase it will take me long to engineer
# R3
parking = pd.read_csv(parking_url, na_values= '?')
print(parking.isnull().sum())
print(parking.shape)
parking.head(10)
parking.rename(str.lower, axis='columns', inplace=True)
parking['parking'] = parking['parking_lot'].str.lower().copy()
for row in range(len(parking)):
    parking.parking[row]=parking.parking_lot[row].split(' ')[0]
parking.parking_lot.value_counts()
# For the time being I will not reduce dimensionality for this dataset because it is straightforward
R4 = pd.get_dummies(parking,columns=['parking']).groupby(['placeid'],as_index=False).sum()
print(R4.shape)
R4.head(10)
#This data set did not read for some reason. I had to make some modifications to the raw file to replace foreign characters.
geo_places = pd.read_csv(geo_places_url, na_values= '?')
print(geo_places.isnull().sum())
print(geo_places.shape)
geo_places.head(10).T
geo_places.rename(str.lower, axis='columns', inplace=True)

# I will cut geo_places because it is a unique value throughout.
print(geo_places['the_geom_meter'].value_counts(), '\n')

#We can see that name are pretty much unique all throughout. At this moment I will not try to make anything out of this column.
print(geo_places['name'].value_counts(), '\n')

#We can see that a few restaurants are near each other when they have the same address.
#because of possible typos that we have seen on other columns I decided Longitude and Latitude is a better measure for distance.
print(geo_places['address'].value_counts(),'\n')

#We can see that city might be a useful feature to clean up. I will start by removing dots, making it all lower caps.
before_changes = geo_places['city'].value_counts()
print(before_changes, '\n')
geo_places['city'] = geo_places['city'].str.replace('.','')
geo_places['city'] = geo_places['city'].str.replace(' ','')
geo_places['city'] = geo_places['city'].str.lower()
geo_places['city'] = geo_places['city'].str.replace('cdvictoria','victoria')
geo_places['city'] = geo_places['city'].str.replace('ciudadvictoria','victoria')
geo_places['city'] = geo_places['city'].str.replace('sanluispotosi','sanluispotos')
geo_places['city'] = geo_places['city'].str.replace('slp','sanluispotos')
geo_places['city'] = geo_places['city'].fillna('sanluispotos')
#after cleaning up this column
print(geo_places['city'].value_counts(),'\n')

# This looks like a column that will need cleaning up as well but similar to city.
print(geo_places['state'].value_counts(),'\n')
geo_places['state'] = geo_places['state'].str.replace('.','')
geo_places['state'] = geo_places['state'].str.replace(' ','')
geo_places['state'] = geo_places['state'].str.lower()
geo_places['state'] = geo_places['state'].str.replace('slp','sanluispotosi')
geo_places['state'] = geo_places['state'].str.replace('sanluispotosi','sanluispotos')
geo_places['state'] = geo_places['state'].fillna('sanluispotos')
# There is a state called Mexico in Mexico. I have to make sure that this value is correct later.
print(geo_places['state'].value_counts(),'\n')

#we can see that this is inteded as a boolean
print(geo_places['franchise'].value_counts(), '\n')
geo_places['franchise'] = geo_places['franchise'].str.replace('f','0')
geo_places['franchise'] = geo_places['franchise'].str.replace('t','1')
geo_places['franchise'] = geo_places['franchise'].astype('int64').astype('bool')
print(geo_places['franchise'].value_counts(), '\n')

# This column has 3 unique values and does not need work.
print(geo_places['other_services'].value_counts(), '\n')

# This column I create from URL to evaluate if the restaurant has a url or not
geo_places['has_url'] = geo_places['url'].copy()
geo_places['has_url'].loc[~geo_places['has_url'].isnull()] = 1  # not nan
geo_places['has_url'].loc[geo_places['has_url'].isnull()] = 0   # nan
geo_places['has_url'] = geo_places['has_url'].astype('int64').astype('bool')

# In order to create some logical order to the column 'price' I will go ahead and encode it now.
# 'low': 0, 'medium':1, 'high':2
geo_places['price'].replace('low', 0, inplace=True)
geo_places['price'].replace('medium', 1, inplace=True)
geo_places['price'].replace('high', 2, inplace=True)
#I'm considering using URL as a way to predict if it has a url or not.
#Restaurants without a URL might just not have a website.
#from looking at the documentation for the dataset I can see that there are no other columns to clean up.
R5 = geo_places.drop(columns = ['fax', 'zip','url','country', 'name','the_geom_meter','address'])
print(R5.shape)
R5.head(30)
user_cuisine = pd.read_csv(user_cuisine_url,na_values= '?')
print(user_cuisine.isnull().sum())
print(user_cuisine.shape)
user_cuisine.head(10)
# At this moment I will not reduce dimensionality for this dataset because I may be able to extract some use for it
user_cuisine.rename(str.lower, axis='columns', inplace=True)
user_cuisine.rename(columns={'rcuisine':'ucuisine'}, inplace=True)
user_cuisine['ucuisine'] = user_cuisine['ucuisine'].str.lower().copy()
user_cuisine['userid'] = user_cuisine['userid'].str.lower()
user_cuisine.ucuisine.value_counts()[:30]
# I keep this column so I can engineer a new column with the total number of cuisines the users selected
total_cuisine = (pd.get_dummies(user_cuisine,columns=['ucuisine']).groupby('userid',as_index=False).sum()
                  .loc[:, 'ucuisine_afghan':].sum(axis=1))
# In order to reduce dimensionality I will go ahead and only keep the top 30
# then make an additional column that holds the sum of the ucuisines to retain some of the use.
not_useful_cuisine = user_cuisine.ucuisine.value_counts()[30:].index.tolist()

for i in range(len(not_useful_cuisine)):
    user_cuisine=user_cuisine[~user_cuisine.ucuisine.str.contains(not_useful_cuisine[i])]
    

# With this dataset I managed to reduce dimensionality from 105 to 32
U6 = pd.get_dummies(user_cuisine,columns=['ucuisine']).groupby(['userid'],as_index=False).sum()
U6['utotal_cuisine'] = total_cuisine
print(U6.shape)
U6.head(10)
user_payment = pd.read_csv(user_payment_url,na_values= '?')
print(user_payment.isnull().sum())
print(user_payment.shape)
user_payment.head(10)
user_payment.rename(str.lower, axis='columns', inplace=True)
user_payment['upayment'] = user_payment['upayment'].str.lower()
user_payment['userid'] = user_payment['userid'].str.lower()<jupyter_output><empty_output><jupyter_text>With this I have made sure to see what type of payments I should include in my final predictions.I noticed that several types of payment specified by the restaurant where not used by the clients and I assume will not affect the target in any way.<jupyter_code>payment = payment_methods.rpayment.value_counts().index.tolist()
for i in range(len(payment)):
    if len(user_payment.upayment[user_payment.upayment == payment[i]].value_counts()) == 1:
        print(user_payment.upayment[user_payment.upayment == payment[i]].value_counts(), '\n')
    else:
        print(f'{payment[i]} has no values \n')
# Here I create a dictionary to make reading these features easier and also take less space
renamed_payments = {'upayment_american_express': 'upays_amex', 
                    'upayment_bank_debit_cards': 'upays_debit',
                   'upayment_cash':'upays_cash',
                   'upayment_mastercard-eurocard': 'upays_mastercard',
                   'upayment_visa': 'upays_visa'}


U7 = pd.get_dummies(user_payment,columns=['upayment']).groupby(['userid'],as_index=False).sum()
U7.rename(columns=renamed_payments, inplace=True)
print(U7.shape)
U7.head(10)
# This dataset looks for the most part clean. I assume that users did not want to specify certain things about their life.
# Which is why we might see NaNs in a couple of the columns. So I will opt to replace them with 'unkown' to look for any trends.
user_profile = pd.read_csv(user_profile_url,na_values= '?')
print(user_profile.isnull().sum())
print(user_profile.shape)
user_profile.head(10)
user_profile.dtypes
user_profile.rename(str.lower, axis='columns', inplace=True)
user_profile['userid'] = user_profile['userid'].str.lower()

# I will make smoker a bool to make sure it is not treated as an object
user_profile.smoker = user_profile.smoker.astype('bool')

U8 = user_profile.copy()
for column in U8.columns:
    #print(column)
    U8[column] = U8[column].fillna('unknown')

# I make add "u" to longitude and latitude to differentiate it from the restaurants.
U8.rename(columns={"latitude": "ulatitude", "longitude": "ulongitude"}, inplace=True)

# In order to create some logical order to the column 'budget' I will go ahead and encode it now.
# 'unknown': -1, 'low': 0, 'medium':1, 'high':2
U8['budget'].replace('unknown', -1, inplace=True)
U8['budget'].replace('low', 0, inplace=True)
U8['budget'].replace('medium', 1, inplace=True)
U8['budget'].replace('high', 2, inplace=True)

U8.head()
rating_final = pd.read_csv(rating_final_url, na_values= '?')
rating_final.rename(str.lower, axis='columns', inplace=True)
rating_final['userid'] = rating_final['userid'].str.lower()
U9 = rating_final.copy()
print(rating_final.isnull().sum())
print(rating_final.shape)
rating_final
# after merging I can succesfully say I have engineered 116 features down from 602
final = (pd.merge(U9, U8, how='left', on=['userid'])
        .merge(U7, how='left', on=['userid'])
        .merge(U6, how='left', on=['userid'])
        .merge(R5, how='left', on=['placeid'])
        .merge(R4, how='left', on=['placeid'])
        .merge(R2, how='left', on=['placeid'])
        .merge(R1, how='left', on=['placeid']))
print(final.shape)

#I concluded that I can fill 'NaN' values with '0' because it will not affect my final results.
for column in final.columns:
    final[column] = final[column].fillna(0)
print(final.isnull().sum())

final.head()<jupyter_output>(1161, 116)
userid                0
placeid               0
rating                0
food_rating           0
service_rating        0
                     ..
accepts_amex          0
accepts_debit         0
accepts_cash          0
accepts_mastercard    0
accepts_visa          0
Length: 116, dtype: int64<jupyter_text>from testing the null values with these lines on merge I concluded that I can fill in 'NaN' with 0 and it will not affect my final resultpd.merge(U9, U8, how='left', on=['userid']).merge(U7, how='left', on=['userid']).isnull().sum()pd.merge(U9, U8, how='left', on=['userid']).merge(U6, how='left', on=['userid']).isnull().sum()pd.merge(U9, U8, how='left', on=['userid']).merge(R5, how='left', on=['placeid']).isnull().sum()pd.merge(U9, U8, how='left', on=['userid']).merge(R4, how='left', on=['placeid']).isnull().sum()pd.merge(U9, U8, how='left', on=['userid']).merge(R2, how='left', on=['placeid']).isnull().sum()pd.merge(U9, U8, how='left', on=['userid']).merge(R1, how='left', on=['placeid']).isnull().sum()<jupyter_code># with this save file I am able to update the file if I need to make changes to the features for the models I will run.
final.to_csv('Restaurant_Consumer_Data_merged',index=False)
rpayment_plot=payment_methods.rpayment.value_counts().plot.bar(title="Payments Accepted")
rpayment_plot.set_xlabel('payments mode',size=15)
rpayment_plot.set_ylabel('count',size=15);
upayment_plot=user_payment.upayment.value_counts().plot.bar(title="User payments")
upayment_plot.set_xlabel('User payments',size=15)
upayment_plot.set_ylabel('count',size=15)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14500157678965625, 'fraction_numerical': 0.016587827183853675, 'mean_word_length': 5.896911700739452, 'pattern_counts': {'":': 2, '<': 14, '<?xml version=': 0, '>': 12, 'https://': 9, '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': '354519', 'license': 'MIT', 'n_tokens_mistral': 5595, 'n_tokens_neox': 5379, 'n_words': 1219, 'path': 'notebooks/Restaurant Data with Consumer Ratings Features.ipynb', 'repo_name': 'JeanFraga/DS8-Build_Week-1', 'size': '15855'}