あつまるエンジニアブログ

集客プラットフォーム事業を手がける株式会社あつまるのエンジニアブログです

Google Analytics - セグメントの理解

f:id:todays_mitsui:20160313223156j:plain

どうも、株式会社あつまるでアクセス解析の自動化などをやっている三井です。

はじめに

この記事はデベロッパー向けに Google Analytics Core Reporting API のセグメントについて掘り下げて解説する記事です。
Google Analytics の初心者向けに「セグメントとは?」と解説するものではありません

Reporting API で動的セグメントを最大限カスタマイズして利用する場合には必要になってくる知識かと思います。
逆に、ライトに『API でセッション数と直帰率とを取得したいだけ~』というような場合には必要性は薄いでしょう。


また、この記事はセグメントについての完全な解説を目指すものではありません。
公式のドキュメントで見落としがちな箇所(実際に私が見落とした箇所)について、図と文章を加えて説明を試みるものです。

セグメントについて何も知らない方は、まずは公式ドキュメントを読みましょう。


導入

セグメント自体についてはGoogle公式のドキュメントで

の2ページに渡って解説してあります。

その他に、セグメントの条件指定には指標(metrics)ディメンション(dimensions)について理解しなければいけないため、

も参照する必要があるでしょう。


以降ではこれらを踏まえてセグメントの全体像について解説します。


セグメント と セグメント条件(segmentCondition)

公式ドキュメントの動的セグメントの構文リファレンスの項によれば、

セグメントは、単一または複数のセグメント条件(segmentCondition)によって構成される

と書かれています。

ここで言う「単一のセグメント条件」がセグメントに設定したい条件の最小単位だと思えば理解がしやすいでしょう。

もちろん、「単一のセグメント条件」を複数個組み合わせてより高度なセグメントを設定することもできますが、解説は後ほどに回します。
まずは「単一のセグメント条件」がどんな構造で出来ているか見ていきましょう。


単純な例を挙げます、
少なくとも 1 つのセッションで Chrome ブラウザを使用したユーザー」だけをふるい分けるセグメント条件は次のように書けます。

users::condition::ga:browser==Chrome

この例を読み解くためには、セグメント条件が階層構造を持っていることを知らなければいけません。


セグメント条件(segmentCondition)の階層構造

セグメント条件は階層構造を持っています。
それぞれの階層は :: で区切られて、全部で3層構造になっています。

f:id:todays_mitsui:20160313223212p:plain

第1階層はスコープ(conditionScope)、第2階層はタイプ(conditionType)、第3階層は条件(dimensionOrMetricConditions)と呼ばれます。


他にも タイプ と 条件 の間に perUserperSession のような指標スコープ(metricScope)が入る事がありますが、これは 条件 に紐付くフラグのようなもので、階層構造とは扱いが異なるものです。

f:id:todays_mitsui:20160313223220p:plain


それぞれの階層について個別に見ていきましょう。


スコープ - ユーザースコープ(users) と セッションスコープ(sessions)

第1階層のスコープ(conditionScope)には、ユーザースコープ(users) と セッションスコープ(sessions) のいずれかを設定することができます。

これらは集計の際にユーザー毎にふるい分けるか、セッション毎にふるい分けるかを指定するものです。


タイプ - 条件(condition) と シーケンス条件(sequence)

第2階層のタイプ(conditionType)には、条件(condition) と シーケンス条件(sequence) のいずれかを設定することができます。

条件(condition) はいわるゆ基本的な条件指定だと思ってください。
それに対して、シーケンス条件(sequence) は条件の強化版のようなもので、「○○した後に□□したユーザー」といった前後関係(ステップ)を指定した条件を設定することが出来ます。

これについては後ほど、セグメント条件の合成の項で解説を加えます。


条件 - 指標条件(metricCondition) と ディメンション条件(dimensionCondition)

第3階層の条件(dimensionOrMetricConditions)には、指標条件(metricCondition) と ディメンション条件(dimensionCondition) のいずれかを設定することができます。

それぞれ指標(metrics)ディメンション(dimension)に対して条件を設定するという意味では言葉のとおりですね。

目立った違いとしては、
指標(metrics)には後述する指標レベルという概念が存在し、指標スコープ(metricScope)を加えることで指標レベルを引き上げることが挙げられます。


セグメント条件(segmentCondition)の階層構造 まとめ

上記をまとめると、「単一のセグメント条件」は下の図のように8種類に分類できることになります。

f:id:todays_mitsui:20160313223318p:plain

一番最初の例、

users::condition::ga:browser==Chrome

では、ユーザー毎に - 条件を設定して - ディメンション(ブラウザ)でふるい分けするという意味になります。


続いては、この「単一のセグメント条件」を組み合わせた、さらに複雑なセグメント条件について解説します。


セグメント条件(segmentCondition)の合成

「複数の条件を組み合わせる」と聞いてまず思い浮かべるのは ANDOR で条件を合成することでしょうか。
Google Analytics においてももちろんそのような設定は可能です。

が、この項で特筆したいのはセグメントは各階層ごとに合成できるということです。
正直、これがややこしさの原因でもありますね。


ユーザースコープとセッションスコープは AND演算子 で合成できます。
同様に条件タイプとシーケンス条件タイプも AND演算子 で合成できます。

条件タイプ下の条件は AND演算子, OR演算子 で合成でき、シーケンス条件タイプ下の条件は AND演算子, OR演算子, FOLLOWED BY演算子, IMMEDIATELY FOLLOWED BY演算子 で合成できます。

言葉だけではアレなので図を加えておきます。

f:id:todays_mitsui:20160313223331p:plain

複雑にしようと思えばここまで出来るという例ですね。


条件の合成 - AND演算子 と OR演算子

説明のため、第3階層の条件にだけ着目します。
先ほどの例でも見たように、「ブラウザが Chrome であること」はga:browser==Chromeというディメンション条件によって指定できるのでした。


では「ブラウザが Chrome で、かつ、所在地がロンドン」ということを条件に設定したい場合はどうでしょう。

このような条件指定は AND演算子(;) によって実現できます。
具体的にはga:browser==Chrome;ga:city==London とすることで、「ブラウザが Chrome で、かつ、所在地がロンドン」を表現できます。

スコープとタイプを補って完全な形のセグメント条件を書くと、

users::condition::ga:browser==Chrome;ga:city==London

これで、「ブラウザが Chrome で、かつ、所在地がロンドン のユーザー」をふるい分けることができます。


同様に OR演算子(,) を使えば、ga:browser==Chrome,ga:city==London という書き方で「ブラウザが Chrome か、または、所在地がロンドン」という条件を表現できます。

完全な形で書くと、

users::condition::ga:browser==Chrome,ga:city==London

ですね。


条件の否定 - NOT演算子

合成とは少し違うのですが、
NOT演算子(!) を使えば、「条件に一致したユーザー(またはセッション)を集計に含めない」という条件を表現できます。

書き方は、条件の先頭を ! からはじめて、

users::condition::!ga:browser==Chrome

このように。
これで「少なくとも一つのセッションで Chrome ブラウザを使っているユーザーを除いた」集計を指定することができます。


紛らわしい表現として、

users::condition::ga:browser!=Chrome

を例に挙げておきましょう。
このような書き方をした場合には、「少なくとも一つのセッションで Chrome 以外のブラウザを使っているユーザーを含めた」集計の指定です。


シーケンス条件の合成 - FOLLOWED BY演算子 と IMMEDIATELY FOLLOWED BY演算子

タイプにシーケンス条件(sequence)を指定した場合、AND演算子 と OR演算子 に加えて、FOLLOWED BY演算子(;->>) と IMMEDIATELY FOLLOWED BY演算子(;->) を使うことができます。
これらはユーザーに対して別セッションに対する条件を記述できるという意味で、条件(condition)の強化版になっています。


FOLLOWED BY演算子(;->>) を使うことで、「前のステップで○○して、その後のステップで□□した」という条件を表現できます。

例を挙げると、

users::sequence::ga:deviceCategory==desktop;->>ga:deviceCategory==mobile

これで、「デスクトップからアクセスして、その後のセッションでモバイルからアクセスしたユーザー」を指定できます。


IMMEDIATELY FOLLOWED BY演算子(;->) の場合は、「前のステップで○○して、直後のステップで□□した」という意味合いになるので、

users::sequence::ga:deviceCategory==desktop;->>ga:deviceCategory==mobile

これで、「デスクトップからアクセスした直後のセッションでモバイルからアクセスしたユーザー」を指定できます。


さらに、タイプがシーケンス条件(sequence)の場合、^演算子を使って「最初のステップで○○して、その後のステップで~」といった条件を表現できるようなのですが、あまりにも煩雑になるため解説は割愛します。

詳しくは Core Reporting API - セグメント - 2. 条件とシーケンスの使用 - シーケンス を参照してください。


タイプの合成 - AND演算子

条件やシーケンス条件は、さらに AND演算子(;) で合成することができます。
つまり、条件とシーケンス条件を併用することができるのです。

ただし、合成に使える演算子は AND演算子(;) のみで、OR演算子(,) を使うことはできません。


スコープの合成 - AND演算子

ユーザースコープのセグメント条件とセッションスコープのセグメント条件もまた、AND演算子(;) で合成して併用することができます。
この場合も合成に使える演算子は AND演算子(;) のみで、OR演算子(,) を使うことはできません。

まずユーザースコープのセグメント条件でユーザーがふるい分けられ、選ばれたユーザーのセッションの中からセッションスコープのセグメント条件に一致するセッションのデータが集計されます。


合成には冗長な表現が許される

さぁ、これこそ私がセグメントの理解に時間を要した最大の要因です。

「ブラウザが Chrome で、かつ、所在地がロンドンのユーザー」をふるい分けるセグメント条件の例。

users::condition::ga:browser==Chrome,ga:city==London

これと同じ意味のセグメント条件を次のような2通りの表現で書くことができます。

users::condition::ga:browser==Chrome,condition::ga:city==London

または

users::condition::ga:browser==Chrome,users::condition::ga:city==London


このように Google Analytics はセグメント条件を合成した際の冗長な表現をできる限り受け入れる設計になっているようです。


逆に、

  • スコープが同じセグメント条件同士が合成されている場合、後者のスコープを省略できる
  • スコープもタイプも同じセグメント条件同士が合成されている場合、後者のスコープとタイプを省略できる

というルールがある、と解釈することもできます。


指標(metrics)のレベル

この後の指標スコープ(metricScope)につながる話題として、指標レベルについて言及しておきます。

Google Analytics で利用可能な指標の中には ページ滞在時間(ga:timeOnPage) のようにヒット(単一アクセス)毎に意味をもつものもあれば、 平均ページ滞在時間(ga:avgTimeOnPage) のようにセッション毎の括りで集計しなければ意味がないものもあります。
または、新規セッション率(ga:percentNewSessions) などはユーザー毎の括りで集計してはじめて意味をもつ指標です。

このように各指標には、その指標が集計上の意味を持ち始める括りが存在し、その括りを指標(metrics)のレベルと呼びます。

上記の例で言えば、ページ滞在時間(ga:timeOnPage) のレベルはヒット(Hit)、平均ページ滞在時間(ga:avgTimeOnPage)のレベルはセッション(Session)、新規セッション率(ga:percentNewSessions)のレベルはユーザー(User) ということになります。


指標スコープによる指標レベルの引き上げ

指標が集計上の意味を持ち始める括りを指標(metrics)のレベルと呼んでいることを紹介しました。
では、各指標はその指標のレベルでのみ意味を持つのでしょうか?
前節と同じ例に当てはめるとすれば、ページ滞在時間(ga:timeOnPage) を使って指定した ga:timeOnPage>60 はヒット毎にしか意味を持たないのでしょうか?

答えは No です。
ga:timeOnPage>60 をセッション毎の括りで解釈すれば「いずれかのセッションでのページ滞在時間の合計が60秒を超えるユーザー」をふるい分ける条件と読むことができるでしょうし、同じくユーザー毎の括りで解釈すれば「全セッションを合計してページ滞在時間が60秒を超えるユーザー」をふるい分ける条件と読むことができるでしょう。

このように「どの括りで集計したときの値を条件に用いるか」という微妙な文脈を表現するために、セグメントの指標条件には指標スコープのための修飾子を付けて指標のレベルを引き上げることができます。


指標スコープのための修飾子は全部で三つ、perHit::, perSession::, perHit:: があります。

微妙な文脈の違いを理解するのはなかなかに難しい事です。実例を見てもらう方が早いでしょう。

  • users::condition::perHit::ga:timeOnPage>60
    → いずれかのヒットでページ滞在時間が60秒を超えるユーザー

  • users::condition::perSession::ga:timeOnPage>60
    → いずれかのセッションでページ滞在時間の合計が60秒を超えるユーザー

  • users::condition::perUser::ga:timeOnPage>60
    → 全てのセッションでのページ滞在時間の合計が60秒を超えるユーザー

こんなのもあります。

  • sessions::condition::perHit::ga:timeOnPage>60
    → いずれかのヒットでページ滞在時間が60秒を超えるセッション

  • sessions::condition::perSession::ga:timeOnPage>60
    → 1セッションを通したページ滞在時間の合計が60秒を超えるセッション

スコープ と 指標スコープ の組み合わせによって条件の意味が微妙に違ってくるわけですね。


では指標スコープを省略した場合には?
その際にはスコープのレベルがデフォルトの指標スコープとして適用される、と公式ドキュメントに書いてあります。

例えば、

  • users::condition::ga:timeOnPage>60
    users::condition::perUser::ga:timeOnPage>60 と同じ

  • sessions::condition::ga:timeOnPage>60
    sessions::condition::perSession::ga:timeOnPage>60 と同じ

といった感じになります。


引き上げの条件

「スコープ と 指標スコープ の組み合わせによって条件の意味が微妙に違ってくる」と書きましたが、では全ての組み合わせが別の意味を持って許されるかというと、そういう訳ではありません。

条件の意味としてあり得ないものは無効(invalid) とされます。

例えば、sessions::condition::perUser::ga:timeOnPage>60 を無理やりに解釈すると、「全てのセッションでのページ滞在時間の合計が60秒を超えるセッション」となり意味が通らないため、このようなセグメント条件は無効です。


どのような組み合わせで引き上げ可能なのか、逆にどのようなときは無効なのか。それについてはシンプルな公式があります。

f:id:todays_mitsui:20160313223355p:plain

この図にあるように、指標スコープの指定は常に スコープ と 指標のレベルの間でなければいけません。
指標に割り当てられているレベルは公式ドキュメントにリファレンスが附属しています。指標: プライマリ スコープ リファレンス を参照してください。


Tips

ここからは Google Analytics を深掘りしていく上で知っておくと便利な Tips を紹介します。


日本語名と英語名の変換

Google Analytics はお馴染みの管理画面もすでに日本語化が行き届いています。「平均滞在時間」「直帰率」「新規セッション率」など、日本語名で書かれればなじみ深い言葉も多いのではないでしょうか。

しかし、日本語名に馴染みすぎていることが Reporting API を活用する上では逆にハードルになります。
API で指標を指定するとき、セグメントを指定するとき、そのときには英語名での入力を迫られます。突然「直帰率の英語名を書け」と言われても戸惑いますよね。

というわけで、日本語名と英語名の変換方法を紹介します。


ずばり、いつもの管理画面を利用しましょう。
英語名を知りたい指標やディメンションを組み込んでカスタムレポートを作成しましょう。

f:id:todays_mitsui:20160313223410p:plain

レポートを保存したら、メニューから表示言語を「US English」に変更します。

f:id:todays_mitsui:20160313223419p:plain

f:id:todays_mitsui:20160313223428p:plain

もう一度カスタムレポートの設定画面を見に行くと...

f:id:todays_mitsui:20160313223435p:plain

ご覧のように指標・ディメンションの英語名を見ることができます。
闇雲にググって調べるよりも確実でスムースです。


Query Explorer の利用

Reporting API に投げるクエリを設計するうえで一番の近道は試行錯誤です。
とにかく様々なディメンション・様々なセグメントでクエリを投げて見て Analytics 内部での集計の仕組みを感じ取ることが最も早いでしょう。まさに習うより慣れろの世界です。

そんな試行錯誤に欠かせないのが Google が公式に提供している Query Explorer というツールです。

f:id:todays_mitsui:20160313223444p:plain

画面上でアカウントの認証をするだけで、集計期間・指標・ディメンション・フィルター・セグメントなどを様々に変え集計を簡単に試すことができます。
いつもの管理画面よりも細かい事ができるので Google Analytics を極めたいと思ったならば親しんでおくことをおすすめします。


まとめ

いささか散文的になりましたが、Google Analytics のセグメント集計の知識についてできる限りまとめてみました。

いま書いておかないと、せっかく必死で体得した知識をあっという間に忘れてしまいますからね。こっちも必死です。


私からは以上です。

Python3 で言語処理100本ノック 2015 - 第2章

どうも、株式会社あつまるで Python 製の社内ツールなどを作っている三井です。
まさか続くとは。

乾・岡崎研究室が公開している 言語処理100本ノック 2015 に取り組んで行きます。
使用する言語は Python3 です。

第2章まで出来たんでまとめます。


第2章: UNIXコマンドの基礎

hightemp.txtは,日本の最高気温の記録を「都道府県」「地点」「℃」「日」のタブ区切り形式で格納したファイルである.
以下の処理を行うプログラムを作成し,hightemp.txtを入力ファイルとして実行せよ.さらに,同様の処理をUNIXコマンドでも実行し,プログラムの実行結果を確認せよ.

hightemp.txt の内容はこんな感じ、

高知県 江川崎 41 2013-08-12
埼玉県 熊谷 40.9 2007-08-16
岐阜県 多治見 40.9 2007-08-16
山形県 山形 40.8 1933-07-25
山梨県 甲府 40.7 2013-08-10
和歌山県 かつらぎ 40.6 1994-08-08
静岡県 天竜 40.6 1994-08-04
山梨県 勝沼 40.5 2013-08-10
埼玉県 越谷 40.4 2007-08-16
群馬県 上里見 40.3 1998-07-04
愛知県 愛西 40.3 1994-08-05
千葉県 牛久 40.2 2004-07-20
静岡県 佐久間 40.2 2001-07-24
愛媛県 宇和島 40.2 1927-07-22
山形県 酒田 40.1 1978-08-03
岐阜県 美濃 40 2007-08-16
群馬県 前橋 40 2001-07-24
千葉県 茂原 39.9 2013-08-11
埼玉県 鳩山 39.9 1997-07-05
大阪府 豊中 39.9 1994-08-08
山梨県 大月 39.9 1990-07-19
山形県 鶴岡 39.9 1978-08-03
愛知県 名古屋 39.9 1942-08-02

タブ区切り形式です。


以下の課題は基本的に Python でコーディングして結果の検算を UNIX コマンドで行います。

もう一つ、第2章に取り組むにあたって方針を立てます。 入力ファイルが巨大なものになってもメモリを圧迫せずに動作する というものです。
テキストファイルを一気に全て読み込むことは避けて、なるべく行毎に読み込むコードに落とし込みます。

10. 行数のカウント

行数をカウントせよ.確認にはwcコマンドを用いよ.

Python

import codecs

count = sum(1 for line in codecs.open("./src/hightemp.txt", "r", "utf-8"))

print(count)
# => 24

というわけでジェネレーター内包表記です。

つづいて UNIX コマンドを用いた場合、

Bash

cat ./src/hightemp.txt | wc -l
# => 24

いいですね。


11. タブをスペースに置換

タブ1文字につきスペース1文字に置換せよ.確認にはsedコマンド,trコマンド,もしくはexpandコマンドを用いよ.

Python

import codecs

for line in codecs.open("./src/hightemp.txt", "r", "utf-8"):
    print(line.replace("\t", " "), end="")
# =>
# 高知県 江川崎 41 2013-08-12
# 埼玉県 熊谷 40.9 2007-08-16
# ...

実質的には .replace() してるだけです。

Bash

cat ./src/hightemp.txt | sed -e 's/\t/ /g'
# =>
# 高知県 江川崎 41 2013-08-12
# 埼玉県 熊谷 40.9 2007-08-16
# ...

sed 好きなんですよね。


12. 1列目をcol1.txtに,2列目をcol2.txtに保存

各行の1列目だけを抜き出したものをcol1.txtに,2列目だけを抜き出したものをcol2.txtとしてファイルに保存せよ.
確認にはcutコマンドを用いよ.

Python

import codecs

with codecs.open("./dest/col1.txt", "w", "utf-8") as f1 \
     , codecs.open("./dest/col2.txt", "w", "utf-8") as f2:
    for line in codecs.open("./src/hightemp.txt", "r", "utf-8"):
        cols = line.split("\t")

        f1.write(cols[0]+"\n")
        f2.write(cols[1]+"\n")

with 記法を使ってます。open() と with 記法を同時に使うと1行が長くなりがちなのがちょっと嫌ですね。

Bash

[ -d dest ] || mkdir dest

cat ./src/hightemp.txt | cut -f1 > dest/col1.txt
cat ./src/hightemp.txt | cut -f2 > dest/col2.txt

cut したものをリダイレクトするだけ。便利ですね。


13. col1.txtとcol2.txtをマージ

12で作ったcol1.txtとcol2.txtを結合し,元のファイルの1列目と2列目をタブ区切りで並べたテキストファイルを作成せよ.
確認にはpasteコマンドを用いよ.

Python

import codecs

with codecs.open("./dest/col1.txt", "r", "utf-8") as rf1 \
     , codecs.open("./dest/col2.txt", "r", "utf-8") as rf2 \
     , codecs.open("./dest/col1+2.txt", "w", "utf-8") as wf:
    for col1, col2 in zip(rf1, rf2):
        wf.write("{0}\t{1}\n".format(col1.strip(), col2.strip()))

面倒だー。ファイルを3つも開いて、さらにその中でループ回してるのでコードが非常にもっさりしてます。

Bash

[ -d dest ] || mkdir dest

paste ./dest/col1.txt ./dest/col2.txt > ./dest/col1+2.txt

一撃!UNIX コマンド便利!


14. 先頭からN行を出力

自然数Nをコマンドライン引数などの手段で受け取り,入力のうち先頭のN行だけを表示せよ.確認にはheadコマンドを用いよ.

Python

import sys
import codecs
import itertools

count = int(sys.argv[1])

with codecs.open("./src/hightemp.txt", "r", "utf-8") as f:
    for line in itertools.islice(f, 0, count):
        print(line, end="")

itertools.islice() を使えばイテレーターを対象にしてスライスが可能なようで。

Bash

cat ./src/hightemp.txt | head -n $1

表示だけが目的ならこれで充分ですね。


15. 末尾のN行を出力

自然数Nをコマンドライン引数などの手段で受け取り,入力のうち末尾のN行だけを表示せよ.確認にはtailコマンドを用いよ.

Python

import sys
import codecs
import itertools

count = int(sys.argv[1])
max_count = sum(1 for line in codecs.open("./src/hightemp.txt", "r", "utf-8"))

with codecs.open("./src/hightemp.txt", "r", "utf-8") as f:
    for line in itertools.islice(f, max_count - count, None):
        print(line, end="")

itertools.islice() は引数にマイナスの数を使えないようです。 f[-5:] みたいに書けると便利なんですが。

Bash

cat ./src/hightemp.txt | tail -n $1

先ほどと同様です。


16. ファイルをN分割する

自然数Nをコマンドライン引数などの手段で受け取り,入力のファイルを行単位でN分割せよ.同様の処理をsplitコマンドで実現せよ.

Python

import sys
import codecs

def line_counts(max_count, n):
    quo = max_count // n
    rem = max_count % n

    return [quo+1] * rem + [quo] * (n - rem)

n = int(sys.argv[1])
max_count = sum(1 for line in codecs.open("./src/hightemp.txt", "r", "utf-8"))

with codecs.open("./src/hightemp.txt", "r", "utf-8") as rf:
    for i, line_count in enumerate(line_counts(max_count, n)):
        with codecs.open("./dest/split.{0}.txt".format(i), "w", "utf-8") as wf:
            for _ in range(line_count):
                wf.write(rf.readline())

line_counts() という関数を定義しています。
この関数は整数 max_count をN分割します。戻り値は整数のリストで、各要素は高々1しか差が無いように調整されます。

具体的に、

line_counts(13, 5)
# => [3, 3, 3, 2, 2]

この関数を使って、例えば入力ファイルの行数が13行で、それを5分割するなら、出力ファイルの行数は3行, 3行, 3行, 2行, 2行にするという方針です。

Bash

これの UNIX コマンドでの解き方が分からなかったんですよね。
split コマンドを素朴に使うと「N分割」ではなく「M行毎に分割」という感じになるので。

というわけで カンニングしました
これをシェルスクリプトで書きたくはないですね、個人的に。


17. 1列目の文字列の異なり

1列目の文字列の種類(異なる文字列の集合)を求めよ.確認にはsort, uniqコマンドを用いよ.

Python

import codecs

prefs = set(line.split("\t")[0] for line in codecs.open("./src/hightemp.txt", "r", "utf-8"))

print(prefs)
# =>
# {
#     '埼玉県', '千葉県', '群馬県', '山形県', '静岡県', '愛知県',
#     '高知県', '岐阜県', '山梨県', '愛媛県', '和歌山県', '大阪府'
# }

unique な集合を得るのが目的なので set を使いました。

Bash

cat ./src/hightemp.txt | cut -f1 | sort | uniq
# =>
# 愛知県
# 愛媛県
# 岐阜県
# 群馬県
# 高知県
# 埼玉県
# 山形県
# 山梨県
# 静岡県
# 千葉県
# 大阪府
# 和歌山県

パイプで繋ぐだけでデータを加工していけている感覚、いいですね。


18. 各行を3コラム目の数値の降順にソート

各行を3コラム目の数値の逆順で整列せよ(注意: 各行の内容は変更せずに並び替えよ).
確認にはsortコマンドを用いよ(この問題はコマンドで実行した時の結果と合わなくてもよい).

Python

import codecs

sorted_lines = sorted(
    codecs.open("./src/hightemp.txt", "r", "utf-8"),
    key=lambda line: float(line.split("\t")[2]),
    reverse=True,
)

print("".join(sorted_lines))
# =>
# 高知県  江川崎  41      2013-08-12
# 埼玉県  熊谷    40.9    2007-08-16
# 岐阜県  多治見  40.9    2007-08-16
# 山形県  山形    40.8    1933-07-25
# 山梨県  甲府    40.7    2013-08-10
# ...

sorted() の機能をフル活用しています。

Bash

# -n オプション: 対象を数値としてソート
# -r オプション: 降順(逆順)ソート
# -k3 オプション: タブ区切りの3列目を比較対象として各順をソート
cat ./src/hightemp.txt | sort -nrk3
# =>
# 高知県  江川崎  41      2013-08-12
# 埼玉県  熊谷    40.9    2007-08-16
# 岐阜県  多治見  40.9    2007-08-16
# 山形県  山形    40.8    1933-07-25
# 山梨県  甲府    40.7    2013-08-10
# ...

sort コマンド便利ですね。タブ区切り形式と相性が良い。


19. 各行の1コラム目の文字列の出現頻度を求め,出現頻度の高い順に並べる

各行の1列目の文字列の出現頻度を求め,その高い順に並べて表示せよ.確認にはcut, uniq, sortコマンドを用いよ.

Python

import codecs
from collections import Counter

pref_counter = Counter(line.split("\t")[0] for line in codecs.open("./src/hightemp.txt", "r", "utf-8"))

print(pref_counter.most_common())
# =>
# [
#     ('山形県', 3), ('埼玉県', 3), ('群馬県', 3), ('山梨県', 3), ('岐阜県', 2),
#     ('愛知県', 2), ('千葉県', 2), ('静岡県', 2), ('愛媛県', 1), ('高知県', 1),
#     ('大阪府', 1), ('和歌山県', 1)
# ]
# ...

collections.Counter を使います。そのために用意されてるモジュールなので。

Bash

cat ./src/hightemp.txt | cut -f1 | sort | uniq -c | sort -rk1
# =>
# 3 山梨県
# 3 山形県
# 3 埼玉県
# 3 群馬県
# 2 千葉県
# 2 静岡県
# 2 岐阜県
# 2 愛知県
# 1 和歌山県
# 1 大阪府
# 1 高知県
# 1 愛媛県

uniq コマンドに -c オプションを渡すことで要素の出現回数をカウントしてくれるようになるんですね。これはいい。
sort を2回しちゃってるところが若干気になりますが。


所感

全体的に UNIX コマンドの便利さを身体で分からせるための出題ですね。勉強になりました。


私からは以上です。


その他の章の回答はこちらから

毎月数時間を要していたスキャンデータ整理をOCRで自動化した

f:id:todays_mitsui:20161119134329j:plain

どうも、株式会社あつまるで財務経理部を全力サポートしている三井です。


企業活動をするなかで見積書や請求書といった書類を発送するシーンは多いですよね。
私が勤める会社でもそういった書類をクライアントに郵送していますが、郵送する前の書類をスキャンしてスキャンデータを残しておく決まりになっています。

書類を作るのに必要なデータはすべて手元にあるものの、現物のスキャンデータがあれば安心なのも分かります。
書類に押したハンコを記録しておく意味もあるのかも知れません。


スキャンしたPDFの整理が負担に

しかし、毎月何百枚という書類のスキャンを取り発送するなかで、スキャンデータを整理する作業が負担になっていました。

スキャンを取る作業自体は書類の束をスキャナーに突っ込むだけなのですが、そうやって出来上がったPDFファイルはファイル名が 無機質な連番 になっています。
後で参照するときに目的のスキャンデータを探すことを考えると、一つひとつに適切なファイル名を付け直しておく必要があるわけです。

数百というファイルに適切な名前を付けるのは、単純ながら手間の掛かる作業です。
これまでは手作業でデータ整理をしていたようですが、スキャンデータのリネームだけで 毎月数時間を掛けていた ようです。
必要な作業とはいえあまり生産的とも思えませんし、今回は OCR 技術を使ってこのスキャンデータの整理を自動化してみました。

(※ OCR: Optical character recognition, 光学文字認識)


名前の付け方を確認しよう

最初にスキャンデータに付けるべき 適切なファイル名 について確認しました。

だいたいどの書類にも右上に「注文番号」と「発行した日付」が印字されています。
スキャンデータのPDFに付けるファイル名は注文番号と発行日付を単に並べたものでいいそうです。

f:id:todays_mitsui:20161119133353p:plain

(※画像は Image です)

例えば、注文番号が「123456」で発行日付が「2016年11月15日」であれば、ファイル名は「123456_20161115.pdf」といった具合ですね。

なるほど、

このファイル名のルール、自動化するにはなかなか都合がよさそうです。
一つずつ見ていきましょう。


1. 読み取る書類は全て弊社のフォーマット

書類毎にフォーマットがバラバラになってしまうと、求めている文字を読み取るだけでもかなり難しい課題になってしまいます。

しかし全てが自社フォーマットであればレイアウトも記載されている内容も揃っています。読み取る場所で悩むことはなさそうです。


2. 読み取り対処の文字は全て活字

手書き文字でもOCRで読めなくはないと思いますが、識字率はかなり低くなってしまうでしょう。
全てが活字で印字されているのは、正確に文字認識するうえでは理想的な条件です。


3. 読み取る文字は数字のみ

数字はたったの10種類しかなく、線もシンプルなのでもっとも文字認識しやすい対象だと云えます。
ひらがな・カタカナ・漢字混じりの文書をOCRで読もうとするとある程度の誤読を覚悟しなければいけませんが、対象が数字のみであることが事前に分かっていれば良い精度を出せそうです。

そんなわけで、充分な勝算があると踏んだ私はこのPDF整理自動化プロジェクトを粛々と進めることにしました。


自動化の方針

f:id:todays_mitsui:20161119125144p:plain

続いて自動化の方針を決定します。
自動化のプログラムはPythonで書くことにしました。具体的には以下のような手順です。

  1. PDFMiner で PDF ファイルから画像データの抜き出し
  2. 画像データ(生バイナリ)を PIL の Image オブジェクトに変換
  3. Tesseract で文字認識
  4. PDF ファイルを複製しつつリネーム

一つずつ解説します。


各工程の詳細

1. PDFMiner で PDF ファイルから画像データの抜き出し

今回は Tesseract という OCR ツールで画像の中の文字を読み取りますが。この Tesseract は JPEG や PNG などの形式しか受け付けてくれません。
PDF を直接読ませることができないので、事前準備として対応した形式に変換する必要がありました。


PDF をレンダリングして画像にする方法もあるようですが、下準備が煩雑で挫折...。
仕方なく今回は PDF を画像に変換するのではなく、PDF に埋め込まれた画像データを抽出する方法を採りました。

画像の抽出には PDFMiner という Python のライブラリを使います。
以下のコードを実行すると PDF に埋め込まれた全ての画像を取得することが出来ます。

from pdfminer.pdfinterp import PDFResourceManager
from pdfminer.pdfinterp import PDFPageInterpreter
from pdfminer.layout import LAParams, LTTextBox, LTTextLine, LTImage,  LTFigure
from pdfminer.converter import PDFPageAggregator


def extract_images(document):
    """PDF ドキュメントから画像形式のデータだけを抽出する"""

    # Create a PDF resource manager object that stores shared resources.
    rsrcmgr = PDFResourceManager()
    # Create a PDF device object.
    device = PDFPageAggregator(rsrcmgr, laparams=LAParams())
    # Create a PDF interpreter object.
    interpreter = PDFPageInterpreter(rsrcmgr, device)

    contents = []

    for page in PDFPage.create_pages(document):
        interpreter.process_page(page)
        layout = device.get_result()

        contents.extend(travarse(layout))

    return [to_pil_image(ltImage) for ltImage in contents]


def travarse(layout):
    """Layout オブジェクトを再帰的に走査して LTImage オブジェクトのみを list で返す"""

    images = []

    for obj in layout:
        if isinstance(obj, LTTextBox) or isinstance(obj, LTTextLine) or isinstance(obj, LTFigure):
            images.extend(travarse(obj))

        elif isinstance(obj, LTImage):
            images.append(obj)

    return images

ちなみに抽出されるデータの形式は生のバイナリ形式になります。


2. 画像データ(生バイナリ)を PIL の Image オブジェクトに変換

画像データを抽出できたのはいいものの、バイナリ形式のままでは何かと扱いにくいので Python で扱いやすい形式にします。 Python における代表的な画像処理ライブラリである Pillow の Image オブジェクト形式に変換しました。

import StringIO
from PIL import Image


def to_pil_image(ltImage):
    """Raw Binary を Image オブジェクトに変換"""

    buffer = StringIO.StringIO()
    buffer.write(ltImage.stream.get_rawdata())
    buffer.seek(0)
    return Image.open(buffer)

Image 形式に変換することで、元々埋め込まれていた画像データが JPG だったのか PNG だったのかを意識する必要がなくなります。
さらに、PIL の機能を使ってトリミングなどの補正処理もやりやすくなるので一石二鳥です。

標準的には Image オブジェクトはファイルストリームから生成することになっているので、 StringIO を使ってバイナリをもとにファイルストリームをエミュレートして Image オブジェクトを作ってあげます。


3. Tesseract で文字認識

いよいよ OCR に掛けて文字を読み取ってみましょう。

今回、OCR のツールとして Tesseract を利用しました。
Tesseract 自体は Python とは直接関係のない一般的な OCR ツールです。
160種類以上もの言語の学習済みデータが最初から付属している のが魅力で、中にはもちろん日本語用のものも含まれています。

事前に Tesseract をセットアップは済ませておく必要があります。
Tesseract を Python から呼び出すのには pyocr というラッパーモジュールを使いました。

試しに注文番号にあたる部分を読ませてみましょう。

f:id:todays_mitsui:20161119125229p:plain

はい、うまく読めているようですね。
先ほど述べたように、活字の数字のみであれば安定して文字認識できます。


続いて日付にあたる部分を読み取りましょう。

日付は「2016年11月15日」というような形式で書かれています。
最終的に正規表現で数字部分だけを抜き出すとはいえ、OCRに掛ける段階では「年」や「月」といった日本語の文字を読み取る必要があります。
Tesseract 標準の日本語用学習済みファイルを使って文字認識してみましょう。上手くいくでしょうか。

f:id:todays_mitsui:20161119125238p:plain

うーん、イマイチですね。
数字の 1] と読み違えています。全く似ていない (長音)に誤読してしまうのは学習に使った教師データに縦書きの文章が含まれているからでしょうか…。

とはいえ幸いなことにいま私が読み取ろうとしている文字は 0~9 と「年月日」の13文字だけです。それ以外の文字はありえない事が分かっているので、誤読の訂正も簡単なのです。
OCRに掛けた結果に ] が含まれれば 1 の誤読であろうと分かります。 Z が含まれれば恐らく 2 の誤読ですね。

というわけで、誤読されていそうな文字を一つひとつ補正する処理を挟みます。

REPLACE_PAIR = (
    (u']', u'1'), (u'}', u'1'), (u'ー', u'1'),
    (u'仔', u'年'), (u'El', u'日'), (u'E|', u'日'),
    (u'E', u'日'), (u'口', u'日'), (u'曰', u'日'),
    (u'Z', u'2'), (u'O', u'0'), (u'〇', u'0'),
    (u'I', u'1'), (u'l', u'1'),
)

# よくある誤読をヒューリスティックに訂正
for before, after in REPLACE_PAIR:
    txt = txt.replace(before, after)

# 空白を削除
txt = re.sub(r'\s+', '', txt)

このような工夫の結果、テスト用に用意したスキャンデータ20個では何とか正答率100%を達成できました。


4. PDF ファイルを複製しつつリネーム

注文番号と日付を読み取ることさえ出来れば、最後のリネームはとても簡単です。
shutil モジュールに含まれる copyfile 関数で複製しつつファイル名を変えてあげます。

import shutil


after = '{0[ordernum]}_{1[year]:0>4}{1[month]:0>2}{1[day]:0>2}.pdf'.format(ordernum, date)
shutil.copyfile(before, 'AFTER/'+after)

このような処理をフォルダに用意したスキャンデータの一つひとつに適用していけば、ファイル整理の自動化は達成されます。

f:id:todays_mitsui:20161119130455p:plain

いい感じです。


配布する

これにて目的は達成出来ましたが、最後にこのプログラムを自分以外の人にも配布することについて検討します。
実務で必要な人が手軽に使えるようにとかんがえると、やはり Windows の実行形式である EXE ファイルを作って配布するべきでしょうね。

Python で書いたプログラムにおいては、Pinstaller というツールを使う事で必要なライブラリなどを自動で取り込んだ EXE ファイルを吐き出してくれます。

$ pyinstaller main.py --onefile --clean

実行するコンピュータに Tesseract が別途セットアップされている必要はあるものの、EXE ファイルを配りさえすれば使ってもらえるのはとても便利です。
Pyinstler のインストールは pip コマンドひとつで済むので使い始めるのも楽でした。


まとめ

というわけで、PDFファイルから画像を抽出し、文字を読み取ってスキャンデータをリネームするところまで一通りの処理を自動化してみました。
Python は様々な分野で必要になる基本的ツールが何かしら揃っていて成熟しているので便利ですね。これはいいものです。

今回書いたコードの一式は GitHub に置いています。

github.com


私からは以上です。

一手間加えた INSERT - レコードが未登録のとき、登録済みのとき、

どうも、株式会社あつまるで元気よく SQL を書いている三井です。


DB にレコードを INSERT するとき、一手間加えて 未登録の場合に限って登録登録済みなら一部フィールドだけ上書き などしたくなりますよね。
ここ最近、そのような SQL を書くことが多かったのでメモしておきます。

ちなみに MySQL の独自構文などもバリバリ使っているので、他のベンダーの DB に適用するときは部分的な書き換えが必要かもしれません。


レコードが存在していなかったら新規登録、存在していれば上書き

INSERT INTO `posts`(
    `id`
    ,`title`
    ,`body`
    ,`created_at`
    ,`updated_at`
)
VALUES (
    42
    ,'test title'
    ,'test body'
    ,'1970-01-01 00:00:00'
    ,'1970-01-01 00:00:00'
)
ON DUPLICATE KEY UPDATE
    `title`       = VALUES(`title`)
    ,`body`       = VALUES(`body`)
    ,`updated_at` = VALUES(`updated_at`)

UNIQUE インデックスまたは PRIMARY KEY を重複させるようなレコードを INSERT しようとしたときに、INSERT ではなく UPDATE が実行される。
その場合でも created_at は上書きされない。

参考


レコードが存在していなかったら新規登録、存在していれば何もしない

INSERT INTO `tags` (
    `id`
    ,`name`
)
SELECT
    42
    ,'Technology'
FROM dual
WHERE NOT EXISTS (
    SELECT `id` FROM `tags`
    WHERE `name` = 'Technology'
)

タグ一覧テーブルからタグ名が 'Technorogy' であるものを探して、存在しなかった場合に限り INSERT する。
8行目でテーブル名として使われている dual は実際には参照されることのないダミーのテーブル名。

参考