Sequence is the most basic data type of set data in esCalc. Let’s get to know the concept of the sequence and related functions. Then you’ll have a deeper understanding about the computing principle of esCalc.
1. Getting to know sequences
A sequence is a data set sorted in a certain order. The data that constitutes a sequence is known as its members. The sequence is a counterpart of the array in high-level languages. It has more than one member. The members can be any data type, such as string, number, floating point, and date, or be null. A sequence has the general features of a set and can perform set operations.
Members of a sequence are ordered. You can find a member according to its sequence number. Note that in a sequence the sequence number starts with 1.
From esCalc Basics: Formulas, in esCalc a cellset constant can be the sequence type. It is defined by clicking Tool>Cellset constant in the menu bar. After setting the type of a cellset constant as Sequence, double-click the Value cell to edit data of the sequence:
When editing a sequence, you can either add members to it sequentially, or delete or move them. Since members of a sequence can be any data type, the cellset constant will be parsed automatically into the corresponding data type according to the string entered.
The sequence constant can be directly used by an expression. For example:
After you enter A2’s expression, you get the following result:
In A2’s result, sequence members are enclosed by []. You can write a constant sequence by enclosing it with []. For example, you can enter [2,3.14,2015-1-1,Hello] directly in A2. Unlike in an expression, there is no equal sign when defining a constant sequence. The result of entering a constant sequence is the same as the above. When defining a constant sequence directly, do not use quotation marks for string members, if any. esCalc will take care of them after the entry is made. But make sure they are not ambiguous.
You can expand the sequence in an esCalc cell. In the above cellset, for example, select cell A2 and click Operation>Expand in the menu bar to expand the row where A2 resides, as shown below:
That way the row where A2 resides is copied to multiple rows that respectively hold values of members according to the number of members in the sequence. The cellset structure of an esCalc spreadsheet will be discussed in depth through a series of articles about esCalc structure. Details about the expand operation acted on the sequence through the cell where it resides will be covered in Data Manipulation in esCalc: Expand Operation.
In addition to cellset constants and constant sequence that can be directly entered, you can define a sequence right through an expression. Use an equal sign at the beginning because this is an expression, though its format is similar to the constant sequence’s format. You can reference the cells and represent a stretch of cells using the colon between cell names in the expression. When getting values of a stretch of cells, the order is to get them by row (i.e. in the form of “z”). For example:
One thing needing your attention is that an expression requires quoting string type data and using date function to convert the date type data. This is unlike the case in a constant sequence where they are entered nakedly. The results of entering expressions in A2 and B2:
Actually the way to use an expression is quite flexible. Besides enclosing members of a sequence with [], there are many other ways of producing expressions whose results are sequences. These will be discussed in the following sections. Generating a sequence by defining cellset constants is in fact one of these ways.
The use of expressions resulting in sequences has nothing to do with the hierarchy of an esCalc spreadsheet, so the remaining part of the article will omit the hierarchy section on the left side of the esCalc.
2. Common operations on sequences
A sequence is analogous to an array. Basic operations performed on it include calculating its length and getting members from it according to positions. For example:
Based on sequence A, A.len() function calculates the number of members of a sequence, or the sequence length. A(n) gets the nth member from the sequence; you can use a sequence parameter to get corresponding members to form a new sequence, like [4,2,1], which consists of integer members and thus is called an integer sequence. A.m(n) function plays the same role as A(n), but more than that it can use a negative number to locate a member backwards, such as C3 that gets the last sequence member. You’ll get the following results after entering those expressions in cells:
As can be seen, there is not a member in B1’s sequence and its length is 0. This is the empty sequence, which can be defined by a mere [].
On top of calculating sequence length, there are aggregate functions for counting, summarizing, calculating average, and etc. For example:
Based on sequence A above, A.count() calculates the number of non-null members of the sequence; A.sum() calculates the sum of all sequence members; A.avg() calculates the average value of non-null members; A.max() finds the member with the biggest value; and A.min() finds the member with the smallest value. Here is what you’ll get as the result cellset after entering those expressions:
Notice the difference between results in B1 and C1, since it is the non-null members whose number that A.count() calculates. Similarly, B2’s A.avg() calculates the average value by removing the null member.
In esCalc, you can use to function to generate a sequence composed of continuous integers. For example:
to(a,b) generates an integer sequence comprising consecutive integers from a to b; in particular, an integer sequence made up of consecutive integers from 1 to n can be represented by the function’s simplified form to(n). Therefore, you can use A.to(a,b) function to generate a sequence composed of members from the ath one to the bth one in sequence A. The following is the result of entering those expressions:
Based on a sequence, you can use a function to locate a member, filter on members, get the sorted sequence, or lists the ranking of members. For example:
A.pos(x) function locates the first position of member x in sequence A; or all its positions using @a option. A.select(x) function gets members of sequence A with which the computing results of expression x are true. A.sort() function sorts the sequence members. A.rank() function calculates the rank for each member. Here is what you’ll get as the result cellset after entering those expressions:
More functions related to sequence can be found in Function Reference.
3. Set operations on sequences
The sequence with its members in esCalc is a type of set. You can perform set operations on it too.
Based on two sequences A and B, use A|B to calculate concatenation, a sequence got through concatenating members in sequence; use A\B to calculate difference, a sequence got by removing B’s members from A; use A^B to calculate intersection, a sequence composed of common members of A and B; and use A&B to calculate union, which, different from the concatenation, is the combination of members of both sequences without duplicates. Besides, you can multiply sequence A by an integer n to get a sequence consisting of what A’s members repeat n times. Here are the results of operations in the above esCalc spreadsheet:
