资金流入流出预测—baseline的理解和跑通

dandellion_
已于 2023-11-14 08:00:01 修改
阅读量569
点赞数 3
文章标签： python 机器学习时序数据库
于 2023-10-17 11:06:06 首次发布
本文链接：https://blog.csdn.net/dandellion_/article/details/133879500
版权
# 数据格式处理
import pandas as pd
import numpy as np

# 时间特征提取
import datetime

# 忽略警告：防止其干扰程序运行
import warnings
warnings.filterwarnings('ignore')

# 设置数据集路径
path = 'E:/编程/资金流入流出预测/Purchase Redemption Data/'


# 提取时间戳：从日期中提取日、月、年、星期
# 定义函数并对参数和返回值的类型进行注释，增加可读性
def add_timestamp(data: pd.DataFrame, time_index: str = 'report_date') -> pd.DataFrame:  # time_index是索引字段名
    # 创建数据的深层拷贝：参数传递
    data_balance = data.copy()
    # 将MySQL类型日期转化为’年月日‘格式
    data_balance['date'] = pd.to_datetime(data_balance[time_index], format="%Y%m%d")
    # 提取时序特征
    data_balance['day'] = data_balance['date'].dt.day
    data_balance['month'] = data_balance['date'].dt.month
    data_balance['year'] = data_balance['date'].dt.year
    data_balance['weekday'] = data_balance['date'].dt.weekday
    '''数据清洗时会删除空值行，此时DataFrame或Series类型的数据不再是连续的索引'''
    # 使用reset_index(drop=True)重置原有索引列，不另添新列
    return data_balance.reset_index(drop=True)


# 载入用户申购赎回数据表
def load_data(path: str = 'user_balance_table.csv') -> pd.DataFrame:
    # 读取数据集文件
    data_balance = pd.read_csv(path)
    # 提取时间戳
    data_balance = add_timestamp(data_balance)
    return data_balance.reset_index(drop=True)


# 统计每日申购总额和赎回总额：截取已知时间范围2014-03-31~2014-08-31的数据
def get_total_balance(data: pd.DataFrame, date: str = '2014-03-31') -> pd.DataFrame:
    total_balance = data.copy()
    # 聚合时间数据，行为'total_purchase_amt'和'total_redeem_amt'，列为不同的 'date'
    # 按日期分组统计总额，求各个date所有用户的'total_purchase_amt', 'total_redeem_amt'两个字段之和，并生成表格
    total_balance = total_balance.groupby(total_balance['date'])[['total_purchase_amt', 'total_redeem_amt']].sum()
    # 使用reset_index(inplace=True)原地重置索引，不创建新对象
    total_balance.reset_index(inplace=True)
    # 由于9月以后的'total_purchase_amt', 'total_redeem_amt'两个字段为空值被清除，这里只取到2014-03-31~2014-08-31的数据
    return total_balance[(total_balance['date'] >= date)].reset_index(drop=True)


# 随机抽取的约 3 万用户中的部分用户在2014年9月首次出现，这部分用户只在测试数据中
# 生成测试集区段数据：截取预测时间范围2014,9,1~2014,9,30的数据
def generate_test_data(data: pd.DataFrame) -> pd.DataFrame:
    # 创建数据的深层拷贝：保证原表格数据不变
    total_balance = data.copy()
    # 规定起始日期
    start = datetime.datetime(2014, 9, 1)
    # 用于储存测试集区段数据的二维数组的初始化
    testdata = []
    # 规定终止日期
    while start != datetime.datetime(2014, 10, 1):
        # 填入日期一列，其余两个字段为空
        temp = [start, np.nan, np.nan]
        testdata.append(temp)
        start += datetime.timedelta(days=1)
    # 将二维数组转为数据框格式
    testdata = pd.DataFrame(testdata)
    # 按日期索引，在空列填入申购总额和赎回总额
    testdata.columns = total_balance.columns
    # 纵向拼接训练集数据与测试集数据
    total_balance = pd.concat([total_balance, testdata], axis=0)
    return total_balance.reset_index(drop=True)


'''数据预处理'''
balance_data = load_data(path + 'user_balance_table.csv')
total_balance = get_total_balance(balance_data, date='2014-03-01')
total_balance = generate_test_data(total_balance)
total_balance = add_timestamp(total_balance, 'date')    # 规定以日期为索引


# 定义生成时间序列规则预测结果的方法
def generate_base(data: pd.DataFrame, month_index: int) -> pd.DataFrame:
    # 创建数据的深层拷贝：保证原表格数据不变
    total_balance = data.copy()
    # 取 'date', 'total_purchase_amt', 'total_redeem_amt' 三个字段的数据
    total_balance = total_balance[['date', 'total_purchase_amt', 'total_redeem_amt']]
    # 选中固定时间段的数据集：month_index依次赋4~9
    total_balance = total_balance[
        (total_balance['date'] >= datetime.datetime(2014, 3, 1)) &
        (total_balance['date'] < datetime.datetime(2014, month_index, 1))]

    # 加入时间戳
    total_balance['weekday'] = total_balance['date'].dt.weekday
    total_balance['day'] = total_balance['date'].dt.day
    total_balance['month'] = total_balance['date'].dt.month

    # 统计翌日因子
    # # 以星期为索引，统计所有用户 各个工作日的 申购总额的平均值 和 赎回总额的平均值
    mean_of_each_weekday = total_balance[['weekday'] + ['total_purchase_amt', 'total_redeem_amt']]\
        .groupby('weekday', as_index=False).mean()
    # # 修改字段名，用于储存新特征
    for name in ['total_purchase_amt', 'total_redeem_amt']:
        mean_of_each_weekday = mean_of_each_weekday.rename(columns={name: name + '_weekdaymean'})
    # # 计算翌日因子：以上均值 分别除以 所有用户 各天的申购总额 的平均值 和 各天的赎回总额 的平均值
    mean_of_each_weekday['total_purchase_amt_weekdaymean'] /= np.mean(total_balance['total_purchase_amt'])
    mean_of_each_weekday['total_redeem_amt_weekdaymean'] /= np.mean(total_balance['total_redeem_amt'])

    # 合并统计结果到原数据集：连接键为 'weekday' 字段，该字段为左参数的索引行
    total_balance = pd.merge(total_balance, mean_of_each_weekday, on='weekday', how='left')

    # 分别统计翌日在(1~31)号出现的频次
    # # 行为 'weekday'，列为 'day'，统计 'weekday' 在 'day' 中出现的频次
    weekday_count = total_balance[['day', 'weekday', 'date']].groupby(['day', 'weekday'], as_index=False).count()
    # # 将频次与翌日因子合并，连接键为 'weekday'
    weekday_count = pd.merge(weekday_count, mean_of_each_weekday, on='weekday')

    # 依据频次对翌日因子进行加权，获得日期因子
    # # unique函数去重，len函数统计 'month' 的列长，得到月份数
    # # 权值即为平均每月翌日数
    weekday_count['total_purchase_amt_weekdaymean'] *= weekday_count['date'] / len(np.unique(total_balance['month']))
    weekday_count['total_redeem_amt_weekdaymean'] *= weekday_count['date'] / len(np.unique(total_balance['month']))
    day_rate = weekday_count.drop(['weekday', 'date'], axis=1).groupby('day', as_index=False).sum()

    # 将训练集中所有日期的均值剔除日期残差得到base
    day_mean = total_balance[['day'] + ['total_purchase_amt', 'total_redeem_amt']].groupby('day', as_index=False).mean()
    day_pre = pd.merge(day_mean, day_rate, on='day', how='left')
    day_pre['total_purchase_amt'] /= day_pre['total_purchase_amt_weekdaymean']
    day_pre['total_redeem_amt'] /= day_pre['total_redeem_amt_weekdaymean']

    # 生成测试集数据
    ''' iterrows：由一个数据框生成一个二维元组，按索引顺序依次遍历，并将一维元组的元素赋给变量
        一维元组：第一个元素是索引，第二个元素是索引对应那行的数据与其字段组成的键值对 所构成的字典'''
    for index, row in day_pre.iterrows():
        if month_index in (2, 4, 6, 9) and row['day'] == 31:
            break
        day_pre.loc[index, 'date'] = datetime.datetime(2014, month_index, int(row['day']))

    # 基于base与翌日因子获得最后的预测结果
    day_pre['weekday'] = day_pre.date.dt.weekday
    day_pre = day_pre[['date', 'weekday'] + ['total_purchase_amt', 'total_redeem_amt']]
    day_pre = pd.merge(day_pre, mean_of_each_weekday, on='weekday')
    day_pre['total_purchase_amt'] *= day_pre['total_purchase_amt_weekdaymean']
    day_pre['total_redeem_amt'] *= day_pre['total_redeem_amt_weekdaymean']
    # # 对预测结果四舍五入取整
    day_pre['total_purchase_amt'] = np.rint(day_pre['total_purchase_amt'])
    day_pre['total_redeem_amt'] = np.rint(day_pre['total_redeem_amt'])

    day_pre = day_pre.sort_values('date')[['date'] + ['total_purchase_amt', 'total_redeem_amt']]
    return day_pre


# 创建数据的深层拷贝：保证原表格数据不变
data = total_balance.copy()

# 生成预测结果
# # 用于储存预测结果的列表初始化
base_list = []
# # 逐月修正模型，从而预测9月数据
for i in range(4, 10):
    base_list.append(generate_base(data, i).reset_index(drop=True))
# # 通过concat函数拼接得到二维数组
base = pd.concat(base_list).reset_index(drop=True)
# # 重命名储存预测结果的字段
for i in ['total_purchase_amt', 'total_redeem_amt']:
    base = base.rename(columns={i: i+'_base'})
# # 将数据集与预测结果合并
data = pd.merge(data.reset_index(drop=True), base.reset_index(drop=True), on='date', how='left').reset_index(drop=True)

# 对结果表重命名
# # 选中需要提交的字段
data = data[['date', 'total_purchase_amt_base', 'total_redeem_amt_base']]
# # 去除无关日期数据
data = data[total_balance['date'] >= datetime.datetime(2014, 9, 1)]
# # 将日期间的'-'去除，把数据的'.0'去除
for i in data.columns:
    if i == 'date':
        data[i] = data[i].astype(str)
        data[i] = data[i].str.replace('-', '')
    else:
        data[i] = data[i].astype(np.int64)

# 保存预测结果到本地：不显示索引值和列名
data.to_csv(path + 'comp_predict_table.csv', index=False, header=False)