General overview
The Analyst calculator is a powerful tool for data editing and calculation. The calculator includes tools for column creation and deletion, calculation tools with common functions, and data selection:
Create new columns.
Calculate and write values into columns. Choose a column for calculation, insert the expression, and click on the "Calculate" button. Values in the column will be rewritten by new values.
Apply calculations on selected records only. Select the record in the table view, open the context menu (right-click), and choose "Add to selection". Write the expression into the box for selection and press the "Apply selection" button.
Explore columns. Click on the column in the list and press the "Describe column" button.
Check your results before saving. Choose table view and explore table records
Save changes. Click on the "Save" button
You can drag a column from the column list and drop it into one of the expression boxes. Column expression will be correctly transformed into processing form. This behavior is implemented in the unique values list too. Predefined functions are possible to drag and drop only into the calculation expression box.
# | Component | Description |
1 | List of columns | List of columns available for expression construction. For inserting to the expression you can drag the list item and drop into one of the expression boxes or just double-click on the name and the column will be added to the expression with the cursor in the right form. |
2 | New | Opens the dialog for creating a new column in the dataset. |
Delete | Removes selected column from dataset | |
3 | Expression box for calculation | Insert expression for calculation. Calculation of values will be applied on selected records only or on all records. Below the expression box are buttons with operands. |
4 | Expression buttons | Click on the buttons to insert symbols in the expression box |
Clear | Clears expression field | |
5 | Functions | List of predefined functions which are possible to use in expression boxes |
6 | Table view | The table view shows selected records - highlighted by yellow color. Table view includes a context menu with functions for manual selection and deselection of records. |
7 | Clear selection | Clears selected records |
8 | Calculate | Launch calculation |
Useful calculation expressions
Below there are some samples of useful calculation expressions for datasets.
Type | Description | Expression |
Missing solar irradiance components | GHI - Global horizontal irradiance | col["DNI"] * sin(radians(sun_elevation)) + col["DIF"] |
DNI - Direct normal irradiance | (col["GHI"] - col["DIF"]) / sin(radians(sun_elevation)) | |
DIF - Diffuse horizontal irradiance | col["GHI"] - col["DNI"] * sin(radians(sun_elevation)) | |
Kt - Clearness index | col["GHI"] / ETR | |
Ktb | col["DNI"] / ETR | |
D2G - Diffuse to global ratio | col["DIF"] / col["GHI"] | |
Unit conversions | Temperature Celsius to Fahrenheit | col["TEMP"] * 1.8 + 32 |
Temperature Fahrenheit to Celsius | (col["TEMP"] - 32)*(5/9) | |
Irradiation kWh/m2 to kJ/m2 | col["DNI"] * 3600 | |
Irradiation kJ/m2 to kWh/m2 | col["DNI"] / 3600 | |
Datetime selection | From a specific date (year) | col["datetime"] > "2018" |
From a specific date (year) | col["datetime"] > "2017-10" | |
From a specific date (year) | col["datetime"] > "2017-10-30" | |
From a specific date (year) | col["datetime"] > "2017-09-30 10:35:00" | |
Exact date (year)* | col["datetime"] == "2017-09-30" | |
Excludes date* | col["datetime"] != "2017-09-30" | |
Combination using logical operators | (col["datetime"] > "2017-09-30 10:35:00") And (col["datetime"] < "2017-09-30 12:15:00") | |
Combination using logical operators | (col["datetime"] > "2017-09-30") And (col["datetime"] < "2017-10-01") | |
Combination using logical operators | (col["datetime"] > "10:00") And (col["datetime"] < "15:00") | |
Column selection | Combination using logical operators | (( col["GHI"] + col["DNI"] ) > 1200) And (col["TEMP"] < 30.2) |
Combination using logical operators | (( col["GHI"] + col["DNI"] ) > 1200) And (Not( col["TEMP"] > 30.2)) | |
Mixed selection | Combination using logical operators | (Not((col["datetime"] > "09:00") And (col["datetime"] < "15:00"))) Or (col["GHI"] > 800 ) And (col["DNI"] < 500) |
*operations == and != support only date value, not datetime or time value
Copy a column
Example: Copy the column DNI from our dataset.
Open Calculator
Push "New" button under column list
Fill "Name" (eg. new_DNI), parameter type (DNI) and press "OK". For filling in other optional information you have to use Metadata editor.
Choose the new column in combobox (eg. col["new_DNI"])
Fill expression box (eg. col["old_DNI]).
Press "Validate" button
If validate step was without errors press "Calculate" button
For permanent saving of the results press "Save" button
Select records marked with a specific flag
Example: Select only those GHI values whose flag value is 2.
Open Calculator
Double-click on column (eg. flg_GHI)
Type "==" or click on "==" button
Type value 2. Final expression will be: col["flg_GHI"] == 2
Press "Validate" button
If validate step was without errors press "Apply" button
Calculate parameters
Example: Diffuse horizontal irradiance (DIF) data was missing in the original imported file.
Open Calculator
Push "New" button under column list
Fill "Name" (DIF), parameter type (DIF) and press "OK". For filling other optional information you have to use Metadata editor.
In expression combo-box choose column for calculation (col["DIF"])
Type in calculation formula into expression box (col["GHI"] - col["DNI"] * sin(radians(sun_elevation)))
Press "Calculate" for launching calculation.
Press "Save" for saving your calculation into your dataset.
Calculate parameters (only if values are empty)
Example: Calculate values for DIF, only for records with empty values:
Open Calculator
Push "Select data" button
Select DIF in column list and press "Describe column"
Double-click on DIF column (will bring column into expression box), press "==" button and double-click on "NaN" value in unique value list. Final expression looks like this: col["DIF"] == NaN
Press "Apply" and close window after the message informs you about the selection. Now you have selected all records with missing DIF value
In expression combo-box choose column for calculation (col["DIF"])
Type in calculation formula into expression box (col["GHI"] - col["DNI"] * sin(radians(sun_elevation)))
Press "Calculate" for launching calculation. Calculation is applied only for selected records
Press "Save" for saving your calculation into your dataset.