2017-04-03

vol.5 Pandasとdataframe

3月13日は、作成したデータフレームからグラフを作ってみましたが、

その前に備忘録として

Pandasとは何なのか？
何のためにdataframeするのか？

自分なりの解釈で書いていきたいと思います。

Pandasについて（備忘録１）

ウィキペディア( https://ja.wikipedia.org/wiki/Pandas )によると、pandasとは
「プログラミング言語 Pythonにおいて、データ解析を支援する機能を提供するライブラリ」らしいです。なるほど。

いや、プログラミングで使うライブラリとは？

プログラミング初心者は、ライブラリとか言われても、
プログラミング自体とライブラリとの関係性がいまいち理解できないというか、
ほかの人はどうか分かりませんが私はそうでした。

そこで、ライブラリに関して分かりやすい記事を見つけたので
シェアしておきます。
http://wa3.i-3-i.info/word1473.html

このページに書かれていることをもとにpandasを改めて説明すると、

Pythonでデータ解析をするとき、
自分で地道にコードを書いていくことも出来なくはないけど、
事前にpandasをインポートしておくとPythonのままpandasの機能（解析に便利なやつ）を使えるよ、　　　

という感じでしょうか。

dataframeについて（備忘録２）

dataframeに関しては、
正直なところ調べてもよく分かりませんが、

とりあえず現時点で調べて分かったことをメモ程度に書いていきます。

参考にしたページはこちら
→ http://cse.naro.affrc.go.jp/takezawa/r-tips/r/39.html　
　 http://qiita.com/kiimiiis/items/0e1646adf0dab0061845

pandasでは主なデータ構造が2つあって、
1つは一次元（Series）、
もう1つは二次元（dataframe）、
これに関してはまだはっきりとは分からないというか、掴めないです。

ただ、
単純に文字と数字を表のように並べて書いたものがSeries、

その表のようなものを構成する文字や数字に、
行ごとに列ごとに意味を付けて表にしたものがdataframe、という感覚です。

dataframeは文字や数字をただ並べただけのものではないので
列ごとに整理することもできますし、
平均値や標準偏差を出したり、グラフを作ったりすることもできます。
クロス集計もできるみたいです。

dataframe、グラフ作成

さて本題に入ります。

まず、書式IDをそのダウンロード数と、
企業の従業員数別に表した表をデータフレームで作成して
「pivot_df_sorted」という名前を付けました。

pivot_df=EMPLOYEE_SCALE_NAME_DF.pivot(index="product_id",columns="employee_scale",values="count").fillna(0)
pivot_df_reindex = pivot_df.reindex(
columns=[u"1～9名",
         u"10～49名",
         u"50～99名",
         u"100～299名",
         u"300～499名",
         u"500～999名",
         u"1000～2999名",
         u"3000～4999名",
         u"5000～9999名",
         u"10000名以上",
         u"(未回答)"])
pivot_df_sorted = pivot_df_reindex.sort_values(by=u"1～9名", ascending=False).fillna(0)
pivot_df_sorted

前回、EMPLOYEE_SCALE_NAME_DFというデータフレームを作成しましたが、
ここでpivotを用いて、
列にproduct_id、行にemployee_scale
を指定して改めてデータフレームを作りました。

ただ、これだけではemployee_scaleの並びがバラバラなので、
reindexという機能でcolumnsの名前を指定し、順番に並べていきます。

前回作成したもの
f:id:yuka_nakayama:20170403105800p:plain

今回改めて作成したもの
f:id:yuka_nakayama:20170403105829p:plain

また、行ごと、列ごとに和を出していきます。

pivot_df_sorted['p_sum']=pivot_df_sorted.sum(axis=1)
pivot_df_sorted.sort_values(by="p_sum")

pivot_df_sorted.loc["es_sum"] = pivot_df_sorted.sum()
pivot_df_sorted

上2行が行ごとの和(p_sum)、
下2行が列ごとの和(es_sum)を出すコードです。

1行目に(axis=1)とありますが、
これは行に対する演算をする、という意味です。

これをもとにグラフを作っていきます。

グラフにするコードは比較的シンプルで、

pivot_df_sorted.loc["es_sum"].drop("p_sum").plot.pie(figsize=(8,8))

だけです。

pivot_df_sortedのes_sum（列ごとの和）を指定して、
p_sum（行ごとの和）はdropで省く。

plotでグラフ化、
pieはグラフの種類の指定です。

f:id:yuka_nakayama:20170403110603p:plain

ここでは円グラフを指定しましたが、

たとえば棒グラフならbar、
散布図ならscatterなどを書きます。

f:id:yuka_nakayama:20170403110114p:plain

2017-04-03

vol.4　JOIN句の練習とpandas dataframe

こんにちは
今日は3月2日と3月7日にやった内容を書いていきたいと思います。

まずJOIN句の使い方の練習です。

初めてJOIN句を使ったのは2月20日でしたが、
ぼんやりとしか使い方が分からなかったため練習しました。

JOINの練習

JOINでは、FROM句のあとにくっつけたいテーブルの名前と、
どの項目でくっつけるのかを書きます。

たとえば

①

USER_DL="""(
SELECT
dl.uid,
integer(
REGEXP_EXTRACT(url, r'/doc/download/complete/([0-9]+)/')
) as product_id,
job_spec1 as job_spec
FROM [project_id: oceanus_prod.oceanus_v3_bizocean_20170225] as dl
JOIN {USER_JOBSPEC} as uj
　　　 on dl.uid=uj.uid
WHERE
　　　 url CONTAINS "ht tps://www.bizocean.jp/doc/download/complete/"
GROUP BY
dl.uid,
product_id,
job_spec
)""".format(USER_JOBSPEC=USER_JOBSPEC)
bq.Query(USER_DL).results()

ここでは予め「USER_JOBSPEC」というSQLを作っています。

USER_JOBSPEC="""(
SELECT
string(member_id) as uid,
job_spec1
FROM
[project_id: bizocean.member]
GROUP BY
uid,
job_spec1
)"""
bq.Query(USER_JOBSPEC).results()

f:id:yuka_nakayama:20170314105011p:plain

このSQL文を上記のUSER_DLのSQL文に組み込むこともできますが、

そうすると長ったらしくなって読みづらくなったり、
エラーの確認が難しくなったりするので、

とりあえずUSER_JOBSPECという文字列で{}を埋めておきます。

そして、

文字列USER_JOBSPECは前に作ったSQLUSER_JOBSPECのことだよー

ということで、format(USER_JOBSPEC=USER_JOBSPEC)で置き換えます。

format()で置き換えないと、
「USER_JOBSPECとかいう文字列は一体何なんだ？」と
エラーが返ってきます。

ちなみに、FROM句にある
[project_id: oceanus_prod.oceanus_v3_bizocean_20170225] は
ユーザーIDとダウンロードした書式のIDを含むテーブルです。

f:id:yuka_nakayama:20170314105045p:plain

両テーブルの共通項であるuid（ユーザーID）でくっつけて、
ユーザーID、ダウンロードした書式ID、職種　　を示す表を作りました。

f:id:yuka_nakayama:20170314105126p:plain

これを応用し、ダウンロードされた書式の名前と、それをダウンロードしたユーザーの職種を示すSQLを作りました。

②

SPEC_DL="""(
SELECT
name,
job_spec
FROM
{USER_DL} as ud
JOIN {PRODUCT_NAME} as pn
on ud.product_id=pn.product_id
GROUP BY
ud.product_id,
job_spec,
name
)""".format(USER_DL=USER_DL,PRODUCT_NAME=PRODUCT_NAME)
bq.Query(SPEC_DL).results()

USER_DLは①で作ったSQLです。
ここでも予めPRODUCT_NAMEというSQLを作っています。

USER_DLとPRODUCT_NAMEではproduct_idが共通して含まれているので、
ON ud.product_id=pn.product_idで繋げます。

JOINしてできたのが以下のものです。

dataframeについて

Pythonでデータを扱うために、
SQLで書いたものをBigQueryでテーブルとして保存し、
dataframeの形式にします。

テーブルで保存しなくてもスキャン出来なくはないですが、
データが大きくなるとスキャン料金が高く付いてしまうので、
一度データを必要なものに絞り込んでからSQL文を書いていきます。

EMPLOYEE_SCALE_NAME_DF=bq.Query(
"""
SELECT
 product_id,
 employee_scale,
 count(product_id) as count
FROM
 [prject_id: work.user_dl_log20160901_20170306]
WHERE
 product_id < 600000
GROUP BY
  employee_scale,
  product_id
ORDER BY
 product_id

 """
).to_dataframe()

少しはコーディングに慣れてきた気もしますが
まだまだ覚えることがたくさんあるので
めげずにやっていきたいと思います。

2017-04-03

vol.3　サブクエリ、正規表現、JOIN句

こんにちは
2月20日のやったことを書いていきます。

この日は、Google Cloud DataLabを使ってデータ分析の方法を教わりました。
書くコードは前回に引き続きSQLです。

前回はBigQueryのQuery Tableにコードを書いていましたが、
複数の文章を書くことができないため、
Google Cloud DataLabを使うことになりました。

今日は
　サブクエリ
　正規表現
　JOIN句
をやりました。

まず実際のコードです。

コード①

DL_COUNT = """
  (SELECT 
    product_id,
    count(uid) AS count
  FROM 
    (SELECT
      uid,
      integer(
        REGEXP_EXTRACT(url, r'/doc/download/complete/([0-9]+)/')
       ) AS product_id,
     FROM
      [project-id:oceanus_prod.oceanus_v3_bizocean_20170217] 
     WHERE
       url CONTAINS "https://www.bizocean.jp/doc/download/complete/" 
     GROUP BY
      uid,
      product_id)
  GROUP BY
     product_id
  ORDER BY
     count DESC) 
"""

bq.Query(DL_COUNT).results()

コード②

bq.Query(
"""
SELECT 
 product.product_id AS product_id,
 product.name,
 dl_count.count AS count
FROM 
 {DL_COUNT} AS dl_count
JOIN [project-id:bizocean.product] AS product
 ON dl_count.product_id = product.product_id
ORDER BY
 　　　count DESC

""".format(DL_COUNT = DL_COUNT)).results()

サブクエリ

上のコード①の、この部分がサブクエリです。

f:id:yuka_nakayama:20170314140216p:plain

サブクエリ内で一度まとめたデータをもとに、全体でさらにデータをまとめていきます。

コード①では、
サブクエリ内でユーザーID(uid)と
そのユーザーがダウンロードした書式のID(product_id)をグループ化しました。

正規表現

正規表現もコード①で使っていて、サブクエリ内の
REGEXP_EXTRACT(url, r'/doc/download/complete/([0-9]+)/')
この部分です。

書式ダウンロード完了時のURLには必ず「doc/download/complete/[書式ID(数字)]」が含まれるので、
「doc/download/complete/[書式ID(数字)]が含まれるURLの
書式IDの部分を表示しました。

コード①では、
サブクエリと正規表現を使って
ダウンロードされた書式のIDとそのダウンロード数を多い順に表示しました。

多い順に10個並べるとこうなります。

product_id count
1 100,532 53
2 104,566 48
3 102,415 45
4 102,372 43
5 100,115 41
6 102,334 38
7 104,478 33
8 103,104 32
9 519,931 31
10 518,138 31

JOIN句

これはコード②で使っています。
簡単にいうとデータテーブルとデータテーブルをくっつけるもので、
上のコードでは
　JOIN [project-id:bizocean.product] AS product
ON dl_count.product_id = product.product_id
別々のデータテーブルにある書式ID(product_id)(本質的に同じもの)を
JOIN句を使ってくっつけました。

dl_countというデータテーブルでは
ダウンロードされた書式のIDとそのダウンロード数、

productというデータテーブルでは
ダウンロードされた書式のID その書式の名前　が表示されているので、

共通項の書式IDでくっつけて
書式ID、書式の名前、ダウンロード数
を出しました。

product_id product_name count
1 100,532 請求書003 シンプルな請求書 53
2 104,566 （契約書雛形）業務委託契約書 48
3 102,415 御見積書 45
4 102,372 金銭貸借契約書（無利子・一括弁済・無担保）（借用書） 43
5 100,115 委任状01 41
6 102,334 工事請負契約書 38
7 104,478 事業計画書 33
8 103,104 書類送付のご案内-シンプル書類送付状 32
9 519,931 職務経歴書006 31
10 518,138 勤怠管理表001 31

まだごく簡単なことしかできませんが、
今日で行数がかなり増えた感じがします。
行数が増えるとカンマを忘れてしまったり
スペルミスをしてしまったりします。

そういったことに気を付けるのはもちろんですが、
エラーが出たときに間違いを見つけやすいように
きれいに整頓して書いたり、
こまめに動かしてみることが大切だと知りました。

＊備忘録

SELECTやWHEREなどの句の使い方にはある程度慣れてきましたが
GROUP BYがまだイマイチ分からなかったので
ここで備忘のために復習します。

「GROUP BY」句
…同じ値を持つ行をグループ化する。
たとえば、

ユーザーID:123456の人がAという書式を5回ダウンロードした
ユーザーID:234567の人がBという書式を1回ダウンロードした
ユーザーID:345678の人がCという書式を2回ダウンロードした

このとき、グループ化しないと

123456,A
123456,A
123456,A
234567,B
345678,C
345678,C

となるが、
重複する行を1つのグループとして、

123456,A
234567,B
345678,C

と出てくる。

（備忘録おわり）

ビズオーシャンでのインターン日記

プログラミング未経験の文系大学生がインターンシップで頑張る日記です

vol.5 Pandasとdataframe

Pandasについて（備忘録１）

dataframeについて（備忘録２）

dataframe、グラフ作成

vol.4　JOIN句の練習とpandas dataframe

JOINの練習

dataframeについて

vol.3　サブクエリ、正規表現、JOIN句

サブクエリ

正規表現

JOIN句

＊備忘録