前回に引き続き、Pythonネタ。
データ分析をする時にPandasを使うと便利ってのは知ってたんだけど、
「SQLのこれって、Pandasでどうやるんだっけ？？？」と、よくなるので備忘録的に。

ちなみにPandasってのはデータ解析用のPythonライブラリで、
CSVファイルとかで読み込んだデータを、SQLを使うかのごとく分析できる便利ツール。

今回はOracleでおなじみのemp表とdept表を使って、SQLと比較しながらやっていこうと思う。

empno, ename, job, mgr, hiredate, sal, comm, deptno
7369, SMITH,  CLERK,     7902, 1980/12/17,  800,     , 20
7499, ALLEN,  SALESMAN,  7698, 1981/02/20, 1600,  300, 30
7521, WARD,   SALESMAN,  7698, 1981/02/22, 1250,  500, 30
7566, JONES,  MANAGER,   7839, 1981/04/02, 2975,     , 20
7654, MARTIN, SALESMAN,  7698, 1981/09/28, 1250, 1400, 30
7698, BLAKE,  MANAGER,   7839, 1981/03/01, 2850,     , 30
7782, CLARK,  MANAGER,   7839, 1981/01/09, 2450,     , 10
7788, SCOTT,  ANALYST,   7566, 1982/12/09, 3000,     , 20
7839, KING,   PRESIDENT,     , 1981/11/17, 5000,     , 10
7844, TURNER, SALESMAN,  7698, 1981/09/08, 1500,    0, 30
7876, ADAMS,  CLERK,     7788, 1983/01/12, 1100,     , 20
7900, JAMES,  CLERK,     7698, 1981/12/03,  950,     , 30
7902, FORD,   ANALYST,   7566, 1981/12/03, 3000,     , 20
7934, MILLER, CLERK,     7782, 1982/01/23, 1300,     , 10

deptno, dname, loc
10, ACCOUNTING, NEW YORK
20, RESEARCH,   DALLAS
30, SALES,      CHICAGO
40, OPERATIONS, BOSTON

 

下準備

まずはPandasをimport。

import pandas as pd

Pandasをimportすると、「read_csv」で簡単にcsvファイルをDataFrameにすることができる。
余計な空白を除去するため、「skipinitialspace」オプションを指定する。

df_emp = pd.read_csv('C:/data/emp.csv', skipinitialspace=True)
df_dept = pd.read_csv('C:/data/dept.csv', skipinitialspace=True)

 

表を表示する

一番単純な命令。SQLならこんな感じ。

SELECT * FROM emp;

DataFrameをそのままprintするだけ。

print(df_emp)

    empno   ename        job     mgr    hiredate   sal    comm  deptno
0    7369   SMITH      CLERK  7902.0  1980/12/17   800     NaN      20
1    7499   ALLEN   SALESMAN  7698.0  1981/02/20  1600   300.0      30
2    7521    WARD   SALESMAN  7698.0  1981/02/22  1250   500.0      30

・・・（省略）・・・

12   7902    FORD    ANALYST  7566.0  1981/12/03  3000     NaN      20
13   7934  MILLER      CLERK  7782.0  1982/01/23  1300     NaN      10

ちなみに、以下のようにすると最初の3件だけ表示することも可能。

print(df_emp.head(3))

    empno   ename        job     mgr    hiredate   sal    comm  deptno
0    7369   SMITH      CLERK  7902.0  1980/12/17   800     NaN      20
1    7499   ALLEN   SALESMAN  7698.0  1981/02/20  1600   300.0      30
2    7521    WARD   SALESMAN  7698.0  1981/02/22  1250   500.0      30

 

特定の列だけ表示する

SELECT句に列名を指定した場合。

SELECT ename, job FROM emp;

Pandasでは列を以下のように指定する。

print(df_emp[['ename', 'job']])

     ename        job
0    SMITH      CLERK
1    ALLEN   SALESMAN
2     WARD   SALESMAN

・・・（省略）・・・

ちなみに、列に別名をつける場合は以下のようにする。

print(df_emp.rename(columns={'ename': '従業員名', 'job': '職種'})[['従業員名', '職種']])

      従業員名         職種
0    SMITH      CLERK
1    ALLEN   SALESMAN
2     WARD   SALESMAN

・・・（省略）・・・

 

重複を排除して表示する

SQLで言えばDISTINCT句を指定した場合。

SELECT DISTINCT job FROM emp;

Pandasでは「drop_duplicates」で重複を除去する。

print(df_emp.drop_duplicates(['job'])['job'])

0        CLERK
1     SALESMAN
3      MANAGER
7      ANALYST
8    PRESIDENT
Name: job, dtype: object

 

列の値で計算する

例えば「sal」列を12倍してみる。

SELECT ename, sal * 12 as yearly FROM emp;

Pandasではいったん計算結果を列に挿入した後、それを表示する。

df_emp['yearly'] = df_emp['sal'] * 12
print(df_emp[['ename', 'yearly']])

     ename  yearly
0    SMITH    9600
1    ALLEN   19200
2     WARD   15000

・・・（省略）・・・

 

条件で絞り込む

SQLならWHERE句を指定した場合。
例えば「job」が”MANAGER”の人だけを抽出する。

SELECT ename, sal FROM emp WHERE job = 'MANAGER';

Pandasは以下のように条件を指定する。

print(df_emp[df_emp['job'] == 'MANAGER'][['ename', 'sal']])

   ename   sal
3  JONES  2975
5  BLAKE  2850
6  CLARK  2450

「query」を利用して指定することも可能。
正直、こちらの方がわかり易い。

print(df_emp.query('job == "MANAGER"')[['ename', 'sal']])

結果は同じなので、省略。

 

条件を数値で指定する

例えば「sal」が3000以上の人だけを抽出する。

SELECT ename, sal FROM emp WHERE sal >= 3000;

Pandasならこう。

print(df_emp[df_emp['sal'] >= 3000][['ename', 'sal']])

もしくはこう。

print(df_emp.query('sal >= 3000')[['ename', 'sal']])

    ename   sal
7   SCOTT  3000
8    KING  5000
12   FORD  3000

 

条件を複数指定する（AND）

例えば「job」が”MANAGER”で、かつ「sal」が2500以上の人だけを抽出する。

SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER' OR sal >= 2500;

Pandasならこう。

print(df_emp[(df_emp['job'] == 'MANAGER') & (df_emp['sal'] >= 2500)][['ename', 'job', 'sal']])

もしくはこう。

print(df_emp.query('job == "MANAGER" & sal >= 2500')[['ename', 'job', 'sal']])

   ename      job   sal
3  JONES  MANAGER  2975
5  BLAKE  MANAGER  2850

 

条件を複数指定する（OR）

例えば「job」が”MANAGER”か、または「sal」が2500以上の人だけを抽出する。

SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER' OR sal >= 2500;

Pandasならこう。

print(df_emp[(df_emp['job'] == 'MANAGER') | (df_emp['sal'] >= 2500)][['ename', 'job', 'sal']])

もしくはこう。

print(df_emp.query('job == "MANAGER" | sal >= 2500')[['ename', 'job', 'sal']])

    ename        job   sal
3   JONES    MANAGER  2975
5   BLAKE    MANAGER  2850
6   CLARK    MANAGER  2450
7   SCOTT    ANALYST  3000
8    KING  PRESIDENT  5000
12   FORD    ANALYST  3000

 

条件を否定する

例えば、「job」が”MANAGER”でなく、かつ”SALESMAN”でもない。
条件的に書き間違えやすいやつ。

SELECT ename, job FROM emp
WHERE NOT (job = 'MANAGER' OR job = 'SALESMAN');

Pandasならこう。

print(df_emp[~((df_emp['job'] == 'MANAGER') | (df_emp['job'] == 'SALESMAN'))][['ename', 'job']])

もしくはこう。

print(df_emp.query('not (job == "MANAGER" or job == "SALESMAN")')[['ename', 'job']])

     ename        job
0    SMITH      CLERK
7    SCOTT    ANALYST
8     KING  PRESIDENT

・・・（省略）・・・

この書き方、SQLだとパフォーマンスが悪くなる典型だけど、
Pandasだとどうなんだろね？

 

条件を範囲で指定する

例えば「sal」が2000以上、3000以下の人。
SQLならBETWEENの方が早いけど、Pandasに対応する命令がないので不等号にしておく。

SELECT ename, sal FROM emp
WHERE 2000 <= sal AND sal <= 3000;

Pandasならこう。

print(df_emp[(2000 <= df_emp['sal']) & (df_emp['sal'] <= 3000)][['ename', 'sal']])

もしくはこう。queryで書くと、BETWEENよりも直感的にわかりやすい気がする。

print(df_emp.query('2000 <= sal <= 3000')[['ename', 'sal']])

    ename   sal
3   JONES  2975
5   BLAKE  2850
6   CLARK  2450
7   SCOTT  3000
12   FORD  3000

queryで指定した方がシンプルだね。

 

複数の値で条件を指定する

例えば「mgr」が7902,7566,7782のいずれかの人。
ORで聞いてもいいんだけど、フツーはこうやるよね。

SELECT ename, mgr FROM emp WHERE mgr IN (7902,7566,7782);

Pandasならこう。

print(df_emp[df_emp['mgr'].isin([7902, 7566, 7782])][['ename', 'mgr']])

もしくはこう。

print(df_emp.query('mgr in [7902, 7566, 7782]')[['ename', 'mgr']])

     ename     mgr
0    SMITH  7902.0
7    SCOTT  7566.0
12    FORD  7566.0
13  MILLER  7782.0

 

NULLを検索する

例えば、「MGR」がNULLの人。
SQLだとNULLをそのまま検索するのはNG。

SELECT ename, mgr FROM emp WHERE mgr IS NULL;

Pandasならこう。

print(df_emp[df_emp['mgr'].isnull()][['ename', 'mgr']])

もしくはこう。

print(df_emp.query('mgr.isnull()')[['ename', 'mgr']])

  ename  mgr
8  KING  NaN

queryの書き方がSQLっぽくないから気をつけて。

 

NULL以外を検索する

さっきと逆で「MGR」がNULLじゃない人。

SELECT ename, mgr FROM emp WHERE mgr IS NOT NULL;

Pandasならこう。

print(df_emp[df_emp['mgr'].notnull()][['ename', 'mgr']])

もしくはこう。

print(df_emp.query('mgr.notnull()')[['ename', 'mgr']])

     ename     mgr
0    SMITH  7902.0
1    ALLEN  7698.0
2     WARD  7698.0

・・・（省略）・・・

 

あいまい検索する（前方一致）

「ename」が"J"で始まる人。

SELECT ename FROM emp WHERE ename LIKE 'J%';

Pandasならこう。

print(df_emp[df_emp['ename'].str.startswith("J")][['ename']])

もしくはこう。

print(df_emp.query('ename.str.startswith("J")')[['ename']])

    ename
3   JONES
11  JAMES

 

あいまい検索する（後方一致）

SQLなら"%"の位置が変わるだけなんだけどね。

print(df_emp[df_emp['ename'].str.endswith("N")][['ename']])

Pandasならこう。

print(df_emp[df_emp['ename'].str.endswith("N")][['ename']])

もしくはこう。

print(df_emp.query('ename.str.endswith("N")')[['ename']])

ename
1   ALLEN
4  MARTIN

 

あいまい検索する（部分一致）

SELECT ename FROM emp WHERE ename LIKE '%LL%';

Pandasならこう。

print(df_emp[df_emp['ename'].str.contains("LL")][['ename']])

もしくはこう。

print(df_emp.query('ename.str.contains("LL")')[['ename']])

     ename
1    ALLEN
13  MILLER

 

あいまい検索する（正規表現）

SQLにはないけれど、Pandasなら正規表現でも検索できる。

print(df_emp[df_emp['ename'].str.match(".*A.*S")][['ename']])

もしくはこう。

print(df_emp.query('ename.str.match(".*A.*S")')[['ename']])

    ename
10  ADAMS
11  JAMES

長くなってきたので、今回はこの辺で。
続きは次回やります。