# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import requests, json, os
import psycopg2
from datetime import datetime
from dateutil import tz
import pandas as pd

requests.__version__

pd.__version__

# # Make Connection to Google cloud function

import urllib.request

QUERY_FUNC = 'https://us-central1-divvy-bike-shari-1562130131708.cloudfunctions.net/query_from_divvy_cloudsql'

# %%time
reslst = requests.post(QUERY_FUNC, json={'stationid': '35, 192'}).content.decode('utf-8')


def to_dataframe(raw):
    lst_of_lst = [v.split(',') for v in raw.split('\n')]
    df = pd.DataFrame(lst_of_lst, columns=['timestamp', 'stationid', 'bikes_avail', 'docks_avail'])
    return df


reslst = requests.post(QUERY_FUNC, json={'stationid': '35,192,100,2'}).content.decode('utf-8')

df = to_dataframe(reslst)

df.shape

df.info()

tmp = df[df.stationid == '35'].copy()

tmp['timeindex'] = pd.to_datetime(df['timestamp']).dt.tz_localize('utc').dt.tz_convert('US/Central')
tmp['month'] = tmp.timeindex.apply(lambda x: x.month)
tmp['day'] = tmp.timeindex.apply(lambda x: x.day)
tmp['hour'] = tmp.timeindex.apply(lambda x: x.hour)

tmp.bikes_avail = tmp.bikes_avail.astype('int')
tmp.docks_avail = tmp.docks_avail.astype('int')

min_df = tmp.groupby(['month', 'day', 'hour'])[['timeindex', 'bikes_avail', 'docks_avail']].min()\
        .reset_index().rename(columns={"bikes_avail": "min_bikes", "docks_avail": "min_docks"})

max_df = tmp.groupby(['month', 'day', 'hour'])[['bikes_avail', 'docks_avail']].max()\
        .reset_index().rename(columns={"bikes_avail": "max_bikes", "docks_avail": "max_docks"})

ave_df = tmp.groupby(['month', 'day', 'hour'])[['bikes_avail', 'docks_avail']].mean()\
        .reset_index().rename(columns={"bikes_avail": "ave_bikes", "docks_avail": "ave_docks"})

min_df.merge(max_df, on=['month', 'day', 'hour']).merge(ave_df, on=['month', 'day', 'hour'])

# +
# df.bikes_avail.rolling(12).min()
# -

# # Visualize data

import plotly
from plotly.offline import iplot, plot

plotly.__version__

# # Query Live Station Status (Up to now)

DIVVY_STATION_URL = 'https://gbfs.divvybikes.com/gbfs/en/station_information.json'

# +
res = requests.get(DIVVY_STATION_URL)
jsonres = res.json()

station_json = json.dumps(jsonres['data']['stations'])
station_status_df = pd.read_json(station_json)
# -

cleaned_stationdata = station_status_df[['capacity', 'lat', 'lon', 'station_id', 'name', 'short_name']]
cleaned_stationdata.to_csv('station.csv')

cleaned_stationdata.head(5)

# # Make Connection to postgres

# ## Initialize connection

# +
gcp_sql_username = os.environ.get('gcp_sql_username')
gcp_sql_password = os.environ.get('gcp_sql_password')

conn = psycopg2.connect(user=gcp_sql_username, password=<PASSWORD>,
                        host='localhost', port='5432')
# -

# ## Query data 

# +
# %%time
DISPLAY_ROWS = 10000

cur = conn.cursor()
cur.execute('SELECT * FROM divvylivedata WHERE stationid = %s;' %('192'))
print("Total rows: {}\nDisplayed rows: {}\n".format(cur.rowcount, DISPLAY_ROWS))

row_counter = 1
row = cur.fetchone()
while row is not None and row_counter <= DISPLAY_ROWS:
#     print(','.join([str(v) for v in row]))
    row = cur.fetchone()
    row_counter += 1
print(row_counter)
cur.close()
# -

# ### Convert unix timestamps into timestamps and consider timezone

utc_timestamp = datetime.utcfromtimestamp(1565246835).strftime('%Y-%m-%d %H:%M:%S')
print(utc_timestamp)

# +
# METHOD 1: Hardcode zones:
from_zone = tz.gettz('UTC')
to_zone = tz.gettz('America/Chicago')

# # METHOD 2: Auto-detect zones:
# from_zone = tz.tzutc()
# to_zone = tz.tzlocal()

# utc = datetime.utcnow()

utc = datetime.strptime(utc_timestamp, '%Y-%m-%d %H:%M:%S')

# Tell the datetime object that it's in UTC time zone since 
# datetime objects are 'naive' by default
utc = utc.replace(tzinfo=from_zone)

# Convert time zone
central = utc.astimezone(to_zone)

print("Local time in Chicago: ", central)
# -

# ## Close connection

if conn:
    conn.close()

StarcoderdataJupyterScriptsDedupFiltered