Statistics: Range, Variance, and Standard Deviation

Measuring the spread can be a useful tool when analyzing a set of numbers. Three common measures of spread of range, variance, and standard deviation.

Here is the data set we will be working with: [2,4,6,7,8,10,15,18,22,26]


Range is the simplest of the three measures. To find the range, all you need to do is subtract the smallest number in the set from the largest number

range = large-small

range = 26-2 = 24


Variance is created by taking the average of the squared difference between each value in the set minus the mean. We square the differences so that values above and below the mean do not cancel each other out.

Let’s find the mean:


If you haven’t seen the x with the line over it before, this is referred to as x bar and it is used to represent the mean.

To find the variance you take the first number in your set, subtract the mean and square the result. You repeat that for each number in your list. Finally you add up all the results and divide by n (the number of items in your list)

ex – ((2-12)^2 + (4-12)^2 +….+(26-12)^2) / 10


variance = 58.56

Now I know what you are thinking, how can the average distance from the mean be 58.56 when the furthest point from the mean (26) is only 14? This is because we are squaring the differences. To get a number more in line with the data set, we have another measure called the standard deviation.

Standard Deviation

The standard deviation returns a value more in-line with what you would expect based on your data. To find the standard deviation – simply take the square root of the variance.

std dev = 7.65

Population vs Sample

The equations above work great if you have the entire population. What I mean by that is, if your data contains all the  data in the set. Using our data, imagine if the numbers were ages of children in a large family. If there are 10 kids in the family, then I have all the ages, so I am dealing with the population.

However, if we instead have sampled 10 random ages from all the kids in a large extended family where the total number of kids is 90. In this case, since we are looking at 10 out of 90, we are not dealing with the population, but the sample.

When working with the sample, you need to make an adjustment to your variance and standard deviation equations. The change is simple. Instead of dividing by n you will now divide by n-1.  This offset makes up for the fact you do not have all the data.



Python: Create, Import, and Use a Module

Today we are going to cover what I consider a flaw in the iPython Notebook environment. Code you write cannot be imported into another program like you can using just a standard Python compiler. At least it cannot under its native .ipynb format.

There is a work around though. Starting jupyter notebooks up with the –script command results in your notebook being saved as both a ipynb file and a .py file. And .py is what we are looking for.


note ‘–script‘ is being deprecated in favor of nbconvert. But as of this moment it still works and will work on older versions you have downloaded as well.

Create a Module

I am going to create a little module call add2. This module contains 3 simple functions: sqr2, sub2, and add2


You can see that now along with my add2.ipynb file, I have a file.


In my instance, my iPython notebooks are stored under my user directory. If you are not sure where you notebooks are, you can use the pwd command (print working directory)


** note the double \\ are due to a formatting function in Python. \ is a break command that can be used for many purposes. Example \t means to insert a tab.


So effectively you have to use \\ if you want a \ in Python, making my working directory C:\Users\Benjamin  

Import the Module

Now if I open up a new notebook and try using the functions I have just created, I will error out.


So, what I need to do is import add2, then I can start calling on the functions I created in add2


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python

SQL: Intro to Joins

More often than not, the information you need does not all reside in one table. To query information from more than one table, use the Join command.

In our example, we are going to be using AdventureWorks2012. If you want to follow along, but do not have SQL Server or AdventureWorks2012 installed, click in the following links:

We are going to be using the following Tables for our Join

Notice they both have a matching field – BusinessEntityID – This field will be important when creating a join.


We can call up both tables individually through separate SQL Queries


However, to combine the two tables into a single result, you will need a Join.

Inner Join

SQL uses Inner Joins by default. In this lesson, I am only going to focus on inner joins. Inner joins work by focusing on columns with matching information. In the example below, the columns with matching information are the Name columns.

The combined result only contains rows who have a match in both source tables. Notice that Sally and Sarah do not appear in the result table.


The syntax is as follows:

Select *
from Table A join Table B
on TableA.Match = TableB.Match


In our example, the matching column is BusinessEntityID in both tables.

To clean the code up a little bit, we can assign aliases to our tables using the “as” keyword. We are using E and S as our aliases. We can now use those aliases in our “on” clause.


And, just as in a regular select statement, we can choose which columns we want. You do however, have to identify which table the columns you are requesting are from. Notice how I did this using the E and S aliases.



MS SQL Server: Database Diagrams

MS SQL Server comes with a function called Database Diagrams. You can use it to produce ERDs (Entity Relationship Diagrams).

Start by right clicking on Database Diagrams > New Database Diagram in the Object Explorer


If you get the following error, you database does not have an owner assigned.


To assign an owner, right click on your database name and select Properties


Under Select a page > FilesBy Owner select radio button> Browse


Select your user name from the list and then hit Okay until you are back to the main screen.


Right click Database Diagrams > New Database Diagram


Click Yes


Now select some tables. In this example, I am choosing Employee and SalesPerson


The diagram shows the tables you picked, and the data fields. It also shows connections.


Notice the tables do not all connect directly with each other. Another point to take note of is the small yellow keys located in from of some fields. These let you know that those fields are the primary key(s) for their respective table. The primary key is the unique identifier for the table.


Python: Error handling

No matter how well crafted your code, you will inevitably come across an error every once in a while. Unfortunately, Python’s default setting when catching an error is to crash the program.

In the example below, I accidentally asked for a letter, when the code int(raw_input()) is looking for an integer.

So when I enter a string, I get an errorpyerror





Using Try, we can handle such mishaps in a more elegant fashion

The syntax is a follows:

        Your Code
         What to do if your code errors out
         What to do if your code is successful


And as you can see from above, it doesn’t just protect against code errors, but it protects against user errors as well.

Using a while loop, we can give the user another chance if they don’t enter a number the first time.

Code explanation

  • while True:  – starts an infinite loop
  • continue – returns to the beginning of the while loop
  • break – exits the loop



You can add the finally: command to the end of a try: statement if you have something you want to execute regardless of whether there was an error or not.


Exception types:

You can also determine what your program does based on the type of error





No Error


A list of Exception types can be found here: Link

If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python

Python: Print Variables and User Input


The basic Print statement in Python is Print “” – note Print ” also works. You can choose either single or double quotes, but you cannot mix them in the same statement.

Print Variables

Printing variables in Python is a bit more complicated.

If the variable you want to print is a number, use the special string %d inside the quotes and % variable outside the quotes.

If you have more than one variable, you need to have a %d for each variable. Then place your variables inside () at the end of the print line.


To print a string, you use %s. Notice below you can mix strings and numbers in the same line.


You can use %r if you are not sure what value the variable will have. It will print both.

User Input

raw_input() allows you ask the user for input.

You can assign the user input to a variable.

You can also pre-define what kind of input you want. Note the error in the second instance. x = int(raw_input()) means I am looking for an integer as input.


If I enter an integer, I do not get the error.


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python

Excel: Scatter Plots

Download worksheet here: ExcelCharts2

Our data set contains 3 sets of readings for 4 sensors (A,B,C,D)


Scatter Plot

Reading 1

Highlight Columns A and B – From Ribbon > Insert>Scatter


Here is a close up of the Scatter Plot icon


Here is our plotting of Reading 1


Reading 2

To add the Reading 2 column to the plot, right click on the chart area and Select Data


Select Add


  • Select the Heading from Column C for Series Name
  • A2:A5 as Series X values
  • C2:C5 as Series Y values


Reading 3

Repeat again for Reading 3


Now, double click on the Y-Axis and go to the Format Axis box that will appear on your right.

Select the Bar Chart Icon, and change the Axis Bounds to minimum of 40 and max of 100.


This will help make the spacing between dots more pronounced. Generally altering a Y Axis away from 0 is considered bad taste as it tends to over-pronounce differences between elements.


Now click on X-Axis and make the changes below.


Next, go to Add Chart Element in the upper right corner. Legend>Right


Here is our scatter plot


Now wait a minute. I know what you are thinking, why is my X Axis 1 – 4 and not A,B,C,D

This is a flaw in Excel. There are some third party packages you can install that will allow you to rename the X Axis in a XY Scatter plot, but not with Excel in its default state.

You can however change the chart type to a Line Chart.

Go to Ribbon > Change Chart Type

Select Line


Now your X-Axis is properly labeled





SQL: Aggregates, Group By, and Having

The Where clause is great. It helps you filter out items from a table, leaving only the records you want. But sometimes you are not looking for the actual records. Instead, sometimes you only want to know how many records.

In this example, I will using the [HumanResources].[EmployeeDepartmentHistory] from AdventureWorks2012:


Count and Group By

The Count(*) function is used to count the number of rows.

Now, when we combine count(*) with a Group By clause, we can count how many records exist for each element in our group by clause.

All non aggregate columns in the select statement need to be in the group by function or you will get an error.

The code below counts the amount of rows containing each departmentID



Run the following query:

select BusinessEntityID, count(*) as amt
from [HumanResources].[EmployeeDepartmentHistory]
group by BusinessEntityID

You will get a list 290 records long. Most of these records will have a 1 in the amt column (meaning there is only one records containing the BusinessEntityID referenced)

If you want to know how many BusinessEntityIDs in this table have more than one record, we just have to add the Having clause to our query.


having can only be used in conjunction with aggregate functions.

Min(), Max()

Min and Max return the minimum and maximum of whatever column you you place in the parenthesis




Python: Line Graph

Let’s build on what we learned in : Python: Intro to Graphs

First, import pyplot from matplotlib

Remember %matplotlib inline let’s you see your graphs in the jupyter notebooks


Line 1

Note I am using a list comprehension to fill the x axis.


Now, let us do some formatting:

  • ‘r:’    –   red dotted line
  • label = ‘up’   –  adds label to the line
  • py.legend(loc=9)  – adds a legend to the chart


Line 2

For line 2, I want to create a reverse curve:

  • d = u  – list u is copied to d
  • d.reverse() – reverses values in d


Plot the line.

  • ‘g-.’ – green dashed line


Combine the two lines


Line 3

Here I use another list comprehension  and zip to make our last like. This adds each element of list u and d in order.

  • ‘b-‘ – blue solid line


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python

Python: Pandas Intro (Dataframes)

Before we continue onto Dataframes, I want to clear up something from the Series exercise. Note the line (from pandas import Series, DataFrame)


Using that line, I can call upon Series or DataFrame directly in my code


In this example below, I did not directly import the methods Series and DataFrame, so I when I tried x = Series() I go an error.

I had to use the full method name of pd.Series() for this to work.



DataFrames provide another level of data management for Python. Those of you who come from a more data driven background with appreciate DataFrames.

Let’s start by creating dictionary.


Now, pass the dictionary to the method DataFrame()

Note, now you have a table looking structure with named columns


You can call up a list of indexes or columns using the methods below:

pdDF3.jpg will return a summary of your DataFrame


Head and Tail

Create a new DataFrame from a dictionary


If you want just see a few of the first elements, you can use the head() method


The tail() method does the last few. You can even choose how many rows you want.



The describe() method gives you some quick statistics on any numeric column



You can slice a DataFrame just as you would a list


Choose What to Display

DataFrames allow you to filter what rows to display by value or column


There is a lot more you can do with Series and DataFrame in pandas, and we will be covering them in later lessons. For now though, I think you have a general idea.

If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python