スクレイピングでNPBの年間観客動員数を取得する【R】
NPBの年間観客動員数は2013年から増加しているらしい*1。もう少し細かいデータを見てみたいなと思ったのでスクレイピングでNPBの年間観客動員数をRを使って取得して、前処理までしたので記事にしてみました。
※ Jupyter notebookで行っているためもしかしたらRstudioだとエラーが発生するところがあるかもしれません
必要なライブラリの読み込み
library(tidyverse) library(rvest) library(glue) library(scales)
tidyverse
データサイエンス用に設計されたパッケージ群です。データの読み込み・抽出・加工・可視化など、データサイエンスの基本的なパッケージ群を読み込むことができます。tidyverseの登場によってRの前処理が簡単になったとか。
rvest
Webスクレイピングに必要なパッケージです。これのおかげで簡単にスクレイピングができます。
glue
Pythonでいうf文字列を実現するために使用します。今回は年度毎に年間観客動員数が載っているPathが異なります。なので、年度毎にPathを生成する必要があるためglueを使用します。
scales
ちょっと自信がないですが、グラフを作成する際に使用するパッケージのようです*2。特に軸とか凡例とかが対象なのかも。今回は棒グラフを作成する際に、scales::label_commaを用いて3桁毎にカンマを打つために使用します。
指数表記の回避
options(scipen=10)
Rはデフォルトだと指数表記になるようなので上記を実行します。scipenの数字を大きくすると桁数が大きくなっても指数表記されないようです。数字は桁数を表していると思うのですが、詳しい内容まではわかりませんでした（ドキュメントはこちら）。
スクレイピングで年度毎の観客動員数を取得する
lst_year <- c("09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20") for (year in lst_year){ # スクレイピング対象のページに対して、アクセス負荷をかけないために5秒あける Sys.sleep(3) # パスの取得 path <- glue("https://baseball-freak.com/audience/{year}/") # 対象のページの取得 d <- read_html(path) # 対象のページのテーブルタグを取得 dfs <- html_table(d) # 最初のリスト（目的のテーブル）を取得 df_audience <- dfs[[1]] # 年度のカラムを追加 df_audience$年度 <- as.integer(glue("20{year}")) # 1試合平均のカラムの名前を平均に変更 # 変更する理由はmutateで文字列からintに変更するときにカラム名を指定できるようにするため # カラムの一番最初が数字だとエラーが起こる df_audience <- dplyr::select(df_audience, 1, 平均 = 2, 3, 4, 5) # 平均・試合数・合計のカラムを文字列からintへ変更 # 具体的には各カラムの数字意外を消して（正確には""に置き換えて）intへ変更 df <- dplyr::mutate(df_audience, 平均=as.integer(gsub (平均,pattern="\\D",replacement = "", ignore.case = TRUE) ), 試合数=as.integer(gsub (試合数,pattern="\\D",replacement = "", ignore.case = TRUE) ), 合計=as.integer(gsub (合計,pattern="\\D",replacement = "", ignore.case = TRUE) ) ) # ifを使って最初（2009年）はdfをdf_mainにして、あとはdf_mainに対してdfを縦にマージする if (year == "09"){ df_main <- df } else { # df_mainに対してdfを縦にマージする df_main <- rbind(df_main, df) } }
説明はコメントを読んでいただけると嬉しいですが、ざっくり説明すると、read_html()で対象のパスのデータを取得しているようです。html_table()では、取得したデータのうちテーブルタグのデータを取得していて、それをdfsの変数に入れています。そのうち一番最初のテーブルに年間観客動員数が入っているのでdfs[[1]]をdf_audience に入れています。あとは前処理として数字にしたいものをINTにしたりしています。最後にif文のところで毎年のデータを縦につなげていきます。
df_NPBにカラム名がチームの行だけを抽出する
df_NPB <- df_main %>% # filterで条件を絞る filter(チーム == "合計")
今回取得したデータには各球団の年間観客動員数も入っている。なので、チームが「合計」のものだけを取得するためにfilter()を使用する。filterしたデータはパイプ演算子を使って変数df_NPBに入れる。これでdf_NPBの合計カラムには年間の観客動員数数のみが入っていることになる。
これでNPBの年間観客動員数の取得が完了！目的が達成できました！
おまけ
おまけで年間観客動員数をグラフ化したいと思います。
年度カラムを文字列に変換
# 棒グラフを作成する際にX軸にすべての年度が表示されるようにするためにINTから文字列に型変換 # 棒グラフでX軸がINTだとすべては表示されない df_NPB$年度 <- as.factor(df_NPB$年度)
初めから年度カラムは文字列にすればよいんだけど、何となく年度はINTにしたくて、グラフを作成する前に文字列に変換するという工程にしました。まぁほかのところでdf_mainを使用する可能性を考えるとこれで良いのかもしれない。文字列にする理由はコメントにもありますが、棒グラフを作成するときにINTだと全部の年度が載らないので文字列にしました。X軸をカテゴリとして扱う感じなのかなと思っています。
グラフ化
g <- ggplot(df_NPB, aes(x=年度, y=合計)) + geom_bar(stat = "identity") + # 3桁カンマにするため scale_y_continuous(labels = label_comma()) + ggtitle("NPB年間観客動員数") + # タイトルをセンターの位置にする theme(plot.title = element_text(hjust = 0.5)) + ylab("観客動員数（人）") + xlab("西暦") plot(g)
matplotlib.tickerで目盛りの数字を3桁カンマ区切りにする
matplotlibでグラフを作成するときに、目盛りの数字を3桁カンマ区切りにする方法をざっと探したけどあまりピンとくるものがなかったので書き留めたいと思います。
個人のブログですので内容に誤りがある場合があります。きちんとした情報が必要なときはきちんと公式ドキュメントを読んでください。
結論
matplotlib.tickerを使います。
tickerは軸の場所やフォーマットを操作するのに使うものらしいです。
このコードを記載すると3桁カンマ区切りができます。
plt.gca().get_yaxis().set_major_formatter(ticker.FuncFormatter(lambda v,p: f'{int(v):,d}')) plt.gca().get_xaxis().set_major_formatter(ticker.FuncFormatter(lambda v,p: f'{int(v):,d}'))
例えばこんな感じ
import pandas as pd import matplotlib.pyplot as plt import matplotlib.ticker as ticker df_1 = pd.DataFrame({'A':['a', 'b', 'c', 'd'], 'B':[10000000,20000000,30000000,40000000]}) plt.bar(df_1['A'],df_1['B']) plt.gca().get_yaxis().set_major_formatter(ticker.FuncFormatter(lambda v,p: f'{int(v):,d}'))
FQ毎のDAUをPostgresで集計
アプリとかゲーム業界とかでよくあるらしいFQの集計方法のサンプルを書きます。
FQについてはこちらの記事がわかりやすいと思います。
連続性は今回は無視したFQになります。
データ作成
DROP TABLE IF EXISTS training.access_log; CREATE SCHEMA IF NOT EXISTS training; CREATE TABLE IF NOT EXISTS training.access_log(user_id VARCHAR (3), log_date DATE); INSERT INTO training.access_log(user_id, log_date) VALUES ('AAA', '2018-8-1') , ('AAA', '2018-8-2') , ('AAA', '2018-8-3') , ('AAA', '2018-8-5') , ('AAA', '2018-8-6') , ('AAA', '2018-8-7') , ('AAA', '2018-8-8') , ('AAA', '2018-8-9') , ('AAA', '2018-8-10') , ('AAA', '2018-8-11') , ('AAA', '2018-8-12') , ('BBB', '2018-8-1') , ('BBB', '2018-8-2') , ('BBB', '2018-8-3') , ('CCC', '2018-8-1') , ('CCC', '2018-8-2') , ('CCC', '2018-8-8') , ('CCC', '2018-8-9') , ('CCC', '2018-8-10') , ('DDD', '2018-8-2') , ('DDD', '2018-8-4') , ('DDD', '2018-8-5') , ('DDD', '2018-8-6') , ('DDD', '2018-8-8') , ('DDD', '2018-8-9') , ('DDD', '2018-8-10') , ('DDD', '2018-8-11') , ('DDD', '2018-8-12') , ('FFF', '2018-8-1') , ('FFF', '2018-8-2') , ('FFF', '2018-8-3') , ('FFF', '2018-8-4') , ('FFF', '2018-8-5') , ('FFF', '2018-8-6') , ('FFF', '2018-8-7') , ('FFF', '2018-8-8') , ('FFF', '2018-8-9') , ('FFF', '2018-8-10') , ('FFF', '2018-8-11') , ('FFF', '2018-8-12') , ('GGG', '2018-8-6') , ('GGG', '2018-8-7') , ('GGG', '2018-8-8') , ('GGG', '2018-8-9') , ('GGG', '2018-8-11')
これ手作業で作成したのですが、サンプルデータを良い感じに作る方法とかないんですかね？PythonとRだったらできるのにSQLだとその辺がちょっとめんどくさい。いや、PythonとRでデータを作成してDBに突っ込めば良いのか。でも、やっぱそれもめんどいな。
集計
WITH left_outer AS ( --1.基準のテーブルに同じテーブルをuser_idをキーにしてLEFT OUTER JOINする SELECT t1.user_id AS user_id , t1.log_date AS log_date , t2.user_id AS user_id_outer , t2.log_date AS log_date_outer FROM training.access_log AS t1 LEFT OUTER JOIN training.access_log AS t2 USING (user_id) ) , make_fq_flg AS ( --2,基準のテーブルのlog_dateをもとに6日間の間のログの有無を判定 SELECT user_id , log_date , user_id_outer , log_date_outer , CASE WHEN log_date BETWEEN log_date_outer AND log_date_outer + CAST('6 days' AS INTERVAL) AND user_id = user_id_outer THEN 1 ELSE 0 END AS fq_flg FROM left_outer ) , make_fq AS ( --3.user_idとlog_date毎にfq_flgをsumすることでFQを計算 --log_dataがn日目のFQ（頻度）かをfq列に算出している SELECT user_id , log_date , sum(fq_flg) AS fq FROM make_fq_flg GROUP BY user_id , log_date order by user_id , log_date ) , count_fq AS ( --4.log_dateとFQ毎のユニークユーザー数（uu）を算出 SELECT log_date , concat('FQ', FQ) AS FQ , COUNT(user_id) AS uu FROM make_fq GROUP BY log_date , concat('FQ', FQ) ) --5最後にpivot SELECT log_date , max(CASE WHEN FQ = 'FQ1' THEN uu END) AS fq1 , max(CASE WHEN FQ = 'FQ2' THEN uu END) AS fq2 , max(CASE WHEN FQ = 'FQ3' THEN uu END) AS fq3 , max(CASE WHEN FQ = 'FQ4' THEN uu END) AS fq4 , max(CASE WHEN FQ = 'FQ5' THEN uu END) AS fq5 , max(CASE WHEN FQ = 'FQ6' THEN uu END) AS fq6 , max(CASE WHEN FQ = 'FQ7' THEN uu END) AS fq7 FROM count_fq GROUP BY log_date ORDER BY log_date
つまづきPOINT
FQで良くつまづくのは個人的にはFQの考え方です。そもそも何の値を算出するのかわからなくなってしまうことが多々あります。SQLを基本に考えていくとlog_dateに対して、その日を含めた前の7日間（log_dateが8月7日であれば8月1日から8月7日までの間）のログインをカウントしています。あるuser_idがある日を基準にFQはいくつかを計算して、日付とFQ毎にユニークユーザー数を算出している認識です。そして、最後はそれらをpivotしている完成。ある日のFQ毎のユニークユーザー数を合計すると、その日のアクティブクユーザー数（DAU）になります。
『継続して○○した日数』とその最大値をPostgreSQLで求める
下記の「『継続して○○した日数』とその最大値をSQLで求める」というのをローカルのPostgresで試してみたのですがいろいろとうまくいかなかったので、試行錯誤してPostgres版を作成しました。
ちなみにただの感想ですが、これ考えた人めっちゃ頭良い。天才。
データ作成
まずはデータを作成
--スキーマ作成 CREATE SCHEMA IF NOT EXISTS training; --テーブル作成 CREATE TABLE IF NOT EXISTS training.access_log(user_id VARCHAR (3), log_date DATE); --データをinsert INSERT INTO training.access_log(user_id, log_date) VALUES ('AAA', '2018-8-1') , ('AAA', '2018-8-2') , ('AAA', '2018-8-3') , ('AAA', '2018-8-5') , ('BBB', '2018-8-2') , ('BBB', '2018-8-3') , ('CCC', '2018-8-2') , ('DDD', '2018-8-2') , ('DDD', '2018-8-4') , ('DDD', '2018-8-5') , ('DDD', '2018-8-6');
集計
集計SQLはこんな感じになりました。
WITH lag_log AS ( --1.同一user_idでの手前のuser_id, log_dateを取得 SELECT user_id , log_date , LAG(user_id, 1) OVER (PARTITION BY user_id ORDER BY log_date) AS lag_user_id , LAG(log_date, 1) OVER (PARTITION BY user_id ORDER BY log_date) AS lag_log_date FROM training.access_log ) , not_cont_date AS ( --2.同一user_idで「日付が連続していないか」を判定 SELECT user_id , log_date , lag_user_id , lag_log_date , CASE WHEN user_id = lag_user_id AND log_date <> lag_log_date + cast('1 days' as INTERVAL) THEN 1 WHEN lag_user_id IS NULL AND lag_log_date IS NULL THEN 1 ELSE 0 END AS not_cont_date_flg FROM lag_log ) , cum_not_cont_date AS ( --3.「日付が連続していないか」の積み上げ和を作成 SELECT user_id , log_date , lag_user_id , lag_log_date , not_cont_date_flg , SUM(not_cont_date_flg) OVER ( PARTITION BY user_id ORDER BY user_id , log_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cum_not_cont_date_flg FROM not_cont_date ) , continue_day AS ( --4.cum_not_cont_date_flgで集計をおこない、レコード数を数える SELECT user_id , cum_not_cont_date_flg , SUM(1) AS cont_days FROM cum_not_cont_date GROUP BY cum_not_cont_date_flg , user_id ORDER BY user_id , cum_not_cont_date_flg ) --5.user_id毎に最大連続日数を取得する SELECT user_id , MAX(cont_days) AS max_cont_days FROM continue_day GROUP BY user_id ORDER BY user_id
つまづきPOINT
1.同一user_idでの手前のuser_id, log_dateを取得
ここは特になかったです。単純にlagができたので問題なし。
2.同一user_idで「日付が連続していないか」を判定
割とここはつまづきました。 同一のuse_idで日付が連続しているかどうかの判定なのでcase文で 'user_id = lag_user_id'は理解できた。がしかし、log_date <> DATEADD(day,-1,lag_log_date)の部分が良くわからなかったし、結局理解できなかった。わからないのは2か所。
なぜlag_log_dateに-1なのか
ここが良くわからなかった。lag関数がDBによって変わるのかなんて思ったけどそんなこともなさそう。なぜ-1なのかはいまだに不明で+1した。
ちなみにPostgresだとDATEADDはないようで、単純にlag_log_date + 1で1日加えることができた。lag_log_date + cast('1 days' as INTERVAL) でも良いらしい。
各user_idの一番最初に1がつかない
各user_idの一番最初は連続していないのでlag_user_idとlag_log_dateがnullのケースでは1を立てる必要がある。元の記事でも結果はそうなっている。がしかし、元の記事のとおり実行しても0になる。nullなので<>でも!=でもfalseになって1が付くようにはならないと思っているし、手元でやってみてもやはり1はつかない。なのでlag_user_idとlag_log_dateがnullであれば1という文を追加した。こちらも結局理解できていない。
3.「日付が連続していないか」の積み上げ和を作成
ここではROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWの部分でつまづいた。先頭にROWSを付けるかどうかで結果が変わる。ちなみにここも良くわかっていない。DBのお作法の違い程度に考えている。
4.cum_not_cont_date_flgで集計をおこない、レコード数を数える
「WHERE pay_flg = 1」がどっから出てくるのか不明で結構な時間迷った。良くわからないけど不要と判断して削除したらできた。
5.user_id毎に最大連続日数を取得する
つまづきPOINTなし
良くわからなかったのは2と3の部分でした。4も結構な時間使ったけど多分扶養なんだと思って気持ちをごまかしている。
でもやっぱりこれ考えた人天才だと思う。
良く使うライブラリ【Python】
pandas
強力なPythonデータ分析ツールキット
pip install pandas import pandas as pd
できることが多すぎるので例は省略します。
matplotlib
グラフ作成に使う
import matplotlib.pyplot as plt import random normalvariate = [random.normalvariate(0, 1) for i in range(1000)] plt.hist(normalvariate)
japanize_matplotlib
matplotlib を日本語表示に対応させる
pip install japanize-matplotlib
import japanize_matplotlib
seedir
ディレクトリの構造を見やすく可視化する
import seedir as sd sd.seedir(style='emoji', itemlimit=10, depthlimit=2, exclude_folders='.git')
datetime
日付や時刻を操作
import datetime
psycopg2
PostgreSQLの操作に必要
pip install psycopg2
import psycopg2
sqlalchemy
Pythonの中でよく利用されているORMの1つ https://www.sqlalchemy.org/
from sqlalchemy import create_engine
glob
ファイルとディレクトリ操作でよく使う。分析よりも何かしらの自動化で使う。
import glob files = glob.glob('*.csv')
df.agg()にgroupbyが使えると聞いて
グループごとに個数・平均値・中央値など複数の集計を出したいときにdf.groupby().agg()を使うと便利ですよ。
これは個人のブログなので間違いがあるかもしれませんので、正しい情報が欲しい人は公式ドキュメントを読んでください。
サンプルコード
import pandas as pd import numpy as np df = pd.DataFrame({'A':[1, 2, 3, 4, 5, np.nan], 'B':[29, 30, 31, 32, 33, 11], 'C':['a', 'a', 'b', 'b', 'c', 'c']})
C列でグルーピングして集計したいときはこんな感じ
df.groupby('C').agg(['sum', 'mean', 'count'])
さらに、Bカラムだけの集計値だけが欲しいというときはこんな感じ。
df.groupby('C').B.agg(['sum', 'mean', 'count'])
pythonの決定木のコードサンプル
毎回決定木を試みるときにどうやって書くのかさっぱり忘れていて、いちから探しているので、とりあえず自分のためにメモを書いておこうと思います。 完全にメモですので、間違いなどがあると思います。参考程度にして、ご自身で実装される際はきちんと公式ドキュメントを読んでください。
# 目的変数 y_name = '****' # 目的変数のカラム名を入れる # 従属変数（使用列）の選択 X_name = ['****', '****', '****', '****'] # 従属変数のカラムを入れる # Xとyに分離 X_train = df[X_name] y_train = df[y_name]
from sklearn.datasets import * from sklearn import tree from dtreeviz.trees import * import graphviz dtree = tree.DecisionTreeClassifier(max_depth=3) dtree.fit(X_train, y_train)
viz = dtreeviz(dtree,X_train,y_train,
target_name='****', # 目的変数名
feature_names=X_name,
class_names=['****', '******'], # 目的変数の0が何を表しているか、1が何を表しているかを書きます
)
viz
正直いうと、コードを書くよりもgraphvizをwindowsで使えるようにするのが大変でした。環境変数とかそのあたりを理解しているとできるっぽい。pip installした後に、Graphvizの本体？をインストールしてシステム環境変数を設定するようです。 参考は以下
