This tutorial will show you how to install the R packages for working with Tabular Data Packages and demonstrate a very simple example of loading a Tabular Data Package from the web and pushing it directly into a local SQL database and send query to retrieve results.

Setup

For this tutorial, we will need the Data Package R package (datapackage.r).

devtools package is required to install the datapackage.r package from github.

And then install the development version of datapackage.r from github.

install.packages("datapackage.r")
# or install the development package
devtools::install_github("frictionlessdata/datapackage.r")

Load

You can start using the package by loading datapackage.r.

library(datapackage.r)

Reading Basic Metadata

In this case, we are using an example Tabular Data Package containing the periodic table stored on GitHub (datapackage.json, data.csv). This dataset includes the atomic number, symbol, element name, atomic mass, and the metallicity of the element. Here are the first five rows:

atomic.number symbol name atomic.mass metal.or.nonmetal.
1 H Hydrogen 1.00794 nonmetal
2 He Helium 4.002602 noble gas
3 Li Lithium 6.941 alkali metal
4 Be Beryllium 9.012182 alkaline earth metal
5 B Boron 10.811 metalloid

Data Packages can be loaded either from a local path or directly from the web.

## [1] TRUE

At the most basic level, Data Packages provide a standardized format for general metadata (for example, the dataset title, source, author, and/or description) about your dataset. Now that you have loaded this Data Package, you have access to this metadata using the metadata dict attribute. Note that these fields are optional and may not be specified for all Data Packages. For more information on which fields are supported, see the full Data Package standard.

## [1] "Periodic Table"

Reading Data

Now that you have loaded your Data Package, you can read its data. A Data Package can contain multiple files which are accessible via the resources attribute. The resources attribute is an array of objects containing information (e.g. path, schema, description) about each file in the package.

You can access the data in a given resource in the resources array by reading the data attribute.

You can further manipulate list objects in R by using purrr, rlist packages.

Loading into an SQL database

Tabular Data Packages contains schema information about its data using Table Schema. This means you can easily import your Data Package into the SQL backend of your choice. In this case, we are creating an SQLite database.

To create a new SQLite database and load the data into SQL we will need DBI package and RSQLite package, which contains SQLite (no external software is needed).

You can install and load them by using:

install.packages(c("DBI","RSQLite"))
library(DBI)
library(RSQLite)

To create a new SQLite database, you simply supply the filename to dbConnect():

dp.database <- dbConnect(RSQLite::SQLite(), "") # temporary database

We will use data.table package to convert the list object with the data to a data frame in order to copy them to database table.

You can easily copy an R data frame into a SQLite database with dbWriteTable():

## [1] "periodic_table_sql"

The data are already to the database.

We can further issue queries to hte database:

Return first 5 elements:

##   atomic number symbol      name atomic mass   metal or nonmetal?
## 1             1      H  Hydrogen    1.007940             nonmetal
## 2             2     He    Helium    4.002602            noble gas
## 3             3     Li   Lithium    6.941000         alkali metal
## 4             4     Be Beryllium    9.012182 alkaline earth metal
## 5             5      B     Boron   10.811000            metalloid

Return all elements with an atomic number of less than 10:

##   atomic number symbol      name atomic mass   metal or nonmetal?
## 1             1      H  Hydrogen    1.007940             nonmetal
## 2             2     He    Helium    4.002602            noble gas
## 3             3     Li   Lithium    6.941000         alkali metal
## 4             4     Be Beryllium    9.012182 alkaline earth metal
## 5             5      B     Boron   10.811000            metalloid
## 6             6      C    Carbon   12.010700             nonmetal
## 7             7      N  Nitrogen   14.006700             nonmetal
## 8             8      O    Oxygen   15.999400             nonmetal
## 9             9      F  Fluorine   18.998403              halogen

More about using databases, SQLite in R you can find in vignettes of DBI and SQLite packages.