2017-09-12
Data cleansing, data cleaning, or data scrubbing is the process of detecting and correcting (or removing) corrupt or inaccurate records from a record set, table, or database and refers to identifying incomplete, incorrect, inaccurate or irrelevant parts of the data and then replacing, modifying, or deleting the dirty or coarse data.
(Wikipedia)
.csv, .xlsxThere's a package for everything. (The python of the statistics world.)
On the other hand, python is also great. You are not required to use R in this course.
library(nycflights13) str(flights)
## Classes 'tbl_df', 'tbl' and 'data.frame': 336776 obs. of 19 variables: ## $ year : int 2013 2013 2013 2013 2013 2013 2013 2013 2013 2013 ... ## $ month : int 1 1 1 1 1 1 1 1 1 1 ... ## $ day : int 1 1 1 1 1 1 1 1 1 1 ... ## $ dep_time : int 517 533 542 544 554 554 555 557 557 558 ... ## $ sched_dep_time: int 515 529 540 545 600 558 600 600 600 600 ... ## $ dep_delay : num 2 4 2 -1 -6 -4 -5 -3 -3 -2 ... ## $ arr_time : int 830 850 923 1004 812 740 913 709 838 753 ... ## $ sched_arr_time: int 819 830 850 1022 837 728 854 723 846 745 ... ## $ arr_delay : num 11 20 33 -18 -25 12 19 -14 -8 8 ... ## $ carrier : chr "UA" "UA" "AA" "B6" ... ## $ flight : int 1545 1714 1141 725 461 1696 507 5708 79 301 ... ## $ tailnum : chr "N14228" "N24211" "N619AA" "N804JB" ... ## $ origin : chr "EWR" "LGA" "JFK" "JFK" ... ## $ dest : chr "IAH" "IAH" "MIA" "BQN" ... ## $ air_time : num 227 227 160 183 116 150 158 53 140 138 ... ## $ distance : num 1400 1416 1089 1576 762 ... ## $ hour : num 5 5 5 5 6 5 6 6 6 6 ... ## $ minute : num 15 29 40 45 0 58 0 0 0 0 ... ## $ time_hour : POSIXct, format: "2013-01-01 05:00:00" "2013-01-01 05:00:00" ...
str(planes)
## Classes 'tbl_df', 'tbl' and 'data.frame': 3322 obs. of 9 variables: ## $ tailnum : chr "N10156" "N102UW" "N103US" "N104UW" ... ## $ year : int 2004 1998 1999 1999 2002 1999 1999 1999 1999 1999 ... ## $ type : chr "Fixed wing multi engine" "Fixed wing multi engine" "Fixed wing multi engine" "Fixed wing multi engine" ... ## $ manufacturer: chr "EMBRAER" "AIRBUS INDUSTRIE" "AIRBUS INDUSTRIE" "AIRBUS INDUSTRIE" ... ## $ model : chr "EMB-145XR" "A320-214" "A320-214" "A320-214" ... ## $ engines : int 2 2 2 2 2 2 2 2 2 2 ... ## $ seats : int 55 182 182 182 55 182 182 182 182 182 ... ## $ speed : int NA NA NA NA NA NA NA NA NA NA ... ## $ engine : chr "Turbo-fan" "Turbo-fan" "Turbo-fan" "Turbo-fan" ...
str(weather)
## Classes 'tbl_df', 'tbl' and 'data.frame': 26130 obs. of 15 variables: ## $ origin : chr "EWR" "EWR" "EWR" "EWR" ... ## $ year : num 2013 2013 2013 2013 2013 ... ## $ month : num 1 1 1 1 1 1 1 1 1 1 ... ## $ day : int 1 1 1 1 1 1 1 1 1 1 ... ## $ hour : int 0 1 2 3 4 6 7 8 9 10 ... ## $ temp : num 37 37 37.9 37.9 37.9 ... ## $ dewp : num 21.9 21.9 21.9 23 24.1 ... ## $ humid : num 54 54 52.1 54.5 57 ... ## $ wind_dir : num 230 230 230 230 240 270 250 240 250 260 ... ## $ wind_speed: num 10.4 13.8 12.7 13.8 15 ... ## $ wind_gust : num 11.9 15.9 14.6 15.9 17.2 ... ## $ precip : num 0 0 0 0 0 0 0 0 0 0 ... ## $ pressure : num 1014 1013 1013 1013 1013 ... ## $ visib : num 10 10 10 10 10 10 10 10 10 10 ... ## $ time_hour : POSIXct, format: "2012-12-31 19:00:00" "2012-12-31 20:00:00" ...
str(airlines)
## Classes 'tbl_df', 'tbl' and 'data.frame': 16 obs. of 2 variables: ## $ carrier: chr "9E" "AA" "AS" "B6" ... ## $ name : chr "Endeavor Air Inc." "American Airlines Inc." "Alaska Airlines Inc." "JetBlue Airways" ...
str(airports)
## Classes 'tbl_df', 'tbl' and 'data.frame': 1396 obs. of 7 variables: ## $ faa : chr "04G" "06A" "06C" "06N" ... ## $ name: chr "Lansdowne Airport" "Moton Field Municipal Airport" "Schaumburg Regional" "Randall Airport" ... ## $ lat : num 41.1 32.5 42 41.4 31.1 ... ## $ lon : num -80.6 -85.7 -88.1 -74.4 -81.4 ... ## $ alt : int 1044 264 801 523 11 1593 730 492 1000 108 ... ## $ tz : num -5 -5 -6 -5 -4 -4 -5 -5 -5 -8 ... ## $ dst : chr "A" "A" "A" "A" ...
dplyrThe dplyr package in R provides simple functions that correspond to the most common data manipulation operations (or verbs) and uses efficient storage approaches.
https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf
verb meaning -------------------------------------------- select() select variables (or columns) filter() subset observations (or rows) mutate() add new variables (or columns) arrange() re-order the observations summarise() reduce to a single row group_by() aggregate left_join() merge two data objects distinct() remove duplicate entries collect() force computation and bring data back into R
filter() extracts a subset of rows of interestlibrary(dplyr)
airports %>% filter(faa %in% c('ALB', 'BDL', 'BTV'))
flights
bycarrier <- flights %>% group_by(carrier) bycarrier
flights %>% summarise(numflights = n()) #n() counts rows
bycarrier %>% summarise(numflights = n()) #n() counts rows
Aggregate the counts of flights at the monthly level.
monthlycounts <- flights %>%
filter(dest %in% c('ALB', 'BDL', 'BTV')) %>%
group_by(year, month) %>%
summarise(numflights = n())
monthlycounts
Aggregate the counts of flights at three airports at the monthly level.
airportmonthlycounts <- flights %>%
filter(dest %in% c('ALB', 'BDL', 'BTV')) %>%
group_by(year, month, dest) %>%
summarise(numflights = n())
airportmonthlycounts
Add a new column by constructing a date variable using mutate(). R has a special "date" data type that is useful; dates can be constructed different ways, including using the ymd() function.
library(lubridate) #To get the ymd() function
airportdailycounts <- flights %>%
filter(dest %in% c('ALB', 'BDL', 'BTV')) %>% group_by(year, month, day, dest) %>%
summarise(numflights = n()) %>% mutate(date = ymd(paste(year, month, day, sep = "-")))
airportdailycounts
library(ggplot2) qplot(date, numflights, colour = dest, geom = "point", xlab = "Year", ylab = "Number of flights per day", data = airportdailycounts)
airportmonthlycounts <- airportmonthlycounts %>%
mutate(FirstOfMonth = ymd(paste(year, "-", month, "-01", sep="")))
qplot(FirstOfMonth, numflights, colour = dest, geom = "point",
xlab = "Date", ylab = "Number of flights per month", data = airportmonthlycounts)
arrange() lets us display the months with the largest number of flights.
airportmonthlycounts %>% arrange(desc(numflights))
Which airline was most reliable flying from New York to Minneapolis/St. Paul (MSP) in January, 2013?
jandelays <- flights %>% select(origin, dest, year, month, day, carrier, arr_delay) %>% filter(dest == 'MSP' & month == 1) qplot(carrier, arr_delay, data = jandelays, geom = "boxplot")
Here, the full carrier names are merged (or joined, in database speak) using the left_join()
merged <- left_join(jandelays, airlines, by = c("carrier" = "carrier"))
merged
?flights gives description: "On-time data for all flights that departed NYC (i.e. JFK, LGA or EWR) in 2013."
flights %>% filter(dest == 'ORD') %>% summarize(count = n())
flights %>% filter(dest == 'YYZ') %>% summarize(count = n())
nycflights13 is just a fraction of the available flight information. See http://www.amherst.edu/~nhorton/precursors for example code using SQLite.
Relational databases, first popularized in the 1970's, provide fast and efficient access to terabyte-sized files. These systems use a structured query language (SQL) to specify data operations.
Database systems have been highly optimized and tuned since they were first invented. Connections between general purpose statistics packages such as R and database systems can be facilitated through use of SQL.
verb meaning -------------------------------------------- SELECT create a new result set from a table FROM specify table WHERE subset observations GROUP BY aggregate ORDER re-order the observations DISTINCT remove duplicate values JOIN merge two data objects