-
Notifications
You must be signed in to change notification settings - Fork 331
Looking for the Best Lightweight Data Analysis Script Tools
A lightweight desktop script tool is a must-have for data analysts. But how do you know which is the most suitable one? Let me walk you through four top script tools to experience and compare their usability, dev efficiency, types of supported data sources, functions for performing structured computations, and, particularly, algorithm implementation performances to find the best one. And esProc SPL is the winner. ${article} for details。
Almost all programming languages can manipulate data. Some are too general to lack functions for performing structured computations, such as C++ and JAVA, which produce lengthy code to deal with daily data analysis scenarios and are more suitable for taking care of major special projects. Some are technically-targeted and too highly-professional for daily analysis work, such as mathematical programming languages MATLAB and R, though they provide functions for structured data processing. My subjects in this article are the lightweight programming languages that are suitable for doing desktop analytic jobs. They are lightweight databases represented by MySQL, Excel VBA, Python pandas and esProc SPL.
Now I’ll scrutinize the pros and cons of each to look at their capabilities.
It’s easy to run a small database, such as HSQLDB, DerbyDB, SQLite or MySQL, on desktop. Here I’ll take MySQL as an example.
The portable version of MySQL is convenient to install and configure. Though an environment configuration problem, like the folder permission issue, can only be solved with the installer version, the user-friendly wizard will make up for the trouble.
MySQL supports executing SQL with its built-in command-line tool, but the interactive user interface is crude. Many turn to a third-party tool, Navicat or Toad, to do the same thing. So the UI design isn’t MySQL’s strength.
The essential strength of a programming language is, of course, the data processing capability. For this, MySQL is intrinsically dependent on SQL to get its ability.
SQL, after nearly 50 years’ evolvement, is close to the limit of its capabilities within its model frame. Almost every basic algorithm has their SQL expression. This significantly lowers the bar for analysts who want to do data processing. In recent years, MySQL began to offer supports for window functions, WITH clause and the stored procedure. That makes it as capable as any large databases. To implement the following algorithm in MySQL, for example:
/*Filtering. emp table stores information of employees in every department*/
select eid, name, deptid, salary from emp where salary between 8000 and 10000 and hireday>='2010-01-01'
/*Sorting*/
select * from emp order by salary
/*Distinct*/
select distinct deptid from emp
/*Grouping aggregation. share table stores the daily closing prices for a certain share*/
select year(sDate),month(sDate),max(price) from share group by year(sDate),month(sDate)
/*Join; dept table stores department information*/
select e.name, e.salary, d.deptname from emp e inner join dept d on e.deptid=d.deptid
/*Windowing; rank employees in each department by their salaries*/
select eid, name, deptid, salary, rank()over( partition by deptid order by salary desc) rk from emp
MySQL handles basic operations really well. But that is not the case with complex operations because SQL isn’t good at handling them.
SQL is not good at implementing the multistep process-mode algorithms. Here one example is to find the department having the most employees and the one with the least employees based on the emp table. Intuitively, there are two steps to get the task done. First, group the table by department and count the employees in each department; second, sort the groups by the number of employees in descending order. Now the first department and the last department are what we need. SQL, however, implements the algorithm by making it a 4-step process. The first step remains the same. Next it calculates the maximum number of employees using max function and finds the corresponding department using a nested query or a join query. Then it finds the department with the least employee with the same method. Finally, combine the results of the second step and the third step using union. The code is as follows:
with tmp as (
select dept, count(*) total from employee group by dept),
deptmax as(
select dept from tmp where total=(
select max(total) from tmp)
),
deptmin as(
select dept from tmp where total=(
select min(total) from tmp)
)
select dept from deptmax
union
select dept from deptmin
It’s unnecessary lengthy.
Considering the time when SQL was invented, it’s understandable that it has certain defects. The order-based calculations are another scenario that SQL is not good at. An example is to find how many consecutive days a certain share rises based on the share table. SQL hasn’t a direct way of expressing the “consecutively rising” concept, so we need to take an extremely roundabout way. First you count the accumulative non-rising days for each transaction date. The transaction dates with same count of non-rising days are consecutive rising days. Then you group records according to whether a date is consecutive rising or not to get the maximum consecutive interval. Even a SQL expert finds it a headache to deal with such an algorithm. And their code solution is hard to read for ordinary users.
select max(consecutive_days)
from (select count(*) consecutive_days
from (select sum(updown_flag) over(order by sdate) no_up_days
from (select sDate,
case when
price>LAG(price) over(order by sDate)
then 0 else 1 end updown_flag
from share) )
group by no_up_days)
Actually this is simpler because of the use of window function. The code is harder to write and read if you use an earlier SQL version.
Another example is to align records by a specified set. The orders table stores records of orders. We need to calculate the amount of large orders (amount >15000) on the current day in an order from Sunday to Saturday. Give a null value to a day without orders. SQL uses pseudo table technique to convert the weekday list to a set of records and then left join the pseudo table to the orders table. The implementation is complicated:
with std as(
select 1 No,'Sun.' name from dual union
select 2 ,'Mon.' from dual union
select 3 ,'Tues.' from dual union
select 4 ,'Wed.' from dual union
select 5 ,'Thur' from dual union
select 6 ,'Fri.' from dual union
select 7 ,'Sat.' from dual
)
select std.No,std.name,data.total from std left join (
select DAYOFWEEK(orders.orderdate) No,sum(amount) total
from orders
where amount>15000
group by DAYOFWEEK(orders.birthday)
) data
on std.No=data.No order by std.No
I can cite many examples of SQL headaches. The language is too old to adapt to our complicated business needs. Though it tries to keep up with the times through a series of patches and upgrades, including WITH clause, the stored procedure and window functions, the frame on which it is based confines its expression.
Besides, SQL is implemented, though not intrinsically, to be internal-oriented. SQL-based databases can compute the data tables inside a database but are hard to read and write data in an external data source.
Yet the first step of data manipulation is data source retrieval and the last of it is result set output in targeted format. One important aspect of evaluating a script tool is its ability to support external data source read/write. Unfortunately MySQL can only read (not including write) one external data source, the CSV files. And the reading process is not simple at all. To import emp.csv file of the standard format into the database, for example, you need 4 steps:
/*Switch to the target database, create a new database table, import file data to the database, create index over the primary key to speed up the import*/
mysql>use testdb;
mysql>create table emp (
-> empid int(10) not null,
-> name varchar(50),
-> deptid int(10),
-> salary float,
-> sex varchar(1),
-> birthday date,
-> hireday date)CHARSET = utf8;
mysql>LOAD DATA INFILE 'd:\data\emp.csv' INTO TABLE emp
->CHARACTER SET utf8
->FIELDS TERMINATED BY ','
->LINES TERMINATED BY '\r\n'
->IGNORE 1 LINES;
mysql>ALTER TABLE emp ADD PRIMARY KEY (empid);
SQL’s closure design, which didn’t take the file retrieval into consideration at the beginning, accounts for the terribly complicated implementation, even though it was later get patched with the file retrieval feature.
A third-party tool, such as Navicat, enables MySQL to support more types of data sources. But essentially they just convert an external data source into a text file and then load it into the MySQL database. The non-native, patch-up method has a lot of drawbacks. Data sources of ancient formats, such as Dbase and Paradox, are best supported but little used. There are very strict requirements for Excel file loading and so successes are rare. The support of JSON only applies to the special two-dimensional format. Actually Navicat doesn’t support almost all common data sources we are using now.
SQL is difficult to debug. This significantly reduces the development speed.
Standard textbook algorithms don’t need debug because they are implemented with only several lines of code. The real-world data manipulation code is complicated, often having about one hundred lines of nested SQL query. Being unable to debug makes it hard to understand and maintain the code, which leads to low performance.
In a nutshell, SQL excels at processing database data with basic operations but falls short in handling data in external data sources and implementing complex algorithms. What SQL doesn’t have has offered opportunities to new script tools that are lightweight and desktop intended for the PC era.
The rise of PCs changed the user group from scientists to ordinary people. Thus Excel, a non-programming desktop data processing tool, emerged and won worldwide popularity. It added a series of add-ins in recent years, including PowerQuery, to expand its support of data source types and strengthen the data processing ability.
That has made it the most powerful data manipulation tools for non-programmers. I’m not exaggerating.
Yet the non-programming advantage soon became a great disadvantage. And VBA was born. VBA targets to create the unlimited data processing ability for Excel by supporting programming.
The issue is how well the target is achieved. As a script tool supporting programming ability, VBA is, theoretically of course, extremely flexible and almighty, particularly its process mode implementation of algorithms and debugging ability. That is radically better than SQL. On the other hand, the language is still general thanks to the lack of special functions for structured computations, though it has them for accessing cells. So it’s painfully complicated to manipulate data in VBA. On many occasions I’d rather use SQL.
Take the file reading as an example, because that is the most basic thing for data processing. To read in order.csv, where the first line is column headers, into the current Excel worksheet, for example, you need to write a very long piece of code:
Const Title As String = "IMPORT CSV TEST"
Sub fMain()
Dim fTextDir As String
Dim pintLen As Integer
Dim pstrValue As String
Dim rowIndex As Integer
Dim i As Integer
rowIndex = 1
pstrValue = ""
pintLen = Len(Title)
fTextDir = "D:/orders.csv"
Open fTextDir For Input As #1
Do While Not EOF(1) ' loop every line
Line Input #1, currLine
If Right(currLine, pintLen) = Title Then
Range(Cells(rowIndex, 1), Cells(rowIndex, 4)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.RowHeight = 27.75
.Font.Name = "Arial"
.Font.Size = 18
.Font.Bold = True
.FormulaR1C1 = Title
.Interior.ColorIndex = 6
.Interior.Pattern = xlSolid
End With
Else
rowDataArr = Split(currLine, ",")
For i = 0 To UBound(rowDataArr)
Cells(rowIndex, i + 1).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.RowHeight = 20
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = False
.FormulaR1C1 = rowDataArr(i)
End With
Next i
End If
rowIndex = rowIndex + 1
Loop
Close #1
End Sub
This is for a file with standard data format. You can imagine the complexity of the code if the data is dirty, such as empty lines, special separators or multiple lines consisting of one record.
Let’s look at PowerQuery. It supports a lot of types of data sources but is convenient only when you load static data according to the wizard. Dynamic loading, however, will be a nightmare. PowerQuery only supports data loading. To output a result set to a target data source, you must turn to a VBA method (but you can output a CSV directly).
It’s even difficult to implement the basic structured algorithms with PowerQuery. To group column A and sum column B in sheet1 for example, you need a large chunk of code. Below is the snippet that omits the data retrieval part:
Public Sub test()
Dim Arr
Dim MyRng As Range
Dim i As Long
Dim Dic As Object
Set MyRng = Range("A1").CurrentRegion
Set MyRng = MyRng.Offset(1).Resize(MyRng.Rows.Count - 1, 2)
Set Dic = CreateObject("Scripting.dictionary")
Arr = MyRng
For i = 1 To UBound(Arr)
If Not Dic.exists(Arr(i, 1)) Then
Dic.Add Arr(i, 1), Arr(i, 2)
Else
Dic.Item(Arr(i, 1)) = Dic.Item(Arr(i, 1)) + Arr(i, 2)
End If
Next i
Sheet2.Range("A1") = "subject"
Sheet2.Range("A2").Resize(Dic.Count) = Application.WorksheetFunction.Transpose(Dic.keys)
Sheet2.Range("B1") = "subtotal"
Sheet2.Range("B2").Resize(Dic.Count) = Application.WorksheetFunction.Transpose(Dic.items)
Set Dic = Nothing
End Sub
In a word, VBA doesn’t truly achieve its target. In fact it is of little value for programmers, especially desktop analysts. That’s really a pity. By focusing on the process mode description, VBA bypasses the implementation of structured algorithms.
Where there is a failure, there is a replacement.
Actually Python is older than VBA. But it was invisible until internet became popular and it jumped on the bandwagon to expand a variety of third-party function libraries using open-source communities. One of the star function libraries is Pandas for data manipulation.
Python is intended to be easy to read and write. It lives up to the initial expectations on the function level. Each function is simple, powerful and easy to use with clear interface. Below are its functions for basic structured computations:
df.query('salary>8000 and salary<10000') #Filering; df is DataFrame type
df.sort_values(by="salary",ascending = True) #Sorting
df.groupby("deptid")['salary'].agg([len, np.sum, np.mean]) #Grouping Aggregation
Relying on the cheap and efficient sources of the open-source communities, Pandas has been able to produce a large rich variety of functions that cover almost all common structured algorithms. Since it inherits Pythons syntax, it’s also easy to call a Pandas function. Thanks to the two merits, Pandas handles basic data manipulation tasks fast and well.
It hits a tie with SQL in the store of functions for structured computations. But it supports much more external data sources than SQL. Below is the read_csv function for retrieving a CSV/TXT file:
import pandas as pd
df=pd.read_csv('D:/emp.csv') #return as a DataFrame
That’s for retrieving a standard format CSV file. By setting parameters, it can handle the non-standard data format, such as the first line being not the column headers and skipping N lines, easily.
Pandas supports loading data from nearly all types of external data source, including databases, JSON files, Excel files and web data, simply through functions. It is also easy to write because its functions have clear interfaces and are easy to call. These are typically Pythons style.
Pandas (Python actually), a standard procedural language, has a merit that SQL hasn’t. It supports common debugging techniques, including breakpoint, step, and jump in/out to quickly eliminate code errors and maintain complicated algorithms easily. It is far more efficient than SQL.
For beginners, Pandas’s rich and easy to use library functions, designed for performing structured computations and accessing external data sources, are attractive. Yet when they dive deep they will see a different picture. The functions that work efficiently and easily as individuals become awkward and difficult when working together to perform daily algorithms. The result is difficult and complicated code.
For example, split_field.csv is a tab-separated text file that has two fields ID and ANOMOALIES. We need to split each ANOMOALIES field value into multiple strings by spaces and combine each string to the corresponding ID field value to generate a new record.
Source data (split_field.csv):
| ID | ANOMALIES |
|---|---|
| 1 | A1 B1 C1 D1 |
| 2 | A2 |
| 3 | A3 B3 C3 |
| 4 | A3 B4 D4 |
| … | … |
Processed data:
| ID | ANOMOLIES |
|---|---|
| 1 | A1 |
| 1 | B1 |
| 1 | C1 |
| 1 | D1 |
| 2 | A2 |
| … | … |
Code to implement the above algorithm:
import pandas as pd
import numpy as np
split_field = pd.read_csv(*'C:\\split_field.csv'*,sep=*'\t'*)
split_dict = split_field.set_index(*'ID'*).T.to_dict(*'list'*)
split_list = []
for key,value in split_dict.items():
anomalies = value[0].split(*' '*)
key_array = np.tile(key,len(anomalies))
split_df = pd.DataFrame(np.array([key_array,anomalies]).T,columns=[*'ID'*,*'ANOMALIES'*])
split_list.append(split_df)
split_field = pd.concat(split_list,ignore_index=True)
print(split_field)
This piece of code is not that simple even though string split is one of Pandas strengths. The code for doing order-based computations will be more difficult. Here’s an example. duty.csv records the daily arrangement of duties. One will work duty for continuous workdays before another takes their shift. The task is to get all the continuous periods of duty shift for each worker.
Source data (duty.csv):
| Date | name |
|---|---|
| 2018-03-01 | Emily |
| 2018-03-02 | Emily |
| 2018-03-04 | Emily |
| 2018-03-04 | Johnson |
| 2018-04-05 | Ashley |
| 2018-03-06 | Emily |
| 2018-03-07 | Emily |
| … | … |
Processed data:
| name | begin | end |
|---|---|---|
| Emily | 2018-03-01 | 2018-03-03 |
| Johnson | 2018-03-04 | 2018-03-04 |
| Ashley | 2018-03-05 | 2018-03-05 |
| Emily | 2018-03-06 | 2018-03-07 |
| … | … | … |
Pandas code to implement the above algorithm:
import pandas as pd
import numpy as np
duty = pd.read_csv('C:\\duty.csv',sep='\t')
name_rec = ''
start = 0
duty_list = []
for i in range(len(duty)):
if name_rec == '':
name_rec = duty['name'][i]
if name_rec != duty['name'][i]:
begin = duty['date'].loc[start:i-1].values[0]
end = duty['date'].loc[start:i-1].values[-1]
duty_list.append([name_rec,begin,end])
start = i
name_rec = duty['name'][i]
begin = duty['date'].loc[start:i].values[0]
end = duty['date'].loc[start:i].values[-1]
duty_list.append([name_rec,begin,end])
duty_b_e = pd.DataFrame(duty_list,columns=['name','begin','end'])
print(duty_b_e)
The two examples show that only individual Pandas functions are easy to read and write but they become difficult to use in handling daily business algorithms. Yet the source data formats in the real world are not always standard and it is impossible to use only the basic algorithms. There are unknowns everywhere anytime. The basic functions must be good at team work and be enough flexible to do data clean, transform and calculation well as needed.
The Pandas problem is due to the loose relationship between Python and its numerous open-source communities where Pandas is a member. Pandas has the right to update its own functions but it is not authorized to change the function invocation syntax. The Python development team also lacks enough resource to take good care of every open-source community to improve the syntax to make functions cooperate conveniently and smoothly.
Pandas also has trouble in handling data (not Big Data) that can’t fit into the memory.
Generally a language handles such data in a recursive way. Read and calculate a small part of the data at a time, store each intermediate result set, and combine those result sets (like filtering) or further process them (like grouping aggregation) to get the final result set. Even a basic structured algorithm, when the data size is large, is not simple, let alone the complicated algorithms such as join, merge, set operations, or the dynamic combination of basic algorithms for doing real-world businesses.
To simplify the implementation of algorithms for handling large amounts of data, it would be better if a script tool provides a mechanism at the low level to enable automatic exchange of memory data and external data top-down and hide computing details bottom-up that let analysts manipulate data with the syntax similar to that used for handling small amounts of data. Python, however, does not equip Pandas with this ability. Desktop analysts have to write the low-level logics on their own. That accounts for incredibly complicated Pandas code for manipulating large amounts of data.
For example, orders.csv stores orders data and we want to find the 3 biggest orders for each salesperson. Here’s the Pandas code:
import pandas as pd
import numpy as np
chunksize=1000000
order_data = pd.read_csv(d:\\orders.csv',iterator=True,chunksize=chunksize)
group_list = []
for chunk in order_data:
for_inter_list = []
top_n = chunk.groupby(by='sellerid',as_index=False)
for index,group in top_n:
group = group.sort_values(by='amount',ascending=False).iloc[:3]
for_inter_list.append(group)
for_inter_df = pd.concat(for_inter_list,ignore_index=True)
group_list.append(for_inter_df)
top_n_gr = pd.concat(group_list,ignore_index=True).groupby(by='sellerid',as_index=False)
top_n_list=[]
for index,group in top_n_gr:
group = group.sort_values(by='amount',ascending=False).iloc[:3]
top_n_list.append(group)
top_3 = pd.concat(top_n_list)
print(top_3)
A great data script tool will try to not only simplify the algorithm expression, but speed up the execution using ways like compressed segmentation and multithreaded processing. Python should have offered these low-level optimization techniques to its open-source communities to ensure stable and uniform abilities of third-party library functions. It didn’t. The open-source communities did it themselves. The joblib community, for instance, achieved the support of multithreaded processing.
So Pandas is faster now, isn’t it?
No! As I said the relationship between Python and the open-source communities is loose. And the relationship between open-source communities is looser. It’s hard for Pandas to work with the third-party multithreading library functions. In theory, the above sample code can be rewritten to use the multiple threads; in practice, faster is impossible!
Another consequence is that this limits Pandas’s access to various data sources.
Pandas supports almost all types of data source because each type of data source has open-source communities and third-party function libraries behind them. MySQL database has three most commonly-used function libraries – sqlalchemy, MySQLdb and PyMySQL. Each database is supported by more than one open-source communities and has multiple functions libraries, and each function library has their own uses.
Professional programmers may believe it’s good to have more choices. But no desktop analysts want to use complex pip command to search for and deploy different function libraries and test their differences. We only want a lightweight desktop script tool that can access data sources using simple and uniform syntax for further data manipulation.
In summary, Pandas has equally noticeable advantages and disadvantages. The advantage is its rich variety of library functions. The disadvantages are complicated implementations of daily algorithms, complex implementations of algorithms for handling large amounts of data, and desktop-analyst-unfriendly.
Is there a lightweight desktop data script tool that is equipped with professional and rich functions for structured computations but that hasn’t Pandas weaknesses? That’s what I’d like to talk about next.
It boats a rich variety of functions for manipulating structured data. For example:
| A | B | |
|---|---|---|
| 1 | =file("d:/emp.csv").impor@tc() | /Load employee data from the file as a table sequence |
| 2 | =A1.select(salary>8000 salary<10000) | /Filter |
| 3 | =A1.sort(-salary) | /Sort in descending order |
| 4 | =A1.groups(deptid;sum(salary),avg(salary)) | /Group aggregate |
| 5 | =A1.group(deptid).conj(~.sort(salary)) | /Windowing; sort employee records in each department by salaries |
| 6 | =connect@l("mssql").query("select * from dept") | /Load department data from the database as a table sequence |
| 7 | =A1.join@i(deptid,A6:deptid,deptname) | /Inner join between file data and database data |
The import() function used in A1 is the method for loading a CSV file with standard format. It can load one with non-standard format, such as the first line not being the column headers and skipping N lines by setting different parameters. And it’s simple.
It’s easy for SPL to read data from and write data to almost all external data sources, including databases (as shown in A6), JSON files, Excel files and web data.
SPL is a standard procedural language. It supports common debugging ways like breakpoint, step, and jump in/out to increase development efficiency.
esProc also has something that Pandas doesn’t have. The biggest one is that esProc is an open-source software maintained by an independent team. It neither relies on the third-party library functions in some open-source communities nor is administered by a so-called superior organization. esProc is able to take a flexible and full perspective to design the syntax. Its functions cooperate as flexible as possible to enable a faster and more convenient solution to real-world business issues.
Here’s an example for arranging the non-standard data format. We need to split ANOMOALIES field of split_field.csv into strings by spaces and transform the original one line into multiple lines. The SPL code is simple:
| A | |
|---|---|
| 1 | =file("C:\split_field.csv").import@t() |
| 2 | =A1.news(ANOMALIES.split("");ID,~:ANOMALIES) |
It’s easy to get all the continuous periods of duty shift for each worker based on duty.csv:
| A | |
|---|---|
| 1 | =file("C:\duty.csv").import@t() |
| 2 | =A1.group@o(name) |
| 3 | =A2.new(name,~.m(1).date:begin,~.m(-1).date:end) |
It’s much simpler than Python. This is truly easy to read and write. With the uniform syntax, SPL can provide the cursor mechanism at the low level to enable desktop analysts to deal with large amounts of data intuitively in the syntax similar to that handling small data. To get the largest three orders for each salesperson based on orders.csv:
| A | |
|---|---|
| 1 | =file("d:\orders.csv").cursor@tc() |
| 2 | =A1.groups(sellerid;top(3; -amount):top3) |
| 3 | =A2.conj(top3) |
The uniform syntax makes it easy for SPL to support the multithreaded processing at the low level. It’s convenient to modify the code to increase performance. You can use multithreading in the above code like this:
| A | B | |
|---|---|---|
| 1 | =file("E:\orders.csv").cursor@tmc(;4) | /Use 4 parallel threads |
| 2 | =A2.groups(sellerid;top(3; -amount):top3) | |
| 3 | =A3.conj(top3) |
The uniform syntax enables SPL to access all databases through one type of interface – Pandas uses different third-party function libraries to do that – and to return result sets as one data type (the table sequence) for any type of data source to calculate them directly. Pandas returns dataframe for certain data sources. For other data sources, it writes the result set as a CSV file and then read it as a dataframe. It’s easy to use and fast to develop because you don’t need to download the third-party library functions any more.
eesProc SPL, in brief, is a desktop-analyst-friendly, lightweight, yet powerful data script tool. It features abundant functions for processing structured data, implements complicated algorithm in a simple and easy way, and greatly simplifies code for phrasing algorithms that manipulate large amounts of data.
SPL Resource： SPL Official Website | SPL Blog | Download esProc SPL | SPL Source Code