「ビックデータ分析・活用のための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']]