数据准备脚本：Python Pandas OR esProc SPL？

客官°小女子只卖身不卖艺 2023-09-27 09:37 48阅读 0赞

# 引言 #

做数据分析和人工智能运算前常常需要大量的数据准备工作，也就是把各种数据源以及各种规格的数据整理成统一的格式。因为情况非常复杂多样，很难有某种可视化工具来完成此项工作，常常需要编程才能实现。

业界有很多免费的脚本语言都适合进行数据准备工作，其中Python Pandas具有多种数据源接口和丰富的计算函数，受到众多用户的喜爱；esProc SPL作为一门较新的数据计算语言，在语法灵活性和计算能力方面也很有特色，下面对两者进行多方面的比较。本文重点比较数据的解析、清洗、计算、输出等日常任务，不涉及人工智能等后续应用或高性能计算等特殊场景。

## 语言特征 ##

**编程范式**

Python是通用开发语言，支持多范式编程，包括完整的面向对象和面向函数，但因为大量Python用户不是专业的应用程序员，很少用到这两种现代复杂的编程范式，最常用的反而是古老简单的面向过程编程范式。

SPL专用于结构化数据计算，也支持常见的三种范式。SPL对面向对象的概念进行了大幅简化，有对象的概念，可以用点号访问属性并进行多步骤计算，但没有继承重载这些内容。SPL对函数式编程也进行了简化，其Lambda表达式甚至比SQL更加简单易用，适合非专业应用程序员。

**语言整体性**

Pandas不是Python的原生类库，而是基于numpy开发的第三方类库（numpy本身也是第三方类库）,没有参与Python的统一设计，也无法获得Python的底层支持，导致语言的整体性不佳，基础数据类型尤其是结构化数据对象（DataFrame）的专业性不强，影响编码效率和计算效率。

SPL是原生类库，可以自底向上设计统一的语法、函数、参数、接口，以及基础数据类型尤其是结构化数据对象（序表），语言的整体性更好。

**运行模式**

Python是用C开发的解释型语言，SPL是用Java开发的解释型语言，两者都可以自动推断数据类型，并据此提供了灵活方便的语法。解释型语言的性能一般不如编译型，但SPL内置大量时间复杂度更低的基础运算，结构化计算的性能经常能超过编译型语言。Pandas由于语言整体性较差，其性能不如Python原生类库。

**IDE**

Python和SPL都有图形化的IDE，包括完整的调试功能，便利的结构化数据对象观察功能，直观的代码块/作用域缩进功能。Python采用空格/tab缩进，SPL采用类Excel的表格式缩进。

**学习难度**

Pandas资料丰富，入门的学习难度较低。但如果要深入开发，就必须学习完整的面向对象编程和函数式编程，难度陡然提高。

SPL刻意简化了对象的概念和函数式编程的接口，无论入门学习还是深入开发，难度都不高。但涉及到高性能计算时需要学习较多特有的算法，难度也会提高。

**代码量**

Pandas库函数丰富，实现简单的数据准备任务时只需单独使用自己库函数，代码量较低。但如果想实现较复杂的数据准备任务，就要大量使用Python原生类库和第三方类库，由于Pandas的语言整体性不佳，难度会陡然增加，代码量也水涨船高。

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：

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

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实现相同的功能：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>=T("d:/Orders.csv") </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=A1.groups(year(OrderDate);count(1),sum(Amount)) </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>=file("d:/resulS.csv").export@t(A2) </td> 
  </tr> 
 </tbody> 
</table>

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：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>=file("D:\\split.csv").import@si() </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=A1.group((#-1)\3) </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>=A2.new(~(1):OrderID, (line=~(2).split("\t"))(1):Client,line(2):SellerId,line(3):Amount,~(3):OrderDate ) </td> 
  </tr> 
 </tbody> 
</table>

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：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>=file("d:/EO.json").read() </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=json(A1) </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>=A2.conj(Orders) </td> 
  </tr> 
  <tr> 
   <td>4 </td> 
   <td>=A3.select(Amount&gt;1000 &amp;&amp; Amount&lt;=2000 &amp;&amp; like@c(Client,\"*business*\")) </td> 
  </tr> 
 </tbody> 
</table>

序表不仅支持二维数据，也支持多层数据。序表支持真正的泛型，与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：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>=json(file("d:/data.json").read()) </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=A1.groups(Dept,Orders.Client:Clt; count(Orders.OrderID):cnt, sum(Orders.Amount):sum) </td> 
  </tr> 
 </tbody> 
</table>

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进行分组汇总：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>=file("d:\\xml\\emp_orders.xml").read() </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=xml(A1,"xml/row") </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>=A2.groups(Dept,Orders.Client:Clt; count(Orders.OrderID):cnt, sum(Orders.Amount):sum) </td> 
  </tr> 
 </tbody> 
</table>

除了文件，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：

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

分组汇总时，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'])
        rate = np.tile(loan_data.loc[i]['Rate'],loan_data.loc[i]['Term'])
        payment = np.tile(np.array(loan_data.loc[i]['mpayment']),loan_data.loc[i]['Term'])
        interest = np.zeros(len(loanamt))
        principal = np.zeros(len(loanamt))
        principalbalance  = np.zeros(len(loanamt))
        loan_amt = loanamt[0]
        for j in range(len(loanamt)):
            interest[j] = loan_amt*loan_data.loc[i]['mrate']
            principal[j] = payment[j] - interest[j]
            principalbalance[j] = loan_amt - principal[j]
            loan_amt = principalbalance[j]
        loan_data_df = pd.DataFrame(np.transpose(np.array([loanid,loanamt,term,rate,payment,interest,principal,principalbalance])),columns = ['loanid','loanamt','term','rate','payment','interest','principal','principalbalance'])
    loan_term_list.append(loan_data_df)
    loan_term_pay = pd.concat(loan_term_list,ignore_index=True)

上面代码用两层循环作为主体结构，先循环每项贷款，再循环生成该项贷款的每一期，然后将各期明细转置为DataFrame，并追加到事先准备好的list里，继续循环下一项贷款，循环结束后将list里的多个小DataFrame合并为一个大DataFrame。业务逻辑是比较清晰的，就是按公式计算各项数据项，但因为两层循环的结构比较复杂，数据类型的转换比较麻烦，导致代码显得冗长。

SPL：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>//省略loan_data的取数过程 </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=loan_data.derive(Rate/100/12:mRate,LoanAmt*mRate*power((1+mRate),Term)/(power((1+mRate),Term)-1):mPayment) </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>=A2.news((t=LoanAmt,Term);LoanID, LoanAmt, mPayment:payment, Term, Rate, t* mRate:interest, payment-interest:principal, t=t-principal:principlebalance) </td> 
  </tr> 
 </tbody> 
</table>

业务逻辑上SPL和Pandas几乎一样，但因为语言整体性强，两层循环可以用一个news函数实现，也不需要麻烦的类型转换，因此代码大幅简化。

**按工龄分组**

按员工工龄将员工分组，并统计每组的员工人数，有些组之间有重复。Pandas：

#省略员工信息emp的取数过程
    def eval_g(dd:dict,ss:str):
    return eval(ss,dd)
    employed_list=['Within five years','Five to ten years','More than ten years','Over fifteen years']
    employed_str_list=["(s<5)","(s>=5) & (s<10)","(s>=10)","(s>=15)"]
    today=datetime.datetime.today()
    emp['HIREDATE']=pd.to_datetime(emp['HIREDATE'])
    employed=((today-emp['HIREDATE'])/np.timedelta64(1,'Y')).apply(math.floor)
    emp['EMPLOYED']=employed
    dd={'s':emp['EMPLOYED']}
    group_cond = []
    for n in range(len(employed_str_list)):
        emp_g = emp.groupby(eval_g(dd,employed_str_list[n]))
    emp_g_index=[index for index in emp_g.size().index]
    if True not in emp_g_index:
        sum_emp=0
    else:
        group=emp_g.get_group(True)
        sum_emp=len(group)
    group_cond.append([employed_list[n],sum_emp])
    group_df=pd.DataFrame(group_cond,columns=['EMPLOYED','NUM'])

Pandas擅长等值分组，也可实现简单的区间枚举分组，遇到本题这种可重复的枚举分组只能硬编码实现，大概过程：循环分组条件，转为等值分组解决问题，处理分组子集，最后合并结果。此外，Pandas没有计算工龄的函数，也要手工实现。

SPL：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
   <td>B </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>/省略员工信息emp的取数过程 </td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>[?&lt;5,?&gt;=5 &amp;&amp; ?&lt;10,?&gt;=10,?&gt;=15] </td> 
   <td>/条件 </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>[Within five years,Five to ten years, More than ten years, Over fifteen years] </td> 
   <td>/组名 </td> 
  </tr> 
  <tr> 
   <td>4 </td> 
   <td>=emp.derive(age(HIREDATE):EMPLOYED) </td> 
   <td>/计算工龄 </td> 
  </tr> 
  <tr> 
   <td>5 </td> 
   <td>=A4.enum@r(A2, EMPLOYED).new(A3(#):EMPLOYED,~.len():NUM) </td> 
   <td>/枚举分组 </td> 
  </tr> 
 </tbody> 
</table>

函数enum用于枚举分组，选项@r处理重复分组的情况，再配合SPL高效的表达能力，整体代码比Pandas简短得多。

通过上面的几个例子可以看出来，Pandas适合简单的数据准备场景，遇到复杂些的结构化数据计算，代码就很难写了。SPL语言整体性好，无论简单场景还是复杂计算，代码量都不多。

## 大数据量计算 ##

如果文件或库表的数据量较大（指超出内存，而不是Big Data），最终都要用循环分段的办法来处理，即：每次读取并计算少量数据，再保留本次计算的中间计算结果，循环结束后合并多个中间计算结果（比如过滤），或对合并结果做二次计算（比如分组汇总）。即使是基本的结构化数据计算，数据量大时也很麻烦，如果涉及关联、归并、并集或综合性计算，代码将更加复杂。

**聚合**

Pandas：

chunk_data = pd.read_csv("orders.txt",sep="\t",chunksize=100000)
    total=0
    for chunk in chunk_data:
        total+=chunk['amount'].sum()

对于聚合这种简单的大文件计算，Pandas代码还算简单。打开大文本时，Pandas提供了一个选项chunksize，用来指定每次读取的记录数，之后就可以用循环分段的办法处理大文本，每次读入一段并聚合，再将计算结果累加起来。

SPL：

=file("orders.txt").cursor@tc().total(sum('amount'))

SPL同样采用循环分段的办法处理大文本，但SPL封装了代码细节，提供了方便的游标机制，允许用类似处理小数据量的语法，直观地处理较大的数据量，所以代码里看不到循环累加的过程。

**过滤**

Pandas：

chunk_data = pd.read_csv("d:/orders.txt",sep="\t",chunksize=100000)
    chunk_list = []
    for chunk in chunk_data:
        chunk_list.append(chunk[chunk.state=="New York"])
    res = pd.concat(chunk_list)

Pandas没有提供游标，只能硬编码进行循环分段，每次将部分数据读入内存进行过滤，过滤的结果也存储于内存中。

上面的方法只适合结果集小于内存的场景，如果结果集大于大内存，就要把每次过滤的结果写入文件中，代码变化较大：

chunk_data = pd.read_csv("d:/orders.txt",sep="\t",chunksize=100000)
    isNew=True
    for chunk in chunk_data:
        need_data = chunk[chunk.state=='New York']
        if isNew == True:
            need_data.to_csv("orders_filter.txt",index=None)
            isNew =False
        else:
            need_data.to_csv("orders_filter.txt",index=None,mode='a',header=None)

首次创建文件和后续追加记录不同，代码细节要小心处理，代码难度显著增加。

SPL:

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>=file(d:/orders.txt).cursor@tc() </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=A1.select(state=="New York") </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>=A2.fetch() </td> 
  </tr> 
 </tbody> 
</table>

游标机制隐藏了底层细节，解题难度显著降低，代码量显著缩小。不难看出，SPL语言的整体性较好，因此能够从底层提供游标机制。

结果集大于内存时，只要简单地把A3改为：

=file("orders_filter.txt").export@tc(A2)

得益于游标机制，SPL不必手工区分首次创建文件和后续追加，代码简短得多。

**排序**

pandas：

def parse_type(s):
        if s.isdigit():
            return int(s)
        try:
           res = float(s)
           return res
        except:
           return s
    def pos_by(by,head,sep):
        by_num = 0
        for col in head.split(sep):
            if col.strip()==by:
                break
            else:
                by_num+=1
        return by_num
    def merge_sort(directory,ofile,by,ascending=True,sep=","):
        with open(ofile,'w') as outfile:
            file_list = os.listdir(directory)
            file_chunk = [open(directory+"/"+file,'r') for file in file_list]
            k_row = [file_chunk[i].readline()for i in range(len(file_chunk))]
            by = pos_by(by,k_row[0],sep)
            outfile.write(k_row[0])
        k_row = [file_chunk[i].readline()for i in range(len(file_chunk))]
        k_by = [parse_type(k_row[i].split(sep)[by].strip())for i in range(len(file_chunk))]
        with open(ofile,'a') as outfile:
            while True:
                for i in range(len(k_by)):
                    if i >= len(k_by):
                        break
                    sorted_k_by = sorted(k_by) if ascending else sorted(k_by,reverse=True)
                    if k_by[i] == sorted_k_by[0]:
    		    outfile.write(k_row[i])
                        k_row[i] = file_chunk[i].readline()
    		if not k_row[i]:
                        file_chunk[i].close()
    		    del(file_chunk[i])
                        del(k_row[i])
                        del(k_by[i])
                    else:
                        k_by[i] = parse_type(k_row[i].split(sep)[by].strip())
    		    if len(k_by)==0:
    			break
    def external_sort(file_path,by,ofile,tmp_dir,ascending=True,chunksize=50000,sep=',',usecols=None,index_col=None):
        os.makedirs(tmp_dir,exist_ok=True)
        try:
            data_chunk = pd.read_csv(file_path,sep=sep,usecols=usecols,index_col=index_col,chunksize=chunksize)
            for chunk in data_chunk:
                chunk = chunk.sort_values(by,ascending=ascending)
                chunk.to_csv(tmp_dir+"/"+"chunk"+str(int(time.time()*10**7))+str(uuid.uuid4())+".csv",index=None,sep=sep)
            merge_sort(tmp_dir,ofile=ofile,by=by,ascending=ascending,sep=sep)
        except Exception:
            print(traceback.format_exc())
        finally:
            shutil.rmtree(tmp_dir, ignore_errors=True)
    infile = "D:/orders.txt"
    ofile = "D:/extra_sort_res_py.txt"
    tmp = "D:/tmp"
    external_sort(infile,'amount',ofile,tmp,ascending=True,chunksize=1000000,sep='\t')

将大文件分成多段，每段分别排序，分别写入N个临时文件；再打开N个临时文件，并维持一个N个成员的数组，指向每个临时文件的当前读取位置，初始位置是第一条记录；之后比较该数组对应的N条记录，将最小记录i写入结果文件，并下移i对应的临时文件的当前读取位置；继续比较N条记录，直至排序结束。这是大文件排序时常用的归并算法，实现过程比较复杂，Pandas缺乏方便的游标机制，只能硬编码实现，代码冗长且不易解读。

SPL：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>=file("D:/orders.txt").cursor@tc() </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=A1.sortx(amount) </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>=file("D:/extra_sort_res_py.txt").export@tc(A2) </td> 
  </tr> 
 </tbody> 
</table>

上面同样采用归并法实现大文件排序，由于SPL支持游标机制，复杂的细节被隐藏起来，只要写出简短的代码就能实现。

大数据量计算还有很多种，比如分组汇总、关联、交集等，很多都比排序复杂，比如分组汇总的第一步通常就是大排序，追求效率就要用更复杂的哈希分堆。Pandas的语言整体性差，不支持游标，只能硬编码实现这些计算，难度非常大，至于综合性的大数据量计算，基本就不用考虑Pandas了。SPL语言整体性较好，有方便的游标机制，代码都不难写，比如大结果集的分组汇总：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>=file(file_path).cursor@tc() </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=A1.groupx(key;sum(coli):total) </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>=file(out_file).export@tc(A2) </td> 
  </tr> 
 </tbody> 
</table>

综合性的，计算每种商品销售额最大的3笔订单：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A </td> 
  </tr> 
  <tr> 
   <td>1 </td> 
   <td>=file(file_path).cursor@tc() </td> 
  </tr> 
  <tr> 
   <td>2 </td> 
   <td>=A1.groups(product;top(3; -amt):three) </td> 
  </tr> 
  <tr> 
   <td>3 </td> 
   <td>=A2.conj(three) </td> 
  </tr> 
 </tbody> 
</table>

Pandas提供了丰富的库函数，但因为没有参与Python的统一设计，无法获得Python的底层支持，导致语言的整体性不佳，只擅长简单的数据准备工作，不适合一般的场景。esProc SPL的语言整体性较好，结构化数据类型更加专业，可以用简洁直观的代码实现一般的数据准备工作，包括解析不规则的数据源，表达多层数据，进行复杂的结构化数据计算，完成大数据量计算。

## SPL资料 ##

*  [SPL下载][SPL]
 *  [SPL源代码][SPL 1]

[SPL]: http://c.raqsoft.com.cn/article/1595816810031
[SPL 1]: https://github.com/SPLWare/esProc