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

panda2

前回の続き。
「SQLのこれって、Pandasでどうやるんだっけ???」を、SQLと比較しながら解説していきます。
なお、データの事前準備のやり方などは
SQLのこれって、Python+Pandasだとどうやるんだっけ?(前編)を参照してください。

 

NULLを別の値に変換する

SQLだとこんな感じにやるヤツ。

SELECT ename, nvl(comm, 0) AS nvlcomm FROM emp;

Pandasなら、こう

print(df_emp.fillna({'comm': 0})[['ename', 'comm']])
     ename  nvlcomm
0    SMITH      0.0
1    ALLEN    300.0
2     WARD    500.0

・・・(省略)・・・

ちなみに、全列をすべて同じ値で変換するならこうも書ける。

print(df_emp.fillna(0)[['ename', 'comm']])

 

NULLを平均値、中央値、最頻値、前後の値に変換する

SQLではやったことないけど、Pandasならこう言う変換もできる。

平均値

平均値は「mean」を使用する。

print(df_emp.fillna(df_emp['comm'].mean())[['ename', 'comm']])
0    SMITH   550.0
1    ALLEN   300.0
2     WARD   500.0

・・・(省略)・・・

中央値

中央値は「median」を使用する。

print(df_emp.fillna(df_emp['comm'].median())[['ename', 'comm']])
0    SMITH   400.0
1    ALLEN   300.0
2     WARD   500.0

・・・(省略)・・・

最頻値

最頻値は「mode」を使用する。なお、「mode」はDataFrameを返却するので、
「iloc」で先頭の値を取得している。

print(df_emp.fillna(df_emp['comm'].mode().iloc[0])[['ename', 'comm']])
0    SMITH     0.0
1    ALLEN   300.0
2     WARD   500.0

・・・(省略)・・・

前後の値

前の値はfillnaのオプションで「method=’ffill’」を指定する。
同様に後の値は「method=’bfill’」を指定する。
時系列のデータ分析などの時に利用すると便利かも。

print(df_emp.fillna(method='ffill')[['ename', 'comm']])
     ename    comm
0    SMITH     NaN
1    ALLEN   300.0
2     WARD   500.0

・・・(省略)・・・

 

表を並び替える

SQLならよくやるやつ。

SELECT ename, sal, comm FROM emp
ORDER BY sal DESC, comm DESC;

これを自前で作るとなかなか面倒だけど、Pandasなら楽勝。

print(df_emp.sort_values(by=["sal", "comm"], ascending=[False,False])[['ename', 'sal', 'comm']])
     ename   sal    comm
8     KING  5000     NaN
7    SCOTT  3000     NaN
12    FORD  3000     NaN

・・・(省略)・・・

 

二つの表を結合する

これもSQLなら簡単だけど、自前で作るとかなり面倒。

SELECT emp.ename, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;

Pandasではいくつか方法があるけれど、今回は汎用的な「merge」を使用する。

print(pd.merge(df_emp, df_dept, how='inner', on='deptno')[['ename', 'dname']])
     ename       dname
0    SMITH    RESEARCH
1    JONES    RESEARCH
2    SCOTT    RESEARCH

・・・(省略)・・・

 

表を自己結合する

自分自身と結合するやつ。
emp表は上司のempnoがあるので、emp表同士を結合して上司のenameを表示する。

SELECT worker.ename, worker.mgr, manager.ename
FROM emp worker INNER JOIN emp manager
ON worker.mgr = manager.empno;

Pandasならこう。

print(pd.merge(df_emp, df_emp, how='inner', left_on='mgr', right_on='empno')[['ename_x', 'mgr_x', 'ename_y']])
   ename_x   mgr_x ename_y
0    SMITH  7902.0    FORD
1    ALLEN  7698.0   BLAKE
2     WARD  7698.0   BLAKE

・・・(省略)・・・

 

表を外部結合する

結合する対象が存在しないデータも表示する場合。
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’」を指定する。

print(pd.merge(df_dept, df_emp, how='left', on='deptno')[['ename', 'dname']])
     ename       dname
0    CLARK  ACCOUNTING
1     KING  ACCOUNTING

・・・(省略)・・・

14     NaN  OPERATIONS

 

表の件数を取得する

SELECT COUNT(*) FROM emp;

Pandasならこう。

print(len(df_emp))

ちなみに、列を指定するとNULLを除外してくれる。

print(df_emp['comm'].count())

 

列の合計を取得する

SELECT SUM(comm) FROM emp;

Pandasならこう。

print(df_emp['comm'].sum())
2200.0

 

列の平均を取得する

SELECT AVG(comm) FROM emp;

Pandasならこう。

print(df_emp['comm'].mean())
550.0

 

列の値ごとに集計し、件数を取得する

SQLなら「GROUP BY」句を使用するヤツ。

SELECT job, COUNT(*) FROM emp GROUP BY JOB;

Pandasも基本はいっしょ。

print(df_emp.groupby('job')['job'].count())
job
ANALYST      2
CLERK        4
MANAGER      3

・・・(省略)・・・

 

列の値ごとに集計し、合計を取得する

SELECT job, SUM(sal) FROM emp GROUP BY JOB;

Pandasならこう。

print(df_emp.groupby('job')['sal'].sum())
job
ANALYST      6000
CLERK        4150
MANAGER      8275

・・・(省略)・・・

 

集計した結果を条件に抽出する

SQLならHAVINGを使うケース。

SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno HAVING AVG(sal) >= 2000;

Pandasならこう。reset_indexで列名を再設定しないと「query」が使えないので注意。

print(df_emp.groupby('deptno')['sal'].mean().reset_index().query('sal >= 2000'))
   deptno          sal
0      10  2916.666667
1      20  2175.000000

 

検索結果を条件にして、さらに絞り込む(副問い合わせ)

SQLなら副問い合わせで絞り込むケース。

SELECT ename FROM emp WHERE empno IN
( SELECT mgr FROM emp WHERE job = 'CLERK' );

Pandasならこう。

print(df_emp[df_emp['empno'].isin(df_emp[df_emp['job'] == 'CLERK']['mgr'])]['ename'])
5     BLAKE
6     CLARK
7     SCOTT

・・・(省略)・・・

今回はここまで。
もっと色々できると思うけど、その辺りはおいおい。


この投稿へのコメント

コメントはありません。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA


この投稿へのトラックバック

トラックバックはありません。

トラックバック URL