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