Can NumeRe Excel?
If you ask people in your environment for a program that can be used to evaluate data, it is highly likely that Microsoft Excel will be mentioned at least once as a possible solution. Depending on the specific application, this is certainly a valid choice. Beyond the simplest cases, however, the question arises as to whether Excel is really suitable for all cases and whether it might not make more sense to work directly with NumeRe.
But can NumeRe do what Excel can do?
In this article, we will prepare a typical example in Excel and NumeRe and compare the various steps. We will not use any overly complex operations. However, Excel professionals who want to take a look at NumeRe are perhaps in the right place for this very reason. Although we will include parts of the code in the text, the complete solution can also be found below.
This article is inspired from a blog article by Appsilon: https://www.appsilon.com/post/excel-functions-in-r
Creating a Data Set
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).
Data Processing - Formulas and Expressions
Simple Statistics
We start with a simple use case: simple statistics of the salary column. We want to determine the range of monthly salaries and calculate the mean, standard deviation and median.
Of course, it makes no sense to calculate a standard deviation of 5 values, but we will do this here for the sake of illustration.Excel
This can be done quickly in Excel using the following functions (which may have different names depending on the language set and whose arguments can be used to directly see the advantage of formatting as a table):
=MIN(Table1[Salary])
=AVERAGE(Table1[Salary])
=MEDIAN(Table1[Salary])
=MAX(Table1[Salary])
=STDEV(Table1[Salary])
NumeRe
In NumeRe we can easily get the statistics of a table or column with the keyword stats:
stats xlstable(:, "Salary")
Alternatively, there are of course dedicated functions for all central statistical values, such as min(xlstable(:, "Salary")), which can then also be converted into a nice table display.
Both solutions are shown in direct succession on the right. This shows that stats is primarily suitable for a quick insight into the data and that targeted statistics are more likely to be calculated with the dedicated functions.
String Concatenation
Concatenating character strings occurs relatively frequently in data evaluations, so let's take a look at how this is implemented using the simple example of composing employee names from first and last names.
Excel
In Excel, you can either use a function such as CONCAT() or the & operator directly. Within the table, the corresponding formula then looks like this:
=[@[First Name]] & " " & [@[Last Name]]
The result is shown below.
NumeRe
NumeRe uses the + operator to concatenate strings, otherwise the syntax hardly differs:
xlstable(:, "Full Name") = xlstable(:, "First Name") + " " + xlstable(:, "Last Name")
Conditions and Logical Expressions
Applying logical conditions to the data is important in many cases in order to make dependencies and correlations visible. We want to determine which of the employees in the table receive a higher salary than the monthly average salary.
Excel
In Excel, use the IF() function as follows:
=IF([@Salary] > AVERAGE([Salary]), "Y", "N")
The result is again shown below. If you are satisfied with logical values instead, you can also simply use =[@Salary] > AVERAGE([Salary]) directly.
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";
Character String Processing
As an example of a very simple processing of character strings, we want to determine the initials of the employees from their first and last names.
Excel
To do this, we need the LEFT() function in Excel, the result of which we link again:
=LEFT([@[First Name]],1) & LEFT([@[Last Name]],1)
This gives the following result:
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"));
Date Operations
Alongside string operations, date operations are one of the most common data operations and can also cause the most headaches. However, we want to look at a simple case here and leave aside subtleties such as time zones and daylight saving time.
Excel
To finally determine how many years of experience our employees have accumulated in the meantime, we use the YEAR() and TODAY() functions in Excel (note that the result may look different if you run the code on your computer):
=YEAR(TODAY()) - YEAR([@[Date Joined]])
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);
Data Aggregation und Visualisation
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
Conclusion: Is NumeRe a Good Alternative to Excel and Should You Switch?
To make a long story short: although you can certainly perform almost all typical Excel tasks with NumeRe without any problems, a complete switch to the NumeRe world and at the same time doing without another spreadsheet application does not really make sense in any scenario. NumeRe significantly extends the functionality of Excel and may have many more advantages, which we will discuss in a future article, but it was never intended to be a complete Excel replacement, especially not for the ubiquitous casual Excel user who is used to working a lot with the mouse.
However, if you are looking for ways to solve more complex problems or even automate entire data evaluations, it can still make sense to get started, as there is great potential in the combination of both worlds in particular. Excel power users would be exactly the right candidates for this.