This topic describes how to create and manage a DataFrame object and how to use DataFrame to process data.
Prepare data
In this topic, a dataset named MovieLens 100K is used. You can download the ml-100k.zip package to your on-premises machine. The u.user file stores user-related data. The u.item file stores movie-related data. The u.data file stores rating-related data.
Create the following tables:
pyodps_ml_100k_users
: stores user-related 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' );
pyodps_ml_100k_movies
: stores movie-related 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 'Music' ,Mystery TINYINT COMMENT 'Mystery' ,Romance TINYINT COMMENT 'Romance' ,SciFi TINYINT COMMENT 'Science fiction' ,Thriller TINYINT COMMENT 'Thriller' ,War TINYINT COMMENT 'War' ,Western TINYINT COMMENT 'West' );
pyodps_ml_100k_ratings
: stores rating-related 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 'Score' ,timestamp BIGINT COMMENT 'Timestamp' )
Run the Tunnel upload command to import data of the files to MaxCompute tables. For more information about Tunnel commands, 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;
Perform operations on DataFrame objects
In this topic, the following tables are used: pyodps_ml_100k_movies
(movie-related data), pyodps_ml_100k_users
(user-related data), and pyodps_ml_100k_ratings
(rating-related data). The following example shows how to perform the operations on these tables in IPython.
Before you use IPython, make sure that Python is installed. IPython is developed based on Python. You must install the Python environment. After the Python environment is installed, you can install IPython by using pip install IPython. After the installation is complete, you can run the IPython command to start the IPython-based interactive environment. Then, you can write and run Python code.
Create a MaxCompute object.
import os from odps import ODPS # Set the environment variable ALIBABA_CLOUD_ACCESS_KEY_ID to the AccessKey ID of your Alibaba Cloud account. # Set the environment variable ALIBABA_CLOUD_ACCESS_KEY_SECRET to the AccessKey secret of the Alibaba Cloud account. # We recommend that you do not directly use your AccessKey ID or AccessKey secret. 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 by specifying a table.
from odps.df import DataFrame users = DataFrame(o.get_table('pyodps_ml_100k_users'))
Query the
dtypes
property to view the fields of the DataFrame object and the data types of the fields.print(users.dtypes)
The following result is returned:
odps.Schema { user_id int64 age int64 sex string occupation string zip_code string }
Use the
head
method to specify the number of first data records that you want to preview.print(users.head(10))
The following result is returned:
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 want to view all the fields, perform the following operations:
Specify the fields that you want to query.
print(users[['user_id', 'age']].head(5))
The following result is returned:
user_id age 0 1 24 1 2 53 2 3 23 3 4 24 4 5 33
Exclude specific fields.
print(users.exclude('zip_code', 'age').head(5))
The following result is returned:
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 specific fields and add new fields based on computation. For example, add the
sex_bool
field and set sex_bool to True ifsex
isM
. If the value of sex is not M, set sex_bool to False.print(users.select(users.exclude('zip_code', 'sex'), sex_bool=users.sex == 'M').head(5))
The following result is returned:
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
Obtain the numbers of male and female users.
print(users.groupby(users.sex).agg(count=users.count()))
The following result is returned:
sex count 0 F 273 1 M 670
To divide users by occupation, obtain the first 10 occupations that have the largest number of users, and sort the occupations in descending order based on the number of users.
df = users.groupby('occupation').agg(count=users['occupation'].count()) print(df.sort(df['count'], ascending=False)[:10])
The following result is returned:
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, you can use the
value_counts
method. The number of records that is returned by using this method is limited by theoptions.df.odps.sort.limit
parameter. For more information, see Configurations.print(users.occupation.value_counts()[:10])
The following result is returned:
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
Show data in a more intuitive graph. You can enable built-in charts in IPython.
%matplotlib inline
Divide users into 30 groups by age and view the histogram of age distribution.
users.age.hist(bins=30, title="Distribution of users' ages", xlabel='age', ylabel='count of users')
The following chart is displayed.
Join
the three tables and save the tables as 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)
The following result is returned:
odps.Schema { movie_id int64 title string release_date string video_release_date string imdb_url string user_id int64 rating int64 unix_timestamp int64 age int64 sex string occupation string zip_code string }
Divide users aged 0 to 80 into eight age groups.
labels = ['0-9', '10-19', '20-29', '30-39', '40-49', '50-59', '60-69', '70-79'] cut_lens = lens[lens, lens.age.cut(range(0, 81, 10), right=False, labels=labels).rename('age_group')]
View the first 10 ages each with only one user, as well as their age groups.
print(cut_lens ['age_group', 'age'].distinct()[:10])
The following result is returned:
age_group age 0 0-9 7 1 10-19 10 2 10-19 11 3 10-19 13 4 10-19 14 5 10-19 15 6 10-19 16 7 10-19 17 8 10-19 18 9 10-19 19
View the total rating and average rating of users in each age group.
print(cut_lens.groupby ('age_group').agg(cut_lens.rating.count().rename ('total_rating'), cut_lens.rating.mean().rename ('avg_rating')))
The following result is returned:
age_group avg_rating total_rating 0 0-9 3.767442 43 1 10-19 3.486126 8181 2 20-29 3.467333 39535 3 30-39 3.554444 25696 4 40-49 3.591772 15021 5 50-59 3.635800 8704 6 60-69 3.648875 2623 7 70-79 3.649746 197
Use DataFrame to process data
Before you perform the following steps, download the Iris dataset. A DataWorks PyODPS node is used in this example. For more information, see Develop a PyODPS 3 task.
Create a table for storing test data.
Use the table management feature of DataWorks to create a table.
In the upper-left corner of the table editing page, click the icon.
Execute the following statement to create a 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 test data.
Right-click the name of the table that you create, and select Import Data. In the Data Import Wizard dialog box, click Next. Then, click Browse to upload the dataset that you downloaded.
Select Match by location and click Import Data.
Click the desired workflow, right-click MaxCompute, and then choose Create Node > PyODPS 3 to create a PyODPS node to store and run code.
Write the code and click the icon. You can view the result in the Runtime Log section in the lower pane. Code details:
from odps.df import DataFrame, output iris = DataFrame(o.get_table('pyodps_iris')) # Create the DataFrame object iris from the MaxCompute table. print(iris.head(10)) print(iris.sepallength.head(5)) # Display part of the iris content. # Use a user-defined function to calculate the sum of two columns of iris. print(iris.apply(lambda row: row.sepallength + row.sepalwidth, axis=1, reduce=True, types='float').rename('sepaladd').head(3)) # Specify the output name and type of the function. @output(['iris_add', 'iris_sub'], ['float', 'float']) def handle(row): # Use the yield keyword to return multiple rows of results. yield row.sepallength - row.sepalwidth,row.sepallength + row.sepalwidth yield row.petallength - row.petalwidth,row.petallength + row.petalwidth # Display the results of the first five rows. axis=1 indicates that the axis of the column extends horizontally. print(iris.apply(handle,axis=1).head(5))
The following result is returned:
# 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