Python – 5-1. pandas(DataFrame) like SQL
pandas(DataFrame)を使用して、SQLのようにデータを取得する方法をまとめてみました。
Oracle使用者にはお馴染みのemp表とdept表を例に、簡単な例をいくつか挙げてみました。
尚、データ中の名前は日本名にしています。
（一般的な名字と名前のランダムな組合わせなので実在の方もいらっしゃるかもしれませんがご了承ください）
データ読込
df_emp = pd.read_csv('emp.csv’, encoding=’utf-8′)
df_dept = pd.read_csv('dept.csv’, encoding=’utf-8′)
emp.csv
empno,ename,job,manager,hiredate,sal,comm,dept
7369,佐藤一郎,CLERK,7902,1980/12/17,800,,20
7499,鈴木三郎,SALESMAN,7698,1981/02/20,1600,300,30
7521,高橋和夫,SALESMAN,7698,1981/02/22,1250,500,30
7566,田中義男,MANAGER,7839,1981/04/02,2975,,20
7654,渡辺大介,SALESMAN,7698,1981/09/28,1250,1400,30
7698,伊藤誠,MANAGER,7839,1981/05/01,2850,,30
7782,山本尚人,MANAGER,7839,1981/06/09,2450,,10
7788,中村健一,ANALYST,7566,1987/04/19,3000,,20
7839,藤原太郎,PRESIDENT,,1981/11/17,5000,,10
7844,加藤卓也,SALESMAN,7698,1981/09/08,1500,,30
7876,吉田翔太,CLERK,7788,1987/05/23,1100,,20
7900,斎藤和也,CLERK,7698,1981/12/03,950,,30
7902,松本亮太,ANALYST,7566,1981/12/03,3000,,20
7934,木村大輝,CLERK,7782,1982/01/23,1300,,10
8001,小林政男,SALESMAN,7698,1985/03/17,1300,,
dept.csv
deptno,dname,loc
10,ACCOUNTING,TOKYO
20,RESEARCH,SENDAI
30,SALES,TOKYO
40,OPERATIONS,NAGOYA
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"')
※query句で文字列を扱うと括り記号がネストする場合、シングルクォートとダブルクォートを使い分ける。
シングルクォートとダブルクォートは逆でも可（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"])')
df_emp.query('job in (["PRESIDENT", "MANAGER"])')
リストを変数で保持する場合は下記のような記述も可能
cl=["PRESIDENT", "MANAGER"]
df_emp.query('job in @cl')
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"])')
リストを変数で保持する場合は下記のような記述も可能
cl=["PRESIDENT", "MANAGER"]
df_emp.query('job not in @cl')
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(".+藤.*郎")')
※ 正規表現を効かせたくない場合は、containsの引数に「regex=False」を付加
df_emp.query('ename.str.contains(".+郎", regex=False)')
※「郎」で終わるもの、を正規表現で表す場合は
df_emp.query('ename.str.contains(".+郎")')
・str.matchを使用した正規表現
df_emp.query('ename.str.match(".*藤.*郎")')
FROM-JOIN
merge文を使用して結合
SQL
SELECT * FROM emp e INNER JOIN dept d ON e.dept = d.deptno;
pandas
・列名が異なる場合は、right_on, left_onでそれぞれ列名を指定
pd.merge(df_emp, df_dept, how='inner', right_on='deptno', left_on='dept')
・列名が同じ名前の場合は、onに列名を1つ指定するだけで結合できる
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 * FROM emp e LEFT OUTER JOIN dept d ON e.dept = d.deptno;
pandas
pd.merge(df_emp, df_dept, how='left', right_on='deptno', left_on='dept')
結合して必要な列だけ取得して外部結合
SQL
SELECT empno, ename, dname, loc, job
FROM emp e LEFT OUTER JOIN dept d
ON e.dept = d.deptno;
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()
※describeを使用すると、下記の結果がまとめて取得できる。
count, mean, std, min, 25%, 50%, 75%, max
df_emp.groupby('dept')[['sal']].agg(['count', 'max', 'min', 'mean', 'std'])
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])
