歩いたら休め

なんでこんな模様をしているのですか？

【Python】PV数、UU数を出すSQLのクエリを作る関数を作った

pandas Python

最近、Google BigQueryにクエリを投げる毎日です。

社内のデータをBigQueryで一元管理しようとしているため、過去に使われていたクエリの絞り込み条件を移植し、それぞれの絞り込み条件でPV数とUU数をひたすらチェックするという面倒くさい作業をしています。

つまり、次のようなクエリをひたすら書いています。

SELECT
  SUM(Tokyo) AS Tokyo_pv,
  SUM(IF(Tokyo_count != 0, 1, 0)) AS Tokyo_uu, -- ユーザーの数を調べればUU数
  SUM(male) AS male_pv, -- ユーザーごとのPV数を合計すれば全体のPV数
  SUM(IF(male_count != 0, 1, 0)) AS male_uu,
  SUM(female) AS female_pv,
  SUM(IF(female_count != 0, 1, 0)) AS female_uu,
  SUM(Tokyo_male) AS Tokyo_male_pv,
  SUM(IF(Tokyo_male_count != 0, 1, 0)) AS Tokyo_male_uu,
FROM
  -- 一旦ユーザーごとにPV数を集計する
  (SELECT
    SUM(IF(pref_id = 13, 1, 0)) AS Tokyo_count,
    SUM(IF(sex = m, 1, 0)) AS male_count,
    SUM(IF(sex = f, 1, 0)) AS female_count,
    SUM(IF(pref_id = 13 AND sex = m, 1, 0)) AS Tokyo_male_count,
  FROM
    [project-***:example_table.20151215]
  GROUP BY
    user_id, date)
GROUP BY
  date

これを、次のようなcsvから作れれば便利ですよね？Excelでカラム名と絞り込み条件を指定すれば、名前や条件を変えたくなったらすぐに反映できて…。

f:id:takeshi0406:20151216000307p:plain

というわけで、Pythonで作ってみました。

import pandas as pd # csvの読み込みにpandasパッケージを使う

def make_sum(conf):
    base = "SUM({colname}) AS {colname}_pv,\n" + \
           "SUM(IF({colname}_count != 0, 1, 0)) AS {colname}_uu,"
    return base.format(**conf)

def make_sums(confs):
    return '\n'.join([make_sum(c) for c in confs])

def make_count(conf):
    base = "SUM(IF({conf}, 1, 0)) AS {colname}_count,"
    return base.format(**conf)

def make_counts(confs):
    return '\n'.join([make_count(c) for c in confs])

# インデントをつける関数
def indent(txt, num=1):
    return '\n'.join(['  '*num + t for t in txt.split('\n')])

def make_query(confs, date):
    base = "SELECT\n" + \
           "{sums}\n" + \
           "FROM\n" + \
           "  (SELECT\n" + \
           "{counts}\n" + \
           "  FROM\n" + \
           "    [project-***:example_table.{date}]\n" + \
           "  GROUP BY\n" + \
           "    user_id, date)\n" + \
           "GROUP BY\n" + \
           "  date"
    return base.format(sums=indent(make_sums(confs)), counts=indent(make_counts(confs), 2), date=date)

# データの読み込み(colmn.csvという名前で用意しています)
csv_data = pd.read_csv('colmn.csv')
confs = csv_data.transpose().to_dict().values()

# printしてコピペするなり、直接BigQueryにクエリを投げるなり、なんなりと
print(make_counts(confs, '20151215'))

軽く説明します。

Pythonの文字列フォーマット

こういう感じに文字列を置き換えられます。

"My name is {name}.".format(name = 'John')
# => My name is John.

format関数による文字列フォーマット（新しい形式） » Python Snippets

function(**dict)で辞書（連想配列）を展開して引数に与える

たくさん引数のある関数に、辞書（連想配列）を展開して与える機能です。

hiro-itasuto7.hatenadiary.jp

例えば

base_query = "SELECT {colmn} FROM {table}"

に対して、

base_query.format(colmn = 'col1', table = 'tab1')

という形で与える代わりに、

conf = {'colmn': 'col1', 'table': 'tab1'}
base_query.format(**conf)

という辞書（連想配列）の形で引数を与えることができます。

データの読み込みについて

つまり、こういう形式の辞書があれば、formatに一気に引数として与えることができるため、

{'colname': 'Tokyo', 'conf': 'pref_id = 13'}

csvを読み込んだ後に、上の形式の辞書が入ったリストに変換すると便利です。

[{'colname': 'Tokyo', 'conf': 'pref_id = 13'},
 {'colname': 'male', 'conf': 'sex = m'},
 {'colname': 'female', 'conf': 'sex = f'},
 {'colname': 'Tokyo_male', 'conf': 'pref_id = 13 AND sex = m'}]

そのためにpandasでデータフレームとして読み込んだ後、

csv_data = pd.read_csv('colmn.csv')
#      colname     conf
# 0    Tokyo   pref_id = 13
# 1    male    sex = m
# 2    female  sex = f
# 3    Tokyo_male  pref_id = 13 AND sex = m

転置を取って、

csv_data.transpose()
#      0   1   2   3
# colname  Tokyo   male    female  Tokyo_male
# conf     pref_id = 13    sex = m     sex = f     pref_id = 13 AND sex = m

辞書に変換して、

csv_data.transpose().to_dict()
# {0: {'colname': 'Tokyo', 'conf': 'pref_id = 13'},
#  1: {'colname': 'male', 'conf': 'sex = m'},
#  2: {'colname': 'female', 'conf': 'sex = f'},
#  3: {'colname': 'Tokyo_male', 'conf': 'pref_id = 13 AND sex = m'}}

（0, 1, 2, 3というカラム名が余計なので）辞書のvalueを取ってあげます。

csv_data.transpose().to_dict().values()
# dict_values([{'colname': 'Tokyo', 'conf': 'pref_id = 13'}, {'colname': 'male', 'conf': 'sex = m'}, {'colname': 'female', 'conf': 'sex = f'}, {'colname': 'Tokyo_male', 'conf': 'pref_id = 13 AND sex = m'}])

余談

pandasに、bigqueryを呼び出して戻り値をdataframeに格納する機能があるらしいのですが、Python2系しか対応していないようです （というわけでprintした結果をブラウザにコピペしています）。 R言語からbigqueryにクエリを投げるbigrqueryは問題なく動作しているので、Rで実装しちゃったほうが楽だったかも。

pandas.io.gbq.read_gbq — pandas 0.17.1 documentation

Google BigQuery

Google BigQuery