<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Data Wrangling with Pandas
Now that we have been exposed to the basic functionality of Pandas, lets explore some more advanced features that will be useful when addressing more complex data management tasks.
As most statisticians/data analysts will admit, often the lion's share of the time spent implementing an analysis is devoted to preparing the data itself, rather than to coding or running a particular model that uses the data. This is where Pandas and  Python's standard library are beneficial, providing high-level, flexible, and efficient tools for manipulating your data as needed.
Step1:  Date/Time data handling
Date and time data are inherently problematic. There are an unequal number of days in every month, an unequal number of days in a year (due to leap years), and time zones that vary over space. Yet information about time is essential in many analyses, particularly in the case of time series analysis.
The datetime built-in library handles temporal information down to the nanosecond.
Step2:  In addition to datetime there are simpler objects for date and time information only, respectively.
Step3:  Having a custom data type for dates and times is convenient because we can perform operations on them easily. For example, we may want to calculate the difference between two times
Step4:  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.
Step5:  For example, we might be interested in the distribution of transit lengths, so we can plot them as a histogram
Step6:  Though most of the transits appear to be short, there are a few longer distances that make the plot difficult to read. This is where a transformation is useful.
Exercise
Apply a logarithmic transformation to the segment length (seg_length) field, then plot its histogram.
Step7:  We can see that although there are date/time fields in the dataset, they are not in any specialized format, such as datetime.
Step8:  Our first order of business will be to convert these data to datetime. The strptime method parses a string representation of a date and/or time field, according to the expected format of this information.
Step9:  The dateutil package includes a parser that attempts to detect the format of the date strings, and convert them automatically.
Step10:  We can convert all the dates in a particular column by using the apply method.
Step11:  As a convenience, Pandas has a to_datetime method that will parse and convert an entire Series of formatted strings into datetime objects.
Step12:  Pandas also has a custom NA value for missing datetime objects, NaT (i.e. "Not a Time").
Step13:  Also, if to_datetime() has problems parsing any particular date/time format, you can pass the spec in using the format= argument.
Merging and joining DataFrame objects
Now that we have the vessel transit information as we need it, we may want a little more information regarding the vessels themselves. In the data/AIS folder there is a second table that contains information about each of the ships that traveled the segments in the segments table.
Step14:  Exercise
Generate counts of vessel types in the vessels DataFrame for only this subset of vessels.
Step15:  The challenge, however, is that several ships have travelled multiple segments, so there is not a one-to-one relationship between the rows of the two tables. The table of vessel information has a one-to-many relationship with the segments.
In Pandas, we can combine tables according to the value of one or more keys that are used to identify rows, much like an index. Using a trivial example
Step16:  Notice that without any information about which column to use as a key, Pandas did the Right Thing™ and used the id column in both tables. Unless specified otherwise, merge will use any common column names as keys for merging the tables. 
Notice also that id=3 from df1 was omitted from the merged table. This is because, by default, merge performs an inner join on the tables, meaning that the merged table represents an intersection of the two tables.
Step17:  The outer join above yields the union of the two tables, so all rows are represented, with missing values inserted as appropriate. One can also perform right and left joins to include all rows of the right or left table (i.e. first or second argument to merge), but not necessarily the other.
Looking at the two datasets that we wish to merge
Step18:  we see that there is a mmsi value (a vessel identifier) in each table, but it is used as an index for the vessels table. In this case, we have to specify to join on the index for this table, and on the mmsi column for the other.
Step19:  In this case, the default inner join is suitable; we are not interested in observations from either table that do not have corresponding entries in the other. 
Notice that the mmsi variable that was an index on the vessels table is no longer an index on the merged table.
Here, we used the merge function to perform the merge; we could also have used the merge method for either of the tables
Step20:  Occasionally, there will be fields in common between two tables that we do not wish to use to join them. Why not? They may contain different information, despite having the same name. In this case, Pandas will by default append suffixes _x and _y to the columns to uniquely identify them.
Step21:  This default behavior can be overridden by specifying a suffixes argument, containing a list of the suffixes to be used for the columns of the left and right columns, respectively.
Concatenation
A common data manipulation is appending rows or columns to a dataset that already conform to the dimensions of the exsiting rows or colums, respectively. 
In NumPy, this is done either with concatenate or the convenience "functions" c_ and r_
Step22:  Notice that c_ and r_ are not really functions at all, since it is performing some sort of indexing operation, rather than being called. They are actually class instances, but they are here behaving mostly like functions. Don't think about this too hard; just know that they are there.
This operation is also called binding or stacking.
With Pandas' indexed data structures, there are additional considerations as the overlap in index values between two data structures affects how they are concatenate.
Lets import two microbiome datasets, each consisting of counts of microorganiams from a particular patient. We will use the first column of each dataset as the index.
Step23:  Let's give the index and columns meaningful labels.
Exercise
Label the lone column in each DataFrame as "Count" and the respective indices as "Taxon"
Step24:  The index of these data is the unique biological classification of each organism, beginning with domain, phylum, class, and for some organisms, going all the way down to the genus level.
Step25:  If we concatenate along axis=0 (the default), we will obtain another data frame with the the rows concatenated
Step26:  However, the index is no longer unique, due to overlap between the two DataFrames.
Step27:  Concatenating along axis=1 will concatenate column-wise, but respecting the indices of the two DataFrames.
Step28:  If we are only interested in taxa that are included in both DataFrames, we can specify a join=inner argument.
Step29:  If we wanted to use the second table to fill values absent from the first table, we could use combine_first.
Step30:  We can also create a hierarchical index based on keys identifying the original tables.
Step31:  Alternatively, you can pass keys to the concatenation by supplying the DataFrames (or Series) as a dict, resulting in a "wide" format table.
Step32:  If you want concat to work like numpy.concatanate, you may provide the ignore_index=True argument.
Exercise
In the data/microbiome subdirectory, there are 9 spreadsheets of microbiome data that was acquired from high-throughput RNA sequencing procedures, along with a 10th file that describes the content of each. Write code that imports each of the data spreadsheets and combines them into a single DataFrame, adding the identifying information from the metadata spreadsheet as columns in the combined DataFrame.
Step33:  Reshaping DataFrame objects
In the context of a single DataFrame, we are often interested in re-arranging the layout of our data. 
This dataset in from Table 6.9 of Statistical Methods for the Analysis of Repeated Measurements by Charles S. Davis, pp. 161-163 (Springer, 2002). These data are from a multicenter, randomized controlled trial of botulinum toxin type B (BotB) in patients with cervical dystonia (spasmodic torticollis) from nine U.S. sites.
Randomized to placebo (N=36), 5000 units of BotB (N=36), 10,000 units of BotB (N=37)
Response variable
Step34:  This dataset includes repeated measurements of the same individuals (longitudinal data). Its possible to present such information in (at least) two ways
Step35:  Have a peek at the structure of the index of the stacked data (and the data itself).
To complement this, unstack pivots from rows back to columns.
Step36:  Exercise
Which columns uniquely define a row? Create a DataFrame called cdystonia2 with a hierarchical index based on these columns.
Step37:  If we want to transform this data so that repeated measurements are in columns, we can unstack the twstrs measurements according to obs.
Step38:  We can now merge these reshaped outcomes data with the other variables to create a wide format DataFrame that consists of one row for each patient.
Step39:  A slightly cleaner way of doing this is to set the patient-level information as an index before unstacking
Step40:  To convert our "wide" format back to long, we can use the melt function, appropriately parameterized. This function is useful for DataFrames where one
or more columns are identifier variables (id_vars), with the remaining columns being measured variables (value_vars). The measured variables are "unpivoted" to
the row axis, leaving just two non-identifier columns, a variable and its corresponding value, which can both be renamed using optional arguments.
Step41:  This illustrates the two formats for longitudinal data
Step42:  Exercise
Try pivoting the cdystonia DataFrame without specifying a variable for the cell values
Step43:  A related method, pivot_table, creates a spreadsheet-like table with a hierarchical index, and allows the values of the table to be populated using an arbitrary aggregation function.
Step44:  For a simple cross-tabulation of group frequencies, the crosstab function (not a method) aggregates counts of data according to factors in rows and columns. The factors may be hierarchical if desired.
Step45:  Data transformation
There are a slew of additional operations for DataFrames that we would collectively refer to as transformations which include tasks such as
Step46:  These rows can be removed using drop_duplicates
Step47:  Value replacement
Frequently, we get data columns that are encoded as strings that we wish to represent numerically for the purposes of including it in a quantitative analysis. For example, consider the treatment variable in the cervical dystonia dataset
Step48:  A logical way to specify these numerically is to change them to integer values, perhaps using "Placebo" as a baseline value. If we create a dict with the original values as keys and the replacements as values, we can pass it to the map method to implement the changes.
Step49:  Alternately, if we simply want to replace particular values in a Series or DataFrame, we can use the replace method. 
An example where replacement is useful is replacing sentinel values with an appropriate numeric value prior to analysis. A large negative number is sometimes used in otherwise positive-valued data to denote missing values.
Step50:  In such situations, we can use replace to substitute nan where the sentinel values occur.
Step51:  We can also perform the same replacement that we used map for with replace
Step52:  Inidcator variables
For some statistical analyses (e.g. regression models or analyses of variance), categorical or group variables need to be converted into columns of indicators--zeros and ones--to create a so-called design matrix. The Pandas function get_dummies (indicator variables are also known as dummy variables) makes this transformation straightforward.
Let's consider the DataFrame containing the ships corresponding to the transit segments on the eastern seaboard. The type variable denotes the class of vessel; we can create a matrix of indicators for this. For simplicity, lets filter out the 5 most common types of ships.
Exercise
Create a subset of the vessels DataFrame called vessels5 that only contains the 5 most common types of vessels, based on their prevalence in the dataset.
Step53:  We can now apply get_dummies to the vessel type to create 5 indicator variables.
Step54:  Discretization
Pandas' cut function can be used to group continuous or countable data in to bins. Discretization is generally a very bad idea for statistical analysis, so use this function responsibly!
Lets say we want to bin the ages of the cervical dystonia patients into a smaller number of groups
Step55:  Let's transform these data into decades, beginnnig with individuals in their 20's and ending with those in their 80's
Step56:  The parentheses indicate an open interval, meaning that the interval includes values up to but not including the endpoint, whereas the square bracket is a closed interval, where the endpoint is included in the interval. We can switch the closure to the left side by setting the right flag to False
Step57:  Since the data are now ordinal, rather than numeric, we can give them labels
Step58:  A related function qcut uses empirical quantiles to divide the data. If, for example, we want the quartiles -- (0-25%], (25-50%], (50-70%], (75-100%] -- we can just specify 4 intervals, which will be equally-spaced by default
Step59:  Alternatively, one can specify custom quantiles to act as cut points
Step60:  Exercise
Use the discretized segment lengths as the input for get_dummies to create 5 indicator variables for segment length
Step61:  Permutation and sampling
For some data analysis tasks, such as simulation, we need to be able to randomly reorder our data, or draw random values from it. Calling NumPy's permutation function with the length of the sequence you want to permute generates an array with a permuted sequence of integers, which can be used to re-order the sequence.
Step62:  Using this sequence as an argument to the take method results in a reordered DataFrame
Step63:  Compare this ordering with the original
Step64:  For random sampling, DataFrame and Series objects have a sample method that can be used to draw samples, with or without replacement
Step65:  Exercise
Generate 10 samples from vessels with replacement.
Step66:  Data aggregation and GroupBy operations
One of the most powerful features of Pandas is its GroupBy functionality. On occasion we may want to perform operations on groups of observations within a dataset. For exmaple
Step67:  This grouped dataset is hard to visualize
Step68:  However, the grouping is only an intermediate step; for example, we may want to iterate over each of the patient groups
Step69:  A common data analysis procedure is the split-apply-combine operation, which groups subsets of data together, applies a function to each of the groups, then recombines them into a new data table.
For example, we may want to aggregate our data with with some function.
<div align="right">*(figure taken from "Python for Data Analysis", p.251)*</div>
We can aggregate in Pandas using the aggregate (or agg, for short) method
Step70:  Notice that the treat and sex variables are not included in the aggregation. Since it does not make sense to aggregate non-string variables, these columns are simply ignored by the method.
Some aggregation functions are so common that Pandas has a convenience method for them, such as mean
Step71:  The add_prefix and add_suffix methods can be used to give the columns of the resulting table labels that reflect the transformation
Step72:  Exercise
Use the quantile method to generate the median values of the twstrs variable for each patient.
Step73:  If we wish, we can easily aggregate according to multiple keys
Step74:  Alternately, we can transform the data, using a function of our choice with the transform method
Step75:  It is easy to do column selection within groupby operations, if we are only interested split-apply-combine operations on a subset of columns
Step76:  Or, as a DataFrame
Step77:  If you simply want to divide your DataFrame into chunks for later use, its easy to convert them into a dict so that they can be easily indexed out as needed
Step78:  By default, groupby groups by row, but we can specify the axis argument to change this. For example, we can group our columns by dtype this way
Step79:  Its also possible to group by one or more levels of a hierarchical index. Recall cdystonia2, which we created with a hierarchical index
Step80:  The level argument specifies which level of the index to use for grouping.
Step81:  Apply
We can generalize the split-apply-combine methodology by using apply function. This allows us to invoke any function we wish on a grouped dataset and recombine them into a DataFrame.
The function below takes a DataFrame and a column name, sorts by the column, and takes the n largest values of that column. We can use this with apply to return the largest values from every group in a DataFrame in a single call.
Step82:  To see this in action, consider the vessel transit segments dataset (which we merged with the vessel information to yield segments_merged). Say we wanted to return the 3 longest segments travelled by each ship
Step83:  Notice that additional arguments for the applied function can be passed via apply after the function name. It assumes that the DataFrame is the first argument.
Recall the microbiome data sets that we used previously for the concatenation example. Suppose that we wish to aggregate the data at a higher biological classification than genus. For example, we can identify samples down to class, which is the 3rd level of organization in each index.
Step84:  Using the string methods split and join we can create an index that just uses the first three classifications
Step85:  However, since there are multiple taxonomic units with the same class, our index is no longer unique
Step86:  We can re-establish a unique index by summing all rows with the same class, using groupby
Step87:  Exercise
Load the dataset in titanic.xls. It contains data on all the passengers that travelled on the Titanic.
Step88:  Women and children first?
Use the groupby method to calculate the proportion of passengers that survived by sex.
Calculate the same proportion, but by class and sex.
Create age categories
<ASSISTANT_TASK:>
Python Code:
%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
Explanation: Data Wrangling with Pandas
Now that we have been exposed to the basic functionality of Pandas, lets explore some more advanced features that will be useful when addressing more complex data management tasks.
As most statisticians/data analysts will admit, often the lion's share of the time spent implementing an analysis is devoted to preparing the data itself, rather than to coding or running a particular model that uses the data. This is where Pandas and  Python's standard library are beneficial, providing high-level, flexible, and efficient tools for manipulating your data as needed.
End of explanation
from datetime import datetime
now = datetime.now()
now
now.day
now.weekday()
Explanation: Date/Time data handling
Date and time data are inherently problematic. There are an unequal number of days in every month, an unequal number of days in a year (due to leap years), and time zones that vary over space. Yet information about time is essential in many analyses, particularly in the case of time series analysis.
The datetime built-in library handles temporal information down to the nanosecond.
End of explanation
from datetime import date, time
time(3, 24)
date(1970, 9, 3)
Explanation: In addition to datetime there are simpler objects for date and time information only, respectively.
End of explanation
my_age = now - datetime(1970, 9, 3)
my_age
my_age.days/365.
Explanation: Having a custom data type for dates and times is convenient because we can perform operations on them easily. For example, we may want to calculate the difference between two times:
End of explanation
segments = pd.read_csv("../data/AIS/transit_segments.csv")
segments.head()
Explanation: 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
segments.seg_length.hist(bins=500)
Explanation: For example, we might be interested in the distribution of transit lengths, so we can plot them as a histogram:
End of explanation
# Write your answer here
Explanation: Though most of the transits appear to be short, there are a few longer distances that make the plot difficult to read. This is where a transformation is useful.
Exercise
Apply a logarithmic transformation to the segment length (seg_length) field, then plot its histogram.
End of explanation
segments.st_time.dtype
Explanation: We can see that although there are date/time fields in the dataset, they are not in any specialized format, such as datetime.
End of explanation
datetime.strptime(segments.st_time.loc[0], '%m/%d/%y %H:%M')
Explanation: Our first order of business will be to convert these data to datetime. The strptime method parses a string representation of a date and/or time field, according to the expected format of this information.
End of explanation
from dateutil.parser import parse
parse(segments.st_time.loc[0])
Explanation: The dateutil package includes a parser that attempts to detect the format of the date strings, and convert them automatically.
End of explanation
segments.st_time.apply(lambda d: datetime.strptime(d, '%m/%d/%y %H:%M'))
Explanation: We can convert all the dates in a particular column by using the apply method.
End of explanation
pd.to_datetime(segments.st_time[:10])
Explanation: As a convenience, Pandas has a to_datetime method that will parse and convert an entire Series of formatted strings into datetime objects.
End of explanation
pd.to_datetime([None])
Explanation: Pandas also has a custom NA value for missing datetime objects, NaT (i.e. "Not a Time").
End of explanation
vessels = pd.read_csv("../data/AIS/vessel_information.csv", index_col='mmsi')
vessels.head()
[v for v in vessels.type.unique() if v.find('/')==-1]
Explanation: Also, if to_datetime() has problems parsing any particular date/time format, you can pass the spec in using the format= argument.
Merging and joining DataFrame objects
Now that we have the vessel transit information as we need it, we may want a little more information regarding the vessels themselves. In the data/AIS folder there is a second table that contains information about each of the ships that traveled the segments in the segments table.
End of explanation
# Write your answer here
Explanation: Exercise
Generate counts of vessel types in the vessels DataFrame for only this subset of vessels.
End of explanation
df1 = pd.DataFrame(dict(id=range(4), age=np.random.randint(18, 31, size=4)))
df2 = pd.DataFrame(dict(id=list(range(3))+list(range(3)), 
                        score=np.random.random(size=6)))
df1
df2
pd.merge(df1, df2)
Explanation: The challenge, however, is that several ships have travelled multiple segments, so there is not a one-to-one relationship between the rows of the two tables. The table of vessel information has a one-to-many relationship with the segments.
In Pandas, we can combine tables according to the value of one or more keys that are used to identify rows, much like an index. Using a trivial example:
End of explanation
pd.merge(df1, df2, how='outer')
Explanation: Notice that without any information about which column to use as a key, Pandas did the Right Thing™ and used the id column in both tables. Unless specified otherwise, merge will use any common column names as keys for merging the tables. 
Notice also that id=3 from df1 was omitted from the merged table. This is because, by default, merge performs an inner join on the tables, meaning that the merged table represents an intersection of the two tables.
End of explanation
segments.head(1)
vessels.head(1)
Explanation: The outer join above yields the union of the two tables, so all rows are represented, with missing values inserted as appropriate. One can also perform right and left joins to include all rows of the right or left table (i.e. first or second argument to merge), but not necessarily the other.
Looking at the two datasets that we wish to merge:
End of explanation
segments_merged = pd.merge(vessels, segments, left_index=True, right_on='mmsi')
segments_merged.head()
Explanation: we see that there is a mmsi value (a vessel identifier) in each table, but it is used as an index for the vessels table. In this case, we have to specify to join on the index for this table, and on the mmsi column for the other.
End of explanation
vessels.merge(segments, left_index=True, right_on='mmsi').head()
Explanation: In this case, the default inner join is suitable; we are not interested in observations from either table that do not have corresponding entries in the other. 
Notice that the mmsi variable that was an index on the vessels table is no longer an index on the merged table.
Here, we used the merge function to perform the merge; we could also have used the merge method for either of the tables:
End of explanation
segments['type'] = 'foo'
pd.merge(vessels, segments, left_index=True, right_on='mmsi').head()
Explanation: Occasionally, there will be fields in common between two tables that we do not wish to use to join them. Why not? They may contain different information, despite having the same name. In this case, Pandas will by default append suffixes _x and _y to the columns to uniquely identify them.
End of explanation
np.concatenate([np.random.random(5), np.random.random(5)])
np.r_[np.random.random(5), np.random.random(5)]
np.c_[np.random.random(5), np.random.random(5)]
Explanation: This default behavior can be overridden by specifying a suffixes argument, containing a list of the suffixes to be used for the columns of the left and right columns, respectively.
Concatenation
A common data manipulation is appending rows or columns to a dataset that already conform to the dimensions of the exsiting rows or colums, respectively. 
In NumPy, this is done either with concatenate or the convenience "functions" c_ and r_:
End of explanation
mb1 = pd.read_excel('../data/microbiome/MID1.xls', 'Sheet 1', index_col=0, header=None)
mb2 = pd.read_excel('../data/microbiome/MID2.xls', 'Sheet 1', index_col=0, header=None)
mb1.shape, mb2.shape
mb1.head()
Explanation: Notice that c_ and r_ are not really functions at all, since it is performing some sort of indexing operation, rather than being called. They are actually class instances, but they are here behaving mostly like functions. Don't think about this too hard; just know that they are there.
This operation is also called binding or stacking.
With Pandas' indexed data structures, there are additional considerations as the overlap in index values between two data structures affects how they are concatenate.
Lets import two microbiome datasets, each consisting of counts of microorganiams from a particular patient. We will use the first column of each dataset as the index.
End of explanation
# Write your answer here
Explanation: Let's give the index and columns meaningful labels.
Exercise
Label the lone column in each DataFrame as "Count" and the respective indices as "Taxon"
End of explanation
mb1.index[:3]
mb1.index.is_unique
Explanation: The index of these data is the unique biological classification of each organism, beginning with domain, phylum, class, and for some organisms, going all the way down to the genus level.
End of explanation
pd.concat([mb1, mb2], axis=0).shape
Explanation: If we concatenate along axis=0 (the default), we will obtain another data frame with the the rows concatenated:
End of explanation
pd.concat([mb1, mb2], axis=0).index.is_unique
Explanation: However, the index is no longer unique, due to overlap between the two DataFrames.
End of explanation
pd.concat([mb1, mb2], axis=1).shape
pd.concat([mb1, mb2], axis=1).head()
Explanation: Concatenating along axis=1 will concatenate column-wise, but respecting the indices of the two DataFrames.
End of explanation
pd.concat([mb1, mb2], axis=1, join='inner').head()
Explanation: If we are only interested in taxa that are included in both DataFrames, we can specify a join=inner argument.
End of explanation
mb1.combine_first(mb2).head()
Explanation: If we wanted to use the second table to fill values absent from the first table, we could use combine_first.
End of explanation
pd.concat([mb1, mb2], keys=['patient1', 'patient2']).head()
pd.concat([mb1, mb2], keys=['patient1', 'patient2']).index.is_unique
Explanation: We can also create a hierarchical index based on keys identifying the original tables.
End of explanation
pd.concat(dict(patient1=mb1, patient2=mb2), axis=1).head()
Explanation: Alternatively, you can pass keys to the concatenation by supplying the DataFrames (or Series) as a dict, resulting in a "wide" format table.
End of explanation
# Write solution here
Explanation: If you want concat to work like numpy.concatanate, you may provide the ignore_index=True argument.
Exercise
In the data/microbiome subdirectory, there are 9 spreadsheets of microbiome data that was acquired from high-throughput RNA sequencing procedures, along with a 10th file that describes the content of each. Write code that imports each of the data spreadsheets and combines them into a single DataFrame, adding the identifying information from the metadata spreadsheet as columns in the combined DataFrame.
End of explanation
cdystonia = pd.read_csv("../data/cdystonia.csv", index_col=None)
cdystonia.head()
Explanation: Reshaping DataFrame objects
In the context of a single DataFrame, we are often interested in re-arranging the layout of our data. 
This dataset in from Table 6.9 of Statistical Methods for the Analysis of Repeated Measurements by Charles S. Davis, pp. 161-163 (Springer, 2002). These data are from a multicenter, randomized controlled trial of botulinum toxin type B (BotB) in patients with cervical dystonia (spasmodic torticollis) from nine U.S. sites.
Randomized to placebo (N=36), 5000 units of BotB (N=36), 10,000 units of BotB (N=37)
Response variable: total score on Toronto Western Spasmodic Torticollis Rating Scale (TWSTRS), measuring severity, pain, and disability of cervical dystonia (high scores mean more impairment)
TWSTRS measured at baseline (week 0) and weeks 2, 4, 8, 12, 16 after treatment began
End of explanation
stacked = cdystonia.stack()
stacked
Explanation: This dataset includes repeated measurements of the same individuals (longitudinal data). Its possible to present such information in (at least) two ways: showing each repeated measurement in their own row, or in multiple columns representing multiple measurements.
The stack method rotates the data frame so that columns are represented in rows:
End of explanation
stacked.unstack().head()
Explanation: Have a peek at the structure of the index of the stacked data (and the data itself).
To complement this, unstack pivots from rows back to columns.
End of explanation
# Write your answer here
Explanation: Exercise
Which columns uniquely define a row? Create a DataFrame called cdystonia2 with a hierarchical index based on these columns.
End of explanation
twstrs_wide = cdystonia2['twstrs'].unstack('obs')
twstrs_wide.head()
Explanation: If we want to transform this data so that repeated measurements are in columns, we can unstack the twstrs measurements according to obs.
End of explanation
cdystonia_wide = (cdystonia[['patient','site','id','treat','age','sex']]
                  .drop_duplicates()
                  .merge(twstrs_wide, right_index=True, left_on='patient', how='inner')
                  .head())
cdystonia_wide
Explanation: We can now merge these reshaped outcomes data with the other variables to create a wide format DataFrame that consists of one row for each patient.
End of explanation
(cdystonia.set_index(['patient','site','id','treat','age','sex','week'])['twstrs']
     .unstack('week').head())
Explanation: A slightly cleaner way of doing this is to set the patient-level information as an index before unstacking:
End of explanation
pd.melt(cdystonia_wide, id_vars=['patient','site','id','treat','age','sex'], 
        var_name='obs', value_name='twsters').head()
Explanation: To convert our "wide" format back to long, we can use the melt function, appropriately parameterized. This function is useful for DataFrames where one
or more columns are identifier variables (id_vars), with the remaining columns being measured variables (value_vars). The measured variables are "unpivoted" to
the row axis, leaving just two non-identifier columns, a variable and its corresponding value, which can both be renamed using optional arguments.
End of explanation
cdystonia.pivot(index='patient', columns='obs', values='twstrs').head()
Explanation: This illustrates the two formats for longitudinal data: long and wide formats. Its typically better to store data in long format because additional data can be included as additional rows in the database, while wide format requires that the entire database schema be altered by adding columns to every row as data are collected.
The preferable format for analysis depends entirely on what is planned for the data, so it is imporant to be able to move easily between them.
Pivoting
The pivot method allows a DataFrame to be transformed easily between long and wide formats in the same way as a pivot table is created in a spreadsheet. It takes three arguments: index, columns and values, corresponding to the DataFrame index (the row headers), columns and cell values, respectively.
For example, we may want the twstrs variable (the response variable) in wide format according to patient, as we saw with the unstacking method above:
End of explanation
# Write your answer here
Explanation: Exercise
Try pivoting the cdystonia DataFrame without specifying a variable for the cell values:
End of explanation
cdystonia.pivot_table(index=['site', 'treat'], columns='week', values='twstrs', 
                      aggfunc=max).head(20)
Explanation: A related method, pivot_table, creates a spreadsheet-like table with a hierarchical index, and allows the values of the table to be populated using an arbitrary aggregation function.
End of explanation
pd.crosstab(cdystonia.sex, cdystonia.site)
Explanation: For a simple cross-tabulation of group frequencies, the crosstab function (not a method) aggregates counts of data according to factors in rows and columns. The factors may be hierarchical if desired.
End of explanation
vessels.duplicated(subset='names')
Explanation: Data transformation
There are a slew of additional operations for DataFrames that we would collectively refer to as transformations which include tasks such as:
removing duplicate values
replacing values
grouping values.
Dealing with duplicates
We can easily identify and remove duplicate values from DataFrame objects. For example, say we want to remove ships from our vessels dataset that have the same name:
End of explanation
vessels.drop_duplicates(['names'])
Explanation: These rows can be removed using drop_duplicates
End of explanation
cdystonia.treat.value_counts()
Explanation: Value replacement
Frequently, we get data columns that are encoded as strings that we wish to represent numerically for the purposes of including it in a quantitative analysis. For example, consider the treatment variable in the cervical dystonia dataset:
End of explanation
treatment_map = {'Placebo': 0, '5000U': 1, '10000U': 2}
cdystonia['treatment'] = cdystonia.treat.map(treatment_map)
cdystonia.treatment
Explanation: A logical way to specify these numerically is to change them to integer values, perhaps using "Placebo" as a baseline value. If we create a dict with the original values as keys and the replacements as values, we can pass it to the map method to implement the changes.
End of explanation
scores = pd.Series([99, 76, 85, -999, 84, 95])
Explanation: Alternately, if we simply want to replace particular values in a Series or DataFrame, we can use the replace method. 
An example where replacement is useful is replacing sentinel values with an appropriate numeric value prior to analysis. A large negative number is sometimes used in otherwise positive-valued data to denote missing values.
End of explanation
scores.replace(-999, np.nan)
Explanation: In such situations, we can use replace to substitute nan where the sentinel values occur.
End of explanation
cdystonia2.treat.replace({'Placebo': 0, '5000U': 1, '10000U': 2})
Explanation: We can also perform the same replacement that we used map for with replace:
End of explanation
# Write your answer here
Explanation: Inidcator variables
For some statistical analyses (e.g. regression models or analyses of variance), categorical or group variables need to be converted into columns of indicators--zeros and ones--to create a so-called design matrix. The Pandas function get_dummies (indicator variables are also known as dummy variables) makes this transformation straightforward.
Let's consider the DataFrame containing the ships corresponding to the transit segments on the eastern seaboard. The type variable denotes the class of vessel; we can create a matrix of indicators for this. For simplicity, lets filter out the 5 most common types of ships.
Exercise
Create a subset of the vessels DataFrame called vessels5 that only contains the 5 most common types of vessels, based on their prevalence in the dataset.
End of explanation
pd.get_dummies(vessels5.type).head(10)
Explanation: We can now apply get_dummies to the vessel type to create 5 indicator variables.
End of explanation
cdystonia.age.describe()
Explanation: Discretization
Pandas' cut function can be used to group continuous or countable data in to bins. Discretization is generally a very bad idea for statistical analysis, so use this function responsibly!
Lets say we want to bin the ages of the cervical dystonia patients into a smaller number of groups:
End of explanation
pd.cut(cdystonia.age, [20,30,40,50,60,70,80,90])[:30]
Explanation: Let's transform these data into decades, beginnnig with individuals in their 20's and ending with those in their 80's:
End of explanation
pd.cut(cdystonia.age, [20,30,40,50,60,70,80,90], right=False)[:30]
Explanation: The parentheses indicate an open interval, meaning that the interval includes values up to but not including the endpoint, whereas the square bracket is a closed interval, where the endpoint is included in the interval. We can switch the closure to the left side by setting the right flag to False:
End of explanation
pd.cut(cdystonia.age, [20,40,60,80,90], labels=['young','middle-aged','old','really old'])[:30]
Explanation: Since the data are now ordinal, rather than numeric, we can give them labels:
End of explanation
pd.qcut(cdystonia.age, 4)[:30]
Explanation: A related function qcut uses empirical quantiles to divide the data. If, for example, we want the quartiles -- (0-25%], (25-50%], (50-70%], (75-100%] -- we can just specify 4 intervals, which will be equally-spaced by default:
End of explanation
quantiles = pd.qcut(segments.seg_length, [0, 0.01, 0.05, 0.95, 0.99, 1])
quantiles[:30]
Explanation: Alternatively, one can specify custom quantiles to act as cut points:
End of explanation
# Write your answer here
Explanation: Exercise
Use the discretized segment lengths as the input for get_dummies to create 5 indicator variables for segment length:
End of explanation
new_order = np.random.permutation(len(segments))
new_order[:30]
Explanation: Permutation and sampling
For some data analysis tasks, such as simulation, we need to be able to randomly reorder our data, or draw random values from it. Calling NumPy's permutation function with the length of the sequence you want to permute generates an array with a permuted sequence of integers, which can be used to re-order the sequence.
End of explanation
segments.take(new_order).head()
Explanation: Using this sequence as an argument to the take method results in a reordered DataFrame:
End of explanation
segments.head()
Explanation: Compare this ordering with the original:
End of explanation
vessels.sample(n=10)
Explanation: For random sampling, DataFrame and Series objects have a sample method that can be used to draw samples, with or without replacement:
End of explanation
# Write your answer here
Explanation: Exercise
Generate 10 samples from vessels with replacement.
End of explanation
cdystonia_grouped = cdystonia.groupby(cdystonia.patient)
Explanation: Data aggregation and GroupBy operations
One of the most powerful features of Pandas is its GroupBy functionality. On occasion we may want to perform operations on groups of observations within a dataset. For exmaple:
aggregation, such as computing the sum of mean of each group, which involves applying a function to each group and returning the aggregated results
slicing the DataFrame into groups and then doing something with the resulting slices (e.g. plotting)
group-wise transformation, such as standardization/normalization
End of explanation
cdystonia_grouped
Explanation: This grouped dataset is hard to visualize
End of explanation
for patient, group in cdystonia_grouped:
    print('patient', patient)
    print('group', group)
Explanation: However, the grouping is only an intermediate step; for example, we may want to iterate over each of the patient groups:
End of explanation
cdystonia_grouped.agg(np.mean).head()
Explanation: A common data analysis procedure is the split-apply-combine operation, which groups subsets of data together, applies a function to each of the groups, then recombines them into a new data table.
For example, we may want to aggregate our data with with some function.
<div align="right">*(figure taken from "Python for Data Analysis", p.251)*</div>
We can aggregate in Pandas using the aggregate (or agg, for short) method:
End of explanation
cdystonia_grouped.mean().head()
Explanation: Notice that the treat and sex variables are not included in the aggregation. Since it does not make sense to aggregate non-string variables, these columns are simply ignored by the method.
Some aggregation functions are so common that Pandas has a convenience method for them, such as mean:
End of explanation
cdystonia_grouped.mean().add_suffix('_mean').head()
Explanation: The add_prefix and add_suffix methods can be used to give the columns of the resulting table labels that reflect the transformation:
End of explanation
# Write your answer here
Explanation: Exercise
Use the quantile method to generate the median values of the twstrs variable for each patient.
End of explanation
cdystonia.groupby(['week','site']).mean().head()
Explanation: If we wish, we can easily aggregate according to multiple keys:
End of explanation
normalize = lambda x: (x - x.mean())/x.std()
cdystonia_grouped.transform(normalize).head()
Explanation: Alternately, we can transform the data, using a function of our choice with the transform method:
End of explanation
cdystonia_grouped['twstrs'].mean().head()
Explanation: It is easy to do column selection within groupby operations, if we are only interested split-apply-combine operations on a subset of columns:
End of explanation
cdystonia_grouped[['twstrs']].mean().head()
Explanation: Or, as a DataFrame:
End of explanation
chunks = dict(list(cdystonia_grouped))
chunks[4]
Explanation: If you simply want to divide your DataFrame into chunks for later use, its easy to convert them into a dict so that they can be easily indexed out as needed:
End of explanation
dict(list(cdystonia.groupby(cdystonia.dtypes, axis=1)))
Explanation: By default, groupby groups by row, but we can specify the axis argument to change this. For example, we can group our columns by dtype this way:
End of explanation
cdystonia2.head(10)
Explanation: Its also possible to group by one or more levels of a hierarchical index. Recall cdystonia2, which we created with a hierarchical index:
End of explanation
cdystonia2.groupby(level='obs', axis=0)['twstrs'].mean()
Explanation: The level argument specifies which level of the index to use for grouping.
End of explanation
def top(df, column, n=5):
    return df.sort_index(by=column, ascending=False)[:n]
Explanation: Apply
We can generalize the split-apply-combine methodology by using apply function. This allows us to invoke any function we wish on a grouped dataset and recombine them into a DataFrame.
The function below takes a DataFrame and a column name, sorts by the column, and takes the n largest values of that column. We can use this with apply to return the largest values from every group in a DataFrame in a single call.
End of explanation
top3segments = segments_merged.groupby('mmsi').apply(top, column='seg_length', n=3)[['names', 'seg_length']]
top3segments.head(15)
Explanation: To see this in action, consider the vessel transit segments dataset (which we merged with the vessel information to yield segments_merged). Say we wanted to return the 3 longest segments travelled by each ship:
End of explanation
mb1.index[:3]
Explanation: Notice that additional arguments for the applied function can be passed via apply after the function name. It assumes that the DataFrame is the first argument.
Recall the microbiome data sets that we used previously for the concatenation example. Suppose that we wish to aggregate the data at a higher biological classification than genus. For example, we can identify samples down to class, which is the 3rd level of organization in each index.
End of explanation
class_index = mb1.index.map(lambda x: ' '.join(x.split(' ')[:3]))
mb_class = mb1.copy()
mb_class.index = class_index
Explanation: Using the string methods split and join we can create an index that just uses the first three classifications: domain, phylum and class.
End of explanation
mb_class.head()
Explanation: However, since there are multiple taxonomic units with the same class, our index is no longer unique:
End of explanation
mb_class.groupby(level=0).sum().head(10)
Explanation: We can re-establish a unique index by summing all rows with the same class, using groupby:
End of explanation
from IPython.core.display import HTML
HTML(filename='data/titanic.html')
Explanation: Exercise
Load the dataset in titanic.xls. It contains data on all the passengers that travelled on the Titanic.
End of explanation
# Write your answer here
Explanation: Women and children first?
Use the groupby method to calculate the proportion of passengers that survived by sex.
Calculate the same proportion, but by class and sex.
Create age categories: children (under 14 years), adolescents (14-20), adult (21-64), and senior(65+), and calculate survival proportions by age category, class and sex.
End of explanation
<END_TASK>