【资金流入流出预测】 数据探索

import pandas as  pd
import numpy as np
import warnings 
import datetime
import seaborn as sns
import matplotlib.pyplot as plt
warnings.filterwarnings('ignore')
%matplotlib inline
plt.rcParams['font.sans-serif'] = ['KaiTi'] # 指定默认字体
plt.rcParams['axes.unicode_minus'] = False # 解决保存图像是负号'-'显示为方块的问题

字段了解

用户信息表： user_profile_table 。

我们总共随机抽取了约 3 万用户，其中部分用户在 2014 年 9 月份第一次出现，这部分用户只在测试数据中 。因此用户信息表是约 2.8 万 个用户的基本数据，在原始数据的基础上处理后，主要包含了用户的性别、城市和星座。具体的字段如下表 1 

user_p = pd.read_csv('user_profile_table.csv',index_col='user_id')

user_p  # 用户信息 性别 城市（数字编码） 星座 
user_p.shape 

(28041, 3)

user_b = pd.read_csv('user_balance_table.csv',index_col='user_id')

户申购赎回数据表：user_balance_table 。

里面有 20130701 至 20140831 申购和赎回信息、以及所有的子类目信息， 数据经过脱敏处理。脱敏之后的数据，基本保持了原数据趋势。数据主要包括用户操作时间和操作记录，其中操作记录包括申购和赎回两个部分。金额的单位是分，即 0.01 元人民币。 如果用户今日消费总量为0，即consume_amt=0，则四个字类目为空。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tF8sc2By-1597837748914)(attachment:image.png)]

user_b.isna().sum()  # balance记录了 每个用户每一天的交易记录 
user_b.fillna(0,inplace=True)  # 缺失值填充

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

mfd_7daily_yield 七日年化收益率是货币基金过去七天每万份基金份额净收益折合成的年收益率。
mfd_daily_yield 万份收益就是指基金公司通常日公布当日每万份基金单位实现的收益金额。

interest = pd.read_csv('mfd_day_share_interest.csv')

上海银行间同业拆放利率（Shibor）表

银行间拆借利率表是 14 个月期间银行之间的拆借利率（皆为年化利率）： mfd_bank_shibor 。具体字段如下表格 4 所示

shibor = pd.read_csv('mfd_bank_shibor.csv')

数据观察

user_p

没有足够信息 可能需要结合其他表格查看

user_p.head()

	sex	city	constellation
user_id
2	1	6411949	狮子座
12	1	6412149	摩羯座
22	1	6411949	双子座
23	1	6411949	双鱼座
25	1	6481949	双鱼座

user_p['sex'].value_counts()  # 男女分布均衡

1    14489
0    13552
Name: sex, dtype: int64

user_b 总体趋势

user_b['date'] = pd.to_datetime(user_b['report_date'], format= "%Y%m%d")
total_b = user_b.groupby(by='date').sum()
total_b.head()

	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
date
2013-07-01	8877639141	252327861	225364535	32488348	32458786	21250722	11208064	5525022	382160	5142862	5142862	0	29562	0.0	89350.0	0.0	292810.0
2013-07-02	9662736960	278810703	252327861	29037390	29007510	16561183	12446327	2554548	350066	2204482	2204482	0	29880	0.0	1650.0	0.0	348416.0
2013-07-03	10045220797	300127606	278810703	27270770	27234156	20493660	6740496	5953867	454406	5499461	5499461	0	36614	0.0	69764.0	0.0	384642.0
2013-07-04	10528358192	312038062	300127606	18321185	18282230	12108178	6174052	6410729	535391	5875338	5875338	0	38955	25078.0	107500.0	0.0	402813.0
2013-07-05	10951103520	320923224	312038062	11648749	11607991	6930431	4677560	2763587	697926	2065661	2065661	0	40758	0.0	51100.0	0.0	646826.0

plt.figure(figsize=(20,5))
for i in ['total_purchase_amt','total_redeem_amt']:
    plt.plot(total_b[i].index,total_b[i].values,label=f'{i}')
    plt.legend()
    
# 观察在14-1至14-4之间有着较大波动  四月之后开始平缓

plt.figure(figsize=(20,5))
for i in ['total_purchase_amt','total_redeem_amt']:
    plt.plot(total_b[i][total_b[i].index >pd.to_datetime('2014-4-1')].index,
             total_b[i][total_b[i].index >pd.to_datetime('2014-4-1')].values,label=f'{i}')
    plt.legend()

user_b 工作日和周末

total_b.reset_index(inplace=True)
total_b['weekday'] = total_b['date'].dt.weekday

tb_weekday_mean = total_b[['weekday','total_purchase_amt','total_redeem_amt']].groupby(by='weekday').mean()

plt.figure(figsize=(20,15))
plt.subplot(3,2,1)
sns.violinplot(x='weekday', y='total_purchase_amt', data = total_b)
plt.subplot(3,2,2)
sns.violinplot(x='weekday', y='total_redeem_amt', data = total_b)
plt.subplot(3,2,3)
sns.boxplot(x='weekday', y='total_purchase_amt', data = total_b)
plt.subplot(3,2,4)
sns.boxplot(x='weekday', y='total_redeem_amt', data = total_b)
plt.subplot(3,2,5)
sns.barplot(x=tb_weekday_mean.index,y=tb_weekday_mean['total_purchase_amt'])
plt.subplot(3,2,6)
sns.barplot(x=tb_weekday_mean.index,y=tb_weekday_mean['total_redeem_amt'])

<matplotlib.axes._subplots.AxesSubplot at 0x1d4c1e86978>

user_b 月份

total_b['month'] = total_b['date'].apply(lambda x:str(x)[:7])
tb_month_mean = total_b[['weekday','total_purchase_amt','total_redeem_amt']].groupby(by='weekday').mean()

plt.figure(figsize=(20,20))
plt.subplot(4,1,1)
sns.violinplot(x='month', y='total_purchase_amt', data = total_b)
plt.subplot(4,1,2)
sns.violinplot(x='month', y='total_redeem_amt', data = total_b)
plt.subplot(4,1,3)
sns.barplot(x='month', y='total_purchase_amt', data = total_b)
plt.subplot(4,1,4)
sns.barplot(x='month', y='total_redeem_amt', data = total_b)

<matplotlib.axes._subplots.AxesSubplot at 0x1d48bbaf198>

日期

total_b['week'] = total_b['date'].dt.week
total_b['year'] = total_b['date'].dt.year
total_b['week'] = total_b['year'].astype('str') + '_' + total_b['week'].astype('str')
day_table = total_b[['week','weekday','total_purchase_amt','total_redeem_amt',]]
tpa_day_c = pd.crosstab(day_table['week'],day_table['weekday'],values=day_table['total_purchase_amt'],aggfunc=np.mean)
tpa_day_c.loc['2014_1',:1] = tpa_day_c.loc['2013_1',:1]
tpa_day_c.drop(index='2013_1',inplace=True)

tpa_day_c = tpa_day_c.reset_index().join(tpa_day_c.reset_index()['week'].str.split('_',expand= True).rename(columns = {0:'w',1:'y'}))

tpa_day_c[['w','y']] = tpa_day_c[['w','y']].astype('int')
tpa_day_c.sort_values(by=['w','y'],inplace=True)

plt.figure(figsize=(16,9))
sns.heatmap(tpa_day_c.iloc[:,1:8],cmap='rainbow')

<matplotlib.axes._subplots.AxesSubplot at 0x1d48e1c0128>

节假日

total_b[total_b['date'] in pd.date_range('2014-5-1','2014-5-3')]

fest_51 = total_b[(total_b['date'] >= pd.to_datetime('2014-4-20'))&(total_b['date'] <= pd.to_datetime('2014-5-10'))]
fest_10 = total_b[(total_b['date'] >= pd.to_datetime('2013-9-20'))&(total_b['date'] <= pd.to_datetime('2013-10-20'))]

plt.figure(figsize=(20,10))
plt.subplot(2,1,1)
plt.axvspan(pd.to_datetime('2014-4-29'),pd.to_datetime('2014-5-1'), facecolor='m',alpha=0.1) 
for i in ['total_purchase_amt','total_redeem_amt']:
    plt.plot(fest_51['date'],fest_51[i].values,label=f'{i}')
    plt.legend()
plt.subplot(2,1,2)
plt.axvspan(pd.to_datetime('2013-10-1'),pd.to_datetime('2013-10-7'), facecolor='m',alpha=0.1) 
for i in ['total_purchase_amt','total_redeem_amt']:
    plt.plot(fest_10['date'],fest_10[i].values,label=f'{i}')
    plt.legend()

结合用户和交易

total_b

	date	report_date	tBalance	yBalance	total_purchase_amt	direct_purchase_amt	purchase_bal_amt	purchase_bank_amt	total_redeem_amt	consume_amt	...	tftocard_amt	share_amt	category1	category2	category3	category4	weekday	month	week	year
0	2013-07-01	8877639141	252327861	225364535	32488348	32458786	21250722	11208064	5525022	382160	...	0	29562	0.0	89350.0	0.0	292810.0	0	2013-07	2013_27	2013
1	2013-07-02	9662736960	278810703	252327861	29037390	29007510	16561183	12446327	2554548	350066	...	0	29880	0.0	1650.0	0.0	348416.0	1	2013-07	2013_27	2013
2	2013-07-03	10045220797	300127606	278810703	27270770	27234156	20493660	6740496	5953867	454406	...	0	36614	0.0	69764.0	0.0	384642.0	2	2013-07	2013_27	2013
3	2013-07-04	10528358192	312038062	300127606	18321185	18282230	12108178	6174052	6410729	535391	...	0	38955	25078.0	107500.0	0.0	402813.0	3	2013-07	2013_27	2013
4	2013-07-05	10951103520	320923224	312038062	11648749	11607991	6930431	4677560	2763587	697926	...	0	40758	0.0	51100.0	0.0	646826.0	4	2013-07	2013_27	2013
5	2013-07-06	11132280418	356057861	320923224	36751272	36709190	31533483	5175707	1616635	300533	...	0	42082	0.0	21800.0	0.0	278733.0	5	2013-07	2013_27	2013
6	2013-07-07	11393980162	361037358	356057861	8962232	8920335	5257768	3662567	3982735	336880	...	0	41897	0.0	0.0	0.0	336880.0	6	2013-07	2013_27	2013
7	2013-07-08	11675810640	409947895	361037358	57258266	57218409	47490938	9727471	8347729	852530	...	0	39857	219851.0	3500.0	0.0	629179.0	0	2013-07	2013_28	2013
8	2013-07-09	12360255326	433273777	409947895	26798941	26755943	21541164	5214779	3473059	765509	...	0	42998	60000.0	0.0	0.0	705509.0	1	2013-07	2013_28	2013
9	2013-07-10	12460909490	461373114	433273777	30696506	30644691	20475437	10169254	2597169	603737	...	0	51815	8000.0	267500.0	0.0	328237.0	2	2013-07	2013_28	2013
10	2013-07-11	13246007838	501939511	461373114	44075197	44021031	38701392	5319639	3508800	981695	...	0	54166	325455.0	2194.0	0.0	654046.0	3	2013-07	2013_28	2013
11	2013-07-12	13628492024	527630842	501939511	34183904	34126845	28358933	5767912	8492573	1371072	...	0	57059	483604.0	173268.0	0.0	714200.0	4	2013-07	2013_28	2013
12	2013-07-13	13970714822	539312730	527630842	15164717	15103661	8461168	6642493	3482829	309654	...	0	61056	0.0	45584.0	0.0	264070.0	5	2013-07	2013_28	2013
13	2013-07-14	14031107658	559143926	539312730	22615303	22554354	18447507	4106847	2784107	592038	...	0	60949	0.0	182560.0	0.0	409478.0	6	2013-07	2013_28	2013
14	2013-07-15	14373330510	594164538	559143926	48128555	48069686	28401224	19668462	13107943	1521794	...	0	58869	679000.0	222000.0	0.0	620794.0	0	2013-07	2013_29	2013
15	2013-07-16	14876599124	632922404	594164538	50622847	50559618	39657799	10901819	11864981	1529687	...	0	63229	744302.0	60000.0	0.0	725385.0	1	2013-07	2013_29	2013
16	2013-07-17	15399998505	651026573	632922404	29015682	28946382	25167271	3779111	10911513	2109498	...	0	69300	1348120.0	50000.0	0.0	711378.0	2	2013-07	2013_29	2013
17	2013-07-18	15540914296	663495722	651026573	24234505	24160561	12633212	11527349	11765356	3195410	...	0	73944	577438.0	2081710.0	0.0	536262.0	3	2013-07	2013_29	2013
18	2013-07-19	15863006572	687931077	663495722	33680124	33603190	25296006	8307184	9244769	1204252	...	0	76934	713031.0	140760.0	0.0	350461.0	4	2013-07	2013_29	2013
19	2013-07-20	16305883200	703769013	687931077	20439079	20360981	14965131	5395850	4601143	1780862	...	0	78098	229770.0	376900.0	0.0	1174192.0	5	2013-07	2013_29	2013
20	2013-07-21	16446799057	722230076	703769013	21142394	21064648	14155152	6909496	2681331	761299	...	0	77746	100000.0	26681.0	0.0	634618.0	6	2013-07	2013_29	2013
21	2013-07-22	16909806480	743534705	722230076	40448896	40374049	33166661	7207388	19144267	605231	...	0	74847	20220.0	64584.0	0.0	520427.0	0	2013-07	2013_30	2013
22	2013-07-23	17493598287	777266801	743534705	58136147	58056583	48105087	9951496	24404051	2173231	...	0	79564	483047.0	2000.0	0.0	1688184.0	1	2013-07	2013_30	2013
23	2013-07-24	17815690740	789430727	777266801	48422518	48337561	38512434	9825127	36258592	712047	...	0	84957	320000.0	12669.0	0.0	379378.0	2	2013-07	2013_30	2013
24	2013-07-25	18238436850	808651309	789430727	57433418	57347209	42064761	15282448	38212836	1557611	...	0	86209	526800.0	183410.0	0.0	847401.0	3	2013-07	2013_30	2013
25	2013-07-26	18620921550	814180757	808651309	44721817	44633279	33299638	11333641	39192369	1035450	...	0	88538	469737.0	48517.0	0.0	517196.0	4	2013-07	2013_30	2013
26	2013-07-27	18480007386	816316315	814180757	17194451	17102400	10482577	6619823	15058893	1922114	...	0	92051	1005922.0	58834.0	0.0	857358.0	5	2013-07	2013_30	2013
27	2013-07-28	18842361408	844888486	816316315	36255382	36164421	32964064	3200357	7683211	1161872	...	0	90961	551807.0	27184.0	0.0	582881.0	6	2013-07	2013_30	2013
28	2013-07-29	19164454008	879801198	844888486	53512076	53423360	41820041	11603319	18599364	1202332	...	0	88716	559277.0	121890.0	0.0	521165.0	0	2013-07	2013_31	2013
29	2013-07-30	19526808100	914233859	879801198	47481243	47387817	33779762	13608055	13048582	1318647	...	0	93426	510220.0	88782.0	0.0	719645.0	1	2013-07	2013_31	2013
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
397	2014-08-02	243764126606	20086601920	20069760015	189092130	186889349	78478704	108410645	172250225	41930977	...	95960150	2202781	20388807.0	7149562.0	0.0	14392608.0	5	2014-08	2014_31	2014
398	2014-08-03	244428785208	20133302100	20086601920	173825397	171634865	62120575	109514290	127125217	40828571	...	62502420	2190532	17238818.0	9473820.0	0.0	14115933.0	6	2014-08	2014_31	2014
399	2014-08-04	244851754228	20141035460	20133302100	330640884	328404472	120904646	207499826	322907524	78477516	...	200460527	2236412	31529783.0	12074693.0	16500000.0	18373040.0	0	2014-08	2014_32	2014
400	2014-08-05	245194160070	20314109791	20141035460	394780870	392589715	164705556	227884159	221706539	74040082	...	111691666	2191155	26591098.0	14768284.0	16410000.0	16270700.0	1	2014-08	2014_32	2014
401	2014-08-06	245496284334	20320584213	20314109791	288821016	286598298	127641624	158956674	282346594	56127546	...	199403632	2222718	21291913.0	19643544.0	1700000.0	13492089.0	2	2014-08	2014_32	2014
402	2014-08-07	245798408628	20314571173	20320584213	247646474	245405658	61837508	183568150	253659514	60050774	...	151933651	2240816	15410546.0	28941346.0	4300000.0	11398882.0	3	2014-08	2014_32	2014
403	2014-08-08	245617153560	20236826133	20314571173	233903717	231616935	54035416	177581519	311648757	67603275	...	200974417	2286782	19163226.0	18669835.0	14800000.0	14970214.0	4	2014-08	2014_32	2014
404	2014-08-09	246221390025	20233477189	20236826133	160262764	158028887	44663092	113365795	163611708	50648384	...	82935238	2233877	18520392.0	22698987.0	0.0	9429005.0	5	2014-08	2014_32	2014
405	2014-08-10	246261683870	20303102834	20233477189	259534870	257313208	76427498	180885710	189909225	55462188	...	109847134	2221662	23077228.0	20717590.0	1000.0	11666370.0	6	2014-08	2014_32	2014
406	2014-08-11	247047187726	20216049969	20303102834	331550471	329331370	102021228	227310142	418603336	60850688	...	316799953	2219101	17201769.0	23642952.0	2600000.0	17405967.0	0	2014-08	2014_33	2014
407	2014-08-12	247872973284	20164788784	20216049969	258493673	256311879	101592768	154719111	309754858	48112423	...	215083227	2181794	15814510.0	14362076.0	1811000.0	16124837.0	1	2014-08	2014_33	2014
408	2014-08-13	248013971282	20122319886	20164788784	261506619	259291103	99082207	160208896	303975517	55277175	...	225763451	2215516	25161278.0	14424643.0	3000000.0	12691254.0	2	2014-08	2014_33	2014
409	2014-08-14	247752153014	20168083115	20122319886	257702660	255446130	113303891	142142239	211939431	38957777	...	127579344	2256530	16476951.0	10331366.0	0.0	12149460.0	3	2014-08	2014_33	2014
410	2014-08-15	248054277540	20176118728	20168083115	244551620	242336052	83330178	159005874	236516007	47779700	...	150651042	2215568	10680763.0	18761561.0	0.0	18337376.0	4	2014-08	2014_33	2014
411	2014-08-16	248658514336	20171964125	20176118728	215059736	212842047	55705873	157136174	219214339	41164497	...	139686821	2217689	17021673.0	12925634.0	5000.0	11212190.0	5	2014-08	2014_33	2014
412	2014-08-17	249141906290	20182378312	20171964125	149978271	147777403	42442478	105334925	139564084	40085759	...	71330556	2200868	7738809.0	19041259.0	0.0	13305691.0	6	2014-08	2014_33	2014
413	2014-08-18	249766284018	20221708442	20182378312	298499146	296308680	84557465	211751215	259169016	55584679	...	150986027	2190466	14850247.0	15566826.0	0.0	25167606.0	0	2014-08	2014_34	2014
414	2014-08-19	249967704609	20233180538	20221708442	266401973	264196458	108645683	155550775	254929877	74648794	...	147252223	2205515	29600198.0	11467928.0	2040000.0	31540668.0	1	2014-08	2014_34	2014
415	2014-08-20	250471237520	20339106448	20233180538	308378692	306133606	134420983	171712623	202452782	73775504	...	100226182	2245086	24165679.0	17235710.0	1650000.0	30724115.0	2	2014-08	2014_34	2014
416	2014-08-21	250471249956	20370906609	20339106448	251763517	249525712	85818188	163707524	219963356	70493107	...	131262433	2237805	28669192.0	10979534.0	420000.0	30424381.0	3	2014-08	2014_34	2014
417	2014-08-22	250672670612	20437873459	20370906609	246316056	244094164	97266695	146827469	179349206	74322748	...	91406173	2221892	31216803.0	10023388.0	210000.0	32872557.0	4	2014-08	2014_34	2014
418	2014-08-23	250934513757	20379907955	20437873459	141412027	139192764	42797254	96395510	199377531	63911142	...	107065000	2219263	22294743.0	13156888.0	100000.0	28359511.0	5	2014-08	2014_34	2014
419	2014-08-24	251176216104	20319023288	20379907955	130195484	127991522	43143686	84847836	191080151	52046987	...	123525938	2203962	19194293.0	12132444.0	900000.0	19820250.0	6	2014-08	2014_34	2014
420	2014-08-25	251921439100	20316184100	20319023288	309574223	307361141	92282025	215079116	312413411	96656751	...	188597044	2213082	35518047.0	11637924.0	1500000.0	48000780.0	0	2014-08	2014_35	2014
421	2014-08-26	252102719042	20337650626	20316184100	306945089	304747497	124460527	180286970	285478563	88192408	...	182471401	2197592	24879168.0	16497042.0	0.0	46816198.0	1	2014-08	2014_35	2014
422	2014-08-27	252304139829	20171681280	20337650626	302194801	299973007	102372488	197600519	468164147	107408171	...	339789039	2221794	34053795.0	19426628.0	6000000.0	47927748.0	2	2014-08	2014_35	2014
423	2014-08-28	252908377196	20118870170	20171681280	245082751	242887647	56734596	186153051	297893861	98896299	...	183265388	2195104	25606866.0	25743565.0	4000000.0	43545868.0	3	2014-08	2014_35	2014
424	2014-08-29	253693882084	20112668503	20118870170	267554713	265335172	87236719	178098453	273756380	103967802	...	141809256	2219541	31191914.0	30028785.0	40000.0	42707103.0	4	2014-08	2014_35	2014
425	2014-08-30	253834880490	20116003141	20112668503	199708772	197483663	84798329	112685334	196374134	70813897	...	93107052	2225109	18388231.0	14302883.0	100000.0	38022783.0	5	2014-08	2014_35	2014
426	2014-08-31	254056442234	20098150321	20116003141	275090213	272878511	148125324	124753187	292943033	106464912	...	161974171	2211702	23745525.0	31675561.0	0.0	51043826.0	6	2014-08	2014_35	2014

427 rows × 22 columns

plt.figure(figsize=(20,15))
plt.subplot(2,1,1)
plt.plot(user_b.groupby(by='date').count()['report_date'].index,
        user_b.groupby(by='date').count()['report_date'].values)
plt.title('人数')
plt.subplot(2,1,2)
plt.plot(user_b.groupby(by='date').mean()['total_purchase_amt'].index,
        user_b.groupby(by='date').mean()['total_purchase_amt'].values)
plt.plot(user_b.groupby(by='date').mean()['total_redeem_amt'].index,
        user_b.groupby(by='date').mean()['total_redeem_amt'].values)
plt.title('平均交易量')

Text(0.5, 1.0, '平均交易量')

结合收益率

total_b['num'] = user_b.set_index('date').groupby(by='date').count()['report_date'].values

interest.set_index(pd.to_datetime(interest['mfd_date'],format='%Y%m%d').dt.date,inplace=True)

total_b = total_b.join(interest)

shibor.set_index(pd.to_datetime(shibor['mfd_date'],format='%Y%m%d').dt.date,inplace=True)
total_b =  total_b.join(shibor.drop(columns='mfd_date'))

total_b.shape

(427, 29)

tb_in = total_b[['total_purchase_amt','direct_purchase_amt','mfd_daily_yield','mfd_7daily_yield']]

tb_in['mfd_daily_yield_-1'] = tb_in['mfd_daily_yield'].shift(-1)

tb_in.corr()

	total_purchase_amt	direct_purchase_amt	mfd_daily_yield	mfd_7daily_yield	mfd_daily_yield_-1
total_purchase_amt	1.000000	0.999986	0.435140	0.426814	0.422715
direct_purchase_amt	0.999986	1.000000	0.436200	0.427733	0.423756
mfd_daily_yield	0.435140	0.436200	1.000000	0.976506	0.972618
mfd_7daily_yield	0.426814	0.427733	0.976506	1.000000	0.965990
mfd_daily_yield_-1	0.422715	0.423756	0.972618	0.965990	1.000000