Looking for the Best Lightweight Data Analysis Script Tools
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?
Join the DZone community and get the full member experience.Join For Free
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.
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:
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:
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.
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:
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:
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:
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:
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:
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:
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):
Published at DZone with permission of Jerry Cruise . See the original article here.
Opinions expressed by DZone contributors are their own.