<jupyter_start><jupyter_text>Intermediate pandasNow that we are familiar with pandas' data structures, we can turn our attention to some of the intermediate features of data frames, which include:    - Easy handling of **missing data**- Intelligent label-based **slicing, fancy indexing, and subsetting** of large data sets- **Hierarchical labeling** of axes- **Sorting and ranking** of data in DataFrames- Data **summarization** tools In this section, we will manipulate data collected from ocean-going vessels on the eastern seaboard. Vessel operations are monitored using the **Automatic Identification System (AIS)**, a safety at sea navigation technology which vessels are required to maintain and that uses transponders to transmit very high frequency (VHF) radio signals containing static information including ship name, call sign, and country of origin, as well as dynamic information unique to a particular voyage such as vessel location, heading, and speed. ![AIS](images/ais.gif)The International Maritime Organization’s (IMO) International Convention for the Safety of Life at Sea requires functioning AIS capabilities on all vessels 300 gross tons or greater and the US Coast Guard requires AIS on nearly all vessels sailing in U.S. waters. The Coast Guard has established a national network of AIS receivers that provides coverage of nearly all U.S. waters. **AIS signals** are transmitted several times each minute and the network is capable of handling thousands of reports per minute and updates as often as every two seconds. Therefore, a typical voyage in our study might include the transmission of hundreds or thousands of AIS encoded signals. This provides a rich source of spatial data that includes both **spatial and temporal information**.For our purposes, we will use **summarized data** that describes the transit of a given vessel through a particular administrative area. The data includes the start and end time of the transit segment, as well as information about the speed of the vessel, how far it travelled, etc.<jupyter_code>import pandas as pd
import numpy as np

vessels = pd.read_csv('../data/AIS/vessel_information.csv', index_col=0)<jupyter_output><empty_output><jupyter_text>Indexing and SelectionIndexing works analogously to indexing in NumPy arrays, except we can use the labels in the `Index` object to extract values in addition to arrays of integers.<jupyter_code># Sample Series object
flag = vessels.flag
flag
# Numpy-style indexing
flag[:10]
# Indexing by label
flag[[298716,725011300]]<jupyter_output><empty_output><jupyter_text>In a `DataFrame` we can slice along either or both axes:<jupyter_code>vessels[['num_names','num_types']]
vessels[vessels.max_loa>700]<jupyter_output><empty_output><jupyter_text>The indexing field `loc` allows us to select subsets of rows and columns in an intuitive way:<jupyter_code>vessels.loc[720768000, ['names','flag', 'type']]
vessels.loc[:4731, 'names']<jupyter_output><empty_output><jupyter_text>Slicing also works with string variables, since an index has an intrinsic order, regardless of label:<jupyter_code>vessels.loc[:310, 'flag':'loa']<jupyter_output><empty_output><jupyter_text>In addition to using `loc` to select rows and columns by **label**, pandas also allows indexing by **position** using the `iloc` attribute.So, we can query rows and columns by absolute position, rather than by name:<jupyter_code>vessels.iloc[:5, 5:8]<jupyter_output><empty_output><jupyter_text>ExerciseYou can use the `isin` method query a DataFrame based upon a list of values as follows:     data['color'].isin(['red', 'blue'])Use `isin` to find all ships that registered in either Denmark or Japan. How many records contain these values?<jupyter_code># Write your answer here<jupyter_output><empty_output><jupyter_text>Indexing with `where`Pandas `DataFrame` objects also posess a `where` index for indexing that returns the values that satisfy the condition, but retain the index of the original `DataFrame`, so that the shape does not change. This is important when **alignment** is required for operations between `DataFrame`s.<jupyter_code>np.random.seed(42)
normal_vals = pd.DataFrame({'x{}'.format(i):np.random.randn(100) for i in range(5)})

normal_vals.head()
normal_vals.where(normal_vals>0).head()<jupyter_output><empty_output><jupyter_text>`where` includes an optional `other` argument that accepts a scalar or tabular values (or a callable) to replace values in the `DataFrame` that do not satisfy the condition.For example, we can use this to return the absolute values of `X`:<jupyter_code>normal_vals.where(normal_vals>0, other=-normal_vals).head()<jupyter_output><empty_output><jupyter_text>Similarly, a callable can be used when we need to modify the replaced value:<jupyter_code>normal_vals.where(normal_vals>0, other=lambda y: -y*100).head()<jupyter_output><empty_output><jupyter_text>Conversely, `mask` is the inverse boolean of `where`:<jupyter_code>normal_vals.mask(normal_vals>0).head()<jupyter_output><empty_output><jupyter_text>Selection with `query`At times, selection using indexing can be verbose because it requires repeated use of the `DataFrame` namespace.<jupyter_code>normal_vals[(normal_vals.x1 > normal_vals.x0) & (normal_vals.x3 > normal_vals.x2)].head()<jupyter_output><empty_output><jupyter_text>For a more concise (and readable) syntax, we can use the new `query` method to perform selection on a `DataFrame`. Instead of having to type the fully-specified column, we can simply pass a string that describes what to select. The query above is then simply:<jupyter_code>normal_vals.query('(x1 > x0) & (x3 > x2)').head()<jupyter_output><empty_output><jupyter_text>The `DataFrame.index` and `DataFrame.columns` are placed in the query namespace by default. If you want to refer to a variable in the current namespace, you can prefix the variable with `@`:<jupyter_code>min_loa = 700
vessels.query('max_loa > @min_loa')<jupyter_output><empty_output><jupyter_text>Operations`DataFrame` and `Series` objects allow for several operations to take place either on a single object, or between two or more objects.For example, we can perform arithmetic on the elements of two objects, such as combining distances travelled across years. First, let's artificially construct two Series, consisting of segment lengths travelled in 2009 and 2010:<jupyter_code>segments = pd.read_csv('../data/AIS/transit_segments.csv')
segments['st_time'] = pd.to_datetime(segments['st_time'])
segments['year'] = segments.st_time.apply(lambda x: x.year)
segments.head()
segments2009 = segments[segments.year==2009]
lengths2009 = pd.Series({name: segments2009[segments2009.name==name].seg_length.sum() 
                         for name in segments2009.name.unique()})
segments2010 = segments[segments.year==2010]
lengths2010 = pd.Series({name: segments2010[segments2010.name==name].seg_length.sum() 
                         for name in segments2010.name.unique()})<jupyter_output><empty_output><jupyter_text>We will learn a much more efficient way of performing this operation when we get to `GroupBy` operations a little later on.<jupyter_code>length2009 = segments.loc[segments.year==2009, 'seg_length']
length2009.index = segments.name[segments.year==2009]

length2010 = segments.loc[segments.year==2010, 'seg_length']
length2010.index = segments.name[segments.year==2010]<jupyter_output><empty_output><jupyter_text>Now, let's add them together, in hopes of getting 2-year travel totals:<jupyter_code>seg_lengths = lengths2009 + lengths2010
seg_lengths<jupyter_output><empty_output><jupyter_text>Pandas' data alignment places `NaN` values for labels that do not overlap in the two Series. In fact, the majority of ships only have data for one of the two years.<jupyter_code>seg_lengths.notnull().mean()<jupyter_output><empty_output><jupyter_text>While we do want the operation to honor the data labels in this way, we probably do not want the missing values to be filled with `NaN`. We can use the `add` method to calculate ship travel totals by using the `fill_value` argument to insert a zero for years that do not have data for a particular vessel:<jupyter_code>lengths2009.add(lengths2010, fill_value=0)<jupyter_output><empty_output><jupyter_text>Operations can also be **broadcast** between rows or columns.For example, if we subtract the maximum LOA (ship length) from the `max_loa` column, we get a the differences between the size of a given vessel to the largest vessel.<jupyter_code>vessels.max_loa - vessels.max_loa.max()<jupyter_output><empty_output><jupyter_text>Or, looking at things row-wise, we can see how a particular vessel compares with the rest of the group with respect to important features:<jupyter_code>vessels[vessels.max_loa==vessels.max_loa.max()]
recs = vessels[['num_names','num_loas', 'max_loa', 'num_types']]
diff = recs - recs.loc[354092000]
diff[:10]<jupyter_output><empty_output><jupyter_text>We can also apply functions to each column or row of a `DataFrame`<jupyter_code>recs.apply(np.median)
def range_calc(x):
    return x.max() - x.min()
stat_range = lambda x: x.max() - x.min()
recs.apply(stat_range)<jupyter_output><empty_output><jupyter_text>Sorting and RankingPandas objects include methods for re-ordering data.<jupyter_code>segments.sort_index().head()
segments.sort_index(ascending=False).head()<jupyter_output><empty_output><jupyter_text>Try sorting the **columns** instead of the rows, in ascending order:<jupyter_code>segments.sort_index(axis=1).head()<jupyter_output><empty_output><jupyter_text>We can also use `sort_values` to sort a `Series` by value, rather than by label.<jupyter_code>segments.seg_length.sort_values(ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>For a `DataFrame`, we can sort according to the values of one or more columns using the `by` argument of `sort_values`:<jupyter_code>segments[['avg_sog','max_sog','min_sog']].sort_values(ascending=[False,True], 
                                           by=['max_sog', 'min_sog']).head(10)<jupyter_output><empty_output><jupyter_text>**Ranking** does not re-arrange data, but instead returns an index that ranks each value relative to others in the Series.<jupyter_code>segments.avg_sog.rank()<jupyter_output><empty_output><jupyter_text>Ties are assigned the mean value of the tied ranks, which may result in decimal values.<jupyter_code>pd.Series([100,100]).rank()<jupyter_output><empty_output><jupyter_text>Alternatively, you can break ties via one of several methods, such as by the order in which they occur in the dataset:<jupyter_code>segments.avg_sog.rank(method='first').sort_values()<jupyter_output><empty_output><jupyter_text>Calling the `DataFrame`'s `rank` method results in the ranks of all columns:<jupyter_code>segments.rank(ascending=False).head()<jupyter_output><empty_output><jupyter_text>Hierarchical indexingWhile the `mmsi` (Maritime Mobile Service Identity) field represents a unique identifier in the `vessels` table, it does not in the `segments` table.We can user hierarchical indexing to build a unique index, however, that may be more meaningful than the ordered set of integers that are given as a default index.<jupyter_code>segments_hier = segments.set_index(['mmsi', 'name', 'year', 'transit', 'segment'])
segments_hier.head(10)
np.random.randint(0, len(segments))
segments.loc[np.random.randint(0, len(segments))]<jupyter_output><empty_output><jupyter_text>This index is a `MultiIndex` object that consists of a sequence of tuples, the elements of which is some combination of the three columns used to create the index. Where there are multiple repeated values, Pandas does not print the repeats, making it easy to identify groups of values.<jupyter_code>segments_hier.index.is_unique<jupyter_output><empty_output><jupyter_text>Try using this hierarchical index to retrieve the first segment of the tenth transit of the *Sentinel* (mmsi=366766980):<jupyter_code>segments_hier.loc[(366766980, 'Sentinel', 2009, 10, 1)]<jupyter_output><empty_output><jupyter_text>With a hierachical index, we can select subsets of the data based on a *partial* index:<jupyter_code>segments.loc[9]<jupyter_output><empty_output><jupyter_text>Hierarchical indices can be created on either or both axes. Here is a trivial example:<jupyter_code>frame = pd.DataFrame(np.arange(12).reshape(( 4, 3)), 
                  index =[['a', 'a', 'b', 'b'], [1, 2, 1, 2]], 
                  columns =[['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']])

frame<jupyter_output><empty_output><jupyter_text>If you want to get fancy, both the row and column indices themselves can be given names:<jupyter_code>frame.index.names = ['key1', 'key2']
frame.columns.names = ['state', 'color']
frame<jupyter_output><empty_output><jupyter_text>With this, we can do all sorts of custom indexing:<jupyter_code>frame.loc['a', 'Ohio']<jupyter_output><empty_output><jupyter_text>Try retrieving the value corresponding to `b2` in `Colorado`:<jupyter_code># Write your answer here<jupyter_output><empty_output><jupyter_text>Additionally, the order of the set of indices in a hierarchical `MultiIndex` can be changed by swapping them pairwise:<jupyter_code>segments_hier.swaplevel('mmsi', 'name').head()
segments_hier.swaplevel('mmsi', 'name').loc['Sentinel']<jupyter_output><empty_output><jupyter_text>Alternately, one can index based on an arbitrary level using the `xs` (cross-section) method:<jupyter_code>segments_hier.xs('Sentinel', level='name')<jupyter_output><empty_output><jupyter_text>Data can also be sorted by any index level, using `sortlevel`:<jupyter_code>segments.sortlevel('max_sog', ascending=False).head()<jupyter_output><empty_output><jupyter_text>Missing dataThe occurence of missing data is so prevalent that it pays to use tools like Pandas, which seamlessly integrates missing data handling so that it can be dealt with easily, and in the manner required by the analysis at hand.Missing data are represented in `Series` and `DataFrame` objects by the `NaN` floating point value. However, `None` is also treated as missing, since it is commonly used as such in other contexts (*e.g.* NumPy).<jupyter_code>foo = pd.Series([np.nan, -3, None, 'foobar'])
foo
foo.isnull()<jupyter_output><empty_output><jupyter_text>To illustrate how to deal with missing values in pandas, we will import an educational database, which reports test scores for children with hearing disabilities, along with associated covariates, several of which are missing.<jupyter_code>testing = pd.read_csv('../data/test_scores.csv', index_col=0)
testing.head(10)<jupyter_output><empty_output><jupyter_text>Missing values may be dropped or indexed out: By default, `dropna` drops entire rows in which one or more values are missing.<jupyter_code>testing.dropna().head(10)<jupyter_output><empty_output><jupyter_text>A similar result can be obtained via indexing.<jupyter_code>testing.isnull().head()
testing[testing.notnull()].head()<jupyter_output><empty_output><jupyter_text>This can be overridden by passing the `how='all'` argument, which only drops a row when every field is a missing value.<jupyter_code>testing.dropna(how='all')<jupyter_output><empty_output><jupyter_text>This can be customized further by specifying how many values need to be present before a row is dropped via the `thresh` argument.<jupyter_code>testing.dropna(thresh=10)<jupyter_output><empty_output><jupyter_text>This is typically used in time series applications, where there are repeated measurements that are incomplete for some subjects.  ExerciseTry using the `axis` argument to drop columns with missing values:<jupyter_code># Write your answer here<jupyter_output><empty_output><jupyter_text>Rather than omitting missing data from an analysis, in some cases it may be suitable to fill the missing value in, either with a default value (such as zero) or a value that is either imputed or carried forward/backward from similar data points. We can do this programmatically in Pandas with the `fillna` argument.<jupyter_code>testing.fillna(0).head(10)
testing.fillna({'family_inv': 0, 'mother_hs':1}).head(10)<jupyter_output><empty_output><jupyter_text>Notice that `fillna` by default returns a new object with the desired filling behavior, rather than changing the `Series` or  `DataFrame` in place (**in general, we like to do this, by the way!**). Missing values can also be interpolated, using any one of a variety of methods:<jupyter_code>testing.fillna(method='bfill').head(10)<jupyter_output><empty_output><jupyter_text>Data summarizationWe often wish to summarize data in `Series` or `DataFrame` objects, so that they can more easily be understood or compared with similar data. The NumPy package contains several functions that are useful here, but several summarization or reduction methods are built into Pandas data structures.<jupyter_code>testing.sum()<jupyter_output><empty_output><jupyter_text>Clearly, `sum` is more meaningful for some columns than others. For methods like `mean` for which application to string variables is not just meaningless, but impossible, these columns are automatically exculded:<jupyter_code>testing.mean()<jupyter_output><empty_output><jupyter_text>The important difference between NumPy's functions and Pandas' methods is that the latter have built-in support for handling missing data. Sometimes we may not want to ignore missing values, and allow the `nan` to propagate.<jupyter_code>testing.mean(skipna=False)<jupyter_output><empty_output><jupyter_text>Passing `axis=1` will summarize over rows instead of columns, which only makes sense in certain situations.<jupyter_code>nonenglish_nonwhite_withHS = testing[['non_english', 'mother_hs', 'non_white']].prod(axis=1)

nonenglish_nonwhite_withHS<jupyter_output><empty_output><jupyter_text>A useful summarization that gives a quick snapshot of multiple statistics for a `Series` or `DataFrame` is `describe`:<jupyter_code>testing.describe()<jupyter_output><empty_output><jupyter_text>`describe` can detect non-numeric data and sometimes yield useful information about it.<jupyter_code>testing.non_english.describe()<jupyter_output><empty_output><jupyter_text>We can also calculate summary statistics *across* multiple columns, for example, correlation and covariance.$$cov(x,y) = \sum_i (x_i - \bar{x})(y_i - \bar{y})$$<jupyter_code>testing.score.cov(testing.age_test)<jupyter_output><empty_output><jupyter_text>$$corr(x,y) = \frac{cov(x,y)}{(n-1)s_x s_y} = \frac{\sum_i (x_i - \bar{x})(y_i - \bar{y})}{\sqrt{\sum_i (x_i - \bar{x})^2 \sum_i (y_i - \bar{y})^2}}$$<jupyter_code>testing.score.corr(testing.age_test)<jupyter_output><empty_output><jupyter_text>Try running `corr` on the entire `baseball` DataFrame to see what is returned:<jupyter_code># Write answer here<jupyter_output><empty_output><jupyter_text>If we have a `DataFrame` with a hierarchical index (or indices), summary statistics can be applied with respect to any of the index levels:<jupyter_code>segments_hier.head()
segments_hier.sum(level='transit').seg_length<jupyter_output><empty_output><jupyter_text>Writing Data to FilesAs well as being able to read several data input formats, Pandas can also export data to a variety of storage formats. We will bring your attention to just a couple of these.<jupyter_code>testing.to_csv("testing.csv")<jupyter_output><empty_output><jupyter_text>The `to_csv` method writes a `DataFrame` to a comma-separated values (csv) file. You can specify custom delimiters (via `sep` argument), how missing values are written (via `na_rep` argument), whether the index is writen (via `index` argument), whether the header is included (via `header` argument), among other options. An efficient way of storing data to disk is in binary format. Pandas supports this using Python’s built-in pickle serialization.<jupyter_code>testing.to_pickle("testing_pickle")<jupyter_output><empty_output><jupyter_text>The complement to `to_pickle` is the `read_pickle` function, which restores the pickle to a `DataFrame` or `Series`:<jupyter_code>pd.read_pickle("testing_pickle").head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0971067177929386, 'fraction_numerical': 0.01284796573875803, 'mean_word_length': 6.875294117647059, 'pattern_counts': {'":': 0, '<': 253, '<?xml version=': 0, '>': 263, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '63', 'dir': 'jupyter-structured-clean-dedup', 'id': '123707', 'license': 'CC0-1.0', 'n_tokens_mistral': 6320, 'n_tokens_neox': 5993, 'n_words': 2049, 'path': 'notebooks/1.2 - Intermediate Pandas.ipynb', 'repo_name': 'alanponce/ngcm_pandas_2016', 'size': '20081'}