Stop Thinking, Just Do!

Sung-Soo Kim's Blog

Perform SQL Selects on R Data Frames

tagsTags

27 September 2015


sqldf: Perform SQL Selects on R Data Frames

R is a high-level programming language used primarily for statistical computing and graphics.

Description: Manipulate R data frames using SQL.

library(sqldf) 

df <- data.frame(
  c(1, 'notBadMal',        'Malcom', 'Reynolds',  '2468-09-20'),
  c(2, 'warriorwoman2511', 'Zoe',    'Washburne', '2484-02-15'),
  c(3, 'veras-man',        'Jayne',   'Cobb',     'N/A')
)

# Underscores are commonly used in SQL.  Exception to the R convention of avoiding them for the
# sake of traceability
crew_members <- as.data.frame(t(df))

colnames(crew_members) <- c('id','user_name','first_name','last_name','birth_date')
rownames(crew_members) <- crew_members[,'id']

crew_members
class(crew_members)

sqldf('SELECT * FROM crew_members')

sqldf('SELECT user_name FROM crew_members ORDER BY user_name')

sqldf('SELECT user_name FROM crew_members WHERE id < 3 ORDER BY user_name')

sqlite

library('RSQLite')
con <- dbConnect(dbDriver("SQLite"), dbname=":memory:")

# DDL
rs <- dbSendQuery(con, "create table CREW_MEMBERS (id number, user_name string, first_name string, last_name string, birth_date DATE)")

# DML
dbSendQuery(con, "insert into CREW_MEMBERS values (1, 'notBadMal', 'Malcom', 'Reynolds', '2468-09-20')")
dbSendQuery(con, "insert into CREW_MEMBERS values (2, 'warriorwoman2511', 'Zoe', 'Washburne', '2484-02-15')")
dbSendQuery(con, "insert into CREW_MEMBERS values (3, 'veras-man', 'Jayne', 'Cobb', NULL)")

# SQL
rs <- dbSendQuery(con, "select * from CREW_MEMBERS")
fetch(rs)
dbClearResult(rs)

# sqldf is using SQLite behind the scenes.
# This does not rely on sqldf but is simple,
# basic SQL interaction with an (in-memory) 
# database
rs <- dbSendQuery(con, "SELECT user_name FROM CREW_MEMBERS ORDER BY user_name")
df <- fetch(rs)
dbClearResult(rs)

rs <- dbSendQuery(con, "SELECT user_name FROM CREW_MEMBERS WHERE id < 3 ORDER BY user_name")
df <- fetch(rs)
dbClearResult(rs)

comments powered by Disqus