Four programming languages, from installation, debugging, tabular data calculation, and more to see which one business people should learn.

Image by unsplash.com

The most common data in business work is tabular data, such as order records, personnel information, sales contracts, etc., which is called structured data in professional terms. Excel is the most commonly used tool when dealing with tabular data. This is because excel provides easy-to-use formulas and user-friendly operations, and can complete most tasks. But sometimes it will be very tedious to do manual operations with excel when encountering some special and difficult operations, as well as repeated actions or the processing of large quantities of files. In this case, if you can program to solve the problems, you can…

Introduce an easy code to deal with the difficult problems of Excel grouping

Photo by Lukas from Pexels

Excel supports grouping & aggregation on data, as well as offering pivot tables to perform multidimensional analysis. But Excel methods and operations are too simple to handle complicated tasks or manage them conveniently. Such tasks include getting ranks on each subset, and filtering and sorting by aggregates. In this article, we’ll give examples of post-grouping calculations, analyze it and provide SPL code solution. SPL (Structured Process Language) is the language used by esProc, the specialized data computing engine. It’s convenient to use for handling structured computations.

I Intra-grouping sorting


The commonly-used scripting languages include SQL, Python Pandas and esProc. Now let’s examine their capabilities one by one.

Photo by Markus Spiske on Unsplash

The time series data refers to the business data ordered by time. The handling of it involves quarters, months, workdays and weekends, and sometimes, complicated order-based calculations. So you need to choose a scripting language that has enough computing ability. The commonly-used scripting languages include SQL, Python Pandas and esProc. Now let’s examine their capabilities one by one.


As the long-lasting and most widely-used language, SQL has been already at its peak within its model frame. Almost every simple operation has their SQL solution, including the order-based calculations of course.

The calculation of link relative ration is one example. The…

The popular post-SQL scripting tools are Java, Python pandas and esProc. Now let’s look at and examine their scripting abilities.

Photo by Eugen Str on Unsplash

SQL (or the stored procedure) can handle most of the database computations. If the computations are complex or hard to deal in SQL, we use another programming language to read data out of the database to manipulate it. Such a programming language handles the data read and manipulation with a simple script. So we call the process the post-SQL scripting.

The scenarios that SQL is not good at handling include complex set-based operations, order-based operations, associative operations and multi-step computations, etc. Due to SQL’s incomplete set orientation and lack of explicit set data type, it’s almost impossible to reuse the…

How to handle dynamic transposition, transposition with inter-row calculations, and join-based transposition.

Image by author

A transposition is to rotate information from one row or column to another to change the data layout, for the purpose of making observations from a new perspective. Some transposition algorithms are simple, such as row to column, column to row and bidirectional transposition. Others are not so simple, such as dynamic transposition, transposition with inter-row calculations and join-based transposition. All are commonly seen in data analyses and thus worth a study.

Basic transposition

Row-to-column transposition and column-to-row transposition are the simplest. Each is the other’s inverse computation.

  1. Row to column: Below is the grouped sales table. Task: Transpose values (rows) Q1-Q4…

image by unsplash.com

What is a large file? A large file is a file that is too large to be read in at one time because of insufficient computer memory. In this case, direct use of desktop data tools (such as Excel) is powerless, often need to write a program to deal with it. Even if the program is written, a large file must be read in batches for calculation and processing. Finally, the batch processing results need to be properly summarized according to different calculation types, which is much more complicated than the processing of small file. There are many types of…

It’s convenient and efficient to manipulate database data in SQL. It’s natural to load data into the database and handle it in SQL when we trying to compute files. The problem is that data loading itself is not simple. It would be extremely convenient if there was a way to perform SQL query directly on files. Here I’ll bring in such a tool, esProc SPL, list various SQL file query scenarios and offers esProc example programs. …

Examine the “difficult” tasks and try to give alternative solutions

Grouping records by column(s) is a common need for data analyses. Such scenarios include counting employees in each department of a company, calculating the average salary of male and female employees respectively in each department, and calculating the average salary of employees of different ages. Pandas has groupby function to be able to handle most of the grouping tasks conveniently. But there are certain tasks that the function finds it hard to manage. Here let’s examine these “difficult” tasks and try to give alternative solutions.

groupby is one of the most important Pandas functions. It is used to group and…

I Common full division

The following 2019 product sales table records sales details of a product for every salesperson. The task is to list the top three salespeople in terms of sales amount.

Below is the Excel data:

The expected result:

Excel provides convenient methods, including easy to use functions and intuitive buttons and menus, for performing simple computations. But it’s hard for it alone to accomplish complicated computations and certain special ones. So it offers the add-in interface through which an external application can be connected to use their language or script to help Excel handle those computations.

Now I’ll investigate a number of common Excel add-ins and evaluate their computational capabilities.

Excel DNA

One of the earliest Excel add-ins, Excel DNA lets you create dynamic library functions for Excel using C#, F#, VB.net, etc.

First, you need to write a user-defined…

Easily Simplify Data Processing

Products and resources that simplify hard data processing tasks. For more, https://www.linkedin.com/in/witness998/detail/recent-activity/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store