Manipulate R Data Frames Using SQL

R Data Frames Using SQL in detail

The sqldf package in R enables seamless manipulation of data frames using SQL commands. It provides an efficient way to work with structured data and can be used to interact with a limited range of databases. Instead of using table names as in traditional SQL, sqldf allows you to specify data frame names, making it easy to execute queries within R.

Key Operations of sqldf

When executing an SQL statement on a data frame using sqldf, the following steps occur:

• A temporary database is created with an appropriate schema.
• The data frames are automatically loaded into this database.
• The SQL query is executed.
• The resulting output is returned as a new data frame in R.
• The temporary database is automatically deleted after execution.

This approach optimizes calculations and improves efficiency by leveraging SQL operations.

install.packages("sqldf")
library(sqldf)

Loading Sample Data

For demonstration, we use two CSV files:

• accidents.csv: Contains Year, Highway, Crash_Count, and Traffic.
• routes.csv: Contains Highway, Region, and Distance.

Set the working directory and load the data:

setwd("C:/Users/User/Documents/R")
accidents <- read.csv("accidents.csv")
routes <- read.csv("routes.csv")

head(accidents)
tail(accidents)
print(routes)

Sample Output:

accidents.csv Data:

Year      Highway   Crash_Count Traffic
1 2000 Highway-101        30     50000
2 2001 Highway-101        35     52000
3 2002 Highway-101        40     54000

routes.csv Data:

Highway      Region    Distance
1 Highway-101  North Zone      200
2 Highway-405  South Zone      150

SQL Operations with sqldf

1. Performing a Left Join

library(tcltk)
join_query <- "SELECT accidents.*, routes.Region, routes.Distance
              FROM accidents
              LEFT JOIN routes ON accidents.Highway = routes.Highway"

accidents_routes <- sqldf(join_query, stringsAsFactors = FALSE)
head(accidents_routes)
tail(accidents_routes)

Sample Output:

Year     Highway   Crash_Count Traffic    Region    Distance
1 2000 Highway-101        30     50000 North Zone       200
2 2001 Highway-101        35     52000 North Zone       200
3 2002 Highway-101        40     54000 North Zone       200

2. Performing an Inner Join

inner_query <- "SELECT accidents.*, routes.Region, routes.Distance
                FROM accidents
                INNER JOIN routes ON accidents.Highway = routes.Highway"

accidents_routes_inner <- sqldf(inner_query, stringsAsFactors = FALSE)
head(accidents_routes_inner)
tail(accidents_routes_inner)

Sample Output:

Year     Highway   Crash_Count Traffic    Region    Distance
1 2000 Highway-101        30     50000 North Zone       200
2 2001 Highway-101        35     52000 North Zone       200

3. Using merge() for Joining Data Frames

The merge() function in R allows for various types of joins, including full outer joins and right joins.

accidents_merge_routes <- merge(accidents, routes, by = "Highway", all.x = TRUE)
head(accidents_merge_routes)
tail(accidents_merge_routes)

Sample Output:

Highway Year Crash_Count Traffic    Region    Distance
1 Highway-101 2000        30     50000 North Zone       200
2 Highway-101 2001        35     52000 North Zone       200

4. Filtering Data Using WHERE Clause

filter_query <- "SELECT * FROM accidents
                WHERE Highway = 'Highway-405'"

filtered_data <- sqldf(filter_query, stringsAsFactors = FALSE)
head(filtered_data)

Sample Output:

Year      Highway  Crash_Count Traffic
1 2000 Highway-405         50    60000
2 2001 Highway-405         55    62000

5. Using Aggregate Functions

The GROUP BY clause helps perform aggregate calculations.

aggregate_query <- "SELECT Highway, AVG(Crash_Count) AS Avg_Crashes
                    FROM accidents
                    GROUP BY Highway"

sqldf(aggregate_query)

Sample Output:

Highway    Avg_Crashes
1 Highway-101        35.5
2 Highway-405        52.5

6. Using plyr for Advanced Aggregation

For more advanced calculations, the plyr package is useful.

library(plyr)
ddply(accidents_merge_routes, .(Highway), function(X) {
  data.frame(
    Avg_Crashes = mean(X$Crash_Count),
    Q1_Crashes = quantile(X$Crash_Count, 0.25),
    Q3_Crashes = quantile(X$Crash_Count, 0.75),
    Median_Crashes = median(X$Crash_Count)
  )
})

Output:

Highway  Avg_Crashes  Q1_Crashes  Q3_Crashes  Median_Crashes
1 Highway-101      35.5       32.5       38.5           35
2 Highway-405      52.5       50.5       54.5           52.5