前回に引き続き、Pythonネタ。
データ分析をする時にPandasを使うと便利ってのは知ってたんだけど、
「SQLのこれって、Pandasでどうやるんだっけ？？？」と、よくなるので備忘録的に。
ちなみにPandasってのはデータ解析用のPythonライブラリで、
CSVファイルとかで読み込んだデータを、SQLを使うかのごとく分析できる便利ツール。
今回はOracleでおなじみのemp表とdept表を使って、SQLと比較しながらやっていこうと思う。
[code language=”python” title=”emp.csv”]
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
[/code]
[code language=”python” title=”dept.csv”]
deptno, dname, loc
10, ACCOUNTING, NEW YORK
20, RESEARCH, DALLAS
30, SALES, CHICAGO
40, OPERATIONS, BOSTON
[/code]
下準備
まずはPandasをimport。
[code language=”python”]
import pandas as pd
[/code]
Pandasをimportすると、「read_csv」で簡単にcsvファイルをDataFrameにすることができる。
余計な空白を除去するため、「skipinitialspace」オプションを指定する。
[code language=”python”]
df_emp = pd.read_csv(‘C:/data/emp.csv’, skipinitialspace=True)
df_dept = pd.read_csv(‘C:/data/dept.csv’, skipinitialspace=True)
[/code]
表を表示する
一番単純な命令。SQLならこんな感じ。
SELECT * FROM emp;
DataFrameをそのままprintするだけ。
[code language=”python”]
print(df_emp)
[/code]
[code language=”python” gutter=”false”]
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
[/code]
ちなみに、以下のようにすると最初の3件だけ表示することも可能。
[code language=”python”]
print(df_emp.head(3))
[/code]
[code language=”python” gutter=”false”]
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
[/code]
特定の列だけ表示する
SELECT句に列名を指定した場合。
SELECT ename, job FROM emp;
Pandasでは列を以下のように指定する。
[code language=”python”]
print(df_emp[[‘ename’, ‘job’]])
[/code]
[code language=”python” gutter=”false”]
ename job
0 SMITH CLERK
1 ALLEN SALESMAN
2 WARD SALESMAN
・・・（省略）・・・
[/code]
ちなみに、列に別名をつける場合は以下のようにする。
[code language=”python”]
print(df_emp.rename(columns={‘ename’: ‘従業員名’, ‘job’: ‘職種’})[[‘従業員名’, ‘職種’]])
[/code]
[code language=”python” gutter=”false”]
従業員名 職種
0 SMITH CLERK
1 ALLEN SALESMAN
2 WARD SALESMAN
・・・（省略）・・・
[/code]
重複を排除して表示する
SQLで言えばDISTINCT句を指定した場合。
SELECT DISTINCT job FROM emp;
Pandasでは「drop_duplicates」で重複を除去する。
[code language=”python”]
print(df_emp.drop_duplicates([‘job’])[‘job’])
[/code]
[code language=”python” gutter=”false”]
0 CLERK
1 SALESMAN
3 MANAGER
7 ANALYST
8 PRESIDENT
Name: job, dtype: object
[/code]
列の値で計算する
例えば「sal」列を12倍してみる。
SELECT ename, sal * 12 as yearly FROM emp;
Pandasではいったん計算結果を列に挿入した後、それを表示する。
[code language=”python”]
df_emp[‘yearly’] = df_emp[‘sal’] * 12
print(df_emp[[‘ename’, ‘yearly’]])
[/code]
[code language=”python” gutter=”false”]
ename yearly
0 SMITH 9600
1 ALLEN 19200
2 WARD 15000
・・・（省略）・・・
[/code]
条件で絞り込む
SQLならWHERE句を指定した場合。
例えば「job」が”MANAGER”の人だけを抽出する。
SELECT ename, sal FROM emp WHERE job = 'MANAGER';
Pandasは以下のように条件を指定する。
[code language=”python”]
print(df_emp[df_emp[‘job’] == ‘MANAGER’][[‘ename’, ‘sal’]])
[/code]
[code language=”python” gutter=”false”]
ename sal
3 JONES 2975
5 BLAKE 2850
6 CLARK 2450
[/code]
「query」を利用して指定することも可能。
正直、こちらの方がわかり易い。
[code language=”python”]
print(df_emp.query(‘job == "MANAGER"’)[[‘ename’, ‘sal’]])
[/code]
結果は同じなので、省略。
条件を数値で指定する
例えば「sal」が3000以上の人だけを抽出する。
SELECT ename, sal FROM emp WHERE sal >= 3000;
Pandasならこう。
[code language=”python”]
print(df_emp[df_emp[‘sal’] >= 3000][[‘ename’, ‘sal’]])
[/code]
もしくはこう。
[code language=”python”]
print(df_emp.query(‘sal >= 3000’)[[‘ename’, ‘sal’]])
[/code]
[code language=”python” gutter=”false”]
ename sal
7 SCOTT 3000
8 KING 5000
12 FORD 3000
[/code]
条件を複数指定する（AND）
例えば「job」が”MANAGER”で、かつ「sal」が2500以上の人だけを抽出する。
SELECT ename, job, sal FROM emp WHERE job = 'MANAGER' OR sal >= 2500;
Pandasならこう。
[code language=”python”]
print(df_emp[(df_emp[‘job’] == ‘MANAGER’) & (df_emp[‘sal’] >= 2500)][[‘ename’, ‘job’, ‘sal’]])
[/code]
もしくはこう。
[code language=”python”]
print(df_emp.query(‘job == "MANAGER" & sal >= 2500’)[[‘ename’, ‘job’, ‘sal’]])
[/code]
[code language=”python” gutter=”false”]
ename job sal
3 JONES MANAGER 2975
5 BLAKE MANAGER 2850
[/code]
条件を複数指定する（OR）
例えば「job」が”MANAGER”か、または「sal」が2500以上の人だけを抽出する。
SELECT ename, job, sal FROM emp WHERE job = 'MANAGER' OR sal >= 2500;
Pandasならこう。
[code language=”python”]
print(df_emp[(df_emp[‘job’] == ‘MANAGER’) | (df_emp[‘sal’] >= 2500)][[‘ename’, ‘job’, ‘sal’]])
[/code]
もしくはこう。
[code language=”python”]
print(df_emp.query(‘job == "MANAGER" | sal >= 2500’)[[‘ename’, ‘job’, ‘sal’]])
[/code]
[code language=”python” gutter=”false”]
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
[/code]
条件を否定する
例えば、「job」が”MANAGER”でなく、かつ”SALESMAN”でもない。
条件的に書き間違えやすいやつ。
SELECT ename, job FROM emp WHERE NOT (job = 'MANAGER' OR job = 'SALESMAN');
Pandasならこう。
[code language=”python”]
print(df_emp[~((df_emp[‘job’] == ‘MANAGER’) | (df_emp[‘job’] == ‘SALESMAN’))][[‘ename’, ‘job’]])
[/code]
もしくはこう。
[code language=”python”]
print(df_emp.query(‘not (job == "MANAGER" or job == "SALESMAN")’)[[‘ename’, ‘job’]])
[/code]
[code language=”python” gutter=”false”]
ename job
0 SMITH CLERK
7 SCOTT ANALYST
8 KING PRESIDENT
・・・（省略）・・・
[/code]
この書き方、SQLだとパフォーマンスが悪くなる典型だけど、
Pandasだとどうなんだろね？
条件を範囲で指定する
例えば「sal」が2000以上、3000以下の人。
SQLならBETWEENの方が早いけど、Pandasに対応する命令がないので不等号にしておく。
SELECT ename, sal FROM emp WHERE 2000 <= sal AND sal <= 3000;
Pandasならこう。
[code language="python"]
print(df_emp[(2000 <= df_emp['sal']) & (df_emp['sal'] <= 3000)][['ename', 'sal']])
[/code]
もしくはこう。queryで書くと、BETWEENよりも直感的にわかりやすい気がする。
[code language="python"]
print(df_emp.query('2000 <= sal <= 3000')[['ename', 'sal']])
[/code]
[code language="python" gutter="false"]
ename sal
3 JONES 2975
5 BLAKE 2850
6 CLARK 2450
7 SCOTT 3000
12 FORD 3000
[/code]
queryで指定した方がシンプルだね。
複数の値で条件を指定する
例えば「mgr」が7902,7566,7782のいずれかの人。
ORで聞いてもいいんだけど、フツーはこうやるよね。
SELECT ename, mgr FROM emp WHERE mgr IN (7902,7566,7782);
Pandasならこう。
[code language="python"]
print(df_emp[df_emp['mgr'].isin([7902, 7566, 7782])][['ename', 'mgr']])
[/code]
もしくはこう。
[code language="python"]
print(df_emp.query('mgr in [7902, 7566, 7782]')[['ename', 'mgr']])
[/code]
[code language="python" gutter="false"]
ename mgr
0 SMITH 7902.0
7 SCOTT 7566.0
12 FORD 7566.0
13 MILLER 7782.0
[/code]
NULLを検索する
例えば、「MGR」がNULLの人。
SQLだとNULLをそのまま検索するのはNG。
SELECT ename, mgr FROM emp WHERE mgr IS NULL;
Pandasならこう。
[code language="python"]
print(df_emp[df_emp['mgr'].isnull()][['ename', 'mgr']])
[/code]
もしくはこう。
[code language="python"]
print(df_emp.query('mgr.isnull()')[['ename', 'mgr']])
[/code]
[code language="python" gutter="false"]
ename mgr
8 KING NaN
[/code]
queryの書き方がSQLっぽくないから気をつけて。
NULL以外を検索する
さっきと逆で「MGR」がNULLじゃない人。
SELECT ename, mgr FROM emp WHERE mgr IS NOT NULL;
Pandasならこう。
[code language="python"]
print(df_emp[df_emp['mgr'].notnull()][['ename', 'mgr']])
[/code]
もしくはこう。
[code language="python"]
print(df_emp.query('mgr.notnull()')[['ename', 'mgr']])
[/code]
[code language="python" gutter="false"]
ename mgr
0 SMITH 7902.0
1 ALLEN 7698.0
2 WARD 7698.0
・・・（省略）・・・
[/code]
あいまい検索する（前方一致）
「ename」が"J"で始まる人。
SELECT ename FROM emp WHERE ename LIKE 'J%';
Pandasならこう。
[code language="python"]
print(df_emp[df_emp['ename'].str.startswith("J")][['ename']])
[/code]
もしくはこう。
[code language="python"]
print(df_emp.query('ename.str.startswith("J")')[['ename']])
[/code]
[code language="python" gutter="false"]
ename
3 JONES
11 JAMES
[/code]
あいまい検索する（後方一致）
SQLなら"%"の位置が変わるだけなんだけどね。
print(df_emp[df_emp['ename'].str.endswith("N")][['ename']])
Pandasならこう。
[code language="python"]
print(df_emp[df_emp['ename'].str.endswith("N")][['ename']])
[/code]
もしくはこう。
[code language="python"]
print(df_emp.query('ename.str.endswith("N")')[['ename']])
[/code]
[code language="python" gutter="false"]
ename
1 ALLEN
4 MARTIN
[/code]
あいまい検索する（部分一致）
SELECT ename FROM emp WHERE ename LIKE '%LL%';
Pandasならこう。
[code language="python"]
print(df_emp[df_emp['ename'].str.contains("LL")][['ename']])
[/code]
もしくはこう。
[code language="python"]
print(df_emp.query('ename.str.contains("LL")')[['ename']])
[/code]
[code language="python" gutter="false"]
ename
1 ALLEN
13 MILLER
[/code]
あいまい検索する（正規表現）
SQLにはないけれど、Pandasなら正規表現でも検索できる。
[code language="python"]
print(df_emp[df_emp['ename'].str.match(".*A.*S")][['ename']])
[/code]
もしくはこう。
[code language="python"]
print(df_emp.query('ename.str.match(".*A.*S")')[['ename']])
[/code]
[code language="python" gutter="false"]
ename
10 ADAMS
11 JAMES
[/code]
長くなってきたので、今回はこの辺で。
続きは次回やります。
コメント