Python Pandas PK esProc SPL，谁才是数据预处理王者？

数据源

数据源种类

Pandas支持多种数据源，包括：

• 文本数据文件，包括TAB分隔的txt、逗号分隔的csv，也可自定义其它分隔符。
• 固定宽度文件fwf，
• 各类关系型数据库，
• Excel，
• Json，
• XML，
• Restful、WebService，
• html抓取，
• sas，
• spss，
• stata，
• 列存格式Parquet，
• 列存格式ORC，
• Google BigQuery，
• 科学数据HDF，
• 数据框feather，
• 剪贴板里的结构化数据，
• 私有格式pickle。

SPL支持的数据源也很多，包括：

• 文本数据文件，包括TAB分隔的txt、逗号分隔的csv，也可自定义其它分隔符，
• 固定宽度文件fwf，
• 各类关系型数据库，
• Excel，
• Json，
• XML，
• Restful、WebService，
• html抓取，
• HBase，
• HDFS，
• Hive，
• Spark，
• Elasticsearch，
• MongoDB，
• Kafka，
• R2dbc，
• FTP，
• Cassandra，
• DynamoDB，
• influxDB，
• Redis，
• SAP，
• 剪贴板里的结构化数据，
• 私有格式btx、ctx。

读写数据库

用SQL查询数据库，用csv文件更新数据库。Pandas：

conn = create_engine('mysql+pymysql://root:password@localhost:3306/testdb')
df_read = pd.read_sql_query('select * from product', conn)
data = pd.read_csv("d:/Orders.csv")
data.to_sql('testdf', conn, index=False)
conn.dispose()

简单读写数据库时，Pandas代码足够优雅。

SPL：

	A
1	=connect(“com.mysql.jdbc.Driver”,“jdbc:mysql://localhost:3306/testdb?user=root&password=password”)
2	=A1.query("select * from product ")
3	=T(“d:/Orders.csv”)
4	=A1.update(A3, testdf; ORDERID)
5	=A1.close()

SPL代码也很简单，整体逻辑与Pandas类似。区别在于,SPL可以把数据源信息写在配置文件里，代码里只要简单引用数据源名，具体来说，A1可以写成：connect(“myDB”)

读写文本文件

规则文本：读取csv文件，简单计算后写入新csv。Pandas：

data = pd.read_csv("d:/Orders.csv")
data['OrderDate']=pd.to_datetime(data['OrderDate'])
result=data.groupby(data['OrderDate'].dt.year).agg({'Amount':[len,np.sum]})
result.to_csv("d:/resultP.csv")

Pandas代码很简洁，但仍有不足之处，一是不能自动解析日期时间类型；二是计算代码里大中小括号都有，既有表达式又有字符串，有明显的可优化之处，语言整体性不佳。

SPL实现相同的功能：

	A
1	=T(“d:/Orders.csv”)
2	=A1.groups(year(OrderDate);count(1),sum(Amount))
3	=file(“d:/resulS.csv”).export@t(A2)

SPL代码也很简洁，且可自动解析日期时间类型，可以只用一种括号，可以只用表达式，语言整体性极佳。

不规则的文本：每三行对应一条记录，其中第二行含三个字段（集合的成员也是集合），将该文件整理成规范的结构化数据对象。Pandas:

data = pd.read_csv("d:/threeLines.txt",header=None)
pos_seq=[i//3 for i in range(len(data))]
def runSplit(x):
    f123=x.iloc[1,0].split("\t")
    f=[x.iloc[0,0],f123[0],f123[1],f123[2],x.iloc[2,0]]
    return pd.DataFrame([f], columns=['OrderID','Client','SellerId','Amount','OrderDate'])
df=data.groupby(pos_seq).apply(runSplit)
df.reset_index(drop=True, inplace=True)			#drop the Second Index

上述解析过程大体分三步：先将文本读为单字段的DataFrame；再进行有序分组，即每三行分一组；最后循环每一组，将组内数据拼成单记录的DataFrame，循环结束时合并各条记录，形成新的DataFrame。
遇到不规则的文本时，Pandas代码明显变复杂了，体现在以下几处。制造形如[0,0,0,1,1,1,2,2,2…]的分组依据时，需要用较复杂的for循环语句，先定义循环计数i，再用i整除并取商。用apply循环各组数据时，需要定义一个处理组内数据的函数，这个函数超出了一句，因此不能用Lambda表达式来简化定义过程（连Java等编译型语言都没有这种限制）。取DataFrame data的成员时，只能用函数iloc（或loc），而取list f123的成员时，可以直接用下标，两者都是集合，但用法大相径庭，只因为DataFrame不是原生类库，语言整体性较差，无法像原生类库那样享受简洁的语法规则。DataFrame本身有索引，apply拼合多个DataFrame时，会加上第二层索引，需要手工去掉一层。

SPL：

	A
1	=file(“D:\split.csv”).import@si()
2	=A1.group((#-1)\3)
3	=A2.new(~(1):OrderID, (line=(2).split(“\t”))(1):Client,line(2):SellerId,line(3):Amount,(3):OrderDate )

SPL的解析逻辑和Pandas一样，但代码简单多了。制造分组依据时，不用复杂的for循环语句，而是用更简单的group(…)循环函数，且无需定义循环计数，#就是默认的循环计数（~是默认的循环变量）。用new循环各组数据时，也要定义一个处理函数，但SPL支持强大且简洁的Lambda表达式，可以把多句代码直接写在new里，不必像Python那样手工定义完整的函数结构。从SPL的任何集合类型（包括序表）取成员时，都可以直接用下标，语法简洁一致。new函数最后也要拼合多条记录，但不会生成无用的新索引。SPL代码更简洁，底层原因是原生类库的语言整体性更强。

多层数据

简单查询：Json文件的上层为销售员，下层为订单，查询出符合条件的所有订单。Pandas:

JsonStr=open('D:/data.json','r').read()
JsonObj=json.loads(JsonStr)
df=pd.json_normalize(JsonObj,['Orders'])
df['OrderDate']=pd.to_datetime(df['OrderDate']) 
result=df.query('Amount>1000 and Amount<2000 and contains("business")')

Pandas代码比较简单。要注意的是，dict、list等Python基本数据支持泛型，且与Json的object、array类型天然对应，适合表示多层Json（但不适合表达二维数据）。相反，DataFrame适合表达二维数据，但同一列的数据类型不可变，不是真正的泛型，无法表达一般的多层Json。DataFrame不擅长表达多层Json，需要用json_normalize函数将多层Json转为二维DataFrame，才能进行后续计算，这说明Pandas的语言整体性不够好。

SPL：

	A
1	=file(“d:/EO.json”).read()
2	=json(A1)
3	=A2.conj(Orders)
4	=A3.select(Amount>1000 && Amount<=2000 && like@c(Client,“*business*”))

序表不仅支持二维数据，也支持多层数据。序表支持真正的泛型，与Json的object、array类型天然对应，适合表示多层数据。多层数据是二维数据的一般形式，序表同样擅长表达二维数据，不需要额外的标准化动作，直接就能计算。

访问层次节点：对Json分组汇总，分组字段既有上层字段，也有下层字段。Pandas：

JsonStr=open('D:/data.json','r').read()
JsonObj=json.loads(JsonStr)
df=json_normalize(JsonObj,record_path=['Orders'],meta=['Name','Gender','Dept'])
result=df.groupby(['Dept','Client']).agg({'Amount':['count','sum']}).reset_index()
result.columns = ['Dept','Clt','cnt','sum']

Pandas DataFrame无法表达多层Json，也就不支持按树形的层次关系直观地访问数据，只能用normalize把多层数据转为二维数据，再访问扁平的二维数据。

SPL：

	A
1	=json(file(“d:/data.json”).read())
2	=A1.groups(Dept,Orders.Client:Clt; count(Orders.OrderID):cnt, sum(Orders.Amount):sum)

SPL序表可以表达多层Json，支持多层数据的计算，比Pandas简洁优雅。多层数据计算的特征之一，是提供方便的语法用来表达树形的层级关系，比如上面代码中的点号"Orders.Client"，可以自由引用任意节点的数据。当层级较多结构复杂时，这种引用方式可以明显提升表达效率。

同理可知，Pandas和SPL虽然都可以计算XML，但DataFrame不支持多层XML，必须转为二维结构，表达能力不强；SPL序表可以表达并计算多层XML，代码更加优雅。

与Json的normalize函数不同，Pandas没有为XML提供方便的标准化函数，官方推荐用XML计算语言把多层XML计算为二维XML，常用的XML计算语言有XSLT和XPath。为了计算XML，还得学习第三方语言，学习成本过高，这里就不举例了。

SPL整体性极佳，可以用与Json类似的代码解析XML，与Json相同的代码计算XML，学习成本很低。比如对多层XML进行分组汇总：

	A
1	=file(“d:\xml\emp_orders.xml”).read()
2	=xml(A1,“xml/row”)
3	=A2.groups(Dept,Orders.Client:Clt; count(Orders.OrderID):cnt, sum(Orders.Amount):sum)

除了文件，Pandas和SPL也可以解析来自RESTful/WebService的多层数据，区别在于Pandas的语言整体性不佳，没有提供内置的RESTful/WebService接口，必须引入第三方类库。其中一种写法：

import requests
resp=requests.get(url="http://127.0.0.1:6868/api/emp_orders")
JsonOBJ=resp.json()

SPL整体性较好，原生支持多层数据和RESTful/WebService：

=json(httpfile("http://127.0.0.1:6868/api/emp_orders").read())

结构化数据对象

生成

Pandas的结构化数据对象是DataFrame，不仅可以由数据源生成，也可以直接构造，下面是常见的构造方法：

#用List构造，2个字段4条记录，行号（索引）是默认的0-3，列名是默认的0-1
df=pd.DataFrame([[1,'apple'],[2,'orange'],[3,'banana'],[4,'watermelon']])
#用Array构造
pd.DataFrame(numpy.array([[1,'apple'],[2,'orange'],[3,'banana'],[4,'watermelon']]))
#用Dict构造，列名是指定的one、two
pd.DataFrame({'one':[1,2,3,4],'two':['apple','orange','banana','watermelon']})

DataFrame由多个Series（列或字段对象）组成，下级是原子数据类型或对象（指针）。Pandas没有真正的记录对象，这在某些场景下会带来方便，但也提高了理解难度，编码时缺乏直观感。使用Pandas时，经常用到Python的原生类库和第三类库numpy里的数据对象，包括Set（数学集合）、List（可重复集合）、Tuple（不可变的可重复集合）、Dict（键值对集合）、Array（数组）等，这些数据对象都是集合，容易与Series和DataFrame发生混淆，互相转化困难，对初学者造成了不少困扰。除了外部类库的集合，Series与自家的集合也容易发生混淆，比如分组后的集合DataFrameGroupBy。这些都说明Pandas的语言整体性不强，缺乏来自底层的支持。

SPL的结构化数据对象是序表，同样可以构造生成：

//先构造出结构，再用序列填入数据，行号是0-3，列名是指定的one、two
T=create(one,two).record([1,"apple",2,"orange",3,"banana",4,"watermelon"])
//先准备序列形式的数据（含列名），再构造生成
["one","two",1,"apple",2,"orange",3,"banana",4,"watermelon"].record(2)
//用序表T0的结构作为新序表的结构，再填入数据
T0.create(one,two).record([1,"apple",2,"orange",3,"banana",4,"watermelon"])

序表由多个Record（记录对象）组成，下级是原子数据类型或对象（指针）。序表有真正的记录对象，大多数场景下易于理解，编码直观。Record与单记录序表虽然本质不同，但业务意义相似，容易混淆，为了减少混淆，SPL经过精心设计，使两者的外部用法保持一致，通常不必特意区分。SPL只有两种集合，序列（类似List）和序表，前者是后者的基础，后者是有结构的前者，序表分组后的集合是序列，两者关系清楚泾渭分明转化容易，学习和编码的成本都很低。可以看出来，SPL可以从底层提供语法支持，整体性较好。
访问数据

Pandas DataFrame自带行号（从0开始）、字段号（列号）、字段名（列名），可以直接通过下标或字段名方便地访问记录：

#取行号列表，index相当于行号字段名
list(df.index)
#取第1条记录
df.iloc[1]
#区间取第1-3条记录（左闭右开）
df.iloc[1:4]
#步进（偶数位置）
df.iloc[1::2]
#倒数第2条（从1开始）
df.iloc[-2]
#用记录序号和字段序号取值
df1.iloc[1,0]
#用记录序号和字段名取值
df.loc[1,'two']

SPL序表自带行号（从1开始）、字段号、字段名，可以通过下标和字段名方便地访问记录，这方面SPL和Pandas区别不大，用法都很方便：

//取行号列表，#是行号的字段名
T.(#)
//取第2条记录（可简写为T(2)）
T.m(2)
//区间取第2-4条记录（左闭右闭）
T.m(2:4)
//步进（偶数位置）
T.step(2,2)
//倒数第二条（从1开始）
T.m(-2)
//用记录序号和字段序号取值
T.m(2).#1
//用记录序号和字段名取值
T.m(2).two

行号（下标）的本质是高性能地址索引，除了行号，Pandas和SPL还提供了其他种类的索引，以及对应的查询函数，包括唯一值的哈希索引，有序值的二分查找索引。性能不是本文重点，且两者功能类似，这里就不多说了。

维护数据

修改指定位置的记录。Pandas:

df.loc[4,['NAME','SALARY']]=['aaa',1000]

Pandas没有直接提供修改函数，而是用Series对象取出记录的部分字段，再用List去修改。Series这里表示的是记录，但通常表示列，List通常表示记录，但也可以表示列，这些规则初学者容易混淆。

SPL：

T.modify(5,"aaa":NAME,1000:SALARY)

SPL直接提供了修改函数，符合初学者的常识。当然，SPL也可以取出记录再修改，两种方法各自适合不同的场景。

在指定位置插入新记录。Pandas：

record=pd.DataFrame([[100,"wang","lao","Femal","CA", pd.to_datetime("1999-01-01"), pd.to_datetime("2009-03-04"),"HR",3000]],columns=df.columns)
df = pd.concat([df.loc[:2], record,df.loc[3:]],ignore_index=True)

Pandas没有真正的记录对象，也没有直接提供插入记录的方法，间接实现起来较麻烦，先构造一条单记录的DataFrame，再将原DataFrame按指定位置拆成前后两个DataFrame，最后把三个DataFrame拼起来。很多易忽略的细节也要处理好，否则无法获得理想结果，比如构造记录时要保证字段名与原DataFrame相同，拼接新DataFrame时不能保留原来的行号。

SPL：

T.insert(3,100,"wang","lao","Femal","CA",date("1999-1-1"),date("2009-3-4"),"HR",3000)

SPL对记录比较重视，直接提供了插入记录的方法，代码简洁易于理解。

添加计算列。Pandas：

today = datetime.datetime.today().year
df["Age"] = today-pd.to_datetime(df["BIRTHDAY"]).dt.year
df["Fullname"]=df["NAME"]+ " " +df["SURNAME"]

Pandas没有提供添加计算列的函数，虽然实现起来问题不大，但添加多个列就要处理多次，还是比较麻烦。Pandas的时间函数也不够丰富，计算年龄比较麻烦。

SPL：

T.derive(age(BIRTHDAY):Age, NAME+""+SURNAME:Fullname)

SPL提供了添加计算列的函数，一次可以添加多个列，且时间函数更加丰富。

结构化数据计算

计算函数

Pandas内置丰富的库函数，支持多种结构化数据计算，包括：遍历循环apply\map\transform\itertuples\iterrows\iteritems、过滤Filter\query\where\mask、排序sort_values、唯一值unique、分组groupby、聚合agg(max\min\mean\count\median\ std\var\cor)、关联join\merge、合并append\concat、转置transpose、移动窗口rolling、shift整体移行。

Pandas没有专门的函数进行记录集合的交、并、差等运算，只能间接实现，代码比较繁琐。Pandas会为类似的计算提供多个函数，比如过滤，这些函数的主体功能互相覆盖，只是参数约定\输出类型\历史版本不同，学习时要注意区分。

SPL的计算函数也很丰富，包括：遍历循环.()、过滤select、排序sort、唯一值id、分组group、聚合max\min\avg\count\median\top\icount\iterate、关联join、合并conj、转置pivot。

SPL对记录集合的集合运算支持较好，针对来源于同一集合的子集，可使用高性能集合运算函数，包括交集isect、并集union、差集diff，对应的中缀运算符是^、&、\。对于来源不同的集合，可用merge函数搭配选项进行集合运算，包括交集@i、并集@u、差集@d。

除了集合运算，SPL还有以下独有的运算函数：分组汇总groups、外键切换switch、有序关联joinx、有序归并merge、迭代循环iterate、枚举分组enum、对齐分组align、计算序号pselect\psort\ptop\pmax\pmin。Pandas没有直接提供这些函数，需要硬编码实现。

有大量功能类似的函数时，Pandas要用不同的名字或者参数进行区分，使用不太方便。而SPL提供了非常独特的函数选项，使功能相似的函数可以共用一个函数名，只用函数选项区分差别。比如，select函数的基本功能是过滤，如果只过滤出符合条件的第1条记录，可使用选项@1：

T.select@1(Amount>1000)

对有序数据用二分法进行快速过滤，使用@b：

T.select@b(Amount>1000)

函数选项还可以组合搭配，比如：

Orders.select@1b(Amount>1000)

结构化运算函数的参数有些很复杂，Pandas需要用选项或参数名来区分复杂的参数，这样易于记忆和理解，但代码难免冗长，也使语法结构不统一。比如左关联：

pd.merge(Orders, Employees, left_on='SellerId', right_on='EId', how='left', suffixes=['_o','_e'])

SPL使用层次参数简化了复杂参数的表达，即通过分号、逗号、冒号自高而低将参数分为三层，不过这样会增加一些记忆难度。同样左关联：

join@1(Orders:o,SellerId ; Employees:e,EId)

层次参数的表达能力也很强，比如join函数里的分号用于区分顶层参数序表，如果进行多表关联，只要继续加分号就可以。Pandas参数的表达能力就差多了，merge函数里表示DataFrame的选项只有left和right，因此只能进行两表关联。

Pandas和SPL都提供了足够丰富的计算函数，进行单个函数的基础计算时，区别不算大。但实际工作中的数据准备通常有一定复杂度，需要灵活运用多个函数，且配合原生的语法才能实现，这种情况下，两者的区别就比较明显了。

同期比

先按年、月分组，统计每个月的销售额，再计算每个月比去年同月份的销售额的增长率。Pandas：

sales['y']=sales['ORDERDATE'].dt.year
sales['m']=sales['ORDERDATE'].dt.month
sales_g = sales[['y','m','AMOUNT']].groupby(by=['y','m'],as_index=False)
amount_df = sales_g.sum().sort_values(['m','y'])
yoy = np.zeros(amount_df.values.shape[0])
yoy=(amount_df['AMOUNT']-amount_df['AMOUNT'].shift(1))/amount_df['AMOUNT'].shift(1)
yoy[amount_df['m'].shift(1)!=amount_df['m']]=np.nan
amount_df['yoy']=yoy

分组汇总时，Pandas很难像SQL那样边计算边分组，通常要先追加计算列再分组，这导致代码变复杂。计算同期比时，Pandas用shift函数进行整体移行，从而间接达到访问“上一条记录”的目的，再加上要处理零和空值等问题，整体代码就更长了。

SPL：

	A
2	=sales.groups(year(ORDERDATE):y,month(ORDERDATE):m;sum(AMOUNT):x)
3	=A2.sort(m)
4	=A3.derive(if(m==m[-1],x/x[-1] -1,null):yoy)

分组汇总时，SPL可以像SQL那样边计算边分组，灵活的语法带来简练的代码。计算同期比时，SPL直接用[-1]表示“上一条记录”，且可自动处理数组越界和被零除等问题，整体代码较短。

除了用[x]表示相对位置，SPL还可以用[x:y]表示相对区间，比如股票的3日移动平均值：

T.derive(Amount[-2:0].avg():ma)

Pandas也可以表示相对区间，但由于语言整体性不佳，无法从语法层面直接支持，所以提供了一个新函数rolling。同样计算股票的3日移动平均值：

df['ma']=df['Close'].rolling(3, min_periods=1).mean()

贷款分期

根据多项贷款的基本信息（金额、期数、利息），计算每项贷款每一期的还款明细（当期还款额、当期利息、当期本金、剩余本金）。Pandas：

loan_data = ......			#省略loan_data的取数过程
loan_data['mrate'] = loan_data['Rate']/(100*12)
loan_data['mpayment'] = loan_data['LoanAmt']*loan_data['mrate']*np.power(1+loan_data['mrate'],loan_data['Term']) \ /(np.power(1+loan_data['mrate'],loan_data['Term'])-1)
loan_term_list = []
for i in range(len(loan_data)):
    loanid = np.tile(loan_data.loc[i]['LoanID'],loan_data.loc[i]['Term'])
    loanamt = np.tile(loan_data.loc[i]['LoanAmt'],loan_data.loc[i]['Term'])
    term = np.tile(loan_data.loc[i]['Term'],loan_data.loc[i]['Term'])


![img](https://img-blog.csdnimg.cn/img_convert/bab9be54d4bfbeaac7ce173f73f4d232.png)
![img](https://img-blog.csdnimg.cn/img_convert/ea9aba16a81c57892a5be254c65d2d25.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

t = []
for i in range(len(loan_data)):
    loanid = np.tile(loan_data.loc[i]['LoanID'],loan_data.loc[i]['Term'])
    loanamt = np.tile(loan_data.loc[i]['LoanAmt'],loan_data.loc[i]['Term'])
    term = np.tile(loan_data.loc[i]['Term'],loan_data.loc[i]['Term'])


[外链图片转存中...(img-f8oUEImF-1714241615740)]
[外链图片转存中...(img-BO1zhiMD-1714241615740)]

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**