「%%bigquery」に続いてSQLを記述するとBigQueryにクエリを投げることができます

例えば、WebUIから実行した「重複なしでバイクステーションの数をカウントする」クエリは以下のように実行します



In [ ]:

    
%%bigquery
SELECT
  COUNT(DISTINCT station_id) as cnt
FROM
  `bigquery-public-data.new_york.citibike_stations`

同じように、WebUIから実行した各種クエリを実行してみます。

営業しているバイクステーション



In [ ]:

    
%%bigquery
SELECT
  COUNT(station_id) as cnt
FROM
  `bigquery-public-data.new_york.citibike_stations`
WHERE
  is_installed = TRUE
  AND is_renting = TRUE
  AND is_returning = TRUE

ユーザーの課金モデル



In [ ]:

    
%%bigquery
SELECT
  usertype,
  gender,
  COUNT(gender) AS cnt
FROM
  `bigquery-public-data.new_york.citibike_trips`
GROUP BY
  usertype,
  gender
ORDER BY
  cnt DESC

バイクの借り方の傾向



In [ ]:

    
%%bigquery
SELECT
  start_station_name,
  end_station_name,
  COUNT(end_station_name) AS cnt
FROM
  `bigquery-public-data.new_york.citibike_trips`
GROUP BY
  start_station_name,
  end_station_name
ORDER BY
  cnt DESC

結果の解釈（一例）

Central Parkの南に地下鉄の駅がある
- 観光客がCentral Parkの観光に利用している
12 Ave & W 40 St => West St & Chambers St
- 通勤での利用（居住区からオフィス街への移動）
南北方面ではなく東西方面の移動が多い
- 地下鉄は南北方向に駅がある
- NY在住者は自転車で東西方向に移動して、南北方向に地下鉄を利用する傾向がある

単純にBigQueryに対してクエリを実行するだけではなく、データの簡易的な可視化などの機能も提供されます。

利用者の調査

最も利用者が多いstart_station_name="Central Park S & 6 Ave", end_station_name="Central Park S & 6 Ave"の利用時間を調査します。 %%bigqueryコマンドに続いて変数名を渡すことで、BigQueryの結果をpandasのDataFrameとして保存することができます。



In [ ]:

    
%%bigquery utilization_time
SELECT
  starttime, stoptime, 
  TIMESTAMP_DIFF(stoptime, starttime, MINUTE) as minute,
  usertype, birth_year, gender
FROM
  `bigquery-public-data.new_york.citibike_trips`
WHERE
  start_station_name = 'Central Park S & 6 Ave' and end_station_name = 'Central Park S & 6 Ave'



In [ ]:

    
# utilization_timeの中身の確認
utilization_time

Pythonによるデータ可視化

データの概要を掴むためにヒストグラム（データのばらつきを確認するための図）を描きます。



In [ ]:

    
# 必要となるライブラリのインポート及び警告が表示されないような設定
import pandas as pd
import warnings
warnings.filterwarnings('ignore')

# ヒストグラムの描画
utilization_time['minute'].hist(bins=range(0,100,2))

30分程度の利用が最も多いことが確認されました。