La manipulación de tramas de datos en R La programación mediante SQL se puede realizar fácilmente mediante el paquete sqldf . Este paquete en R proporciona un mecanismo que permite la manipulación de marcos de datos con SQL y también ayuda a conectarse con un número limitado de bases de datos. El paquete sqldf en R se usa básicamente para ejecutar comandos SQL o declaraciones en marcos de datos. Uno puede simplemente especificar la declaración SQL usando nombres de marcos de datos en lugar de nombres de tablas en R, y luego suceden las siguientes cosas:
- Se crea una base de datos con el esquema adecuado o el diseño de la tabla.
- Los marcos de datos se cargan en la base de datos creada automáticamente
- Se ejecuta la instrucción o comando SQL específico
- El resultado se recupera en R, y
- Automáticamente la base de datos se elimina.
Esto hace que la existencia de la base de datos sea bastante transparente. Este método puede conducir a un cálculo de R más rápido. El resultado se obtiene utilizando algunas heurísticas para determinar la clase que se asignará a cada columna del marco de datos resultante.
Se pueden realizar un puñado de operaciones SQL en R usando el paquete sqldf. Usemos dos archivos csv de los datos de Highway.
- crashes.csv que contiene Year, Road, N_Crashes y Volume.
- roads.csv que contiene Road, District y Length.
Para trabajar con el paquete sqldf, primero instálelo de la siguiente manera:
install.packages("sqldf")
Después de una instalación adecuada, incluya el paquete en el script R de la siguiente manera:
library(sqldf)
Ahora cargue los datos en el script. Para hacerlo, cambie el directorio actual al directorio que contiene los archivos csv crashes.csv y road.csv usando la función setwd() .
Ejemplo:
r
# Importing required library
library(sqldf)
# Changing the directory
setwd("C:\\Users\\SHAONI\\Documents\\
R\\win-library")
# Reading the csv files
crashes <- read.csv("crashes.csv")
roads <- read.csv("roads.csv")
# Displaying the data in crashes.csv
head(crashes)
tail(crashes)
# Displaying the data in roads.csv
print(roads)
Producción:
Year Road N_Crashes Volume
1 1991 Interstate 65 25 40000
2 1992 Interstate 65 37 41000
3 1993 Interstate 65 45 45000
4 1994 Interstate 65 46 45600
5 1995 Interstate 65 46 49000
6 1996 Interstate 65 59 51000
Year Road N_Crashes Volume
105 2007 Interstate 275 32 21900
106 2008 Interstate 275 21 21850
107 2009 Interstate 275 25 22100
108 2010 Interstate 275 24 21500
109 2011 Interstate 275 23 20300
110 2012 Interstate 275 22 21200
Road District Length
1 Interstate 65 Greenfield 262
2 Interstate 70 Vincennes 156
3 US-36 Crawfordsville 139
4 US-40 Greenfield 150
5 US-52 Crawfordsville 172
Ahora realice cualquier operación SQL en estos datos utilizando la función sqldf() del paquete sqldf.
Unión y fusión de marcos de datos
La operación SQL más común es la operación de combinación . Uno puede realizar la combinación izquierda y la combinación interna usando sqldf() . Actualmente, sqldf() no admite las operaciones de combinación externa completa y combinación derecha. Junto con el paquete sqldf, debemos incluir el paquete tcltk .
Ejemplo 1: Realización de una operación de combinación izquierda
r
# Perform Left Join
# Importing required library
library(sqldf)
library(tcltk)
# Setting the directory
setwd("C:\\Users\\SHAONI\\Documents\\
R\\win-library")
# Reading the csv files
crashes <- read.csv("crashes.csv")
roads <- read.csv("roads.csv")
# Performing left join
join_string <- "select crashes.*,
roads.District,
roads.Length
from crashes
left join
roads on
crashes.Road = roads.Road"
# Resultant data frame
crashes_join_roads <- sqldf(join_string,
stringsAsFactors = FALSE)
head(crashes_join_roads)
tail(crashes_join_roads)
Producción:
Year Road N_Crashes Volume District Length
1 1991 Interstate 65 25 40000 Greenfield 262
2 1992 Interstate 65 37 41000 Greenfield 262
3 1993 Interstate 65 45 45000 Greenfield 262
4 1994 Interstate 65 46 45600 Greenfield 262
5 1995 Interstate 65 46 49000 Greenfield 262
6 1996 Interstate 65 59 51000 Greenfield 262
Year Road N_Crashes Volume District Length
105 2007 Interstate 275 32 21900 <NA> NA
106 2008 Interstate 275 21 21850 <NA> NA
107 2009 Interstate 275 25 22100 <NA> NA
108 2010 Interstate 275 24 21500 <NA> NA
109 2011 Interstate 275 23 20300 <NA> NA
110 2012 Interstate 275 22 21200 <NA> NA
Explicación:
crashes_join_roads es un nuevo marco de datos creado por la instrucción sqldf que almacena el resultado de la operación de unión. La función u operación sqldf() requiere al menos un carácter de string junto con la operación SQL. El parámetro stringsAsFactors se usa para asignar la clase de carácter a los datos categóricos en lugar de la clase de factor.
Ejemplo 2: Realización de unión interna
r
# Perform Inner Join
# Importing required package
library(sqldf)
library(tcltk)
# Selecting the proper directory
setwd("C:\\Users\\SHAONI\\Documents\\
R\\win-library")
# Reading the csv files
crashes <- read.csv("crashes.csv")
roads <- read.csv("roads.csv")
# Performing the inner join
join_string2 <- "select crashes.*,
roads.District,
roads.Length
from crashes
inner join
roads on
crashes.Road = roads.Road"
# The new data frame
crashes_join_roads2 <- sqldf(join_string2,
stringsAsFactors = FALSE)
head(crashes_join_roads2)
tail(crashes_join_roads2)
Producción:
Year Road N_Crashes Volume District Length 1 1991 Interstate 65 25 40000 Greenfield 262 2 1992 Interstate 65 37 41000 Greenfield 262 3 1993 Interstate 65 45 45000 Greenfield 262 4 1994 Interstate 65 46 45600 Greenfield 262 5 1995 Interstate 65 46 49000 Greenfield 262 6 1996 Interstate 65 59 51000 Greenfield 262 Year Road N_Crashes Volume District Length 83 2007 US-36 49 24000 Crawfordsville 139 84 2008 US-36 52 24500 Crawfordsville 139 85 2009 US-36 55 24700 Crawfordsville 139 86 2010 US-36 35 23000 Crawfordsville 139 87 2011 US-36 33 21000 Crawfordsville 139 88 2012 US-36 31 20500 Crawfordsville 139
Aquí solo las filas coincidentes se mantienen en el marco de datos resultante.
Ahora veamos cómo funciona la función merge() . En R, la operación de combinación es capaz de realizar combinación izquierda, combinación derecha, combinación interna y combinación externa completa, a diferencia de la función sqldf(). Además, uno puede realizar fácilmente la operación equivalente como sqldf() usando la operación merge().
Ejemplo 3:
r
# Perform Merge operation
# Import required library
library(sqldf)
library(tcltk)
setwd("C:\\Users\\SHAONI\\Documents\\
R\\win-library")
# Reading the two csv files
crashes <- read.csv("crashes.csv")
roads <- read.csv("roads.csv")
# Merge the two data frames
crashes_merge_roads2 <- merge(crashes,
roads,
by = c("Road"),
all.x = TRUE)
head(crashes_merge_roads2)
tail(crashes_merge_roads2)
Producción:
Road Year N_Crashes Volume District Length
1 Interstate 275 1994 21 21200 <NA> NA
2 Interstate 275 1995 28 23200 <NA> NA
3 Interstate 275 1996 22 20000 <NA> NA
4 Interstate 275 1997 27 18000 <NA> NA
5 Interstate 275 1998 21 19500 <NA> NA
6 Interstate 275 1999 22 21000 <NA> NA
Road Year N_Crashes Volume District Length
105 US-40 2003 94 55200 Greenfield 150
106 US-40 2004 25 55300 Greenfield 150
107 US-40 2009 67 65000 Greenfield 150
108 US-40 2010 102 67000 Greenfield 150
109 US-40 2011 87 67500 Greenfield 150
110 US-40 2012 32 67500 Greenfield 150
Veremos que las filas en los marcos de datos resultantes se reorganizan cuando usamos la función merge().
Uso de la cláusula where
R puede realizar las operaciones exactas como SQL. Por lo tanto, para usar una declaración SQL donde incluir cualquier condición, use la cláusula where .
Ejemplo:
veamos cómo realizar una combinación interna mediante la combinación de la operación de fusión y subconjunto al incluir la cláusula where en la consulta.
r
# Using where clause
# Importing required library
library(sqldf)
library(plyr)
library(tcltk)
setwd("C:\\Users\\SHAONI\\Documents\\
R\\win-library")
crashes <- read.csv("crashes.csv")
roads <- read.csv("roads.csv")
# Using the where clause
join_string2 <- "select crashes.*,
roads.District,
roads.Length
from crashes
inner join
roads on
crashes.Road = roads.Road
where
crashes.Road = 'US-40'"
crashes_join_roads4 <- sqldf(join_string2,
stringsAsFactors = FALSE)
head(crashes_join_roads4)
tail(crashes_join_roads4)
Producción:
Year Road N_Crashes Volume District Length 1 1991 US-40 46 21000 Greenfield 150 2 1992 US-40 101 21500 Greenfield 150 3 1993 US-40 76 23000 Greenfield 150 4 1994 US-40 72 21000 Greenfield 150 5 1995 US-40 75 24000 Greenfield 150 6 1996 US-40 136 23500 Greenfield 150 Year Road N_Crashes Volume District Length 17 2007 US-40 45 59500 Greenfield 150 18 2008 US-40 23 61000 Greenfield 150 19 2009 US-40 67 65000 Greenfield 150 20 2010 US-40 102 67000 Greenfield 150 21 2011 US-40 87 67500 Greenfield 150 22 2012 US-40 32 67500 Greenfield 150
Funciones agregadas
En el paquete sqldf, las operaciones agregadas se pueden realizar utilizando la cláusula group by .
Ejemplo:
r
# Perform aggregate operations
# Import required library
library(sqldf)
library(tcltk)
setwd("C:\\Users\\SHAONI\\Documents\\
R\\win-library")
crashes <- read.csv("crashes.csv")
roads <- read.csv("roads.csv")
# Group by clause
group_string <- "select crashes.Road,
avg(crashes.N_Crashes)
as Mean_Crashes
from crashes
left join
roads on
crashes.Road = roads.Road
group by 1"
sqldf(group_string)
Producción:
Road Mean_Crashes 1 Interstate 275 24.95455 2 Interstate 65 107.81818 3 Interstate 70 65.18182 4 US-36 48.00000 5 US-40 68.68182
La función sqldf() se puede usar para realizar ciertos tipos de manipulaciones de datos. Para superar estas limitaciones, use el paquete plyr en R Script. El paquete plyr de Hadley Wickham se puede utilizar para realizar cálculos avanzados y manipulaciones de datos. Vamos a ver cómo funciona.
Ejemplo:
r
# Importing required library
library(sqldf)
library(plyr)
library(tcltk)
setwd("C:\\Users\\SHAONI\\Documents\\
R\\win-library")
crashes <- read.csv("crashes.csv")
roads <- read.csv("roads.csv")
ddply(
crashes_merge_roads,
c("Road"),
function(X)
data.frame(
Mean_Crashes = mean(X$N_Crashes),
Q1_Crashes = quantile(X$N_Crashes, 0.25),
Q3_Crashes = quantile(X$N_Crashes, 0.75),
Median_Crashes = quantile(X$N_Crashes, 0.50))
)
Producción:
Road Mean_Crashes Q1_Crashes Q3_Crashes Median_Crashes
1 Interstate 65 107.81818 63.25 140.25 108.5
2 Interstate 70 65.18182 52.00 75.50 66.5
3 US-36 48.00000 42.00 57.25 47.0
4 US-40 68.68182 45.25 90.75 70.0