Pandas: SQLを書くようにデータ分析

はじめに

PandasはPythonのオープンソースのデータ解析ライブラリです。Pandasは構造化データを3つに分類します。

• Series、1次元シーケンスで、column名がない、ただ一つのcolumnのDataFrameと見ることができます；
• DataFrameはSpark SQLのDataFrameと同様にR言語から生まれたもので、Seriesのコンテナとして見える2次元構造化データをcolumnかつschema化したものです。；
• Panelは3次元の構造化データでDataFrameのコンテナとして見ることができます。

DataFrameが一般的なので、この記事ではDataFrameを主に取り上げます。DataFrameは、プレーンテキストやJsonなどのデータを読み込んで生成することができます。

import pandas as pd
import numpy as np


df = pd.DataFrame({'total_bill': [16.99, 10.34, 23.68, 23.68, 24.59],
                   'tip': [1.01, 1.66, 3.50, 3.31, 3.61],
                   'sex': ['Female', 'Male', 'Male', 'Male', 'Female']})

DataFrameには次のような特徴があります。

# data type of columns
df.dtypes
# indexes
df.index
# return pandas.Index
df.columns
# each row, return array[array]
df.values
# a tuple representing the dimensionality of df
df.shape

SQLを書くように使いましょう

select

SQLのselectは、列の名前で選びます。Pandasはより柔軟性があり、列の名前だけでなく、列が置かれているポジションによっても選ぶことができます。相関関数は次のようになります。

• loc、列labelに基づいて、特定の行を選択することができます(行indexによって);
• iloc、行/列に基づいたpositionです。

df.loc[1:3, ['total_bill', 'tip']]
df.loc[1:3, 'tip': 'total_bill']
df.iloc[1:3, [1, 2]]
df.iloc[1:3, 1: 3]

• at、行indexと列labelを指定することによって、すばやくDataFrameの要素の位置を定めます;
• iatは、atと似ていますが、positionによって位置づけられます。

df.at[3, 'tip']
df.iat[3, 1]

ixは、locとilocの組み合わせで、ラベルと位置の両方をサポートします。（もう廃棄されました）

df.ix[1:3, [1, 2]]
df.ix[1:3, ['total_bill', 'tip']]

さらに、より簡潔な行/列の選び方があります。

df[1: 3]
df[['total_bill', 'tip']]

where

Pandasでwhere filterを実現する方法としてよく使われているのは：

df[df[colunm] boolean expr]

例えば：

df[df['sex'] == 'Female']
df[df['total_bill'] > 20]

# or
df.query('total_bill > 20')

where文にはand, or, in, notというキーワードがよく使われます。Pandasにも対応する実装があります。

# and
df[(df['sex'] == 'Female') & (df['total_bill'] > 20)]
# or
df[(df['sex'] == 'Female') | (df['total_bill'] > 20)]
# in
df[df['total_bill'].isin([21.01, 23.68, 24.59])]
# not
df[-(df['sex'] == 'Male')]
df[-df['total_bill'].isin([21.01, 23.68, 24.59])]
# string function
df = df[(-df['app'].isin(sys_app)) & (-df.app.str.contains('^機械学習\d+$'))]

where条件をふるいにかけた一行だけのdataframeに、ある列の値を取ります。2つの実装があります。

total = df.loc[df['tip'] == 1.66, 'total_bill'].values[0]
total = df.get_value(df.loc[df['tip'] == 1.66].index.values[0], 'total_bill')

distinct

drop_duplicatesはある列に基づいてdataframeデータの重複を削除します。

df.drop_duplicates(subset=['sex'], keep='first', inplace=True)

パラメータを含みます:

• subset、選択した列のためにdistinctをして、デフォルトはすべての列です；
• keep、オプション値{'first', 'last', False}は、重複する要素の最初、最後を残すか、全てを削除します；
• inplace、inplaceでデフォルトはFalseで新しいdataframeを返しますTrueの場合は、リセットした元のdataframeを返します。

group

groupは、countやavgといった集計関数(Aggregate functions)と一緒に使うのが一般的です。Pandasは集計関数のサポートが限られており、countとsize関数でSQLのcountを実装しています。

df.groupby('sex').size()
df.groupby('sex').count()
df.groupby('sex')['tip'].count()

いろんな集計関数を一つのコードにいれば、aggでdictを指定します。

select sex, max(tip), sum(total_bill) as total
from tips_tb
group by sex;

df.groupby('sex').agg({'tip': np.max, 'total_bill': np.sum})

# count(distinct **)
df.groupby('tip').agg({'sex': pd.Series.nunique})

as

SQLではas修正列という別名が使われますが、Pandasもそのような修正に対応しています。

# first implementation
df.columns = ['total', 'pit', 'xes']
# second implementation
df.rename(columns={'total_bill': 'total', 'tip': 'pit', 'sex': 'xes'}, inplace=True)

join

Pandasでの実装にも2種類あります。

# 1.
df.join(df2, how='left'...)

# 2. 
pd.merge(df1, df2, how='left', left_on='app', right_on='app')

1つ目はDataFrameのindexでjoinしますが、2つ目はonで指定した列でjoinします。Pandasはleft、right、inner、full outerの4つのjoin方式を満たしています。

order

Pandasでは複数の列のorderをサポートし、それぞれの列の昇順/降順を調整することができます。

df.sort_values(['total_bill', 'tip'], ascending=[False, True])

top

グローバルのtopに対して：

df.nlargest(3, columns=['total_bill'])

グループトップでのMySQLの実装は(joinから採用されています)

select a.sex, a.tip
from tips_tb a
where (
	select count(*)
	from tips_tb b
	where b.sex = a.sex and b.tip > a.tip
) < 2
order by a.sex, a.tip desc;

Pandasはこれと同じような考え方で実現されます。

# 1.
df.assign(rn=df.sort_values(['total_bill'], ascending=False)
          .groupby('sex')
          .cumcount()+1)\
    .query('rn < 3')\
    .sort_values(['sex', 'rn'])
    
# 2.
df.assign(rn=df.groupby('sex')['total_bill']
          .rank(method='first', ascending=False)) \
    .query('rn < 3') \
    .sort_values(['sex', 'rn'])

replace

replace関数はdataframeのグローバルな修正を提供し、where条件(locと組み合わせて)フィルタリングして修正することもできます。

# overall replace
df.replace(to_replace='Female', value='Sansa', inplace=True)

# dict replace
df.replace({'sex': {'Female': 'Sansa', 'Male': 'Leone'}}, inplace=True)

# replace on where condition 
df.loc[df.sex == 'Male', 'sex'] = 'Leone'

カスタム

上記のSQL操作に加えて、Pandasは各列/各要素のカスタマイズ操作を提供しており、そのために次の3つの関数が設計されています:

• map(func)、Seriesの関数で、DataFrameは直接呼び出すことができません。
• apply(func)、DataFrameのある行/列をfunc操作します。
• applymap(func)、element-wise関数で各要素をfunc操作します。

df['tip'].map(lambda x: x - 1)
df[['total_bill', 'tip']].apply(sum)
df.applymap(lambda x: x.upper() if type(x) is str else x)

map、applyにはちょっと複雑で、詳細の使い方は他の資料で調べてください。

実例

前期比

既存の2ヶ月のアプリのUVデータを取得するには、前月比UV増加です;この操作は2つのDataframe left joinの後に指定された列で減算する操作と等価です。

def chain(current, last):
    df1 = pd.read_csv(current, names=['app', 'tag', 'uv'], sep='\t')
    df2 = pd.read_csv(last, names=['app', 'tag', 'uv'], sep='\t')
    df3 = pd.merge(df1, df2, how='left', on='app')
    df3['uv_y'] = df3['uv_y'].map(lambda x: 0.0 if pd.isnull(x) else x)
    df3['growth'] = df3['uv_x'] - df3['uv_y']
    return df3[['app', 'growth', 'uv_x', 'uv_y']].sort_values(by='growth', ascending=False)

差集合

与えられた列に対して、あるDataframeは別のDataframeの値をフィルタリングします。集合の差集合操作に相当します。

def difference(left, right, on):
    """
    difference of two dataframes
    :param left: left dataframe
    :param right: right dataframe
    :param on: join key
    :return: difference dataframe
    """
    df = pd.merge(left, right, how='left', on=on)
    left_columns = left.columns
    col_y = df.columns[left_columns.size]
    df = df[df[col_y].isnull()]
    df = df.ix[:, 0:left_columns.size]
    df.columns = left_columns
    return df