ひと目でわかるPandasとSQLの対応表

はじめに

こんな方にオススメ。
・SQLは知っているが、pandasとの対応付けが知らない方。
・簡単に動かしながら理解を深めたい方。

環境

Anaconda 3.7
Jupyter Notebook

ソース

from sklearn.datasets import load_boston        
import pandas as pd     

boston = load_boston() # データセットの読み込み      
df = pd.DataFrame(boston.data,columns=boston.feature_names)
df["test_data"] = "test_data_val"       
df.loc[df.NOX < 0.5, "NOX"]=None # NaN値作成のため更新
# 以下Ptyhon内のコードを記載し、実行

対応表

項目	SQL	Python
列選択	select RM, AGE from df	df[["RM", "AGE"]]
選択列名称変更	select RM as af_rm, AGE　as af_age from df	df.rename(columns={"RM":"af_rm", "AGE":"af_age"}, inplace=True) df
選択列追加	select RM, AGE, '1' as flg from df	df["flg"] = "1" # df.assign(flg="1")でも可 df
条件抽出(単一)	select RM, AGE from df where RM > 6	df[df["RM"] > 6]
条件抽出(複数)	select RM, AGE from dfwhere RM > 6 and AGE = '100'	df[["RM", "AGE"]].query('RM > 6 and AGE == "100"')
条件抽出(IS NULL)	select RM, AGE from df where NOX IS NULL	df[df["NOX"].isnull()]
条件抽出(IS NOT NULL)	select RM, AGE from df where NOX IS NOT NULL	df[df["NOX"].notnull()]
条件抽出(BETWEEN)	select RM, AGE from df where RM between 6 and 8	df[["RM", "AGE"]].query('RM >= 6 and RM <= 8')
条件抽出(前方一致)	select RM, AGE from df where test_data like 'te%'	df[df["test_data"].str.startswith('te')]
条件抽出(部分一致)	select RM, AGE from df where test_data like '%t_d%'	df[df["test_data"].str.contains('[ぁ-んァ-ン一-龥a-z]t_d[ぁ-んァ-ン一-龥a-z]', regex=True)]
条件抽出(後方一致)	select RM, AGE from df where test_data like '%ta'	df[df["test_data"].str.endswith('al')]
更新	update df set NOX=null where NOX < 0.5	df.loc[df.NOX < 0.5, "NOX"]=None df
集計(件数)	select count(RM) from df	df.RM.describe()
集計(平均値)	select avg(RM) from df	df.RM.describe()
集計(標準偏差)	select STDEV(RM) from df	df.RM.describe()
集計(最小値)	select min(RM) from df	df.RM.describe()
集計(最大値)	select max(RM) from df	df.RM.describe()
重複削除表示	select distinct CHAS from df	df.drop_duplicates(["CHAS"]).head()
行削除	delete from df where index in (0,2,4)	df.drop(df.index[[0,2,4]],inplace=True) df
列削除	ALTER TABLE df DROP ZN	del df["ZN"] df
グループ化	select ZN, count(ZN) from df group by ZN	df.groupby("ZN").ZN.count()
ソート(昇順)	select RM from df order by asc	df["RM"].sort_values(ascending=True)
ソート(降順)	select RM from df order by desc	df["RM"].sort_values(ascending=False)

結合編

2つ目のコピーテーブルを作成

from sklearn.datasets import load_boston        
import pandas as pd     

boston = load_boston() # データセットの読み込み      
df = pd.DataFrame(boston.data,columns=boston.feature_names)     
df["test_data"] = "test_data_val"       
df.loc[df.NOX < 0.5, "NOX"]=None # NaN値作成のため更新      

df2 = df.copy() # コピーテーブル作成       
df2["test_data"] = "no_join_test_data"      

df.loc[df.index[[0,1]], "test_data"]="join_key" # 1,2行目をjoinkey作成     
df2.loc[df.index[[0]], "test_data"]="join_key" # 1行目をjoinkey作成            

# 以下Ptyhon内のコードを記載し、実行

項目	SQL	Python
内部結合	select df.RM, df.AGE from df inner join df2 on df.test_data = df2.test_data	df_mg=pd.merge(df,df2, how="inner", on = "test_data") df_mg[["RM_x","AGE_x"]]
外部結合	select df.RM, df.AGE from df outer join df2 on df.test_data = df2.test_data	df_mg=pd.merge(df,df2, how="outer", on = "test_data") df_mg[["RM_x","AGE_x"]]

その他

index振り直し

df.reset_index(drop=True)

カーソルループ処理相当

# DataFrameのレコード分ループ
# iは対象レコード番号(rowid相当)
# vはレコード情報。取得するカラムを指定して取得。
for i,v in df.iterrows():
  print("i=",i)
  print("v=",v["RM"])

参照

https://qiita.com/takaiyuk/items/5232442eaeb01299b265
https://qiita.com/ysdyt/items/9ccca82fc5b504e7913a

GitHub

上記実行済のipynbファイルは以下をプルして下さい。
https://github.com/bossjerom/python/tree/master/pandas