MaxCompute:Column operations

ifelse

ifelse applies to Boolean fields. When the condition is True, it returns the first argument; otherwise, it returns the second.

>>> (iris.sepallength > 5).ifelse('gt5', 'lte5').rename('cmp5').head(5)
   cmp5
0   gt5
1  lte5
2  lte5
3  lte5
4  lte5

switch

switch handles multiple conditions, matching each value against a sequence of condition-result pairs and returning the corresponding result. Use the default parameter to specify the fallback value when no condition matches.

>>> iris.sepallength.switch(4.9, 'eq4.9', 5.0, 'eq5.0', default='noeq').rename('equalness').head(5)
   equalness
0       noeq
1      eq4.9
2       noeq
3       noeq
4      eq5.0

Import switch from odps.df to apply it as a standalone function across multiple columns:

>>> from odps.df import switch
>>> switch(iris.sepallength == 4.9, 'eq4.9',   # condition 1: exact match 4.9
...        iris.sepallength == 5.0, 'eq5.0',   # condition 2: exact match 5.0
...        default='noeq').rename('equalness').head(5)
   equalness
0       noeq
1      eq4.9
2       noeq
3       noeq
4      eq5.0

Conditional assignment (PyODPS V0.7.8 and later)

Modify column values based on conditions using assignment syntax:

>>> iris[iris.sepallength > 5, 'cmp5'] = 'gt5'    # set 'gt5' where sepallength > 5
>>> iris[iris.sepallength <= 5, 'cmp5'] = 'lte5'  # set 'lte5' where sepallength <= 5
>>> iris.head(5)
   cmp5
0   gt5
1  lte5
2  lte5
3  lte5
4  lte5

Comparison and range checks

Compare a sequence against another sequence or a scalar:

>>> (iris.sepallength < 5).head(5)
   sepallength
0        False
1         True
2         True
3         True
4        False

Sequential comparisons such as 3 <= iris.sepallength <= 5 are not supported. Use between instead:

>>> iris.sepallength.between(3, 5).head(5)   # inclusive by default
   sepallength
0        False
1         True
2         True
3         True
4         True

To exclude endpoints, set inclusive=False:

>>> iris.sepallength.between(3, 5, inclusive=False).head(5)
   sepallength
0        False
1         True
2         True
3         True
4        False

Datetime arithmetic

PyODPS supports date arithmetic. Use the datetime unit types from odps.df to add or subtract time intervals. Subtracting two datetime columns returns the difference in milliseconds as int64.

>>> from odps.df import day
>>> df
                           a                          b
0 2016-12-06 16:43:12.460001 2016-12-06 17:43:12.460018
1 2016-12-06 16:43:12.460012 2016-12-06 17:43:12.460021
2 2016-12-06 16:43:12.460015 2016-12-06 17:43:12.460022

>>> df.a - day(3)                          # subtract 3 days from column a
                           a
0 2016-12-03 16:43:12.460001
1 2016-12-03 16:43:12.460012
2 2016-12-03 16:43:12.460015

>>> (df.b - df.a).dtype                    # difference between two datetime columns
int64
>>> (df.b - df.a).rename('a')              # result is in milliseconds
         a
0  3600000
1  3600000
2  3600000

The following table lists the available datetime unit types for arithmetic.

explode

Use explode when you need to expand a collection column into individual rows — one row per element. This is useful for downstream aggregations or joins that expect flat row-level data.

For LIST columns, explode returns one column by default. Set pos=True to also return the element's position index (similar to Python's enumerate):

>>> df.a.explode()
    a
0  a1
1  b1
2  c1

>>> df.a.explode(pos=True)                # include position index
   a_pos   a
0      0  a1
1      1  b1
2      0  c1

>>> df.a.explode(['pos', 'value'], pos=True)   # specify column names
   pos value
0    0    a1
1    1    b1
2    0    c1

For DICT columns, explode returns two columns: keys and values.

>>> df.b.explode()
  b_key  b_value
0    a2        0
1    b2        1
2    c2        2
3    d2        3
4    e2        4

>>> df.b.explode(['key', 'value'])         # specify column names
  key  value
0  a2      0
1  b2      1
2  c2      2
3  d2      3
4  e2      4

Use explode with column selection to combine exploded columns with the original columns:

>>> df[df.id, df.a.explode()]
   id   a
0   1  a1
1   1  b1
2   2  c1

>>> df[df.id, df.a.explode(), df.b.explode()]   # cross-product of both collections
   id   a b_key  b_value
0   1  a1    a2        0
1   1  a1    b2        1
2   1  a1    c2        2
3   1  b1    a2        0
4   1  b1    b2        1
5   1  b1    c2        2
6   2  c1    d2        3
7   2  c1    e2        4

LIST-specific methods

In addition to len and explode, LIST columns support the following methods.

DICT-specific methods

In addition to len and explode, DICT columns support the following methods.

isin and notin

isin checks whether each element exists in a specified collection. notin checks the opposite.

>>> iris.sepallength.isin([4.9, 5.1]).rename('sepallength').head(5)
   sepallength
0         True
1         True
2        False
3        False
4        False

cut

cut divides sequence values into discrete segments (bins).

>>> iris.sepallength.cut(range(6), labels=['0-1', '1-2', '2-3', '3-4', '4-5']).rename('sepallength_cut').head(5)
   sepallength_cut
0             None
1              4-5
2              4-5
3              4-5
4              4-5

You can use the include_under and include_over operations to specify the maximum and minimum values, respectively.

>>> labels = ['0-1', '1-2', '2-3', '3-4', '4-5', '5-']
>>> iris.sepallength.cut(range(6), labels=labels, include_over=True).rename('sepallength_cut').head(5)
   sepallength_cut
0               5-
1              4-5
2              4-5
3              4-5
4              4-5