资金流入流出预测-挑战Baseline

资金流入流出预测赛题背景

蚂蚁金服拥有上亿会员，每天都涉及大量的资金流入和流出，资金管理压力会非常大。
在既保证资金流动性风险最小，又满足日常业务运转的情况下，精确地预测资金的流入流出情况变得尤为重要。

赛题描述

余额宝用户的申购赎回数据（2013年7月-2014年8月；每天；28041位用户，2840421条记录）等信息

预测未来每日的资金流入流出情况

Overview

数据集

• 用户信息表： user_profile_table

  1. user_id : 用户 ID
  2. Sex : 用户性别（ 1 ：男， 0 ：女 ）
  3. City : 所在城市
  4. constellation : 星座

• 用户申购赎回数据表： user_balance_table

  1. user_id : 用户 ID
  2. report_date : 日期
  3. tBalance : 今日余额
  4. yBalance : 昨日余额
  5. total_purchase_amt : 今日总购买量 = 直接购买 + 收益
  6. direct_purchase_amt : 今日直接购买量
  7. purchase_bal_amt : 今日支付宝余额购买量
  8. purchase_bank_amt : 今日银行卡购买量
  9. total_redeem_amt : 今日总赎回量 = 消费 + 转出
  10. consume_amt : 今日消费总量
  11. transfer_amt : 今日转出总量
  12. tftobal_amt : 今日转出到支付宝余额总量
  13. tftocard_amt : 今日转出到银行卡总量
  14. share_amt : 今日收益
  15. category : 今日类目 消费总额

• 收益表为余额宝在 14 个月内的收益率表： mfd_day_share_interest

  1. mfd_date:日期
  2. mfd_daily_yield:万份收益，即 1 万块钱的收益
  3. mfd_7daily_yield: 七日年化收益率（ % ）

• 银行间拆借利率表是 14 个月期间银行之间的拆借利率（皆为年化利率）： mfd_bank_shibor

  1. mfd_date: 日期
  2. Interest_O_N: 隔夜利率(W 星期、M 月 Y 年)

导入相关库

import pandas as  pd
import numpy as np
import warnings 
import datetime
import seaborn as sns
import matplotlib.pyplot as plt
import datetime 
from scipy import stats
import warnings
warnings.filterwarnings('ignore')

读取数据集

data_balance = pd.read_csv(dataset_path+'user_balance_table.csv')

	user_id	report_date	tBalance	yBalance	total_purchase_amt	direct_purchase_amt	purchase_bal_amt	purchase_bank_amt	total_redeem_amt	consume_amt	transfer_amt	tftobal_amt	tftocard_amt	share_amt	category1	category2	category3	category4
0	1	20140805	20385	20383	2	0	0	0	0	0	0	0	0	2	NaN	NaN	NaN	NaN
1	1	20140808	20391	20389	2	0	0	0	0	0	0	0	0	2	NaN	NaN	NaN	NaN
2	1	20140811	20397	20395	2	0	0	0	0	0	0	0	0	2	NaN	NaN	NaN	NaN
3	1	20140814	20403	20401	2	0	0	0	0	0	0	0	0	2	NaN	NaN	NaN	NaN
4	1	20140817	20409	20407	2	0	0	0	0	0	0	0	0	2	NaN	NaN	NaN	NaN

为数据集添加时间戳

data_balance['date'] = pd.to_datetime(data_balance['report_date'], 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['week'] = data_balance['date'].dt.week
data_balance['weekday'] = data_balance['date'].dt.weekday

data_balance.head()

	user_id	report_date	tBalance	yBalance	total_purchase_amt	direct_purchase_amt	purchase_bal_amt	purchase_bank_amt	total_redeem_amt	consume_amt	...	category1	category2	category3	category4	date	day	month	year	week	weekday
0	1	20140805	20385	20383	2	0	0	0	0	0	...	NaN	NaN	NaN	NaN	2014-08-05	5	8	2014	32	1
1	1	20140808	20391	20389	2	0	0	0	0	0	...	NaN	NaN	NaN	NaN	2014-08-08	8	8	2014	32	4
2	1	20140811	20397	20395	2	0	0	0	0	0	...	NaN	NaN	NaN	NaN	2014-08-11	11	8	2014	33	0
3	1	20140814	20403	20401	2	0	0	0	0	0	...	NaN	NaN	NaN	NaN	2014-08-14	14	8	2014	33	3
4	1	20140817	20409	20407	2	0	0	0	0	0	...	NaN	NaN	NaN	NaN	2014-08-17	17	8	2014	33	6

5 rows × 24 columns

时间序列分析

分析每日总购买与赎回量的时间序列

# 聚合时间数据
total_balance = data_balance.groupby(['date'])['total_purchase_amt','total_redeem_amt'].sum().reset_index()
# 生成测试集区段数据
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)

# 为数据集添加时间戳total_balance['day'] = total_balance['date'].dt.day
total_balance['month'] = total_balance['date'].dt.month
total_balance['year'] = total_balance['date'].dt.year
total_balance['week'] = total_balance['date'].dt.week
total_balance['weekday'] = total_balance['date'].dt.weekday

total_balance.head()

	date	total_purchase_amt	total_redeem_amt	day	month	year	week	weekday
0	2013-07-01	32488348.0	5525022.0	1	7	2013	27	0
1	2013-07-02	29037390.0	2554548.0	2	7	2013	27	1
2	2013-07-03	27270770.0	5953867.0	3	7	2013	27	2
3	2013-07-04	18321185.0	6410729.0	4	7	2013	27	3
4	2013-07-05	11648749.0	2763587.0	5	7	2013	27	4

# 画出每日总购买与赎回量的时间序列图fig = plt.figure(figsize=(20,6))
plt.plot(total_balance['date'], total_balance['total_purchase_amt'],label='purchase')
plt.plot(total_balance['date'], total_balance['total_redeem_amt'],label='redeem')plt.legend(loc='best')
plt.title("The lineplot of total amount of Purchase and Redeem from July.13 to Sep.14")
plt.xlabel("Time")
plt.ylabel("Amount")
plt.show()

星期特征分析

# 画出每个星期的数据分布于整体数据的分布图a = plt.figure(figsize=(10,10))
scatter_para = {'marker':'.', 's':3, 'alpha':0.3}
line_kws = {'color':'k'}
plt.subplot(2,2,1)
plt.title('The distrubution of total purchase')
sns.violinplot(x='weekday', y='total_purchase_amt', data = total_balance_1, scatter_kws=scatter_para, line_kws=line_kws)
plt.subplot(2,2,2)
plt.title('The distrubution of total purchase')
sns.distplot(total_balance_1['total_purchase_amt'].dropna())
plt.subplot(2,2,3)
plt.title('The distrubution of total redeem')
sns.violinplot(x='weekday', y='total_redeem_amt', data = total_balance_1, scatter_kws=scatter_para, line_kws=line_kws)
plt.subplot(2,2,4)
plt.title('The distrubution of total redeem')
sns.distplot(total_balance_1['total_redeem_amt'].dropna())

# 按翌日对数据聚合后取均值week_sta = total_balance_1[['total_purchase_amt', 'total_redeem_amt', 'weekday']].groupby('weekday', as_index=False).mean()

week_sta

	weekday	total_purchase_amt	total_redeem_amt
0	0	3.075183e+08	3.268733e+08
1	1	3.173125e+08	3.138971e+08
2	2	3.133103e+08	3.076354e+08
3	3	3.019386e+08	2.763898e+08
4	4	2.398712e+08	2.591565e+08
5	5	1.837682e+08	1.855464e+08
6	6	1.884616e+08	1.980553e+08

# 分析星期的中位数特征plt.figure(figsize=(12, 5))
ax = plt.subplot(1,2,1)
plt.title('The barplot of average total purchase with each weekday')
ax = sns.barplot(x="weekday", y="total_purchase_amt", data=week_sta, label='Purchase')
ax.legend()
ax = plt.subplot(1,2,2)
plt.title('The barplot of average total redeem with each weekday')
ax = sns.barplot(x="weekday", y="total_redeem_amt", data=week_sta, label='Redeem')
ax.legend()

月份特征分析

# 画出每个月的购买总量分布估计图(kdeplot)plt.figure(figsize=(15,10))
plt.title('The Probability Density of total purchase amount in Each Month')
plt.ylabel('Probability')
plt.xlabel('Amount')
for i in range(7, 12):sns.kdeplot(total_balance[(total_balance['date'] >= datetime.date(2013,i,1)) & (total_balance['date'] < datetime.date(2013,i+1,1))]['total_purchase_amt'],label='13Y,'+str(i)+'M')
for i in range(1, 9):sns.kdeplot(total_balance[(total_balance['date'] >= datetime.date(2014,i,1)) & (total_balance['date'] < datetime.date(2014,i+1,1))]['total_purchase_amt'],label='14Y,'+str(i)+'M')