Working with text data

Crandas provides functionalities to work directly with string columns (i.e. columns containing text). In this section we will see how to work with text and what functionalities are available. Furthermore, we will explore more efficient alternatives that can be used when string-specific functions are not necessary.

When working with strings in crandas, the maximum length of the strings is important. Because the length of a string is kept private, all strings are stored on the server as padded strings of the maximum length, and this maximum length is hence known to all parties. When uploading a string column, it is recommended to specify this length manually depending on your data model, to avoid revealing the actual maximum length of your strings [1]. If you don’t, crandas will automatically derive the maximum length from your data.

To specify the length of the string, you can do the following:

import crandas as cd

# 'strings' is a column with maximum length 6
table = cd.DataFrame({"strings": ["a", "bunch", "of", "words"]}, ctype={"strings": "varchar[6]"})

# here, 'strings' is a column with maximum length 5 based on the data;
# crandas will warn the user that the maximum length is revealed
automatic = cd.DataFrame({"strings": ["a", "bunch", "of", "words"]})

Warning

If you specify a maximum length that is smaller than a your data, your data will be truncated to that length, potentially losing information.

[1]

The maximum length of your strings may reveal confidential information. For example, suppose another party has uploaded a list of names of persons, and you have a person of interest with a long name that may or may not be present in the dataset. If the length of this name is longer than the length of all person names, you know that this person is not present in the dataset. To avoid this issue, prior to uploading data, the other party should consider a reasonable maximum length of names of persons, and supply this length manually.

String encoding and efficiency

Crandas supports two types of strings: unicode and ASCII (meaning that all characters in the string have 0 <= ord(ch) <= 127). Certain operations (in particular, comparison, searching, and regular expression matching), are more efficient for ASCII strings.

Added in version 1.8: Support for ASCII.

By default, crandas autodetects whether or not the more efficient ASCII mode can be used. This information (whether or not ASCII mode is used) becomes part of the public metadata of the column, and crandas will give a ColumnBoundDerivedWarning to indicate that the column metadata is derived from the data in the column. See Type detection from data for more information.

Instead of deriving the mode from the data, it is also possible to explicitly specify the ctype varchar[ascii] or varchar[unicode], e.g.:

import crandas as cd

# ASCII autodetected: efficient operations available; warning given
cdf = cd.DataFrame({"a": ["string"]})

# Unicode autodetected: efficient operations not available; warning given
cdf = cd.DataFrame({"a": ["stri\U0001F600ng"]})

# ASCII annotated; efficient operations available; no warning given
cdf = cd.DataFrame({"a": ["string"]}, ctype={"a": "varchar[ascii]"})

# Unicode annotated; efficient operations not available; no warning given
cdf = cd.DataFrame({"a": ["string"]}, ctype={"a": "varchar[unicode]"})

String manipulation

Besides truncating strings, crandas offers the possibility of converting a string into lowercase by using CSeries.lower(). This function will convert any string into lowercase, which can be useful when combining data from different sources, to ensure compatibility.

import crandas as cd

data = cd.DataFrame({"col": ["AAP", "Noot", "Mies", "VuUR", "Vis"]})

data = data.assign(lower_col = data["col"].lower())

Searching for strings

A common operation is to check whether a string is present in a table. If the strings are unique, you can do a join. This is likely the fastest method, and it keeps the search string private.

import crandas as cd

table = cd.DataFrame({"s": ["a", "bunch", "of", "strings"]})
search = cd.DataFrame({"needle": ["strings"]})

# This table will either be empty or contain a single matching row
cd.merge(table, search, left_on="s", right_on="needle")

This also works to do private set intersection, i.e. check which strings are present in two tables.

left = cd.DataFrame({"s": ["a", "bunch", "of", "strings"]})
right = cd.DataFrame({"s": ["strings", "bunch"]})

# This table will contain all strings that are present in both tables
cd.merge(left, right, on="s")

If the strings are not unique, you can make the strings unique by using CSeries.groupby().

table = cd.DataFrame({"s": ["to", "be", "or", "not", "to", "be"]})
unique_strings = table.groupby("s").as_table()
search = cd.DataFrame({"needle": ["to"]})
cd.merge(unique_strings, search, left_on="s", right_on="needle")

This does not work if table contains other columns that you also want in the search result. In that case, if the single public search string, you can simply use the equality operator:

table = cd.DataFrame({"s": ["to", "be", "or", "not", "to", "be"]})
# the string to be searched for will be public, as well as the number of matches
matching = table[table["s"] == "not"]

This approach works a bit differently from the join. Although the join is usually faster, the simple equality search may be preferable. To use the latter approach while keeping the search string private, you can upload the search string as a table:

table = cd.DataFrame({"s": ["to", "be", "or", "not", "to", "be"]})
search = cd.DataFrame({"needle": ["not"]})

# this will still reveal the number of matches
matching = table[table["s"] == search["needle"].as_value()]

To use this search approach, rather than the join on the unique values, together with keeping the number of matches private, can work like this:

table = cd.DataFrame({"s": ["to", "be", "or", "not", "to", "be"]})
search = cd.DataFrame({"needle": ["to"]})
matches = table["s"] == search["needle"].as_value()
# the result is a table with either a 1 (at least 1 match found) or 0 (no match)
(matches.as_table()[""].sum(mode="regular") > 0).open()

Substring search and regular expressions

Crandas supports searching within text cells with the CSeries.contains() function, that checks whether a given substring exists in a given string, and via the CSeries.fullmatch() function, that checks whether the string matches a given regular expression.

For searching, the substring to search for can be given as a string (in which case the servers learn which string is searched for); or as a crandas.CSeries (in which case this information will remain hidden). In the latter case, the function CSeriesColRef.as_value() can be used to interpret a single-row column as a value that can be used in function definitions, e.g.:

import crandas as cd

cdf = cd.DataFrame({"haystack": ["abcdefghijk", "lmnopqrstuvw"]})

# Search; substring becomes known to servers
needle = "vwx"
cdf.assign(found=cdf["haystack"].contains(needle)).open()

# Search; substring remains hidden from servers but same for all rows
cneedle = cd.DataFrame({"needle": [needle]})["needle"].as_value()
cdf.assign(needle=cneedle, found=lambda x: x.haystack.contains(x.needle)).open()

# Private search (different substring per row)
cneedles = cd.DataFrame({"needle": ["abcde", "klm"]})
cdf.assign(needle=cneedles["needle"], found=lambda x: x.haystack.contains(x.needle)).open()

For regular expression matching, a regular expression can be given as documented in re.Re. The regular expression can contain references to other columns to require that the (the respetive row of the) given column should occur in the regular expression at the given place:

import crandas as cd
import crandas.re

# Simple example
cdf = cd.DataFrame({"a": ["has a!", "has bb!", "has xx?!"],
                    "b": ["",  "b",  "xxx"],
                    "c": ["a", "c", "c"]})

re = crandas.re.Re(r"has (a|bb).*")
cdf["a"].fullmatch(re, cdf["b"], cdf["c"]).as_table().open()

# Example with substrings via (?1)

cdf = cd.DataFrame({"a": ["has a!", "has bb!", "has xx?!"],
                    "b": ["",  "bb",  "xxx"],
                    "c": ["a", "c", "c"]})

re = crandas.re.Re(r"has ((?1)|(?2)).*")
cdf["a"].fullmatch(re, cdf["b"], cdf["c"]).as_table().open()

Strings and bytes

Whenever there is string data in a table that does not require string functionalities, it might make sense to convert it to bytes before uploading it to the engine, as byte encoding is considerably more efficient.

To specify a list of bytes rather than strings, you would do the following:

string_data = ["AAP", "Noot", "Mies", "VuUR", "Vis"]

# This converts data to bytes
byte_data = [s.encode() for s in string_data]

df = cd.DataFrame({"strings": string_data, "bytes": byte_data})

While strings are more natural, when you upload large databases, using byte columns will make a difference in speed and efficiency.

After learning the things we can do with strings, we will learn how to work with dates in the crandas in the next section.