4-2-2 ABC分析で売れ筋を判別する conn = psycopg2.connect("dbname=BigData host=localhost user=testuser") df = pd.read_sql("SELECT * FROM purchase_detail_log", conn) print(df.head(10)) df1 = df.groupby('category')['price'].sum() df2 = pd.Data…

4-4-6 売上を把握するための大事なポイント conn = psycopg2.connect("dbname=BigData host=localhost user=testuser") df = pd.read_sql("SELECT * FROM purchase_log_long", conn) print(df) #難しい df['dt'] = pd.to_datetime(df['dt']) df = df.set_ind…

4-4-2 移動平均を用いて日別の推移を見る 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 purchase_log", conn) print(df.head(10)) gb…

4章 売上を把握するためのデータ抽出 4-4 時系列に沿ってデータを集約する 4-4-1 日別の売上を集計する import pandas as pd import psycopg2 import numpy as np conn = psycopg2.connect("dbname=BigData host=localhost user=testuser") df = pd.read_sql…

3-4-3 条件のフラグを0と1で表現する import pandas as pd import psycopg2 import numpy as np conn = psycopg2.connect("dbname=BigData host=localhost user=testuser") df1 = pd.read_sql("SELECT * FROM mst_users_with_card_number", conn) df2 = pd.r…

3-4 複数のテーブルに対する操作 3-4-1 複数のテーブルを縦に並べる import pandas as pd import psycopg2 import numpy as np conn = psycopg2.connect("dbname=BigData host=localhost user=testuser") df1 = pd.read_sql("SELECT * FROM app1_mst_users",…

3-3-2 グループの中での順序を扱う conn = psycopg2.connect("dbname=BigData host=localhost user=testuser") df = pd.read_sql("SELECT * FROM popular_products", conn) print(df) df['row'] = df['score'].rank( ascending=False, method='first' ).asty…

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…

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'] …

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", con…

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…