%matplotlib inline
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, freq='M')
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
"""


