利用python做RFM模型

最新推荐文章于 2022-10-12 11:49:27 发布

weixin_46147435

最新推荐文章于 2022-10-12 11:49:27 发布

阅读量398

点赞数

文章标签： python 数据分析数学建模

本文链接：https://blog.csdn.net/weixin_46147435/article/details/112919571

版权

源数据是2020年整年的交易情况，由12张表汇总而成。现对用户进行分层，建立RFM模型

一、加载所需的库和源数据

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime
%matplotlib inline
plt.style.use("ggplot")

df1 = pd.read_csv("C:\\rfm\\202001.csv")

df1.head()

	businessId	最后交易时间	交易金额	交易笔数
0	3	2020-01-31 14:31:51	212320.0	16
1	12	2020-01-23 11:32:46	121200.0	4
2	17	2020-01-02 11:43:28	1476.0	2
3	18	2020-01-04 17:14:24	44289.0	3
4	23	2020-01-29 19:42:58	58177.0	11

df1.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 984243 entries, 0 to 984242
Data columns (total 4 columns):
businessId    984243 non-null int64
最后交易时间        984243 non-null object
交易金额          984243 non-null float64
交易笔数          984243 non-null int64
dtypes: float64(1), int64(2), object(1)
memory usage: 30.0+ MB

df2 = pd.read_csv("C:\\rfm\\202002.csv")

df2.head()

	businessId	最后交易时间	交易金额	交易笔数
0	3	2020-02-27 13:59:31	206940.0	15
1	12	2020-02-14 12:51:47	73000.0	2
2	23	2020-02-29 16:29:45	88732.0	14
3	25	2020-02-29 16:23:06	42578.0	4
4	32	2020-02-29 20:57:17	48588.0	8

df2.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 889712 entries, 0 to 889711
Data columns (total 4 columns):
businessId    889712 non-null int64
最后交易时间        889712 non-null object
交易金额          889712 non-null float64
交易笔数          889712 non-null int64
dtypes: float64(1), int64(2), object(1)
memory usage: 27.2+ MB

df3 = pd.read_csv("C:\\rfm\\202003.csv")

df4 = pd.read_csv("C:\\rfm\\202004.csv")

df5 = pd.read_csv("C:\\rfm\\202005.csv")

df6 = pd.read_csv("C:\\rfm\\202006.csv")

df7 = pd.read_csv("C:\\rfm\\202007.csv")

df8 = pd.read_csv("C:\\rfm\\202008.csv")

df9 = pd.read_csv("C:\\rfm\\202009.csv")

df10 = pd.read_csv("C:\\rfm\\202010.csv")

df11 = pd.read_csv("C:\\rfm\\202011.csv")

df12 = pd.read_csv("C:\\rfm\\202012.csv")

二、表合并，这里采用外连接的方式，对两张表为空的数据均保留

df1_2= pd.merge(left = df1,right = df2,on = "businessId",how = "outer")

df3_4 = pd.merge(left = df3,right = df4,on = "businessId",how = "outer")

df5_6 = pd.merge(left = df5,right = df6,on = "businessId",how = "outer")

df7_8 = pd.merge(left = df7,right = df8,on = "businessId",how = "outer")

df9_10 = pd.merge(left = df9,right = df10,on = "businessId",how = "outer")

df11_12 = pd.merge(left = df11,right = df12,on = "businessId",how = "outer")

df12_34 = pd.merge(left = df1_2,right = df3_4,on = "businessId",how = "outer")

df12_34.head()

	businessId	最后交易时间_x_x	交易金额_x_x	交易笔数_x_x	最后交易时间_y_x	交易金额_y_x	交易笔数_y_x	最后交易时间_x_y	交易金额_x_y	交易笔数_x_y	最后交易时间_y_y	交易金额_y_y	交易笔数_y_y
0	3	2020-01-31 14:31:51	212320.0	16.0	2020-02-27 13:59:31	206940.0	15.0	2020-03-29 13:16:33	165290.0	15.0	2020-04-30 08:47:12	134040.0	12.0
1	12	2020-01-23 11:32:46	121200.0	4.0	2020-02-14 12:51:47	73000.0	2.0	2020-03-26 17:23:27	122800.0	5.0	2020-04-26 22:17:48	6000.0	1.0
2	17	2020-01-02 11:43:28	1476.0	2.0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	18	2020-01-04 17:14:24	44289.0	3.0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
4	23	2020-01-29 19:42:58	58177.0	11.0	2020-02-29 16:29:45	88732.0	14.0	2020-03-31 14:33:01	142977.0	19.0	2020-04-30 18:59:36	110822.0	16.0

df56_78 = pd.merge(left = df5_6,right = df7_8,on = "businessId",how = "outer")

df910_1112 = pd.merge(left = df9_10,right = df11_12,on = "businessId",how = "outer")

df1234_5678 = pd.merge(left = df12_34,right = df56_78,on = "businessId",how = "outer")

df1234_5678.head()

	businessId	最后交易时间_x_x_x	交易金额_x_x_x	交易笔数_x_x_x	最后交易时间_y_x_x	交易金额_y_x_x	交易笔数_y_x_x	最后交易时间_x_y_x	交易金额_x_y_x	交易笔数_x_y_x	...	交易笔数_x_x_y	最后交易时间_y_x_y	交易金额_y_x_y	交易笔数_y_x_y	最后交易时间_x_y_y	交易金额_x_y_y	交易笔数_x_y_y	最后交易时间_y_y_y	交易金额_y_y_y	交易笔数_y_y_y
0	3	2020-01-31 14:31:51	212320.0	16.0	2020-02-27 13:59:31	206940.0	15.0	2020-03-29 13:16:33	165290.0	15.0	...	14.0	2020-06-30 12:11:46	101160.0	9.0	2020-07-27 14:12:07	56920.0	6.0	NaN	NaN	NaN
1	12	2020-01-23 11:32:46	121200.0	4.0	2020-02-14 12:51:47	73000.0	2.0	2020-03-26 17:23:27	122800.0	5.0	...	3.0	2020-06-24 10:45:51	50000.0	2.0	2020-07-28 10:45:23	98000.0	3.0	2020-08-25 14:27:52	95000.0	3.0
2	17	2020-01-02 11:43:28	1476.0	2.0	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	18	2020-01-04 17:14:24	44289.0	3.0	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	2020-06-05 17:23:49	25000.0	1.0	NaN	NaN	NaN	2020-08-29 19:39:01	69658.0	2.0
4	23	2020-01-29 19:42:58	58177.0	11.0	2020-02-29 16:29:45	88732.0	14.0	2020-03-31 14:33:01	142977.0	19.0	...	16.0	2020-06-28 17:24:47	84131.0	14.0	2020-07-30 14:29:10	152755.0	25.0	2020-08-31 17:30:30	157242.0	27.0

5 rows × 25 columns

df= pd.merge(left = df1234_5678,right = df910_1112,on = "businessId",how = "outer")

df.head()

	businessId	最后交易时间_x_x_x	交易金额_x_x_x	交易笔数_x_x_x	最后交易时间_y_x_x	交易金额_y_x_x	交易笔数_y_x_x	最后交易时间_x_y_x	交易金额_x_y_x	交易笔数_x_y_x	...	交易笔数_x_x	最后交易时间_y_x	交易金额_y_x	交易笔数_y_x	最后交易时间_x_y	交易金额_x_y	交易笔数_x_y	最后交易时间_y_y	交易金额_y_y	交易笔数_y_y
0	3	2020-01-31 14:31:51	212320.0	16.0	2020-02-27 13:59:31	206940.0	15.0	2020-03-29 13:16:33	165290.0	15.0	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1	12	2020-01-23 11:32:46	121200.0	4.0	2020-02-14 12:51:47	73000.0	2.0	2020-03-26 17:23:27	122800.0	5.0	...	1.0	2020-10-26 21:08:04	159000.0	4.0	2020-11-02 10:16:56	48000.0	1.0	2020-12-18 12:32:36	162000.0	4.0
2	17	2020-01-02 11:43:28	1476.0	2.0	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	18	2020-01-04 17:14:24	44289.0	3.0	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	2020-11-17 14:37:58	79997.0	4.0	NaN	NaN	NaN
4	23	2020-01-29 19:42:58	58177.0	11.0	2020-02-29 16:29:45	88732.0	14.0	2020-03-31 14:33:01	142977.0	19.0	...	18.0	2020-10-31 12:47:22	32988.0	7.0	2020-11-30 15:20:17	12887.0	4.0	2020-12-27 17:27:23	14571.0	3.0

5 rows × 37 columns

三、到这里已经把12张表均汇总到一张表中，会发现里面有很多空值，现把空值替换为0

df=df.fillna(0)

df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 1729033 entries, 0 to 1729032
Data columns (total 37 columns):
businessId      int64
最后交易时间_x_x_x    object
交易金额_x_x_x      float64
交易笔数_x_x_x      float64
最后交易时间_y_x_x    object
交易金额_y_x_x      float64
交易笔数_y_x_x      float64
最后交易时间_x_y_x    object
交易金额_x_y_x      float64
交易笔数_x_y_x      float64
最后交易时间_y_y_x    object
交易金额_y_y_x      float64
交易笔数_y_y_x      float64
最后交易时间_x_x_y    object
交易金额_x_x_y      float64
交易笔数_x_x_y      float64
最后交易时间_y_x_y    object
交易金额_y_x_y      float64
交易笔数_y_x_y      float64
最后交易时间_x_y_y    object
交易金额_x_y_y      float64
交易笔数_x_y_y      float64
最后交易时间_y_y_y    object
交易金额_y_y_y      float64
交易笔数_y_y_y      float64
最后交易时间_x_x      object
交易金额_x_x        float64
交易笔数_x_x        float64
最后交易时间_y_x      object
交易金额_y_x        float64
交易笔数_y_x        float64
最后交易时间_x_y      object
交易金额_x_y        float64
交易笔数_x_y        float64
最后交易时间_y_y      object
交易金额_y_y        float64
交易笔数_y_y        float64
dtypes: float64(24), int64(1), object(12)
memory usage: 501.3+ MB

四、可以看出时间类型为字符串格式，这里需要更改为日期格式，金额和笔数为浮点格式，可改可不改

df['交易金额_x_x_x'].astype("int")
df['交易金额_y_x_x'].astype("int")
df['交易金额_x_y_x'].astype("int")
df['交易金额_y_y_x'].astype("int")
df['交易金额_x_x_y'].astype("int")
df['交易金额_y_x_y'].astype("int")
df['交易金额_x_y_y'].astype("int")
df['交易金额_y_y_y'].astype("int")
df['交易金额_x_x'].astype("int")
df['交易金额_y_x'].astype("int")
df['交易金额_x_y'].astype("int")
df['交易金额_y_y'].astype("int")

五、接下来就是把12列交易金额和交易笔数相加得到一个总和

df["总交易金额"] = df.apply(lambda x:x["交易金额_x_x_x"] + x["交易金额_y_x_x"] +x["交易金额_x_y_x"]+x["交易金额_y_y_x"]+x["交易金额_x_x_y"]+
                       x["交易金额_y_x_y"] +x["交易金额_x_y_y"] +x["交易金额_y_y_y"]+x["交易金额_x_x"]+x["交易金额_y_x"]+x["交易金额_x_y"]
                       +x["交易金额_y_y"],axis=1)

df.head()

	businessId	最后交易时间_x_x_x	交易金额_x_x_x	交易笔数_x_x_x	最后交易时间_y_x_x	交易金额_y_x_x	交易笔数_y_x_x	最后交易时间_x_y_x	交易金额_x_y_x	交易笔数_x_y_x	...	最后交易时间_y_x	交易金额_y_x	交易笔数_y_x	最后交易时间_x_y	交易金额_x_y	交易笔数_x_y	最后交易时间_y_y	交易金额_y_y	交易笔数_y_y	总交易金额
0	3	2020-01-31 14:31:51	212320.0	16.0	2020-02-27 13:59:31	206940.0	15.0	2020-03-29 13:16:33	165290.0	15.0	...	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	996010.0
1	12	2020-01-23 11:32:46	121200.0	4.0	2020-02-14 12:51:47	73000.0	2.0	2020-03-26 17:23:27	122800.0	5.0	...	2020-10-26 21:08:04	159000.0	4.0	2020-11-02 10:16:56	48000.0	1.0	2020-12-18 12:32:36	162000.0	4.0	1084000.0
2	17	2020-01-02 11:43:28	1476.0	2.0	0	0.0	0.0	0	0.0	0.0	...	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	1476.0
3	18	2020-01-04 17:14:24	44289.0	3.0	0	0.0	0.0	0	0.0	0.0	...	0	0.0	0.0	2020-11-17 14:37:58	79997.0	4.0	0	0.0	0.0	218944.0
4	23	2020-01-29 19:42:58	58177.0	11.0	2020-02-29 16:29:45	88732.0	14.0	2020-03-31 14:33:01	142977.0	19.0	...	2020-10-31 12:47:22	32988.0	7.0	2020-11-30 15:20:17	12887.0	4.0	2020-12-27 17:27:23	14571.0	3.0	1029513.0

5 rows × 38 columns

df["总交易笔数"] = df.apply(lambda x:x["交易笔数_x_x_x"] + x["交易笔数_y_x_x"] +x["交易笔数_x_y_x"]+x["交易笔数_y_y_x"]+x["交易笔数_x_x_y"]+
                       x["交易笔数_y_x_y"] +x["交易笔数_x_y_y"] +x["交易笔数_y_y_y"]+x["交易笔数_x_x"]+x["交易笔数_y_x"]+x["交易笔数_x_y"]
                       +x["交易笔数_y_y"],axis=1)

df

	businessId	最后交易时间_x_x_x	交易金额_x_x_x	交易笔数_x_x_x	最后交易时间_y_x_x	交易金额_y_x_x	交易笔数_y_x_x	最后交易时间_x_y_x	交易金额_x_y_x	交易笔数_x_y_x	...	交易金额_y_x	交易笔数_y_x	最后交易时间_x_y	交易金额_x_y	交易笔数_x_y	最后交易时间_y_y	交易金额_y_y	交易笔数_y_y	总交易金额	总交易笔数
0	3	2020-01-31 14:31:51	212320.0	16.0	2020-02-27 13:59:31	206940.0	15.0	2020-03-29 13:16:33	165290.0	15.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	996010.00	87.0
1	12	2020-01-23 11:32:46	121200.0	4.0	2020-02-14 12:51:47	73000.0	2.0	2020-03-26 17:23:27	122800.0	5.0	...	159000.0	4.0	2020-11-02 10:16:56	48000.0	1.0	2020-12-18 12:32:36	162000.0	4.0	1084000.00	33.0
2	17	2020-01-02 11:43:28	1476.0	2.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	1476.00	2.0
3	18	2020-01-04 17:14:24	44289.0	3.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	2020-11-17 14:37:58	79997.0	4.0	0	0.0	0.0	218944.00	10.0
4	23	2020-01-29 19:42:58	58177.0	11.0	2020-02-29 16:29:45	88732.0	14.0	2020-03-31 14:33:01	142977.0	19.0	...	32988.0	7.0	2020-11-30 15:20:17	12887.0	4.0	2020-12-27 17:27:23	14571.0	3.0	1029513.00	174.0
5	25	2020-01-29 10:53:43	57245.3	5.0	2020-02-29 16:23:06	42578.0	4.0	2020-03-17 16:57:56	43058.0	3.0	...	50674.5	4.0	2020-11-28 08:55:22	39692.8	3.0	2020-12-28 18:01:53	55016.0	5.0	602419.60	52.0
6	32	2020-01-30 14:44:19	57511.0	13.0	2020-02-29 20:57:17	48588.0	8.0	2020-03-31 21:10:33	76483.0	21.0	...	107941.0	22.0	2020-11-30 14:59:37	36785.0	8.0	2020-12-30 11:02:06	74436.0	19.0	733722.00	166.0
7	46	2020-01-09 15:12:07	19912.0	4.0	0	0.0	0.0	0	0.0	0.0	...	74400.0	6.0	2020-11-17 12:38:38	71588.0	3.0	2020-12-15 16:17:46	89500.0	4.0	562050.00	32.0
8	48	2020-01-23 09:35:40	14277.0	2.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	14277.00	2.0
9	51	2020-01-19 10:57:34	11154.0	3.0	2020-02-10 22:13:12	5500.0	1.0	2020-03-24 08:34:13	15900.0	4.0	...	14400.0	2.0	0	0.0	0.0	2020-12-10 16:29:01	18900.0	2.0	175804.00	21.0
10	52	2020-01-30 21:03:57	316530.0	187.0	2020-02-29 21:40:10	355020.0	249.0	2020-03-31 20:29:49	401275.0	236.0	...	108523.0	89.0	2020-11-30 21:13:34	135800.0	77.0	2020-12-29 19:29:53	62353.0	47.0	3130300.00	1842.0
11	55	2020-01-28 18:21:59	32795.8	44.0	2020-02-29 16:12:03	17712.0	142.0	2020-03-31 20:06:28	26290.2	96.0	...	2307.0	17.0	2020-11-09 21:00:55	1065.0	11.0	2020-12-23 19:44:17	6561.0	52.0	337440.66	1342.0
12	66	2020-01-30 16:46:43	17703.0	24.0	2020-02-28 16:35:13	32809.0	40.0	2020-03-30 17:46:29	39523.1	46.0	...	64121.6	51.0	2020-11-25 16:53:17	21514.6	25.0	2020-12-16 11:01:06	17796.0	20.0	296694.60	341.0
13	80	2020-01-10 19:03:20	14500.0	1.0	0	0.0	0.0	0	0.0	0.0	...	720.0	1.0	0	0.0	0.0	0	0.0	0.0	34255.00	11.0
14	83	2020-01-20 19:45:25	205631.0	14.0	2020-02-20 17:11:20	192956.0	6.0	2020-03-25 17:02:54	225414.0	13.0	...	55584.0	3.0	2020-11-24 11:53:01	23544.0	4.0	2020-12-28 16:27:39	97651.0	10.0	1309749.00	86.0
15	92	2020-01-18 10:30:01	3000.0	1.0	2020-02-28 17:03:01	2000.0	1.0	0	0.0	0.0	...	0.0	0.0	2020-11-26 16:08:33	10000.0	1.0	2020-12-08 15:14:21	5000.0	1.0	64380.00	14.0
16	95	2020-01-30 11:44:20	217473.0	21.0	2020-02-24 14:47:17	319291.0	21.0	2020-03-31 16:10:58	202474.0	16.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	1447496.00	113.0
17	97	2020-01-21 12:26:34	10251.0	14.0	2020-02-23 15:25:05	40145.0	38.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	50396.00	52.0
18	98	2020-01-23 23:51:17	4950.0	1.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	4950.00	1.0
19	105	2020-01-04 13:58:55	22550.0	1.0	2020-02-29 14:36:16	60890.0	3.0	2020-03-29 14:47:53	109800.0	4.0	...	178750.0	6.0	2020-11-21 10:04:30	116720.0	4.0	2020-12-25 09:33:23	142166.0	5.0	939337.00	37.0
20	108	2020-01-31 11:50:15	198417.8	46.0	2020-02-27 16:02:07	192150.0	21.0	2020-03-25 17:36:33	190504.0	40.0	...	267410.6	99.0	2020-11-26 18:51:56	211821.5	88.0	0	0.0	0.0	2405387.30	541.0
21	110	2020-01-28 17:32:30	99970.0	61.0	2020-02-28 13:53:46	43510.0	29.0	2020-03-31 17:16:22	56860.0	43.0	...	53390.0	39.0	2020-11-30 14:22:36	66360.0	46.0	2020-12-31 16:36:57	73505.0	48.0	799060.00	542.0
22	116	2020-01-31 17:43:42	43586.0	14.0	2020-02-23 17:17:46	74036.0	24.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	296129.00	88.0
23	129	2020-01-30 21:26:06	82450.0	33.0	2020-02-05 15:16:47	10960.0	2.0	2020-03-11 17:58:35	5480.0	3.0	...	71820.0	32.0	2020-11-30 21:04:39	50150.0	23.0	2020-12-18 09:23:39	40810.0	18.0	587560.00	258.0
24	131	2020-01-30 13:06:23	60000.0	3.0	2020-02-26 12:18:47	81500.0	6.0	2020-03-27 21:15:31	25000.0	4.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	459500.00	29.0
25	137	2020-01-20 10:02:21	3268.0	8.0	2020-02-27 12:35:38	6723.0	13.0	2020-03-18 13:20:07	3329.0	7.0	...	12411.0	9.0	2020-11-25 20:58:23	6288.0	6.0	2020-12-29 14:46:19	11051.0	10.0	60642.00	71.0
26	149	2020-01-24 09:56:02	73451.0	7.0	2020-02-28 14:52:52	58582.0	8.0	2020-03-29 08:16:01	66294.0	10.0	...	35454.0	3.0	2020-11-30 12:32:53	95372.0	10.0	2020-12-28 21:28:15	42987.0	4.0	751987.00	83.0
27	164	2020-01-26 11:24:23	37390.0	7.0	2020-02-28 11:30:20	64441.0	12.0	2020-03-14 15:30:40	7520.0	3.0	...	71505.0	7.0	0	0.0	0.0	0	0.0	0.0	286030.00	41.0
28	169	2020-01-29 13:17:07	259083.0	10.0	2020-02-13 15:15:04	35122.0	1.0	2020-03-19 09:41:58	259228.0	6.0	...	258987.0	8.0	2020-11-06 21:13:32	84310.0	3.0	2020-12-19 11:53:49	180653.0	7.0	1702356.00	57.0
29	171	2020-01-10 16:24:23	10160.0	1.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	2020-11-03 10:01:31	16818.0	2.0	0	0.0	0.0	26978.00	3.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1729003	3399569	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:06:07	288.0	1.0	288.00	1.0
1729004	3399582	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:12:39	760.0	1.0	760.00	1.0
1729005	3399587	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:15:23	560.0	1.0	560.00	1.0
1729006	3399588	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:16:24	10.0	1.0	10.00	1.0
1729007	3399589	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:17:30	220.0	1.0	220.00	1.0
1729008	3399592	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:18:34	10.0	1.0	10.00	1.0
1729009	3399596	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:21:22	5000.0	1.0	5000.00	1.0
1729010	3399599	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:30:15	2269.0	3.0	2269.00	3.0
1729011	3399601	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:22:50	221.0	1.0	221.00	1.0
1729012	3399602	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:22:56	500.0	1.0	500.00	1.0
1729013	3399604	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:23:59	230.0	1.0	230.00	1.0
1729014	3399610	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:38:19	5320.0	2.0	5320.00	2.0
1729015	3399615	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:29:33	2581.0	1.0	2581.00	1.0
1729016	3399617	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:30:08	10.0	1.0	10.00	1.0
1729017	3399618	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:30:43	7000.0	1.0	7000.00	1.0
1729018	3399619	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:30:53	854.0	1.0	854.00	1.0
1729019	3399622	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:32:20	888.0	1.0	888.00	1.0
1729020	3399629	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:33:42	3123.0	1.0	3123.00	1.0
1729021	3399630	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:34:25	956.0	1.0	956.00	1.0
1729022	3399633	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:34:51	220.0	1.0	220.00	1.0
1729023	3399634	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:35:01	5028.0	1.0	5028.00	1.0
1729024	3399638	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:39:23	35000.0	1.0	35000.00	1.0
1729025	3399639	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:40:09	10.0	1.0	10.00	1.0
1729026	3399642	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:58:45	220.0	1.0	220.00	1.0
1729027	3399643	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:06:34	220.0	1.0	220.00	1.0
1729028	3399644	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:10:40	220.0	1.0	220.00	1.0
1729029	3399645	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:15:51	652.0	1.0	652.00	1.0
1729030	3399646	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:27:21	220.0	1.0	220.00	1.0
1729031	3399647	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:28:51	220.0	1.0	220.00	1.0
1729032	3399648	0	0.0	0.0	0	0.0	0.0	0	0.0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:30:49	220.0	1.0	220.00	1.0

1729033 rows × 39 columns

截止到这里可以看到原表中多了两列总交易金额和总交易笔数，下面求最后一次交易时间，也就是最大的一个交易时间。这里先把12列交易时间拿出来，再把类型转换为日期格式，接着求最大值

df2 = df.loc[:,["最后交易时间_x_x_x" ,"最后交易时间_y_x_x" ,"最后交易时间_x_y_x","最后交易时间_y_y_x","最后交易时间_x_x_y",
                       "最后交易时间_y_x_y","最后交易时间_x_y_y","最后交易时间_y_y_y","最后交易时间_x_x","最后交易时间_y_x","最后交易时间_x_y"
                      ,"最后交易时间_y_y"]]

df2.head()

	最后交易时间_x_x_x	最后交易时间_y_x_x	最后交易时间_x_y_x	最后交易时间_y_y_x	最后交易时间_x_x_y	最后交易时间_y_x_y	最后交易时间_x_y_y	最后交易时间_y_y_y	最后交易时间_x_x	最后交易时间_y_x	最后交易时间_x_y	最后交易时间_y_y
0	2020-01-31 14:31:51	2020-02-27 13:59:31	2020-03-29 13:16:33	2020-04-30 08:47:12	2020-05-29 14:18:27	2020-06-30 12:11:46	2020-07-27 14:12:07	0	0	0	0	0
1	2020-01-23 11:32:46	2020-02-14 12:51:47	2020-03-26 17:23:27	2020-04-26 22:17:48	2020-05-18 09:12:24	2020-06-24 10:45:51	2020-07-28 10:45:23	2020-08-25 14:27:52	2020-09-20 15:32:14	2020-10-26 21:08:04	2020-11-02 10:16:56	2020-12-18 12:32:36
2	2020-01-02 11:43:28	0	0	0	0	0	0	0	0	0	0	0
3	2020-01-04 17:14:24	0	0	0	0	2020-06-05 17:23:49	0	2020-08-29 19:39:01	0	0	2020-11-17 14:37:58	0
4	2020-01-29 19:42:58	2020-02-29 16:29:45	2020-03-31 14:33:01	2020-04-30 18:59:36	2020-05-30 17:02:40	2020-06-28 17:24:47	2020-07-30 14:29:10	2020-08-31 17:30:30	2020-09-15 14:04:22	2020-10-31 12:47:22	2020-11-30 15:20:17	2020-12-27 17:27:23

df2['最后交易时间_x_x_x'] = pd.to_datetime(df2['最后交易时间_x_x_x'])

df2['最后交易时间_y_x_x'] = pd.to_datetime(df2['最后交易时间_y_x_x'])

df2['最后交易时间_x_y_x'] = pd.to_datetime(df2['最后交易时间_x_y_x'])

df2['最后交易时间_y_y_x'] = pd.to_datetime(df2['最后交易时间_y_y_x'])
df2['最后交易时间_x_x_y'] = pd.to_datetime(df2['最后交易时间_x_x_y'])
df2['最后交易时间_y_x_y'] = pd.to_datetime(df2['最后交易时间_y_x_y'])
df2['最后交易时间_x_y_y'] = pd.to_datetime(df2['最后交易时间_x_y_y'])
df2['最后交易时间_y_y_y'] = pd.to_datetime(df2['最后交易时间_y_y_y'])
df2['最后交易时间_x_x'] = pd.to_datetime(df2['最后交易时间_x_x'])
df2['最后交易时间_y_x'] = pd.to_datetime(df2['最后交易时间_y_x'])
df2['最后交易时间_x_y'] = pd.to_datetime(df2['最后交易时间_x_y'])
df2['最后交易时间_y_y'] = pd.to_datetime(df2['最后交易时间_y_y'])

df2.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 1729033 entries, 0 to 1729032
Data columns (total 12 columns):
最后交易时间_x_x_x    datetime64[ns]
最后交易时间_y_x_x    datetime64[ns]
最后交易时间_x_y_x    datetime64[ns]
最后交易时间_y_y_x    datetime64[ns]
最后交易时间_x_x_y    datetime64[ns]
最后交易时间_y_x_y    datetime64[ns]
最后交易时间_x_y_y    datetime64[ns]
最后交易时间_y_y_y    datetime64[ns]
最后交易时间_x_x      datetime64[ns]
最后交易时间_y_x      datetime64[ns]
最后交易时间_x_y      datetime64[ns]
最后交易时间_y_y      datetime64[ns]
dtypes: datetime64[ns](12)
memory usage: 251.5 MB

df2['time'] = df2[['最后交易时间_x_x_x','最后交易时间_y_x_x',"最后交易时间_x_y_x","最后交易时间_y_y_x","最后交易时间_x_x_y",
             "最后交易时间_y_x_y","最后交易时间_x_y_y","最后交易时间_y_y_y","最后交易时间_x_x","最后交易时间_y_x",
             "最后交易时间_x_y","最后交易时间_y_y"]].max(axis=1)

df2

	最后交易时间_x_x_x	最后交易时间_y_x_x	最后交易时间_x_y_x	最后交易时间_y_y_x	最后交易时间_x_x_y	最后交易时间_y_x_y	最后交易时间_x_y_y	最后交易时间_y_y_y	最后交易时间_x_x	最后交易时间_y_x	最后交易时间_x_y	最后交易时间_y_y	time
0	2020-01-31 14:31:51	2020-02-27 13:59:31	2020-03-29 13:16:33	2020-04-30 08:47:12	2020-05-29 14:18:27	2020-06-30 12:11:46	2020-07-27 14:12:07	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-07-27 14:12:07
1	2020-01-23 11:32:46	2020-02-14 12:51:47	2020-03-26 17:23:27	2020-04-26 22:17:48	2020-05-18 09:12:24	2020-06-24 10:45:51	2020-07-28 10:45:23	2020-08-25 14:27:52	2020-09-20 15:32:14	2020-10-26 21:08:04	2020-11-02 10:16:56	2020-12-18 12:32:36	2020-12-18 12:32:36
2	2020-01-02 11:43:28	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-01-02 11:43:28
3	2020-01-04 17:14:24	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-06-05 17:23:49	1970-01-01 00:00:00	2020-08-29 19:39:01	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-11-17 14:37:58	1970-01-01 00:00:00	2020-11-17 14:37:58
4	2020-01-29 19:42:58	2020-02-29 16:29:45	2020-03-31 14:33:01	2020-04-30 18:59:36	2020-05-30 17:02:40	2020-06-28 17:24:47	2020-07-30 14:29:10	2020-08-31 17:30:30	2020-09-15 14:04:22	2020-10-31 12:47:22	2020-11-30 15:20:17	2020-12-27 17:27:23	2020-12-27 17:27:23
5	2020-01-29 10:53:43	2020-02-29 16:23:06	2020-03-17 16:57:56	2020-04-27 16:25:57	2020-05-25 10:21:13	2020-06-27 11:03:30	2020-07-28 17:16:47	2020-08-11 07:35:29	2020-09-28 16:40:02	2020-10-31 14:59:34	2020-11-28 08:55:22	2020-12-28 18:01:53	2020-12-28 18:01:53
6	2020-01-30 14:44:19	2020-02-29 20:57:17	2020-03-31 21:10:33	2020-04-22 20:31:57	2020-05-27 22:09:51	2020-06-18 17:42:17	2020-07-28 09:32:37	2020-08-14 10:36:22	2020-09-29 20:51:18	2020-10-28 21:58:01	2020-11-30 14:59:37	2020-12-30 11:02:06	2020-12-30 11:02:06
7	2020-01-09 15:12:07	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-04-25 17:39:24	2020-05-19 15:25:26	2020-06-20 20:22:10	2020-07-18 16:22:22	2020-08-31 16:59:50	1970-01-01 00:00:00	2020-10-29 09:08:11	2020-11-17 12:38:38	2020-12-15 16:17:46	2020-12-15 16:17:46
8	2020-01-23 09:35:40	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-01-23 09:35:40
9	2020-01-19 10:57:34	2020-02-10 22:13:12	2020-03-24 08:34:13	2020-04-06 21:32:15	2020-05-09 11:24:50	2020-06-04 16:05:41	2020-07-06 20:34:53	2020-08-17 21:58:05	2020-09-24 18:26:57	2020-10-25 22:57:32	1970-01-01 00:00:00	2020-12-10 16:29:01	2020-12-10 16:29:01
10	2020-01-30 21:03:57	2020-02-29 21:40:10	2020-03-31 20:29:49	2020-04-30 20:01:00	2020-05-31 20:44:56	2020-06-30 20:19:46	2020-07-31 18:55:48	2020-08-31 21:42:45	2020-09-30 17:42:12	2020-10-29 22:02:23	2020-11-30 21:13:34	2020-12-29 19:29:53	2020-12-29 19:29:53
11	2020-01-28 18:21:59	2020-02-29 16:12:03	2020-03-31 20:06:28	2020-04-30 22:01:53	2020-05-31 18:41:43	2020-06-30 21:03:52	2020-07-29 19:46:21	2020-08-31 19:52:34	2020-09-30 20:26:46	2020-10-11 20:50:07	2020-11-09 21:00:55	2020-12-23 19:44:17	2020-12-23 19:44:17
12	2020-01-30 16:46:43	2020-02-28 16:35:13	2020-03-30 17:46:29	2020-04-20 13:28:00	2020-05-26 21:01:23	2020-06-29 12:10:58	2020-07-12 10:28:04	2020-08-25 13:01:13	2020-09-25 11:50:59	2020-10-30 12:07:28	2020-11-25 16:53:17	2020-12-16 11:01:06	2020-12-16 11:01:06
13	2020-01-10 19:03:20	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-06-16 19:18:07	1970-01-01 00:00:00	2020-08-28 11:24:59	2020-09-10 10:29:39	2020-10-07 14:29:30	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-10-07 14:29:30
14	2020-01-20 19:45:25	2020-02-20 17:11:20	2020-03-25 17:02:54	2020-04-28 14:44:13	2020-05-26 20:25:39	2020-06-14 09:40:06	2020-07-07 16:24:09	2020-08-15 10:54:46	1970-01-01 00:00:00	2020-10-25 18:47:11	2020-11-24 11:53:01	2020-12-28 16:27:39	2020-12-28 16:27:39
15	2020-01-18 10:30:01	2020-02-28 17:03:01	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-05-08 20:32:54	2020-06-09 09:18:44	2020-07-31 15:44:47	2020-08-06 08:46:20	2020-09-29 07:20:05	1970-01-01 00:00:00	2020-11-26 16:08:33	2020-12-08 15:14:21	2020-12-08 15:14:21
16	2020-01-30 11:44:20	2020-02-24 14:47:17	2020-03-31 16:10:58	2020-04-30 14:55:47	2020-05-31 16:33:59	2020-06-30 10:10:18	2020-07-06 18:25:35	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-07-06 18:25:35
17	2020-01-21 12:26:34	2020-02-23 15:25:05	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-02-23 15:25:05
18	2020-01-23 23:51:17	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-01-23 23:51:17
19	2020-01-04 13:58:55	2020-02-29 14:36:16	2020-03-29 14:47:53	2020-04-25 09:30:46	2020-05-30 12:29:41	2020-06-02 09:09:12	2020-07-21 14:17:09	2020-08-22 13:53:16	2020-09-16 09:11:22	2020-10-25 15:33:54	2020-11-21 10:04:30	2020-12-25 09:33:23	2020-12-25 09:33:23
20	2020-01-31 11:50:15	2020-02-27 16:02:07	2020-03-25 17:36:33	2020-04-28 20:28:11	2020-05-31 08:42:21	2020-06-29 11:03:56	2020-07-28 08:33:50	2020-08-30 12:53:57	2020-09-28 19:53:30	2020-10-29 12:45:50	2020-11-26 18:51:56	1970-01-01 00:00:00	2020-11-26 18:51:56
21	2020-01-28 17:32:30	2020-02-28 13:53:46	2020-03-31 17:16:22	2020-04-30 15:16:38	2020-05-31 17:12:35	2020-06-30 13:29:34	2020-07-31 16:11:41	2020-08-31 17:19:51	2020-09-30 14:50:28	2020-10-31 17:26:33	2020-11-30 14:22:36	2020-12-31 16:36:57	2020-12-31 16:36:57
22	2020-01-31 17:43:42	2020-02-23 17:17:46	1970-01-01 00:00:00	2020-04-30 17:37:01	2020-05-08 09:57:43	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-05-08 09:57:43
23	2020-01-30 21:26:06	2020-02-05 15:16:47	2020-03-11 17:58:35	2020-04-30 10:34:40	2020-05-31 10:59:53	2020-06-30 14:27:39	2020-07-31 14:25:46	2020-08-31 14:58:13	2020-09-25 10:52:34	2020-10-31 21:01:35	2020-11-30 21:04:39	2020-12-18 09:23:39	2020-12-18 09:23:39
24	2020-01-30 13:06:23	2020-02-26 12:18:47	2020-03-27 21:15:31	2020-04-27 17:04:44	2020-05-27 19:35:31	2020-06-24 11:44:32	2020-07-21 13:53:36	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-07-21 13:53:36
25	2020-01-20 10:02:21	2020-02-27 12:35:38	2020-03-18 13:20:07	2020-04-13 13:03:05	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-08-24 17:37:01	2020-09-28 11:38:06	2020-10-30 16:31:25	2020-11-25 20:58:23	2020-12-29 14:46:19	2020-12-29 14:46:19
26	2020-01-24 09:56:02	2020-02-28 14:52:52	2020-03-29 08:16:01	2020-04-19 11:45:28	2020-05-22 21:07:46	2020-06-18 16:27:11	2020-07-07 08:41:27	2020-08-24 22:12:21	2020-09-12 09:48:10	2020-10-17 19:11:53	2020-11-30 12:32:53	2020-12-28 21:28:15	2020-12-28 21:28:15
27	2020-01-26 11:24:23	2020-02-28 11:30:20	2020-03-14 15:30:40	2020-04-12 15:14:49	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-07-03 16:06:56	2020-08-31 09:28:22	2020-09-24 16:10:26	2020-10-25 10:23:57	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-10-25 10:23:57
28	2020-01-29 13:17:07	2020-02-13 15:15:04	2020-03-19 09:41:58	2020-04-26 10:02:58	2020-05-31 09:48:45	2020-06-21 10:23:34	2020-07-18 17:54:43	2020-08-31 11:16:42	2020-09-19 09:11:47	2020-10-26 16:24:24	2020-11-06 21:13:32	2020-12-19 11:53:49	2020-12-19 11:53:49
29	2020-01-10 16:24:23	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-11-03 10:01:31	1970-01-01 00:00:00	2020-11-03 10:01:31
...	...	...	...	...	...	...	...	...	...	...	...	...	...
1729003	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:06:07	2020-12-31 22:06:07
1729004	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:12:39	2020-12-31 22:12:39
1729005	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:15:23	2020-12-31 22:15:23
1729006	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:16:24	2020-12-31 22:16:24
1729007	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:17:30	2020-12-31 22:17:30
1729008	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:18:34	2020-12-31 22:18:34
1729009	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:21:22	2020-12-31 22:21:22
1729010	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:30:15	2020-12-31 22:30:15
1729011	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:22:50	2020-12-31 22:22:50
1729012	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:22:56	2020-12-31 22:22:56
1729013	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:23:59	2020-12-31 22:23:59
1729014	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:38:19	2020-12-31 22:38:19
1729015	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:29:33	2020-12-31 22:29:33
1729016	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:30:08	2020-12-31 22:30:08
1729017	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:30:43	2020-12-31 22:30:43
1729018	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:30:53	2020-12-31 22:30:53
1729019	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:32:20	2020-12-31 22:32:20
1729020	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:33:42	2020-12-31 22:33:42
1729021	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:34:25	2020-12-31 22:34:25
1729022	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:34:51	2020-12-31 22:34:51
1729023	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:35:01	2020-12-31 22:35:01
1729024	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:39:23	2020-12-31 22:39:23
1729025	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:40:09	2020-12-31 22:40:09
1729026	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:58:45	2020-12-31 22:58:45
1729027	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:06:34	2020-12-31 23:06:34
1729028	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:10:40	2020-12-31 23:10:40
1729029	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:15:51	2020-12-31 23:15:51
1729030	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:27:21	2020-12-31 23:27:21
1729031	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:28:51	2020-12-31 23:28:51
1729032	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:30:49	2020-12-31 23:30:49

1729033 rows × 13 columns

到这里会发现表中没有与原表中相匹配的列，这里把索引转换为列，利用索引跟原来的表相匹配得到结果

df2 = df2.reset_index()

df2.head()

	index	最后交易时间_x_x_x	最后交易时间_y_x_x	最后交易时间_x_y_x	最后交易时间_y_y_x	最后交易时间_x_x_y	最后交易时间_y_x_y	最后交易时间_x_y_y	最后交易时间_y_y_y	最后交易时间_x_x	最后交易时间_y_x	最后交易时间_x_y	最后交易时间_y_y	time
0	0	2020-01-31 14:31:51	2020-02-27 13:59:31	2020-03-29 13:16:33	2020-04-30 08:47:12	2020-05-29 14:18:27	2020-06-30 12:11:46	2020-07-27 14:12:07	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-07-27 14:12:07
1	1	2020-01-23 11:32:46	2020-02-14 12:51:47	2020-03-26 17:23:27	2020-04-26 22:17:48	2020-05-18 09:12:24	2020-06-24 10:45:51	2020-07-28 10:45:23	2020-08-25 14:27:52	2020-09-20 15:32:14	2020-10-26 21:08:04	2020-11-02 10:16:56	2020-12-18 12:32:36	2020-12-18 12:32:36
2	2	2020-01-02 11:43:28	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-01-02 11:43:28
3	3	2020-01-04 17:14:24	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-06-05 17:23:49	1970-01-01 00:00:00	2020-08-29 19:39:01	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-11-17 14:37:58	1970-01-01 00:00:00	2020-11-17 14:37:58
4	4	2020-01-29 19:42:58	2020-02-29 16:29:45	2020-03-31 14:33:01	2020-04-30 18:59:36	2020-05-30 17:02:40	2020-06-28 17:24:47	2020-07-30 14:29:10	2020-08-31 17:30:30	2020-09-15 14:04:22	2020-10-31 12:47:22	2020-11-30 15:20:17	2020-12-27 17:27:23	2020-12-27 17:27:23
5	5	2020-01-29 10:53:43	2020-02-29 16:23:06	2020-03-17 16:57:56	2020-04-27 16:25:57	2020-05-25 10:21:13	2020-06-27 11:03:30	2020-07-28 17:16:47	2020-08-11 07:35:29	2020-09-28 16:40:02	2020-10-31 14:59:34	2020-11-28 08:55:22	2020-12-28 18:01:53	2020-12-28 18:01:53
6	6	2020-01-30 14:44:19	2020-02-29 20:57:17	2020-03-31 21:10:33	2020-04-22 20:31:57	2020-05-27 22:09:51	2020-06-18 17:42:17	2020-07-28 09:32:37	2020-08-14 10:36:22	2020-09-29 20:51:18	2020-10-28 21:58:01	2020-11-30 14:59:37	2020-12-30 11:02:06	2020-12-30 11:02:06
7	7	2020-01-09 15:12:07	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-04-25 17:39:24	2020-05-19 15:25:26	2020-06-20 20:22:10	2020-07-18 16:22:22	2020-08-31 16:59:50	1970-01-01 00:00:00	2020-10-29 09:08:11	2020-11-17 12:38:38	2020-12-15 16:17:46	2020-12-15 16:17:46
8	8	2020-01-23 09:35:40	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-01-23 09:35:40
9	9	2020-01-19 10:57:34	2020-02-10 22:13:12	2020-03-24 08:34:13	2020-04-06 21:32:15	2020-05-09 11:24:50	2020-06-04 16:05:41	2020-07-06 20:34:53	2020-08-17 21:58:05	2020-09-24 18:26:57	2020-10-25 22:57:32	1970-01-01 00:00:00	2020-12-10 16:29:01	2020-12-10 16:29:01
10	10	2020-01-30 21:03:57	2020-02-29 21:40:10	2020-03-31 20:29:49	2020-04-30 20:01:00	2020-05-31 20:44:56	2020-06-30 20:19:46	2020-07-31 18:55:48	2020-08-31 21:42:45	2020-09-30 17:42:12	2020-10-29 22:02:23	2020-11-30 21:13:34	2020-12-29 19:29:53	2020-12-29 19:29:53
11	11	2020-01-28 18:21:59	2020-02-29 16:12:03	2020-03-31 20:06:28	2020-04-30 22:01:53	2020-05-31 18:41:43	2020-06-30 21:03:52	2020-07-29 19:46:21	2020-08-31 19:52:34	2020-09-30 20:26:46	2020-10-11 20:50:07	2020-11-09 21:00:55	2020-12-23 19:44:17	2020-12-23 19:44:17
12	12	2020-01-30 16:46:43	2020-02-28 16:35:13	2020-03-30 17:46:29	2020-04-20 13:28:00	2020-05-26 21:01:23	2020-06-29 12:10:58	2020-07-12 10:28:04	2020-08-25 13:01:13	2020-09-25 11:50:59	2020-10-30 12:07:28	2020-11-25 16:53:17	2020-12-16 11:01:06	2020-12-16 11:01:06
13	13	2020-01-10 19:03:20	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-06-16 19:18:07	1970-01-01 00:00:00	2020-08-28 11:24:59	2020-09-10 10:29:39	2020-10-07 14:29:30	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-10-07 14:29:30
14	14	2020-01-20 19:45:25	2020-02-20 17:11:20	2020-03-25 17:02:54	2020-04-28 14:44:13	2020-05-26 20:25:39	2020-06-14 09:40:06	2020-07-07 16:24:09	2020-08-15 10:54:46	1970-01-01 00:00:00	2020-10-25 18:47:11	2020-11-24 11:53:01	2020-12-28 16:27:39	2020-12-28 16:27:39
15	15	2020-01-18 10:30:01	2020-02-28 17:03:01	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-05-08 20:32:54	2020-06-09 09:18:44	2020-07-31 15:44:47	2020-08-06 08:46:20	2020-09-29 07:20:05	1970-01-01 00:00:00	2020-11-26 16:08:33	2020-12-08 15:14:21	2020-12-08 15:14:21
16	16	2020-01-30 11:44:20	2020-02-24 14:47:17	2020-03-31 16:10:58	2020-04-30 14:55:47	2020-05-31 16:33:59	2020-06-30 10:10:18	2020-07-06 18:25:35	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-07-06 18:25:35
17	17	2020-01-21 12:26:34	2020-02-23 15:25:05	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-02-23 15:25:05
18	18	2020-01-23 23:51:17	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-01-23 23:51:17
19	19	2020-01-04 13:58:55	2020-02-29 14:36:16	2020-03-29 14:47:53	2020-04-25 09:30:46	2020-05-30 12:29:41	2020-06-02 09:09:12	2020-07-21 14:17:09	2020-08-22 13:53:16	2020-09-16 09:11:22	2020-10-25 15:33:54	2020-11-21 10:04:30	2020-12-25 09:33:23	2020-12-25 09:33:23
20	20	2020-01-31 11:50:15	2020-02-27 16:02:07	2020-03-25 17:36:33	2020-04-28 20:28:11	2020-05-31 08:42:21	2020-06-29 11:03:56	2020-07-28 08:33:50	2020-08-30 12:53:57	2020-09-28 19:53:30	2020-10-29 12:45:50	2020-11-26 18:51:56	1970-01-01 00:00:00	2020-11-26 18:51:56
21	21	2020-01-28 17:32:30	2020-02-28 13:53:46	2020-03-31 17:16:22	2020-04-30 15:16:38	2020-05-31 17:12:35	2020-06-30 13:29:34	2020-07-31 16:11:41	2020-08-31 17:19:51	2020-09-30 14:50:28	2020-10-31 17:26:33	2020-11-30 14:22:36	2020-12-31 16:36:57	2020-12-31 16:36:57
22	22	2020-01-31 17:43:42	2020-02-23 17:17:46	1970-01-01 00:00:00	2020-04-30 17:37:01	2020-05-08 09:57:43	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-05-08 09:57:43
23	23	2020-01-30 21:26:06	2020-02-05 15:16:47	2020-03-11 17:58:35	2020-04-30 10:34:40	2020-05-31 10:59:53	2020-06-30 14:27:39	2020-07-31 14:25:46	2020-08-31 14:58:13	2020-09-25 10:52:34	2020-10-31 21:01:35	2020-11-30 21:04:39	2020-12-18 09:23:39	2020-12-18 09:23:39
24	24	2020-01-30 13:06:23	2020-02-26 12:18:47	2020-03-27 21:15:31	2020-04-27 17:04:44	2020-05-27 19:35:31	2020-06-24 11:44:32	2020-07-21 13:53:36	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-07-21 13:53:36
25	25	2020-01-20 10:02:21	2020-02-27 12:35:38	2020-03-18 13:20:07	2020-04-13 13:03:05	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-08-24 17:37:01	2020-09-28 11:38:06	2020-10-30 16:31:25	2020-11-25 20:58:23	2020-12-29 14:46:19	2020-12-29 14:46:19
26	26	2020-01-24 09:56:02	2020-02-28 14:52:52	2020-03-29 08:16:01	2020-04-19 11:45:28	2020-05-22 21:07:46	2020-06-18 16:27:11	2020-07-07 08:41:27	2020-08-24 22:12:21	2020-09-12 09:48:10	2020-10-17 19:11:53	2020-11-30 12:32:53	2020-12-28 21:28:15	2020-12-28 21:28:15
27	27	2020-01-26 11:24:23	2020-02-28 11:30:20	2020-03-14 15:30:40	2020-04-12 15:14:49	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-07-03 16:06:56	2020-08-31 09:28:22	2020-09-24 16:10:26	2020-10-25 10:23:57	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-10-25 10:23:57
28	28	2020-01-29 13:17:07	2020-02-13 15:15:04	2020-03-19 09:41:58	2020-04-26 10:02:58	2020-05-31 09:48:45	2020-06-21 10:23:34	2020-07-18 17:54:43	2020-08-31 11:16:42	2020-09-19 09:11:47	2020-10-26 16:24:24	2020-11-06 21:13:32	2020-12-19 11:53:49	2020-12-19 11:53:49
29	29	2020-01-10 16:24:23	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-11-03 10:01:31	1970-01-01 00:00:00	2020-11-03 10:01:31
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1729003	1729003	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:06:07	2020-12-31 22:06:07
1729004	1729004	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:12:39	2020-12-31 22:12:39
1729005	1729005	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:15:23	2020-12-31 22:15:23
1729006	1729006	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:16:24	2020-12-31 22:16:24
1729007	1729007	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:17:30	2020-12-31 22:17:30
1729008	1729008	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:18:34	2020-12-31 22:18:34
1729009	1729009	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:21:22	2020-12-31 22:21:22
1729010	1729010	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:30:15	2020-12-31 22:30:15
1729011	1729011	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:22:50	2020-12-31 22:22:50
1729012	1729012	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:22:56	2020-12-31 22:22:56
1729013	1729013	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:23:59	2020-12-31 22:23:59
1729014	1729014	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:38:19	2020-12-31 22:38:19
1729015	1729015	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:29:33	2020-12-31 22:29:33
1729016	1729016	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:30:08	2020-12-31 22:30:08
1729017	1729017	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:30:43	2020-12-31 22:30:43
1729018	1729018	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:30:53	2020-12-31 22:30:53
1729019	1729019	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:32:20	2020-12-31 22:32:20
1729020	1729020	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:33:42	2020-12-31 22:33:42
1729021	1729021	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:34:25	2020-12-31 22:34:25
1729022	1729022	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:34:51	2020-12-31 22:34:51
1729023	1729023	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:35:01	2020-12-31 22:35:01
1729024	1729024	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:39:23	2020-12-31 22:39:23
1729025	1729025	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:40:09	2020-12-31 22:40:09
1729026	1729026	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 22:58:45	2020-12-31 22:58:45
1729027	1729027	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:06:34	2020-12-31 23:06:34
1729028	1729028	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:10:40	2020-12-31 23:10:40
1729029	1729029	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:15:51	2020-12-31 23:15:51
1729030	1729030	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:27:21	2020-12-31 23:27:21
1729031	1729031	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:28:51	2020-12-31 23:28:51
1729032	1729032	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	1970-01-01 00:00:00	2020-12-31 23:30:49	2020-12-31 23:30:49

1729033 rows × 14 columns

df = df.reset_index()

df

	index	businessId	最后交易时间_x_x_x	交易金额_x_x_x	交易笔数_x_x_x	最后交易时间_y_x_x	交易金额_y_x_x	交易笔数_y_x_x	最后交易时间_x_y_x	交易金额_x_y_x	...	交易金额_y_x	交易笔数_y_x	最后交易时间_x_y	交易金额_x_y	交易笔数_x_y	最后交易时间_y_y	交易金额_y_y	交易笔数_y_y	总交易金额	总交易笔数
0	0	3	2020-01-31 14:31:51	212320.0	16.0	2020-02-27 13:59:31	206940.0	15.0	2020-03-29 13:16:33	165290.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	996010.00	87.0
1	1	12	2020-01-23 11:32:46	121200.0	4.0	2020-02-14 12:51:47	73000.0	2.0	2020-03-26 17:23:27	122800.0	...	159000.0	4.0	2020-11-02 10:16:56	48000.0	1.0	2020-12-18 12:32:36	162000.0	4.0	1084000.00	33.0
2	2	17	2020-01-02 11:43:28	1476.0	2.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	1476.00	2.0
3	3	18	2020-01-04 17:14:24	44289.0	3.0	0	0.0	0.0	0	0.0	...	0.0	0.0	2020-11-17 14:37:58	79997.0	4.0	0	0.0	0.0	218944.00	10.0
4	4	23	2020-01-29 19:42:58	58177.0	11.0	2020-02-29 16:29:45	88732.0	14.0	2020-03-31 14:33:01	142977.0	...	32988.0	7.0	2020-11-30 15:20:17	12887.0	4.0	2020-12-27 17:27:23	14571.0	3.0	1029513.00	174.0
5	5	25	2020-01-29 10:53:43	57245.3	5.0	2020-02-29 16:23:06	42578.0	4.0	2020-03-17 16:57:56	43058.0	...	50674.5	4.0	2020-11-28 08:55:22	39692.8	3.0	2020-12-28 18:01:53	55016.0	5.0	602419.60	52.0
6	6	32	2020-01-30 14:44:19	57511.0	13.0	2020-02-29 20:57:17	48588.0	8.0	2020-03-31 21:10:33	76483.0	...	107941.0	22.0	2020-11-30 14:59:37	36785.0	8.0	2020-12-30 11:02:06	74436.0	19.0	733722.00	166.0
7	7	46	2020-01-09 15:12:07	19912.0	4.0	0	0.0	0.0	0	0.0	...	74400.0	6.0	2020-11-17 12:38:38	71588.0	3.0	2020-12-15 16:17:46	89500.0	4.0	562050.00	32.0
8	8	48	2020-01-23 09:35:40	14277.0	2.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	14277.00	2.0
9	9	51	2020-01-19 10:57:34	11154.0	3.0	2020-02-10 22:13:12	5500.0	1.0	2020-03-24 08:34:13	15900.0	...	14400.0	2.0	0	0.0	0.0	2020-12-10 16:29:01	18900.0	2.0	175804.00	21.0
10	10	52	2020-01-30 21:03:57	316530.0	187.0	2020-02-29 21:40:10	355020.0	249.0	2020-03-31 20:29:49	401275.0	...	108523.0	89.0	2020-11-30 21:13:34	135800.0	77.0	2020-12-29 19:29:53	62353.0	47.0	3130300.00	1842.0
11	11	55	2020-01-28 18:21:59	32795.8	44.0	2020-02-29 16:12:03	17712.0	142.0	2020-03-31 20:06:28	26290.2	...	2307.0	17.0	2020-11-09 21:00:55	1065.0	11.0	2020-12-23 19:44:17	6561.0	52.0	337440.66	1342.0
12	12	66	2020-01-30 16:46:43	17703.0	24.0	2020-02-28 16:35:13	32809.0	40.0	2020-03-30 17:46:29	39523.1	...	64121.6	51.0	2020-11-25 16:53:17	21514.6	25.0	2020-12-16 11:01:06	17796.0	20.0	296694.60	341.0
13	13	80	2020-01-10 19:03:20	14500.0	1.0	0	0.0	0.0	0	0.0	...	720.0	1.0	0	0.0	0.0	0	0.0	0.0	34255.00	11.0
14	14	83	2020-01-20 19:45:25	205631.0	14.0	2020-02-20 17:11:20	192956.0	6.0	2020-03-25 17:02:54	225414.0	...	55584.0	3.0	2020-11-24 11:53:01	23544.0	4.0	2020-12-28 16:27:39	97651.0	10.0	1309749.00	86.0
15	15	92	2020-01-18 10:30:01	3000.0	1.0	2020-02-28 17:03:01	2000.0	1.0	0	0.0	...	0.0	0.0	2020-11-26 16:08:33	10000.0	1.0	2020-12-08 15:14:21	5000.0	1.0	64380.00	14.0
16	16	95	2020-01-30 11:44:20	217473.0	21.0	2020-02-24 14:47:17	319291.0	21.0	2020-03-31 16:10:58	202474.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	1447496.00	113.0
17	17	97	2020-01-21 12:26:34	10251.0	14.0	2020-02-23 15:25:05	40145.0	38.0	0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	50396.00	52.0
18	18	98	2020-01-23 23:51:17	4950.0	1.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	4950.00	1.0
19	19	105	2020-01-04 13:58:55	22550.0	1.0	2020-02-29 14:36:16	60890.0	3.0	2020-03-29 14:47:53	109800.0	...	178750.0	6.0	2020-11-21 10:04:30	116720.0	4.0	2020-12-25 09:33:23	142166.0	5.0	939337.00	37.0
20	20	108	2020-01-31 11:50:15	198417.8	46.0	2020-02-27 16:02:07	192150.0	21.0	2020-03-25 17:36:33	190504.0	...	267410.6	99.0	2020-11-26 18:51:56	211821.5	88.0	0	0.0	0.0	2405387.30	541.0
21	21	110	2020-01-28 17:32:30	99970.0	61.0	2020-02-28 13:53:46	43510.0	29.0	2020-03-31 17:16:22	56860.0	...	53390.0	39.0	2020-11-30 14:22:36	66360.0	46.0	2020-12-31 16:36:57	73505.0	48.0	799060.00	542.0
22	22	116	2020-01-31 17:43:42	43586.0	14.0	2020-02-23 17:17:46	74036.0	24.0	0	0.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	296129.00	88.0
23	23	129	2020-01-30 21:26:06	82450.0	33.0	2020-02-05 15:16:47	10960.0	2.0	2020-03-11 17:58:35	5480.0	...	71820.0	32.0	2020-11-30 21:04:39	50150.0	23.0	2020-12-18 09:23:39	40810.0	18.0	587560.00	258.0
24	24	131	2020-01-30 13:06:23	60000.0	3.0	2020-02-26 12:18:47	81500.0	6.0	2020-03-27 21:15:31	25000.0	...	0.0	0.0	0	0.0	0.0	0	0.0	0.0	459500.00	29.0
25	25	137	2020-01-20 10:02:21	3268.0	8.0	2020-02-27 12:35:38	6723.0	13.0	2020-03-18 13:20:07	3329.0	...	12411.0	9.0	2020-11-25 20:58:23	6288.0	6.0	2020-12-29 14:46:19	11051.0	10.0	60642.00	71.0
26	26	149	2020-01-24 09:56:02	73451.0	7.0	2020-02-28 14:52:52	58582.0	8.0	2020-03-29 08:16:01	66294.0	...	35454.0	3.0	2020-11-30 12:32:53	95372.0	10.0	2020-12-28 21:28:15	42987.0	4.0	751987.00	83.0
27	27	164	2020-01-26 11:24:23	37390.0	7.0	2020-02-28 11:30:20	64441.0	12.0	2020-03-14 15:30:40	7520.0	...	71505.0	7.0	0	0.0	0.0	0	0.0	0.0	286030.00	41.0
28	28	169	2020-01-29 13:17:07	259083.0	10.0	2020-02-13 15:15:04	35122.0	1.0	2020-03-19 09:41:58	259228.0	...	258987.0	8.0	2020-11-06 21:13:32	84310.0	3.0	2020-12-19 11:53:49	180653.0	7.0	1702356.00	57.0
29	29	171	2020-01-10 16:24:23	10160.0	1.0	0	0.0	0.0	0	0.0	...	0.0	0.0	2020-11-03 10:01:31	16818.0	2.0	0	0.0	0.0	26978.00	3.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1729003	1729003	3399569	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:06:07	288.0	1.0	288.00	1.0
1729004	1729004	3399582	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:12:39	760.0	1.0	760.00	1.0
1729005	1729005	3399587	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:15:23	560.0	1.0	560.00	1.0
1729006	1729006	3399588	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:16:24	10.0	1.0	10.00	1.0
1729007	1729007	3399589	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:17:30	220.0	1.0	220.00	1.0
1729008	1729008	3399592	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:18:34	10.0	1.0	10.00	1.0
1729009	1729009	3399596	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:21:22	5000.0	1.0	5000.00	1.0
1729010	1729010	3399599	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:30:15	2269.0	3.0	2269.00	3.0
1729011	1729011	3399601	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:22:50	221.0	1.0	221.00	1.0
1729012	1729012	3399602	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:22:56	500.0	1.0	500.00	1.0
1729013	1729013	3399604	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:23:59	230.0	1.0	230.00	1.0
1729014	1729014	3399610	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:38:19	5320.0	2.0	5320.00	2.0
1729015	1729015	3399615	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:29:33	2581.0	1.0	2581.00	1.0
1729016	1729016	3399617	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:30:08	10.0	1.0	10.00	1.0
1729017	1729017	3399618	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:30:43	7000.0	1.0	7000.00	1.0
1729018	1729018	3399619	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:30:53	854.0	1.0	854.00	1.0
1729019	1729019	3399622	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:32:20	888.0	1.0	888.00	1.0
1729020	1729020	3399629	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:33:42	3123.0	1.0	3123.00	1.0
1729021	1729021	3399630	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:34:25	956.0	1.0	956.00	1.0
1729022	1729022	3399633	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:34:51	220.0	1.0	220.00	1.0
1729023	1729023	3399634	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:35:01	5028.0	1.0	5028.00	1.0
1729024	1729024	3399638	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:39:23	35000.0	1.0	35000.00	1.0
1729025	1729025	3399639	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:40:09	10.0	1.0	10.00	1.0
1729026	1729026	3399642	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 22:58:45	220.0	1.0	220.00	1.0
1729027	1729027	3399643	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:06:34	220.0	1.0	220.00	1.0
1729028	1729028	3399644	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:10:40	220.0	1.0	220.00	1.0
1729029	1729029	3399645	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:15:51	652.0	1.0	652.00	1.0
1729030	1729030	3399646	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:27:21	220.0	1.0	220.00	1.0
1729031	1729031	3399647	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:28:51	220.0	1.0	220.00	1.0
1729032	1729032	3399648	0	0.0	0.0	0	0.0	0.0	0	0.0	...	0.0	0.0	0	0.0	0.0	2020-12-31 23:30:49	220.0	1.0	220.00	1.0

1729033 rows × 40 columns

六、两张表合并得到含有总交易金额、总交易笔数和最后一次交易时间的列

df3 = pd.merge(left = df,right = df2,on = "index",how = "outer")

df4 = df3.loc[:,["businessId","总交易金额" ,"总交易笔数" ,"time"]]

df4

	businessId	总交易金额	总交易笔数	time
0	3	996010.00	87.0	2020-07-27 14:12:07
1	12	1084000.00	33.0	2020-12-18 12:32:36
2	17	1476.00	2.0	2020-01-02 11:43:28
3	18	218944.00	10.0	2020-11-17 14:37:58
4	23	1029513.00	174.0	2020-12-27 17:27:23
5	25	602419.60	52.0	2020-12-28 18:01:53
6	32	733722.00	166.0	2020-12-30 11:02:06
7	46	562050.00	32.0	2020-12-15 16:17:46
8	48	14277.00	2.0	2020-01-23 09:35:40
9	51	175804.00	21.0	2020-12-10 16:29:01
10	52	3130300.00	1842.0	2020-12-29 19:29:53
11	55	337440.66	1342.0	2020-12-23 19:44:17
12	66	296694.60	341.0	2020-12-16 11:01:06
13	80	34255.00	11.0	2020-10-07 14:29:30
14	83	1309749.00	86.0	2020-12-28 16:27:39
15	92	64380.00	14.0	2020-12-08 15:14:21
16	95	1447496.00	113.0	2020-07-06 18:25:35
17	97	50396.00	52.0	2020-02-23 15:25:05
18	98	4950.00	1.0	2020-01-23 23:51:17
19	105	939337.00	37.0	2020-12-25 09:33:23
20	108	2405387.30	541.0	2020-11-26 18:51:56
21	110	799060.00	542.0	2020-12-31 16:36:57
22	116	296129.00	88.0	2020-05-08 09:57:43
23	129	587560.00	258.0	2020-12-18 09:23:39
24	131	459500.00	29.0	2020-07-21 13:53:36
25	137	60642.00	71.0	2020-12-29 14:46:19
26	149	751987.00	83.0	2020-12-28 21:28:15
27	164	286030.00	41.0	2020-10-25 10:23:57
28	169	1702356.00	57.0	2020-12-19 11:53:49
29	171	26978.00	3.0	2020-11-03 10:01:31
...	...	...	...	...
1729003	3399569	288.00	1.0	2020-12-31 22:06:07
1729004	3399582	760.00	1.0	2020-12-31 22:12:39
1729005	3399587	560.00	1.0	2020-12-31 22:15:23
1729006	3399588	10.00	1.0	2020-12-31 22:16:24
1729007	3399589	220.00	1.0	2020-12-31 22:17:30
1729008	3399592	10.00	1.0	2020-12-31 22:18:34
1729009	3399596	5000.00	1.0	2020-12-31 22:21:22
1729010	3399599	2269.00	3.0	2020-12-31 22:30:15
1729011	3399601	221.00	1.0	2020-12-31 22:22:50
1729012	3399602	500.00	1.0	2020-12-31 22:22:56
1729013	3399604	230.00	1.0	2020-12-31 22:23:59
1729014	3399610	5320.00	2.0	2020-12-31 22:38:19
1729015	3399615	2581.00	1.0	2020-12-31 22:29:33
1729016	3399617	10.00	1.0	2020-12-31 22:30:08
1729017	3399618	7000.00	1.0	2020-12-31 22:30:43
1729018	3399619	854.00	1.0	2020-12-31 22:30:53
1729019	3399622	888.00	1.0	2020-12-31 22:32:20
1729020	3399629	3123.00	1.0	2020-12-31 22:33:42
1729021	3399630	956.00	1.0	2020-12-31 22:34:25
1729022	3399633	220.00	1.0	2020-12-31 22:34:51
1729023	3399634	5028.00	1.0	2020-12-31 22:35:01
1729024	3399638	35000.00	1.0	2020-12-31 22:39:23
1729025	3399639	10.00	1.0	2020-12-31 22:40:09
1729026	3399642	220.00	1.0	2020-12-31 22:58:45
1729027	3399643	220.00	1.0	2020-12-31 23:06:34
1729028	3399644	220.00	1.0	2020-12-31 23:10:40
1729029	3399645	652.00	1.0	2020-12-31 23:15:51
1729030	3399646	220.00	1.0	2020-12-31 23:27:21
1729031	3399647	220.00	1.0	2020-12-31 23:28:51
1729032	3399648	220.00	1.0	2020-12-31 23:30:49

1729033 rows × 4 columns

七、计算最后一次交易时间与表中最大的一次交易时间的差值，算出R，把总交易金额改为M，总交易笔数改为F

df4["R"] = -(df4.time - df4.time.max())/np.timedelta64(1,"D")
df4.rename(columns = {"总交易笔数":"F","总交易金额":"M"},inplace = True
        )
df4.head()

	businessId	M	F	time	R
0	3	996010.0	87.0	2020-07-27 14:12:07	157.407940
1	12	1084000.0	33.0	2020-12-18 12:32:36	13.477049
2	17	1476.0	2.0	2020-01-02 11:43:28	364.511169
3	18	218944.0	10.0	2020-11-17 14:37:58	44.389988
4	23	1029513.0	174.0	2020-12-27 17:27:23	4.272338

rfm = df4.loc[:,["businessId","M" ,"F" ,"R"]]

rfm.head()

	businessId	M	F	R
0	3	996010.0	87.0	157.407940
1	12	1084000.0	33.0	13.477049
2	17	1476.0	2.0	364.511169
3	18	218944.0	10.0	44.389988
4	23	1029513.0	174.0	4.272338

rfm.describe()

	businessId	M	F	R
count	1.729033e+06	1.729033e+06	1.729033e+06	1.729033e+06
mean	1.928541e+06	2.685263e+05	8.307820e+01	1.024406e+02
std	9.058440e+05	4.836615e+05	1.978434e+02	1.127358e+02
min	3.000000e+00	1.000000e+01	1.000000e+00	0.000000e+00
25%	1.238238e+06	2.291300e+04	8.000000e+00	6.647581e+00
50%	2.076301e+06	1.001700e+05	2.800000e+01	4.332269e+01
75%	2.668493e+06	3.010450e+05	7.900000e+01	1.936252e+02
max	3.399648e+06	2.541733e+07	2.169400e+04	3.659962e+02

八、构造rfm函数，把用户分为八大类，得到每类的用户数，交易金额，交易次数等情况

def rfm_func(x):
    level = x.apply(lambda x:"1" if x >= 1 else "0")
    label = level.R + level.F + level.M
    d = {
        '011':'重要价值客户', 
        '111':'重要唤回客户',
        '001':'重要深耕客户',
        '101':'重要挽留客户',
        '010':'一般价值客户',
        '110':'一般唤回客户',
        '000':'新客户',
        '100':'流失客户'
    }
    result = d[label]
    return result
rfm["label"] = rfm[["R","F","M"]].apply(lambda x:x-x.median()).apply(rfm_func,axis = 1)
rfm.head()

	businessId	M	F	R	label
0	3	996010.0	87.0	157.407940	重要唤回客户
1	12	1084000.0	33.0	13.477049	重要价值客户
2	17	1476.0	2.0	364.511169	流失客户
3	18	218944.0	10.0	44.389988	重要挽留客户
4	23	1029513.0	174.0	4.272338	重要价值客户

rfm.groupby("label").sum()

	businessId	M	F	R
label
一般价值客户	183950574239	5.516117e+09	5043434.0	1.056885e+06
一般唤回客户	115782223261	3.730829e+09	3353214.0	1.037503e+07
新客户	436911070553	6.620066e+09	2345231.0	3.057678e+06
流失客户	988606699527	1.179006e+10	3629429.0	1.122922e+08
重要价值客户	945619095134	3.262352e+11	101731808.0	4.202278e+06
重要唤回客户	352482481210	7.681337e+10	24595629.0	2.961039e+07
重要挽留客户	158595596900	1.666728e+10	1469621.0	1.538149e+07
重要深耕客户	152562505367	1.691790e+10	1476587.0	1.147301e+06

rfm.groupby("label").count()

	businessId	M	F	R
label
一般价值客户	90708	90708	90708	90708
一般唤回客户	60177	60177	60177	60177
新客户	192143	192143	192143	192143
流失客户	521509	521509	521509	521509
重要价值客户	508064	508064	508064	508064
重要唤回客户	190944	190944	190944	190944
重要挽留客户	88028	88028	88028	88028
重要深耕客户	77460	77460	77460	77460

plt.rcParams['font.sans-serif']=['SimHei']
plt.rcParams['axes.unicode_minus'] = False
for label,gropued in rfm.groupby('label'):
    x= gropued['F']
    y = gropued['R']

    plt.scatter(x,y,label = label) # 利用循环绘制函数
plt.legend(loc='best') # 图例位置
plt.xlabel('Frequency')
plt.ylabel('Recency')
plt.show()

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

rfm.describe()

	businessId	M	F	R
count	1.729033e+06	1.729033e+06	1.729033e+06	1.729033e+06
mean	1.928541e+06	2.685263e+05	8.307820e+01	1.024406e+02
std	9.058440e+05	4.836615e+05	1.978434e+02	1.127358e+02
min	3.000000e+00	1.000000e+01	1.000000e+00	0.000000e+00
25%	1.238238e+06	2.291300e+04	8.000000e+00	6.647581e+00
50%	2.076301e+06	1.001700e+05	2.800000e+01	4.332269e+01
75%	2.668493e+06	3.010450e+05	7.900000e+01	1.936252e+02
max	3.399648e+06	2.541733e+07	2.169400e+04	3.659962e+02