Basic table operations¶
This section will cover essential basic functionality in crandas, including inspecting, assigning new columns, computing with columns, boolean logic, and manipulating column names.
Inspecting CDataFrames¶
While you cannot normally access the data stored in a CDataFrame, you can use the CDataFrame.describe() method to inspect a CDataFrame to get summary statistics of the numeric data.
import crandas as cd
df = cd.DataFrame({'A': [1, 2, 3, 4, 5], 'B': [-1, -1, -1, -1, -1]})
>>> print(df.describe())
A B
0 type integer integer
1 count 5 5
2 mean 3.0 -1.0
3 std 1.581139 0.0
4 min 1 -1
5 max 5 -1
See Working with numeric data for details
Assigning new columns¶
Assigning new columns to an existing table unfortunately is not as simple as it would be in pandas.
Using df["new_col"] = [5,4,3,2,1] will not work in crandas.
To add a column to a CDataFrame, we use the CDataFrame.assign() method.
df = cd.DataFrame({"col1":[1,2,3,4,5], "col2": [6,7,8,9,10]})
# Create a new column called 'col3' which is equal to 'col1' - 1
df = df.assign(col3=df["col1"] - 1)
# Create a 2 new columns: "col4" which is equal to 'col1' + 1, and "col5" which is equal to "col2" - 1
df = df.assign(col4=lambda x: x.col1 + 1, col5=lambda x: x.col2 - 1)
>>> print(df.open())
col1 col2 col3 col4 col5
0 1 6 0 2 5
1 2 7 1 3 6
2 3 8 2 4 7
3 4 9 3 5 8
4 5 10 4 6 9
Computing with columns¶
Given a column, we can check whether its values are equal, greater (or equal) than or less (or equal) than a given value.
df = cd.DataFrame({
"id": [1,2,3,4,5],
"is_member": [0,1,1,0,1],
"height": [175,162,151,160,180]
}
)
#We check who has id=2
id_is2 = df["id"] == 2
>>> print(id_is2.as_table().open())
0 0
1 1
2 0
3 0
4 0
#We check which rows hold the information of someone who is 160cm or taller
at_least160 = df["height"] >= 160
>>> print(at_least160.as_table().open())
0 1
1 1
2 0
3 1
4 1
Of course, we probably want to do more than just creating this column. We can also filter based on this value (Find out more about filtering in Selecting data):
df = cd.DataFrame({
"id": [1,2,3,4,5],
"is_member": [0,1,1,0,1],
"height": [175,162,151,160,180]
}
)
# Filter the df such that only members are included (using ==)
member = df[df["is_member"] == 1]
# Print the mean member height
print(member['height'].mean())
# For not_member we can change '==' to '!='
not_member = df[df['is_member'] != 1]
# Print the mean not_member height
print(not_member['height'].mean())
Note
You can also use boolean operations over columns, like (df['is_member'] > 3) & (df['height'] < 160). We support and &, or | and xor ^.
Conditionals over columns¶
Added in version 1.15: Addition of where(), when().then() as well as conditionals over tables and support for null values.
Once you are able to check whether a column fulfills a certain property, you might want to create a new column with a value based on that.
Crandas has multiple ways to make this happen.
We can use the CSeries.if_else() method to create simple conditionals.
df = cd.DataFrame({
"id": [1,2,3,4,5],
"is_member": [0,1,1,0,1],
"height": [175,162,151,160,180]
}
)
# Create a column that shows the cms above 160 in the person's height or -1 if they are shorter
df = df.assign(cms_above_160=(df["height"] >= 160).if_else(df["height"] - 160, -1))
We can use CSeries.if_else() to make categorical columns out of numerical ones, by nesting the function:
Note
Crandas provides easier ways to create categorical columns, as seen in Categorical data.
# Creates 3 categories for height:
# 0 for below 160, 1 for between 160 and 170 and 2 for above 170
df = df.assign(
height_cats=(df["height"] >= 170).if_else(2, (df["height"] >= 160).if_else(1, 0))
)
We can also use the CSeries.where() method when we want to only keep the value when a condition is met and leave a null value instead.
# This command returns a column where only the heights of members are shown
# Non-member heights are turned to null values
df["height"].where(df["is_member"]==1)
# If we add an else value, we can fill that in whenever the condition is not met
# Here, the height of non-members is set to 175
df["height"].where(df["is_member"]==1,175)
If we want to use multiple nested conditions, this syntax will quickly become hard to parse.
Crandas also provides when().then() syntax similar to polars.
This syntax can be used to more easily represent multiple conditionals and values using the function when():
df = df.assign(
is_it_three=cd.when(df["height"] > 175)
.then("bigger")
.when(df["height"] < 175)
.then("smaller")
.when(df["height"] == 175)
.then("perfect")
)
# The function `cd.when()`` can be used to add multiple conditions
df = df.assign(
val=cd.when(
cd.col("member") == 1, # cd.col() allows us to refer to the column in the context of the table
cd.col("height") > 175, # we can add multiple conditions that refer to different columns
)
.then("tall member")
.otherwise(-1) # .otherwise() gives us the value in the case no condition is met
)
The CSeries.if_else() function can also be applied to a CDataFrame as a whole.
This is useful in situations where multiple columns are determined under the same condition.
For example, consider a table of food products where several columns need to be set based on the food’s type.
Instead of applying CSeries.if_else() on the respective columns, it is also possible make a CDataFrame for each type of food, and select the correct instance using CSeries.if_else():
cdf = cd.DataFrame({"product": ["apple", "crisps", "beer"],
"type": ["fruit", "snack", "alcoholic drink"]})
cdf_fruit = cdf.assign(healthy=1, vat_tariff=0.09) # values for case 1
cdf_snack = cdf.assign(healthy=0, vat_tariff=0.09) # values for case 2
cdf_drink = cdf.assign(healthy=0, vat_tariff=0.21) # values for case 3
# Select cdf_fruit, cdf_snack, or cdf_drink based on cdf["type"]
(cdf["type"]=="fruit").if_else(cdf_fruit,
(cdf["type"]=="snack").if_else(cdf_snack, cdf_drink)).open()
# product type healthy vat_tariff
# 0 apple fruit 1 0.09
# 1 crisps snack 0 0.09
# 2 beer alcoholic drink 0 0.21
Manipulating column names¶
Crandas provides methods to rename, add suffixes, or add prefixes to column names.
The CDataFrame.rename() allows you to rename specific columns by providing a dictionary, where the keys are the current column names, and the values are the new column names.
df = cd.DataFrame({"col1": [1,2,3], "col2": [4,5,6]})
df = df.rename(columns={"col1": "A", "col2": "B"})
>>> print(list(df.columns))
['A','B']
If instead you would like to add a prefix or a suffix to all column names in a CDataFrame then you should use either of the following methods:
CDataFrame.add_suffix()CDataFrame.add_prefix()
df = cd.DataFrame({"col1": [1,2,3], "col2": [4,5,6]})
df = df.add_prefix("_1")
df = df.add_suffix('_2')
>>> print(list(df.columns))
['_1col1_2','_1col2_2']
Hint
Adding prefixes or suffixes to your column names can be useful when merging tables which have columns with the same names.
Now that we know how to work with columns, in the next section we will find out how to select specific rows and how to filter based on values found in the data.
Return value modes¶
Crandas has different return value modes. By default, the result of a computation (e.g. an aggregation such as CSeriesColRef.mean() or CSeriesColRef.sum()) is directly
disclosed to the user. However, it is also possible to keep the value secret, and only use it as an intermediate result to compute the final outcome. This can be done using mode="regular":
max1 = table1["col1"].max(mode="regular")
max2 = table2["col1"].max(mode="regular")
>>> max1
<crandas.crandas.ReturnValue at 0x7fa019c94f90>
# compute the product of the maximum elements in table1 and table2, without opening
# either of them
product_max = max1 * max2
>>> product_max.open()[0]
12
# filter on the elements having the maximum value, without revealing this value
table1[table1['col1']==table1['col1'].max(mode='regular')]