Quick start - MaxCompute - Alibaba Cloud Documentation Center

Create and manipulate DataFrame objects for basic data processing in MaxCompute.

Data preparation

The examples use the u.user, u.item, and u.data files, which contain user, movie, and rating data, respectively.

Create tables:

The pyodps_ml_100k_users table for user data.

CREATE TABLE IF NOT EXISTS pyodps_ml_100k_users
(
  user_id    BIGINT COMMENT 'User ID',
  age        BIGINT COMMENT 'Age',
  sex        STRING COMMENT 'Gender',
  occupation STRING COMMENT 'Occupation',
  zip_code   STRING COMMENT 'Zip code'
);

The pyodps_ml_100k_movies table for movie data.

CREATE TABLE IF NOT EXISTS pyodps_ml_100k_movies
(
    movie_id            BIGINT COMMENT 'Movie ID',
    title              STRING COMMENT 'Movie title',
    release_date       STRING COMMENT 'Release date',
    video_release_date STRING COMMENT 'Video release date',
    IMDb_URL           STRING COMMENT 'IMDb URL',
    unknown            TINYINT COMMENT 'Unknown',
    Action             TINYINT COMMENT 'Action',
    Adventure          TINYINT COMMENT 'Adventure',
    Animation          TINYINT COMMENT 'Animation',
    Children           TINYINT COMMENT 'Children',
    Comedy             TINYINT COMMENT 'Comedy',
    Crime              TINYINT COMMENT 'Crime',
    Documentary        TINYINT COMMENT 'Documentary',
    Drama              TINYINT COMMENT 'Drama',
    Fantasy            TINYINT COMMENT 'Fantasy',
    FilmNoir           TINYINT COMMENT 'Film Noir',
    Horror             TINYINT COMMENT 'Horror',
    Musical            TINYINT COMMENT 'Musical',
    Mystery            TINYINT COMMENT 'Mystery',
    Romance            TINYINT COMMENT 'Romance',
    SciFi              TINYINT COMMENT 'Sci-Fi',
    Thriller           TINYINT COMMENT 'Thriller',
    War                TINYINT COMMENT 'War',
    Western            TINYINT COMMENT 'Western'
);

The pyodps_ml_100k_ratings table for rating data.

CREATE TABLE IF NOT EXISTS pyodps_ml_100k_ratings
(
    user_id    BIGINT COMMENT 'User ID',
    movie_id  BIGINT COMMENT 'Movie ID',
    rating    BIGINT COMMENT 'Rating',
    timestamp BIGINT COMMENT 'Timestamp'
)

Use Tunnel Upload to import local data files into MaxCompute tables. For more information about Tunnel operations, see Tunnel Commands.

Tunnel upload -fd | path_to_file/u.user pyodps_ml_100k_users;
Tunnel upload -fd | path_to_file/u.item pyodps_ml_100k_movies;
Tunnel upload -fd | path_to_file/u.data pyodps_ml_100k_ratings;

DataFrame operations

You now have three tables: pyodps_ml_100k_movies (movies), pyodps_ml_100k_users (users), and pyodps_ml_100k_ratings (ratings). The following examples run in IPython.

Note

IPython requires Python. Run pip install IPython to install it, then run ipython to start the interactive environment.

Create an ODPS object.

import os
from odps import ODPS
# Make sure that the ALIBABA_CLOUD_ACCESS_KEY_ID environment variable is set to your Access Key ID,
# and the ALIBABA_CLOUD_ACCESS_KEY_SECRET environment variable is set to your Access Key Secret.
# We recommend that you avoid hardcoding the Access Key ID and Access Key Secret in your code.
o = ODPS(
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_ID'),
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_SECRET'),
    project='your-default-project',
    endpoint='your-end-point',
)

Create a DataFrame object from a table object.

from odps.df import DataFrame
users = DataFrame(o.get_table('pyodps_ml_100k_users'));

Use the dtypes property to view the columns and their data types.

print(users.dtypes)

Output:

odps.Schema {
  user_id             int64
  age                 int64
  sex                 string
  occupation          string
  zip_code            string
}

Use the head method to preview the first N rows.

print(users.head(10))

Output:

   user_id  age  sex     occupation  zip_code
0        1   24    M     technician     85711
1        2   53    F          other     94043
2        3   23    M         writer     32067
3        4   24    M     technician     43537
4        5   33    F          other     15213
5        6   42    M      executive     98101
6        7   57    M  administrator     91344
7        8   36    M  administrator     05201
8        9   29    M        student     01002
9       10   53    M         lawyer     90703

If you do not need all columns, use one of the following methods:

Select a subset of columns.

print(users[['user_id', 'age']].head(5))

Output:

   user_id  age
0        1   24
1        2   53
2        3   23
3        4   24
4        5   33

Exclude specific columns.

print(users.exclude('zip_code', 'age').head(5))

Output:

   user_id  sex  occupation
0        1    M  technician
1        2    F       other
2        3    M      writer
3        4    M  technician
4        5    F       other

Exclude some columns and add new computed columns. For example, create a boolean column named sex_bool that is True if the value of sex is M and False otherwise.

print(users.select(users.exclude('zip_code', 'sex'), sex_bool=users.sex == 'M').head(5))

Output:

   user_id  age  occupation  sex_bool
0        1   24  technician      True
1        2   53       other     False
2        3   23      writer      True
3        4   24  technician      True
4        5   33       other     False

Count male and female users.

print(users.groupby(users.sex).agg(count=users.count()))

Output:

   sex  count
0    F    273
1    M    670

Group users by occupation, sort them in descending order, and view the top 10 occupations by count.

df = users.groupby('occupation').agg(count=users['occupation'].count())
df1 = df.sort(df['count'], ascending=False)
print(df1.head(10))

Output:

      occupation  count
0        student    196
1          other    105
2       educator     95
3  administrator     79
4       engineer     67
5     programmer     66
6      librarian     51
7         writer     45
8      executive     32
9      scientist     31

Alternatively, use the value_counts method for a shorter syntax. The number of rows returned by this method is limited by the options.df.odps.sort.limit configuration. For more information, see Configuration.

df = users.occupation.value_counts()[:10]
print(df.head(10))

Output:

      occupation  count
0        student    196
1          other    105
2       educator     95
3  administrator     79
4       engineer     67
5     programmer     66
6      librarian     51
7         writer     45
8      executive     32
9      scientist     31

Use join to combine the three tables and save the result to a new table named pyodps_ml_100k_lens.

movies = DataFrame(o.get_table('pyodps_ml_100k_movies'))
ratings = DataFrame(o.get_table('pyodps_ml_100k_ratings'))
o.delete_table('pyodps_ml_100k_lens', if_exists=True)
lens = movies.join(ratings).join(users).persist('pyodps_ml_100k_lens')
print(lens.dtypes)

Output:

odps.Schema {
  movie_id                          int64       
  title                             string      
  release_date                      string      
  ideo_release_date                 string      
  imdb_url                          string      
  unknown                           int64       
  action                            int64       
  adventure                         int64       
  animation                         int64       
  children                          int64       
  comedy                            int64       
  crime                             int64       
  documentary                       int64       
  drama                             int64       
  fantasy                           int64       
  filmnoir                          int64       
  horror                            int64       
  musical                           int64       
  mystery                           int64       
  romance                           int64       
  scifi                             int64       
  thriller                          int64       
  war                               int64       
  western                           int64       
  user_id                           int64       
  rating                            int64       
  timestamp                         int64       
  age                               int64       
  sex                               string      
  occupation                        string      
  zip_code                          string      
}

DataFrame data processing

First, download the Iris dataset. The following steps use a PyODPS node in DataWorks. For more information, see Develop a PyODPS 3 task.

Create a test data table.
Create a table in DataWorks:
1. In the Business Flow pane, right-click MaxCompute and select Create Table. In the Create Table dialog box, select a Path, enter a Name, and click Create to go to the table editor.
2. Click DDL in the upper-left corner of the edit page.
3. Enter the following DDL statement, and then run the statement to create the table.
```
CREATE TABLE pyodps_iris (
    sepallength double COMMENT 'sepal length (cm)',
    sepalwidth double COMMENT 'sepal width (cm)',
    petallength double COMMENT 'petal length (cm)',
    petalwidth double COMMENT 'petal width (cm)',
    name string COMMENT 'name'
) ;
```
Upload the test data.
1. Right-click the new table, select Import Data, and click Next to upload the dataset that you downloaded.
  
  In the Import Data dialog box, set Data Import Method to Upload Local File and File Format to CSV. Select the downloaded iris.csv file. Set Delimiter to Comma, Source Charset to GBK, and Start Row to 1. Select Yes for First Row Is Header. After confirming the data preview is correct, click Next.
2. Click Match by Position to import the data.
In the Business Flow pane, right-click MaxCompute, select Create Node, and then select PyODPS 3 to create a PyODPS node for storing and running your code.

Enter the code and click the Run icon . After the code runs, you can view the results in the Run log tab below. The code is as follows:

from odps import ODPS
from odps.df import DataFrame, output
import os
# Make sure that the ALIBABA_CLOUD_ACCESS_KEY_ID environment variable is set to your Access Key ID,
# and the ALIBABA_CLOUD_ACCESS_KEY_SECRET environment variable is set to your Access Key Secret.
# We recommend that you avoid hardcoding the Access Key ID and Access Key Secret in your code.
o = ODPS(
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_ID'),
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_SECRET'),
    project='your-default-project',
    endpoint='your-end-point',
)
# Create a DataFrame object named iris from the MaxCompute table.
iris = DataFrame(o.get_table('pyodps_iris'))
print(iris.head(10))
# Print part of the iris DataFrame.
print(iris.sepallength.head(5))
# Use a custom function to calculate the sum of two columns in the iris DataFrame.
print(iris.apply(lambda row: row.sepallength + row.sepalwidth, axis=1, reduce=True, types='float').rename('sepaladd').head(3))
# Specify the output names and types for the function.
@output(['iris_add', 'iris_sub'], ['float', 'float'])
def handle(row):
    # Use the yield keyword to return multiple output rows.
    yield row.sepallength - row.sepalwidth, row.sepallength + row.sepalwidth
    yield row.petallength - row.petalwidth, row.petallength + row.petalwidth
# Print the first 5 rows of the result. axis=1 indicates a row-by-row operation.
  print(iris.apply(handle, axis=1).head(5))

Results:

# print(iris.head(10))
   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
5          4.6         3.4          1.4         0.3  Iris-setosa
6          5.0         3.4          1.5         0.2  Iris-setosa
7          4.4         2.9          1.4         0.2  Iris-setosa
8          4.9         3.1          1.5         0.1  Iris-setosa
9          5.4         3.7          1.5         0.2  Iris-setosa
# print(iris.sepallength.head(5))
   sepallength
0          4.9
1          4.7
2          4.6
3          5.0
4          5.4
# print(iris.apply(lambda row: row.sepallength + row.sepalwidth, axis=1, reduce=True, types='float').rename('sepaladd').head(3))
   sepaladd
0       7.9
1       7.9
2       7.7
# print(iris.apply(handle,axis=1).head(5))
   iris_add  iris_sub
0       1.9       7.9
1       1.2       1.6
2       1.5       7.9
3       1.1       1.5
4       1.5       7.7