Print out some of the data table

``r dt = data.table(id = 1:5, wage = c(18, 31, 44, 22, 27), educ = c(12, 16, 14, 12, 18), female dt

This doesn't modify anything: it just prints out the first few rows of the table. 

## Variations on the basic theme

Print out a few subsets of the data table, or a collapsed summary of the data table.
```r
dt[wage > 20]                                  # i argument only means, "limit the sample to rows where wage > 20"
dt[, mean(wage)]                               # empty i means us all rows, j means take the average of the wage over all rows. 
dt[, .(avg = mean(wage)), by = female]         # collapse by sex, take the avrage wage.
dt[educ >= 16, .(avg = mean(wage)), by = female]  # all three

• Inside the brackets you refer to columns by bare name, with no dt$
• Slots you do not need, you leave empty
• None of these commands alter dt itself. They return a new table or a single value, and leave dt alone

Changing the table

# limit the sample to rows where wage > 20 and assign the result back to dt. This "deletes" rows where wage <= 20.
dt = dt[wage > 20]       


# limit the sample to rows where educ >= 16
# collapse by sex, take the average wage 
# assign the result back to dt. 
# This "deletes" rows where educ < 16 and overwrites the original dt with the new collapsed summary.
dt =dt[educ >= 16, .(avg = mean(wage)), by = female]  

Filtering

dt[educ >= 16]                  # college grads
dt[female == 1 & married == 1]  # married women
dt[wage > 20 | educ > 12]       # either condition
dt[1:10]                        # the first ten rows

Filtering on Missing Values

dt[!is.na(wage)]           # drop the missing
dt[is.na(wage)]            # look at what you would be dropping

Sorting

dt[order(wage)]        # low to high
dt[order(-wage)]       # high to low
dt[order(educ, -wage)] # by education, then wage within it

• order() in the i slot sorts the result, and leaves dt itself alone
• To sort the table itself, data.table has a by-reference version:

setorder(dt, educ, -wage)   # dt is now sorted; no assignment needed

• Note the set prefix. That is the tell that something changed in place

j Does More Than One Job

The j slot is where you say what you want back.

dt[, .(wage, educ)]           # select columns
dt[, .(lwage = log(wage))]    # compute a new one
dt[, mean(wage)]              # a single number
dt[, .(avg = mean(wage), n = .N)]   # a small summary table

• .() is shorthand for list(), and it bundles the results into columns
• Without .(), a bare expression returns a raw value
• .N is the number of rows in the current group.

Selecting and Dropping

dt[, .(name, wage, educ)]      # keep three columns
dt[, !c("wage", "educ")]       # drop two, keep the rest

cols <- c("wage", "educ")
dt[, ..cols]                   # select using a character vector

• ..cols means “look for cols outside the table, not for a column named cols”
• The two dots read like ../ in a file path, one level up

Two Ways to Change an Object

Base R usually works on copy-on-modify principles.

• When you change an object, R quietly makes a whole new copy of the object with the change in it
• Safe and easy to reason about.
• But is slow and uses a lot of memory because you have a lot of copies floating around in the background.

data.table works on modify-by-reference principles, where possible.

• Under modify-by-reference, When you change an object, the change happens to the existing object.
• No copy is made.
• Faster. And uses less memory.
• But there are some weird consequences, which you will see in a moment.

Renaming

# rename in place, by reference
setnames(dt, old = c("educ", "exper"), new = c("years_school", "years_work"))

# or rename on the fly, only in the result
dt[1:5, .(schooling = educ, pay = wage)]

• setnames() changes the table itself; the set prefix again
• Renaming inside .() changes only what comes back, which is often what you want

The Walrus Operator

:= adds, changes, or deletes a column in place.

dt[, lwage := log(wage)]     # add a column
dt[, age_sq := age^2]        # add another
dt[, wage := wage / 1000]    # overwrite an existing one

• The column is written into dt directly
• There is no assignment arrow anywhere.

It Does Not Print

dt[, lwage := log(wage)]      # runs, changes dt, prints nothing

dt[, lwage := log(wage)][]    # the trailing [] prints the result

• Add [] on the end when you want to see what you just did

Several Columns at Once

# LHS := RHS form
dt[, c("lwage", "age_sq") := .(log(wage), age^2)]

# functional form
dt[, ':=' (lwage = log(wage), age_sq = age^2)]

• One caution: you cannot use a column you are creating in the same call

dt[, ':=' (z = 5:6, z_sq = z^2)]   # error: z does not exist yet
dt[, z := 5:6][, z_sq := z^2]      # chain instead

Deleting a Column

dt[, lwage := NULL]                       # gone
dt[, c("lwage", "age_sq") := NULL]        # both gone

• Assigning NULL removes the column from the table, in place

Sub-Assign by Reference

Give := an i, and it changes only those rows.

dt2 <- data.table(a = -2:2, b = LETTERS[1:5])

# where a is negative, set b to NA
dt2[a < 0, b := NA][]
#>     a      b
#> 1: -2   <NA>
#> 2: -1   <NA>
#> 3:  0      C
#> 4:  1      D
#> 5:  2      E

• Say which rows in i, then assign in j. Nothing else in the table is touched
• Only the targeted cells are rewritten
• Nice way to recode: dt[wage > 100, wage := NA]

Try it with a data table using $ to modify

dt <- data.table(x = 1:5, y = 6:10)
pryr::address(dt)         # address of the object df points to
dt_copy <- dt             # another name for the same object
pryr::address(dt_copy)    # address of the object df_copy points to
dt$x <- dt$x * 10         # change the object df points to
pryr::address(dt)         # address of the object df points to
pryr::address(dt_copy)    # address of the object df_copy points to

The $ operator is copy-on-modify, so it creates a new copy of the data.table when you modify it.

Now try it with the walrus operator :=

:= modifies in place, so a second name for a table is not a second table.

dt <- data.table(x = 1:5, y = 6:10)
pryr::address(dt)         # address of the object df points to
dt_copy <- dt             # another name for the same object
pryr::address(dt_copy)    # address of the object df_copy points to
dt[, x := x * 10]         # change the object df points to
pryr::address(dt)         # address of the object df points to
pryr::address(dt_copy)    # address of the object df_copy points to 

• If you compare dt and dt_copy you will see that x has been mulitiplied by 10 in both dt and dt_copy
• That is modify-by-reference in action

Making a Real Copy in data.table

dt_copy <- copy(dt)      # an actual, separate copy
dt_copy[, wage := NULL]  # dt is untouched

• copy() is explicit, and it is the only way to get a genuinely independent table
• It costs memory, which is exactly why data.table will not do it behind your back

Chaining

Answer a multi-step question by hanging brackets off each other.

dt[married == 1
   ][, .(avg_wage = mean(wage)), by = educ
   ][order(-avg_wage)]

• Each [...] takes the result of the one before it
• Read it as a sentence: filter, then summarize by group, then sort

by

Add by, and j runs separately within each group.

# average wage by education level
dt[, .(avg_wage = mean(wage)), by = educ]

# count and average by two grouping variables
dt[, .(n = .N, avg_wage = mean(wage)),
   by = .(female, married)]

• Name your summaries inside .(), or you get a column called V1
• Group by several variables with .(), the same way you bundle columns

Grouping on an Expression

dt[, .(avg_wage = mean(wage)), by = .(college = educ >= 16)]

• The grouping variable does not have to exist in the data
• Name it inside .() and the result has a readable column instead of educ >= 16

Grouped Assignment

Combine by with := to write a group-level number onto every row.

# each person's education-group mean, on their own row
dt[, grp_mean := mean(wage), by = educ]

# group sizes
dt[, n_in_grp := .N, by = educ]

# deviation from the group mean
dt[, wage_dev := wage - mean(wage), by = educ]

Many Columns at Once: .SD

You want the mean of six columns.

cols <- c("wage", "educ", "exper")

dt[, lapply(.SD, mean, na.rm = TRUE),
   by = female, .SDcols = cols]

• .SD is the Subset of the Data: the current group’s rows, as a table
• .SDcols limits it to the columns you name
• lapply(.SD, mean) runs mean on each of those columns in turn

.SD, Continued

# all numeric columns, no list needed
dt[, lapply(.SD, mean, na.rm = TRUE), .SDcols = is.numeric]

# transform several columns in place
dt[, (cols) := lapply(.SD, as.numeric), .SDcols = cols]

• .SDcols accepts a function like is.numeric, or a range like wage:exper
• The parentheses in (cols) := say “use the values in cols as the column names”

keyby

dt[, .(avg_wage = mean(wage)), keyby = educ]

• keyby works exactly like by, and also sorts the result and sets a key
• Sorted output is usually what you wanted anyway

What is a Key?

A key is an ordering of the rows that makes subsetting extremely fast.

• Normally, finding matching rows means checking every row in the table
• With a key, the data are already sorted, so R can jump to the right block and stop

Setting a Key

setkey(dt, id)              # one key column, unquoted
setkey(dt, state, year)     # or several

key(dt)                     # what is the key right now?
#> [1] "state" "year"

• setkey() sorts the table in place, so there is the set prefix once more
• A table has one key at a time, and setting a new one replaces the old

Joining Two Tables

# people: one row per person, with a state code
# states: one row per state, with a policy variable

merged <- merge(x = people, y = states,
                by = "state", all.x = TRUE)

• Always name x = and y = so the direction of the join is unambiguous
• by = is the key column both tables share
• all.x = TRUE keeps every row of x, matched or not. That is a left join

The Join Types

Long and Wide

• Wide: one row per unit, with a column per time period
• Long: one row per unit-period, with the value in a single column

melt: Wide to Long

wide <- data.table(id    = c(1, 2),
                   y2019 = c(10, 20),
                   y2020 = c(11, 22))

long <- melt(wide, id.vars = "id",
             variable.name = "year",
             value.name = "y")
#>    id  year  y
#> 1:  1 y2019 10
#> 2:  2 y2019 20
#> 3:  1 y2020 11
#> 4:  2 y2020 22

• id.vars names the columns that stay. Everything else gets stacked
• Name the two new columns, or you get variable and value

dcast: Long to Wide

back <- dcast(long, id ~ year, value.var = "y")
#>    id y2019 y2020
#> 1:  1    10    11
#> 2:  2    20    22

• The formula reads rows ~ columns: id ~ year means one row per id, one column per year
• value.var says which column fills the cells
• dcast can aggregate as it goes: dcast(long, id ~ year, fun.aggregate = mean)

Data Cleaning: Read, transform, filter, merge, check, summarize.

pacman::p_load(data.table, here)

dt <- fread(here("raw_data", "wages.csv"))

dt[, lwage := log(wage)]
dt <- dt[!is.na(lwage) & educ > 0]

panel <- merge(x = dt, y = states, by = "state", all.x = TRUE)
stopifnot(nrow(panel) == nrow(dt))

panel[, .(avg_lwage = mean(lwage), n = .N),
      keyby = .(state, female)]

Recap

• dt[i, j, by] — rows, then compute, then within groups
• := and set assigns by reference
• By reference means a second name is not a second table.
• by groups, .SD handles many columns, setkey() makes the big jobs fast
• merge(x =, y =, by =, all.x =) joins, and you check nrow() afterwards
• melt() goes wide to long, dcast() goes back