R : Working with data and some basic dataframe manipulations

Introduction

I wanted a long time to write a blogpost about the basic manipulations of data frames in R. Data frames in R are a fundamental concept and probably the most used feature to store table information.

This is the first in a series about the manipulations about dataframes. First I'll show the script and after that, an outputresult.

Create a data frame

First let's create a data frame for the manipulations.

rm(list=ls())

data.frame.x<-data.frame(names=c("Hennie","Robert","John","Jan"),
                         age=c(30,18,25,70),
                         Hobby=c("Tennis","Football","Tennis","motorcross"))

data.frame.y<-data.frame("Person_name"=c("Hennie","Robert","Kees","Henry"),
                         age=c(15,75,32,51),
                         "Pet"=c("Dog","Cat","Bird","Mouse"))
data.frame.x
data.frame.y

Below the result when the code is executed.

> data.frame.x<-data.frame(names=c("Hennie","Robert","John","Jan"),
+                          age=c(30,18,25,70),
+                          Hobby=c("Tennis","Football","Tennis","motorcross"))
> 
> data.frame.y<-data.frame("Person_name"=c("Hennie","Robert","Kees","Henry"),
+                          age=c(15,75,32,51),
+                          "Pet"=c("Dog","Cat","Bird","Mouse"))
> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> data.frame.y
  Person_name age   Pet
1      Hennie  15   Dog
2      Robert  75   Cat
3        Kees  32  Bird
4       Henry  51 Mouse

Select a column

There are multiple ways of selecting columns in R.

data.frame.x[,1]

data.frame.x$names       

Here is the output when the code is executed.

> data.frame.x[,1]
[1] Hennie Robert John   Jan   
Levels: Hennie Jan John Robert
> data.frame.x$names
[1] Hennie Robert John   Jan   
Levels: Hennie Jan John Robert  

Select multiple columns

It is also possible to select multiple columns of a dataframe.

data.frame.x[,1:3]
data.frame.x[,c(1:3)]
data.frame.x[,c(1,3)]    

And this results in the following output when executed.

 > data.frame.x[,1:3]
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> data.frame.x[,c(1:3)]
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> data.frame.x[,c(1,3)]
   names      Hobby
1 Hennie     Tennis
2 Robert   Football
3   John     Tennis
4    Jan motorcross

Select a row

The same we can do with rows is selecting a row.

data.frame.x[3,] 

Resulting in:

> data.frame.x[3,]
  names age  Hobby
3  John  25 Tennis

Selecting multiple rows

Selecting multiple rows can be done in a similar way as selecting multiple columns

data.frame.x[2:5,]
data.frame.x[c(2:5),]
data.frame.x[c(2:5),]     
 

Resulting in :

> data.frame.x[2:5,]
    names age      Hobby
2  Robert  18   Football
3    John  25     Tennis
4     Jan  70 motorcross
NA   <NA>  NA       <NA>
> data.frame.x[c(2:5),]
    names age      Hobby
2  Robert  18   Football
3    John  25     Tennis
4     Jan  70 motorcross
NA   <NA>  NA       <NA>
> data.frame.x[c(2:5),]
    names age      Hobby
2  Robert  18   Football
3    John  25     Tennis
4     Jan  70 motorcross
NA   <NA>  NA       <NA>

Notice the NA for the rows that are not found in the data frame

Selecting values in a data frame

It is also possible to select a cell value or to subset a data frame.

data.frame.x[1,2]
data.frame.x[c(2:3),c(1,3)]     

Resulting in:

> data.frame.x[1,2]
[1] 30
> data.frame.x[c(2:3),c(1,3)]
   names    Hobby
2 Robert Football
3   John   Tennis

Adding rows with rbind()

It is also possible to add rows to a data frame. Now there is a 'problem' with the factors in the dataframe. When R creates a factor, it only allows whatever is originally in the dataframe that was first loaded. Anything new that doesn’t fit into one of these categories is rejected as nonsense (becomes NA).

For instance "Jim" was not originally in the data frame and when the following code is executed an error occurs.

data.frame.x
data.frame.x <-rbind(data.frame.x, c("Jim",90,"Football"))  

Resulting in:

> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
5   <NA>  90   Football
> data.frame.x <-rbind(data.frame.x, c("Jim",90,"Football"))
Warning message:
In `[<-.factor`(`*tmp*`, ri, value = "Jim") :
  invalid factor level, NA generated

An error occurs because Jim was not one of the list (factors), Hennie, Robert, John, Jan and therefore an error happens. NA is inserted for Jim. When we execute the following code with factors that was already originally in the dataframe it will work.

data.frame.x <-rbind(data.frame.x, c("Hennie",90,"Football"))
data.frame.x     

No Na in the dataframe.

> data.frame.x <-rbind(data.frame.x, c("Hennie",90,"Football"))
> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
5 Hennie  90   Football

But how do we add new values to list of factors? We hae add manually the new categories to the Factors as a new level with the level() function.

levels(data.frame.x$names) <- c(levels(data.frame.x$names), 'Jim')
levels(data.frame.x$Hobby) <- c(levels(data.frame.x$Hobby), 'Bridge')
data.frame.x <-rbind(data.frame.x, c("Jim",90,"Bridge"))
data.frame.x

Here is the executed code.

> levels(data.frame.x$names) <- c(levels(data.frame.x$names), 'Jim')
> levels(data.frame.x$Hobby) <- c(levels(data.frame.x$Hobby), 'Bridge')
> data.frame.x <-rbind(data.frame.x, c("Jim",90,"Bridge"))
> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
5    Jim  90     Bridge    

Or convert the columns to characters, add the rows and turn them back to factors.

data.frame.x$names <- as.character(data.frame.x$names)
data.frame.x$Hobby <- as.character(data.frame.x$Hobby)
data.frame.x <-rbind(data.frame.x, c("Jim",90,"Bridge"))
str(data.frame.x)
data.frame.x
data.frame.x$names <- as.factor(data.frame.x$names)
data.frame.x$Hobby <- as.factor(data.frame.x$Hobby)
str(data.frame.x)   

The code executed..

> data.frame.x$names <- as.character(data.frame.x$names)
> data.frame.x$Hobby <- as.character(data.frame.x$Hobby)
> data.frame.x <-rbind(data.frame.x, c("Jim",90,"Bridge"))
> str(data.frame.x)
'data.frame': 10 obs. of  3 variables:
 $ names: chr  "Hennie" "Robert" "John" "Jan" ...
 $ age  : chr  "30" "18" "25" "70" ...
 $ Hobby: chr  "Tennis" "Football" "Tennis" "motorcross" ...
> data.frame.x
    names age      Hobby
1  Hennie  30     Tennis
2  Robert  18   Football
3    John  25     Tennis
4     Jan  70 motorcross
5     Jim  90     Bridge
6     Jim  90     Bridge
7     Jim  90     Bridge
8     Jim  90     Bridge
9     Jim  90     Bridge
10    Jim  90     Bridge
> data.frame.x$names <- as.factor(data.frame.x$names)
> data.frame.x$Hobby <- as.factor(data.frame.x$Hobby)
> str(data.frame.x)
'data.frame': 10 obs. of  3 variables:
 $ names: Factor w/ 5 levels "Hennie","Jan",..: 1 5 4 2 3 3 3 3 3 3
 $ age  : chr  "30" "18" "25" "70" ...
 $ Hobby: Factor w/ 4 levels "Bridge","Football",..: 4 2 4 3 1 1 1 1 1 1

Adding columns with cbind()

cbind() is a lot easier adding columns than adding rows to a dataframe, because the datatype of the column is only one.

data.frame.x
new.column<-c('190', '168','174', '123')
data.frame.x <-cbind(data.frame.x, new.column)
colnames(data.frame.x)[colnames(data.frame.x)=="new.column"] <- "height"
data.frame.x  

And this is the executed code

> data.frame.x
   names age      Hobby height
1 Hennie  30     Tennis    190
2 Robert  18   Football    168
3   John  25     Tennis    174
4    Jan  70 motorcross    123
> new.column<-c('190', '168','174', '123')
> data.frame.x <-cbind(data.frame.x, new.column)
> colnames(data.frame.x)[colnames(data.frame.x)=="new.column"] <- "height"
> data.frame.x
   names age      Hobby height height
1 Hennie  30     Tennis    190    190
2 Robert  18   Football    168    168
3   John  25     Tennis    174    174
4    Jan  70 motorcross    123    123

Option number 2 is adding an extra column in a following manner.

data.frame.x
data.frame.x$new.column<-c('190', '168','174', '123')
colnames(data.frame.x)[colnames(data.frame.x)=="new.column"] <- "height"
data.frame.x      

Resulting in

> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> data.frame.x$new.column<-c('190', '168','174', '123')
> colnames(data.frame.x)[colnames(data.frame.x)=="new.column"] <- "height"
> data.frame.x
   names age      Hobby height
1 Hennie  30     Tennis    190
2 Robert  18   Football    168
3   John  25     Tennis    174
4    Jan  70 motorcross    123 

Show first n rows with head()

The head() function is like the TOP keyword in SQL and gives the first n rows of a data frame.

data.frame.x
head(data.frame.x,2)      

And this is the executed code

> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> head(data.frame.x,2)
   names age    Hobby
1 Hennie  30   Tennis
2 Robert  18 Football

Show bottom n rows with tail()

The tail function is the same as the head() function but then it returns the bottom n rows of the dataframe.

data.frame.x
tail(data.frame.x,2)

And the results are..

> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> tail(data.frame.x,2)
  names age      Hobby
3  John  25     Tennis
4   Jan  70 motorcross

Getting some columninformation with class()

If information is need about a patriular column the class() function can be used

class(data.frame.x$age)

Resulting in.

> class(data.frame.x$age)
[1] "numeric"      
 

names()

names() is the name of the function for getting or setting the names of an object.

data.frame.x
names(data.frame.x) 

And this is the executed code.

> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> names(data.frame.x)
[1] "names" "age"   "Hobby"  
 

dim()

knowing the dimensions of a dataframe can be handy sometimes and this can be executed with the dim function

data.frame.x
dim(data.frame.x)      

The executed code...

> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> dim(data.frame.x)
[1] 4 3

Showing some statistial information about an object with Summary()

In case more information is need about the values in an object a function summary() is available.

summary(data.frame.x)   

>summary(data.frame.x)
    names        age               Hobby  
 Hennie:1   Min.   :18.00   Football  :1  
 Jan   :1   1st Qu.:23.25   motorcross:1  
 John  :1   Median :27.50   Tennis    :2  
 Robert:1   Mean   :35.75                 
            3rd Qu.:40.00                 
            Max.   :70.00 

Occurence of every name is 1, the average of age is 35.75 and tennis is counted twice.

Showing the structure of an object (data frame) with str()

If more information is needed about an object and in this particular case a data frame you can use the str() function in R.

str(data.frame.x) 

Resulting in the following output.

> str(data.frame.x)
'data.frame': 4 obs. of  3 variables:
 $ names: Factor w/ 4 levels "Hennie","Jan",..: 1 4 3 2
 $ age  : num  30 18 25 70
 $ Hobby: Factor w/ 3 levels "Football","motorcross",..: 3 1 3 2
 

Factors is a type that is like categorization of the values and used in statistical functions. Age is numerical column.

Count the rows with nrow()

With nrow() it is possible to count the rows in a data frame

nrow(data.frame.x)     

Resulting in

> nrow(data.frame.x)    # number of data rows
[1] 4

Count the columns with ncol()

Offcourse it is also possible to count the number of columns. this is done with the ncol() function

ncol(data.frame.x)     

Resulting in

> ncol(data.frame.x)
[1] 3

attributes()

The attributes function acesses the attributes of an object and in this example it is the dataframe.

data.frame.x
attributes(data.frame.x)    

And this is the result of the executed code.

> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> attributes(data.frame.x)
$names
[1] "names" "age"   "Hobby"

$row.names
[1] 1 2 3 4

$class
[1] "data.frame"   
 

merge()

Now it is also possible to merge two data frames with the merge function. Now the first merge function in the code below tries to find similar columns and that could be age but there are now common ages and therefore the merge fails. the second one has the columnnames specified  and the all=TRUE is a full outer join between the two dataframes.

data.frame.x
data.frame.y
merge(data.frame.x, data.frame.y)
merge(data.frame.x, data.frame.y, by.x="names", by.y="Person_name",all=TRUE)     

And this is the executed code

> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> data.frame.y
  Person_name age   Pet
1      Hennie  15   Dog
2      Robert  75   Cat
3        Kees  32  Bird
4       Henry  51 Mouse
> merge(data.frame.x, data.frame.y)
[1] age         names       Hobby       Person_name Pet        
<0 rows> (or 0-length row.names)
> 
> merge(data.frame.x, data.frame.y, by.x="names", by.y="Person_name",all=TRUE)
   names age.x      Hobby age.y   Pet
1 Hennie    30     Tennis    15   Dog
2    Jan    70 motorcross    NA  <NA>
3   John    25     Tennis    NA  <NA>
4 Robert    18   Football    75   Cat
5  Henry    NA       <NA>    51 Mouse
6   Kees    NA       <NA>    32  Bird 
 

subset()

The subset( ) function is an easy way to select variables and observations. In the following example, we select all rows that have a value of age greater than or equal to 20 or age less then 10 and the columns names and hobbty is returned

data.frame.x
subset.frame.x <- subset(data.frame.x, age >= 20 | age < 10, 
                  select=c(names, Hobby))
subset.frame.x

And this is the result...

> data.frame.x
   names age      Hobby
1 Hennie  30     Tennis
2 Robert  18   Football
3   John  25     Tennis
4    Jan  70 motorcross
> subset.frame.x <- subset(data.frame.x, age >= 20 | age < 10, 
+                   select=c(names, Hobby))
> subset.frame.x
   names      Hobby
1 Hennie     Tennis
3   John     Tennis
4    Jan motorcross  
 

Conclusion

In this blogpost I've discovered the different data frame manipulations in R. 

Greetz,
Hennie

First impression of SQL Server R Services (part I)

Introduction

I wanted to know more about R Services and I decided to walkhrough the tutorial provided by Microsoft. There are a couple of lessons you can follow to understand R Services. So this will the first blogpost about R Services and many will follow during my discovery of R services. R services is a powerful addition to SQL Server and one of the advantages is that you don't need to load your data to the client anymore. You can puss your code to the R Services of SQL Server and have advantages of a powerful server.

So this blogpost is about the first step (Lesson 1: Prepare the Data (Data science End-to-End walkthrough) and that is getting everything ready for analysis: installing packages, getting the data, loading the data in SQL Server, installing some of the code in SQL Server.

The data is a representative sampling of the New York City taxi data set, which contains records of over 173 million individual trips in 2013, including the fares and tip amounts paid for each trip.
For more information about how this data was originally collected, and how you can get the full data set, see http://chriswhong.com/open-data/foil_nyc_taxi/.

To make the data easier to work with, the Microsoft data science team performed downsampling to get just 1% of the data.

Walkthrough the steps

First I opened R Studio and created a new script and copied the code for installing the packages.

if (!('ggmap' %in% rownames(installed.packages()))){  
  install.packages('ggmap')  
}  
if (!('mapproj' %in% rownames(installed.packages()))){  
  install.packages('mapproj')  
}  
if (!('ROCR' %in% rownames(installed.packages()))){  
  install.packages('ROCR')  
}  
if (!('RODBC' %in% rownames(installed.packages()))){  
  install.packages('RODBC')  
}  
 
 

And I executed this code for installing the packages on the server.

       
# Install required packages on the server
install.packages("ggmap", lib=grep("Program Files", .libPaths(), value=TRUE)[1])  
install.packages("mapproj", lib=grep("Program Files", .libPaths(), value=TRUE)[1])       
 

The next step was downloading the data from Github. The code I copied from the Micoroft site gave some errors because there were some missing slashes.

       
$source = 'https://raw.githubusercontent.com/Azure/Azure-MachineLearning-DataScience/master/Misc/RSQL/Download_Scripts_R_Walkthrough.ps1'
$ps1_dest = "$pwd\Download_Scripts_R_Walkthrough.ps1"
$wc = New-Object System.Net.WebClient
$wc.DownloadFile($source, $ps1_dest)
.\Download_Scripts_R_Walkthrough.ps1 –DestDir 'C:\tempR'      
 

And this a printscreen of the entry and the execution.

Once the script is finished the files are in the TempR folder:

Next step was to install the scripts in SQL Server. This is done with Run_R_Walkthrough.ps1 Powershell script.

      
.\RunSQL_R_Walkthrough.ps1

Input the database server name (the full address): WIN-11283AFOULP
Input the name of the database you want to create: nyctaxi
Input the user name which has the previlige to create the database:
Input the password of user name which has the previlige to create the databas
Input the path to the csv file you want to upload to the database: c:\tempR       
 

Here a printscreen of this process.

The script is executed and results are shown in the powershell window. I've grabbed the dump and placed it here.

Start creating database and table on your SQL Server, and uploading data to the table. It may take a while...
---------------------------------------------
Executed SQL packet:
DECLARE @db_name varchar(255), @tb_name varchar(255)
DECLARE @create_db_template varchar(max), @create_tb_template varchar(max), @create_tb_template2 varchar(max)
DECLARE @sql_script varchar(max)
set @db_name = 'nyctaxi'
SET @tb_name = 'nyctaxi_sample'
SET @create_db_template = 'create database {db_name}'
SET @create_tb_template = '
use {db_name}
CREATE TABLE {tb_name}
(
       medallion varchar(50) not null,
       hack_license varchar(50)  not null,
       vendor_id char(3),
       rate_code char(3),
       store_and_fwd_flag char(3),
       pickup_datetime datetime  not null,
       dropoff_datetime datetime,
       passenger_count int,
       trip_time_in_secs bigint,
       trip_distance float,
       pickup_longitude varchar(30),
       pickup_latitude varchar(30),
       dropoff_longitude varchar(30),
       dropoff_latitude varchar(30),
       payment_type char(3),
       fare_amount float,
       surcharge float,
       mta_tax float,
       tolls_amount float,
       total_amount float,
       tip_amount float,
       tipped int,
       tip_class int
)
CREATE CLUSTERED COLUMNSTORE INDEX [nyc_cci] ON {tb_name} WITH (DROP_EXISTING = OFF)
'

SET @create_tb_template2 = '
use {db_name}
CREATE TABLE nyc_taxi_models
(
        model varbinary(max) not null
)
'

-- Create database
SET @sql_script = REPLACE(@create_db_template, '{db_name}', @db_name)
EXECUTE(@sql_script)

-- Create table
SET @sql_script = REPLACE(@create_tb_template, '{db_name}', @db_name)
SET @sql_script = REPLACE(@sql_script, '{tb_name}', @tb_name)
EXECUTE(@sql_script)

-- Create the table to persist the trained model
SET @sql_script = REPLACE(@create_tb_template2, '{db_name}', @db_name)
EXECUTE(@sql_script)

Execution succesful
-----------------------------------------
C:\tempR\create-db-tb-upload-data.sql execution done
start loading the data to SQL Server table...
Unable to load BCP resource DLL.  BCP cannot continue.
This step (creating database and tables, and uploading data to table) takes 27.19 seconds.
Start running the .sql files to register all stored procedures used in this walkthrough...
---------------------------------------------
Executed SQL packet:
USE [nyctaxi]

Execution succesful
---------------------------------------------
Executed SQL packet:
/****** Object:  StoredProcedure [dbo].[PersistModel]    Script Date: 11/05/2015 23:04:39 ******/
SET ANSI_NULLS ON

Execution succesful
---------------------------------------------
Executed SQL packet:
SET QUOTED_IDENTIFIER ON

Execution succesful
---------------------------------------------
Executed SQL packet:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PersistModel')
  DROP PROCEDURE PersistModel

Execution succesful
---------------------------------------------
Executed SQL packet:
-- =============================================
-- Author:              <Author,,Name>
-- Create date: <Create Date,,>
-- Description: Store model in database. Input is a binary representation of R Model object passed as Hex string
-- =============================================
CREATE PROCEDURE [dbo].[PersistModel]
@m nvarchar(max)
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        insert into nyc_taxi_models (model) values (convert(varbinary(max),@m,2))
END

Execution succesful
-----------------------------------------
C:\tempR\PersistModel.sql execution done
---------------------------------------------
Executed SQL packet:
USE [nyctaxi]

Execution succesful
---------------------------------------------
Executed SQL packet:

/****** Object:  StoredProcedure [dbo].[PredictTip]    Script Date: 10/29/2015 4:36:55 PM ******/
SET ANSI_NULLS ON

Execution succesful
---------------------------------------------
Executed SQL packet:

SET QUOTED_IDENTIFIER ON

Execution succesful
---------------------------------------------
Executed SQL packet:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PredictTipBatchMode')
  DROP PROCEDURE PredictTipBatchMode

Execution succesful
---------------------------------------------
Executed SQL packet:

CREATE PROCEDURE [dbo].[PredictTipBatchMode] @inquery nvarchar(max)
AS
BEGIN

  DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1
    model
  FROM nyc_taxi_models);
  EXEC sp_execute_external_script @language = N'R',
                                  @script = N'
mod <- unserialize(as.raw(model));
print(summary(mod))
OutputDataSet<-rxPredict(modelObject = mod, data = InputDataSet, outData = NULL,
          predVarNames = "Score", type = "response", writeModelVars = FALSE, overwrite = TRUE);
str(OutputDataSet)
print(OutputDataSet)
',
                                  @input_data_1 = @inquery,
                                  @params = N'@model varbinary(max)',
                                  @model = @lmodel2
  WITH RESULT SETS ((Score float));

END


Execution succesful
-----------------------------------------
C:\tempR\PredictTipBatchMode.sql execution done
---------------------------------------------
Executed SQL packet:
USE [nyctaxi]

Execution succesful
---------------------------------------------
Executed SQL packet:

/****** Object:  StoredProcedure [dbo].[PredictTipSingleMode]    Script Date: 10/31/2015 5:44:43 PM ******/
SET ANSI_NULLS ON

Execution succesful
---------------------------------------------
Executed SQL packet:

SET QUOTED_IDENTIFIER ON

Execution succesful
---------------------------------------------
Executed SQL packet:

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PredictTipSingleMode')
  DROP PROCEDURE PredictTipSingleMode

Execution succesful
---------------------------------------------
Executed SQL packet:


CREATE PROCEDURE [dbo].[PredictTipSingleMode] @passenger_count int = 0,
@trip_distance float = 0,
@trip_time_in_secs int = 0,
@pickup_latitude float = 0,
@pickup_longitude float = 0,
@dropoff_latitude float = 0,
@dropoff_longitude float = 0
AS
BEGIN

  DECLARE @inquery nvarchar(max) = N'
  SELECT * FROM [dbo].[fnEngineerFeatures](
  @passenger_count,
@trip_distance,
@trip_time_in_secs,
@pickup_latitude,
@pickup_longitude,
@dropoff_latitude,
@dropoff_longitude)
        '
  DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1
    model
  FROM nyc_taxi_models);
  EXEC sp_execute_external_script @language = N'R',
                                  @script = N'

mod <- unserialize(as.raw(model));
print(summary(mod))
OutputDataSet<-rxPredict(modelObject = mod, data = InputDataSet, outData = NULL,
          predVarNames = "Score", type = "response", writeModelVars = FALSE, overwrite = TRUE);
str(OutputDataSet)
print(OutputDataSet)
',
                                  @input_data_1 = @inquery,
                                  @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,
@trip_time_in_secs int ,
@pickup_latitude float ,
@pickup_longitude float ,
@dropoff_latitude float ,
@dropoff_longitude float',
                                  @model = @lmodel2,
                                                                    @passenger_count =@passenger_count ,
                                                                        @trip_distance=@trip_distance,
                                                                        @trip_time_in_secs=@trip_time_in_secs,
                                                                        @pickup_latitude=@pickup_latitude,
                                                                        @pickup_longitude=@pickup_longitude,
                                                                        @dropoff_latitude=@dropoff_latitude,
                                                                        @dropoff_longitude=@dropoff_longitude
  WITH RESULT SETS ((Score float));

END



Execution succesful
-----------------------------------------
C:\tempR\PredictTipSingleMode.sql execution done
---------------------------------------------
Executed SQL packet:
USE [nyctaxi]

Execution succesful
---------------------------------------------
Executed SQL packet:

/****** Object:  UserDefinedFunction [dbo].[fnCalculateDistance]    Script Date: 10/29/2015 4:33:52 PM ******/
SET ANSI_NULLS ON

Execution succesful
---------------------------------------------
Executed SQL packet:

SET QUOTED_IDENTIFIER ON

Execution succesful
---------------------------------------------
Executed SQL packet:

IF EXISTS (SELECT * FROM sys.objects WHERE type IN ('FN', 'IF') AND name = 'fnCalculateDistance')
  DROP FUNCTION fnCalculateDistance

Execution succesful
---------------------------------------------
Executed SQL packet:

CREATE FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
-- User-defined function calculate the direct distance between two geographical coordinates.
RETURNS float
AS
BEGIN
  DECLARE @distance decimal(28, 10)
  -- Convert to radians
  SET @Lat1 = @Lat1 / 57.2958
  SET @Long1 = @Long1 / 57.2958
  SET @Lat2 = @Lat2 / 57.2958
  SET @Long2 = @Long2 / 57.2958
  -- Calculate distance
  SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
  --Convert to miles
  IF @distance <> 0
  BEGIN
    SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
  END
  RETURN @distance
END

Execution succesful
-----------------------------------------
C:\tempR\fnCalculateDistance.sql execution done
---------------------------------------------
Executed SQL packet:
USE [nyctaxi]

Execution succesful
---------------------------------------------
Executed SQL packet:

/****** Object:  UserDefinedFunction [dbo].[fnEngineerFeatures]    Script Date: 10/29/2015 4:36:07 PM ******/
SET ANSI_NULLS ON

Execution succesful
---------------------------------------------
Executed SQL packet:

SET QUOTED_IDENTIFIER ON

Execution succesful
---------------------------------------------
Executed SQL packet:

IF EXISTS (SELECT * FROM sys.objects WHERE type IN ('FN', 'IF') AND name = 'fnEngineerFeatures')
  DROP FUNCTION fnEngineerFeatures

Execution succesful
---------------------------------------------
Executed SQL packet:

CREATE FUNCTION [dbo].[fnEngineerFeatures] (
@passenger_count int = 0,
@trip_distance float = 0,
@trip_time_in_secs int = 0,
@pickup_latitude float = 0,
@pickup_longitude float = 0,
@dropoff_latitude float = 0,
@dropoff_longitude float = 0)
RETURNS TABLE
AS
  RETURN
  (
  -- Add the SELECT statement with parameter references here
  SELECT
    @passenger_count AS passenger_count,
    @trip_distance AS trip_distance,
    @trip_time_in_secs AS trip_time_in_secs,
    [dbo].[fnCalculateDistance](@pickup_latitude, @pickup_longitude, @dropoff_latitude, @dropoff_longitude) AS direct_di
stance

  )

Execution succesful
-----------------------------------------
C:\tempR\fnEngineerFeatures.sql execution done
Completed registering all stored procedures used in this walkthrough.
This step (registering all stored procedures) takes 22.5 seconds.
Plug in the database server name, database name, user name and password into the R script file
This step (plugging in database information) takes 0.2 seconds.    
 

Two tables are created, nyc_taxi_models and nyctaxi_sample

Now the table needs to be filled with the data. There seems to be one datafile available but there are two tables created. I don't know why.

The data is loaded and inserted in the table nyctaxi_sample and contains 1.703.957 rows.

The right number in table.

Conclusion

The first blogpost about R Services and it is to early to say something about R Services. In the next blogpost I'll elaborate more about R Services and the functions needed.

Greet,

Hennie

Microsoft Azure ML

Introduction

Now Machine Learning (ML) is becoming more and more important, it's a good idea to get a grasp of the capabilities of Machine Learning (ML). In this blogpost I'll investigate machine learning in more detail. What is Machine learning?, what is the process of creating a ML model? and what are common algorithms (in Microsoft Azure ML)?

Datamining techniques was already available in SSAS, but hardly used by customers or BI consultants. Microsoft released a while ago Azure Machine Learning and this tool provides a way of applying historical data to a problem by creating a model and using it to successfully predict future behaviors or trends.

For this blogpost I'll use Microsoft Azure ML studio as an example. Below a screenshot of a try out of the Microsoft Azure ML.On the right, the data flow, clean up and a projection and on the left the Linear Regression algorithm that is applied to the data, such that the model is trained.

Machine learning process

Everybody knows the quiz where someone  needs to guess an answer about what another person is telling about the characteristics about the object, like the game, called Pictionary. E.g: Me: “Its round, green, and edible” You: “It’s an apple!”. So, based from what you know (train the model) you can guess that the answer should be an apple (score and test the model).

And, you have to continue to learn the model by adding new data, like green apples, red apples and yellow apples to improve the model. So, there are a couple of steps:

1. Get the data.
2. Preprocess the data.
3. Define features.
4. Choose and apply an algorithm.
5. Predict new incoming data.

1. Get the data

First thing to do is to get the data from a source. There are multiple options for loading the data in ML Studio. 

File Formats

The following file formats are supported in ML:

• CSV file.
• TSV file.
• Plain text.
• Svmlight file (Support Vector Machine).
• Attribute relation file format.
• Zip file.
• RObject or Workspace.

Reader options

There are also some other input options available, like below:

• Web Url via HTTP.
• Hive Query.
• Azure SQL Database.
• Azure Table.
• Azure Blob storage.
• Data Feed Provider.

2. Preprocess the data

A dataset usually requires some preprocessing before it can be analyzed. You may notice some missing values in columns on different rows and these missing values needs cleaning in order to let the model analyze the data properly

In Microsoft Azure ML there are all kinds of manipulations (transformations?) possible:

• Filtering.
• Manipulation like adding columns, adding rows, Clean missing data, group categorical values or project columns
• Create samples and splitting the data (for a training set and a test set).
• etc

3. Define features

In machine learning, we are not talking about dimensions (or attributes) but about features. These are individual measurable properties of something you’re interested in. Each column in the dataset is a feature and finding a proper set of features is a tedious and important task for creating a predictive model. For instance some columns can have a strong correlation and therefore it will not add much new information to the model.

We'll select the features (columns) with the Project Columns module. For training the model it's needed that the dependent variable, the variable that we are going to predict, is in the data set.

4. Choose and apply an algorithm

Constructing a predictive model consists selecting an algorithm and train and test this algorithm in order to get the best result. These are algorithms that are currently available in Microsoft Azure ML:

• Anomaly Detection
• One-Class Support Vector Machine
• PCA-Based anomaly Detection
• Classification
• Multiclass Decision Forest
• Multiclass Decision Jungle
• Multiclass Logistic Regression
• Multiclass Neural Network
• One-vs-All Multiclass
• Two-Class Averaged Perceptron
• Two-Class Bayes Point MAchine
• Two-Class Boosted Decision Tree
• Two-Class Decision Forest
• Two-Class Decision Jungle
• Two-Class Locally-Deep Support Vector Machine
• Two-class Logistic Regression
• Two-Class Neural Network
• Two-Class Support Vector Machine
• Clustering
•  K-Means Clustering
• Regression
• Bayesian Linear Regression
• Boosted Decision Tree Regression
• Decision Forest Regression
• Fast Forest Quantile Regression
• Lineair Regression
• Neural Network Regression
• Ordinal Regression
• Poisson Regression

So, most of the algorithms focusses on classification and regression. Both algorithms are so called supervised learning algorithms. Classification algorithms are used for predicting responses that can have just a few known values (such as married, single, or divorced) based on the other columns in the dataset. Regression algorithms are used for prediciting values based on contineous variables like age.

An example of an algorithm:

5. Predict new incoming data.

Now that we have trained the model, we have to score the model. In the split we have a training set created by spliting 75% of the data and in order to score the model, we have to compare this with the 25% test set to see how well the model functions.

In this example I've dragged a score model component in the diagram.

Below an example of the scoring experiment

Here you can see the  price and the predicted price based on the features of the data set.

When you compare the price with the scored labels with this diagram (below), you'll see that the low end is quite accurate but not upper right (because of the lack of sufficient data?).

Finally, to test the quality of the results, select and drag the Evaluate Model module to the experiment canvas, and connect the left input port to the output of the Score Model module. With this component it's possible to test two different algorithms for the best fit.

Conclusion

I've covered a small part of Azure Machine learning to get an impression of the possibilities. The possibilities of the free version are great and very useful for machine learning.

Greetz,

Hennie

R : An introduction (Part I)

Introduction

Today a blogpost about R. R is a Statisitcal Programming Language and can be downloaded from the CRAN site. From Wikipedia : R is a free software programming language for statistical computing and graphics and it seems that R is widely used among statisticians and Data miners. R is an implementation of the S programming language. I was curieus about the name R and it seems that it's named after first names of the first authors of R.

Installation of R and RStudio

In this blogpost I'll write about the installation of R studio and some small examples. This is a very popular IDE environment and provides a powerful and productive user interface for R. It's free and open source , available for Windows, Max and Linux. But before you can use R studio you need install R. If you don't, you'll recieve the following message:

For this blogpost I've installed the following software:

• R (version 3.0.2). This is the console application and has some basic functionality and it seems to be differ between the operating systems.
• R Studio (version 0.98). R Studio is cross platform application that can be used for analysis. 

R studio

When you have started R studio the following window appears.

Here is a screenshot of some expressions I've borrowed from Wikipedia.

There are some pre installed datasets in R and one of them is "cars". This is a set of data containing the braking distances that were recorded for cars travelling at different speeds. I've tried some graphics.

Conclusion

R Studio is a great tool for analyzing datasets with R. There is a lot of information available on the internet that will help you learn R. Here is a good resource : Wikibooks.

Greetz,
Hennie