在《Python 和 SPL 数据读取与计算性能测试对比》中，我们对比了Python和SPL在数据读取和计算方面的性能。日常数据处理的过程中，还会有许多对数据集改写的动作，这一次我们对比一下Python和SPL在这方面的性能。

测试环境

系统：Windows 11

内存：16G

CPU：8核

数据：1G规模的TPCH

本文中使用的数据不是特别大，能够完全放入内存中。

数据使用orders表，数据量是150万行。

文本文件大小是172M，以符号”|”分割，数据形式如下：

1|18451|O|193738.97|1996-01-02|5-LOW|Clerk#000000951|0|nstructions sleep furiously among |

2|39001|O|41419.39|1996-12-01|1-URGENT|Clerk#000000880|0| foxes. pending accounts at the pending, silent asymptot|

3|61657|F|208403.57|1993-10-14|5-LOW|Clerk#000000955|0|sly final accounts boost. carefully regular ideas cajole carefully. depos|

4|68389|O|30234.04|1995-10-11|5-LOW|Clerk#000000124|0|sits. slyly regular warthogs cajole. regular, regular theodolites acro|

5|22243|F|149065.30|1994-07-30|5-LOW|Clerk#000000925|0|quickly. bold deposits sleep slyly. packages use slyly|

...

插入记录

为orders在第8行插入一条记录，记录的内容是

[8,80665,'F',192181.76,'1995-01-16','3-MEDIUM','Clerk#000000946',0,'efulpackages.blithelyfinalaccountssleepcare']

Python代码

import pandas as pd
import numpy as np
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|')
s = time.time()
cols = orders_data.columns
rcd = [8,80665,'F',192181.76,'1995-01-16','3-MEDIUM','Clerk#000000946',0,'eful packages. blithely final accounts sleep care']
orders_data_insert = pd.DataFrame(np.insert(orders_data.values, 7, values=rcd, axis=0),columns=cols)
e = time.time()
print(e-s)

耗时0.32秒。

Pandas的DataFrame本质是Numpy的矩阵，但矩阵的一些方法并没有完全继承，比如insert()，Numpy既可以行插入，又可以列插入，但Pandas的insert只支持列插入，要插入行就要先转到Numpy.array()，插入数据后再转回DataFrame，所以一个简单的插入动作却需要俩次数据转换，所以耗时比较多。

SPL代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	[8,80665,F,192181.76,1995-01-16,3-MEDIUM,Clerk#000000946,0,eful packages. blithely final accounts sleep care]
5	=A2.record@i(A4,8)
6	=interval@ms(A3,now())

耗时0.001秒。

删除记录

删除第10000条记录

Python代码

import pandas as pd
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|')
s = time.time()
orders_data_delete = orders_data.drop(index=9999)
e = time.time()
print(e-s)
print(orders_data_delete.iloc[9998:10001])

耗时0.13秒

SPL代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.delete(10000)
5	=interval@ms(A3,now())

耗时0.001秒

修改记录

将第 100万 条记录的 O_CUSTKEY 改为 1000000，O_ORDERDATE 改为 1996-10-10。

Python代码

import pandas as pd
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|',parse_dates=['O_ORDERDATE'],infer_datetime_format=True)
s = time.time()
orders_data.loc[999999,['O_CUSTKEY','O_ORDERDATE']]=[1000000,pd.to_datetime('1996-10-10')]
e = time.time()
print(e-s)

耗时0.006秒

SPL代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.modify(1000000,1000000:O_CUSTKEY,date("1996-10-10"):O_ORDERDATE )
5	=interval@ms(A3,now())

耗时0.001秒

修改字段名

将O_ORDERKEY改为O_KEY，O_TOTALPRICE改为O_T_PRICE。

Python代码

import pandas as pd
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|',parse_dates=['O_ORDERDATE'],infer_datetime_format=True)
s = time.time()
orders_data.rename(columns={'O_ORDERKEY':'O_KEY','O_TOTALPRICE':'O_T_PRICE'},inplace=True)
e = time.time()
print(e-s)

耗时0.002秒

SPL代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.rename(O_ORDERKEY:O_KEY,O_TOTALPRICE:O_T_PRICE)
5	=interval@ms(A3,now())

耗时0.001秒

增加字段

由于Pandas擅长数字运算，不擅长字符串运算，将增加字段测试分为两个，分别是增加一列数字运算列，增加一列字符串运算列。

增加数字运算列

增加一列O_TOTALPRICE与平均值的差。

Python代码

import pandas as pd
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|')
s = time.time()
mprice = orders_data['O_TOTALPRICE'].mean()
orders_data['O_DIF_AVG'] = orders_data['O_TOTALPRICE']-mprice
e = time.time()
print(e-s)

耗时0.01秒。

SPL代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.avg(O_TOTALPRICE)
5	=A2.derive(O_TOTALPRICE-A4:O_DIF_AVG)
6	=interval@ms(A3,now())

耗时0.30秒。

SPL企业版列式计算代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|").i()
3	=now()
4	=A2.avg(O_TOTALPRICE)
5	=A2.derive@o(O_TOTALPRICE-A4:O_DIF_AVG)
6	=interval@ms(A3,now())

耗时0.01秒。

SPL企业版列式计算适合列式计算，性能提高很多。

增加字符串运算列

增加一列O_CLERK的数字号码。

Python代码

import pandas as pd
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|')
s = time.time()
orders_data['O_CLERK_NUM'] = orders_data['O_CLERK'].str.split("#",expand=True)[1].astype(int)
e = time.time()
print(e-s)

耗时2.2秒。

同样是增加列运算，Python字符串运算和数字运算性能相差两个数量级。

SPL代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.derive(int(O_CLERK.split("#")(2)):O_CLERK_NUM)
5	=interval@ms(A3,now())
6	D:\TPCHdata\tpchtbl1g\orders.tbl

耗时0.51秒

SPL的字符串运算相较于数字运算也偏慢，但没有差出数量级。

SPL企业版列式计算代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|").i()
3	=now()
4	=A2.derive@o(int(O_CLERK.split("#")(2)):O_CLERK_NUM)
5	=interval@ms(A3,now())
6	D:\TPCHdata\tpchtbl1g\orders.tbl

耗时0.47秒。

提取字段

提取前三个字段。

Python代码

import pandas as pd
import numpy as np
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|')
s = time.time()
cols = orders_data.columns
orders_3cols = orders_data.iloc[:,:3]
e = time.time()
print(e-s)

耗时0.02秒。

SPL代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.new(#1,#2,#3)
5	=interval@ms(A3,now())

耗时0.14秒。

SPL企业版列式计算代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|").i()
3	=now()
4	=A2.new@o(#1,#2,#3)
5	=interval@ms(A3,now())

耗时0.001秒。

过滤修改

将O_ORDERSTATUS为O的订单的O_TOTALPRICE调低10%

Python代码

import pandas as pd
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|',parse_dates=['O_ORDERDATE'],infer_datetime_format=True)
s = time.time()
update_price = orders_data[orders_data['O_ORDERSTATUS']=='O']['O_TOTALPRICE']*0.9
orders_data.loc[orders_data['O_ORDERSTATUS']=='O','O_TOTALPRICE'] = update_price
e = time.time()
print(e-s)

耗时0. 20秒。

过滤修改的思路很简单，先过滤再修改即可，可是Python不支持这样的修改动作，只能对着原DataFrame修改，所以要先算出所需数据的90%，然后对着原数据修改。这样明显是过滤了两遍。

SPL代码

	A
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.select(O_ORDERSTATUS=="O")
5	=A4.run(O_TOTALPRICE*=0.9)
6	=interval@ms(A3,now())

耗时0.14秒

SPL按照正常思路来做即可。

缺失值操作

对缺失值的操作其实也是对数据的修改。这里就以三项操作来对比Python和SPL的性能。

设置缺失值

每个字段随机设置5-10个缺失值。

Python代码

import pandas as pd
import numpy as np
import random
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|',parse_dates=['O_ORDERDATE'],infer_datetime_format=True)
s = time.time()
l = len(orders_data)
cols = orders_data.columns
for i in cols:
for j in range(random.randint(5,11)):
r = random.randint(0, l)
orders_data.loc[r,i] = np.nan
e = time.time()
print(e-s)

耗时0.05秒。

SPL代码

	A	B
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.len()
5	for A2.fname()	=(rand(6)+5).(rand(A4)+1)
6		=A2(B5).field(A5,null)
7	=interval@ms(A3,now())

耗时0.007秒

删除缺失值

将上例数据中包含缺失值的记录删除。

Python代码

import pandas as pd
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders_na.tbl"
orders_data = pd.read_csv(orders_file,sep = '|')
s = time.time()
orders_data = orders_data.dropna()
e = time.time()
print(e-s)

耗时0.75秒。

SPL代码

	A
1	D:\TPCHdata\tpchtbl1g\orders_na.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.select(!~.array().pos(null))
5	=interval@ms(A3,now())

耗时0.40秒

前值插补

用前值插补缺失值

Python代码

import pandas as pd
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders_na.tbl"
orders_data = pd.read_csv(orders_file,sep = '|')
s = time.time()
orders_data.fillna(method='ffill',inplace=True)
e = time.time()
print(e-s)

耗时0.71秒。

Pandas提供了前值插补的方法，代码写起来倒是简单。

SPL代码

	A	B
1	D:\TPCHdata\tpchtbl1g\orders_na.tbl
2	=file(A1).import@t(;,"|")
3	=now()
5	for A2.fname()	=A2.calc(A4,${A5}=if(!${A5},${A5}[-1],${A5}))
6	=interval@ms(A3,now())

耗时0.19秒。

SPL没有现成的方法插补缺失值，但自己写出来也不算复杂。

随机值插补

各字段分别用自己的随机值插补缺失值

Python代码

import pandas as pd
import numpy as np
import random
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders_na.tbl"
orders_data = pd.read_csv(orders_file,sep = '|')
s = time.time()
cols = orders_data.columns
l = len(orders_data)
for c in cols:
while True:
randn = random.randint(0,l)
rand = orders_data.loc[randn,c]
if rand!=np.nan:
break
orders_data[c].fillna(rand,inplace=True)
e = time.time()
print(e-s)

耗时0.21秒。

SPL代码

	A	B	C
1	D:\TPCHdata\tpchtbl1g\orders_na.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.len()
5	=A2.pselect@a(~.array().pos(null)>0)
6	for A2.fname()	=null
7		for !B7	>B6=A2(rand(A4)+1).${A6}
8		=A2.calc(A5,${A6}=if(!${A6},B6,${A6}))
9	=interval@ms(A3,now())

耗时0.17秒。

字段类型转换

将非数字类型的字段转换为数字。对于同一个字段，同一个字符串转换成相同的数字。

Python代码

import pandas as pd
import time
orders_file = "D:/TPCHdata/tpchtbl1g/orders.tbl"
orders_data = pd.read_csv(orders_file,sep = '|')
s = time.time()
dtp = orders_data.dtypes
o_cols = dtp[dtp=='object'].index
for c in o_cols:
cmap = {}
gc = orders_data.groupby(c)
cn = 0
for g in gc:
cn+=1
cmap[g[0]]=cn
orders_data[c] = orders_data[c].map(cmap)
e = time.time()
print(e-s)

耗时20.4秒。

SPL代码

	A	B
1	D:\TPCHdata\tpchtbl1g\orders.tbl
2	=file(A1).import@t(;,"|")
3	=now()
4	=A2.fname()
5	=A2(1).array().pselect@a(!ifnumber(~))
6	for A4(A5)	=A2.group(${A6})
7		>B6.run(~.field(A6,B6.#))
8	=interval@ms(A3,now())

耗时2.85秒。

总结

数据处理能力对比表，单位：秒

		Python	SPL社区版	SPL企业版列式
插入记录		0.32	0.001
删除记录		0.13	0.001
修改记录		0.006	0.001
修改字段名		0.002	0.001
增加字段	增加数字运算列	0.01	0.30	0.01
	增加字符串运算列	2.20	0.51	0.47
提取字段		0.02	0.14	0.001
过滤修改		0.20	0.14
缺失值操作	设置缺失值	0.05	0.007
	删除缺失值	0.75	0.40
	前值插补	0.71	0.19
	随机值插补	0.21	0.17
	字段类型转换	20.4	2.85

综合比较这些修改记录的项目，相较于SPL社区版，Python只在增加数字运算列和提取字段两项占优，其它方面均落后，这是因为Pandas的数据结构是矩阵，纯数字运算是它最大的优势，正因为矩阵这一数据结构，Pandas运算不再灵活，如处理字符串就比较慢。另外Pandas的矩阵继承也不完善，有些动作要绕到Numpy才能做，比如插入记录。Python还有个优势是它的数学方法比较多，比如前值插补缺失值，写起来确实简单，但本文对比的是两者的性能，在这方面Python并没有优势。

SPL的数据结构是序表，这些运算无非是遍历、定位、修改等动作，SPL的序表是有序集合，做这些运算很灵活，而且高效。SPL社区版足够灵活，擅长逐行修改等动作，比如A.run()，A.field()等，计算列时效率不太高，比如增加数字列，提取字段等。SPL企业版的纯序列（纯序表）弥补了这一缺陷，面对列式计算同样有高性能，也不比Python差，不过纯序列（纯序表）不擅长行式计算，所以本文中关于行运算的例子并没有列出企业版列式计算的代码，使用时要根据实际情况选择性能更好的处理方式。