Python Coding Reference
— 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"')

※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"])')

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])

2021-10-14

Posted by tfurukaw