【pandas】Pythonで営業データ分析をやってみた【初心者向け】


海外のyoutuberのKeith Galliという方のPythonのレクチャー動画を見てワークをやってみました。

【PythonのPandasライブラリを用いて現実のデータサイエンス課題を解いてみよう！】
[Solving real world data science tasks with Python Pandas!](https://youtu.be/eMOA1pPVUc4)
難易度は高くなく、平易な英語なので是非ご覧ください。


1時間半ほどの動画です。非常に丁寧な説明です。
Googleでstackoverflow(海外版プログラミングQ&Aサイト)で、
解決法を調べる過程なども見せてくれています。

CSVのエクセルデータを用いて分析するタスクになります。
データは彼のGithubからダウンロード出来ます。

[Githubはこちらから]("https://github.com/KeithGalli/Pandas-Data-Science-Tasks")

私はjupyter notebookで実行しています。

コードに補足を日本語で入れながらやっています。

動画をみて、実際に触ってみたらPandasの練習に最適かと思います。

この記事に目を通すだけでもだいたい雰囲気はつかめると思います。


## データの読み込みからデータフレーム作成

pandas,osライブラリを読み込む


```python
import pandas as pd
import os
```

まずどんなデータが入っているかを確認します(ファイル名一覧取得)


```python
files = [file for file in os.listdir("Sales_Data")]
for file in files:
    print(file)
```

    Sales_April_2019.csv
    Sales_August_2019.csv
    Sales_December_2019.csv
    Sales_February_2019.csv
    Sales_January_2019.csv
    Sales_July_2019.csv
    Sales_June_2019.csv
    Sales_March_2019.csv
    Sales_May_2019.csv
    Sales_November_2019.csv
    Sales_October_2019.csv
    Sales_September_2019.csv


ファイル名から2019年1-12月までのデータが入っているようです、分析するためにデータをひとつにまとめたいです 

まず、すべてのデータを格納するための空のデータフレームを作成します


```python
all_months_data = pd.DataFrame()
```

すべての月データを合体させるために、それぞれの月のcsvファイルを空データフレームに次々にいれていきます



```python
for file in files:
    df = pd.read_csv("Sales_Data/" + file)
    all_months_data = pd.concat([all_months_data,df])
```

さて、データの最初の5行はこんな感じです


```python
all_months_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
    </tr>
    <tr>
      <td>1</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
    </tr>
  </tbody>
</table>
</div>



合体したデータを出力します (このとき通し番号はいらないので、引数にindex=Falseを加えます)


```python
all_months_data.to_csv("all_data.csv",index=False)
```

出力したデータを確認出来たら、合体したデータを改めて読み込んでみます


```python
all_data = pd.read_csv("all_data.csv")
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
    </tr>
    <tr>
      <td>1</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
    </tr>
  </tbody>
</table>
</div>



## ワーク１：一番売り上げがあった月はいつで、どれぐらい稼いだでしょうか？

カラムをみてみると、Order Date＝注文日の列はありますが、月だけのデータはないですので、新しく作りましょう

注文日の列の最初の2文字が月に当たるデータになりそうです


```python
all_data['Month'] = all_data['Order Date'].str[0:2]
```

文字列化したので、もう一度数値に戻しておきましょう


```python
all_data['Month'] = all_data['Month'].astype('int32')
all_data.head()
```


    ---------------------------------------------------------------------------

    ValueError                                Traceback (most recent call last)

    <ipython-input-77-ffc394ccb2ad> in <module>
    ----> 1 all_data['Month'] = all_data['Month'].astype('int32')
          2 all_data.head()


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in astype(self, dtype, copy, errors, **kwargs)
       5880             # else, only a single dtype is given
       5881             new_data = self._data.astype(
    -> 5882                 dtype=dtype, copy=copy, errors=errors, **kwargs
       5883             )
       5884             return self._constructor(new_data).__finalize__(self)


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/managers.py in astype(self, dtype, **kwargs)
        579 
        580     def astype(self, dtype, **kwargs):
    --> 581         return self.apply("astype", dtype=dtype, **kwargs)
        582 
        583     def convert(self, **kwargs):


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/managers.py in apply(self, f, axes, filter, do_integrity_check, consolidate, **kwargs)
        436                     kwargs[k] = obj.reindex(b_items, axis=axis, copy=align_copy)
        437 
    --> 438             applied = getattr(b, f)(**kwargs)
        439             result_blocks = _extend_blocks(applied, result_blocks)
        440 


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py in astype(self, dtype, copy, errors, values, **kwargs)
        557 
        558     def astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):
    --> 559         return self._astype(dtype, copy=copy, errors=errors, values=values, **kwargs)
        560 
        561     def _astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py in _astype(self, dtype, copy, errors, values, **kwargs)
        641                     # _astype_nansafe works fine with 1-d only
        642                     vals1d = values.ravel()
    --> 643                     values = astype_nansafe(vals1d, dtype, copy=True, **kwargs)
        644 
        645                 # TODO(extension)


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/dtypes/cast.py in astype_nansafe(arr, dtype, copy, skipna)
        705         # work around NumPy brokenness, #1987
        706         if np.issubdtype(dtype.type, np.integer):
    --> 707             return lib.astype_intsafe(arr.ravel(), dtype).reshape(arr.shape)
        708 
        709         # if we have a datetime/timedelta array of objects


    pandas/_libs/lib.pyx in pandas._libs.lib.astype_intsafe()


    ValueError: cannot convert float NaN to integer


エラーが出てしまいました

エラー文を読むと、NaNを数値化することは出来ないよと言っています

is_na 関数とany関数を組み合わせると、１つでもNaNがあればTrueと返してくれます

axis = 1は行ごとに処理を返しくれる関数です

NaNを含む行をすべて返してもらいます


```python
nan_df = all_data[all_data.isna().any(axis=1)]
nan_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>356</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>735</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>1433</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>1553</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <td>185176</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>185438</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>186042</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>186548</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>186826</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>
<p>545 rows × 7 columns</p>
</div>



545行もありました

NaNを含むデータを消しましょう


```python
all_data = all_data.dropna(how='all')
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>04</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>04</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>04</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>04</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/30/19 09:27</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>04</td>
    </tr>
  </tbody>
</table>
</div>



よしでは、先ほどの月を数値に戻すもう一度実行してみましょう


```python
all_data['Month'] = all_data['Month'].astype('int32')
all_data.head()
```


    ---------------------------------------------------------------------------

    ValueError                                Traceback (most recent call last)

    <ipython-input-80-ffc394ccb2ad> in <module>
    ----> 1 all_data['Month'] = all_data['Month'].astype('int32')
          2 all_data.head()


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in astype(self, dtype, copy, errors, **kwargs)
       5880             # else, only a single dtype is given
       5881             new_data = self._data.astype(
    -> 5882                 dtype=dtype, copy=copy, errors=errors, **kwargs
       5883             )
       5884             return self._constructor(new_data).__finalize__(self)


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/managers.py in astype(self, dtype, **kwargs)
        579 
        580     def astype(self, dtype, **kwargs):
    --> 581         return self.apply("astype", dtype=dtype, **kwargs)
        582 
        583     def convert(self, **kwargs):


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/managers.py in apply(self, f, axes, filter, do_integrity_check, consolidate, **kwargs)
        436                     kwargs[k] = obj.reindex(b_items, axis=axis, copy=align_copy)
        437 
    --> 438             applied = getattr(b, f)(**kwargs)
        439             result_blocks = _extend_blocks(applied, result_blocks)
        440 


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py in astype(self, dtype, copy, errors, values, **kwargs)
        557 
        558     def astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):
    --> 559         return self._astype(dtype, copy=copy, errors=errors, values=values, **kwargs)
        560 
        561     def _astype(self, dtype, copy=False, errors="raise", values=None, **kwargs):


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py in _astype(self, dtype, copy, errors, values, **kwargs)
        641                     # _astype_nansafe works fine with 1-d only
        642                     vals1d = values.ravel()
    --> 643                     values = astype_nansafe(vals1d, dtype, copy=True, **kwargs)
        644 
        645                 # TODO(extension)


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/dtypes/cast.py in astype_nansafe(arr, dtype, copy, skipna)
        705         # work around NumPy brokenness, #1987
        706         if np.issubdtype(dtype.type, np.integer):
    --> 707             return lib.astype_intsafe(arr.ravel(), dtype).reshape(arr.shape)
        708 
        709         # if we have a datetime/timedelta array of objects


    pandas/_libs/lib.pyx in pandas._libs.lib.astype_intsafe()


    ValueError: invalid literal for int() with base 10: 'Or'


次は、また新しいエラーが出ています

'Or'という文言が含まれていたっぽいですね

'Or'が含まれている行を見てみましょう


```python
temp_df = all_data[all_data['Order Date'].str[0:2] == "Or"]
temp_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>519</td>
      <td>Order ID</td>
      <td>Product</td>
      <td>Quantity Ordered</td>
      <td>Price Each</td>
      <td>Order Date</td>
      <td>Purchase Address</td>
      <td>Or</td>
    </tr>
    <tr>
      <td>1149</td>
      <td>Order ID</td>
      <td>Product</td>
      <td>Quantity Ordered</td>
      <td>Price Each</td>
      <td>Order Date</td>
      <td>Purchase Address</td>
      <td>Or</td>
    </tr>
    <tr>
      <td>1155</td>
      <td>Order ID</td>
      <td>Product</td>
      <td>Quantity Ordered</td>
      <td>Price Each</td>
      <td>Order Date</td>
      <td>Purchase Address</td>
      <td>Or</td>
    </tr>
    <tr>
      <td>2878</td>
      <td>Order ID</td>
      <td>Product</td>
      <td>Quantity Ordered</td>
      <td>Price Each</td>
      <td>Order Date</td>
      <td>Purchase Address</td>
      <td>Or</td>
    </tr>
    <tr>
      <td>2893</td>
      <td>Order ID</td>
      <td>Product</td>
      <td>Quantity Ordered</td>
      <td>Price Each</td>
      <td>Order Date</td>
      <td>Purchase Address</td>
      <td>Or</td>
    </tr>
  </tbody>
</table>
</div>



"Or"の正体は"Order Date"だったみたいですね。

"Order Date"という文言が含まれたデータ以外を抜き出してデータをリフレッシュしましょう


```python
all_data = all_data[all_data['Order Date'].str[0:2] != "Or"]
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>04</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>04</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>04</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>04</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/30/19 09:27</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>04</td>
    </tr>
  </tbody>
</table>
</div>



では3度目の正直で、数値化を実行してみましょう


```python
all_data['Month'] = all_data['Order Date'].str[0:2]
all_data['Month'] = all_data['Month'].astype('int32')
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>4</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>4</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/30/19 09:27</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>4</td>
    </tr>
  </tbody>
</table>
</div>



いけました

次に、Quantity Ordered と　Price Each　はあるものの肝心の売り上げがありません

PQ(売り上げ) = P(価格) × Q(数量) なので PQに当たる列を追加します


```python
all_data['Sales'] = all_data['Quantity Ordered'] * all_data['Price Each'] 
```


    ---------------------------------------------------------------------------

    TypeError                                 Traceback (most recent call last)

    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/ops/__init__.py in na_op(x, y)
        967         try:
    --> 968             result = expressions.evaluate(op, str_rep, x, y, **eval_kwargs)
        969         except TypeError:


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/computation/expressions.py in evaluate(op, op_str, a, b, use_numexpr, **eval_kwargs)
        220     if use_numexpr:
    --> 221         return _evaluate(op, op_str, a, b, **eval_kwargs)
        222     return _evaluate_standard(op, op_str, a, b)


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/computation/expressions.py in _evaluate_numexpr(op, op_str, a, b, truediv, reversed, **eval_kwargs)
        126     if result is None:
    --> 127         result = _evaluate_standard(op, op_str, a, b)
        128 


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/computation/expressions.py in _evaluate_standard(op, op_str, a, b, **eval_kwargs)
         69     with np.errstate(all="ignore"):
    ---> 70         return op(a, b)
         71 


    TypeError: can't multiply sequence by non-int of type 'str'

    
    During handling of the above exception, another exception occurred:


    TypeError                                 Traceback (most recent call last)

    <ipython-input-84-7c1e2b69cbe2> in <module>
    ----> 1 all_data['Sales'] = all_data['Quantity Ordered'] * all_data['Price Each']
    

    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/ops/__init__.py in wrapper(left, right)
       1046 
       1047         with np.errstate(all="ignore"):
    -> 1048             result = na_op(lvalues, rvalues)
       1049         return construct_result(
       1050             left, result, index=left.index, name=res_name, dtype=None


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/ops/__init__.py in na_op(x, y)
        968             result = expressions.evaluate(op, str_rep, x, y, **eval_kwargs)
        969         except TypeError:
    --> 970             result = masked_arith_op(x, y, op)
        971 
        972         return missing.dispatch_fill_zeros(op, x, y, result)


    /opt/anaconda3/lib/python3.7/site-packages/pandas/core/ops/__init__.py in masked_arith_op(x, y, op)
        445         if mask.any():
        446             with np.errstate(all="ignore"):
    --> 447                 result[mask] = op(xrav[mask], com.values_from_object(yrav[mask]))
        448 
        449     else:


    TypeError: can't multiply sequence by non-int of type 'str'


またしても、文字列が入っていました

とりあえず計算が出来る形に書き換えてあげたいので、"to_numeric"関数を使います


```python
all_data['Quantity Ordered'] = pd.to_numeric(all_data['Quantity Ordered'])
all_data['Price Each'] = pd.to_numeric(all_data['Price Each'])
```

もう一回やってみます


```python
all_data['Sales'] = all_data['Quantity Ordered'] * all_data['Price Each'] 
```


```python
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
      <th>Sales</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>4</td>
      <td>23.90</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/30/19 09:27</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
    </tr>
  </tbody>
</table>
</div>



出ましたね。やっと問いに対する答えが出そうです

一応、問いを再掲しておきます。

## ワーク１：一番売り上げがあった月はいつで、どれぐらい稼いだでしょうか？


```python
all_data.groupby('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>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Sales</th>
    </tr>
    <tr>
      <th>Month</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1</td>
      <td>10903</td>
      <td>1.811768e+06</td>
      <td>1.822257e+06</td>
    </tr>
    <tr>
      <td>2</td>
      <td>13449</td>
      <td>2.188885e+06</td>
      <td>2.202022e+06</td>
    </tr>
    <tr>
      <td>3</td>
      <td>17005</td>
      <td>2.791208e+06</td>
      <td>2.807100e+06</td>
    </tr>
    <tr>
      <td>4</td>
      <td>20558</td>
      <td>3.367671e+06</td>
      <td>3.390670e+06</td>
    </tr>
    <tr>
      <td>5</td>
      <td>18667</td>
      <td>3.135125e+06</td>
      <td>3.152607e+06</td>
    </tr>
    <tr>
      <td>6</td>
      <td>15253</td>
      <td>2.562026e+06</td>
      <td>2.577802e+06</td>
    </tr>
    <tr>
      <td>7</td>
      <td>16072</td>
      <td>2.632540e+06</td>
      <td>2.647776e+06</td>
    </tr>
    <tr>
      <td>8</td>
      <td>13448</td>
      <td>2.230345e+06</td>
      <td>2.244468e+06</td>
    </tr>
    <tr>
      <td>9</td>
      <td>13109</td>
      <td>2.084992e+06</td>
      <td>2.097560e+06</td>
    </tr>
    <tr>
      <td>10</td>
      <td>22703</td>
      <td>3.715555e+06</td>
      <td>3.736727e+06</td>
    </tr>
    <tr>
      <td>11</td>
      <td>19798</td>
      <td>3.180601e+06</td>
      <td>3.199603e+06</td>
    </tr>
    <tr>
      <td>12</td>
      <td>28114</td>
      <td>4.588415e+06</td>
      <td>4.613443e+06</td>
    </tr>
  </tbody>
</table>
</div>



パット見分からないので図示しましょう

図示する用のライブラリをインストールします


```python
import matplotlib.pyplot as plt
```

X軸用の範囲も決めながらグラフ化します


```python
months = range(1,13)
results = all_data.groupby('Month').sum()
plt.bar(months,results['Sales'])
plt.xticks(months)
plt.xlabel('Month')
plt.ylabel('Sales')
plt.show()
```



![output_60_0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266200/07ceb1ab-fd42-7bad-6f04-5371c964db87.png)


以上がワーク1でした。

## ワーク２：どの都市が一番の売り上げだったか

売り上げの列を作った今、簡単そうなワークですが、やってみましょう

その前に、改めて、データがどんなものだったかを確認します


```python
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
      <th>Sales</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>4</td>
      <td>23.90</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/30/19 09:27</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
    </tr>
  </tbody>
</table>
</div>



Purchase Addressの部分のデータの一部を抜き取って、集計する感じでしょうかね

ではSalesの時と同様に、City列を作りましょう

Addressの一部を抜き出さないといけないですが、Adressのデータをみると、"番地,都市名,郵便番号"という感じで、２個目の都市名だけを取り出したいです。

split関数で","で分けること、とそれを同じ列のすべての列で適用するためにapply関数を使いましょう


```python
all_data['City'] = all_data['Purchase Address'].apply(lambda x: x.split(',')[1])
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
      <th>Sales</th>
      <th>City</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>4</td>
      <td>23.90</td>
      <td>Dallas</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
      <td>Boston</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
      <td>Los Angeles</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/30/19 09:27</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles</td>
    </tr>
  </tbody>
</table>
</div>



Cityだけうまくわけられましたね！

さて、Cityで集計する際に、ここでひとつ考えないといけない問題があります。

それは、Cityといっても、同じ名前のCityだけど他の国にも同じ名前があるケースです。これを考慮しないと、合算されてしまい意図しない集計結果が出てしまいます。(例：オレゴン州とメイン州の両方にあるポートランド)

とはいえ、国名として新しく列を作る必要はありません。集計するだけなので、Cityの列のセルにState(2文字)も収めるだけでいいです。今回は、City(State)という形にします。 ex: Dallas(TX)

さきほど、lambda関数で一行で書いてしまいましたが、せっかくなので、都市名、国名をそれぞれ取得する関数を作りましょう。読み手にも親切です。


```python
def get_city(address):
    return address.split(',')[1]

def get_state(address):
    return address.split(',')[2].split(' ')[1]
# Purchaseアドレスのデータはコンマ(,)のあと空白(" ")があるので、合体したときに不自然にならないようにsplitで削る

all_data['City'] = all_data['Purchase Address'].apply(lambda x: f"{get_city(x)} ({get_state(x)})")
all_data.drop("State",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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
      <th>Sales</th>
      <th>City</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>4</td>
      <td>23.90</td>
      <td>Dallas (TX)</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
      <td>Boston (MA)</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
      <td>Los Angeles (CA)</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/30/19 09:27</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
    </tr>
    <tr>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
      <td>...</td>
    </tr>
    <tr>
      <td>186845</td>
      <td>259353</td>
      <td>AAA Batteries (4-pack)</td>
      <td>3</td>
      <td>2.99</td>
      <td>09/17/19 20:56</td>
      <td>840 Highland St, Los Angeles, CA 90001</td>
      <td>9</td>
      <td>8.97</td>
      <td>Los Angeles (CA)</td>
    </tr>
    <tr>
      <td>186846</td>
      <td>259354</td>
      <td>iPhone</td>
      <td>1</td>
      <td>700.00</td>
      <td>09/01/19 16:00</td>
      <td>216 Dogwood St, San Francisco, CA 94016</td>
      <td>9</td>
      <td>700.00</td>
      <td>San Francisco (CA)</td>
    </tr>
    <tr>
      <td>186847</td>
      <td>259355</td>
      <td>iPhone</td>
      <td>1</td>
      <td>700.00</td>
      <td>09/23/19 07:39</td>
      <td>220 12th St, San Francisco, CA 94016</td>
      <td>9</td>
      <td>700.00</td>
      <td>San Francisco (CA)</td>
    </tr>
    <tr>
      <td>186848</td>
      <td>259356</td>
      <td>34in Ultrawide Monitor</td>
      <td>1</td>
      <td>379.99</td>
      <td>09/19/19 17:30</td>
      <td>511 Forest St, San Francisco, CA 94016</td>
      <td>9</td>
      <td>379.99</td>
      <td>San Francisco (CA)</td>
    </tr>
    <tr>
      <td>186849</td>
      <td>259357</td>
      <td>USB-C Charging Cable</td>
      <td>1</td>
      <td>11.95</td>
      <td>09/30/19 00:18</td>
      <td>250 Meadow St, San Francisco, CA 94016</td>
      <td>9</td>
      <td>11.95</td>
      <td>San Francisco (CA)</td>
    </tr>
  </tbody>
</table>
<p>185950 rows × 9 columns</p>
</div>



OKです、あとは普通に集計すればいいだけですね


```python
all_data.groupby('City').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>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Month</th>
      <th>Sales</th>
    </tr>
    <tr>
      <th>City</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Atlanta (GA)</td>
      <td>16602</td>
      <td>2.779908e+06</td>
      <td>104794</td>
      <td>2.795499e+06</td>
    </tr>
    <tr>
      <td>Austin (TX)</td>
      <td>11153</td>
      <td>1.809874e+06</td>
      <td>69829</td>
      <td>1.819582e+06</td>
    </tr>
    <tr>
      <td>Boston (MA)</td>
      <td>22528</td>
      <td>3.637410e+06</td>
      <td>141112</td>
      <td>3.661642e+06</td>
    </tr>
    <tr>
      <td>Dallas (TX)</td>
      <td>16730</td>
      <td>2.752628e+06</td>
      <td>104620</td>
      <td>2.767975e+06</td>
    </tr>
    <tr>
      <td>Los Angeles (CA)</td>
      <td>33289</td>
      <td>5.421435e+06</td>
      <td>208325</td>
      <td>5.452571e+06</td>
    </tr>
    <tr>
      <td>New York City (NY)</td>
      <td>27932</td>
      <td>4.635371e+06</td>
      <td>175741</td>
      <td>4.664317e+06</td>
    </tr>
    <tr>
      <td>Portland (ME)</td>
      <td>2750</td>
      <td>4.471893e+05</td>
      <td>17144</td>
      <td>4.497583e+05</td>
    </tr>
    <tr>
      <td>Portland (OR)</td>
      <td>11303</td>
      <td>1.860558e+06</td>
      <td>70621</td>
      <td>1.870732e+06</td>
    </tr>
    <tr>
      <td>San Francisco (CA)</td>
      <td>50239</td>
      <td>8.211462e+06</td>
      <td>315520</td>
      <td>8.262204e+06</td>
    </tr>
    <tr>
      <td>Seattle (WA)</td>
      <td>16553</td>
      <td>2.733296e+06</td>
      <td>104941</td>
      <td>2.747755e+06</td>
    </tr>
  </tbody>
</table>
</div>



San Franciscoが一位です。あと、Portlandもちゃんとわけられていることがわかります。

さっきのmonthで使ったコードをアレンジして


```python
results = all_data.groupby('City').sum()
cities = [city for city, df in all_data.groupby('City')]
# x軸ラベル用のcity：上記でgroubbyしたときと同じ並び順にしないと数字とラベルがバラバラになる
plt.bar(cities,results['Sales'])
plt.xticks(cities,rotation="vertical")
# citiesをそのまま表示したらかさばってしまうので垂直表示に
plt.ylabel('Sales')
plt.xlabel('City name')
plt.show()
```


![output_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266200/0a29b3c1-60b1-9180-8204-830f27bcf59f.png)



できました！

## ワーク３：顧客が商品を買う見込みを最大化するためにいつ・どの時間に広告を打つ(ディスプレイする)べきでしょうか？

一気にワークがデータサイエンスっぽくなりましたね (ただ、結論を先に言うとそこまで厳密なロジックではないです)

さて、いつも通り、データを振り返ってみましょう


```python
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
      <th>Sales</th>
      <th>City</th>
      <th>State</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>04/19/19 08:46</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>4</td>
      <td>23.90</td>
      <td>Dallas (TX)</td>
      <td>TX 75001</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>04/07/19 22:30</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
      <td>Boston (MA)</td>
      <td>MA 02215</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/12/19 14:38</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>04/30/19 09:27</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
    </tr>
  </tbody>
</table>
</div>



おそらく、Order Dataの列とSales列の関連性をみるとよさそうです

Order Dateを日付データとして扱いやすいようにデータの型を変えてみましょう


```python
all_data['Order Date'] = pd.to_datetime(all_data['Order Date'])
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
      <th>Sales</th>
      <th>City</th>
      <th>State</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>2019-04-19 08:46:00</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>4</td>
      <td>23.90</td>
      <td>Dallas (TX)</td>
      <td>TX 75001</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>2019-04-07 22:30:00</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
      <td>Boston (MA)</td>
      <td>MA 02215</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>2019-04-12 14:38:00</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>2019-04-12 14:38:00</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>2019-04-30 09:27:00</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
    </tr>
  </tbody>
</table>
</div>



データの形式が変わりましたね

Hour,Minuteの列を作りましょう

MonthやSalesで列を作るときは文字列だったので、Splitなどを使っていましたが、先ほどデータの型を変えたおかげで


```python
all_data['Hour'] = all_data['Order Date'].dt.hour
all_data['Minute'] = all_data['Order Date'].dt.minute
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
      <th>Sales</th>
      <th>City</th>
      <th>State</th>
      <th>Hour</th>
      <th>Minute</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>2019-04-19 08:46:00</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>4</td>
      <td>23.90</td>
      <td>Dallas (TX)</td>
      <td>TX 75001</td>
      <td>8</td>
      <td>46</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>2019-04-07 22:30:00</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
      <td>Boston (MA)</td>
      <td>MA 02215</td>
      <td>22</td>
      <td>30</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>2019-04-12 14:38:00</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>14</td>
      <td>38</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>2019-04-12 14:38:00</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>14</td>
      <td>38</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>2019-04-30 09:27:00</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>9</td>
      <td>27</td>
    </tr>
  </tbody>
</table>
</div>



一旦データを把握するために可視化しましょう


```python
hours = [hour for hour, df in all_data.groupby('Hour')]
plt.plot(hours,all_data.groupby(['Hour']).count())
#時間をX軸に、時間ごとにOrder数(行数)を集計する
plt.xticks(hours)
plt.grid()
#出来るだけ時間ごとの明確な傾向が見たいのでGridをつける
plt.xlabel('Hour')
plt.ylabel('Orders')
plt.show()
```


![output_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266200/93938257-497e-9b07-596d-62d8229152d9.png)



ピークは、AM:11とPM:7ぐらいですね。したがって、最もオーダー数(客数)が多いこの時間帯に広告をディスプレイするのが良さそうです。

## ワーク4: どの商品が最も一緒にセットで買われているでしょうか？


```python
all_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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
      <th>Sales</th>
      <th>City</th>
      <th>State</th>
      <th>Hour</th>
      <th>Minute</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>176558</td>
      <td>USB-C Charging Cable</td>
      <td>2</td>
      <td>11.95</td>
      <td>2019-04-19 08:46:00</td>
      <td>917 1st St, Dallas, TX 75001</td>
      <td>4</td>
      <td>23.90</td>
      <td>Dallas (TX)</td>
      <td>TX 75001</td>
      <td>8</td>
      <td>46</td>
    </tr>
    <tr>
      <td>2</td>
      <td>176559</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>2019-04-07 22:30:00</td>
      <td>682 Chestnut St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
      <td>Boston (MA)</td>
      <td>MA 02215</td>
      <td>22</td>
      <td>30</td>
    </tr>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>2019-04-12 14:38:00</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>14</td>
      <td>38</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>2019-04-12 14:38:00</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>14</td>
      <td>38</td>
    </tr>
    <tr>
      <td>5</td>
      <td>176561</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>2019-04-30 09:27:00</td>
      <td>333 8th St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>9</td>
      <td>27</td>
    </tr>
  </tbody>
</table>
</div>



OrderIDが一緒のものでグループ分けすればわかりそうですね

かぶっている値は、duplicated関数を使うと楽にソート出来ます。


```python
df = all_data[all_data['Order ID'].duplicated(keep=False)]
df.head(20)
```




<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>Order ID</th>
      <th>Product</th>
      <th>Quantity Ordered</th>
      <th>Price Each</th>
      <th>Order Date</th>
      <th>Purchase Address</th>
      <th>Month</th>
      <th>Sales</th>
      <th>City</th>
      <th>State</th>
      <th>Hour</th>
      <th>Minute</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>2019-04-12 14:38:00</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>14</td>
      <td>38</td>
    </tr>
    <tr>
      <td>4</td>
      <td>176560</td>
      <td>Wired Headphones</td>
      <td>1</td>
      <td>11.99</td>
      <td>2019-04-12 14:38:00</td>
      <td>669 Spruce St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.99</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>14</td>
      <td>38</td>
    </tr>
    <tr>
      <td>18</td>
      <td>176574</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>2019-04-03 19:42:00</td>
      <td>20 Hill St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>600.00</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>19</td>
      <td>42</td>
    </tr>
    <tr>
      <td>19</td>
      <td>176574</td>
      <td>USB-C Charging Cable</td>
      <td>1</td>
      <td>11.95</td>
      <td>2019-04-03 19:42:00</td>
      <td>20 Hill St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.95</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>19</td>
      <td>42</td>
    </tr>
    <tr>
      <td>30</td>
      <td>176585</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>2019-04-07 11:31:00</td>
      <td>823 Highland St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
      <td>Boston (MA)</td>
      <td>MA 02215</td>
      <td>11</td>
      <td>31</td>
    </tr>
    <tr>
      <td>31</td>
      <td>176585</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>2019-04-07 11:31:00</td>
      <td>823 Highland St, Boston, MA 02215</td>
      <td>4</td>
      <td>99.99</td>
      <td>Boston (MA)</td>
      <td>MA 02215</td>
      <td>11</td>
      <td>31</td>
    </tr>
    <tr>
      <td>32</td>
      <td>176586</td>
      <td>AAA Batteries (4-pack)</td>
      <td>2</td>
      <td>2.99</td>
      <td>2019-04-10 17:00:00</td>
      <td>365 Center St, San Francisco, CA 94016</td>
      <td>4</td>
      <td>5.98</td>
      <td>San Francisco (CA)</td>
      <td>CA 94016</td>
      <td>17</td>
      <td>0</td>
    </tr>
    <tr>
      <td>33</td>
      <td>176586</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>2019-04-10 17:00:00</td>
      <td>365 Center St, San Francisco, CA 94016</td>
      <td>4</td>
      <td>600.00</td>
      <td>San Francisco (CA)</td>
      <td>CA 94016</td>
      <td>17</td>
      <td>0</td>
    </tr>
    <tr>
      <td>119</td>
      <td>176672</td>
      <td>Lightning Charging Cable</td>
      <td>1</td>
      <td>14.95</td>
      <td>2019-04-12 11:07:00</td>
      <td>778 Maple St, New York City, NY 10001</td>
      <td>4</td>
      <td>14.95</td>
      <td>New York City (NY)</td>
      <td>NY 10001</td>
      <td>11</td>
      <td>7</td>
    </tr>
    <tr>
      <td>120</td>
      <td>176672</td>
      <td>USB-C Charging Cable</td>
      <td>1</td>
      <td>11.95</td>
      <td>2019-04-12 11:07:00</td>
      <td>778 Maple St, New York City, NY 10001</td>
      <td>4</td>
      <td>11.95</td>
      <td>New York City (NY)</td>
      <td>NY 10001</td>
      <td>11</td>
      <td>7</td>
    </tr>
    <tr>
      <td>129</td>
      <td>176681</td>
      <td>Apple Airpods Headphones</td>
      <td>1</td>
      <td>150.00</td>
      <td>2019-04-20 10:39:00</td>
      <td>331 Cherry St, Seattle, WA 98101</td>
      <td>4</td>
      <td>150.00</td>
      <td>Seattle (WA)</td>
      <td>WA 98101</td>
      <td>10</td>
      <td>39</td>
    </tr>
    <tr>
      <td>130</td>
      <td>176681</td>
      <td>ThinkPad Laptop</td>
      <td>1</td>
      <td>999.99</td>
      <td>2019-04-20 10:39:00</td>
      <td>331 Cherry St, Seattle, WA 98101</td>
      <td>4</td>
      <td>999.99</td>
      <td>Seattle (WA)</td>
      <td>WA 98101</td>
      <td>10</td>
      <td>39</td>
    </tr>
    <tr>
      <td>138</td>
      <td>176689</td>
      <td>Bose SoundSport Headphones</td>
      <td>1</td>
      <td>99.99</td>
      <td>2019-04-24 17:15:00</td>
      <td>659 Lincoln St, New York City, NY 10001</td>
      <td>4</td>
      <td>99.99</td>
      <td>New York City (NY)</td>
      <td>NY 10001</td>
      <td>17</td>
      <td>15</td>
    </tr>
    <tr>
      <td>139</td>
      <td>176689</td>
      <td>AAA Batteries (4-pack)</td>
      <td>2</td>
      <td>2.99</td>
      <td>2019-04-24 17:15:00</td>
      <td>659 Lincoln St, New York City, NY 10001</td>
      <td>4</td>
      <td>5.98</td>
      <td>New York City (NY)</td>
      <td>NY 10001</td>
      <td>17</td>
      <td>15</td>
    </tr>
    <tr>
      <td>189</td>
      <td>176739</td>
      <td>34in Ultrawide Monitor</td>
      <td>1</td>
      <td>379.99</td>
      <td>2019-04-05 17:38:00</td>
      <td>730 6th St, Austin, TX 73301</td>
      <td>4</td>
      <td>379.99</td>
      <td>Austin (TX)</td>
      <td>TX 73301</td>
      <td>17</td>
      <td>38</td>
    </tr>
    <tr>
      <td>190</td>
      <td>176739</td>
      <td>Google Phone</td>
      <td>1</td>
      <td>600.00</td>
      <td>2019-04-05 17:38:00</td>
      <td>730 6th St, Austin, TX 73301</td>
      <td>4</td>
      <td>600.00</td>
      <td>Austin (TX)</td>
      <td>TX 73301</td>
      <td>17</td>
      <td>38</td>
    </tr>
    <tr>
      <td>225</td>
      <td>176774</td>
      <td>Lightning Charging Cable</td>
      <td>1</td>
      <td>14.95</td>
      <td>2019-04-25 15:06:00</td>
      <td>372 Church St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>14.95</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>15</td>
      <td>6</td>
    </tr>
    <tr>
      <td>226</td>
      <td>176774</td>
      <td>USB-C Charging Cable</td>
      <td>1</td>
      <td>11.95</td>
      <td>2019-04-25 15:06:00</td>
      <td>372 Church St, Los Angeles, CA 90001</td>
      <td>4</td>
      <td>11.95</td>
      <td>Los Angeles (CA)</td>
      <td>CA 90001</td>
      <td>15</td>
      <td>6</td>
    </tr>
    <tr>
      <td>233</td>
      <td>176781</td>
      <td>iPhone</td>
      <td>1</td>
      <td>700.00</td>
      <td>2019-04-03 07:37:00</td>
      <td>976 Hickory St, Dallas, TX 75001</td>
      <td>4</td>
      <td>700.00</td>
      <td>Dallas (TX)</td>
      <td>TX 75001</td>
      <td>7</td>
      <td>37</td>
    </tr>
    <tr>
      <td>234</td>
      <td>176781</td>
      <td>Lightning Charging Cable</td>
      <td>1</td>
      <td>14.95</td>
      <td>2019-04-03 07:37:00</td>
      <td>976 Hickory St, Dallas, TX 75001</td>
      <td>4</td>
      <td>14.95</td>
      <td>Dallas (TX)</td>
      <td>TX 75001</td>
      <td>7</td>
      <td>37</td>
    </tr>
  </tbody>
</table>
</div>



同じIDのものが連続で並ぶデータになりました

上のデータセットは、IDがかぶっているオーダー(≒セット買いしたオーダー以外なし)です

IDでグループ分けしたうえで、そのIDで購入したオーダーが格納された列を作ります。(例 ID:1111 → 注文: Apple,Banana,Orange)


```python
df = all_data[all_data['Order ID'].duplicated(keep=False)]
df['Grouped'] = df.groupby('Order ID')['Product'].transform(lambda x: ','.join(x))
#transformはapply関数のようなものです。一旦ここでは説明省略します。
df = df[['Order ID','Grouped']].drop_duplicates()
df.head(10)
```

    /opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
    A value is trying to be set on a copy of a slice from a DataFrame.
    Try using .loc[row_indexer,col_indexer] = value instead
    
    See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
      





<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>Order ID</th>
      <th>Grouped</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>3</td>
      <td>176560</td>
      <td>Google Phone,Wired Headphones</td>
    </tr>
    <tr>
      <td>18</td>
      <td>176574</td>
      <td>Google Phone,USB-C Charging Cable</td>
    </tr>
    <tr>
      <td>30</td>
      <td>176585</td>
      <td>Bose SoundSport Headphones,Bose SoundSport Hea...</td>
    </tr>
    <tr>
      <td>32</td>
      <td>176586</td>
      <td>AAA Batteries (4-pack),Google Phone</td>
    </tr>
    <tr>
      <td>119</td>
      <td>176672</td>
      <td>Lightning Charging Cable,USB-C Charging Cable</td>
    </tr>
    <tr>
      <td>129</td>
      <td>176681</td>
      <td>Apple Airpods Headphones,ThinkPad Laptop</td>
    </tr>
    <tr>
      <td>138</td>
      <td>176689</td>
      <td>Bose SoundSport Headphones,AAA Batteries (4-pack)</td>
    </tr>
    <tr>
      <td>189</td>
      <td>176739</td>
      <td>34in Ultrawide Monitor,Google Phone</td>
    </tr>
    <tr>
      <td>225</td>
      <td>176774</td>
      <td>Lightning Charging Cable,USB-C Charging Cable</td>
    </tr>
    <tr>
      <td>233</td>
      <td>176781</td>
      <td>iPhone,Lightning Charging Cable</td>
    </tr>
  </tbody>
</table>
</div>



良い感じです。データがうまい具合にまとまってます。

このGrouped列で"どれが、どれと、どんな組み合わせで、何回ずつ"などを調べなければいけません

便利なライブラリを使いながら集計しましょう。ライブラリの具体的なイメージは以下のstackoverflowをご覧ください。

※inputとoutputのコードを眺めてだいたいこういうことか程度でOKです
https://stackoverflow.com/questions/56187042/python-itertools-make-combinations-with-sum/56187946#56187946


```python
from itertools import combinations
from collections import Counter

count = Counter()
for row in df['Grouped']:
    row_list = row.split(',')
    count.update(Counter(combinations(row_list,2)))
    
count.most_common(10)
```




    [(('iPhone', 'Lightning Charging Cable'), 1005),
     (('Google Phone', 'USB-C Charging Cable'), 987),
     (('iPhone', 'Wired Headphones'), 447),
     (('Google Phone', 'Wired Headphones'), 414),
     (('Vareebadd Phone', 'USB-C Charging Cable'), 361),
     (('iPhone', 'Apple Airpods Headphones'), 360),
     (('Google Phone', 'Bose SoundSport Headphones'), 220),
     (('USB-C Charging Cable', 'Wired Headphones'), 160),
     (('Vareebadd Phone', 'Wired Headphones'), 143),
     (('Lightning Charging Cable', 'Wired Headphones'), 92)]



ちょっと出力が汚いので、微修正します


```python
for key,value in count.most_common(10):
    print(key,value)
```

    ('iPhone', 'Lightning Charging Cable') 1005
    ('Google Phone', 'USB-C Charging Cable') 987
    ('iPhone', 'Wired Headphones') 447
    ('Google Phone', 'Wired Headphones') 414
    ('Vareebadd Phone', 'USB-C Charging Cable') 361
    ('iPhone', 'Apple Airpods Headphones') 360
    ('Google Phone', 'Bose SoundSport Headphones') 220
    ('USB-C Charging Cable', 'Wired Headphones') 160
    ('Vareebadd Phone', 'Wired Headphones') 143
    ('Lightning Charging Cable', 'Wired Headphones') 92


iPhoneとチャージケーブルが一番多いですね

## ワーク５(Final Work)：どの商品が一番売れているでしょうか。なぜそれが一番売れていると思いますか？

最後のワークです。自分で考える(仮説の部分)が入ってきましたね。
データを可視化しましょう。

数量ベースで考えます


```python
product_group = all_data.groupby('Product')
quantity_ordered = product_group.sum()['Quantity Ordered']
products = [product for product,df in product_group]
plt.bar(products,quantity_ordered)
plt.xlabel('Product')
plt.ylabel('Quantity Ordered')
plt.xticks(products,rotation="vertical",size=8)
plt.show()
```

![output_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266200/4826919d-22a1-a0de-bb4e-918f067b27f0.png)



バッテリー・ケーブルとかが多いですかね。

仮に値段が安いからと推測します。値段と売り上げを調べます。


```python
prices = all_data.groupby('Product').mean()['Price Each']

fig, ax1 = plt.subplots()

ax2 = ax1.twinx()
# x軸共通でy軸の値が異なるグラフ生成
ax1.bar(products,quantity_ordered)
ax2.plot(products,prices,'b-')

ax1.set_xlabel('Product Name')
ax1.set_ylabel('Quanity Ordered', color="g")
ax2.set_ylabel('Price', color="b")
ax1.set_xticklabels(products,rotation="vertical",size=8)

plt.show()

```


![output_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/266200/3870c40f-4299-370e-b534-c57e454a487e.png)


以上で終了です。