「ビックデータ分析・活用のためのSQLレシピ」を読む

3-2-6 IPアドレスを扱う

import pandas as pd
import psycopg2
import numpy as np
import ipaddress

ip1 = ipaddress.ip_address('127.0.0.1')
ip2 = ipaddress.ip_address('127.0.0.2')
ip3 = ipaddress.ip_address('192.168.0.1')
df = pd.DataFrame({'lt':[ip1 < ip2],'gt':[ip1 > ip3]})
print(df)

ip = ipaddress.ip_address('127.0.0.1')
nw = ipaddress.ip_network('127.0.0.0/8')
df = pd.DataFrame({'is_contained':[ip in nw]})
print(df)

3-3 1つのテーブルに対する操作

3-3-1 グループの特徴を捉える

import pandas as pd
import psycopg2
import numpy as np

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM review", conn)
print(df)

df1 = pd.DataFrame({'user_id':df.groupby('user_id')['user_id'].count(),
                'product_id':df.groupby('user_id')['product_id'].count(),
                'sum':df.groupby('user_id')['score'].sum(),
                'mean':df.groupby('user_id')['score'].mean(),
                'max':df.groupby('user_id')['score'].max(),
                'min':df.groupby('user_id')['score'].min()
               })
print(df1)

#別解
df2 = df.groupby('user_id').agg({'score':['count','sum','mean','max','min'],
                             'product_id':['count']})
print(df2)

「ビックデータ分析・活用のためのSQLレシピ」を読む

3-2 複数の値に対する操作

3-2-1 文字列を連結する

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM mst_user_location", conn)
print(df)

df['pref_city'] = df['pref_name'] + df['city_name']
print(df[['user_id','pref_city']])

#別解
df = pd.read_sql("SELECT * FROM mst_user_location", conn)
#間にスペースを挟む
df['pref_city'] = df['pref_name'].str.cat(df['city_name'],sep = ' ')
print(df[['user_id','pref_city']])

3-2-2 複数の値を比較する

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM quarterly_sales", conn)
print(df)

import numpy as np

df.loc[df['q1'] < df['q2'], 'judge_q1_q2'] = '+'
df.loc[df['q1'] > df['q2'], 'judge_q1_q2'] = '-'
df.loc[df['q1'] == df['q2'], 'judge_q1_q2'] = ' '
df['diff_q2_q1'] = df['q2'] - df['q1']
df['sign_q2_q1'] = np.sign(df['q2'] - df['q1'])

df['greatest_sales'] = df[['q1','q2','q3','q4']].max(axis=1)
df['least_sales'] =    df[['q1','q2','q3','q4']].min(axis=1)
df[['year','greatest_sales','least_sales']]

df['average'] = df[['q1','q2','q3','q4']].mean(axis=1)
print(df)

3-2-3 2つの値の比率を計算する

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM advertising_stats", conn)
print(df)

df1 = df[df.dt=='2017-04-01']
df1['ctf'] = df1['clicks']/df1['impressions']
print(df1[['dt','ad_id','ctf']])

df['ctf'] = df['clicks']/df['impressions']
print(df[['dt','ad_id','ctf']])

3-2-4 2つの値の距離を計算する

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM location_1d", conn)
print(df)

df['abs'] = np.abs(df.x1 - df.x2)
df['rms'] = np.sqrt(np.power(df['x1'] - df['x2'],2)) #df['x1']とdf.x1は等価
print(df)

xy平面で2点間のユーグリッド距離を計算する

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM location_2d", conn)
print(df)

df['dist'] = np.sqrt(np.power(df.x1 - df.x2,2) + np.power(df.y1 - df.y2,2))
print(df[['dist']])

3-2-5 日付・時刻を計算する

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM mst_users_with_birthday", conn)
print(df)

df['register_stamp'] = pd.to_datetime(df['register_stamp'])
df['birth_date'] = pd.to_datetime(df['birth_date'])
from pandas.tseries.offsets import Hour,Minute,Day
#before 1 month NG
#月の差分計算はうまくいかなかった
df['after_1_hour'] = df['register_stamp'] + Hour(1)
df['before_30_minutes'] = df['register_stamp'] - Minute(30)
df['after_1_day'] = df['register_stamp'] + Day(1)
print(df)

df[['user_id','register_stamp','after_1_hour','before_30_minutes','after_1_day']]

from datetime import datetime
df['today'] = datetime.today()
df['diff_days'] = df['today'] - df['register_stamp']
df[['user_id','today','register_stamp','diff_days']]

#年齢計算はうまくいかなかった
df['age'] = (df['today'] -  df['birth_date'])/365
df[['user_id','today','birth_date','age']]

「ビックデータ分析・活用のためのSQLレシピ」を読む

3-1-3 文字列を配列に分解する

import re

def process(s):
    m=re.search('//[^/]+([^?#]+)',s)
    return m.group(0).split('/')[3]

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM access_log", conn)
df['path1']=df['url'].map(process)

3-1-4 日付やタイムスタンプを扱う

import datetime 
import time

df = pd.DataFrame({'dt':[datetime.date.today()],'timestamp':[time.ctime()]})

指定した値の日付や時刻データを取得する

import datetime 
import time
from dateutil.parser import parse

df = pd.DataFrame({'dt':[parse('2015-10-12')],'timestamp':[parse('2015-10-12 12:00:00')]})

日付け/時刻から特定のフィールドを取り出す

import datetime 
import time
from dateutil.parser import parse

df = pd.DataFrame({'stamp':[parse('2015-10-12 12:00')]})
df['year'] = df['stamp'].map(lambda x:x.year)
df['month'] = df['stamp'].map(lambda x:x.month)
df['day'] = df['stamp'].map(lambda x:x.day)
df['hour'] = df['stamp'].map(lambda x:x.hour)

#別解
df = pd.DataFrame({'stamp':[parse('2015-11-30 23:00')]})
df['year'] = df['stamp'].dt.year
df['month'] = df['stamp'].dt.month
df['day'] = df['stamp'].dt.day
df['hour'] = df['stamp'].dt.hour

3-1-5 欠損値をデフォルト

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM purchase_log_with_coupon", conn)

df = df.fillna(0)

「ビックデータ分析・活用のためのSQLレシピ」を読む

3-1 1つの値に対する操作

3-1-1 コードをラベルに置き換える

import pandas as pd
import psycopg2

conn = psycopg2.connect("dbname=BigData host=localhost user=testuser")
df = pd.read_sql("SELECT * FROM mst_users", conn)

dev = {
    1:"PC",
    2:"SP",
    3:"アプリ"
}
df['register_device'] = df['register_device'].map(dev)

3-1-2 URLから要素を取り出す

conn = psycopg2.connect("dbname=BigData host=localhost 
user=testuser")
df = pd.read_sql("SELECT * FROM access_log", conn)

df['referrer_host'] =  df['url'].str.extract('https?://([^/]*)',expand=False)
df[['stamp','referrer_host']]

df = pd.read_sql("SELECT * FROM access_log", conn)
df['path'] = df['url'].str.extract('//[^/]+([^?#]+)',expand=False)
df['id'] = df['url'].str.extract('id=([^&]*)',expand=False)
df[['stamp','url','path','id']]

10年戦えるデータ分析入門

8章 遅れて来た分析SQL最強の武器 -ウインドウ関数

8.1 サブクエリで複雑なSELECT文を組み立てる

かなり複雑なことをやらせられるようになった。FORM句
のあとにSELECT文をかける。またin句のあとにもかける。

8.2 ウインドウ関数でグループ全体を対象にした計算をする

主要なウインドウ関数(postgreSQL)
count
sum
avg
rank グループ内をソートして順位付けする(重複あり)
row_numer グループ内をソートして順位付けする(重複なし)
lag グループ内をソートして前の行の値をとる
lead グループ内をソートして後ろの行の値をとる
通常の集約関数もウインドウ関数になる
構文は
ウインドウ関数 OVER (PARTITION BY col
ORDER BY col [DESC or ASC]
(PARTITION BYは必須ではない)

実行される順番
ジョイン処理
WHERE句での絞り込み
GROUP BY句によるグルーピング
SELECT句による計算と集約
HAVING句による絞り込み
ウインドウ関数　　ここで実行される
ORDER BY句によりソート
LIMIT句による制限

8.3 履歴テーブルから最新行を取る

　row_number関数を使う

8.4 対全体比

　sum関数を使って累積和を求める。

8.5 デシル分析をする

　ntile関数を使う

8.6 時系列データの処理

　移動平均

9章 縦と横は難しい

9.1 横持テーブルと縦持テーブル

9.2 横持から縦持ちへの変換

　CASE * WHEN ENDで変換する
　unnest関数を使うと(postgreSQL固有)を使うともっと簡単にできる

9.3 縦持ちから横持への変換

　横->縦と同じようにCASE句を使う
　array_agg関数を使うと(postgreSQL固有)を使うと簡単にできる

9.4 可変長の値を行に展開する

　JSON,XML形式データへの対応

10章 アクセスログのセッション分析をする

10.1 アクセスログとセッション

　累積和でセッションIDをつくる

10.2 セッションに対するパターンマッチ

第2部は省略　

10年戦えるデータ分析入門

7章 ジョインを制する者はRDBMSを制す　応用編

7.3 一歩進んだjoin

セルフジョイン　１年前の売り上げと比較するなどに使用される

7.4 組み合わせを生成するジョインでバスケット分析

かなり複雑

10年戦えるデータ分析入門

6章 ジョインを制する者はRDBMSを制す 基礎編

6.2 joinでテーブルを連結する

基本構文
SELECT
*
FROM
assess_log as a
join customers as c
on a.customer_id = c.customer_id
;
joinにはインナー結合とアウター結合がある

実行順
ジョイン処理
WHERE句による行絞り込み
GROUP BY句によるグルーピング
SELECT句による計算と集約
HAVING句による絞り込み
ORDER句による並び替え
LIMIT句による制限

6.5 テーブルを作成する

外部からデータを取り入れるときは
COPY table name FROM 'csvファイルのフルパス', WITH FORMAT csv;