'''
Pandas is one of the most powerful contributions to python for quick and easy data analysis. Data Science is dominated by
one common data structure - the table. Python never had a great native way to manipulate tables in ways that many analysts
are used to (if you're at all familliar with spreadsheets or relational databases). The basic Pandas data structure is the Data 
Frame which, if you are an R user, should sound familliar.

This module is a very high level treatment of basic data operations one typically uses when manipulating tables in Python. 
To really learn all of the details, refer to the book.
'''

#To import
import pandas as pd #Its common to use pd as the abbreviation
from pandas import Series, DataFrame #Wes McKinney recommends importing these separately - they are used so often and benefit from having their own namespace

'''
The Series - for this module we'll skip the Series (see book for details), but we will define it. A Series is a one dimensional
array like object that has an array plus an index, which labels the array entries. Once we present a Data Frame, one can think
of a series as similar to a Data Frame with just one column.
'''

'''
A simple example of the DataFrame - building one from a dictionary
(note for this to work each list has to be the same length)
'''
data = {'state':['OH', 'OH', 'OH', 'NV', 'NV'], 
        'year':[2000, 2001, 2002, 2001, 2002],
        'pop':[1.5, 1.7, 3.6, 2.4, 2.9]}

frame = pd.DataFrame(data) #This function will turn the dict to the data frame. Notice that the keys become columns and an index is created

frame

#To retrieve columns...use dict-like notation or use the column name as an attribute
frame['state'], frame.state

#To retrieve a row, you can index it like a list, or use the actual row index name using the .ix method
frame[1:2], frame.ix[1]

#Assigning a new column is easy too
frame['big_pop'] = (frame['pop']>3)
frame

'''
One operation on data that is frequent enough to highlight here is sorting
'''
import numpy as np

df = pd.DataFrame(np.random.randn(10,1), columns = ['Rand1'])
df['OrigOrd'] = df.index.values
df = df.sort_index(by = 'Rand1', ascending = False) #Sorting by a particular column
df

df = df.sort_index() #Now sorting back, using the index
df

'''
Some of the real power we are after is the ability to condense, merge and concatenate data sets. This is where we
want Python to have the same data munging functionality we usually get from executing SQL statements on relational
databases.
'''

alpha = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']
df1 = DataFrame({'rand_float':np.random.randn(10), 'key':alpha})
df2 = DataFrame({'rand_int':np.random.randint(0, 5, size = 10), 'key':alpha})

'''
So we have two dataframes that share indexes (in this case all of them). We want to combine them. In sql we would execute
a join, such as Select * from table1 a join table2 b on a.key=b.key;
'''
df_merge = pd.merge(df1,df2,on='key')
df_merge

'''
Now that we have this merged table, we might want to summarize it within a key grouping
'''
df_merge.groupby('rand_int').mean()

'''
You can have multiple aggregation functions, but the syntax isn't the same
'''
df_merge.groupby('rand_int').agg([np.sum, np.mean, len, np.std])