Split a table by ticker with group_by, collapse each group to one row with summarise, and run a return calculation inside each ticker so no return crosses from one ticker into the next.
group_by() splits a table by the values in a column, runs your calculation separately inside each piece, and puts the answers back together. Nothing about the calculation changes. Only the stretch of rows it sees does.
In this lesson I do it twice on the price file: once with summarise(), which turns 120 rows into 3, and once with mutate(), which keeps all 120 rows and stops a return being computed across the join between two tickers.
Step 1. Load the file and sort it
The file is simulated. read.csv() hands back Date as character, so as.Date() turns it into a real date that arithmetic and comparisons work on later.
I sort by Ticker then Date, so all forty AAA rows sit together in date order, then CCC, then DDD.
library(dplyr) # group_by, summarise, mutate, lagpx <-read.csv("prices.csv") # simulated daily closes for three tickerspx$Date <-as.Date(px$Date) # character to datepx <- px |>arrange(Ticker, Date) # AAA block, then CCC, then DDDprint(dim(px)) # -> [1] 120 4
[1] 120 4
print(head(px, 3))
Date Ticker Close Volume
1 2026-01-02 AAA 186.66 2183494
2 2026-01-05 AAA 186.38 8463199
3 2026-01-06 AAA 188.51 5299573
table() counts rows per value, which is a quick check that each ticker brought the same number of days.
print(table(px$Ticker))
AAA CCC DDD
40 40 40
# -># -> AAA CCC DDD# -> 40 40 40
Step 2. One row per ticker with summarise
summarise() collapses each group to a single row. n() counts the rows in the group, and mean() and last() see only that group’s closes. Three groups in, three rows out.
summarise() returns a tibble, which prints numbers rounded to three significant figures. as.data.frame() prints the values in full.
by_ticker <- px |>group_by(Ticker) |># split into three blocks of 40 rowssummarise(n =n(), # rows in this tickeravg =round(mean(Close), 2), # average close for this tickerlast =last(Close)) # final close, since rows are date sortedprint(dim(by_ticker)) # -> [1] 3 4
[1] 3 4
print(as.data.frame(by_ticker))
Ticker n avg last
1 AAA 40 183.69 169.42
2 CCC 40 437.15 435.94
3 DDD 40 112.36 107.57
last(Close) means the last row in the group, not the highest date. It gives the closing price only because I sorted by Date in Step 1. Sort first, then use last().
Step 3. The same three averages in base R
Base R does the summarise job in one line, two different ways.
print(round(tapply(px$Close, px$Ticker, mean), 2)) # split Close by Ticker, apply mean
AAA CCC DDD
183.69 437.15 112.36
# -> AAA CCC DDD# -> 183.69 437.15 112.36print(aggregate(Close ~ Ticker, data = px, FUN = mean)) # same thing, as a data frame
tapply() returns a named vector. aggregate() returns a data frame. Both match the summarise() column. For one statistic on one column either is shorter than the dplyr version; summarise() wins as soon as you want several statistics at once, as in Step 2.
Step 4. mutate inside a group
summarise() throws rows away. mutate() keeps every row and adds a column, and grouping decides which rows the calculation can reach back into.
In Lesson 15 I ran Close / lag(Close) - 1 on this stacked table with no grouping, and CCC’s first day came back as a 146% return, because lag() reached across the join and handed it AAA’s last close. Grouping first stops that. arrange(Date, .by_group = TRUE) sorts inside each group rather than across the whole table, so each ticker’s rows are in date order before lag() runs.
g <- px |>group_by(Ticker) |># three blocks, one per tickerarrange(Date, .by_group =TRUE) |># date order within each blockmutate(ret = Close /lag(Close) -1) |># lag stops at the block edgeungroup() # drop the grouping againprint(as.data.frame(g[39:42, c("Date", "Ticker", "Close", "ret")]))
Date Ticker Close ret
1 2026-02-25 AAA 171.42 -0.007066728
2 2026-02-26 AAA 169.42 -0.011667250
3 2026-01-02 CCC 416.98 NA
4 2026-01-05 CCC 423.19 0.014892801
Row 3 is NA. CCC’s first day has no CCC day before it, so there is no return to compute, exactly as the first day of a single ticker has none. The two AAA rows above it and the CCC row below it hold the same numbers Lesson 15 printed. Only the boundary row moved.
Count the NAs.
print(sum(is.na(g$ret))) # -> [1] 3 <- first row of each ticker
[1] 3
Three tickers, three first days, three missing returns. The ungrouped version in Lesson 15 had one. That count is how I check the grouping took.
Step 5. ungroup when you are done
mutate() on a grouped table hands back a table that is still grouped, and every later verb obeys that grouping until you remove it.
h <- px |>group_by(Ticker) |>mutate(ret = Close /lag(Close) -1) # no ungroup hereprint(class(h)[1]) # -> [1] "grouped_df"
The same filter() gives six rows or two. Grouped, row_number() restarts inside each ticker and you get the first two rows of all three. Ungrouped, you get the first two rows of the table. summarise() drops one level of grouping for you; mutate(), filter() and arrange() do not. Call ungroup() as soon as the per-group work is finished, as I did in Step 4, and the next line does what it looks like it does.
Your turn
Starting from g, count the returns per ticker, and find the best and worst day for each. Use sum(!is.na(ret)) to count and na.rm = TRUE on max() and min(), since each ticker has one NA.
TipShow answer
ans <- g |>group_by(Ticker) |># back into three blockssummarise(days =sum(!is.na(ret)), # returns, not rowsbest =round(max(ret, na.rm =TRUE), 4), # best day for this tickerworst =round(min(ret, na.rm =TRUE), 4)) # worst dayprint(as.data.frame(ans))# -> Ticker days best worst# -> 1 AAA 39 0.0272 -0.0357# -> 2 CCC 39 0.0272 -0.0195# -> 3 DDD 39 0.0402 -0.0651
40 rows each, 39 returns each. Without na.rm = TRUE every best and worst would be NA, because one NA in the input makes max() return NA.
Lesson 20 puts two tables together on a shared column.