<jupyter_start><jupyter_text>Lesson 3: Pandas Part 1This notebook is based on the official Pandas [documentation](https://pandas.pydata.org/pandas-docs/stable/getting_started/index.html).  Pandas is a Python package that we will use to manage labeled data in R-like DataFrames.  Unless otherwise credited, quoted text comes from this document.  The official [10 Minutes to Pandas](https://pandas.pydata.org/pandas-docs/stable/getting_started/10min.htmlmin) documentation is an excellent resource, and provides the titular guide which we will drawn from and expanded upon for this tutorial.  InstructionsThis tutorial provides step-by-step training divided into numbered sections. The sections often contain embeded exectable code for demonstration.  This tutorial is accompanied by a practice notebook: [L03-Pandas_Part1-Practice.ipynb](./L03-Pandas_Part1-Practice.ipynb). Throughout this tutorial sections labeled as "Tasks" are interspersed and indicated with the icon: ![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/16/Apps-gnome-info-icon.png). You should follow the instructions provided in these sections by performing them in the practice notebook.  When the tutorial is completed you can turn in the final practice notebook.    Introduction*Where does this tool fit in my life?*> For data scientists, working with data is typically divided into multiple stages: munging and cleaning data, analyzing / modeling it, then organizing the results of the analysis into a form suitable for plotting or tabular display. pandas is the ideal tool for all of these tasks. *Why use this package?*> pandas is a Python package providing fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python  Pandas Cheat SheetCheat sheets to remind you of popular Pandas functionality are available online.  You can find cheat sheets here:+ [Data Quest Pandas cheat sheet](./media/pandas-cheet-sheet.pdf)+ [Data Camp cheat sheet](./media/pandas-cheet-sheet.pdf) --- 1. Getting StartedFirst, we need to import the pandas library (and NumPy library too).  All packages are imported at the top of the notebook. Execute the code in the following cell to get started with this notebook.<jupyter_code># Numpy and pandas usage are often intertwined.
# These abbreviations are ubiquitiously used.
import numpy as np
import pandas as pd<jupyter_output><empty_output><jupyter_text>The code above imports both NumPy and Pandas as variables named `np` and `pd`. We can use these variables to access the functionality of both libraries respectively.  Use of `np` and `pd` is commonly used by many. You will find online tutorials and documentation that use this convention. Therefore, we will use those object names for the rest of this class.You may be wondering why we didn't import pandas like this:  ```pythonimport pandas```We could, but the first is far more commonly seen and the latter would require that we prefix all calls to pandas fuction with `pandas.`. It is easier to just prefix with `pd.`.   Task 1a: Setup![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)In the practice notebook, import the following packages:+ `numpy` as `np`+ `pandas` as `pd` --- 2. Data Object CreationHere we will learn to create and use the two data structures provided by Pandas: **Series** and **DataFrames**.  For additional help, see the [Data Structure Intro section](https://pandas.pydata.org/pandas-docs/stable/getting_started/dsintro.htmldsintro).In short, Pandas provides two new objects in which to store and manipulate data.Dimensions | Name |	Description-----------|-------|--------------------------------------1 |	Series | 	1D labeled homogeneously-typed array2 |	DataFrame | 	General 2D labeled, size-mutable tabular structure with potentially heterogeneously-typed columnTypical usage of this Pandas with data analysis will involve the creation of one or more `DataFrame` objects, and manipulating them with the provided methods.  2.1 The `pd.Series` Data ObjectIn Pandas, a Series is a one dimensional array, and similar to NumPy, all the values are of the same data type (i.e. integer, string, boolean, etc.) or **NaN**.  NaN means "not a number" but serves as a marker when a value is missing.  The following cell creates a new series containing an array of 5 numbers and one missing value by simply passing a list of 6 elements to the `pd.Series` function. Execute the cell:<jupyter_code># Create the series object.
my_series = pd.Series([1, 3, 5, np.nan, 6, 8])<jupyter_output><empty_output><jupyter_text>Notice that we have introduced a missing values by using the NumPy variable `np.nan`. Execute the next cell to see what the new series object named `my_series` contains<jupyter_code># Call the object in the notebook.
# This (mostly) calls the __repr__() function of a given object.
my_series<jupyter_output><empty_output><jupyter_text>Task 2a Create a `pd.Series` object![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)Use the practice notebook perform the following:+ Create a series of your own design.  2.2 The `pd.DataFrame` Data ObjectIn Pandas, a DataFrame is a two-dimensional array.  Just like NumPy, it has axes labeled "rows" and "cols".  However, unlike NumPy, Pandas DataFrames have a few advantages over Numpy 2D matrices:  1. DataFrames allows for different data types in each column, where as NumPy arrays must always be of the same data type.2. DataFrames provide greater flexibilty for indexing and slicing. 2.2.1 Create a DataFrame with a dictionaryA dataframe is created by passing a Python 2D dictionary (i.e. **dict**) to the `pd.DataFrame` function.  Remember dictionaries have key/value pairs. The dictionary is interpreted in the following way:- The keys in the dictionary argument becomes the column names- The value in the dictionary argument contains the column values- Each "column" must have the same number of values.The following cell contains code that creates a new DataFrame object named `df_1`.  This dataframe consists of two columns, one named "alpha" and the other "beta".  Each column has 5 elements. Notice that the columns contain different data types.  Execute the following cell.<jupyter_code># Creating a dataframe with a dictionary.
df_1 = pd.DataFrame(
    {'alpha': [0, 1, 2, 3, 4],
     'beta': ['a', 'b', 'c', 'd', 'e']})<jupyter_output><empty_output><jupyter_text>Execute the next cell to see what the new data frame object named `df_1` contains<jupyter_code># Call the object in the notebook.
# This time we get a html output, as the notebook shell
# sees a _repr_html_() function to call.
df_1<jupyter_output><empty_output><jupyter_text>Observe that a DataFrame can be visualized as a data table. Importantly, note that, a numerical index has been automatically generated and is shown in the left-most column.  2.2.2. Create a DataFrame with a Numpy arrayAlternatively, we can create a DataFrame by passing in a Numpy matrix.  Remember that Numpy arrays can only store one data type, so if the data contains mixed types you would not use this approach. The following code creates a Dataframe of 6 rows and 4 columns and fills it with random numbers generated using the Numpy `np.random.randn` function.  Execute the following cell:<jupyter_code>df = pd.DataFrame(np.random.randn(6, 4))<jupyter_output><empty_output><jupyter_text>Let's take a look at our new data frame by executing the following cell:<jupyter_code>df<jupyter_output><empty_output><jupyter_text>Observe that because we did not provide a dictionary, we do not have header names for each column. Rather a numeric column index was automatically generated.  Task 2b: Creating a DataFrame![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)In the practice notebook perform the following:+ Create a pd.DataFrame object from a Python dictionary. Design the data as you like.  2.3. Create a DataFrame with row and column labelsRather than have a numeric index for each row and column of the DataFrame, we may want to give each row a human-readable label. This will help make the data more accessible (as will be seen later).  To demonstrate, lets reuse the 6x4 datafame we created in section 2.2.2. Using this dataset we want to rename the row indexes with hypothetical dates that each row of information was collected, and we want to give the columns names, such as `A1`, `A2`, `B1` and `C1`.  First, let's set the row index names.  The code below uses the `pd.date_range` function to create a list of dates. Most likely, if we have dates in a real input dataset we would not be creating a list of dates, but for the purposes of demonstration we will generate a series of regularly spaces dates over a 6 day period: one for each of the 6 rows. Note that the `start` argument for the `pd.date_range` function expects the date in YYYYMMDD format.<jupyter_code>dates = pd.date_range('20200301', periods=6)
dates<jupyter_output><empty_output><jupyter_text>Now, we can re-create the DataFrame from section 2.2.2 with dates as row indexes and  `A1`, `A2`, `B1` and `C1` as column names. We do this by using the `index` and `columns` arguments when creating the DataFrame as shown in the following cell.  Execute the cell to see the results:<jupyter_code>df = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=('A1','A2', 'B1', 'C1'))
df<jupyter_output><empty_output><jupyter_text>Task 2c: Create DataFrame with labels![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)In the practice notebook perform the following:+ Create a 10x5 dataframe of random numeric integers that follow a [Guassian (normal) Distribution](https://docs.scipy.org/doc/numpy-1.15.0/reference/generated/numpy.random.normal.html).   + Center the distrubtion at 0.85.  + We will use these values as assumed grades for a class of students+ Adjust the row indexes to be the names of hypothetical students.+ Adjust the columsn to be the names of hypothetical projects, homework, exam names, etc. --- 3. Loading DataCreating a DataFrame manually, as in the previous examples, would be tedious for large datasets.  Fortunately, Pandas provides a variety of functions for importing data from files.  This saves you from writing your own Python code to open a file, read in the contents and format it for inclusiong into a DataFrame. For this tutorial we will look at two of these import functions:  `pd.read_csv` and `pd.read_excel`.For this portion of the tutorial, we will use a rather [famous set of data concerning iris flowers](https://en.wikipedia.org/wiki/Iris_flower_data_set).  You can find a copy of the comma-separated file accompanying this notebook in the `data` directory. You can view the file [here](./data/iris.csv).![Iris flower](https://upload.wikimedia.org/wikipedia/commons/thumb/4/41/Iris_versicolor_3.jpg/193px-Iris_versicolor_3.jpg)  3.1 Importing Using `pd.read_csv`The `pd.read_csv` function can be used to read in files that are in comma-separated format or tab delimited format: two very common data formats.  This function is very flexible and has a large number of arguments. See the [`pd.read_csv` online documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html) for a thorough listing of arguments. The iris data is in CSV format and is compatible with the default arguments for the `pd.read_csv` function, therefore we can easily import this data by providing the path to the file as the only argument to the function. This will automatically create a DataFrame for us! Execute the following line to import the data:<jupyter_code>iris_df = pd.read_csv('data/iris.csv')<jupyter_output><empty_output><jupyter_text>Task 3a: Import the iris.csv file![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)In the practice notebook:+ Import the iris dataset.+ Take a look at the `pd.read_csv` online documentation. Write example code in a Markup cell for how you would import this file if it were tab-delimited.  3.2 Importing using `pd.read_excel()`It is common for data to be stored in Excel worksheets (provided the data is not too large).  You can easily export data from Excel to CSV or Tab delimited formats but Pandas provides  the `pd.read_excel` function to save you the time.  We will not practice loading data from Excel, but take a look at the [`read_excel` online documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html) for details. --- 4. Explore your DataThere are a variety of functions for exploring data once you have imported it. Here we will discuss `pd.head`, `pd.tail`, `pd.sample`, and `pd.describe`. 4.1 The `pd.head` functionAs with any Python variable, you can display its contents by simply typing the variable name and pressing the "enter" key.  Howevever, for very large data files this may not be desired.  The data used in this tutorial is not extremly large but for large data we need better ways to examine the data.To easily examine the first set of rows in the DataFrame, use the `head` function of the `pd.DataFrame` object. By deafult it displays the first 5 rows. Execute the following cells to view the first five rows of the iris dataset.<jupyter_code>iris_df.head()<jupyter_output><empty_output><jupyter_text>This is an excellent way to check if you created your `pd.DataFrame` object correctly.  You can change the number of rows returned by providing an argument.  Note, in the table displayed above that the row index have been generated as numeric values but the column indexes are the names of the column headers from the input data file.  If the file has headers, the `pd.read_csv` function will automatically use those as column index names.The following requests the first 10 rows of the data frame:<jupyter_code>iris_df.head(10)<jupyter_output><empty_output><jupyter_text>4.2 The `pd.tail` functionSimliar to the `pd.head` function, the `pd.tail` function lets you peak at the last 5 rows of data.  To demonstrate, execute the following cell.<jupyter_code>iris_df.tail()<jupyter_output><empty_output><jupyter_text>4.3 The `pd.sample` functionThe `pd.sample` function selects a random set of rows to display. By default it only selects 1 row, we can provide the number of rows we would like (e.g. 5) by providing the number as an arugment:<jupyter_code>iris_df.sample(5)<jupyter_output><empty_output><jupyter_text>Repeat execution of the function to see a different set of randomly selected rows:<jupyter_code>iris_df.sample(5)<jupyter_output><empty_output><jupyter_text>Note, the `sample` function can be very important beyond just viewing random rows. If you ever need to select a random sample of records from a dataset the `sample` function can do that for you.  4.4 The `pd.describe` functionThe `pd.describe` function helps demonstrate the power of Pandas over Python dictionaries and Numpy arrays.  It provides a summary of statistics for the data frame in one simple function call. These statistics include the count, mean, standard deviation, min, max and quartiles.Execute the following cell to demonstrate:<jupyter_code>iris_df.describe()<jupyter_output><empty_output><jupyter_text>Task 4a: Explore Data![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)In the practice notebook: + Use `head`, `tail` and `sample` with the iris dataset. + Do the same with the dataset you created in task 2c. --- 5. Accessing DataIn this section we will learn to retreive values, **slice** the data frame, and use column and row labels (rather than numeric indexes) to find data.  5.1 Viewing column and row labels.Before we delve into retreiving data, lets first look at some properties of DataFrames for retrieving the column and row index names.  It is convenient to work with, or check the indexes and columns of a data frame using their names rather than a numeric index.  First, to retrieve the columns names use the `columns` property of the DataFrame.<jupyter_code>iris_df.columns<jupyter_output><empty_output><jupyter_text>Similary, we can get a description of the index using the `index` property:<jupyter_code>iris_df.index<jupyter_output><empty_output><jupyter_text>Observe that here our index is numeric and runs from 0 to 150, incremented by 1  Task 5a: Viewing columns and rows![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)In the practice notebook:+ Display the columns and indexes of the iris dataset.+ Do the same with the dataset you created in Task 2c.  5.2. Get all the valuesPerhaps you would like to extract the contents of the DataFrame into a 2-dimensional Python list. You will lose the power of Pandas, but you should know how to do this. There are two ways to extract values: the new Pandas > v0.24 way and the older way.  This following code example used to be the standard way of getting the values of a dataframe as a NumPy array:```pythonmy_array = df.values```This is depreciated in version 0.24 of Pandas to instead require the use of `to_dict` function.```pythonmy_array = df.to_dict()```You can verify what version of `pandas` you have:<jupyter_code>pd.__version__<jupyter_output><empty_output><jupyter_text>Task 5b: Get Values![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)In the practice notebook: + Check the version of `pandas` you have + Use the appropriate method to convert the iris data to a dictionary.+ Do the same with the dataset you created in Task 2c.  5.3 Basic SelectionsPandas provides a variety of row/column selectors that are similar to the way that lists and arrays are indxed in Python and NumPy. This tutorial will introduce those.   5.3.1 Get a columnThe easiest way to select a single column is to use the column label! When in doubt, use the `.column` property of the DataFrame to find the list of column labels.  Using the column label will result in the column being returned as a Series object.  For example, if we want the `sepal_length` column of the iris data frame we can execute the following:<jupyter_code>slength = iris_df['sepal_length']
slength.head()<jupyter_output><empty_output><jupyter_text>Observe, we can use the `.head` function to get the first few rows of a Series as well as a DataFrame.  5.3.2 Get rows (slicing)Similar to slicing of Python lists, we can do the same with Pandas DataFrames:```pythoniris_df[start:end]```As a reminder, indexing of a list in Python uses 0-based indexing and negative numbers index in reverse order:``` +---+---+---+---+---+---+ | P | y | t | h | o | n | +---+---+---+---+---+---+   0   1   2   3   4   5    -6  -5  -4  -3  -2  -1```**Important:** Recall the recommendation to *not* using this type of indexing (slicing) in production with very large data. Instead use other provided functions (`.loc`, `iloc`, etc). However, to practice, let's get the first row of the iris dataset by slicing:<jupyter_code>iris_df[0:1]<jupyter_output><empty_output><jupyter_text>Next lets get the first five rows:<jupyter_code>iris_df[0:5]<jupyter_output><empty_output><jupyter_text>5.4. Selecting with `df.loc` and `df.iloc`Aside from the column name and slicing (as shown in the previous sections) Pandas provides the `df.loc` and `df.iloc` accessor.  These accessors perform better for selecting subsets of the dataframe. The `.loc` accessor is used to select rows and columns using index names and `.iloc` is used for selecting by numeric indexes.  Although, if your DataFrame only has numeric row indexes then `.loc` can still be used.However, please note:> While standard Python / NumPy expressions for selecting and setting are intuitive and come in handy for interactive work, for production code, we recommend the optimized pandas data access methods, '.at', '.iat', '.loc' and '.iloc'.In other words, rather than index Pandas arrays using something like, `iris[1:5,]`, instead use the functions that will be described below.  They are much faster and optimzied for larger datasets.  5.4.1 Using `.loc`The `.loc` accessor allows for the slicing of the DataFrame by using the labels of either rows and columns.  The syntax is as follows:```pythondf.loc[row_start:row_end, column_start:column_end]```Remember, because the rows in the iris data frame are indexed using integers, we can use `.loc` to get rows using the integer values.  To get the first row:<jupyter_code>iris_df.loc[0]<jupyter_output><empty_output><jupyter_text>To get the first two rows:<jupyter_code>iris_df.loc[0:1]  # Get the first two rows.<jupyter_output><empty_output><jupyter_text>Observe that when only one row is returned that a Series is provided.  When multiple rows are returned then a new DataFrame object is returned. Next, recall that we constructed a 6x4 dataframe in section 2.3 that was filled with random values and we set the row names using datetime values. The DataFrame was named `df`.  As a reminder here are the first 2 rows:<jupyter_code>df.head(2)<jupyter_output><empty_output><jupyter_text>In this data frame the rows are indexed using strings that represent dates. We can therefore use those labels to slice the dataframe.  The following extracts the first row:<jupyter_code>df.loc['2020-03-01']<jupyter_output><empty_output><jupyter_text>Similarly we can get the first two rows:<jupyter_code>df.loc['2020-03-01':'2020-03-02']<jupyter_output><empty_output><jupyter_text>5.4.2 Using `.loc` to get columnsRemember for our iris data frame the column indexes have the names as provided by the `.columns` property.```pythoniris_df.columnsIndex(['sepal_length', 'sepal_width', 'petal_length', 'petal_width',       'species'],      dtype='object')``` We can use these names to retrive values for specific columns. For example, the code below extracts rows 4 through 6 and the columns 'sepal_width' and 'petal_width':<jupyter_code>iris_df.loc[4:6, 'sepal_width': 'petal_width']<jupyter_output><empty_output><jupyter_text>The use of the colon in the code above between 'sepal_width' and 'petal_width' implies a range. Therefore every column between those two are included. This is why 'petal_length' appears in the output.  If we wanted to specifically select only the two columns and none other, we could provide the names in a list:<jupyter_code>iris_df.loc[4:6, ['sepal_width', 'petal_width']]<jupyter_output><empty_output><jupyter_text>Observe that now we only have 'sepal_width', and 'petal_width' in the resulting data frame. Moreover, we can limit the rows to only those specified in the same way:<jupyter_code>iris_df.loc[[4,6], ['sepal_width', 'petal_width']]<jupyter_output><empty_output><jupyter_text>Task 5c: Using `loc`![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)In the practice notebooks:+ Use any iris dataframe to:  + Select a row slice with `loc`.  + Select a row and column slice with `loc`.  + Take a look at the [Pandas documentation for the `at` selector](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.at.html). Use what you learn there to select a single item with Pandas `at` accessor.  5.4.2 Using `.iloc`The `.iloc` accessor allows us to slice the data frame using an integer index, regardless of what the index (or column) labels actually are.The syntax is the same as `.loc` with the exception of that integers are used instead of labels:```pythondf.iloc[row_start:row_end, column_start:column_end]```To retrieve the first row of the iris data we use the numeric index:<jupyter_code>iris_df.iloc[0]  # Get the first row.<jupyter_output><empty_output><jupyter_text>We can use any combination of integer indexes to get a subset of rows and columns.  The following examples retrieves rows 2 to 5, and excludes the 'species' column.<jupyter_code>iris_df.iloc[2:5, :-1]<jupyter_output><empty_output><jupyter_text>Task 5d: Using `iloc`![Task](http://icons.iconarchive.com/icons/sbstnblnd/plateau/96/Apps-gnome-info-icon.png)In the practice notebooks:+ Use any iris dataframe to:        + Select a row slice with `iloc`.    + Select a row and column slice with `iloc`.    + Take a look at the [Pandas documentation for the `iat` selector](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.iat.html). Use what you learn there to select a single item with Pandas `iat` accessor.  5.5 Boolean IndexingPandas allows us to use boolean values to extract specific rows of data that meet certain conditions.To clarify this, lets first examine what occurs when we apply a condition to a data frame.  Create a new dataframe with the following<jupyter_code>df_2 = pd.DataFrame(
    {'alpha': [0, 1, 2, 3, 4],
     'beta': [5, 6, 7, 8, 9]})
df_2<jupyter_output><empty_output><jupyter_text>Let's apply a condition to the data frame to find all values > 1.0<jupyter_code>df_2 > 1.0<jupyter_output><empty_output><jupyter_text>Notice that the resulting data frame contains `True` and `False` values in place of every value that did or did not meet the condition.  We can use this new data frame to subset the original dataframe.Lets consider the iris dataset for a more realistic example.  Suppose we want to find all rows with a `sepal_length` greater than 5.8. We know from the call to `describe` that the mean is approximately 5.8. First, we will identify rows that meet our criteria:<jupyter_code>condition = iris_df['sepal_length'] > 5.8
condition<jupyter_output><empty_output><jupyter_text>Notice that the Series `condition` indicates `False` if the sepal_length value in the row is  5.8. We can use this array to slice the rows of the dataframe in the following way (only rows with a `True` in the `condition` object are returned):<jupyter_code>iris_df[condition].head()<jupyter_output><empty_output><jupyter_text>You could rewrite the lines above to combine them into a single line with identical results:<jupyter_code>iris_df[iris_df['sepal_length'] > 5.8].head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08000306231817486, 'fraction_numerical': 0.01133057724697596, 'mean_word_length': 4.9429026387625115, 'pattern_counts': {'":': 0, '<': 149, '<?xml version=': 0, '>': 157, 'https://': 10, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '37', 'dir': 'jupyter-structured-clean-dedup', 'id': '17185', 'license': 'Apache-2.0', 'n_tokens_mistral': 7702, 'n_tokens_neox': 7354, 'n_words': 3419, 'path': 'L03-Pandas_Part1-Lesson.ipynb', 'repo_name': 'Huiting120/Data-Analytics-With-Python', 'size': '26124'}