<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <a id="top"></a>
<center><h1>Python <a href="http
Step1:  <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]
Step2:  df.info() shows data types, number of rows and columns, and memory usage of your data frame
Step3:  <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
Step4:  <a id='removing_duplicates'></a>
Removing duplicates
[back to top]
Step5:  Define duplicates by column name(s)
Step6:  <a id="map"></a>
Creating a new column based on values from another column
[back to top]
Step7:  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
Step8:  Or we could use apply() and apply the meat2animal() function to the new column
Step9:  We can also use dataframe.assign() function which was added in pandas version 0.16
Step10:  [back to top]
<a id='remove_column'></a>
Removing or dropping a column
[back top top]
Step11:  <a id="replacing_values"></a>
Replacing Values in a Series
[back to top]
Step12:  If you want to replace -999 with NaN
Step13:  If you want to replace multiple values at once, you instead pass a list then the substitute value
Step14:  <a name="drop_fill_na"></a>
Filling or Dropping NaN ("Not a Number") Values
[back to top]
Dropping rows that have NaN
Step15:  Filling/replacing NaN values with something else (replace NaN with 0/zero)
Step16:  <a name="renaming_axis"></a>
Renaming Axis Indexes
[back to top]
Step17:  If you want to create a transformed version of a data set without modifying the original, a useful method is rename
Step18:  <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
Step19:  You can also apply str functions to modify the index or column labels
Step20:  <a id="binning"></a>
Binning Data Into Intervals
[back to top]
Step21:  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
Step22:  If you want the right value to be exclusive in the intervals
Step23:  You can also pass your own bin names by passing a list or array to the labels option
Step24:  [back to top]
<a id="group"></a>
Grouping Data ( see also value_count() and pivot_table() )
[back to top]
Step25:  <a id='slicing'></a>
Sub-selecting or Slicing a Data Frame
[back to top]
Filtering by label name
Step26:  Getting first n rows of data frame using index slicing syntax
Step27:  Slicing based on data frame's index range
Step28:  <a id='loc'></a>
Slicing based on column labels/names using loc
[view df]
Step29:  Slicing based on row index label and column label combined using loc
Step30:  <a id='iloc'></a>
Slicing based on index position of the row or column using iloc
[view df]
Step31:  <a id='boolean'></a>
Boolean Indexing
[view df]
The boolean operators are
Step32:  or I've seen some people do boolean filtering by passing "criteria" variable to the data frame like so
Step33:  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
Step34:  Using ~ to do a "NOT"
Step35:  Filtering using query() method
Step36:  <a id='partial_column_labels'></a>
Obtaining columns with partial column labels
[back to top]
Step37:  <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]
Step38:  <a id='cum_sum'></a>
Getting Cumulative Sum
[back to top]
Step39:  <a id='pivot_table'></a>
Pivot table example - see also this excellent article on pivot tables
[back to top]
Step40:  Calculating the means of each group
Step41:  Getting counts by group
Step42:  Getting cumulative sum or running total of the group counts
Step43:  <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
Step44:  Goal
Step45:  For those curious how to do this using dplyr / dplython
Step46:  Here's a comparison I made between dplython and pandas.
<a id='perc_rows'>
Percent of rows
[back to top]
Step47:  <a id='perc_columns'>
Percent of columns
[back to top]
Step48:  <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!
Step49:  You can also do df.dtypes to accomplish similar output, but it's nice to see actual data along with the columns.
Step50:  <a id="index2column">
Converting a data frame index to a column
[back to top]
Step51:  <a id="column2index">
How to set the data frame's index to be one of the columns
[back to top]
Step52:  To remove the index name ("a"), do
Step53:  <a id='missing_dates'>
How to add or fill in missing dates
[back to top]
Step54:  From above, we have holes in our data.  Let's fill in those missing holes using the idx date_range we specified above.
Step56:  <a id='database'>
How to connect and query against a database registered as an ODBC data source (Windows)
[back to top]
Step57:  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.
Step58:  Below is what data looks like in 'wide' format
Step59:  This is what data looks like in 'long' format
Step60:  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]
Step61:  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]
Step62:  <a id='category'>
Using category data type to control sort order
[back to top]
Step63:  Now define the order of the cetgorical data using set_categories()
[back to top] [back to section]
Step64:  <a id='merge'>
Merging 2 data frames using merge()
[back to top]
Step65:  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.
Step66:  NOTE
Step67:  <a id='convert_type'>
Converting a data type of a column in a data frame
[back to top]
Step68:  <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
Step69:  With version 1.5+
Step70:  <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
Step71:  For box plots, instead of making sub-plots, pandas intelligently splits the data using the by= parameter"
Step72:  <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]
Step73:  <a id="plotly">
Making plotly charts directly from data frame.  See plotly documentation for more details.
Need to first install plotly and cufflinks
Step74:  <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
Step75:  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
Step76:  Let's fetch the exchange rate data
Step77:  Sanity check on the data
Step78:  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.
Step79:  Alternatively, you can use pandas built-in facility to do this
Step80:  Next, we'll use %%R magic command which will make the entire code cell R executable
<ASSISTANT_TASK:>
Python Code:
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
<END_TASK>