Pandasの基本操作をJupyter Labで書いてみた（後編）

## 第１４回
## DataFrameのNaNについて


```python
import numpy as np
import pandas as pd
```


```python
df = pd.read_csv('train.csv')
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>35.0</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>



#### NaNを含んでいたレコードが全てdropされています．(indexはそのままになってます．基本，.reset_index()しない限りindexは再振りされません


```python
df.dropna().head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>6</td>
      <td>7</td>
      <td>0</td>
      <td>1</td>
      <td>McCarthy, Mr. Timothy J</td>
      <td>male</td>
      <td>54.0</td>
      <td>0</td>
      <td>0</td>
      <td>17463</td>
      <td>51.8625</td>
      <td>E46</td>
      <td>S</td>
    </tr>
    <tr>
      <td>10</td>
      <td>11</td>
      <td>1</td>
      <td>3</td>
      <td>Sandstrom, Miss. Marguerite Rut</td>
      <td>female</td>
      <td>4.0</td>
      <td>1</td>
      <td>1</td>
      <td>PP 9549</td>
      <td>16.7000</td>
      <td>G6</td>
      <td>S</td>
    </tr>
    <tr>
      <td>11</td>
      <td>12</td>
      <td>1</td>
      <td>1</td>
      <td>Bonnell, Miss. Elizabeth</td>
      <td>female</td>
      <td>58.0</td>
      <td>0</td>
      <td>0</td>
      <td>113783</td>
      <td>26.5500</td>
      <td>C103</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>



#### axis=1を引数にいれるとNaNを含むカラムをdropできます（デフォルトはaxis=0で行）．
##### あまり使わない．モデルを組む際に，データ数を減らさずにデータを説明する変数(説明変数)を減らす作戦のときに使いますが，「NaNが一つでもあるのでその説明変数を減らす」ということはまずありません．どの説明変数がモデル構築に重要なのかというのは非常に重要かつ慎重に考えるべき問題です．


```python
df.dropna(axis=1) .head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
    </tr>
  </tbody>
</table>
</div>



#### カラム名のリストをsubset引数に渡すことで，そのカラムにおいてNaNを含む行のみをdropしてくれます． 
#### 特定のカラムにおけるNaNの行だけを落とす必要がでてきます．とても便利なので覚えておきましょう．
#### 当然， .dropna() しても，元の df は上書きされません．元の df を更新したい場合はおなじみの inplace=True か df = df.dropna() で再代入します．


```python
df.dropna(subset=['Age']).head() ###index=888がdropされている.
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>35.0</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>



## .fillna(value)

#### NaNに特定のValueを代入する.


```python
df.fillna('THIS IS IT').head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>THIS IS IT</td>
      <td>S</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>THIS IS IT</td>
      <td>S</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>35</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
      <td>THIS IS IT</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>



#### 特定のカラムに含まれるNaNにカラムの平均値を代入する


```python
df['Age'].mean()
```




    29.69911764705882




```python
df['Age'].fillna(df['Age'].mean()).head()
```




    0    22.0
    1    38.0
    2    26.0
    3    35.0
    4    35.0
    Name: Age, dtype: float64




```python
df['Age'] = df['Age'].fillna(df['Age'].mean())
```


```python
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>35.0</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>




```python
pd.isna(df).head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
    </tr>
    <tr>
      <td>1</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
    </tr>
    <tr>
      <td>2</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
    </tr>
    <tr>
      <td>3</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
    </tr>
    <tr>
      <td>4</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>False</td>
      <td>True</td>
      <td>False</td>
    </tr>
  </tbody>
</table>
</div>



#　Cabin_nanカラムを使いして，CabinのNaN判定結果を代入する
df['Cabin_nan'] = pd.isna(df['Cabin'])
df

## 第１５回
## .groupby()関数()でgroupby


```python
df = pd.read_csv('train.csv')
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>35.0</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>



#### Pclassでgroupby
#### df.groupby(‘Pclass’)だけではgroupbyしてグループにまとめた後になにすればいいかわからないので,その後に.mean()や.count()などの関数をコールします．


```python
df.groupby('Pclass').mean()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Fare</th>
    </tr>
    <tr>
      <th>Pclass</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>461.597222</td>
      <td>0.629630</td>
      <td>38.233441</td>
      <td>0.416667</td>
      <td>0.356481</td>
      <td>84.154687</td>
    </tr>
    <tr>
      <td>2</td>
      <td>445.956522</td>
      <td>0.472826</td>
      <td>29.877630</td>
      <td>0.402174</td>
      <td>0.380435</td>
      <td>20.662183</td>
    </tr>
    <tr>
      <td>3</td>
      <td>439.154786</td>
      <td>0.242363</td>
      <td>25.140620</td>
      <td>0.615071</td>
      <td>0.393075</td>
      <td>13.675550</td>
    </tr>
  </tbody>
</table>
</div>




```python
df = df[df['Pclass']==1]
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>6</td>
      <td>7</td>
      <td>0</td>
      <td>1</td>
      <td>McCarthy, Mr. Timothy J</td>
      <td>male</td>
      <td>54.0</td>
      <td>0</td>
      <td>0</td>
      <td>17463</td>
      <td>51.8625</td>
      <td>E46</td>
      <td>S</td>
    </tr>
    <tr>
      <td>11</td>
      <td>12</td>
      <td>1</td>
      <td>1</td>
      <td>Bonnell, Miss. Elizabeth</td>
      <td>female</td>
      <td>58.0</td>
      <td>0</td>
      <td>0</td>
      <td>113783</td>
      <td>26.5500</td>
      <td>C103</td>
      <td>S</td>
    </tr>
    <tr>
      <td>23</td>
      <td>24</td>
      <td>1</td>
      <td>1</td>
      <td>Sloper, Mr. William Thompson</td>
      <td>male</td>
      <td>28.0</td>
      <td>0</td>
      <td>0</td>
      <td>113788</td>
      <td>35.5000</td>
      <td>A6</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>



#### 'Pclass'==1の各カラムの統計量を取ります


```python
df[df['Pclass']==1].describe()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Fare</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>count</td>
      <td>216.000000</td>
      <td>216.000000</td>
      <td>216.0</td>
      <td>186.000000</td>
      <td>216.000000</td>
      <td>216.000000</td>
      <td>216.000000</td>
    </tr>
    <tr>
      <td>mean</td>
      <td>461.597222</td>
      <td>0.629630</td>
      <td>1.0</td>
      <td>38.233441</td>
      <td>0.416667</td>
      <td>0.356481</td>
      <td>84.154687</td>
    </tr>
    <tr>
      <td>std</td>
      <td>246.737616</td>
      <td>0.484026</td>
      <td>0.0</td>
      <td>14.802856</td>
      <td>0.611898</td>
      <td>0.693997</td>
      <td>78.380373</td>
    </tr>
    <tr>
      <td>min</td>
      <td>2.000000</td>
      <td>0.000000</td>
      <td>1.0</td>
      <td>0.920000</td>
      <td>0.000000</td>
      <td>0.000000</td>
      <td>0.000000</td>
    </tr>
    <tr>
      <td>25%</td>
      <td>270.750000</td>
      <td>0.000000</td>
      <td>1.0</td>
      <td>27.000000</td>
      <td>0.000000</td>
      <td>0.000000</td>
      <td>30.923950</td>
    </tr>
    <tr>
      <td>50%</td>
      <td>472.000000</td>
      <td>1.000000</td>
      <td>1.0</td>
      <td>37.000000</td>
      <td>0.000000</td>
      <td>0.000000</td>
      <td>60.287500</td>
    </tr>
    <tr>
      <td>75%</td>
      <td>670.500000</td>
      <td>1.000000</td>
      <td>1.0</td>
      <td>49.000000</td>
      <td>1.000000</td>
      <td>0.000000</td>
      <td>93.500000</td>
    </tr>
    <tr>
      <td>max</td>
      <td>890.000000</td>
      <td>1.000000</td>
      <td>1.0</td>
      <td>80.000000</td>
      <td>3.000000</td>
      <td>4.000000</td>
      <td>512.329200</td>
    </tr>
  </tbody>
</table>
</div>



#### meanだけ取り出す
#### 一つ一つ取り出していては大変なので.groupby()でまとめて見るとよい


```python
df[df['Pclass']==1].describe().loc['mean']
```




    PassengerId    461.597222
    Survived         0.629630
    Pclass           1.000000
    Age             38.233441
    SibSp            0.416667
    Parch            0.356481
    Fare            84.154687
    Name: mean, dtype: float64



#### groupby後は，indexにgroupbyの第一引数である by に指定した値をとります．上の例ではPclassの値(1, 2, 3)です．groubyの結果も当然DataFrameなので， .loc[] で特定のグループのSeriesを取ってくることができます．



```python
df.groupby('Pclass').mean().loc[1]
```




    PassengerId    461.597222
    Survived         0.629630
    Age             38.233441
    SibSp            0.416667
    Parch            0.356481
    Fare            84.154687
    Name: 1, dtype: float64




```python
df.groupby('Pclass').count().loc[1] #count()やsum()なども可能
```




    PassengerId    216
    Survived       216
    Name           216
    Sex            216
    Age            186
    SibSp          216
    Parch          216
    Ticket         216
    Fare           216
    Cabin          176
    Embarked       214
    Name: 1, dtype: int64




```python
df.groupby('Pclass').describe()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead tr th {
        text-align: left;
    }

    .dataframe thead tr:last-of-type th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr>
      <th></th>
      <th colspan="8" halign="left">PassengerId</th>
      <th colspan="8" halign="left">Survived</th>
      <th colspan="8" halign="left">Age</th>
      <th colspan="8" halign="left">SibSp</th>
      <th colspan="8" halign="left">Parch</th>
      <th colspan="8" halign="left">Fare</th>
    </tr>
    <tr>
      <th></th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
    </tr>
    <tr>
      <th>Pclass</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>216.0</td>
      <td>461.597222</td>
      <td>246.737616</td>
      <td>2.0</td>
      <td>270.75</td>
      <td>472.0</td>
      <td>670.5</td>
      <td>890.0</td>
      <td>216.0</td>
      <td>0.629630</td>
      <td>0.484026</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>186.0</td>
      <td>38.233441</td>
      <td>14.802856</td>
      <td>0.92</td>
      <td>27.0</td>
      <td>37.0</td>
      <td>49.0</td>
      <td>80.0</td>
      <td>216.0</td>
      <td>0.416667</td>
      <td>0.611898</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>1.0</td>
      <td>3.0</td>
      <td>216.0</td>
      <td>0.356481</td>
      <td>0.693997</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>4.0</td>
      <td>216.0</td>
      <td>84.154687</td>
      <td>78.380373</td>
      <td>0.0</td>
      <td>30.92395</td>
      <td>60.2875</td>
      <td>93.5</td>
      <td>512.3292</td>
    </tr>
    <tr>
      <td>2</td>
      <td>184.0</td>
      <td>445.956522</td>
      <td>250.852161</td>
      <td>10.0</td>
      <td>234.50</td>
      <td>435.5</td>
      <td>668.0</td>
      <td>887.0</td>
      <td>184.0</td>
      <td>0.472826</td>
      <td>0.500623</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>1.0</td>
      <td>1.0</td>
      <td>173.0</td>
      <td>29.877630</td>
      <td>14.001077</td>
      <td>0.67</td>
      <td>23.0</td>
      <td>29.0</td>
      <td>36.0</td>
      <td>70.0</td>
      <td>184.0</td>
      <td>0.402174</td>
      <td>0.601633</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>1.0</td>
      <td>3.0</td>
      <td>184.0</td>
      <td>0.380435</td>
      <td>0.690963</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>1.0</td>
      <td>3.0</td>
      <td>184.0</td>
      <td>20.662183</td>
      <td>13.417399</td>
      <td>0.0</td>
      <td>13.00000</td>
      <td>14.2500</td>
      <td>26.0</td>
      <td>73.5000</td>
    </tr>
    <tr>
      <td>3</td>
      <td>491.0</td>
      <td>439.154786</td>
      <td>264.441453</td>
      <td>1.0</td>
      <td>200.00</td>
      <td>432.0</td>
      <td>666.5</td>
      <td>891.0</td>
      <td>491.0</td>
      <td>0.242363</td>
      <td>0.428949</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>1.0</td>
      <td>355.0</td>
      <td>25.140620</td>
      <td>12.495398</td>
      <td>0.42</td>
      <td>18.0</td>
      <td>24.0</td>
      <td>32.0</td>
      <td>74.0</td>
      <td>491.0</td>
      <td>0.615071</td>
      <td>1.374883</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>1.0</td>
      <td>8.0</td>
      <td>491.0</td>
      <td>0.393075</td>
      <td>0.888861</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>0.0</td>
      <td>6.0</td>
      <td>491.0</td>
      <td>13.675550</td>
      <td>11.778142</td>
      <td>0.0</td>
      <td>7.75000</td>
      <td>8.0500</td>
      <td>15.5</td>
      <td>69.5500</td>
    </tr>
  </tbody>
</table>
</div>




```python
df.groupby('Pclass').describe()['Age'] #Ageだけ取り出した
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>count</th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>25%</th>
      <th>50%</th>
      <th>75%</th>
      <th>max</th>
    </tr>
    <tr>
      <th>Pclass</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>186.0</td>
      <td>38.233441</td>
      <td>14.802856</td>
      <td>0.92</td>
      <td>27.0</td>
      <td>37.0</td>
      <td>49.0</td>
      <td>80.0</td>
    </tr>
    <tr>
      <td>2</td>
      <td>173.0</td>
      <td>29.877630</td>
      <td>14.001077</td>
      <td>0.67</td>
      <td>23.0</td>
      <td>29.0</td>
      <td>36.0</td>
      <td>70.0</td>
    </tr>
    <tr>
      <td>3</td>
      <td>355.0</td>
      <td>25.140620</td>
      <td>12.495398</td>
      <td>0.42</td>
      <td>18.0</td>
      <td>24.0</td>
      <td>32.0</td>
      <td>74.0</td>
    </tr>
  </tbody>
</table>
</div>



#### JupyterではDataFrameのカラムや行が表示しきれない場合は省略されて表示されます．
#### 省略させずに全てのカラムを（もしくは全ての行を）表示させたい場合はそれぞれ以下を実行することで省略させないようにすることができます．


```python
# カラムを省略せずに表示
pd.set_option('display.max_columns', None)
# 行を省略せずに表示
pd.set_option('display.max_rows', None)
```

## groupbyの結果をfor文でまわす


```python
for i, group_df in df.groupby('Pclass'):
    print("{}: group_df's type is {} and has {}".format(i, type(group_df), len(group_df)))
```

    1: group_df's type is <class 'pandas.core.frame.DataFrame'> and has 216
    2: group_df's type is <class 'pandas.core.frame.DataFrame'> and has 184
    3: group_df's type is <class 'pandas.core.frame.DataFrame'> and has 491


#### 各Pclassのグループの中で，各レコードが何番目にFareが高いか数字を振ってみる


```python
df = pd.read_csv('train.csv')
results = []
for i, group_df in df.groupby('Pclass'):
    sorted_group_df = group_df.sort_values('Fare')
    sorted_group_df['RankInClass'] = np.arange(len(sorted_group_df))
    results.append(sorted_group_df)

results_df = pd.concat(results)
results_df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
      <th>RankInClass</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>633</td>
      <td>634</td>
      <td>0</td>
      <td>1</td>
      <td>Parr, Mr. William Henry Marsh</td>
      <td>male</td>
      <td>NaN</td>
      <td>0</td>
      <td>0</td>
      <td>112052</td>
      <td>0.0</td>
      <td>NaN</td>
      <td>S</td>
      <td>0</td>
    </tr>
    <tr>
      <td>822</td>
      <td>823</td>
      <td>0</td>
      <td>1</td>
      <td>Reuchlin, Jonkheer. John George</td>
      <td>male</td>
      <td>38.0</td>
      <td>0</td>
      <td>0</td>
      <td>19972</td>
      <td>0.0</td>
      <td>NaN</td>
      <td>S</td>
      <td>1</td>
    </tr>
    <tr>
      <td>815</td>
      <td>816</td>
      <td>0</td>
      <td>1</td>
      <td>Fry, Mr. Richard</td>
      <td>male</td>
      <td>NaN</td>
      <td>0</td>
      <td>0</td>
      <td>112058</td>
      <td>0.0</td>
      <td>B102</td>
      <td>S</td>
      <td>2</td>
    </tr>
    <tr>
      <td>806</td>
      <td>807</td>
      <td>0</td>
      <td>1</td>
      <td>Andrews, Mr. Thomas Jr</td>
      <td>male</td>
      <td>39.0</td>
      <td>0</td>
      <td>0</td>
      <td>112050</td>
      <td>0.0</td>
      <td>A36</td>
      <td>S</td>
      <td>3</td>
    </tr>
    <tr>
      <td>263</td>
      <td>264</td>
      <td>0</td>
      <td>1</td>
      <td>Harrison, Mr. William</td>
      <td>male</td>
      <td>40.0</td>
      <td>0</td>
      <td>0</td>
      <td>112059</td>
      <td>0.0</td>
      <td>B94</td>
      <td>S</td>
      <td>4</td>
    </tr>
  </tbody>
</table>
</div>



## 表の結合
#### 表の結合とは，大きく二つあります．
#### 特定のカラムやindexをKeyにして結合する
#### DataFrameを単純に横に(もしくは縦に)結合する（ガッチャンコさせる）


```python
import pandas as pd
```


```python
df1 = pd.DataFrame({'Key':['k0','k','k2'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key':['k0','k1','k2'],
                   'C':['c0','c2','c3'],
                   'D':['d0','d1','d2']})
```


```python
df1
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>A</th>
      <th>B</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k</td>
      <td>a1</td>
      <td>b1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
    </tr>
  </tbody>
</table>
</div>




```python
df2
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>c2</td>
      <td>d1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>c3</td>
      <td>d2</td>
    </tr>
  </tbody>
</table>
</div>



#### どちらも’Key’というカラムを持っていて，その値はどちらも同じです．
#### 他のカラムはそれぞれ別の値を持っています．この’Key’というカラムをKey(キー)にして二つのDataFrameを横に結合します．結合には .merge() を使います．


```python
df1.merge(df2)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>A</th>
      <th>B</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
      <td>c3</td>
      <td>d2</td>
    </tr>
  </tbody>
</table>
</div>



#### DataFrameを単純に横に(もしくは縦に)結合する（ガッチャンコさせる）
#### pd.concat()を使う　concat=concatenate


```python
# 縦 （よく使う）
pd.concat([df1,df2], axis=0) #デフォルトはaxis=0
```

    /opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:2: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
    of pandas will change to not sort by default.
    
    To accept the future behavior, pass 'sort=False'.
    
    To retain the current behavior and silence the warning, pass 'sort=True'.
    
      





<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>A</th>
      <th>B</th>
      <th>C</th>
      <th>D</th>
      <th>Key</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>a0</td>
      <td>b0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>k0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>a1</td>
      <td>b1</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>k</td>
    </tr>
    <tr>
      <td>2</td>
      <td>a2</td>
      <td>b2</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>k2</td>
    </tr>
    <tr>
      <td>0</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>c0</td>
      <td>d0</td>
      <td>k0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>c2</td>
      <td>d1</td>
      <td>k1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>c3</td>
      <td>d2</td>
      <td>k2</td>
    </tr>
  </tbody>
</table>
</div>




```python
#横
pd.concat([df1,df2], axis=1)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>A</th>
      <th>B</th>
      <th>Key</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k</td>
      <td>a1</td>
      <td>b1</td>
      <td>k1</td>
      <td>c2</td>
      <td>d1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
      <td>k2</td>
      <td>c3</td>
      <td>d2</td>
    </tr>
  </tbody>
</table>
</div>



## 第１６回
## .merge()の使い方

#### how : どう結合するか→{‘left’, ‘right’, ‘outer’, ‘inner’}, デフォルトは ‘inner’
#### on : keyにするカラムを指定（どちらのDataFrameにも存在するカラム）．指定をしないと共通のカラムで結合される
#### left_on：leftのDataFrameのkeyにするカラム
#### right_on：rightのDataFrameのkeyにするカラム
#### left_index：leftのKeyをindexにする場合Trueを指定
#### right_index：rightのKeyをindexにする場合Trueを指定

## how


```python
df1
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>A</th>
      <th>B</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k</td>
      <td>a1</td>
      <td>b1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
    </tr>
  </tbody>
</table>
</div>




```python
df2
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>c2</td>
      <td>d1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>c3</td>
      <td>d2</td>
    </tr>
  </tbody>
</table>
</div>




```python
df1.merge(df2, how='left')
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>A</th>
      <th>B</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k</td>
      <td>a1</td>
      <td>b1</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
      <td>c3</td>
      <td>d2</td>
    </tr>
  </tbody>
</table>
</div>




```python
df1.merge(df2, how='outer')
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>A</th>
      <th>B</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k</td>
      <td>a1</td>
      <td>b1</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
      <td>c3</td>
      <td>d2</td>
    </tr>
    <tr>
      <td>3</td>
      <td>k1</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>c2</td>
      <td>d1</td>
    </tr>
  </tbody>
</table>
</div>




```python
df1.merge(df2, how='inner')
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>A</th>
      <th>B</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
      <td>c3</td>
      <td>d2</td>
    </tr>
  </tbody>
</table>
</div>



## on
#### 引数onは，結合するときにどのカラムをKeyにして結合するかを指定します．leftの表もrightの表もどちらにもあるカラムしか指定できません．
#### なお，共通のカラムがある場合はなにも指定しなくてもそのカラムがKeyとなり結合されます，が，基本は指定しましょう．わかりやすいし安全です．「予期せぬカラムで結合してた」ということもよくあります．複数の共通カラムがある場合は，どのカラムで結合されるかわからないですし，たとえ一つしか共通カラムがない場合でも指定するのがいいと思います．（今までの例はonの説明前だったので意図的にon引数を指定せずに書いてました．）
#### indexをKeyにする場合は後述のright_index, left_indexをTrueにします．また，それぞれの表(DataFrame)のカラム名が異なる場合は後述のleft_on, right_onを指定します．


```python
df1 = pd.DataFrame({'Key':['k0','k1','k2'],
                    'ID':['aa','bb','cc'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key':['k0','k1','k3'],
                    'ID':['aa','bb','cc'],
                   'C':['c0','c1','c3'],
                   'D':['d0','d1','d3']})
```


```python
df1.merge(df2, on='Key')
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key</th>
      <th>ID_x</th>
      <th>A</th>
      <th>B</th>
      <th>ID_y</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>aa</td>
      <td>a0</td>
      <td>b0</td>
      <td>aa</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>bb</td>
      <td>a1</td>
      <td>b1</td>
      <td>bb</td>
      <td>c1</td>
      <td>d1</td>
    </tr>
  </tbody>
</table>
</div>




```python
df1.merge(df2, on='ID')
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key_x</th>
      <th>ID</th>
      <th>A</th>
      <th>B</th>
      <th>Key_y</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>aa</td>
      <td>a0</td>
      <td>b0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>bb</td>
      <td>a1</td>
      <td>b1</td>
      <td>k1</td>
      <td>c1</td>
      <td>d1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>cc</td>
      <td>a2</td>
      <td>b2</td>
      <td>k3</td>
      <td>c3</td>
      <td>d3</td>
    </tr>
  </tbody>
</table>
</div>



#### suffixを変更する


```python
df1.merge(df2, on='ID', suffixes=('_left', '_right'))
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key_left</th>
      <th>ID</th>
      <th>A</th>
      <th>B</th>
      <th>Key_right</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>aa</td>
      <td>a0</td>
      <td>b0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>bb</td>
      <td>a1</td>
      <td>b1</td>
      <td>k1</td>
      <td>c1</td>
      <td>d1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>cc</td>
      <td>a2</td>
      <td>b2</td>
      <td>k3</td>
      <td>c3</td>
      <td>d3</td>
    </tr>
  </tbody>
</table>
</div>



## left_on, right_on
#### Keyにしたいカラム名がleftとrightで異なるとき，この引数を指定します．


```python
df1 = pd.DataFrame({'Key1':['k0','k1','k2'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key2':['k0','k1','k3'],
                   'C':['c0','c1','c3'],
                   'D':['d0','d1','d3']})
```


```python
df1.merge(df2, left_on='Key1', right_on='Key2')
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key1</th>
      <th>A</th>
      <th>B</th>
      <th>Key2</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>a1</td>
      <td>b1</td>
      <td>k1</td>
      <td>c1</td>
      <td>d1</td>
    </tr>
  </tbody>
</table>
</div>



## left_index, right_index
#### カラムではなくIndexをKeyに指定したい場合，left_index, right_indexにTrueを指定します．


```python
df1.merge(df2, left_index=True, right_index=True)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key1</th>
      <th>A</th>
      <th>B</th>
      <th>Key2</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>a1</td>
      <td>b1</td>
      <td>k1</td>
      <td>c1</td>
      <td>d1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
      <td>k3</td>
      <td>c3</td>
      <td>d3</td>
    </tr>
  </tbody>
</table>
</div>



## join
#### join関数を使うとindexで結合してくれますが，mergeでもほぼ同じことができるので覚える必要はない


```python
df1 = pd.DataFrame({'Key1':['k0','k1','k2'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key2':['k0','k1','k3'],
                   'C':['c0','c1','c3'],
                   'D':['d0','d1','d3']})
```


```python
df1.join(df2)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key1</th>
      <th>A</th>
      <th>B</th>
      <th>Key2</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>a1</td>
      <td>b1</td>
      <td>k1</td>
      <td>c1</td>
      <td>d1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
      <td>k3</td>
      <td>c3</td>
      <td>d3</td>
    </tr>
  </tbody>
</table>
</div>




```python
df1.merge(df2, left_index=True, right_index=True)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key1</th>
      <th>A</th>
      <th>B</th>
      <th>Key2</th>
      <th>C</th>
      <th>D</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>a1</td>
      <td>b1</td>
      <td>k1</td>
      <td>c1</td>
      <td>d1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
      <td>k3</td>
      <td>c3</td>
      <td>d3</td>
    </tr>
  </tbody>
</table>
</div>




```python
df1 = pd.DataFrame({'Key1':['k0','k1','k2'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key2':['k0','k1','k3'],
                   'C':['c0','c1','c3'],
                   'D':['d0','d1','d3']})
df3 = pd.DataFrame({'Key3':['k0','k1','k4'],
                   'E':['c0','c1','c3'],
                   'F':['d0','d1','d3']})
df1.join([df2, df3])
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Key1</th>
      <th>A</th>
      <th>B</th>
      <th>Key2</th>
      <th>C</th>
      <th>D</th>
      <th>Key3</th>
      <th>E</th>
      <th>F</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>k0</td>
      <td>a0</td>
      <td>b0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
      <td>k0</td>
      <td>c0</td>
      <td>d0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>k1</td>
      <td>a1</td>
      <td>b1</td>
      <td>k1</td>
      <td>c1</td>
      <td>d1</td>
      <td>k1</td>
      <td>c1</td>
      <td>d1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>k2</td>
      <td>a2</td>
      <td>b2</td>
      <td>k3</td>
      <td>c3</td>
      <td>d3</td>
      <td>k4</td>
      <td>c3</td>
      <td>d3</td>
    </tr>
  </tbody>
</table>
</div>



## 第１７回
## .unique() .nunique()


```python
import pandas as pd
df = pd.read_csv('train.csv')
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>35.0</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>




```python
df['Pclass'].unique()
```




    array([3, 1, 2])




```python
df['Pclass'].nunique()
```




    3



## .value_counts()


```python
df['Pclass'].value_counts()
```




    3    491
    1    216
    2    184
    Name: Pclass, dtype: int64



## （超重要） .apply() 
#### apply()関数を使って，DataFrameの全てのレコードに処理をして，その結果を別のカラムに格納することができます． 各行に処理をapplyするイメージです


```python
def get_age_group(age):
    return str(age)[0] + '0s'

get_age_group(45)
```




    '40s'




```python
df = pd.DataFrame({'name':['John','Mike','Emily'],
                  'age':['23','36','42']})
df
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>age</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>John</td>
      <td>23</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Mike</td>
      <td>36</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Emily</td>
      <td>42</td>
    </tr>
  </tbody>
</table>
</div>




```python
df['age'].apply(get_age_group)
```




    0    20s
    1    30s
    2    40s
    Name: age, dtype: object



## lambda関数を使った.apply()の使い方


```python
#lambda関数に変数fに代入して
f = lambda x: str(x)[0] + '0s'
#試しに43を入れる
f(43)
```




    '40s'




```python
df['age_group'] = df['age'].apply(lambda x: str(x)[0] + '0s')
```


```python
df
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>age</th>
      <th>age_group</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>John</td>
      <td>23</td>
      <td>20s</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Mike</td>
      <td>36</td>
      <td>30s</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Emily</td>
      <td>42</td>
      <td>40s</td>
    </tr>
  </tbody>
</table>
</div>



## レコード全体に対して使う.apply()の使い方


```python
df = pd.DataFrame({'name':['John','Mike','Emily'],
                  'age':['23','36','42']})
df['description'] = df.apply(lambda row:'{} is {} years old'.format(row['name'], row['age']), axis=1)
```


```python
df
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>name</th>
      <th>age</th>
      <th>description</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>John</td>
      <td>23</td>
      <td>John is 23 years old</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Mike</td>
      <td>36</td>
      <td>Mike is 36 years old</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Emily</td>
      <td>42</td>
      <td>Emily is 42 years old</td>
    </tr>
  </tbody>
</table>
</div>



## 第１８回
## .to_csv()でDataFrameをcsv形式で保存


```python
df = pd.read_csv('train.csv')
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>35.0</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>




```python
df['Adult'] = df['Age'].apply(lambda x: x>20)
df.tail()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
      <th>Adult</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>886</td>
      <td>887</td>
      <td>0</td>
      <td>2</td>
      <td>Montvila, Rev. Juozas</td>
      <td>male</td>
      <td>27.0</td>
      <td>0</td>
      <td>0</td>
      <td>211536</td>
      <td>13.00</td>
      <td>NaN</td>
      <td>S</td>
      <td>True</td>
    </tr>
    <tr>
      <td>887</td>
      <td>888</td>
      <td>1</td>
      <td>1</td>
      <td>Graham, Miss. Margaret Edith</td>
      <td>female</td>
      <td>19.0</td>
      <td>0</td>
      <td>0</td>
      <td>112053</td>
      <td>30.00</td>
      <td>B42</td>
      <td>S</td>
      <td>False</td>
    </tr>
    <tr>
      <td>888</td>
      <td>889</td>
      <td>0</td>
      <td>3</td>
      <td>Johnston, Miss. Catherine Helen "Carrie"</td>
      <td>female</td>
      <td>NaN</td>
      <td>1</td>
      <td>2</td>
      <td>W./C. 6607</td>
      <td>23.45</td>
      <td>NaN</td>
      <td>S</td>
      <td>False</td>
    </tr>
    <tr>
      <td>889</td>
      <td>890</td>
      <td>1</td>
      <td>1</td>
      <td>Behr, Mr. Karl Howell</td>
      <td>male</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>111369</td>
      <td>30.00</td>
      <td>C148</td>
      <td>C</td>
      <td>True</td>
    </tr>
    <tr>
      <td>890</td>
      <td>891</td>
      <td>0</td>
      <td>3</td>
      <td>Dooley, Mr. Patrick</td>
      <td>male</td>
      <td>32.0</td>
      <td>0</td>
      <td>0</td>
      <td>370376</td>
      <td>7.75</td>
      <td>NaN</td>
      <td>Q</td>
      <td>True</td>
    </tr>
  </tbody>
</table>
</div>




```python
df.to_csv('train_w_adult.csv')
```


```python
df = pd.read_csv('train_w_adult.csv')
df.head(3)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Unnamed: 0</th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
      <th>Adult</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
      <td>True</td>
    </tr>
    <tr>
      <td>1</td>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
      <td>True</td>
    </tr>
    <tr>
      <td>2</td>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
      <td>True</td>
    </tr>
  </tbody>
</table>
</div>



#### df.head(3) の結果をみると，前回保存した時のindex情報が’Unnamed:0’という謎のカラムに保存されています．
#### .to_csv()にindex=Falseを指定するとindexを保存しないで済みます．基本常にindex=Falseを指定してcsv形式に保存しておきます．
#### 保存先にすでに同じファイルがある場合，上書き保存されるので注意してください．


```python
df = pd.read_csv('train.csv')
df['Adult'] = df['Age'].apply(lambda x: x>20)
df.to_csv('train_w_adult.csv', index=False)
```


```python
df = pd.read_csv('train_w_adult.csv')
df.head(3)
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
      <th>Adult</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
      <td>True</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
      <td>True</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
      <td>True</td>
    </tr>
  </tbody>
</table>
</div>




```python
df = pd.DataFrame({'A':[['a', 'b'], 2, 3], 'B':[['c', 'd'], 5, 6]})
df
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>A</th>
      <th>B</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>[a, b]</td>
      <td>[c, d]</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>5</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>6</td>
    </tr>
  </tbody>
</table>
</div>




```python
# 格納されている値がリストであることを確認します．
type(df['A'].iloc[0])
```




    list




```python
# csvで保存
df.to_csv('temp.csv', index=False)
# 保存したcsvを読み込み
df = pd.read_csv('temp.csv')
df
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>A</th>
      <th>B</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>['a', 'b']</td>
      <td>['c', 'd']</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>5</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>6</td>
    </tr>
  </tbody>
</table>
</div>




```python
type(df['A'].iloc[0])
```




    str



## .iterrows()でDataFrameをイテレーション
#### DataFrameをfor文でイテレーションするときに使います．覚えにくいですが，「rows」を「iteration」するのでiter + row + s. と覚えましょう．forで回せるものになるので複数系のsがあると考えましょう．

#### 「イテレーション」というのは繰り返し処理を回すことを意味します．ループです．例えばリストだったらfor i in list:でイテレーションできました（第4回参照）

#### DataFrameでは，リストのように直接for i in df:というのはできません． .iterrows() という関数を使って以下のように書きます．


```python
df = pd.read_csv('train.csv')
for idx, row in df.iterrows():
    if row['Age'] > 40 and row['Pclass'] == 3 and row['Sex'] == 'male' and row['Survived'] == 1:
        print('{} is very lucky guy...!'.format(row['Name']))
```

    Dahl, Mr. Karl Edwart is very lucky guy...!
    Sundman, Mr. Johan Julian is very lucky guy...!


####  .apply() では，各レコードの処理をした結果を別のカラムに保存するときに使い，今回の .iterows() では値を返すのではなく処理だけをしたいときに使うことが多いです．

#### 例えばDataFrameにファイルパスが格納されていて，それを .iterrows() してファイルを移動させたり読み込んだりします．

## .sort_values()で特定のカラムでソート


```python
#年齢が若い順にソート
df.sort_values('Age')
df.head()
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>PassengerId</th>
      <th>Survived</th>
      <th>Pclass</th>
      <th>Name</th>
      <th>Sex</th>
      <th>Age</th>
      <th>SibSp</th>
      <th>Parch</th>
      <th>Ticket</th>
      <th>Fare</th>
      <th>Cabin</th>
      <th>Embarked</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>3</td>
      <td>Braund, Mr. Owen Harris</td>
      <td>male</td>
      <td>22.0</td>
      <td>1</td>
      <td>0</td>
      <td>A/5 21171</td>
      <td>7.2500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>
      <td>female</td>
      <td>38.0</td>
      <td>1</td>
      <td>0</td>
      <td>PC 17599</td>
      <td>71.2833</td>
      <td>C85</td>
      <td>C</td>
    </tr>
    <tr>
      <td>2</td>
      <td>3</td>
      <td>1</td>
      <td>3</td>
      <td>Heikkinen, Miss. Laina</td>
      <td>female</td>
      <td>26.0</td>
      <td>0</td>
      <td>0</td>
      <td>STON/O2. 3101282</td>
      <td>7.9250</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
    <tr>
      <td>3</td>
      <td>4</td>
      <td>1</td>
      <td>1</td>
      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>
      <td>female</td>
      <td>35.0</td>
      <td>1</td>
      <td>0</td>
      <td>113803</td>
      <td>53.1000</td>
      <td>C123</td>
      <td>S</td>
    </tr>
    <tr>
      <td>4</td>
      <td>5</td>
      <td>0</td>
      <td>3</td>
      <td>Allen, Mr. William Henry</td>
      <td>male</td>
      <td>35.0</td>
      <td>0</td>
      <td>0</td>
      <td>373450</td>
      <td>8.0500</td>
      <td>NaN</td>
      <td>S</td>
    </tr>
  </tbody>
</table>
</div>



## .pivot_table()でピボットテーブルを作成


```python
data = {'Data':['Jan-1','Jan-1','Jan-1','Jan-2','Jan-2','Jan-2'],
       'User':['Emily', 'John','Nick','Kevin','Emily','John'],
       'Method':['Card','Card','Cash','Card','Cash','Cash'],
       'Price':[100,250,200,460,200,130]}
df = pd.DataFrame(data)
df
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Data</th>
      <th>User</th>
      <th>Method</th>
      <th>Price</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>Jan-1</td>
      <td>Emily</td>
      <td>Card</td>
      <td>100</td>
    </tr>
    <tr>
      <td>1</td>
      <td>Jan-1</td>
      <td>John</td>
      <td>Card</td>
      <td>250</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Jan-1</td>
      <td>Nick</td>
      <td>Cash</td>
      <td>200</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Jan-2</td>
      <td>Kevin</td>
      <td>Card</td>
      <td>460</td>
    </tr>
    <tr>
      <td>4</td>
      <td>Jan-2</td>
      <td>Emily</td>
      <td>Cash</td>
      <td>200</td>
    </tr>
    <tr>
      <td>5</td>
      <td>Jan-2</td>
      <td>John</td>
      <td>Cash</td>
      <td>130</td>
    </tr>
  </tbody>
</table>
</div>



#### valuesには，集計したいカラムを入れます．今回ではPriceです．
#### まず，それぞれのセルに入るのがvaluesで指定した値（今回ではPrice）．集計したいカラムです．
#### それに対してindexとcolumnsをそれぞれ指定したいカラムをリストで渡すだけ．


```python
df.pivot_table(values='Price', index=['Data', 'User'], columns=['Method'])
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Method</th>
      <th>Card</th>
      <th>Cash</th>
    </tr>
    <tr>
      <th>Data</th>
      <th>User</th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td rowspan="3" valign="top">Jan-1</td>
      <td>Emily</td>
      <td>100.0</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>John</td>
      <td>250.0</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>Nick</td>
      <td>NaN</td>
      <td>200.0</td>
    </tr>
    <tr>
      <td rowspan="3" valign="top">Jan-2</td>
      <td>Emily</td>
      <td>NaN</td>
      <td>200.0</td>
    </tr>
    <tr>
      <td>John</td>
      <td>NaN</td>
      <td>130.0</td>
    </tr>
    <tr>
      <td>Kevin</td>
      <td>460.0</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>
</div>




```python
df.pivot_table(values='Price', index=['Data', 'Method'], columns=['User'])
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>User</th>
      <th>Emily</th>
      <th>John</th>
      <th>Kevin</th>
      <th>Nick</th>
    </tr>
    <tr>
      <th>Data</th>
      <th>Method</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td rowspan="2" valign="top">Jan-1</td>
      <td>Card</td>
      <td>100.0</td>
      <td>250.0</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>Cash</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>200.0</td>
    </tr>
    <tr>
      <td rowspan="2" valign="top">Jan-2</td>
      <td>Card</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>460.0</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>Cash</td>
      <td>200.0</td>
      <td>130.0</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>
</div>



#### 最初に「どのカラムを集計したいのか」を明確にしてそのカラムをvaluesに入れてしまえばあとは欲しい情報をindexとcolumnsに入れていくだけです．

## .xs()でcross-section操作

#### .xs() はcross sectionの略です．これもあまり使いませんが，ピボットのような複数のindexをもったDataFrameを操作する際に重宝します．ピボットと合わせて覚えておくといい．
#### この .xs() は何をするときに必要かというと，先ほどのピボットテーブルで，例えば「Card」の行だけうまく抜き出したいときに使います．(まさにcross-section)




```python
#pivot.xs('Card', level = 'Method')
```