<jupyter_start><jupyter_text>PyData @ Str<NAME> <EMAIL> 2016-03-29 This tutorial, and Python in general, run more smoothly under Python 3.x.Whether you're running on Python 2 or Python 3, please install [Python-Future](http://python-future.org/futurize.html):```bashconda install future```<jupyter_code>from __future__ import (absolute_import, division,
                        print_function, unicode_literals)
from future import standard_library
standard_library.install_aliases()
from future.builtins import (
         bytes, dict, int, list, object, range, str,
         ascii, chr, hex, input, next, oct, open,
         pow, round, super, filter, map, zip)<jupyter_output><empty_output><jupyter_text>Welcome  If your world of data is like this:![messy](img/messy_garage_3.jpg)  Pandas is like this:![knife](img/knife.jpg)  What is Pandas? * The python data analysis library  > *"Python has long been great for data munging and preparation, but less so for data analysis and modeling. pandas helps fill this gap, enabling you to carry out your entire data analysis workflow in Python" -- pandas.pydata.org** <NAME>, original author:    * started work on Pandas in 2008 while at AQR Capital Management    * conveinced AQR to open-source pandas.    * provides a 10 minute tour of Pandas: [video](https://vimeo.com/59324550) and [notebook](http://nbviewer.ipython.org/urls/gist.github.com/wesm/4757075/raw/a72d3450ad4924d0e74fb57c9f62d1d895ea4574/PandasTour.ipynb)* More details from PyData.org:    * *"high-level building block for doing practical, real world data analysis in Python."*    * *"pandas is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language."*    * *"provides fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive."*  Pandas Overview  Summary * ``pandas`` is widely used for doing practical data analysis* ``pandas`` fills a gap of convenient tools for data analysis in the python ecosystem* ``pandas`` is consistent, built around its own data structures* ``pandas`` is fast, built on numpy, but goes beyond.* ``pandas`` is tightly integrated with the rest of the scientific Python ecosystem  Practical Use ``pandas`` is built for doing practical data analysis using Python* Used extensively in production in financial applications.* Well suited for tabular data (e.g. column data, spread-sheets, databases)* Can replace or integrate with Excel and SQL queries.  Fills the Data Analysis Gap ``pandas`` fills a gap in the Python scientific eco-system:* Convenient methods for **reading/writing** a large variety of file formats, e.g. Excel, HDF, SQL, CSV, etc.* Meaningful **labels** for quantitative and categorical data* Easy handling **missing data** and **NaN** (much harder to do with ``numpy`` arrays alone).* SQL-like methods for manipulating tabular data, such as pivot, groupby, merge/join.* Convenient methods for routine mathematical and **summary statistics**  Pandas keeps you from doing this:![homemadeknife](img/homemadeknife.JPG)  Pandas Overview - What Are We Going To Do Today?  - Pandas Data Structures: How We Work With Data - Pandas I/O: How We Get Our Data into Pandas - Slicing/Dicing/Mixing/Matching Til Your Fingers Bleed! -  Example: The Turning Tuesday Hypothesis: Beat the Stock Market With This One Simple Trick... - Where To Go From Here  Getting Started:  Step 1. Download AnacondaGo to [www.continuum.io](www.continuum.io) to get Anaconda!  Step 2. Get Some Data```git clone https://www.github.com/talumbau/strata-data```Or go to [https://www.github.com/talumbau/strata_data](https://www.github.com/talumbau/strata_data)  Step 3: Navigate to where you cloned the repo/downloaded the data:```   cd ~/dev/strata_data/```   Step 4: Start the jupyter notebook:```    jupyter notebook```   Step 5: Start the strata_data notebook:   Step 6: wait a while...  Cheatsheet: view this notebook online and follow the links! [https://anaconda.org/talumbau/strata_pandas/notebook](https://anaconda.org/talumbau/strata_pandas/notebook) to view and download this notebook!  Data Structures Pandas provides two primary data structures:* `Series` is built for 1-dimensional series data* `DataFrame` is built for 2-dimensional collections of tabular dataThese data structures differ and are related as follows:* **Series**: 1-dimensional array of **homogeneous** data* **DataFrame**: 2-dimensional table of **heterogeneous** data, composed of multiple **Series**Each of these also contains an integrated **`index`**:* **Series** objects include a second array called the **index** that can contain homogeneous values of any type like integers, strings or datetime objects. * **DataFrame** objects include a column **index**. The **index** objects allow for very sophisticated selection operations using square brackets (`[]`) along with some specialized selection functions.*side note: Pandas has another data structure called Panels which is not commonly used*  Fast Operations ``pandas`` is fast, built on numpy, but goes beyond:* `Series` and `DataFrames` are built upon `numpy.ndarray`* ``pandas`` supports the same `ufunc` operations as in ``numpy``, and the same fast vectorized computations.* goes beyond ``numpy`` by providing elementwise string and datetime operations on indexing.* ``pandas`` uses and supports additional C extensions written in Cython.  Integration with Ecosystem ``pandas`` is tightly integrated with the rest of the scientific Python ecosystem* built on Numpy arrays and ufuncs* pandas data structures can be passed into numpy, matplotlib, and bokeh methods* has built-in visualization using Matplotlib* a dependency for Python statistics library statsmodel* a [NumFOCUS](http://www.numfocus.org) suported project  Documentation ``pandas`` is an enormous library. We will concentrate on only a few elementary data analysis tasks using ``pandas``, but more extensive descriptions of the motivations and functionality of ``pandas`` can be found in the resources below: * [pandas.pydata.org](http://pandas.pydata.org/)  * [pandas documentation](http://pandas.pydata.org/pandas-docs/stable/)  * [10 minutes to Pandas](http://pandas.pydata.org/pandas-docs/stable/10min.html)  * [Cookbook at PyData](http://pandas.pydata.org/pandas-docs/stable/cookbook.html)  Installation The easiest way to install pandas and its depedencies is by installing the Anaconda distribution.* http://pandas.pydata.org/pandas-docs/stable/install.html If you have not already installed Anaconda, do so, and then use the following conda command to install pandas:<jupyter_code>## Uncomment the following line to use conda to install pandas

!conda install -y pandas
!conda install -y pandas-datareader<jupyter_output><empty_output><jupyter_text>Test your ``pandas`` install by trying to import it: * the `import` should not generate an error* the version should be > 0.15.0<jupyter_code>import pandas
pandas.__version__<jupyter_output><empty_output><jupyter_text>Should you have an older version, use the following conda to upgrade pandas<jupyter_code>## To upgrade pandas, uncomment and run the following:

!conda install pandas=0.16.0<jupyter_output>Collecting package metadata (current_repodata.json): done
Solving environment: failed with initial frozen solve. Retrying with flexible solve.
Collecting package metadata (repodata.json): done
Solving environment: failed with initial frozen solve. Retrying with flexible solve.

PackagesNotFoundError: The following packages are not available from current channels:

  - pandas=0.16.0

Current channels:

  - https://conda.anaconda.org/conda-forge/osx-64
  - https://conda.anaconda.org/conda-forge/noarch
  - https://repo.anaconda.com/pkgs/main/osx-64
  - https://repo.anaconda.com/pkgs/main/noarch
  - https://repo.anaconda.com/pkgs/r/osx-64
  - https://repo.anaconda.com/pkgs/r/noarch

To search for alternate channels that may provide the conda package you're
looking for, navigate to

    https://anaconda.org

and use the search bar at the top of the page.<jupyter_text>Take a moment to read the docstring or use tab-complete to see what's in the library<jupyter_code>## To explore pandas contents, uncomment the following:

pandas.  ## press <tab>
# pandas?   ## press <enter><jupyter_output><empty_output><jupyter_text>Pandas Demonstration  Set-up<jupyter_code># community convention, import as "pd"
import pandas as pd

# to imbed plots, prevents pop-up windows for plots
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Data Reading * Pandas makes it easy to read data from files and even from web sites.* Here we use the Pandas Datareader module to read Google stock data from 2010 to the end of 2014.<jupyter_code># Reading data from a file

google = pd.read_csv('data/goog.csv', index_col='Date', parse_dates=True)
google
# Reading data from the Web
from pandas_datareader import data as pd_data

start  = pd.Timestamp('2010-1-1')
end    = pd.Timestamp('2014-12-31')
google = pd_data.DataReader("GOOG", 'google', start, end)

## Note: this is how we generated the data file read above!
# google.to_csv('data/goog.csv', index=True, encoding='utf-8')<jupyter_output><empty_output><jupyter_text>Data Containers * The `google` object is a Pandas DataFrame container* standard library python methods like `type()` work on it<jupyter_code>type(google)<jupyter_output><empty_output><jupyter_text>Inspect the available attributes ("fields") and methods of the DataFrame by using tab-completion:<jupyter_code>## To explore the DataFrame contents, uncomment the following:

google.   ## uncomment, place cursor after dot, press <tab><jupyter_output><empty_output><jupyter_text>Data Inspection Use the method `DataFrame.head()` to inspect the first few rows of data:* great way to inspect smaller data sets* useful for verifying you've read the right file<jupyter_code># Inspect the first few recods of the DataFrame
google.head(e)<jupyter_output><empty_output><jupyter_text>Now use the Pandas `DataFrame.info()` method to see a bit more detail, including:* the name of each column* the Numpy dtype of each column* how many real values are present* amount of memory used*side note: Pandas has excellent support for not-a-number (NaN) entries in DataFrames and Series.*<jupyter_code># What data do we have
# Weekends and holidays are not included
google.info()<jupyter_output><empty_output><jupyter_text>Data Indexing * Pandas DataFrames have a special Index, goes beyond integer indexing* Pandas DataFrames are indexed by column label, then (row) Index:<jupyter_code># Access an entire column using []
google['Open']
# Inspect the first 5 rows or the 'Open' column, using integer indexing

google['Open'].iloc[0:5]
# Inspect the first 5 rows or the 'Open' column, using date indexing

google.loc['2010-01-04':'2010-01-08','Open']<jupyter_output><empty_output><jupyter_text>Data Filtering<jupyter_code># Filtering by conditional
google_up = google[ google['Close'] > google['Open'] ]
google_up.head()
# Filtering out missing data

google_filtered = google[pd.isnull(google['Volume']) == False ]
google_filtered.head()<jupyter_output><empty_output><jupyter_text>Data Statistics In Pandas we can very easily perform many types of statistical operations.<jupyter_code>google.describe()<jupyter_output><empty_output><jupyter_text>Data Computations One simple example motivated by finance is to compute the "volatility" -- the rolling standard deviation of the log-return over the business days of the year.* First we use the `pandas` method `.pct_change()` to compute the *return* per day based on percent change.* Next we use `numpy` to compute the log of the return.* Finally we use `pandas` method `.rolling_std()` to compute the volatility<jupyter_code># Compute the returns from daily percent change
# Append a new column
google['Return'] = google['Close'].pct_change()
google['Return'].iloc[0:5]
# Compute the log() of the return using numpy
# Append another new column
import numpy as np
google['LogReturn'] = np.log(1+google['Return'])
google['LogReturn'].iloc[0:5]
# Compute volatility as rolling standard deviation of the log() of the returns
# Append yet another new column
window_size = 252
google['Volatility'] = pd.rolling_std(google['LogReturn'], window=window_size) * np.sqrt(window_size)
google['Volatility'].iloc[window_size-5:window_size+5]<jupyter_output><empty_output><jupyter_text>Data Visualization Finally, Pandas is coupled to Matplotlib:* Columns from a single DataFrame can be plotted using `DataFrame.plot()`.* Here we will plot the newly created columns of data, `LogReturn` and `Volatility`.<jupyter_code>google.info()
google[['Close','Volatility']].plot(subplots=True, figsize=(15,6))<jupyter_output><empty_output><jupyter_text>Pandas Data Structures: How we work with Data Pandas provides two primary data structures:* `Series` is built for 1-dimensional series data* `DataFrame` is built for 2-dimensional collections of tabular dataThese data structures differ and are related as follows:* **Series**: 1-dimensional array of **homogeneous** data* **DataFrame**: 2-dimensional table of **heterogeneous** data, composed of multiple **Series**Each of these also contains an integrated **`index`**:* **Series** objects include a second array called the **index** that can contain homogeneous values of any type like integers, strings or datetime objects. * **DataFrame** objects include a column **index**. The **index** objects allow for very sophisticated selection operations using square brackets (`[]`) along with some specialized selection functions.*side note: Pandas has another data structure called Panels which is not commonly used*  Set-up<jupyter_code># Numerical computing
import numpy as np

# Draw pictures!
import matplotlib.pyplot as plt
%matplotlib inline

# Python people are weird!
import pandas as pd

# Set Jupyter notebook display options
pd.options.display.max_rows = 8
pd.options.display.max_columns = 8

# Check version number
pd.__version__<jupyter_output><empty_output><jupyter_text>Should you have an older version, use the following conda to upgrade pandas<jupyter_code>## To upgrade pandas, uncomment and run the following:

# !conda install pandas=0.16.0<jupyter_output><empty_output><jupyter_text>Pandas Series * Designed for 1-dimensional, homogeneous series data* Composed of an array of data and an special Index object* Access to data contents can be done by `position` index (0,1,2,...) or by `label` index ('a','b','c',...)* Index label type can be integers, strings, or datetime objects. * Operations like *retrieving*,  *joining*, and *aligning* data are made easy.  Creating a Series<jupyter_code># Example: Creating a Series from a data column from file

file   = "data/exoplanets.csv"
data   = pd.read_csv(file)
series = data['NAME']

print( "\n" )
print( series )

print( "\n" )
print( type( series ) )
# Example: Creating a Series a list

new_list = [5,10,15,20,25]
pd.Series(new_list)
# Example: Creating a Series from a dictionary... index will be sorted

new_dictionary = {'b': 100, 'a': 200.0, 'd':450, 'c':700}
pd.Series(new_dictionary)<jupyter_output><empty_output><jupyter_text>With that, you now have enough information to know the Big, Big, Secret of Pandas: (Almost) All the complexity in pandas (and it is actually, somewhat complex in parts) arises from the ability to index by *POSITION* (e.g. 0, 1, 2, ith location) and also index by *LABEL* ('a', 'b', 'c', etc.).<jupyter_code># Example: Creating a Series from an ORDERED dictionary
#          ... to preserver order of the indices.

from collections import OrderedDict
od = OrderedDict([('b', 100), ('a',200), ('d',450), ('c',700)])
pd.Series(od)
# Example: Creating a Series from a NumPy array

array1  = np.arange(1,6)*10.0
series1 = pd.Series(array1)

print( "\n" )
print( array1 )
print( type(array1) )

print( "\n" )
print( series1 )
print( type(series1) )<jupyter_output><empty_output><jupyter_text>Pandas Index The pandas Series data structure always has an `Index`<jupyter_code># The default Index is just a sequence of integers:
array1  = np.arange(1,6)*10.0
series1 = pd.Series(array1)

series1.index
# The Index is used for indexing

print( series1.iloc[0] )  # "Positional" indexing
# The Index can be set to a sequence of labels:

index2 = ['a','b','c','d','a']
series2 = pd.Series(np.arange(1,6)*10.0, index=index2)
# The Index labels are used for indexing

series2.loc['b']  # "label" indexing
# Indexing when the label is not unique?

series2.loc['a']<jupyter_output><empty_output><jupyter_text>Iterating over a Series<jupyter_code>for key, value in series2.iteritems():
    print(key, value)<jupyter_output><empty_output><jupyter_text>Pandas Slicing<jupyter_code># Positional indexing and slicing

series1.iloc[1:3]
# Be careful when slicing with integers versus labels

print( series2.iloc[1:2]     )  # Positional indexing is permitted with slicing
print("\n")
print( series2.loc['b':'c'] )  # Ranges of labels can be sliced (slicing here is *inclusive*)
# Example: slice from the end
series2.iloc[-1:]
# Example: slice from the beginning
series2.iloc[:-3]
# Example: slice with labels, but with integer step size
series2.loc['d':'b':-2]<jupyter_output><empty_output><jupyter_text>Series Arithmetic<jupyter_code># Arithmetic with Series is as with numpy arrays
print(series1)

series2 = series1 * 2 
print(series2)

series3 = series2 * 3
print(series3)<jupyter_output><empty_output><jupyter_text>Series with NumPy<jupyter_code># numpy ufuncs can be applied elementwise to Series

series_2pi = pd.Series( np.linspace(-1*np.pi, +1*np.pi, 100) )
series_2pi
# Even though pandas was built on numpy, numpy methods on Series return a Series

series_sin = np.sin( series_2pi )
print( series_sin )
print( type( series_sin ) )<jupyter_output><empty_output><jupyter_text>Series with Matplotlib<jupyter_code>plt.plot(series_2pi, series_sin)<jupyter_output><empty_output><jupyter_text>Series Operations<jupyter_code># You can add two series

series1 = pd.Series([1,2,3,4,5])
print(series1)

series2 = pd.Series([10,20,30,40,50])
print(series2)

series3 = series1 + series2
print(series3)
# Even better when you assign labels to your data!

series1 = pd.Series([1,2,3,4,5],      index=['a','b','c','d','e'])
series2 = pd.Series([10,20,30,40,50], index=['c','d','e','f','g'])

print( series1 + series2 )<jupyter_output><empty_output><jupyter_text>SQL users: what just happened here?????<jupyter_code># So when you apply a method like mean(), the NaNs are handled intelligently...

(series1 + series2).mean()
# Contrast how NumPy and Pandas handle NaN:

print( "Pandas mean: ", pd.Series( [1.0, 2.0, np.nan] ).mean() )
print( "Numpy mean:  ", np.array(  [1.0, 2.0, np.nan] ).mean() )<jupyter_output><empty_output><jupyter_text>Pandas DataFrame What is a DataFrame?* 2-dimensional labled tabular data (think spreadsheet or SQL table) * **heterogeneous** data, composed of multiple **Series*** an **Index** or "row label" for selection operationsDataFrame inputs:* python dict of 1D sequences (e.g. ndarrays, lists, dicts, or Series)* 2-D numpy.ndarray* pandas Series* another DataFrameDataFrame Operations:- Pandas DataFrame objects have column names:    - dictionary style: df['DATE']    - attribute style: df.DATE- DataFrame binary operations (+ - / *) defaults to outer join, on both columns as well as the index- NaN can be handled after join  Creating a DataFrame<jupyter_code># Creating a DataFrame from a file read:

file    = "data/exoplanets.csv"
df_file = pd.read_csv(file)
df_file
# Creating a DataFrame from a dictionary

dictionary = {'a':[1,2,3,4,5,6,7,8,9,10],
              'b':np.linspace(0,np.pi,10),
              'c':0.0,
              'd':["a","b","c","a","b","c","a","b","c","a"]}

df_dict = pd.DataFrame(dictionary)
df_dict
# Inspect the DataFrame colums and index
df_dict.columns
df_dict.index<jupyter_output><empty_output><jupyter_text>Now use the Pandas `DataFrame.info()` method to see a bit more detail, including:* the name of each column* the Numpy dtype of each column* how many actual values are present* amount of memory used*side note: Pandas has excellent support for not-a-number (NaN) entries in DataFrames and Series.*<jupyter_code>df_dict.info()<jupyter_output><empty_output><jupyter_text>DataFrame Indexing * Pandas DataFrames have a special Index, goes beyond integer indexing* Pandas DataFrames are indexed by column label, then (row) Index:<jupyter_code>df_grades = pd.DataFrame({'Alice' : [1,2,3,4,5], 
                         'Bob':np.random.random(5), 
                         'Chuck' : np.random.random(5)},
                         index=['Jan', 'Feb', 'Mar', 'Apr', 'May'])
df_grades
# Indexing is by column first (gives a Series)
series_a = df_grades['Alice']

print( series_a )
print( type(series_a) )
# Indexing the column name, gives a Series, then 'positional' indexing
df_grades['Alice'].iloc[1]
# Indexing the column name, gives a Series, then 'label' indexing
df_grades.loc['Jan','Alice']<jupyter_output><empty_output><jupyter_text>Iterating over a DataFrame As seen with Series, we can iterate over DataFrames<jupyter_code>for key, value in df_grades['Alice'].iteritems():
    print('Key:', '%s' % key, ', Value:', '%s' % value)<jupyter_output><empty_output><jupyter_text>DataFrames support iteration over the `Index`<jupyter_code>for index, row in df_grades.iterrows():
    print('Index:', '%s' % index, ', Row:', '%s' % row['Alice'])<jupyter_output><empty_output><jupyter_text>DataFrame Manipulation This is just a brief introduction working with DataFrames. There's a *lot* more to learn...<jupyter_code># Extract a column as a new DataFrame
df_col = df_grades[['Alice']]
print( df_col, "\n", type(df_col) )
# Extract a row as a new DataFrame
df_row = df_grades.loc['Jan':'Jan']
print( df_row, "\n", type(df_row) )
# Slicing along rows works just as with Series
df_grades.loc['Jan':'Mar','Alice']
# For column range, need to use the .ix attribute of the DataFrame
df_grades.loc[:,'Alice':'Bob']
# For extracting row and column ranges
df_grades.loc['Mar':'Apr', 'Bob':'Chuck']
# Boolean masks work too
df_grades.loc[df_grades['Bob'] < .5]
# Pandas DataFrames can be cast to other types

my_list  = list(df_grades['Alice'])
print( my_list, type(my_list) )

my_array = np.array(df_grades['Alice'])
print( my_array, type(my_array) )<jupyter_output><empty_output><jupyter_text>Summary of what we've just seen: ***  Pandas I/O: How We Get Data Into Pandas pandas has excellent support for file I/O (read/write) for a wide variety of common file formats.  Set-Up<jupyter_code># Import modules needed for this lesson
import numpy as np
import pandas as pd
%matplotlib inline

# Set Jupyter notebook display options
pd.options.display.max_rows = 6
pd.options.display.max_columns = 8

# Test the version of pandas: it should be 0.16 or greater
pd.__version__<jupyter_output><empty_output><jupyter_text>Example Data The data used throughout this demonstration is from the catalog of exoplanets -- planets outside our solar system -- compiled by [exoplanets.org](http://exoplanets.org/table)  Creating a Temporary Directory Any time you are handling data, it is commonlly needed to handle "tmp" files.<jupyter_code># Remove old tmp dir, Create a new tmp dir
import os
if not os.path.exists("tmp"):
    os.mkdir("tmp")

# Define a convenience function to help us clean up
def clean_tmp(file_name="tmp/exoplanet.csv"):
    if os.path.isfile(file_name):
        os.remove(file_name)<jupyter_output><empty_output><jupyter_text>Reading and Inspecting Below is a simple demonstration of reading and inspecting data.We will see a much more detailed treatment of examining your data in the next lesson.<jupyter_code>import pandas as pd
file_name = "data/exoplanets.csv"
data = pd.read_csv(file_name)
print(type(data))
# Preview data after a read.
# Notice the first column
data.iloc[0:2]
# There is an Index array imbedded in the DataFrame
data.index
# We can use a column to create a better Index
data = pd.read_csv(file_name, parse_dates=True, index_col='DATE')
data.head(10)     # another way to preview data after a read
# Notice it is not just an Index, but a DatetimeIndex; note the dtype
data.index
# You can sort by the Index
data = data.set_index(data.index.sort_values(ascending=False))
data.iloc[0:3]<jupyter_output><empty_output><jupyter_text>Minimal Cleanup Later, we will cover data clean-up in much more detail. Here are just a few simple things to do to inspect and clean-up data.  Listing Columns<jupyter_code># View the labels of some or all of the columns
# data.columns[0:10]
data.columns<jupyter_output><empty_output><jupyter_text>Extracting Columns<jupyter_code>data['RADIUS(rjupiter)']
extract_data = data['RADIUS(rjupiter)']
print(type(extract_data))
extract_data.iloc[0:10]<jupyter_output><empty_output><jupyter_text>Adding Columns - zero fill:     ```df['var'] = 0```   - values from NumPy array: ```df['my_data'] = data```   - note: df.var construct can not create a column by that     name; only used to access existing columns by name<jupyter_code># Add a column labeled 'price'
data['price'] = 1e6 # One *MILLION* dollars!
# Inspect the updated DataFrame
data.price.iloc[0:5]<jupyter_output><empty_output><jupyter_text>Deleting Columns<jupyter_code># Demonstration that you can delete columns after a file read
del data['FIRSTURL']
# Inspect the results
data.iloc[0:5]<jupyter_output><empty_output><jupyter_text>Renaming Columns<jupyter_code># Change the label of a single column
data = data.rename(columns={'NAME':'PLANET'})
data.iloc[0:3]<jupyter_output><empty_output><jupyter_text>Question/Exercise1. What is average length of a year for the known expoplanets?2. Plot the period (year length) for the exoplanets in order of their discovery date. Why does the plot look odd?3. Interstellar real estate: Instead of planets being ONE MILLION DOLLARS each, they are 1 million dollars per Jupiter mass - but anything over 10 parsecs is out "in the boonies" so we subtract 25,000 dollars for each parsec beyond 10. Create a column with the price of each planet.<jupyter_code>%matplotlib inline
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>File Formats Pandas has an extensive set of I/O methods:* Can read from a wide range of flat files, including Excel and HDF5* Can also read SQL queries into memory* Sensible defaults, automatically labeled and typed `DataFrames`<jupyter_code># Print a list of all read_* methods in pandas

print("".join(["pd.%s\n" % reader 
               for reader in dir(pd) 
               if reader.startswith('read_')]))
# DataFrame can be cast to files or other objects

print("".join(["pd.DataFrame.%s\n" % reader 
               for reader in dir(pd.DataFrame) 
               if reader.startswith('to_')]))<jupyter_output><empty_output><jupyter_text>Below are external links to details on the file formats that pandas can read.- [CSV](https://en.wikipedia.org/wiki/Comma-separated_values)- Excel- SQL- [JSON](http://www.json.org/)- [HDF5](https://www.hdfgroup.org/HDF5/)- [pickle](https://docs.python.org/3/library/pickle.html)- [msgpack](http://msgpack.org/)- [Stata](https://en.wikipedia.org/wiki/Stata)- [Google BigQuery](https://en.wikipedia.org/wiki/BigQuery)  With that said, now is a good time to say:  NEVER WRITE YOUR OWN CSV READER IN PYTHON. (it's probably a bad idea) ![messy](img/the-more-you-know.png)  We will now demonstrate using pandas reading and writing to a variety of file formats.  CSV http://pandas.pydata.org/pandas-docs/stable/io.htmlcsv-text-files<jupyter_code>df = pd.read_csv('data/exoplanets.csv', 
                 parse_dates=['DATE'],
                 encoding='utf-8')
df.info()
# The first exoplanet to get news headlines was in 1996.
# Use some indexing to inspect the rows.
# Combining head() and .iloc[] bracket indexing.
df.head(8).iloc[-1:]<jupyter_output><empty_output><jupyter_text>Let's do some simple data extractions to prove we can.<jupyter_code># Extract the name using [row index] and [column label]
df.loc[7,'NAME']
# Extract the mass using the [column label] and [row index]
df.loc[7,'MASS(mjupiter)']
# Extract the mass using and [row index] and [column index]
# The MASS(mjupiter) column is the 5th labeled column (not counting the Index)
# All pandas DataFrames have the method .iloc() 
df.iloc[7,5]
df.to_csv('tmp/exoplanets.csv', index=False, encoding='utf-8')<jupyter_output><empty_output><jupyter_text>CSV Online Pandas can read a data table directly from the web<jupyter_code>url = 'http://waterdatafortexas.org/reservoirs/individual/austin.csv'
df  = pd.read_csv(url, comment='#', index_col='date', parse_dates=True)
df

# note: you can parse dates, and ignore comment lines<jupyter_output><empty_output><jupyter_text>Excel http://pandas.pydata.org/pandas-docs/stable/io.htmlexcel-files<jupyter_code># Save the DataFrame as an Excel file
file_name = 'tmp/exoplanets.xls'
clean_tmp(file_name)

df.to_excel(file_name, index=False, encoding='utf-8')
# Read the Excel file
file_name = 'tmp/exoplanets.xls'
df = pd.read_excel(file_name, sheetnames=[0], encoding='utf-8')
df.head(3)<jupyter_output><empty_output><jupyter_text>SQLite http://pandas.pydata.org/pandas-docs/stable/io.htmlsql-queries<jupyter_code>file_name = "tmp/exoplanets.sqlite"
clean_tmp(file_name)

from sqlalchemy import create_engine
engine = create_engine('sqlite:///' + file_name)
df.to_sql('table', engine, index=False)
df = pd.read_sql('table', engine)
df.head()<jupyter_output><empty_output><jupyter_text>JSON http://pandas.pydata.org/pandas-docs/stable/io.htmljson<jupyter_code># Writing to JSON file
file_name = "tmp/exoplanets.json"
clean_tmp(file_name)

df.to_json(file_name)
# Reading the JSON file results in columns sorted by label

file_name = "tmp/exoplanets.json"
df = pd.read_json(file_name)
df.head()
pd.read_json?<jupyter_output><empty_output><jupyter_text>HDF5 http://pandas.pydata.org/pandas-docs/stable/io.htmlhdf5-pytables `format='table'` is used because its appendable, queryable, and supports all pandas dtypes.<jupyter_code>file_name = "tmp/exoplanets.hdf"
clean_tmp(file_name)

df.to_hdf(file_name,
          'df',
           mode='w',
           format='table',
           encoding='utf-8')
file_name = "tmp/exoplanets.hdf"
df = pd.read_hdf(file_name,'df',encoding='utf-8')
df.head()<jupyter_output><empty_output><jupyter_text>FAQ: File Formats Frequently asked file-format questions, and answers:- which formats provide good fidelity  - hdf5, pickle, msgpack  - which formats can you query  - hdf5, sql  - which formats can you iterate  - csv, hdf5, sql  - which formats provide better interoprability  - csv, json, excel  - which formats can you transmit over the wire  - json, msgpack  - which formats have better compression  - hdf5, pickle, msgpack  - which formats allow multiple datasets in the same file  - hdf5, msgpack  Reading Large Data Let's set some expectations for read times for various file formats  Read Performance  Timing Reads: Common Formats<jupyter_code>%timeit pd.read_excel('tmp/exoplanets.xls', sheetnames=[0])
%timeit pd.read_sql('table', engine)
%timeit pd.read_json('tmp/exoplanets.json')
%timeit pd.read_csv('tmp/exoplanets.csv', parse_dates=['DATE'])
%timeit pd.read_hdf('tmp/exoplanets.hdf','df')<jupyter_output><empty_output><jupyter_text>Timing Reads: Pickle and MSGPack<jupyter_code>df.to_pickle('tmp/exoplanets.pkl')
df.to_msgpack('tmp/exoplanets.msgpack',encoding='utf-8')
%timeit pd.read_pickle('tmp/exoplanets.pkl')
%timeit pd.read_msgpack('tmp/exoplanets.msgpack', encoding='utf-8')<jupyter_output><empty_output><jupyter_text>Reading Compressed Data You may encounter compressed data files, e.g. ZIP or TAR.Here we deomstrate some simple strategies for handling compressed data.  Cleaning Temporary Files Example: clean up tmp files created in this notebook<jupyter_code># Always check for existence before removing
if os.path.exists("tmp"):
    os.listdir("tmp")
# Build a list of files inside, remove each one by one, then remove the dir
if os.path.exists("tmp"):
    file_list = os.listdir("tmp")
    for file in file_list:
        clean_tmp(os.path.join("tmp", file))
    os.rmdir("tmp")<jupyter_output><empty_output><jupyter_text>***  Pandas: Examing Data  Set-Up<jupyter_code># Import modules needed for this lesson
import pandas as pd

# Set Jupyter notebook display options
pd.options.display.max_rows = 6
pd.options.display.max_columns = 8
pd.options.display.width = 100

# Test the version of pandas: it should be 0.16 or greater
pd.__version__
print("Hello world")<jupyter_output><empty_output><jupyter_text>***  Examining Columns  Read Data into a DataFrame CSV files, being tabular data, have a natural mapping into Pandas DataFrames. * Here we read in beer reviews from beeradvocate.com, via [Stanford SNAP](https://snap.stanford.edu/data/web-BeerAdvocate.html). * Pandas can tell if a file is zipped, and will unzip in memory.<jupyter_code>df = pd.read_csv('data/beer2.csv.gz',
                  index_col=0,
                  parse_dates=['time'])
df.head()<jupyter_output><empty_output><jupyter_text>Pandas provides useful inpsection tools to help understand the dtypes that were generated for each column. Notice that some columns do not have 50,000 *non-null* values.<jupyter_code># Notice the column (1) names, (2) number of non-null, (3) dtype, (4) memory usage
df.info()
# Summary statistics, using the built-in .describe() method

df.describe()<jupyter_output><empty_output><jupyter_text>Column selection A variety of column operations are available<jupyter_code># The column names, as an Index object
print( df.columns )
# The column labels can be converted to list of strings

list(df.columns)<jupyter_output><empty_output><jupyter_text>Columns in a pandas DataFrame can be accessed using the .attribute method<jupyter_code># View column data using .attribute, column label

df.beer_style<jupyter_output><empty_output><jupyter_text>Or with dictionary-like key selection<jupyter_code># View column data using [bracket], column label string

df['beer_style']<jupyter_output><empty_output><jupyter_text>A single column (**Series**) can be selected using the column name as an attribute (or accessor) of the **DataFrame** object.<jupyter_code>beer_series = df.beer_name
print( type(beer_series) )
beer_series.head()<jupyter_output><empty_output><jupyter_text>Dropping and Adding Columns<jupyter_code>df.head(3)
# Adding a new column

df['alcohol'] = 0.0
df.head(3)
df.info()<jupyter_output><empty_output><jupyter_text>Over-writing one column with another<jupyter_code>df['alcohol'] = df['abv']
df
# Dropping a column

df.drop(['abv'], axis=1).head(3)
df.info()<jupyter_output><empty_output><jupyter_text>String Operations Varios srtring operations can be applied to the columna data values<jupyter_code># Print first 4 letter of each string in the column

df.beer_style.str[0:4]
# Print the length of each string in the column

df.beer_style.str.len()
# Get boolean (True/False) mask for strings matching a pattern

df.beer_style.str.contains('[A|a]merican')
# Extract the rows that match the True

boolean_mask = df.beer_style.str.contains('[A|a]merican')
df.beer_style[boolean_mask]
# To see full documentation for str() method, uncomment the following:

# help(df.beer_style.str)<jupyter_output><empty_output><jupyter_text>Datetime Operations Using the `.dt` accessor, datetime manipulations can be applied to a datetime series or column elementwise.<jupyter_code># select the `time` column, noteice the dtype=datetime

df.time<jupyter_output><empty_output><jupyter_text>Because the `dtype=datatime`, we can perform DataTime operations on the column<jupyter_code># Example: Just show the data, not the full date-time

df.time.dt.date
# Just show the time, not the full date-time

df.time.dt.time
# Just show the hour

df.time.dt.hour
# To see full help documentation for the DataTime properties, uncomment the following:

help(df.time.dt)<jupyter_output><empty_output><jupyter_text>Indexing Much of the same indexing methods avialable in Numpy can be applied to Pandas DataFrames and Series.  Column Indexing ...can be indexed by brackets or by .attribute accessor<jupyter_code># Recall the column names: they are your "indices"
df.columns
# "Indexing" by brackets and any one of the column names/labels

df['beer_name']
# Indexing into rows of the column

df['beer_name'][0:3]
 # Index by .attribute accessor
    
df.beer_name
# Indexing into rows of the column

df.beer_name[0:3]<jupyter_output><empty_output><jupyter_text>Columns are indexed first, then rows<jupyter_code>df['beer_name'][0]
df['beer_name'][0:3]
df.ix[0:2, 'beer_name']<jupyter_output><empty_output><jupyter_text>Summary: DataFrames and indexed by (1) by Column and then (2) by Row  Boolean arrays<jupyter_code># boolean series
df.abv < 5
# boolean indexing along rows
# The index value from the full dataframe is preserved
df[df.abv < 5]

# Note the number of rows left after the operation<jupyter_output><empty_output><jupyter_text>The `.loc` accessor The `.loc` accessor method allows for chained selection of rows and columns. The selection pattern is row first```df.loc[ row , column ]``` **Remember**: using `.loc` means that the right endpoint in a slice is returned.<jupyter_code>df.head(3)
df.loc[0,'abv']
df.loc[2,'time']<jupyter_output><empty_output><jupyter_text>The values passed to `.loc` for either rows or columns can be values in the indices or **boolean indexing arrays**.<jupyter_code>df.loc[df.abv < 5, ['beer_style', 'review_overall']]<jupyter_output><empty_output><jupyter_text>Using bitwise comparison operations between boolean arrays can provide complex selection logic.<jupyter_code># reviews written after June 2009 for beer with abv<5
(df.abv < 5) & (df.time > pd.Timestamp('2009-06'))
# reviews written after June 2009 for beer with abv<5 OR reviews with a rating >= 4.5
boolean_chain = ( 
                   ( (df.abv < 5)  & (df.time > pd.Timestamp('2009-06'))  )
                 | (df.review_overall >= 4.5)
                 )
df[boolean_chain]<jupyter_output><empty_output><jupyter_text>isin with strings<jupyter_code># .contains() method returns booleans...

df.beer_style.str.contains('IPA')
#... so .contain() can be used with .loc() as well:

df[df.beer_style.str.contains('IPA')]
# Summary statistics for hoppy (IPA) beers

df[ df.beer_style.str.contains('IPA') ].describe()
# Summary statistics for NOT hoppy (IPA) beers

df[ df.beer_style.str.contains('IPA')==False ].describe()<jupyter_output><empty_output><jupyter_text>Statistical Operations through Indexing<jupyter_code># count unique entries of beer_id

beer_ids = df.beer_id.value_counts(sort=True)  # default is sorted, but wanted to be explicit
beer_ids
# left is ID, right is number of times it appears
# All reviews for the top three most reviewed beers
print( "Top 3 \nBeer ID, Reviews \n", beer_ids[0:3], "\n" )

df[df.beer_id.isin(beer_ids[0:3].index)]
df[df.beer_id.isin(beer_ids[0:3].index)].beer_name.unique()<jupyter_output><empty_output><jupyter_text>***  Positional Indexing<jupyter_code>df.head(5)<jupyter_output><empty_output><jupyter_text>The `.iloc` accessor If you prefer NumPy-like indexing, use the `.iloc` accessor:Similar to `.loc`, the `.iloc` accessor allows row and column by integer position or slice only.<jupyter_code>## implied row
df.iloc[[2,5,10]]
# row, column
df.iloc[[2,5,10],0:3]<jupyter_output><empty_output><jupyter_text>***  The Index Object The pandas containers Series and DataFrame both have an associated Index* the Index can be a simple column of integers, such as 0, 1, 2, ..., but that is the least useful* the Index can be a column of lables, like string labels, such as "A", "B", "C".* the Index can be **hierarchical** (aka "MultiIndex") The `Index` is the farthest left column shown in the DataFrame output. The values are printed in bold. In this DataFrame each row is indexed by a unique integer from 0 to 49999.<jupyter_code># Reread the data file, to start fresh
df = pd.read_csv('data/beer2.csv.gz',
                  index_col=0,
                  parse_dates=['time'])
df.head()
df.index<jupyter_output><empty_output><jupyter_text>The values in the `Index` in this case are unique.* In general, an Index need not be unique (examples to come...)* There are many advantages to having a unique `Index`.<jupyter_code># The current index is unique
df.index.is_unique<jupyter_output><empty_output><jupyter_text>Changing the Index The `Index` of a DataFrame can be reset to any currently existing column or a custom Pandas `Index` object.<jupyter_code># What column names do we have to use as an Index?

list(df.columns)<jupyter_output><empty_output><jupyter_text>While each review is unique, we may want to reference this data set by other clasifications like `beer_id`.<jupyter_code># Use the 'beer_id' column as the Index

dfb = df.set_index('beer_id', inplace=False)  # default inplace=False, more on this later
dfb.head(20)
# Notice that even these beer IDs are not unique. That's okay.

dfb.index.is_unique
# Use .describe() to show beer_id is NOT unique
# Compare "count" vs "unique"

dfb.select_dtypes(include=['object']).describe()
# Example: This index is NOT unique: 
# There are 240 entries for beer_id 1904

len(dfb.loc[1904])<jupyter_output><empty_output><jupyter_text>Questions/Exercises:1. How many people reviewed Pliny The Elder?2. What was the average review overall?  Hierarchical Indexing The default Index has only 1 "level" and it is not named<jupyter_code># Inspect the Index attributes
print( type(df.index) )
print( df.index.nlevels )  # number/count of levels in the Index
print( df.index.names )    # names of levels in the Index<jupyter_output><empty_output><jupyter_text>The Index set to `beer_id` is named, but still has only 1 "level"<jupyter_code># Inspect the Index attributes
print( type(dfb.index) )
print( dfb.index.nlevels )  # number/count of levels in the Index
print( dfb.index.names )    # names of levels in the Index<jupyter_output><empty_output><jupyter_text>For high dimensional data, heirarchical indexing is ideal* Pandas supports multi-level indexing with `MultiIndex`* a `MultiIndex` can be built from multiple columns, e.g. `profile_name`, `beer_id`, and `time`. * This will aggregate the data by those columns* Each of these three categories is called a `level`.<jupyter_code># sort_index is required to fully associate profile_name with beer_id
reviews = df.set_index(['profile_name', 'beer_id', 'time']).sort_index()
reviews.head(5)

# Notice first 3 "column" labels: these are the Index levels<jupyter_output><empty_output><jupyter_text>Now the Index has 3 levels and each is named<jupyter_code># Inspect the Index attributes
print( type(reviews.index) )
print( reviews.index.nlevels )  # number/count of levels in the Index
print( reviews.index.names )    # names of levels in the Index<jupyter_output><empty_output><jupyter_text>Now we can easily subset the data using the levels of the hierarchical index<jupyter_code>ind_column = 'beer_name'
reviews[ind_column]
ind_column  = 'beer_name'
ind_profile = '05Harley'
reviews[ind_column][ind_profile]
ind_column  = 'beer_name'
ind_profile = '05Harley'
ind_beer_id = 1307
reviews[ind_column][ind_profile][ind_beer_id]
# The whole multiindex is unique
reviews.index.is_unique<jupyter_output><empty_output><jupyter_text>Using hierarchical index in not only more convenient, but faster than using fancy and boolean selection.<jupyter_code># Create a Series, indexed by profile_name (people who reviewed beers)
# Take the top 5 most prolific reviewers.
top_reviewers = (reviews
                        .index
                        .get_level_values('profile_name')
                        .value_counts()
                        .head(5)
                )
top_reviewers
# The .index attribute is itself a Numpy-like array
top_reviewers.index<jupyter_output><empty_output><jupyter_text>Iterating over the MultiIndex<jupyter_code>type(reviews)
reviews.head(3)
# Example: iterating with a hierarchical index

for index, row in reviews.head().iterrows():
    print('Index:', index, ', Row:', '%s' % row['abv'])
# Example: iterating with a hierarchical index, printing only one level

for index, row in reviews.head().iterrows():
    print('Index:beer_id', index[1], ', Row:', '%s' % row['abv'])<jupyter_output><empty_output><jupyter_text>MultiIndex .loc accessor When using MultiIndex and Hierarchical indexers* the `.loc` accessor now requires entries for each `level`. * a slice or index value can be provided for each level.Usage:```df.loc[level0, level1, level2, etc.]``` **Remember**: using `.loc` means that the right endpoint in a slice is returned. **INCLUSIVE**<jupyter_code>pd.options.display.max_rows=4
reviews.loc[top_reviewers.index, :, :]<jupyter_output><empty_output><jupyter_text>The more complete usage of MulitIindex and `.loc` is to provide the index selection in a tuple whose length is the number of `level`s.<jupyter_code>reviews.loc[[('BeerFMAndy',100,pd.Timestamp('2009-10-22 03:39:21'))]]<jupyter_output><empty_output><jupyter_text>Multi-axis indexers<jupyter_code>x = [9,10,11,12]
# Here are the scores for all 91 of BeerFMAndy's reviews
# for November only
idx=pd.IndexSlice
reviews.loc[idx['BeerFMAndy',:,'2009-11':'2009-11'],['beer_name','review_overall']]
slice?
# Alternative syntax using slice objects
# slice(None) == :
reviews.loc[('BeerFMAndy',slice(None),slice('2009-11','2009-11')),['beer_name','review_overall']]
reviews.loc[idx[top_reviewers.index, [92,54919], :], ['beer_name','beer_style']]
# SQL like query
(reviews
        .query('profile_name in @top_reviewers.index and \
                beer_id in [92,54919]')
        [['beer_name','beer_style']]
 )
# All review for 92 and 54919
reviews.loc[idx[:, [92, 54919], :], ['beer_name' ,'beer_style']]<jupyter_output><empty_output><jupyter_text>Categorical data types In addition to the Numpy `dtypes` Pandas introduces a `categorical` dtype.  Overview of categoricals The categorical data type is useful in the following cases:* A string variable consisting of only a few different values.     * Converting such a string variable to a categorical variable will save some memory.* The lexical order of a variable is not the same as the logical order (“one”, “two”, “three”).     * By converting to a categorical and specifying an order on the categories, sorting and min/max will use the logical order instead of the lexical order.* As a signal to other python libraries that this column should be treated as a categorical variable    * e.g. to use suitable statistical methods or plot types.<jupyter_code>df.info()<jupyter_output><empty_output><jupyter_text>Categorical Example: Decrease Memory Use Candidates for `categorical` dtypes are columns that have only a small fraction of unique strings. In the next cell we'll see that there are only 104 unique `beer_style`s out of the 50000 independent reviews.<jupyter_code># Select all columns of dtype object 
# Find out which ones have fewest unique values

df.select_dtypes(include=['object']).describe()
df[['beer_style']]
# fancy selection returns a DataFrame (not a Series) with a single column:

df[['beer_style']].info()<jupyter_output><empty_output><jupyter_text>Convert to categorical:* `beer_style` column converted to a `categorical`* reassign column back into the DataFrame* memory usage for this column has decreased!<jupyter_code>df['beer_style'] = df['beer_style'].astype('category')
df[['beer_style']].info()<jupyter_output><empty_output><jupyter_text>A slightly harder way to select IPA styles<jupyter_code>cats = (df
          .beer_style
          .cat
          .categories[df.beer_style.cat.categories.str.contains('IPA')]
        )
cats
# generate the boolean indexer
df.beer_style.isin(cats)
df[df.beer_style.isin(cats)]<jupyter_output><empty_output><jupyter_text>Categoricals: How it Works `categorical` series or columns store only the unique strings and provide an integer `code` lookup. Notice the dtype of the code series object. Pandas will select the smallest allowable dtype for the number of unique categories.<jupyter_code>df.beer_style.cat.codes
df.beer_style.cat.categories
df.beer_style.cat.ordered<jupyter_output><empty_output><jupyter_text>Section Review Examining Data* Reading data files* Inspecting the DataFrame* Examining Columns   * Selecting Columns   * Adding and Removing Columns   * String operations   * DateTime operations* Indexing   * Attribute and Bracket style indexing   * Column then row indexing   * Setting/Changing the Index   * Boolean indexing   * Hierarchical indexing with MultiIndex   * .loc() and .iloc()* Categorical dtype ***  Pandas GroupBy Overview  Set-Up<jupyter_code>import numpy as np

import matplotlib.pyplot as plt
%matplotlib inline

import pandas as pd
pd.options.display.max_rows    = 6
pd.options.display.max_columns = 6
pd.options.display.width       = 80<jupyter_output><empty_output><jupyter_text>What is Groupby? Groupby is a fundamental operation in data analysis and is well supported in pandas.> http://pandas.pydata.org/pandas-docs/stable/groupby.htmlA “group by” involves one or more of the following:1. **Split** a table into groups2. **Apply** a function to each groups3. **Combine** the resultsThe split and combine are often straight-forward. The apply is more interestingIn the apply step, perform one or more of the following:* **Aggregation:** compute a summary statistic, e.g. count, sum, mean, or std.* **Transformation:** group-specific computations and return a like-indexed.* **Filtration:** discard some groups, filtering based on mean  Groupby Input Types In pandas the first step in a groupby looks like this: ``df.groupby( grouper )`` `grouper` can be many things- **``Series``** e.g. `df['A']` used as `df.groupby(df['A'])`- **`string`** e.g. `"A"` is a column label, used as `df.groupby('A')`, which is equivalent to `df.groupby(df['A'])`- **function** (to be applied on the index)- **dict** : groups by *values*- **`levels=[]`**, names of levels in a MultiIndex  Quick Demo A very brief example of the power of pandas groupby:  Read Data<jupyter_code>file = "data/exoplanets.csv"
data = pd.read_csv(file, parse_dates=True, index_col='DATE')
data.head(3)<jupyter_output><empty_output><jupyter_text>Create a groupby object<jupyter_code>grouped = data.groupby(lambda x: x.year)
grouped
len(grouped.groups)<jupyter_output><empty_output><jupyter_text>Split<jupyter_code>grouped.get_group(1997)<jupyter_output><empty_output><jupyter_text>Apply<jupyter_code># What can we aggregate? How about distance away from Earth
data.columns
grouped.agg('mean')['DIST(PARSEC)']

# 14 Parsecs... Pfsss!<jupyter_output><empty_output><jupyter_text>Visualize<jupyter_code># Early detections were planets around nearby stars
# Later detections found around stars farther away

grouped.agg('mean')['DIST(PARSEC)'].plot()<jupyter_output><empty_output><jupyter_text>GroupBy with Real Data  Choose your Groups We'll use the same dataset of beer reviews seen in previous notebooks.<jupyter_code>df = pd.read_csv('data/beer2.csv.gz',
                  index_col=0,
                  parse_dates=['time'])<jupyter_output><empty_output><jupyter_text>The unique values in the column (in this case, `beer_style`) will make up the **keys** or "group names" in the groupby.<jupyter_code># Example values in the `beer_style` column

df['beer_style'].iloc[0:5]
# The unique values in the `beer_style` column will be the keys of our groupby
print( len( df['beer_style'] ) )
print( len( df['beer_style'].unique() ) )<jupyter_output><empty_output><jupyter_text>Create GroupBy Object<jupyter_code>gr = df.groupby('beer_style')

print( type( gr ) )
# The number of groups matches the number of unique entries in the column

gr.ngroups
# Example of those column entries

list(gr.groups)[0:5]<jupyter_output><empty_output><jupyter_text>Split<jupyter_code># Separating one group from many

df_dunkel = gr.get_group('Dunkelweizen')
df_dunkel.info()

# note the number of rows is much less!
len(df_dunkel)
# The ordering of the columsn is not ideal for viewing results...

df_dunkel.head()
# For convenient viewing of groupby results
# Rearrange the columns such that the groupby keys are first

cols = ['beer_style'] + df.columns.difference(['beer_style']).tolist()
cols
# Another group: Separate the 'Tripel' group from the others

df_tripel = gr.get_group('Tripel')[cols]
df_tripel.head()
# Inspet the group

df_tripel.info()
# Alternatively, you could extract a "group" using .loc()

df.loc[df.beer_style=='Tripel',cols]<jupyter_output><empty_output><jupyter_text>Apply In the **split-apply-combine** strategy, during the apply step, one often aggregates across a group<jupyter_code>type(gr)
# Aggregation methods apply to each group separately.

gr.agg('mean')<jupyter_output><empty_output><jupyter_text>This says apply the `mean` function to each column. Non-numeric columns (nuisance columns) are excluded. We can also select a subset of columns to perform the aggregation on.<jupyter_code># Apply aggregation method only to a subset of columns

review_columns = ['abv','review_overall','review_appearance',
                  'review_palate','review_taste']
gr[review_columns].agg('mean')<jupyter_output><empty_output><jupyter_text>The `.` attribute lookup works as well.<jupyter_code>gr.abv.agg('mean')<jupyter_output><empty_output><jupyter_text>Find the `beer_style` with the greatest variance in `abv`.<jupyter_code># ex: groupby, then select abv, then aggregrate with std, then sort

(df
   .groupby('beer_style')
   .abv
   .std()
   .sort_values(ascending=False)
 )<jupyter_output><empty_output><jupyter_text>Note: the pattern used above is a common idiom in Pandas: a string of `.method()` calls. Multiple Aggregations on one column<jupyter_code>gr['review_aroma'].agg([np.mean, np.std, 'count'])<jupyter_output><empty_output><jupyter_text>Single Aggregation on multiple columns<jupyter_code>gr[review_columns].mean()<jupyter_output><empty_output><jupyter_text>Multiple aggregations on multiple columns<jupyter_code>result = gr[review_columns].agg(['mean', 'count', 'std'])
result.columns.names=['characteristic','measure']
result<jupyter_output><empty_output><jupyter_text>Transform Combined Series / DataFrame is the same shape as the input. Example: standardize the reviews by subtracting the mean.<jupyter_code># Define a function to perform the demeaning

def de_mean(reviews):
    s = reviews - reviews.mean()
    return s
# Demonstrate the demeaning function acting on a Series

de_mean(df.review_overall)
# Use transform() to apply the demean function to grouped data

df.groupby('profile_name').transform(de_mean)<jupyter_output><empty_output><jupyter_text>Performance: Often, it is better to work with the groupby object directly<jupyter_code># Rather than applying the de_mean(), 
# use the built-in pandas method mean() and 
# move the arithmetic up to "df - df.groupby()"

(

    df - df.groupby('profile_name').transform('mean')

).select_dtypes(exclude=['object'])
%timeit df.groupby('profile_name').transform(de_mean)
%timeit (df-df.groupby('profile_name').transform('mean')).select_dtypes(exclude=['object'])
df.groupby('profile_name').ngroups<jupyter_output><empty_output><jupyter_text>***  GroupBy with Multiple Columns You can group by **levels** of a MultiIndex.To understand this, we need to talk about `stack()` and `unstack()` ***<jupyter_code>import continuum_style; continuum_style.style()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11026882978914958, 'fraction_numerical': 0.011865963231719394, 'mean_word_length': 5.460912052117264, 'pattern_counts': {'":': 0, '<': 474, '<?xml version=': 0, '>': 474, 'https://': 19, 'lorem ipsum': 0, 'www.': 8, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '115', 'dir': 'jupyter-structured-clean-dedup', 'id': '23227', 'license': 'MIT', 'n_tokens_mistral': 18245, 'n_tokens_neox': 17452, 'n_words': 5763, 'path': 'modules/module_5_3_2/strata_pandas.ipynb', 'repo_name': 'ryanjmccall/sb_ml_eng_capstone', 'size': '55573'}