Data Science Tutorial 02 @ Data Science Society

In [2]:

    

# TODO: You Must Change the setting bellow
MYSQL = {
    'user': 'root',
    'passwd': '',
    'db': 'coupon_purchase',
    'host': '127.0.0.1',
    'port': 3306,
    'local_infile': True,
    'charset': 'utf8',
}
DATA_DIR = '/home/nasuno/recruit_kaggle_datasets'  #  ディレクトリの名前に日本語(マルチバイト文字)は使わないでください。
OUTPUTS_DIR = '/home/nasuno/recruit_kaggle/outputs'  # 予測結果などを保存するディレクトリ。


    

In [3]:

    

%matplotlib inline
import matplotlib.pyplot as plt
import MySQLdb
import numpy

from sklearn.utils import shuffle
from sklearn.cross_validation import train_test_split
from sklearn.metrics import f1_score, accuracy_score
from sklearn.linear_model import LogisticRegression
from datetime import datetime, timedelta
from itertools import product

# Random Seed
rng = numpy.random.RandomState(1234)

dbcon = MySQLdb.connect(**MYSQL)
dbcur = dbcon.cursor()


    

In [4]:

    

validation_start = datetime.strptime('2012-06-17 00:00:00', '%Y-%m-%d %H:%M:%S')
validation_end = validation_start + timedelta(days=7)
dbcur.execute(''' DROP TABLE IF EXISTS coupon_visit_train_training;''')  # チュートリアルの便宜上一回削除します。
dbcur.execute(''' CREATE TABLE IF NOT EXISTS coupon_visit_train_training LIKE coupon_visit_train;''')
dbcur.execute('''
    INSERT INTO coupon_visit_train_training
        SELECT * 
        FROM coupon_visit_train
        WHERE i_date >= "2011-07-01 00:00:00" AND i_date < %s
    ;
''', (validation_start, ))

dbcur.execute(''' DROP TABLE IF EXISTS coupon_visit_train_validation;''')  # チュートリアルの便宜上一回削除します。
dbcur.execute(''' CREATE TABLE IF NOT EXISTS coupon_visit_train_validation LIKE coupon_visit_train;''')
dbcur.execute('''
    INSERT INTO coupon_visit_train_validation
        SELECT * 
        FROM coupon_visit_train
        WHERE i_date >= %s
    ;
''', (validation_start, ))


    

    
Out[4]:





65468L

In [5]:

    

# validation 期間に購買されうるクーポンの抽出
dbcur.execute('''
    SELECT
        coupon_id_hash
        FROM coupon_list_train
        WHERE
            NOT (dispend <= %s OR dispfrom > %s)
    ;
''', (validation_start, validation_end))
coupon_ids = []
for row in dbcur.fetchall():
    coupon_ids.append(row[0])


    

In [6]:

    

# user_idsをselectして、ランダムに、購買アイテムを割り当てる。
dbcur.execute('''
    SELECT
        user_id_hash
        FROM user_list
    ;
''')
user_pcoupon_pred = {}
for row in dbcur.fetchall():
    user_pcoupon_pred[row[0]] =list(shuffle(coupon_ids, random_state=rng)[:10])


    

In [7]:

    

#  validation期間に購買したクーポンリストを抽出。
dbcur.execute('''
    SELECT
        user_id_hash, view_coupon_id_hash
        FROM coupon_visit_train_validation
        WHERE purchase_flg = 1
    ;
''')
user_pcoupon_true = {}
for row in dbcur.fetchall():
    if row[0] not in user_pcoupon_true:
        user_pcoupon_true[row[0]] = []
    user_pcoupon_true[row[0]].append(row[1])


#  ap10を算出する関数を定義。
def get_ap10(y_pred, y_true):
    ap10 = 0.
    y_true = set(y_true)
    for i in range(len(y_pred)):
        if y_pred[i] in y_true:
            c = set(y_pred[:i + 1])
            ap10 += len(y_true & c) / float(i + 1)
    ap10 /= min(len(y_true), 10)
    return ap10

map10 = 0.
n_purchased_user = 0.
for user_id in user_pcoupon_pred:
    if user_id not in user_pcoupon_true:
        # 当該ユーザがvalidation期間にcouponを買わなかった場合、
        # ap@10は0 
        continue
    n_purchased_user += 1
    y_true = user_pcoupon_true[user_id]
    y_pred = user_pcoupon_pred[user_id]
    
    map10 += get_ap10(y_pred, y_true)

max_map10 = n_purchased_user / len(user_pcoupon_pred)
map10 /= len(user_pcoupon_pred)

print 'max_map@10: %.5f, map@10: %.5f' % (max_map10, map10)


    

    




max_map@10: 0.08438, map@10: 0.00038

In [8]:

    

output = ['USER_ID_hash,PURCHASED_COUPONS']
for user_id in user_pcoupon_pred:
    output.append(user_id + ',' + ' '.join(user_pcoupon_pred[user_id]))

output = '\n'.join(output)
with open(OUTPUTS_DIR + '/random_prediction_valid.csv', 'wb') as fid:
    fid.write(output)


    

In [39]:

    

# ユニークな都道府県リストの取得
dbcur.execute(''' SELECT pref_name FROM prefecture_locations ORDER BY pref_name ; ''')
pref_data = []
for row in dbcur.fetchall():
    pref_data.append(row[0])

# ユーザの素性を作成。(ユーザの素性はtraining、validation, testで共通)
dbcur.execute('''
    SELECT
        t1.user_id_hash,
        IF(t1.sex_id = 'm', 1, 0),
        (t1.age-15)/65,
        ''' + ', '.join([u'IF(t1.pref_name = "' + p[0] + u'", 1, 0)' for i, p in enumerate(pref_data)]) + '''
    FROM user_list AS t1
''')

user_feature = {}  # ユーザの素性ベクトル
for row in dbcur.fetchall():
    user_feature[row[0]] = row[1:]


    

In [40]:

    

training_start = validation_start - timedelta(days=7)  # 訓練開始日時を算出。

# カテゴリリストの取得
dbcur.execute(''' SELECT DISTINCT(capsule_text) FROM coupon_list_train ORDER BY capsule_text;''')
capsule_data = []
for row in dbcur.fetchall():
    capsule_data.append(row[0])

# ジャンルリストの取得
dbcur.execute(''' SELECT DISTINCT(genre_name) FROM coupon_list_train ORDER BY genre_name;''')
genre_data = []
for row in dbcur.fetchall():
    genre_data.append(row[0])

# 大エリアリストの取得
dbcur.execute(''' SELECT DISTINCT(large_area_name) FROM coupon_list_train ORDER BY large_area_name;''')
larea_data = []
for row in dbcur.fetchall():
    larea_data.append(row[0])

# 都道府県リストの取得
dbcur.execute(''' SELECT DISTINCT(ken_name) FROM coupon_list_train ORDER BY ken_name;''')
pref_data = []
for row in dbcur.fetchall():
    pref_data.append(row[0])

# 小エリアリストの取得
dbcur.execute(''' SELECT DISTINCT(small_area_name) FROM coupon_list_train ORDER BY small_area_name;''')
sarea_data = []
for row in dbcur.fetchall():
    sarea_data.append(row[0])

def get_item_feature(f_date, t_date):
    #  クーポンの素性を作成する関数。
    # @f_date：対象期間の開始日時
    # @t_date：対象期間の終了日時
    # テーブルが訓練用のテーブルとなっている為、training とvalidationのデータを作成する際にしか利用できない。
    dbcur.execute('''
        SELECT
            coupon_id_hash,
            ''' + ', '.join([u'IF(capsule_text = "' + p[0] + u'", 1, 0)' for i, p in enumerate(capsule_data)]) + ''',
            ''' + ', '.join([u'IF(genre_name = "' + p[0] + u'", 1, 0)' for i, p in enumerate(genre_data)]) + ''',
            COALESCE(CAST(usable_date_mon AS SIGNED), 0),
            COALESCE(CAST(usable_date_tue AS SIGNED), 0),
            COALESCE(CAST(usable_date_wed AS SIGNED), 0),
            COALESCE(CAST(usable_date_thu AS SIGNED), 0),
            COALESCE(CAST(usable_date_fri AS SIGNED), 0),
            COALESCE(CAST(usable_date_sat AS SIGNED), 0),
            COALESCE(CAST(usable_date_sun AS SIGNED), 0),
            COALESCE(CAST(usable_date_holiday AS SIGNED), 0),
            COALESCE(CAST(usable_date_before_holiday AS SIGNED), 0),
            ''' + ', '.join([u'IF(large_area_name = "' + p[0] + u'", 1, 0)' for i, p in enumerate(larea_data)]) + ''',
            ''' + ', '.join([u'IF(ken_name = "' + p[0] + u'", 1, 0)' for i, p in enumerate(pref_data)]) + ''',
            ''' + ', '.join([u'IF(small_area_name = "' + p[0] + u'", 1, 0)' for i, p in enumerate(sarea_data)]) + '''

        FROM coupon_list_train
        WHERE
            NOT (dispend <= %s OR dispfrom > %s)
        ;
    ''', (f_date, t_date))
    item_feature = {} # クーポンの素性
    for row in dbcur.fetchall():
        item_feature[row[0]] = row[1:]
        
    return item_feature

item_feature_train = get_item_feature(training_start, validation_start)  # training 期間のクーポンの素性
item_feature_valid = get_item_feature(validation_start, validation_end) # validation 期間のクーポンの素性
print 'n_item_train: %d, n_item_valid: %d' % (len(item_feature_train), len(item_feature_valid))


    

    




n_item_train: 576, n_item_valid: 665






    




/usr/local/lib/python2.7/dist-packages/IPython/kernel/__main__.py:60: Warning: Truncated incorrect INTEGER value: 'NA'

In [41]:

    

def get_purchased_coupons(f_date, t_date):
    # 実際に購買されるクーポンの取得
    # @f_date：対象期間の開始日時
    # @t_date：対象期間の終了日時
    dbcur.execute('''
        SELECT user_id_hash, view_coupon_id_hash 
        FROM coupon_visit_train
        WHERE i_date >= %s AND i_date < %s AND purchase_flg = 1
        ORDER BY user_id_hash, view_coupon_id_hash
        ;
    ''', (f_date, t_date))

    purchased_items = {}  # 各ユーザがどのクーポン群を購入するかを辞書型で返す。
    for row in dbcur.fetchall():
        if row[0] not in purchased_items:
            purchased_items[row[0]] = set([])
        purchased_items[row[0]].add(row[1])

    return purchased_items

user_pcoupon_train = get_purchased_coupons(training_start, validation_start)  # training 期間に各ユーザが実際に買ったクーポン
user_pcoupon_valid = get_purchased_coupons(validation_start, validation_end)  # validation 期間に各ユーザが実際に買ったクーポン


    

In [42]:

    

n_pairs_train = len(user_feature) * len(item_feature_train)  # ユーザ数×trainingクーポン数
n_pairs_valid = len(user_feature) * len(item_feature_valid)  # ユーザ数×validation クーポン数
print 'n_train_datasets: %d, n_validation_datasets: %d, n_puser: %d' %(n_pairs_train, n_pairs_valid, len([1 for a in user_pcoupon_train if len(a) > 0]))


    

    




n_train_datasets: 13174848, n_validation_datasets: 15210545, n_puser: 1419

In [43]:

    

# 訓練データに利用するユーザをtraining期間に、実際にクーポンを購入したユーザに限定し、そのユーザIDとクーポンのIDの全組み合せを出力する。
pairs_train = list(product([k for k in user_pcoupon_train if len(user_pcoupon_train[k]) > 0], item_feature_train.keys()))
print 'n_train_datasets: %d' %(len(pairs_train), )

features_train = []  # 学習に用いる素性
labels_train = []  # 学習に用いるラベル
for pair in pairs_train:  # 各ユーザ、アイテムペアについて
    user_id, item_id = pair
    features_train.append(user_feature[user_id] + item_feature_train[item_id])  # 単純な結合
    if user_id in user_pcoupon_train and item_id in user_pcoupon_train[user_id]:
        #  購買された
        labels_train.append(1)
    else:
        #  購買されなかった
        labels_train.append(0)


    

    




n_train_datasets: 817344

In [44]:

    

model = LogisticRegression()  # ロジスティック回帰のモデル構築(ハイパーパラメタの調整は省略)。インスタンス化。
model.fit(features_train, labels_train)  #  x, yを入力して学習


    

    
Out[44]:





LogisticRegression(C=1.0, class_weight=None, dual=False, fit_intercept=True,
          intercept_scaling=1, max_iter=100, multi_class='ovr',
          penalty='l2', random_state=None, solver='liblinear', tol=0.0001,
          verbose=0)

In [45]:

    

purchase_index = numpy.argmax(model.classes_)  # 1(=購買ラベル)がついている方のカラムインデックスを取得
item_index_to_item_id = sorted(item_feature_valid.keys())  #  クーポンの番号をクーポンIDに変換する。
map10 = 0.
for user_id in user_feature:  # map@10はユーザごとにap@10を算出する。
    if user_id not in user_pcoupon_valid:  # 購入したクーポンが亡ければ、ap@10は0なので、スコア評価時には飛ばす。
        continue
    feature = []
    for item_id in item_index_to_item_id:
        feature.append(user_feature[user_id] + item_feature_valid[item_id])  # 単純にユーザ素性とクーポン素性を結合
    y_proba = model.predict_proba(feature)  # 各クーポンの購買確率を算出
    y_pred_indices = numpy.argsort(y_proba[:, purchase_index])[-10:][::-1]  # 購入確率が高いクーポン上位10個のクーポン番号を取得
    y_pred_item_ids = [item_index_to_item_id[i] for i in y_pred_indices]  # クーポン番号をクーポンIDに変換。
    map10 += get_ap10(y_pred_item_ids, user_pcoupon_valid[user_id])  # ap@10を計算して、map@10に足す。
    

map10 /= len(user_feature)  # map@10はユーザ平均なので、全ユーザで割る。
print 'MAP@10: %.5f' % (map10, )


    

    




MAP@10: 0.00021

In [56]:

    

n = 10
#print ['sex', 'age'] + pref_data
label_names = (
    ['user_' + c for c in (['sex', 'age'] + pref_data)]
    + ['item_' + c for c in (
       capsule_data
       + genre_data 
       + ['mon', 'tue', 'wed', 'thu', 'fri', 'sat', 'sun', 'holiday', 'before_holiday'] 
       + larea_data 
       + pref_data 
       + sarea_data
        
      )]
)
print "合計の素性数：%d" % (model.coef_.shape[1])
print "降順に10個"
for i in numpy.argsort(abs(model.coef_[0]))[-n:][::-1]:
    print 'index: %d, name: %s, %.4f' % (i, label_names[i], model.coef_[0][i])


print "昇順に10個"
for i in numpy.argsort(abs(model.coef_[0]))[:n]:
    print 'index: %d, name: %s, %.4f' % (i, label_names[i], model.coef_[0][i])


    

    




合計の素性数：207
降順に10個
index: 94, name: item_holiday, -0.5783
index: 91, name: item_fri, -0.3374
index: 92, name: item_sat, -0.2281
index: 88, name: item_tue, -0.2216
index: 89, name: item_wed, 0.1964
index: 93, name: item_sun, 0.1443
index: 1, name: user_age, 0.1401
index: 0, name: user_sex, -0.0780
index: 87, name: item_mon, -0.0696
index: 95, name: item_before_holiday, 0.0595
昇順に10個
index: 103, name: item_関東, 0.0000
index: 131, name: item_東京都, 0.0000
index: 132, name: item_栃木県, 0.0000
index: 133, name: item_沖縄県, 0.0000
index: 134, name: item_滋賀県, 0.0000
index: 135, name: item_熊本県, 0.0000
index: 136, name: item_石川県, 0.0000
index: 137, name: item_神奈川県, 0.0000
index: 138, name: item_福井県, 0.0000
index: 139, name: item_福岡県, 0.0000

In [87]:

    

N = 50
x = numpy.linspace(0, 10, N)
y1_train = x + rng.rand(N)*5 + 5
y2_train = x + rng.rand(N)*5

y_valid = x + rng.rand(N) * 5 + 1.5
plt.figure()
plt.plot(x, y1_train, 'o')
plt.plot(x, y1_train)
plt.plot(x, x + 7.5)


    

    
Out[87]:





[<matplotlib.lines.Line2D at 0x7f6e9348d790>]






    

In [17]:

    

dbcur.execute('''
    SELECT
        COUNT(*),
        SUM(purchase_flg),
        COUNT(DISTINCT(view_coupon_id_hash))
    FROM
        coupon_visit_train
    GROUP BY user_id_hash
    ;
''')
n_view = []
n_purchase = []
n_view_u = []

for row in dbcur.fetchall():
    n_view.append(int(row[0]))
    n_purchase.append(int(row[1]))
    n_view_u.append(int(row[2]))

n_view = numpy.asarray(n_view)
n_purchase = numpy.asarray(n_purchase)
n_view_u = numpy.asarray(n_view_u)

### user-coldstartがどういった状況か見る為に、最初の20件だけ見る。
span = 20
fig = plt.figure(figsize=(18, 8))
ax = fig.add_subplot(2, 3, 1)
ax.hist(n_view, bins=numpy.arange(0, span), cumulative=True)
ax.set_title('page view count distribution')

ax = fig.add_subplot(2, 3, 2)
ax.hist(n_purchase, bins=numpy.arange(0, span), cumulative=True)
ax.set_title('purchase count distribution')

ax = fig.add_subplot(2, 3, 3)
ax.hist(n_view_u, bins=numpy.arange(0, span), cumulative=True)
ax.set_title('unique page view count distribution')

ax = fig.add_subplot(2, 3, 4)
ax.plot(n_view, n_purchase, 'x')
ax.set_title('X=page view count, Y=purchase count')

ax = fig.add_subplot(2, 3, 5)
ax.plot(n_view_u, n_purchase, 'x')
ax.set_title('X=unique page view count, Y=purchase count')

ax = fig.add_subplot(2, 3, 6)
ax.plot(n_view, n_view_u, 'x')
ax.set_title('X=page view count, Y=unique page view count')

plt.show()


    

In [18]:

    

## 3Dにしても良く分からないことが多いので，辞めましょう。
from mpl_toolkits.mplot3d import Axes3D
fig = plt.figure(figsize=(5, 5))
ax = fig.add_subplot(111, projection='3d')
ax.scatter(n_view, n_view_u, n_purchase, marker='x')
ax.set_xlabel('X Label')
ax.set_ylabel('Y Label')
ax.set_zlabel('Z Label')

plt.show()


    

In [ ]:

    

dbcur.execute('''
    SELECT
        t1.coupon_id_hash, COUNT(t2.view_coupon_id_hash), COALESCE(SUM(t2.purchase_flg), 0)
    FROM coupon_list_test AS t1
    LEFT JOIN coupon_visit_train AS t2 ON t1.coupon_id_hash = t2.view_coupon_id_hash
    GROUP BY t1.coupon_id_hash
    ORDER BY SUM(t2.purchase_flg)
    ;
''')
view_count = []
purchase_count = []
for row in dbcur.fetchall():
    view_count.append(int(row[1]))
    purchase_count.append(int(row[2]))

view_count = numpy.asarray(view_count)
purchase_count = numpy.asarray(purchase_count)
plt.figure()
plt.plot(purchase_count, view_count, '.')
plt.show()


    

In [ ]:

    

dbcur.execute('''
    SELECT
        AVG(same_pref_purchase_cnt),
        AVG(same_pref_view_cnt),
        AVG(same_pref_purchase_cnt / same_pref_view_cnt),
        AVG(diff_pref_purchase_cnt),
        AVG(diff_pref_view_cnt),
        AVG(diff_pref_purchase_cnt / diff_pref_view_cnt)
    FROM (
        SELECT
            t1.user_id_hash,
            SUM(t1.pref_name = t3.ken_name AND purchase_flg = 1) AS same_pref_purchase_cnt,
            SUM(t1.pref_name = t3.ken_name) AS same_pref_view_cnt,
            SUM(t1.pref_name != t3.ken_name AND purchase_flg = 1) AS diff_pref_purchase_cnt,
            SUM(t1.pref_name != t3.ken_name) AS diff_pref_view_cnt
        FROM user_list AS t1
        LEFT JOIN coupon_visit_train AS t2 ON t1.user_id_hash = t2.user_id_hash
        LEFT JOIN coupon_list_train AS t3 ON t2.view_coupon_id_hash = t3.coupon_id_hash
        WHERE t1.pref_name != ""
        GROUP BY t1.user_id_hash
    ) AS t1

    ;
''')
data = None
for row in dbcur.fetchall():
    data = row
print 'same_purchase: %.2f, same_view: %.2f, same_rate: %.2f, diff_purchase: %.2f, diff_view: %.2f, diff_rate: %.2f' % (data)


    

In [ ]:

    

dbcur.execute('''
    SELECT
        t1.sex_id,
        AVG(t1.discount_rate_view),
        AVG(t1.discount_rate_purchase)
    FROM (
        SELECT
            t1.user_id_hash,
            t1.sex_id,
            AVG(100 - t3.price_rate) AS discount_rate_view,
            COALESCE(SUM(IF(t2.purchase_flg, 100 - t3.price_rate, 0)) / SUM(t2.purchase_flg), 0) AS discount_rate_purchase
            
        FROM user_list AS t1
        LEFT JOIN coupon_visit_train AS t2 ON t1.user_id_hash = t2.user_id_hash
        LEFT JOIN coupon_list_train AS t3 ON t2.view_coupon_id_hash = t3.coupon_id_hash
        GROUP BY t1.user_id_hash
    ) AS t1
    GROUP BY t1.sex_id
    ;
''')
data = []
for row in dbcur.fetchall():
    row = list(row)
    row[1] = float(row[1])
    row[2] = float(row[2])
    data.append(tuple(row))
for row in data:
    print 'sex_id: %s, discount_rate_view: %.2f, discount_rate_purchase: %.2f' % (row)


    

In [20]:

    

dbcur.execute('''
    SELECT
        SUM(purchase_flg)
    FROM coupon_visit_train_validation
    WHERE purchase_flg = 1
    GROUP BY user_id_hash
    ;
''')
x = []
for row in dbcur.fetchall():
    x.append(int(row[0]))
plt.figure()
plt.hist(x, bins=numpy.arange(1, 15))
plt.show()


    

In [21]:

    

dbcur.execute('''
    SELECT
        AVG(t1.same_purchase),
        AVG(t1.same_view),
        AVG(t1.same_purchase / t1.same_view) AS same_rate,
        AVG(t1.diff_purchase),
        AVG(t1.diff_view),
        AVG(t1.diff_purchase / t1.diff_view) AS diff_rate
    FROM (
        SELECT
            t1.user_id_hash,
            SUM(t1.genre_name = t3.genre_name AND t2.purchase_flg = 1) AS same_purchase,
            SUM(t1.genre_name = t3.genre_name) AS same_view,
            SUM(t1.genre_name != t3.genre_name AND t2.purchase_flg = 1) AS diff_purchase,
            SUM(t1.genre_name != t3.genre_name) AS diff_view
        FROM (
            SELECT
                t1.user_id_hash, t1.view_coupon_id_hash, t3.genre_name
            FROM coupon_visit_train_training AS t1
            LEFT JOIN coupon_visit_train_training AS t2 ON t1.user_id_hash = t2.user_id_hash AND t1.i_date < t2.i_date
            LEFT JOIN coupon_list_train AS t3 ON t1.view_coupon_id_hash = t3.coupon_id_hash
            WHERE t1.purchase_flg = 1 AND t2.user_id_hash IS NULL
            GROUP BY t1.user_id_hash
        ) AS t1
        LEFT JOIN coupon_visit_train_validation AS t2 ON t1.user_id_hash = t2.user_id_hash
        LEFT JOIN coupon_list_train AS t3 ON t2.view_coupon_id_hash = t3.coupon_id_hash
        LEFT JOIN (
            SELECT user_id_hash
            FROM coupon_visit_train_validation
            WHERE purchase_flg = 1
            GROUP BY user_id_hash
        ) AS t4 ON t1.user_id_hash = t4.user_id_hash
        WHERE t4.user_id_hash IS NOT NULL
        GROUP BY t1.user_id_hash
    ) AS t1
    ;
''')

data = None
for row in dbcur.fetchall():
    data = row
print 'same_purchase: %.2f, same_view: %.2f, same_rate: %.2f, diff_purchase: %.2f, diff_view: %.2f, diff_rate: %.2f' % (data)


    

    




same_purchase: 0.64, same_view: 4.24, same_rate: 0.22, diff_purchase: 0.98, diff_view: 7.09, diff_rate: 0.23

In [ ]: