Simple Equations in Sympy

Simple Equation Solution with sympy¶

Thanks to sympy, even if you forgot highschool math, you can still do highschool math. Sympy is part of the basic Anaconda distribution, so there is no prep work needed to try it out in an Anaconda environment. Below, a simple quadratic equation with integer solutions, solved with sympy. A notebook can be downloaded for the code below.

In [5]:

# setup
from sympy.solvers import solve
from sympy import Symbol
# prepare to work with single unknown
x = Symbol('x')
type(x)

Out[5]:

sympy.core.symbol.Symbol

So that we can be sure the sympy solution is correct, we'll start with something where we know the answer. This quadratic should have solutions at -3 and 2.

(x + 3) * (x - 2) = 0

x^2 + x - 6 = 0

The solve function from sympy.solver looks for 2 arguments, the equation to solve and the symbol to solve for.

In [7]:

solve (x**2 + x - 6, x)

Out[7]:

[-3, 2]

Flowcharts in Graphviz

Flowcharts are great but WYSIWYG flowcharting software is horrible. Fortunately, there us Graphviz. This free tool allows for the creation of network diagrams, including flowcharts, using The Dot Language. We'll use the pydot python library, though there are other choices. Start by installing graphviz. Use conda to install pydot by typing

> conda install pydot

into the shell (Linux/OSX) or the Anaconda shell (Windows). Then you are ready to create nodes, connect them with edges, and build a typical flowchart.

A notebook is available with the code below. Download and open in jupyter.

# imports
import os
import pydot
from IPython.display import Image, display
# setup
ng = pydot.Dot(graph_type='digraph')
# nodes
startN = pydot.Node("start", style="filled", fillcolor="yellow")
stopN = pydot.Node("stop", style="filled", fillcolor="red")
# modifiers
makeChartN = pydot.Node("make\nchart", shape="box")
dullChartN = pydot.Node("remove\ndetail", shape="box")
noiseChartN = pydot.Node("add\nnoise", shape="box")
# tests
infoCapN = pydot.Node("useful items\n >? N + random", shape="diamond")
noiseFloorN = pydot.Node("distractions\n <? N + random", shape="diamond")
# collection
nodeL = [startN, makeChartN, dullChartN, noiseChartN, infoCapN, noiseFloorN, stopN]
# add nodes to chart
for nodeN in nodeL:
    ng.add_node(nodeN)
# add edges
ng.add_edge(pydot.Edge(startN, makeChartN))
ng.add_edge(pydot.Edge(makeChartN, infoCapN))
 
ng.add_edge(pydot.Edge(infoCapN, dullChartN, label="Y"))
ng.add_edge(pydot.Edge(infoCapN, noiseFloorN, label="N"))
 
ng.add_edge(pydot.Edge(dullChartN, noiseFloorN))
 
ng.add_edge(pydot.Edge(noiseFloorN, noiseChartN, label="Y"))
ng.add_edge(pydot.Edge(noiseFloorN, stopN, label="N"))
 
ng.add_edge(pydot.Edge(noiseChartN, infoCapN))
# display
ng.write_png("typicalFlowchart.png")
Image("typicalFlowchart.png")

It's Past Time to Switch to Python 3

For a long time, it made sense to stick with Python 2 over Python 3 because important libraries worked best for Python 2. Then even after libraries worked correctly in Python 3, a user who had a large collection of libraries installed might not want to start all over with Python 3. Anaconda has changed all that with a Python distribution that includes all of the fundamental data science libraries, a package manager that assists in keeping packages up to date, and other goodies. So all the examples here will be Python 3 from now on.

SQL on pandas Tables with sqlite3

It can be extremely convenient to use SQL with pandas dataframes. Maybe you are working with pandas dataframes and you want to implement logic that you are borrowing from another environment. Or you could be trying to demonstrate something about SQL, and want a database populated with data that can work on your audience desktops. The sqlite3 module makes this simple. The writeup on python.org is very good but more examples are always better. Here we'll populate some dataframes with wikiHelp, load them into an sqlite3 database, and join them using sql. Of course, we can also join dataframes in pandas, so we will compare the results of of the joins.

Loading dataframes

Here we populate dataframes with extracts from Wikipedia List of 100 wealthiest countries and List of countries by continent.

from wikiHelp import WIKI, getWtable

# CONST
WEALTH = "List_of_countries_by_wealth_per_adult"
CONTINENTS = """List of sovereign states
and dependent territories by continent""" # make narrow for show
CONTINENT = "_".join(CONTINENTS.split()) # string for wiki

# get 1th table from wealth per adult
wealthDF = getWtable("%s%s" %(WIKI, WEALTH), tabNum=1) 
print (wealthDF.iloc[0])
# get 3th continent table
noAmDF = getWtable("%s%s" %(WIKI, CONTINENT), tabNum=3)
print (noAmDF.iloc[0])

The first record from each dataframe is below.

wealthDF

• country World
• totalWealth 280,289
• wealthPerAdult 56,541
• medianWealthPerAdult 3,582
• Name: 0, dtype: object

noAmDF

• flag
• name Anguilla
• capital The Valley
• status Overseas territory of the United Kingdom
• Name: 0, dtype: object

Loading dataframes into sqlite3 database Below, we instantiate an sqlite3 connection, and load our dataframes into it using the pandas to_sql method. The method requires a string table name and a connection, and we supply also an index=False. The database is created in RAM just so that there is no file to clean up, but the code to create instead on disk is shown.

import sqlite3
conn = sqlite3.connect(':memory:') # create connection in ram
# conn = sqlite3.connect('example.db') # create conn on disk
wealthDF.to_sql("wealth", conn, index=False) # load wealthDF
noAmDF.to_sql("northAmerica", conn, index=False) # load noAmDF

We can now access the northAmerica and wealth tables in the database with sql, or use pandas to access the same data he noAmDF and wealthDF. We will use the pandas read_sql method, which we will supply with a string of sql and a connection. For some sql that's quick and easy to check, we will simply count the records in the tables with "select count(*) from tablename ", which we can compare to the len of the dataframe.

qS = 'select count(*) as n from %s' # query string
for tableName, df in zip("wealth northAmerica".split() \
                                 , (wealthDF, noAmDF)):
    tableLen = pd.read_sql(qS %(tableName), conn).iloc[0].n
    print ("%s\t%d\t%d" %(tableName, tableLen, len(df)))

• wealth 107 107
• northAmerica 45 45

Which shows matching lengths. The simplicity of the read_sql call is really amazing, so we'll look at it again with a join. Let's look at the mean wealth and capital only for countries in North America. The sql would be

select wealth.country, wealth.wealthPerAdult
    , continent.capital
from northAmerica continent
inner join wealth wealth
    on wealth.country = continent.name

In Python, we send that sql through the read_sql and view the resulting dataframe.

qS = """select wealth.country, wealth.wealthPerAdult
    , continent.capital
from northAmerica continent
inner join wealth wealth
    on wealth.country = continent.name """
joinDF = pd.read_sql(qS, conn) # join 2 tables in sql
print(joinDF.to_string(index=False))

The Python to perform the same join on the original dataframes is below, were we also check that the resulting dataframe is identical to the one from above. The output shows the same entried in a different order because the database is unordered like a Python dictionary and we made no effort to impose an order on the output.

keepL = "country wealthPerAdult capital".split() # ordered field list 
mergeDF = wealthDF.merge(noAmDF, left_on="country", right_on="name" \
                         , how="inner")[keepL] # merge 2 dataframe
print(mergeDF.to_string(index=False))

Reading Wikipedia Tables

This module, wikiHelp.py, is a small group of functions for loading a table from a Wikipedia page into a Pandas dataframe. The motivation is to have a tool that provides consistent results loading small dataframes for practice exercises. Once the module is imported, we can easily read in the Nth table from a Wikipedia page as a dataframe simply as below:

from wikiHelp import WIKI, getWtable
CONTINENT = "_".join(["List_of_sovereign_states" \
    , "and_dependent_territories_by_continent"])
africaDF = getWtable("%s%s" %(WIKI, CONTINENT), tabNum=0)
print africaDF.shape

Reading and Writing Excel Workbooks with Python

Lots of data is stored in Excel workbooks, so it's convenient to be able to read that. Workbooks can also be a great way of delivering results, since they make it easy for your clients to perform ad hoc checks. The PyWin32 (formerly win32all) module provides the ability to create and access many different types of Windows files on a Windows machine. But if you want to work with Excel files without Windows, you can use the Python Excel modules xlrd, xlwt, and xlutil for reading, writing, and filtering Excel files. Below we'll show simple examples of reading and writing Excel workbooks with xlrt and xlwt.

The packages have an excellent tutorial pdf, and the examples below are just a subset of what's shown there. Maybe the most interesting part of the tutorial is the last page, which shows the power of the packages by suggesting exercises an instructor might use for a workshop on the packages, including:

• inserting a row into a worksheet
• splitting a workbook into one file per worksheet
• scanning a directory and reporting the location of error cells

Let's start by reading a workbook using the open_workbook from xlrd. For this workbook, we know there are 3 worksheets.

>>> # get to directory with workbook
>>> import os
>>> os.chdir("~/blog") 
>>> os.chddir("xlutils")
>>> # open workbook
>>> from xlrd import open_workbook 
>>> book = open_workbook("workbook.xls")
>>> print book.nsheets # check number of worksheets
3
>>> print book.sheet_names() # check sheet names
[u'letters', u'numbers', u'Sheet3']

And we can obtain the first worksheet using the sheet_by_index method on the workbook.

>>> sheet = book.sheet_by_index(0) # get first worksheet
>>> sheet.name # check name
u'letters'
>>> sheet.nrows # get number of rows
3
>>> sheet.ncols # and cols
1

Finally the values in the worksheet can be obtained with the workbook's cell method. Using that method in a list comprehension with the numbers of rows and columns already obtained we load the worksheet into a list.

>>> val = sheet.cell(0, 0)
>>> val.value
u'a'
>>> valL = [sheet.cell(i, 0).value for i in range(sheet.nrows)]
>>> valL
[u'a', u'b', u'c']

Writing a workbook is even simpler. First we open a workbook with the xlwt Workbook, and obtain a worksheet with the workbook's add_sheet method. On the sheet, we can write values with the write method. The arguments for the write method are (row, column, value). The row and column use 0 offset counting, so row 0 and column 0 are A1. Finally to save the workbook, we use the workbook's save method. 

>>> wb = Workbook() # obtain a new workbook
>>> ws = wb.add_sheet('s0') # obtain a new worksheet named s0
>>> ws.write(0, 0, '1') # write a '1' in the top left cell
>>> ws.write(0, 1, 1) # write a 1 in B1
>>> wb.save('workbook2.xls') # save workbook