<p>Learn the fundamentals of R programming essential for data science.</p>



<p>This is an essential course in R programming and discusses the core programming principles of R.</p>



<p>The first section covers the fundamentals of R programming language and how to start using it for finance. You will also learn the basics of data analysis with R using the common data structures such as vectors, matrices, lists and data frames.</p>



<p>The next section covers the key topics like control structures, relational and logical operators, conditional statements, loops and functions.</p>



<p>You will also learn about how to import data into R from various sources such as flat files, excel files, databases, web and other software.</p>



<p>Finally, you will learn the concepts of R functions so that you can write your own functions and automate analysis tasks.</p>

<p>Relational Operators are used to compare values in R objects. There are six relational operators:</p>



<figure class="wp-block-table"><table><tbody><tr><th>Operator</th><th>Description</th></tr><tr><td>&lt;</td><td>Less than</td></tr><tr><td>&gt;</td><td>Greater than</td></tr><tr><td>&lt;=</td><td>Less than or equal to</td></tr><tr><td>&gt;=</td><td>Greater than or equal to</td></tr><tr><td>==</td><td>Equal to</td></tr><tr><td>!=</td><td>Not equal to</td></tr></tbody></table></figure>



<p>Each element of the first vector is compared with the corresponding element of the second vector. The result of comparison is a Boolean value.</p>



<p>Let's look at each of these operators in detail.</p>



<h3>Greater Than (&gt;)</h3>



<p>The <code>greater than (>)</code> symbol checks if each element of the first vector is greater than the corresponding element of the second vector. The result will be a vector with logical values (TRUE or FALSE) depending on whether the condition is true or not.</p>



<pre class="wp-block-code"><code>The following are two vectors containing returns from two stocks over the past five days.
stock_A &lt;- c(10, 8, 9, 11, 12)
stock_B &lt;- c(8, 11, 10, 10, 12)
# Compare if Stock A's returns are higher than Stock B's returns
stock_A > stock_B</code></pre>



<p>The result will be as follows:</p>



<pre class="wp-block-code"><code>&#91;1]  TRUE FALSE FALSE  TRUE FALSE</code></pre>



<p>As you can see, Stock A's returns were higher than Stock B on day 1 (10 &gt; 8) and day 4 (11 &gt; 10). So, for these days, the resulting vector contains TRUE, while for the rest of the days, it is FALSE. Specially observe the last day where the returns were same for both the stocks (12 and 12). Since the condition was exclusively checking for returns being greater, the condition is FALSE here.</p>



<h3>Less Than (&lt;)</h3>



<p>The <code>less than (&lt;)</code> symbol checks if each element of the first vector is less than the corresponding element of the second vector. This is just the opposite of the 'greater than' operator.</p>



<pre class="wp-block-code"><code>> stock_A &lt;- c(10, 8, 9, 11, 12)
> stock_B &lt;- c(8, 11, 10, 10, 12)
> 
> #compare if Stock A's returns are less than Stock B's returns
> 
> stock_A &lt; stock_B
&#91;1] FALSE  TRUE  TRUE FALSE FALSE</code></pre>



<h3>Equal (==)</h3>



<p>The <code>equal (==)</code> operator checks if each element of the first vector is equal to the corresponding element of the second vector. In our vectors, this condition meets only for the returns on the last day (12 == 12) as shown below:</p>



<pre class="wp-block-code"><code>> stock_A &lt;- c(10, 8, 9, 11, 12)
> stock_B &lt;- c(8, 11, 10, 10, 12)
> 
> #compare if Stock A's returns are equal to Stock B's returns
> 
> stock_A == stock_B
&#91;1] FALSE FALSE FALSE FALSE  TRUE</code></pre>



<h3>Not Equal To (!=)</h3>



<p>The <code>Not Equal (!=)</code> operator checks if each element of the first vector is unequal to the corresponding element of the second vector.</p>



<pre class="wp-block-code"><code>> # The following are two vectors containing returns from two stocks over the past five days.
> 
> stock_A &lt;- c(10, 8, 9, 11, 12)
> stock_B &lt;- c(8,11,10,10,12)
> 
> #compare if Stock A's returns are not equal to Stock B's returns
> 
> stock_A != stock_B
&#91;1]  TRUE  TRUE  TRUE  TRUE FALSE</code></pre>



<h3>Greater Than Equal To (&gt;=)</h3>



<p>The <code>Greater Than Equal To (>=)</code> operator checks if each element of the first vector is greater than or equal to the corresponding element of the second vector.</p>



<pre class="wp-block-code"><code>> # The following are two vectors containing returns from two stocks over the past five days.
> 
> stock_A &lt;- c(10, 8, 9, 11, 12)
> stock_B &lt;- c(8,11,10,10,12)
> 
> #compare if Stock A's returns are greater than or equal to Stock B's returns
> 
> stock_A >= stock_B
&#91;1]  TRUE FALSE FALSE  TRUE  TRUE</code></pre>



<p>Compare this with the&nbsp;<code>Greater Than (&gt;)</code>&nbsp;operator. Everything else is same, just the last element now returns TRUE (because of the equal to condition).</p>



<h3>Less Than Equal To (&lt;=)</h3>



<p>The <code>Less Than Equal To (&lt;=)</code> operator checks if each element of the first vector is less than or equal to the corresponding element of the second vector.</p>



<pre class="wp-block-code"><code>> stock_A &lt;- c(10, 8, 9, 11, 12)
> stock_B &lt;- c(8,11,10,10,12)
> 
> #compare if Stock A's returns are lower than or equal to Stock B's returns
> 
> stock_A &lt;= stock_B
&#91;1] FALSE  TRUE  TRUE FALSE  TRUE</code></pre>

Relational Operators in R

<p>In programming, you will often come across situations where you will need to evaluate multiple conditions. For example, you may want to perform an action when the price of a stock is above 10 but below 12. Logical operators come into play in such situations. These logical operators allow a program to make a decision based on multiple conditions.</p>



<p>In R, there are five logical operators.</p>



<h3>AND (&amp;)</h3>



<p>This is called the Logical AND Operator. The logical AND operator (&amp;) returns the boolean value true if both operands are true and returns false otherwise. The following examples illustrate this:</p>



<pre class="wp-block-code"><code>#Assign values to Stock A and Stock B
stock_A = 10
stock_B = 12
#Test if Stock A is equal to 10 and Stock B is equal to 12.
(stock_A==10) &amp; (stock_B==12)</code></pre>



<p>This will return TRUE if both the conditions evaluate to TRUE. The result will be TRUE as shown below:</p>



<pre class="wp-block-code"><code>> (stock_A==10) &amp; (stock_B==12)
&#91;1] TRUE</code></pre>



<p>Below is another example of &amp; operator on two vectors.</p>



<pre class="wp-block-code"><code># The following are two vectors containing returns from two stocks over the past five days.
stock_A &lt;- c(10, 8, 9, 11, 12)
stock_B &lt;- c(8,11,10,10,12)
# Stock A exceeds 10 (Inclusive) and Stock B is below 10 (Inclusive)
(stock_A >=10) &amp; (stock_B &lt;=10)</code></pre>



<p>The conditions are satisfied at element level. For the first element, both conditions are true (Stock A: 10&gt;=10 and Stock B: 8 &lt;=10). Since we are using AND operator, both conditions should be TRUE, which it is in our case, so, for the first element, the result will be TRUE. For the 2nd element both conditions are FALSE. Stock A value is 8 which is not greater than or equal to 10. Similarly for Stock B actual value is 11, which is not less than or equal to 10 as required by our condition. Since both conditions are FALSE, the result will be FALSE. The only time the result will be TRUE will be when both the conditions are TRUE.</p>



<p>The results are shown below:</p>



<pre class="wp-block-code"><code>> (stock_A >=10) &amp; (stock_B &lt;=10)
&#91;1]  TRUE FALSE FALSE  TRUE FALSE</code></pre>



<h3>AND (&amp;&amp;)</h3>



<p>If you have used other programming languages then you would know that &amp;&amp; is also used for AND operator. It is indeed used, but there is a big difference. The AND operator with two ampersand signs (&amp;&amp;) is called longer form operator and in R, the longer form evaluates left to right examining only the first element of each vector.</p>



<p>So if you use &amp;&amp; on single values, there is no difference, but if you use it on vectors, only the first elements of the vectors would be evaluated.</p>



<pre class="wp-block-code"><code>> #Assign values to Stock A and Stock B
> stock_A = 10
> stock_B = 12
> 
> #Test if Stock A is equal to 10 and Stock B is equal to 12.
> (stock_A==10) &amp;&amp; (stock_B==12) 
&#91;1] TRUE
> 
> # The following are two vectors containing returns from two stocks over the past five days.
> 
> stock_A &lt;- c(10, 8, 9, 11, 12)
> stock_B &lt;- c(8,11,10,10,12)
> 
> # Stock A exceeds 10 (Inclusive) and Stock B is below 10 (Inclusive)
> 
> (stock_A >=10) &amp;&amp; (stock_B &lt;=10)
&#91;1] TRUE
></code></pre>



<p>As you can see, only the first element of the vector is evaluated.</p>



<h3>OR (|)</h3>



<p>This is called the Logical OR Operator. The logical OR operator (|) returns the boolean value true if either operand is true and returns false otherwise.</p>



<pre class="wp-block-code"><code>> #Assign values to Stock A and Stock B
> stock_A = 10
> stock_B = 12
> 
> #Test if Stock A is equal to 10 or Stock B is equal to 12.
> (stock_A==10) | (stock_B==15) 
&#91;1] TRUE
> 
> # The following are two vectors containing returns from two stocks over the past five days.
> 
> stock_A &lt;- c(10, 8, 9, 11, 12)
> stock_B &lt;- c(8,11,10,10,12)
> 
> # Stock A exceeds 10 (Inclusive) OR Stock B is below 10 (Inclusive)
> 
> (stock_A >=10) | (stock_B &lt;=10)
&#91;1]  TRUE FALSE  TRUE  TRUE  TRUE</code></pre>



<h3>OR (||)</h3>



<p>The long form OR operator (||) works just like the long form AND operator. Only the first element of the vector is evaluated.</p>



<h3>NOT (!)</h3>



<p>This is called Logical NOT operator. It takes each element of the vector and gives the opposite logical value.</p>



<pre class="wp-block-code"><code>> #Assign values to Stock A and Stock B
> stock_A = 10
> stock_B = 12
> 
> #Examples of Logical NOT Operator
> !((stock_A==10) | (stock_B==15))
&#91;1] FALSE
> !TRUE
&#91;1] FALSE
> !(5>10)
&#91;1] TRUE</code></pre>



<p>Now that we know about relational and logical operators, we can move to the next topic - Conditional Statements.</p>

Logical Operators in R

<p>In programming, we often want to check a conditional statement and then do something if the condition is met and do something else if the condition is not met. This is done using the If conditions.</p>



<p>There are two general forms of decision making structures found in most of the programming languages:</p>



<div class="wp-block-image"><figure class="aligncenter size-large"><img src="https://financetrain.com/wp-content/uploads/27536-if-condition.png" alt="" class="wp-image-27602"/></figure></div>



<h3><strong>if statement</strong></h3>



<pre class="wp-block-code"><code>if(BooleanExpression)
    statement;
if(BooleanExpression) {
    statement1;
    statement2;
    ...
}</code></pre>



<p>If the BoolenExpression is true, then the statements will be executed.</p>



<h3><strong>if...else statement</strong></h3>



<pre class="wp-block-code"><code>    statement or block 1
else
    statement or block 2</code></pre>



<h3>Examples</h3>



<p>Let's look at a few examples of the conditional statements.</p>



<pre class="wp-block-code"><code>#Imaginary stock prices
stock_A &lt;- 100
stock_B &lt;- 120
#Check if Stock B's price is above 100.
if(stock_B>100) {
  print("We recommend Stock B")
}</code></pre>



<p>When the above code is compiled and executed, it produces the following result:</p>



<pre class="wp-block-code"><code>&#91;1] "We recommend Stock B"</code></pre>



<p>Let's take another example to evaluate an If...Else statement.</p>



<pre class="wp-block-code"><code>#Imaginary stock prices
stock_A &lt;- 100
stock_B &lt;- 120
#Check if Stock A's price is greater than Stock B's price.
if(stock_A>stock_B) {
  print("We recommend Stock A")
} else {
  print("We recommend Stock B")
}</code></pre>



<p>The above code will produce the following results:</p>



<pre class="wp-block-code"><code>&#91;1] "We recommend Stock B"</code></pre>



<p>As you can see, the Boolean Express (stock_A&gt;stock_B) is false. Therefore, the statements in the 'else' block are evaluated.</p>



<h3>The if...else if...else Statement</h3>



<p>We can use <code>else if</code> to further customize our conditional statement. With the <code>if else</code> we can extend the if statement as much as you want. In the entire control structure, as soon as a condition is met (TRUE) the corresponding block of statements will be executed, and the rest of the structure will be ignored.</p>



<pre class="wp-block-code"><code>if (BooleanExpression1) {
  expression1
} else if (BooleanExpression2) {
  expression2
} else if (BooleanExpression3) {
  expression3
} else {
  expression4
}</code></pre>



<p>If BooleanExpression1 is TRUE, then expression1 will be executed. If FALSE, then it will check for BooleanExpression2. If BooleanExpression2 is TRUE, then expression2 will be execued, otherwise, it will evaluate BooleanExpression3. If BooleanExpression3 is TRUE, expression3 will be executed, otherwise, expression 4 will be executed.</p>



<p>Let's look at an example:</p>



<pre class="wp-block-code"><code>#Imaginary stock prices
stock_A &lt;- 100
stock_B &lt;- 120
#Compare Stock A and Stock B
if (stock_A>stock_B) {
  print("We recommend Stock A")
} else if (stock_A==stock_B) {
  print("We are neutral to both stocks")
} else {
  print("We recommend Stock B")
}</code></pre>



<p>The above code will produce the following results:</p>



<pre class="wp-block-code"><code>&#91;1] "We recommend Stock B"</code></pre>



<p>In our if conditions, we can use logical operators to check for multiple conditions, as shown below:</p>



<pre class="wp-block-code"><code>#Imaginary stock prices
stock_A &lt;- 100
stock_B &lt;- 120
if (stock_A>=100 &amp; stock_B>=100) {
  print("Sell both stocks")
} else {
  print("No recommendation")
}</code></pre>



<p>What do you think will print?</p>

Conditional Statements in R

<p>R has three statements that provide explicit looping. They are&nbsp;<code>for</code>,&nbsp;<code>while</code>&nbsp;and&nbsp;<code>repeat</code>. Each of the three statements returns the value of the last statement that was evaluated. R also provides other functions for implicit looping such as&nbsp;<code>tapply</code>,&nbsp;<code>apply</code>, and&nbsp;<code>lapply</code>.</p>



<p>There are two statements that can be used to explicitly control looping. They are&nbsp;<code>break</code>&nbsp;and&nbsp;<code>next</code>. The break statement causes an exit from the innermost loop that is currently being executed. The next statement immediately causes control to return to the start of the loop. The next iteration of the loop (if there is one) is then executed. No statement below&nbsp;<code>next</code>&nbsp;in the current loop is evaluated.</p>



<p>In this lesson, we will discuss the <code>for</code> loop. The syntax of the for loop is:</p>



<pre class="wp-block-code"><code>for ( name in vector )
    statement1</code></pre>



<p>Here&nbsp;<em>vector</em>&nbsp;can be either a vector or a list. For each element in vector the variable&nbsp;<code>name</code>&nbsp;is set to the value of that element and&nbsp;<em>statement1</em>&nbsp;is evaluated. Basically, the loop iterates over the vector.</p>



<h3>Example: Generate Fibonacci sequence for ‘n’ numbers</h3>



<p>The following example shows generating Fibonacci sequence for ‘n’ numbers using the <code>For</code> loop.</p>



<pre class="wp-block-code"><code>n &lt;- 10
fib &lt;- numeric(n)
fib&#91;1] &lt;- 1
fib&#91;2] &lt;- 1
for (i in 3:n)
{
  fib&#91;i] &lt;- fib&#91;i-1]+fib&#91;i-2] 
}
print(fib)</code></pre>



<p>The above code will output the Fibonacci series as shown below:</p>



<pre class="wp-block-code"><code>> print(fib)
 &#91;1]  1  1  2  3  5  8 13 21 34 55</code></pre>



<h3>Example: Print Stock Prices</h3>



<p>Below is another simple example that prints the stocks prices of a stock from a vector containing stock prices for the past five days.</p>



<pre class="wp-block-code"><code>stock_A &lt;- c(10, 8, 9, 11, 12)
for (i in stock_A)
{
  print(i)
}</code></pre>



<p>This will print the stock prices one by one as the loop iterates over the elements in the vector:</p>



<pre class="wp-block-code"><code>&#91;1] 10
&#91;1] 8
&#91;1] 9
&#91;1] 11
&#91;1] 12</code></pre>



<h3>Example: Break</h3>



<p>The below example demonstrates the use of <code>break</code> statement. For this example, we would like to break and end the loop if the stock price is equal to 11.</p>



<pre class="wp-block-code"><code>stock_A &lt;- c(10, 8, 9, 11, 12)
for (i in stock_A)
{
  if(i == 11)
  {
    break
  } 
  print(i)
}</code></pre>



<p>The loop will execute normally. Each time, it will check if <code>i</code> is equal to 11, if not, it will proceed to print the value of <code>i</code>. If <code>i</code> becomes equal to 11, then the <code>break</code> statement will be executed and the loop will be exited.</p>



<pre class="wp-block-code"><code>&#91;1] 10
&#91;1] 8
&#91;1] 9</code></pre>



<p></p>

For Loop in R Programming

<p>The general format of the while statement is:</p>



<pre class="wp-block-code"><code>while (condition) {
  statement
}</code></pre>



<p>Note that a while loop may never execute the statement. The statement is executed repeatedly until condition becomes false.</p>



<p>In contrast, a repeat loop has the general format as below:</p>



<pre class="wp-block-code"><code>repeat {
  print('test')
}</code></pre>



<p><code>repeat</code>&nbsp;statement will execute at least once, and continue until it is explicitly interrupted with a break statement. In fact, break will immediately exit from any loop structure.</p>



<p>Unlike <code>for</code> loop which iterates over a vector list, for using <code>while</code> loop you need to have an indicator variable <code>i</code> and change its value within each iteration. Otherwise you will have an infinite loop.</p>



<h3>Example: While Loop</h3>



<p>The following example shows how to calculate factorial of 10 using the while loop.</p>



<pre class="wp-block-code"><code># Calculate 10! using a while loop
i &lt;- 10
f &lt;- 1
while(i>1) {
  f &lt;- i*f
  i &lt;- i-1
  cat(i, f, "\n")
}
f</code></pre>



<p>We start with an iterator <code>i</code> with a value of 10. Then each time the while loop iterates, we reduce the value of <code>i</code> by 1. Finally, once <code>i</code> becomes equal to 1, the while <code>loop</code> ends. </p>



<p>The cat() function is used to concatenate values of different variables and strings. It takes the arguments, converts them into strings and then concatenates them. In our example, cat(i, f, "\n"), it takes three things - i, f and '\n'' - and prints the results - the value of i, f and then a new line break.</p>



<p>The results are shown below:</p>



<pre class="wp-block-code"><code>9 10 
8 90 
7 720 
6 5040 
5 30240 
4 151200 
3 604800 
2 1814400 
1 3628800</code></pre>



<h3>Example: Repeat Loop</h3>



<p>Let's now take a look at <code>repeat</code> statement.</p>



<pre class="wp-block-code"><code>repeat {
  expressions
  if(condition) break
}</code></pre>



<p>The repeat loop does not contain a limit. Therefore it is necessary to include an if statement with the break command to make sure you do not have an infinite loop.</p>



<p>The following code shows how to calculate factorial of 10 using <code>repeat</code> statement.</p>



<pre class="wp-block-code"><code># Calculate 10! using a repeat loop
i &lt;- 10
f &lt;- 1
repeat {
  f &lt;- i*f
  i &lt;- i-1
  cat(i, f, "\n")
  if(i&lt;1) break
}
f</code></pre>



<p>Notice the <code>if</code> condition inside the repeat loop. The result will be exactly the same as with <code>while</code> loop.</p>



<pre class="wp-block-code"><code>9 10 
8 90 
7 720 
6 5040 
5 30240 
4 151200 
3 604800 
2 1814400 
1 3628800 
0 3628800</code></pre>



<p>In the above examples we used <code>while</code> and <code>repeat</code> loops to calculate the factorial of a number. However, R also has a function that we can use to calculate the factorial. We will learn more math and statistical functions as we go along.</p>



<pre class="wp-block-code"><code>> factorial(10)
&#91;1] 3628800</code></pre>



<p></p>

While and Repeat Loop in R Programming

<p>Functions are an important concept in R and we will be using it all the time. In fact, we have already been using functions in our previous examples. For example, we used the&nbsp;<code>summary()</code>&nbsp;to summarize an R object. Similarly, we used the&nbsp;<code>str()</code>&nbsp;function to learn about the structure of an R object.</p>



<p>R functions are objects that evaluate multiple expressions using arguments that are passed to them.</p>



<p>To understand how to use functions, let's take the function to calculate standard deviation in R. The function is defined as&nbsp;<code>sd()</code>.</p>



<p>Let's take our vector of stock prices for the past five days. We can use the <code>sd()</code> function to calculate its standard deviation.</p>



<pre class="wp-block-code"><code>#Stock A's Price Data
stock_A &lt;- c(10, 8, 9, 11, 12)
#Calculate Standard Deviation of Stock A
sd(stock_A)</code></pre>



<p>The <code>sd()</code> function takes the vector as an input and returns the standard deviation.</p>



<pre class="wp-block-code"><code>&#91;1] 1.581139</code></pre>



<h3>Function Help</h3>



<p>Functions have named arguments which potentially have default values. To learn about how to use a function or its argument list, we can use R documentation. For example, to get help on the <code>sd()</code> function type <code>help(sd)</code> or <code>?sd</code>. This will open the R documentation with details of the function as shown below:</p>



<div class="wp-block-image"><figure class="aligncenter size-large"><img src="https://financetrain.com/wp-content/uploads/27549-sd-help.png" alt="" class="wp-image-27613"/></figure></div>



<p>As you can see, the&nbsp;<code>sd()</code>&nbsp;function takes two arguments. The first argument is&nbsp;<code>x</code>&nbsp;which is the vector. The second argument&nbsp;<code>na.rm</code>&nbsp;has a default value of&nbsp;<code>FALSE</code>. This argument checks whether missing values should be removed. Since the argument&nbsp;<code>na.rm</code>&nbsp;has a default value, it is an optional argument which means we don't have to necessarily specify it unless we want to use a value other than the default value.</p>



<p>If you don't want to get full help on the function but just want to get the list of its arguments, you can use the <code>args()</code> function as shown below:</p>



<pre class="wp-block-code"><code>> args(sd)
function (x, na.rm = FALSE) 
NULL
></code></pre>



<h3>Argument Matching</h3>



<p>R functions arguments can be matched positionally or by name. So, the following calls to <code>sd()</code> are all equivalent:</p>



<pre class="wp-block-code"><code>#Stock A's Price Data
stock_data &lt;- c(10, 8, 9, 11, 12)
#Calculate Standard Deviation of the Stock
sd(stock_data)
sd(x = stock_data)
sd(x = stock_data, na.rm = FALSE)
sd(na.rm = FALSE, x = stock_data)
sd(na.rm = FALSE, stock_data)</code></pre>



<p>Let's say our dataset contained a missing value. In that case, we would need to specify <code>na.rm = TRUE</code> for the function to evaluate correctly.</p>



<pre class="wp-block-code"><code>> #Stock A's Price Data
> stock_data &lt;- c(10, 8, 9, NA, 11, 12)
> 
> #Calculate Standard Deviation of the Stock. 
> 
> #This will include the 'NA' value in calculation 
> #and the function will not evaluate correctly.
> sd(x = stock_data, na.rm = FALSE)
&#91;1] NA
> 
> #This will exclude the 'NA' value in calculation 
> #and the function will evaluate correctly.
> sd(x = stock_data, na.rm = TRUE)
&#91;1] 1.581139
></code></pre>



<p>Most of the time, named arguments are useful on the command line when you have a long argument list and you want to use the defaults for everything except for an argument near the end of the list.</p>



<p>Named arguments also help if you can remember the name of the argument and not its position on the argument list.</p>



<h2>Some More Examples of Functions</h2>



<p>Below are some more examples of functions for vectors:</p>



<ul><li><code>c()</code>&nbsp;- combines values, vectors, and/or lists to create new objects.</li><li><code>unique()</code>&nbsp;- returns a vector containing one element for each unique value in the vector.</li><li><code>rev()</code>&nbsp;- reverse the order of elements in a vector.</li><li><code>sort()</code>&nbsp;- sorts the elements in a vector.</li><li><code>append()</code>&nbsp;- append or insert elements in a vector.</li><li><code>sum()</code>&nbsp;- sum of the elements of a vector.</li><li><code>min()</code>&nbsp;- minimum value in a vector.</li><li><code>max()</code>&nbsp;- maximum value in a vector.</li></ul>

Functions in R Programming

<p>In the previous lesson, we learned about how to use functions in R. This is very useful because R has a lot of built-in functions which we can use to make coding easy. The beauty of functions is that we can just use them in our programs without knowing anything about their inner workings.</p>



<p>Remember the&nbsp;<code>sd()</code>&nbsp;function? We just used it in our program to calculate the standard deviation. Inside of it, it is actually a detailed program which calculates the standard deviation. However, since we are just users of the function, we simply use it and don't have to know anything about what's happening inside of it. Another important thing about functions is that we can use them multiple times in our code and because they abstract away functionality, they make our programs short, clean and error-free.</p>



<p>The good thing is that in R we can write our own functions. This can be useful when we know that we will be using a custom piece of functionality multiple times in our programs.</p>



<p>Functions are created using the <code>function()</code> directive and are stored as R objects just like anything else. In particular, they are R objects of class "function".</p>



<pre class="wp-block-code"><code>f &lt;- function(&lt;arguments>) {
  ## Do something interesting
}</code></pre>



<p>Functions in R are “first class objects”, which means that they can be treated much like any other R object. Importantly,</p>



<ul><li>Functions can be passed as arguments to other functions</li><li>Functions can be nested, so that you can define a function inside of another function</li></ul>



<p>The return value of a function is the last expression in the function body to be evaluated.</p>



<h3>Example: DoubleIt Function</h3>



<p>Let's create a simple function that will take a numeric value as an argument and returns the double of the number as its output.</p>



<pre class="wp-block-code"><code>doubleMe &lt;− function ( x = 0 ) {
  output &lt;− 2 * x
}</code></pre>



<p>A few points:</p>



<ul><li>Our function is named&nbsp;<code>doubleMe</code></li><li>When the function is executed, the last named thing is the one that comes back from the function. Note, explicit use of a return function is not required (though we can do it with&nbsp;<code>return()</code>&nbsp;statement). The last named thing comes back to us.</li><li>The function takes one argument (x), which has a default value of 0. So, even if you don't specify any input argument, it will use the default value 0 and execute the function</li></ul>



<p>Sample Results:</p>



<pre class="wp-block-code"><code>> #print double of 7
> print(doubleMe(7))
&#91;1] 14
> 
> #print double of 8
> print(doubleMe(8))
&#91;1] 16
> #print double of vector. vector in ⇒ vector out
> print(doubleMe(c(2,5,7)))
&#91;1]  4 10 14
></code></pre>



<h3>Example: Duration of a Bond</h3>



<p>Let's now take a more concrete example. Given yield per coupon period y, no. of periods to maturity n, and Coupon rate per period, c, the Macauley Modified Duration of the bond can be calculated as follows:</p>



<p>Macauley Modified Bond Duration: BD = 1/(1+y){(1+y)/y - [1+y + n(c-y)] / {c[(1+y)n - 1] + y}}</p>



<p>Suppose you have many bonds with different y, n and c. We can write a new function which can help us calculate the duration of R.</p>



<pre class="wp-block-code"><code>#Function to calculate duration in R
MDur=function(y,n,c){
  mduration=(1/(1+y))*((1+y)/y - (1+y+n*(c-y)) / (c*((1+y)^n - 1) + y))
  mduration
}</code></pre>



<p>The inputs are y, n and c. The equation defines how we get the modified duration by using the input values. And the last statement gives the return value (the duration)</p>



<p><strong>Results:</strong></p>



<pre class="wp-block-code"><code>> #Calculate Modified duration (y=4%, n=20, and c=6%)
> print(MDur(0.04,20,.06))
&#91;1] 12.57829</code></pre>



<h3>Example: Coefficient of Variation</h3>



<p>Here's a function for calculating the coefficient of variation (the ratio of the standard deviation to the mean) for a vector:</p>



<pre class="wp-block-code"><code>coef.of.var &lt;- function(x){
  meanval &lt;- mean(x,na.rm=TRUE) # recall this means "ignore NAs"
  sdval &lt;- sd(x,na.rm=TRUE)
  return(sdval/meanval)
}</code></pre>



<p>This function says "if you give me an object, that I will call x, I will store its&nbsp;<code>mean()</code>&nbsp;as&nbsp;<code>meanval</code>, then its&nbsp;<code>sd()</code>&nbsp;as&nbsp;<code>sdval</code>, and then return their ratio&nbsp;<code>sdval/meanval</code>."</p>



<p>R has some interesting datasets which can be directly loaded into an R session. One such data set is <code>airquality</code>. This can be loaded into our R session using <code>data(airquality)</code>. Once it is loaded you can use the data, check its summary, structure, etc. This dataset contains New York Air Quality Measurements. This particular dataset contains a data frame with 154 observations on 6 variables.</p>



<pre class="wp-block-code"><code>&#91;,1]    Ozone    numeric    Ozone (ppb)
&#91;,2]    Solar.R    numeric    Solar R (lang)
&#91;,3]    Wind    numeric    Wind (mph)
&#91;,4]    Temp    numeric    Temperature (degrees F)
&#91;,5]    Month    numeric    Month (1--12)
&#91;,6]    Day    numeric    Day of month (1--31)</code></pre>



<p>In the following example, we load this dataset and then calculate Coefficient of Variation on the Ozone column.</p>



<pre class="wp-block-code"><code>> data(airquality)
> coef.of.var(airquality$Ozone)
&#91;1] 0.7830151
></code></pre>

Creating Functions in R

<p>In the earlier lessons, we learned about how we can use the&nbsp;<code>for</code>&nbsp;loop to iterate over various R objects. R also provides other functions for implicit looping such as&nbsp;<code>apply</code>,&nbsp;<code>lapply</code>&nbsp;and&nbsp;<code>sapply</code>&nbsp;which are even easier to use. It is a whole family of functions that are commonly referred to as 'Apply' family of functions.</p>



<p>The&nbsp;<code>apply</code>&nbsp;functions are not just for compactness of code, but also for speed. If speed is an issue, such as when working with large data sets or long-running simulations, one must avoid explicit loops (read 'for' loop, or 'while' loop) as much as possible, because with&nbsp;<code>apply()</code>&nbsp;function and its variants, R can do them a lot faster than you can.</p>



<h3>The&nbsp;<code>apply</code>&nbsp;Function</h3>



<p>The <code>apply()</code> function applies a simple function over dimensions of a data structure. The <code>apply()</code> function has the following structure:</p>



<pre class="wp-block-code"><code>> args(apply)
function (X, MARGIN, FUN, ...)</code></pre>



<ul><li>X is any R structure with dimensions (matrix, data frame, array .. NOT lists or vectors)</li><li>MARGIN is the dimension number (1 = rows, 2 = columns)</li><li>FUN is the function to apply (example: <code>mean()</code>)</li><li>... represents additional arguments to the function</li></ul>



<h3>Example</h3>



<p>Let's define a simple matrix <code>myMatrix</code> which we will use to understand the <code>apply()</code> function. The <code>rpois()</code> function is a built-in function that can be used to simulate N independent Poisson random variables. For example, we can generate 30 Poisson random numbers with parameter λ = 3 as follows: <code>> rpois(30, 3)</code></p>



<pre class="wp-block-code"><code>> myMatrix &lt;- matrix(rpois(30,3),5)
> myMatrix
     &#91;,1] &#91;,2] &#91;,3] &#91;,4] &#91;,5] &#91;,6]
&#91;1,]    3    1    7    4    4    3
&#91;2,]    2    3    5    5    2    3
&#91;3,]    2    5    1    2    2    1
&#91;4,]    3    5    1    4    5    3
&#91;5,]    0    0    4    2    1    2
></code></pre>



<p>Now let's take a few examples to understand how we can use the&nbsp;<code>apply()</code>&nbsp;function.</p>



<p>Calculate the <code>sum</code> of each row.</p>



<pre class="wp-block-code"><code>> apply(myMatrix,1,sum)
&#91;1] 22 20 13 21  9</code></pre>



<p>The&nbsp;<code>apply</code>&nbsp;function applied the function&nbsp;<code>sum</code>&nbsp;to each row (specified with MARGIN=1) of the matrix&nbsp;<code>myMatrix</code>.</p>



<p>We can do the same column-wise by changing MARGIN=2, as shown below:</p>



<pre class="wp-block-code"><code>> apply(myMatrix,2,sum)
&#91;1] 10 14 18 17 14 12</code></pre>



<p>The following two examples show row minima and column maxima:</p>



<pre class="wp-block-code"><code>> apply(myMatrix,1,min) #row minima
&#91;1] 1 2 1 1 0
> apply(myMatrix,2,max) #col maxima
&#91;1] 3 5 7 5 5 3
></code></pre>



<p>The&nbsp;<code>apply</code>&nbsp;function can be used with matrixes, arrays, and data frames. There are other functions which can be applied to lists and vectors.</p>



<h3>The&nbsp;<code>lapply()</code>&nbsp;Function</h3>



<p>The analog of&nbsp;<code>apply()</code>&nbsp;for lists is&nbsp;<code>lapply()</code>. It applies the given function to all elements of the specified list.</p>



<h3><strong>Example</strong></h3>



<pre class="wp-block-code"><code>> lapply(list(1:5,20:26),median)
&#91;&#91;1]]
&#91;1] 3
&#91;&#91;2]]
&#91;1] 23
></code></pre>



<p>As you can see, the <code>lapply()</code> function returns a list (always). You can use the <code>unlist()</code> to flatten the list and produce a simple vector.</p>



<pre class="wp-block-code"><code>> unlist(lapply(list(1:5,20:26),median))
&#91;1]  3 23</code></pre>



<p>We can also use the <code>apply</code> family of functions on our own custom functions as shown below:</p>



<pre class="wp-block-code"><code>#The custom oilCost function - oil cost for two gallons
oilCost &lt;− function ( x=0 ) {
  output &lt;− x*2
}
#sample vector containing oil prices for the past 5 days (per gallon)
oil_price &lt;- c(10,9,8,9,11)
#Use apply function to calculate two gallon oil cost each day
lapply(oil_price,oilCost)</code></pre>



<p>Result will be a list with cost for two gallons.</p>



<pre class="wp-block-code"><code>> lapply(oil_price,oilCost)
&#91;&#91;1]]
&#91;1] 20
&#91;&#91;2]]
&#91;1] 18
&#91;&#91;3]]
&#91;1] 16
&#91;&#91;4]]
&#91;1] 18
&#91;&#91;5]]
&#91;1] 22
></code></pre>



<p>Let's say our custom function allows us to specify the quantity (instead of the fixed quantity of 2). We can modify our function as follows:</p>



<pre class="wp-block-code"><code>#The custom oilCost function - oil cost for a specified quantity of gallons
oilCost &lt;− function ( x,qty ) {
  output &lt;− x*qty
}
#sample vector containing oil prices for the past 5 days (per gallon)
oil_price &lt;- c(10,9,8,9,11)
#Use apply function to calculate three gallon oil cost each day
unlist(lapply(oil_price,oilCost,qty=3))
#Use apply function to calculate four gallon oil cost each day
unlist(lapply(oil_price,oilCost,qty=4))</code></pre>



<p>The function&nbsp;<code>oilCost</code>&nbsp;is applied to the vector&nbsp;<code>oil_price</code>&nbsp;and the&nbsp;<code>qty</code>&nbsp;is supplied as the third argument.</p>



<p><strong>Result:</strong></p>



<pre class="wp-block-code"><code>> #Use apply function to calculate three gallon oil cost each day
> unlist(lapply(oil_price,oilCost,qty=3))
&#91;1] 30 27 24 27 33
> 
> #Use apply function to calculate three gallon oil cost each day
> unlist(lapply(oil_price,oilCost,qty=4))
&#91;1] 40 36 32 36 44</code></pre>



<h3>The&nbsp;<code>sapply()</code>&nbsp;Function</h3>



<p>As we saw, the <code>lapply</code> function takes a list or a vector, but always returns a list. This is because the input list can have different elements of different classes. That's why the output must be a list to accomodate output elements having different classes. However, there are many scenarios where all the elements of the output are of the same class (e.g. integer), and we would rather get the output as a vector. Earlier we achieved it using the <code>unlist</code> function. However, the <code>sapply</code> is a better choice as it automatically does this for us. It takes a list or a vector as an input and returns a vector or a matrix when possible. internally, it calls <code>lapply</code> and simplifies the output. The following example shows the difference between <code>lapply</code> and <code>sapply</code>.</p>



<pre class="wp-block-code"><code>> myList &lt;- list(Pois = rpois(10,3), Norm = rnorm(5), Unif = runif(5,1,10))
> myList
$Pois
 &#91;1] 7 5 3 3 2 2 0 1 4 4
$Norm
&#91;1]  1.56840563 -0.08843082  0.19965929  0.20645959  0.85374249
$Unif
&#91;1] 8.729636 8.109225 7.884665 9.558795 1.016197
> lapply(myList,mean)
$Pois
&#91;1] 3.1
$Norm
&#91;1] 0.5479672
$Unif
&#91;1] 7.059704
> sapply(myList,mean)
     Pois      Norm      Unif 
3.1000000 0.5479672 7.0597036 
></code></pre>



<p>In this lesson, we saw three functions in the <code>apply</code> family, namely, <code>apply()</code>, <code>lapply()</code>, and <code>sapply()</code>. The entire family is made up of the <code>apply()</code>, <code>lapply()</code> , <code>sapply()</code>, <code>vapply()</code>, <code>mapply()</code>, <code>rapply()</code>, and <code>tapply()</code> functions.</p>

Apply Functions in R

<p>While working on data science projects, you will come across data from many data sources in a variety of formats. It is important for a data scientist to have a solid understanding of the various data sources, data formats, how to bring data into R and how to clean the data for statistical analysis. In the next few lessons, we will focus on how to import data from the following data sources:</p>



<h3>1. Flat Files</h3>



<p>Flat files are spreadsheet-style files with data stored in rows and columns having one record per line. We see flat files every day in the form of comma-separated value files (CSV) and tab-delimited value files.</p>



<h3>2. Excel Files</h3>



<p>These are the most familiar types of files for all financial professionals. In quantitative finance, both R and Excel are the basic tools for any type of analysis. While in this course, we will learn about how to import excel files into R, in future courses we will also learn about how we can use Excel in conjunction with R to perform data analysis.</p>



<h3>3. Databases</h3>



<p>This involves importing data from various types of databases such as MySQL, MSSQL, Oracle, etc.</p>



<h3>4. Web</h3>



<p>Nowadays a lot of information resides on the web and data scientists need to work with this data. We will learn about how to access and import data from the web using APIs, and other web protocols.</p>



<h3>5. Statistical Software Packages</h3>



<p>Sometimes you may have to import data from other statistical software packages such as SAS, STATA and SPSS. Each of them has their own file format and we will learn about how to import data in these formats.</p>



<p>In the next lesson, we will begin by learning to import flat files.</p>

Importing Data from External Data Sources in R

<p>R provides a variety of functions for importing data files. The most common functions which we will use are&nbsp;<code>read.csv()</code>,&nbsp;<code>read.delim()</code>, and&nbsp;<code>read.table()</code>. These functions are loaded in R by default as a part of the&nbsp;<code>utils</code>&nbsp;package when you start R.</p>



<h3>read.csv()</h3>



<p><code>read.csv()</code>&nbsp;is used to import csv (comma separated values) files. R imports the data into a data frame.</p>



<p>Let's understand how to use this function using an actual data file.</p>



<div class="wp-block-file"><a href="https://financetrain.com/wp-content/uploads/top-100-stocks.csv">top-100-stocks</a><a href="https://financetrain.com/wp-content/uploads/top-100-stocks.csv" class="wp-block-file__button" download>Download</a></div>



<h3>Setup</h3>



<p><strong>Step 1: </strong>Download the file called <code>top-100-stocks.csv</code> on your computer. This file contains fundamental variables for top 100 stocks in the US (Data as of January 9, 2017)</p>



<p><strong>Step 2: </strong>Store this file in a location of your choice. For this example, I have stored my downloaded copy of the file in the path <code>"C:\r-programming\data\importing data"</code>.</p>



<p><strong>Step 3: </strong>Set this as working directory in R using the <code>setwd()</code> function. You can check if the working directory is set correctly using the <code>getwd()</code> function.</p>



<pre class="wp-block-code"><code>> setwd("C:/r-programming/data/importing data")
> getwd()
&#91;1] "C:/r-programming/data/importing data"</code></pre>



<p><strong>Step 4:</strong> Check that the CSV file exists in the working directory using the <code>dir()</code> function. The <code>dir()</code> function will list all the files stored in the working directory.</p>



<pre class="wp-block-code"><code>> dir()
&#91;1] "top-100-stocks.csv"</code></pre>



<h3>Writing the function</h3>



<pre class="wp-block-code"><code>read.csv("top-100-stocks.csv", stringsAsfactors = FALSE)</code></pre>



<p>The first argument of read.csv function is the path to the file you want to import in R. In our case since the file is already in the working directory, we just need to specify the file name. However, if the file was in some other location, then the things would be a bit tricky because depending on your operating system, the file paths will be formed differently. To avoid having to deal with escaping backslashes in file paths, you can use the&nbsp;<code>file.path()</code>&nbsp;function to construct file paths that are correct, independent of the operating system you work on.</p>



<p>For example, to set the file path to <code>C:/r-programming/data/importing data</code>, we will use the <code>file.path()</code> function as follows:</p>



<pre class="wp-block-code"><code>> path &lt;- file.path("C:","r-programming","data","importing data","top-100-stocks.csv")
> path
&#91;1] "C:/r-programming/data/importing data/top-100-stocks.csv"
></code></pre>



<p>This stores the file path in the variable&nbsp;<code>path</code>, which can be passed to the&nbsp;<code>read.csv()</code>&nbsp;function instead of the file name.</p>



<p>The second argument&nbsp;<code>stringsAsfactors</code>&nbsp;is very important. When you import a data table into R, the columns that contain character strings can either be imported as factors or as character data. The default option is to convert character strings into factors, i.e., categorical data (example, gender, colors, types, etc). If your data is not categorical data, then you can set&nbsp;<code>stringsAsfactors</code>&nbsp;to&nbsp;<code>FALSE</code>.</p>



<h3>Execute the Command</h3>



<p>We can now execute the command and R will import the data as a data frame, as shown below:</p>



<div class="wp-block-image"><figure class="aligncenter size-large"><img src="https://financetrain.com/wp-content/uploads/ID-27366-readcsv.png" alt="" class="wp-image-27635"/></figure></div>



<h3>Analyzing Results</h3>



<p>Now that you have the data in R as a data frame, you can do all kinds of analysis on it. To get started, you can use <code>str()</code> or <code>summary()</code> function on the data frame to get a summary of the data.</p>



<pre class="wp-block-code"><code>> str(top100stocks)
'data.frame':    100 obs. of  9 variables:
 $ Symbol   : chr  "WINS" "CWEI" "TCK" "CRBP" ...
 $ Name     : chr  "Wins Fin Hldgs Ord" "Clayton Williams Energy" "Teck Resources Ltd" "Corbus Pharma Cmn" ...
 $ MarketCap: chr  "4,051,490" "2,009,420" "13,887,640" "368,830" ...
 $ P.E      : chr  "0.00" "0.00" "102.04" "0.00" ...
 $ EPS      : chr  "0.00" "-17.70" "0.24" "-0.37" ...
 $ NetIncome: chr  "N/A" "-98" "-1,939" "-9" ...
 $ Beta     : chr  "N/A" "2.29" "1.49" "2.57" ...
 $ Dividend : chr  "0.00" "0.00" "0.08" "0.00" ...
 $ DivYield : chr  "0.00%" "0.00%" "0.31%" "0.00%" ...
></code></pre>



<h3>Important Notes</h3>



<ul><li>The&nbsp;<code>read.csv()</code>&nbsp;function has HEADER argument as true by default indicating whether the file contains the names of the variables as its first line. However, if the data being imported doesn't have a header, this should be set to false.</li><li>Sometimes your data may be in the EU (European Union) format, where commas are used as decimal separators and semicolons are used as field separators. In such a case, you need to import it to R using the&nbsp;<code>read.csv2()</code>&nbsp;function which takes care of this format difference.</li></ul>

Importing Data Using read.csv in R

<p>We learnt that we can use&nbsp;<code>read.csv()</code>&nbsp;function to import data from files in comma separated values (CSV) format. A data table can reside in a text file where the cells inside the table are separated by blank characters. If your data uses another character to separate the fields, not a comma, R also has the more general&nbsp;<code>read.table()</code>&nbsp;function.</p>



<p>So if your separator is a tab, for instance, this would work:</p>



<pre class="wp-block-code"><code>mydata &lt;- read.table("filename.txt", sep="\t", header=TRUE)</code></pre>



<p>The command above also indicates there's a header row in the file with&nbsp;<code>header=TRUE</code>.</p>



<p>If, say, your separator is a character such as | you would change the separator part of the command to sep="|".</p>



<h3>Categories or values?</h3>



<p>Because of R's roots as a statistical tool, when you import non-numerical data, R may assume that character strings are statistical factors -- things like "poor," "average" and "good" -- or "success" and "failure."</p>



<p>But your text columns may not be categories that you want to group and measure, just names of companies or employees. If you don't want your text data to be read in as factors, add&nbsp;<code>stringsAsFactor=FALSE</code>&nbsp;to read.table, like this:</p>



<pre class="wp-block-code"><code>mydata &lt;- read.table("filename.txt", sep="\t", header=TRUE, stringsAsFactor=FALSE)</code></pre>



<h3>Example</h3>



<p>For this example, we will take a text file which contains tab delimited historical price data for Goldman Sachs stock. </p>



<div class="wp-block-file"><a href="https://financetrain.com/wp-content/uploads/GS-Stock-Prices.txt">GS-Stock-Prices</a><a href="https://financetrain.com/wp-content/uploads/GS-Stock-Prices.txt" class="wp-block-file__button" download>Download</a></div>



<p>We can now import this data into r using the&nbsp;<code>data.table()</code>&nbsp;function.</p>



<pre class="wp-block-code"><code>> setwd("C:/r-programming/data/importing data")
> getwd()
&#91;1] "C:/r-programming/data/importing data"
> dir()
&#91;1] "GS-Stock-Prices.txt" "top-100-stocks.csv" 
> gs_data &lt;- read.table("GS-Stock-Prices.txt", sep="\t", header=TRUE, stringsAsFactor=FALSE);
> gs_data
         Time   Open   High    Low   Last  Volume
1   1/24/2017 231.86 236.06 230.84 233.68 4448100
2   1/23/2017 231.86 233.75 230.75 232.67 3136100
3   1/20/2017 231.62 233.23 230.54 232.20 5211800
4   1/19/2017 234.07 234.75 230.62 231.41 4561800
5   1/18/2017 236.00 237.69 231.52 234.29 7590400
6   1/17/2017 242.94 243.06 235.61 235.74 6277100
7   1/13/2017 245.43 247.77 242.91 244.30 4186000
8   1/12/2017 245.06 245.47 241.57 243.84 4022300
9   1/11/2017 242.77 245.84 242.00 245.76 3532500
10  1/10/2017 240.87 243.44 239.05 242.57 3432900
11   1/9/2017 243.25 244.69 241.47 242.89 3022700
12   1/6/2017 242.29 246.20 241.37 244.90 3591000
13   1/5/2017 242.72 243.23 236.78 241.32 3562600
14   1/4/2017 241.44 243.32 240.03 243.13 2728700
15   1/3/2017 242.70 244.97 237.97 241.57 4384200
16 12/30/2016 238.51 240.50 237.40 239.45 2355500
17 12/29/2016 240.75 241.07 236.64 238.18 2619000
18 12/28/2016 243.69 244.50 240.44 240.65 3052900
19 12/27/2016 241.95 242.59 240.40 241.56 1998100
> summary(gs_data)
     Time                Open            High            Low       
 Length:19          Min.   :231.6   Min.   :233.2   Min.   :230.5  
 Class :character   1st Qu.:237.3   1st Qu.:239.1   1st Qu.:233.6  
 Mode  :character   Median :241.9   Median :243.2   Median :238.0  
                    Mean   :240.0   Mean   :241.7   Mean   :237.3  
                    3rd Qu.:242.9   3rd Qu.:244.8   3rd Qu.:240.9  
                    Max.   :245.4   Max.   :247.8   Max.   :242.9  
      Last           Volume       
 Min.   :231.4   Min.   :1998100  
 1st Qu.:235.0   1st Qu.:3037800  
 Median :241.3   Median :3562600  
 Mean   :239.5   Mean   :3879668  
 3rd Qu.:243.0   3rd Qu.:4416150  
 Max.   :245.8   Max.   :7590400  
></code></pre>



<h3>Point and Click Data Import</h3>



<p>If you'd prefer, R allows you to use a series of menu clicks to load data instead of 'reading' data from the command line as just described. To do this, go to the Workspace tab of RStudio's upper-right window, find the menu option to "Import Dataset," then choose a local text file or URL.</p>



<p>As data are imported via menu clicks, the R command that RStudio generated from your menu clicks will appear in your console. You may want to save that data-reading command into a script file if you're using this for significant analysis work, so that others -- or you -- can reproduce that work.</p>



<h3>Copying Data Snippets</h3>



<p>If you've got just a small section of data already in a table, say a spreadsheet, or a Web HTML table, you can control-C copy those data to your Windows clipboard and import them into R.</p>



<p>The command below handles clipboard data with a header row that's separated by tabs, and stores the data in a data frame (x):</p>



<pre class="wp-block-code"><code>x &lt;- read.table(file = "clipboard", sep="\t", header=TRUE)</code></pre>



<p>On a Mac, the pipe ("pbpaste") function will access data you've copied with command-c, so this will do the equivalent of the previous Windows command:</p>



<pre class="wp-block-code"><code>x &lt;- read.table(pipe("pbpaste"), sep="\t")</code></pre>

Import Data using read.table in R

<p>R has a data manipulation package called&nbsp;<code>data.table()</code>&nbsp;which is extensively used for data manipulation. Specially the package is very useful as a data cleaning tool for big data.</p>



<p>The&nbsp;<code>data.table</code>&nbsp;package comes with a function called&nbsp;<code>fread</code>&nbsp;which is a very efficient and speedy function for reading data from files. It is similar to&nbsp;<code>read.table</code>&nbsp;but faster and more convenient. The good thing is that it detects column types (colClasses) and separators (sep) automatically, however you can always specify them manually. Similarly, it can automatically detect the header names and apply to the columns. If the headers are not found, it will conveniently name them automatically.</p>



<h3>Installing and Loading data.table Package</h3>



<p>Before we can use the functions in <code>data.table</code> package, we need to install and load the package in R. We can do so using the <code>install.packages()</code> and <code>library()</code> command.</p>



<pre class="wp-block-code"><code>> install.packages("data.table")
> library(data.table)</code></pre>



<h3>Importing Data Using fread</h3>



<p>Once the package is loaded, we can use the <code>fread</code> function to read the data as shown below:</p>



<div class="wp-block-file"><a href="https://financetrain.com/wp-content/uploads/GS-Stock-Prices.txt">GS-Stock-Prices</a><a href="https://financetrain.com/wp-content/uploads/GS-Stock-Prices.txt" class="wp-block-file__button" download>Download</a></div>



<pre class="wp-block-code"><code>> mydata &lt;-fread("GS-Stock-Prices.txt")
> mydata
          Time   Open   High    Low   Last  Volume
 1:  1/24/2017 231.86 236.06 230.84 233.68 4448100
 2:  1/23/2017 231.86 233.75 230.75 232.67 3136100
 3:  1/20/2017 231.62 233.23 230.54 232.20 5211800
 4:  1/19/2017 234.07 234.75 230.62 231.41 4561800
 5:  1/18/2017 236.00 237.69 231.52 234.29 7590400
 6:  1/17/2017 242.94 243.06 235.61 235.74 6277100
 7:  1/13/2017 245.43 247.77 242.91 244.30 4186000
 8:  1/12/2017 245.06 245.47 241.57 243.84 4022300
 9:  1/11/2017 242.77 245.84 242.00 245.76 3532500
10:  1/10/2017 240.87 243.44 239.05 242.57 3432900
11:   1/9/2017 243.25 244.69 241.47 242.89 3022700
12:   1/6/2017 242.29 246.20 241.37 244.90 3591000
13:   1/5/2017 242.72 243.23 236.78 241.32 3562600
14:   1/4/2017 241.44 243.32 240.03 243.13 2728700
15:   1/3/2017 242.70 244.97 237.97 241.57 4384200
16: 12/30/2016 238.51 240.50 237.40 239.45 2355500
17: 12/29/2016 240.75 241.07 236.64 238.18 2619000
18: 12/28/2016 243.69 244.50 240.44 240.65 3052900
19: 12/27/2016 241.95 242.59 240.40 241.56 1998100
></code></pre>



<h3>fread - Drop and Select</h3>



<p>The&nbsp;<code>fread</code>&nbsp;command has two special arguments called&nbsp;<code>drop</code>&nbsp;and&nbsp;<code>select</code>&nbsp;which can be used to select or drop the variables/columns that we need to import.</p>



<p>In our dataset, we have six columns and we can use these arguments to select or drop the columns we want. Some examples below:</p>



<pre class="wp-block-code"><code># Drop columns 2 to 4. Import only Time, last and Volume
fread("GS-Stock-Prices.txt", drop = 2:4)
# Import only column 1 and 5, i.e., Time and Last price.
fread("GS-Stock-Prices.txt", select = c(1, 5))
# Drop 'Open', 'Last' and 'Volume' columns
fread("GS-Stock-Prices.txt", drop = c("Open", "Last", "Volumn")
#import only 'Time' and 'Last Price' columns
fread("GS-Stock-Prices.txt", select = c("Time", "Last")</code></pre>

Importing Data Using data.table – fread in R

<p>Microsoft Excel is another tool which is commonly used for data analysis. There are many R packages that can be used to interact with Excel. Once such package is&nbsp;<code>readxl</code>&nbsp;which was developed by Hadley Wickham.</p>



<p>An Excel file contains tabular data in multiple sheets. For this lesson, we have an excel sheet named <code>stock-prices.xlsx</code> (Downloadable above) that contains stock price data for December 2016 and November 2016 in two different sheets labeled - 'Dec-2016' and 'Nov-2016'.</p>



<div class="wp-block-file"><a href="https://financetrain.com/wp-content/uploads/stock-prices.xlsx">stock-prices</a><a href="https://financetrain.com/wp-content/uploads/stock-prices.xlsx" class="wp-block-file__button" download>Download</a></div>



<div class="wp-block-image"><figure class="aligncenter size-large"><img src="https://financetrain.com/wp-content/uploads/27483-stock-prices-excel.png" alt="" class="wp-image-27648"/></figure></div>



<p>It is generally a good idea to first explore your data by directly opening it in MS Excel before you import it in R. Now that we know what data we are importing, let's look at how we can import it using&nbsp;<code>readxl</code>.</p>



<h3><code>readxl()</code></h3>



<p>We will use two functions from&nbsp;<code>readxl</code>&nbsp;package:</p>



<ul><li><code>excel_sheets()</code>&nbsp;is used to get a list of sheets in the excel file</li><li><code>read_excel()</code>&nbsp;is used to actually import the data from an excel sheet</li></ul>



<p>These functions support both&nbsp;<code>.xls</code>&nbsp;and&nbsp;<code>.xlsx</code>&nbsp;file formats.</p>



<h3>Install and Load read.xl() Package</h3>



<p>The first thing we need to do is to install and load the desired package.</p>



<pre class="wp-block-code"><code>> install.packages("readxl")
> library(readxl)</code></pre>



<h3>Importing Data</h3>



<p>Now that the&nbsp;<code>readxl</code>&nbsp;package is loaded in our R session, we can use the functions to read the data.</p>



<p>We will first place the excel sheet into our working directory so that it is easy to refer to in the functions. The <code>dir()</code> function can be used to confirm that the file is in the working directory. The function <code>excel_sheets()</code> can be used to get a list of all sheets in our excel file. To use the function simply pass the file name to the function and it will return a character vector with a list of sheet names.</p>



<pre class="wp-block-code"><code>> dir()
&#91;1] "GS-Stock-Prices.txt" "stock-prices.xlsx"   "top-100-stocks.csv" 
> excel_sheets("stock-prices.xlsx")
&#91;1] "Dec-2016" "Nov-2016" "Sheet3"  
></code></pre>



<p>We can now use the <code>read_excel()</code> function to import the data from an excel sheet. If we just pass the excel file name to the function, it will import data from the first sheet by default. The imported data is in the form of a tibble. If we want to load data from a specific sheet we can specify the sheet name or the sheet index in the <code>sheet</code> argument.</p>



<pre class="wp-block-code"><code># The following function will load data from first sheet
read_excel("stock-prices.xlsx")
# The following two commands will load data from the second sheet
read_excel("stock-prices.xlsx", sheet=2)
read_excel("stock-prices.xlsx", sheet="Nov-2016")</code></pre>



<p>Once the data is imported, we can explore it using the standard R functions.</p>



<pre class="wp-block-code"><code>> stockdata &lt;- read_excel("stock-prices.xlsx", sheet="Nov-2016")
> str(stockdata)
Classes ‘tbl_df’, ‘tbl’ and 'data.frame':    21 obs. of  6 variables:
 $ Time  : POSIXct, format: "2016-11-01" "2016-11-02" ...
 $ Open  : num  179 177 177 176 179 ...
 $ High  : num  179 178 178 177 182 ...
 $ Low   : num  177 176 176 175 179 ...
 $ Last  : num  178 177 176 176 181 ...
 $ Volume: num  2900600 2104700 1972700 1846400 3338700 ...
> summary(stockdata)
      Time                          Open            High            Low       
 Min.   :2016-11-01 00:00:00   Min.   :176.3   Min.   :177.3   Min.   :174.7  
 1st Qu.:2016-11-08 00:00:00   1st Qu.:180.1   1st Qu.:182.7   1st Qu.:179.0  
 Median :2016-11-15 00:00:00   Median :206.3   Median :209.7   Median :204.8  
 Mean   :2016-11-14 21:42:51   Mean   :198.1   Mean   :201.3   Mean   :197.2  
 3rd Qu.:2016-11-22 00:00:00   3rd Qu.:210.0   3rd Qu.:211.9   3rd Qu.:209.6  
 Max.   :2016-11-30 00:00:00   Max.   :215.2   Max.   :220.8   Max.   :215.0  
      Last           Volume        
 Min.   :175.9   Min.   : 1846400  
 1st Qu.:181.9   1st Qu.: 2646100  
 Median :209.2   Median : 3338700  
 Mean   :200.1   Mean   : 4306352  
 3rd Qu.:211.1   3rd Qu.: 5276100  
 Max.   :219.3   Max.   :11345400  
></code></pre>



<h3>xlsx</h3>



<p>We can also use the <code>xlsx</code> package to access Excel files. The first row should contain variable/column names.</p>



<pre class="wp-block-code"><code># read in the first worksheet from the workbook stock-prices.xlsx
# first row contains variable names
library(xlsx)
stock_data &lt;- read.xlsx("stock-prices.xlsx", 1)
# read in the worksheet named Nov-2016
mydata &lt;- read.xlsx("stock-prices.xlsx", sheetName = "Nov-2016")</code></pre>



<p>The imported data is loaded as a data frame.</p>



<h3>gdata</h3>



<p>Another alternative package for working with Excel data in R is the&nbsp;<code>gdata</code>&nbsp;package which is maintained by Gregory Warnes.&nbsp;<code>gdata</code>&nbsp;is actually an entire suite of tools for data manipulation. The package makes handling and management of data very convenient in R. In the&nbsp;<code>gdata</code>&nbsp;package the function we use to read Excel data is the&nbsp;<code>read.xls()</code>&nbsp;function. By default it only supports XLS files. However, the support for&nbsp;<code>.xlsx</code>&nbsp;files can be incorporated by simply installing a driver.</p>



<p>Internally the&nbsp;<code>read.xls()</code>&nbsp;makes use of the read.table() function to import the data as a data frame. So, all the arguments for&nbsp;<code>read.table()</code>&nbsp;are available for us to use while importing data from Excel using&nbsp;<code>read.xls()</code>.</p>



<p>The <code>gdata</code> package requires perl interpreter to be installed on the system. Without that the function will not run.</p>



<p></p>

Importing Data from Excel  in R

<p>Many organizations use MS Excel for data analysis and financial modelling and it is likely that your company also expects you to perform and report data analysis through Excel. However, you may be more interested in performing the actual analysis using R tools and then presenting the results in Excel. The good news is that R has a package which does exactly this. It's called XLConnect. XLConnect is a package that allows for reading, writing and manipulating Microsoft Excel files from within R.</p>



<p>Using XL Connect, you can:</p>



<ul><li>Read an excel file with multiple sheets</li><li>Write an excel file with multiple sheets</li><li>Read/write formulas</li><li>Export the results of an analysis in an excel report</li></ul>



<p>XLConnect is different from other data importing packages because:</p>



<ul><li>It runs on all operating systems that support Java. XLConnect is written in Java and runs on Window, Linux, and Mac OS.</li><li>There’s nothing else to load. XLConnect doesn’t require any other libraries or software. If you have Java installed, it should work. It does not even require any installation of Microsoft Excel or any other special drivers to be able to read &amp; write Excel files. The only requirement is a recent version of a Java Runtime Environment (JRE).</li></ul>



<h3>Install and Load XLConnect</h3>



<p>The package can easily be installed from CRAN via <code>install.packages("XLConnect")</code>. Once installed, we can load it using the <code>require(XLConnect)</code>.</p>



<pre class="wp-block-code"><code>#Installed XLConnect
install.packages("XLConnect")
#load the package
require(XLConnect)</code></pre>



<p>You're now ready to use XLConnect!</p>



<h3>Example 1</h3>



<p>XLConnect can be used to create and manipulate excel files from scratch. In the below example, we will create a new excel workbook, add a new sheet to it and then load the tab-delimited stock price data about Goldman Sachs into it.</p>



<div class="wp-block-file"><a href="https://financetrain.com/wp-content/uploads/stock-prices.xlsx">stock-prices</a><a href="https://financetrain.com/wp-content/uploads/stock-prices.xlsx" class="wp-block-file__button" download>Download</a></div>



<div class="wp-block-file"><a href="https://financetrain.com/wp-content/uploads/GS-Stock-Prices.txt">GS-Stock-Prices</a><a href="https://financetrain.com/wp-content/uploads/GS-Stock-Prices.txt" class="wp-block-file__button" download>Download</a></div>



<pre class="wp-block-code"><code>#loading the package
require(XLConnect)
#create an Excel workbook. Both .xls and .xlsx file formats can be used.
mywb &lt;- loadWorkbook("StockPrices.xlsx", create = TRUE)
#Create a sheet called GSStock within the Excel workbook
createSheet(mywb, name = "GSStock")
#load data from the flat file into R as a data frame
gs_data &lt;- read.table("GS-Stock-Prices.txt", sep="\t", header=TRUE, stringsAsFactor=FALSE);
#write the gs_data data frame into GSStock sheet in the new workbook
writeWorksheet(mywb, gs_data, sheet = "GSStock", startRow = 1, startCol = 1)
#save the workbook to the corresponding Excel file and writes the file to disk.
saveWorkbook(mywb)</code></pre>



<h3>Functions Used</h3>



<ul><li>The&nbsp;<code>loadWorkbook()</code>&nbsp;function loads a Microsoft Excel workbook, so that it can then be further manipulated. Setting the create argument to&nbsp;<code>TRUE</code>&nbsp;will ensure the file will be created, if it does not exist yet. Both .xls and .xlsx file formats can be used.</li><li><code>createSheet()</code>&nbsp;creates a sheet of a chosen name in the workbook specified as the object argument.</li><li><code>writeWorksheet()</code>&nbsp;writes data into a worksheet (name or index specified as the sheet argument) of an Excel workbook (object). The&nbsp;<code>startRow</code>&nbsp;and&nbsp;<code>startCol</code>&nbsp;are both 1 by default, meaning that if they are not explicitly specified, the data will start being filled into the A1 cell of the worksheet</li><li><code>saveWorkbook()</code>&nbsp;saves a workbook to the corresponding Excel file and writes the file to disk. you can expect to find the file in the working directory.</li></ul>



<p>The newly created excel file is shown below:</p>



<div class="wp-block-image"><figure class="aligncenter size-large"><img src="https://financetrain.com/wp-content/uploads/27489-gs-stock-data.png" alt="" class="wp-image-27652"/></figure></div>



<h2>Example 2</h2>



<p>In the previous example, we used XLConnect functions to create a new excel file and load data into it. In this example, we will use XLConnect functions to read data from the sheets in a pre-existing excel file.</p>



<pre class="wp-block-code"><code>#Load the workbook in R
mywb = loadWorkbook("stock-prices.xlsx", create = FALSE)
#List all sheets in the workbook
getSheets(mywb)
#reading worksheets of an Excel workbook
data = readWorksheet(mywb, sheet = "Dec-2016")
#print data
data</code></pre>



<p>We first used the&nbsp;<code>loadWorkbook()</code>&nbsp;function to load the workbook into an R object. Then we got a list of sheets using the&nbsp;<code>getSheets()</code>&nbsp;function. Then we used&nbsp;<code>readWorksheet()</code>&nbsp;function to read the data from the sheet named 'Dec-2016'. The dat is loaded into a variable called 'data' which can then be used to perform analysis.</p>



<p>While our objective here was to introduce you to the XLConnect package, we will soon have a full course dedicated to it.</p>



<p>You can learn more about XLConnect using the <a href="https://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf" target="_blank" rel="noreferrer noopener nofollow">package vignette</a>, the numerous demos available via demo(package = "XLConnect") or browse through the <a href="https://cran.r-project.org/web/packages/XLConnect/XLConnect.pdf" target="_blank" rel="noreferrer noopener nofollow">comprehensive reference manual</a>.</p>

Using XLConnect in R Programming

<p>Most companies use relational databases to store their data and it is important for a data scientist to know how they can import data from these data bases into R. Some examples of popular relational databases include MySQL, MS SQL Server, and Oracle. Luckily for us, the R community has created R packages for almost every database that we can use to connect to and retrieve information from them.</p>



<p>In simplest terms, a relational database is one that presents information in tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related according to common keys or concepts, and the ability to retrieve related data from a table is the basis for the term relational database.</p>



<p>In this lesson, we will take an example of a MySQL database. We will connect to the database, fetch the list of tables in the database and then learn how to import data from these tables.</p>



<h2>Our Sample Database</h2>



<p>Our database is named <code>enwrap86_ftstockdata </code>and contains two tables:</p>



<p><strong>1. Stocks:</strong>&nbsp;This table contains stock information that does not change frequently, such as the company name and its earnings per share (EPS).</p>



<div class="wp-block-image"><figure class="aligncenter size-large"><img src="https://financetrain.com/wp-content/uploads/27509-DB-stocks.png" alt="" class="wp-image-27656"/></figure></div>



<p><strong>2. Prices:</strong>&nbsp;This table contains stock data, accumulated over a period of time. In this table, we record the following information for each stock: its daily opening and closing price per share, its daily high and low price per share, and the volume of shares traded.</p>



<div class="wp-block-image"><figure class="aligncenter size-large"><img src="https://financetrain.com/wp-content/uploads/27509-DB-prices.png" alt="" class="wp-image-27657"/></figure></div>



<h2>Install The Database Connector Package</h2>



<p>Depending on the database you're connecting to, you will need to install the appropriate package. Some examples of R packages are:</p>



<ul><li>MySQL - RMySQL</li><li>PostgresSQL - RPostgresSQL</li><li>Oracle Database - ROracle</li></ul>



<p>Since we're using MySQL database, we will install RMySQL package. How you interact with the database, that is which R functions you use to access and manipulate the data is specified in another R package called DBI. In more technical terms, DBI is the interface and RMySQL is the implementation. Installing a database package such as RMySQL will also automatically install the DBI package. The RMySQL package will be installed using the&nbsp;<code>install.packages("RMySQL")</code>&nbsp;command.</p>



<pre class="wp-block-code"><code>> install.packages("RMySQL")</code></pre>



<h2>Establish a Connection</h2>



<p>Now that you have the right packages, you can load the package in the R session and establish a connection with the database. Loading just the&nbsp;<code>DBI</code>&nbsp;package is sufficient.</p>



<p>We use&nbsp;<code>dbConnect()</code>&nbsp;to create a connection between the R session and a SQL database. The function takes many arguments in order to do so. The first argument is the DBIdriver object for the specific database. For MySQL databases, we will use RMySQL::MySQL(). Each driver will define what other arguments are required, e.g., "dbname" for the database name, "username", and "password".</p>



<p>For connecting to a remote MySQL database you'll specify the following arguments in dbConnect(): dbname, host, port, user and password. We have all these details for our database:</p>



<pre class="wp-block-preformatted">dbname = "enwrap86_ftstockdata"
host = "109.199.108.179"
port = 3306
user = "dbusername"
password = "*********"</pre>



<p>Using all these details, we can now create a&nbsp;<code>dbConnect()</code>&nbsp;call to connect to the database. We will store the connection into a variable called&nbsp;<code>con</code>.</p>



<pre class="wp-block-code"><code># Load the DBI package
library(DBI)
# Create a database connection
con &lt;- dbConnect(RMySQL::MySQL(), 
                 dbname = "enwrap86_ftstockdata", 
                 host = "109.199.108.179", 
                 port = 3306,
                 user = "enwrap86_dataadm",
                 password = "X+8NHCNrFJGR")</code></pre>



<p>Once this command is executed successfully, we will have a successful database connection.</p>



<h3>List DB Tables</h3>



<p>You can fetch a list of all tables in the database using the&nbsp;<code>dbListTables()</code>&nbsp;function. As an argument, you will pass the&nbsp;<code>con</code>&nbsp;connection object.</p>



<pre class="wp-block-code"><code>> dbListTables(con)
&#91;1] "Prices" "Stocks"</code></pre>



<p>As you can see, it returns the two table names that we had introduced earlier.</p>



<h3>Read Data from a Table</h3>



<p>We can now use the&nbsp;<code>dbReadTable()</code>&nbsp;function to read the data from these tables. The function takes two arguments 1) the connection object and 2) the table name.</p>



<p>In the following example, we import the data from the 'Stocks' table.</p>



<pre class="wp-block-code"><code>> dbReadTable(con,'Stocks')
   Symbol                  Name MarketCap     PE  EPS NetIncome Beta Dividend DivYield
1    TECK    Teck Resources Ltd        13 102.04 0.24        -1 1.49     0.08     0.31
2      CC      Chemours Company         3  22.56 0.97       -90 3.45     0.12     0.55
3     AKS AK Steel Holding Corp         3  21.33 0.51      -509 2.66     0.00     0.00
4     KEM            Kemet Corp       296  32.05 0.20       -54 3.40     0.00     0.00
5    HIIQ  Health Insurance Inn       253  19.77 0.87         1 0.67     0.00     0.00
6    NVDA    Nvidia Corporation        54  50.62 2.01       614 1.31     0.56     0.55
7     GGB           Gerdau S.A.         6  91.25 0.04        -1 2.33     0.01     0.33
8    GORO    Gold Resource Corp       306  54.10 0.10         3 0.32     0.02     0.37
9     EVI       Envirostarm Inc       142  52.78 0.27         2 0.92     0.00     0.00
10    SKY          Skyline Corp       123  34.93 0.42         2 2.44     0.00     0.00</code></pre>



<p>The resulting data is a data frame. We could store this in a variable and use it for further data manipulation.</p>



<p>In the following example, we import the "Prices" table from the&nbsp;<code>finance_stockdata</code>&nbsp;database and store the resulting data frame as&nbsp;<code>prices</code>. We can then chec the structure of the&nbsp;<code>prices</code>&nbsp;object or do anything we want to with this data.</p>



<pre class="wp-block-code"><code>> prices &lt;-dbReadTable(con,'Prices')
> str(prices)</code></pre>



<h3>Import All Tables</h3>



<p>We can also import all tables from the databse into a list using the&nbsp;<code>lapply()</code>&nbsp;function.</p>



<pre class="wp-block-code"><code># Store table names in 'stock_data'
stock_data &lt;- dbListTables(con)
# Import all tables into 'data_tables'
data_tables &lt;- lapply(stock_data, dbReadTable, conn = con)
# Print out tables
data_tables</code></pre>



<p>The first argument in&nbsp;<code>lapply()</code>&nbsp;is the table names. The second argument is the function you want to execute for each of these table names, in this case&nbsp;<code>dbReadTable</code>. The third argument is the connection object. You can read more about the&nbsp;<code>lapply()</code>&nbsp;function from R help using&nbsp;<code>help(lapply)</code>.</p>



<p>The DBI package also specifies functions to create new tables, add new data into these tables, and to remove tables. However, since our focus in on how to import data, we will not get into these other functions.</p>



<h2>Disconnect the Database</h2>



<p>Once you're done with your work of importing data and other database related activities, it is important to disconnect the database using the&nbsp;<code>dbDisconnect()</code>&nbsp;function. The function will execute and return TRUE if disconnection was successful.</p>



<pre class="wp-block-code"><code>> dbDisconnect(con)
&#91;1] TRUE</code></pre>



<p>If you try to print the 'con' object now, you will see that it is no longer available.</p>



<pre class="wp-block-code"><code>> con
Error in .local(dbObj, ...) : 
  internal error in RS_DBI_getConnection: corrupt connection handle</code></pre>



<p><em>Finance Train members can download the sql file for the database used in the lesson below:</em></p>



<div class="wp-block-file"><a href="https://financetrain.com/wp-content/uploads/finance_stockdata.sql_.zip">finance_stockdata.sql_</a><a href="https://financetrain.com/wp-content/uploads/finance_stockdata.sql_.zip" class="wp-block-file__button" download>Download</a></div>

Importing Data from a Database in R

<p>In the previous lesson we learned that we can use the&nbsp;<code>dbReadTable()</code>&nbsp;function to import a table into R. However, this can be complicated if our table contains millions of records and we are actually interested only in a sub-set of records that meet a certain condition. In such a scenario, we have two choices:</p>



<ol><li>Import the entire table as a data frame into R and then use R functions such as&nbsp;<code>subset()</code>&nbsp;to select the data that you're interested in. This approach can work but is a problem if the original data table is large.</li><li>We can use the&nbsp;<code>dbGetQuery()</code>&nbsp;function which accepts SQL statements specifying what data we want to fetch. For example, to perform a simple aggregate query, there is no need to pull a database table into R and apply an R function to the data frame. Instead, we issue a select statement and retrieve the results table as a data frame.</li></ol>



<h3>Example 1</h3>



<p>Let's consider our <code>enwrap86_ftstockdata</code> database which has two tables <code>Stocks</code> and <code>Prices</code>. In the following command we fetch all records from the table <code>Prices</code> just for the stock with symbol <code>TECK</code>.</p>



<pre class="wp-block-code"><code># Load the DBI package
library(DBI)
# Create a database connection
con &lt;- dbConnect(RMySQL::MySQL(), 
                 dbname = "enwrap86_ftstockdata", 
                 host = "109.199.108.179", 
                 port = 3306,
                 user = "enwrap86_dataadm",
                 password = "X+8NHCNrFJGR")
# Select all price data for the stock TECK
TECK_Prices &lt;- dbGetQuery(con, "SELECT * FROM Prices WHERE Symbol = 'TECK'")
# Print TECK Prices
TECK_Prices</code></pre>



<h3>Example 2</h3>



<p>We can perform all kinds of queries including things such as JOIN where we fetch data combined from multiple tables. For example, let's say we want to retrieve the name of the stock (Available in Stocks table) along with it's Maximum Last (closing) prices (From prices table). The common key between the two tables in the 'Symbol field so, we can create a join between the two tables and retrieve the desired data as shown below:</p>



<pre class="wp-block-code"><code># get the name of each stock along with it's maximum last price
Max_Prices &lt;- dbGetQuery(con, "SELECT s.Name,p.Last as MaxLast FROM Stocks AS s
    LEFT JOIN Prices AS p ON (p.Symbol = s.Symbol)
    WHERE p.Last = (
        SELECT MAX(Last) FROM Prices AS p2
            WHERE p2.Symbol = s.Symbol
    )")
# Print Max Prices
Max_Prices</code></pre>



<p>The result will look as follows:</p>



<pre class="wp-block-code"><code>> # Print Max Prices
> Max_Prices
                    Name MaxLast
1     Teck Resources Ltd   26.25
2       Chemours Company   25.59
3  AK Steel Holding Corp   11.11
4             Kemet Corp    7.12
5   Health Insurance Inn   20.70
6     Nvidia Corporation  111.77
7            Gerdau S.A.    4.05
8     Gold Resource Corp    5.45
9        Envirostarm Inc   15.25
10          Skyline Corp   15.43
></code></pre>



<p>You may find this query a bit intimidating, but I promise you we will go indepth into SQL in a course dedicated to mastering SQL queries.</p>



<h3>Send - Fetch - Clear</h3>



<p>When the result table is huge, we may not want to bring it into R in its entirety, but instead fetch the tuples in batches, possibly reducing the batches to simple summaries before requesting the next batch. Instead of dbGetQuery, we use dbSendQuery to fetch results in batches. The DBI package provides functions to keep track of whether the statement produces output, how many rows were affected by the operation, how many rows have been fetched (if statement is a query), and whether there are more rows to fetch.</p>



<p>In the example below, rather than using <code>dbReadTable</code> to pull over the entire <code>Prices</code> table, the <code>dbSendQuery</code> function is used to send the query to the database without retrieving the results. Then, the <code>dbFetch</code> function pulls over tuples in blocks. In this example, the first 50 tuples are retrieved, then the next 200, after which we determine that there are more results to be fetched (dbHasCompleted) and clear the results object (dbClearResult) without bringing over any more tuples from the MySQL database.</p>



<pre class="wp-block-code"><code>ps = dbSendQuery(con, "SELECT * FROM Prices;")
firstBatch = dbFetch(ps, n = 50)
secondBatch = dbFetch(ps, n = 30)
dbHasCompleted(ps)
dbClearResult(ps)</code></pre>



<p>Let's take a relook at what's happening here:</p>



<ul><li>The actual prices table contains 190 records. This is a small database so we don't have to fetch it in batches, but for this example, we will use the batch fetching principles which will work really well for large data sets.</li><li>The&nbsp;<code>dbSendQuery</code>&nbsp;function only submits and synchronously executes the SQL query to the database engine. It does not extract any records.</li><li>We then use the&nbsp;<code>dbFetch</code>&nbsp;function to actually fetch the records. We execute this function twice, first to fetch the first 50 records and then to fetch the next 30 records.</li><li>We then use the&nbsp;<code>dbHasCompleted</code>&nbsp;function to check if there still are more records to be fetched. It returns in TRUE/FALSE so you can use it in a loop or condition.</li><li>Finally, we must call&nbsp;<code>dbClearResult</code>&nbsp;function when you finish fetching the records you need.</li><li>In addition, the n = −1 assignment for the parameter specifies that all remaining tuples are to fetched. The fetch function converts each attribute in the result set to the corresponding type in R.</li><li>In addition, dbListResults(con) gives a list of all currently active result set objects for the connection con, and dbGetRowCount(rs) provides a status of the number of rows that have been fetched in the query.</li></ul>



<p>When finished, we free up resources by disconnecting the connection:</p>



<pre class="wp-block-code"><code>dbDisconnect(con)</code></pre>

SQL Queries from R

<p>While working as a data scientist, you will often deal with data stored on the web. Typically you will have a URl for the data and you will make an HTTP request to fetch the data. The data itself can come in many formats, such as:</p>



<ul><li>Files such as csv, delimited and excel files</li><li>Data available through APIs. The most popular format of data available through APIs is JSON</li><li>Data available in web tables inside web pages</li><li>And many more</li></ul>



<p>In this lesson, we will look at how to import data in R from files and from APIs. While there are many packages and techniques to get data from the web, we will introduce some of these to you.</p>



<h2>CSV Files</h2>



<p>Earlier we used&nbsp;<code>read.csv()</code>&nbsp;from the<code>utils</code>&nbsp;package to import CSV files stored locally. The&nbsp;<code>read.util()</code>&nbsp;function is equally efficient to load data from csv files stored on the internet.</p>



<p>For this lesson, we will use a csv files which contains fundamentals of the top 100 stocks in the US. The file is available at the following URL:</p>



<div class="wp-block-file"><a href="https://financetrain.com/wp-content/uploads/top-100-stocks-1.csv">top-100-stocks</a><a href="https://financetrain.com/wp-content/uploads/top-100-stocks-1.csv" class="wp-block-file__button" download>Download</a></div>



<p>If you paste this URL directly in a web browser, the browser will simply download the CSV file. To import the data from this file into R, you will need to pass the URL instead of the local file path as shown below:</p>



<pre class="wp-block-code"><code>#Import the csv file into variable 'top_stocks'
top_stocks &lt;- read.csv("http://financetrain.com/assets/course/data/top-100-stocks.csv")
#print top-stocks
top_stocks</code></pre>



<p>The result will be exactly the same as you would import it from your local system.</p>



<p>Notice that the URL for the data starts with http (Hyper Text Transfer protocol). HTTP is the language of the web. It's a set of rules about data exchange between computers. Hyper Text Transfer Protocol Secure (HTTPS) is the secure version of HTTP, the protocol over which data is sent between your browser and the website that you are connected to. The 'S' at the end of HTTPS stands for 'Secure'. It means all communications between your browser and the website are encrypted.&nbsp;<code>read.csv()</code>&nbsp;is capable of handling both HTTP and HTTPS urls.</p>



<h3>Tab Delimited Files</h3>



<p>Just like we used <code>read.csv()</code> to import CSV files, we had used read.table() to import files containing data with some other delimiter such as tab or by blank characters. The following example shows usage of read.table() for importing data from a file containing tab delimited historical price data for Goldman Sachs stock.</p>



<pre class="wp-block-code"><code>#Import the csv file into variable 'top_stocks'
GS_Stock_Prices &lt;- read.table("http://financetrain.com/assets/course/data/GS-Stock-Prices.txt", sep="\t", header=TRUE, stringsAsFactor=FALSE)
#print top-stocks
GS_Stock_Prices</code></pre>



<p>Also take a look at the&nbsp;<a href="https://github.com/tidyverse/readr" target="_blank" rel="noreferrer noopener">readr</a>&nbsp;package which has many utilities to import data from flat files and works well with files on the web.</p>



<h3>Excel Files</h3>



<p>Earlier we learned about reading data from excel files using&nbsp;<code>readxl</code>,&nbsp;<code>xlsx</code>&nbsp;and&nbsp;<code>gdata</code>&nbsp;packages which are pretty good.</p>



<p><code>gdata</code> can handle .xls files that are on the internet while <code>readxl</code> can't. <code>gdata</code> is good but it is not preferred by me because 1) it cannot handle .xlsx files and 2) installing the package also requires you to have a perl interpreter installed on your system, which makes it too much work. But if you need to, you can try it.</p>



<h3>APIs</h3>



<p>This is where the crux of interacting with web data lies. While loading csv, excel and other files is good and you will need to know about it, the majority of financial data that you will access from the web is available through APIs. In the rest of the lesson, we will focus on what APIs are and how to access data from them.</p>



<h4>What is an API</h4>



<p>An Application Programming Interface (API) is a set of commands, functions and protocols which programmers can use when building a software. It is a way in which different software components interact with each other.</p>



<p>An API can take many forms, however, we will focus specifically on Web APIs. A Web API as the name suggests, is an API over the web which can be accessed using HTTP protocol. A Web API can be used to pass a variety of information including structured and processed data which is what we are interested in. All major web applications provide access to their data through a Web API. For example, Facebook, Amazon, YouTube, and Twitter, all have very powerful APIs which can be used to fetch a variery of data from their servers.</p>



<p>There are also many Web APIs for financial data. One very popular service that provides access to financial data via a web API is Quandl. We will be using Quandl API to fetch some data in the examples below.</p>



<h3>JSON</h3>



<p>JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. JSON is a minimal, readable format for structuring data and is widely used in APIs. All of the data from Quandl is available in JSON format, where all the data is available in a nice and structured format.</p>



<figure class="wp-block-image size-large"><img src="https://financetrain.com/wp-content/uploads/47515-json.png" alt="" class="wp-image-27670"/></figure>



<h3>jsonlite</h3>



<p>To convert json into an R data structure, we can use an R package called jsonlite.&nbsp;<code>jsonlite</code>&nbsp;is a fast JSON parser and generator optimized for statistical data and the web. The package offers flexible, robust, high performance tools for working with JSON in R and is particularly powerful for building pipelines and interacting with a web API.</p>



<p><strong>Installing and Loading jsonlite</strong></p>



<p>The first thing we need to do is to install and load the <code>jsonlite</code> package.</p>



<pre class="wp-block-code"><code>> install.packages("jsonlite")
> library(jsonlite)</code></pre>



<p><strong>Importing JSON Data</strong></p>



<p>Once we have the <code>jsonlite</code> package installed, we are ready to import json from any Web API. For this example, we have taken a dataset from the Quandl API. It is a JSON file containing Daily Bitcoin exchange rate (BTC vs. EUR) on Kraken. The URL for the API request is as follows:</p>



<pre class="wp-block-code"><code>https://www.quandl.com/api/v3/datasets/BCHARTS/KRAKENEUR.json?api_key=&lt;your-own-api-key></code></pre>



<p>You can try pasting this URL in your web browser to view the raw JSON file. Quandl API requires an API key to access the data. Notice that I have used an API key in the above URL - <code>api_key=AKENEUR.json?api_key=&lt;your-own-api-key></code>. You can create a free account on the Quandl website to get your own api key which you can use in all your API calls.</p>



<p>We can use the <code>fromJSON</code> function from the <code>jsonlite</code> package for importing the JSON file. Simply pass the URL as an argument in this function and it will download the data and convert it into a named R list.</p>



<pre class="wp-block-code"><code>> quandl_data &lt;- fromJSON("https://www.quandl.com/api/v3/datasets/BCHARTS/KRAKENEUR.json?api_key=&lt;your-own-api-key>")
> str(quandl_data)
List of 1
 $ dataset:List of 21
  ..$ id                   : int 11320841
  ..$ dataset_code         : chr "KRAKENEUR"
  ..$ database_code        : chr "BCHARTS"
  ..$ name                 : chr "Bitcoin Markets (krakenEUR)"
  ..$ description          : chr "Daily Bitcoin exchange rate (BTC vs. EUR) on Kraken. Updated daily at 6:00pm EST. Data is sourced from http://www.bitcoincharts"| __truncated__
  ..$ refreshed_at         : chr "2017-02-02T00:20:03.221Z"
  ..$ newest_available_date: chr "2017-02-02"
  ..$ oldest_available_date: chr "2014-01-08"
  ..$ column_names         : chr &#91;1:8] "Date" "Open" "High" "Low" ...
  ..$ frequency            : chr "daily"
  ..$ type                 : chr "Time Series"
  ..$ premium              : logi FALSE
  ..$ limit                : NULL
  ..$ transform            : NULL
  ..$ column_index         : NULL
  ..$ start_date           : chr "2014-01-08"
  ..$ end_date             : chr "2017-02-02"
  ..$ data                 : chr &#91;1:1122, 1:8] "2017-02-02" "2017-02-01" "2017-01-31" "2017-01-30" ...
  ..$ collapse             : NULL
  ..$ order                : NULL
  ..$ database_id          : int 8431</code></pre>



<p>Now that you have all the data in an R list, you are ready to use it in your data analysis. As you can see, at the top level, everything is in a list named <code>dataset</code> which contains 21 elements. As an example, to retrieve the description of the data, we can use the following command:</p>



<pre class="wp-block-code"><code>> # Get the description of the data
> quandl_data$dataset$description
&#91;1] "Daily Bitcoin exchange rate (BTC vs. EUR) on Kraken. Updated daily at 6:00pm EST. Data is sourced from http://www.bitcoincharts.com . For other Bitcoin data and charts see our Bitcoin markets page (http://www.quandl.com/markets/bitcoin-data)."</code></pre>



<p>Similarly we can retrieve other pieces of information, for example,&nbsp;<code>quandl_data$dataset$data</code>&nbsp;contains the actual data we are interested in.</p>



<p>The <code>jsonlite</code> package is capable of many other things, which we will explore in detail as we learn more data science. You can also learn more about jsonlite from this <a href="https://cran.r-project.org/web/packages/jsonlite/vignettes/json-aaquickstart.html" target="_blank" rel="noreferrer noopener nofollow">vignette</a>. I also suggest that you familiarize yourself with the JSON format. <a href="https://json.org/example.html" target="_blank" rel="noreferrer noopener nofollow">Here</a> are some examples.</p>

Importing Data from Web in R

R Programming for Data Science

This frontmatter description will appear below the title

R Programming for Data Science

Lessons

Free Guides - Getting Started with R and Python

Accelerate your finance career with cutting-edge data skills.