Collection-MaxCompute(MaxCompute)-阿里云帮助中心

A CollectionExpr represents all operations on two-dimensional datasets within a DataFrame. You can view a CollectionExpr as a MaxCompute table or a spreadsheet. A DataFrame object is a special case of a CollectionExpr. CollectionExpr includes many operations for two-dimensional datasets, such as column operations, filtering, and transformations.

Prerequisites

To work with the examples in this topic, complete the following steps:

Prepare the pyodps_iris sample table. For more information, see DataFrame data processing.
Create a DataFrame. For more information, see Create a DataFrame from a MaxCompute table.

Get the type

You can use dtypes to retrieve the types of all columns in a CollectionExpr. The dtypes method returns a Schema type. The following code provides an example.

print(iris.dtypes)

The following result is returned:

odps.Schema {
  sepallength           float64
  sepalwidth            float64
  petallength           float64
  petalwidth            float64
  name                  string
}

Select, add, or remove columns

Select columns

To select specific columns from a CollectionExpr and create a new dataset, you can use the expr[columns] syntax. The following code provides an example.

print(iris['name', 'sepallength'].head(5))

The following result is returned:

          name  sepallength
0  Iris-setosa          4.9
1  Iris-setosa          4.7
2  Iris-setosa          4.6
3  Iris-setosa          5.0
4  Iris-setosa          5.4

Note

To select only one column, you need to add a comma after the column name or explicitly mark it as a list. For example, use iris[iris.sepallength, ] or iris[[iris.sepallength]]. Otherwise, a Sequence object is returned instead of a Collection.

Delete columns

To exclude specific columns from an existing dataset when you create a new one, you can use the exclude method. The following code provides an example.

print(iris.exclude('sepallength', 'petallength')[:5].head(5))

The following result is returned:

   sepalwidth  petalwidth         name
0         3.0         0.2  Iris-setosa
1         3.2         0.2  Iris-setosa
2         3.1         0.2  Iris-setosa
3         3.6         0.2  Iris-setosa
4         3.9         0.4  Iris-setosa

PyODPS 0.7.2 and later versions support another syntax that lets you directly delete columns from the dataset. The following code provides an example.

del iris['sepallength']
del iris['petallength']
print(iris[:5].head(5))

The following result is returned:

   sepalwidth  petalwidth         name
0         3.0         0.2  Iris-setosa
1         3.2         0.2  Iris-setosa
2         3.1         0.2  Iris-setosa
3         3.6         0.2  Iris-setosa
4         3.9         0.4  Iris-setosa

Add columns

To add a new column that is the result of a transform operation to an existing Collection, you can use the expr[expr, new_sequence] syntax. The new column becomes part of the new Collection.

Example: Add 1 to the sepalwidth column in the iris dataset, rename the new column to sepalwidthplus1, and append it to the end of the dataset to create a new dataset. The following code shows an example:

print(iris[iris, (iris.sepalwidth + 1).rename('sepalwidthplus1')].head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name  \
0          4.9         3.0          1.4         0.2  Iris-setosa   
1          4.7         3.2          1.3         0.2  Iris-setosa   
2          4.6         3.1          1.5         0.2  Iris-setosa   
3          5.0         3.6          1.4         0.2  Iris-setosa   
4          5.4         3.9          1.7         0.4  Iris-setosa   

   sepalwidthplus1  
0              4.0  
1              4.2  
2              4.1  
3              4.6  
4              4.9

When you use the expr[expr, new_sequence] syntax, the transformed column might have the same name as an original column. To merge the column with the original Collection, you must rename the new column. PyODPS 0.7.2 and later versions support appending columns directly to the current dataset. The following code provides an example.

iris['sepalwidthplus1'] = iris.sepalwidth + 1
print(iris.head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name  \
0          4.9         3.0          1.4         0.2  Iris-setosa   
1          4.7         3.2          1.3         0.2  Iris-setosa   
2          4.6         3.1          1.5         0.2  Iris-setosa   
3          5.0         3.6          1.4         0.2  Iris-setosa   
4          5.4         3.9          1.7         0.4  Iris-setosa  

   sepalwidthplus1  
0              4.0  
1              4.2  
2              4.1  
3              4.6  
4              4.9

Add and delete columns at the same time

You can first exclude the original column using the exclude method and then add the transformed new column. This prevents name conflicts. The following code shows an example:

print(iris[iris.exclude('sepalwidth'), iris.sepalwidth * 2].head(5))

Result:

   sepallength  petallength  petalwidth         name  sepalwidth
0          4.9          1.4         0.2  Iris-setosa         6.0
1          4.7          1.3         0.2  Iris-setosa         6.4
2          4.6          1.5         0.2  Iris-setosa         6.2
3          5.0          1.4         0.2  Iris-setosa         7.2
4          5.4          1.7         0.4  Iris-setosa         7.8

PyODPS 0.7.2 and later versions support overwriting columns directly in the current dataset. The following code provides an example.

iris['sepalwidth'] = iris.sepalwidth * 2
print(iris.head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name
0          4.9         6.0          1.4         0.2  Iris-setosa
1          4.7         6.4          1.3         0.2  Iris-setosa
2          4.6         6.2          1.5         0.2  Iris-setosa
3          5.0         7.2          1.4         0.2  Iris-setosa
4          5.4         7.8          1.7         0.4  Iris-setosa

Another way to add and delete columns is to create a new Collection by calling select and passing the columns that you want to select as parameters. To rename a column, you can use a keyword parameter and set the new column name as the parameter name. The following code provides an example.
```
print(iris.select('name', sepalwidthminus1=iris.sepalwidth - 1).head(5))
```
The following result is returned:
```
          name  sepalwidthminus1
0  Iris-setosa               2.0
1  Iris-setosa               2.2
2  Iris-setosa               2.1
3  Iris-setosa               2.6
4  Iris-setosa               2.9
```
You can also pass a lambda expression, which accepts the result of the previous operation as a parameter. When executed, PyODPS inspects these lambda expressions, passes the Collection generated in the previous step, and replaces it with the correct columns. The following code provides an example.
```
print(iris['name', 'petallength'][[lambda x: x.name]].head(5))
```
The following result is returned:
```
          name
0  Iris-setosa
1  Iris-setosa
2  Iris-setosa
3  Iris-setosa
4  Iris-setosa
```

PyODPS 0.7.2 and later versions support conditional assignment of data. The following code provides an example.

iris[iris.sepallength > 5.0, 'sepalwidth'] = iris.sepalwidth * 2
print(iris.head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name
0          4.9         3.0          1.4         0.2  Iris-setosa
1          4.7         3.2          1.3         0.2  Iris-setosa
2          4.6         3.1          1.5         0.2  Iris-setosa
3          5.0         3.6          1.4         0.2  Iris-setosa
4          5.4         7.8          1.7         0.4  Iris-setosa

Add constants and random numbers

Add a constant

DataFrame supports appending a column of constants to a Collection. To append a constant, you can use Scalar. You must manually specify the column name when you add the column. The following code provides an example.

from odps.df import Scalar
print(iris[iris, Scalar(1).rename('id')][:5].head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name  id
0          4.9         3.0          1.4         0.2  Iris-setosa   1
1          4.7         3.2          1.3         0.2  Iris-setosa   1
2          4.6         3.1          1.5         0.2  Iris-setosa   1
3          5.0         3.6          1.4         0.2  Iris-setosa   1
4          5.4         3.9          1.7         0.4  Iris-setosa   1

To specify a column with null values, you can use NullScalar. You must provide the field type. The following code provides an example.

from odps.df import NullScalar
print(iris[iris, NullScalar('float').rename('fid')][:5].head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name   fid
0          4.9         3.0          1.4         0.2  Iris-setosa  None
1          4.7         3.2          1.3         0.2  Iris-setosa  None
2          4.6         3.1          1.5         0.2  Iris-setosa  None
3          5.0         3.6          1.4         0.2  Iris-setosa  None
4          5.4         3.9          1.7         0.4  Iris-setosa  None

A simplified syntax was introduced in PyODPS 0.7.12 and later versions. The following code provides an example.

iris['id'] = 1
print(iris.head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name  id
0          4.9         3.0          1.4         0.2  Iris-setosa   1
1          4.7         3.2          1.3         0.2  Iris-setosa   1
2          4.6         3.1          1.5         0.2  Iris-setosa   1
3          5.0         3.6          1.4         0.2  Iris-setosa   1
4          5.4         3.9          1.7         0.4  Iris-setosa   1

Note that this syntax cannot automatically detect the type of null values. When you add a column of null values, you must still use the following code:

iris['null_col'] = NullScalar('float')
print(iris.head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name null_col
0          4.9         3.0          1.4         0.2  Iris-setosa     None
1          4.7         3.2          1.3         0.2  Iris-setosa     None
2          4.6         3.1          1.5         0.2  Iris-setosa     None
3          5.0         3.6          1.4         0.2  Iris-setosa     None
4          5.4         3.9          1.7         0.4  Iris-setosa     None

Add random numbers

DataFrame also supports adding a column of random numbers to a Collection. This column has a FLOAT type, and its values range from 0 to 1. Each row has a different value. To append a column of random numbers, you can use RandomScalar. The parameter is a random number seed, which is optional. The following code provides an example.

from odps.df import RandomScalar
iris[iris, RandomScalar().rename('rand_val')][:5]

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name  rand_val
0          4.9         3.0          1.4         0.2  Iris-setosa  0.000471
1          4.7         3.2          1.3         0.2  Iris-setosa  0.799520
2          4.6         3.1          1.5         0.2  Iris-setosa  0.834609
3          5.0         3.6          1.4         0.2  Iris-setosa  0.106921
4          5.4         3.9          1.7         0.4  Iris-setosa  0.763442

Filter data

Collection provides a data filtering feature. You can filter data using AND (&), OR (|), NOT (~), the filter method, lambda expressions, or other query methods.

Example 1: Query for data where sepallength is greater than 5.

print(iris[iris.sepallength > 5].head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name
0          5.4         3.9          1.7         0.4  Iris-setosa
1          5.4         3.7          1.5         0.2  Iris-setosa
2          5.8         4.0          1.2         0.2  Iris-setosa
3          5.7         4.4          1.5         0.4  Iris-setosa
4          5.4         3.9          1.3         0.4  Iris-setosa

Example 2: AND (&) condition.

print(iris[(iris.sepallength < 5) & (iris['petallength'] > 1.5)].head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth             name
0          4.8         3.4          1.6         0.2      Iris-setosa
1          4.8         3.4          1.9         0.2      Iris-setosa
2          4.7         3.2          1.6         0.2      Iris-setosa
3          4.8         3.1          1.6         0.2      Iris-setosa
4          4.9         2.4          3.3         1.0  Iris-versicolor

Example 3: OR (|) condition.

print(iris[(iris.sepalwidth < 2.5) | (iris.sepalwidth > 4)].head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth             name
0          5.7         4.4          1.5         0.4      Iris-setosa
1          5.2         4.1          1.5         0.1      Iris-setosa
2          5.5         4.2          1.4         0.2      Iris-setosa
3          4.5         2.3          1.3         0.3      Iris-setosa
4          5.5         2.3          4.0         1.3  Iris-versicolor

Note

You must use & and | for AND and OR conditions. Do not use and or or.

Example 4: The NOT (~) operator.

print(iris[~(iris.sepalwidth > 3)].head(5))

The result is as follows:

   sepallength  sepalwidth  petallength  petalwidth         name
0          4.9         3.0          1.4         0.2  Iris-setosa
1          4.4         2.9          1.4         0.2  Iris-setosa
2          4.8         3.0          1.4         0.1  Iris-setosa
3          4.3         3.0          1.1         0.1  Iris-setosa
4          5.0         3.0          1.6         0.2  Iris-setosa

Example 5: Explicitly call the filter method and provide multiple AND conditions.

print(iris.filter(iris.sepalwidth > 3.5, iris.sepalwidth < 4).head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name
0          5.0         3.6          1.4         0.2  Iris-setosa
1          5.4         3.9          1.7         0.4  Iris-setosa
2          5.4         3.7          1.5         0.2  Iris-setosa
3          5.4         3.9          1.3         0.4  Iris-setosa
4          5.7         3.8          1.7         0.3  Iris-setosa

Example 6: Use a lambda expression for consecutive operations.

print(iris[iris.sepalwidth > 3.8]['name', lambda x: x.sepallength + 1].head(5))

The following result is returned:

          name  sepallength
0  Iris-setosa          6.4
1  Iris-setosa          6.8
2  Iris-setosa          6.7
3  Iris-setosa          6.4
4  Iris-setosa          6.2

Example 7: For a Collection, if it contains a column of the BOOLEAN type, you can use that column directly as a filter condition.

# Query the schema
print(df.dtypes)
# Returned result
odps.Schema {
  a boolean
  b int64
}

# The 'a' column is of the boolean type. Perform a filtering operation.
print(df[df.a])
# Returned result
      a  b
0  True  1
1  True  3

Therefore, when you select a single Sequence from a Collection, only a BOOLEAN column is valid. This means you can perform the following filtering operations on a Collection.

df[df.a, ]       # Select a column.
df[[df.a]]       # Select a column.
df.select(df.a)  # Explicitly select a column.
df[df.a]         # The 'a' column is a boolean column. Perform a filtering operation.
df.a             # Select a single column.
df['a']          # Select a single column.

Example 8: Use the query method from Pandas to filter data with a search statement. In the expression, you can directly use column names such as sepallength. In the search statement, both & and and represent an AND operation, and both | and or represent an OR operation.

print(iris.query("(sepallength < 5) and (petallength > 1.5)").head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth             name
0          4.8         3.4          1.6         0.2      Iris-setosa
1          4.8         3.4          1.9         0.2      Iris-setosa
2          4.7         3.2          1.6         0.2      Iris-setosa
3          4.8         3.1          1.6         0.2      Iris-setosa
4          4.9         2.4          3.3         1.0  Iris-versicolor

If an expression needs to use a local variable, add an @ prefix to the variable.

var = 4
print(iris.query("(sepalwidth < 2.5) | (sepalwidth > @var)").head(5))

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth             name
0          5.7         4.4          1.5         0.4      Iris-setosa
1          5.2         4.1          1.5         0.1      Iris-setosa
2          5.5         4.2          1.4         0.2      Iris-setosa
3          4.5         2.3          1.3         0.3      Iris-setosa
4          5.5         2.3          4.0         1.3  Iris-versicolor

The following table describes the syntax supported by the query method.

Syntax	Description
name	Names without an `@` prefix are treated as column names. Names with a prefix retrieve local variables.
operator	The following operators are supported: `+`, `-`, ``, `/`, `//`, `%`, `*`, `==`, `!=`, `<`, `<=`, `>`, `>=`, `in`, and `not in`.
bool	Boolean operations. `&` and `and` represent AND. `\|` and `or` represent OR.
attribute	Get an object attribute.
index, slice, subscript	Slicing operations.

Parallel multi-row output

For columns of the LIST and MAP types, the explode method converts the column into a multi-row output. You can also use the apply method to generate a multi-row output. To perform operations such as aggregation, you often need to merge this output with columns from the original table. In this case, you can use the parallel multi-row output feature provided by DataFrame. The syntax maps the collection that is generated by the multi-row output function to the column names in the original collection. The following examples show how to create a parallel multi-row output.
- Query the sample data:
```
print(df)
```
  The following result is returned:
```
   id         a             b
0   1  [a1, b1]  [a2, b2, c2]
1   2      [c1]      [d2, e2]
```
- Example 1:
```
print(df[df.id, df.a.explode(), df.b])
```
  The following result is returned:
```
   id   a             b
0   1  a1  [a2, b2, c2]
1   1  b1  [a2, b2, c2]
2   2  c1      [d2, e2]
```
- Example 2:
```
print(df[df.id, df.a.explode(), df.b.explode()])
```
  The following result is returned:
```
   id   a   b
0   1  a1  a2
1   1  a1  b2
2   1  a1  c2
3   1  b1  a2
4   1  b1  b2
5   1  b1  c2
6   2  c1  d2
7   2  c1  e2
```
If a multi-row output method does not produce any output for a specific input, the input row does not appear in the final result by default. To include the row in the result, you can set keep_nulls=True. In this case, the values in parallel with that row are null. The following examples show how this works.
- Query the sample data:
```
print(df)
```
  The following result is returned:
```
   id         a
0   1  [a1, b1]
1   2        []
```
- Example 1:
```
print(df[df.id, df.a.explode()])
```
  The following result is returned:
```
   id   a
0   1  a1
1   1  b1
```
- Example 2:
```
print(df[df.id, df.a.explode(keep_nulls=True)])
```
  The following result is returned:
```
   id     a
0   1    a1
1   1    b1
2   2  None
```
For an example of how to use the explode method to create a parallel multi-row output, see Operations on collection types.

Limit the number of rows

Output the first three rows of data.

print(iris[:3].execute())

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name
0          4.9         3.0          1.4         0.2  Iris-setosa
1          4.7         3.2          1.3         0.2  Iris-setosa
2          4.6         3.1          1.5         0.2  Iris-setosa

For MaxCompute SQL, backend slicing does not support the start or step methods, but it does support the limit method.

print(iris.limit(3).execute())

The following result is returned:

   sepallength  sepalwidth  petallength  petalwidth         name
0          4.9         3.0          1.4         0.2  Iris-setosa
1          4.7         3.2          1.3         0.2  Iris-setosa
2          4.6         3.1          1.5         0.2  Iris-setosa

Note

Slicing operations can be applied only to a Collection, not a Sequence.