pandasとSQLの対応

pandasでは、SQLと似たデータ操作ができる。
SQLのデータ操作とpandasのデータ操作の対応をメモっておく。
個人的に利用頻度が高い順に書いてる。

### limit -> head
任意の行数を抽出するために利用する。

```sql
select *
from table
limit 10
```
↓pandas

```python
table.head(10)
```

### order by -> sort_values
ソート処理

```sql
select *
from table
order by col1
```
↓pandas

```python
table.sort_values(by='col1')
```
order byとsort_valuesのソートは、デフォルトで昇順になる。
order byなら最後にdescをつけるが、sort_valuesでは、ascending=Falseとする（Trueなら昇順）。

```python
table.sort_values(by='col1', ascending=False)
```

また、複数の条件でソートも可能

```sql
select *
from table
order by col1, col2 desc
```
↓pandas

```python
table.sort_values(by=['col1', 'col2'], ascending=[True, False])
```

### where -> []

```sql
select *
from table
where col1 = 'a'
```
↓pandas

```python
table[table.col1 == 'a']
```

in句

```sql
select *
from table
where col1 in ('a', 'b')
```
↓pandas

```python
table[table.col1.isin(['a', 'b'])]
```

複数条件

```sql
select *
from table
where col1 = 'a' and col2 = 1
```
↓pandas

```python
table[(table.col1 == 'a')&(table.col2 == 1)]
```
各条件を()で囲って、&で結ぶ。
andではなく、orの場合は、|を利用する

```python
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

```python
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
```sql
select
    col1,
    sum(col2)
from table
group by col1
```
↓pandas

```python
table.groupby('col1').agg({'col2': 'sum'})
```
groupbyに指定されたカラムは、結果のデータフレームにおいてインデックスになってしまうので、

```python
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

```python
table.groupby(['col1', 'col2'], as_index=False).agg({'col3': 'sum'})
```

### join -> merge
```sql
select
    *
from table t
left join table2 t2
on table.col1 = table2.col1
```
↓pandas

```python
pd.merge(table, table2, on='col1', how='left')
```
howには、inner、outer、rightも揃ってる。

onで複数のカラムを利用する場合

```sql
select
    *
from table t
left join table2 t2
on table.col1 = table2.col1
    and table.col2 = table2.col2
```
↓pandas

```python
pd.merge(table, table2, on=['col1', 'col2'], how='left')
```
onにリストを指定。

### update -> loc[]
```sql
update table
set col1 = 'b'
where col2 = 1
```
↓pandas

```python
table.loc[table.col2 == 1, 'col1'] = 'b'
```
.locの第一引数にwhere条件、第二引数に更新対象のカラムを指定する。

### delete -> []
```sql
delete
from table
where col1 = 'a'
```
↓pandas

```python
table = table[table.col1 != 'a']
```
条件一致しないものだけ抽出して、削除する。

### columnの別名 -> rename
```sql
select
    col1 as new_col1
from table
```
↓pandas

```python
table.rename(columns={'col1': 'new_col1'})
```

### union -> concat
```sql
select
    *
from table
union
select
    *
from table2
```
↓pandas

```python
pd.concat([table, table2], ignore_index=True)
```
concat関数の第一引数は、リストなので3つ以上のデータフレームのユニオンも可能。
ignore_indexを指定しないとインデックスの値は、UNION前のデータフレームのものが引き継がれる。
つまり、インデックスの値がかぶることがあるので、指定しておいた方が良いかも。

### insert -> append
```sql
insert into
table(
    col1,
    col2
)
values(
    'a',
    '2'
)
```

↓pandas

```python
table = table.append({'col': 'a', 'b': 2}, ignore_index=True)
```

ignore_indexは、unionと同様。

### case when -> apply
```sql
select
    case when col1 = 'a' then 1
            when col1 = 'b' then 2
            else 3 end as case_col1
from table
```
↓pandas

```python
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)
とした場合と結果は一致する。

```sql
select
    *,
    case when col1 = 'a' then 1
            when col1 = 'b' then 2
            else 3 end as case_col1
from table
```
↓pandas

```python
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
```sql
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

```python
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で対応してる。