# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---
# # Introduction to pandas
#
# *I'd like to thank <NAME> who graciously allowed me to derive pieces of this notebook and others from his amazing SQL/pandas course notes.*
#
# Pandas is kind of like a table in the database or a spreadsheet that we can manipulate programmatically in Python. Pandas has two primary entities that you must be careful to distinguish to avoid getting confused:
#
# * `DataFrame` is 2D tabular data structure; it has both rows and columns
# * `Series` is a 1D array (colume) data structure
#
# While a series looks like  a column from a data frame, they are really separate kinds of objects with different sets of functions that you can apply to them.  It is so important to distinguish between them that I tend to prefix the name of all data frame objects with `df_`, such as `df_salesinfo`.
#
# Pandas lets you do a lot of querying, merging, and aggregation just like a database, but these data frames only exist in memory. That means:
#
# * you can only operate on data that fits in memory
# * data frames you construct disappear when you're Python program terminates, as opposed to the persistent disk
# * pandas is not suitable for use on problems requiring multiple computers
#
# Pandas data frames will be your primary data structure until machine learning, when we discuss building more complicated data structures such as decision trees.
# ## The first steps of EDA
#
# While we go through this material, keep in mind that there is a basic process you can follow when confronted with a new data set. I always start out the following way:
#
# 1.  How many rows are there?
# 1.   What are the column names and column datatypes?
# 1.  Look at the data to see if the assigned/current column datatypes makes sense
# 1.  Identify categorical columns, nominal and ordinal
# 1.   Compute basic point statistics for numerical columns, such as min/max/mean/median
# 1.   Visualize some or all of the numerical columns to examine their distributions
# 1.  Which columns have missing values? Watch out for sentinel values that are physically present, but indicate missing values (e.g., 0, -1, -999, 'none or unspecified', '')
# ## How to start using pandas
#
# First, we have to tell Python that we want to use pandas. I also tell it to import numpy, because I often want to use both of these libraries in conjunction with each other:
import numpy as np
import pandas as pd
# Let's load the cars data again from the `data` subdirectory under this `notebooks` directory (since we are already familiar with).  You can [download this file](https://raw.githubusercontent.com/parrt/msds593/master/notebooks/data/cars.csv) onto your own computer wherever you want, but make sure that you specify the appropriate file name when loading it with pandas.
# what is my current working directory? use "pwd": "print working directory"
# !pwd        # Anything after ! char is sent to Terminal for execution
# !ls -l data # list information about files in the data subdirectory
# In a spreadsheet, the start of the `cars.csv` file looks like:
#
# <img src="images/excel.png" width="250">
df_cars = pd.read_csv("data/cars.csv")
df_cars.head()
# ###  Exercise 0
#
# Just so that you have your notebook set up for today's lab, make sure you can load the CSV file as shown above.
# ## Element data types
#
# You can learn more about all of the [pandas data types](https://numpy.org/doc/stable/user/basics.types.html), but the key idea is that we must distinguish between integers, floating-point values (reals), boolean, strings, and datetime.
#
# |Description| Python | Pandas|
# | ----------- | --------- | ---- |
# |Integers| `int`|`int64`|
# |Text| `str`| `object`|
# |Double/Float| `float`|`float64` |
# |Boolean (T/F)| `bool`| `bool`|
# |Date/Time| `datetime` | `datetime64`|
# |Categorical| n/a | `category` (held internally as an integer)|
#
# The reason that we emphasize knowing types is that these objects behave differently and respond to a different set of functions, which we'll see shortly.
#
# Let me drive home this point about types and values.  All entities in Python have both a value and associated type; everything is just bits in the computer and so Python needs to know how to interpret the bits as a string or number or date. The Python literal `34` has value 34 and type `int`.  The Python literal `'hi'` has value *hi* and type `str`.
a = 34
b = 'hi'
type(a), type(b), a.__class__, b.__class__, a.__class__.__name__, b.__class__.__name__
# Here are some more complicated types:
type([1,2,3]), type(np.array([1,2,3]))
type(df_cars), type(df_cars['MPG'])  # `[..]` indexing a data frame returns a column (as a series object)
type(3.14159), df_cars['MPG'].dtype # what is data type of a float and the MPG column?
# You can think of `float` and  pandas' `float64` as the same thing, but they are technically distinct types in the Python runtime environment; they could behave differently, depending on the operation you perform.
# ### Reading in nonnumeric data
#
# Next, let's look at some public domain data from [Kaggle's Uber Pickups in New York City competition](https://www.kaggle.com/fivethirtyeight/uber-pickups-in-new-york-city). (The license says public domain so it should be okay to host the 100 rows in this repository for educational purposes.)  The goal is to look at date-related, string, and [categorical data types](https://pandas.pydata.org/pandas-docs/stable/user_guide/categorical.html).
df_uber = pd.read_csv("data/kaggle-uber-other-federal.csv")
df_uber.head(3)
df_uber.info() # everything looks like a string (object)
# Ooops.  It read everything in as a string (`object`). Let's try this again being more specific using a dictionary to specify the type of `Status` and tell it to parse two of the columns as date related types:
df_uber = pd.read_csv("data/kaggle-uber-other-federal.csv",
                      parse_dates=['Date','Time'],
                      dtype={'Status':'category'})
df_uber.info()
df_uber.head(2)
# Ooops. That `Time` column got today's date added on. Convert to just time info:
df_uber['Time'] = df_uber['Time'].dt.time
df_uber.head(2)
# ### Exercise 1
#
# 1. Make sure that you can load the `kaggle-uber-other-federal.csv` data into a data frame  with the specified column type overrides for dates and categorical `Status`.
# 1. Display the `Date` column
# 1. Display the `Status` column
# ## Getting an initial sniff of the data
#
# The first step in EDA (of course, after you've decided what problem you're trying to solve) is to get a basic idea of what the data looks like. We've already seen how to get all of the data types:
df_cars.info()
# We also want to know the names of the columns which is provided by `info()` but we can ask for it explicitly, which is useful because we sometimes want to operate on the column names:
df_cars.columns, type(df_cars.columns) # gets a pandas `Index` object
df_cars.columns.values, type(df_cars.columns.values) # gets a numpy version
list(df_cars.columns) # get a simple Python list of column names
# Next, we want to know how many rows there are:
len(df_cars)
# The next thing to do is probably to get some summary statistics about the data, at least for numeric columns, which is what we have in the cars data set:
df_cars.describe()
mpg = df_cars['MPG']
mpg.mean(), mpg.sum()
df_cars['MPG'] / df_cars['ENG']  # element-wise division of column
# Here's a cool trick for formatting the output of dataframes in notebooks:
df_cars.describe().style.format("{:.2f}")
df_cars.head(5).style.format("{:.2f}")
# ### Exercise 2
#
# 1. Display the column names for `df_cars` data frame.
# 1. Display the number of rows
# 1. Display the average of the `WGT` column
# 1. Display the median of the `CYL` column
# 1. Display the ratio of `ENG` to `CYL`, indicating how much horsepower each car gets per cylinder
# ### Visualizing the raw data
#
# Summary statistics are great but they can often hide a great deal of variability, so I like to visualize the data to get the broad "shape" of the data.  We've seen histograms before so let's pull in our `histo` function and look at the distribution of car weight and engine power.
import matplotlib.pyplot as plt
# %config InlineBackend.figure_format = 'retina'
# incorporate function created in previous notebook
def histo(ax, X, color='#FEE08F'):
    n, bins, bpatches = ax.hist(X, color=color)
    # make it look good
    ax.spines['top'].set_visible(False)
    ax.spines['right'].set_visible(False)
    ax.spines['left'].set_linewidth(.5)
    ax.spines['bottom'].set_linewidth(.5)
    for rect in bpatches:
        rect.set_linewidth(.5)
        rect.set_edgecolor('grey')
    return n, bins, bpatches

fig, ax = plt.subplots(figsize=(3,2))  # make one subplot (ax) on the figure
histo(ax, df_cars['WGT'])
ax.set_xlabel("Weight")
ax.set_ylabel("Count")
plt.show() # not needed actually as plot shows up by default in notebook
# ### Exercise 3
#
# Repeat the same process for the `ENG` column to get a histogram.
# ## Series objects
#
# Let's look in more detail at the `Series` objects that I think of as representing columns of dataframes, but are actually useful on their own. For example, I might simply have some timeseries data I want to store. A series object is a one-dimensional sequence of values, all with the same data type. For example, I could have a series of integers, strings, or floating-point numbers. Here's a numeric series:
a = pd.Series([10,2.4,81,1005]) # make a series with four numbers of type float
a
len(a), a.dtype
b = pd.Series(['Xue','Mary','Ollie'])
print("Type is", b.dtype)
b
# There are lots of functions you can perform on series. For example, here are some operations for numerical columns:
a.min(), a.max(), a.mean(), a.sum(), a.count()
# The string-related functions or available using the `str` member of the series:
b.str.lower()
b.str.extract("([a-z])") # get first letter using regular expression
# ### Series indexes and indexing
#
# Each series object has an associated index that sort of names the elements of the series, just as the index of the data frame identifies the rows of the data (see below).  The index values are commonly integers or strings. If we don't specifically specify an index, the index is just a series of consecutive integers starting from zero. That behaves then just like a list in Python when we use the `[...]` index operator.
#
# We all understand the idea of a list of numbers in Python and we access the elements using the same syntax. The only difference is that the values inside the `[...]` can only be integers for Python lists. In pandas, the index type to use must be the same as the type of the index of the series.
#
# This shows the standard index is simply the integer positions:
a.index
# But, we can create a series with, for example, an index whose members are strings:
b = pd.Series([10,2.4,81,1005], index=['t','u','v','w'])
b
b.index
a[2], a[3]
b['u'], b['w']
# While convenient, this direct indexing makes it very easy to get confused if you are not very good with keeping types in your head or naming your variables with type information. It's better to use the more verbose but explicit `iloc` or `loc`:
a.iloc[2], a.loc[2], b.loc['u']
# We use the same notation to set values:
a.iloc[2] = 9999
a
b.loc['u'] = 9999
b
# ### Selecting multiple values with indexing
#
# We can even use **multiple values** when indexing:
b.loc[['u','w']]
list(b.loc[['u','w']])  # get a simple Python list of values
# Note the ambiguity of the square brackets, which mean both index operator and list literal in Python: `[['u','w']]`.
# ### Pandas matches up series index values
#
# The index of the series elements  makes column arithmetic (like `a+b`) a little more complex but much more sophisticated. For example, imagine that we have values associated with multiple states in the US, but where the values are not lined up according to state:
income = pd.Series([100,110,200,45], index=['WA','NV','CA','IA'])
taxes  = pd.Series([20,50,25,5], index=['IA','CA','NV','WA']) # diff order
print(income)
print(taxes)
income - taxes
# The key thing to notice here is that we don't have tax information for CA. When we add the two series together, pandas lines them up according to the index values so that things match up: WA to WA etc.
# ### Relational operators for series
#
# To give you a taste of the power of pandas,  we can even use relational operators to select elements from a series. Here's how to get all values less than 100 from `a`:
a.loc[a<100]
b.loc[b>100]
# Wow. How does that work? Relational operators like `a<100` give a series of Boolean values indicating where the expression is true.
a<100
# And, just like with other multiple index values, we can ask for the index of a list of Booleans (there must be a Boolean for each value in the series):
a.loc[[True,False,True,False]] # note the list within the index operators
# ###  Series look like dictionaries
#
# You can think of a series as a dictionary where the index values map to values in the series. But, unlike dictionaries, Series index values do not have to be unique:
x = pd.Series([1,2,3], index=['u','u','t'])
x
# So, here's how to get all `u` members from series `x`.
x.loc['u']
# To avoid using the index and use simply the integer position of the series value, remember you can use `iloc`:
x.iloc[0]
# ### Arithmetic with series
#
# As with numpy vectors, we can add series together and perform lots of other operations. Let's make some simple series of numbers and add them together:
a = pd.Series(range(5,10))
b = pd.Series(range(30,35))
print(a)
print(b)
a+b
a.values # get numpy array underlying the series
a.values + b.values  # using ".values" extract the underlying numpy array
(a+b).values
# Keep in mind: Any arithmetic involving a nan, results in a nan.
#
# We can also scale series very easily using arithmetic operators:
income*2
# ###  Exercise 4
#
# 1. Make a `Series` object with year values: `1991,1992,1993,1994,1995,1996,1997,1998,1999,2000`.
# 1. Print out how many total values there are using code not manual counting
# 1. Print all values less than 1997
# 1. Make another series with rainfall values `12.09,12.35,12.51,10.25,10.18,10.59,10.26,10.48,8.67,10.23`
# 1. Print out the years for which rainfall was less than 11.
# 1. Scale that rainfall series by 20% and print it out
# ## Something's missing
#
# Real-world data sets often have missing values. For example, if we are capturing ocean temperature with a series of devices bobbing in the ocean, some of them will be swallowed by sharks or be destroyed and we won't get any more data from them. Or,  if we are conducting a survey, some respondents will leave some entries blank. 
#
# We need a way to represent missing data. A common way to do this is to choose a sentinel value like -1 or a string like 'n/a' or 'missing'. I've even seen data set where 1000 was used as the missing value indicator for year values. Pandas formalizes missing values by representing them as "not a number", a special floating-point value, `np.nan`, from numpy that indicates the value is invalid and should not be treated as valid.
c = pd.Series([10,20,np.nan,40])
c
# When doing EDA, it's very common to ask if there are missing values, which we can do with `isnull()`:
c.isnull() # return a Boolean indicating whether the series value is nan or correct
# **A subtle but important point here**: `np.nan` has type `float` and, since all values in a series must be the same type, a series of integer values with at least one missing value causes the entire series to be floating-point. But, non-numeric series stay as `object`.
d = pd.Series(['hi','mom',np.nan])
d
d.isnull()
# BTW, `isna()` is a synonym:
# To get all of the non-missing data, we can use that function as an index value!
d.isna()
c.loc[~c.isnull()]  # ~c.isnull() means "not null" or "not np.nan"
d.loc[~d.isnull()]
# If you ever want a version of a series with the NaNs replaced with a fill value, there is a handy function for you:
c.fillna(-1)
# ###  Functions do different things with missing values
#
# To be very careful that you know how the various functions behave with respect to np.nan. Here are some samples:
c = pd.Series([10,20,np.nan,40])
c
len(c), c.count(), c.sum(), c.sum(skipna=False), np.sum(c), sum(c)
c * 3
# ###  Exercise 5
#
# 1. Make a `Series` object with values: `1991,1992,1993,1994,1995,np.nan,1997,1998,1999,2000`.
# 1. Print out the non-NAN values
# 1. Set the NaN value to be 1996 and print the series out again
# 1. Verify that `count()` on that series does not include the NaN
# ## Data frames
#
# A `DataFrame` object is a two-dimensional matrix with rows and columns just like a spreadsheet or database table.  Each column can have different data types, but all values within a column must be of the same data type; the columns behave like series objects.
#
# Data frames columns are ordered and the name-to-column mapping is stored in an index.  Data frames also have an index for the rows, just like a series has an index into the values of the series. So, a data frame has two indexes which lets us zero in, for example, on a specific element using row and column index values.
#
# Here is the car data again:
df_cars = pd.read_csv("data/cars.csv")
df_cars.head()
# To extract a column, we have seen the `[...]` index operator, which gets a series object:
type(df_cars['MPG'])
mpg = df_cars['MPG']
mpg
# Once we have a series object, we can use the series index to get elements (repetition to improve your retention):
mpg.iloc[3], mpg.loc[3]   # the default index is just the integer position like iloc
# <img src="images/redbang.png" width="30" align="left">So, when you index into a data frame, you get a column. When you index into a series, you get an element. That's why I recommend that you use `.loc[...]` initially until you are good at keeping track of datatypes, such as series versus dataframes. It's a well-known confusion point for new students.
#
# Let's look at the Uber data again and extract three columns. Please note the use of a list as an index value and the fact that we get a data frame back not a series because we asked for more than one column:
df_status = df_uber[['Date','Time','Status']]
print(type(df_status))
df_status.head(3)
# ### Dataframe indexing
# The columns form an index but there is also an index for the rows. The default index for the rows is just the integer position.
df_status.index
# To get a data frame row at a specific integer position as a Series, use `iloc`:
df_status.iloc[1]
df_status.iloc[2:4] # a slice of rows
df_status.iloc[:] # get all rows
df_status.iloc[0:len(df_status)] # SAME
df_status.iloc[:,1] # get all rows for column at position 1 as a Series
df_status.iloc[0:len(df_status),1] # SAME
# **To access a row using the index**, instead of by position, use `loc`:
df_status.loc[1] # default index is the position
# We can actually specify both a row and column using the `loc` operator:
df_status.loc[1,'Time'], df_status.loc[1,'Status']
# Another very useful operation is to set the index to one of the columns of the data frame:
df_status = df_status.set_index('Date')   # note that I reassign it to itself
df_status.head(5)
# Then we can conveniently ask for all rows with a specific date using the index:
df_status.loc['07/03/2014'].head(3)
# We can reset the index as well to make the index become a column again:
df_status = df_status.reset_index()
df_status.head(3)
# ### Functions on columns
#
# As we did in the section on series, we can apply functions to columns in a data frame. A very common task is to look at the unique values:
df_status['Status'].unique()
df_status['Date'].unique()
# Aside from computing the unique values, we can also ask for the count for each unique value:
df_status['Status'].value_counts() # like a histogram
# Because the columns are retrieved as series objects, we can do the same column arithmetic:
df_cars['MPG']/df_cars['WGT']
# ### Missing values in a data frame
# Returning to our friend the missing value, we can ask for a matrix of Boolean values indicating whether a specific row and column value is missing:
df_status.isnull().head(5)   # notice I am chaining the functions here isnull->head
df_status.isnull().sum()  # how many in each column are missing? it turns out none are missing:
# By default, the summation occurs down the rows, which pandas indicates with a default of `axis=0`:
df_status.isnull().sum(axis=0)
# We can sum down the columsn with `axis=1`:
df_status.isnull().sum(axis=1)  # how many in each row are missing?
df_status.isnull().any() # don't care about the count, just absence of values
df_status.isnull().any().any()
# ### Altering data frames
#
# We often want to alter the values in a data frame, such as when we clean up data in preparation for modeling. We can set individual values, rows, or entire columns.
df_status['junk'] = 0      # insert a new column and set to 0
df_status.head(3)
df_status['junk'] = 99     # overwrite a column with a single value
df_status.head(3)
df_status['junk'] = range(100,199)  # set a column to a new series of data
df_status.head(3)
df_status = df_status.drop('junk', axis=1)    # axis=1 means drop a column
df_status.head(3)
# Or you can use `del df_status['junk']` which might be a bit more obvious.
df_status.loc[0,'Status'] = 'Arrived'     # Update a single categorical value in one column
df_status.head(3)
# **WARNING**: Injecting a new column into, say, `df_status` is no problem as long as the data frame referred to is the entire data frame, and not a subset (sometimes called a view).  For example, the following gets an error because I'm updating a "slice" of the original data frame.
df_x = df_status.iloc[5:10]
df_x['foo'] = 0
# My usual solution is simply to make a copy so I get a copy and not a subset view of the original data frame:
df_x = df_status.iloc[5:10].copy()
df_x['foo'] = 0
df_x.head(2)
df_status.head(2)   # original does not have the 'foo' column
# Most of the time we are not deleting rows but we can do so using use `drop`.  Most likely we are asking for a subset of the data.  For example, here is how to find all trips that arrived:
df_status[df_status['Status']=='Arrived'].head(3)
# ### Exercise 6
#
# For the cars data set:
#
# 1. Use numpy's function `np.corrcoef(x,y)` to compute the correlation between a car's weight and the miles per gallon; that function returns a matrix of x with x, x with y, etc... so the diagonal will always be correlation 1.0.  You should see a strongly negative correlation
# 1. Display the records for all 8 cylinder cars
# 1. Create a new column called `ENG2WGT` that has the engine to weight ratio
#
# From the `df_uber` data frame:
#
# 1.  Create a new data frame containing `Time`, `Status`, and `PU_Address` columns
# 1.  Set the index of that data frame to `Time`
# 1.  Display records at positions between 10 and 15 inclusively
# 1.  Display the `PU_Address` for records whose index is `'2020-09-10 07:30:00'`
# 1.  Reset the data frame so that `Time` is a column again.
# 1.  Create a new column called `year` that has the year extracted from the `Time` column using `.dt.year`
# ### Exercise 7
#
# Make a list of all of the key operations we performed in this notebook. For example, you can give an example of each and then a brief description. The goal here is to get these commands into your brain and the only way to do that is repetition. If you cut-and-paste, you will not learn very quickly at all!