import pandas as pd
import numpy as np

vessels = pd.read_csv('../data/AIS/vessel_information.csv', index_col=0)

"""
Explanation: Intermediate pandas
Now 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. 

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.
End of explanation
"""


# Sample Series object
flag = vessels.flag
flag

# Numpy-style indexing
flag[:10]

# Indexing by label
flag[[298716,725011300]]

"""
Explanation: Indexing and Selection
Indexing 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.
End of explanation
"""


vessels[['num_names','num_types']]

vessels[vessels.max_loa>700]

"""
Explanation: In a DataFrame we can slice along either or both axes:
End of explanation
"""


vessels.loc[720768000, ['names','flag', 'type']]

vessels.loc[:4731, 'names']

"""
Explanation: The indexing field loc allows us to select subsets of rows and columns in an intuitive way:
End of explanation
"""


vessels.loc[:310, 'flag':'loa']

"""
Explanation: Slicing also works with string variables, since an index has an intrinsic order, regardless of label:
End of explanation
"""


vessels.iloc[:5, 5:8]

"""
Explanation: 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:
End of explanation
"""


# Write your answer here

"""
Explanation: Exercise
You 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?
End of explanation
"""


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()

"""
Explanation: 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 DataFrames.
End of explanation
"""


normal_vals.where(normal_vals>0, other=-normal_vals).head()

"""
Explanation: 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:
End of explanation
"""


normal_vals.where(normal_vals>0, other=lambda y: -y*100).head()

"""
Explanation: Similarly, a callable can be used when we need to modify the replaced value:
End of explanation
"""


normal_vals.mask(normal_vals>0).head()

"""
Explanation: Conversely, mask is the inverse boolean of where:
End of explanation
"""


normal_vals[(normal_vals.x1 > normal_vals.x0) & (normal_vals.x3 > normal_vals.x2)].head()

"""
Explanation: Selection with query
At times, selection using indexing can be verbose because it requires repeated use of the DataFrame namespace.
End of explanation
"""


normal_vals.query('(x1 > x0) & (x3 > x2)').head()

"""
Explanation: 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:
End of explanation
"""


min_loa = 700

vessels.query('max_loa > @min_loa')

"""
Explanation: 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 @:
End of explanation
"""


segments = pd.read_csv('../data/AIS/transit_segments.csv', parse_dates=['st_time'])

segments['year'] = segments.st_time.dt.year
segments.year.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()})

"""
Explanation: Operations
DataFrame and Series objects allow for several operations to take place either on a single object, or between two or more objects.
For this section, we will import a complementary dataset of transits (voyages) of each vessel. This is a much larger dataset, since ships each have multiple transits over several years.
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:
End of explanation
"""


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]

"""
Explanation: We will learn a much more efficient way of performing this operation when we get to GroupBy operations a little later on.
End of explanation
"""


seg_lengths = lengths2009 + lengths2010
seg_lengths

"""
Explanation: Now, let's add them together, in hopes of getting 2-year travel totals:
End of explanation
"""


seg_lengths.notnull().mean()

"""
Explanation: 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.
End of explanation
"""


lengths2009.add(lengths2010, fill_value=0)

"""
Explanation: 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:
End of explanation
"""


vessels.max_loa - vessels.max_loa.max()

"""
Explanation: 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.
End of explanation
"""


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]

"""
Explanation: 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:
End of explanation
"""


recs.apply(np.median)

def range_calc(x):
    return x.max() - x.min()

recs.apply(range_calc)

"""
Explanation: We can also apply functions to each column or row of a DataFrame
End of explanation
"""


segments.sort_index().head()

segments.sort_index(ascending=False).head()

"""
Explanation: Sorting and Ranking
Pandas objects include methods for re-ordering data.
End of explanation
"""


segments.sort_index(axis=1).head()

"""
Explanation: Try sorting the columns instead of the rows, in ascending order:
End of explanation
"""


segments.seg_length.sort_values(ascending=False).head(10)

"""
Explanation: We can also use sort_values to sort a Series by value, rather than by label.
End of explanation
"""


segments[['avg_sog','max_sog','min_sog']].sort_values(ascending=[False,True], 
                                           by=['max_sog', 'min_sog']).head(10)

"""
Explanation: For a DataFrame, we can sort according to the values of one or more columns using the by argument of sort_values:
End of explanation
"""


segments.avg_sog.rank()

"""
Explanation: Ranking does not re-arrange data, but instead returns an index that ranks each value relative to others in the Series.
End of explanation
"""


pd.Series([100,100]).rank()

"""
Explanation: Ties are assigned the mean value of the tied ranks, which may result in decimal values.
End of explanation
"""


segments.avg_sog.rank(method='first').sort_values()

"""
Explanation: Alternatively, you can break ties via one of several methods, such as by the order in which they occur in the dataset:
End of explanation
"""


segments.rank(ascending=False).head()

"""
Explanation: Calling the DataFrame's rank method results in the ranks of all columns:
End of explanation
"""


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))]

"""
Explanation: Hierarchical indexing
While 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.
End of explanation
"""


segments_hier.index.is_unique

"""
Explanation: 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.
End of explanation
"""


segments_hier.loc[(366766980, 'Sentinel', 2009, 10, 1)]

"""
Explanation: Try using this hierarchical index to retrieve the first segment of the tenth transit of the Sentinel (mmsi=366766980):
End of explanation
"""


segments.loc[9]

"""
Explanation: With a hierachical index, we can select subsets of the data based on a partial index:
End of explanation
"""


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

"""
Explanation: Hierarchical indices can be created on either or both axes. Here is a trivial example:
End of explanation
"""


frame.index.names = ['key1', 'key2']
frame.columns.names = ['state', 'color']
frame

"""
Explanation: If you want to get fancy, both the row and column indices themselves can be given names:
End of explanation
"""


frame.loc['a', 'Ohio']

"""
Explanation: With this, we can do all sorts of custom indexing:
End of explanation
"""


# Write your answer here

"""
Explanation: Try retrieving the value corresponding to b2 in Colorado:
End of explanation
"""


segments_hier.swaplevel('mmsi', 'name').head()

segments_hier.swaplevel('mmsi', 'name').loc['Sentinel']

"""
Explanation: Additionally, the order of the set of indices in a hierarchical MultiIndex can be changed by swapping them pairwise:
End of explanation
"""


segments_hier.xs('Sentinel', level='name')

"""
Explanation: Alternately, one can index based on an arbitrary level using the xs (cross-section) method:
End of explanation
"""


segments.sortlevel('max_sog', ascending=False).head()

"""
Explanation: Data can also be sorted by any index level, using sortlevel:
End of explanation
"""


foo = pd.Series([np.nan, -3, None, 'foobar'])
foo

foo.isnull()

"""
Explanation: Missing data
The 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).
End of explanation
"""


testing = pd.read_csv('../data/test_scores.csv', index_col=0)
testing.head(10)

"""
Explanation: 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.
End of explanation
"""


testing.dropna().head(10)

"""
Explanation: Missing values may be dropped or indexed out:
By default, dropna drops entire rows in which one or more values are missing.
End of explanation
"""


testing.isnull().head()

testing[testing.notnull()].head()

"""
Explanation: A similar result can be obtained via indexing.
End of explanation
"""


testing.dropna(how='all')

"""
Explanation: This can be overridden by passing the how='all' argument, which only drops a row when every field is a missing value.
End of explanation
"""


testing.dropna(thresh=10)

"""
Explanation: This can be customized further by specifying how many values need to be present before a row is dropped via the thresh argument.
End of explanation
"""


# Write your answer here

"""
Explanation: This is typically used in time series applications, where there are repeated measurements that are incomplete for some subjects.
Exercise
Try using the axis argument to drop columns with missing values:
End of explanation
"""


testing.fillna(0).head(10)

testing.fillna({'family_inv': 0, 'mother_hs':1}).head(10)

"""
Explanation: 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.
End of explanation
"""


testing.fillna(method='bfill').head(10)

"""
Explanation: 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:
End of explanation
"""


testing.sum()

"""
Explanation: Data summarization
We 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.
End of explanation
"""


testing.mean()

"""
Explanation: 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:
End of explanation
"""


testing.mean(skipna=False)

"""
Explanation: 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.
End of explanation
"""


nonenglish_nonwhite_withHS = testing[['non_english', 'mother_hs', 'non_white']].prod(axis=1)

nonenglish_nonwhite_withHS

"""
Explanation: Passing axis=1 will summarize over rows instead of columns, which only makes sense in certain situations.
End of explanation
"""


testing.describe()

"""
Explanation: A useful summarization that gives a quick snapshot of multiple statistics for a Series or DataFrame is describe:
End of explanation
"""


testing.non_english.describe()

"""
Explanation: describe can detect non-numeric data and sometimes yield useful information about it.
End of explanation
"""


testing.score.cov(testing.age_test)

"""
Explanation: 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})$$
End of explanation
"""


testing.score.corr(testing.age_test)

"""
Explanation: $$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}}$$
End of explanation
"""


# Write answer here

"""
Explanation: Try running corr on the entire testing DataFrame to see what is returned:
End of explanation
"""


segments_hier.head()

segments_hier.sum(level='transit').seg_length

"""
Explanation: If we have a DataFrame with a hierarchical index (or indices), summary statistics can be applied with respect to any of the index levels:
End of explanation
"""


testing.to_csv("testing.csv")

"""
Explanation: Writing Data to Files
As 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.
End of explanation
"""


testing.to_pickle("testing_pickle")

"""
Explanation: 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.
End of explanation
"""


pd.read_pickle("testing_pickle").head()

"""
Explanation: The complement to to_pickle is the read_pickle function, which restores the pickle to a DataFrame or Series:
End of explanation
"""


