#install.packages (c("RSQLite",
# "nycflights13",
# "DBI",
# "tidyverse"))
library(tidyverse)
## ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.2 ──
## ✔ ggplot2 3.3.6 ✔ purrr 0.3.4
## ✔ tibble 3.1.8 ✔ dplyr 1.0.10
## ✔ tidyr 1.2.1 ✔ stringr 1.4.1
## ✔ readr 2.1.2 ✔ forcats 0.5.2
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
library(DBI)
library(RSQLite)
library(nycflights13)
# SQLite database
con <- dbConnect(SQLite(), ":memory:")
copy_to(con, flights, "flights")
copy_to(con, airports, "airports")
copy_to(con, airlines, "airlines")
str(flights)
## tibble [336,776 × 19] (S3: tbl_df/tbl/data.frame)
## $ year : int [1:336776] 2013 2013 2013 2013 2013 2013 2013 2013 2013 2013 ...
## $ month : int [1:336776] 1 1 1 1 1 1 1 1 1 1 ...
## $ day : int [1:336776] 1 1 1 1 1 1 1 1 1 1 ...
## $ dep_time : int [1:336776] 517 533 542 544 554 554 555 557 557 558 ...
## $ sched_dep_time: int [1:336776] 515 529 540 545 600 558 600 600 600 600 ...
## $ dep_delay : num [1:336776] 2 4 2 -1 -6 -4 -5 -3 -3 -2 ...
## $ arr_time : int [1:336776] 830 850 923 1004 812 740 913 709 838 753 ...
## $ sched_arr_time: int [1:336776] 819 830 850 1022 837 728 854 723 846 745 ...
## $ arr_delay : num [1:336776] 11 20 33 -18 -25 12 19 -14 -8 8 ...
## $ carrier : chr [1:336776] "UA" "UA" "AA" "B6" ...
## $ flight : int [1:336776] 1545 1714 1141 725 461 1696 507 5708 79 301 ...
## $ tailnum : chr [1:336776] "N14228" "N24211" "N619AA" "N804JB" ...
## $ origin : chr [1:336776] "EWR" "LGA" "JFK" "JFK" ...
## $ dest : chr [1:336776] "IAH" "IAH" "MIA" "BQN" ...
## $ air_time : num [1:336776] 227 227 160 183 116 150 158 53 140 138 ...
## $ distance : num [1:336776] 1400 1416 1089 1576 762 ...
## $ hour : num [1:336776] 5 5 5 5 6 5 6 6 6 6 ...
## $ minute : num [1:336776] 15 29 40 45 0 58 0 0 0 0 ...
## $ time_hour : POSIXct[1:336776], format: "2013-01-01 05:00:00" "2013-01-01 05:00:00" ...
SELECT year, month, day, origin, dest
FROM flights
where dest=="IAH" AND origin="EWR"
Limit 10
Displaying records 1 - 10
| 2013 |
1 |
1 |
EWR |
IAH |
| 2013 |
1 |
1 |
EWR |
IAH |
| 2013 |
1 |
1 |
EWR |
IAH |
| 2013 |
1 |
1 |
EWR |
IAH |
| 2013 |
1 |
1 |
EWR |
IAH |
| 2013 |
1 |
1 |
EWR |
IAH |
| 2013 |
1 |
1 |
EWR |
IAH |
| 2013 |
1 |
1 |
EWR |
IAH |
| 2013 |
1 |
1 |
EWR |
IAH |
| 2013 |
1 |
1 |
EWR |
IAH |
dim(flights)
## [1] 336776 19
names(flights)
## [1] "year" "month" "day" "dep_time"
## [5] "sched_dep_time" "dep_delay" "arr_time" "sched_arr_time"
## [9] "arr_delay" "carrier" "flight" "tailnum"
## [13] "origin" "dest" "air_time" "distance"
## [17] "hour" "minute" "time_hour"
SELECT *
FROM airports
Displaying records 1 - 10
| 04G |
Lansdowne Airport |
41.13047 |
-80.61958 |
1044 |
-5 |
A |
America/New_York |
| 06A |
Moton Field Municipal Airport |
32.46057 |
-85.68003 |
264 |
-6 |
A |
America/Chicago |
| 06C |
Schaumburg Regional |
41.98934 |
-88.10124 |
801 |
-6 |
A |
America/Chicago |
| 06N |
Randall Airport |
41.43191 |
-74.39156 |
523 |
-5 |
A |
America/New_York |
| 09J |
Jekyll Island Airport |
31.07447 |
-81.42778 |
11 |
-5 |
A |
America/New_York |
| 0A9 |
Elizabethton Municipal Airport |
36.37122 |
-82.17342 |
1593 |
-5 |
A |
America/New_York |
| 0G6 |
Williams County Airport |
41.46731 |
-84.50678 |
730 |
-5 |
A |
America/New_York |
| 0G7 |
Finger Lakes Regional Airport |
42.88356 |
-76.78123 |
492 |
-5 |
A |
America/New_York |
| 0P2 |
Shoestring Aviation Airfield |
39.79482 |
-76.64719 |
1000 |
-5 |
U |
America/New_York |
| 0S9 |
Jefferson County Intl |
48.05381 |
-122.81064 |
108 |
-8 |
A |
America/Los_Angeles |
SELECT year, month, day, dest, name
FROM flights
INNER JOIN airports
ON flights.dest=airports.faa
LIMIT 10
Displaying records 1 - 10
| 2013 |
1 |
1 |
IAH |
George Bush Intercontinental |
| 2013 |
1 |
1 |
IAH |
George Bush Intercontinental |
| 2013 |
1 |
1 |
MIA |
Miami Intl |
| 2013 |
1 |
1 |
ATL |
Hartsfield Jackson Atlanta Intl |
| 2013 |
1 |
1 |
ORD |
Chicago Ohare Intl |
| 2013 |
1 |
1 |
FLL |
Fort Lauderdale Hollywood Intl |
| 2013 |
1 |
1 |
IAD |
Washington Dulles Intl |
| 2013 |
1 |
1 |
MCO |
Orlando Intl |
| 2013 |
1 |
1 |
ORD |
Chicago Ohare Intl |
| 2013 |
1 |
1 |
PBI |
Palm Beach Intl |
SELECT year, month, day, origin, name
FROM flights
INNER JOIN airports
ON flights.origin=airports.faa
Displaying records 1 - 10
| 2013 |
1 |
1 |
EWR |
Newark Liberty Intl |
| 2013 |
1 |
1 |
LGA |
La Guardia |
| 2013 |
1 |
1 |
JFK |
John F Kennedy Intl |
| 2013 |
1 |
1 |
JFK |
John F Kennedy Intl |
| 2013 |
1 |
1 |
LGA |
La Guardia |
| 2013 |
1 |
1 |
EWR |
Newark Liberty Intl |
| 2013 |
1 |
1 |
EWR |
Newark Liberty Intl |
| 2013 |
1 |
1 |
LGA |
La Guardia |
| 2013 |
1 |
1 |
JFK |
John F Kennedy Intl |
| 2013 |
1 |
1 |
LGA |
La Guardia |
table(flights$origin)
##
## EWR JFK LGA
## 120835 111279 104662
SELECT *
FROM airlines
Displaying records 1 - 10
| 9E |
Endeavor Air Inc. |
| AA |
American Airlines Inc. |
| AS |
Alaska Airlines Inc. |
| B6 |
JetBlue Airways |
| DL |
Delta Air Lines Inc. |
| EV |
ExpressJet Airlines Inc. |
| F9 |
Frontier Airlines Inc. |
| FL |
AirTran Airways Corporation |
| HA |
Hawaiian Airlines Inc. |
| MQ |
Envoy Air |
SELECT year, month, day, flights.carrier AS carrier, name
FROM flights
INNER JOIN airlines
ON flights.carrier =airlines.carrier
LIMIT 50
Displaying records 1 - 10
| 2013 |
1 |
1 |
UA |
United Air Lines Inc. |
| 2013 |
1 |
1 |
UA |
United Air Lines Inc. |
| 2013 |
1 |
1 |
AA |
American Airlines Inc. |
| 2013 |
1 |
1 |
B6 |
JetBlue Airways |
| 2013 |
1 |
1 |
DL |
Delta Air Lines Inc. |
| 2013 |
1 |
1 |
UA |
United Air Lines Inc. |
| 2013 |
1 |
1 |
B6 |
JetBlue Airways |
| 2013 |
1 |
1 |
EV |
ExpressJet Airlines Inc. |
| 2013 |
1 |
1 |
B6 |
JetBlue Airways |
| 2013 |
1 |
1 |
AA |
American Airlines Inc. |
dbDisconnect(con)