Month: May 2016

R: Vector operations

On By Ben Larson Ph.D.In RLeave a comment

This is one area where R really shines. Consider two vectors a= (1,2,3,4) and b= (5,6,7,8). If you wanted to add the elements of the two vectors together like this:

1+5,2+6,3+7,4+8

In most programming languages, you would need to utilize a loop. And you could do it with a loop in R.

for (i in 1:4){
      print(a[i]+b[i])
}

Or, thanks to vector operations, you can just use the + sign

vectorFunc.jpg

You can also use -, *, /

vectorFunc1.jpg

You  can pass vector to a function and it will automatically iterate through it for you.

vectorFunc2.jpg

 

 

R: seq() and rep()

On By Ben Larson Ph.D.In RLeave a comment

If you want to count out a sequence of number in R, you can simply use a :

1:15 goes from 1 to 15, while 6:22 goes from 6 to 22

seqRep.jpg

You can even assign these sequences to a variable, creating a vector

seqRep1.jpg

However, you can only count by 1 using this method.

seq()

With seq(1,15) I can count from 1 to 15, just like using 1:15

seqRep2.jpg

If I add a third argument though, now I am counting from 1 to 15 by 4

seqRep3.jpg

rep()

rep() stands for replicate. Using rep(), I can make a list of repeating elements

seqRep4

You can of course create vectors with both seq() and rep() by assigning them to a variable

seqRep5.jpg

Remember R indexes start at 1 not 0

so using the vector above, a[3] = 9 – a[1:3] = 1,5,9

 

 

 

Python: Linear Regression

On By Ben Larson Ph.D.In Python1 Comment

Regression is still one of the most widely used predictive methods. If you are unfamiliar with Linear Regression, check out my: Linear Regression using Excel lesson. It will explain the more of the math behind what we are doing here. This lesson is focused more on how to code it in Python.

We will be working with the following data set: Linear Regression Example File 1

Import the data

Using pandas .read_excel()

linReg.jpg

What we have is a data set representing years worked at a company and salary.

Let’s plot it

Before we go any further, let’s plot the data.

Looking at the plot, it looks like there is a possible correlation.

linReg1.jpg

Linear Regression using scipy

scipy library contains some easy to use maths and science tools. In this case, we are importing stats from scipy

the method stats.linregress() produces the following outputs: slope, y-intercept, r-value, p-value, and standard error.

linReg2

I set slope to m and y-intercept to b: so we match the linear formula y = mx+b

Using the results of our regression, we  can create an easy function to predict a salary. In the example below, I want to predict the salary of a person who has been working there 10 years.

linReg3.jpg

Our p value is nice and low. This means our variables do have an effect on each other

linReg4.jpg

Our standard error is 250, but this can be misleading based on the size of the values in your regression. A better measurement is r squared. We find that by squaring our r output

linReg5.jpg

R squared runs from 0 (bad) to 1 (good). Our R squared is .44. So our regression is not that great. I prefer to keep a r squared value at least .6 or above.

Plot the Regression Line

We can use our pred() function to find the y-coords needed to plot our regression line.

Passing pred() a x value of 0 I get our bottom value. I pass pred() a x value of 35 to get our top value.

I then redo my scatter plot just like above. Then I plot my line using plt.plot().

linReg6.jpg

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

Follow this link for more Python content: Python

 

 

Python: Accessing a SQL database

On By Ben Larson Ph.D.In MS SQL Server, Python3 Comments 
create database sandbox;

use sandbox;
CREATE TABLE employee_id (
        emp_nm varchar(30) not null,
        emp_id varchar(8),
        b_emp_id varchar(8),
        PRIMARY KEY(emp_nm) );

If you really want to do data work, you need to be able to connect to a database. In this example I will show you how to connect to and query data from MS SQL Server with the AdventureWorks2012 database installed.

This lesson assumes some very basic knowledge of SQL. If SQL is a complete mystery, head over to my SQL page: SQL  If you check out the first 4 intro lessons, you will know everything about SQL you need to know for this lesson.

Install pyodbc

To connect to the database, we need to install pyodbc. Go to your Anaconda terminal and type: pip install pyodbc

sqlpython.jpg

Now open up your jupyter notebook and start a new notebook

Connect to Database

import pyodbc

cnxn is our variable – it is commonly used as a shorten version of connection

syntax: pyodbc.connect(‘DRIVER={SQL Server}; SERVER=server name; DATABASE=database name;UID = user name; PWD = password’)

finally cursor =cnxn.cursor() creates a cursor for us. In SQL, a cursor is used to step through your results one row at a time.

sqlpython1.jpg

cursor.execute(place sql query here)  – this is how you pass a sql query – note query goes in quotes

tables = cursor.fetchall() – fetch all the rows in your query results

sqlpython2

We can now iterate through the rows in tables.

sqlpython3.jpg

I don’t like the layout of this. Also we can’t really work with the data.

Pandas Dataframe

first import pandas

  • d= [] – create empty dictionary
  • d.dappend({‘Name’:row.Name, ‘Class’: row.GroupName}) – fill dictionary 1 row at a time
  • df = pd.DataFrame(d) – convert your dictionary to a dataframe

sqlpython4.jpg

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

Follow this link for more Python content: Python

Python: Read CSV and Excel with Pandas

On By Ben Larson Ph.D.In Python1 Comment

Earlier is showed you how to use the Python CSV library to read and write to CSV files. While CSV does work, and I still use elements of it occasionally, you will find working with Pandas to be so much easier.

Practice Files Excel: Linear Regression Example File 1

CSV: heightWeight_w_headers

Let’s start with our CSV file. Unzip it and place it in a folder where you will be able to find it.

If you want to make your life a little easier, when you open your Jupyter Notebook, type pwd

This will give you your present working directory. If you place your csv file in this directory, all you have to do is call it by name.

csv1

I am not going to do that however. I want to show you how to work with directories.

Get Full Directory Path

For Windows Users, if you hold down the Shift key while right clicking on your file, you will see an option that says: Copy as Path

csv2

Read the CSV file

First import pandas as pd

Then assign a variable = pd.read_csv(file name) – paste the full path of your CSV file here.

variable.head() = the first 5 rows from your data frame.

pandaCSV

Write CSV file

Okay, let’s write a CSV file. First, let’s add some rows to current dataframe.

We will do this be first creating a new dataframe with 3 rows of data.

pandaCSV1.jpg

Now we will use the .append() function to append this new dataframe to end of our existing dataframe.

The ignore_index = True argument tells the new rows to continue using the index of the main dataframe. Otherwise, the new rows would start counting at 0 again. If you want to try it, just leave out the argument: HgtWgt_df.append(df2)

pandaCSV2.jpg

Now, we just use the df.to_csv() command to write our appended dataframe to a new CSV file

pandaCSV3

And there is our new file

pandaCSV4.jpg

Read Excel File

I’d love to be able to wow you with how complicated reading an Excel file is, but the difference between the Excel file reading and CSV is one word – excel.

pandaexcel.jpg

The only caveat is if your Excel file has multiple sheets. By default pd.read_excel() goes to sheet 1. If you want it to read sheet 4 instead, you would add: pd.read_excel(filename, sheetname= 4)

Write to Excel File

pandaexcel1.jpg

Bonus note

If you haven’t already, please check out my earlier CSV lesson: Python: Working with CSV Files

This will really give you an appreciation for how powerful and time saving pandas really is.

 

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

Follow this link for more Python content: Python

 

 

R: Loops – For, While, Repeat

On By Ben Larson Ph.D.In RLeave a comment

Loops are how we get computers to repeat tasks. In R, there are three standard loops: For, While, and Repeat

For

The For loop is a simple loop that iterates through a set of elements. With each iteration (running of the loop) the action found inside the loop is repeated.

In the loop I created below, 1:20 means 1 through 20. i in 1:20 mean count 1 through 20, assigning the current value of the count to i for the iteration of the loop.

rloop

Here are the results

rloop1.jpg

You can use a vector to iterate through. You can even use strings.

rloop2

Nested Loop

You can even nest your loops (running a loop inside another loop)

My main loop counts to 2. Each time it runs it prints its count and then it runs a second loop that steps through a vector containing 3 strings. It prints out each string in order, then returns to the top of the main loop and does it one more time.

rloop3.jpg

While loop

While loops work off of conditional logic. The general concept is while some condition is True, the loop will iterate. Once the condition is False, the loop terminates.

Below is state, while c is less than 10, iterate through the loop. Inside the loop I created a counter that adds 2 to the value of c each time. The end result is, I have created a listing of even numbers from 0 to 8

rloop4

Repeat Loop

Repeat loops repeat themselves (shocking!! I know!) until they are terminated using the break command.

Note the break command can be used in For and While loops if needed.

rloop5

The Code

# loop prints numbers 1 - 20
for (i in 1:20){
    print (i)
}

# loop prints elements in vector
for (i in c("Dog","Cat","Frog")){
     print(i)
}

#nested loop
for (n in 1:2){
   print(n)
   for (i in c("Dog","Cat","Frog")){
      print(i)
   } 
}

#while loop
c <- 0
while (c<10){
   print(c)
   c <- c+2
}

#repeat loop
a <-0
repeat{
   print(a)
   if (a==10){
     break}
   else {
    a<-a+2}
}

 

R: Create Functions in R

On By Ben Larson Ph.D.In RLeave a comment

Learning to use functions in R will improve your programming skills greatly. The way I think of functions is I think of them as mini programs you create inside your program.

Below I create function called Times2. The syntax for creating a function is:

FunctionName <- function(parameters) {
              Action (body of the function)    
              }

rFunc.jpg

You call the function simply by calling it’s name and giving it a parameter.

rFunc1.jpg

You can even pass the function a vector

rFunc2.jpg

Nest a Function

You can nest functions – call a function from inside another function

rFunc3

Assign Function Output to a Variable

Below, I took the print statements out of the functions and assigned Times2Sqr(2) to a variable y

rFunc4.jpg

You can work with this variable just like any other variable

rFunc5.jpg

Recursive Function

Recursive Functions are functions that call themselves.

I am using If and Else in this example. If they are foreign to you, don’t worry, I will cover them in a future lesson.

rFunc6.jpg

The Code

# create a function in R

Times2 <- function(x) {
   y <- x*2
   print(y)
   }
 
Times2(2)
Times2(8)

x <- c(1,3,6,3)
Times2(x)

#nested functions
Times2Sqr <- function (x) {
   y <- Times2(x)**2
   print(y)
   }
 
Times2Sqr(2)

# assign function value to variable
Times2 <- function(x) {
   y <- x*2
   }
Times2Sqr <- function (x) {
   y <- Times2(x)**2
   }
 
y <- Times2Sqr(2)

#recursive functions
Recur <- function(x) {
 if (x==0)
 return(1)
 else 
 return (x * Recur(x-1))
 
}

Recur(6)

SQL: Join Multiple Tables

On By Ben Larson Ph.D.In MS SQL ServerLeave a comment

If you know how to join 2 tables, I have good news for you. Joining more than 2 is a piece of cake.

Let’s take the example below. We have three tables. JobTitle holds a list of Job Titles. Table JobType holds a list of Job Titles (Supervisor, Technical, Hourly, etc). Finally table Employee holds employee names, addresses and emails.

These tables are related through primary keys(PK) and foreign keys(FK). It is through these PK and FK that we will connect these tables.

3join.jpg

If we want a list of Employees like this : Name, Address, Email, JobTitle and JobType, we would use the syntax below:

select Name, Address, Email, JobTitle, JobType
from Employee as e join JotTitle as j 
          on e.jobid = j.jobid join JobType as jt
          on j.jobTypeID = jt.jobTypeID

**remember MS SQL Server is not case sensitive by default

So as you can see, literally all we had to do is repeat the steps we did to join our first two tables.

If you would like to try an example in the AdventureWorks2012 database, try this:

 select loginid, name, StartDate
 from HumanResources.Employee as e 
 join HumanResources.EmployeeDepartmentHistory as h
 on e.BusinessEntityID = h.BusinessEntityID join HumanResources.Department as d
 on d.DepartmentID = h.DepartmentID

Python: Co-variance and Correlation

On By Ben Larson Ph.D.In Python2 Comments

In this lesson, I will using data from a CSV file. You can download the file here: heightWeight

If you do not know how to import CSV files into Python, check out my lesson on it first: Python: Working with CSV Files

The Data

The data set includes 20 heights (inches) and weights(pounds). Given what you already know, you could tell me the average height and average weight. You could tell me medians, variances and standard deviations.

correl

But all of those measurements are only concerned with a single variable. What if I want to see how height interacts with weight? Does weight increase as height increases?

**Note while there are plenty of fat short people and overly skinny tall people, when you look at the population at large, taller people will tend to weigh more than shorter people. This generalization of information is very common as it gives you a big picture view and is not easily skewed by outliers.

Populate Python with Data

The first thing we are going to focus on is co-variance. Let’s start by getting our data in Python.

correl1.jpg

Now there is a small problem. Our lists are filled with strings, not numbers. We can’t do calculations on strings.

We can fix this by populating converting the values using int(). Below I created 2 new lists (height and weight), created a for loop counting up to number of values in our lists : range(len(hgt)). Then I filled the new lists using lst.append(int(value))

correl2.jpg

**Now I know I could have resolved this in fewer steps, but this is a tutorial, so I want to provide more of a walk through.

Co-variance

Co-variance tells us how much two variables disperse from the mean together. There are multiple ways to find co-variance, but for me, using a dot product approach has always been the simplest.

For those unfamiliar with dot product. Imagine I had 2 lists (a,b) with 4 elements each. The dot product with calculated as so: a[0]*b[0]+a[1]*b[1]+a[2]*b[2]+a[3]*b[3]

Here is how it works:

If I take the individual variance of height[0] and weight[0] and they are both positive or negative – the product will be positive. – both variables are moving in the same direction

One positive and one negative will be negative. The variables are moving in different directions

One you add them all up, a positive number will mean that overall, you variables seem to have a positive co-variance (if a goes up, b goes up – if a goes down, b goes down)

If the final result is negative, you have negative co-variance (if a goes up, b goes down – if a goes down, b goes up)

If your final answer is 0 – your variables have no measurable interaction

Okay, let’s program this thing

** we will be using numpy’s mean() – mean and dot() – dot product methods and corrcoef() – correlation coefficient

First we need to find the individual variances from mean for each list

I create a function called ind_var that uses a list comprehension to subtract the mean from each element in the list.

correl3.jpg

Now, let’s change out the print statement for a return, because we are going to be using this function inside another function.

correl4.jpg

Co-variance function

Now let’s build the co-variance function. Here we are taking the dot product of the variances of each element of height and weight. We then divide the result by the N-1 (the number of elements – 1 : the minus 1 is due to the fact we are dealing with sample data not population)

correl6

So what were are doing:

  • Take the first height (68 inches) and subtract the mean (66.8) from it (1.2)
  • Take the first weight (165 lbs) and subtract the mean(165.8)  from it (-0.8)
  • We then multiply these values together (-0.96)
  • We repeat this for each element
  • Add the elements up.
  • Divide by 19 (20-1)
  • 144.75789 is our answer

Our result is 144.75789  – a positive co-variance. So when height goes up – weight goes up, when height goes down – weight goes down.

But what does 144 mean? Not much unfortunately. The co-variance doesn’t relate any information as to what units we are working with. 144 miles is a long way, 144 cm not so much.

Correlation

So we have another measurement known as correlation. A very basic correlation equation divides out the standard deviation of both height and weight. The result of a correlation is between 1 and -1. With -1 being perfect anti-correlation and 1 being perfect correlation. 0 mean no correlation exists.

With my equation get 1.028  – more than one. This equation is simplistic and prone to some error.

correl7.jpg

numpy’s corrcoef() is more accurate. It shows us a correlation matrix. Ignore the 1’s – they are part of what is known as the identity. Instead look at the other numbers = 0.97739.  That is about as close to one as you will ever get in reality. So even if my equation is off, it isn’t too far off.

correl9

Now just to humor me. Create another list to play with.

correl10

Let’s run this against height in my correlation function

correl11.jpg

Run these value through the more accurate corrcoef() . This will show my formula is still a bit off, but for the most part, it is not all that bad.

correl8

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

Follow this link for more Python content: Python

 

R: Data Types Tutorial

On By Ben Larson Ph.D.In UncategorizedLeave a comment

In my introduction to R, I covered Lists and Vectors briefly, but I will offer a quick review of them.

Lists

Lists in R are collections of data. These data can be of any type. You can have numbers, letters, and booleans all in a single list if you so choose.

The most important things to remember about lists is that they store any data type and you create them using the syntax: list(data,data,…,data)

dataTy

Vectors

Vectors are a collection of like data types

Here I created 2 vectors – one numeric and one character

syntax: c(data,data,…data1)

dataTy1.jpg

One thing to keep in mind with R is that unlike most programming languages – the indexes in R start at 1.

(1,3) — returns 1rst and 3rd element in vector

(1:3) — returns 1rst through 3rd element

dataTy2

Matrices

A matrix is a multiple dimension vector. All columns must be same length and all elements must be same type.

You shape you matrix by providing row and column size(nrow = and ncol =). You can also name the columns and rows using dimnames=

syntax: matrix (vector, nrow=,ncol=, dimnames=)

dataTy3

Arrays

Arrays allow you to build multi-dimensional arrays. In the example below, I build a 2 dimensional 3×4 array

dataTy4.jpg

Factor

A factor takes a vector and aggregates like items.

As you can see when I print the factor out, it shows all elements in the vector and provides a list of unique elements

nlevels() returns the number of unique elements.

dataTy5

summary() returns a list of the unique elements and a count of items in those elements.

dataTy6

Dataframe

Dataframes allow you to make a tabular table. Also, unlike matrices, the columns in a dataframe can contain different data types

dataTy7