<jupyter_start><jupyter_text>Introduction to PandasThis notebook provides an introduction to the `pandas` package. The content borrows heavily from the book *Data Science Handbook*, which was written by <NAME> and is available at https://jakevdp.github.io/PythonDataScienceHandbook/ (accessed 12/17/2019).The following table of contents lists the topics discussed in this notebook. Clicking on any topic will advance the notebook to the associated area. Table of Contents 1. [Pandas](pandas)    1.1 [Pandas Objects](Pandas_Objects)    1.2 [Importing Data](Importing_Data_with_Pandas)    1.3 [Indexing and Selecting Data](Indexing_and_Selecting_with_pandas)    1.4 [Handling Missing Data](Pandas_Missing_Data)    1.5 [Grouping and Aggregating Data](Grouping_and_Aggregation_with_Pandas)    1.6 [Pivot Tables](Pivot_Tables)    1.7 [Combining Datasets](Combining_Datasets)     DisclaimerThis notebook is by no means a comprehensive resource for the `pandas` package. Also, it is important to realize that the Python language and the available packages will continue to evolve. That being said, the objects, functions, and methods described in this notebook may one day change. If changes occur, areas of this notebook that use deprecated features may cease to work and will need to be revised or omitted.  Pandas The `pandas` package is one of the most popular Python tools for data management and manipulation. `pandas` is built *on top* of `numpy`. Thus, much of the functionality and methods that are available in `numpy` are also available in `pandas`. From https://en.wikipedia.org/wiki/Pandas_(software) (accessed 1/7/2018):>Pandas is a software library written for the Python programming language for data manipulation and analysis. In particular, it offers data structures and operations for manipulating numerical tables and time series. It is free software released under the three-clause BSD license. The name is derived from the term "panel data", an econometrics term for multidimensional, structured data sets. Major features of the library are:> - DataFrame object for data manipulation with integrated indexing.> - Tools for reading and writing data between in-memory data structures and different file formats.> - Data alignment and integrated handling of missing data.> - Reshaping and pivoting of data sets.> - Label-based slicing, fancy indexing, and subsetting of large data sets.> - Data structure column insertion and deletion.> - Group by engine allowing split-apply-combine operations on data sets.> - Data set merging and joining.> - Hierarchical axis indexing to work with high-dimensional data in a lower-dimensional data structure.> - Time series-functionality: Date range generation and frequency conversion, moving window statistics, moving window linear regressions, date shifting and lagging.> The library is highly optimized for performance, with critical code paths written in Cython or C.The following code block uses the *import - as* approach to import Pandas. The alias *pd* is a standard convention. We also import NumPy.[Back to Table of Contents](Table_of_Contents)<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Pandas Objects Pandas objects can be thought of as enhanced NumPy arrays. The two main Pandas objects are:1. The Pandas `Series`, which is a one-dimensional array of indexed data, and 2. The Pandas `DataFrame`, which is two-dimensional array of indexed data.The fact that Pandas objects are indexed allows us to lookup and access values by the index. Also, in contrast to NumPy arrays which are for storing numeric data, Pandas objects can store pretty much any Python data type.[Back to Table of Contents](Table_of_Contents) Pandas `Series` ObjectTo demonstrate the Pandas `Series` object, suppose we have the following 24 data points for sales over a 24 month period stored in a list object named `sales`. Also, we have a list object named `month` that stores the month that the sales occured in using the date format YYYY-MM.[Back to Table of Contents](Table_of_Contents)<jupyter_code>month = ['2015-01-31', '2015-02-28', '2015-03-31', '2015-04-30',
         '2015-05-31', '2015-06-30', '2015-07-31', '2015-08-31',
         '2015-09-30', '2015-10-31', '2015-11-30', '2015-12-31',
         '2016-01-31', '2016-02-29', '2016-03-31', '2016-04-30',
         '2016-05-31', '2016-06-30', '2016-07-31', '2016-08-31',
         '2016-09-30', '2016-10-31', '2016-11-30', '2016-12-31']

sales = [872, 873, 990, 1129, 969, 964, 1148, 614, 1068, 1138, 1057,  748,
         1255, 1016, 1058, 1064, 1229, 1060, 1144, 1119, 1188,  817,  638, 1205]<jupyter_output><empty_output><jupyter_text>The following code block uses the two lists to create a Pandas `Series` that is stored in a variable named `sales_series`.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_series = pd.Series(sales, index = month)

print(f'The sales_series series is\n {sales_series}')<jupyter_output>The sales_series series is
 2015-01-31     872
2015-02-28     873
2015-03-31     990
2015-04-30    1129
2015-05-31     969
2015-06-30     964
2015-07-31    1148
2015-08-31     614
2015-09-30    1068
2015-10-31    1138
2015-11-30    1057
2015-12-31     748
2016-01-31    1255
2016-02-29    1016
2016-03-31    1058
2016-04-30    1064
2016-05-31    1229
2016-06-30    1060
2016-07-31    1144
2016-08-31    1119
2016-09-30    1188
2016-10-31     817
2016-11-30     638
2016-12-31    1205
dtype: int64<jupyter_text>The following code block shows how we can access individual values, or a range of values, in the series.[Back to Table of Contents](Table_of_Contents)<jupyter_code># Get the sales for June 2015
sales_series['2015-06-30']

# Get the sales for June 2015 through December 2015 
sales_series['2015-06-30':'2015-12-31']<jupyter_output><empty_output><jupyter_text>Using NumPy-like syntax, we can do computations on `Series` objects. The following code blocks provides examples.[Back to Table of Contents](Table_of_Contents)<jupyter_code># Compute the total sales for June 2015 through December 2015 
sales_series['2015-06-30':'2015-12-31'].sum()
# Compute the mean sales for June 2015 through December 2015 
sales_series['2015-06-30':'2015-12-31'].mean()
# Compute the maximum sales for June 2015 through December 2015 
sales_series['2015-06-30':'2015-12-31'].max()<jupyter_output><empty_output><jupyter_text>Pandas `DataFrame` ObjectWe will now look at Pandas `DataFrame` object. The following cell provides four lists that provide information on the sales of two products, A and B, in three stores, W1, E1, and E2, over 10 years.[Back to Table of Contents](Table_of_Contents)<jupyter_code>stores_list = ['W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 
               'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 'W1', 
               'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 
               'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 'E1', 
               'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 
               'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2', 'E2']

dates_list = ['12/31/2007', '12/31/2008', '12/31/2009', '12/31/2010', '12/31/2011', '12/31/2012', 
              '12/31/2013', '12/31/2014', '12/31/2015', '12/31/2016', '12/31/2007', '12/31/2008', 
              '12/31/2009', '12/31/2010', '12/31/2011', '12/31/2012', '12/31/2013', '12/31/2014', 
              '12/31/2015', '12/31/2016', '12/31/2007', '12/31/2008', '12/31/2009', '12/31/2010', 
              '12/31/2011', '12/31/2012', '12/31/2013', '12/31/2014', '12/31/2015', '12/31/2016', 
              '12/31/2007', '12/31/2008', '12/31/2009', '12/31/2010', '12/31/2011', '12/31/2012', 
              '12/31/2013', '12/31/2014', '12/31/2015', '12/31/2016', '12/31/2007', '12/31/2008', 
              '12/31/2009', '12/31/2010', '12/31/2011', '12/31/2012', '12/31/2013', '12/31/2014', 
              '12/31/2015', '12/31/2016', '12/31/2007', '12/31/2008', '12/31/2009', '12/31/2010', 
              '12/31/2011', '12/31/2012', '12/31/2013', '12/31/2014', '12/31/2015', '12/31/2016']

product_list = ['A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 
                'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 
                'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 
                'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 
                'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 
                'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B']

sales_list = [1957, 1968, 1983, 2001, 1894, 1850, 2045, 1860, 1784, 1856, 
              1891, 1822, 1846, 1903, 1924, 1897, 1890, 1858, 1871, 1880, 
              1858, 1909, 1977, 1717, 1751, 1797, 1804, 1845, 1895, 1713, 
              1904, 1812, 1733, 1868, 1872, 1909, 2034, 1856, 1813, 1806, 
              1862, 1800, 1840, 1882, 1819, 1854, 1716, 1845, 1877, 1879, 
              1696, 1783, 1799, 1852, 1793, 1877, 1687, 1824, 1839, 1889]<jupyter_output><empty_output><jupyter_text>The following code block first uses the list to construct a Python dictionary, uses the dictionary to define a Pandas `DataFrame` object named `sales_data`, and uses the `head()` method to print the first 10 rows of the `DataFrame`.    The head() function: The head() function is a Pandas object method that prints the first five rows of a Pandas object by default. Specifying an integer argument instructs Pandas to return the specified number of rows from the beginning of the object. Similar to the head() method, the tail() method returns records from the end of a Pandas object.    [Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_dictionary = {'Store': stores_list,
                    'Product': product_list,
                    'Sales': sales_list,
                    'Year': dates_list}

sales_data = pd.DataFrame.from_dict(sales_dictionary)

sales_data.head(10)<jupyter_output><empty_output><jupyter_text>A key thing to note is that the format of the `DataFrame` object is very similar to what you would see when working with Microsoft Excel worksheets. There are many other ways to create Pandas objects from scratch. However, for the sake of space, we will stop here. See https://jakevdp.github.io/PythonDataScienceHandbook/03.01-introducing-pandas-objects.html or use Google to find other examples. [Back to Table of Contents](Table_of_Contents)  Importing Data One of the most useful applications of Pandas is that it provides users with an easy way to import data from other sources. The following table was copied from https://pandas.pydata.org/pandas-docs/stable/io.html. Note that the hyperlinks will take you to sites descibing the different file formats or methods.Format TypeData DescriptionReaderWritertextCSVread_csvto_csvtextJSONread_jsonto_jsontextHTMLread_htmlto_htmltextLocal clipboardread_clipboardto_clipboardbinaryMS Excelread_excelto_excelbinaryHDF5 Formatread_hdfto_hdfbinaryFeather Formatread_featherto_featherbinaryParquet Formatread_parquetto_parquetbinaryMsgpackread_msgpackto_msgpackbinaryStataread_statato_statabinarySASread_sas&160;binaryPython Pickle Formatread_pickleto_pickleSQLSQLread_sqlto_sqlSQLGoogle Big Queryread_gbqto_gbq[Back to Table of Contents](Table_of_Contents) The following code block shows how the `read_csv` method that is available in Pandas can be used to read a file off the internet that uses semi-colon delimiters, store it in an object named `my_data`, and use the `head()` method to print the first fives rows of the `DataFrame` object.[Back to Table of Contents](Table_of_Contents)<jupyter_code>my_data = pd.read_csv('https://archive.ics.uci.edu/ml/machine-learning-databases/00409/Daily_Demand_Forecasting_Orders.csv',
           delimiter = ';')

my_data.head()<jupyter_output><empty_output><jupyter_text>Indexing and Selecting Data Although we have already seen some ways to access data in Pandas objects, this section will look at such methods in more detail.[Back to Table of Contents](Table_of_Contents) Indexing and Selecting Data in Pandas `Series` ObjectsRecall the `sales_series` object that we created earlier.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_series<jupyter_output><empty_output><jupyter_text>It's interesting to note that the Pandas `Series` object acts like both a one-dimensional NumPy array and a standard Python dictionary. For example, to get index values for a `Series` object, you can use the `keys()` method as shown below.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_series.keys()<jupyter_output><empty_output><jupyter_text>Also, as shown earlier, we can use NumPy-like slicing as is demonstrated in the following code block.[Back to Table of Contents](Table_of_Contents)<jupyter_code># Get the sales for June 2015 through December 2015 
sales_series['2015-06-30':'2015-12-31']<jupyter_output><empty_output><jupyter_text>Finally, we can use the boolean array capabilities of NumPy to get subsets of the `Series` object that satisfy a specified condition. As an example, the following code block identifies the months with sales greater than or equal to 1,000.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_series[(sales_series >= 1000)].keys()<jupyter_output><empty_output><jupyter_text>Method chaining: The previous example provides an opportunity to discuss an interesting aspect of Python programming. Specifically, the ability to chain several methods together to perform complex operations in few lines of code.     As an example let's suppose that we want to make a list of months with sales greater than or equal to 1,000. The previous code block simply returns the index values from the `Series` object where the condition is true. The following code block shows a *chained* expression that first use th `values` attribute to return a NumPy array containing the index values, and then uses the `list` method to convert the array into a list, all in one line of code. [Back to Table of Contents](Table_of_Contents)<jupyter_code>list(sales_series[(sales_series >= 1000)].keys().values)<jupyter_output><empty_output><jupyter_text>Indexing and Selecting Data in Pandas `DataFrame` ObjectsWe will now look at accessing data stored in a Pandas `DataFrame` object. Just as a reminder, the following code block prints the first five rows of the `sales_data` object that we created earlier.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.head()<jupyter_output><empty_output><jupyter_text>As shown in the following code block, we can select an entire column of the `DataFrame` object, resulting in a `Series`, by providing the column name in brackets.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data['Sales']<jupyter_output><empty_output><jupyter_text>Moreover, we can return multiple columns by providing the names of the desired columns as a list. Since we are returning multiple columns, the resulting object is a `DataFrame`.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data[['Product','Sales']]<jupyter_output><empty_output><jupyter_text>Similar to NumPy, we can perform calculations using entire columns.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data['Sales']/365<jupyter_output><empty_output><jupyter_text>We can extract a subset of the available records by providing a boolean expression in brackets.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data[sales_data['Product']=='A']
sales_data.head()<jupyter_output><empty_output><jupyter_text>Being build on top of NumPy, we can create quite complicated boolean expressions to select subsets of records. The following code block shows how we can define and use multi-condition boolean expression to select records where the store is *E1* and the sales is greater than 1,850.[Back to Table of Contents](Table_of_Contents)<jupyter_code>my_condition = (sales_data['Store'] == 'E1') & (sales_data['Sales'] > 1850)

sales_data[my_condition]<jupyter_output><empty_output><jupyter_text>Using the `.loc` *indexer* method for Pandas, we can use this boolean expression to return a subset of the available columns.[Back to Table of Contents](Table_of_Contents)<jupyter_code>my_condition = (sales_data['Store'] == 'E1') & (sales_data['Sales'] > 1850)

## The following loc expression uses the boolean expression to filter the rows,
## and then uses the list of column names to define the subset of columns to return
sales_data.loc[my_condition,['Product','Year']]<jupyter_output><empty_output><jupyter_text>Please see https://jakevdp.github.io/PythonDataScienceHandbook/03.02-data-indexing-and-selection.html for more discussion on indexing and selecting with Pandas objects.[Back to Table of Contents](Table_of_Contents)  Handling Missing Data In this section, we will look at a few capabilities built into Pandas for the purpose of handling missing data. For this demonstration, we will use a subset of the data contained in the `sales_data` object. The following code block creates and prints the data subset.[Back to Table of Contents](Table_of_Contents)<jupyter_code>my_subset = sales_data.copy()
my_condition = (my_subset['Product']=='A') & (my_subset['Store']=='W1')
my_subset = my_subset[my_condition]
my_subset<jupyter_output><empty_output><jupyter_text>For the sake of demonstration, assume that we were missing sales for the years 2011 and 2014. The following code block uses the `None` value to delete these sales values from the `my_subset` object. Note that in the resulting DatafFrame, the sales values are replaced with `NaN`, which stands for __Not a Number__.[Back to Table of Contents](Table_of_Contents)<jupyter_code>my_subset.loc[my_subset['Year']=='12/31/2011', 'Sales'] = None

my_subset.loc[my_subset['Year']=='12/31/2014', 'Sales'] = None

my_subset<jupyter_output><empty_output><jupyter_text>In some situations, it may be reasonable to *forward fill* values whenever missing values are encountered. Essentially, the *forward filling* method use a preceding value in the DataFrame, and can be accomplished using the `fillna` Pandas method as shown in the following code block. **Note that this function can take an *axis* argument, specifying the direction of filling. In this case, we want to fill across the rows, or axis 0.**[Back to Table of Contents](Table_of_Contents)<jupyter_code>my_subset.fillna(axis = 0, method = 'ffill')<jupyter_output><empty_output><jupyter_text>In contrast, it is also possible to *back fill* values, using values that follow in the DataFrame. This approach can be accomplished as shown in the following code block.[Back to Table of Contents](Table_of_Contents)<jupyter_code>my_subset.fillna(axis = 0, method = 'bfill')<jupyter_output><empty_output><jupyter_text>Instead of forward or back filling missing values, we may also use the `interpolate` method to take the average of preceding and following values. This approach is demonstrated below.[Back to Table of Contents](Table_of_Contents)<jupyter_code>my_subset.interpolate(axis = 0)<jupyter_output><empty_output><jupyter_text>The final approach to handling missing values that we will cover is to drop the values. This can be done using the `dropna` method shown in the following code block.[Back to Table of Contents](Table_of_Contents)<jupyter_code>my_subset.dropna(axis = 0)<jupyter_output><empty_output><jupyter_text>We can also drop missing values by specifying a condition that the records with missing values will fail. For example, the following code block uses the condition `my_subset['Sales']>0` to effectively drop the records with missing sales values. [Back to Table of Contents](Table_of_Contents)<jupyter_code>my_subset[my_subset['Sales']>0]<jupyter_output><empty_output><jupyter_text>Grouping and Aggregating Data In this section, we will look at how to use the `groupby` Pandas method to aggregate data that resides in a Pandas DataFrame object. Essentiall, the `groupby` method allows us to define a subset of the columns in a DataFrame to aggregate on. We can then use defined and user-specifed functions to compute aggregate statistics for the aggregated data. The following table, modified from that found at https://jakevdp.github.io/PythonDataScienceHandbook/03.08-aggregation-and-grouping.html, describes some of the defined aggregations.AggregationDescriptioncount()Total number of itemsfirst(), last()First and last itemmean(), median()Mean and medianmin(), max()Minimum and maximumstd(), var()Standard deviation and variancemad()Mean absolute deviationprod()Product of all itemssum()Sum of all itemsunique()Unique valuesnunique()Number of unique valuesThe following code block demonstrates how to use the `groupby` method to compute the total sales for each of the stores included in the DataFrame. [Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store'])['Sales'].sum()<jupyter_output><empty_output><jupyter_text>The following code block provides a closer look at how the `groupby` method works. Specifically, if we convert the grouped object to a list, we see that we get a list of tuples. The first value in the tuple specifies the grouping, and the second item specifies the data that belongs to the associated group.[Back to Table of Contents](Table_of_Contents)<jupyter_code>list(sales_data.groupby(['Store']))<jupyter_output><empty_output><jupyter_text>If a single column is specified for grouping, any aggregations are returned as a `Series` object. Thus, we can access the values using the indexing and selection methods for `Series` objects. For example, the following code block shows how to get the total sales for store *E1*.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store'])['Sales'].sum()['E1']<jupyter_output><empty_output><jupyter_text>If more than one column is specified for grouping, aggregated data is again returned as a `Series` object. However, this object will utilize a *multi-level* index as shown below.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store','Year'])['Sales'].sum()<jupyter_output><empty_output><jupyter_text>To access the total sales for store *E1* during 2007, we first need to get the data for store *E1*, as shown below.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store','Year'])['Sales'].sum()['E1']<jupyter_output><empty_output><jupyter_text>Then, we can index into the resulting `Series` to get the 2007 sales.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store','Year'])['Sales'].sum()['E1']['12/31/2007']<jupyter_output><empty_output><jupyter_text>Essentially, when multiple columns are grouped on, the resulting aggregations can be accessed in a manner that is similar to a multi-level dictionary. If we would like the object returned to resemble a DataFrame instead, we can set the optional `as_index` argument to `False`. This is demonstrated below. [Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store','Year'], as_index = False)['Sales'].sum()<jupyter_output><empty_output><jupyter_text>In addition to grouping on several columns, we can also perform several aggregations using the `agg` method. The following code block shows an example where we group the `sales_data` object by *Store* and *Year*, and then determine the *mean*, *total*, and *maximum* sales for each grouping. [Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store','Year'], as_index = False)['Sales'].agg({'mean','sum','max'})<jupyter_output><empty_output><jupyter_text>As you can see in the previous code block, even though we specified `as_index = False`, the returned object still utilizes a multi-level index. However, by *chaining* the expression with the `reset_index()` method, we can obtain a standard DataFrame object as shown below.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store','Year'], as_index = False)['Sales'].agg({'mean','sum','max'}).reset_index()<jupyter_output><empty_output><jupyter_text>We can also customize the column names as shown below.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store','Year'], as_index = False)['Sales'].agg({'Mean Sales':'mean',
                                                                     'Total Sales':'sum',
                                                                     'Maximum Sales':'max'}).reset_index()<jupyter_output><empty_output><jupyter_text>Finally, in addition to using the defined aggregations, a user can specify custom aggregations using a *lambda function*. A *lambda function* is essentially a function without a defined name. To apply a lambda function, we use the syntax `lambda my_var: my_func(my_var)`, where `my_var` represents a variable that will be passed as an argument and `myfunc()` represents a function that receives `my_var` as an argument. In the case of our groupby object, the values belonging to each group will be passed as the variable.The following code block shows how to use a lambda function to compute the $90^{th}$ percentile of the sales for each store, using NumPy's `percentile` method, along with the *mean*, *total*, and *maximum* sales for each store.[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby(['Store','Year'], as_index = False)['Sales'].agg({'Mean Sales':'mean',
                                                                     'Total Sales':'sum',
                                                                     'Maximum Sales':'max',
                                                                     '90% Sales': lambda x: np.percentile(x, 90)}).reset_index()<jupyter_output><empty_output><jupyter_text>It is also possible to perform different aggregations for different variables. However, it becomes quite complex to work with the returned objects. FOr more information, see https://pandas.pydata.org/pandas-docs/stable/groupby.html.[Back to Table of Contents](Table_of_Contents)  Pivot Tables The previous section showed how the `groupby` functionality available in Pandas allows you to aggregate data that resides in a `DataFrame` object in a very flexible manner. However, many users are very familiar with the aggregation capabilities made available by Microsoft Excel's pivot table functionality. Noting this, the Pandas package comes with a builtin `pivot_table` method that attempts to replicate this functionality in a simple fashion.The following code block shows how to use this function to compute the `mean` sales for each store, by year. [Back to Table of Contents](Table_of_Contents)<jupyter_code>pd.pivot_table(sales_data, values = 'Sales', columns = 'Store', index = 'Year', aggfunc = 'mean')<jupyter_output><empty_output><jupyter_text>The following code block shows how to use this function to compute the total sales for each store, by year.[Back to Table of Contents](Table_of_Contents)<jupyter_code>pd.pivot_table(sales_data, values = 'Sales', columns = 'Store', index = 'Year', aggfunc = 'sum')
sales_data['Store'].unique()<jupyter_output><empty_output><jupyter_text>Combining Datasets The final thing that we consider in this notebook is how to merge DataFrame objects. In particular, let's assume that we have another DataFrame object that includes information on each of the stores. The following code block creates such an object.[Back to Table of Contents](Table_of_Contents)<jupyter_code>stores_list = ['W1', 'E1', 'E2']

city_list = ['City A', 'City B', 'City C',]

state_list = ['State 1', 'State 2', 'State 2']

my_dict = {'Store': stores_list,
           'City': city_list,
           'State': state_list}

store_info = pd.DataFrame.from_dict(my_dict)

store_info<jupyter_output><empty_output><jupyter_text>Let's assume that we want the total sales by state. To calculate this quantity, we first need to group the data in the `sales_data` object by store. This grouping is performed in the following code block, and the resulting object is stored in a new object called `grouped_data`.[Back to Table of Contents](Table_of_Contents)<jupyter_code>grouped_data = sales_data.groupby('Store', as_index = False)['Sales'].sum()
grouped_data<jupyter_output><empty_output><jupyter_text>We can now use Pandas `merge` method to merge the `grouped_data` object with the `store_info` object. Since they both have a column that is named *Store*, the method will merge on this common column. This is shown below.[Back to Table of Contents](Table_of_Contents)<jupyter_code>grouped_data.merge(store_info)<jupyter_output><empty_output><jupyter_text>We can now *chain* the `groupby` method to the end of the merged data to obtain the total sales by store as is shown in the following code block.[Back to Table of Contents](Table_of_Contents)<jupyter_code>grouped_data.merge(store_info).groupby('State', as_index = False)['Sales'].sum()<jupyter_output><empty_output><jupyter_text>**Note that we could have performed the merging and aggregation in a single, *chained* statement as follows.**[Back to Table of Contents](Table_of_Contents)<jupyter_code>sales_data.groupby('Store', as_index = False)['Sales'].sum()\
.merge(store_info).groupby('State', as_index = False)['Sales'].sum()<jupyter_output><empty_output><jupyter_text>Let's now consider the case where we don't have nformation for all of the stores. The following code block creates a new version of the `store_info` object where we only have information for stores *E1* and *W1*.[Back to Table of Contents](Table_of_Contents)<jupyter_code>stores_list = ['W1', 'E1', ]

city_list = ['City A', 'City B',]

state_list = ['State 1', 'State 2']

my_dict = {'Store': stores_list,
           'City': city_list,
           'State': state_list}

store_info2 = pd.DataFrame.from_dict(my_dict)

store_info2<jupyter_output><empty_output><jupyter_text>The following code block shows the result of using the `merge` method to combine the `grouped_data` object with our new `store_info2` object. As you can see, with the default arguments, the returned object only includes records for stores included in both objects. Actually, the `method` is performing what is known as a *inner* where only records in both DataFrame objects are returned.[Back to Table of Contents](Table_of_Contents)<jupyter_code>grouped_data.merge(store_info2)<jupyter_output><empty_output><jupyter_text>If we wanted the object returened to include records for all stores, even if one is missing in one of the DataFrames being joined, we can specify an *outer* join using the `how` argument. This is shown in the following code block. Since he `store_info2` object does not include any information for store *E2*, we will get a `NaN` value for the *City* and *State* columns of the associated row in the returned object.[Back to Table of Contents](Table_of_Contents)<jupyter_code>grouped_data.merge(store_info2, how = 'outer')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10190774395558282, 'fraction_numerical': 0.053261887084799384, 'mean_word_length': 4.983005021243724, 'pattern_counts': {'":': 0, '<': 193, '<?xml version=': 0, '>': 211, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 21, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '48', 'dir': 'jupyter-structured-clean-dedup', 'id': '476470', 'license': 'MIT', 'n_tokens_mistral': 10376, 'n_tokens_neox': 8948, 'n_words': 3469, 'path': '3-Pandas_Introduction/Pandas_Introduction.ipynb', 'repo_name': 'nkfreeman/Python_Tutorials', 'size': '30988'}