Skip to content

5-1. pandas(DataFrame)-likeSQL

python

import pandas as pd
import numpy as np

データ読込

df_emp = pd.read_csv('emp.csv', encoding='utf-8')
df_emp
df_dept = pd.read_csv('dept.csv', encoding='utf-8')
df_dept

SELECT

'empno','ename'列のみ表示

SQL

SELECT empno, ename FROM emp;

pandas

df_emp[['empno', 'ename']]
df_emp.loc[:, ['empno', 'ename']]

式の適用(列同士の計算)

SQL

SELECT empno, ename (sal + NVL(comm, 0)) as total FROM emp;

pandas

* 一般的な方法
df_emp['total'] = df_emp['sal'] + df_emp['comm'].fillna(0)

* 名前付き関数を使う方法
def f_add(df1):
    return df1['sal'] + np.nan_to_num(df1['comm'])
df_emp['total'] = df_emp.apply(f_add, axis=1)
※axis=1で行ごとに処理することを表す

* lambda式を使う方法
f_add = lambda x, y: x + np.nan_to_num(y)
df_emp['total'] = df_emp.apply(lambda row:f_add(*row[['sal','comm']]), axis=1)

WHERE

pandasではブールインデックスを使用する方法とqueryメソッドを使用する方法があるので、両方記述します

deptno=10 のみ取得

SQL

SELECT * FROM emp WHERE dept = 10;

pandas

df_emp[df_emp.dept == 10]
df_emp[df_emp['dept'] == 10]
df_emp.query('dept == 10')

job = SALESMAN のみ取得

SQL

SELECT * FROM emp WHERE job = 'SALESMAN';

pandas

df_emp[(df_emp.job == 'SALESMAN')]
df_emp.query('job == "SALESMAN"')

job = SALESMAN 以外を取得

SQL

SELECT * FROM emp WHERE job != 'SALESMAN';

pandas

df_emp[(df_emp.job != 'SALESMAN')]
df_emp.query('job != "SALESMAN"')

deptno=30 and sal>=1500 の複合条件を満たすものを取得

SQL

SELECT * FROM emp WHERE deptno=30 and sal>=1500;

pandas

df_emp[(df_emp.dept == 30) & (df_emp.sal >= 1500)]
df_emp.query('dept == 30 and sal >= 1500')

NaN の判定(comm がNaN以外を表示)

SQL

SELECT * FROM emp WHERE comm IS NOT NULL;

pandas

df_emp[(~df_emp.comm.isnull())]
df_emp.query('comm.notna()', engine='python')

job列が文字列「PRESIDENT」か「MANAGER」

SQL

SELECT * FROM emp WHERE job IN ('PRESIDENT', 'MANAGER');

pandas

df_emp[(df_emp.job.isin(['PRESIDENT', 'MANAGER']))]
df_emp.query('job.isin(["PRESIDENT", "MANAGER"])')

job列が文字列「PRESIDENT」か「MANAGER」

SQL

SELECT * FROM emp WHERE job NOT IN ('PRESIDENT', 'MANAGER');

pandas

df_emp[~(df_emp.job.isin(['PRESIDENT', 'MANAGER']))]
df_emp.query('not job.isin(["PRESIDENT", "MANAGER"])')
df_emp.query('job not in (["PRESIDENT", "MANAGER"])')

リストを変数で保持する場合は下記の記述も可能
l=["PRESIDENT", "MANAGER"]
df_emp.query('job not in @l')

ename列に文字列「藤」を含む

SQL

SELECT * FROM emp WHERE ename LIKE '%藤%';

pandas

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

ename列が文字列「藤」で始まる

SQL

SELECT * FROM emp WHERE ename LIKE '藤%';

pandas

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

ename列が文字列「郎」で終わる

SQL

SELECT * FROM emp WHERE ename LIKE '%郎';

pandas

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

ename列が文字列「藤郎」に一致(正規表現)

SQL

SELECT * FROM emp WHERE ename LIKE '%藤%郎';

pandas

str.containsを使用した正規表現
df_emp[df_emp.ename.str.contains('.*藤.*郎')]
df_emp.query('ename.str.contains(".*藤.*郎")')
※「藤」で始まるものを含めたくなければ、
df_emp.query('ename.str.contains(".+藤.*郎")')

str.matchを使用した正規表現
df_emp.query('ename.str.match(".*藤.*郎")')

※「郎」で終わるもの、を正規表現で
df_emp.query('ename.str.contains(".+郎")')
※正規表現を効かせたくない場合は、containsの引数に「regex=False」を付加
df_emp.query('ename.str.contains(".+郎", regex=False)')

FROM-JOIN

列名が異なる場合は、right_on, left_onでそれぞれ列名を指定

SQL

SELECT empno, ename FROM emp;

pandas

pd.merge(df_emp, df_dept, how='inner', right_on='deptno', left_on='dept')

列名が同じであれば on句 で1つの列名を指定するだけで結合できる

SQL

SELECT empno, ename FROM emp;

pandas

pd.merge(df_emp, df_dept.rename(columns={'deptno': 'dept'}), how='inner', on='dept')

※ indicatorにtrueを指定すると、_mergeという列が追加され、both, left_only, right_onlyの分類情報が取得できる pd.merge(df_emp, df_dept, how='inner', right_on='deptno', left_on='dept', indicator=True)

外部結合

SQL

SELECT empno, ename FROM emp;

pandas

pd.merge(df_emp, df_dept, how='left', right_on='deptno', left_on='dept')

結合して必要な列だけ取得(SELECT)

SQL

SELECT empno, ename FROM emp;

pandas

pd.merge(df_emp, df_dept, how='left', right_on='deptno', left_on='dept')[['empno', 'ename', 'dname', 'loc', 'job']]

GROUP BY

グルーピングして統計情報(count, sum, avg等)を取得

SQL

SELECT dept, COUNT(sal), MAX(sal), MIN(sal), AVG(sal), STD(sal) FROM emp GROUP BY dept;

pandas

df_emp.groupby('dept')[['sal']].describe()
df_emp.groupby('dept')[['sal']].agg(['count', 'max', 'min', 'mean', 'std'])

dept別にグルーピングしてsalがdept別で最大の ename を取得

loc別にグルーピングしてsal+commがdept別で最大の ename を取得

ORDER BY

deptno の昇順に表示

SQL

SELECT empno, ename FROM emp ORDER BY dept;

pandas

df_emp[['dept', 'empno', 'ename']].sort_values(by='dept', ascending=True)

sal の降順に表示

SQL

SELECT empno, ename FROM emp ORDER BY sal DESC;

pandas

df_emp[['dept', 'empno', 'ename', 'sal']].sort_values(by='sal', ascending=False)

deptno の昇順, sal の降順に表示

SQL

SELECT empno, ename FROM emp ORDER BY dept, sal DESC;

pandas

df_emp[['dept', 'sal', 'empno', 'ename']].sort_values(by=['dept', 'sal'], ascending=[True, False])