Go to Qiita Advent Calendar 2024 Top
LoginSignup
8
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@Hase8388(hase hiro)inUnipos株式会社

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

Last updated at Posted at 2019-07-22

これなに?

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 
-- hourとtransactionの数の相関係数
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 
-- hourとtransactionの数の相関係数
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 
-- hourとtransactionの数の総関係数
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

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

参考文献

8
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?