R & SQL: Simple Data Science with R and SQL


Hello World!

The topic of today's article is databases.

As Data Scientists and Statisticians work with data everyday, they wont actually use that 50 lines text file data-sets provided by teachers in the statistical analysis courses in a real-world applications.

Statisticians work with massive amounts of data, whether this data is stored in flat-files or in databases, the size of the analyzed data will definitely be more then a couple of hundred records.
Thus the need for a way to extract data from large tables stored in databases in a simple, intuitive way.


Whats is SQL ?


SQL means Structured Query Language. For me, it has always been the "Simple Query Language".

I've always used the term "Simple" to describe the simplicity of learning and using basic sql functions.



SQL is the standard language for relational database management systems (Here I am referring to the ANSI definition ).

So it is used to work with databases, performing various operations like creating tables, extracting data, updating tables, deleting and dropping entire databases...etc

You can fined more about the basics of this awesome language in the links bellow.

what are the different R & sql packages? 


As most of you know, R is a statistical analysis software. It has an amazing community, a wide range of packages (now 5000+ packages available ) and a strong documentation and it is used in almost all domains.

The old way of working with data stored in databases was to separate the extraction of the data and the analysis in two operations that are made in two or more software and that require time to first extract this data, aggregate it and save it in flat files and then open it with an analysis software and work on it.

With R, you can do all these operations in one interface, with the help of packages that will provide a way to connect to databases, use the SQL language to extract your data ( and also to add, delete and updates your tables) from your Rstudio interface ( I use Rstudio for it's simplicity and the various cool things that I can do,in just one interface).

The package:


For this task, we will use the ODBC Connectivity package "RODBC" . 

This package is great for managing connections not only with SQL server databases but also with Mysql, PostgreSQL,Oracle and SQLite.   So it covers almost all the basic database sources that we need to use.

Simple example :


The RODBC package offers multiple possibilities to query the database and do many operations.


Making a connection with the database. The logic of the package is that the user need to make a connection channel from the client, in our case the R package, to the database management system.
And this connection need to be closed every time the work with the database is over.

the code bellow will give you a basic look on how to do the connection and the simple manipulation of a database in R.

I will try to enrich this post with more examples in the near future.








Popular posts from this blog

Mathematical Symbols in LATEX

Image
I have been going through my hard drive and organizing some of the files I have, PDF's, eBooks and articles and I found a very interesting documents about LATEX math symbols. Please deactivate ad-blockers to be able to see the pdf document ! UPDATE : I have published this article a few years and it has attracted the most attention of all my blogposts, so I'd like to thank all of the visitors that shared this post. Please take some time to check out other articles I have shared about Data Science, Machine learning and Big Data: Data Mining basic and advanced concepts - Part 2 : Describing Data Here are some of the most interesting parts of it:
Read More >>

Analyzing Twitter data with R (part 3: Cleaning & organizing the Data)

Image
After we have explained in the previous parts, how to set up the access to Twitter's API and how to import tweets with a simple R command, in this third part we will try to organize and clean the data we have imported in good reusable format. So let's pick up from where we left off , after finishing the authentication process, now we want to import certain tweets. the command  searchTwitter  from the package twitteR will do the job for us.  we need to create a new object that will contain the data we are about to import and give it the result of our  searchTwitter function. In our example, we will be querying Twitter for the tweets containing the hashtag #Tunisia , we will import 1000 English tweets. and we will have a preview of the imported data with the head()  function:
Read More >>

Linkin Park: Analyzing the causes of death of Rock band members

Image
Being a part of the Y generation, I grew up listening to various types and genres of music, from Rap to RnB, to Rock and electronic music. I was personally choked to hear about the death of the lead singer of the Linkin Park band. Their music was and still is popular until today. The news was as choking to me as to anyone else who knew that band. After knowing about the news, I had a discussion about this tragedy , and a question popped up about the fact that rock band members mostly die of suicide or drug over-dose. I wasn't really sure that this information is true, so I really couldn't have a real argument to say that it is maybe (or not ) the most commun reason of death for Rock stars. And since I love playing with data, I decided to fact-check the hypothesis stating that:  "The most commun cause of death among rock band members is suicide" . To start, we have to get  some data! and since there is no available data-set to analyse , I had to be creativ...
Read More >>