Skip to content
Prev 15202 / 15274 Next
Original Raw

join two tibbles: need transpose and which variables for which values

Joshua Ulrich
On Sun, Apr 6, 2025 at 10:45?PM Arnaud Gaboury <arnaud.gaboury at gmail.com> wrote:
This is easy to do with xts/zoo, especially if you know that
read.zoo() can transpose the long data.frame via its 'split' argument.

The majority of the code below is needed because you want all the
price data in a wide format, with the quantity data in the same object
but in a long format. It would be simpler if all the data were in a
wide format and the data for each currency was in its own xts object.

library(xts)
trades_long_to_wide <-
function(trades)
{
    wide <- as.xts(read.zoo(trades, split = "symbol", tz = ""))

    # remove _price colnames
    colnames(wide) <- gsub("^(price)\\.(.*)", "\\2", colnames(wide))

    # replace all the NA in the qty columns with 0
    qty_cols <- grep("executed_qty", colnames(wide))
    wide$executed_qty <- 0
    for (j in qty_cols) {
        jcol <- wide[, j]
        jcol <- ifelse(is.na(jcol), 0, jcol)
        wide[, "executed_qty"] <- jcol + wide[, "executed_qty"]
    }

    wide <- wide[, -qty_cols]  # remove all the individual qty cols
    return(wide)
}
# extract necessary columns
trades <- all_trade_final[, c("time", "symbol", "price", "executed_qty")]
# NOTE: as.xts automatically finds the 'time' column for you
result_xts <- merge(trades_long_to_wide(trades), as.xts(token_daily_close))
# convert back to data.frame
result_df <- data.frame(time = index(result_xts), result_xts, row.names = NULL)

In case this is also true of your actual data:
1. The trade prices and quantities must be wrong. Several instruments
have price/qty that differ by several orders of magnitude within a
couple days.
2. The 'time' in your trades data is in UTC while the 'time' in your
price data is in your local timezone. I'm not sure if you expect/want
those two 'time' columns to be in different timezones. I'd convert
them to the same timezone immediately after importing the data to
avoid confusion.

Best,
Josh

  
    

      
      
    

  


  


  


  
  

    

  

    
      
    
    
Thread (2 messages)

  

  

    
            
        Arnaud Gaboury
        
        join two tibbles: need transpose and which variables for which values
      
      Apr 6
    
    
      
            
        Joshua Ulrich
        
        join two tibbles: need transpose and which variables for which values
      
      Apr 7