pandas:【SQL文で例える】データ抽出方法
準備
前提
- 行のインデックス番号、Seriesなどの基礎
- データ（データフレーム）作成
- データ抽出のあれこれ
- pandasまとめのデータ抽出に関するリンク
- pandasまとめ
- SQLの知識
- あればSQLから考えられる
データ作成
import pandas as pd
dic = {'id':['01','02','03','04','05'],'name':['a','b','c','d','e'],'value':[70,38,29,93,18],'fold':[1,0,0,2,1],'file':['f1','f2','f3','f4','f5'],'file_base':['f1','f3','f3','f5','f3'],'key':['a001','a002','a003','b001','c001']}
df =pd.DataFrame(dic)
print(df)
print(df.shape)id name value fold file file_base key 0 01 a 70 1 f1 f1 a001 1 02 b 38 0 f2 f3 a002 2 03 c 29 0 f3 f3 a003 3 04 d 93 2 f4 f5 b001 4 05 e 18 1 f5 f3 c001 (5, 7)
- サイズ(5, 7)のデータフレーム作成
- id,name,file,file_base…文字列型
- value,fold…数値型
- 一番左は行のインデックス番号
WHERE句
select * from df where fold = 1;
例1
df[df.fold==1]id name value fold file file_base key 0 01 a 70 1 f1 f1 a001 4 05 e 18 1 f5 f3 c001
- df[行条件]で抽出している
- 列はすべてなので列名の指定はなし
- 行条件
- df.fold==1
- 行の指定（ブールインデックス参照）
- fold列が1と等しい行のみTrueになったデータ
例2
df.iloc[df.index[df.fold==1]]id name value fold file file_base key 0 01 a 70 1 f1 f1 a001 4 05 e 18 1 f5 f3 c001
- df.loc[行番号]で抽出している
※例1と違って行番号なので注意- 列はすべてなので列名の指定はなし
- 行番号
- df.index[df.fold==1]
- df.fold==1
- fold列が1と等しい行のみTrueになったデータ
- df.index[…]
- 引数にブールインデックスを指定
- Trueになっている行のみインデックス番号を返す
- この例だとインデックス番号は0と4
select id, name from df where fold = 0;
例1
df[df.fold==0][['id','name']]id name 1 02 b 2 03 c
- df[行条件][列名]で抽出している
- 行条件
- df.fold==0
- 行の指定（ブールインデックス参照）
- fold列が0と等しい行のみTrueになったデータ
- 列名
- ['id’,’name’]
- リスト形式で複数列指定
例2（df.locを使用した例）
df.loc[df.index[df.fold==0],['id','name']]id name 1 02 b 2 03 c
- df.loc[行番号,列名]で抽出している
※例1と違って行番号なので注意 - 行番号
- df.index[df.fold==0]
- df.fold==0
- fold列が0と等しい行のみTrueになったデータ
- df.index[…]
- 引数にブールインデックスを指定
- Trueになっている行のみインデックス番号を返す
- この例だとインデックス番号は1と2
- 列名
- ['id’,’name’]
- リスト形式で複数列指定
副問合せ、GROUP BY
select * from df where file in (select file_base from df where fold != 2 group by file_base);
全文
df[df['file'].isin(df.loc[:,'file_base'][df['fold'] != 2].unique())]id name value fold file file_base key 0 01 a 70 1 f1 f1 a001 2 03 c 29 0 f3 f3 a003
- 次の3つの構成
- SELECT句
- df[…]
- 全列なので行のみ指定
- WHERE句
- df['file’].isin(…)
- file列を絞り込む
- …に対象のリスト
- サブクエリ（副問い合わせ部分）
- df.loc[:,’file_base’][df['fold’] != 2].unique()
- SQL文ではサブクエリの部分
- fold列が2以外の条件（where fold !=2）
- file_base列でuniqueを使ってGroup byと同等の処理（group by file_base）
サブクエリ（df.loc[:,’file_base’][df['fold’] != 2].unique()）
w = df.loc[:,'file_base'][df['fold'] != 2].unique()
w1 = df.loc[:,'file_base']
w2 = w1[df['fold'] != 2]
w3 = w2.unique()
print(w)
print(w.shape)
print(w1)
print(w1.shape)
print(w2)
print(w2.shape)
print(w3)
print(w3.shape)['f1' 'f3'] (2,) 0 f1 1 f3 2 f3 3 f5 4 f3 Name: file_base, dtype: object (5,) 0 f1 1 f3 2 f3 4 f3 Name: file_base, dtype: object (4,) ['f1' 'f3'] (2,)
- w
- ['f1’ 'f3’]がwの結果
- w1
- df.locを使って列絞り込み
- 行は「:」なので全5行
- (4, )のサイズになっている（Series）
- w2
- fold列が2でないデータのboolインデックス
- w1の全5行からboolインデックス参照
- 結果4行
- w3
- f1,f1,f1,f3のデータをユニーク（unique）にする
- w結果['f1’ 'f3’]を得る
WHERE句（df['file’].isin(…)）
sub_query = df.loc[:,'file_base'][df['fold'] != 2].unique()
w = df['file'].isin(sub_query)
print(w)0 True 1 False 2 True 3 False 4 False Name: file, dtype: bool
- sub_query
- 前項の通り[f1,f3]
- リストになっている
- w
- file列がf1,f3の行がTrueになっているブールインデックス
- インデックス番号0,2の行がTure
SELECT句（df[…]）
w = df['file'].isin(df.loc[:,'file_base'][df['fold'] != 2].unique())
rtn = df[w]
print(rtn)id name value fold file file_base key 0 01 a 70 1 f1 f1 a001 2 03 c 29 0 f3 f3 a003
- w
- 前項の通りブールインデックス
- file列がf1,f3の行がTrue
- rtn
- ブールインデックス参照により行が抽出される
- 抽出される行はインデックス番号が0,2
- 最初の”全文”の結果と一致する
部分一致（LIKE）
select * from df where key like 'a%’;（前方一致）
select * from df where key like '%001’;（後方一致）
全文
x = df[df['key'].str.contains('^a')]
y = df[df['key'].str.contains('001$')]
print(x)
print(x.shape)
print(y)
print(y.shape)id name value fold file file_base key 0 01 a 70 1 f1 f1 a001 1 02 b 38 0 f2 f3 a002 2 03 c 29 0 f3 f3 a003 (3, 7) id name value fold file file_base key 0 01 a 70 1 f1 f1 a001 3 04 d 93 2 f4 f5 b001 4 05 e 18 1 f5 f3 c001 (3, 7)
- a
- key列がaで始まる行を抽出
- 「^a」
- 正規表現の記号＝「^」
- aで始まると言う意味
- b
- key列が001で終わる行を抽出
- 「001$」
- 正規表現の記号＝「$」
- 001で終わると言う意味
LIKE句の部分だけ実行
df['key'].str.contains('^a')0 True 1 True 2 True 3 False 4 False Name: key, dtype: bool
- df['key’]
- key列を指定
- str.contains('a’)
- 文字列aを含む行がTrue
- インデックス番号0,1,2がTrueのブールインデックス
集計（COUNT(*) GROUP BY）
select count(*) from df group by file_base;
a = df.file_base.value_counts()
print(a)f3 3 f1 1 f5 1 Name: file_base, dtype: int64
- df.file_base
- 列名を指定
- group by句に相当
- value_counts()
- 集計関数
- count(*)に相当
- f3が3件、f1とf5は1件ずつの結果を得た