About-R_ConnectToSqlServer

This post will demonstrate how to connect R to Microsoft SQL Server, so that data can be extracted directly from a database by using SQL-statements. The approach described in this post is supported by both SQL Server 2012 and the upcoming SQL Server “14”. You can connect to SQL Server using different techniques – one of them is by using ODBC. This post will use ODBC.

The first time you need to connect to a database, you need to perform some one-time tasks, which are:

• Create a ODBC DSN data source
• Install necessary R-packages from CRAN

The screenshot below shows a table containing the well-known data set ‘weather.nomnial’. The table is part of a database named ‘MiningDataSets’. The goal of this tutorial is to load all this data into R.

Create DSN
First we need to setup a user DSN data source pointing at our SQL Server using ODBC. The data source will be called from R using the package “RODBC”

1. Open “Administrative Tools” and “ODBC Data Sources (32 bit)”

1. On the tab “User DSN” press “Add”
2. Select “SQL Server” in the provider list
3. Now give the data source a name and a description. Remember this name – you will need it later on in the process. Last provide the name of the server where the SQL Server is installed. Press “Next”.

1. Select the way in which you will authenticate against the SQL Server. In this case we use Integrated Security. Press “Next”.

1. You now have the possibility to select the default database for the data source. Here we choose to point at the ‘MiningDataSets’. Press “Next” and the “Finish”.

1. On the last page remember to press the “Test Connection” button and ensure that the connection could be established.
2. The User DSN is now created and active.

Install and load RODBC
Support for SQL Server is not possible using native R – therefore we have to install the RODBC package from CRAN.

1. Open up R and in the console window type: install.packages(“RODBC”)
   
2. The RODBC packages is now downloaded and installed on your system. Next step is to load the package into R so the functions of the package can be used. In the console window type: library(“RODBC”)
   
3. The RODBC package is now loaded and ready

Connect with R
Now it is time to connect to the SQL Server database from R and retrieve the nominal weather dataset.

1. When calling the database you first have to open a connection to the database. And after you have performed you operations, you have to close the connection again. This is done by using the commands odbcConnect() and odbcClose(). The name specified as the parameter is the name of the ODBC user data source.
   
2. First we want to read an entire table into R – this can be done by using the sqlFetch command.
   
3. If you want to load the data into a data.frame, this can easily be done
   
4. If you want to execute a specific SQL-query, this can be done using the sqlQuery-command.
   

You can use many more SQL-statements against the database by using different RODBC-functions. You can get more help about the RODBC-package by typing RShowDoc(“RODBC”, package=”RODBC”)

I hope this little tutorial helped you in connecting R to SQL Server

SOURCE https://andersspur.wordpress.com/2013/11/26/connect-r-to-sql-server-2012-and-14/

About-R_DataTypeConversionToSqlServer

Data Type Conversion between R and SQL Server

The following table shows the changes in data types and values when data from SQL Server is used in an R script and then returned to SQL Server.

SQL Server type	R class	Type in RESULT SET	Comments
smalldatetime	POSIXct	datetime	Represented as GMT
smallmoney	numeric	float
datetime	POSIXct	datetime	Represented as GMT
money	numeric	float
uniqueidentifier	character	varchar(max)
numeric(p,s)	numeric	float
decimal(p,s)	numeric	float
date	POSIXct	datetime	Represented as GMT
tinyint	integer	int
bit	logical	bit
smallint	integer	int
int	integer	int
float	numeric	float
real	numeric	float
bigint	numeric	float
binary(n) n <= 8000	raw	varbinary(max)	Only allowed as input parameter and output
char(n) n <= 8000	character	varchar(max)
varbinary(n) n <= 8000	raw	varbinary(max)	Only allowed as input parameter and output
varchar(n) n <= 8000	character	varchar(max)
varbinary(max)	raw	varbinary(max)	Only allowed as input parameter and output

https://msdn.microsoft.com/en-us/library/mt590948.aspx

About-R_DataScienceR

R Data Science Tutorials

• This repo contains a curated list of R tutorials and packages for Data Science, NLP and Machine Learning. This also serves as a reference guide for several common data analysis tasks.
• Curated list of Python tutorials for Data Science, NLP and Machine Learning.
• Comprehensive topic-wise list of Machine Learning and Deep Learning tutorials, codes, articles and other resources.

The R Language

• Awesome-R Repository on GitHub
• R Reference Card: Cheatsheet
• R bloggers: blog aggregator
• R Resources on GitHub
• Awesome R resources
• Data Mining with R
• Rob J Hyndman's R Blog
• Simple R Tricks and Tools (Video)
• RStudio GitHub Repo
• Tidying Messy Data in R Video
• Baseball Research with R
• 600 websites about R
• Implementation of 17 classification algorithms in R
• Cohort Analysis and LifeCycle Grids mixed segmentation with R
• Using R and Tableau
• COMPREHENSIVE VIEW ON CRAN PACKAGES
• Using R for Statistical Tables and Plotting Distributions
• Extended Model Formulas in R: Multiple Parts and Multiple Responses
• R vs Python: head to head data analysis
• R for Data Science: Hadley Wickham's Book
• R Study Group at UPenn
• Program-Defined Functions in R

Important Questions

• In R, why is bracket better than subset?
• Subsetting Data in R
• Quickly reading very large tables as dataframes in R
• Using R to show data
• How can I view the source code for a function?
• How to make a great R reproducible example?
• R Grouping functions: sapply vs. lapply vs. apply. vs. tapply vs. by vs. aggregate
• Tricks to manage the available memory in an R session
• Difference between Assignment operators '=' and '<-' in R
• What is the difference between require() and library()?
• How can I view the source code for a function?
• How can I change fonts for graphs in R?

Common DataFrame Operations

• Create an empty data.frame
• Sort a dataframe by column(s)
• Merge/Join data frames (inner, outer, left, right)
• Drop data frame columns by name
• Remove rows with NAs in data.frame
• Quickly reading very large tables as dataframes in R
• Drop factor levels in a subsetted data frame
• Convert R list to data frame
• Convert data.frame columns from factors to characters
• Extracting specific columns from a data frame

Learning R

• Free resources for learning R
• Online Courses
  • Introduction to R for Data Science - Microsoft | edX
  • Introduction to R on DataCamp
  • tryR on Codeschool
• swirl: Learn R, in R
• Data Analysis and Visualization Using R
• MANY R PROGRAMMING TUTORIALS
• A Handbook of Statistical Analyses Using R, Find Other Chapters
• *Cookbook for R *
• Learning R in 7 simple steps

Caret Package in R

• Ensembling Models with caret
• Model Training and Tuning
• Caret Model List
• relationship-between-data-splitting-and-traincontrol
• Specify model generation parameters
• Tutorial, Paper
• Ensembling models with R, Ensembling Regression Models in R

R Cheatsheets

• Data Wrangling in R
• ggplot2 Cheatsheet
• Shiny Cheatsheet
• devtools Cheatsheet
• markdown Cheatsheet, reference
• Data Exploration Cheatsheet

Reference Slides

• Awesome R Reference Card
• Association Rule Mining
• Time Series Analysis
• Data Exploration and Visualisation
• Regression and Classification
• Text Mining on Twitter Data

Using R for Multivariate Analysis

• Little Book of R for Multivariate Analysis!
• THE FREQPARCOORD PACKAGE FOR MULTIVARIATE VISUALIZATION
• Use of freqparcoord for Regression Diagnostics

Time Series Analysis

• Time Series Forecasting (Online Book)
• A Little Book of Time Series Analysis in R
• Quick R: Time Series and Forecasting
• Components of Time Series Data
• Unobserved Component Models using R
• The Holt-Winters Forecasting Method
• CRAN Task View: Time Series Analysis

Bayesian Inference

• Packages for Bayesian Inference
• Bayesian Inference in R: Video
• R and Bayesian Statistics

Machine Learning using R

• Machine Learning with R
• Using R for Multivariate Analysis (Online Book)
• CRAN Task View: Machine Learning & Statistical Learning
• Machine Learning Using R (Online Book)
• Linear Regression and Regularization Code
• Cheatsheet
• Multinomial and Ordinal Logistic Regression in R

Neural Networks in R

• Visualizing Neural Nets in R
• nnet package
• Fitting a neural network in R; neuralnet package
• Neural Networks with R – A Simple Example
• NeuralNetTools 1.0.0 now on CRAN
• Introduction to Neural Networks in R
• Step by Step Neural Networks using R
• R for Deep Learning
• Neural Networks using package neuralnet, Paper

Sentiment Analysis

• Different Approaches
• Sentiment analysis with machine learning in R
• First shot: Sentiment Analysis in R
• qdap package, code
• sentimentr package
• tm.plugin.sentiment package
• Packages other than sentiment
• Sentiment Analysis and Opinion Mining
• tm_term_score
• vaderSentiment Paper, vaderSentiment code

Imputation in R

• Imputation in R
• Imputation with Random Forests
• How to Identify and Impute Multiple Missing Values using R
• MICE
• error in implementation of random forest in mice r package
• mice.impute.rf {mice}

NLP and Text Mining in R

• What algorithm I need to find n-grams?
• NLP R Tutorial
• Introduction to the tm Package Text Mining in R
• Adding stopwords in R tm
• Text Mining
• Word Stemming in R
• Classification of Documents using Text Mining Package “tm”
• Text mining tools techniques and applications
• Text Mining: Overview,Applications and Issues
• Text Mining pdf
• Text Mining Another pdf
• Good PPT
• Scraping Twitter and Web Data Using R

Visualisation in R

• ggplot2 tutorial
• SHINY EXAMPLES
• Comprehensive Guide to Data Visualization in R
• Interactive visualizations with R – a minireview
• Beginner's guide to R: Painless data visualization
• Data Visualization in R with ggvis
• Multiple Visualization Articles in R

Statistics with R

• Using R for Biomedical Statistics (Online Book)
• Elementary Statistics with R
• A Hands-on Introduction to Statistics with R
• Quick R: Basic Statistics
• Quick R: Descriptive Statistics
• Explore Statistics with R | edX

Useful R Packages

• TIDY DATA HADLEY PAPER
  • Package ‘tidyr’: tidyr is an evolution of reshape2. It's design specifically for data tidying (not general reshaping or aggregating) and works well with dplyr data pipelines.
• BROOM
• plyr, stringr, reshape2 tutorial Video, CODE
• dplyr
  • Code Files in this Repo
  • dplyr tutorial 1, dplyr tutorial 2
  • Do your "data janitor work" like a boss with dplyr
• ggplot2
  • ggplot2 tutorial
  • A quick introduction to ggplot()
  • R Graphics cookbook
  • ggplot2 Version of Figures in “Lattice: Multivariate Data Visualization with R”
• A speed test comparison of plyr, data.table, and dplyr
• data.table
  • Introduction to the data.table package in R
  • Fast summary statistics in R with data.table
• Other Packages
  • Package 'e1071'
  • Package ‘AppliedPredictiveModeling’
  • Package ‘stringr’: stringr is a set of simple wrappers that make R's string functions more consistent, simpler and easier to use.
  • Package ‘stringdist’: Implements an approximate string matching version of R's native 'match' function. Can calculate various string distances based on edits (damerau-levenshtein, hamming, levenshtein, optimal sting alignment), qgrams or heuristic metrics
  • Package ‘FSelector’: This package provides functions for selecting attributes from a given dataset
  • Ryacas – an R interface to the yacas computer algebra system
  • Scatterplot3d – an R package for Visualizing Multivariate Data
  • tm.plugin.webmining intro
  • Solving Differential Equations in R - ODE examples
  • Structural Equation Modeling With the sem Package in R
  • prettyScree - prettyGraphs

Market Basket Analysis in R

• Market Basket Analysis with R
• Step by Step explanation of Market Basket

SPECIAL THANKS TO M. KARN https://github.com/ujjwalkarn/DataScienceR

About-R_PrincipalComponentsRegression

Principal Components Regression, Pt.1: The Standard Method

http://www.win-vector.com/blog/2016/05/pcr_part1_xonly/

Principal Components Regression, Pt. 2: Y-Aware Methods

http://www.win-vector.com/blog/2016/05/pcr_part2_yaware/

Principal Components Regression, Pt. 3: Picking the Number of Components

http://www.win-vector.com/blog/2016/05/pcr_part3_pickk/

Principal Components Regression in R, an operational tutorial

http://blog.revolutionanalytics.com/2016/05/principal-components-tutorial.html

Principal Components Regression in R: Part 2

http://blog.revolutionanalytics.com/2016/05/principal-components-regression-part-2.html

Principal Components Regression in R: Part 3

http://blog.revolutionanalytics.com/2016/05/principal-components-regression-in-r-part-3.html