How to import data in R Programming?

Importing Data in R

Importing data in R programming means that we can read data from external files, write data to external files, and can access those files from outside the R environment. File formats like CSV, XML, xlsx, JSON, and web data can be imported into the R environment to read the data and perform data analysis, and also the data present in the R environment can be stored in external files in the same file formats.

Reading CSV Files

CSV (Comma Separated Values) is a text file in which the values in columns are separated by a comma.

For importing data in the R programming environment, we have to set our working directory with the setwd() function.

For example:

setwd("C:/Users/intellipaat/Desktop/BLOG/files")

To read a csv file, we use the in-built function read.csv() that outputs the data from the file as a data frame.

For example:

read.data <- read.csv("file1.csv")<br>
print(read.data)

Output:

Drive Innovation as a Data Science Leader

Dive into Data Science with Us

Explore Program

Analyzing a CSV File

#To print number of columns<br>
print(ncol(read.data))

Output:

[1] 5

#To print number of rows<br>
print(nrow(read.data))

Output:

[1] 8

#To print the range of salary packages<br>
range.sal <- range(read.data$empsalary)<br>
print(range.sal)

Output:

[1] 20000 36000

#To print the details of a person with the highest salary, we use the subset() function to extract variables and observations<br>
max.sal <- subset(read.data, empsalary == max(empsalary))<br>
print(max.sal)

Output:

#To print the details of all people working in Finance department<br>
fin.per <- subset(read.data, empdept == “Finance”)<br>
print(fin.per)

Output:

Writing to a CSV File

To write data to a CSV file, we use the write.csv() function. The output file is stored in the working directory of our R programming environment.
For example:

#To print the details of people having salary between 30000 and 40000 and store the results in a new file<br>
per.sal <- subset(read.data, empsalary >= "30000" & empsalary <= "40000")<br>
print(per.sal)

Output:

# Writing data into a new CSV file<br>
write.csv(per.sal,"output.csv")<br>
new.data <- read.csv("output.csv")<br>
print(new.data)

Output:

# To exclude the extra column X from the above file<br>
write.csv(per.sal,"output.csv", row.names = FALSE)<br>
new.data <- read.csv("output.csv")<br>
print(new.data)

Reading XML Files

XML (Extensible Markup Language) file shares both data and file format on the web, and elsewhere, using the ASCII text. Like an html file, it also contains markup tags, but the tags in an XML file describe the meaning of the data contained in the file rather than the structure of the page.

For importing data in R from XML files, we need to install the XML package, which can be done as follows:

install.packages("XML")

To read XML files, we use the in-built function xmlParse().

For example:

#To load required xml package to read XML files<br>
library("XML") <br>
#To load other required packages<br>
library("methods") <br>
#To give the input file name to the function<br>
newfile <- xmlParse(file = "file.xml") <br>
print(newfile)

Output:

<?xml version="1.0"?><br>
<RECORDS><br>
<EMPLOYEE><br>
<ID>1</ID><br>
<NAME>Sam</NAME><br>
<SALARY>32000</SALARY><br>
<STARTDATE>1/1/2001</STARTDATE><br>
<DEPT>HR</DEPT><br>
</EMPLOYEE><br>
<EMPLOYEE><br>
<ID>2</ID><br>
<NAME>Rob</NAME><br>
<SALARY>36000</SALARY><br>
<STARTDATE>9/3/2006</STARTDATE><br>
<DEPT>IT</DEPT><br>
</EMPLOYEE><br>
<EMPLOYEE><br>
<ID>3</ID><br>
<NAME>Max</NAME><br>
<SALARY>42000</SALARY><br>
<STARTDATE>1/5/2011</STARTDATE><br>
<DEPT>Sales</DEPT><br>
</EMPLOYEE><br>
<EMPLOYEE><br>
<ID>4</ID><br>
<NAME>Ivar</NAME><br>
<SALARY>50000</SALARY><br>
<STARTDATE>25/1/2001</STARTDATE><br>
<DEPT>Tech</DEPT><br>
</EMPLOYEE><br>
<EMPLOYEE><br>
<ID>5</ID><br>
<NAME>Robert</NAME><br>
<SALARY>25000</SALARY><br>
<STARTDATE>13/7/2015</STARTDATE><br>
<DEPT>Sales</DEPT><br>
</EMPLOYEE><br>
<EMPLOYEE><br>
<ID>6</ID><br>
<NAME>Leon</NAME><br>
<SALARY>57000</SALARY><br>
<STARTDATE>5/1/2000</STARTDATE><br>
<DEPT>IT</DEPT><br>
</EMPLOYEE><br>
<EMPLOYEE><br>
<ID>7</ID><br>
<NAME>Samuel</NAME><br>
<SALARY>45000</SALARY><br>
<STARTDATE>27/3/2003</STARTDATE><br>
<DEPT>Operations</DEPT><br>
</EMPLOYEE><br>
<EMPLOYEE><br>
<ID>8</ID><br>
<NAME>Jack</NAME><br>
<SALARY>24000</SALARY><br>
<STARTDATE>6/1/2016</STARTDATE><br>
<DEPT>Sales</DEPT><br>
</EMPLOYEE><br>
</RECORDS>

#To get the root node of xml file<br>
rootnode <- xmlRoot(newfile)<br>
#To get the number of nodes in the<br>
rootrootsize <- xmlSize(rootnode)<br>
print(rootsize)

Output:      [1] 8

#To print a specific node<br>
print(rootnode[1])

Output:

$EMPLOYEE<br>
<EMPLOYEE><br>
<ID>1</ID><br>
<NAME>Sam</NAME><br>
<SALARY>32000</SALARY><br>
<STARTDATE>1/1/2001</STARTDATE><br>
<DEPT>HR</DEPT><br>
</EMPLOYEE><br>
attr(,"class")<br>
[1] "XMLInternalNodeList" "XMLNodeList"

#To print elements of a particular node<br>
print(rootnode[[1]][[1]])<br>
print(rootnode[[1]][[3]])<br>
print(rootnode[[1]][[5]])

Output:

<ID>1</ID><br>
<SALARY>32000</SALARY><br>
<DEPT>HR</DEPT>

Shape your future in Data Science for free.

Achieve More with Free Data Science Training

Explore Program

Converting an XML to a Data Frame

To perform data analysis effectively after importing data in R, we convert the data in an XML file to a Data Frame. After converting, we can perform data manipulation and other operations as performed in a data frame.

For example:

library("XML")<br>
library("methods")<br>
#To convert the data in xml file to a data frame<br>
xmldataframe <- xmlToDataFrame("file.xml")<br>
print(xmldataframe)

Output:

Reading JSON Files

JSON (JavaScript Object Notation) file is used to exchange data between a web application and a server. They are text-based human-readable files and can be edited by a normal text editor.
Importing data in R from a JSON file requires the rjson package that can be installed as follows:

install.packages("rjson")

Now to read json files, we use the in-built function from JSON() which stores the data as a list.

For example:

#To load rjson package<br>
library("rjson")<br>
#To give the file name to the function<br>
newfile <- fromJSON(file = "file1.json")<br>
#To print the file<br>
print(newfile)

Output:

$ID<br>
[1] "1" "2" "3" "4" "5" "6" "7" "8"<br>
$Name<br>
[1] "Sam"    "Rob"    "Max"    "Robert" "Ivar"   "Leon"   "Samuel" "Ivar"<br>
$Salary<br>
[1] "32000" "27000" "35000" "25000" "37000" "41000" "36000" "51000"<br>
$StartDate<br>
[1] "1/1/2001"  "9/3/2003"  "1/5/2004"  "14/11/2007" "13/7/2015" "4/3/2007"<br>
[7] "27/3/2013"  "25/7/2000"<br>
$Dept<br>
[1] "IT"         "HR"         "Tech"       "HR"         "Sales"      "HR"<br>
[7] "Operations" "IT"

Converting a JSON File to a Data Frame

To convert JSON file to a Data Frame, we use the as.data.frame() function.
For example:

library("rjson")<br>
newfile <- fromJSON(file = "file1.json")<br>
#To convert a JSON file to a data frame<br>
jsondataframe <- as.data.frame(newfile)<br>
print(jsondataframe)

Output:

Reading Excel Files

Microsoft Excel is a very popular spreadsheet program that stores data in xls and xlsx format. We can read and write data, from and to Excel files using the readxl package in R.

<br>
To install the readxl package, run the following command<br>
install.packages("readxl")<br>
For importing data in R programming from an excel file, we use the read_excel() function that stores it as a data frame.<br>
newfile <- read_excel("sheet1.xlsx)<br>
print(newfile)<br>

Output:

Reading HTML Tables

HTML TABLES

You can import HTML tables into R with the following command.

# Assign your URL to `url`<br>
url <- ""
# Read the HTML table<br>
data_df <- readHTMLTable(url,<br>
which=3)

If the above-mentioned table shows an error, you can use the following.

The following command is a combination of RCurl and XML packages.

# Activate the libraries<br>
library(XML)<br>
library(RCurl)
# Assign your URL to `url`<br>
url <- "YourURL"
# Get the data<br>
urldata <- getURL(url)
# Read the HTML table<br>
data <- readHTMLTable(urldata,<br>
stringsAsFactors = FALSE)

Alternatively, you can use the rawToChar argument to convert raw objects as the following, to import data from HTML tables into R via httr package.

# Activate `httr`<br>
library(httr)
# Get the URL data<br>
urldata <- GET(url)
# Read the HTML table<br>
data <- readHTMLTable(rawToChar(urldata$content),<br>
stringsAsFactors = FALSE)

To read HTML tables from websites and retrieve data from them, we use the XML and RCurl packages in R programming.

To install XML and RCurl packages, run the following command:

install.packages("XML")<br>
install.packages("RCurl")

To load the packages, run the following command:

library("XML")<br>
library("RCurl")

For example, we will fetch the ‘Ease of Doing Business Index’ table from a URL using the readHTMLTable() function which stores it as a Data Frame.

#To fetch a table from any website paste the url<br>
url <- "https://en.wikipedia.org/wiki/Ease_of_doing_business_index#Ranking"<br>
tabs <- getURL(url)<br>
#To fetch the first table, if the webpage has more than one table, we use which = 1<br>
tabs <- readHTMLTable(tabs,which = 1, stringsAsFactors = F)<br>
head(tabs)

Output:

We use the str() function to analyze the structure of the data frame.
For example:

str(tabs)

Output:

'data.frame':  191 obs. of  16 variables:<br>
$ V1 : chr  "Classification" "Very Easy" "Very Easy" "Very Easy" ...<br>
$ V2 : chr  "Jurisdiction" "New Zealand" "Singapore" "Denmark" ...<br>
$ V3 : chr  "2019" "1" "2" "3" ...<br>
$ V4 : chr  "2018" "1" "2" "3" ...<br>
$ V5 : chr  "2017" "1" "2" "3" ...<br>
$ V6 : chr  "2016" "2" "1" "3" ...<br>
$ V7 : chr  "2015" "2" "1" "4" ...<br>
$ V8 : chr  "2014" "3" "1" "5" ...<br>
$ V9 : chr  "2013" "3" "1" "5" ...<br>
$ V10: chr  "2012" "3" "1" "5" ...<br>
$ V11: chr  "2011" "3" "1" "6" ...<br>
$ V12: chr  "2010" "2" "1" "6" ...<br>
$ V13: chr  "2009" "2" "1" "5" ...<br>
$ V14: chr  "2008" "2" "1" "5" ...<br>
$ V15: chr  "2007" "2" "1" "7" ...<br>
$ V16: chr  "2006" "1" "2" "8" ...

#To print rows from 5 to 10 and columns from 1 to 8<br>
T1 <- tabs[5:10, 1:8]<br>
head(T1)

Output:

#To find the position of India in the Table<br>
T1 <- subset(tabs,tabs$V2 == "India")<br>
head(T1)

Output:

1. SPSS FILES INTO R

To initiate the SPSS files import into R, you have to install the foreign package and run the read.spss() in the final step to proceed further. The following command will complete the import.

# Activate the `foreign` library<br>
library(foreign)
# Read the SPSS data<br>
mySPSSData <- read.spss("example.sav")

This works fine if you are currently using SPSS software.

The following command will come handy if you like to view the results in a data frame.

# Activate the `foreign` library<br>
library(foreign)
# Read the SPSS data<br>
mySPSSData <- read.spss("example.sav",<br>
to.data.frame=TRUE,<br>
use.value.labels=FALSE)

You can set the use.value.labels argument to FALSE, if you wish to not convert value labels variables to R factors. Also, to.data.frame argument can be set to TRUE to receive output in data frame display.

2. STATA FILES

You can import stata files to R via foreign package through the following command.

# Activate the `foreign` library<br>
library(foreign)
# Read Stata data into R<br>
mydata <- read.dta("")

3. SYSTAT FILES

You can import Systat files to R via foreign package through the following command.

# Activate the `foreign` library<br>
library(foreign)
# Read Systat data<br>
mydata <- read.systat("")

4. SAS FILES

To initiate the importing of SAS files into R, install the sas7bdat package and invoke the read.sas7bdat() function to proceed further.

# Activate the `sas7bdat` library<br>
library(sas7bdat)
# Read in the SAS data<br>
mySASData <- read.sas7bdat("example.sas7bdat")

Alternatively, if you are using foreign library, you can initiate the import process with read.ssd() and read.xport() functions accordingly.

5. MINITAB

To import minitab (.mto) files into R, you need to install the foreign package and use the function read.mtp() to initiate the process. This can be done through the following command.

# Activate the `foreign` library<br>
library(foreign)
# Read the Minitab data<br>
myMTPData <- read.mtp("example2.mtp")

6. RDA/ RDATA

You can import your .rdata file into R through the following command.

load(".RDA")

7. READ RELATIONAL AND NON-RELATIONAL DATABASES INTO R

The following are the steps to import data from relational databases by using MonetDB.

Step 1: Create a database by using the MonetDB daemon monetdbd and a new database called “voc”
Step 2: Install MonetBD.R from R shell

> install.packages("MonetDB.R")

Step 3: Load the MonetDB.R library

> library(MonetDB.R)<br>
Loading required package: DBI<br>
Loading required package: digest

Step 4: Create a connection to the database

> conn <- dbConnect(MonetDB.R(), host="localhost", dbname="demo", user="monetdb", password="monetdb")

Step 5: Create a database directly from R

> dbGetQuery(conn,"SELECT 'insert data'")<br>
single_value

Step 6: Repeat Step 4 multiple times.
Step 7: Install and load dplyr to manipulate datasets in R

> install.packages("dplyr")<br>
> library(dplyr)<br>
Attaching package: ‘dplyr’<br>
The following objects are masked from ‘package:stats’:<br>
filter, lag<br>
The following objects are masked from ‘package:base’:<br>
intersect, setdiff, setequal, union

Step 8: Make a connection to database for dplyr

> monetdb_conn <- src_monetdb("demo") Final step: Create database for future import in R > craftsmen <- tbl(monetdb_conn, "insert data")<br>
impotenten <- tbl(monetdb_conn, "insert data")<br>
invoices <- tbl(monetdb_conn, "insert data")<br>
passengers <- tbl(monetdb_conn, "insert data")<br>
seafarers <- tbl(monetdb_conn, "insert data")<br>
soldiers <- tbl(monetdb_conn, "insert data")<br>
total <- tbl(monetdb_conn, "insert data")<br>
voyages <- tbl(monetdb_conn, "insert data")

8. IMPORTING DATA FROM NON-RELATIONAL DATABASES

The following are the steps to import data from non-relational databases to R by using MongoDB.

Step 1: Install MongoDB.

import pandas as pandas<br>
import pymongo as pymongo
df = pandas.read_table('../data/csdata.txt')<br>
lst = [dict([(colname, row[i]) for i, colname in enumerate(df.columns)]) for row in df.values]<br>
for i in range(3):<br>
print lst[i]
con = pymongo.Connection('localhost', port = 27017)<br>
test = con.db.test<br>
test.drop()<br>
for i in lst:<br>
test.save(i)

Step 2: Using RMango, write the following command.

library(RMongo)<br>
mg1 <- mongoDbConnect('db')<br>
print(dbShowCollections(mg1))<br>
query <- dbGetQuery(mg1, 'test', "{'AGE': {'$lt': 10}, 'LIQ': {'$gte': 0.1}, 'IND5A': {'$ne': 1}}")<br>
data1 <- query[c('AGE', 'LIQ', 'IND5A')]<br>
summary(data1)

Step 3: You will receive the output as the following.

Loading required package: rJava<br>
Loading required package: methods<br>
Loading required package: RUnit<br>
[1] "system.indexes" "test"<br>
AGE LIQ IND5A<br>
Min. :6.000 Min. :0.1000 Min. :0<br>
1st Qu.:7.000 1st Qu.:0.1831 1st Qu.:0<br>
Median :8.000 Median :0.2970 Median :0<br>
Mean :7.963 Mean :0.3745 Mean :0<br>
3rd Qu.:9.000 3rd Qu.:0.4900 3rd Qu.:0<br>
Max. :9.000 Max. :1.0000 Max. :0

9. IMPORTING DATA THROUGH WEB SCRAPING

Step 1: Install the packages.

library(rvest)<br>
library(stringr)<br>
library(plyr)<br>
library(dplyr)<br>
library(ggvis)<br>
library(knitr)<br>
options(digits = 4)

Step 2: Using PhantomJS, command the following.

// scrape_techstars.js
var webPage = require('webpage');<br>
var page = webPage.create();
var fs = require('fs');<br>
var path = 'techstars.html'
page.open('http://www.techstars.com/companies/stats/', function (status) {<br>
var content = page.content;<br>
fs.write(path,content,'w')<br>
phantom.exit();<br>
});

Step 3: Use system() function.

# Let phantomJS scrape techstars, output is written to techstars.html<br>
system("./phantomjs scrape_techstars.js")

Step 4:

batches <- html("techstars.html") %>%<br>
html_nodes(".batch")
class(batches)<br>
[1] "XMLNodeSet"

Step 5:

batch_titles <- batches %>%<br>
html_nodes(".batch_class") %>%<br>
html_text()
batch_season <- str_extract(batch_titles, "(Fall|Spring|Winter|Summer)")<br>
batch_year <- str_extract(batch_titles, "([[:digit:]]{4})")<br>
# location info is everything in the batch title that is not year info or season info<br>
batch_location <- sub("\s+$", "",<br>
sub("([[:digit:]]{4})", "",<br>
sub("(Fall|Spring|Winter|Summer)","",batch_titles)))
# create data frame with batch info.<br>
batch_info <- data.frame(location = batch_location,<br>
year = batch_year,<br>
season = batch_season)
breakdown <- lapply(batches, function(x) {<br>
company_info <- x %>% html_nodes(".parent")<br>
companies_single_batch <- lapply(company_info, function(y){ as.list(gsub("\[\+\]\[\-\]\s", "", y %>%<br>
html_nodes("td") %>%<br>
html_text()))<br>
})<br>
df <- data.frame(matrix(unlist(companies_single_batch),<br>
nrow=length(companies_single_batch),<br>
byrow=T,<br>
dimnames = list(NULL, c("company","funding","status","hq"))))<br>
return(df)<br>
})
# Add batch info to breakdown<br>
batch_info_extended <- batch_info[rep(seq_len(nrow(batch_info)),<br>
sapply(breakdown, nrow)),]<br>
breakdown_merged <- rbind.fill(breakdown)
# Merge all information<br>
techstars <- tbl_df(cbind(breakdown_merged, batch_info_extended)) %>%<br>
mutate(funding = as.numeric(gsub(",","",gsub("\$","",funding))))

Step 6:

## Source: local data frame [535 x 7]<br>
##<br>
## company funding status hq location year season<br>
## 1 Accountable 110000 Active Fort Worth, TX Austin 2013 Fall<br>
## 2 Atlas 1180000 Active Austin, TX Austin 2013 Fall<br>
## 3 Embrace 110000 Failed Austin, TX Austin 2013 Fall<br>
## 4 Filament Labs 1490000 Active Austin, TX Austin 2013 Fall<br>
## 5 Fosbury 300000 Active Austin, TX Austin 2013 Fall<br>
## 6 Gone! 840000 Active San Francisco, CA Austin 2013 Fall<br>
## 7 MarketVibe 110000 Acquired Austin, TX Austin 2013 Fall<br>
## 8 Plum 1630000 Active Austin, TX Austin 2013 Fall<br>
## 9 ProtoExchange 110000 Active Austin, TX Austin 2013 Fall<br>
## 10 Testlio 1020000 Active Austin, TX Austin 2013 Fall<br>
## .. ... ... ... ... ... ... ...<br>
names(techstars)<br>
## [1] "company" "funding" "status" "hq" "location" "year"<br>
## [7] "season"

10. IMPORTING DATA THROUGH TM PACKAGE

You can initiate the import data through TM package by installing and activating it as follows.

text <- readLines("")<br>
And in the final step, write the following<br>
docs <- Corpus(VectorSource(text))

In this tutorial, we learned what importing data in R is, how to read files in different formats in R, and how to convert data from files to data frames for efficient data manipulation, and also check which package version is loaded in R. In the next session, we are going to talk about data manipulation in R. Enroll in the Data Science program today and learn to solve complex challenges.