{"id":343,"date":"2013-11-25T14:38:04","date_gmt":"2013-11-25T19:38:04","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=343"},"modified":"2013-11-25T14:38:04","modified_gmt":"2013-11-25T19:38:04","slug":"importing-data-into-r-from-different-sources","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2013\/11\/25\/importing-data-into-r-from-different-sources\/","title":{"rendered":"Importing Data Into R from Different Sources"},"content":{"rendered":"

I have found that I get data from many different sources. \u00a0These sources range from simple .csv files to more complex relational databases, to structure XML or JSON files. \u00a0I have compiled the different approaches that one can use to easily access these datasets.<\/p>\n

Local Column Delimited Files<\/strong><\/p>\n

This is probably the most common and easiest approach to load data into R. \u00a0It simply requires one line to do everything that is needed to set up the data. \u00a0Then a couple additional lines to tidy up the dataset.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
file <- <\/code>\"c:\\\\my_folder\\\\my_file.txt\"<\/code><\/div>\n
raw_data <- read.csv(file, sep=<\/code>\",\"<\/code>); \u00a0##<\/code>'sep'<\/code> can be a number of options including \\t for tab delimited<\/code><\/div>\n
names(raw_data) <- c(<\/code>\"VAR1\"<\/code>,<\/code>\"VAR2\"<\/code>,<\/code>\"RESPONSE1\"<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Text File From the Internet<\/strong><\/p>\n

I find this very useful when I need to get datasets from a Web site. \u00a0This is particularly useful if I need to rerun the script and the Web site continually updates their data. \u00a0This save me from having to download the dataset into a csv file each time I need to run an update. \u00a0In this example I use one of my favorite data sources which comes from the National Data Buoy Center. \u00a0This example pulls data from a buoy (buoy #44025) off the coast of New Jersey. \u00a0Conveniently you can use the same read.csv() function that you would use if read the file from you own computer. \u00a0You simply replace the file location with the URL of the data.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
file <- <\/code>\"<a href=\"<\/code>http:\/\/www.ndbc.noaa.gov\/view_text_file.php?filename=<\/a><\/code>44025<\/code>h<\/code>2011<\/code>.txt.gz&dir=data\/historical\/stdmet\/<\/code>\">http:\/\/www.ndbc.noaa.gov\/view_text_file.php?filename=44025h2011.txt.gz&dir=data\/historical\/stdmet\/<\/a<\/a>>\"<\/code><\/div>\n
<\/div>\n
raw_data <- read.csv(file, header=T, skip=<\/code>1<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Files From Other Software<\/strong><\/p>\n

Often I will have Excel files, SPSS files, or SAS dataset set to me. \u00a0Once again I can either export the data as a csv file and then import using the\u00a0read.csv<\/em>\u00a0function. \u00a0However, taking that approach every time means that there is an additional step. \u00a0By adding unnecessary steps to a process increases the risk that the data might get corrupted due to human error. \u00a0Furthermore, if the data is updated from time to time then the data that you downloaded last week may not have the most current data.<\/p>\n

 <\/p>\n

SPSS<\/em><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
library(foreign)<\/code><\/div>\n
file <- <\/code>\"C:\\\\my_folder\\\\my_file.sav\"<\/code><\/div>\n
raw <- as.data.frame(read.spss(file))<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

 <\/p>\n

Microsoft Excel<\/em><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
library(XLConnect)<\/code><\/div>\n
<\/div>\n
file <- <\/code>\"C:\\\\my_folder\\\\my_file.xlsx\"<\/code><\/div>\n
raw_wb <- loadWorkbook(file, create=F)<\/code><\/div>\n
raw <- as.data.frame( readWorksheet(raw_wb, sheet=<\/code>'Sheet1'<\/code>) )<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Data From Relational Databases<\/strong><\/p>\n

There is the RMySQL library which is very useful. \u00a0However, I have generally been in the habit of using the RODBC library. \u00a0The reason for this is that I will often jump between databases (e.g. Oracle, MSSQL, MySQL). \u00a0By using the RODBC library I can keep all of my connections in one location and use the same functions regardless of the databases. \u00a0This example below will work on any standard SQL database. \u00a0You just need to make sure you set up an ODBC connection call (in this example) MY_DATABASE.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n<\/td>\n
\n
\n
library(RODBC)<\/code><\/div>\n
channel <- odbcConnect(<\/code>\"MY_DATABASE\"<\/code>, uid=<\/code>\"username\"<\/code>, pwd=<\/code>\"password\"<\/code>)<\/code><\/div>\n
<\/div>\n
raw <- sqlQuery(channel, <\/code>\"SELECT * FROM Table1\"<\/code>);<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Data from Non-Relational Databases<\/strong><\/p>\n

R has the capability to pull data from non-relational databases. \u00a0These include Hadoop (rhbase), Cassandra (RCassandra), MongoDB (rmongodb). \u00a0I personally have not used RCassandra but here is the\u00a0documentation<\/a>. \u00a0The example here uses MongoDB using an\u00a0example<\/a>\u00a0provided by MongoDB.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n
24<\/div>\n
25<\/div>\n
26<\/div>\n
27<\/div>\n
28<\/div>\n
29<\/div>\n
30<\/div>\n
31<\/div>\n
32<\/div>\n
33<\/div>\n
34<\/div>\n
35<\/div>\n
36<\/div>\n
37<\/div>\n
38<\/div>\n
39<\/div>\n
40<\/div>\n
41<\/div>\n
42<\/div>\n<\/td>\n
\n
\n
library(rmongodb)<\/code><\/div>\n
MyMongodb <- <\/code>\"test\"<\/code><\/div>\n
ns <- <\/code>\"articles\"<\/code><\/div>\n
mongo <- mongo.create(db=MyMmongodb)<\/code><\/div>\n
<\/div>\n
list.d <- mongo.bson.from.list(list(<\/code><\/div>\n
\"_id\"<\/code>=<\/code>\"wes\"<\/code>,<\/code><\/div>\n
name=list(first=<\/code>\"Wesley\"<\/code>, last=<\/code>\"\"<\/code>),<\/code><\/div>\n
sex=<\/code>\"M\"<\/code>,<\/code><\/div>\n
age=<\/code>40<\/code>,<\/code><\/div>\n
value=c(<\/code>\"7\"<\/code>, <\/code>\"5\"<\/code>,<\/code>\"8\"<\/code>,<\/code>\"2\"<\/code>)<\/code><\/div>\n
))<\/code><\/div>\n
mongo.insert(mongo, <\/code>\"test.MyPeople\"<\/code>, list.d)<\/code><\/div>\n
list.d<\/code>2<\/code> <- mongo.bson.from.list(list(<\/code><\/div>\n
\"_id\"<\/code>=<\/code>\"Article1\"<\/code>,<\/code><\/div>\n
when=mongo.timestamp.create(strptime(<\/code>\"2012-10-01 01:30:00\"<\/code>,<\/code><\/div>\n
\"%Y-%m-%d %H:%M:%s\"<\/code>), increment=<\/code>1<\/code>),<\/code><\/div>\n
author=<\/code>\"wes\"<\/code>,<\/code><\/div>\n
title=<\/code>\"Importing Data Into R from Different Sources\"<\/code>,<\/code><\/div>\n
text=<\/code>\"Provides R code on how to import data into R from different sources.\"<\/code>,<\/code><\/div>\n
tags=c(<\/code>\"R\"<\/code>, <\/code>\"MongoDB\"<\/code>, <\/code>\"Cassandra\"<\/code>,<\/code>\"MySQL\"<\/code>,<\/code>\"Excel\"<\/code>,<\/code>\"SPSS\"<\/code>),<\/code><\/div>\n
comments=list(<\/code><\/div>\n
list(<\/code><\/div>\n
who=<\/code>\"wes\"<\/code>,<\/code><\/div>\n
when=mongo.timestamp.create(strptime(<\/code>\"2012-10-01 01:35:00\"<\/code>,<\/code><\/div>\n
\"%Y-%m-%d %H:%M:%s\"<\/code>), increment=<\/code>1<\/code>),<\/code><\/div>\n
comment=<\/code>\"I'm open to comments or suggestions on other data sources to include.\"<\/code><\/div>\n
)<\/code><\/div>\n
)<\/code><\/div>\n
)<\/code><\/div>\n
)<\/code><\/div>\n
list.d<\/code>2<\/code><\/div>\n
mongo.insert(mongo, <\/code>\"test.MyArticles\"<\/code>, list.d<\/code>2<\/code>)<\/code><\/div>\n
<\/div>\n
res <- mongo.find(mongo, <\/code>\"test.MyArticles\"<\/code>, query=list(author=<\/code>\"wes\"<\/code>), fields=list(title=<\/code>1<\/code>L))<\/code><\/div>\n
out <- NULL<\/code><\/div>\n
while (mongo.cursor.next(res)){<\/code><\/div>\n
out <- c(out, list(mongo.bson.to.list(mongo.cursor.value(res))))<\/code><\/div>\n
<\/div>\n
}<\/code><\/div>\n
<\/div>\n
out<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

 <\/p>\n

Copied and Pasted Text<\/strong><\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n
24<\/div>\n
25<\/div>\n
26<\/div>\n
27<\/div>\n
28<\/div>\n
29<\/div>\n
30<\/div>\n
31<\/div>\n
32<\/div>\n
33<\/div>\n
34<\/div>\n
35<\/div>\n
36<\/div>\n
37<\/div>\n
38<\/div>\n
39<\/div>\n
40<\/div>\n
41<\/div>\n
42<\/div>\n
43<\/div>\n
44<\/div>\n
45<\/div>\n
46<\/div>\n
47<\/div>\n
48<\/div>\n
49<\/div>\n
50<\/div>\n
51<\/div>\n
52<\/div>\n
53<\/div>\n
54<\/div>\n
55<\/div>\n
56<\/div>\n
57<\/div>\n
58<\/div>\n
59<\/div>\n
60<\/div>\n<\/td>\n
\n
\n
raw_txt <- \"<\/code><\/div>\n
STATE READY TOTAL<\/code><\/div>\n
AL <\/code>36<\/code> 36<\/code><\/div>\n
AK <\/code>5<\/code> 8<\/code><\/div>\n
AZ <\/code>15<\/code> 16<\/code><\/div>\n
AR <\/code>21<\/code> 27<\/code><\/div>\n
CA <\/code>43<\/code> 43<\/code><\/div>\n
CT <\/code>56<\/code> 68<\/code><\/div>\n
DE <\/code>22<\/code> 22<\/code><\/div>\n
DC <\/code>7<\/code> 7<\/code><\/div>\n
FL <\/code>130<\/code> 132<\/code><\/div>\n
GA <\/code>53<\/code> 54<\/code><\/div>\n
HI <\/code>11<\/code> 16<\/code><\/div>\n
ID <\/code>11<\/code> 11<\/code><\/div>\n
IL <\/code>24<\/code> 24<\/code><\/div>\n
IN <\/code>65<\/code> 77<\/code><\/div>\n
IA <\/code>125<\/code> 130<\/code><\/div>\n
KS <\/code>22<\/code> 26<\/code><\/div>\n
KY <\/code>34<\/code> 34<\/code><\/div>\n
LA <\/code>27<\/code> 34<\/code><\/div>\n
ME <\/code>94<\/code> 96<\/code><\/div>\n
MD <\/code>25<\/code> 26<\/code><\/div>\n
MA <\/code>82<\/code> 92<\/code><\/div>\n
Mi <\/code>119<\/code> 126<\/code><\/div>\n
MN <\/code>69<\/code> 80<\/code><\/div>\n
MS <\/code>43<\/code> 43<\/code><\/div>\n
MO <\/code>74<\/code> 82<\/code><\/div>\n
MT <\/code>34<\/code> 40<\/code><\/div>\n
NE <\/code>9<\/code> 13<\/code><\/div>\n
NV <\/code>64<\/code> 64<\/code><\/div>\n
NM <\/code>120<\/code> 137<\/code><\/div>\n
NY <\/code>60<\/code> 62<\/code><\/div>\n
NJ <\/code>29<\/code> 33<\/code><\/div>\n
NH <\/code>44<\/code> 45<\/code><\/div>\n
ND <\/code>116<\/code> 135<\/code><\/div>\n
NC <\/code>29<\/code> 33<\/code><\/div>\n
OH <\/code>114<\/code> 130<\/code><\/div>\n
OK <\/code>19<\/code> 22<\/code><\/div>\n
PA <\/code>101<\/code> 131<\/code><\/div>\n
RI <\/code>32<\/code> 32<\/code><\/div>\n
Sc <\/code>35<\/code> 45<\/code><\/div>\n
SD <\/code>25<\/code> 25<\/code><\/div>\n
TN <\/code>30<\/code> 34<\/code><\/div>\n
TX <\/code>14<\/code> 25<\/code><\/div>\n
UT <\/code>11<\/code> 11<\/code><\/div>\n
VT <\/code>33<\/code> 49<\/code><\/div>\n
VA <\/code>108<\/code> 124<\/code><\/div>\n
WV <\/code>27<\/code> 36<\/code><\/div>\n
WI <\/code>122<\/code> 125<\/code><\/div>\n
WY <\/code>12<\/code> 14<\/code><\/div>\n
\"<\/code><\/div>\n
raw_data <- textConnection(raw_txt)<\/code><\/div>\n
raw <- read.table(raw_data, header=TRUE, comment.char=<\/code>\"#\"<\/code>, sep=<\/code>\"\"<\/code>)<\/code><\/div>\n
close.connection(raw_data)<\/code><\/div>\n
<\/div>\n
raw<\/code><\/div>\n
<\/div>\n
###Or the following line can be used<\/code><\/div>\n
<\/div>\n
raw <- read.table(header=TRUE, text=raw_txt)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

\u00a0Structured Local or Remote Data<\/strong><\/p>\n

One feature that I find quite useful is when there is a Web site with a table that I want to analyze. \u00a0R has the capability to read through the HTML and import the table that you want. \u00a0This example uses the\u00a0XML<\/em>\u00a0library and pulls down the population by country in the world. \u00a0Once the data is brought into R it may need to be cleaned up a bit removing unnecessary columns and other stray characters. \u00a0The examples here use remote data from other Web sites. \u00a0If the data is available as a local file then it can be imported in a similar fashion just using filename rather than the URL.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
library(XML)<\/code><\/div>\n
<\/div>\n
url<\/code> <- <\/code>\"http:\/\/en.wikipedia.org\/wiki\/List_of_countries_by_population<\/a>\"<\/code><\/div>\n
population = readHTMLTable(<\/code>url<\/code>, which=<\/code>3<\/code>)<\/code><\/div>\n
population<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Or you can use the feature to simple grab XML content. \u00a0I have found this particularly useful when I need geospatial data and need to get the latitude\/longitude of a location (this example uses Open Street Maps API provided by MapQuest). \u00a0This example obtains the results for the\u00a0coordinates\u00a0of the United States White House.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
url<\/code> <- <\/code>\"http:\/\/open.mapquestapi.com\/geocoding\/v1\/address?location=1600%20Pennsylvania%20Ave<\/a>,%20Washington,%20DC&outFormat=xml\"<\/code><\/div>\n
mygeo <- xmlToDataFrame(<\/code>url<\/code>)<\/code><\/div>\n
mygeo$result<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

An alternate approach is to use a JSON format. \u00a0I generally find that JSON is a better format and it can be readily used in most programming languages.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n<\/td>\n
\n
\n
library(rjson)<\/code><\/div>\n
url<\/code> <- <\/code>\"http:\/\/open.mapquestapi.com\/geocoding\/v1\/address?location=1600%20Pennsylvania%20Ave<\/a>,%20Washington,%20DC&outFormat=json\"<\/code><\/div>\n
<\/div>\n
raw_json <- scan(<\/code>url<\/code>, <\/code>\"\"<\/code>, sep=<\/code>\"\\n\"<\/code>)<\/code><\/div>\n
<\/div>\n
mygeo <- fromJSON(raw_json)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

I have found that I get data from many different sources. \u00a0These sources range from simple .csv files to more complex relational databases, to structure… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-343","post","type-post","status-publish","format-standard","hentry","category-r"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/343","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=343"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/343\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=343"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=343"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}