SQLでやるこの操作ってpandas.DataFrameではどうやるの！

pandasを触り始めた時に感じた思いをタイトルにしてみました。テーブル操作においてSQLを使うことに慣れすぎて、pandasのDataFrameを操作しようとした時どうやるのか戸惑うことが多かったです。

SQLの基本的な構文がpandasではどう実現するのかを1つ１つ並べて確認して行きます。備忘録になれば幸いです。

# pandasとは
という説明は全部ふっとしばします。その代わりに以下のサイトに綺麗にまとまっているので読んでみてください。

- [pandasで使われるデータ構造 ～1次元、2次元、3次元のデータの扱い方～](http://www.madopro.net/entry/2016/10/14/022723)

# 事前準備
titanicのコードを使います。[kaggleのコンペティション](https://www.kaggle.com/c/titanic/data)からデータをダウンロードしてきます。

```py
import pandas

df = pd.read_csv("local/path/to/train.csv")
df1 = df[['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age','Fare', 'Embarked']]
df2 = df[['PassengerId', 'SibSp', 'Parch']]

print(df.columns)
print(df1)
print(df2)
```
```
Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp',
       'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],
      dtype='object')

   PassengerId  Survived  Pclass     Sex   Age     Fare Embarked
0            1         0       3    male  22.0   7.2500        S
1            2         1       1  female  38.0  71.2833        C
2            3         1       3  female  26.0   7.9250        S
3            4         1       1  female  35.0  53.1000        S
4            5         0       3    male  35.0   8.0500        S
...

   PassengerId  SibSp  Parch
0            1      1      0
1            2      1      0
2            3      0      0
3            4      1      0
4            5      0      0
...
```
後で、joinするためにdf1とdf2に分割しておきます。

# SELECT
カラムを指定して取得する場合。

```sql
SELECT PassengerId, Sex, Age
FROM df1
;
```
```py
df1[['PassengerId', 'Sex', 'Age']]

# もしくは
df1.loc[:, ['PassengerId', 'Sex', 'Age']]
```
```
   PassengerId     Sex   Age
0            1    male  22.0
1            2  female  38.0
2            3  female  26.0
3            4  female  35.0
4            5    male  35.0
```
locはlabel-locationの略です。`loc[row,colum]`のように使います。`:`は「全て」です。

# WHERE
## 条件1つ
```sql
SELECT *
FROM df1
WHERE Age > 40
;
```

```py
df1[df1.Age > 40]
```

```
    PassengerId  Survived  Pclass     Sex   Age     Fare Embarked
6             7         0       1    male  54.0  51.8625        S
11           12         1       1  female  58.0  26.5500        S
15           16         1       2  female  55.0  16.0000        S
33           34         0       2    male  66.0  10.5000        S
35           36         0       1    male  42.0  52.0000        S
...
```
## AND条件
```sql
SELECT *
FROM df1
WHERE Age > 40 AND Sex = 'male'
;
```

```py
df1[(df1.Sex == 'male') & (df1.Age > 40)]
```

```
    PassengerId  Survived  Pclass   Sex   Age     Fare Embarked
6             7         0       1  male  54.0  51.8625        S
33           34         0       2  male  66.0  10.5000        S
35           36         0       1  male  42.0  52.0000        S
54           55         0       1  male  65.0  61.9792        C
62           63         0       1  male  45.0  83.4750        S
...
```
## OR条件
```sql
SELECT *
FROM df1
WHERE Age > 40 OR Sex = 'male'
;
```

```py
df1[(df1.Sex == 'male') | (df1.Age > 40)]
```

```
   PassengerId  Survived  Pclass   Sex   Age     Fare Embarked
0            1         0       3  male  22.0   7.2500        S
4            5         0       3  male  35.0   8.0500        S
5            6         0       3  male   NaN   8.4583        Q
6            7         0       1  male  54.0  51.8625        S
7            8         0       3  male   2.0  21.0750        S
...
```

## NULLのレコードを抽出
```sql
SELECT *
FROM df1
WHERE Age IS NULL
;
```

```py
df1[df1.Age.isnull()]
```

```
   PassengerId  Survived  Pclass     Sex  Age     Fare Embarked
5             6         0       3    male  NaN   8.4583        Q
17           18         1       2    male  NaN  13.0000        S
19           20         1       3  female  NaN   7.2250        C
26           27         0       3    male  NaN   7.2250        C
28           29         1       3  female  NaN   7.8792        Q
...
```

## NULLでないレコードを抽出
```sql
SELECT *
FROM df1
WHERE Age IS NOT NULL
;
```

```py
df1[df1.Age.notnull()]
```

```
   PassengerId  Survived  Pclass     Sex   Age     Fare Embarked
0            1         0       3    male  22.0   7.2500        S
1            2         1       1  female  38.0  71.2833        C
2            3         1       3  female  26.0   7.9250        S
3            4         1       1  female  35.0  53.1000        S
4            5         0       3    male  35.0   8.0500        S
...
```

# LIMIT
上から3レコードを取得する場合。

```sql
SELECT *
FROM passengers
LIMIT 3
;
```

```py
df1.head(3)

# もしくは
df1[0:3]

# もしくは
df1.iloc[0:3,:]
```
```
   PassengerId  Survived  Pclass     Sex   Age     Fare Embarked
0            1         0       3    male  22.0   7.2500        S
1            2         1       1  female  38.0  71.2833        C
2            3         1       3  female  26.0   7.9250        S
```
ilocはinteger-locationの略です。locが範囲をラベルで指定するものである一方で、ilocは範囲を数字（インデックス）で指定します。

# ORDER BY

```sql
SELECT *
FROM df1
ORDER BY Age DESC
;
```

```py
df1.sort_values(by="Age", ascending=False)
```

```
     PassengerId  Survived  Pclass   Sex   Age     Fare Embarked
630          631         1       1  male  80.0  30.0000        S
851          852         0       3  male  74.0   7.7750        S
493          494         0       1  male  71.0  49.5042        C
96            97         0       1  male  71.0  34.6542        C
116          117         0       3  male  70.5   7.7500        Q
...
```

# GROUP BY

## 数を数える
```sql
SELECT Survived, COUNT(Survived)
FROM passengers
GROUP BY Survived
;
```

```py
df1.groupby("Survived").size().reset_index(name='counts')
```

```
Survived
0    549
1    342
dtype: int64
```

## 平均をとる
```sql
SELECT Survived, MEAN(Age)
FROM passengers
GROUP BY Survived
;
```

```py
df1[['Survived', 'Age']].groupby("Survived").mean().reset_index(name='counts')
```

```
                Age
Survived           
0         30.626179
1         28.343690
```

# JOIN
## 内部結合

```sql
SELECT *
FROM df1 INNER JOIN df2
;
```

```py
pd.merge(df1, df2, on="PassengerId", how="inner")
```

```
   PassengerId  Survived  Pclass     Sex   Age     Fare Embarked  SibSp  Parch
0            1         0       3    male  22.0   7.2500        S      1      0
1            2         1       1  female  38.0  71.2833        C      1      0
2            3         1       3  female  26.0   7.9250        S      0      0
3            4         1       1  female  35.0  53.1000        S      1      0
4            5         0       3    male  35.0   8.0500        S      0      0
...
```

## 外部結合

```sql
SELECT *
FROM df1 LEFT OUTER JOIN df2
;
```
```py
pd.merge(df1, df2, on="PassengerId", how="outer")
```

```
   PassengerId  Survived  Pclass     Sex   Age     Fare Embarked  SibSp  Parch
0            1         0       3    male  22.0   7.2500        S      1      0
1            2         1       1  female  38.0  71.2833        C      1      0
2            3         1       3  female  26.0   7.9250        S      0      0
3            4         1       1  female  35.0  53.1000        S      1      0
4            5         0       3    male  35.0   8.0500        S      0      0
...
```

# UPDATE

## 値を変更する場合（条件なし）

```sql
UPDATE df1
SET Survived = 1
;
```

```py
d = df1.copy()
d.loc[:, 'Survived'] = 1
```

```
   PassengerId  Survived  Pclass     Sex   Age     Fare Embarked
0            1         1       3    male  22.0   7.2500        S
1            2         1       1  female  38.0  71.2833        C
2            3         1       3  female  26.0   7.9250        S
3            4         1       1  female  35.0  53.1000        S
4            5         1       3    male  35.0   8.0500        S
...
```

## 値を変更する場合（条件あり）

```sql
UPDATE df1
SET Sex = (CASE WHEN Sex = 'male' THEN 0 ELSE 1)
;
```

```py
df1.replace({'male': 0, 'female': 1})
```

```
   PassengerId  Survived  Pclass  Sex   Age     Fare Embarked
0            1         0       3    0  22.0   7.2500        S
1            2         1       1    1  38.0  71.2833        C
2            3         1       3    1  26.0   7.9250        S
3            4         1       1    1  35.0  53.1000        S
4            5         0       3    0  35.0   8.0500        S
...
```

## 欠損値

欠損値の扱いも更新の1つですが、以下の記事がよくまとまっているので参考にしてください。
[pandasで欠損値NaNを除外（削除）・置換（穴埋め）する](https://note.nkmk.me/python-pandas-nan-dropna-fillna/)


# 最後に
SQLでこうやる処理pandasでどうやるねん！というイライラから、簡単な操作について、SQLの書き方とpandasの書き方を比較してみました。間違っているよ、こう書いたほうがいいよなどあれば、コメントください。