What is data.table and fread?

Read the price file with fread, which hands back a data.table with the dates already parsed, and write row, column and group work in the DT[i, j, by] form.

data.table is a data frame with a second indexing syntax bolted on. fread() is its file reader: it reads a csv and hands back a data.table with every column type already worked out, dates included.

In this lesson I read the same simulated price file with fread(), put read.csv() next to it, and rewrite three calls from Lessons 13 and 19 in the DT[i, j, by] form.

Step 1. fread reads the file and the dates

prices.csv sits next to this lesson: simulated daily closes for four tickers from 2020 to 2025.

library(data.table)

dt <- fread("prices.csv")   # read the simulated price file

print(class(dt))            # -> [1] "data.table" "data.frame"

[1] "data.table" "data.frame"

print(dim(dt))              # -> [1] 6264    4

[1] 6264    4

print(class(dt$Date))       # -> [1] "IDate" "Date"

[1] "IDate" "Date"

A data.table is also a data.frame, so everything the earlier lessons did to a data frame still works on this. The Date column came back as IDate, data.table’s date held as an integer, and class() lists Date after it.

Lesson 12 needed a second line to get there.

base <- read.csv("prices.csv")    # the Lesson 12 reader
print(class(base$Date))           # -> [1] "character"

[1] "character"

base$Date <- as.Date(base$Date)   # the conversion Lesson 12 wrote by hand
print(class(base$Date))           # -> [1] "Date"

[1] "Date"

One call against two, and the difference is the date column.

Printing a data.table puts a row of types under the header.

print(head(dt, 4))

         Date Ticker  Close   Volume
       <IDat> <char>  <num>    <int>
1: 2020-01-01    AAA  75.08 25594073
2: 2020-01-01    BBB 144.61 10012162
3: 2020-01-01    CCC 160.26 24917606
4: 2020-01-01    DDD  58.98 27663847

# ->          Date Ticker  Close   Volume
# ->        <IDat> <char>  <num>    <int>
# -> 1: 2020-01-01    AAA  75.08 25594073
# -> 2: 2020-01-01    BBB 144.61 10012162
# -> 3: 2020-01-01    CCC 160.26 24917606
# -> 4: 2020-01-01    DDD  58.98 27663847

<IDat> is the date, <char> character, <num> double, <int> integer. That is what str() told you in Lesson 12, printed on every table instead of on request. The row numbers carry a colon.

Step 2. The DT[i, j, by] shape

Three slots inside one pair of brackets. i picks rows. j says what to compute. by splits the rows into groups and runs j inside each one. Leave a slot empty and it does nothing.

i on its own is a row filter, and it needs no comma, because there is nowhere else for the expression to go.

aapl <- dt[Ticker == "AAA"]   # i only: keep the AAA rows

print(dim(aapl))               # -> [1] 1566    4

[1] 1566    4

print(head(aapl, 3))

         Date Ticker Close   Volume
       <IDat> <char> <num>    <int>
1: 2020-01-01    AAA 75.08 25594073
2: 2020-01-02    AAA 76.94 23185470
3: 2020-01-03    AAA 77.34 13469734

# ->          Date Ticker Close   Volume
# ->        <IDat> <char> <num>    <int>
# -> 1: 2020-01-01    AAA 75.08 25594073
# -> 2: 2020-01-02    AAA 76.94 23185470
# -> 3: 2020-01-03    AAA 77.34 13469734

That is filter(Ticker == "AAA") from Lesson 13, and 1566 rows is what Lesson 13 counted.

j computes. Wrap it in .() and each name becomes a column of the answer.

print(dt[, .(mean_close = mean(Close))])                    # j only: one number

   mean_close
        <num>
1:   238.2686

# ->    mean_close
# ->         <num>
# -> 1:   238.2686

print(dt[Ticker == "AAA", .(mean_close = mean(Close))])    # i and j together

   mean_close
        <num>
1:   121.7021

# ->    mean_close
# ->         <num>
# -> 1:   121.7021

That is summarise(), and the second call is filter() then summarise() in one line. 238.2686 mixes four tickers, so it is not a price anything traded at.

by is the third slot.

print(dt[, .(n = .N, last = last(Close)), by = Ticker])   # split by ticker, then compute

   Ticker     n   last
   <char> <int>  <num>
1:    AAA  1566 199.30
2:    BBB  1566 299.38
3:    CCC  1566 828.51
4:    DDD  1566 219.73

# ->    Ticker     n   last
# ->    <char> <int>  <num>
# -> 1:    AAA  1566 199.30
# -> 2:    BBB  1566 299.38
# -> 3:    CCC  1566 828.51
# -> 4:    DDD  1566 219.73

Here is Lesson 19 doing the same job on the data frame from Step 1.

library(dplyr)

same <- base |>
  group_by(Ticker) |>                            # split into four blocks
  summarise(n = n(), last = last(Close))         # collapse each to one row

print(as.data.frame(same))

  Ticker    n   last
1    AAA 1566 199.30
2    BBB 1566 299.38
3    CCC 1566 828.51
4    DDD 1566 219.73

# ->   Ticker    n   last
# -> 1    AAA 1566 199.30
# -> 2    BBB 1566 299.38
# -> 3    CCC 1566 828.51
# -> 4    DDD 1566 219.73

Same four rows, same closes. group_by() moved into by, summarise() into j, n() into .N.

last(Close) returns the bottom row of each group, not the highest date, exactly as in Lesson 19. It gives the closing price because the file arrives in date order.

Step 3. .N and .SD

.N is the number of rows data.table is currently looking at. In j with no by that is the whole table; behind an i filter it is the rows that survived.

print(dt[, .N])                                    # -> [1] 6264

[1] 6264

print(dt[Close > 200, .N])                         # -> [1] 2568

[1] 2568

print(dt[Ticker == "DDD" & Close > 200, .N])      # -> [1] 255

[1] 255

nrow() gives the first number too. .N counts after i without building the filtered table.

.SD is the current group, as a table of its own, holding the columns named in .SDcols. Run a function down its columns with lapply().

print(dt[, lapply(.SD, mean), by = Ticker,        # mean of every .SD column
         .SDcols = c("Close", "Volume")])          # the two columns to average

   Ticker    Close   Volume
   <char>    <num>    <num>
1:    AAA 121.7021 21883038
2:    BBB 222.0658 21651077
3:    CCC 465.7275 21506874
4:    DDD 143.5791 21359348

# ->    Ticker    Close   Volume
# ->    <char>    <num>    <num>
# -> 1:    AAA 121.7021 21883038
# -> 2:    BBB 222.0658 21651077
# -> 3:    CCC 465.7275 21506874
# -> 4:    DDD 143.5791 21359348

AAA’s 121.7021 is the number Step 2 got from dt[Ticker == "AAA", .(mean_close = mean(Close))]. Two columns and four tickers, one line, and naming a third column in .SDcols changes nothing else.

Your turn

For each ticker, count the rows, count the days the close finished above 200, and take the average volume rounded to whole shares. One DT[i, j, by] call.

Show answer

ans <- dt[, .(days    = .N,                       # rows in this ticker
              above   = sum(Close > 200),         # TRUE counts as 1
              avg_vol = round(mean(Volume))),     # average shares traded
          by = Ticker]                            # one row per ticker

print(ans)
# ->    Ticker  days above  avg_vol
# ->    <char> <int> <int>    <num>
# -> 1:   AAA  1566     3 21883038
# -> 2:    BBB  1566   957 21651077
# -> 3:   CCC  1566  1353 21506874
# -> 4:   DDD  1566   255 21359348

AAA closed above 200 on 3 of its 1566 days. CCC did it on 1353.

Lesson 31 uses := and shift() to add a return column per ticker to this table.