Working with numeric data

Crandas offers a wide range of tools for working with numeric data. These tools allow you to perform various operations on columns, such as arithmetic operations and aggregations, to analyze and process your data. In this guide, we will provide you with an introduction to working with these various operations. First, we will familiarize you with the particularities of numerical types in the VDL.

Warning

Currently, arithmetic functionality is only available for integers and not for fixed-point numbers. Always make sure that data is of the right format when working with it in the VDL.

Integer data types and conversion

Crandas supports signed and unsigned integer numbers with several bit lengths: 8, 16, 24, 32, 40, 48, 56, 64, 72, 80, 88, and 96. For example, a 16-bit unsigned integer is referred to as an uint16; a 24-bit signed integer is referred to as an int24. It is possible to specify the type of integer as a ctype (see ctypes), e.g.:

import crandas as cd
cdf = cd.DataFrame({"vals": [1,2,3]}, ctype={"vals": "int40"})

Crandas does not allow computations to overflow. For example, the following does not work:

cdf = cd.DataFrame({"vals": [1,2,3]}, ctype={"vals": "int40"})
cdf = cdf.assign(cube=lambda x: x.vals*x.vals*x.vals) # this overflows

In this case, because the column vals is 40-bit, its cube will be 120-bit, so the above computation fails with the error message Integer operation overflow: value does not fit in 96 bits.

Note

In many cases, the cube of a 16-int bit can easily fit in 16 bits; clearly 3^3 = 9 can be expressed with 16 bits (it can be expressed with only five). However, as the values in the VDL are secret-shared it is very costly to preemptively check whether the result of an operation would fit the current type. Therefore, the VDL assumes that it cannot fit, in order to prevent overflow errors.

In this example, it is possible to convert the column vals to another integer type so that the computation of the cube can succeed, e.g.:

cdf = cd.DataFrame({"vals": [1,2,3]}, ctype={"vals": "int40"})
cdf = cdf.assign(vals=lambda x: x.vals.astype("int8"))
cdf = cdf.assign(cube=lambda x: x.vals*x.vals*x.vals) # this works

Note that the above conversion does not actually check that the column values lie in the specified range. If this is not the case, then the result of computing on the column is undefined. To check that the conversion succeeds, the following can be used:

cdf = cd.DataFrame({"vals": [1,2,3]}, ctype={"vals": "int40"})
cdf = cdf.assign(vals=lambda x: x.vals.astype("int8", validate=True))
cdf = cdf.assign(cube=lambda x: x.vals*x.vals*x.vals) # this works

Automatic type detection for integers

If no ctype is specified for an integer column, the smallest integer type is selected that is valid for the column, e.g.:

cdf = cd.DataFrame({"vals": [1,2,3]})

In this example, the column vals will be derived to be of type uint8 (unsigned 8-bit integer) because all values lie in the range from 0 to 255 (inclusive).

Generally, the server is able to execute operations faster for columns that have a smaller range, e.g., filtering on the values of an uint8 column is faster than filtering on the values of a uint24 column.

Because the column type is derived from the data, this potentially leaks information. Crandas warns about this by giving a ColumnBoundDerivedWarning, e.g.: ColumnBoundDerivedWarning: Column "vals" was automatically derived to be of type uint8. For more information, see: Type detection from data.

Fixed-point columns

Added in version 1.7: Fixed-point columns

The VDL also supports the existence of fixed-point columns. Fixed-point is a way to represent non-integer numbers by determining a fixed number of digits after the decimal point. This representation is less flexible than floating-point numbers, which is the usual way these numbers are represented. However, they are more efficient when doing calculations in MPC.

Note

Currently, fixed-point columns can only be used in linear and logistic regression. Arithmetic in fixed-points will be added in a later release.

When uploading a fixed-point column, we do not only need to name the bounds explicitly, but we might also want to determine the precision. The precision parameter determines the number of digits after the decimal point. Default precision is 20 bits, which roughly corresponds to six decimal places.

To specify that we are uploading a fixed-point column, we use the name fp

df = cd.DataFrame({"fixedpoints":[1.2, 0.4, 3]}, ctype={"fixedpoints": "fp[precision=10,min=0,max=3]"})

Warning

When uploading fixed-point data without specifying precision, the VDL will not check the optimal precision for the data. It will simply truncate the data at the default precision (20).

Note that when we open the table, the values are not exact, as some rounding is needed to represent the value in this form.

>>> df.open()
    fixedpoints
    0     1.200195
    1     0.400391
    2     3.000000

Note

As we have a limited space to represent each number, the higher the precision, the smaller the accepted range for the number. Therefore, the highest precision is not necessarily the best.

Boolean columns

As a special case, the ctype bool will be used for columns that contain the boolean values True and False, and for columns for which this ctype is explicitly specified:

cdf = cd.DataFrame({"col1": [True,True,False],   # automatically a bool
                    "col2": [1,0,1]},            # specified using ctype
                ctype={"col2": "bool"})

Certain operations, such as filtering on a column and using binary operators &, |, ^, ~ work only on boolean columns. In the example below, crandas implicitly understands that the column is boolean.

cdf = cd.DataFrame({"vals": [1,2,3], "filter": [1,0,1]})
cdf = cdf[cdf["filter"]]

Note

The Virtual Data Lake will check whether the column has 0s and 1s, if so it will automatically treat it as a boolean (even if it is not).

Inspecting the integer type

In the current version of crandas, it is not possible to check the integer type directly. The feature is coming soon and will be accessible through repr.

Elementwise Operations and Comparisons

In this section, we will demonstrate how to perform basic arithmetic operations on columns using comparisons in crandas. All of these operations output a CSeries with the result of applying each operation to each row of the columns.

df = cd.DataFrame({"a":[1,2,3,4], "b":[5,6,7,8]})

# Equal to
df_equal_to = df["a"] == df["b"]

# Not equal to
df_not_equal_to = df["a"] != df["b"]

# Less than
df_less_than = df["a"] < df["b"]

# Less than or equal to
df_less_than_equal = df["a"] <= df["b"]

# Greater than
df_greater_than = df["a"] > df["b"]

# Greater than or equal to
df_greater_than_equal = df["a"] >= df["b"]

# Add
df_add = df["a"] + df["b"]

# Subtract
df_subtract = df["a"] - df["b"]

# Multiply
df_multiply = df["a"] * df["b"]

# Negate
df_negate = -df["a"]

# Absolute value
df_absolute = abs(df["a"])

# Public integer exponentiation
df_pow = df["a"] ** 2

Beyond these standard common operations, crandas has functions that compares two columns and returns a column with wither the maximum or the minimum in each row:

# Calculate the minimum between "a" and "b" - returns a series
abmin = cd.series_min(df["a"], df["b"])

# Calculate the maximum between "a" and "b" - returns a series
abmax = cd.series_max(df["a"], df["b"])

Aggregation operations

In addition to this, crandas provides several aggregation operations, which can be applied to columns.

df = cd.DataFrame({"a":[1,2,3,4], "b":[5,6,7,8]})

# Sum of a column
a_sum = df["a"].sum()

# Mean of a column
a_mean = df["a"].mean()

# Variance of a column
a_var = df["a"].var()

# Count number of items of a column (not NULL elements)
a_count = df["a"].count()

# Maximum of a column
a_max = df["a"].max()

# Minimum of a column
a_min = df["a"].min()

# Sum of squares of a column
a_sum_of_squares = df["a"].sum_squares()

Maximum and minimum can be applied over a whole table, computing the maximum per (numeric) column.

# Compute the maximum of each (numeric) column in df
df_maximums = df.max()

# Compute the minimum of each (numeric) column in df
df_minimums = df.min()

Note

Note the difference between the elementwise functions that compare the minimum/maximum of two columns (CSeriesColRef.max() / CSeriesColRef.min()) and the ones that compute the minimum/maximum of the elements in one column (crandas.series_min() / crandas.series_max())

Working with Vectors

Crandas allows for vector operations on integer vectors.

df = cd.DataFrame({
    "col1":[[1,2,3],[3,2,4],[2,3,1]],
    "col2":[[2,3,3],[4,2,6],[3,3,3]]
    })

Addition and multiplication work the same way as if we working with scalars:

# Addition
df["col1"] + df["col2"]

# Multiplication
df["col1"] * df["col2"]

Moreover, we also provide vector operations, such as inner product and the sum of the elements of a vector. These two functions (CSeries.inner() and CSeries.vsum()) are implemented as functions of the column, so the notation might be slightly unnatural (in particular for the inner product).

# Assign a new column "inner" with the inner product of "col1" and "col2"
df = df.assign(inner=df["col1"].inner(df["col2"]))

# For each row in col1, add all the elements in that vector
df['col1'].vsum()

Hint

Whenever you want to access the result of a column operation without adding it to a table, use as_table() like this (df["col1"]+df["col2"]).as_table().

Warning

Currently, integer vectors are only supported for integers that fit in int32 or uint32.

Now that we know how to work with numeric columns as well as the operations we can do with them, in the next section we will learn how to combine multiple tables. This feature is one of the most important to allow for data sharing, as it will allow us to consolidate data from multiple sources into one.