python実戦データ分析100本ノックのメモ

pythonで機械学習のコードを書いているときに、pandasやmatplotlibの使い方やデータの加工の流れがよく分からず、どうせなら実践的なデータを用いてデータ分析の流れを理解したいと思い、本書を手に取った。

## 一章で学んだこと
1. データの全体像を把握する
    - 顧客の目的は何か
    - そもそもどんなデータがあるか
    - 最も粒度の大きなデータファイルはどれか
    - 欠損値はあるか
    - 各データファイルで共通するデータ列はあるか
2. 使用するデータを作成する
    - 何を主軸にデータ同士を結合させるか
    - 結合後に集計ミスは無いか→検算
3. 各種統計量（平均、中央値など）を把握する
    - 更に詳しくデータの全体像を把握する
4. 目的に沿って、必要なデータを集計する
    - グラフ化して解決策をデータから読み取る


```python

```


```python
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
```


```python
customer_master = pd.read_csv('customer_master.csv')
customer_master.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>customer_id</th>
      <th>customer_name</th>
      <th>registration_date</th>
      <th>customer_name_kana</th>
      <th>email</th>
      <th>gender</th>
      <th>age</th>
      <th>birth</th>
      <th>pref</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>IK152942</td>
      <td>平田 裕次郎</td>
      <td>2019-01-01 00:25:33</td>
      <td>ひらた ゆうじろう</td>
      <td>hirata_yuujirou@example.com</td>
      <td>M</td>
      <td>29</td>
      <td>1990/6/10</td>
      <td>石川県</td>
    </tr>
    <tr>
      <th>1</th>
      <td>TS808488</td>
      <td>田村 詩織</td>
      <td>2019-01-01 01:13:45</td>
      <td>たむら しおり</td>
      <td>tamura_shiori@example.com</td>
      <td>F</td>
      <td>33</td>
      <td>1986/5/20</td>
      <td>東京都</td>
    </tr>
    <tr>
      <th>2</th>
      <td>AS834628</td>
      <td>久野 由樹</td>
      <td>2019-01-01 02:00:14</td>
      <td>ひさの ゆき</td>
      <td>hisano_yuki@example.com</td>
      <td>F</td>
      <td>63</td>
      <td>1956/1/2</td>
      <td>茨城県</td>
    </tr>
    <tr>
      <th>3</th>
      <td>AS345469</td>
      <td>鶴岡 薫</td>
      <td>2019-01-01 04:48:22</td>
      <td>つるおか かおる</td>
      <td>tsuruoka_kaoru@example.com</td>
      <td>M</td>
      <td>74</td>
      <td>1945/3/25</td>
      <td>東京都</td>
    </tr>
    <tr>
      <th>4</th>
      <td>GD892565</td>
      <td>大内 高史</td>
      <td>2019-01-01 04:54:51</td>
      <td>おおうち たかし</td>
      <td>oouchi_takashi@example.com</td>
      <td>M</td>
      <td>54</td>
      <td>1965/8/5</td>
      <td>千葉県</td>
    </tr>
  </tbody>
</table>
</div>




```python
item_master = pd.read_csv('item_master.csv')
item_master.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>item_id</th>
      <th>item_name</th>
      <th>item_price</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>S001</td>
      <td>PC-A</td>
      <td>50000</td>
    </tr>
    <tr>
      <th>1</th>
      <td>S002</td>
      <td>PC-B</td>
      <td>85000</td>
    </tr>
    <tr>
      <th>2</th>
      <td>S003</td>
      <td>PC-C</td>
      <td>120000</td>
    </tr>
    <tr>
      <th>3</th>
      <td>S004</td>
      <td>PC-D</td>
      <td>180000</td>
    </tr>
    <tr>
      <th>4</th>
      <td>S005</td>
      <td>PC-E</td>
      <td>210000</td>
    </tr>
  </tbody>
</table>
</div>




```python
transaction_1 = pd.read_csv('transaction_1.csv')#いつ、誰が、いくら買ったのか
transaction_2 = pd.read_csv('transaction_2.csv')#いつ、誰が、いくら買ったのか
transaction_detail_1 = pd.read_csv('transaction_detail_1.csv')#誰が、何を、いくつ買ったのか
transaction_detail_2 = pd.read_csv('transaction_detail_2.csv')#誰が、何を、いくつ買ったのか
```


```python
transaction_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>transaction_id</th>
      <th>price</th>
      <th>payment_date</th>
      <th>customer_id</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>T0000000113</td>
      <td>210000</td>
      <td>2019-02-01 01:36:57</td>
      <td>PL563502</td>
    </tr>
    <tr>
      <th>1</th>
      <td>T0000000114</td>
      <td>50000</td>
      <td>2019-02-01 01:37:23</td>
      <td>HD678019</td>
    </tr>
    <tr>
      <th>2</th>
      <td>T0000000115</td>
      <td>120000</td>
      <td>2019-02-01 02:34:19</td>
      <td>HD298120</td>
    </tr>
    <tr>
      <th>3</th>
      <td>T0000000116</td>
      <td>210000</td>
      <td>2019-02-01 02:47:23</td>
      <td>IK452215</td>
    </tr>
    <tr>
      <th>4</th>
      <td>T0000000117</td>
      <td>170000</td>
      <td>2019-02-01 04:33:46</td>
      <td>PL542865</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>4995</th>
      <td>T0000005108</td>
      <td>210000</td>
      <td>2019-06-15 02:42:41</td>
      <td>HD315748</td>
    </tr>
    <tr>
      <th>4996</th>
      <td>T0000005109</td>
      <td>150000</td>
      <td>2019-06-15 03:36:16</td>
      <td>HI215420</td>
    </tr>
    <tr>
      <th>4997</th>
      <td>T0000005110</td>
      <td>50000</td>
      <td>2019-06-15 03:44:06</td>
      <td>IK880102</td>
    </tr>
    <tr>
      <th>4998</th>
      <td>T0000005111</td>
      <td>210000</td>
      <td>2019-06-15 04:14:06</td>
      <td>IK074758</td>
    </tr>
    <tr>
      <th>4999</th>
      <td>T0000005112</td>
      <td>50000</td>
      <td>2019-06-15 04:42:38</td>
      <td>HD444151</td>
    </tr>
  </tbody>
</table>
<p>5000 rows × 4 columns</p>
</div>




```python
transaction_2
```




<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>transaction_id</th>
      <th>price</th>
      <th>payment_date</th>
      <th>customer_id</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>T0000005113</td>
      <td>295000</td>
      <td>2019-06-15 07:20:27</td>
      <td>TS169261</td>
    </tr>
    <tr>
      <th>1</th>
      <td>T0000005114</td>
      <td>50000</td>
      <td>2019-06-15 07:35:47</td>
      <td>HI599892</td>
    </tr>
    <tr>
      <th>2</th>
      <td>T0000005115</td>
      <td>85000</td>
      <td>2019-06-15 07:56:36</td>
      <td>HI421757</td>
    </tr>
    <tr>
      <th>3</th>
      <td>T0000005116</td>
      <td>50000</td>
      <td>2019-06-15 08:40:55</td>
      <td>OA386378</td>
    </tr>
    <tr>
      <th>4</th>
      <td>T0000005117</td>
      <td>120000</td>
      <td>2019-06-15 08:44:23</td>
      <td>TS506913</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>1781</th>
      <td>T0000006894</td>
      <td>180000</td>
      <td>2019-07-31 21:20:44</td>
      <td>HI400734</td>
    </tr>
    <tr>
      <th>1782</th>
      <td>T0000006895</td>
      <td>85000</td>
      <td>2019-07-31 21:52:48</td>
      <td>AS339451</td>
    </tr>
    <tr>
      <th>1783</th>
      <td>T0000006896</td>
      <td>100000</td>
      <td>2019-07-31 23:35:25</td>
      <td>OA027325</td>
    </tr>
    <tr>
      <th>1784</th>
      <td>T0000006897</td>
      <td>85000</td>
      <td>2019-07-31 23:39:35</td>
      <td>TS624738</td>
    </tr>
    <tr>
      <th>1785</th>
      <td>T0000006898</td>
      <td>85000</td>
      <td>2019-07-31 23:41:38</td>
      <td>AS834214</td>
    </tr>
  </tbody>
</table>
<p>1786 rows × 4 columns</p>
</div>




```python
transaction_detail_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>detail_id</th>
      <th>transaction_id</th>
      <th>item_id</th>
      <th>quantity</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>0</td>
      <td>T0000000113</td>
      <td>S005</td>
      <td>1</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1</td>
      <td>T0000000114</td>
      <td>S001</td>
      <td>1</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2</td>
      <td>T0000000115</td>
      <td>S003</td>
      <td>1</td>
    </tr>
    <tr>
      <th>3</th>
      <td>3</td>
      <td>T0000000116</td>
      <td>S005</td>
      <td>1</td>
    </tr>
    <tr>
      <th>4</th>
      <td>4</td>
      <td>T0000000117</td>
      <td>S002</td>
      <td>2</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>4995</th>
      <td>4995</td>
      <td>T0000004865</td>
      <td>S003</td>
      <td>1</td>
    </tr>
    <tr>
      <th>4996</th>
      <td>4996</td>
      <td>T0000004866</td>
      <td>S001</td>
      <td>3</td>
    </tr>
    <tr>
      <th>4997</th>
      <td>4997</td>
      <td>T0000004867</td>
      <td>S001</td>
      <td>3</td>
    </tr>
    <tr>
      <th>4998</th>
      <td>4998</td>
      <td>T0000004868</td>
      <td>S005</td>
      <td>1</td>
    </tr>
    <tr>
      <th>4999</th>
      <td>4999</td>
      <td>T0000004869</td>
      <td>S003</td>
      <td>1</td>
    </tr>
  </tbody>
</table>
<p>5000 rows × 4 columns</p>
</div>




```python
transaction_detail_2
```




<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>detail_id</th>
      <th>transaction_id</th>
      <th>item_id</th>
      <th>quantity</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>5000</td>
      <td>T0000004870</td>
      <td>S002</td>
      <td>3</td>
    </tr>
    <tr>
      <th>1</th>
      <td>5001</td>
      <td>T0000004871</td>
      <td>S003</td>
      <td>1</td>
    </tr>
    <tr>
      <th>2</th>
      <td>5002</td>
      <td>T0000004872</td>
      <td>S001</td>
      <td>2</td>
    </tr>
    <tr>
      <th>3</th>
      <td>5003</td>
      <td>T0000004873</td>
      <td>S004</td>
      <td>1</td>
    </tr>
    <tr>
      <th>4</th>
      <td>5004</td>
      <td>T0000004874</td>
      <td>S003</td>
      <td>2</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>2139</th>
      <td>7139</td>
      <td>T0000006894</td>
      <td>S004</td>
      <td>1</td>
    </tr>
    <tr>
      <th>2140</th>
      <td>7140</td>
      <td>T0000006895</td>
      <td>S002</td>
      <td>1</td>
    </tr>
    <tr>
      <th>2141</th>
      <td>7141</td>
      <td>T0000006896</td>
      <td>S001</td>
      <td>2</td>
    </tr>
    <tr>
      <th>2142</th>
      <td>7142</td>
      <td>T0000006897</td>
      <td>S002</td>
      <td>1</td>
    </tr>
    <tr>
      <th>2143</th>
      <td>7143</td>
      <td>T0000006898</td>
      <td>S002</td>
      <td>1</td>
    </tr>
  </tbody>
</table>
<p>2144 rows × 4 columns</p>
</div>



# 縦方向ユニオン


```python
transaction = pd.concat([transaction_1, transaction_2], ignore_index = True)
transaction
# print(transaction['transaction_id'].duplicated())
```




<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>transaction_id</th>
      <th>price</th>
      <th>payment_date</th>
      <th>customer_id</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>T0000000113</td>
      <td>210000</td>
      <td>2019-02-01 01:36:57</td>
      <td>PL563502</td>
    </tr>
    <tr>
      <th>1</th>
      <td>T0000000114</td>
      <td>50000</td>
      <td>2019-02-01 01:37:23</td>
      <td>HD678019</td>
    </tr>
    <tr>
      <th>2</th>
      <td>T0000000115</td>
      <td>120000</td>
      <td>2019-02-01 02:34:19</td>
      <td>HD298120</td>
    </tr>
    <tr>
      <th>3</th>
      <td>T0000000116</td>
      <td>210000</td>
      <td>2019-02-01 02:47:23</td>
      <td>IK452215</td>
    </tr>
    <tr>
      <th>4</th>
      <td>T0000000117</td>
      <td>170000</td>
      <td>2019-02-01 04:33:46</td>
      <td>PL542865</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>6781</th>
      <td>T0000006894</td>
      <td>180000</td>
      <td>2019-07-31 21:20:44</td>
      <td>HI400734</td>
    </tr>
    <tr>
      <th>6782</th>
      <td>T0000006895</td>
      <td>85000</td>
      <td>2019-07-31 21:52:48</td>
      <td>AS339451</td>
    </tr>
    <tr>
      <th>6783</th>
      <td>T0000006896</td>
      <td>100000</td>
      <td>2019-07-31 23:35:25</td>
      <td>OA027325</td>
    </tr>
    <tr>
      <th>6784</th>
      <td>T0000006897</td>
      <td>85000</td>
      <td>2019-07-31 23:39:35</td>
      <td>TS624738</td>
    </tr>
    <tr>
      <th>6785</th>
      <td>T0000006898</td>
      <td>85000</td>
      <td>2019-07-31 23:41:38</td>
      <td>AS834214</td>
    </tr>
  </tbody>
</table>
<p>6786 rows × 4 columns</p>
</div>




```python
transaction_detail = pd.concat([transaction_detail_1, transaction_detail_2], ignore_index = True)
transaction_detail
```




<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>detail_id</th>
      <th>transaction_id</th>
      <th>item_id</th>
      <th>quantity</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>0</td>
      <td>T0000000113</td>
      <td>S005</td>
      <td>1</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1</td>
      <td>T0000000114</td>
      <td>S001</td>
      <td>1</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2</td>
      <td>T0000000115</td>
      <td>S003</td>
      <td>1</td>
    </tr>
    <tr>
      <th>3</th>
      <td>3</td>
      <td>T0000000116</td>
      <td>S005</td>
      <td>1</td>
    </tr>
    <tr>
      <th>4</th>
      <td>4</td>
      <td>T0000000117</td>
      <td>S002</td>
      <td>2</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>7139</th>
      <td>7139</td>
      <td>T0000006894</td>
      <td>S004</td>
      <td>1</td>
    </tr>
    <tr>
      <th>7140</th>
      <td>7140</td>
      <td>T0000006895</td>
      <td>S002</td>
      <td>1</td>
    </tr>
    <tr>
      <th>7141</th>
      <td>7141</td>
      <td>T0000006896</td>
      <td>S001</td>
      <td>2</td>
    </tr>
    <tr>
      <th>7142</th>
      <td>7142</td>
      <td>T0000006897</td>
      <td>S002</td>
      <td>1</td>
    </tr>
    <tr>
      <th>7143</th>
      <td>7143</td>
      <td>T0000006898</td>
      <td>S002</td>
      <td>1</td>
    </tr>
  </tbody>
</table>
<p>7144 rows × 4 columns</p>
</div>



# 横方向ジョイン


```python
join_data  = pd.merge(transaction_detail, transaction[['transaction_id', 'payment_date', 'customer_id']], on='transaction_id', how='left')
join_data
```




<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>detail_id</th>
      <th>transaction_id</th>
      <th>item_id</th>
      <th>quantity</th>
      <th>payment_date</th>
      <th>customer_id</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>0</td>
      <td>T0000000113</td>
      <td>S005</td>
      <td>1</td>
      <td>2019-02-01 01:36:57</td>
      <td>PL563502</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1</td>
      <td>T0000000114</td>
      <td>S001</td>
      <td>1</td>
      <td>2019-02-01 01:37:23</td>
      <td>HD678019</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2</td>
      <td>T0000000115</td>
      <td>S003</td>
      <td>1</td>
      <td>2019-02-01 02:34:19</td>
      <td>HD298120</td>
    </tr>
    <tr>
      <th>3</th>
      <td>3</td>
      <td>T0000000116</td>
      <td>S005</td>
      <td>1</td>
      <td>2019-02-01 02:47:23</td>
      <td>IK452215</td>
    </tr>
    <tr>
      <th>4</th>
      <td>4</td>
      <td>T0000000117</td>
      <td>S002</td>
      <td>2</td>
      <td>2019-02-01 04:33:46</td>
      <td>PL542865</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>7139</th>
      <td>7139</td>
      <td>T0000006894</td>
      <td>S004</td>
      <td>1</td>
      <td>2019-07-31 21:20:44</td>
      <td>HI400734</td>
    </tr>
    <tr>
      <th>7140</th>
      <td>7140</td>
      <td>T0000006895</td>
      <td>S002</td>
      <td>1</td>
      <td>2019-07-31 21:52:48</td>
      <td>AS339451</td>
    </tr>
    <tr>
      <th>7141</th>
      <td>7141</td>
      <td>T0000006896</td>
      <td>S001</td>
      <td>2</td>
      <td>2019-07-31 23:35:25</td>
      <td>OA027325</td>
    </tr>
    <tr>
      <th>7142</th>
      <td>7142</td>
      <td>T0000006897</td>
      <td>S002</td>
      <td>1</td>
      <td>2019-07-31 23:39:35</td>
      <td>TS624738</td>
    </tr>
    <tr>
      <th>7143</th>
      <td>7143</td>
      <td>T0000006898</td>
      <td>S002</td>
      <td>1</td>
      <td>2019-07-31 23:41:38</td>
      <td>AS834214</td>
    </tr>
  </tbody>
</table>
<p>7144 rows × 6 columns</p>
</div>




```python
print(len(transaction))
```

    6786



```python
print(len(transaction_detail))
```

    7144



```python
print(len(join_data))
```

    7144


# マスターデータをジョイン


```python
join_data  = pd.merge(join_data, customer_master, on='customer_id', how='left')
join_data  = pd.merge(join_data, item_master, on='item_id', how='left')
join_data.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>detail_id</th>
      <th>transaction_id</th>
      <th>item_id</th>
      <th>quantity</th>
      <th>payment_date</th>
      <th>customer_id</th>
      <th>customer_name</th>
      <th>registration_date</th>
      <th>customer_name_kana</th>
      <th>email</th>
      <th>gender</th>
      <th>age</th>
      <th>birth</th>
      <th>pref</th>
      <th>item_name</th>
      <th>item_price</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>0</td>
      <td>T0000000113</td>
      <td>S005</td>
      <td>1</td>
      <td>2019-02-01 01:36:57</td>
      <td>PL563502</td>
      <td>井本 芳正</td>
      <td>2019-01-07 14:34:35</td>
      <td>いもと よしまさ</td>
      <td>imoto_yoshimasa@example.com</td>
      <td>M</td>
      <td>30</td>
      <td>1989/7/15</td>
      <td>熊本県</td>
      <td>PC-E</td>
      <td>210000</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1</td>
      <td>T0000000114</td>
      <td>S001</td>
      <td>1</td>
      <td>2019-02-01 01:37:23</td>
      <td>HD678019</td>
      <td>三船 六郎</td>
      <td>2019-01-27 18:00:11</td>
      <td>みふね ろくろう</td>
      <td>mifune_rokurou@example.com</td>
      <td>M</td>
      <td>73</td>
      <td>1945/11/29</td>
      <td>京都府</td>
      <td>PC-A</td>
      <td>50000</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2</td>
      <td>T0000000115</td>
      <td>S003</td>
      <td>1</td>
      <td>2019-02-01 02:34:19</td>
      <td>HD298120</td>
      <td>山根 小雁</td>
      <td>2019-01-11 08:16:02</td>
      <td>やまね こがん</td>
      <td>yamane_kogan@example.com</td>
      <td>M</td>
      <td>42</td>
      <td>1977/5/17</td>
      <td>茨城県</td>
      <td>PC-C</td>
      <td>120000</td>
    </tr>
    <tr>
      <th>3</th>
      <td>3</td>
      <td>T0000000116</td>
      <td>S005</td>
      <td>1</td>
      <td>2019-02-01 02:47:23</td>
      <td>IK452215</td>
      <td>池田 菜摘</td>
      <td>2019-01-10 05:07:38</td>
      <td>いけだ なつみ</td>
      <td>ikeda_natsumi@example.com</td>
      <td>F</td>
      <td>47</td>
      <td>1972/3/17</td>
      <td>兵庫県</td>
      <td>PC-E</td>
      <td>210000</td>
    </tr>
    <tr>
      <th>4</th>
      <td>4</td>
      <td>T0000000117</td>
      <td>S002</td>
      <td>2</td>
      <td>2019-02-01 04:33:46</td>
      <td>PL542865</td>
      <td>栗田 憲一</td>
      <td>2019-01-25 06:46:05</td>
      <td>くりた けんいち</td>
      <td>kurita_kenichi@example.com</td>
      <td>M</td>
      <td>74</td>
      <td>1944/12/17</td>
      <td>長崎県</td>
      <td>PC-B</td>
      <td>85000</td>
    </tr>
  </tbody>
</table>
</div>




```python
len(customer_master)
```




    5000




```python
len(item_master)
```




    5



# 必要なデータ列を作成する


```python
join_data['price'] = join_data['quantity'] * join_data['item_price']
join_data[['quantity', 'item_price', 'price']].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>quantity</th>
      <th>item_price</th>
      <th>price</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>1</td>
      <td>210000</td>
      <td>210000</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1</td>
      <td>50000</td>
      <td>50000</td>
    </tr>
    <tr>
      <th>2</th>
      <td>1</td>
      <td>120000</td>
      <td>120000</td>
    </tr>
    <tr>
      <th>3</th>
      <td>1</td>
      <td>210000</td>
      <td>210000</td>
    </tr>
    <tr>
      <th>4</th>
      <td>2</td>
      <td>85000</td>
      <td>170000</td>
    </tr>
  </tbody>
</table>
</div>




```python
join_data
```




<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>detail_id</th>
      <th>transaction_id</th>
      <th>item_id</th>
      <th>quantity</th>
      <th>payment_date</th>
      <th>customer_id</th>
      <th>customer_name</th>
      <th>registration_date</th>
      <th>customer_name_kana</th>
      <th>email</th>
      <th>gender</th>
      <th>age</th>
      <th>birth</th>
      <th>pref</th>
      <th>item_name</th>
      <th>item_price</th>
      <th>price</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>0</td>
      <td>T0000000113</td>
      <td>S005</td>
      <td>1</td>
      <td>2019-02-01 01:36:57</td>
      <td>PL563502</td>
      <td>井本 芳正</td>
      <td>2019-01-07 14:34:35</td>
      <td>いもと よしまさ</td>
      <td>imoto_yoshimasa@example.com</td>
      <td>M</td>
      <td>30</td>
      <td>1989/7/15</td>
      <td>熊本県</td>
      <td>PC-E</td>
      <td>210000</td>
      <td>210000</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1</td>
      <td>T0000000114</td>
      <td>S001</td>
      <td>1</td>
      <td>2019-02-01 01:37:23</td>
      <td>HD678019</td>
      <td>三船 六郎</td>
      <td>2019-01-27 18:00:11</td>
      <td>みふね ろくろう</td>
      <td>mifune_rokurou@example.com</td>
      <td>M</td>
      <td>73</td>
      <td>1945/11/29</td>
      <td>京都府</td>
      <td>PC-A</td>
      <td>50000</td>
      <td>50000</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2</td>
      <td>T0000000115</td>
      <td>S003</td>
      <td>1</td>
      <td>2019-02-01 02:34:19</td>
      <td>HD298120</td>
      <td>山根 小雁</td>
      <td>2019-01-11 08:16:02</td>
      <td>やまね こがん</td>
      <td>yamane_kogan@example.com</td>
      <td>M</td>
      <td>42</td>
      <td>1977/5/17</td>
      <td>茨城県</td>
      <td>PC-C</td>
      <td>120000</td>
      <td>120000</td>
    </tr>
    <tr>
      <th>3</th>
      <td>3</td>
      <td>T0000000116</td>
      <td>S005</td>
      <td>1</td>
      <td>2019-02-01 02:47:23</td>
      <td>IK452215</td>
      <td>池田 菜摘</td>
      <td>2019-01-10 05:07:38</td>
      <td>いけだ なつみ</td>
      <td>ikeda_natsumi@example.com</td>
      <td>F</td>
      <td>47</td>
      <td>1972/3/17</td>
      <td>兵庫県</td>
      <td>PC-E</td>
      <td>210000</td>
      <td>210000</td>
    </tr>
    <tr>
      <th>4</th>
      <td>4</td>
      <td>T0000000117</td>
      <td>S002</td>
      <td>2</td>
      <td>2019-02-01 04:33:46</td>
      <td>PL542865</td>
      <td>栗田 憲一</td>
      <td>2019-01-25 06:46:05</td>
      <td>くりた けんいち</td>
      <td>kurita_kenichi@example.com</td>
      <td>M</td>
      <td>74</td>
      <td>1944/12/17</td>
      <td>長崎県</td>
      <td>PC-B</td>
      <td>85000</td>
      <td>170000</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>7139</th>
      <td>7139</td>
      <td>T0000006894</td>
      <td>S004</td>
      <td>1</td>
      <td>2019-07-31 21:20:44</td>
      <td>HI400734</td>
      <td>宍戸 明</td>
      <td>2019-01-04 13:24:40</td>
      <td>ししど あきら</td>
      <td>shishido_akira@example.com</td>
      <td>M</td>
      <td>64</td>
      <td>1955/1/13</td>
      <td>福井県</td>
      <td>PC-D</td>
      <td>180000</td>
      <td>180000</td>
    </tr>
    <tr>
      <th>7140</th>
      <td>7140</td>
      <td>T0000006895</td>
      <td>S002</td>
      <td>1</td>
      <td>2019-07-31 21:52:48</td>
      <td>AS339451</td>
      <td>相原 みき</td>
      <td>2019-02-11 19:34:02</td>
      <td>あいはら みき</td>
      <td>aihara_miki@example.com</td>
      <td>F</td>
      <td>74</td>
      <td>1945/2/3</td>
      <td>北海道</td>
      <td>PC-B</td>
      <td>85000</td>
      <td>85000</td>
    </tr>
    <tr>
      <th>7141</th>
      <td>7141</td>
      <td>T0000006896</td>
      <td>S001</td>
      <td>2</td>
      <td>2019-07-31 23:35:25</td>
      <td>OA027325</td>
      <td>松田 早紀</td>
      <td>2019-04-17 09:23:50</td>
      <td>まつだ さき</td>
      <td>matsuda_saki@example.com</td>
      <td>F</td>
      <td>40</td>
      <td>1979/5/25</td>
      <td>福島県</td>
      <td>PC-A</td>
      <td>50000</td>
      <td>100000</td>
    </tr>
    <tr>
      <th>7142</th>
      <td>7142</td>
      <td>T0000006897</td>
      <td>S002</td>
      <td>1</td>
      <td>2019-07-31 23:39:35</td>
      <td>TS624738</td>
      <td>進藤 正敏</td>
      <td>2019-02-20 18:15:56</td>
      <td>しんどう まさとし</td>
      <td>shinndou_masatoshi@example.com</td>
      <td>M</td>
      <td>56</td>
      <td>1963/2/21</td>
      <td>東京都</td>
      <td>PC-B</td>
      <td>85000</td>
      <td>85000</td>
    </tr>
    <tr>
      <th>7143</th>
      <td>7143</td>
      <td>T0000006898</td>
      <td>S002</td>
      <td>1</td>
      <td>2019-07-31 23:41:38</td>
      <td>AS834214</td>
      <td>田原 結子</td>
      <td>2019-04-07 03:20:19</td>
      <td>たはら ゆうこ</td>
      <td>tahara_yuuko@example.com</td>
      <td>F</td>
      <td>74</td>
      <td>1944/12/18</td>
      <td>愛知県</td>
      <td>PC-B</td>
      <td>85000</td>
      <td>85000</td>
    </tr>
  </tbody>
</table>
<p>7144 rows × 17 columns</p>
</div>



# データの検算


```python
print(join_data['price'].sum())
print(transaction['price'].sum())
```

    971135000
    971135000



```python
join_data['price'].sum() == transaction['price'].sum()
```




    True



# 各種統計量を把握
- 欠損データ
- 全体の数字感


```python
join_data.isnull().sum()
```




    detail_id             0
    transaction_id        0
    item_id               0
    quantity              0
    payment_date          0
    customer_id           0
    customer_name         0
    registration_date     0
    customer_name_kana    0
    email                 0
    gender                0
    age                   0
    birth                 0
    pref                  0
    item_name             0
    item_price            0
    price                 0
    dtype: int64




```python
join_data.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>detail_id</th>
      <th>quantity</th>
      <th>age</th>
      <th>item_price</th>
      <th>price</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>count</th>
      <td>7144.000000</td>
      <td>7144.000000</td>
      <td>7144.000000</td>
      <td>7144.000000</td>
      <td>7144.000000</td>
    </tr>
    <tr>
      <th>mean</th>
      <td>3571.500000</td>
      <td>1.199888</td>
      <td>50.265677</td>
      <td>121698.628219</td>
      <td>135937.150056</td>
    </tr>
    <tr>
      <th>std</th>
      <td>2062.439494</td>
      <td>0.513647</td>
      <td>17.190314</td>
      <td>64571.311830</td>
      <td>68511.453297</td>
    </tr>
    <tr>
      <th>min</th>
      <td>0.000000</td>
      <td>1.000000</td>
      <td>20.000000</td>
      <td>50000.000000</td>
      <td>50000.000000</td>
    </tr>
    <tr>
      <th>25%</th>
      <td>1785.750000</td>
      <td>1.000000</td>
      <td>36.000000</td>
      <td>50000.000000</td>
      <td>85000.000000</td>
    </tr>
    <tr>
      <th>50%</th>
      <td>3571.500000</td>
      <td>1.000000</td>
      <td>50.000000</td>
      <td>102500.000000</td>
      <td>120000.000000</td>
    </tr>
    <tr>
      <th>75%</th>
      <td>5357.250000</td>
      <td>1.000000</td>
      <td>65.000000</td>
      <td>187500.000000</td>
      <td>210000.000000</td>
    </tr>
    <tr>
      <th>max</th>
      <td>7143.000000</td>
      <td>4.000000</td>
      <td>80.000000</td>
      <td>210000.000000</td>
      <td>420000.000000</td>
    </tr>
  </tbody>
</table>
</div>




```python
print(join_data['payment_date'].min())
```

    2019-02-01 01:36:57



```python
print(join_data['payment_date'].max())
```

    2019-07-31 23:41:38


# 月別でデータを集計


```python
join_data.dtypes
```




    detail_id              int64
    transaction_id        object
    item_id               object
    quantity               int64
    payment_date          object
    customer_id           object
    customer_name         object
    registration_date     object
    customer_name_kana    object
    email                 object
    gender                object
    age                    int64
    birth                 object
    pref                  object
    item_name             object
    item_price             int64
    price                  int64
    dtype: object




```python
join_data['payment_date'] = pd.to_datetime(join_data['payment_date'])
join_data['payment_month']  = join_data['payment_date'].dt.strftime('%Y%m')
join_data[['payment_date', 'payment_month']]
```




<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>payment_date</th>
      <th>payment_month</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>2019-02-01 01:36:57</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2019-02-01 01:37:23</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2019-02-01 02:34:19</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>3</th>
      <td>2019-02-01 02:47:23</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>4</th>
      <td>2019-02-01 04:33:46</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>7139</th>
      <td>2019-07-31 21:20:44</td>
      <td>201907</td>
    </tr>
    <tr>
      <th>7140</th>
      <td>2019-07-31 21:52:48</td>
      <td>201907</td>
    </tr>
    <tr>
      <th>7141</th>
      <td>2019-07-31 23:35:25</td>
      <td>201907</td>
    </tr>
    <tr>
      <th>7142</th>
      <td>2019-07-31 23:39:35</td>
      <td>201907</td>
    </tr>
    <tr>
      <th>7143</th>
      <td>2019-07-31 23:41:38</td>
      <td>201907</td>
    </tr>
  </tbody>
</table>
<p>7144 rows × 2 columns</p>
</div>




```python
join_data.dtypes
```




    detail_id                      int64
    transaction_id                object
    item_id                       object
    quantity                       int64
    payment_date          datetime64[ns]
    customer_id                   object
    customer_name                 object
    registration_date             object
    customer_name_kana            object
    email                         object
    gender                        object
    age                            int64
    birth                         object
    pref                          object
    item_name                     object
    item_price                     int64
    price                          int64
    payment_month                 object
    dtype: object




```python
join_data.groupby('payment_month').sum()
```




<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>detail_id</th>
      <th>quantity</th>
      <th>age</th>
      <th>item_price</th>
      <th>price</th>
    </tr>
    <tr>
      <th>payment_month</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>201902</th>
      <td>676866</td>
      <td>1403</td>
      <td>59279</td>
      <td>142805000</td>
      <td>160185000</td>
    </tr>
    <tr>
      <th>201903</th>
      <td>2071474</td>
      <td>1427</td>
      <td>58996</td>
      <td>142980000</td>
      <td>160370000</td>
    </tr>
    <tr>
      <th>201904</th>
      <td>3476816</td>
      <td>1421</td>
      <td>59246</td>
      <td>143670000</td>
      <td>160510000</td>
    </tr>
    <tr>
      <th>201905</th>
      <td>4812795</td>
      <td>1390</td>
      <td>58195</td>
      <td>139655000</td>
      <td>155420000</td>
    </tr>
    <tr>
      <th>201906</th>
      <td>6369999</td>
      <td>1446</td>
      <td>61070</td>
      <td>147090000</td>
      <td>164030000</td>
    </tr>
    <tr>
      <th>201907</th>
      <td>8106846</td>
      <td>1485</td>
      <td>62312</td>
      <td>153215000</td>
      <td>170620000</td>
    </tr>
  </tbody>
</table>
</div>




```python
join_data.groupby('payment_month').sum()['price']
```




    payment_month
    201902    160185000
    201903    160370000
    201904    160510000
    201905    155420000
    201906    164030000
    201907    170620000
    Name: price, dtype: int64




```python
join_data.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>detail_id</th>
      <th>transaction_id</th>
      <th>item_id</th>
      <th>quantity</th>
      <th>payment_date</th>
      <th>customer_id</th>
      <th>customer_name</th>
      <th>registration_date</th>
      <th>customer_name_kana</th>
      <th>email</th>
      <th>gender</th>
      <th>age</th>
      <th>birth</th>
      <th>pref</th>
      <th>item_name</th>
      <th>item_price</th>
      <th>price</th>
      <th>payment_month</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>0</td>
      <td>T0000000113</td>
      <td>S005</td>
      <td>1</td>
      <td>2019-02-01 01:36:57</td>
      <td>PL563502</td>
      <td>井本 芳正</td>
      <td>2019-01-07 14:34:35</td>
      <td>いもと よしまさ</td>
      <td>imoto_yoshimasa@example.com</td>
      <td>M</td>
      <td>30</td>
      <td>1989/7/15</td>
      <td>熊本県</td>
      <td>PC-E</td>
      <td>210000</td>
      <td>210000</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>1</th>
      <td>1</td>
      <td>T0000000114</td>
      <td>S001</td>
      <td>1</td>
      <td>2019-02-01 01:37:23</td>
      <td>HD678019</td>
      <td>三船 六郎</td>
      <td>2019-01-27 18:00:11</td>
      <td>みふね ろくろう</td>
      <td>mifune_rokurou@example.com</td>
      <td>M</td>
      <td>73</td>
      <td>1945/11/29</td>
      <td>京都府</td>
      <td>PC-A</td>
      <td>50000</td>
      <td>50000</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2</td>
      <td>T0000000115</td>
      <td>S003</td>
      <td>1</td>
      <td>2019-02-01 02:34:19</td>
      <td>HD298120</td>
      <td>山根 小雁</td>
      <td>2019-01-11 08:16:02</td>
      <td>やまね こがん</td>
      <td>yamane_kogan@example.com</td>
      <td>M</td>
      <td>42</td>
      <td>1977/5/17</td>
      <td>茨城県</td>
      <td>PC-C</td>
      <td>120000</td>
      <td>120000</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>3</th>
      <td>3</td>
      <td>T0000000116</td>
      <td>S005</td>
      <td>1</td>
      <td>2019-02-01 02:47:23</td>
      <td>IK452215</td>
      <td>池田 菜摘</td>
      <td>2019-01-10 05:07:38</td>
      <td>いけだ なつみ</td>
      <td>ikeda_natsumi@example.com</td>
      <td>F</td>
      <td>47</td>
      <td>1972/3/17</td>
      <td>兵庫県</td>
      <td>PC-E</td>
      <td>210000</td>
      <td>210000</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>4</th>
      <td>4</td>
      <td>T0000000117</td>
      <td>S002</td>
      <td>2</td>
      <td>2019-02-01 04:33:46</td>
      <td>PL542865</td>
      <td>栗田 憲一</td>
      <td>2019-01-25 06:46:05</td>
      <td>くりた けんいち</td>
      <td>kurita_kenichi@example.com</td>
      <td>M</td>
      <td>74</td>
      <td>1944/12/17</td>
      <td>長崎県</td>
      <td>PC-B</td>
      <td>85000</td>
      <td>170000</td>
      <td>201902</td>
    </tr>
  </tbody>
</table>
</div>



# 月別、商品別でデータを集計


```python
join_data.groupby(['payment_month', 'item_name']).sum()#２つの要素を軸にしてデータを集計
```




<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></th>
      <th>detail_id</th>
      <th>quantity</th>
      <th>age</th>
      <th>item_price</th>
      <th>price</th>
    </tr>
    <tr>
      <th>payment_month</th>
      <th>item_name</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th rowspan="5" valign="top">201902</th>
      <th>PC-A</th>
      <td>192357</td>
      <td>483</td>
      <td>17274</td>
      <td>16700000</td>
      <td>24150000</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>138010</td>
      <td>297</td>
      <td>11928</td>
      <td>20145000</td>
      <td>25245000</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>81497</td>
      <td>165</td>
      <td>7727</td>
      <td>18240000</td>
      <td>19800000</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>91435</td>
      <td>173</td>
      <td>8377</td>
      <td>29340000</td>
      <td>31140000</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>173567</td>
      <td>285</td>
      <td>13973</td>
      <td>58380000</td>
      <td>59850000</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201903</th>
      <th>PC-A</th>
      <td>635721</td>
      <td>520</td>
      <td>18300</td>
      <td>18200000</td>
      <td>26000000</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>413971</td>
      <td>300</td>
      <td>12084</td>
      <td>20230000</td>
      <td>25500000</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>245842</td>
      <td>159</td>
      <td>7178</td>
      <td>17160000</td>
      <td>19080000</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>247832</td>
      <td>143</td>
      <td>6719</td>
      <td>25020000</td>
      <td>25740000</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>528108</td>
      <td>305</td>
      <td>14715</td>
      <td>62370000</td>
      <td>64050000</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201904</th>
      <th>PC-A</th>
      <td>1076125</td>
      <td>518</td>
      <td>18198</td>
      <td>18450000</td>
      <td>25900000</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>638789</td>
      <td>276</td>
      <td>10926</td>
      <td>18360000</td>
      <td>23460000</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>496212</td>
      <td>183</td>
      <td>8203</td>
      <td>20160000</td>
      <td>21960000</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>368204</td>
      <td>135</td>
      <td>6340</td>
      <td>22860000</td>
      <td>24300000</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>897486</td>
      <td>309</td>
      <td>15579</td>
      <td>63840000</td>
      <td>64890000</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201905</th>
      <th>PC-A</th>
      <td>1487314</td>
      <td>497</td>
      <td>18040</td>
      <td>18100000</td>
      <td>24850000</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>990251</td>
      <td>298</td>
      <td>12080</td>
      <td>20485000</td>
      <td>25330000</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>631230</td>
      <td>171</td>
      <td>7466</td>
      <td>18480000</td>
      <td>20520000</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>567773</td>
      <td>144</td>
      <td>6913</td>
      <td>24840000</td>
      <td>25920000</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>1136227</td>
      <td>280</td>
      <td>13696</td>
      <td>57750000</td>
      <td>58800000</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201906</th>
      <th>PC-A</th>
      <td>1874692</td>
      <td>520</td>
      <td>18426</td>
      <td>17750000</td>
      <td>26000000</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>1229074</td>
      <td>282</td>
      <td>11613</td>
      <td>19720000</td>
      <td>23970000</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>891651</td>
      <td>182</td>
      <td>8644</td>
      <td>20040000</td>
      <td>21840000</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>792209</td>
      <td>160</td>
      <td>7195</td>
      <td>27000000</td>
      <td>28800000</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>1582373</td>
      <td>302</td>
      <td>15192</td>
      <td>62580000</td>
      <td>63420000</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201907</th>
      <th>PC-A</th>
      <td>2339702</td>
      <td>505</td>
      <td>17941</td>
      <td>17950000</td>
      <td>25250000</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>1727205</td>
      <td>332</td>
      <td>13591</td>
      <td>22525000</td>
      <td>28220000</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>946483</td>
      <td>162</td>
      <td>7060</td>
      <td>17400000</td>
      <td>19440000</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>912353</td>
      <td>145</td>
      <td>6851</td>
      <td>25200000</td>
      <td>26100000</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>2181103</td>
      <td>341</td>
      <td>16869</td>
      <td>70140000</td>
      <td>71610000</td>
    </tr>
  </tbody>
</table>
</div>




```python
join_data.groupby(['payment_month', 'item_name']).sum()[['price', 'quantity']]#２つの要素を軸にしてデータを集計
```




<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></th>
      <th>price</th>
      <th>quantity</th>
    </tr>
    <tr>
      <th>payment_month</th>
      <th>item_name</th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th rowspan="5" valign="top">201902</th>
      <th>PC-A</th>
      <td>24150000</td>
      <td>483</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>25245000</td>
      <td>297</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>19800000</td>
      <td>165</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>31140000</td>
      <td>173</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>59850000</td>
      <td>285</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201903</th>
      <th>PC-A</th>
      <td>26000000</td>
      <td>520</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>25500000</td>
      <td>300</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>19080000</td>
      <td>159</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>25740000</td>
      <td>143</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>64050000</td>
      <td>305</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201904</th>
      <th>PC-A</th>
      <td>25900000</td>
      <td>518</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>23460000</td>
      <td>276</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>21960000</td>
      <td>183</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>24300000</td>
      <td>135</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>64890000</td>
      <td>309</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201905</th>
      <th>PC-A</th>
      <td>24850000</td>
      <td>497</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>25330000</td>
      <td>298</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>20520000</td>
      <td>171</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>25920000</td>
      <td>144</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>58800000</td>
      <td>280</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201906</th>
      <th>PC-A</th>
      <td>26000000</td>
      <td>520</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>23970000</td>
      <td>282</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>21840000</td>
      <td>182</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>28800000</td>
      <td>160</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>63420000</td>
      <td>302</td>
    </tr>
    <tr>
      <th rowspan="5" valign="top">201907</th>
      <th>PC-A</th>
      <td>25250000</td>
      <td>505</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>28220000</td>
      <td>332</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>19440000</td>
      <td>162</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>26100000</td>
      <td>145</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>71610000</td>
      <td>341</td>
    </tr>
  </tbody>
</table>
</div>




```python
pd.pivot_table(join_data, index = 'item_name', columns = 'payment_month', values = ['price', 'quantity'], aggfunc = 'sum')
```




<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="6" halign="left">price</th>
      <th colspan="6" halign="left">quantity</th>
    </tr>
    <tr>
      <th>payment_month</th>
      <th>201902</th>
      <th>201903</th>
      <th>201904</th>
      <th>201905</th>
      <th>201906</th>
      <th>201907</th>
      <th>201902</th>
      <th>201903</th>
      <th>201904</th>
      <th>201905</th>
      <th>201906</th>
      <th>201907</th>
    </tr>
    <tr>
      <th>item_name</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>
      <th>PC-A</th>
      <td>24150000</td>
      <td>26000000</td>
      <td>25900000</td>
      <td>24850000</td>
      <td>26000000</td>
      <td>25250000</td>
      <td>483</td>
      <td>520</td>
      <td>518</td>
      <td>497</td>
      <td>520</td>
      <td>505</td>
    </tr>
    <tr>
      <th>PC-B</th>
      <td>25245000</td>
      <td>25500000</td>
      <td>23460000</td>
      <td>25330000</td>
      <td>23970000</td>
      <td>28220000</td>
      <td>297</td>
      <td>300</td>
      <td>276</td>
      <td>298</td>
      <td>282</td>
      <td>332</td>
    </tr>
    <tr>
      <th>PC-C</th>
      <td>19800000</td>
      <td>19080000</td>
      <td>21960000</td>
      <td>20520000</td>
      <td>21840000</td>
      <td>19440000</td>
      <td>165</td>
      <td>159</td>
      <td>183</td>
      <td>171</td>
      <td>182</td>
      <td>162</td>
    </tr>
    <tr>
      <th>PC-D</th>
      <td>31140000</td>
      <td>25740000</td>
      <td>24300000</td>
      <td>25920000</td>
      <td>28800000</td>
      <td>26100000</td>
      <td>173</td>
      <td>143</td>
      <td>135</td>
      <td>144</td>
      <td>160</td>
      <td>145</td>
    </tr>
    <tr>
      <th>PC-E</th>
      <td>59850000</td>
      <td>64050000</td>
      <td>64890000</td>
      <td>58800000</td>
      <td>63420000</td>
      <td>71610000</td>
      <td>285</td>
      <td>305</td>
      <td>309</td>
      <td>280</td>
      <td>302</td>
      <td>341</td>
    </tr>
  </tbody>
</table>
</div>




```python

```


```python
graph_data = pd.pivot_table(join_data, index = 'payment_month', columns='item_name', values = 'price', aggfunc='sum')
graph_data
# index: 行見出し
# columns: 列見出し
# 第一引数: 元データのpandas.DataFrameオブジェクト
# values: 元データの列名指定→その列に対する結果のみが算出される
```




<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>item_name</th>
      <th>PC-A</th>
      <th>PC-B</th>
      <th>PC-C</th>
      <th>PC-D</th>
      <th>PC-E</th>
    </tr>
    <tr>
      <th>payment_month</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>201902</th>
      <td>24150000</td>
      <td>25245000</td>
      <td>19800000</td>
      <td>31140000</td>
      <td>59850000</td>
    </tr>
    <tr>
      <th>201903</th>
      <td>26000000</td>
      <td>25500000</td>
      <td>19080000</td>
      <td>25740000</td>
      <td>64050000</td>
    </tr>
    <tr>
      <th>201904</th>
      <td>25900000</td>
      <td>23460000</td>
      <td>21960000</td>
      <td>24300000</td>
      <td>64890000</td>
    </tr>
    <tr>
      <th>201905</th>
      <td>24850000</td>
      <td>25330000</td>
      <td>20520000</td>
      <td>25920000</td>
      <td>58800000</td>
    </tr>
    <tr>
      <th>201906</th>
      <td>26000000</td>
      <td>23970000</td>
      <td>21840000</td>
      <td>28800000</td>
      <td>63420000</td>
    </tr>
    <tr>
      <th>201907</th>
      <td>25250000</td>
      <td>28220000</td>
      <td>19440000</td>
      <td>26100000</td>
      <td>71610000</td>
    </tr>
  </tbody>
</table>
</div>




```python
plt.plot(list(graph_data.index), graph_data['PC-A'], label='PC-A')
plt.plot(list(graph_data.index), graph_data['PC-B'], label='PC-B')
plt.plot(list(graph_data.index), graph_data['PC-C'], label='PC-C')
plt.plot(list(graph_data.index), graph_data['PC-D'], label='PC-D')
plt.plot(list(graph_data.index), graph_data['PC-E'], label='PC-E')
plt.legend()
```




    <matplotlib.legend.Legend at 0x7fd7cfd302e0>




    
![png](output_46_1.png)
    



```python

```


```python

```

## 学んだこと
1. データの全体像を把握する
    - 顧客の目的は何か
    - そもそもどんなデータがあるか
    - 最も粒度の大きなデータファイルはどれか
    - 欠損値はあるか
    - 各データファイルで共通するデータ列はあるか
2. 使用するデータを作成する
    - 何を主軸にデータ同士を結合させるか
    - 結合後に集計ミスは無いか→検算
3. 各種統計量（平均、中央値など）を把握する
    - 更に詳しくデータの全体像を把握する
4. 目的に沿って、必要なデータを集計する
    - グラフ化して解決策をデータから読み取る


```python

```


```python

```






# 二章で学んだこと
- データ加工など前処理を行わないと、間違った結果や解釈を抽出してしまう
    - 丁寧に欠損値や半角全角部分を加工してあげれば、正しい結果や解釈が出来るようになる


```python

```


```python
import pandas as pd
```

# データを読み込む


```python
uriage_data = pd.read_csv('uriage.csv')
uriage_data.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>purchase_date</th>
      <th>item_name</th>
      <th>item_price</th>
      <th>customer_name</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>2019-06-13 18:02:34</td>
      <td>商品A</td>
      <td>100.0</td>
      <td>深井菜々美</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2019-07-13 13:05:29</td>
      <td>商 品 S</td>
      <td>NaN</td>
      <td>浅田賢二</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2019-05-11 19:42:07</td>
      <td>商 品 a</td>
      <td>NaN</td>
      <td>南部慶二</td>
    </tr>
    <tr>
      <th>3</th>
      <td>2019-02-12 23:40:45</td>
      <td>商品Z</td>
      <td>2600.0</td>
      <td>麻生莉緒</td>
    </tr>
    <tr>
      <th>4</th>
      <td>2019-04-22 03:09:35</td>
      <td>商品a</td>
      <td>NaN</td>
      <td>平田鉄二</td>
    </tr>
  </tbody>
</table>
</div>




```python
kokyaku_data = pd.read_excel('kokyaku_daicho.xlsx')
kokyaku_data.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>顧客名</th>
      <th>かな</th>
      <th>地域</th>
      <th>メールアドレス</th>
      <th>登録日</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>須賀ひとみ</td>
      <td>すが ひとみ</td>
      <td>H市</td>
      <td>suga_hitomi@example.com</td>
      <td>2018/01/04</td>
    </tr>
    <tr>
      <th>1</th>
      <td>岡田　 敏也</td>
      <td>おかだ としや</td>
      <td>E市</td>
      <td>okada_toshiya@example.com</td>
      <td>42782</td>
    </tr>
    <tr>
      <th>2</th>
      <td>芳賀 希</td>
      <td>はが のぞみ</td>
      <td>A市</td>
      <td>haga_nozomi@example.com</td>
      <td>2018/01/07</td>
    </tr>
    <tr>
      <th>3</th>
      <td>荻野  愛</td>
      <td>おぎの あい</td>
      <td>F市</td>
      <td>ogino_ai@example.com</td>
      <td>42872</td>
    </tr>
    <tr>
      <th>4</th>
      <td>栗田 憲一</td>
      <td>くりた けんいち</td>
      <td>E市</td>
      <td>kurita_kenichi@example.com</td>
      <td>43127</td>
    </tr>
  </tbody>
</table>
</div>



# データの揺れを見てみる
- データの揺れ：データに混在する入力ミスや表記方法の違いなど、不整合を起こしている状態


```python
uriage_data['item_name'].head()
```




    0      商品A
    1    商 品 S
    2    商 品 a
    3      商品Z
    4      商品a
    Name: item_name, dtype: object




```python
uriage_data['item_price'].head()
```




    0     100.0
    1       NaN
    2       NaN
    3    2600.0
    4       NaN
    Name: item_price, dtype: float64




```python
uriage_data['purchase_date'] = pd.to_datetime(uriage_data['purchase_date'])
uriage_data['purchase_month'] = uriage_data['purchase_date'].dt.strftime('%Y%m')
res = uriage_data.pivot_table(index='purchase_month', columns='item_name', aggfunc='size', fill_value=0)

res

# データの揺れのため、列数が26→99になってしまっている
```




<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>item_name</th>
      <th>商品W</th>
      <th>商 品 n</th>
      <th>商品E</th>
      <th>商品M</th>
      <th>商品P</th>
      <th>商品S</th>
      <th>商品W</th>
      <th>商品X</th>
      <th>商  品O</th>
      <th>商  品Q</th>
      <th>...</th>
      <th>商品k</th>
      <th>商品l</th>
      <th>商品o</th>
      <th>商品p</th>
      <th>商品r</th>
      <th>商品s</th>
      <th>商品t</th>
      <th>商品v</th>
      <th>商品x</th>
      <th>商品y</th>
    </tr>
    <tr>
      <th>purchase_month</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>
      <th>201901</th>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>1</td>
      <td>1</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201902</th>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>1</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201903</th>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>1</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201904</th>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201905</th>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
    </tr>
    <tr>
      <th>201906</th>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201907</th>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
      <td>0</td>
      <td>2</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
  </tbody>
</table>
<p>7 rows × 99 columns</p>
</div>




```python
res = uriage_data.pivot_table(values='item_price', index='purchase_month', columns='item_name', aggfunc='sum', fill_value=0)

res

## 横軸をpriceだけにしたところで、列数は変わらず
```




<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>item_name</th>
      <th>商品W</th>
      <th>商 品 n</th>
      <th>商品E</th>
      <th>商品M</th>
      <th>商品P</th>
      <th>商品S</th>
      <th>商品W</th>
      <th>商品X</th>
      <th>商  品O</th>
      <th>商  品Q</th>
      <th>...</th>
      <th>商品k</th>
      <th>商品l</th>
      <th>商品o</th>
      <th>商品p</th>
      <th>商品r</th>
      <th>商品s</th>
      <th>商品t</th>
      <th>商品v</th>
      <th>商品x</th>
      <th>商品y</th>
    </tr>
    <tr>
      <th>purchase_month</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>
      <th>201901</th>
      <td>0</td>
      <td>1400</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>1100</td>
      <td>1200</td>
      <td>1500</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201902</th>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>2400</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1900</td>
      <td>2000</td>
      <td>2200</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201903</th>
      <td>0</td>
      <td>0</td>
      <td>500</td>
      <td>1300</td>
      <td>1600</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201904</th>
      <td>2300</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1700</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1900</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201905</th>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1900</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>1200</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>2500</td>
    </tr>
    <tr>
      <th>201906</th>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>2300</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>1600</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>2400</td>
      <td>0</td>
    </tr>
    <tr>
      <th>201907</th>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>...</td>
      <td>0</td>
      <td>0</td>
      <td>1500</td>
      <td>0</td>
      <td>1800</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
      <td>0</td>
    </tr>
  </tbody>
</table>
<p>7 rows × 99 columns</p>
</div>



# データの揺れを補正
## 商品名を補正


```python
# 重複しないデータの数を表示
print(len(pd.unique(uriage_data.item_name)))
```

    99



```python
# csvファイルの各列にアクセスする方法は、csvファイル名['列名'] or csvファイル名.列名
uriage_data.item_name
```




    0         商品A
    1       商 品 S
    2       商 品 a
    3         商品Z
    4         商品a
            ...  
    2994      商品Y
    2995      商品M
    2996      商品Q
    2997      商品H
    2998      商品D
    Name: item_name, Length: 2999, dtype: object




```python
uriage_data['item_name']
```




    0         商品A
    1       商 品 S
    2       商 品 a
    3         商品Z
    4         商品a
            ...  
    2994      商品Y
    2995      商品M
    2996      商品Q
    2997      商品H
    2998      商品D
    Name: item_name, Length: 2999, dtype: object




```python
uriage_data['item_name'] = uriage_data['item_name'].str.upper()#文字列を大文字に変更
uriage_data['item_name']
```




    0         商品A
    1       商 品 S
    2       商 品 A
    3         商品Z
    4         商品A
            ...  
    2994      商品Y
    2995      商品M
    2996      商品Q
    2997      商品H
    2998      商品D
    Name: item_name, Length: 2999, dtype: object




```python
uriage_data['item_name'] = uriage_data['item_name'].str.replace(' ', '') # 半角スペースを削除
uriage_data.item_name 
```




    0       商品A
    1       商品S
    2       商品A
    3       商品Z
    4       商品A
           ... 
    2994    商品Y
    2995    商品M
    2996    商品Q
    2997    商品H
    2998    商品D
    Name: item_name, Length: 2999, dtype: object




```python
uriage_data['item_name'] = uriage_data['item_name'].str.replace('　', '') # 全角スペースを削除
uriage_data.item_name
```




    0       商品A
    1       商品S
    2       商品A
    3       商品Z
    4       商品A
           ... 
    2994    商品Y
    2995    商品M
    2996    商品Q
    2997    商品H
    2998    商品D
    Name: item_name, Length: 2999, dtype: object




```python
uriage_data.sort_values(by='item_name', ascending=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>purchase_date</th>
      <th>item_name</th>
      <th>item_price</th>
      <th>customer_name</th>
      <th>purchase_month</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>2019-06-13 18:02:34</td>
      <td>商品A</td>
      <td>100.0</td>
      <td>深井菜々美</td>
      <td>201906</td>
    </tr>
    <tr>
      <th>1748</th>
      <td>2019-05-19 20:22:22</td>
      <td>商品A</td>
      <td>100.0</td>
      <td>松川綾女</td>
      <td>201905</td>
    </tr>
    <tr>
      <th>223</th>
      <td>2019-06-25 08:13:20</td>
      <td>商品A</td>
      <td>100.0</td>
      <td>板橋隆</td>
      <td>201906</td>
    </tr>
    <tr>
      <th>1742</th>
      <td>2019-06-13 16:03:17</td>
      <td>商品A</td>
      <td>100.0</td>
      <td>小平陽子</td>
      <td>201906</td>
    </tr>
    <tr>
      <th>1738</th>
      <td>2019-02-10 00:28:43</td>
      <td>商品A</td>
      <td>100.0</td>
      <td>松田浩正</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>...</th>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <th>2880</th>
      <td>2019-04-22 00:36:52</td>
      <td>商品Y</td>
      <td>NaN</td>
      <td>田辺光洋</td>
      <td>201904</td>
    </tr>
    <tr>
      <th>2881</th>
      <td>2019-04-30 14:21:09</td>
      <td>商品Y</td>
      <td>NaN</td>
      <td>高原充則</td>
      <td>201904</td>
    </tr>
    <tr>
      <th>1525</th>
      <td>2019-01-24 10:27:23</td>
      <td>商品Y</td>
      <td>2500.0</td>
      <td>五十嵐春樹</td>
      <td>201901</td>
    </tr>
    <tr>
      <th>1361</th>
      <td>2019-05-28 13:45:32</td>
      <td>商品Y</td>
      <td>2500.0</td>
      <td>大崎ヒカル</td>
      <td>201905</td>
    </tr>
    <tr>
      <th>3</th>
      <td>2019-02-12 23:40:45</td>
      <td>商品Z</td>
      <td>2600.0</td>
      <td>麻生莉緒</td>
      <td>201902</td>
    </tr>
  </tbody>
</table>
<p>2999 rows × 5 columns</p>
</div>



## 商品名の補正ができたか検証


```python
print(len(pd.unique(uriage_data.item_name)))
```

    26



```python
pd.unique(uriage_data.item_name)
```




    array(['商品A', '商品S', '商品Z', '商品V', '商品O', '商品U', '商品L', '商品C', '商品I',
           '商品R', '商品X', '商品G', '商品P', '商品Q', '商品Y', '商品N', '商品W', '商品E',
           '商品K', '商品B', '商品F', '商品D', '商品M', '商品H', '商品T', '商品J'],
          dtype=object)




```python

```


```python

```

# 金額欠損値を補完


```python
uriage_data.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>purchase_date</th>
      <th>item_name</th>
      <th>item_price</th>
      <th>customer_name</th>
      <th>purchase_month</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>2019-06-13 18:02:34</td>
      <td>商品A</td>
      <td>100.0</td>
      <td>深井菜々美</td>
      <td>201906</td>
    </tr>
    <tr>
      <th>1</th>
      <td>2019-07-13 13:05:29</td>
      <td>商品S</td>
      <td>NaN</td>
      <td>浅田賢二</td>
      <td>201907</td>
    </tr>
    <tr>
      <th>2</th>
      <td>2019-05-11 19:42:07</td>
      <td>商品A</td>
      <td>NaN</td>
      <td>南部慶二</td>
      <td>201905</td>
    </tr>
    <tr>
      <th>3</th>
      <td>2019-02-12 23:40:45</td>
      <td>商品Z</td>
      <td>2600.0</td>
      <td>麻生莉緒</td>
      <td>201902</td>
    </tr>
    <tr>
      <th>4</th>
      <td>2019-04-22 03:09:35</td>
      <td>商品A</td>
      <td>NaN</td>
      <td>平田鉄二</td>
      <td>201904</td>
    </tr>
  </tbody>
</table>
</div>




```python
uriage_data.isnull().any(axis=0)
```




    purchase_date     False
    item_name         False
    item_price         True
    customer_name     False
    purchase_month    False
    dtype: bool




```python
flg_is_null = uriage_data['item_price'].isnull() # 欠損値の場所を特定
flg_is_null
```




    0       False
    1        True
    2        True
    3       False
    4        True
            ...  
    2994    False
    2995    False
    2996     True
    2997     True
    2998    False
    Name: item_price, Length: 2999, dtype: bool




```python
for trg in list(uriage_data.loc[flg_is_null, 'item_name'].unique()):
    
    
```


      File "<ipython-input-63-ae47c7fde99c>", line 3
        
        ^
    SyntaxError: unexpected EOF while parsing




```python
uriage_data.loc[flg_is_null, 'item_name'].unique()
```




    array(['商品S', '商品A', '商品P', '商品N', '商品W', '商品R', '商品I', '商品L', '商品F',
           '商品O', '商品B', '商品C', '商品V', '商品Q', '商品U', '商品K', '商品T', '商品X',
           '商品E', '商品M', '商品G', '商品J', '商品D', '商品H', '商品Y'], dtype=object)




```python

```