[R-SIG-Finance] apply a function to a data.frame column with condition

[Enrico Schumann]

On Thu, 06 Mar 2025, Arnaud Gaboury writes:

> On Thu, 2025-03-06 at 07:29 +0100, Enrico Schumann wrote:
>> On Wed, 05 Mar 2025, Arnaud Gaboury writes:
>> 
>> > I work with Binancer [1] and PMwR [2] packages to write a trading
>> > journal.
>> > Here is a sample of my data frame:
>> > portfolio <- data.frame(
>> >   symbol = c("BTCUSDT", "BTCUSDT", "ETHUBTC", "AAVEBTC",
>> > "ENAUSDT"),
>> >   time = c("2024-12-17 10:01:30", "2024-12-18 21:32:53", "2025-01-
>> > 02
>> > 10:34:49", "2025-01-02 11:14:43", "2025-01-02 11:15:22")
>> > )
>> > 
>> > I want to fetch the 'BTCUSDT' price for a specific time if the
>> > symbol
>> > of same row end with these 3 letters : 'BTC'. In my sample, it will
>> > be
>> > rows 3 and 4.
>> > Here is how I find BTCUSDT price for date 2025-01-02 10:34:49:
>> > 
>> > get_btc_price <- function(time) {
>> >   klines <- binance_klines('BTCUSDT', start_time = '2025-01-02
>> > 10:34:49', end_time = '2025-01-02 10:34:49')
>> > }
>> > 
>> > I was thinking of this code to write my new column BTCUSDT_price:
>> > 
>> > df <- portfolio %>%
>> >   mutate(
>> >     BTCUSDT_price = ifelse(str_detect(symbol, "BTC$"),
>> > map_dbl(time,
>> > get_btc_price), NA_real_)
>> >   )}
>> > 
>> > But the code returns:
>> > Error in `mutate()`:
>> > ℹ In argument: `BTCUSDT_price = ifelse(...)`.
>> > Caused by error in `map_dbl()`:
>> > ℹ In index: 1.
>> > Caused by error in `[.data.table`:
>> > ! Item 1 of j is 12 which is outside the column number range
>> > [1,ncol=0]
>> > 
>> > I have tried other workarounds but all give me errors.
>> > 
>> > Thank you for help
>> > 
>> > 
>> > [1]https://cran.r-project.org/web/packages/binancer
>> > [2]https://cran.r-project.org/web/packages/PMwR
>> > 
>> 
>> You want to compute a new column for your data.frame as
>> a function of two existing columns.
>> 
>> In base R, if you don't want to use a loop (and there
>> would be nothing wrong with a loop), you could use
>> 'mapply'; and then cbind the column to your data.frame:
>> 
>>     new.column <- mapply(
>>         function(symbol, time) {
>>             if (grepl("BTC$", symbol)) {
>>                 ## your get_price computation, using
>>                 ## 'symbol' and 'time'
>>                 paste("do something with", symbol, time)
>>                 
>>             } else
>>                 NA
>>         },
>>         portfolio$symbol,
>>         portfolio$time
>>     )
>> 
>>     ## new.column
>>     ##                                         BTCUSDT 
>>     ##                                              NA 
>>     ##                                         BTCUSDT 
>>     ##                                              NA 
>>     ##                                         ETHUBTC 
>>     ## "do something with ETHUBTC 2025-01-02 10:34:49" 
>>     ##                                         AAVEBTC 
>>     ## "do something with AAVEBTC 2025-01-02 11:14:43" 
>>     ##                                         ENAUSDT 
>>     ##                                              NA 
>
> Thank you for your answer.
> A very warm thanks for your PMwR package. It helps me managing my
> portfolio in a professional way. Unfortunately there isn't so much
> material to manage, follow, compute ratio etc. 
> By any chance, do you have any cool build-in functions to cook monthly
> data (PL, valuation etc) as we compute every month always the same data
> ?

Thank you.

I am afraid there are no built-in, ready-to-use reports in
PMwR.  That was a deliberate choice: PMwR should only
provide simple tools for computing P/L, returns, or for
backtesting, say. For analyzing the results of such
computations, you have all of R for analysis and summarizing
:-)

For my reporting needs, I typically prepare templates in
plain text, HTML or LaTeX and then fill in stats such as
returns etc. See e.g.
https://cran.r-project.org/web/packages/PMwR/vignettes/FinTeX.pdf

For equity series, i.e. total value of a portfolio, using
NAVseries and its summary method might be a start, see e.g.
https://enricoschumann.net/R/packages/PMwR/manual/PMwR.html#NAVseries-summaries
There are handful of functions for analyzing such series,
e. g. `drawdowns` or `streaks`.  But as I said, most
analysis I do with basic R functions.


-- 
Enrico Schumann
Lucerne, Switzerland
https://enricoschumann.net