2018-08-25_cleaning_data_with_python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Notebook Author: [Trenton McKinney][1]\n",
    "- Course: **[DataCamp: Cleaning Data in Python][2]**\n",
    " - This [notebook][3] was created as a reproducible reference.\n",
    " - The material is from the course\n",
    " - I completed the exercises\n",
    " - If you find the content beneficial, consider a [DataCamp Subscription][4].\n",
    " \n",
    "\n",
    "  [1]: https://trenton3983.github.io/\n",
    "  [2]: https://learn.datacamp.com/courses/cleaning-data-in-python\n",
    "  [3]: https://github.com/trenton3983/DataCamp/blob/master/2018-08-25_cleaning_data_with_python.ipynb\n",
    "  [4]: https://www.datacamp.com/join-me/MjQwNzMyMg=="
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cleaning Data in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Your Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "from numpy import NaN\n",
    "from glob import glob\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('max_columns', 200)\n",
    "pd.set_option('max_rows', 300)\n",
    "pd.set_option('display.expand_frame_repr', True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading and viewing your data\n",
    "\n",
    "In this chapter, you're going to look at a subset of the Department of Buildings Job Application Filings dataset from the NYC Open Data portal. This dataset consists of job applications filed on January 22, 2017.\n",
    "\n",
    "Your first task is to load this dataset into a DataFrame and then inspect it using the .head() and .tail() methods. However, you'll find out very quickly that the printed results don't allow you to see everything you need, since there are too many columns. Therefore, you need to look at the data in another way.\n",
    "\n",
    "The .shape and .columns attributes let you see the shape of the DataFrame and obtain a list of its columns. From here, you can see which columns are relevant to the questions you'd like to ask of the data. To this end, a new DataFrame, df_subset, consisting only of these relevant columns, has been pre-loaded. This is the DataFrame you'll work with in the rest of the chapter.\n",
    "\n",
    "Get acquainted with the dataset now by exploring it with pandas! This initial exploratory analysis is a crucial first step of data cleaning."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data can be downloaded from here:\n",
    "https://data.cityofnewyork.us/Housing-Development/DOB-Job-Application-Filings/ic3t-wcy2/data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the file into a DataFrame: df\n",
    "df = pd.read_csv('data/DOB_Job_Application_Filings.csv')\n",
    "\n",
    "# Print the head of df\n",
    "print(df.head())\n",
    "\n",
    "# Print the tail of df\n",
    "print(df.tail())\n",
    "\n",
    "# Print the shape of df\n",
    "print(df.shape)\n",
    "\n",
    "# Print the columns of df\n",
    "print(df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create Subset\n",
    "df_subset = df.loc[(df['Pre- Filing Date'] == '01/22/2017')].copy()\n",
    "\n",
    "# Print the head and tail of df_subset\n",
    "print(df_subset.head())\n",
    "print(df_subset.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Further diagnosis\n",
    "\n",
    "In the previous exercise, you identified some potentially unclean or missing data. Now, you'll continue to diagnose your data with the very useful .info() method.\n",
    "\n",
    "The .info() method provides important information about a DataFrame, such as the number of rows, number of columns, number of non-missing values in each column, and the data type stored in each column. This is the kind of information that will allow you to confirm whether the 'Initial Cost' and 'Total Est. Fee' columns are numeric or strings. From the results, you'll also be able to see whether or not all columns have complete data in them.\n",
    "\n",
    "The full DataFrame df and the subset DataFrame df_subset have been pre-loaded. Your task is to use the .info() method on these and analyze the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(df))\n",
    "print(len(df_subset))\n",
    "\n",
    "# Print the info of df\n",
    "print(df.info())\n",
    "\n",
    "# Print the info of df_subset\n",
    "print(df_subset.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Excellent! Notice that the columns 'Initial Cost' and 'Total Est. Fee' are of type object. The currency sign in the beginning of each value in these columns needs to be removed, and the columns need to be converted to numeric. In the full DataFrame, note that there are a lot of missing values. You saw in the previous exercise that there are also a lot of 0 values. Given the amount of data that is missing in the full dataset, it's highly likely that these 0 values represent missing data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculating summary statistics\n",
    "\n",
    "You'll now use the .describe() method to calculate summary statistics of your data.\n",
    "\n",
    "In this exercise, the columns 'Initial Cost' and 'Total Est. Fee' have been cleaned up for you. That is, the dollar sign has been removed and they have been converted into two new numeric columns: initial_cost and total_est_fee. You'll learn how to do this yourself in later chapters. It's also worth noting that some columns such as Job # are encoded as numeric columns, but it does not make sense to compute summary statistics for such columns.\n",
    "\n",
    "This cleaned DataFrame has been pre-loaded as df. Your job is to use the .describe() method on it in the IPython Shell and select the statement below that is False."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_subset['Initial Cost'] = df_subset['Initial Cost'].str.replace(',', '')\n",
    "df_subset['Total Est. Fee'] = df_subset['Total Est. Fee'].str.replace(',', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_subset['Initial Cost'] = df_subset['Initial Cost'].str.replace('$', '').astype('float')\n",
    "df_subset['Total Est. Fee'] = df_subset['Total Est. Fee'].str.replace('$', '').astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_subset['Initial Cost']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_subset['Total Est. Fee']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_subset.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Frequency counts for categorical data\n",
    "\n",
    "As you've seen, .describe() can only be used on numeric columns. So how can you diagnose data issues when you have categorical data? One way is by using the .value_counts() method, which returns the frequency counts for each unique value in a column!\n",
    "\n",
    "This method also has an optional parameter called dropna which is True by default. What this means is if you have missing data in a column, it will not give a frequency count of them. You want to set the dropna column to False so if there are missing values in a column, it will give you the frequency counts.\n",
    "\n",
    "In this exercise, you're going to look at the 'Borough', 'State', and 'Site Fill' columns to make sure all the values in there are valid. When looking at the output, do a sanity check: Are all values in the 'State' column from NY, for example? Since the dataset consists of applications filed in NY, you would expect this to be the case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the value counts for 'Borough'\n",
    "print(df['Borough'].value_counts(dropna=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the value_counts for 'State'\n",
    "print(df['State'].value_counts(dropna=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the value counts for 'Site Fill'\n",
    "print(df['Site Fill'].value_counts(dropna=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fantastic work! Notice how not all values in the 'State' column are NY. This is an interesting find, as this data is supposed to consist of applications filed in NYC. Curiously, all the 'Borough' values are correct. A good start as to why this may be the case would be to find and look at the codebook for this dataset. Also, for the 'Site Fill' column, you may or may not need to recode the NOT APPLICABLE values to NaN in your final analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Visualizing single variables with histograms\n",
    "\n",
    "Up until now, you've been looking at descriptive statistics of your data. One of the best ways to confirm what the numbers are telling you is to plot and visualize the data.\n",
    "\n",
    "You'll start by visualizing single variables using a histogram for numeric values. The column you will work on in this exercise is 'Existing Zoning Sqft'.\n",
    "\n",
    "The .plot() method allows you to create a plot of each column of a DataFrame. The kind parameter allows you to specify the type of plot to use - kind='hist', for example, plots a histogram.\n",
    "\n",
    "In the IPython Shell, begin by computing summary statistics for the 'Existing Zoning Sqft' column using the .describe() method. You'll notice that there are extremely large differences between the min and max values, and the plot will need to be adjusted accordingly. In such cases, it's good to look at the plot on a log scale. The keyword arguments logx=True or logy=True can be passed in to .plot() depending on which axis you want to rescale.\n",
    "\n",
    "Finally, note that Python will render a plot such that the axis will hold all the information. That is, if you end up with large amounts of whitespace in your plot, it indicates counts or values too small to render."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot the histogram\n",
    "df['Existing Zoning Sqft'].plot(kind='hist', rot=70, logx=True, logy=True, figsize=(10, 8), edgecolor='black')\n",
    "\n",
    "# Display the histogram\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Excellent work! While visualizing your data is a great way to understand it, keep in mind that no one technique is better than another. As you saw here, you still needed to look at the summary statistics to help understand your data better. You expected a large amount of counts on the left side of the plot because the 25th, 50th, and 75th percentiles have a value of 0. The plot shows us that there are barely any counts near the max value, signifying an outlier."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Visualizing multiple variables with boxplots\n",
    "\n",
    "Histograms are great ways of visualizing single variables. To visualize multiple variables, boxplots are useful, especially when one of the variables is categorical.\n",
    "\n",
    "In this exercise, your job is to use a boxplot to compare the 'initial_cost' across the different values of the 'Borough' column. The pandas .boxplot() method is a quick way to do this, in which you have to specify the column and by parameters. Here, you want to visualize how 'initial_cost' varies by 'Borough'.\n",
    "\n",
    "pandas and matplotlib.pyplot have been imported for you as pd and plt, respectively, and the DataFrame has been pre-loaded as df."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['initial_cost'] = df['Initial Cost'].str.replace(',', '')\n",
    "df['total_est_fee'] = df['Total Est. Fee'].str.replace(',', '')\n",
    "df['initial_cost'] = df['Initial Cost'].str.replace('$', '').astype('float')\n",
    "df['total_est_fee'] = df['Total Est. Fee'].str.replace('$', '').astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the boxplot\n",
    "df.boxplot(column='initial_cost', by='Borough', rot=90, figsize=(10, 8))\n",
    "\n",
    "# Display the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great work! You can see the 2 extreme outliers are in the borough of Manhattan. An initial guess could be that since land in Manhattan is extremely expensive, these outliers may be valid data points. Again, further investigation is needed to determine whether or not you can drop or keep those points in your data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Visualizing multiple variables with scatter plots\n",
    "\n",
    "Boxplots are great when you have a numeric column that you want to compare across different categories. When you want to visualize two numeric columns, scatter plots are ideal.\n",
    "\n",
    "In this exercise, your job is to make a scatter plot with 'initial_cost' on the x-axis and the 'total_est_fee' on the y-axis. You can do this by using the DataFrame .plot() method with kind='scatter'. You'll notice right away that there are 2 major outliers shown in the plots.\n",
    "\n",
    "Since these outliers dominate the plot, an additional DataFrame, df_subset, has been provided, in which some of the extreme values have been removed. After making a scatter plot using this, you'll find some interesting patterns here that would not have been seen by looking at summary statistics or 1 variable plots.\n",
    "\n",
    "When you're done, you can cycle between the two plots by clicking the 'Previous Plot' and 'Next Plot' buttons below the plot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create and display the first scatter plot\n",
    "df.plot(kind='scatter', x='initial_cost', y='total_est_fee', rot=70, figsize=(10, 8))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Excellent work! In general, from the second plot it seems like there is a strong correlation between 'initial_cost' and 'total_est_fee'. In addition, take note of the large number of points that have an 'initial_cost' of 0. It is difficult to infer any trends from the first plot because it is dominated by the outliers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tidying Data for Analysis\n",
    "### Tidy data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* **\"Tidy Data\"** paper by Hadley Wickham, PhD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Principles of tidy data\n",
    "\n",
    "1. Columns represent separate variables\n",
    "2. Rows represent individual observations\n",
    "3. Observational units form tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Converting to tidy data\n",
    "\n",
    "index | name | treatment a | treatment b\n",
    "--- | --- | --- | ---\n",
    "0 | Daniel | - | 42\n",
    "1 | John | 12 | 31\n",
    "2 | Jane | 24 | 27\n",
    "\n",
    "\n",
    "index | name | treatment | value\n",
    "--- | --- | --- | ---\n",
    "0 | Daniel | treatment a | -\n",
    "1 | John | treatment a | 12\n",
    "2 | Jane | treatment a | 24\n",
    "3 | Daniel | treatment b | 42\n",
    "4 | John | treatment b | 31\n",
    "5 | Jane | treatment b | 27\n",
    "\n",
    "\n",
    "* Better for reporting vs better for analysis\n",
    "* Tidy data makes it easier to fix common data problems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Recognizing tidy data\n",
    "\n",
    "For data to be tidy, it must have:\n",
    "\n",
    "Each variable as a separate column.\n",
    "Each row as a separate observation.\n",
    "As a data scientist, you'll encounter data that is represented in a variety of different ways, so it is important to be able to recognize tidy (or untidy) data when you see it.\n",
    "\n",
    "In this exercise, two example datasets have been pre-loaded into the DataFrames df1 and df2. Only one of them is tidy. Your job is to explore these further in the IPython Shell and identify the one that is not tidy, and why it is not tidy.\n",
    "\n",
    "In the rest of this course, you will frequently be asked to explore the structure of DataFrames in the IPython Shell prior to performing different operations on them. Doing this will not only strengthen your comprehension of the data cleaning concepts covered in this course, but will also help you realize and take advantage of the relationship between working in the Shell and in the script."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_csv('data/df1_recognizing_tidy_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_csv('data/df2_recognizing_tidy_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['value'] = df2['value'].replace('#NUM!', 'NaN')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exactly! Notice that the variable column of df2 contains the values Solar.R, Ozone, Temp, and Wind. For it to be tidy, these should all be in separate columns, as in df1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reshaping your data using melt\n",
    "\n",
    "Melting data is the process of turning columns of your data into rows of data. Consider the DataFrames from the previous exercise. In the tidy DataFrame, the variables Ozone, Solar.R, Wind, and Temp each had their own column. If, however, you wanted these variables to be in rows instead, you could melt the DataFrame. In doing so, however, you would make the data untidy! This is important to keep in mind: Depending on how your data is represented, you will have to reshape it differently.\n",
    "\n",
    "In this exercise, you will practice melting a DataFrame using pd.melt(). There are two parameters you should be aware of: id_vars and value_vars. The id_vars represent the columns of the data you do not want to melt (i.e., keep it in its current shape), while the value_vars represent the columns you do wish to melt into rows. By default, if no value_vars are provided, all columns not set in the id_vars will be melted. This could save a bit of typing, depending on the number of columns that need to be melted.\n",
    "\n",
    "The (tidy) DataFrame airquality has been pre-loaded. Your job is to melt its Ozone, Solar.R, Wind, and Temp columns into rows. Later in this chapter, you'll learn how to bring this melted DataFrame back into a tidy form."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Melt airquality (df1): airquality_melt\n",
    "airquality_melt = pd.melt(df1, id_vars=['Month', 'Day'])\n",
    "\n",
    "# Print the head of airquality_melt\n",
    "print(airquality_melt.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well done! This exercise demonstrates that melting a DataFrame is not always appropriate if you want to make it tidy. You may have to perform other transformations depending on how your data is represented."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Customizing melted data\n",
    "\n",
    "When melting DataFrames, it would be better to have column names more meaningful than variable and value.\n",
    "\n",
    "The default names may work in certain situations, but it's best to always have data that is self explanatory.\n",
    "\n",
    "You can rename the variable column by specifying an argument to the var_name parameter, and the value column by specifying an argument to the value_name parameter. You will now practice doing exactly this. The DataFrame airquality has been pre-loaded for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Melt airquality (df1): airquality_melt\n",
    "airquality_melt = pd.melt(df1, id_vars=['Month', 'Day'], var_name='measurement', value_name='reading')\n",
    "\n",
    "# Print the head of airquality_melt\n",
    "print(airquality_melt.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great work! The DataFrame is more informative now. In the next video, you'll learn about pivoting, which is the opposite of melting. You'll then be able to convert this DataFrame back into its original, tidy, form!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivoting data\n",
    "#### Pivot: un-melting data\n",
    "\n",
    "* Opposite of melting\n",
    "* In melting, we turned columns into rows\n",
    "* Pivoting: turn unique values into separate columns\n",
    "* Analysis friendly shape to reporting friendly shape\n",
    "* Violates tidy data principle: rows contain observations\n",
    "    * Multiple variables stored in the same column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot data\n",
    "\n",
    "Pivoting data is the opposite of melting it. Remember the tidy form that the airquality DataFrame was in before you melted it? You'll now begin pivoting it back into that form using the .pivot_table() method!\n",
    "\n",
    "While melting takes a set of columns and turns it into a single column, pivoting will create a new column for each unique value in a specified column.\n",
    "\n",
    ".pivot_table() has an index parameter which you can use to specify the columns that you don't want pivoted: It is similar to the id_vars parameter of pd.melt(). Two other parameters that you have to specify are columns (the name of the column you want to pivot), and values (the values to be used when the column is pivoted). The melted DataFrame airquality_melt has been pre-loaded for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot airquality_melt: airquality_pivot\n",
    "airquality_pivot = airquality_melt.pivot_table(index=['Month', 'Day'], columns='measurement', values='reading')\n",
    "\n",
    "# Print the head of airquality_pivot\n",
    "print(airquality_pivot.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Excellent work! Notice that the pivoted DataFrame does not actually look like the original DataFrame. In the next exercise, you'll turn this pivoted DataFrame back into its original form."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Resetting the index of a DataFrame\n",
    "\n",
    "After pivoting airquality_melt in the previous exercise, you didn't quite get back the original DataFrame.\n",
    "\n",
    "What you got back instead was a pandas DataFrame with a [hierarchical index (also known as a MultiIndex)](http://pandas.pydata.org/pandas-docs/stable/advanced.html).\n",
    "\n",
    "Hierarchical indexes are covered in depth in [Manipulating DataFrames with pandas](https://www.datacamp.com/courses/manipulating-dataframes-with-pandas). In essence, they allow you to group columns or rows by another variable - in this case, by 'Month' as well as 'Day'.\n",
    "\n",
    "There's a very simple method you can use to get back the original DataFrame from the pivoted DataFrame: .reset_index(). Dan didn't show you how to use this method in the video, but you're now going to practice using it in this exercise to get back the original DataFrame from airquality_pivot, which has been pre-loaded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the index of airquality_pivot\n",
    "print(airquality_pivot.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reset the index of airquality_pivot: airquality_pivot_reset\n",
    "airquality_pivot_reset = airquality_pivot.reset_index()\n",
    "\n",
    "# Print the new index of airquality_pivot_reset\n",
    "print(airquality_pivot_reset.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the head of airquality_pivot_reset\n",
    "print(airquality_pivot_reset.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivoting duplicate values\n",
    "\n",
    "So far, you've used the .pivot_table() method when there are multiple index values you want to hold constant during a pivot. In the video, Dan showed you how you can also use pivot tables to deal with duplicate values by providing an aggregation function through the aggfunc parameter. Here, you're going to combine both these uses of pivot tables.\n",
    "\n",
    "Let's say your data collection method accidentally duplicated your dataset. Such a dataset, in which each row is duplicated, has been pre-loaded as airquality_dup. In addition, the airquality_melt DataFrame from the previous exercise has been pre-loaded. Explore their shapes in the IPython Shell by accessing their .shape attributes to confirm the duplicate rows present in airquality_dup.\n",
    "\n",
    "You'll see that by using .pivot_table() and the aggfunc parameter, you can not only reshape your data, but also remove duplicates. Finally, you can then flatten the columns of the pivoted DataFrame using .reset_index().\n",
    "\n",
    "NumPy and pandas have been imported as np and pd respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "airquality_dup = pd.read_csv('data/airquality_dup.csv')\n",
    "airquality_dup.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivot airquality_dup: airquality_pivot\n",
    "airquality_pivot = airquality_dup.pivot_table(index=['Month', 'Day'], columns='measurement', values='reading', aggfunc=np.mean)\n",
    "airquality_pivot = airquality_pivot.reset_index()\n",
    "print(airquality_pivot.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df1.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Beyond melt and pivot\n",
    "### Splitting a column with .str\n",
    "\n",
    "The dataset you saw in the video, consisting of case counts of tuberculosis by country, year, gender, and age group, has been pre-loaded into a DataFrame as tb.\n",
    "\n",
    "In this exercise, you're going to tidy the 'm014' column, which represents males aged 0-14 years of age. In order to parse this value, you need to extract the first letter into a new column for gender, and the rest into a column for age_group. Here, since you can parse values by position, you can take advantage of pandas' vectorized string slicing by using the str attribute of columns of type object.\n",
    "\n",
    "Begin by printing the columns of tb in the IPython Shell using its .columns attribute, and take note of the problematic column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tb = pd.read_csv('data/tb.csv')\n",
    "tb.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Melt tb: tb_melt\n",
    "tb_melt = pd.melt(tb, id_vars=['country', 'year'])\n",
    "print(tb_melt.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the 'gender' column\n",
    "tb_melt['gender'] = tb_melt.variable.str[0]\n",
    "print(tb_melt.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the 'age_group' column\n",
    "tb_melt['age_group'] = tb_melt.variable.str[1:]\n",
    "print(tb_melt.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Superb! Notice the new 'gender' and 'age_group' columns you created. It is vital to be able to split columns as needed so you can access the data that is relevant to your question."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Splitting a column with .split() and .get()\n",
    "\n",
    "Another common way multiple variables are stored in columns is with a delimiter. You'll learn how to deal with such cases in this exercise, using a [dataset consisting of Ebola cases and death counts by state and country](https://data.humdata.org/dataset/ebola-cases-2014). It has been pre-loaded into a DataFrame as ebola.\n",
    "\n",
    "Print the columns of ebola in the IPython Shell using ebola.columns. Notice that the data has column names such as Cases_Guinea and Deaths_Guinea. Here, the underscore _ serves as a delimiter between the first part (cases or deaths), and the second part (country).\n",
    "\n",
    "This time, you cannot directly slice the variable by position as in the previous exercise. You now need to use Python's built-in string method called .split(). By default, this method will split a string into parts separated by a space. However, in this case you want it to split by an underscore. You can do this on Cases_Guinea, for example, using Cases_Guinea.split('_'), which returns the list ['Cases', 'Guinea'].\n",
    "\n",
    "The next challenge is to extract the first element of this list and assign it to a type variable, and the second element of the list to a country variable. You can accomplish this by accessing the str attribute of the column and using the .get() method to retrieve the 0 or 1 index, depending on the part you want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola = pd.read_csv('data/ebola.csv')\n",
    "print(ebola.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Melt ebola: ebola_melt\n",
    "ebola_melt = pd.melt(ebola, id_vars=['Date', 'Day'], var_name='type_country', value_name='counts')\n",
    "print(ebola_melt.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola_melt.type_country.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the 'str_split' column\n",
    "ebola_melt['str_split'] = ebola_melt['type_country'].str.split('_')\n",
    "ebola_melt['str_split'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the 'type' column\n",
    "ebola_melt['type'] = ebola_melt['str_split'].str.get(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the 'country' column\n",
    "ebola_melt['country'] = ebola_melt['str_split'].str.get(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the head of ebola_melt\n",
    "print(ebola_melt.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining Data for Analysis\n",
    "### Concatenating Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining rows of data\n",
    "\n",
    "The dataset you'll be working with here relates to [NYC Uber data](http://data.beta.nyc/dataset/uber-trip-data-foiled-apr-sep-2014). The original dataset has all the originating Uber pickup locations by time and latitude and longitude. For didactic purposes, you'll be working with a very small portion of the actual data.\n",
    "\n",
    "Three DataFrames have been pre-loaded: uber1, which contains data for April 2014, uber2, which contains data for May 2014, and uber3, which contains data for June 2014. Your job in this exercise is to concatenate these DataFrames together such that the resulting DataFrame has the data for all three months.\n",
    "\n",
    "Begin by exploring the structure of these three DataFrames in the IPython Shell using methods such as .head()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uber1 = pd.read_csv('data/uber1.csv')\n",
    "uber2 = pd.read_csv('data/uber2.csv')\n",
    "uber3 = pd.read_csv('data/uber3.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(uber1.head())\n",
    "print(uber2.head())\n",
    "print(uber3.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "row_concat = pd.concat([uber1, uber2, uber3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(row_concat.shape)\n",
    "print(row_concat.head())\n",
    "print(row_concat.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining columns of data\n",
    "\n",
    "Think of column-wise concatenation of data as stitching data together from the sides instead of the top and bottom. To perform this action, you use the same pd.concat() function, but this time with the keyword argument axis=1. The default, axis=0, is for a row-wise concatenation.\n",
    "\n",
    "You'll return to the Ebola dataset you worked with briefly in the last chapter. It has been pre-loaded into a DataFrame called ebola_melt. In this DataFrame, the status and country of a patient is contained in a single column. This column has been parsed into a new DataFrame, status_country, where there are separate columns for status and country.\n",
    "\n",
    "Explore the ebola_melt and status_country DataFrames in the IPython Shell. Your job is to concatenate them column-wise in order to obtain a final, clean DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "status_country = ebola_melt[['type', 'country']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola_melt_2 = ebola_melt[['Date', 'Day', 'type_country', 'counts']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(status_country.head())\n",
    "print(ebola_melt_2.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola_tidy = pd.concat([ebola_melt_2, status_country], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(ebola_tidy.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding and Concatenating Data\n",
    "\n",
    "### Finding files that match a pattern\n",
    "\n",
    "You're now going to practice using the glob module to find all csv files in the workspace. In the next exercise, you'll programmatically load them into DataFrames.\n",
    "\n",
    "As Dan showed you in the video, the glob module has a function called glob that takes a pattern and returns a list of the files in the working directory that match that pattern.\n",
    "\n",
    "For example, if you know the pattern is part_ single digit number .csv, you can write the pattern as 'part_?.csv' (which would match part_1.csv, part_2.csv, part_3.csv, etc.)\n",
    "\n",
    "Similarly, you can find all .csv files with '*.csv', or all parts with 'part_*'. The ? wildcard represents any 1 character, and the * wildcard represents any number of characters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_files = glob('data/uber?.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(csv_files)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Iterating and concatenating all matches\n",
    "\n",
    "Now that you have a list of filenames to load, you can load all the files into a list of DataFrames that can then be concatenated.\n",
    "\n",
    "You'll start with an empty list called frames. Your job is to use a for loop to iterate through each of the filenames, read each filename into a DataFrame, and then append it to the frames list.\n",
    "\n",
    "You can then concatenate this list of DataFrames using pd.concat(). Go for it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_data = []\n",
    "for filename in csv_files:\n",
    "    data = pd.read_csv(filename)\n",
    "    list_data.append(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uber_new = pd.concat(list_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(uber_new.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge Data\n",
    "\n",
    "```python\n",
    "pd.merge(left=state_populations, right=state_codes, on=None, left_on='state', right_on='name')\n",
    "```\n",
    "\n",
    "use the 'on' parameter if the columns from the DataFrames are spelled the same\n",
    "\n",
    "**Types of Merges**\n",
    "* One-to-one\n",
    "* Many-to-one / One-to-many\n",
    "* Many-to-many\n",
    "\n",
    "### 1-to-1 data merge\n",
    "\n",
    "Merging data allows you to combine disparate datasets into a single dataset to do more complex analysis.\n",
    "\n",
    "Here, you'll be using survey data that contains readings that William Dyer, Frank Pabodie, and Valentina Roerich took in the late 1920 and 1930 while they were on an expedition towards Antarctica. The dataset was taken from a sqlite database from the [Software Carpentry SQL lesson](http://swcarpentry.github.io/sql-novice-survey/).\n",
    "\n",
    "Two DataFrames have been pre-loaded: site and visited. Explore them in the IPython Shell and take note of their structure and column names. Your task is to perform a 1-to-1 merge of these two DataFrames using the 'name' column of site and the 'site' column of visited."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "site = pd.DataFrame(data=[['DR-1', -49.85, -128.57],\n",
    "                          ['DR-3', -47.15, -126.72],\n",
    "                          ['MSK-4', -48.87, -123.40]],\n",
    "                    index=range(0,3), columns=['name', 'lat', 'long'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "site"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visited = pd.DataFrame(data=[[619, 'DR-1', '1927-02-08'],\n",
    "                             [734, 'DR-3', '1939-01-07'],\n",
    "                             [837, 'MSK-4', '1932-01-14']],\n",
    "                       index=range(0,3), columns=['ident', 'site', 'dated'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visited"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "o2o = pd.merge(left=site, right=visited, on=None, left_on='name', right_on='site')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "o2o"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Superb! Notice the 1-to-1 correspondence between the name column of the site DataFrame and the site column of the visited DataFrame. This is what made the 1-to-1 merge possible."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Many-to-1 data merge\n",
    "\n",
    "In a many-to-one (or one-to-many) merge, one of the values will be duplicated and recycled in the output. That is, one of the keys in the merge is not unique.\n",
    "\n",
    "Here, the two DataFrames site and visited have been pre-loaded once again. Note that this time, visited has multiple entries for the site column. Confirm this by exploring it in the IPython Shell.\n",
    "\n",
    "The .merge() method call is the same as the 1-to-1 merge from the previous exercise, but the data and output will be different."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visited_new = pd.DataFrame(data=[[622, 'DR-1', '1927-02-10'],\n",
    "                                 [735, 'DR-3', '1930-01-12'],\n",
    "                                 [751, 'DR-3', '1930-02-26'],\n",
    "                                 [752, 'DR-3', 'NaN'],\n",
    "                                 [844, 'DR-1', '1932-03-22']],\n",
    "                           index=range(0,5), columns=['ident', 'site', 'dated'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visited_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visited = pd.concat([visited, visited_new]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visited"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m2o = pd.merge(left=site, right=visited, on=None, left_on='name', right_on='site')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "m2o"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Many-to-many data merge\n",
    "\n",
    "The final merging scenario occurs when both DataFrames do not have unique keys for a merge. What happens here is that for each duplicated key, every pairwise combination will be created.\n",
    "\n",
    "Two example DataFrames that share common key values have been pre-loaded: df1 and df2. Another DataFrame df3, which is the result of df1 merged with df2, has been pre-loaded. All three DataFrames have been printed - look at the output and notice how pairwise combinations have been created. This example is to help you develop your intuition for many-to-many merges.\n",
    "\n",
    "Here, you'll work with the site and visited DataFrames from before, and a new survey DataFrame. Your task is to merge site and visited as you did in the earlier exercises. You will then merge this merged DataFrame with survey.\n",
    "\n",
    "Begin by exploring the site, visited, and survey DataFrames in the IPython Shell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(data=[['a', 1], ['a', 2], ['b', 3], ['b', 4]], index=range(0,4), columns=['c1', 'c2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame(data=[['a', 10], ['a', 20], ['b', 30], ['b', 40]], index=range(0,4), columns=['c1', 'c2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame(data=[['a', 1, 10], ['a', 1, 20], ['a', 2, 10], ['a', 2, 20], ['b', 3, 30], ['b', 3, 40], ['b', 4, 30], ['b', 4, 40]], index=range(0,8), columns=['c1', 'c2_x', 'c2_y'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "survey = pd.read_csv('data/survey.csv')\n",
    "survey.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge site and visited: m2m\n",
    "m2m = pd.merge(left=site, right=visited, left_on='name', right_on='site')\n",
    "m2m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge m2m and survey: m2m\n",
    "m2m = pd.merge(left=m2m, right=survey, left_on='ident', right_on='taken')\n",
    "m2m.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning Data for Analysis\n",
    "### Data Types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Converting data types\n",
    "\n",
    "In this exercise, you'll see how ensuring all categorical variables in a DataFrame are of type category reduces memory usage.\n",
    "\n",
    "The [tips dataset](https://github.com/mwaskom/seaborn-data/blob/master/tips.csv) has been loaded into a DataFrame called tips. This data contains information about how much a customer tipped, whether the customer was male or female, a smoker or not, etc.\n",
    "\n",
    "Look at the output of tips.info() in the IPython Shell. You'll note that two columns that should be categorical - sex and smoker - are instead of type object, which is pandas' way of storing arbitrary strings. Your job is to convert these two columns to type category and note the reduced memory usage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# path_to_data = r'E:\\Users\\Trenton J. McKinney\\PycharmProjects\\DataCamp\\DataCamp-master\\10-cleaning-data-in-python\\_datasets\\tips.csv'\n",
    "path_to_data = r'D:\\PythonProjects\\DataCamp\\DataCamp-master\\10-cleaning-data-in-python\\_datasets\\tips.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips = pd.read_csv(path_to_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips.sex = tips.sex.astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips.smoker = tips.smoker.astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(tips.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Excellent! By converting sex and smoker to categorical variables, the memory usage of the DataFrame went down from 13.4 KB to 10.1KB. This may seem like a small difference here, but when you're dealing with large datasets, the reduction in memory usage can be very significant!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Working with numeric data\n",
    "\n",
    "If you expect the data type of a column to be numeric (int or float), but instead it is of type object, this typically means that there is a non numeric value in the column, which also signifies bad data.\n",
    "\n",
    "You can use the pd.to_numeric() function to convert a column into a numeric data type. If the function raises an error, you can be sure that there is a bad value within the column. You can either use the techniques you learned in Chapter 1 to do some exploratory data analysis and find the bad value, or you can choose to ignore or coerce the value into a missing value, NaN.\n",
    "\n",
    "A modified version of the tips dataset has been pre-loaded into a DataFrame called tips. For instructional purposes, it has been pre-processed to introduce some 'bad' data for you to clean. Use the .info() method to explore this. You'll note that the total_bill and tip columns, which should be numeric, are instead of type object. Your job is to fix this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert 'total_bill' to a numeric dtype\n",
    "tips['total_bill'] = pd.to_numeric(tips['total_bill'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert 'tip' to a numeric dtype\n",
    "tips['tip'] = pd.to_numeric(tips['tip'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the info of tips\n",
    "print(tips.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great work! The 'total_bill' and 'tip' columns in this DataFrame are stored as object types because the string 'missing' is used in these columns to encode missing values. By coercing the values into a numeric type, they become proper NaN values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Regular Expressions to Clean Strings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![alt text](images/regex_example.JPG \"Regex Example\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "inport re\n",
    "pattern = re.compile('\\$\\d*\\.\\d{2}')\n",
    "result = pattern.match('$17.89')\n",
    "bool(result)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### String parsing with regular expressions\n",
    "\n",
    "In the video, Dan introduced you to the basics of regular expressions, which are powerful ways of defining patterns to match strings. This exercise will get you started with writing them.\n",
    "\n",
    "When working with data, it is sometimes necessary to write a regular expression to look for properly entered values. Phone numbers in a dataset is a common field that needs to be checked for validity. Your job in this exercise is to define a regular expression to match US phone numbers that fit the pattern of xxx-xxx-xxxx.\n",
    "\n",
    "The [regular expression module](https://docs.python.org/3/library/re.html) in python is re. When performing pattern matching on data, since the pattern will be used for a match across multiple rows, it's better to compile the pattern first using re.compile(), and then use the compiled pattern to match values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Import re.\n",
    "* Compile a pattern that matches a phone number of the format xxx-xxx-xxxx.\n",
    "    * Use \\d{x} to match x digits. Here you'll need to use it three times: twice to match 3 digits, and once to match 4 digits.\n",
    "    * Place the regular expression inside re.compile().\n",
    "* Using the .match() method on prog, check whether the pattern matches the string '123-456-7890'.\n",
    "* Using the same approach, now check whether the pattern matches the string '1123-456-7890'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compile the pattern: prog\n",
    "prog = re.compile('\\d{3}-\\d{3}-\\d{4}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# See if the pattern matches\n",
    "result = prog.match('123-456-7890')\n",
    "print(bool(result))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# See if the pattern matches\n",
    "result2 = prog.match('1123-456-7890')\n",
    "print(bool(result2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Extracting numerical values from strings\n",
    "\n",
    "Extracting numbers from strings is a common task, particularly when working with unstructured data or log files.\n",
    "\n",
    "Say you have the following string: 'the recipe calls for 6 strawberries and 2 bananas'.\n",
    "\n",
    "It would be useful to extract the 6 and the 2 from this string to be saved for later use when comparing strawberry to banana ratios.\n",
    "\n",
    "When using a regular expression to extract multiple numbers (or multiple pattern matches, to be exact), you can use the re.findall() function. Dan did not discuss this in the video, but it is straightforward to use: You pass in a pattern and a string to re.findall(), and it will return a list of the matches.\n",
    "\n",
    "* Write a pattern that will find all the numbers in the following string: 'the recipe calls for 10 strawberries and 1 banana'. To do this:\n",
    "    * Use the re.findall() function and pass it two arguments: the pattern, followed by the string.\n",
    "    * \\d is the pattern required to find digits. This should be followed with a + so that the previous element is matched one or more times. This ensures that 10 is viewed as one number and not as 1 and 0.\n",
    "* Print the matches to confirm that your regular expression found the values 10 and 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the numeric values: matches\n",
    "matches = re.findall('\\d+', 'the recipe calls for 10 strawberries and 1 banana')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the matches\n",
    "print(matches)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pattern matching\n",
    "\n",
    "In this exercise, you'll continue practicing your regular expression skills. For each provided string, your job is to write the appropriate pattern to match it.\n",
    "\n",
    "* Write patterns to match:\n",
    "    * A telephone number of the format xxx-xxx-xxxx. You already did this in a previous exercise.\n",
    "    * A string of the format: A dollar sign, an arbitrary number of digits, a decimal point, 2 digits.\n",
    "        * Use \\$ to match the dollar sign, \\d* to match an arbitrary number of digits, \\\\. to match the decimal point, and \\d{x} to match x number of digits.\n",
    "    * A capital letter, followed by an arbitrary number of alphanumeric characters.\n",
    "        * Use [A-Z] to match any capital letter followed by \\w* to match an arbitrary number of alphanumeric characters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the first pattern\n",
    "pattern1 = bool(re.match(pattern='\\d{3}-\\d{3}-\\d{4}', string='123-456-7890'))\n",
    "print(pattern1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the second pattern\n",
    "pattern2 = bool(re.match(pattern='\\$\\d*\\.\\d{2}', string='$123.45'))\n",
    "print(pattern2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the third pattern\n",
    "pattern3 = bool(re.match(pattern='[A-Z]\\w*', string='Australia'))\n",
    "print(pattern3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using Functions to Clean Data\n",
    "#### Custom Functions to Clean Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, you'll dive into some of the grittier aspects of data cleaning. You'll learn about string manipulation and pattern matching to deal with unstructured data, and then explore techniques to deal with missing or duplicate data. You'll also learn the valuable skill of programmatically checking your data for consistency, which will give you confidence that your code is running correctly and that the results of your analysis are reliable!\n",
    "\n",
    "#### Complex Cleaning\n",
    "\n",
    "* Cleaning step requires multiple steps\n",
    "    * Extract number from string\n",
    "    * Perform transformation on extracted number\n",
    "* Python function\n",
    "\n",
    "#### Using Apply"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex1_df = pd.DataFrame(data=[['Daniel', 18, 42],\n",
    "                               ['John', 12, 31],\n",
    "                               ['Jane', 24, 27]],\n",
    "                         index=range(0,3),\n",
    "                         columns=['name', 'treatment a', 'treatment b'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex1_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex1_df = ex1_df.set_index('name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex1_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex1_df.apply(np.mean, axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex1_df.apply(np.mean, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex2_df = pd.DataFrame(data=[[121577873, 2, 'MANHATTAN', '$75000.00', '$986.00'],\n",
    "                            [520129502, 1, 'STATEN ISLAND', '$0.00', '$1144.00'],\n",
    "                            [121601560, 1, 'MANHATTAN', '$30000.00', '$522.50'],\n",
    "                            [121601203, 1, 'MANHATTAN', '$1500.00', '$225.00'],\n",
    "                            [121601338, 1, 'MANHATTAN', '$19500.00', '$389.50']],\n",
    "                      index=range(0,5),\n",
    "                      columns=['Job #', 'Doc #', 'Borough', 'Initial Cost', 'Total Est. Fee'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex2_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Regular expression pattern for monetary values\n",
    "pattern = re.compile('^\\$\\d*\\.\\d{2}$')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def diff_money(row, pattern):\n",
    "    icost = row['Initial Cost']\n",
    "    tef = row['Total Est. Fee']\n",
    "    \n",
    "    if bool(pattern.match(icost)) and bool(pattern.match(tef)):\n",
    "        icost = icost.replace('$', '')\n",
    "        tef = tef.replace('$', '')\n",
    "        \n",
    "        icost = float(icost)\n",
    "        tef = float(tef)\n",
    "        \n",
    "        return icost - tef\n",
    "    else:\n",
    "        return(NaN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex2_df['diff'] = ex2_df.apply(diff_money,\n",
    "                              axis=1,\n",
    "                              pattern=pattern)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex2_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Custom functions to clean data\n",
    "\n",
    "You'll now practice writing functions to clean data.\n",
    "\n",
    "The tips dataset has been pre-loaded into a DataFrame called ```tips```. It has a ```'sex'``` column that contains the values ```'Male'``` or ```'Female'```. Your job is to write a function that will recode ```'Female'``` to ```0```, ```'Male'``` to ```1```, and return ```np.nan``` for all entries of ```'sex'``` that are neither ```'Female'``` nor ```'Male'```.\n",
    "\n",
    "Recoding variables like this is a common data cleaning task. Functions provide a mechanism for you to abstract away complex bits of code as well as reuse code. This makes your code more readable and less error prone.\n",
    "\n",
    "As Dan showed you in the videos, you can use the ```.apply()``` method to apply a function across entire rows or columns of DataFrames. However, note that each column of a DataFrame is a pandas Series. Functions can also be applied across Series. Here, you will apply your function over the ```'sex'``` column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Instructions\n",
    "\n",
    "* Define a function named ```recode_gender()``` that has one parameter: ```gender```.\n",
    "    * If ```gender``` equals ```'Male'```, return ```1```.\n",
    "    * Else, if ```gender``` equals ```'Female'```, return ```0```.\n",
    "    * If ```gender``` does not equal ```'Male'``` or ```'Female'```, return ```np.nan``. NumPy has been pre-imported for you.\n",
    "* Apply your ```recode_gender()``` function over ```tips.sex``` using the ```.apply()``` method to create a new column: ```'recode'```. Note that when passing in a function inside the ```.apply()``` method, you don't need to specify the parentheses after the function name.\n",
    "* Hit 'Submit Answer' and take note of the new ```'gender_recode'``` column in the ```tips``` DataFrame!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips = pd.read_csv('tips_data_cleaning.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define recode_gender()\n",
    "def recode_gender(gender):\n",
    "\n",
    "    # Return 0 if gender is 'Female'\n",
    "    if gender == 'Female':\n",
    "        return 0\n",
    "    \n",
    "    # Return 1 if gender is 'Male'    \n",
    "    elif gender == 'Male':\n",
    "        return 1\n",
    "    \n",
    "    # Return np.nan    \n",
    "    else:\n",
    "        return NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply the function to the sex column\n",
    "tips['recode'] = tips.sex.apply(recode_gender)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the first five rows of tips\n",
    "print(tips.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well done! For simple recodes, you can also use the ```replace``` method. You can also convert the column into a categorical type."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lambda Functions\n",
    "\n",
    "You'll now be introduced to a powerful Python feature that will help you clean your data more effectively: lambda functions. Instead of using the ```def``` syntax that you used in the previous exercise, lambda functions let you make simple, one-line functions.\n",
    "\n",
    "For example, here's a function that squares a variable used in an ```.apply()``` method:\n",
    "\n",
    "```python\n",
    "def my_square(x):\n",
    "    return x ** 2\n",
    "```\n",
    "\n",
    "```python\n",
    "df.apply(my_square)\n",
    "```\n",
    "\n",
    "The equivalent code using a lambda function is:\n",
    "\n",
    "```python\n",
    "df.apply(lambda x: x ** 2)\n",
    "```\n",
    "\n",
    "The lambda function takes one parameter - the variable ```x```. The function itself just squares ```x``` and returns the result, which is whatever the one line of code evaluates to. In this way, lambda functions can make your code concise and Pythonic.\n",
    "\n",
    "The tips dataset has been pre-loaded into a DataFrame called ```tips```. Your job is to clean its ```'total_dollar'``` column by removing the dollar sign. You'll do this using two different methods: With the ```.replace()``` method, and with regular expressions. The regular expression module ```re``` has been pre-imported.\n",
    "\n",
    "#### Instructions\n",
    "\n",
    "* Use the .replace() method inside a lambda function to remove the dollar sign from the 'total_dollar' column of tips.\n",
    "    * You need to specify two arguments to the .replace() method: The string to be replaced (\"$\"), and the string to replace it by ('').\n",
    "    * Apply the lambda function over the 'total_dollar' column of tips.\n",
    "* Use a regular expression to remove the dollar sign from the 'total_dollar' column of tips.\n",
    "    * The pattern has been provided for you: It is the first argument of the re.findall() function.\n",
    "    * Complete the rest of the lambda function and apply it over the 'total_dollar' column of tips. Notice that because re.findall() returns a list, you have to slice it in order to access the actual value.\n",
    "* Hit 'Submit Answer' to verify that you have removed the dollar sign from the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the lambda function using replace\n",
    "tips['total_dollar_replace'] = tips.total_dollar.apply(lambda x: x.replace('$', ''))\n",
    "\n",
    "# Write the lambda function using regular expressions\n",
    "tips['total_dollar_re'] = tips.total_dollar.apply(lambda x: re.findall('\\d+\\.\\d+', x)[0])\n",
    "\n",
    "# Print the head of tips\n",
    "print(tips.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Duplicate and Missing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Duplicate Data\n",
    "* Can skew results\n",
    "* ```.drop_duplicates()``` method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex3_df = pd.DataFrame(data=[['Daniel', 'male', '', 42],\n",
    "                            ['John', 'male', 12, 31],\n",
    "                            ['Jane', 'female', 24, 27],\n",
    "                            ['Daniel', 'male', '', 42]],\n",
    "                      index=range(0,4),\n",
    "                      columns=['name', 'gender', 'treatment a', 'treatment b'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex3_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex3_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Missing Data\n",
    "\n",
    "* Leave as-is\n",
    "* Drop them\n",
    "* Fill missing value using various Pandas methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex4_df = pd.DataFrame(columns=['total_bill', 'tip', 'sex', 'smoker', 'day', 'time', 'size', 'total_dollar', 'recode', 'total_dollar_replace', 'total_dollar_re'],\n",
    "                      data=[[16.99,1.01,'Female','No','Sun','Dinner',2,'$16.99' ,0,16.99 ,16.99],\n",
    "                            [NaN,1.66,'Male','No','Sun','Dinner',3,'$10.34' ,1,10.34 ,10.34],\n",
    "                            [21.01,3.5,'Male','No','Sun','Dinner',3,'$21.01' ,1,21.01 ,21.01],\n",
    "                            [23.68,NaN,'Male','No','Sun','Dinner',2,'$23.68' ,1,23.68 ,23.68],\n",
    "                            [24.59,3.61,NaN,NaN,'Sun',NaN,4,'$24.59' ,0,24.59 ,24.59]])\n",
    "\n",
    "ex4_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ex4_df_nan = ex4_df\n",
    "ex4_df_nan.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### .dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips_dropped = ex4_df_nan.dropna()\n",
    "tips_dropped.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### .fillna()\n",
    "\n",
    "* Fill with a user provided value\n",
    "* Usa a summary statistic\n",
    "    * mean\n",
    "    * median\n",
    "    * Use caution when filling with a test statistic\n",
    "        * The value used, must make sense\n",
    "        * Median is a better statistics if there are outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips_fill = ex4_df.copy()\n",
    "tips_fill['sex'] = tips_fill['sex'].fillna('missing')\n",
    "tips_fill[['total_bill', 'size']] = tips_fill[['total_bill', 'size']].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips_fill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mean_tip = ex4_df['tip'].mean()\n",
    "mean_tip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tips_fill['tip'] = tips_fill['tip'].fillna(mean_tip)\n",
    "tips_fill"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Dropping duplicate data\n",
    "\n",
    "Duplicate data causes a variety of problems. From the point of view of performance, they use up unnecessary amounts of memory and cause unneeded calculations to be performed when processing data. In addition, they can also bias any analysis results.\n",
    "\n",
    "A dataset consisting of the performance of songs on the Billboard charts has been pre-loaded into a DataFrame called billboard. Check out its columns in the IPython Shell. Your job in this exercise is to subset this DataFrame and then drop all duplicate rows.\n",
    "\n",
    "* Create a new DataFrame called tracks that contains the following columns from billboard: 'year', 'artist', 'track', and 'time'.\n",
    "* Print the info of tracks. This has been done for you.\n",
    "* Drop duplicate rows from tracks using the .drop_duplicates() method. Save the result to tracks_no_duplicates.\n",
    "* Print the info of tracks_no_duplicates. This has been done for you, so hit 'Submit Answer' to see the results!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "billboard = pd.read_csv(r'data\\billboard.csv', dtype={'year': 'int64'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "billboard.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "billboard.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the new DataFrame: tracks\n",
    "tracks = billboard[['year', 'artist', 'track', 'time']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tracks.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print info of tracks\n",
    "print(tracks.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the duplicates: tracks_no_duplicates\n",
    "tracks_no_duplicates = tracks.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print info of tracks\n",
    "print(tracks_no_duplicates.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Filling missing data\n",
    "\n",
    "Here, you'll return to the airquality dataset from Chapter 2. It has been pre-loaded into the DataFrame airquality, and it has missing values for you to practice filling in. Explore airquality in the IPython Shell to checkout which columns have missing values.\n",
    "\n",
    "It's rare to have a (real-world) dataset without any missing values, and it's important to deal with them because certain calculations cannot handle missing values while some calculations will, by default, skip over any missing values.\n",
    "\n",
    "Also, understanding how much missing data you have, and thinking about where it comes from is crucial to making unbiased interpretations of data.\n",
    "\n",
    "* Calculate the mean of the Ozone column of airquality using the .mean() method on airquality.Ozone.\n",
    "* Use the .fillna() method to replace all the missing values in the Ozone column of airquality with the mean, oz_mean.\n",
    "* Hit 'Submit Answer' to see the result of filling in the missing values!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "airquality = pd.read_csv(r'DataCamp-master\\10-cleaning-data-in-python\\_datasets\\airquality.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "airquality.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the mean of the Ozone column: oz_mean\n",
    "oz_mean = airquality.Ozone.mean()\n",
    "\n",
    "# Replace all the missing values in the Ozone column with the mean\n",
    "airquality['Ozone'] = airquality.Ozone.fillna(oz_mean)\n",
    "\n",
    "# Print the info of airquality\n",
    "print(airquality.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Testing with Asserts\n",
    "\n",
    "#### Assert Statements\n",
    "\n",
    "* Programmatically vs visually checking\n",
    "* If we drop of fill NaNs, we expect 0 missing values\n",
    "* We can write an assert statement to verify this\n",
    "* We can detect early warnings and errors\n",
    "* This gives us confidence that our code is running correctly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert 1 == 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert 1 == 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### Google stock data\n",
    "gsd_df = pd.DataFrame(columns=['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close'],\n",
    "                      data=[['2017-02-09', 831.729980 , NaN, 826.500000, 830.059998, 1192000.0, NaN],\n",
    "                            ['2017-02-08', 830.530029, 834.250000, 825.109985, 829.880005, 1300600.0, 829.880005],\n",
    "                            ['2017-02-07', NaN, NaN, 823.289978, NaN, 1664800.0, NaN],\n",
    "                            ['2017-02-06', 820.919983, 822.390015, NaN, 821.619995, NaN, 821.619995],\n",
    "                            ['2017-02-03', NaN, 826.130005, 819.349976, 820.130005, 1524400.0, 820.130005]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gsd_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert gsd_df.Close.notnull().all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gsd_df_0 = gsd_df.fillna(value=0)\n",
    "\n",
    "assert gsd_df_0.Close.notnull().all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Testing your data with asserts\n",
    "\n",
    "Here, you'll practice writing assert statements using the Ebola dataset from previous chapters to programmatically check for missing values and to confirm that all values are positive. The dataset has been pre-loaded into a DataFrame called ebola.\n",
    "\n",
    "In the video, you saw Dan use the .all() method together with the .notnull() DataFrame method to check for missing values in a column. The .all() method returns True if all values are True. When used on a DataFrame, it returns a Series of Booleans - one for each column in the DataFrame. So if you are using it on a DataFrame, like in this exercise, you need to chain another .all() method so that you return only one True or False value. When using these within an assert statement, nothing will be returned if the assert statement is true: This is how you can confirm that the data you are checking are valid.\n",
    "\n",
    "Note: You can use pd.notnull(df) as an alternative to df.notnull().\n",
    "\n",
    "* Write an assert statement to confirm that there are no missing values in ebola.\n",
    "    * Use the pd.notnull() function on ebola (or the .notnull() method of ebola) and chain two .all() methods (that is, .all().all()). The first .all() method will return a True or False for each column, while the second .all() method will return a single True or False.\n",
    "* Write an assert statement to confirm that all values in ebola are greater than or equal to 0.\n",
    "    * Chain two all() methods to the Boolean condition (ebola >= 0)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola = pd.read_csv(r'data\\ebola.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assert that there are no missing values\n",
    "assert ebola.notnull().all().all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assert that all values are >= 0\n",
    "assert (ebola >= 0).all().all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola_0 = ebola.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ebola_0.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assert that there are no missing values\n",
    "assert ebola_0.notnull().all().all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assert that all values are >= 0\n",
    "assert (ebola_0 >= 0).all().all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Putting It All Together\n",
    "\n",
    "* Use the techniques you've learned on the Gapminder data\n",
    "* Clean and tidy data saved to a file\n",
    "    * Ready to be loaded for analysis!\n",
    "* Dataset consists of life expectancy by country and year\n",
    "* Data will come in multiple pars\n",
    "    * Load\n",
    "    * Preliminary quality diagnosis\n",
    "    * Combine into single dataset\n",
    "    \n",
    "```python\n",
    "import pandas as pd\n",
    "df = pd.read_csv('my_data.csv')\n",
    "df.head()\n",
    "df.info()\n",
    "df.columns\n",
    "df.describe()\n",
    "df.column.value_counts()\n",
    "df.column.plot('hist')\n",
    "```\n",
    "\n",
    "#### Data quality\n",
    "\n",
    "```python\n",
    "def cleaning_function(row_data):\n",
    "    # data cleaning steps\n",
    "    return ...\n",
    "\n",
    "# axis=1 -> row\n",
    "# axis=0 -> column, default\n",
    "df.apply(cleaning_function, axis=1)\n",
    "\n",
    "# use asset to check that expected values are what's in the data\n",
    "# no error -> value passed into the assert statement is True\n",
    "assert (df.column_data > 0).all()\n",
    "```\n",
    "\n",
    "#### Combining Data\n",
    "\n",
    "```python\n",
    "pd.merge(df1, df2, ...)\n",
    "pd.concat([df1, df2, df3, ...])\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exploratory analysis\n",
    "Whenever you obtain a new dataset, your first task should always be to do some exploratory analysis to get a better understanding of the data and diagnose it for any potential issues.\n",
    "\n",
    "The Gapminder data for the 19th century has been loaded into a DataFrame called ```g1800s```. In the IPython Shell, use pandas methods such as ```.head()```, ```.info()```, and ```.describe()```, and DataFrame attributes like ```.columns``` and ```.shape``` to explore it.\n",
    "\n",
    "Use the information that you acquire from your exploratory analysis to choose the true statement from the options provided below.\n",
    "\n",
    "#### Possible Answers\n",
    "* The DataFrame has 259 rows and 100 columns.\n",
    "* The DataFrame has no missing values encoded as NaN.\n",
    "* #### 100 of the columns are of type float64 and 1 column is of type object.\n",
    "* The DataFrame takes up 203.2+ KB of memory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1800s = pd.read_csv(r'data\\g1800s.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1800s.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1800s.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1800s.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1800s.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1800s.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exactly! This information is provided by the g1800s.info(). 'Life expectancy' is the only column in the DataFrame that is not of type float64."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Visualizing your data\n",
    "\n",
    "Since 1800, life expectancy around the globe has been steadily going up. You would expect the Gapminder data to confirm this.\n",
    "\n",
    "The DataFrame ```g1800s``` has been pre-loaded. Your job in this exercise is to create a scatter plot with life expectancy in ```'1800'``` on the x-axis and life expectancy in ```'1899'``` on the y-axis.\n",
    "\n",
    "Here, the goal is to visually check the data for insights as well as errors. When looking at the plot, pay attention to whether the scatter plot takes the form of a diagonal line, and which points fall below or above the diagonal line. This will inform how life expectancy in 1899 changed (or did not change) compared to 1800 for different countries. If points fall on a diagonal line, it means that life expectancy remained the same!\n",
    "\n",
    "* Import matplotlib.pyplot as plt.\n",
    "* Use the .plot() method on g1800s with kind='scatter' to create a scatter plot with '1800' on the x-axis and '1899' on the y-axis.\n",
    "* Display the plot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1800s.plot(kind='scatter', x='1800', y='1899')\n",
    "\n",
    "# Specify axis labels\n",
    "plt.xlabel('Life Expectancy by Country in 1800')\n",
    "plt.ylabel('Life Expectancy by Country in 1899')\n",
    "\n",
    "# Specify axis limits\n",
    "plt.xlim(20, 55)\n",
    "plt.ylim(20, 55)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Excellent work! As you can see, there are a surprising number of countries that fall on the diagonal line. In fact, examining the DataFrame reveals that the life expectancy for 140 of the 260 countries did not change at all in the 19th century! This is possibly a result of not having access to the data for all the years back then. In this way, visualizing your data can help you uncover insights as well as diagnose it for errors."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Thinking about the question at hand\n",
    "\n",
    "Since you are given life expectancy level data by country and year, you could ask questions about how much the average life expectancy changes over each year.\n",
    "\n",
    "Before continuing, however, it's important to make sure that the following assumptions about the data are true:\n",
    "\n",
    "* 'Life expectancy' is the first column (index 0) of the DataFrame.\n",
    "* The other columns contain either null or numeric values.\n",
    "* The numeric values are all greater than or equal to 0.\n",
    "* There is only one instance of each country.\n",
    "\n",
    "You can write a function that you can apply over the entire DataFrame to verify some of these assumptions. Note that spending the time to write such a script will help you when working with other datasets as well.\n",
    "\n",
    "#### Instructions\n",
    "\n",
    "* Define a function called check_null_or_valid() that takes in one argument: row_data.\n",
    "* Inside the function, convert no_na to a numeric data type using pd.to_numeric().\n",
    "* Write an assert statement to make sure the first column (index 0) of the g1800s DataFrame is 'Life expectancy'.\n",
    "* Write an assert statement to test that all the values are valid for the g1800s DataFrame. Use the check_null_or_valid() function placed inside the .apply() method for this. Note that because you're applying it over the entire DataFrame, and not just one column, you'll have to chain the .all() method twice, and remember that you don't have to use () for functions placed inside .apply().\n",
    "* Write an assert statement to make sure that each country occurs only once in the data. Use the .value_counts() method on the 'Life expectancy' column for this. Specifically, index 0 of .value_counts() will contain the most frequently occuring value. If this is equal to 1 for the 'Life expectancy' column, then you can be certain that no country appears more than once in the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_null_or_valid(row_data):\n",
    "    \"\"\"\n",
    "    Function that takes a row of data,\n",
    "    drops all missing values,\n",
    "    and checks if all remaining values are greater than or equal to 0\n",
    "    \"\"\"\n",
    "    no_na = row_data.dropna()\n",
    "    numeric = pd.to_numeric(no_na)\n",
    "    ge0 = numeric >= 0\n",
    "    return ge0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check whether the first column is 'Life_Expectancy'\n",
    "assert g1800s.columns[0] == 'Life_Expectancy'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check whether the values in the row are valid\n",
    "assert g1800s.iloc[:, 1:].apply(check_null_or_valid, axis=1).all().all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check that there is only one instance of each country\n",
    "assert g1800s['Life_Expectancy'].value_counts()[0] == 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1800s['Life_Expectancy'].value_counts()[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Assembling your data\n",
    "Here, three DataFrames have been pre-loaded: ```g1800s```, ```g1900s```, and ```g2000s```. These contain the Gapminder life expectancy data for, respectively, the 19th century, the 20th century, and the 21st century.\n",
    "\n",
    "Your task in this exercise is to concatenate them into a single DataFrame called ```gapminder```. This is a row-wise concatenation, similar to how you concatenated the monthly Uber datasets in Chapter 3.\n",
    "\n",
    "#### Instructions\n",
    "* Use pd.concat() to concatenate g1800s, g1900s, and g2000s into one DataFrame called gapminder. Make sure you pass DataFrames to pd.concat() in the form of a list.\n",
    "* Print the shape and the head of the concatenated DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1900s = pd.read_csv(r'data\\g1900s.csv')\n",
    "g2000s = pd.read_csv(r'data\\g2000s.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder = pd.concat([g1800s, g1900s, g2000s], sort=True, axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the shape of gapminder\n",
    "gapminder.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the head of gapminder\n",
    "gapminder.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initial Impressions of the Data\n",
    "\n",
    "#### Principles of Tidy Data\n",
    "\n",
    "* Rows form observations\n",
    "* Columns form variables\n",
    "* Tidying data will make data cleaning easier\n",
    "* Melting turns columns into rows\n",
    "* Pivot will take unique values from a column and create new columns\n",
    "\n",
    "#### Checking Data Types\n",
    "\n",
    "```python\n",
    "df.dtypes\n",
    "df['column'] = df['column'].to_numeric()\n",
    "df['column'] = df['column'].astype(str)\n",
    "```\n",
    "\n",
    "* Numeric values coded as strings is a sign for a bad data value\n",
    "\n",
    "#### Additional Calculations and Saving Your Data\n",
    "\n",
    "```python\n",
    "df['new_column'] = df['column_1'] + df['column_2']\n",
    "df['new_column'] = df.apply(my_function, axis=1)\n",
    "df.to_csv['my_data.csv']\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reshaping your data\n",
    "\n",
    "Now that you have all the data combined into a single DataFrame, the next step is to reshape it into a tidy data format.\n",
    "\n",
    "Currently, the gapminder DataFrame has a separate column for each year. What you want instead is a single column that contains the year, and a single column that represents the average life expectancy for each year and country. By having year in its own column, you can use it as a predictor variable in a later analysis.\n",
    "\n",
    "You can convert the DataFrame into the desired tidy format by melting it.\n",
    "\n",
    "#### Instructions\n",
    "\n",
    "* Reshape ```gapminder``` by melting it. Keep ```'Life expectancy'``` fixed by specifying it as an argument to the ```id_vars``` parameter.\n",
    "* Rename the three columns of the melted DataFrame to ```'country'```, ```'year'```, and ```'life_expectancy'``` by passing them in as a list to ```gapminder_melt.columns```.\n",
    "```python\n",
    "gapminder_melt.columns = ['country', 'year', 'life_expectancy']\n",
    "```\n",
    "* Print the head of the melted DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Melt gapminder: gapminder_melt # rename columns\n",
    "gapminder_melt = pd.melt(gapminder, id_vars=['Life_Expectancy'], var_name='year', value_name='life_expectancy')\n",
    "gapminder_melt = gapminder_melt.rename(columns={'Life_Expectancy': 'country'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print the head of gapminder_melt\n",
    "gapminder_melt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder_melt.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking the data types\n",
    "\n",
    "Now that your data are in the proper shape, you need to ensure that the columns are of the proper data type. That is, you need to ensure that ```country``` is of type ```object```, ```year``` is of type ```int64```, and ```life_expectancy``` is of type ```float64```.\n",
    "\n",
    "The tidy DataFrame has been pre-loaded as ```gapminder```. Explore it in the IPython Shell using the ```.info()``` method. Notice that the column ```'year'``` is of type ```object```. This is incorrect, so you'll need to use the ```pd.to_numeric()``` function to convert it to a numeric data type.\n",
    "\n",
    "NumPy and pandas have been pre-imported as ```np``` and ```pd```.\n",
    "\n",
    "#### Instructions\n",
    "\n",
    "* Convert the year column of gapminder using pd.to_numeric().\n",
    "* Assert that the country column is of type np.object. This has been done for you.\n",
    "* Assert that the year column is of type np.int64.\n",
    "* Assert that the life_expectancy column is of type np.float64."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder_melt.year = pd.to_numeric(gapminder_melt.year, downcast='integer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder_melt.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test if life_expectancy is of type float64\n",
    "assert gapminder_melt.life_expectancy.dtypes == np.float64"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test if year is of type int64\n",
    "assert gapminder_melt.year.dtypes == np.int16"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test if country is of type object\n",
    "assert gapminder_melt.country.dtypes == np.object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder_melt.year.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Looking at country spellings\n",
    "\n",
    "Having tidied your DataFrame and checked the data types, your next task in the data cleaning process is to look at the ```'country'``` column to see if there are any special or invalid characters you may need to deal with.\n",
    "\n",
    "It is reasonable to assume that country names will contain:\n",
    "\n",
    "* The set of lower and upper case letters.\n",
    "* Whitespace between words.\n",
    "* Periods for any abbreviations.\n",
    "\n",
    "To confirm that this is the case, you can leverage the power of regular expressions again. For common operations like this, Pandas has a built-in string method - ```str.contains()``` - which takes a regular expression pattern, and applies it to the Series, returning ```True``` if there is a match, and ```False``` otherwise.\n",
    "\n",
    "Since here you want to find the values that do not match, you have to invert the boolean, which can be done using ```~```. This Boolean series can then be used to get the Series of countries that have invalid names.\n",
    "\n",
    "#### Instructions\n",
    "\n",
    "* Create a Series called ```countries``` consisting of the ```'country'``` column of ```gapminder```.\n",
    "* Drop all duplicates from ```countries``` using the ```.drop_duplicates()``` method.\n",
    "* Write a regular expression that tests your assumptions of what characters belong in ```countries```:\n",
    "    * Anchor the pattern to match exactly what you want by placing a ```^``` in the beginning and ```$``` in the end.\n",
    "    * Use ```A-Za-z``` to match the set of lower and upper case letters, ```\\.``` to match periods, and \\s to match whitespace between words.\n",
    "* Use ```str.contains()``` to create a Boolean vector representing values that match the pattern.\n",
    "* Invert the mask by placing a ```~``` before it.\n",
    "* Subset the ```countries``` series using the ```.loc[]``` accessor and ```mask_inverse```. Then hit 'Submit Answer' to see the invalid country names!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the series of countries: countries\n",
    "countries = pd.Series(gapminder_melt.country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop all the duplicates from countries\n",
    "countries = countries.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the regular expression: pattern\n",
    "pattern = '^[A-Za-z\\.\\s]*$'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the Boolean vector: mask\n",
    "mask = countries.str.contains(pattern)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Invert the mask: mask_inverse\n",
    "mask_inverse = ~mask"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Subset countries using mask_inverse: invalid_countries\n",
    "invalid_countries = countries.loc[mask_inverse]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Print invalid_countries\n",
    "print(invalid_countries)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Excellent work! As you can see, not all these country names are actually invalid so maybe the assumptions need to be tweaked a little. However, there certainly are a few cases worth further investigation, such as St. Barth?lemy. Whenever you are dealing with columns of raw data consisting of strings, it is important to check them for consistency like this."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### More data cleaning and processing\n",
    "\n",
    "It's now time to deal with the missing data. There are several strategies for this: You can drop them, fill them in using the mean of the column or row that the missing value is in (also known as [imputation](#https://en.wikipedia.org/wiki/Imputation_(statistics)), or, if you are dealing with time series data, use a forward fill or backward fill, in which you replace missing values in a column with the most recent known value in the column. See [pandas Foundations](#https://www.datacamp.com/courses/pandas-foundations) for more on forward fill and backward fill.\n",
    "\n",
    "In general, it is not the best idea to drop missing values, because in doing so you may end up throwing away useful information. In this data, the missing values refer to years where no estimate for life expectancy is available for a given country. You could fill in, or guess what these life expectancies could be by looking at the average life expectancies for other countries in that year, for example. Whichever strategy you go with, it is important to carefully consider all options and understand how they will affect your data.\n",
    "\n",
    "In this exercise, you'll practice dropping missing values. Your job is to drop all the rows that have ```NaN``` in the ```life_expectancy``` column. Before doing so, it would be valuable to use assert statements to confirm that ```year``` and ```country``` do not have any missing values.\n",
    "\n",
    "Begin by printing the shape of ```gapminder``` in the IPython Shell prior to dropping the missing values. Complete the exercise to find out what its shape will be after dropping the missing values!\n",
    "\n",
    "#### Instructions\n",
    "\n",
    "* Assert that ```country``` and ```year``` do not contain any missing values. The first assert statement has been written for you. Note the chaining of the ```.all()``` method to ```pd.notnull()``` to confirm that all values in the column are not null.\n",
    "* Drop the rows in the data where any observation in ```life_expectancy``` is missing. As you confirmed that ```country``` and ```year``` don't have missing values, you can use the ```.dropna()``` method on the entire ```gapminder``` DataFrame, because any missing values would have to be in the ```life_expectancy``` column. The ```.dropna()``` method has the default keyword arguments ```axis=0``` and ```how='any'```, which specify that rows with any missing values should be dropped.\n",
    "* Print the shape of ```gapminder```."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assert that country does not contain any missing values\n",
    "assert pd.notnull(gapminder_melt.country).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assert that year does not contain any missing values\n",
    "assert pd.notnull(gapminder_melt.year).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the missing values\n",
    "gapminder_melt = gapminder_melt.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder_melt.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Great work! After dropping the missing values from 'life_expectancy', the number of rows in the DataFrame has gone down from 169260 to 43857. In general, you should avoid dropping too much of your data, but if there is no reasonable way to fill in or impute missing values, then dropping the missing data may be the best solution."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Wrapping up\n",
    "Now that you have a clean and tidy dataset, you can do a bit of visualization and aggregation. In this exercise, you'll begin by creating a histogram of the ```life_expectancy``` column. You should not get any values under 0 and you should see something reasonable on the higher end of the ```life_expectancy``` age range.\n",
    "\n",
    "Your next task is to investigate how average life expectancy changed over the years. To do this, you need to subset the data by each year, get the ```life_expectancy``` column from each subset, and take an average of the values. You can achieve this using the [.groupby()](#http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html) [method](#http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html). This ```.groupby()``` method is covered in greater depth in [Manipulating DataFrames with pandas](#https://www.datacamp.com/courses/manipulating-dataframes-with-pandas).\n",
    "\n",
    "Finally, you can save your tidy and summarized DataFrame to a file using the ```.to_csv()``` method.\n",
    "\n",
    "```matplotlib.pyplot``` and pandas have been pre-imported as ```plt``` and ```pd```. Go for it!\n",
    "\n",
    "#### Instructions\n",
    "\n",
    "* Create a histogram of the ```life_expectancy``` column using the ```.plot()``` method of ```gapminder```. Specify ```kind='hist'```.\n",
    "* Group ```gapminder``` by ```'year'``` and aggregate ```'life_expectancy'``` by the ```mean```. To do this:\n",
    "    * Use the ```.groupby()``` method on ```gapminder``` with ```'year'``` as the argument. Then select ```'life_expectancy'``` and chain the ```.mean()``` method to it.\n",
    "* Print the head and tail of ```gapminder_agg```. This has been done for you.\n",
    "* Create a line plot of average life expectancy per year by using the ```.plot()``` method (without any arguments in plot) on ```gapminder_agg```.\n",
    "* Save ```gapminder``` and ```gapminder_agg``` to csv files called ```'gapminder.csv'``` and ```'gapminder_agg.csv'```, respectively, using the ```.to_csv()``` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add first subplot\n",
    "plt.subplot(2, 1, 1)\n",
    "gapminder_melt.life_expectancy.plot(kind='hist')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group gapminder: gapminder_agg\n",
    "gapminder_agg = gapminder_melt.groupby('year')['life_expectancy'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder_agg.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add second subplot\n",
    "plt.subplot(2, 1, 2)\n",
    "gapminder_agg.plot(kind='line')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add first subplot\n",
    "plt.subplot(2, 1, 1)\n",
    "gapminder_melt.life_expectancy.plot(kind='hist')\n",
    "\n",
    "# Add second subplot\n",
    "plt.subplot(2, 1, 2)\n",
    "gapminder_agg.plot(kind='line')\n",
    "\n",
    "# Add title and specify axis labels\n",
    "plt.title('Life expectancy over the years')\n",
    "plt.ylabel('Life expectancy')\n",
    "plt.xlabel('Year')\n",
    "\n",
    "# Display the plots\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gapminder_agg.to_csv(r'data\\gapminder_agg.csv')\n",
    "gapminder_melt.to_csv(r'data\\gapminder_tidy.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Amazing work! You've stepped through each stage of the data cleaning process and your data is now ready for serious analysis! Looking at the line plot, it seems like life expectancy has, as expected, increased over the years. There is a surprising dip around 1920 that may be worth further investigation!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}