import sqlite3
import pandas as pd

# 轉換用程式碼，就不做成函式，直接攤平講解

if __name__ == '__main__':
    # 欲讀取的資料庫
    cnx = sqlite3.connect('test.sqlite')

    # 兩張table 的內部資料 dataframe
    customers = pd.read_sql_query("SELECT * FROM customers", cnx)
    orders = pd.read_sql_query("SELECT * FROM orders", cnx)

    # 第一步驟: 決定先用group by 看一下 customer 客戶有幾筆資料!
    """
    和網站中不同的是，我們希望能夠根據不同的客戶比例抽樣
    因為不知道抽樣比例，所以要先計算幾種客戶及客戶數量
    """
    df = pd.read_sql_query("SELECT * FROM orders", cnx)
    groupby = df.groupby('customer_name').size().reset_index(name='size')

    df = groupby.sort_values('size', ascending=False)
    df = df.reset_index()

    # 第二步驟: 調整customer groupby 數量, 取得白名單，可隨心所欲調整, 目前為100
    """
    樣本的比例若占太少，不予採用，這邊設10
    """
    df = df[df['size'] > 10]

    white_list = df['customer_name'].tolist()
    value_list = df['size'].tolist()

    # 第三步驟: 取出order 全部的資料並filter by 白名單 white list
    # 主要是篩選掉剛剛比例較少的資料名單
    df = pd.read_sql_query("SELECT * FROM orders", cnx)
    df = df[df['customer_name'].isin(white_list)]

    # 第四步驟: 隨機抽樣
    """
    使用pandas
    DataFrame.sample(n=None, frac=None, replace=False, weights=None, random_state=None, axis=None)
    n是要抽取的行數。（例如n=20000時，抽取其中的2W行）
    frac是抽取的比列。（有一些時候，我們並對具體抽取的行數不關係，我們想抽取其中的百分比，這個時候就可以選擇使用frac，例如frac=0.8，就是抽取其中80%）
    replace：是否為有放回抽樣，取replace=True時為有放回抽樣。
    weights這個是每個樣本的權重，具體可以看官方文件說明。
    random_state這個在之前的文章已經介紹過了。
    axis是選擇抽取資料的行還是列。axis=0的時是抽取行，axis=1時是抽取列（也就是說axis=1時，在列中隨機抽取n列，在axis=0時，在行中隨機抽取n行）
    """
    fracs = {}
    df_len = len(df)
    # 依照剛剛篩選的客戶名單與各個客戶的訂單數量組成比例
    for c, v in zip(white_list, value_list):
        fracs[c] = v/df_len

    # 定義想抽多少, N = 想抽樣出多少資料
    N = 5000
    df = pd.concat(dff.sample(n=int(fracs.get(i) * N)) for i, dff in df.groupby('customer_name'))

    # 可供檢查，利用group by 檢查數量
    # df = df.groupby('customer_id').size().reset_index(name='size')
    print(df)

    """
    原本的範例程式碼！
    # df1 = pd.DataFrame({'group_id': ['A', 'B', 'C'] * 40,
    #                     'vals': np.random.randn(120)})
    # N = len(df1)
    # fracs = {'A': 1 / 20, 'B': 1 / 30, 'C': 1 / 60}
    # print(pd.concat(dff.sample(n=int(fracs.get(i) * N)) for i, dff in df1.groupby('group_id')))
    """

    # 第五步驟: 準備作業: 存新的sqlite
    # 準備要存入的table資料
    orders = df

    # 我們同時也要利用白名單篩選customer 當作我們的第二個table
    df = pd.read_sql_query("SELECT * FROM customers", cnx)
    customers = df[df['customer_name'].isin(white_list)]

    # 第六步驟: 存進資料庫: 新增資料庫

    conn = sqlite3.connect('sampling.sqlite')  # You can create a new database by changing the name within the quotations
    c = conn.cursor()  # The database will be saved in the location where your 'py' file is saved

    # Create table - customers
    c.execute('''CREATE TABLE customers
                 ([id] integer, [customer_name] text, [region] text, [country] text)''')

    # Create table - orders
    c.execute('''CREATE TABLE orders
                 ([id] INTEGER PRIMARY KEY, [order_id] text, [ship_date] date, [customer_name] text, [customer_name] text,
                 [department_id] text, [unit] real, [price] real, 
                 FOREIGN KEY(customer_id) REFERENCES customers(customer_name))''')

    conn.commit()

    # 第七步驟: 存進資料庫: 塞資料
    customers.to_sql('customers', conn, if_exists='append', index=False)
    orders.to_sql('orders', conn, if_exists='append', index=False)

    """
    使用pandas
    DataFrame.sample(n=None, frac=None, replace=False, weights=None, random_state=None, axis=None)
    n是要抽取的行數。（例如n=20000時，抽取其中的2W行）
    frac是抽取的比列。（有一些時候，我們並對具體抽取的行數不關係，我們想抽取其中的百分比，這個時候就可以選擇使用frac，例如frac=0.8，就是抽取其中80%）
    replace：是否為有放回抽樣，取replace=True時為有放回抽樣。
    weights這個是每個樣本的權重，具體可以看官方文件說明。
    random_state這個在之前的文章已經介紹過了。
    axis是選擇抽取資料的行還是列。axis=0的時是抽取行，axis=1時是抽取列（也就是說axis=1時，在列中隨機抽取n列，在axis=0時，在行中隨機抽取n行）
    """
    fracs = {}
    df_len = len(df)
    # 依照抽樣依據的欄位, 可能是項目名稱/id去組抽樣比例
    # 在這段程式碼, white_list是客戶名稱, value_list 是客戶對應的資料筆數
    # 這段程式碼主要是組成類似 {'A': 1 / 20, 'B': 1 / 30, 'C': 1 / 60}, 這種比例
    for c, v in zip(white_list, value_list):
        fracs[c] = v/df_len

    # 定義想抽多少, N = 想抽樣出多少資料
    N = 5000
    # 接著開始依據比例抽樣
    df = pd.concat(dff.sample(n=int(fracs.get(i) * N)) for i, dff in df.groupby('customer_name'))

# random 製作出一個資料dataframe
df1 = pd.DataFrame({'group_id': ['A', 'B', 'C'] * 40,
                    'vals': np.random.randn(120)})

N = len(df1)
# 這邊是定義抽樣的類別跟比例
fracs = {'A': 1 / 20, 'B': 1 / 30, 'C': 1 / 60}
# 這邊是印出來，可以選擇用變數存
print(pd.concat(dff.sample(n=int(fracs.get(i) * N)) for i, dff in df1.groupby('group_id')))