Comparing Online and Offline Spending Amount and Frequency

import pandas as pd

def get_ka_info(filepath, sheet_name):
    """
    获得纯粹的用户分组标签信息
    :param filepath: 标签文件路径
    :param sheet_name: 标签文件表名
    :return: 用户分组标签信息
    """
    data = pd.read_excel(filepath, sheet_name=sheet_name)
    data = data[['km_clu', 'account_id']]
    return data

def get_trans_info():
    """
    处理交易数据，主要进行人店天合并和去除退单
    :return: 处理好的fact_trans
    """
    # Read in the data
    fact_trans = pd.read_csv('data/Fact_Trans.csv', parse_dates=['sap_time'], index_col=0,
                             dtype={'trans_id': 'str', 'account_id': 'int', 'product_id': 'str', 'store_id': 'int'})

    # Merge by customer-store-day
    agg_dict = {'sales': 'sum', 'qtys': 'sum', 'discount_amt': 'sum', 'store_id': 'first'}
    fact_trans = fact_trans.groupby(['account_id', 'sap_time'], as_index=False).agg(agg_dict)

    # Remove negative orders
    fact_trans = fact_trans.loc[fact_trans['sales'] > 0]

    return fact_trans

def calculate_result(channel, fact_trans, ka):
    """
    计算线上和线下每一个用户的交易金额和交易频次
    :param channel: online 或者其他，指线上或者线下
    :param fact_trans: 交易数据，由上游函数处理得到
    :param ka: 用户分组标签，由上游函数处理得到
    :return: None
    """
    dim_store = pd.read_csv('data/Dim_Store.csv')

    if channel == 'online':
        dim_store = dim_store.loc[dim_store['region'] == 'EBUSINESS']
    else:
        dim_store = dim_store.loc[dim_store['region'] != 'EBUSINESS']

    dim_store = dim_store[['store_id', 'store_code', 'region']]

    # Merge the store data with the transaction data
    store_trans = fact_trans.merge(dim_store, on='store_id')

    # Merge the ka data with the store transaction data
    ka = ka.merge(store_trans, on='account_id')

    # Extract the relevant information
    ka = ka[['km_clu', 'account_id', 'sales', 'store_id', 'store_code']]

    # Calculate the number of online purchases
    frequency = ka.groupby(ka['account_id']).apply(lambda x: len(x))
    frequency = pd.DataFrame({'frequency': frequency})

    # Calculate the spending amount by group
    agg_dict = {'sales': 'sum', 'store_id': 'first', 'store_code': 'first', 'km_clu': 'first'}
    monetary = ka.groupby(['account_id']).agg(agg_dict)

    result = pd.merge(monetary, frequency, on='account_id')

    return result

def validate_result():
    """
    校验线上数据有效
    :return: True or False
    """
    valid_store_id_list = pd.read_csv('online.csv')['store_id'].unique()

    data = pd.read_excel('online_info.xlsx')
    all_online_monetary = sum(data['sales'])
    all_online_frequency = sum(data['frequency'])

    offline_data = pd.read_excel('offline_info.xlsx')
    all_offline_monetary = sum(offline_data['sales'])
    all_offline_frequency = sum(offline_data['frequency'])

    result_store_id_list = data['store_id'].unique()

    if set(result_store_id_list) & set(valid_store_id_list) == set(result_store_id_list):
        return all_online_monetary, all_online_frequency, all_offline_monetary, all_offline_frequency

if __name__ == '__main__':
    ka_path = 'CLTV_data.xlsx'
    ka_sheet = 'dat_seg'

    data_ka = get_ka_info(ka_path, ka_sheet)
    data_trans = get_trans_info()

    calculate_result('online', data_trans, data_ka).to_excel('online_info.xlsx')
    print('线上交易信息文件online_info.xlsx已经保存')
    calculate_result('offline', data_trans, data_ka).to_excel('offline_info.xlsx')
    print('线下交易信息文件offline_info.xlsx已经保存')

    result = validate_result()
    if result:
        print("""
        经过校验: 结果真实有效。
        线上消费总金额%d
        线上消费总频次%d
        线下消费总金额%d
        线下消费总频次%d
        """ % result, """
        线上消费金额占总消费金额{:.2%}
        线下消费金额占总消费金额{:.2%}
        线上消费频次占总消费频次{:.2%}
        线下消费频次占总消费频次{:.2%}
        """.format(result[0] / (result[0] + result[2]),
                   result[2] / (result[0] + result[2]),
                   result[1] / (result[1] + result[3]),
                   result[3] / (result[1] + result[3])))