Can NumeRe Excel?

Excel

Getting data into Excel is a no-brainer. You can simply type it directly into the cells of a new worksheet and - if you make an effort - format it as a table, as shown on the right. We've probably all done this hundreds of times, so we won't go into it any further here. By the way, formatting as a table has significant advantages and should therefore always be done if possible.

NumeRe

NumeRe, on the other hand, is somewhat more restrictive in terms of how the tables are structured. Values in the cells of individual columns must have the same data type. To create the table, you can use new xlstable(). The values are then written to this column by column using xlstable(:, 1) = {1:5} for the ID, for example, as shown again in detail below. Alternatively, it is of course also possible to write the data row by row.

xlstable(#, :) = {"ID", "First Name", "Last Name", "Age", "Department", "Salary", "Date Joined"};

xlstable(:, "ID") = {1:5};

xlstable(:, "First Name") = {"John", "Jane", "Mark", "Bob", "Susan"};

xlstable(:, "Last Name") = {"Doe", "Dean", "Markson", "Barston", "Cup"};

xlstable(:, "Age") = {31, 33, 27, 41, 39};

xlstable(:, "Department") = {"IT", "Sales", "Marketing", "IT", "IT"};

xlstable(:, "Salary") = {5000, 4500, 5000, 6500, 7000};

xlstable(:, "Date Joined") = to_time("", {"2018-05-27", "2020-08-14", "2021-07-13", "2015-01-10", "2012-09-04"});

xlstable().convert(7, "datetime");

Perhaps a key observation that can be made here is that only values can be stored in the cells of a NumeRe table, not expressions. This is in contrast to the procedure known from Excel. Once you have understood this, you have already successfully overcome the fundamental difference between NumeRe and Excel.

Alternatively, you can also load any existing Excel file directly (note that it must not be open in Excel at the same time). Apart from XLSX files, other file formats such as *.csv, *.txt, *.jdx, *.ods or *.ndat are of course also supported by default:

load "data.xlsx" -totable=xlstable()

Of course, you can also edit the table manually or copy data into it. You can do this by using the edit command on the table:

edit xlstable()

The result finally looks - apart from the additional quotation marks - almost similar to the result in Excel. To see this, you can either type show xlstable() or double-click on the corresponding entry in the variable viewer (located at the bottom right of the window).

NumeRe

NumeRe uses the + operator to concatenate strings, otherwise the syntax hardly differs: 

xlstable(:, "Full Name") = xlstable(:, "First Name") + " " + xlstable(:, "Last Name")

NumeRe

NumeRe can also evaluate logical expressions directly, but the target column must be converted manually.

xlstable(:, "Higher than avg. Salary") = xlstable(:, "Salary") > avg(xlstable(:, "Salary"));

xlstable().convert(ncols, "logical");

To obtain a completely identical result as in Excel, you can also use the more complex ternary operator ? :  operator:

xlstable(:, "Higher than avg. Salary") = xlstable(:, "Salary") > avg(xlstable(:, "Salary")) ? "Y" : "N";

NumeRe

NumeRe can access the first character of a string directly using firstch():

xlstable(:, "Initials") = firstch(xlstable(:, "First Name")) + firstch(xlstable(:, "Last Name"));

If you want to determine the initials from the complete names instead, you can use the following line, for example, which may look a bit complex:

xlstable(:, "Initials") = firstch(textparse(xlstable(:, "Full Name"), "%s %a")) + firstch(textparse(xlstable(:, "Full Name"), "%a %s"));

NumeRe

NumeRe always works with dates in seconds (in UTC+0, to be precise). This must be taken into account when working with time(). As an immediate advantage, the following method gives us the result in proportionate years, which we also round to one decimal place:

xlstable(:, "Years exp.") = round((time() - xlstable(:, "Date Joined")) / (24*3600*365), 1);

As a final step for this article, we will look at the possibility of aggregating data on the basis of logical expressions and generating a simple bar chart from this. The data aggregation is kept very simple; we only want to determine how the monthly salaries are distributed across the individual departments.

Excel

To do this in Excel, we use the SUMIF() function here. To make the function copyable, we refer to the cell to the left of the expression for the comparison value. This results in the formula

=SUMIF(Table1[Department], H9, Table1[Salary])

Creating a bar chart from this result then only requires a few clicks.

NumeRe

In NumeRe, the same task requires a little more effort, but also offers more control over the actual process. First, we create a new table with new department_salaries(). To populate this, we use the table method TAB().indexof() to determine the indices for our comparison values, which we can then use to add up directly using sum(). In addition, we embed the entire logic in a for loop that iterates over the list of unique departments in order to keep the whole thing scalable.

for (sDepartment -> strunique(xlstable(:, "Department")))

idx{} = xlstable().indexof(5, sDepartment);

department_salaries(nrows+1, :) = {sDepartment, sum(xlstable(idx{}, "Salary"))};

endfor -mask

However, it should be noted that the actual operation to compile the information for IT as a single department can also be formulated in a single line. This means that the additional writing effort is rather insignificant compared to Excel.

sum(xlstable(xlstable().indexof(5, "IT"), "Salary"))

To then display the result as a bar chart, just a single additional line of code is required, whereby the bars option is crucial here. Without this, you would get an ordinary scatter plot instead of a bar chart.

plot department_salaries(:, 2) -set xticklabels=department_salaries(:, 1) box bars [:, 0:20'k] ylabel="Salary" xlabel="Department" font=heros