import pandas as pd
import numpy as np
# read json, lines=True because each line is a single json
business = pd.read_json('Yelp/yelp_academic_dataset_business.json', lines=True)
print(business.shape)
business.head()

# extract five consecutive digits after a white space at the end of the address
business['zipcode'] = business['full_address'].str.extract('(\s\d{5}$)', expand=True)
business[['zipcode', 'full_address']].tail()

"""
Explanation: For excerises 1-4, use the Yelp business json file. For exercises 5-6, use the Yelp review json file.
Exercise 1: Create a new column that contains only the zipcode.
End of explanation
"""


# create a set containing all the possible categories
cat = set([])

for row in business['categories']:
    if 'Restaurants' in row:
        cat = cat.union(set(row))

cat = cat.difference(set(['Restaurants']))

# function that creates a dictionary based on the previous categories and sets the values to 1 if the category is presenti in the argument
def cat_dict(row):
    if 'Restaurants' in row:
        d = dict.fromkeys(cat, 0)
        for e in row:
            if e != 'Restaurants':
                d[e] = 1
        return d
    else:
        return np.NaN

business['Restaurant_type'] = business['categories'].apply(cat_dict)

business[['Restaurant_type', 'categories']].head()

"""
Explanation: Exercise 2: The table contains a column called 'categories' and each entry in this column is populated by a list. We are interested in those businesses that restaurants. Create a new column 'Restaurant_type' that contains a description of the restaurant based on the other elements of 'categories.
That is, if we have '[Sushi Bars, Japanese, Restaurants]' in categories the 'Restaurant_type will be '{'SushiBars': 1, 'Japanese': 1, 'Mexican': 0, ...}'
End of explanation
"""


# if the value is a boolean replaces the spaces in the key with underscores and returns 0-1 instead of True-False
# else replaces the spaces in the key with underscores, adds the value to the key and returns 1
def replace_entry(key, value):
    if type(value) is bool:
        if value:
            return key.replace(' ', '_'), 1
        else:
            return key.replace(' ', '_'), 0
    else:
        return key.replace(' ', '_') + '_' + str(value).replace(' ', '_'), 1

# returns the dictionary after applying the function above, navigates through the dictionary inside the values
def clean_dict(dct):
    
    out_dct = {}

    for key in dct:
        value = dct[key]
        if type(value) is dict:
            for subkey in value:
                new_key, new_value = replace_entry(subkey, value[subkey])
                out_dct[key.replace(' ', '_') + '_' + new_key] = new_value
        else:
            new_key, new_value = replace_entry(key, value)
            out_dct[new_key] = new_value

    return out_dct

business['attributes'] = business['attributes'].apply(clean_dict)

business[['attributes']].head(10)

"""
Explanation: Exercise 3: Lets clean the 'attributes' column. The entries in this column are dictionaries. We need to do two things:
1) Turn all the True or False in the dictionary to 1s and 0s.
2) There are some entries within dictionaries that are dictionaries themselves, lets turn the whole entry into just one dictionary, for example if we have
'{'Accepts Credit Cards': True, 'Alcohol': 'none','Ambience': {'casual': False,'classy': False}}'
then turn it into
'{'Accepts Credit Cards':1, 'Alcohol_none': 1, 'Ambience_casual': 0, 'Ambience_classy': 0}'.
There might be other entries like {'Price Range': 1} where the values are numerical so we might want to change that into {'Price_Range_1': 1}.
The reason we modify categorical variables like this is that machine learning algorithms cannot interpret textual data like "True" and "False". They need numerical inputs such as 1 and 0.
End of explanation
"""


# dct = {'Friday': {'close': '21:00', 'open': '11:00'},
#  'Monday': {'close': '21:00', 'open': '11:00'},
#  'Thursday': {'close': '21:00', 'open': '11:00'},
#  'Tuesday': {'close': '21:00', 'open': '11:00'},
#  'Wednesday': {'close': '21:00', 'open': '11:00'}}

dct = {'Friday': {'close': '02:00', 'open': '11:00'},
 'Monday': {'close': '02:00', 'open': '11:00'},
 'Saturday': {'close': '02:00', 'open': '12:00'},
 'Sunday': {'close': '22:00', 'open': '12:00'},
 'Thursday': {'close': '02:00', 'open': '11:00'},
 'Tuesday': {'close': '02:00', 'open': '11:00'},
 'Wednesday': {'close': '02:00', 'open': '11:00'}}

day_map = {'Friday': '2017-08-04',
 'Monday': '2017-07-31',
 'Saturday': '2017-08-05',
 'Sunday': '2017-08-06',
 'Thursday': '2017-08-03',
 'Tuesday': '2017-08-01',
 'Wednesday': '2017-08-02'}

def daily_hours(dct):
    # initialize output
    hours = dict.fromkeys(range(0, 7), 0)
    for day in dct:
        # set start datetime, end datetime and end of day datetime
        start = pd.to_datetime(day_map[day] + ' ' + dct[day]['open'])
        end = pd.to_datetime(day_map[day] + ' ' + dct[day]['close'])
        end_of_day = pd.to_datetime(day_map[day] + ' 00:00:00') + pd.Timedelta(1, unit='D')
        # if closing hours is in the next day add one to the end and add to output the two days
        if dct[day]['close'] < dct[day]['open']:
            end = end + pd.Timedelta(1, unit='D')
            diff = end_of_day - start
            hours[start.dayofweek] += int(np.floor(diff.seconds / 3600))
            diff = end - end_of_day
            hours[end_of_day.dayofweek] += int(np.floor(diff.seconds / 3600))
        # else add only the current day
        else:
            diff = end - start
            hours[start.dayofweek] += int(np.floor(diff.seconds / 3600))
    return hours[0], hours[1], hours[2], hours[3], hours[4], hours[5], hours[6]

business['Monday'], business['Tuesday'], business['Wednesday'], business['Thursday'], business['Friday'], business['Saturday'], business['Sunday'] = zip(*business['hours'].map(daily_hours))

#daily_hours(dct)

# solution code:
FMT = '%H:%M'

def Time(row, date):
    try:
        closing = pd.datetime.strptime(row['hours'][date]['close'], FMT)
        opening = pd.datetime.strptime(row['hours'][date]['open'], FMT)
        time = closing - opening
        
        if time < pd.Timedelta(days=0):
            time += pd.Timedelta(days=1)
        
    except:
        time = pd.Timedelta(days=0)
    
    return time

business.apply(lambda x: Time(x, 'Monday'), axis=1).head(10)

business[['hours', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']].head(10)

"""
Explanation: Exercise 4: Create a new column for every day of the week and fill it with the amount of hours the business is open that day.
Your approach should handle businesses that stay open late like bars and nightclubs.
End of explanation
"""


review = pd.read_json('Yelp/yelp_academic_dataset_review.json', lines=True)
print(review.shape)
review.head()

average_review = review[['business_id', 'stars']].groupby('business_id').mean()

average_review.head()

"""
Explanation: Exercise 5: Create a table with the average review for a business.
You will need to pull in a new json file and merge DataFrames for the next 2 exercises.
End of explanation
"""


df = pd.merge(business[['business_id', 'name' , 'Restaurant_type', 'Friday', 'Saturday', 'attributes', 'zipcode']], average_review, left_on='business_id', right_index=True, how='inner')
df.drop('business_id', axis=1, inplace=True)
names = ['Business_Name', 'Restaurant_type', 'Friday hours', 'Saturday hours', 'Attributes', 'Zipcode', 'Average Rating']
df.columns = names
#remove restaurants
df = df[df['Restaurant_type'] == df['Restaurant_type']]
df.head()

"""
Explanation: Exercise 6: Create a new table that only contains restaurants with the following schema:
Business_Name | Restaurant_type | Friday hours | Saturday hours | Attributes | Zipcode | Average Rating
End of explanation
"""


