Управление фреймами данных R с помощью SQL

Опубликовано: 30 Ноября, 2021

Управление фреймами данных в R Программирование с использованием SQL можно легко выполнить с sqldf пакета sqldf. Этот пакет в R предоставляет механизм, который позволяет манипулировать фреймами данных с помощью SQL, а также помогает подключаться к ограниченному количеству баз данных. Пакет sqldf в R в основном используется для выполнения команд или операторов SQL в фреймах данных. Можно просто указать оператор SQL, используя имена фреймов данных вместо имен таблиц в R, и тогда произойдет следующее:

  • Создается база данных с правильной схемой или макетом таблицы.
  • Фреймы данных загружаются в созданную базу данных автоматически.
  • Выполняется конкретный оператор или команда SQL.
  • Результат возвращается в R, и
  • Автоматически удаляется база данных.

Это делает существование базы данных довольно прозрачным. Этот метод может привести к более быстрому вычислению R. Результат получается с использованием некоторой эвристики, чтобы определить класс, который должен быть присвоен каждому столбцу результирующего кадра данных.

Работа с sqldf в R

Несколько операций SQL можно выполнить в R с помощью пакета sqldf. Воспользуемся двумя CSV-файлами из данных Highway.

  • crashes.csv, который содержит год, дорогу, N_Crashes и объем.
  • road.csv, который содержит Road, District и Length.

Для работы с пакетом sqldf сначала установите его следующим образом:

 install.packages ("sqldf")

После правильной установки включите пакет в сценарий R следующим образом:

библиотека (sqldf)

Теперь загрузите данные в скрипт. Для этого измените текущий каталог на каталог, содержащий файлы csv crashes.csv и Road.csv, используя setwd() .
Пример:

# 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)

Выход:

  Год Дорога N_Crashes Объем
1 1991 Межгосударственный 65 25 40000
2 1992 Межгосударственный 65 37 41000
3 1993 Межгосударственный 65 45 45000
4 1994 Межгосударственный 65 46 45600
5 1995 Межгосударственный 65 46 49000
6 1996 Межгосударственный 65 59 51000

    Год Дорога N_Crashes Объем
105 2007 Межгосударственный 275 32 21900
106 2008 Межгосударственный 275 21 21850
107 2009 Межгосударственный 275 25 22100
108 Межгосударственный транспорт 2010 г. 275 24 21500
109 2011 Межгосударственный 275 23 20300
110 2012 Межгосударственный 275 22 21200

           Длина дорожного района
1 Межгосударственный 65 Гринфилд 262
2 Межгосударственный 70 Vincennes 156
3 US-36 Crawfordsville 139
4 US-40 Гринфилд 150
5 US-52 Crawfordsville 172

Теперь выполните любую операцию SQL с этими данными, используя sqldf() пакета sqldf.

Объединение и объединение фреймов данных

Самая распространенная операция SQL - это операция соединения . Можно выполнить левое соединение и внутреннее соединение с помощью sqldf() . В настоящее время sqldf() не поддерживает все операции внешнего и правого соединения. Наряду с пакетом sqldf нам нужно включить пакет tcltk

Пример 1: выполнение операции левого соединения

# 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)

Выход:

  Год Дорога N_Crashes Объем Район Длина
1 1991 Межгосударственный 65 25 40000 Гринфилд 262
2 1992 Межгосударственный 65 37 41000 Гринфилд 262
3 1993 Межгосударственный 65 45 45000 Гринфилд 262
4 1994 Межгосударственный 65 46 45600 Гринфилд 262
5 1995 Межгосударственный 65 46 49000 Гринфилд 262
6 1996 Межгосударственный 65 59 51000 Гринфилд 262

    Год Дорога N_Crashes Объем Район Длина
105 2007 Межгосударственный 275 32 21900 <NA> NA
106 Межгосударственный транспорт 2008 г. 275 21 21850 <NA> NA
107 2009 Межгосударственный 275 25 22100 <NA> NA
108 Межгосударственный транспорт 2010 г. 275 24 21500 <NA> NA
109 2011 Межгосударственный 275 23 20300 <NA> NA
110 2012 Межгосударственный 275 22 21200 <NA> NA

Объяснение:
Crashes_join_roads - это новый фрейм данных, созданный оператором sqldf, в котором хранится результат операции соединения. sqldf() требует как минимум строкового символа вместе с операцией SQL. Параметр stringsAsFactors используется для присвоения символьного класса категориальным данным вместо класса факторов.

Пример 2: выполнение внутреннего соединения

# 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)

Выход:

 
  Год Дорога N_Crashes Объем Район Длина
1 1991 Межгосударственный 65 25 40000 Гринфилд 262
2 1992 Межгосударственный 65 37 41000 Гринфилд 262
3 1993 Межгосударственный 65 45 45000 Гринфилд 262
4 1994 Межгосударственный 65 46 45600 Гринфилд 262
5 1995 Межгосударственный 65 46 49000 Гринфилд 262
6 1996 Межгосударственный 65 59 51000 Гринфилд 262

   Год Дорога N_Crashes Объем Район Длина
83 2007 US-36 49 24000 Крофордсвилль 139
84 2008 US-36 52 24500 Крофордсвилль 139
85 2009 US-36 55 24700 Crawfordsville 139
86 2010 US-36 35 23000 Крофордсвилль 139
87 2011 US-36 33 21000 Крофордсвилль 139
88 2012 US-36 31 20500 Крофордсвилль 139

Здесь в итоговом фрейме данных сохраняются только совпадающие строки.

Теперь посмотрим, как работает функция merge() В R операция слияния может выполнять левое соединение, правое соединение, внутреннее соединение и полное внешнее соединение, в отличие от функции sqldf() . Кроме того, можно легко выполнить эквивалентную операцию, такую как sqldf() с помощью операции merge()

Пример 3:

# 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)

Выход:

 
     Год дороги N_Crashes Объем Район Длина
1 Межгосударственный 275 1994 21 21200 <NA> NA
2 Межгосударственный 275 1995 28 23200 <NA> NA
3 Межгосударственный 275 1996 22 20000 <NA> NA
4 Межгосударственный 275 1997 27 18000 <NA> NA
5 Межгосударственный 275 1998 21 19500 <NA> NA
6 Межгосударственный 275 1999 22 21000 <NA> NA

     Год дороги N_Crashes Объем Район Длина
105 US-40 2003 94 55200 Гринфилд 150
106 US-40 2004 25 55300 Гринфилд 150
107 US-40 2009 67 65000 Гринфилд 150
108 US-40 2010 102 67000 Гринфилд 150
109 US-40 2011 87 67500 Гринфилд 150
110 US-40 2012 32 67500 Гринфилд 150

Мы увидим, что строки в результирующих фреймах данных переупорядочиваются, когда мы используем функцию merge()

Использование предложения where

R может выполнять те же операции, что и SQL. Следовательно, чтобы использовать оператор SQL для включения любого условия, используйте предложение where .

Пример:
Давайте посмотрим, как выполнить внутреннее соединение, используя комбинацию операции слияния и операции подмножества, включив в запрос предложение where.

# 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)

Выход:

  Год Дорога N_Crashes Объем Район Длина
1 1991 US-40 46 21000 Гринфилд 150
2 1992 US-40 101 21500 Гринфилд 150
3 1993 США-40 76 23000 Гринфилд 150
4 1994 США-40 72 21000 Гринфилд 150
5 1995 US-40 75 24000 Гринфилд 150
6 1996 US-40 136 23500 Гринфилд 150

   Год Дорога N_Crashes Объем Район Длина
17 2007 US-40 45 59500 Гринфилд 150
18 2008 США-40 23 61000 Гринфилд 150
19 2009 US-40 67 65000 Гринфилд 150
20 2010 US-40 102 67000 Гринфилд 150
21 2011 US-40 87 67500 Гринфилд 150
22 2012 US-40 32 67500 Гринфилд 150

Агрегатные функции

В пакете sqldf агрегированные операции могут выполняться с помощью предложения group by .

Пример:

# 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)

Выход:

      Дорога Mean_Crashes
1 Межгосударственный 275 24,95455
2 Межгосударственный 65 107,81818
3 Межгосударственный 70 65.18182
4 US-36 48.00000
5 US-40 68.68182

sqldf() можно использовать для выполнения определенных операций с данными. Чтобы преодолеть эти ограничения, используйте пакет plyr в сценарии R. Пакет plyr Hadley Wickham можно использовать для выполнения сложных вычислений и манипуляций с данными. Посмотрим, как это работает.

Пример:

# 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))
)

Выход:

 
     Среднее значение дороги_ Сбои Q1_ Сбои Q3_ Сбои Среднее значение_ Сбои
1 Межгосударственный 65 107,8 1818 63,25 140,25 108,5
2 Межгосударственный 70 65,18182 52,00 75,50 66,5
3 US-36 48,00000 42,00 57,25 47,0
4 США-40 68,68182 45,25 90,75 70,0
SQL R язык

РЕКОМЕНДУЕМЫЕ СТАТЬИ

График переменной важности с использованием пакета случайного леса в R
21 Февраля, 2023
Как писать в определенной строке или столбце Использование write.xlsx в R
21 Февраля, 2023
Функция browser() в R
21 Февраля, 2023
Р – класс S4
21 Февраля, 2023
Как преобразовать SQL в запрос LINQ?
20 Февраля, 2023
Визуализация кластеров с использованием Hull Plots в ggplot2 с использованием ggforce
20 Февраля, 2023
Microsoft Azure — TDE включен в базе данных SQL Azure
19 Февраля, 2023
Microsoft Azure — настройка Azure SQL
19 Февраля, 2023