text_plain_output_1.png
import pandas as pd
from google.cloud import bigquery
client = bigquery.Client()

dataset_ref = client.dataset('san_francisco_bikeshare', project='bigquery-public-data')
dataset = client.get_dataset(dataset_ref)

tables = list(client.list_tables(dataset))

table_ref_regions = dataset_ref.table('bikeshare_regions')
table_ref_station_info = dataset_ref.table('bikeshare_station_info')
table_ref_station_status = dataset_ref.table('bikeshare_station_status')
table_ref_trips = dataset_ref.table('bikeshare_trips')
bikeshare_regions = client.get_table(table_ref_regions)
bikeshare_station_info = client.get_table(table_ref_station_info)
bikeshare_station_status = client.get_table(table_ref_station_status)
bikeshare_trips = client.get_table(table_ref_trips)

client.list_rows(bikeshare_trips, max_results=5).to_dataframe()

query1 = '\n         SELECT r.name AS region, COUNT(si.station_id) AS station_count\n         FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_station_info` AS si\n         INNER JOIN `bigquery-public-data.san_francisco_bikeshare.bikeshare_regions` AS r\n          USING (region_id)\n         GROUP BY region\n         ORDER BY station_count DESC;\n         '
query_job1 = client.query(query1)
query_result1 = query_job1.to_dataframe()

query2a = '\n         SELECT start_station_name, COUNT(start_station_name) AS start_station_count\n         FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`\n         GROUP BY start_station_name\n         ORDER BY start_station_count DESC;\n         '
query_job2a = client.query(query2a)
query_result2a = query_job2a.to_dataframe()

query2b = '\n         SELECT end_station_name, COUNT(end_station_name) AS end_station_count\n         FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`\n         GROUP BY end_station_name\n         ORDER BY end_station_count DESC;\n         '
query_job2b = client.query(query2b)
query_result2b = query_job2b.to_dataframe()

query2c = '\n  WITH s AS (\n  SELECT start_station_name, COUNT(start_station_name) AS start_station_count\n  FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`\n  GROUP BY start_station_name\n  ORDER BY start_station_count DESC\n),\ne AS (\n  SELECT end_station_name, COUNT(end_station_name) AS end_station_count\n  FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`\n  GROUP BY end_station_name\n  ORDER BY end_station_count DESC\n)\nSELECT s.start_station_name AS station_name, s.start_station_count AS starting_count, e.end_station_count AS ending_count\nFROM s \nINNER JOIN e\n  ON s.start_station_name = e.end_station_name\nORDER BY start_station_count DESC, end_station_count DESC;\n         '
query_job2c = client.query(query2c)
query_result2c = query_job2c.to_dataframe()
print(query_result2c)