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

from IPython.core.display import display, HTML

from sqlalchemy import create_engine
engine = create_engine(open('~/.sqlconninfo').read())

import pandas as pd

z = pd.read_sql_query("""SELECT distinct start_station_id as id, 
                      start_station_name as name, 
                      start_station_latitude as latitude,
                      start_station_longitude as longitude
                      from bike_ingest order by id
                      """, engine)

display(HTML(z.to_html()))

m = z.groupby('id').count()
display(HTML(z.merge(m[m.name > 1], left_on='id', right_index=True, how='inner').to_html()))

z = pd.read_sql_query("""SELECT DISTINCT start_station_id as id, start_station_name as name, start_station_latitude as lat, start_station_longitude as lon
                            FROM bike_ingest ;""", engine)
z

z = pd.read_sql_query("""SELECT DISTINCT end_station_id as id, end_station_name as name, end_station_latitude as lat, end_station_longitude as lon
                            FROM bike_ingest ;""", engine)
z

z = pd.read_sql_query("""
SELECT DISTINCT
  id,
  name,
  lat,
  lon
FROM (
       SELECT
         start_station_id        AS id,
         start_station_name      AS name,
         start_station_latitude  AS lat,
         start_station_longitude AS lon
       FROM bike_ingest AS T1
       UNION ALL
       SELECT
         end_station_id        AS id,
         end_station_name      AS name,
         end_station_latitude  AS lat,
         end_station_longitude AS lon
       FROM bike_ingest AS T2
     ) AS T;
                      """, engine)

m = z[['name', 'id']].groupby('id').count()

display(HTML((m[m.name > 1].merge(z, left_index=True, right_on="id", how='inner')).to_html()))

z = pd.read_sql_query("""SELECT * from bike_ingest limit 1;""", engine)
z.columns



StarcoderdataJupyterScriptsDedupFiltered