• トップ
  • ブログ一覧
  • SQLとpandasで同じ操作をする方法
  • SQLとpandasで同じ操作をする方法

    あだっちー(エンジニア)あだっちー(エンジニア)
    2023.01.18

    IT技術

    はじめに

    業務でSQLとpandasを使う機会があり、両者で同じ操作をする際の基本的な構文の使い方を整理していきたいと思います。

    pandasは、pythonのデータ解析ライブラリで具体的には下記のようなことが出来ます。

    • csvやexcelなど様々な形式のファイルの入出力
    • データの処理(欠損値処理・結合・ピボット・抽出)
    • データの簡単なグラフ化

    pandasにはデータフレームなどの独自のデータ構造があり、表形式のデータをSQLのように高速かつ、簡単に操作することが出来ます。

    それでは、仮のテーブル名は「sample」として、SQL・pandasの順にコードを記述していきます。

    基本的な操作

    基本的な操作をささっと紹介していきます!

    列選択

    1SELECT col1, col2
    2FROM sample
    1sample[['col1', 'col2']]

    列追加

    1SELECT *, 1 AS flg
    2FROM sample
    1sample["flg"] = 1

    列名変更

    1SELECT col1 AS re_col1, col2 AS re_col2
    2FROM sample
    1sample.rename(columns={"col1": "re_col1", "score": "re_col2"})
    2#もしくは全カラム付け直す
    3sample_table.columns = ["id","re_name","re_score","date"]

    条件抽出(WHERE句)

    1SELECT *
    2FROM sample
    3WHERE col3 > 50;
    1sample[sample["col3"] > 50]

    複数条件抽出

    1SELECT * 
    2FROM sample
    3WHERE col3 > 50 AND col4 = 1
    1sample[(sample["col3"] > 50) & (sample["col4"] == 1)]

    並び替え(ORDER BY句)

    1SELECT * FROM sample 
    2-- デフォルトで昇順
    3ORDER BY col3 
    4-- DESCを付けると降順
    5ORDER BY col3 DESC
    1# デフォルトで昇順
    2sample.sort_values("col3")
    3# ascending=Falseで降順
    4sample.sort_values("col3", ascending=False)

    グループ化・条件分岐の操作

    グループ化(GROUP BY)

    1SELECT col5 , SUM(col6) FROM sample
    2GROUP BY col5

    pandasでgroupby を使うと、デフォルトでグループラベルが index になります。

    index にしたく無い場合は as_index=False を指定するか

    グループ化した後にreset_index()をしましょう

    1sample[["col5","col6"]].groupby("col5", as_index=False).sum()

    条件分岐(CASE WHEN)

    1SELECT 
    2  CASE
    3    WHEN col6 >= 50 THEN 1 ELSE 0 
    4  END AS is_50over
    5FROM sample
    1sample['is_50over'] = sample['col6'].map(lambda x : 1 if x >= 50 else 0)

    複数条件分岐(CASE WHEN)

    1SELECT
    2  CASE
    3    WHEN col6 < 100 THEN 1
    4    WHEN col6 >= 100 AND col6 < 200 THEN 2
    5    WHEN col6 >= 200 AND col6 < 300 THEN 3
    6    ELSE 4
    7  END AS cate
    8FROM sample

    lamda関数を使って記述することも出来ますが、複数条件の場合は関数を作った方が楽な場合が多いです。

    1def cate(x):
    2  if  x < 100:
    3    return 1
    4  elif x >= 100 and x < 200:
    5    return 2
    6  elif x >= 200 and x < 300:
    7    return 3
    8  else:
    9    return 4
    10
    11sample['cate'] = sample['col6'].map(cate)

    テーブル結合の操作

    内部結合(INNER JOIN)

    1SELECT * FROM
    2  sample1 AS s1
    3INNER JOIN
    4  sample2 AS s2
    5ON
    6  s1.col1 = s2.col1
    1pd.merge(sample1, sample2, on="col1", how="inner")

    左結合(LEFT JOIN)

    1SELECT * FROM 
    2  sample1 AS s1 
    3LEFT JOIN 
    4  sample2 AS s2 
    5ON 
    6  s1.col1 = s2.col1
    1pd.merge(sample1, sample2, on="col1", how="left")

    right joinの場合は how="right"にします。

    外部結合(OUTER JOIN)

    1SELECT * FROM 
    2  sample1 AS s1 
    3OUTER JOIN 
    4  sample2 AS s2 
    5ON s1.col1 = s2.col1
    1pd.merge(sample1, sample2, on="col1", how="outer")
    あだっちー(エンジニア)

    あだっちー(エンジニア)

    おすすめ記事