6
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Last updated at Posted at 2019-10-14

#はじめに
こんな方にオススメ。
・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

6
9
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?