読者です 読者をやめる 読者になる 読者になる

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

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

vol.6 データを数字に変換


4月に入ってからは
機械学習でデータ分析するための下準備を進めていますが、

機械学習でデータ分析をするには
すべてのデータを数字に変換する必要があるので
今回はそれを中心に書いていって、


そのほか、
わたしが新しく知った関数がいくつかあるので、
最後のほうに備忘録として書こうと思います。




ユーザーに関するあらゆるデータを数字に変換


といっても、ユーザーのデータには

①連続値:数字で説明できるデータ(従業員数や年収など)
②離散値:数字で説明できないデータ(役職や業種など)

があって、、、



①従業員数や年数など


これらは順番に並べられそうなので、
その順番通りに数字を振っていく。



例えば従業員数、

# employee_scale
member_df["employee_scale_num"] = member_df['employee_scale'].map({
    '1~9名':0,
    '10~49名':1,
    '50~99名':2,
    '100~299名':3,
    '300~499名':4,
    '500~999名':5,
    '1000~2999名':6,
    '3000~4999名':7,
    '5000~9999名':8,
    '10000名以上':9,
  })
#member_df


「1~9名」や「10~49名」は
そのままでは単なる文字列なので、
「map」で連続値に変換し、意味を持たせる。



②役職や業種など

こっちは数字にして並べることができないので、
真偽値に変換してデータフレームの表を埋めていく。

真偽値に変換するには
Pandasの「get_dummies()」を使用

bool_columns = ['managerial_position', 'job_spec1', 'industry']
pd.set_option("display.max_columns", 560)
member_df = pd.get_dummies(member_df, columns=bool_columns)


・bool_columns = ['managerial_position', 'job_spec1', 'industry']
役職、職種、業種などで同じ作業をしていくので、
「bool_columns」で括ると1回の作業で済む

・pd.set_option("display.max_columns", 560)
columns数を560に設定

・member_df = pd.get_dummies(member_df, columns=bool_columns)
get_dumiesで真偽値に変換
(既成のmember_dfのcolumns部分に[bool_columns]をもってきて)真偽値に変換、
変更を上書きされたデータフレームとしてのmember_dfを表示



f:id:yuka_nakayama:20170425110155p:plain


これはデータフレーム全体のごく一部でしかないので
分かりずらいが、


1番上のユーザーの役職は「その他」、
上から5番目のユーザーの役職は「一般社員・職員」

であることを示す

(真であれば1、偽であれば0)






備忘録

⑴ランダム抽出

機械学習のための下準備をするうえで
データが大きすぎると時間やお金がかかってしまうので、


ユーザー数を少なめにして、データを小さくしてスキャンを試みる。


たくさんのユーザーからたとえば100人のみをランダムに抽出するとき、

SELECTで
 rand() を使って全ユーザーに0以上1未満の数をランダムに振る、
 (振った数を random とおく)
ORDER BYで
 random を降順に並べる

で、LIMIT 100で絞ると、
結果としてユーザー100人をランダム抽出できたことになる。


⑵日数の差を求める

例えば入会日から現在までの日数を求めるとき、


DATEDIFFで期間(日数)を求める
期間…CURRENT_TIMESTAMP(サーバーの現在の日時) と
   TIMESTAMP(create_date)(入会した日時)  の期間

DATEDIFF(CURRENT_TIMESTAMP(),TIMESTAMP(create_date)) as create_date_duration

vol.5 Pandasとdataframe


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

その前に備忘録として


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


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




Pandasについて(備忘録1)

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


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


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



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



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

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

という感じでしょうか。

dataframeについて(備忘録2)


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

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_DLPRODUCT_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()





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

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.2 SQL初歩的なデータ抽出とグラフ作成

こんにちは

2月14日(インターン2日目)は、

Google BigQueryにある実際のデータを使ってSQLをぽちぽちしてみました。

SQLは、データの管理や操作などができるデータベース言語として広く普及しています。








現時点ではまだすごく簡単なコードですが、とりあえずどこに何を書けばいいのかがよく分からず、何度もエラーを出してました…。




うーーん、、どこに何を書けばいいのか、というのが本当に難しかったです。

この日は「SELECT」、「FROM」、「WHERE」、「GROUP BY」、「ORDER BY」、「LIMIT」の句だけを使ったのですが、句を並べるのにも順番があって、

句の中も、カンマで区切るとかカッコで括るとか、ややこしいな~という。

それで数時間奮闘していたのですが、なんとなく、なんとなく出来てきた気がします。

エラーの頻度はかなり減りました。







できたのは、

①男女別の年齢ごとの会員数

コード…

SELECT
 gender,
 age,
 count(gender) as count
FROM
 [project-id:bizocean.member]
WHERE
 gender ="male"
GROUP BY
 gender,
 age
ORDER BY
 count desc
LIMIT 1000    

女性の年齢別会員数はmaleをfemaleにしました。

f:id:yuka_nakayama:20170220172041p:plain

②業種別会員数

コード…

SELECT
 industry ,
 count( industry ) as count
FROM
 [project-id:bizocean.member]
GROUP BY
 industry
ORDER BY
 count desc
LIMIT 1000  

f:id:yuka_nakayama:20170220172138p:plain

③一番会員が多い業種の職種別会員数

コード…

SELECT
 industry,
 job_spec1,
 count( job_spec1) as count
FROM
  [project-id:bizocean.member]
WHERE
  industry CONTAINS "小売"
GROUP BY
 industry,
 job_spec1
ORDER BY
 count desc
LIMIT 1000

f:id:yuka_nakayama:20170220172238p:plain

でした。

(グラフタイトルなどがちゃんとしていないのは目をつぶってください…)




②で小売・卸売・商社の会員が一番多いと分かったので、

③でその会員の職種を見てみました。

なぜ小売・卸売・商社が多いのか、なぜその中でも営業職や経営者が多いのか、

その日はもう時間がなくできませんでしたが

次回のインターンで、その会員の方たちがどういう書式をダウンロードしているのかなどを見て分析を進めていきたいと思いました。

vol.1 初日

はじめまして

 

このブログでは、

プログラミング未経験の文系大学生が

インターンシップを通して成長していく過程を伝えていきたいと思います。

 

 

自己紹介

私は都内の大学に通っている女性で、現在2年生です。

上記の通り、プログラミング経験はありません。

 

高校生の時から就職は衣料品メーカーを考えていて、商品開発をしてみたいけどデザインできないし、うーん、、と思いながら大学に進学しました。

大学では経営学科に属していますが、統計学系の講義をいくつか受けたり、他大学の知り合いが統計学をやっていたりして、データ分析の存在を知りました。

それで、データ分析ができれば商品開発に携われる!来年の春から始まる就活までにある程度のプログラミングスキルを身に着けたい!と思い、

ビズオーシャンのエンジニアリング部でインターン生として雇っていただくことになりました。

 

 

株式会社ビズオーシャンの紹介

株式会社ビズオーシャンは、もともとは株式会社ミロク情報サービスという財務や会計システムなどのサービスを提供する会社の一部署でした。

 

が、経営責任の明確化や投資効率の最大化などを目的として2016年4月に完全に分社化しました。

ビズオーシャンが運営している同名のサイト「bizocean」では、事業計画書、契約書、スケジュール管理表など、主にビジネスシーンで活用できるテンプレートを会員に無料で提供しています。

2017年2月現在、テンプレートは20,000点以上ありますが、約200万人の会員にメールマガジン送信することで、無料提供を可能にしています。

 

 

 

ビズオーシャンのサイトでは、会員登録のときに

勤めている会社の業態、勤務地、年齢なども登録するのですが、

こういう情報から、いつ、どんな人が、どんなテンプレをダウンロードしているのか、など細かく分析できるようになれたらなー思います。

 

 

今朝初めてオフィスに来たのですが、

雰囲気がわりとラフな感じで、みなさん優しそうな方ばかりなので、

色々と教えてもらいながら頑張っていこうと思います。