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

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 = df.groupby('dt')
s = pd.Series(gb['purchase_amount'].sum())
df1 = pd.DataFrame({'total_amount':s,
                'saven_day_avg_amount':s.rolling(window=7,min_periods=1).mean(),
                'saven_day_avg_amount_strict':s.rolling(window=7,min_periods=7).mean()
               })
print(df1)

4-4-3 当月売上の累計を求める

gb = df.groupby('dt')
df1 = pd.DataFrame({'total_amount':pd.Series(gb['purchase_amount'].sum())})
df1.index = pd.to_datetime(df1.index)
df1['agg_amount'] = df1['total_amount'].cumsum()
print(df1)