from IPython.core.display import HTML
HTML("<iframe src=http://pandas.pydata.org  width=800 height=350></iframe>")

"""
Explanation: Introduction to Pandas
pandas is a Python package providing fast, flexible, and expressive data structures designed to work with panel data.
pandas is well suited for tabular data with heterogeneously-typed colums, as in an SQL table or Excel spreadsheet
Key Features:

Easy handling of missing data
Automatic and explicit data alignment
Intelligent label-based slicing, indexing and subsetting of large data sets
Powerful, flexible group by functionality to perform split-apply-combine operations on data sets
Robust IO Tools for loading data from flat files, Excel files, databases etc.
End of explanation
"""


%matplotlib inline
import pandas as pd
import numpy as np

"""
Explanation: Before we explore the package pandas, let's import pandas package. We often use pd to refer to pandas in convention.
End of explanation
"""


counts = pd.Series([223, 43, 53, 24, 43])
counts

type(counts)

"""
Explanation: Series
A Series is a single vector of data (like a Numpy array) with an index that labels each element in the vector.
End of explanation
"""


counts[0]

counts[1:4]

"""
Explanation: If an index is not specified, a default sequence of integers is assigned as index. 


We can access the values like an array
End of explanation
"""


counts.values

counts.index

"""
Explanation: You can get the array representation and index object of the Series via its values and index atrributes, respectively.
End of explanation
"""


fruit = pd.Series([223,  43,  53,  24, 43],
                 index=['apple', 'orange', 'banana', 'pears', 'lemon'])

fruit

fruit.index

"""
Explanation: We can assign meaningful labels to the index, if they are available:
End of explanation
"""


fruit['apple']

fruit[['apple', 'lemon']]

"""
Explanation: These labels can be used to refer to the values in the Series.
End of explanation
"""


fruit.name = 'counts'
fruit.index.name = 'fruit'
fruit

"""
Explanation: We can give both the array of values and the index meaningful labels themselves:
End of explanation
"""


fruit > 50

fruit[fruit > 50]

"""
Explanation: Operations can be applied to Series without losing the data structure.
Use bool array to filter Series
End of explanation
"""


fruit2 = pd.Series([11, 12, 13, 14, 15],
                   index=fruit.index)
fruit2

fruit2 = fruit2.drop('apple')
fruit2

fruit2['grape'] = 18
fruit2

fruit3 = fruit + fruit2
fruit3

"""
Explanation: Critically, the labels are used to align data when used in operations with other Series objects.
End of explanation
"""


fruit3.dropna()

fruit3

fruit3.isnull()

"""
Explanation: Contrast this with arrays, where arrays of the same length will combine values element-wise; Adding Series combined values with the same label in the resulting series.
Notice that the missing values were propogated by addition.
End of explanation
"""


data = {'state': ['Ohio', 'Ohio', 'Ohio', 'Nevada', 'Nevada'],
       'year':[2000, 2001, 2002, 2001, 2003],
       'pop':[1.5, 1.7, 3.6, 2.4, 2.9]}
df = pd.DataFrame(data)
df

len(df)  # Get the number of rows in the dataframe

df.shape # Get the (rows, cols) of the dataframe

df.T

df.columns  # get the index of columns

df.index  # get the index of the row

df.dtypes

df.describe()

"""
Explanation: DataFrame
A DataFrame is a tabular data structure, encapsulating multiple series like columns in a spreadsheet.Each column can be a different value type (numeric, string, boolean etc).
End of explanation
"""


df

df['state']  # indexing by label

df[['state', 'year']]  # indexing by a list of label

df[:2]  # numpy-style indexing

df.iloc[0, 0]

df.iloc[0, :]

df.iloc[:, 1]

df.iloc[:2, 1:3]

df.loc[:, 'state']

df.loc[:, ['state', 'year']]

"""
Explanation: There are three basic ways to access the data in the dataframe
use DataFrame[] to access data quickly
use DataFrame.iloc[row, col] integer position based selection method
use DataFrame.loc[row, col] label based selection method
End of explanation
"""


df['debt'] = np.random.randn(len(df))
df['rain'] = np.abs(np.random.randn(len(df)))
df

df = df.drop('debt', axis=1)
df

row1 = pd.Series([4.5, 'Nevada', 2005, 2.56], index=df.columns)
df.append(row1,ignore_index=True)

df.drop([0, 1])

"""
Explanation: Add new column and delete column
End of explanation
"""


df['pop'] < 2

df

df.loc[df['pop'] < 2, 'pop'] = 2

df

df['year'] == 2001

(df['pop'] > 3) | (df['year'] == 2001)

df.loc[(df['pop'] > 3) | (df['year'] == 2001), 'pop'] = 3
df

"""
Explanation: data filtering
End of explanation
"""


df.sort_index(ascending=False)

df.sort_index(axis=1, ascending=False)

"""
Explanation: Sorting index
End of explanation
"""


df

df.loc[:, ['pop', 'rain']].sum()

df.loc[:,['pop', 'rain']].mean()

df.loc[:, ['pop', 'rain']].var()

df.loc[:, ['pop', 'rain']].cumsum()

"""
Explanation: Summarizing and Computing Descriptive Statistics
Built in functions to calculate the values over row or columns.
End of explanation
"""


df

df.loc[:, ['pop', 'rain']].apply(lambda x: x.max() - x.min())  # apply new functions to each row

"""
Explanation: Apply functions to each column or row of a DataFrame
End of explanation
"""


df

df.groupby(df['state']).mean()

df.groupby(df['state'])[['pop', 'rain']].apply(lambda x: x.max() - x.min())

grouped = df.groupby(df['state'])
group_list = []
for name, group in grouped:
    print(name)
    print(group)
    print('\n')

"""
Explanation: Grouped and apply
End of explanation
"""


df

df_h = df.set_index(['state', 'year'])
df_h

df_h.index.is_unique

df_h.loc['Ohio', :].max() - df_h.loc['Ohio', :].min()

"""
Explanation: Set Hierarchical indexing
End of explanation
"""


df

df.to_csv('test_csv_file.csv',index=False)

%more test_csv_file.csv

df_csv = pd.read_csv('test_csv_file.csv')
df_csv

"""
Explanation: Import and Store Data

Read and write csv file.
End of explanation
"""


writer = pd.ExcelWriter('test_excel_file.xlsx')
df.to_excel(writer, 'sheet1', index=False)
writer.save()

df_excel = pd.read_excel('test_excel_file.xlsx', sheetname='sheet1')
df_excel

pd.read_table??

"""
Explanation: Read and write excel file.
End of explanation
"""


df = pd.DataFrame([[1, 6.5, 3.], [1., np.nan, np.nan],
                [np.nan, np.nan, np.nan], [np.nan, 6.5, 3.]])

df      

cleaned = df.dropna()  # delete rows with Nan value
cleaned

df.dropna(how='all')  # delete rows with all Nan value

df.dropna(thresh=2)  # keep the rows with at least thresh non-Nan value

df.fillna(0)  # fill Nan with a constant

"""
Explanation: Filtering out Missing Data
You have a number of options for filtering out missing data.
End of explanation
"""


variables = pd.DataFrame({'normal': np.random.normal(size=100), 
                       'gamma': np.random.gamma(1, size=100), 
                       'poisson': np.random.poisson(size=100)})
variables.head()


variables.shape

variables.cumsum().plot()

variables.cumsum().plot(subplots=True)

"""
Explanation: Plotting in DataFrame
End of explanation
"""


