概要

pythonによるデータ分析入門を参考に、MovieLens 1Mを使ってsqlで普段やってるようなこと(joinとかgroup byとかsortとか)をpandasにやらせてみる。

@CretedDate 2014/09/28
@Versions Python2.7.6 pandas0.14.1-2

データの取得

下記からMovieLens 1Mを頂いてきて解凍する。

http://grouplens.org/datasets/movielens/

ファイルの読み込み

落としてきたファイルを解凍すると、movies.dat、rating.dat、users.datという3つのファイルが入っているので、read_csvで読み込む。

import pandas as pd

movies = pd.read_csv( 'ml-1m/movies.dat', sep='::', header=None,
    names=['movie_id', 'title', 'genres'] )

ratings = pd.read_csv( 'ml-1m/ratings.dat', sep='::', header=None,
    names=['user_id', 'movie_id', 'rating', 'timestamp'] )

users = pd.read_csv( 'ml-1m/users.dat', sep='::', header=None,
    names=['user_id', 'gender', 'age', 'occupation', 'zip'] )

COUNT

usersのgenderをcountしてみる。sqlで書くと下記のようなイメージ。

select gender, count(*) from users group by gender;

usersの中身は下記のような構成。(上から5件表示)

>>> users[:5]

   user_id gender  age  occupation    zip
0        1      F    1          10  48067
1        2      M   56          16  70072
2        3      M   25          15  55117
3        4      M   45           7  02460
4        5      M   25          20  55455

value_countsを利用して男女の件数を取得する例。

>>> users['gender'].value_counts()

M    4331
F    1709

単純に select count(gender) from users するだけなら count

>>> users['gender'].count()

6040

nanやnoneはcountされない。試しにgenderを1つNoneにしてCOUNTしてみる。

>>> users['gender'][0] = None
>>> users['gender'].count()

6039

ちゃんと1減った。

カラムを指定せずに users.count() とすると各カラムのcountが出る。

>>> users.count()

user_id       6040
gender        6039
age           6040
occupation    6040
zip           6040

LIMIT

select *  from users limit 10;

的なことをする。

# こういう書き方とか
>>> users[0:10]

# head使ったり
users.head(10)

   user_id gender  age  occupation    zip
0        1      F    1          10  48067
1        2      M   56          16  70072
2        3      M   25          15  55117
3        4      M   45           7  02460
4        5      M   25          20  55455
5        6      F   50           9  55117
6        7      M   35           1  06810
7        8      M   25          12  11413
8        9      M   25          17  61614
9       10      F   35           1  95370

当然tailもできる。5〜9までを取る場合。

# こういう書き方とか
>>> users[5:10]

# headしてからtailしたり
>>> users.head(10).tail(5)

   user_id gender  age  occupation    zip
5        6      F   50           9  55117
6        7      M   35           1  06810
7        8      M   25          12  11413
8        9      M   25          17  61614
9       10      F   35           1  95370

GROUP BY

先ほどやった男女のCOUNTをgroupbyを使ってやってみる。

>>> users.groupby('gender').size()

gender
F         1709
M         4331

meanで男女ごとのageの平均値を取ってみる。

select gender, average(age) from users group by gender;
>>> users[['gender', 'age']].groupby('gender').mean()

              age
gender           
F       30.859567
M       30.552297

meanだけでなく、sum, median, stdなんかも使える。

下記のような書き方も可。

>>> users[['gender', 'age']].groupby('gender').agg( np.mean )

describeすると一度に一般的な情報のセットが出て便利。

>>> users[['gender', 'age']].groupby('gender').describe()

                      age
gender                   
F      count  1709.000000
       mean     30.859567
       std      13.242564
       min       1.000000
       25%      25.000000
       50%      25.000000
       75%      45.000000
       max      56.000000
M      count  4331.000000
       mean     30.552297
       std      12.757110
       min       1.000000
       25%      25.000000
       50%      25.000000
       75%      35.000000
       max      56.000000

ageの集計をpivot_tableを利用して実行してみる。男女の年齢の平均値を出す場合。

>>> users.pivot_table( 'age', rows='gender', aggfunc='mean' )

gender
F         30.859567
M         30.552297

もう少し複雑に、occupatin毎の年齢の平均をだしてみる。

>>> users.pivot_table( 'age', rows='gender', cols='occupation', aggfunc='mean' )

occupation         0          1          2          3          4          5   \
gender                                                                         
F           30.788793  35.387560  33.681319  33.640000  20.978632  30.967742   
M           29.405010  36.451411  29.102273  32.424658  20.154286  28.950617   

(以下略)


# 上記は各occupationがカラムになっている
>>> users.pivot_table( 'age', rows='gender', cols='occupation', aggfunc='mean' ).columns
Int64Index([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20], dtype='int64')

# occupationは0〜20まで存在していて、それが全部カラムになっている
>>> users['occupation'].order().unique()
array([ 0,  1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 11, 12, 13, 14, 15, 16,
       17, 18, 19, 20])

SORT

年齢で並べてみる。

select * from users order by age limit 10;
>>> users.sort('age').head()

      user_id gender  age  occupation    zip
0           1      F    1          10  48067
5175     5176      M    1          10  60423
5172     5173      M    1          10  60423
2107     2108      M    1          10  60462
3297     3298      M    1          10  20876

descendingでsortする。

select * from users order by age desc limit 10;
>>> users.sort('age', ascending=False).head()

      user_id gender  age  occupation         zip
5214     5215      F   56           6       91941
5536     5537      F   56           1       10543
3286     3287      M   56          13  27514-3540
885       886      F   56           0       14830
886       887      F   56          16       55345

ageとuser_idでsortしてみる。age降順、user_id昇順で。

select * from users order by age desc, user_id asc limit 10;
>>> users.sort(['age', 'user_id'], ascending=[False, True]).head()

      user_id gender  age  occupation         zip
5214     5215      F   56           6       91941
5536     5537      F   56           1       10543
3286     3287      M   56          13  27514-3540
885       886      F   56           0       14830
886       887      F   56          16       55345

sort_indexという関数もあるが、frame.pyのコードを見る限りでは、sortと挙動は同じ。

inplace=Trueを設定すると、破壊的にsortされる。

>>> users.sort('age', inplace=True)
>>> users.head()

      user_id gender  age  occupation    zip
0           1      F    1          10  48067
5175     5176      M    1          10  60423
5172     5173      M    1          10  60423
2107     2108      M    1          10  60462
3297     3298      M    1          10  20876

WHERE

20代だけ抽出してみる。

select * from users where age >= 20 and age < 30;
>>> users[ (users['age'] >= 20) & (users['age'] < 30) ].head()

      user_id gender  age  occupation         zip
5214     5215      F   56           6       91941
5536     5537      F   56           1       10543
3286     3287      M   56          13  27514-3540
885       886      F   56           0       14830
886       887      F   56          16       55345

こういう書き方もある。

>>> users[ users['age'].lt(30) & users['age'].gt(20) ].head()

query を使った方が楽。但しnumexprのインストールが必要。

$ pip install numexpr
>>> users.query( 'age >= 20 and age < 30' ).head()

文字列の比較をしてみる。titleにStoryを含む映画の抽出例。

select * from movies where title like '%Story%';
>>> movies[ movies['title'].str.contains('Story') ].head()

     movie_id                                              title                         genres
0           1                                   Toy Story (1995)    Animation|Children's|Comedy
124       126                  NeverEnding Story III, The (1994)   Adventure|Children's|Fantasy
292       295                  Pyromaniac's Love Story, A (1995)                 Comedy|Romance
833       844                       Story of Xinghua, The (1993)                          Drama
865       876  Police Story 4: Project S (Chao ji ji hua) (1993)                         Action

JOIN

ratingsとmoviesをjoinしてみる。

select * from ratings r left outer join movies m.movie_id on r.movie_id;

mergeは同名のcolumnをうまくつないでくれるらしい。

>>> pd.merge( ratings, movies ).head()

   user_id  movie_id  rating  timestamp                                      title genres  
0        1      1193       5  978300760   One Flew Over the Cuckoo's Nest (1975)  Drama  
1        2      1193       5  978298413   One Flew Over the Cuckoo's Nest (1975)  Drama  
2       12      1193       4  978220179   One Flew Over the Cuckoo's Nest (1975)  Drama  
3       15      1193       4  978199279   One Flew Over the Cuckoo's Nest (1975)  Drama  
4       17      1193       5  978158471   One Flew Over the Cuckoo's Nest (1975)  Drama  

デフォルトではleft outer join的な挙動になっている。howを指定するとその他の動きもできる

>>> # right outer join
>>> pd.merge( ratings, movies, how='right' )

>>> # full outer join
>>> pd.merge( ratings, movies, how='outer' )

>>> # inner join
>>> pd.merge( ratings, movies, how='inner' )

違う名前のカラムを結合させてみる。例として無意味にratingsのuser_idとmoviesのmovie_idを繋いでみる。

>>> pd.merge( ratings, movies, how='left', left_on='user_id', right_on='movie_id' ).head()

   user_id  movie_id_x  rating  timestamp  movie_id_y             title  \
0        1        1193       5  978300760           1  Toy Story (1995)   
1        1         661       3  978302109           1  Toy Story (1995)   
2        1         914       3  978301968           1  Toy Story (1995)   
3        1        3408       4  978300275           1  Toy Story (1995)   
4        1        2355       5  978824291           1  Toy Story (1995)  

結果は一部略。user_id=1とjoinしているので、movie_id=1のToy Storyと連結されている。

UNION

特にUNIONの例として使えそうなカラムもないので、movie_idとtitleおよびmovie_idとgenresを3行ずつ抽出してunionしてお茶を濁してみる。

select movie_id, title from movies limit 3
union all
select movie_id, genres from movies limit 3;
>>> # concatする用のdataframeを用意
>>> df1 = movies[['movie_id', 'title']].head(3)
>>> df2 = movies[['movie_id', 'genres']].head(3)

>>> # 名前を合わせる
>>> df2.columns = ['movie_id', 'title']

>>> # concat
>>> pd.concat( [df1, df2] )

   movie_id                         title
0         1              Toy Story (1995)
1         2                Jumanji (1995)
2         3       Grumpier Old Men (1995)
0         1   Animation|Children's|Comedy
1         2  Adventure|Children's|Fantasy
2         3                Comedy|Romance