<jupyter_start><jupyter_text>Get Latitude & Longitude from Yelp API<jupyter_code>search_business = 'The Counting Room' # Not the actual Restaurant 
location = 'Brooklyn, NY'
# Make Yelp API Call to get Latitude & Longitude for Business
loc = Location(search_business, location)
lat, long = loc.lat_long()<jupyter_output><empty_output><jupyter_text>Import / Clean / Prep File<jupyter_code>w_start = '2017-01-01'
w_end = '2019-06-30'

# Restaurant File
sales_file = 'csv/rest_1_both_pos_by_check.csv'

# Complete Daily Reservations File
resy_file = 'csv/rest_1_covers_new.csv'

# Weather File
weather_csv_file = f'csv/weather_{w_start}_to_{w_end}.csv'
 # Read in Sales File
data = pd.read_csv(sales_file, index_col = 'date', parse_dates=True)
sales_df = pd.DataFrame(data)
def prep_sales_df(df):
    
        # Dinner Only
        df = df[df.day_part == 'Dinner']
        
        # Fill NaN
        df.fillna(0, inplace=True)
        
        # Filter Out $0 Sales
        df = df[df.net_sales > 0]
        
        # Create Unique Check ID
        df['check_id'] = df.index.strftime('%Y%m%d') + '-' + df.index.strftime('%H%M') + '-' + \
                    df['check_no'].astype(int).astype(str) + '-' + df['revenue_center'].str[0] + '-' + pd.factorize(df['net_sales'])[0].astype(str)
         
            
        df.check_no = df.check_no.astype(int)
        
        # Drop Covers, Day Part, & Check_No
        df = df.drop(['covers', 'day_part'], axis=1)
    
        df.index = df.index.normalize()
        
        return df
    
sales_df = prep_sales_df(sales_df)
sales_df.head()
# Send this Long Format File to CSV for Dashboards
sales_df.to_csv('csv/rest_1_sales_by_check_long_0117_0619.csv')<jupyter_output><empty_output><jupyter_text>High Level View of Annual Revenue<jupyter_code>sales_df.groupby([sales_df.index.year, 'revenue_center'])['net_sales'].agg({'sum': 'sum', 'check_avg': 'mean'})
dfw = pd.read_csv(weather_csv_file, index_col='date', parse_dates=True)
dfw.head()
dfr = pd.read_csv(resy_file, index_col='date', parse_dates=True)

def prep_resy_df(df):
                  
        # Fill NaN
        df.fillna(0, inplace=True)
        
        return df
    
dfr = prep_resy_df(dfr)
rental_fees = pd.read_csv('csv/pos_1_rental_fees.csv')
rental_fees.head()
def clean_rental_fees(df):
    
    df.columns = ['date', 'rev_center', 'check_no', 'rental_fees']
    df.date = pd.to_datetime(df.date)
    
    df.rental_fees = [float(x.strip('$').replace(',', '')) for x in df.rental_fees]
    
    df.rev_center = df.rev_center.replace('Patio', 'Outside')
    
    df = df.set_index('date')
    
    return df

rental_fees = clean_rental_fees(rental_fees)
rental_fees.head()<jupyter_output><empty_output><jupyter_text>Load Data to AWS RDS  Connect Helper Function<jupyter_code>def connect():
    
    # Set up a connection to the postgres server.
    conn_string = "host="+ PGHOST +" port="+ "5432" +" dbname="+ PGDATABASE +" user=" + PGUSER \
                  +" password="+ PGPASSWORD
    
    conn = psycopg2.connect(conn_string)
    print(":) :) :)")

    # Create a cursor object
    cursor = conn.cursor()
    
    return conn, cursor
conn, cursor = connect()
conn.close()<jupyter_output><empty_output><jupyter_text>Drop Table Helper Function<jupyter_code>def drop_table(table_name):
    
    conn, cursor = connect()
    
    cursor.execute(f'DROP TABLE {table_name}')
    conn.commit()
    conn.close()
drop_table('weather')<jupyter_output>:) :) :)<jupyter_text>Create Sales & Reservations Tables<jupyter_code>create_rev_center_table = """
    CREATE TABLE IF NOT EXISTS rev_center(
        id INTEGER PRIMARY KEY NOT NULL,
        name VARCHAR NOT NULL
    )"""

create_check_table = """
    CREATE TABLE IF NOT EXISTS checks(
        check_id VARCHAR(36) PRIMARY KEY NOT NULL,
        date DATE,
        check_no INTEGER,
        rev_center_id INTEGER REFERENCES rev_center(id),
        net_sales NUMERIC (7, 2)
    )"""

create_resy_table = """
    CREATE TABLE IF NOT EXISTS reservations (
        id INTEGER PRIMARY KEY NOT NULL,
        date DATE,
        inside_covers INTEGER,
        outside_covers INTEGER,
        reserved_covers INTEGER,
        walkin_covers INTEGER,
        waitlist_covers INTEGER,
        no_show_covers INTEGER,
        no_show_parties INTEGER
    )
    """

create_rental_fees_table = """
    CREATE TABLE IF NOT EXISTS rental_fees (
        check_no INTEGER PRIMARY KEY,
        date DATE,
        rev_center_id INTEGER REFERENCES rev_center(id),
        rental_fees NUMERIC(7, 2)
    )"""

create_weather_table = """
        CREATE TABLE IF NOT EXISTS weather ( 
            DATE DATE, 
            date_id INT PRIMARY KEY, 
            apparent_temperature NUMERIC (4, 2), 
            humidity NUMERIC (3, 2), 
            precip_intensity_max NUMERIC (5, 4), 
            precip_max_time TIME, 
            precip_prob NUMERIC (3, 2), 
            precip_type TEXT, 
            pressure NUMERIC (6,2), 
            summary TEXT, 
            temperature NUMERIC (4, 2)
            )
            """<jupyter_output><empty_output><jupyter_text>Create Table Helper Function<jupyter_code>def create_table(create_query):
    
    conn, cursor = connect()
    
    cursor.execute(f'{create_query}')
    conn.commit()
    conn.close()
create_table(create_weather_table)<jupyter_output>:) :) :)<jupyter_text>Populate Databases<jupyter_code>rev_centers = {'Dining Room': 1, 'Bar': 2, 'PDR': 3, 'Window': 4, 'Outside': 5}
def populate_rev_center(df):
    
    conn, cursor = connect()
    
    for r, i in rev_centers.items():
        cursor.execute("INSERT INTO rev_center (id, name) VALUES (%s, %s)", (i, r) )
        conn.commit()
        conn.close()
    
populate_rev_center(sales_df)
                
def populate_checks(df):
    
    conn, cursor = connect()
    
    for row in range(len(df)):
        
        cursor.execute("INSERT INTO checks (check_id, date, check_no, rev_center_id, net_sales) VALUES (%s, %s, %s, %s, %s)",
                      (df.iloc[row]['check_id'],
                       pd.to_datetime(df.index[row]),
                       df.iloc[row]['check_no'],
                       rev_centers[df.iloc[row]['revenue_center']],
                       df.iloc[row]['net_sales']) )
        conn.commit()
        conn.close()
        
populate_checks(sales_df)
def populate_reservations(df):
    
    conn, cursor = connect()
    
    for row in range(len(df)):
        
        cursor.execute("""
            INSERT INTO reservations (id, date, inside_covers, outside_covers, reserved_covers,
            walkin_covers, waitlist_covers, no_show_covers, no_show_parties) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)""",
                (df.index[row].strftime('%Y%m%d'),
                pd.to_datetime(df.index[row]),
                df.iloc[row]['inside_covers'],
                df.iloc[row]['outside_covers'],
                df.iloc[row]['reserved_covers'],
                df.iloc[row]['walkin_covers'],
                df.iloc[row]['waitlist_covers'],
                df.iloc[row]['no_show_covers'],
                df.iloc[row]['no_show_parties']) )
        conn.commit()
    conn.close()
        
populate_reservations(dfr)
def populate_rental_fees(df):
    
    conn, cursor = connect()
    
    for row in range(len(df)):
        
        cursor.execute("INSERT INTO rental_fees (check_no, date, rev_center_id, rental_fees) VALUES (%s, %s, %s, %s)",
                      (df.iloc[row]['check_no'],
                       pd.to_datetime(df.index[row]),
                       rev_centers[df.iloc[row]['rev_center']],
                       df.iloc[row]['rental_fees']) )
        conn.commit()
    conn.close()
        
populate_rental_fees(rental_fees)
def populate_weather(df):
    
    conn, cursor = connect()
    
    for row in range(len(df)):
        
        cursor.execute("""
            INSERT INTO weather (date, date_id, apparent_temperature, humidity, precip_intensity_max, precip_max_time,
            precip_prob, precip_type, pressure, summary, temperature) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
                (pd.to_datetime(df.index[row]),
                 df.index[row].strftime('%Y%m%d'),
                 df.iloc[row]['apparent_temperature'],
                 df.iloc[row]['humidity'],
                 df.iloc[row]['precip_intensity_max'],
                 df.iloc[row]['precip_max_time'],
                 df.iloc[row]['precip_prob'],
                 df.iloc[row]['precip_type'],
                 df.iloc[row]['pressure'],
                 df.iloc[row]['summary'],
                 df.iloc[row]['temperature']) )
        conn.commit()
    conn.close()

populate_weather(dfw)<jupyter_output>:) :) :)
{'fraction_non_alphanumeric': 0.13179892362303905, 'fraction_numerical': 0.007214015802129852, 'mean_word_length': 2.509039775010044, 'pattern_counts': {'":': 1, '<': 30, '<?xml version=': 0, '>': 31, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '349011', 'license': 'MIT', 'n_tokens_mistral': 3140, 'n_tokens_neox': 2942, 'n_words': 568, 'path': 'load_aws_rds.ipynb', 'repo_name': 'maks-p/final_project_bi', 'size': '8733'}