```python
! pip install qeds
import pandas as pd
import numpy as np
import qeds
```

    Requirement already satisfied: qeds in c:\users\asus\anaconda3\lib\site-packages (0.6.2)
    Requirement already satisfied: openpyxl in c:\users\asus\anaconda3\lib\site-packages (from qeds) (3.0.0)
    Requirement already satisfied: quandl in c:\users\asus\anaconda3\lib\site-packages (from qeds) (3.5.0)
    Requirement already satisfied: statsmodels in c:\users\asus\anaconda3\lib\site-packages (from qeds) (0.10.1)
    Requirement already satisfied: pandas in c:\users\asus\anaconda3\lib\site-packages (from qeds) (0.25.1)
    Requirement already satisfied: scikit-learn in c:\users\asus\anaconda3\lib\site-packages (from qeds) (0.21.3)
    Requirement already satisfied: numpy in c:\users\asus\anaconda3\lib\site-packages (from qeds) (1.16.5)
    Requirement already satisfied: pandas-datareader in c:\users\asus\anaconda3\lib\site-packages (from qeds) (0.8.1)
    Requirement already satisfied: requests in c:\users\asus\anaconda3\lib\site-packages (from qeds) (2.22.0)
    Requirement already satisfied: matplotlib in c:\users\asus\anaconda3\lib\site-packages (from qeds) (3.1.1)
    Requirement already satisfied: plotly in c:\users\asus\anaconda3\lib\site-packages (from qeds) (4.5.4)
    Requirement already satisfied: pyarrow in c:\users\asus\anaconda3\lib\site-packages (from qeds) (0.16.0)
    Requirement already satisfied: seaborn in c:\users\asus\anaconda3\lib\site-packages (from qeds) (0.9.0)
    Requirement already satisfied: scipy in c:\users\asus\anaconda3\lib\site-packages (from qeds) (1.3.1)
    Requirement already satisfied: quantecon in c:\users\asus\anaconda3\lib\site-packages (from qeds) (0.4.6)
    Requirement already satisfied: jdcal in c:\users\asus\anaconda3\lib\site-packages (from openpyxl->qeds) (1.4.1)
    Requirement already satisfied: et-xmlfile in c:\users\asus\anaconda3\lib\site-packages (from openpyxl->qeds) (1.0.1)
    Requirement already satisfied: more-itertools in c:\users\asus\anaconda3\lib\site-packages (from quandl->qeds) (7.2.0)
    Requirement already satisfied: six in c:\users\asus\anaconda3\lib\site-packages (from quandl->qeds) (1.12.0)
    Requirement already satisfied: python-dateutil in c:\users\asus\anaconda3\lib\site-packages (from quandl->qeds) (2.8.0)
    Requirement already satisfied: inflection>=0.3.1 in c:\users\asus\anaconda3\lib\site-packages (from quandl->qeds) (0.3.1)
    Requirement already satisfied: patsy>=0.4.0 in c:\users\asus\anaconda3\lib\site-packages (from statsmodels->qeds) (0.5.1)
    Requirement already satisfied: pytz>=2017.2 in c:\users\asus\anaconda3\lib\site-packages (from pandas->qeds) (2019.3)
    Requirement already satisfied: joblib>=0.11 in c:\users\asus\anaconda3\lib\site-packages (from scikit-learn->qeds) (0.13.2)
    Requirement already satisfied: lxml in c:\users\asus\anaconda3\lib\site-packages (from pandas-datareader->qeds) (4.4.1)
    Requirement already satisfied: chardet<3.1.0,>=3.0.2 in c:\users\asus\anaconda3\lib\site-packages (from requests->qeds) (3.0.4)
    Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in c:\users\asus\anaconda3\lib\site-packages (from requests->qeds) (1.24.2)
    Requirement already satisfied: idna<2.9,>=2.5 in c:\users\asus\anaconda3\lib\site-packages (from requests->qeds) (2.8)
    Requirement already satisfied: certifi>=2017.4.17 in c:\users\asus\anaconda3\lib\site-packages (from requests->qeds) (2019.9.11)
    Requirement already satisfied: cycler>=0.10 in c:\users\asus\anaconda3\lib\site-packages (from matplotlib->qeds) (0.10.0)
    Requirement already satisfied: kiwisolver>=1.0.1 in c:\users\asus\anaconda3\lib\site-packages (from matplotlib->qeds) (1.1.0)
    Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in c:\users\asus\anaconda3\lib\site-packages (from matplotlib->qeds) (2.4.2)
    Requirement already satisfied: retrying>=1.3.3 in c:\users\asus\anaconda3\lib\site-packages (from plotly->qeds) (1.3.3)
    Requirement already satisfied: sympy in c:\users\asus\anaconda3\lib\site-packages (from quantecon->qeds) (1.4)
    Requirement already satisfied: numba>=0.38 in c:\users\asus\anaconda3\lib\site-packages (from quantecon->qeds) (0.45.1)
    Requirement already satisfied: setuptools in c:\users\asus\anaconda3\lib\site-packages (from kiwisolver>=1.0.1->matplotlib->qeds) (41.4.0)
    Requirement already satisfied: mpmath>=0.19 in c:\users\asus\anaconda3\lib\site-packages (from sympy->quantecon->qeds) (1.1.0)
    Requirement already satisfied: llvmlite>=0.29.0dev0 in c:\users\asus\anaconda3\lib\site-packages (from numba>=0.38->quantecon->qeds) (0.29.0)


# Cleaning Data - Exercises

## Exercise 1

**Convert the string below into a number.**


```python
c2n = "#39"

new_c2n = c2n.replace("#", "")

print(float(new_c2n))
```

    39.0


## Exercises 2-3

**For these exercises, we create the following DataFrame:**


```python
df = pd.DataFrame({"numbers": ["#23", "#24", "#18", "#14", "#12", "#10", "#35"],
                   "nums": ["23", "24", "18", "14", np.nan, "XYZ", "35"],
                   "colors": ["green", "red", "yellow", "orange", "purple", "blue", "pink"],
                   "other_column": [0, 1, 0, 2, 1, 0, 2]})
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>numbers</th>
      <th>nums</th>
      <th>colors</th>
      <th>other_column</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>#23</td>
      <td>23</td>
      <td>green</td>
      <td>0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>#24</td>
      <td>24</td>
      <td>red</td>
      <td>1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>#18</td>
      <td>18</td>
      <td>yellow</td>
      <td>0</td>
    </tr>
    <tr>
      <td>3</td>
      <td>#14</td>
      <td>14</td>
      <td>orange</td>
      <td>2</td>
    </tr>
    <tr>
      <td>4</td>
      <td>#12</td>
      <td>NaN</td>
      <td>purple</td>
      <td>1</td>
    </tr>
  </tbody>
</table>
</div>



## Exercise 2

   **Make a new column called `colors_upper` that contains the elements of `colors` with all uppercase letters.**


```python
df['colors_upper']=df['colors'].str.upper()
df.head()

#if we want only the first letter as capital letter 
#df['colors_upper'] = df["colors"].str.capitalize()
```




<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>numbers</th>
      <th>nums</th>
      <th>colors</th>
      <th>other_column</th>
      <th>colors_upper</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>#23</td>
      <td>23</td>
      <td>green</td>
      <td>0</td>
      <td>GREEN</td>
    </tr>
    <tr>
      <td>1</td>
      <td>#24</td>
      <td>24</td>
      <td>red</td>
      <td>1</td>
      <td>RED</td>
    </tr>
    <tr>
      <td>2</td>
      <td>#18</td>
      <td>18</td>
      <td>yellow</td>
      <td>0</td>
      <td>YELLOW</td>
    </tr>
    <tr>
      <td>3</td>
      <td>#14</td>
      <td>14</td>
      <td>orange</td>
      <td>2</td>
      <td>ORANGE</td>
    </tr>
    <tr>
      <td>4</td>
      <td>#12</td>
      <td>NaN</td>
      <td>purple</td>
      <td>1</td>
      <td>PURPLE</td>
    </tr>
  </tbody>
</table>
</div>



## Exercise 3

1. **Convert the column `"nums"` to a numeric type using `pd.to_numeric` and save it to the DataFrame as `"nums_tonumeric"`.**

    - Notice that there is a missing value, and a value that is not a number. Look at the documentation for `pd.to_numeric` and think about how to overcome this.


```python
df["nums_tonumeric"] = pd.to_numeric(df["nums"], errors='coerce')
df
# errors "coerce" means that we force the conversion of string to NaN
```




<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>numbers</th>
      <th>nums</th>
      <th>colors</th>
      <th>other_column</th>
      <th>colors_upper</th>
      <th>nums_tonumeric</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>#23</td>
      <td>23</td>
      <td>green</td>
      <td>0</td>
      <td>GREEN</td>
      <td>23.0</td>
    </tr>
    <tr>
      <td>1</td>
      <td>#24</td>
      <td>24</td>
      <td>red</td>
      <td>1</td>
      <td>RED</td>
      <td>24.0</td>
    </tr>
    <tr>
      <td>2</td>
      <td>#18</td>
      <td>18</td>
      <td>yellow</td>
      <td>0</td>
      <td>YELLOW</td>
      <td>18.0</td>
    </tr>
    <tr>
      <td>3</td>
      <td>#14</td>
      <td>14</td>
      <td>orange</td>
      <td>2</td>
      <td>ORANGE</td>
      <td>14.0</td>
    </tr>
    <tr>
      <td>4</td>
      <td>#12</td>
      <td>NaN</td>
      <td>purple</td>
      <td>1</td>
      <td>PURPLE</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>5</td>
      <td>#10</td>
      <td>XYZ</td>
      <td>blue</td>
      <td>0</td>
      <td>BLUE</td>
      <td>NaN</td>
    </tr>
    <tr>
      <td>6</td>
      <td>#35</td>
      <td>35</td>
      <td>pink</td>
      <td>2</td>
      <td>PINK</td>
      <td>35.0</td>
    </tr>
  </tbody>
</table>
</div>



2. **Think about why this could be a bad idea of used without knowing what your data looks like. (Think about what happens when you apply it to the `"numbers"` column before replacing the `"#"`.)**


```python
#that would set all values to NaN
```

## Exercise 4

**For this exercise, we use data from an article written by The Upshot at the NYTimes, which has order information from almost 2,000 Chipotle orders and includes information on what was ordered and how much it cost.**


```python
chipotle = qeds.data.load("chipotle_raw")
chipotle.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>quantity</th>
      <th>item_name</th>
      <th>choice_description</th>
      <th>item_price</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>1</td>
      <td>Chips and Fresh Tomato Salsa</td>
      <td>NaN</td>
      <td>$2.39</td>
    </tr>
    <tr>
      <td>1</td>
      <td>1</td>
      <td>1</td>
      <td>Izze</td>
      <td>[Clementine]</td>
      <td>$3.39</td>
    </tr>
    <tr>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Nantucket Nectar</td>
      <td>[Apple]</td>
      <td>$3.39</td>
    </tr>
    <tr>
      <td>3</td>
      <td>1</td>
      <td>1</td>
      <td>Chips and Tomatillo-Green Chili Salsa</td>
      <td>NaN</td>
      <td>$2.39</td>
    </tr>
    <tr>
      <td>4</td>
      <td>2</td>
      <td>2</td>
      <td>Chicken Bowl</td>
      <td>[Tomatillo-Red Chili Salsa (Hot), [Black Beans...</td>
      <td>$16.98</td>
    </tr>
  </tbody>
</table>
</div>



1. **Make sure the `item_price` column has a numeric `dtype` (probably float).**


```python
chipotle["item_price_numeric"] = pd.to_numeric(chipotle["item_price"].str.replace("$", ""))
chipotle.dtypes
chipotle.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>quantity</th>
      <th>item_name</th>
      <th>choice_description</th>
      <th>item_price</th>
      <th>item_price_numeric</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>1</td>
      <td>1</td>
      <td>Chips and Fresh Tomato Salsa</td>
      <td>NaN</td>
      <td>$2.39</td>
      <td>2.39</td>
    </tr>
    <tr>
      <td>1</td>
      <td>1</td>
      <td>1</td>
      <td>Izze</td>
      <td>[Clementine]</td>
      <td>$3.39</td>
      <td>3.39</td>
    </tr>
    <tr>
      <td>2</td>
      <td>1</td>
      <td>1</td>
      <td>Nantucket Nectar</td>
      <td>[Apple]</td>
      <td>$3.39</td>
      <td>3.39</td>
    </tr>
    <tr>
      <td>3</td>
      <td>1</td>
      <td>1</td>
      <td>Chips and Tomatillo-Green Chili Salsa</td>
      <td>NaN</td>
      <td>$2.39</td>
      <td>2.39</td>
    </tr>
    <tr>
      <td>4</td>
      <td>2</td>
      <td>2</td>
      <td>Chicken Bowl</td>
      <td>[Tomatillo-Red Chili Salsa (Hot), [Black Beans...</td>
      <td>$16.98</td>
      <td>16.98</td>
    </tr>
  </tbody>
</table>
</div>



2. **What is the average price of an item with chicken?**


```python
chipotle.loc[chipotle['item_name'].str.match('Chicken'),"item_price_numeric"].mean()
```




    10.133724358974309



3. **What is the average price of an item with steak?**   


```python
chipotle.loc[chipotle['item_name'].str.match('Steak'),"item_price_numeric"].mean()
```




    10.518888888888851



4. **Did chicken or steak produce more revenue (total)?**   


```python
chipotle.loc[chipotle['item_name'].str.match('Chicken'),"item_price_numeric"].sum()>chipotle.loc[chipotle['item_name'].str.match('Steak'),"item_price_numeric"].sum()

#we create a boolean which confirms that chicken produces more total revenue compared to steak
```




    True




```python
#Alternative solution:
chipotle['revenue']=chipotle['item_price_numeric']*chipotle['quantity']

steak= chipotle[chipotle['item_name'].str.contains(pat='Steak')].sum()
print('Product containing steak generated a revenue of', steak.loc['revenue'], 'dollars')
steak= chipotle[chipotle['item_name'].str.contains(pat='Chicken')].sum()
print('Product containing chicken generated a revenue of', steak.loc['revenue'], 'dollars')
```

    Product containing steak generated a revenue of 8072.619999999973 dollars
    Product containing chicken generated a revenue of 17742.14999999992 dollars


5. **How many missing items are there in this dataset? How many missing items in each column?**


```python
#in the dataset
chipotle.isnull().sum().sum()
```




    1246




```python
#in each column
chipotle.isnull().sum()
```




    order_id                 0
    quantity                 0
    item_name                0
    choice_description    1246
    item_price               0
    item_price_numeric       0
    revenue                  0
    dtype: int64



ipynb
746e4e2a7426208cb7c91f3ad8b89082980ba0d3
Jupyter Notebook
71497927ed4d54ddf6fd5abe2ddabb5966eb0304
Apache-2.0
remi-sudo/Classes
Session_7/5_data_clean_exercises.ipynb
71497927ed4d54ddf6fd5abe2ddabb5966eb0304
Apache-2.0
remi-sudo/Classes
Session_7/5_data_clean_exercises.ipynb
71497927ed4d54ddf6fd5abe2ddabb5966eb0304
Apache-2.0
remi-sudo/Classes
Session_7/5_data_clean_exercises.ipynb