前回の続き。
「SQLのこれって、Pandasでどうやるんだっけ？？？」を、SQLと比較しながら解説していきます。
なお、データの事前準備のやり方などは
SQLのこれって、Python＋Pandasだとどうやるんだっけ？（前編）を参照してください。
NULLを別の値に変換する
SQLだとこんな感じにやるヤツ。
SELECT ename, nvl(comm, 0) AS nvlcomm FROM emp;
Pandasなら、こう
[code language=”python”]
print(df_emp.fillna({‘comm’: 0})[[‘ename’, ‘comm’]])
[/code]
[code language=”python” gutter=”false”]
ename nvlcomm
0 SMITH 0.0
1 ALLEN 300.0
2 WARD 500.0
・・・（省略）・・・
[/code]
ちなみに、全列をすべて同じ値で変換するならこうも書ける。
[code language=”python”]
print(df_emp.fillna(0)[[‘ename’, ‘comm’]])
[/code]
NULLを平均値、中央値、最頻値、前後の値に変換する
SQLではやったことないけど、Pandasならこう言う変換もできる。
平均値
平均値は「mean」を使用する。
[code language=”python”]
print(df_emp.fillna(df_emp[‘comm’].mean())[[‘ename’, ‘comm’]])
[/code]
[code language=”python” gutter=”false”]
0 SMITH 550.0
1 ALLEN 300.0
2 WARD 500.0
・・・（省略）・・・
[/code]
中央値
中央値は「median」を使用する。
[code language=”python”]
print(df_emp.fillna(df_emp[‘comm’].median())[[‘ename’, ‘comm’]])
[/code]
[code language=”python” gutter=”false”]
0 SMITH 400.0
1 ALLEN 300.0
2 WARD 500.0
・・・（省略）・・・
[/code]
最頻値
最頻値は「mode」を使用する。なお、「mode」はDataFrameを返却するので、
「iloc」で先頭の値を取得している。
[code language=”python”]
print(df_emp.fillna(df_emp[‘comm’].mode().iloc[0])[[‘ename’, ‘comm’]])
[/code]
[code language=”python” gutter=”false”]
0 SMITH 0.0
1 ALLEN 300.0
2 WARD 500.0
・・・（省略）・・・
[/code]
前後の値
前の値はfillnaのオプションで「method=’ffill’」を指定する。
同様に後の値は「method=’bfill’」を指定する。
時系列のデータ分析などの時に利用すると便利かも。
[code language=”python”]
print(df_emp.fillna(method=’ffill’)[[‘ename’, ‘comm’]])
[/code]
[code language=”python” gutter=”false”]
ename comm
0 SMITH NaN
1 ALLEN 300.0
2 WARD 500.0
・・・（省略）・・・
[/code]
表を並び替える
SQLならよくやるやつ。
SELECT ename, sal, comm FROM emp ORDER BY sal DESC, comm DESC;
これを自前で作るとなかなか面倒だけど、Pandasなら楽勝。
[code language=”python”]
print(df_emp.sort_values(by=["sal", "comm"], ascending=[False,False])[[‘ename’, ‘sal’, ‘comm’]])
[/code]
[code language=”python” gutter=”false”]
ename sal comm
8 KING 5000 NaN
7 SCOTT 3000 NaN
12 FORD 3000 NaN
・・・（省略）・・・
[/code]
二つの表を結合する
これもSQLなら簡単だけど、自前で作るとかなり面倒。
SELECT emp.ename, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno;
Pandasではいくつか方法があるけれど、今回は汎用的な「merge」を使用する。
[code language=”python”]
print(pd.merge(df_emp, df_dept, how=’inner’, on=’deptno’)[[‘ename’, ‘dname’]])
[/code]
[code language=”python” gutter=”false”]
ename dname
0 SMITH RESEARCH
1 JONES RESEARCH
2 SCOTT RESEARCH
・・・（省略）・・・
[/code]
表を自己結合する
自分自身と結合するやつ。
emp表は上司のempnoがあるので、emp表同士を結合して上司のenameを表示する。
SELECT worker.ename, worker.mgr, manager.ename FROM emp worker INNER JOIN emp manager ON worker.mgr = manager.empno;
Pandasならこう。
[code language=”python”]
print(pd.merge(df_emp, df_emp, how=’inner’, left_on=’mgr’, right_on=’empno’)[[‘ename_x’, ‘mgr_x’, ‘ename_y’]])
[/code]
[code language=”python” gutter=”false”]
ename_x mgr_x ename_y
0 SMITH 7902.0 FORD
1 ALLEN 7698.0 BLAKE
2 WARD 7698.0 BLAKE
・・・（省略）・・・
[/code]
表を外部結合する
結合する対象が存在しないデータも表示する場合。
dept表の「OPERATIONS」は所属する従業員がいない
(emp表にdeptnoが40のデータが存在しない）ので、
単純に結合すると表示されない。
SQLならLEFT JOINするところ。
SELECT e.ename, d.dname FROM dept d LEFT JOIN emp e ON e.deptno = d.deptno;
Pandasも基本は一緒。結合方法「how=’left’」を指定する。
[code language=”python”]
print(pd.merge(df_dept, df_emp, how=’left’, on=’deptno’)[[‘ename’, ‘dname’]])
[/code]
[code language=”python” gutter=”false”]
ename dname
0 CLARK ACCOUNTING
1 KING ACCOUNTING
・・・（省略）・・・
14 NaN OPERATIONS
[/code]
表の件数を取得する
SELECT COUNT(*) FROM emp;
Pandasならこう。
[code language=”python”]
print(len(df_emp))
[/code]
ちなみに、列を指定するとNULLを除外してくれる。
[code language=”python”]
print(df_emp[‘comm’].count())
[/code]
列の合計を取得する
SELECT SUM(comm) FROM emp;
Pandasならこう。
[code language=”python”]
print(df_emp[‘comm’].sum())
[/code]
[code language=”python” gutter=”false”]
2200.0
[/code]
列の平均を取得する
SELECT AVG(comm) FROM emp;
Pandasならこう。
[code language=”python”]
print(df_emp[‘comm’].mean())
[/code]
[code language=”python” gutter=”false”]
550.0
[/code]
列の値ごとに集計し、件数を取得する
SQLなら「GROUP BY」句を使用するヤツ。
SELECT job, COUNT(*) FROM emp GROUP BY JOB;
Pandasも基本はいっしょ。
[code language=”python”]
print(df_emp.groupby(‘job’)[‘job’].count())
[/code]
[code language=”python” gutter=”false”]
job
ANALYST 2
CLERK 4
MANAGER 3
・・・（省略）・・・
[/code]
列の値ごとに集計し、合計を取得する
SELECT job, SUM(sal) FROM emp GROUP BY JOB;
Pandasならこう。
[code language=”python”]
print(df_emp.groupby(‘job’)[‘sal’].sum())
[/code]
[code language=”python” gutter=”false”]
job
ANALYST 6000
CLERK 4150
MANAGER 8275
・・・（省略）・・・
[/code]
集計した結果を条件に抽出する
SQLならHAVINGを使うケース。
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) >= 2000;
Pandasならこう。reset_indexで列名を再設定しないと「query」が使えないので注意。
[code language=”python”]
print(df_emp.groupby(‘deptno’)[‘sal’].mean().reset_index().query(‘sal >= 2000’))
[/code]
[code language=”python” gutter=”false”]
deptno sal
0 10 2916.666667
1 20 2175.000000
[/code]
検索結果を条件にして、さらに絞り込む（副問い合わせ）
SQLなら副問い合わせで絞り込むケース。
SELECT ename FROM emp WHERE empno IN ( SELECT mgr FROM emp WHERE job = 'CLERK' );
Pandasならこう。
[code language=”python”]
print(df_emp[df_emp[‘empno’].isin(df_emp[df_emp[‘job’] == ‘CLERK’][‘mgr’])][‘ename’])
[/code]
[code language=”python” gutter=”false”]
5 BLAKE
6 CLARK
7 SCOTT
・・・（省略）・・・
[/code]
今回はここまで。
もっと色々できると思うけど、その辺りはおいおい。
コメント