I have a tibble with exchange quotations that give me 5 buying price values and 5 selling price values per day (or 5 rows with 2 columns per day)
I'm trying to make an iterative solution where I cycle through the values, calculating sum and average for every 5 rows and save the result in a new column, repeated for the specific rows, so, for example:
buyPrice <- c(1:10)
sellPrice <- c(11:20)
exchange <- tibble(buyPrice, sellPrice)
# A tibble: 10 × 2
buyPrice sellPrice
<int> <int>
1 1 11
2 2 12
3 3 13
4 4 14
5 5 15
6 6 16
7 7 17
8 8 18
9 9 19
10 10 20
In this case, I would expect, after the work that I would have an averageBuy
and an averageSell
vector that I could appent to the tibble as new columns with the average values for every five rows:
# A tibble: 10 × 4
buyPrice sellPrice averageBuy averageSell
<int> <int> <dbl> <dbl>
1 1 11 3 13
2 2 12 3 13
3 3 13 3 13
4 4 14 3 13
5 5 15 3 13
6 6 16 8 18
7 7 17 8 18
8 8 18 8 18
9 9 19 8 18
10 10 20 8 18
Comming from other language, I was trying something in the veins of:
getAverageByDay <- function(dataSet, columnNumber) {
temporarySum <- 0
average <- c()
dataSetRows <- nrow(dataSet)
averageList <- rep(0,dataSetRows)
for (i in 1:dataSetRows) {
if (i %% 5 == 0) {
temporarySum <- temporarySum + dataSet[[i,columnNumber]]
average <- temporarySum / 5
averageList[(i-4):i] <- rep(average,5)
temporarySum <- 0
average <- 0
} else {
temporarySum <- temporarySum + dataSet[[i,columnNumber]]
}
}
return(averageList)
}
averageBuy <- getAverageByDay(exchange, 1)
averageSell <- getAverageByDay(exchange, 2)
Where I would append the resulting vectors as the new columns in the tibble. Everything else works, but this assignment that never happens:
averageList[(i-4):i] <- rep(average,5)
Which in turn returns NULL
from the function.
If there's something more r specific than a for loop, I'd very much appreciate to know. Also, not very expensive if possible. Thanks
This is how I would solve the problem.
library(dplyr)
buyPrice <- c(1:10)
sellPrice <- c(11:20)
exchange <- tibble(buyPrice, sellPrice)
exchange |>
mutate(group = cumsum(row_number() %% 5 == 1)) |>
mutate(
averageBuy = mean(buyPrice),
averageSell = mean(sellPrice),
.by = group
)
#> # A tibble: 10 × 5
#> buyPrice sellPrice group averageBuy averageSell
#> <int> <int> <int> <dbl> <dbl>
#> 1 1 11 1 3 13
#> 2 2 12 1 3 13
#> 3 3 13 1 3 13
#> 4 4 14 1 3 13
#> 5 5 15 1 3 13
#> 6 6 16 2 8 18
#> 7 7 17 2 8 18
#> 8 8 18 2 8 18
#> 9 9 19 2 8 18
#> 10 10 20 2 8 18
Created on 2024-05-12 with reprex v2.0.2