Edited at

BigQuery + jupyterで機械学習を行う(その2: 前処理、EDA、特徴量生成)


これなに?

BQを利用して、機械学習を行うために有用そうな方法をまとめた記事です

この記事の中では


  • データセットの概観を確認し、探索的に分析

  • 分析した結果から精度向上に役立ちそうな特徴量を生成

使用するデータセットやjupyterとの接続方法については、前記事にまとめてあります


わりと長くなったので、BQMLの説明は次の記事に回します


使用するデータセット

前回の記事で作成したgoogle_analyticsのdatasetから、特定の商品をclickする確率を予測します(データセットはgoogleAnalyticsを使用します)

前回と同様にmagic commandを利用します

from google.cloud.bigquery import magics

from google.oauth2 import service_account
credentials = (service_account.Credentials.from_service_account_file('[/path/to/key.json]'))
magics.context.credentials = credentials

%load_ext google.cloud.bigquery

PROJECT ='[自身の作成したプロジェクトID]'

%%bigquery --project $PROJECT 

SELECT * FROM `google_analytics.ga_dataset` LIMIT 5




hour
browser
isMobile
country
productPrice
isClick




0
13
Chrome
False
United States
20990000
None


1
13
Chrome
False
United States
990000
None


2
7
Firefox
False
Canada
16990000
None


3
7
Firefox
False
Canada
16990000
None


4
13
Chrome
False
United States
12990000
None


データセットの特徴を調べる

まず、isClickがどのようになっているか確認します

%%bigquery --project $PROJECT 

SELECT
isClick, COUNT(1) AS numIsClick
FROM `google_analytics.ga_dataset`
GROUP BY isClick




isClick
numIsClick




0
None
90973


1
True
2034

この結果を見る限り、どうやらclickしなかった場合、はFalseではなく、nullが入っているようです


今後の分析のことを考えると、1/0で二値化したほうが楽なので、変換します


また、productPriceの値も極端に大きいので10の6乗で割ります

%%bigquery --project $PROJECT 

-- 新たなdatasetを定義
CREATE OR REPLACE TABLE google_analytics.ga_dataset_cleansed AS
SELECT
*EXCEPT(isClick, productPrice),
IF(isClick, 1, 0) AS isClick,
productPrice/1e6 AS productPrice
FROM `google_analytics.ga_dataset`

%%bigquery --project $PROJECT 

SELECT * FROM `google_analytics.ga_dataset_cleansed` LIMIT 5




hour
browser
isMobile
country
isClick
productPrice




0
0
Chrome
False
United States
0
0.0


1
0
Chrome
False
United States
0
0.0


2
1
Chrome
False
United States
0
0.0


3
1
Chrome
False
United States
0
0.0


4
1
Chrome
False
United States
0
0.0


pandasを利用して統計的な特徴を確認

次に、pandasを利用して、データセットの概観を確認します。ただし、全部とってくると当然メモリに乗らない(乗る量ならそもそもBigQueryを使わなくてもよい)ので、RAND()関数で適当にsamplingしてきます

%%bigquery data_sampled --project $PROJECT 

-- 10%だけサンプリング
SELECT
* FROM `google_analytics.ga_dataset_cleansed`
WHERE RAND() < 0.1

概観を確認します

data_sampled.describe()




hour
isClick
productPrice




count
9230.000000
9230.000000
9230.000000


mean
12.747996
0.021993
23.121088


std
5.346544
0.146670
25.548049


min
0.000000
0.000000
0.000000


25%
9.000000
0.000000
7.967500


50%
12.000000
0.000000
16.990000


75%
17.000000
0.000000
21.990000


max
23.000000
1.000000
575.700000

大まかな値段の分布やclickする確率がわかります。CTRは0.02程度で、productPriceの分布は右に歪んでそうですね


よりよい特徴量を探索

よりよい説明変数を見つけたり、逆に役に立たない変数を削ったりするために、特徴量ごとに分布や特性を分析、可視化してきます


hour特徴量

hourごとに見たときになにか特徴がないかを確認します

%%bigquery --project $PROJECT 

-- hourtransactionの数の相関係数
WITH data AS(
SELECT
hour, COUNT(1) AS numTrans
FROM `google_analytics.ga_dataset_cleansed` GROUP BY hour
)
SELECT CORR(hour, numTrans) AS corr FROM data




corr




0
0.32829

transction数と時間にはかなり相関がありますね。加えて、おそらくhourの値は国ごとに共通なので、時差のことを踏まえると、国別に見るとより相関が高いかもしれません

%%bigquery --project $PROJECT 

-- hourtransactionの数の相関係数
WITH data AS(
SELECT
country, hour, COUNT(1) AS numTrans
FROM `google_analytics.ga_dataset_cleansed` GROUP BY country, hour
)
-- 国ごと集計
SELECT
country, SUM(numTrans) AS numTrans, CORR(hour, numTrans) AS corr
FROM data GROUP BY country
-- transction数が多い国のみ表示
ORDER BY numTrans DESC LIMIT 10




country
numTrans
corr




0
United States
71621
0.378118


1
United Kingdom
2502
-0.377689


2
Canada
2067
0.377426


3
India
2020
-0.086196


4
Brazil
1438
0.140260


5
Mexico
971
0.446485


6
Taiwan
901
0.563291


7
Japan
891
-0.219272


8
Australia
643
-0.066862


9
Spain
570
0.884690

国ごとに見たほうがtransaction数と時間にかなり相関がありそうです。同じ時間でも国ごとに特徴が違うならば、hour特徴量はそのまま使うよりもcountryとの交差特徴量として生成したほうがより有用そうです


交差項の作成のために、bqにはML.FEATURE_CROSSという関数が用意されているので、これで交差特徴量を作成します

%%bigquery --project $PROJECT 

SELECT
country,
hour,
ML.FEATURE_CROSS(
STRUCT(
country,
-- 数値型の場合はサポートされていないので、CASTする
CAST(hour AS STRING) AS hour
)) AS countryHour
FROM `google_analytics.ga_dataset_cleansed` LIMIT 5




country
hour
country_hour




0
United States
0
{'country_hour': 'United States_0'}


1
United States
0
{'country_hour': 'United States_0'}


2
United States
1
{'country_hour': 'United States_1'}


3
United States
1
{'country_hour': 'United States_1'}


4
United States
1
{'country_hour': 'United States_1'}


productPrice特徴量

商品の値段分布にどのような特徴があるかを見てみます


まず、観測数を10分割したときに区切りとなる値をAPPROX_QUANTIELS関数で見てみます

%%bigquery --project $PROJECT 

-- hourtransactionの数の総関係数
WITH data AS(
SELECT
APPROX_QUANTILES(productPrice, 10) AS q
FROM `google_analytics.ga_dataset_cleansed`
)
SELECT
quantile
FROM data, UNNEST(q) AS quantile




quantile




0
0.00


1
1.99


2
4.99


3
10.99


4
16.99


5
16.99


6
17.99


7
20.99


8
25.00


9
59.99


10
575.70

25以上の値の数は少なく、また、17あたりに値が集中しているようです


ここで、値段ごとにctrがどれ位変わるかを見るために、ML.BUCKETIZE関数で、指定した境界値ごとの値に区切ってみます

%%bigquery --project $PROJECT 

SELECT
productPrice,
ML.BUCKETIZE(
productPrice,
[2, 5, 16, 17, 20, 50]
) AS priceBin
FROM `google_analytics.ga_dataset_cleansed`
LIMIT 5




productPrice
priceBin




0
0.0
bin_1


1
0.0
bin_1


2
0.0
bin_1


3
0.0
bin_1


4
0.0
bin_1

値段ごとにctrがどれ位変わるかを確認してみます

%%bigquery --project $PROJECT 

WITH data AS(
SELECT
isClick,
productPrice,
ML.BUCKETIZE(
productPrice,
[2, 5, 16, 17, 20, 50]
) AS priceBin
FROM `google_analytics.ga_dataset_cleansed`
)

SELECT
priceBin,
MIN(productPrice) AS minPrice,
MAX(productPrice) AS maxPrice,
COUNT(1) AS numTrans,
SUM(isClick) AS click,
SUM(isClick)/COUNT(1) AS CTR
FROM data
GROUP BY priceBin
ORDER BY priceBin




priceBin
minPrice
maxPrice
numTrans
click
CTR




0
bin_1
0.00
1.99
9247
168
0.018168


1
bin_2
2.00
4.99
11340
306
0.026984


2
bin_3
5.60
15.99
15928
365
0.022916


3
bin_4
16.79
16.99
17176
285
0.016593


4
bin_5
17.50
19.99
10084
140
0.013883


5
bin_6
20.00
49.95
17630
322
0.018264


6
bin_7
50.00
575.70
11602
448
0.038614

値段が高くなると、CTRが下がるような傾向は見られず、また、16.8まわりに値段が極端に集中してます


なので、productPriceはそのまま数値としてではなく、binで区切り、カテゴリ特徴量に変換し、モデルに入力します


カテゴリ系の特徴量

ブラウザごと、あるいは携帯からのアクセスかどうかで、CTRに違いが現れるかを確認します

%%bigquery --project $PROJECT 

SELECT
browser,
isMobile,
COUNT(1) AS numTrans,
SUM(isClick) AS numClick,
SUM(isClick)/COUNT(1) AS CTR
FROM `google_analytics.ga_dataset_cleansed`
GROUP BY browser, isMobile
ORDER BY browser, isMobile DESC




browser
isMobile
numTrans
numClick
CTR




0
Amazon Silk
True
26
0
0.000000


1
Chrome
True
9271
159
0.017150


2
Chrome
False
67104
1574
0.023456


3
Edge
False
870
5
0.005747


4
Firefox
False
3699
55
0.014869


5
Internet Explorer
True
34
0
0.000000


6
Internet Explorer
False
1500
13
0.008667


7
Opera
False
140
2
0.014286


8
Safari
True
7207
146
0.020258


9
Safari
False
2570
71
0.027626


10
Safari (in-app)
True
215
9
0.041860


11
UC Browser
True
68
0
0.000000


12
YaBrowser
False
303
0
0.000000

同じブラウザでも、携帯からのアクセスかどうかで異なるようなので、この2つも交差特徴量として入力します


モデルに入力するデータセットを作成する

以上をまとめて、機械学習モデルに入力するデータセットを作成します

%%bigquery --project $PROJECT 

CREATE OR REPLACE TABLE google_analytics.ga_dataset_feature AS
SELECT
ML.FEATURE_CROSS(
STRUCT(
country,
CAST(hour AS STRING) AS hour
))
AS countryHour,
ML.FEATURE_CROSS(
STRUCT(
browser,
isMobile
))
AS browserMobile,
ML.BUCKETIZE(
productPrice,
[2, 5, 16, 17, 20, 50]
) AS priceBin,
isClick
FROM `google_analytics.ga_dataset_cleansed`

%%bigquery --project $PROJECT 

SELECT * FROM `google_analytics.ga_dataset_feature` LIMIT 5




countryHour
browserMobile
priceBin
isClick




0
{'country_hour': 'Peru_9'}
{'browser_isMobile': 'Chrome_false'}
bin_1
0


1
{'country_hour': 'Peru_9'}
{'browser_isMobile': 'Chrome_false'}
bin_1
0


2
{'country_hour': 'Peru_9'}
{'browser_isMobile': 'Chrome_false'}
bin_1
0


3
{'country_hour': 'Peru_9'}
{'browser_isMobile': 'Chrome_false'}
bin_1
0


4
{'country_hour': 'Peru_9'}
{'browser_isMobile': 'Chrome_false'}
bin_1
0

次に、このデータセットをモデルを作成し入力します(また今度書きます)


参考文献