Calculator

General overview

Caution: Use this tool carefully to avoid data loss or dataset corruption.

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 the "Calculate" button. New values will rewrite values in the column.   

  • 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 can be dragged and dropped into the calculation expression box.

 

#

Component

Description

1

Columns

List of parameters available for expression construction. For inserting the expression you can drag the list item and drop it 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 columns from the dataset

3

Expression box for calculation

Insert expression for calculation. The calculation will be applied to selected records only or all records if there is no selection.

4

 

Expression buttons

Click on the buttons to insert symbols in the expression box

Clear

Clears expression field

5

Functions

List of predefined functions and constants which can be used in the expression boxes

6

Table view

The table view shows selected records - highlighted by yellow color. The table view includes a context menu with functions for manual selection and deselection of records.

7

Clear selection

Clears selected records

8

Calculate

Start calculation

9

Describe column

Describe column opens and expands the Column description section. All unique values in the column are listed. This can be used to see all existing flag columns.

Values are often used in a calculation or selection expression. Users can drag & drop or double-click the value.

Basic statistics are displayed at the bottom of the Column description section.

(see screenshot below)

10

Switch selection

Invert selection. Selected points are deselected and not selected points are selected.

11

Selected records only

Show/hide unselected rows for better readability.

12

Save

Save changes

13

Close

Close calculator window

14

Add to selection

Manually select white (unselected) rows and add them to selection. Calculation will be applied only to selected rows.

Remove from selection

Manually select yellow (selected) rows and remove them from selection.

Selected rows on the top

This option reorders table view and moves selected rows on the top.

image-20241009-133421.png
Flag column description example

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.

  1. Open Calculator

  2. Push the "New" button under the column list

  3. Fill in "Name" (eg. new_DNI), parameter type (DNI), and press "OK". For filling in other optional information you have to use Metadata editor.

  4. Choose the new column in a combo box (eg. col["new_DNI"])

  5. Fill the expression box (eg. col["old_DNI]).

  6. Press "Validate" button

  7. If validate step was without errors press the "Calculate" button

  8. For permanent saving of the results press the "Save" button

Select records marked with a specific flag

Example: Select only those GHI values whose flag value is 2.

  1. Open Calculator

  2.  Double-click on column (eg. flg_GHI)

  3.  Type "==" or click on "==" button

  4.  Type value 2. Final expression will be: col["flg_GHI"] == 2

  5. Press "Validate" button

  6. If validate step was without errors press the "Apply" button

Calculate parameters

Example: Diffuse horizontal irradiance (DIF) data was missing in the original imported file. 

  1. Open Calculator

  2. Push the "New" button under the column list

  3. Fill in "Name" (DIF), parameter type (DIF), and press "OK". For filling in other optional information you have to use Metadata editor.

  4. In the expression combo-box choose a column for calculation (col["DIF"])

  5. Type in calculation formula into expression box (col["GHI"] - col["DNI"] * sin(radians(sun_elevation)))

  6. Press "Calculate" to launch the calculation.

  7. Press "Save" to 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:

  1. Open Calculator

  2. Push "Select data" button

  3. Select DIF in the column list and press "Describe column"

  4. Double-click on the DIF column (will bring the column into the expression box), press "==" button, and double-click on "NaN" value in a unique value list. Final expression looks like this: col["DIF"] == NaN

  5. Press "Apply" and close the window after the message informs you about the selection. Now you have selected all records with missing DIF value

  6. In the expression combo-box choose a column for calculation (col["DIF"])

  7. Type in calculation formula into expression box (col["GHI"] - col["DNI"] * sin(radians(sun_elevation)))

  8. Press "Calculate" to launch the calculation. Calculation is applied only for selected records

  9. Press "Save" to save your calculation into your dataset.

Functions

Below is description about all available functions and constants.

Function name

Description

Example

NaN

A constant representing “Not a number”, used as an empty value or represents a gap.

col["DIF"] == NaN

sin()

Calculate sine

sin(radians(sun_elevation))

cos()

Calculate cosine

cos(col[“DNI“])

arcsin()

Arc sine

arcsin(col[“DNI“])

arccos()

Arc cosine

arccos(col[“DNI“])

degrees()

Convert radians to degrees

degrees(col[“DNI“])

radians()

Convert degrees to radians

radians(sun_elevation)

abs()

Calculate absolute value

abs(col[“GHI”])

power()

Calculate power to a number

power(col[“DNI”],2)

round()

Round number to a specified number of decimal places

round(clear_sky_DNI - col[“DNI“],2)

clear_sky_GHI

Calculated theoretical curve for GHI. Theoretical curves are not the same as model clear sky curves, theoretical curves are simplified.

clear_sky_GHI - col[“GHI“]

clear_sky_DNI

Calculated theoretical curve for DNI. Theoretical curves are not the same as model clear sky curves, theoretical curves are simplified.

clear_sky_DNI - col[“DNI“]

sun_elevation

Calculated sun elevation in degrees.

sun_elevation

sun_aspect

Calculated sun aspect in degrees.

sun_aspect

sun_azimuth

Calculated sun azimuth in degrees.

sun_azimuth

solar_constant

A solar constant = 1367 W/m2

solar_constant

declination

Calculated declination in degrees.

declination

ETR_normal

Calculated Normal Extraterrestrial Solar Irradiance.

ETR_normal

ETR_horizontal

Calculated Horizontal Extraterrestrial Solar Irradiance.

ETR_horizontal

air_mass

Calculated air mass

air_mass

clearness_index_mod()

Global horizontal modified clearness index

clearness_index_mod(col["GHI"])

clearness_index_dni_mod()

Beam normal modified clearness index

clearness_index_mod(col["DNI"])

albedo()

Calculate albedo from GHI and RHI

albedo(col["GHI"], col["RHI"], minimal_sun_elevation=5)

relative_difference()

Calculate relative difference, e.g. (GHI_1 - GHI_2) / GHI_1 *100

relative_difference(col["GHI_1"],col["GHI_2"])

calculate_GHI()

Calculate GHI from DNI and DIF

calculate_GHI(col["DNI"],col["DIF"])

calculate_DNI()

Calculate DNI from GHI and DIF

calculate_DNI(col["GHI"],col["DIF"])

calculate_DIF()

Calculate DIF from DNI and GHI

calculate_DIF(col["GHI"],col["DNI"])

mod()

Calculate modulo

mod(col[“WD“],360)