Skip to content

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.

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. If you don't, crandas will automatically derive the maximum length from your data.

Privacy

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.

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.

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.

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]:

# 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 or uppercase by using .lower() and .upper() respectively. These functions will convert any string into lowercase/uppercase, which can be useful when combining data from different sources, to ensure compatibility. Additionally, it is possible to transform characters in a particular location by using the indices argument.

data = cd.DataFrame({"col": ["UPPER", "Capitalized", "MiXEd"]})

data["lower_col"] = data["col"].lower()
data["upper_col"] = data["col"].upper(indices=[1,3])

To concatenate two strings, simply use the + operator.

table = cd.DataFrame(
    {"first_name": ["John", "Jan"], "last_name": ["Doe", "Jansen"]},
    auto_bounds=True
)
full_names = table["first_name"] + " " + table["last_name"]

It is also possible to substitute characters for other characters or even longer strings by using the .substitute() method.

table = cd.DataFrame({"a": ["PÆR", "á"]}, auto_bounds=True)
# In this example, every instance of "á", "à" or "ä" is replaced by "a"
table["no_accents"] = table["a"].substitute(
    {"a": ["á", "à", "ä"], "AE": ["Æ"]}, output_size=4
)

# We can also substitute one character for multiple, but we then need to
# determine the new string length
# In this case, we know that there is only one appearance of Æ per string,
# so we make the size one longer
table["ae"] = table["no_accents"].substitute({"AE": ["Æ"]}, output_size=4)

In order to strip the leading and trailing spaces crandas offers the functionality .strip().

data = cd.DataFrame({"col": ["  left and right ", "    left", "right   ", "no effect"]})

data["stripped"] = data["col"].strip()

Crandas also allows for python slicing semantics for strings. This means that one can use the common python representation of slices [start:stop:step] for string columns. Note that negative numbers for the slices are currently not supported.

data = cd.DataFrame(
    {"col": ["Roseman Labs", "Encrypt", "link", "and", "analyze", "sensitive", "data"]}
)

data["start_at_three"] = data["col"][3:]
data["start_end_at_five"] = data["col"][:5]
data["skip_one"] = data["col"][::2]
data["all_at_once"] = data["col"][3:5:2]

Opening this code will give us the following table

>>> data.open()
            col start_at_three start_end_at_five skip_one all_at_once
0  Roseman Labs      eman Labs             Rosem   RsmnLb           e
1       Encrypt           rypt             Encry     Ecyt           r
2          link              k              link       ln           k
3           and                              and       ad
4       analyze           lyze             analy     aaye           l
5     sensitive         sitive             sensi    sniie           s
6          data              a              data       dt           a

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.

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 .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.sum(mode="regular") > 0).open()

Substring search and regular expressions

Crandas supports searching within text cells with the .contains() function, that checks whether a given substring exists in a given string, and via the .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 CSeries (in which case this information will remain hidden). In the latter case, the function .as_value() can be used to interpret a single-row column as a value that can be used in function definitions:

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. The regular expression can contain references to other columns to require that the (the respective row of the) given column should occur in the regular expression at the given place:

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"]).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"]).open()

The functionalities listed in this section allow for exact string searches. However, in some cases, strings might not match exactly, for instance due to typos or spelling variants. For this purpose, crandas also offers several functionalities for fuzzy string matching.

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