What is a data frame?

Build a table from equal-length vectors, pull rows, columns and blocks out of it, add a computed column, sort it, and turn position values into weights.

A data frame is a table: several equal-length vectors laid side by side, one per column. Lesson 10 ended by saying it is a list whose elements are columns, and that is literally true. The list syntax you already know still works on it.

In this lesson I build a small book of positions as a data frame, take rows, columns and blocks out of it, then add a value column, sort by it, keep the rows I want, and finish with weights that sum to 1.

Step 1. Build one from vectors

data.frame() takes named vectors and stacks them as columns. Every vector must be the same length, because a table cannot have a ragged edge. stringsAsFactors = FALSE keeps the tickers as plain text. Without it, older R turned character columns into factors.

df <- data.frame(
  Ticker = c("AAA", "CCC", "DDD"),   # character column
  Close  = c(185.40, 410.20, 128.75),   # numeric column
  Shares = c(10, 4, 20),                # numeric column
  stringsAsFactors = FALSE              # keep Ticker as text, not a factor
)

print(df)

  Ticker  Close Shares
1    AAA 185.40     10
2    CCC 410.20      4
3    DDD 128.75     20

# ->   Ticker  Close Shares
# -> 1    AAA 185.40     10
# -> 2    CCC 410.20      4
# -> 3    DDD 128.75     20

The 1 2 3 down the left are row names, not a column. R prints them for you and right-aligns each column under its name.

Step 2. Ask it about its own shape

Four functions tell you what you are holding.

print(nrow(df))     # -> [1] 3           <- rows, one per position

[1] 3

print(ncol(df))     # -> [1] 3           <- columns

[1] 3

print(dim(df))      # -> [1] 3 3         <- rows then columns

[1] 3 3

print(names(df))    # -> [1] "Ticker" "Close"  "Shares"

[1] "Ticker" "Close"  "Shares"

str() gives the whole picture in four lines: the size, then one line per column with its type.

str(df)

'data.frame':   3 obs. of  3 variables:
 $ Ticker: chr  "AAA" "CCC" "DDD"
 $ Close : num  185 410 129
 $ Shares: num  10 4 20

# -> 'data.frame':  3 obs. of  3 variables:
# ->  $ Ticker: chr  "AAA" "CCC" "DDD"
# ->  $ Close : num  185 410 129
# ->  $ Shares: num  10 4 20

chr is character, num is numeric. The $ bullets are the same ones str() printed for a list in Lesson 10, and length() confirms why.

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

[1] "data.frame"

print(length(df))   # -> [1] 3           <- length counts columns, not rows

[1] 3

A data frame is a list of three columns, so its length is 3. str() rounds 185.4 to 185 for display only.

Step 3. Pull a column out

$ hands you the column as a plain vector. [[ ]] does exactly the same, which is the list syntax from Lesson 10 applied to a table.

print(df$Close)                            # -> [1] 185.40 410.20 128.75

[1] 185.40 410.20 128.75

print(df[["Close"]])                       # -> [1] 185.40 410.20 128.75

[1] 185.40 410.20 128.75

print(class(df$Close))                     # -> [1] "numeric"

[1] "numeric"

print(identical(df$Close, df[["Close"]]))  # -> [1] TRUE

[1] TRUE

Single brackets behave the way they did on a list: they select part of the object and hand back the same kind of object.

print(df["Close"])

   Close
1 185.40
2 410.20
3 128.75

# ->    Close
# -> 1 185.40
# -> 2 410.20
# -> 3 128.75

print(class(df["Close"]))    # -> [1] "data.frame"    <- a one-column table

[1] "data.frame"

df[["Close"]] is a vector you can multiply. df["Close"] is a table with one column.

Step 4. Rows, columns and blocks

A data frame takes two indexes, df[rows, columns]. Leave one side empty and R keeps all of it.

print(df[1, ])              # first row, every column

  Ticker Close Shares
1    AAA 185.4     10

# ->   Ticker Close Shares
# -> 1    AAA 185.4     10

print(df[, "Close"])        # -> [1] 185.40 410.20 128.75    <- every row, one column

[1] 185.40 410.20 128.75

Give both sides and you get a block.

print(df[2:3, c("Ticker", "Close")])   # rows 2 and 3, two named columns

  Ticker  Close
2    CCC 410.20
3    DDD 128.75

# ->   Ticker  Close
# -> 2    CCC 410.20
# -> 3    DDD 128.75

The row names stayed 2 and 3. They label the original rows, so they no longer match the printed order once you subset.

Step 5. Add a column, sort, keep rows

A new column is an assignment to a name that does not exist yet. The arithmetic runs element by element down the two columns, as it does on any pair of vectors.

df$Value <- df$Close * df$Shares   # price times shares, row by row

print(df)

  Ticker  Close Shares  Value
1    AAA 185.40     10 1854.0
2    CCC 410.20      4 1640.8
3    DDD 128.75     20 2575.0

# ->   Ticker  Close Shares  Value
# -> 1    AAA 185.40     10 1854.0
# -> 2    CCC 410.20      4 1640.8
# -> 3    DDD 128.75     20 2575.0

order() does not sort. It returns the row positions in sorted order, and you feed those to the row index. A minus sign in front sorts from large to small.

print(order(-df$Value))       # -> [1] 3 1 2      <- biggest position is row 3

[1] 3 1 2

print(df[order(-df$Value), ]) # rows in that order, every column

  Ticker  Close Shares  Value
3    DDD 128.75     20 2575.0
1    AAA 185.40     10 1854.0
2    CCC 410.20      4 1640.8

# ->   Ticker  Close Shares  Value
# -> 3    DDD 128.75     20 2575.0
# -> 1    AAA 185.40     10 1854.0
# -> 2    CCC 410.20      4 1640.8

To keep rows, put a logical vector in the row slot. This is the logical indexing from Lesson 5, now choosing rows instead of elements.

print(df$Value > 2000)        # -> [1] FALSE FALSE  TRUE     <- one TRUE per row

[1] FALSE FALSE  TRUE

print(df[df$Value > 2000, ])  # keep the rows where it is TRUE

  Ticker  Close Shares Value
3    DDD 128.75     20  2575

# ->   Ticker  Close Shares Value
# -> 3    DDD 128.75     20  2575

One row survived. Lower the threshold and two do.

print(df[df$Value > 1700, ])  # same idea, looser test

  Ticker  Close Shares Value
1    AAA 185.40     10  1854
3    DDD 128.75     20  2575

# ->   Ticker  Close Shares Value
# -> 1    AAA 185.40     10  1854
# -> 3    DDD 128.75     20  2575

Step 6. Weights that sum to 1

Divide each position value by the total and you have the weight of the book held in each name.

df$Weight <- df$Value / sum(df$Value)   # each value over the book total

print(df)

  Ticker  Close Shares  Value    Weight
1    AAA 185.40     10 1854.0 0.3054466
2    CCC 410.20      4 1640.8 0.2703219
3    DDD 128.75     20 2575.0 0.4242314

# ->   Ticker  Close Shares  Value    Weight
# -> 1    AAA 185.40     10 1854.0 0.3054466
# -> 2    CCC 410.20      4 1640.8 0.2703219
# -> 3    DDD 128.75     20 2575.0 0.4242314

print(round(df$Weight, 3))   # -> [1] 0.305 0.270 0.424

[1] 0.305 0.270 0.424

print(sum(df$Weight))        # -> [1] 1

[1] 1

The weights sum to 1 because every value was divided by the sum of all of them. Those are the same three numbers Lesson 10 got from a list of lists.

A tibble from the tidyverse is a data frame that prints differently and never converts text to factors. Everything in this lesson works on one. I stay with data.frame here.

Lesson 12 reads a table off disk instead of typing it in.

Your turn

Add a fourth position to df: 30 shares of BBB at 92.50. rbind() stacks a new one-row data frame under the old rows. Rebuild Value and Weight, sort by weight from large to small, and check the weights still sum to 1.

Show answer

df2 <- rbind(df[, c("Ticker", "Close", "Shares")],            # drop the computed columns first
             data.frame(Ticker = "BBB", Close = 92.50, Shares = 30))

df2$Value  <- df2$Close * df2$Shares                          # value each position
df2$Weight <- df2$Value / sum(df2$Value)                      # each as a share of the book

print(nrow(df2))                                              # -> [1] 4
print(df2[order(-df2$Weight), ])                              # heaviest position first
# ->   Ticker  Close Shares  Value    Weight
# -> 4    BBB  92.50     30 2775.0 0.3137437
# -> 3   DDD 128.75     20 2575.0 0.2911315
# -> 1   AAA 185.40     10 1854.0 0.2096147
# -> 2   CCC 410.20      4 1640.8 0.1855101

print(sum(df2$Weight))                                        # -> [1] 1
print(df2[df2$Weight > 0.25, c("Ticker", "Weight")])          # the two big holdings
# ->   Ticker    Weight
# -> 3   DDD 0.2911315
# -> 4    BBB 0.3137437