<jupyter_start><jupyter_text>Aggregating and Combining `pandas` DataFrames<jupyter_code>import pandas as pd
import numpy as np
import requests as rq
from sklearn.preprocessing import OneHotEncoder
from zipfile import ZipFile<jupyter_output><empty_output><jupyter_text>Learning Goals - Use GroupBy objects to organize and aggregate data- Create pivot tables from DataFrames- Combine DataFrames by merging and appending  We'll work with the Austin Animal Center dataset and with data from King County's Department of Assessments (Seattle housing data).  Austin Animal Center Data<jupyter_code>data = rq.get('https://data.austintexas.gov/resource/9t4d-g238.json').text
animals = pd.read_json(data)
animals.head()<jupyter_output><empty_output><jupyter_text>Aggregating over DataFrames: `.groupby()` Those of you familiar with SQL have probably used the GROUP BY command. (And if you haven't, you'll see it very soon!) Pandas has this, too.The `.groupby()` method is especially useful for aggregate functions applied to the data grouped in particular ways.<jupyter_code>animals.groupby('animal_type')
animals.columns<jupyter_output><empty_output><jupyter_text>We can group by multiple columns, and also return a DataFrameGroupBy objectNotice the object type [DataFrameGroupBy](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html) object.   `.groups` and `.get_group()`<jupyter_code>animals.groupby(['animal_type', 'outcome_type'])
# This retuns each group indexed by the group name: I.E. 'Bird', along with the row indices of each value
animals.groupby('animal_type').groups<jupyter_output><empty_output><jupyter_text>Once we know we are working with a type of object, it opens up a suite of attributes and methods. One attribute we can look at is groups.<jupyter_code># Once we know the group indices, we can return the groups using those indices.
animals.groupby('animal_type').get_group('Dog')<jupyter_output><empty_output><jupyter_text>Multi-Indexing<jupyter_code># Same goes for multi index groupbys
animal_outcome = animals.groupby(['animal_type', 'outcome_type'])
animal_outcome.groups
# animal_outcome.groups is a dictionary, so we can access the group names using keys()
animal_outcome.groups.keys()
# We can then get a specific group, such as Cats that were adopted
animal_outcome.get_group(('Cat', 'Adoption'))<jupyter_output><empty_output><jupyter_text>Aggregating Once again, as we will see in SQL, groupby objects are intended to be used with aggregation. In SQL, we will see that our queries that include GROUP BY require aggregation performed on columns.We can use `.sum()`, `.mean()`, `.count()`, `.max()`, `.min()`, etc. Find a list of common aggregations [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html).<jupyter_code>animals.groupby('animal_type').count()<jupyter_output><empty_output><jupyter_text>ExerciseUse `.groupby()` to find the most recently born of each (main) animal type.     Answer    animals.groupby('animal_type')['date_of_birth'].max()      Pivoting a DataFrame  `.pivot_table()` Those of you familiar with Excel have probably used Pivot Tables. Pandas has a similar functionality.Grouping by two different columns can be very helpful.<jupyter_code>animals.groupby(by=['outcome_type', 'sex_upon_outcome']).agg(len)<jupyter_output><empty_output><jupyter_text>But it has the unsavory side effect of creating a two-level index. This can be a good time to use `.pivot_table()`.(There is also a `.pivot()`. For the somewhat subtle differences, see [here](https://stackoverflow.com/questions/30960338/pandas-difference-between-pivot-and-pivot-table-why-is-only-pivot-table-workin).)  Example<jupyter_code>df = pd.DataFrame({"sex": ["male", "male", "male", "male", "male",
                          "female", "female", "female", "female"],
                    "num_puppies": ["one", "one", "one", "two", "two",
                          "one", "one", "two", "two"],
                    "breed": ["terrier", "retriever", "retriever", "terrier",
                          "terrier", "retriever", "terrier", "terrier",
                          "retriever"],
                    "past_owners": [1, 2, 2, 3, 3, 4, 5, 6, 7],
                    "family_members": [2, 4, 5, 5, 6, 6, 8, 9, 9]})
df
# This first example aggregates values by taking the sum.

table = pd.pivot_table(df, values='past_owners', index=['sex', 'num_puppies'],
                     columns=['breed'], aggfunc=np.sum)
table<jupyter_output><empty_output><jupyter_text>Back to Austin animals<jupyter_code>animals.pivot_table(index='outcome_type', columns='sex_upon_outcome', aggfunc=len)<jupyter_output><empty_output><jupyter_text>ExerciseUse `.pivot_table()` to add up the number of my tasks by category. Hint: Use `sum()` as your aggregating function.<jupyter_code>tasks = pd.DataFrame({'category': ['house', 'house', 'school', 'school'],
                      'descr': ['kitchen', 'laundry', 'git', 'Python'],
                      'priority': [2, 3, 4, 1], 'num_tasks': [2, 1, 2, 3]})

tasks<jupyter_output><empty_output><jupyter_text>Answer    tasks.pivot_table(values='num_tasks', index='category', aggfunc=sum)      Methods for Combining DataFrames: `.join()`, `.merge()`, `pd.concat()`  `.join()`<jupyter_code>toy1 = pd.DataFrame([[63, 142], [33, 47]], columns=['age', 'HP'])
toy2 = pd.DataFrame([[63, 100], [33, 200]], columns=['age', 'MP'])

toy1
toy2
# We can't just join these as they are, since we haven't specified our suffixes.

toy1.join(toy2)
toy1.join(toy2, lsuffix='1', rsuffix='2')<jupyter_output><empty_output><jupyter_text>If we don't want to keep both, we could set the overlapping column as the index in each DataFrame:<jupyter_code>toy1.set_index('age').join(toy2.set_index('age'))<jupyter_output><empty_output><jupyter_text>For more on this method, check out the [doc](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html)!  `.merge()` Or we could use `.merge()`:<jupyter_code>toy1.merge(toy2)
ds_chars = pd.read_csv('data/ds_chars.csv', index_col=0)
ds_chars
states = pd.read_csv('data/states.csv', index_col=0)
states<jupyter_output><empty_output><jupyter_text>The `how` ParameterThis parameter in both `.join()` and `.merge()` tells the compiler what sort of join to effect. We'll cover this in detail when we discuss SQL.<jupyter_code>ds_chars.merge(states,
               left_on='home_state',
               right_on='state',
               how='inner')
ds_chars.merge(states,
               left_on='home_state',
               right_on='state',
               how='outer')<jupyter_output><empty_output><jupyter_text>`pd.concat()` This method takes a *list* of pandas objects as arguments.<jupyter_code>ds_full = pd.concat([ds_chars, states])
ds_full<jupyter_output><empty_output><jupyter_text>`pd.concat()`–– and many other pandas operations –– make use of an `axis` parameter. For this particular method I need to specify whether I want to concatenate the DataFrames *row-wise* (`axis=0`) or *column-wise* (`axis=1`). The default is `axis=0`, so let's override that!<jupyter_code>ds_full = pd.concat([ds_chars, states], axis=1)
ds_full<jupyter_output><empty_output><jupyter_text>King County Assessments As data scientists, we want to build a model to predict the sale price of a house in Seattle in 2019, based on its square footage. We know that the King County Department of Assessments has comprehensive data available on real property sales in the Seattle area. We need to prepare the data.  First, get the data! Go [here](https://info.kingcounty.gov/assessor/DataDownload/default.aspx) and download two files: "Real Property Sales" and "Residential Building". Then unzip them. (Or you can run the cells below if you prefer.)<jupyter_code># %%bash
# cd data
# curl -o property_sales.zip https://aqua.kingcounty.gov/extranet/assessor/Real%20Property%20Sales.zip 
# %%bash
# cd data
# curl -o res_bldg.zip https://aqua.kingcounty.gov/extranet/assessor/Residential%20Building.zip 
# zf = ZipFile('data/property_sales.zip', 'r')
# zf.extractall('data')
# zf.close()
# zf = ZipFile('data/res_bldg.zip', 'r')
# zf.extractall('data')
# zf.close()
# You'll need to use a new encoding here. List of all encodings here:
# https://docs.python.org/3/library/codecs.html#standard-encodings

# Both of these csv files have many columns, so we'll just pre-select
# which ones we want to use.

sales_df = pd.read_csv('/Users/gdamico/Downloads/EXTR_RPSale.csv',
                       encoding='latin-1',
                       usecols=['Major', 'Minor', 'DocumentDate', 'SalePrice'])
sales_df.info()
bldg_df = pd.read_csv('~/Downloads/EXTR_ResBldg.csv',
                     usecols=['Major', 'Minor', 'SqFtTotLiving', 'ZipCode'])
bldg_df.info()
sales_data = pd.merge(sales_df, bldg_df, on=['Major', 'Minor'])
sales_data.head()
sales_data.info()<jupyter_output><empty_output><jupyter_text>We can see right away that we're missing ZIP codes for many of the sales transactions.<jupyter_code>sales_data.loc[sales_data['ZipCode'].isna()].head()<jupyter_output><empty_output><jupyter_text>ExerciseWhat percentage of housing records are missing ZIP codes?     Answer    sales_data['ZipCode'].isna().sum() / sales_data.shape[0]     Let's drop the rows with missing zip codes.<jupyter_code>sales_data = sales_data.loc[~sales_data['ZipCode'].isna(), :]

sales_data.head()<jupyter_output><empty_output><jupyter_text>Time Permitting: Data Cleaning with Pandas  1. Investigate and drop rows with invalid values in the SalePrice and SqFtTotLiving columns.     One possible answer here    sales_data = sales_data[sales_data['SalePrice'] > 10000]      2. Investigate and handle non-numeric ZipCode values Can you find a way to shorten ZIP+4 codes to the first five digits?<jupyter_code>def is_integer(x):
    try:
        _ = int(x)
    except ValueError:
        return False
    return True

sales_data.loc[sales_data['ZipCode'].apply(is_integer) == False, 'ZipCode'].head()<jupyter_output><empty_output><jupyter_text>One possible answer here    def five_digit_ZIP(x):    try:        return int(str(x)[:5])    except:        return xsales_data['ZipCode'] = sales_data['ZipCode'].map(five_digit_ZIP)sales_data = sales_data.loc[sales_data['ZipCode'].apply(is_integer) == True, :]sales_data['ZipCode'] = sales_data['ZipCode'].map(int)      3. Add a column for PricePerSqFt     Answer here    sales_data['PricePerSqFt'] = sales_data['SalePrice'] / sales_data['SqFtTotLiving']      4. Subset the data to 2021 sales only. We can assume that the DocumentDate is approximately the sale date.     Answer here    sales_data['DocumentDate'] = pd.to_datetime(sales_data['DocumentDate'])sales_data = sales_data.loc[sales_data['DocumentDate'] > '12/31/2020']      5. What is the mean price per square foot for a house sold in Seattle in 2021?     Answer here    sales_data['PricePerSqFt'].mean()      Level Up: `pandas.set_option()` We can adjust how `pandas` works by setting options in advance.  Block Scientific Notation For example, suppose we want to prevent numbers from being displayed in scientific notation.<jupyter_code>df = pd.DataFrame([[1e9, 2e9], [3e9, 4e9]])
df<jupyter_output><empty_output><jupyter_text>Then we can use:<jupyter_code>pd.set_option('display.float_format', '{:.2f}'.format)

df<jupyter_output><empty_output><jupyter_text>See More Rows Or suppose we want `pandas` to show more rows.<jupyter_code>df2 = pd.DataFrame(np.array(range(100)))
df2<jupyter_output><empty_output><jupyter_text>In that case we can use:<jupyter_code>pd.set_option('display.max_rows', 100)

df2<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1275567446275999, 'fraction_numerical': 0.011823595408647622, 'mean_word_length': 4.954779033915725, 'pattern_counts': {'":': 5, '<': 101, '<?xml version=': 0, '>': 103, 'https://': 9, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '167774', 'license': 'MIT', 'n_tokens_mistral': 4001, 'n_tokens_neox': 3736, 'n_words': 1039, 'path': 'Phase_1/ds-aggregating_combining_dataframes-nbz32-main/aggregating_combining_dataframes.ipynb', 'repo_name': 'BenJMcCarty/ds-east-042621-lectures', 'size': '11587'}