pandasでは、SQLと似たデータ操作ができる。
SQLのデータ操作とpandasのデータ操作の対応をメモっておく。
個人的に利用頻度が高い順に書いてる。
limit -> head
任意の行数を抽出するために利用する。
select *
from table
limit 10
↓pandas
table.head(10)
order by -> sort_values
ソート処理
select *
from table
order by col1
↓pandas
table.sort_values(by='col1')
order byとsort_valuesのソートは、デフォルトで昇順になる。
order byなら最後にdescをつけるが、sort_valuesでは、ascending=Falseとする（Trueなら昇順）。
table.sort_values(by='col1', ascending=False)
また、複数の条件でソートも可能
select *
from table
order by col1, col2 desc
↓pandas
table.sort_values(by=['col1', 'col2'], ascending=[True, False])
where -> []
select *
from table
where col1 = 'a'
↓pandas
table[table.col1 == 'a']
in句
select *
from table
where col1 in ('a', 'b')
↓pandas
table[table.col1.isin(['a', 'b'])]
複数条件
select *
from table
where col1 = 'a' and col2 = 1
↓pandas
table[(table.col1 == 'a')&(table.col2 == 1)]
各条件を()で囲って、&で結ぶ。
andではなく、orの場合は、|を利用する
table[(table.col1 == 'a')|(table.col2 == 1)]
これらの条件指定は、下に書いてある、deleteやupdateの時にも利用できる。
関数 -> agg
select
sum(col1),
avg(col1),
max(col1),
min(col2),
count(col2),
count(distinct(col2)),
from table
↓pandas
table.agg({'col1': ['sum', 'mean', 'max'], 'col2': ['min', 'count', 'nunique']})
nuniqueが、count distinctに対応。
distinct関数は次のようになる。
select
distinct(col1)
from talbe
↓pandas
table.agg({'col1': 'unique'})
sqlでは、集計関数とdistinctは一緒には使えないが、pandasでは利用可能
table.agg({'col1': ['sum', 'mean', 'max', 'min', 'count', 'nunique', 'unique']})
のようにできる。
sqlとpandasの関数対応
- sum -> sum
- avg -> mean
- max -> max
- min -> min
- count -> count
- count distinct -> nunique
- distinct -> unique
group by -> groupby
select
col1,
sum(col2)
from table
group by col1
↓pandas
table.groupby('col1').agg({'col2': 'sum'})
groupbyに指定されたカラムは、結果のデータフレームにおいてインデックスになってしまうので、
table.groupby('col1', as_index=False).agg({'col2': 'sum'})
のように、as_index=Falseを入れると扱いやすい。
複数のカラムでgroup byする場合
select
col1,
col2,
sum(col3)
from table
group by col1, col2
↓pandas
table.groupby(['col1', 'col2'], as_index=False).agg({'col3': 'sum'})
join -> merge
select
*
from table t
left join table2 t2
on table.col1 = table2.col1
↓pandas
pd.merge(table, table2, on='col1', how='left')
howには、inner、outer、rightも揃ってる。
onで複数のカラムを利用する場合
select
*
from table t
left join table2 t2
on table.col1 = table2.col1
and table.col2 = table2.col2
↓pandas
pd.merge(table, table2, on=['col1', 'col2'], how='left')
onにリストを指定。
update -> loc[]
update table
set col1 = 'b'
where col2 = 1
↓pandas
table.loc[table.col2 == 1, 'col1'] = 'b'
.locの第一引数にwhere条件、第二引数に更新対象のカラムを指定する。
delete -> []
delete
from table
where col1 = 'a'
↓pandas
table = table[table.col1 != 'a']
条件一致しないものだけ抽出して、削除する。
columnの別名 -> rename
select
col1 as new_col1
from table
↓pandas
table.rename(columns={'col1': 'new_col1'})
union -> concat
select
*
from table
union
select
*
from table2
↓pandas
pd.concat([table, table2], ignore_index=True)
concat関数の第一引数は、リストなので3つ以上のデータフレームのユニオンも可能。
ignore_indexを指定しないとインデックスの値は、UNION前のデータフレームのものが引き継がれる。
つまり、インデックスの値がかぶることがあるので、指定しておいた方が良いかも。
insert -> append
insert into
table(
col1,
col2
)
values(
'a',
'2'
)
↓pandas
table = table.append({'col': 'a', 'b': 2}, ignore_index=True)
ignore_indexは、unionと同様。
case when -> apply
select
case when col1 = 'a' then 1
when col1 = 'b' then 2
else 3 end as case_col1
from table
↓pandas
def case_when(x):
if x.col1 == 'a':
return 1
elif x.col1 == 'b':
return 2
else:
return 3
table.apply(case_when, axis=1)
axis=1としているのは、行単位にデータを取ってくるため。axis=0としたらcase_whenのxには、列データが入ってくる。
printでxを表示させればわかりやすい。xの型はpandasのSeries型になる。
カラムを追加して返すことも可能。
下の方法は、上の方法で
table['case_col1'] = table.apply(case_when, axis=1)
とした場合と結果は一致する。
select
*,
case when col1 = 'a' then 1
when col1 = 'b' then 2
else 3 end as case_col1
from table
↓pandas
def case_when(x):
if x.col1 == 'a':
ret = 1
elif x.col1 == 'b':
ret = 2
else:
ret = 3
x['case_col1'] = ret
return x
table.apply(case_when, axis=1)
group by case when -> groupby.apply
select
col1,
sum(case when col2 < 10 then col3 else 0 end) as sum_under10_col3,
sum(case when col2 >= 10 then col3 else 0 end) as sum_over10_col3
from table
group by col1
↓pandas
def case_when(df):
sum_under10_col3 = df[df.col2 < 10].col3.sum()
sum_over10_col3 = df[df.col2 >= 10].col3.sum()
return pd.Series({'sum_under10_col3': sum_under10_col3, 'sum_over10_col3': sum_over10_col3})
table.groupby('col1').apply(case_when).reset_index()
結構煩雑だけど、一応同じことはできる。
case_when関数には、データフレームが送られるので、それらを集計して、その結果をSeriesで返している。
reset_index()は、col1をインデックスから外してる。
applyを使ってる時に、as_index=Falseにするとcol1が自動的に列に加わってくれない。おそらく、case_when関数の中でreturnするときにSeriesの中にcol1を含めないといけない。
それは面倒なので、reset_indexで対応してる。