<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Pandas
pandas is a Python package providing fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python. Additionally, it has the broader goal of becoming the most powerful and flexible open source data analysis / manipulation tool available in any language. It is already well on its way toward this goal.
Source
Step1:  Object Creation
See the Data Structure Intro section
Creating a Series by passing a list of values, letting pandas create a default integer index
Step2:  Creating a DataFrame by passing a numpy array, with a datetime index and labeled columns
Step3:  Having specific dtypes
Step4:  If you’re using IPython, tab completion for column names (as well as public attributes) is automatically enabled. Here’s a subset of the attributes that will be completed
Step5:  Transposing your data
Step6:  Sorting by an axis
Step7:  Sorting by values
Step8:  Selection
Note
Step9:  Selecting via [], which slices the rows.
Step10:  Selection by Label
See more in Selection by Label
For getting a cross section using a label
Step11:  Selecting on a multi-axis by label
Step12:  Showing label slicing, both endpoints are included
Step13:  Reduction in the dimensions of the returned object
Step14:  For getting a scalar value
Step15:  For getting fast access to a scalar (equiv to the prior method)
Step16:  Selection by Position
See more in Selection by Position
Select via the position of the passed integers
Step17:  By integer slices, acting similar to numpy/python
Step18:  By lists of integer position locations, similar to the numpy/python style
Step19:  For slicing rows explicitly
Step20:  For slicing columns explicitly
Step21:  For getting a value explicitly
Step22:  Boolean Indexing
Using a single column’s values to select data.
Step23:  A where operation for getting.
Step24:  Using the isin() method for filtering
Step25:  Setting
Setting a new column automatically aligns the data by the indexes
Step26:  Setting values by label
Step27:  Setting values by position
Step28:  Setting by assigning with a numpy array
Step29:  The result of the prior setting operations
Step30:  A where operation with setting.
Step31:  Missing Data
pandas primarily uses the value np.nan to represent missing data. It is by default not included in computations.
See the Missing Data section
Reindexing allows you to change/add/delete the index on a specified axis. This returns a copy of the data.
Step32:  To drop any rows that have missing data.
Step33:  Filling missing data
Step34:  To get the boolean mask where values are nan
Step35:  Operations
See the Basic section on Binary Ops
Stats
Operations in general exclude missing data.
Performing a descriptive statistic
Step36:  Same operation on the other axis
Step37:  Operating with objects that have different dimensionality and need alignment. In addition, pandas automatically broadcasts along the specified dimension.
Step38:  Apply
Applying functions to the data
Step39:  Histogramming
See more at Histogramming and Discretization
Step40:  Series is equipped with a set of string processing methods in the str attribute that make it easy to operate on each element of the array, as in the code snippet below. Note that pattern-matching in str generally uses regular expressions by default (and in some cases always uses them). See more at Vectorized String Methods.
Step41:  Merge
Concat
pandas provides various facilities for easily combining together Series, DataFrame, and Panel objects with various kinds of set logic for the indexes and relational algebra functionality in the case of join / merge-type operations.
See the Merging section
Concatenating pandas objects together with concat()
Step42:  Join
SQL style merges. See the Database style joining
Step43:  Append
Append rows to a dataframe. See the Appending
Step44:  Grouping
By “group by” we are referring to a process involving one or more of the following steps
- Splitting the data into groups based on some criteria
- Applying a function to each group independently
- Combining the results into a data structure
See the Grouping section
Step45:  Grouping and then applying a function sum to the resulting groups.
Step46:  Grouping by multiple columns forms a hierarchical index, which we then apply the function.
Step47:  Reshaping
See the sections on Hierarchical Indexing and Reshaping.
Stack
Step48:  The stack() method “compresses” a level in the DataFrame’s columns.
Step49:  With a “stacked” DataFrame or Series (having a MultiIndex as the index), the inverse operation of stack() is unstack(), which by default unstacks the last level
Step50:  Pivot Tables
See the section on Pivot Tables.
In [100]
Step51:  We can produce pivot tables from this data very easily
Step52:  Time Series
pandas has simple, powerful, and efficient functionality for performing resampling operations during frequency conversion (e.g., converting secondly data into 5-minutely data). This is extremely common in, but not limited to, financial applications.
See the Time Series section
Step53:  Time zone representation
Step54:  Convert to another time zone
Step55:  Converting between time span representations
Step56:  Converting between period and timestamp enables some convenient arithmetic functions to be used. In the following example, we convert a quarterly frequency with year ending in November to 9am of the end of the month following the quarter end
Step57:  Categoricals
Since version 0.15, pandas can include categorical data in a DataFrame. For full docs, see the categorical introduction and the API documentation.
Step58:  Convert the raw grades to a categorical data type.
Step59:  Rename the categories to more meaningful names (assigning to Series.cat.categories is inplace!)
Step60:  Reorder the categories and simultaneously add the missing categories (methods under Series .cat return a new Series per default).
Step61:  Sorting is per order in the categories, not lexical order.
Step62:  Grouping by a categorical column shows also empty categories.
Step63:  Plotting
Plotting docs.
Step64:  On DataFrame, plot() is a convenience to plot all of the columns with labels
Step65:  Getting Data In/Out
CSV
Writing to a csv file
Step66:  Reading from a csv file
Step67:  HDF5
Reading and writing to HDFStores
Writing to a HDF5 Store
Step68:  Reading from a HDF5 Store
Step69:  Excel
Reading and writing to MS Excel
Writing to an excel file
Step70:  Reading from an excel file
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
Explanation: Pandas
pandas is a Python package providing fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python. Additionally, it has the broader goal of becoming the most powerful and flexible open source data analysis / manipulation tool available in any language. It is already well on its way toward this goal.
Source: http://pandas.pydata.org/pandas-docs/stable/
10 Minutes to pandas
This is a short introduction to pandas, geared mainly for new users. You can see more complex recipes in the Cookbook
The original document is posted at http://pandas.pydata.org/pandas-docs/stable/10min.html
Customarily, we import as follows:
End of explanation
s = pd.Series([1,3,5,np.nan,6,8])
s
Explanation: Object Creation
See the Data Structure Intro section
Creating a Series by passing a list of values, letting pandas create a default integer index:
End of explanation
dates = pd.date_range('20130101', periods=6)
dates
df = pd.DataFrame(np.random.randn(6,4), index=dates, columns = ['Ann', "Bob", "Charly", "Don"])
                  ## columns=list('ABCD'))
df
df.Charly+df.Don
df2 = pd.DataFrame({ 'A' : 1.,
   ....:                      'B' : pd.Timestamp('20130102'),
   ....:                      'C' : pd.Series(1,index=list(range(4)),dtype='float32'),
   ....:                      'D' : np.array([3] * 4,dtype='int32'),
   ....:                      'E' : pd.Categorical(["test","train","test","train"]),
   ....:                      'F' : 'foo' })
   ....: 
df2
Explanation: Creating a DataFrame by passing a numpy array, with a datetime index and labeled columns:
End of explanation
df2.dtypes
Explanation: Having specific dtypes
End of explanation
df.head()
df.tail(3)
df.index
df.columns
df.values
df.describe()
df2.columns
df2.dtypes
df2.describe()
Explanation: If you’re using IPython, tab completion for column names (as well as public attributes) is automatically enabled. Here’s a subset of the attributes that will be completed:
<pre>
In [13]: df2.&lt;TAB&gt;
df2.A                  df2.boxplot
df2.abs                df2.C
df2.add                df2.clip
df2.add_prefix         df2.clip_lower
df2.add_suffix         df2.clip_upper
df2.align              df2.columns
df2.all                df2.combine
df2.any                df2.combineAdd
df2.append             df2.combine_first
df2.apply              df2.combineMult
df2.applymap           df2.compound
df2.as_blocks          df2.consolidate
df2.asfreq             df2.convert_objects
df2.as_matrix          df2.copy
df2.astype             df2.corr
df2.at                 df2.corrwith
df2.at_time            df2.count
df2.axes               df2.cov
df2.B                  df2.cummax
df2.between_time       df2.cummin
df2.bfill              df2.cumprod
df2.blocks             df2.cumsum
df2.bool               df2.D
</pre>
As you can see, the columns A, B, C, and D are automatically tab completed. E is there as well; the rest of the attributes have been truncated for brevity.
Viewing Data
See the Basics section
See the top & bottom rows of the frame
End of explanation
df.T
Explanation: Transposing your data
End of explanation
df.sort_index(axis=1, ascending=False)
Explanation: Sorting by an axis
End of explanation
df.sort_values(by='Bob', ascending=True)
Explanation: Sorting by values
End of explanation
df['Ann']
Explanation: Selection
Note: While standard Python / Numpy expressions for selecting and setting are intuitive and come in handy for interactive work, for production code, we recommend the optimized pandas data access methods, .at, .iat, .loc, .iloc and .ix.
See the indexing documentation Indexing and Selecting Data and MultiIndex / Advanced Indexing
Getting
Selecting a single column, which yields a Series, equivalent to df.A
End of explanation
df[1:3]
df['20130102':'20130104']
Explanation: Selecting via [], which slices the rows.
End of explanation
dates[0]
df.loc[dates[0]]
Explanation: Selection by Label
See more in Selection by Label
For getting a cross section using a label
End of explanation
df.loc[:,['Ann','Bob']]
Explanation: Selecting on a multi-axis by label
End of explanation
df.loc['20130102':'20130104',['A','B']]
Explanation: Showing label slicing, both endpoints are included
End of explanation
df.loc['20130102',['A','B']]
Explanation: Reduction in the dimensions of the returned object
End of explanation
df.loc[dates[0],'A']
Explanation: For getting a scalar value
End of explanation
df.at[dates[0],'A']
Explanation: For getting fast access to a scalar (equiv to the prior method)
End of explanation
df.iloc[3]
Explanation: Selection by Position
See more in Selection by Position
Select via the position of the passed integers
End of explanation
df.iloc[3:5,0:2]
Explanation: By integer slices, acting similar to numpy/python
End of explanation
df.iloc[[1,2,4],[0,2]]
Explanation: By lists of integer position locations, similar to the numpy/python style
End of explanation
df.iloc[1:3,:]
Explanation: For slicing rows explicitly
End of explanation
df.iloc[:,1:3]
Explanation: For slicing columns explicitly
End of explanation
In [37]: df.iloc[1,1]
Out[37]: -0.17321464905330858
For getting fast access to a scalar (equiv to the prior method)
In [38]: df.iat[1,1]
Out[38]: -0.17321464905330858
Explanation: For getting a value explicitly
End of explanation
flt = (df.Ann >= 0.5) & (df.Ann < 1.5) 
df[flt]
In [39]: df[(df.Ann >= 0.5) & (df.Ann < 1.5)]
Explanation: Boolean Indexing
Using a single column’s values to select data.
End of explanation
df[df > 0]
Explanation: A where operation for getting.
End of explanation
df2 = df.copy()
df2['E'] = ['one', 'one','two','three','four','three']
df2
df2[df2['E'].isin(['two','four'])]
Explanation: Using the isin() method for filtering:
End of explanation
s1 = pd.Series([1,2,3,4,5,6], index=pd.date_range('20130102', periods=6))
s1
df['F'] = s1
df['G'] = df['Ann']-df['Bob']
df
df.G
Explanation: Setting
Setting a new column automatically aligns the data by the indexes
End of explanation
df.at[dates[0],'Ann'] = 17.6
df
Explanation: Setting values by label
End of explanation
df.iat[5,2] = 349
df
Explanation: Setting values by position
End of explanation
df.loc[:,'D'] = np.array([5] * len(df))
Explanation: Setting by assigning with a numpy array
End of explanation
df
Explanation: The result of the prior setting operations
End of explanation
df2 = df.copy()
df2[df2 > 0] = -df2
df2
Explanation: A where operation with setting.
End of explanation
df1 = df.reindex(index=dates[0:4], columns=list(df.columns) + ['E'])
df1.loc[dates[0]:dates[1],'E'] = 1
df1
Explanation: Missing Data
pandas primarily uses the value np.nan to represent missing data. It is by default not included in computations.
See the Missing Data section
Reindexing allows you to change/add/delete the index on a specified axis. This returns a copy of the data.
End of explanation
df1.dropna(how='any')
Explanation: To drop any rows that have missing data.
End of explanation
df1
df1.fillna(value=5)
Explanation: Filling missing data
End of explanation
pd.isnull(df1)
Explanation: To get the boolean mask where values are nan
End of explanation
df.mean(0)
Explanation: Operations
See the Basic section on Binary Ops
Stats
Operations in general exclude missing data.
Performing a descriptive statistic
End of explanation
df.mean(1)
Explanation: Same operation on the other axis
End of explanation
s = pd.Series([1,3,5,np.nan,6,8], index=dates).shift(2)
s
df.sub(s, axis='index')
Explanation: Operating with objects that have different dimensionality and need alignment. In addition, pandas automatically broadcasts along the specified dimension.
End of explanation
df
df.apply(np.cumsum)
df
df.max() ##df.apply(max)
df.Ann.max()-df.Ann.min()
df.apply(lambda x: x.max() - x.min())
Explanation: Apply
Applying functions to the data
End of explanation
s = pd.Series(np.random.randint(0, 7, size=10))
s
s.value_counts()
Explanation: Histogramming
See more at Histogramming and Discretization
End of explanation
s = pd.Series(['A', 'B', 'C', 'Aaba', 'Baca', np.nan, 'CABA', 'dog', 'cat'])
s.str.lower()
Explanation: Series is equipped with a set of string processing methods in the str attribute that make it easy to operate on each element of the array, as in the code snippet below. Note that pattern-matching in str generally uses regular expressions by default (and in some cases always uses them). See more at Vectorized String Methods.
End of explanation
df = pd.DataFrame(np.random.randn(10, 4))
df
# break it into pieces
pieces = [df[:3], df[3:7], df[7:]]
pd.concat(pieces)
Explanation: Merge
Concat
pandas provides various facilities for easily combining together Series, DataFrame, and Panel objects with various kinds of set logic for the indexes and relational algebra functionality in the case of join / merge-type operations.
See the Merging section
Concatenating pandas objects together with concat():
End of explanation
left = pd.DataFrame({'key': ['foo', 'boo', 'foo'], 'lval': [1, 2, 3]})
#right = pd.DataFrame({'key': ['boo', 'foo', 'foo'], 'rval': [4, 5, 6]})
right = pd.DataFrame({'key': ['foo', 'foo'], 'rval': [5, 6]})
left
right
pd.merge(left, right, on='key', how='left')
Explanation: Join
SQL style merges. See the Database style joining
End of explanation
df = pd.DataFrame(np.random.randn(8, 4), columns=['A','B','C','D'])
df
s = df.iloc[3]
df.append(s, ignore_index=True)
Explanation: Append
Append rows to a dataframe. See the Appending
End of explanation
df = pd.DataFrame({'A' : ['foo', 'bar', 'foo', 'bar',
   ....:                           'foo', 'bar', 'foo', 'foo'],
   ....:                    'B' : ['one', 'one', 'two', 'three',
   ....:                           'two', 'two', 'one', 'three'],
   ....:                    'C' : np.random.randn(8),
   ....:                    'D' : np.random.randn(8)})
   ....: 
df
Explanation: Grouping
By “group by” we are referring to a process involving one or more of the following steps
- Splitting the data into groups based on some criteria
- Applying a function to each group independently
- Combining the results into a data structure
See the Grouping section
End of explanation
df.groupby('A').sum()
Explanation: Grouping and then applying a function sum to the resulting groups.
End of explanation
df.groupby(['A','B']).sum()
Explanation: Grouping by multiple columns forms a hierarchical index, which we then apply the function.
End of explanation
tuples = list(zip(*[['bar', 'bar', 'baz', 'baz',
   ....:                      'foo', 'foo', 'qux', 'qux'],
   ....:                     ['one', 'two', 'one', 'two',
   ....:                      'one', 'two', 'one', 'two']]))
   ....: 
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=['A', 'B'])
df2 = df[:4]
df2
Explanation: Reshaping
See the sections on Hierarchical Indexing and Reshaping.
Stack
End of explanation
stacked = df2.stack()
stacked
Explanation: The stack() method “compresses” a level in the DataFrame’s columns.
End of explanation
stacked.unstack()
stacked.unstack(1)
stacked.unstack(0)
Explanation: With a “stacked” DataFrame or Series (having a MultiIndex as the index), the inverse operation of stack() is unstack(), which by default unstacks the last level:
End of explanation
df = pd.DataFrame({'A' : ['one', 'one', 'two', 'three'] * 3,
   .....:                    'B' : ['A', 'B', 'C'] * 4,
   .....:                    'C' : ['foo', 'foo', 'foo', 'bar', 'bar', 'bar'] * 2,
   .....:                    'D' : np.random.randn(12),
   .....:                    'E' : np.random.randn(12)})
   .....: 
df
Explanation: Pivot Tables
See the section on Pivot Tables.
In [100]:
End of explanation
pd.pivot_table(df, values='D', index=['A', 'B'], columns=['C'])
Explanation: We can produce pivot tables from this data very easily:
End of explanation
rng = pd.date_range('1/1/2012', periods=100, freq='S')
ts = pd.Series(np.random.randint(0, 500, len(rng)), index=rng)
ts.resample('5Min').sum()
Out[105]: 
2012-01-01    25083
Freq: 5T, dtype: int64
Explanation: Time Series
pandas has simple, powerful, and efficient functionality for performing resampling operations during frequency conversion (e.g., converting secondly data into 5-minutely data). This is extremely common in, but not limited to, financial applications.
See the Time Series section
End of explanation
rng = pd.date_range('3/6/2012 00:00', periods=5, freq='D')
ts = pd.Series(np.random.randn(len(rng)), rng)
ts
ts_utc = ts.tz_localize('UTC')
ts_utc
Explanation: Time zone representation
End of explanation
ts_utc.tz_convert('US/Eastern')
Explanation: Convert to another time zone
End of explanation
rng = pd.date_range('1/1/2012', periods=5, freq='M')
ts = pd.Series(np.random.randn(len(rng)), index=rng)
ts
ps = ts.to_period()
ps
ps.to_timestamp()
Explanation: Converting between time span representations
End of explanation
prng = pd.period_range('1990Q1', '2000Q4', freq='Q-NOV')
ts = pd.Series(np.random.randn(len(prng)), prng)
ts.index = (prng.asfreq('M', 'e') + 1).asfreq('H', 's') + 9
ts.head()
Explanation: Converting between period and timestamp enables some convenient arithmetic functions to be used. In the following example, we convert a quarterly frequency with year ending in November to 9am of the end of the month following the quarter end:
End of explanation
df = pd.DataFrame({"id":[1,2,3,4,5,6], "raw_grade":['a', 'b', 'b', 'a', 'a', 'e']})
Explanation: Categoricals
Since version 0.15, pandas can include categorical data in a DataFrame. For full docs, see the categorical introduction and the API documentation.
End of explanation
df["grade"] = df["raw_grade"].astype("category")
df["grade"]
Out[124]: 
0    a
1    b
2    b
3    a
4    a
5    e
Name: grade, dtype: category
Categories (3, object): [a, b, e]
Explanation: Convert the raw grades to a categorical data type.
End of explanation
df["grade"].cat.categories = ["very good", "good", "very bad"]
Explanation: Rename the categories to more meaningful names (assigning to Series.cat.categories is inplace!)
End of explanation
df["grade"] = df["grade"].cat.set_categories(["very bad", "bad", "medium", "good", "very good"])
df["grade"]
Explanation: Reorder the categories and simultaneously add the missing categories (methods under Series .cat return a new Series per default).
End of explanation
df.sort_values(by="grade")
Out[128]: 
   id raw_grade      grade
5   6         e   very bad
1   2         b       good
2   3         b       good
0   1         a  very good
3   4         a  very good
4   5         a  very good
Explanation: Sorting is per order in the categories, not lexical order.
End of explanation
df.groupby("grade").size()
Explanation: Grouping by a categorical column shows also empty categories.
End of explanation
ts = pd.Series(np.random.randn(1000), index=pd.date_range('1/1/2000', periods=1000))
ts = ts.cumsum()
%matplotlib inline
ts.plot()
Explanation: Plotting
Plotting docs.
End of explanation
df = pd.DataFrame(np.random.randn(1000, 4), index=ts.index,
   .....:                   columns=['A', 'B', 'C', 'D'])
   .....: 
df = df.cumsum()
%matplotlib inline
plt.figure(); df.plot(); plt.legend(loc='best')
Explanation: On DataFrame, plot() is a convenience to plot all of the columns with labels:
End of explanation
df.to_csv('foo.csv')
Explanation: Getting Data In/Out
CSV
Writing to a csv file
End of explanation
pd.read_csv('foo.csv')
Explanation: Reading from a csv file
End of explanation
## df.to_hdf('foo.h5','df')
Explanation: HDF5
Reading and writing to HDFStores
Writing to a HDF5 Store
End of explanation
## pd.read_hdf('foo.h5','df')
Explanation: Reading from a HDF5 Store
End of explanation
df.to_excel('foo.xlsx', sheet_name='Sheet1')
Explanation: Excel
Reading and writing to MS Excel
Writing to an excel file
End of explanation
pd.read_excel('foo.xlsx', 'Sheet1', index_col=None, na_values=['NA'])
Explanation: Reading from an excel file
End of explanation
<END_TASK>