class: center, middle, inverse, title-slide # Data Science for Economists ## Lecture 5: Data cleaning & wrangling: (2) data.table ### Grant McDermott ### University of Oregon |
EC 607
--- name: toc  # Table of contents 1. [Prologue](#prologue) 2. [Introduction](#intro) 3. [data.table basics](#basics) 4. [Working with rows: DT[i, ]](#i) 5. [Manipulating columns: DT[, j]](#j) 6. [Grouping: DT[, , by]](#by) 7. [Keys](#keys) 8. [Merging datasets](#merge) 9. [Reshaping data](#reshape) 10. [data.table + tidyverse workflows](#tidyverse) 10. [Summary](#summary) --- class: inverse, center, middle name: prologue # Prologue

 --- # Checklist We'll be using the following packages in today's lecture: - Already installed: **dplyr**, **ggplot2**, **nycflights13** - New: **data.table**, **tidyfast**, **dtplyr**, **microbenchmark** -- The following code chunk will install (if necessary) and load everything for you. ```r if (!require(pacman)) install.packages('pacman', repos = 'https://cran.rstudio.com') pacman::p_load(dplyr, data.table, dtplyr, tidyfast, microbenchmark, ggplot2, nycflights13) options(dplyr.summarise.inform = FALSE) ## Turn off annoying dplyr group_by messages (optional) ``` --- class: inverse, center, middle name: intro # Introduction

 --- # Why learn data.table? The **tidyverse** is great. As I keep hinting, it will also provide a bridge to many of the big data tools that we'll encounter later in the course (SQL databases, etc.) -- So why bother learning another data wrangling package/syntax? -- When it comes to **data.table**, I can think of at least five reasons: 1. Concise 2. Insanely fast 3. Memory efficient 4. Feature rich (and stable) 5. Dependency free -- Before we get into specifics, here are a few examples to whet your appetite... --- # Why learn data.table? (cont.) ### 1) Concise These two code chunks do the same thing: ```r # library(dplyr) ## Already loaded # data(starwars, package = "dplyr") ## Optional to bring the DF into the global env starwars %>% filter(species=="Human") %>% group_by(homeworld) %>% summarise(mean_height=mean(height)) ``` vs ```r # library(data.table) ## Already loaded starwars_dt = as.data.table(starwars) starwars_dt[species=="Human", mean(height), by=homeworld] ``` --- name:fast # Why learn data.table? (cont.) ### 2) Insanely fast .small90[ ```r collapse_dplyr = function() { storms %>% group_by(name, year, month, day) %>% summarize(wind = mean(wind), pressure = mean(pressure), category = dplyr::first(category)) } storms_dt = as.data.table(storms) collapse_dt = function() { storms_dt[, .(wind = mean(wind), pressure = mean(pressure), category = first(category)), by = .(name, year, month, day)] } microbenchmark(collapse_dplyr(), collapse_dt(), times = 1) ``` ``` ## Unit: milliseconds ## expr min lq mean median uq ## collapse_dplyr() 112.396376 112.396376 112.396376 112.396376 112.396376 ## collapse_dt() 5.982716 5.982716 5.982716 5.982716 5.982716 ## max neval ## 112.396376 1 ## 5.982716 1 ``` ] -- .small90[ **Result:** data.table is 20x faster! (Thanks to [Keith Head](https://twitter.com/ckhead) for this example.) ] --- # Why learn data.table? (cont.) ### 3) Memory efficient Measuring and comparing memory use [gets complicated](https://stackoverflow.com/a/61376971). But see [here](https://jangorecki.gitlab.io/r-talks/2019-06-18_Poznan_why-data.table/why-data.table.pdf) (esp. from slide 12) for a thorough walkthrough of data.table's memory use and efficiency. ### 4) Features and 5) No dependencies I'll lump these together, since they really have to do with the stability of your code over time. Just to emphasise the point about [dependencies](http://www.tinyverse.org/), though: ```r tools::package_dependencies("data.table", recursive = TRUE)[[1]] ``` ``` ## [1] "methods" ``` ```r tools::package_dependencies("dplyr", recursive = TRUE)[[1]] ``` ``` ## [1] "ellipsis" "assertthat" "glue" "magrittr" "methods" "pkgconfig" ## [7] "R6" "Rcpp" "rlang" "tibble" "tidyselect" "utils" ## [13] "BH" "plogr" "tools" "cli" "crayon" "fansi" ## [19] "lifecycle" "pillar" "vctrs" "purrr" "grDevices" "utf8" ## [25] "digest" ``` --- # Before we continue... The purpose of this lecture is *not* to convince you that data.table is superior to the tidyverse. (Or vice versa.) For sure, people have strong opinions on the matter and you may find yourself pulling strongly in one direction or the other. And that's okay, but... My goal is simply to show you another powerful tool that you can use to tackle big (or small!) data problems efficiently in R. -- FWIW, I'm a big fan of both the tidyverse and data.table, and use them both in my own work. - Knowing how to use each of them and how they complement each other has, I believe, made me a much more effective R user/empirical economist/data scientist/etc. -- We'll get back to the point about complementarity at the end of the lecture. --- class: inverse, center, middle name: basics # data.table basics

 --- # The data.table object We've already seen that the tidyerse provides its own enhanced version of a data.frame in the form of tibbles. -- The same is true for data.table. In fact, data.table functions only work on objects that have been converted to data.tables first. - Beyond simple visual enhancements (similar to tibbles), the specialised internal structure of data.table objects is a key reason why the package is so fast. (More [here](https://rdatatable.gitlab.io/data.table/articles/datatable-intro.html#what-is-datatable-1a) and [here](https://twitter.com/matloff/status/1131372631372918784).) -- To create a data.table, we have a couple of options: - `fread('mydata.csv')` reads a CSV into R as a data.table (and is very fast). - `data.table(x = 1:10)` creates a new data.table from scratch - `as.data.table(d)` coerces an existing data frame (`d`) to a data.table. - `setDT(d)` coerces an existing data frame to a data.table *by reference*; i.e. we don't have to (re)assign it. --- # What does "modify by reference" mean? That last bullet leads us to an important concept that underlies much of data.table's awesomeness: It tries, as much as possible, to *modify by reference*. -- What does this mean? -- We don't have time to go into details here, but the very (very) short version is that there are basically two ways of changing or assigning objects in R. 1. **Copy-on-modify:** Creates a copy of your data. Implies extra computational overhead.* 2. **Modify-in-place:** Avoids creating copies and simply changes the data where it sits in memory. .footnote[* In truth, we need to distinguish between shallow and deep copies. But that's more than I want you to worry about here.] -- When we say that data.table "modifies by reference", that essentially means it modifies objects in place. This translates to lower memory overhead and faster computation time! -- P.S. Further reading if this stuff interests you: (a) [Reference semantics](https://rdatatable.gitlab.io/data.table/articles/datatable-reference-semantics.html) data.table vignette, (b) [Names and Values](https://adv-r.hadley.nz/names-values.html) section of *Advanced R* (Hadley Wickham), (c) Nice [blog post](https://tysonbarrett.com//jekyll/update/2019/07/12/datatable/) by Tyson Barrett that's accessible to beginners. --- # data.table syntax All data.tables accept the same basic syntax: .center[ .large2[DT[i, j, by]] ] ![:col_row On which rows?, What to do?, Grouped by what?] -- .center[dplyr "equivalents":] ![:col_list filter(); slice(); arrange(), select(); mutate(), group_by()] -- While the tidyverse tends to break up operations step-by-step, data.table aims to do everything in one concise expression. - We can execute complex data wrangling commands as a single, fluid thought. - Although, as we'll see in a bit, you can certainly chain (pipe) multiple operations together too. --- # A Quick Example We'll dive into the details (and quirks) of data.table shortly. But first, a quick side-by-side comparison with dplyr, since that will help to orientate us for the rest of the lecture. Using our starwars dataset, say we want to know: > What is the average height of the human characters by gender? -- .pull-left[ ### dplyr ```r data(starwars, package = "dplyr") starwars %>% filter(species=="Human") %>% group_by(gender) %>% summarise(mean(height, na.rm=T)) ``` ] .pull-right[ ### data.table ```r starwars_dt = as.data.table(starwars) starwars_dt[ species=="Human", mean(height, na.rm=T), by = gender] ``` ] --- # A Quick Example We'll dive into the details (and quirks) of data.table shortly. But first, a quick side-by-side comparison with dplyr, since that will help to orientate us for the rest of the lecture. Using our starwars dataset, say we want to know: > What is the average height of the human characters by gender? .pull-left[ ### dplyr ```r data(starwars, package = "dplyr") starwars %>% * filter(species=="Human") %>% group_by(gender) %>% summarise(mean(height, na.rm=T)) ``` ] .pull-right[ ### data.table ```r starwars_dt = as.data.table(starwars) starwars_dt[ * species=="Human", ## i mean(height, na.rm=T), by = gender] ``` ] --- # A Quick Example We'll dive into the details (and quirks) of data.table shortly. But first, a quick side-by-side comparison with dplyr, since that will help to orientate us for the rest of the lecture. Using our starwars dataset, say we want to know: > What is the average height of the human characters by gender? .pull-left[ ### dplyr ```r data(starwars, package = "dplyr") starwars %>% filter(species=="Human") %>% group_by(gender) %>% * summarise(mean(height, na.rm=T)) ``` ] .pull-right[ ### data.table ```r starwars_dt = as.data.table(starwars) starwars_dt[ species=="Human", * mean(height, na.rm=T), ## j by = gender] ``` ] --- # A Quick Example We'll dive into the details (and quirks) of data.table shortly. But first, a quick side-by-side comparison with dplyr, since that will help to orientate us for the rest of the lecture. Using our starwars dataset, say we want to know: > What is the average height of the human characters by gender? .pull-left[ ### dplyr ```r data(starwars, package = "dplyr") starwars %>% filter(species=="Human") %>% * group_by(gender) %>% summarise(mean(height, na.rm=T)) ``` ] .pull-right[ ### data.table ```r starwars_dt = as.data.table(starwars) starwars_dt[ species=="Human", mean(height, na.rm=T), * by = gender] ## by ``` ] --- # A Quick Example We'll dive into the details (and quirks) of data.table shortly. But first, a quick side-by-side comparison with dplyr, since that will help to orientate us for the rest of the lecture. Using our starwars dataset, say we want to know: > What is the average height of the human characters by gender? .pull-left[ ### dplyr ```r data(starwars, package = "dplyr") starwars %>% filter(species=="Human") %>% group_by(gender) %>% summarise(mean(height, na.rm=T)) ``` ``` ## # A tibble: 2 x 2 ## gender `mean(height, na.rm = T)` ## <chr> <dbl> ## 1 feminine 160. ## 2 masculine 182. ``` ] .pull-right[ ### data.table ```r starwars_dt = as.data.table(starwars) starwars_dt[ species=="Human", mean(height, na.rm=T), by = gender] ``` ``` ## gender V1 ## 1: masculine 182.3478 ## 2: feminine 160.2500 ``` ] --- class: inverse, center, middle name: i # Working with rows: DT[i, ]

 --- # Subset by rows (filter) Subsetting by rows is very straightforward in data.table. Everything works pretty much the same as you'd expect if you're coming from dplyr. - `DT[x == "string", ]`: Subset to rows where variable x equals "string" - `DT[y > 5, ]`: Subset to rows where variable y is greater than 5 - `DT[1:10, ]`: Subset to the first 10 rows -- Multiple filters/conditions are fine too: - `DT[x=="string" & y>5, ]`: Subset to rows where x is "string" AND y is greater than 5 -- Note that we don't actually need commas when we're only subsetting on `i` (i.e. no `j` or `by` components). - `DT[x=="string"]` is equivalent to `DT[x=="string", ]` - `DT[1:10]` is equivalent to `DT[1:10, ]` - etc. --- # Subset by rows (filter) cont. Just to emphasise the point, here's an example of subsetting by rows using our starwars data.table from earlier. ```r starwars_dt[height>190 & species=='Human'] ``` ``` ## name height mass hair_color skin_color eye_color birth_year ## 1: Darth Vader 202 136 none white yellow 41.9 ## 2: Qui-Gon Jinn 193 89 brown fair blue 92.0 ## 3: Dooku 193 80 white fair brown 102.0 ## 4: Bail Prestor Organa 191 NA black tan brown 67.0 ## sex gender homeworld species ## 1: male masculine Tatooine Human ## 2: male masculine <NA> Human ## 3: male masculine Serenno Human ## 4: male masculine Alderaan Human ## films ## 1: The Empire Strikes Back,Revenge of the Sith,Return of the Jedi,A New Hope ## 2: The Phantom Menace ## 3: Attack of the Clones,Revenge of the Sith ## 4: Attack of the Clones,Revenge of the Sith ## vehicles starships ## 1: TIE Advanced x1 ## 2: Tribubble bongo ## 3: Flitknot speeder ## 4: ``` --- # Order by rows (arrange) ```r starwars_dt[order(birth_year)] ## (temporarily) sort by youngest to oldest starwars_dt[order(-birth_year)] ## (temporarily) sort by oldest to youngest ``` -- While ordering as per the above is very straightforward, data.table also provides an optimised `setorder()` function for reordering *by reference*. -- ```r setorder(starwars_dt, birth_year, na.last = TRUE) starwars_dt[1:5, name:birth_year] ## Only print subset to stay on the slide ``` ``` ## name height mass hair_color skin_color eye_color birth_year ## 1: Wicket Systri Warrick 88 20 brown brown brown 8 ## 2: IG-88 200 140 none metal red 15 ## 3: Luke Skywalker 172 77 blond fair blue 19 ## 4: Leia Organa 150 49 brown light brown 19 ## 5: Wedge Antilles 170 77 brown fair hazel 21 ``` --- class: inverse, center, middle name: j # Manipulating columns: DT[, j]

 --- # j: One slot to rule them all Recall some of the dplyr verbs that we used to manipulate our variables in different ways: - `select()` - `mutate()` - `summarise()` - `count()` -- data.table recognizes that all of these verbs are just different versions of telling R... >"*Do something to this variable in my dataset*" ... and it let's you do all of those operations in one place: the `j` slot. -- However, this concision requires a few syntax tweaks w.r.t. how we change and assign variables in our dataset. - Some people find this off-putting (or, at least, weird) when they first come to data.table. - I hope to convince you that these syntax tweaks aren't actually that difficult to grok and give us a *lot* of power in return. --- # Modifying columns := To add, delete, or change columns in data.table, we use the **`:=`** operator. - Known as the *walrus* operator (geddit??) -- For example, - `DT[, xsq := x^2]`: Create a new column (`xsq`) from an existing one (`x`) - `DT[, x := as.character(x)]`: Change an existing column -- **Important:** `:=` is *modifying by reference*, i.e. in place. So we don't have to (re)assign the object to save these changes. -- However, we also won't see these changes printed to screen unless we ask R explicitly. ```r DT = data.table(x = 1:2) # DT[, xsq := x^2] ## Modifies in place but doesn't print the result DT[, x_sq := x^2][] ## Adding [] prints the result. ``` ``` ## x x_sq ## 1: 1 1 ## 2: 2 4 ``` --- # Modifying columns := (cont.) As I keep saying, *modifying by reference* has important implications for data manipulation. Consider what happens if we copy our data.table and then remove a column. ```r DT_copy = DT DT_copy[, x_sq := NULL] ``` -- Clearly, "x_sq" has been removed from `DT_copy`. But what of the original `DT` table? -- ```r DT ``` ``` ## x ## 1: 1 ## 2: 2 ``` -- Uh-oh! It too has been removed... exactly as modifying by reference demands. To avoid this behaviour, use the explicit [`data.table::copy()`](https://rdatatable.gitlab.io/data.table/reference/copy.html) function. Run this next chunk yourself: ```r DT[, x_sq := x^2] DT_copy = copy(DT) DT_copy[, x_sq := NULL] DT ## x_sq is still there (run and confirm for yourself) ``` --- # Modifying columns := (cont.) ### Sub-assign by reference One really cool implication of `:=` is data.table's [sub-assign by reference](https://rdatatable.gitlab.io/data.table/articles/datatable-reference-semantics.html#ref-i-j) functionality. As a simple example, consider another fake dataset. ```r DT2 = data.table(a = -2:2, b = LETTERS[1:5]) ``` -- Now, imagine we want to locate all rows where "a" is negative and replace the corresponding "b" cell with NA. -- - In dplyr you'd have to do something like `...mutate(b = ifelse(a < 0, NA, b))`. -- - In data.table, simply specify which rows to target (`i`) and then sub-assign (`j`) directly. ```r DT2[a < 0, b := NA][] ## Again, just adding the second [] to print to screen ``` ``` ## a b ## 1: -2 <NA> ## 2: -1 <NA> ## 3: 0 C ## 4: 1 D ## 5: 2 E ``` --- # Modifying columns := (cont.) To modify multiple columns simultaneously, we have two options. 1. LHS `:=` RHS form: `DT[, c("var1", "var2") := .(val1, val2)]` 2. Functional form: `DT[, ':=' (var1=val1, var2=val2)]` -- Personally, I *much* prefer the functional form and so that's what I'll use going forward. E.g. ```r DT[, ':=' (y = 3:4, y_name = c("three", "four"))] DT ## Another way to print the results instead of appending [] ``` ``` ## x x_sq y y_name ## 1: 1 1 3 three ## 2: 2 4 4 four ``` -- Note, however, that dynamically assigning dependent columns in a single step (like we did with dplyr::mutate) doesn't work. ```r DT[, ':=' (z = 5:6, z_sq = z^2)][] ``` ``` ## Error in eval(jsub, SDenv, parent.frame()): object 'z' not found ``` --- # Aside: Chaining data.table operations That last example provides as good a time as any to mention that you can chain multiple data.table operations together. -- The native data.table way is simply to append consecutive `[]` terms. ```r DT[, z := 5:6][, z_sq := z^2][] ``` ``` ## x x_sq y y_name z z_sq ## 1: 1 1 3 three 5 25 ## 2: 2 4 4 four 6 36 ``` -- But if you prefer the **magrittr** pipe, then that's also possible. Just prefix each step with `.`: ```r # library(magrittr) ## Not needed since we've already loaded %>% via dplyr DT %>% .[, xyz := x+y+z] %>% .[, xyz_sq := xyz^2] %>% .[] ``` ``` ## x x_sq y y_name z z_sq xyz xyz_sq ## 1: 1 1 3 three 5 25 9 81 ## 2: 2 4 4 four 6 36 12 144 ``` --- # Modifying columns := (cont.) To remove a column from your dataset, set it to NULL. ```r DT[, y_name := NULL] DT ``` ``` ## x x_sq y z z_sq xyz xyz_sq ## 1: 1 1 3 5 25 9 81 ## 2: 2 4 4 6 36 12 144 ``` --- # Subsetting on columns (select) We can also use the `j` slot to subset our data on columns. I'll return to the starwars dataset for these examples... -- Subset by column position: ```r starwars_dt[1:2, c(1:3, 10)] ``` ``` ## name height mass homeworld ## 1: Wicket Systri Warrick 88 20 Endor ## 2: IG-88 200 140 <NA> ``` -- Or by name: ```r # starwars_dt[, c("name", "height", "mass", "homeworld")] ## Also works # starwars_dt[, list(name, height, mass, homeworld)] ## So does this starwars_dt[1:2, .(name, height, mass, homeworld)] ``` ``` ## name height mass homeworld ## 1: Wicket Systri Warrick 88 20 Endor ## 2: IG-88 200 140 <NA> ``` --- # Aside: What's with the .()? We've now seen `.()` in a couple places, e.g the previous slide and [this slide](#fast) from earlier if you were paying close attention. - `.()` is just a data.table shortcut for `list()`. We'll be using `.()` quite liberally once we start working subsetting and/or grouping by multiple variables at a time. You can think of it as one of data.table's syntactical quirks. But, really, it's just there to give you more options. You can often — if not always — use these three forms interchangeably in data.table: - `.(var1, var2, ...)` - `list(var1, var2, ...)` - `c("var1", "var2", ...)` -- I like the `.()` syntax best — less typing! — but each to their own. -- Okay, back to subsetting on columns... --- # Subsetting on columns (cont.) You can also exclude columns through negation. Try this next code chunk yourself: ```r starwars_dt[, !c("name", "height")] ``` -- ### Renaming columns You can rename (set) a column by reference. Again, run this yourself: ```r setnames(starwars_dt, old = c("name", "homeworld"), new = c"(alias", "crib"))[] ## Better change it back, in case we use "name" or "homeworld" on a later slide setnames(starwars_dt, old = c("alias", "crib"), new = c("name", "homeworld")) ``` -- While the `setnames()` approach offers performance benefits, I often find it convenient to dynamically (and/or temporarily) rename columns when subsetting them. For example: ```r starwars_dt[1:2, .(alias = name, crib = homeworld)] ``` ``` ## alias crib ## 1: Wicket Systri Warrick Endor ## 2: IG-88 <NA> ``` --- # Subsetting on columns (cont.) One last thing I'll mention w.r.t. to subsetting columns is that you can also use dplyr verbs on data.tables if you prefer. For example run the following code chunk for yourself. (You'll get a warning about efficiency loss, but this will be very minor for a case like this.) ```r starwars_dt[1:5, ] %>% select(crib = homeworld, everything()) ``` -- I don't want to preempt myself, though. I'll get back to dplyr+data.table functionality at the end of the lecture.... --- # Aggregating Finally, we can do aggregating manipulations in `j`. ```r starwars_dt[, mean(height, na.rm=T)] ``` ``` ## [1] 174.358 ``` -- Note that we don't keep anything unless we assign the result to a new object. If you want to add the new aggregated column to your original dataset, use `:=`. ```r starwars_dt[, mean_height := mean(height, na.rm=T)] %>% ## Add mean height as column .[1:5, .(name, height, mean_height)] ## Just to keep everything on the slide ``` ``` ## name height mean_height ## 1: Luke Skywalker 172 174.358 ## 2: C-3PO 167 174.358 ## 3: R2-D2 96 174.358 ## 4: Darth Vader 202 174.358 ## 5: Leia Organa 150 174.358 ``` --- # Aggregating (cont.) data.table also provides [special convenience symbols](https://rdatatable.gitlab.io/data.table/reference/special-symbols.html) for common aggregation tasks in `j`. For example, we can quickly count the number of observations using `.N`. ```r starwars_dt[, .N] ``` ``` ## [1] 87 ``` -- Of course, this is a pretty silly example since it's just going to give us the total number of rows in the dataset. Like most forms of aggregation, `.N` is much more interesting when it is applied by group. - This provides a nice segue to our next section... --- name: by class: inverse, center, middle # Group by: DT[, , by]

 --- # by data.table's `by` argument functions very similarly to the `dplyr::group_by` equivalent. Try these next few examples in your own R console: - `starwars_dt[, mean(height, na.rm=T), by = species]`: Collapse by single variable - `starwars_dt[, .(species_height = mean(height, na.rm=T)), by = species]`: As above, but explicitly name the summary variable - `starwars_dt[, mean(mass, na.rm=T), by = height>190]`: Conditionals work too. - `starwars_dt[, species_n := .N, by = species][]`: Add an aggregated column to the data (here: number of observations by species group) -- To perform aggregations by multiple variables, we'll use the `.()` syntax again. ```r starwars_dt[, .(mean_height = mean(height, na.rm=T)), by = .(species, homeworld)] %>% head(4) ## Just to keep everything on the slide ``` ``` ## species homeworld mean_height ## 1: Human Tatooine 179.2500 ## 2: Droid Tatooine 132.0000 ## 3: Droid Naboo 96.0000 ## 4: Human Alderaan 176.3333 ``` --- # Efficient subsetting with .SD We've seen how to group by multiple variables. But what if we want to *summarise* multiple variables, regardless of how we are grouping? One solution is to again use `.()` and write everything out, e.g. ```r ## Run yourself if you'd like to check the output starwars_dt[, .(mean(height, na.rm=T), mean(mass, na.rm=T), mean(birth_year, na.rm=T)), by = species] ``` -- But this soon become tedious. Imagine we have even more variables. Do we really have to write out `mean(..., na.rm=T)` for each one? -- Fortunately, the answer is "no". data.table provides a special `.SD` symbol for **s**ubsetting **d**ata. In truth, .SD can do a [lot more](https://rdatatable.gitlab.io/data.table/articles/datatable-sd-usage.html) than what I'm about to show you, but here's how it would work in the present case... .right[*See next slide.*] --- # Efficient subsetting with .SD (cont.) ```r starwars_dt[, lapply(.SD, mean, na.rm=T), .SDcols = c("height", "mass", "birth_year"), by = species] %>% head(2) ## Just keep everything on the slide ``` ``` ## species height mass birth_year ## 1: Ewok 88.0 20.00 8.00000 ## 2: Droid 131.2 69.75 53.33333 ``` --- count: false # Efficient subsetting with .SD (cont.) ```r starwars_dt[, * lapply(.SD, mean, na.rm=T), .SDcols = c("height", "mass", "birth_year"), by = species] %>% head(2) ## Just keep everything on the slide ``` ``` ## species height mass birth_year ## 1: Ewok 88.0 20.00 8.00000 ## 2: Droid 131.2 69.75 53.33333 ``` First, we specify what we want to *do* on our data subset (i.e. `.SD`). In this case, we want the mean for each element, which we obtain by iterating over with the base `lapply()` function.1 .footnote[ 1 We'll learn more about iteration once we get to the programming section of the course. ] --- count: false # Efficient subsetting with .SD (cont.) ```r starwars_dt[, lapply(.SD, mean, na.rm=T), * .SDcols = c("height", "mass", "birth_year"), by = species] %>% head(2) ## Just keep everything on the slide ``` ``` ## species height mass birth_year ## 1: Ewok 88.0 20.00 8.00000 ## 2: Droid 131.2 69.75 53.33333 ``` First, we specify what we want to *do* on our data subset (i.e. `.SD`). In this case, we want the mean for each element, which we obtain by iterating over with the base `lapply()` function.1 Then, we specify *which columns* to subset with the `.SDcols` argument. .footnote[ 1 We'll learn more about iteration once we get to the programming section of the course. ] --- count: false # Efficient subsetting with .SD (cont.) ```r starwars_dt[, lapply(.SD, mean, na.rm=T), .SDcols = c("height", "mass", "birth_year"), by = species] %>% head(2) ## Just keep everything on the slide ``` ``` ## species height mass birth_year ## 1: Ewok 88.0 20.00 8.00000 ## 2: Droid 131.2 69.75 53.33333 ``` First, we specify what we want to *do* on our data subset (i.e. `.SD`). In this case, we want the mean for each element, which we obtain by iterating over with the base `lapply()` function.1 Then, we specify *which columns* to subset with the `.SDcols` argument. P.S. One annoyance I have is that the `.()` syntax doesn't work for `.SDcols`. However, we can at least feed it consecutive columns without quotes, e.g. `.SDcols = height:mass`. See [here](https://rdatatable.gitlab.io/data.table/articles/datatable-intro.html#how-can-we-specify-just-the-columns-we-would-like-to-compute-the-mean-on). .footnote[ 1 We'll learn more about iteration once we get to the programming section of the course. ] --- # Efficient subsetting with .SD (cont.) Just to add: We need only specify `.SDcols` if we want to subset specific parts of the data. (You can also use shortcuts like `.SDcols = is.numeric` or `.SDcols = patterns('abc')`.) If we instead want to apply the same function on *all* the variables in our dataset, then `.SD` by itself will suffice. -- As a quick example, recall our earlier DT object that contains only numeric variables. ```r DT ``` ``` ## x x_sq y z z_sq xyz xyz_sq ## 1: 1 1 3 5 25 9 81 ## 2: 2 4 4 6 36 12 144 ``` -- We can obtain the mean for each variable as follows. ```r DT[, lapply(.SD, mean)] ``` ``` ## x x_sq y z z_sq xyz xyz_sq ## 1: 1.5 2.5 3.5 5.5 30.5 10.5 112.5 ``` --- # keyby The last thing I want to mention w.r.t. `by` is its close relative: `keyby`. The `keyby` argument works exactly like `by` — you can use it as a drop-in replacement — except that it orders the results and creates a **key**. - Setting a key for a data.table will allow for various (and often astonishing) performance gains.1 - Keys are important enough that I want to save them for their own section, though... .footnote[1 Note that you won't see an immediate performance gain with `keyby`, but subsequent operations will certainly benefit. (Of course, you can get an immediate boost by setting the key ahead of time, but I'll explain all that on the next slide...)] --- name: keys class: inverse, center, middle # Keys

 --- # What are keys? Keys are a way of ordering the data that allows for *extremely* fast subsetting. The data.table [vignette](https://rdatatable.gitlab.io/data.table/articles/datatable-keys-fast-subset.html) describes them as "supercharged rownames". I know that might sound a bit abstract, but here's the idea in a nutshell... -- Imagine that we want to filter a dataset based on a particular value (e.g. find all the human characters in our starwars dataset). - Normally, we'd have to search through the whole dataset to identify matching cases. - But, if we've set an appropriate key, then the data are already ordered in such a way that we (i.e. our computer) only has to search through a much smaller subset. -- **Analogy:** Think of the way a filing cabinet might divide items by alphabetical order: Files starting "ABC" in the top drawer, "DEF" in the second drawer, etc. To find *Alice's* file, you'd only have to search the top draw. For *Fred*, the second draw, and so on. -- Not only is this much quicker, but the same idea also carries over to *all other* forms of data manipulation that rely on subsetting (aggregation by group, joins, etc.) -- P.S. We'll get there later in the course, but keys are also the secret sauce in databases. --- # How do I set a key? You can set a key when you first create a data.table. E.g. - `DT = data.table(x = 1:10, y = LETTERS[1:10], key = "x")` - `DT = as.data.table(d, key = "x")` - `setDT(d, key = "x")` -- Or, you can set keys on an existing data.table with the `setkey()` function. - `setkey(DT, x)`: Note that the key doesn't have to be quoted this time -- **Important:** Since keys just describe a particular ordering of the data, you can set a key on *multiple* columns. (More [here](https://rdatatable.gitlab.io/data.table/articles/datatable-keys-fast-subset.html#key-properties).) E.g. - `DT = as.data.table(DF, key = c("x", "y"))` - `setkey(DT, x, y)`: Again, no quotes needed --
 P.S. Use the `key()` function to see what keys are currently set for your data.table. You can only ever have one key per table at a time, but it's very easy to change them using one of the above commands. --- # Example Recall the [speed benchmark](#fast) that we saw at the very beginning of the lecture: data.table ended up being 20x faster than dplyr for a fairly standard summarising task. -- Let's redo the benchmark, but this time include a version where we pre-assign a key. For optimal performance, the key should match the same variables that we're grouping/subsetting on. - Again, a key can be set on multiple variables, although the lead grouping variable (in the below case: "name") is the most important. -- .small90[ ```r ## First create a keyed version of the storms data.table. ## Note that key variables match the 'by' grouping variables below. storms_dt_key = as.data.table(storms, key = c("name", "year", "month", "day")) ## Collapse function for this keyed data.table. Everything else stays the same. collapse_dt_key = function() { storms_dt_key[, .(wind = mean(wind), pressure = mean(pressure), category = first(category)), by = .(name, year, month, day)] } ## Run the benchmark on all three functions. microbenchmark(collapse_dplyr(), collapse_dt(), collapse_dt_key(), times = 1) ``` ] .right[*See next slide for results*] --- # Example (cont.) ``` ## Unit: milliseconds ## expr min lq mean median uq ## collapse_dplyr() 112.849845 112.849845 112.849845 112.849845 112.849845 ## collapse_dt_key() 1.107796 1.107796 1.107796 1.107796 1.107796 ## max neval ## 112.849845 1 ## 1.107796 1 ``` -- The keyed data.table version is now **100x** (!!!) faster than dplyr.  -- It's not just this toy example. In my experience working with real-life data, setting keys almost always leads to huge speed-ups... and those gains tend to scale as the datasets increase in size. -- **Bottom line:** data.table is already plenty fast. But use keys if you're really serious about performance. --- name: merge class: inverse, center, middle # Merging datasets

 --- # Merge (aka join) options data.table provides two ways to merge datasets. - `DT1[DT2, on = "id"]` - `merge(DT1, DT2, by = "id")` -- I prefer the latter because it offers extra functionality (see `?merge.data.table`), but each to their own.1 .footnote[1 For a really good summary of the different join options (left, right, full, anti, etc.) using these two methods, as well as their dplyr equivalents, see [here](https://atrebas.github.io/post/2019-03-03-datatable-dplyr/#joinbind-data-sets).] -- I'm going to keep things brief by simply showing you how to repeat the same left join that we [practiced](https://raw.githack.com/uo-ec607/lectures/master/05-tidyverse/05-tidyverse.html#54) with dplyr in the last lecture, using data from the **nycflights13** package. ```r # library(nycflights13) ## Already loaded flights_dt = as.data.table(flights) planes_dt = as.data.table(planes) ``` --- # Left join example (cont.) Here's a comparison with the dplyr equivalents from last week. I'll let you run and compare these yourself. (Note that the row orders will be different.) .pull-left[ **dplyr** ```r left_join( flights, planes, by = "tailnum" ) ``` ] .pull-right[ **data.table** ```r merge( flights_dt, planes_dt, all.x = TRUE, ## omit for inner join by = "tailnum") ``` ] -- If you run these, you'll see that both methods handle the ambiguous "year" columns by creating "year.x" and "year.y" variants. We avoided this in dplyr by using `rename()`. How might you avoid the same thing in data.table? -- Possible answer: Use `setnames()`. ```r merge( flights_dt, * setnames(planes_dt, old = "year", new = "year_built"), all.x = TRUE, by = "tailnum") ``` --- # Use keys for lightning fast joins The only other thing I'll point out is that setting [keys](#keys) can lead to dramatic speed-ups for merging data.tables. I'll demonstrate using an inner join this time. ```r merge_dt = function() merge(flights_dt, planes_dt, by = "tailnum") flights_dt_key = as.data.table(flights, key = "tailnum") planes_dt_key = as.data.table(planes, key = "tailnum") merge_dt_key = function() merge(flights_dt_key, planes_dt_key, by = "tailnum") microbenchmark(merge_dt(), merge_dt_key(), times = 1) ``` ``` ## Unit: milliseconds ## expr min lq mean median uq max ## merge_dt() 301.44576 301.44576 301.44576 301.44576 301.44576 301.44576 ## merge_dt_key() 36.09671 36.09671 36.09671 36.09671 36.09671 36.09671 ## neval ## 1 ## 1 ``` -- So, we get about a **10x** speed-up for this simple case just by setting keys. More importantly, ordering your data by setting keys will make a huge difference once you start working with big datasets and making complicated joins. Trust me. (FWIW, the [same is true](https://tysonbarrett.com//jekyll/update/2019/10/11/speed_of_joins/) for dplyr.) --- name: reshape class: inverse, center, middle # Reshaping data

 --- # Reshaping options with data.table In the tidyverse lecture, we saw how to reshape data using the tidyr::pivot* functions. data.table offers its own functions for flexibly reshaping data: - `melt()`: convert wide data to long data - `dcast()`: convert long data to wide data -- However, I also want to flag the [**tidyfast**](https://tysonbarrett.com/tidyfast/index.html) package by Tyson Barrett, which implements data.table versions of the tidyr::pivot* functions (among other things). - `tidyfast::dt_pivot_longer()`: wide to long - `tidyfast::dt_pivot_wider()`: long to wide -- I'll demonstrate reshaping with both options on the same fake "stocks" data that we created last time: ```r stocks = data.table(time = as.Date('2009-01-01') + 0:1, X = rnorm(2, 0, 1), Y = rnorm(2, 0, 2), Z = rnorm(2, 0, 4)) ``` --- # Reshape from wide to long Our data are currently in wide format. ```r stocks ``` ``` ## time X Y Z ## 1: 2009-01-01 -0.36176990 -3.863700 -3.806386 ## 2: 2009-01-02 0.04221233 1.064251 -9.912991 ``` To convert this into long format, we could do either of the following: .pull-left[.small90[ ```r # See ?melt.data.table for options melt(stocks, id.vars ="time") ``` ``` ## time variable value ## 1: 2009-01-01 X -0.36176990 ## 2: 2009-01-02 X 0.04221233 ## 3: 2009-01-01 Y -3.86370033 ## 4: 2009-01-02 Y 1.06425112 ## 5: 2009-01-01 Z -3.80638594 ## 6: 2009-01-02 Z -9.91299065 ``` ]] .pull-right[.small90[ ```r stocks %>% dt_pivot_longer(X:Z, names_to="stock", values_to="price") ``` ``` ## time stock price ## 1: 2009-01-01 X -0.36176990 ## 2: 2009-01-02 X 0.04221233 ## 3: 2009-01-01 Y -3.86370033 ## 4: 2009-01-02 Y 1.06425112 ## 5: 2009-01-01 Z -3.80638594 ## 6: 2009-01-02 Z -9.91299065 ``` ]] --- # Reshape from long to wide Let's quickly save the long-format stocks data.table. I'll use the `melt()` approach and also throw in some extra column-naming options, just so you can see those in action. ```r stocks_long = melt(stocks, id.vars ="time", variable.name = "stock", value.name = "price") stocks_long ``` ``` ## time stock price ## 1: 2009-01-01 X -0.36176990 ## 2: 2009-01-02 X 0.04221233 ## 3: 2009-01-01 Y -3.86370033 ## 4: 2009-01-02 Y 1.06425112 ## 5: 2009-01-01 Z -3.80638594 ## 6: 2009-01-02 Z -9.91299065 ``` -- .pull-left[.small90[ ```r dcast(stocks_long, time ~ stock, value.var = "price") ``` ``` ## time X Y Z ## 1: 2009-01-01 -0.36176990 -3.863700 -3.806386 ## 2: 2009-01-02 0.04221233 1.064251 -9.912991 ``` ]] .pull-right[.small90[ ```r stocks_long %>% dt_pivot_wider(names_from=stock, values_from=price) ``` ``` ## time X Y Z ## 1: 2009-01-01 -0.36176990 -3.863700 -3.806386 ## 2: 2009-01-02 0.04221233 1.064251 -9.912991 ``` ]] --- name: tidyverse class: inverse, center, middle # data.table + tidyverse workflows

 --- # Choosing a workflow that works for you When it comes to data work in R, we truly are spoilt for choice. We have two incredible data wrangling ecosystems to choose from. - **tidyverse** (esp. **dplyr** and **tidyr**) - **data.table** Over the last two lectures, we've explored some of the key features of each. It's only natural that people might find themselves gravitating to one or the other. - Some people love the expressiveness and modularity of the tidyverse. - Others love the concision and power of data.table. -- And that's cool. But I'll repeat a point I made earlier: I use both ecosystems in my own work and honestly believe that this has been to my benefit. These next few slides offer a few additional thoughts on how data.table and the tidyverse can be combined profitably in your own workflow. --- # Pick and choose The first point is perhaps the most obvious one: The tidyverse consists of *multiple* packages. Just because you prefer to use data.table instead of dplyr+tidyr for your data.wrangling needs, doesn't mean that other tidyverse packages are off limits too. Case in point: Almost every hardcore data.table user I know is a hardcore **ggplot2** user too. .pull-left[ .small75[ ```r ## library(ggplot2) # already loaded storms_dt[, .(wind = mean(wind), pressure = mean(pressure), category = first(category)), by = .(name, year, month, day)] %>% ggplot(aes(x = pressure, y = wind, col=category)) + geom_point(alpha = 0.3) + theme_minimal() ``` ]] .pull-right[  ] --- # Don't be a fanatic Closely related to the second first point: Don't try to shoehorn every problem into a tidyverse or data.table framework. - Recall my [admonition](https://raw.githack.com/uo-ec607/lectures/master/05-tidyverse/05-tidyverse.html#11) from last time: "A combination of tidyverse and base R is often the best solution to a problem. You can add data.table to that list. -- Having worked extensively with both packages, I think it's fair to say that there are things the tidyverse (dplyr+tidyr) does better, and there are things that data.table does better. - If you find a great solution on StackOverflow that uses the "other" package... use it. -- Plus, as I hinted earlier, you *can* use tidyverse verbs on data.tables. Try yourself: ```r starwars_dt %>% group_by(homeworld) %>% summarise(height = mean(height, na.rm=T)) ``` .footnote[1 This does [incur](https://stackoverflow.com/a/27513921) a performance penalty. Luckily there's a better solution coming on the next slide...] -- **Bottom line:** Don't be a fanatic. Insisting on ecosystem purity is rarely worth it. --- # dtplyr Do you love dplyr's syntax, but want data.table's performance? -- Well, you're in luck! -- Hadley Wickham's **dtplyr** package provides a data.table "back-end" for dplyr. - Basically, write your code as if you were using dplyr and then it gets automatically translated to (and evaluated as) data.table code. -- If this sounds appealing to you (and it should) I strongly encourage you to check out the [package website](https://dtplyr.tidyverse.org/) for details. But here's quick example, using our benchmark from earlier. .font80[ ```r # library(dtplyr) ## Already loaded storms_dtplyr = lazy_dt(storms) ## dtplyr requires objects to be set as "lazy" data.tables collapse_dtplyr = function() { storms_dtplyr %>% group_by(name, year, month, day) %>% summarize(wind = mean(wind), pressure = mean(pressure), category = first(category)) %>% as_tibble() } ## Just compare dtplyr with normal dplyr and data.table versions (i.e. no keys) microbenchmark::microbenchmark(collapse_dplyr(), collapse_dtplyr(), times = 1) ``` ] .right[*See next slide for results*] --- # dtplyr (cont.) ``` ## Unit: milliseconds ## expr min lq mean median uq ## collapse_dplyr() 117.106259 117.106259 117.106259 117.106259 117.106259 ## collapse_dtplyr() 5.416915 5.416915 5.416915 5.416915 5.416915 ## max neval ## 117.106259 1 ## 5.416915 1 ``` -- A **20x** speed-up for free? I'd take it! -- **Aside:** dtplyr automatically prints its data.table translation to screen. This can be super helpful when you first come over to data.table from the tidyverse. ```r lazy_dt(starwars) %>% filter(species=="Human") %>% group_by(gender) %>% summarise(height = mean(height, na.rm=TRUE)) ``` ``` ## Source: local data table [?? x 2] ## Call: `_DT2`[species == "Human"][, .(height = mean(height, na.rm = TRUE)), ## keyby = .(gender)] ## ## gender height ## <chr> <dbl> ## 1 feminine 160. ## 2 masculine 182. ## ## # Use as.data.table()/as.data.frame()/as_tibble() to access results ``` --- name: summary class: inverse, center, middle # Summary

 --- # Summary data.table is a powerful data wrangling package that combines concise syntax with incredible performance. It is also very lightweight, despite being feature rich. The basic syntax is `DT[i, j, by]` - `i` On which rows? - `j` What to do? - `by` Grouped by what? data.table (re)introduces some new ideas like modify by reference (e.g. `:=`), as well as syntax (e.g. `.()`, `.SD` `.SDcols`, etc.). - All of these ideas support data.table's core goals: Maximise performance and flexibility, whilst maintaining a concise and consistent syntax. They are worth learning. Pro tip: Use keys to order your data and yield dramatic speed-ups. The tidyverse and data.table are often viewed as substitutes, but you can profitably combine both into your workflow... even if you favour one for (most of) your data wrangling needs. --- # Further resources As hard as it may be to believe, there's a ton of data.table features that we didn't cover today. Some, we'll get to in later lectures (e.g. the insanely fast `fread()` and `fwrite()` CSV I/O functions). Others, we won't, but hopefully I've given you enough of a grounding to continue exploring on your own. Here are some recommended further resources: - http://r-datatable.com (Official website. See the vignettes, especially.) - https://github.com/Rdatatable/data.table#cheatsheets (Cheatsheet.) - https://atrebas.github.io/post/2019-03-03-datatable-dplyr (Really nice, side-by-side comparisons of common data.table and dplyr operations.) - https://brooksandrew.github.io/simpleblog/articles/advanced-data-table/ (Some cool advanced data.table tricks.) And related packages: - https://tysonbarrett.com/tidyfast - https://dtplyr.tidyverse.org --- # PS — Assignment Your next assignment is up. Impress me with your data wrangling skills using either the tidyverse or data.table (or both!) --- class: inverse, center, middle # Next lecture: Webscraping (1)


Zend Framework
LICENSE
This source file is subject to the new BSD license that is bundled with this package in the file LICENSE.txt. It is also available through the world-wide-web at this URL: http://framework.zend.com/license/new-bsd If you did not receive a copy of the license and are unable to obtain it through the world-wide-web, please send an email to<EMAIL_ADDRESS>so we can send you a copy immediately.
Gdata Http Client object.
Class to extend the generic Zend Http Client with the ability to perform secure AuthSub requests
resource $_authSubPrivateKeyId = 'null'
OpenSSL private key resource id This key is used for AuthSub authentication. If this value is set, it is assuemd that secure AuthSub is desired.
string $_authSubToken = 'null'
Token for AuthSub authentication.
If this token is set, AuthSub authentication is used.
string $_clientLoginKey = 'null'
Token for ClientLogin authentication.
If this token is set, and the AuthSub key is not set, ClientLogin authentication is used
string $_clientLoginToken = 'null'
Token for ClientLogin authentication.
If only this token is set, ClientLogin authentication is used.
bool $_streamingRequest = 'null'
True if this request is being made with data supplied by a stream object instead of a raw encoded string.
_prepareBody(
)
:
string
Prepare the request body (for POST and PUT requests)
filterHttpRequest(
string $method, string $url, array $headers
=
array, string $body
=
null, string $contentType
=
null
)
:
array
Filters the HTTP requests being sent to add the Authorization header.
If both AuthSub and ClientLogin tokens are set, AuthSub takes precedence. If an AuthSub key is set, then secure AuthSub authentication is used, and the request is signed. Requests must be signed only with the private key corresponding to the public key registered with Google. If an AuthSub key is set, but openssl support is not enabled in the PHP installation, an exception is thrown.
filterHttpResponse(
Zend_Http_Response $response
)
:
Zend_Http_Response
Method for filtering the HTTP response, though no filtering is currently done.
getAdapter(
)
:
Zend_Http_Client_Adapter_Interface|string
Return the current connection adapter
getAuthSubPrivateKeyId(
)
:
string
Gets the openssl private key id
getAuthSubToken(
)
:
string
Gets the AuthSub token used for authentication
getClientLoginToken(
)
:
string
Gets the ClientLogin token used for authentication
getStreamingRequest(
)
:
boolean
Check whether the client is set to perform streaming requests.
resetParameters(
$clearAll
=
false
)
:
Zend_Http_Client
Clear all custom parameters we set.
setAdapter(
Zend_Http_Client_Adapter_Interface $adapter
)
:
void
Load the connection adapter
setAuthSubPrivateKey(
string $key, string $passphrase
=
null
)
:
Zend_Gdata_HttpClient
Sets the PEM formatted private key to be used for secure AuthSub auth.
In order to call this method, openssl must be enabled in your PHP installation. Otherwise, a Zend_Gdata_App_InvalidArgumentException will be thrown.
setAuthSubPrivateKeyFile(
string $file, string $passphrase
=
null, bool $useIncludePath
=
false
)
:
void
Sets the PEM formatted private key, as read from a file.
This method reads the file and then calls setAuthSubPrivateKey() with the file contents.
setAuthSubToken(
string $token
)
:
Zend_Gdata_HttpClient
Sets the AuthSub token used for authentication
setClientLoginToken(
string $token
)
:
Zend_Gdata_HttpClient
Sets the ClientLogin token used for authentication
setRawDataStream(
string $data, string $enctype
=
null
)
:
Zend_Http_Client
Set the raw (already encoded) POST data from a stream source.
This is used to support POSTing from open file handles without caching the entire body into memory. It is a wrapper around Zend_Http_Client::setRawData().
setStreamingRequest(
boolean $value
)
:
void
Set the streamingRequest variable which controls whether we are sending the raw (already encoded) POST data from a stream source.


The show revolves around the adventures of Rick Sanchez, an eccentric and alcoholic mad scientist, and Morty Smith, Rick's 14-year-old grandson, a kind but easily distressed boy, whose naive but grounded moral compass plays counterpoint to Rick's Machiavellian ego. Other characters involve the rest of the Smith family, Summer Smith, Morty's 17-year-old sister, a more conventional teenager who worries about improving her status among her peers and sometimes follows Rick and Morty on their adventures; Beth Smith, Rick's daughter, a generally level-headed person and assertive force in the household, who is dissatisfied with her marriage; Jerry Smith, Beth's husband, a simple-minded and insecure person, who disapproves of Rick's influence over his family.


In order to setup your applications, you have first to setup an Identity Bridge.
Please choose one of the options below.
If you are not sure, you can invite people from your organization, such as a Systems Administrator to complete this process.
About this section
This is the section of the application where you setup your IdentityBridge.
IdentityBridge
An IdentityBridge is a tool that connects authoritative user repositories to cloud infrastructure. By securely connecting user repositories, organizations can store passwords in one place but leverage them to protect many different applications.
In order to connect your users to cloud applications, we must first authenticate those users. Some organizations keep their user information on-premise, in repositories like Active Directory, other organizations use cloud repositories like Google Apps, or infrastructure services like SAML.
PingOne supports many methods of bridging identities into the cloud; choose the method that best suits your organization.
PingOne AD Connect
PingOne AD Connect is a free software tool that can be downloaded and deployed locally on a Windows server so that Active Directory identities can be bridged into CAS. If you don't know which option to pick, and you have Active Directory in your organization, this is your best bet.
For more information on what it means to install AD Connect in your organization, you can watch a quick video here.
Ping Federate (SAML)
PingFederate is a powerful federation server that can send SAML assertions to PingOne (among many other things). Choose this option if you are a PingFederate administrator.
For more information please check out the PingFederate product page.
Google Apps
Google Apps for your Domain is a cloud-based business suite that can generate OpenID assertions and send them to PingOne. If you are already using Google Apps for mail and calendar, there are just a few extra steps you must take to also use Google Apps as your Identity Provider.
For more information please check Google Apps official page.
3rd-party SAML
There are many federation tools that can generate SAML assertions and send them to PingOne. Examples of 3rd Party SAML tools are ADFS, Shibboleth, CA Siteminder, Oracle Identity Federation, and many others. PingOne can accept SAML 2.0 assertions from all of these providers.
3-Party OpenToken IdP
Plug in your own authenticator to PingOne using OpenToken.
If you want to hack some new ways of authenticating people, this is the right choice for you. How about authenticating user through "face recognition"?
 Stop worrying about scalibility, geo-dedundency, availibility, here is your one stop shop to get it all!
For more information please check out the Cloud Direcotry product page.
PingOne Super IdP
Use the Web Portal as your IdP in hacker's mode.
Since we know you are the administrator of your account, we will give you the power to customize the identity attributes (subjects, assertions, claims, anything you can think of) to be sent to PingOne Token Processor, and by doing so, you can impersonate any users under your account.


Event Location:
Rivier College - Education Center Building
420 South Main Street
Nashua, New Hampshire
Abstract:
A Dynamic Dialog session: According to research compiled by Gartner from discussions with many BI clients, a substantial disparity exists, in the implementation of BI, between theory (standard approaches, promises, claims and expectations) and practice (shortfalls, compromises and other realities). In this session, Business Intelligence Architect and Microsoft MVP Bill Pearson leads a dialog within which we will gain an understanding of the general types of discrepancies between the “ideal” approach to BI and the compromised reality that a large number of organizations experience. We’ll then share and class experiences as a group, and discuss ways to avoid these discrepancies in future projects.
Speaker(s): William E Pearson III,
Abstract:
Are you confused where to start when it comes to performance monitoring? If so then come learn the essentials of daily performance monitoring using readily available TSQL scripts, stored procedures and utilities. You will see how easy it is to identify potential bottlenecks and poor performing queries without requiring an extensive infrastructure. Most DBA’s these days are overworked and under budgeted. You will walk away with a solid grasp on how to approach the seemingly daunting task of performance monitoring.
Speaker(s): Andrew Kelly,
Abstract:
Stored procedures and functions are the mainstay of SQL Server coding. This presentation will show techniques to improve their performance. We’ll start with techniques to learn where the time is really going using standard server traces and the new Extended Events. Once we know where the time is going we look at strategies to decrease it: • Managing indexes on temp tables - Why heaps are often the best choice for temp tables - How building non-clustered indexes on heaps is faster than building clustered indexes. • Rewriting scalar and multi-statement functions to as inline functions Each technique is illustrated with examples drawn from real cases in Andy's work.
Speaker(s): Andrew Novick,
Abstract:
While SQL Server Standard Edition ships without several advanced enterprise features, it nonetheless can be used as a cost-effective data platform for many Big Data OLTP applications. This talk will highlight what exactly SQL Standard Edition is, discuss the scale-up vs. scale-out debate, dive into techniques for ordering, splitting and joining very large tables, and present a case study.
Speaker(s): Robert McCauley,
Abstract:
Join SQL Server MVPs and Community Speakers Aaron Betrand, Andy Kelly, Joey D'Antoni and Mike Walsh as we discuss some of the worst practices we often see bite SQL Server environments. Most of these are settings and configurations that are destined to cause headaches from the defaults shipped with SQL Server, some are misunderstandings but all will cause you heartache. This will be a VERY interactive panel discussion. We'll cover a list of worst practices, share horror stories from our own experiences and take plenty of questions from the audience. We'll try and leave time at the end for you to throw your DBA or performance questions at this panel with over 50 years of SQL Server experience.
Speaker(s): Mike Walsh,
Abstract:
Have you heard about all of the new Availability Group features in SQL 2012? Thinking about implementing a DR solution, but don't know where to start. In this presentation you'll learn all about the new DR features in SQL Server 2012 and see them in practice.
Speaker(s): Joseph D'Antoni,
Abstract:
Whether we inherit our Analysis Services environments from predecessors or create them - learning as we go - ourselves, we often conclude that performance (processing and / or querying) and functionality can be enhanced. In this session, Microsoft BI Architect and SQL Server MVP Bill Pearson overviews ways to launch new capabilities, and to propel performance beyond its current plateau, using design best practices to better meet consumer performance and functional needs. This is the initial session of a set of detailed Best Practices presentations.
Speaker(s): William E Pearson III,
Abstract:
Enterprise Information Management (EIM) is an industry term for managing your data for data integration, quality, and governance. This session revolves around a demo which brings together the EIM functionality in SQL Server, a key part of our Credible, Consistent Data story for the 2012 release. We will show you how SQL Server Integration Services (SSIS), Data Quality Services (DQS), Master Data Services (MDS) and other Microsoft technologies work together to provide a comprehensive EIM solution.
Speaker(s): Matt Masson,
Abstract:
Microsoft SQL Server 2012 PowerPivot and Power View provide a way for you to create impressive and dynamic reports for data analysts, business decision makers, and information workers. You can easily create and interact with large amount of data from multiple sources and publish share these reports views. In this session I will give you an overview of the key features of PowerPivot and Power View.
Speaker(s): Sunil Kadimdiwan,
Abstract:
We will cover several advanced topics in Transactional Replication including how to make a ddl change to one table in the publication without reinitializing the publication; how to make a ddl change to a table without having to snapshot; creating replicated tables on file systems other than Primary – including partitioned file systems. And hint – we will be leaving the wizard in Redmond!! Bring a copy of SQL 2012 Developers Edition with AdventureWorks2012 if you want to play along.
Speaker(s): John Flannery,
Abstract:
Transform traditional database development by using SQL Server Data Tools - a declarative, model-based experience that supports both online and offline (project-based) database development.
Speaker(s): Andy Roberts,
Abstract:
SQL Server 2012: Efficient and effective database design is so important that leaving it solely to the DBAs is simply not a solution. We as developers will more than likely have to create applications to access the data stores and as such must ensure that all application related considerations are taken into account within the database design.In this session, (aimed at software developers) we shall be touching upon several concepts of special importance to the developer. These include database development "patterns and anti-patterns", final proofing of design and creating versatile deployment scripts that can and will be utilized in future endeavors.
Speaker(s): Steve Simon,
Abstract:
In this “demo-tastic” presentation, SSIS trainer, author, and consultant Andy Leonard explains the what, why, and how of an SSIS framework that delivers metadata-driven package execution, connections management, and centralizes logging. Key takeaways: 1) Developers can migrate packages from Development, through their lifecycle, to Production without editing SSIS Connection Managers properties. 2) A metadata-driven approach to SSIS package execution. 3) Demonstration of a centralized logging reporting application.
Speaker(s): Andy Leonard,
Abstract:
SQL Server 2012 brings new efficient way of processing and designing data structures for analytics and reporting. These new concepts called BI Semantic Model and Tabular Model. In this session we will take a tour of building corporate BI Semantic Model step-by step using (SSDT). We also cover what are the Tabular model and Multidimensional models. How do I build a Tabular model, measures and KPI.
Speaker(s): Slava Kokaev,
Abstract:
Are you really interested in the new Always On Availability Group feature in SQL 2012? Like really interested? We will talk about the basic architecture of AlwaysOn, then dive deep into the system views behind it, and then use Extended Events, to walk through what's going on inside of SQL and the Windows cluster to make AlwaysOn work.
Speaker(s): Joseph D'Antoni,
Abstract:
SSRS 2012: Having just completed a PRACTICAL dashboard to answer JUST this exact question, we shall be looking at how to create a reporting tool that will provide YOUR management with all their answers. We shall be focusing on the plethora of information that may be reaped from Dynamic Managment Views (DMV) and see how KPI's may be incorporated into the mix.
Speaker(s): Steve Simon,
Abstract:
Every developer eventually comes against business logic that can not be handled with a single simple or complex query. TSQL provides the developer with several constructs that can store temporary result sets that are passed to the next query in the script. I will be covering derived tables, local temporary tables, global temporary tables, table variables, user tables in tempdb, and common table expressions in my examples using the AdventureWorks database. The pros and cons of each contruct will be examined. Advanced options like enabling trace flag 1118 will be explored.
Speaker(s): John Miner,
Abstract:
Did you know that SQL Server keeps track of almost every call you make to the database instance? Do you know what information it collects and more importantly how to use that information effectively? Don’t get this confused with statistics used by the optimizer to generate the query plan. These are the stats gathered during the executions. In this session we will show you how to identify the query activity on your instance and allow you to make smart decisions on which ones to address first and get the biggest ROI on your precious time. Please note that most of this is applicable to SQL2008 as well.
Speaker(s): Andrew Kelly,
Abstract:
A Dynamic Dialog session: Whether you’re considering implementing business intelligence for the first time, have completed projects in the past (and are perhaps looking at later phases), or are somewhere in between, an investment in this session will likely pay dividends. Business Intelligence Architect and Microsoft MVP Bill Pearson leads a dialog about common pitfalls or traps that we can encounter in designing and implementing BI. We’ll discuss, as a group, environmental and other challenges we have experienced, and share ways to avoid or minimize them, in our ongoing efforts with BI.
Speaker(s): William E Pearson III,
Abstract:
Provision, Create, Maintain and use SQL Azure Databases
Speaker(s): Andy Roberts,
Abstract:
Matt Masson will discuss the latest and greatest in SSIS 2012. More details to be provided.
Speaker(s): Mike Walsh,
Abstract:
This highly-interactive, demo-intense presentation is for beginners and developers just getting started with SSIS. Attend and learn how to build SSIS packages from the ground up.
Speaker(s): Andy Leonard,
Abstract:
The session will cover a collection of minor bad habits that can occasionally lead to major problems.
Speaker(s): Aaron Bertrand,
Abstract:
Cube space; the final frontier. In this Star Trek themed introduction to MDX, we will discuss the fundamentals of cube structure and vocabulary, including tuples, members, sets, hierarchies, and more. We will introduce and demonstrate the basic syntax of MDX with queries that include navigating hierarchies and even some time-based expressions. This session will give you the tools you need to write simple, yet meaningful, MDX queries in your own environment.
Speaker(s): Mark Vaillancourt,
This is a list of speakers from the XML Guidebook records. The details and URLs were valid at the time of the event.
LinkedIn: http://www.linkedin.com/in/mikepwalsh
Contact: https://www.straightpathsql.com
Mike Walsh is an experienced SQL Server professional and has worked with SQL Server since version 6.5. He has been in the role of DBA, developer, and performance tuner, but always leans on his DBA and tuning experience. He has been awarded the SQL Server (now Data Platform) designation 6 times since 2011 and is a current MVP. He runs a SQL Server consultancy focused on DBA services and cloud enablement. He has spoken several times at PASS and regional events.
LinkedIn: http://www.linkedin.com/in/AndyLeonard
Contact: https://andyleonard.blog
Andy Leonard is founder and Chief Data Engineer at Enterprise Data Analytics, Microsoft Data Platform MVP, creator of the DILM (Data Integration Lifecycle Management) Suite, an SSIS trainer, consultant, developer, Business Intelligence Markup Language (Biml) developer and BimlHero, SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of "The Biml Book and SQL Server Integration Services Design Patterns" and author of "Managing Geeks - A Journey of Leading by Doing", "Data Integration Lifecycle Management with SSIS", "Building Custom Tasks for SSIS", and the "Stairway to Integration Services". Andy blogs at andyleonard.blog where you can learn more on the About Andy page.
Contact: http://blogs.msdn.com/andyroberts
Andy Roberts is a Data Platform Technology Specialist for Microsoft in the Northeast district. and has spent 12 years as a consultant for Microsoft Consulting Services. As a consultant Andy assisted customers in implementing mission critical OLTP and DW/BI solutions on SQL Server. Andy wore many hats as a consultant including application developer, solution architect, mentor to development teams, mentor to DBAs, BI lead, SCRUM Master, and the guy-that-knows-a-bunch-of-stuff-about Microsoft.
LinkedIn: http://www.linkedin.com/profile/view?id=3997984amp;trk=hb_tab_pro_top
Contact: http://joeydantoni.com
Joseph D'Antoni is a Senior Consultant and Microsoft Data Platform MVP with over 20 years of experience working in both Fortune 500 and smaller firms. He is a Principal Consultant for Denny Cherry Associates and lives in Malvern, PA. He is a frequent speaker at major tech events like Microsoft Ignite, PASS Summit, and Enterprise Data World. He blogs about all topics technology at joeydantoni.com. He believes that no single platform is the answer to all technology problems. Joseph holds a BS in Computer Information Systems from Louisiana Tech University and an MBA from North Carolina State University, and is the co-author of the Microsoft book "Introducing SQL Server 2016”.
LinkedIn: https://www.linkedin.com/e/fpf/252170891
Contact: https://mvp.support.microsoft.com/profile=02C5CD07-7511-4151-A678-3A4A3451F85E
Bill Pearson created Island Technologies Inc. in 1997, and has developed a large and diverse customer base since. Bill#39;s background as a CPA, Internal Auditor, Management Accountant, and SQL Server MVP (BI) enables him to provide value to clients as a liaison between Accounting / Finance and Information Services. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies and focuses his practice upon the integrated Microsoft Business Intelligence
LinkedIn: http://www.linkedin.com/profile/view?id=3997984amp;trk=hb_tab_pro_top
Contact: http://joeydantoni.com
Joseph D'Antoni is a Senior Consultant and Microsoft Data Platform MVP with over 20 years of experience working in both Fortune 500 and smaller firms. He is a Principal Consultant for Denny Cherry Associates and lives in Malvern, PA. He is a frequent speaker at major tech events like Microsoft Ignite, PASS Summit, and Enterprise Data World. He blogs about all topics technology at joeydantoni.com. He believes that no single platform is the answer to all technology problems. Joseph holds a BS in Computer Information Systems from Louisiana Tech University and an MBA from North Carolina State University, and is the co-author of the Microsoft book "Introducing SQL Server 2016”.
LinkedIn: http://www.linkedin.com/in/mikepwalsh
Contact: https://www.straightpathsql.com
Mike Walsh is an experienced SQL Server professional and has worked with SQL Server since version 6.5. He has been in the role of DBA, developer, and performance tuner, but always leans on his DBA and tuning experience. He has been awarded the SQL Server (now Data Platform) designation 6 times since 2011 and is a current MVP. He runs a SQL Server consultancy focused on DBA services and cloud enablement. He has spoken several times at PASS and regional events.
LinkedIn: Public Profile https://www.linkedin.com/in/andrewnovick
Contact: http://www.NovickSoftware.com
Andy Novick is a SQL Server MVP and consultant building business solutions with SQL Server. He has expertise in database design, T-SQL coding, SQL Server security, database performance tuning, partitioning, service broker, and database administration. Andy is a frequent speaker at local user group meetings and an author of two books on SQL Server.
Contact: http://www.mattmasson.com/
Matt Masson is a Program Manager in the Information Management and Machine Learning (IMML) organization. Matt has worked on multiple data integration products, including Power Query, SSIS, DQS, MDS, and the Data Management Gateway for Power BI. He has authored two books on SSIS (Apress, and MS Press), and is a frequent presenter at Microsoft conferences. You can find his blog at http://www.mattmasson.com/.
Contact: http://sqlblog.com/blogs/andrew_kelly/default.aspx
Andrew J. Kelly is a SQL Server MVP with over 20 years’ experience with relational databases and application development but specializes in Performance, Scalability and Maintainability of large scale SQL Servers. He is a regular speaker each year and a contributing editor and writer for SQL Server Magazine.
Contact: http://blogs.msdn.com/andyroberts
Andy Roberts is a Data Platform Technology Specialist for Microsoft in the Northeast district. and has spent 12 years as a consultant for Microsoft Consulting Services. As a consultant Andy assisted customers in implementing mission critical OLTP and DW/BI solutions on SQL Server. Andy wore many hats as a consultant including application developer, solution architect, mentor to development teams, mentor to DBAs, BI lead, SCRUM Master, and the guy-that-knows-a-bunch-of-stuff-about Microsoft.
LinkedIn: http://www.linkedin.com/in/kokaev/
Contact: http://bostonbi.org/Blog.aspx
Slava Kokaev is a lead BI architect. Over a 6 years he spend on Business Analytics side Modeling, Analyzing and Optimizing business processes. Designing and deploying Analytical Solutions. Last 10 years Slava is specializing in Design and Development of the BI Systems and Custom BI Applications He is the leader of the Boston Microsoft BI Users Group. And heavily involved in MS BI Community by: organizing and speaking in BI Code Camps, Firestarters, SqlSaturdays and recording video tutoria
Contact: https://blogs.sentryone.com/author/AaronBertrand
Aaron, Product Manager at SentryOne, produces performance and optimization tools for the entire Microsoft data platform, as well as Plan Explorer, a free tool for SQL Server execution plan analysis. In his spare time, he is either playing volleyball, curling, blogging at sqlperformance.com and blogs.sentryone.com, or contributing to SQL Server conversations on Twitter and dba.stackexchange.com. He has worked with SQL Server since 6.5 and has been an MVP since 1997.
LinkedIn: https://www.linkedin.com/e/fpf/252170891
Contact: https://mvp.support.microsoft.com/profile=02C5CD07-7511-4151-A678-3A4A3451F85E
Bill Pearson created Island Technologies Inc. in 1997, and has developed a large and diverse customer base since. Bill#39;s background as a CPA, Internal Auditor, Management Accountant, and SQL Server MVP (BI) enables him to provide value to clients as a liaison between Accounting / Finance and Information Services. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies and focuses his practice upon the integrated Microsoft Business Intelligence
Contact: http://www.infogoldusa.com
Steve Simon is a senior BI engineer with MyWebGrocer. He has been involved with database design and analysis for over 25 years. Steve has presented papers at eight PASS Summits and one at PASS Europe 2009 and 2010. He has just recently presented a Business Intelligence paper at Oracle Open World in San Francisco . Steve has also presented 5 papers at the Information Builders' (IBI) International Summits. He is a PASS regional mentor chair of the Oracle/SQL Server VC
LinkedIn: http://www.linkedin.com/in/markvsql/
Contact: http://markvsql.com
Mark is a Data Solutions Architect for Microsoft, helping organizations design data solutions in Azure. He has been working with Microsoft data technologies since 2007. Before joining Microsoft, he was a Business Intelligence consultant and two-time Microsoft Data Platform MVP. With a background in Theater and Improvised Comedy, Mark makes sure that his presentations are both informative and fun.
LinkedIn: http://www.linkedin.com/pub/john-miner/a/85a/889
Contact: http://www.craftydba.com
John has over twenty years of IT experience including a Masters degree in Computer Science from the University of Rhode Island with concentrations in database technologies and programming languages. He also has Microsoft Certificates for Database Administration (MCDBA) and System Admistration (MCSA). John is currently a Senior SQL Server Developer / Administrator in charge of best practices by the IT team at Sensata Technologies.
LinkedIn: http://www.linkedin.com/in/johnwflannery/
John is the Data Architect at Evolution 1 in Simsbury Connecticut – and Adjunct Professor at Quinnipiac University in Hamden, Connecticut. He has 30 years industry experience; working with SQL server since version 4.2 on OS/2, Oracle since 7.3.4. John holds a Master of Science – Computer Science degree from Rensselaer (1995). John is co-leader of the Hartford SQL User Group - and organizer of SQLSaturday 184.
Contact: http://sqlblog.com/blogs/andrew_kelly/default.aspx
Andrew J. Kelly is a SQL Server MVP with over 20 years’ experience with relational databases and application development but specializes in Performance, Scalability and Maintainability of large scale SQL Servers. He is a regular speaker each year and a contributing editor and writer for SQL Server Magazine.
LinkedIn: http://www.linkedin.com/in/AndyLeonard
Contact: https://andyleonard.blog
Andy Leonard is founder and Chief Data Engineer at Enterprise Data Analytics, Microsoft Data Platform MVP, creator of the DILM (Data Integration Lifecycle Management) Suite, an SSIS trainer, consultant, developer, Business Intelligence Markup Language (Biml) developer and BimlHero, SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of "The Biml Book and SQL Server Integration Services Design Patterns" and author of "Managing Geeks - A Journey of Leading by Doing", "Data Integration Lifecycle Management with SSIS", "Building Custom Tasks for SSIS", and the "Stairway to Integration Services". Andy blogs at andyleonard.blog where you can learn more on the About Andy page.
LinkedIn: https://www.linkedin.com/e/fpf/252170891
Contact: https://mvp.support.microsoft.com/profile=02C5CD07-7511-4151-A678-3A4A3451F85E
Bill Pearson created Island Technologies Inc. in 1997, and has developed a large and diverse customer base since. Bill#39;s background as a CPA, Internal Auditor, Management Accountant, and SQL Server MVP (BI) enables him to provide value to clients as a liaison between Accounting / Finance and Information Services. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies and focuses his practice upon the integrated Microsoft Business Intelligence
Contact: http://www.infogoldusa.com
Steve Simon is a senior BI engineer with MyWebGrocer. He has been involved with database design and analysis for over 25 years. Steve has presented papers at eight PASS Summits and one at PASS Europe 2009 and 2010. He has just recently presented a Business Intelligence paper at Oracle Open World in San Francisco . Steve has also presented 5 papers at the Information Builders' (IBI) International Summits. He is a PASS regional mentor chair of the Oracle/SQL Server VC
LinkedIn: https://www.linkedin.com/in/sunilkadimdiwan
Contact: http://www.infotrove.com/events.html
Sunil Kadimdiwan Sunil Kadimdiwan has 25+ years’ experience in architecting and implementing database solutions. He has deep knowledge of the Microsoft SQL Server and Business Intelligence technology stack. He is a frequent speaker at SQLSaturdays, Code Camps, User Group meetings
The following is a list of sponsors that helped fund the event.


--- layout: post title: Killer Drones date: 2017-11-17 08:35:13.000000000 +00:00 type: post parent_id: '0' published: true password: '' status: publish categories: [] tags: - featured meta: _rest_api_published: '1' _rest_api_client_id: "-1" _thumbnail_id: '379' _publicize_job_id: '11535161493' author: login: octagonalsquid email<EMAIL_ADDRESS>display_name: octagonalsquid first_name: '' last_name: '' ---
This is a creepy PSA from a near future.


Before Change
assert_spec("a/b:c")
assert_spec(":c")
with temporary_dir() as tmpdir:
os.mkdir(os.path.join(tmpdir, "foo"))
with pushd(tmpdir):
assert_spec("foo")
assert_not_spec("foo")
After Change
def assert_not_spec(arg: str) -> None:
assert ArgSplitter.spec(arg) is False
unambiguous_specs = [
"a/b/c",
"a/b/c/",
"a/b:c",
"a/b/c.txt",
":c",
"::",
"a/",
"./a.txt",
".",
]
for s in unambiguous_specs:
assert_spec(s)
directories_vs_goals = ["foo", "a_b_c"]
files_vs_subscopes = ["cache.java", "cache.tmp.java"]
ambiguous_specs = [*directories_vs_goals, *files_vs_subscopes]
for spec in ambiguous_specs:


Ambient properties are named characteristics of the container that apply to all controls in the container unless otherwise specified. Examples of ambient properties are default colors and fonts.
To change ambient properties
1. From the Container menu, choose Ambient Properties.
2. Choose a property name.
3. When Enabled is checked, you can specify appropriate values for the property value and/or property type.
4. Choose Set Value. If the Property Type is VT_COLOR or VT_FONT, select a color or font.
To add a non-standard property
1. From the Container menu, choose Ambient Properties.
2. Click the New Property… button.
3. Enter a property name and DISPID.
4. Click OK.


Elements consist of content bookended by a start tag and an end tag. Elements consist of content bookended by a start tag and an end tag.