<jupyter_start><jupyter_text>Runtime Dependencies: Must Run First!<jupyter_code>import numpy as np
import pandas as pd

# ### Bonus: Multiple Outputs Per Cell
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"<jupyter_output><empty_output><jupyter_text>Module 5: Pandas — SQL Functionality In the previous notebook, you had an intoduction to Pandas, Series, DataFrames, and basic manipulations including broadcasting and column-wise operations.Now, we're going to add operations that are considered SQL-like for tabular data.Let's have an overview:- 5.8 Aggregate Functionality- 5.9 Filters- 5.10 Group By- 5.11 Summarize- 5.12 Merging Tables- 5.13 Window: Shift- 5.14 Window: Rolling- 5.15 Window: ExpandingLet's get into it!  Module 5.8: Aggregate Functions Eariler, you learned how to calculate new columns based upon existing data.Now, we can start doing the opposite: calculating column statistics!In NumPy, we covered a bunch of array calculation methods for summary statistics, like mean, max, min, sum, product, variance, and standard deviation. We use these in a very similar way!Let's import our vaccination data, as of Feb8 2021.<jupyter_code>loc = "https://raw.githubusercontent.com/mhall-simon/python/main/data/covid/covid19_vaccinations_in_the_united_states.csv"

df = pd.read_csv(loc, header=2, index_col="State/Territory/Federal Entity")
df<jupyter_output><empty_output><jupyter_text>Aggregate Functions on DataFrame Let's take a minute to discuss what the mean of each column means!<jupyter_code>df.mean()<jupyter_output><empty_output><jupyter_text>Aggregate Functions on Subset of Data Just like with broadcasting and element-wise operations, our indexing comes back!Let's calculate the standard deviation of vaccines delivered per 100K:<jupyter_code>df['Delivered per 100K'].std()<jupyter_output><empty_output><jupyter_text>Let's calculate how many people in the United States have at least one dose (as of Feb8):<jupyter_code>df['People with 1+ Doses'].sum()<jupyter_output><empty_output><jupyter_text>Let's calculate how many people in the United States have both their doses (as of Feb8):<jupyter_code>df['People with 2 Doses'].sum()<jupyter_output><empty_output><jupyter_text>Module 5.9: Filtering Data Sometimes, we don't want all of our data!This is where filtering comes in handy!The general rule for filtering is that we need to have a logical statement that creates True/False values, and then the filter selects out the True rows.The general syntax looks like:```pythondf[predicate]```Where the predicate is any boolean expression that can be mapped onto the underlying DataFrame.  National COVID Dataset — As of 1-12-2021 https://github.com/mhall-simon/python/blob/main/data/covid/national-history-1-12-2020.csv<jupyter_code>loc = "https://raw.githubusercontent.com/mhall-simon/python/main/data/covid/national-history-1-12-2020.csv"

df = pd.read_csv(loc, parse_dates=True, index_col=0)
df.head()<jupyter_output><empty_output><jupyter_text>Filtering with Single Argument This operation is going to select the subset of data where daily deaths is greater than or equal to 3500.<jupyter_code>df[df.deathIncrease >= 3500]<jupyter_output><empty_output><jupyter_text>Let's look at the predicate closer! It's a series of True/False values, and we extract the true values.<jupyter_code>df.deathIncrease >= 3500<jupyter_output><empty_output><jupyter_text>Filtering with Multiple Arguments: And<jupyter_code>df[(df.index.year==2021) & (df.positiveIncrease >= 200000)]<jupyter_output><empty_output><jupyter_text>Filtering with Multiple Arguments: Or<jupyter_code>df[(df.deathIncrease >= 3000) | (df.positiveIncrease >= 300000)]<jupyter_output><empty_output><jupyter_text>Module 5.10: Groupby Functionality In databases, we often need to collapse our data vertically and calculate useful summary statistics and calculate potential categorical differences.Earlier, when we covered aggregate functions, we did something really similar! Except, that was for the entire column.Now, we can create groups within columns, and calculate aggregates for those groups!There's a lot of things we can potentially aggregate on:**Time Based:**- Months- Quarters- Years**Categorical:**- State- Country- Store- ColorAnd more!  Groupby FormulaThe groupby call in Pandas has a few considerations:1. DataFrame to Group2. Arguments to Group On3. Slice of DataFrame to Return4. Method to Calculate Summary Statistic  State-By-State COVID Data - As of 12-29-2020<jupyter_code>loc = "https://raw.githubusercontent.com/mhall-simon/python/main/data/covid/all-states-history-12-29-2020.csv"

df = pd.read_csv(loc, parse_dates=True, dtype={'dataQualityGrade': str}, index_col=0)
df.head(10)<jupyter_output><empty_output><jupyter_text>Groupby State Let's focus in on the first groupby argument, state. This is taking each state, and collapsing the data down by that category. To start, each state has a daily entry (about 300+ per state), and at the end, each state will have just one row!Also, I am returning the "deathIncrease" column from the original dataset, and am summing the entries that are grouped together. *(This calculates the cumulative deaths per state)*<jupyter_code>df.groupby('state')['deathIncrease'].sum()<jupyter_output><empty_output><jupyter_text>Groupby Month-Year Let's get summary statistics for each month that has passed since the pandemic began last January.Since the DateTime information is in the index, we can do some cool things easily!If we just did month, January 2021 and January 2020 would be combined into one!<jupyter_code>df.groupby([df.index.month, df.index.year])[['positiveIncrease','deathIncrease']].sum()<jupyter_output><empty_output><jupyter_text>Combining Filters with Groupby We can also combine filters with our groupby!Just keep in mind that the order matters! If data is filtered out, you cannot use that in aggregation.It'll depend upon what you want to do!Let's filter out New York's COVID data, and then run a Month-Year groupby.<jupyter_code>filt = df[df.state=='NY']

filt.groupby([filt.index.month, filt.index.year])[['positiveIncrease','deathIncrease']].sum()<jupyter_output><empty_output><jupyter_text>Now let's do this the opposite way: run a Month-Year-State groupby and then select out NYS.Step 1: Complex Group By<jupyter_code>grpd = df.groupby([df.index.month, df.index.year, df.state])[['positiveIncrease','deathIncrease']].sum()
grpd<jupyter_output><empty_output><jupyter_text>Step 2: Filter From Multi-Indexed DataFrame<jupyter_code>grpd[grpd.index.get_level_values(2)=='NY']<jupyter_output><empty_output><jupyter_text>Note: The inner is still a predicate array of True/False!<jupyter_code>grpd.index.get_level_values(2)=='NY'<jupyter_output><empty_output><jupyter_text>Module 5.11: Summary Functions In Pandas, summary functions are what we want returned and calculated from the grouping operation.Above, you saw an example of returning a column or two with a NumPy array operation being performed on them:```python['deathIncrease'].sum()[['positiveIncrease','deathIncrease']].sum()```These work, but sometimes we want more complex calculations and data! Back to our state by state covid data!<jupyter_code>loc = "https://raw.githubusercontent.com/mhall-simon/python/main/data/covid/all-states-history-12-29-2020.csv"

df = pd.read_csv(loc, parse_dates=True, dtype={'dataQualityGrade': str}, index_col=0)
df.head(10)<jupyter_output><empty_output><jupyter_text>Simple Aggregation — Multiple Calculations on One Column<jupyter_code>df.groupby([df.index.month, df.index.year]).agg({'positiveIncrease': ['sum','max','std']})<jupyter_output><empty_output><jupyter_text>Let's take a second to think about what each box means.Sum - cumulate cases for each monthMax - most cases in singe day by a single stateStd - standard deviation of new daily cases, time series separated by state  Complex Aggregation — Multiple Calculations on Multiple Columns I'm breaking this one up to make it easier to see! Otherwise functionally everything is the same:```pythondf.groupby([df.index.month, df.index.year]).agg({'positiveIncrease': ['sum','max','std'],'deathIncrease': ['sum','max','std']})```It would look like this above when it's all in one line!<jupyter_code>groupby_obj = df.groupby([df.index.month, df.index.year])

mapping = {'positiveIncrease': ['sum','max','std'], 'deathIncrease': ['sum','max','std']}

groupby_obj.agg(mapping)<jupyter_output><empty_output><jupyter_text>Module 5.12: Merging Tables Together We often have data located in multiple tables, spreadsheets, or flat files that is related to each other.We separate this data so that it's easier to import, manage, or analyze. However, we often want to recombine these into one.There's four main ways to combine data:1. Concat: Join Datasets Based Upon Axis, Add Rows or Columns2. Append: Special Case of Concat (Add Rows, Outer Join)3. Join: Combine Datasets on Common Key Using Indicies2. Merge: Combine Datasets on Common Key Using Indicies / Columns (Left on... Right on... Left Index... Right Index...)These may seem similar at first, but they're very different operations. The best is when you need to perform multiple operations on a highly-segmented dataset!In this overview, I am showing examples of Concat and Merge. *Append is a special case of Concat and Join is a subset of Merge*  Concatenating Tables Together In the following Excel sheet, there's a sheet for each month (0,1,2).I'm wrapping the functionality in a function so we can make it fast and easy to use!This first example is the same between concatenate and append!<jupyter_code>loc="https://github.com/mhall-simon/python/blob/main/data/misc/inventory-sales-3months.xlsx?raw=true"

df = pd.read_excel(loc, sheet_name=0)
df
def pandas_concat(df, loc, n):
    new = pd.read_excel(loc, sheet_name=n)
    return pd.concat([df,new])
df = pandas_concat(df, loc, 1)
df = pandas_concat(df, loc, 2)
df<jupyter_output><empty_output><jupyter_text>Merge Operations Above, you saw how to concatenate data into a DataFrame, which makes our sets longer.However, we sometimes have common-keyed data in multiple sheets that we want together for analysis.A very common example of this is a CRM. We can have multiple tables of data all tied together using a common ID number. For analytical predictions or to run a regression, we may need all of the data in one place.There's 4 types of merge operations:1. Outer - Return all rows, regardless of matches2. Inner - Only return rows that had keys match3. Left - Return only left table rows, regardless of matches4. Right - Return only right table rows, regardless of matchesThese differences will determine what is returned from the merge operation!I skip over right merge, as right merge is the same as a left merge if you flip the two arguments!Below, I am importing an Excel spreadsheet with 3 small CRM tables.<jupyter_code>loc = "https://github.com/mhall-simon/python/blob/main/data/misc/small-crm.xlsx?raw=true"

contacts = pd.read_excel(loc, sheet_name=0, index_col=0)
contacts

business = pd.read_excel(loc, sheet_name=1, index_col=0)
business

pipeline = pd.read_excel(loc, sheet_name=2, index_col=0)
pipeline<jupyter_output><empty_output><jupyter_text>Outer Merge Let's merge together our contact and business information using an outer merge first:<jupyter_code>pd.merge(contacts, business, left_on="ID", right_on="Owner", how="outer")<jupyter_output><empty_output><jupyter_text>Inner Merge Now let's change it to an inner merge. This will only return rows with matches, so Olive and Taylor drop out.<jupyter_code>pd.merge(contacts, business, left_on="ID", right_on="Owner", how="inner")<jupyter_output><empty_output><jupyter_text>Left Merge Let's now have business information as the left table, and merge contact information on the right hand side.We're then going to take that data to merge everthing together (all three tables)<jupyter_code>business_contacts = pd.merge(business, contacts, left_on="Owner", right_index=True, how="left")
business_contacts
merged = pd.merge(pipeline, business_contacts, left_on="Business", right_index=True, how="left")
merged<jupyter_output><empty_output><jupyter_text>Bonus Box: We Can Filter / Groupby & Work With This Too! This following line of code calculates revenue by company, where the stage is beyond negotiation.<jupyter_code>merged[merged.Stage!="Negotiation"].groupby('Business')['Revenue'].sum()<jupyter_output><empty_output><jupyter_text>You're the manager and need to give weekly progress updates for your clients work in progress. Let's identify who you need to call today:Since all the fields are the same per business, we can pull the contact info with max. (As strings also have max / min properties)<jupyter_code>merged[merged.Stage=="Work In Progress"].groupby('Business')[['Name','Phone']].max()<jupyter_output><empty_output><jupyter_text>Module 5.13: Window - Shift In Excel, you can easily reference other cells above or below in Time Series data for calculations.We can do the same thing with Pandas using the built-in shift functionality!A positive shift integer references above your current row, and a negative shift integer references below.  Calculating Simple Returns Using Shift With the shift function, we're able to calculate simple returns!The simple return assumes that you purchased the stock at the close of the previous period, and sell at the end of the current period.Keep In Mind: The Order of Your Data Set Impacts What Way we need to shift! Also, shift operations on unsorted data may not work as expected.<jupyter_code>## Import & Sort Data
loc = "https://raw.githubusercontent.com/mhall-simon/python/main/data/misc/TSLA.csv"
df = pd.read_csv(loc)
df.sort_index(inplace=True)

df['Previous'] = df.Close.shift(1)
df['Return'] = (df.Close - df.Close.shift(1)) / df.Close.shift(1)
df<jupyter_output><empty_output><jupyter_text>Modules 5.14 - 5.15: How Do You Calculate Average? The next 2 modules cover some important window functions, which are essential for data science if you want to start calculating more complex statistical moments in our data.As an example, I'm going to walk through "What is the average new daily case increase in the United States?" There's a few ways we can approach this!- Average From Entire Dataset- Rolling Average of Last 14 Days- Expanding Window From Beginning to Date<jupyter_code>loc = "https://raw.githubusercontent.com/mhall-simon/python/main/data/covid/national-history-1-12-2020.csv"

df = pd.read_csv(loc, index_col=0)
df = df[['positiveIncrease']]
df.sort_index(inplace=True)

# Average From All Data:
ave = df.positiveIncrease.mean()
df['Average'] = ave

# 14 Day Rolling Average
df['14DayRolling'] = df.positiveIncrease.rolling(14).mean()

# Expanding Average
df['Expanding'] = df.positiveIncrease.expanding().mean()

# Quick Plot of Data:
df[['Average','14DayRolling','Expanding']].plot()<jupyter_output><empty_output><jupyter_text>Thought exercise: What average do you think is the most important for fighting covid?  Module 5.14: Window - Rolling Rolling allows us to move a fixed window as we keep progressing through our time series data!This allows us to calculate things like what is the 7 day rolling average, and similar window functions!It's pretty easy to use. We just subset a series of data from our DataFrame, and then we can apply the rolling method.Besides the length of the window, the other main keyword argument is the minimum number of periods. By default, this is generally set to the size of the window. Check out the docs for some specialized windows and changes you can do beyond the basics!<jupyter_code>loc = "https://raw.githubusercontent.com/mhall-simon/python/main/data/covid/national-history-1-12-2020.csv"

df = pd.read_csv(loc, index_col=0)
df = df[['positiveIncrease']]
df.sort_index(inplace=True)
df['Rolling7Day'] = df.positiveIncrease.rolling(7).mean()
df.head(10)
df['Rolling7DayNoMin'] = df.positiveIncrease.rolling(7, min_periods=1).mean()
df.head(10)<jupyter_output><empty_output><jupyter_text>Module 5.15: Window - Expanding Expanding is another type of window! This one goes from the current row in your time series data to the beginning.These are very useful for year-to-date and beginning-current calculations.These have a very similar syntax to the above window functions.Keep in mind that this one will slowly approach the sample average of the series if using the mean function!<jupyter_code>loc = "https://raw.githubusercontent.com/mhall-simon/python/main/data/covid/national-history-1-12-2020.csv"

df = pd.read_csv(loc, index_col=0)
df = df[['positiveIncrease']]
df.sort_index(inplace=True)
df['ExpandingAverage'] = df.positiveIncrease.expanding().mean()
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09519827998088867, 'fraction_numerical': 0.014512661251791686, 'mean_word_length': 6.119472789115647, 'pattern_counts': {'":': 0, '<': 129, '<?xml version=': 0, '>': 134, 'https://': 11, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '32', 'dir': 'jupyter-structured-clean-dedup', 'id': '235688', 'license': 'MIT', 'n_tokens_mistral': 5258, 'n_tokens_neox': 4884, 'n_words': 1807, 'path': 'modules/module-05/mod5_nb2_pandas_sql_func.ipynb', 'repo_name': 'mhall-simon/python', 'size': '16744'}