import pandas as pd
pd.set_option("display.max_rows",1000)    # or pd.options.display.max_rows=1000
pd.set_option("display.max_columns",20)   # or pd.options.display.max_columns=20 
pd.set_option('precision',7)
pd.set_option('large_repr', 'truncate')

"""
Explanation: <a id="top"></a>
<center><h1>Python <a href="http://pandas.pydata.org/pandas-docs/stable/">Pandas</a> Cheat Sheet</h1></center>
As a data analyst, these are common tasks I perform using Pandas

Pandas display output options
Quick summary statistics, # of rows, columns, etc
Sorting More Than One Column
Removing duplicates
Creating a column based on values from another column
Removing a column
Replacing values in a series
Dropping or Filling NaN/NA values
Reading CSV or Excel files
Renaming axis
Renaming indexes or columns
Binning data into intervals
Grouping data
Sub-selecting or Slicing a Data Frame
Boolean indexing
Obtaining columns with partial column labels
Getting value counts
Getting cumulative sum
Pivot table
Creating a new column based on a grouping using transform() method
Pivot table Percent of Rows
Pivot table Percent of Columns
Transpose a data frame
Converting index to a column
Converting column to index
How to add or fill in missing dates
How to connect to an ODBC data source (Windows)
How to convert data in wide format to long format using melt()
How to convert data in long format data to wide format using pivot()
Using category data type to control sort order
Merging 2 data frames using merge()
Finding rows containing data with missing values
Converting a data type of a column in a data frame
Plotting data frames using MATPLOTLIB ver 1.5+
Plotting data frame directly and creating sub-plots
Creating a Bokeh chart from a Data Frame
Making plotly charts directly from data frame
Method chaining
Sending Pandas data frame to R using rpy2 IPython notebook extension
Python clone of R's dplyr
BONUS #1: A HUGE list of python and pandas snippets by Chris Albon
BONUS #2: More goodies from a major pandas contributor, Tom Augspurger

<a id='options'></a>
Setting display output options
[back to top]
End of explanation
"""


import pandas as pd
data = pd.DataFrame({'group': ['a', 'a', 'a', 'b','b', 'b', 'c', 'c','c'],
                 'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})
data

data.describe()

"""
Explanation: <a id="quick_summary">
Quick summary statistics using df.describe() and data types using df.info().  Also check out pandas-summary - an extension for data frame's describe() method.
[back to top]
End of explanation
"""


data.info()

"""
Explanation: df.info() shows data types, number of rows and columns, and memory usage of your data frame
End of explanation
"""


data.sort_values(by=['group','ounces'], ascending=[False, True], inplace=True)
data

"""
Explanation: <a id='sorting'></a>
Sorting More Than One Column
[back to top]
Just pass a list of columns you want to sort and pass corresponding list of True/False to the ascending parameter
End of explanation
"""


import pandas as pd
data = pd.DataFrame({'k1': ['one'] * 3 + ['two'] * 4, 'k2': [3, 2, 1, 3, 3, 4, 4]})

data.sort_values(by='k2')
data

data.drop_duplicates()  # by default, duplicate is defined by all columns

"""
Explanation: <a id='removing_duplicates'></a>
Removing duplicates
[back to top]
End of explanation
"""


data.drop_duplicates(subset='k1')  # duplicate in column k1 only

"""
Explanation: Define duplicates by column name(s):
End of explanation
"""


import pandas as pd
data = pd.DataFrame({'food': ['bacon', 'pulled pork', 'bacon', 'Pastrami','corned beef', 'Bacon', 'pastrami', 'honey ham','nova lox'],
                 'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})
data

"""
Explanation: <a id="map"></a>
Creating a new column based on values from another column
[back to top]
End of explanation
"""


meat_to_animal = {
'bacon': 'pig',
'pulled pork': 'pig',
'pastrami': 'cow',
'corned beef': 'cow',
'honey ham': 'pig',
'nova lox': 'salmon'
}

def meat2animal(series):
    if series["food"]=='bacon':
        return 'pig'
    elif series["food"]=='pulled pork':
        return 'pig'
    elif series["food"]=='pastrami':
        return 'cow'
    elif series["food"]=='corned beef':
        return 'cow'
    elif series["food"]=='honey ham':
        return 'pig'
    else:
        return 'salmon'

data['animal'] = data['food'].map(str.lower).map(meat_to_animal)
data

"""
Explanation: Suppose you wanted to add a column indicating the type of animal that each food came from. Let’s write down a mapping of each distinct meat type to the kind of animal using a dictionary and a function:
End of explanation
"""


# axis=1 means to apply the function for each row, but I prefer to use axis='columns' instead
data['animal2'] = data.apply(meat2animal,axis='columns')
data

"""
Explanation: Or we could use apply() and apply the meat2animal() function to the new column:
End of explanation
"""


import pandas as pd
import numpy as np

df = pd.DataFrame({'data1' : np.random.randn(5),
                   'data2' : np.random.randn(5)})

df.assign(ratio = df['data1'] / df['data2'])

"""
Explanation: We can also use dataframe.assign() function which was added in pandas version 0.16
End of explanation
"""


data.drop('animal2', axis='columns', inplace=True)  # dropping/removing a column
data

"""
Explanation: [back to top]
<a id='remove_column'></a>
Removing or dropping a column
[back top top]
End of explanation
"""


import pandas as pd
import numpy as np

data = pd.Series([1., -999., 2., -999., -1000., 3.])
data

"""
Explanation: <a id="replacing_values"></a>
Replacing Values in a Series
[back to top]
End of explanation
"""


data.replace(-999, np.nan, inplace=True)
data

"""
Explanation: If you want to replace -999 with NaN:
End of explanation
"""


import pandas as pd
data = pd.Series([1., -999., 2., -999., -1000., 3.])
data

data.replace([-999, -1000], np.nan, inplace=True)
data

"""
Explanation: If you want to replace multiple values at once, you instead pass a list then the substitute value:
End of explanation
"""


data.dropna()

"""
Explanation: <a name="drop_fill_na"></a>
Filling or Dropping NaN ("Not a Number") Values
[back to top]
Dropping rows that have NaN:
End of explanation
"""


data.fillna(value=0)

"""
Explanation: Filling/replacing NaN values with something else (replace NaN with 0/zero):
End of explanation
"""


import pandas as pd
data = pd.DataFrame(np.arange(12).reshape((3, 4)),index=['Ohio', 'Colorado', 'New York'],columns=['one', 'two', 'three', 'four'])
data

data.index.map(str.upper)

data.index = data.index.map(str.upper)
data

"""
Explanation: <a name="renaming_axis"></a>
Renaming Axis Indexes
[back to top]
End of explanation
"""


data.rename(index=str.title, columns=str.upper, inplace=True)  # str.title means to make the 1st letter capitalized only
data

"""
Explanation: If you want to create a transformed version of a data set without modifying the original, a useful method is rename:
End of explanation
"""


import pandas as pd
data = pd.DataFrame(np.arange(12).reshape((3, 4)),index=['Ohio', 'Colorado', 'New York'],columns=['one', 'two', 'three', 'four'])
data

data.rename(index={'Ohio': 'INDIANA'},columns={'three': 'peekaboo'},inplace=True)
data

"""
Explanation: <a id="renaming_indexes_columns"></a>
Renaming Indexes or Columns
[back to top]
<i>rename()</i> can be used in conjunction with a dict-like object providing new values for a subset of the axis labels:
End of explanation
"""


data.rename(index=str.title, columns=str.upper, inplace=True)  # str.title means to make the 1st letter capitalized only
data

"""
Explanation: You can also apply str functions to modify the index or column labels
End of explanation
"""


ages = [20, 22, 25, 27, 21, 23, 37, 31, 61, 45, 41, 32]

"""
Explanation: <a id="binning"></a>
Binning Data Into Intervals
[back to top]
End of explanation
"""


bins = [18, 25, 35, 60, 100]

cats = pd.cut(ages, bins)
cats

cats.labels

cats.levels

pd.value_counts(cats)

"""
Explanation: Let’s divide these into bins of 18 to 25, 26 to 35, 35 to 60, and finally 60 and older. To do so, you have to use <i>cut()</i> function:
End of explanation
"""


pd.cut(ages, [18, 26, 36, 61, 100], right=False)

"""
Explanation: If you want the right value to be exclusive in the intervals:
End of explanation
"""


group_names = ['Youth', 'YoungAdult', 'MiddleAged', 'Senior']
pd.cut(ages, bins, labels=group_names)
pd.value_counts(pd.cut(ages, bins, labels=group_names))

"""
Explanation: You can also pass your own bin names by passing a list or array to the labels option:
End of explanation
"""


import pandas as pd

df = pd.DataFrame({'key1' : ['a', 'a', 'b', 'b', 'a'],
                   'key2' : ['one', 'two', 'one', 'two', 'one'],
                   'data1' : np.random.randn(5),
                   'data2' : np.random.randn(5)})
df

grouped = df['data1'].groupby(df['key1'])
grouped.mean()

"""
Explanation: [back to top]
<a id="group"></a>
Grouping Data ( see also value_count() and pivot_table() )
[back to top]
End of explanation
"""


import numpy as np
import pandas as pd

dates = pd.date_range('20130101',periods=6)
df = pd.DataFrame(np.random.randn(6,4),index=dates,columns=list('ABCD'))
df

"""
Explanation: <a id='slicing'></a>
Sub-selecting or Slicing a Data Frame
[back to top]
Filtering by label name: [loc]<br>
Filtering by index row and/or column: [iloc]
<a id='df'></a>
End of explanation
"""


df[0:3]  # get first 3 rows of the data frame

"""
Explanation: Getting first n rows of data frame using index slicing syntax
End of explanation
"""


df['20130102':'20130104'] # get rows by index range

"""
Explanation: Slicing based on data frame's index range
End of explanation
"""


df.loc[:,['A','B']]  # syntax is: df.loc[rows_index, cols_index]

"""
Explanation: <a id='loc'></a>
Slicing based on column labels/names using loc
[view df]
End of explanation
"""


df.loc['20130102':'20130104',['A','B']]

"""
Explanation: Slicing based on row index label and column label combined using loc
End of explanation
"""


df.iloc[3]  # returns 4th row (index=3) of the data frame

df.iloc[3:5,0:2]  # returns specific range of rows and columns of the data frame

df.iloc[[1,5],[0,2]] # returns specific rows and columns using lists containing columns or row indexes

df.iloc[1:3,:]  # returning specific rows and returning all columns

df.iloc[:,1:3]  # returning all rows and specific columns

df.iloc[1,1]  # getting secific scalar/single value

"""
Explanation: <a id='iloc'></a>
Slicing based on index position of the row or column using iloc
[view df]
End of explanation
"""


df[df.A > -0.5]  # or df[df["A"] > -0.5], this syntax works when there is a space in the column name

"""
Explanation: <a id='boolean'></a>
Boolean Indexing
[view df]
The boolean operators are: | for or, <strong>&</strong> for and, and ~ for not. These must be grouped by using parentheses.
End of explanation
"""


criteria = df.A > -0.5
df[criteria]

df2 = df.copy()
df2['E']=['one', 'one','two','three','four','three']
df2

df2[df2['E'].isin(['two','four'])]  # read as "return rows where column E contains two or four"

"""
Explanation: or I've seen some people do boolean filtering by passing "criteria" variable to the data frame like so:
End of explanation
"""


df2[df2.E.str.contains("tw|ou")]

"""
Explanation: We can also do partial string matching.  So let's say you don't know the exact spelling a word is you want to match, you can do this:
End of explanation
"""


df2[~df2['E'].isin(['two','four'])]  # column E containing values not in two or four

"""
Explanation: Using ~ to do a "NOT"
End of explanation
"""


import numpy as np
import pandas as pd

dates = pd.date_range('20130101',periods=6)
df = pd.DataFrame(np.random.randn(6,4),index=dates,columns=list('ABCD'))
df

df.query('A > C')

df.query('A > 0')

df.query('A > 0 & A < 1')

df.query('A > B | A > C')  # where A is greater than B or A is greater than C

"""
Explanation: Filtering using query() method
End of explanation
"""


import pandas as pd

df = pd.DataFrame({'key1' : ['a', 'a', 'b', 'b', 'a'],
                   'key2' : ['one', 'two', 'one', 'two', 'one'],
                   'data1' : np.random.randn(5),
                   'data2' : np.random.randn(5)})
df

df.filter(like='data')

"""
Explanation: <a id='partial_column_labels'></a>
Obtaining columns with partial column labels
[back to top]
End of explanation
"""


import pandas as pd
data = pd.DataFrame({'group': ['a', 'a', 'a', 'b','b', 'b', 'c', 'c','c'],
                 'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})
data

data.group.value_counts()

"""
Explanation: <a id='value_counts'></a>
Getting Value Counts
It is so tempting to use the groupby() function or pivot_table, but most of the time, value_counts() function is all we need.
[back to top]
End of explanation
"""


data.group.value_counts().cumsum()

"""
Explanation: <a id='cum_sum'></a>
Getting Cumulative Sum
[back to top]
End of explanation
"""


import pandas as pd
data = pd.DataFrame({'group': ['a', 'a', 'a', 'b','b', 'b', 'c', 'c','c'],
                 'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})
data

"""
Explanation: <a id='pivot_table'></a>
Pivot table example - see also this excellent article on pivot tables
[back to top]
End of explanation
"""


data.pivot_table(values='ounces',index='group',aggfunc=np.mean)

"""
Explanation: Calculating the means of each group
End of explanation
"""


data.pivot_table(values='ounces',index='group',aggfunc='count')

"""
Explanation: Getting counts by group
End of explanation
"""


data.pivot_table(values='ounces',index='group',aggfunc='count').cumsum()

import pandas as pd

# sample data can be dowloaded here: http://pbpython.com/extras/sales-funnel.xlsx
df = pd.read_csv('/home/pybokeh/Downloads/sales-funnel.csv')

df.head()

by_rep = pd.pivot_table(data=df, index='Rep', columns='Product', values='Quantity', aggfunc='count', fill_value=0)
by_rep

"""
Explanation: Getting cumulative sum or running total of the group counts
End of explanation
"""


from dplython import *

diamonds.head()

"""
Explanation: <a id="transform">
Creating a new column based on a grouping using transform() method
[back to top]
Let's get sample data from dplython library which is a clone of dplyr
End of explanation
"""


(diamonds.assign(carat_diff=diamonds
                 .groupby('color')['carat']
                 .transform(lambda x:x-x.mean())
                )
                .sort_values(by=['color','carat_diff'])
                .head(10)
)

"""
Explanation: Goal: Create a column that contains the difference between a diamond's carat and the average of the carats for that diamond's color.<br><br>
Key take-away is to use the transform method of your specific column of the groupby object.
End of explanation
"""


# Using dplyr/dplython syntax
(diamonds >>
  group_by(X.color) >>
  mutate(carat_diff = X.carat - X.carat.mean()) >>
  ungroup() >>
  arrange(X.color, X.carat_diff) >>
  head(10)
)

"""
Explanation: For those curious how to do this using dplyr / dplython:
End of explanation
"""


perc_of_rows = by_rep.apply(lambda x : x / x.sum() * 100, axis='columns') # or axis=1
perc_of_rows

"""
Explanation: Here's a comparison I made between dplython and pandas.
<a id='perc_rows'>
Percent of rows
[back to top]
End of explanation
"""


perc_of_columns = by_rep.apply(lambda x : x / x.sum() * 100, axis='index') # or axis=0
perc_of_columns

"""
Explanation: <a id='perc_columns'>
Percent of columns
[back to top]
End of explanation
"""


import pandas as pd

df = pd.DataFrame({'key1' : ['a', 'a', 'b', 'b', 'a'],
                   'key2' : ['one', 'two', 'one', 'two', 'one'],
                   'data1' : np.random.randn(5),
                   'data2' : np.random.randn(5)})
df

df.head(1).transpose()

"""
Explanation: <a id='transpose'>
Transpose a data frame
[back to top]
Let's say you have a data frame with several columns and having a hard time scrolling to see what the data looks like.  Transpose it!
End of explanation
"""


df.dtypes

"""
Explanation: You can also do df.dtypes to accomplish similar output, but it's nice to see actual data along with the columns.
End of explanation
"""


import pandas as pd
data = pd.DataFrame({'group': ['a', 'a', 'a', 'b','b', 'b', 'c', 'c','c'],
                 'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})
data

data.reset_index(level=0, inplace=True)
data

"""
Explanation: <a id="index2column">
Converting a data frame index to a column
[back to top]
End of explanation
"""


import pandas as pd
col = ['a','b','c']
data = pd.DataFrame([[1,2,3],[10,11,12],[20,21,22]],columns=col)
data

data = data.set_index('a')
data

"""
Explanation: <a id="column2index">
How to set the data frame's index to be one of the columns
[back to top]
End of explanation
"""


data.index.name = None
data

"""
Explanation: To remove the index name ("a"), do:
End of explanation
"""


import pandas as pd

idx = pd.date_range('09-01-2013', '09-30-2013')

s = pd.Series({'09-02-2013': 2,
               '09-03-2013': 10,
               '09-06-2013': 5,
               '09-07-2013': 1})
s

"""
Explanation: <a id='missing_dates'>
How to add or fill in missing dates
[back to top]
End of explanation
"""


s.index = pd.DatetimeIndex(s.index)

s = s.reindex(idx, fill_value=0)
s

"""
Explanation: From above, we have holes in our data.  Let's fill in those missing holes using the idx date_range we specified above.
End of explanation
"""


import pyodbc           # If using PostgreSQL, MySQL, SQLite, Redshift, MS SQL Server, or Oracle, use db.py instead
import pandas as pd
from getpass import getpass    # Module that will create a text input widget AND mask your password

userid = 'your_userid'
pw = getpass(prompt='Enter your password: ')

cnxn_string = 'DSN=your_dsn;UID=' + userid + ';PWD=' + pw

cnxn = pyodbc.connect(cnxn_string)
cursor = cnxn.cursor()

sql = """
SELECT * from your_table...
"""

df = pd.read_sql(sql, cnxn, index_col=None, parse_dates={'some_column':"%Y-%m-%d"})

# Close connections
cursor.close()
cnxn.close()

"""
Explanation: <a id='database'>
How to connect and query against a database registered as an ODBC data source (Windows)
[back to top]
End of explanation
"""


%matplotlib inline
import matplotlib.pyplot as plt
from pandas_datareader import data as web
from datetime import datetime
import pandas as pd

stocks = {'tech':['GOOGL', 'MSFT', 'LNKD', 'YHOO', 'FB','HPQ','AMZN'],
          'auto':['TM','F','GM','HMC','NSANY','HYMTF'],
          'housing':['HD','WMT','LOW']
          }

start_date = datetime(2016,1,1)
end_date = datetime(2016, 4, 21)

def get_px(stock, start, end):
    return web.get_data_yahoo(stock, start, end)['Adj Close']

df = pd.DataFrame({n: get_px(n, start_date, end_date) for n in stocks['tech']})

"""
Explanation: Also check out Yhat's db.py.  Currently, doesn't support IBM DB2.  So I'm stuck with pyodbc at work for now.
<a id='melt'>
How to convert data in wide format to long format using pd.melt()
[back to top]
Certain charting libaries like ggplot expect the data to be in long format.  Let's get some data in wide format.
End of explanation
"""


df.head(10)

# Create a column from the index values
df.reset_index(level=0, inplace=True)
df.head(10)

"""
Explanation: Below is what data looks like in 'wide' format:
End of explanation
"""


df_long = pd.melt(df, id_vars=['Date']).dropna()
df_long.head()

df_long.tail()

df_long.rename(columns={'variable': 'Stock', 'value':'Price'},inplace=True)
df_long.head(10)

"""
Explanation: This is what data looks like in 'long' format:
End of explanation
"""


%matplotlib inline
import matplotlib.pyplot as plt
from ggplot import *

stock_plot = ggplot(df_long, aes(x='Date', y='Price', color='Stock')) + \
        geom_line() + \
        ylab('Price (US$)') + \
        ggtitle('Auto Stocks: Adj Close') + \
        scale_x_date(labels='%b %d %y',breaks=date_breaks('3 months') )
    
# Have to use MATPLOTLIB api to rotate x tick labels.  Hopefully some day, ggplot will implement a rotation option.
fig = stock_plot.draw()
ax = fig.gca()
labels = ax.get_xticklabels() 
for label in labels: 
    label.set_rotation(270)
plt.show()

"""
Explanation: Now we can plot the stock prices.  Since the data is in long format, we can take advantage of ggplot's color= parameter, which will magically make line charts with different colors for each stock.  If the data was in wide format, you would have had to manually specify all the different stocks to plot or use a loop construct.
[back to top] [back to section]
End of explanation
"""


pivoted = df_long.pivot(index='Date', columns='Stock', values='Price')
pivoted.head()

# since data is now in wide format, we can plot data frames directly
pivoted.plot.line()

"""
Explanation: Sadly, ggplot's development appears to have stagnated.  I would recommend using other charting libraries instead.
<a id='pivot'>
How to convert data in long format to wide format using dataframe.pivot()
In contrast to ggplot, there are charting libraries like plotly and bokeh that expect the data to be in wide format.  If you're plotting directly from pandas data frames, the data frames must be in wide format also.
[back to top]
End of explanation
"""


import pandas as pd
data = pd.DataFrame({'medal': ['bronze', 'silver', 'silver', 'gold','bronze', 'bronze', 'gold', 'gold','gold'],
                 'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})
data

data["medal"] = data["medal"].astype("category")
data.dtypes

data.sort_values(by=["medal"])

"""
Explanation: <a id='category'>
Using category data type to control sort order
[back to top]
End of explanation
"""


data["medal"].cat.set_categories([ "gold","silver","bronze"],inplace=True)

data.sort_values(by=["medal"])

"""
Explanation: Now define the order of the cetgorical data using set_categories()
[back to top] [back to section]
End of explanation
"""


import pandas as pd
left = pd.DataFrame({'group': ['a', 'a', 'a', 'b','b', 'b', 'c', 'c','c'],
                 'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})
left

right = pd.DataFrame({'label': ['a','b','c'],
                     'value': ['alpha','beta','charlie']})
right

"""
Explanation: <a id='merge'>
Merging 2 data frames using merge()
[back to top]
End of explanation
"""


inner_joined = pd.merge(left, right, how='inner', left_on='group', right_on='label')
inner_joined

"""
Explanation: By default, merge() does an inner join, but you can specify 'left', 'right', or 'outer' joins.  These types of joins are familiar if you have SQL background.  Here's a good SQL joins visualizer.
End of explanation
"""


import numpy as np
import pandas as pd
df = pd.DataFrame([range(3), [0, np.NaN, 0], [0, 0, np.NaN], range(3), range(3)])
df

df.isnull().any(axis=1)

df[df.isnull().any(axis=1)]

"""
Explanation: NOTE: To merge 2 Series, you have to use pd.concat() instead
For more on merge, join, and concatenate, see the official docs
YouTube video on joining data sets using pandas
<a id="missing">
Finding rows of a data frame containing missing data
[back to top]
End of explanation
"""


import pandas as pd

a = [['a', '1.2', '4.2'], ['b', '70', '0.03'], ['x', '5', '0']]
df = pd.DataFrame(a, columns=['one', 'two', 'three'])
df

df.dtypes

df[['two', 'three']] = df[['two', 'three']].astype(float)

df.dtypes

"""
Explanation: <a id='convert_type'>
Converting a data type of a column in a data frame
[back to top]
End of explanation
"""


%matplotlib inline
import pandas as pd
import matplotlib.pyplot as plt

df = pd.DataFrame({"var1":[1,2,3,4,5,6], "var2":[1,2,3,4,5,6]})
plt.plot(df["var1"], df["var2"])
plt.show()

"""
Explanation: <a id="matplotlib">
Plotting data frames using MATPLOTLIB version 1.5 and up
[back to top]
Starting with version 1.5, you can now pass a "data" parameter inside MATPLOTLIB's plot() function
Prior to version 1.5:
End of explanation
"""


plt.plot("var1", "var2", data=df)
plt.show()

"""
Explanation: With version 1.5+
End of explanation
"""


%matplotlib inline
import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns

df = pd.read_excel('/home/pybokeh/Downloads/temp.xlsx', sheetname='Claims')
df.hist(column='DTF', by='GRADE_SHORT')
plt.show()

"""
Explanation: <a id="subplots">
Plotting data frame directly and creating sub-plots or grouping using column=, by= parameters
[back to top]
When making histograms, pandas knows to make sub-plots based on the by=column option:
End of explanation
"""


df.boxplot(column='DTF', by='GRADE_SHORT')
plt.show()

"""
Explanation: For box plots, instead of making sub-plots, pandas intelligently splits the data using the by= parameter":
End of explanation
"""


from bokeh.sampledata.autompg import autompg as df
from bokeh.charts import BoxPlot, output_notebook, show, hplot

df.head()

box = BoxPlot(df, values='mpg', label='cyl', color='cyl',
               title="MPG Box Plot by Cylinder Count", plot_width=400, plot_height=400)

# Embed the chart within this notebook
output_notebook()

show(box)

"""
Explanation: <a id="bokeh">
Creating a Bokeh Chart from a Data Frame - see the docs for more examples
conda install bokeh or pip instal bokeh
[back to top]
End of explanation
"""


import pandas as pd
from plotly.offline import download_plotlyjs, init_notebook_mode, iplot
import numpy as np
import cufflinks as cf
init_notebook_mode()

df = pd.DataFrame(np.random.rand(10, 5), columns=['A', 'B', 'C', 'D', 'E'])

iplot(df.iplot(asFigure=True, kind='box', title='Plotly Pandas', dimensions=(800,500)), show_link=False)

"""
Explanation: <a id="plotly">
Making plotly charts directly from data frame.  See plotly documentation for more details.
Need to first install plotly and cufflinks: pip install plotly and pip install cufflinks
[back to top]
End of explanation
"""


%matplotlib inline
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

df = (pd.read_csv('/home/pybokeh/temp/vehicles.csv',
                 usecols=['year', 'make', 'model', 'comb08', 'fuelType', 'fuelType1', 
                          'fuelType2', 'atvType', 'cylinders', 'VClass'])
      .rename(columns={'comb08':'combmpg'})
      .query("make in('Honda','Acura','Toyota','Lexus') "
             "& fuelType1 in('Regular Gasoline','Premium Gasoline','Midgrade Gasoline') "
             "& cylinders in(4, 6) "
             "& VClass in('Compact Cars','Subcompact Cars','Midsize Cars','Large Cars','Sport Utility','Minivan') "
             "& ~(fuelType2 in('E85','Electricity','Natural Gas','Propane'))")
      ['combmpg'].plot.hist(alpha=0.5, label='Honda Motor Co')
     )
plt.title("Histogram of Combined Hwy+City MPG", weight="bold")
plt.show()

"""
Explanation: <a id="chaining">
Method Chaining
[back to top]
With method chaining, it eliminates the need for making intermediary variables.  You can process your data frame in a series of method calls when you enclose your data frame with parenthesis. Here's a very contrived example:
End of explanation
"""


# load the rpy2 extension
%load_ext rpy2.ipython

"""
Explanation: Also check out pipe() method.
<a id="rpy2">
Sending Pandas data frame to R using rpy2 IPython notebook extension
[back to top]
Assuming you have R and rpy2 installed, we can send a Pandas data frame to R using the rpy2 IPython notebook extension to take advantage of R's various statistical and graphing packages.
Simple example where I will pull Yen currency exchange rate data from the web and graph it using R's ggplot2 library:
End of explanation
"""


import pandas as pd

pandas_df = pd.read_csv('http://research.stlouisfed.org/fred2/data/EXJPUS.txt', sep='  ', skiprows=28, engine='python',
                 parse_dates=[0], header=None, names=['Month','Value'])

"""
Explanation: Let's fetch the exchange rate data:
End of explanation
"""


pandas_df.dtypes

pandas_df.tail()

"""
Explanation: Sanity check on the data:
End of explanation
"""


%R -i pandas_df

"""
Explanation: We'll use this one-liner %R magic command to send the Pandas data frame to R as input using the "-i" flag.  See here for other flags.
End of explanation
"""


from rpy2.robjects import pandas2ri
pandas2ri.activate()

"""
Explanation: Alternatively, you can use pandas built-in facility to do this:
End of explanation
"""


%%R -w 800 -h 500 -u px # instead of px, you can also choose 'in', 'cm', or 'mm'
df = as.data.frame(pandas_df)  # ensure data frame that was passed in from Python will be R's data frame type
library(ggplot2)        # plotting library
library(scales)         # enables us to change the x label time scale

p = ggplot(pandas_df, aes(pandas_df$Month, pandas_df$Value))
p = p + 
    geom_line() +
    scale_x_datetime(labels=date_format("%Y-%m"), breaks="5 years", minor_breaks="1 year") +
    ggtitle("Yen to US Dollars") +
    xlab("Time") +
    ylab("Yen Value")
print(p)

"""
Explanation: Next, we'll use %%R magic command which will make the entire code cell R executable:
End of explanation
"""


