SQLのこれって、Python+Pandasだとどうやるんだっけ?(前編)

前回に引き続き、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
長くなってきたので、今回はこの辺で。
続きは次回やります。
この投稿へのトラックバック
トラックバックはありません。
- トラックバック URL
この投稿へのコメント