Practical Business Intelligence: Optimize Business Intelligence for Efficient Data Analysis

In order for us to begin scraping websites with R, we must first get our environment set up with R, as well as RStudio. R and RStudio are available to download on many different platforms whether it be Windows, Mac, or Linux. As with everything else in this book, the environment will be set up in a Microsoft Windows 10 64-bit environment.

Downloading and installing R

R is quite easy to download and install. Any online search for CRAN (Comprehensive R Archive Network) will lead you to the latest version of R to download from the location that is closest to you. The search for R led me to the following website:  https://cran.r-project.org/bin/windows/base/.

At the time of writing this book, the latest version of R is version 3.3.2 for Windows 32/64-bit, as can be seen in the following screenshot:

Once the file has been downloaded, the next step is to begin the installation of the executable file as shown in the following screenshot:

Select the default destination where...

When we first open up RStudio, the first thing that we see is four main quadrants. These four quadrants represent our coding input as well as coding output, as shown in the following screenshot:

Let's now set up a new file, which we will use to get our web scraper up and running, by going to File | New File | R Script, as seen in the following screenshot:

We now have a blank R file to begin our coding. The next step is identifying the website data that will be the source of our web scraping exercise.

The following link (https://github.com/asherif844/PracticalBusinessIntelligence/wiki/AdventureWorks---Weekly-Data-by-Discount) within GitHub has a useful table that we can scrape from to incorporate into our database.

The full data in the table can also be seen in the following screenshot:

In order to scrape this Wikipedia table from R, we will need to install a couple of libraries within our R framework...

Chances are that you already have some version of Python installed on your machine, especially if you have a Mac. For the purposes of this exercise, we will be downloading Python 3 onto our Windows machine. At the time of writing this book, Python 3.5.1 is available for download; however, for the purposes of this book, we will go with the more reliable version, 3.4.4.

Downloading and installing Python

Similar to R, Python has a dedicated website for downloading appropriate versions of Python: https://www.python.org/downloads/

Once the executable file has been downloaded, run it to get the installation process started, as shown in the following screenshot:

Select the appropriate directory for the installation of Python 3.4.4. When asked to customize Python during the installation, allow all the features to be selected, especially Add python.exe to Path, as this function will allow you to just type python in a command prompt without having to type in the full path...

Let's start a new Python notebook by going to File and selecting New Jupyter Notebook. We can assign it the following name: PercentBikeRiders by Country. We will scrape a table from the following Wikipedia website: https://github.com/asherif844/PracticalBusinessIntelligence/wiki/AdventureWorks---Detail-by-CountryCode.

This table lists country codes with the percentage of bicycle riders, as seen in the following screenshot:

In our new notebook, our first lines of code will import all of the required modules that we just finished installing, as seen in the following script:

#import packages into the project 
from bs4 import BeautifulSoup 
from urllib.request import urlopen 
import pandas as pd 

Once those have been imported, click on the play symbol button on the toolbar to execute the code inside of the cells.

At this point, you can continue to work inside of PyCharm directly, or you can copy the server IP address (http://127.0.0.1:8888) that pops...

We now have two different data frames saved as comma separated value (CSV) files from GitHub and we wish to upload them to our database in Microsoft SQL Server.

Importing DiscountCodebyWeek

The two files are:

• DiscountCodebyWeek.csv

• CountryRegionBikes.csv

The first step is to log in to SQL Server using Management Studio and right-click on the AdventureWorks2014 database. The next step is to click on Tasks and then on Import Data.

Set your data source to be a Flat File Source and browse to select the source DiscountCodebyWeek.csv.

When choosing your data source, select the Advanced tab and rename the first column to Index, as seen in the following screenshot:

Additionally, we can rename both "WeekinYear" as well as "DiscountCode" to WeekInYear and DiscountCode by selecting each one and removing the quotes. We also want to change the data type of DiscountCode from string [DT_STR] to numeric [DT_NUMERIC] to confirm that the database will treat DiscountCode...

We have covered quite a bit in this chapter to get us started with both R and Python but our work will pay off as we move along with subsequent chapters. We went through two exercises to scrape data from GitHub using both R and Python. As can be seen, both tools have popular packages that allow for easy scraping of data. Both approaches were described in detail to allow you to find which process works better for you. Python is more generally known as a web scraping software tool; however, R has similar capabilities for similar tasks. Both approaches were presented to offer you more tools to keep in your toolbox. These are not the only packages that either programming language has to offer to allow for web scraping, but they are some of the more popular ones. Further investigation will show many other scraping packages such as scrapy for Python.

In the next chapter, we will begin our BI development with Microsoft Excel and PowerBI.