Управление фреймами данных R с помощью SQL
Управление фреймами данных в 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