2016-05-11.sagews

Math 480: Open Source Mathematical Software

2016-05-11

William Stein

Lectures 20: Pandas (part 2 of 3)

Today:

1. Reminder: your homework and peer grading is due Friday at 6pm. Update about peer grading guidelines.

2. Start the screencast.

3. More pandas...

• getting data into SMC

• Series

• DataFrames

%auto
import pandas as pd
pd.__version__
import numpy as np
import matplotlib.pyplot as plt
import matplotlib
matplotlib.style.use('ggplot')
%default_mode python    # avoid Sage data types like Integer, which cause trouble (due to the Sage preparser).
%typeset_mode True

u'0.18.1'

1. Getting data into SMC

Some deer at the Google data center
in Iowa where SMC lives.

• WARNING: SMC has absolutely no direct access to the files on your laptop. It is a remote computer account running in the cloud. That said, if you use pandas/python, etc, on your own computer at some point, it will of course have direct access. (Maybe do a live demo of python get-pip.py then pip install --user pandas on my mac laptop.)

• +New, paste the link into the box, click "Download from Internet".

• +New, drag and drop, upload a file (only up to 30MB right now).

• Use wget or curl from the terminal or in a %sh cell.

• Use scp or rsync (see project settings... SSH button) from your computer to the project.

I've never mentioned %sh before -- it's just like the terminal but from a worksheet. As long as a command isn't interactive, it should work for you...

%sh
wget http://spatialkeydocs.s3.amazonaws.com/FL_insurance_sample.csv.zip

%sh
ls

%sh
unzip FL_insurance_sample.csv.zip

df = pd.read_csv('FL_insurance_sample.csv')
df.head(10)

df.describe()

pd.Series

http://pandas.pydata.org/pandas-docs/version/0.18.1/dsintro.html#series

---

• Datasets are built out of pd.Series objects, which are like a List of values, but with a specific type (like floating point number, integer, string, ...) and an index. More precisely, a pd.Series is a "One-dimensional ndarray with axis labels"

• Larger pd.DataFrame objects are built out of several such series. Each of them is a column, must have the same length, and each column has a name.

Examples:

names = pd.Series(["Bob", "Marlene", "Joe", "Jane"])
age   = pd.Series([55, 18, 71, 22])

names

0 Bob 1 Marlene 2 Joe 3 Jane dtype: object

age

0 55 1 18 2 71 3 22 dtype: int64

s = pd.Series(['john', 'private', 389, 'nathan'], index=['name', 'rank', 'number', 'name'])
s

name john rank private number 389 name nathan dtype: object

s['rank']

private

s['name']

name john name nathan dtype: object

t = pd.Series([39, 'seargent'], index=['number', 'rank'])
s + t

name NaN name NaN number 428 rank privateseargent dtype: object

Indexing...

age

0 55 1 18 2 71 3 22 dtype: int64

age[2]

$\displaystyle 71$

age[1:-1]

1 18 2 71 dtype: int64

age[age > 30]

0 55 2 71 dtype: int64

age[age > 30][age < 60]

0 55 dtype: int64

︠7c1b1564-a513-4f85-8acc-88bbe02333f8i︠
%md
... so a pd.Series is much like a Python dict except you can do
arithmetic, there is an order, you can have the same
key multiple times, indexing is amazing.  And everything is super fast.

... so a pd.Series is much like a Python dict except you can do arithmetic, there is an order, you can have the same key multiple times, indexing is amazing. And everything is super fast.

Exercise right now!:

• Make a Python dict d of your choosing, then try doing pd.Series(d). It should work.

• Make up 3 different pd.Series objects with various choices of values and index.

• Try adding them together and see what happens.

︠d9362b43-5b4a-42e2-b2d4-5ed18cd9ca73i︠
%md
<img src="nyt.png" width=500 class="pull-right" style="margin-left:50px">



I got this in a "spam" email from [Enthought](https://www.enthought.com/?utm_source=Enthought+Contacts&utm_campaign=0ca52d18bb-1605_Data_Import_Tool_Webinar_EU_5_10_205_10_2016&utm_medium=email&utm_term=0_906d1cd342-0ca52d18bb-422543997) today on [a course](http://blog.enthought.com/python/webinar-fast-forward-through-the-dirty-work-of-data-analysis-new-python-data-import-and-manipulation-tool-makes-short-work-of-data-munging-drudgery/#.VzNw_BUrKHo) they run about Pandas!

> "50 to 80 percent of time is spent wading through the tedium of the first two steps – acquiring and wrangling data – before even getting to the real work of analysis and insight." -- [New York Times](http://mobile.nytimes.com/2014/08/18/technology/for-big-data-scientists-hurdle-to-insights-is-janitor-work.html?_r=2&utm_source=Enthought+Contacts&utm_campaign=0ca52d18bb-1605_Data_Import_Tool_Webinar_EU_5_10_205_10_2016&utm_medium=email&utm_term=0_906d1cd342-0ca52d18bb-422543997)

I got this in a "spam" email from Enthought today on a course they run about Pandas!

"50 to 80 percent of time is spent wading through the tedium of the first two steps – acquiring and wrangling data – before even getting to the real work of analysis and insight." -- New York Times

pd.DataFrame

Documentation: http://pandas.pydata.org/pandas-docs/version/0.18.1/dsintro.html#dataframe

DataFrame = "a 2-dimensional labeled data structure with columns of potentially different types."

Think of it as:

• a spreadsheet, or

• a SQL table, or

• a dict of pd.Series objects.

Now, we build a pd.DataFrame:

names = pd.Series(["William", "Marlene", "Jon", "Jane"])
age   = pd.Series([42, 68, 19, 22])

# We are literally making this data frame from a dictionary of series objects!
# (This gives us no control over the order of the columns though...)
people = pd.DataFrame({"name": names, "age":  age})
people

	age	name
0	42	William
1	68	Marlene
2	19	Jon
3	22	Jane

# Excplicit control of column order:
pd.DataFrame({"name": names, "age":  age}, columns=['name', 'age'])

	name	age
0	William	42
1	Marlene	68
2	Jon	19
3	Jane	22

The bold numbers or values on the left are called the "index".

The index is used to uniquely identify a row.

Use people.ix[i] to get the row with given index!

This isn't obvious. You just have to memorize it. Learn it now.

# get the row with index "1" -- which
people.ix[1]

age 68 name Marlene Name: 1, dtype: object

Of course, the rows don't have to be indexed by numbers -- anything hashable is allowed.

d = pd.DataFrame({'col1':{'name':'william', 'rank':'private'},
                  'col2':{'name':'jon', 'rank':'general', 'ser':5077}})
d

	col1	col2
name	william	jon
rank	private	general
ser	NaN	5077

d.ix['rank']

col1 private col2 general Name: rank, dtype: object

d.ix['ser']

col1 NaN col2 5077 Name: ser, dtype: object

** Exercise right now!** Make a data frame for which there are two rows with the same index i, showing that the index need not be unique! [Hint: we made such a series above]

What happens when you type d.ix[i]?

︠ce38cd3c-2f72-406a-b456-d3450536992f︠

︠44542d09-b5b5-4b26-a463-c31d4b605538i︠
%md

NOTE: Besides `.ix`, there are also other properties for various purposes. Read more about them here:

* `.ix`, `.loc`, `.iloc`: http://pandas.pydata.org/pandas-docs/stable/indexing.html#different-choices-for-indexing

NOTE: Besides .ix, there are also other properties for various purposes. Read more about them here:

• .ix, .loc, .iloc: http://pandas.pydata.org/pandas-docs/stable/indexing.html#different-choices-for-indexing

d = pd.DataFrame({'col1':{'name':'william', 'rank':'private'},
                  'col2':{'name':'jon', 'rank':'general', 'ser':5077}})
d

	col1	col2
name	william	jon
rank	private	general
ser	NaN	5077

d.loc[['name', 'rank']]

	col1	col2
name	william	jon
rank	private	general

d.iloc[1]

col1 private col2 general Name: rank, dtype: object

d.iloc[0]

col1 william col2 jon Name: name, dtype: object

d.iloc[-1]

col1 NaN col2 5077 Name: ser, dtype: object

Question: How to get all rows starting from index 1 up to the end?

Answer: slicing.

people = pd.DataFrame({"name": names, "age":  age})
people

	age	name
0	42	William
1	68	Marlene
2	19	Jon
3	22	Jane

people[:2]

	age	name
0	42	William
1	68	Marlene

people[1:3]

	age	name
1	68	Marlene
2	19	Jon

people[::2]

	age	name
0	42	William
2	19	Jon

CSV

CSV is a very simple column-oriented data format, where a text file contains rows and a comma is a delimiter for the columns.

see animals.csv

The first row contains the header, describing the columns.

Pandas then imports this file via pd.read_csv() (the argument of this function could not only be a filename, but also a URL link 'http://...' pointint to a csv file)

open("animals.csv",'w').write(
"""name,type,tail,legs,age,color
joe,elephant,Y,4,16,gray
tim,dog,Y,4,7,golden
susan,cat,Y,4,4,black
frank,fish,N,0,1,green
olivia,spider,N,6,.5,black
minki,cat,Y,4,6,white""")

%sh
cat animals.csv

name,type,tail,legs,age,color joe,elephant,Y,4,16,gray tim,dog,Y,4,7,golden susan,cat,Y,4,4,black frank,fish,N,0,1,green olivia,spider,N,6,.5,black minki,cat,Y,4,6,white

animals = pd.read_csv("animals.csv")
animals

	name	type	tail	legs	age	color
0	joe	elephant	Y	4	16.0	gray
1	tim	dog	Y	4	7.0	golden
2	susan	cat	Y	4	4.0	black
3	frank	fish	N	0	1.0	green
4	olivia	spider	N	6	0.5	black
5	minki	cat	Y	4	6.0	white

# access columns
animals.name

0 joe 1 tim 2 susan 3 frank 4 olivia 5 minki Name: name, dtype: object

The power of Pandas is, that you can start asking questions about this data, filter it, compare it with other datasets, or even picture it in a plot very easily.

animals.describe()

	legs	age
count	6.000000	6.000000
mean	3.666667	5.750000
std	1.966384	5.654644
min	0.000000	0.500000
25%	4.000000	1.750000
50%	4.000000	5.000000
75%	4.000000	6.750000
max	6.000000	16.000000

# average age?
animals.age.mean()

$\displaystyle 5.75$

# which ones are younger than 6?
animals[animals.age < 6]

	name	type	tail	legs	age	color
2	susan	cat	Y	4	4.0	black
3	frank	fish	N	0	1.0	green
4	olivia	spider	N	6	0.5	black

animals.ix[animals.legs == 4]

	name	type	tail	legs	age	color
0	joe	elephant	Y	4	16.0	gray
1	tim	dog	Y	4	7.0	golden
2	susan	cat	Y	4	4.0	black
5	minki	cat	Y	4	6.0	white

Question: which animal(s) has/have the largest number of legs?

animals.name.ix[animals.legs == animals.legs.max()]

4 olivia Name: name, dtype: object

animals.legs

0 4 1 4 2 4 3 0 4 6 5 4 Name: legs, dtype: int64

animals.legs.plot.hist()

Plotting

In a related library "statsmodels", there are also various datasets included. Try plotting one of them (where it makes sense)

%auto
from statsmodels import datasets

x = datasets.statecrime
d = x.load_pandas().data # accesses the pandas dataframe
d.head(10) # .head or .tail give you the n-th rows at top or at the bottom

	violent	murder	hs_grad	poverty	single	white	urban
state
Alabama	459.9	7.1	82.1	17.5	29.0	70.0	48.65
Alaska	632.6	3.2	91.4	9.0	25.5	68.3	44.46
Arizona	423.2	5.5	84.2	16.5	25.7	80.0	80.07
Arkansas	530.3	6.3	82.4	18.8	26.3	78.4	39.54
California	473.4	5.4	80.6	14.2	27.8	62.7	89.73
Colorado	340.9	3.2	89.3	12.9	21.4	84.6	76.86
Connecticut	300.5	3.0	88.6	9.4	25.0	79.1	84.83
Delaware	645.1	4.6	87.4	10.8	27.6	71.9	68.71
District of Columbia	1348.9	24.2	87.1	18.4	48.0	38.7	100.00
Florida	612.6	5.5	85.3	14.9	26.6	76.9	87.44

d.violent.plot.hist()