# **************     Cookbook / More To Know / Python       ************** 
# **************   Copyright 2019 Deephaven Data Labs, LLC  ************** 

# This notebook is part of the Deephaven Query Cookbook. For more information, 
# please refer to the Deephaven Documentation Portal at 
# https://docs.deephaven.io/latest/Content/quickReference/cookbook/cookbook.htm


# Table Tricks

import illumon.iris.DBTimeUtils as dbtu
import illumon.iris.TableTools as tt
trades = db.t("LearnIris", "StockTrades").where("Date=`2017-08-25`")\
    .view("Date", "ExchangeTimestamp", "Sym", "Last")\
    .head(10)

time = dbtu.convertDateTime("2017-08-25T04:05:00 NY")
custom = tt.emptyTable(1).updateView("Date=`2017-08-25`", "ExchangeTimestamp=time", "Sym=`AAPL`", "Last=160.0")

tradesNewRow = tt.merge(trades, custom)

indexes = tradesNewRow.updateView("Index=i")

prevPrice = indexes.updateView("PreviousPrice=Last_[i-1]")

difference = prevPrice.updateView("Difference=Last-PreviousPrice")

meta = difference.getMeta()

cellValue = difference.getColumn("Difference").get(5)
print (cellValue)



# Bad Math

import illumon.iris.DBTimeUtils as dbtu
import illumon.iris.TableTools as tt
import illumon.iris.ComboAggregateFactory as caf
trades = db.t("LearnIris", "StockTrades").where("Date=`2017-08-25`")\
    .view("Date", "ExchangeTimestamp", "Sym", "Last", "Size")

dollarsInvolved = trades.updateView("DollarsInvolved=Last*Size")

summary = dollarsInvolved.by(caf.AggCombo(caf.AggSum("TotalSize=Size", "TotalDollars=DollarsInvolved"), caf.AggLast("Last")), "Sym")

spx = tt.emptyTable(1).updateView("Sym=`SPX`", "TotalSize=0", "TotalDollars=0.0", "Last=2465.84")
withSpx = tt.merge(summary, spx)

vwap = withSpx.updateView("VWAP=TotalDollars/TotalSize")

sum = vwap.view("VWAP").sumBy()

vwapIfThen = withSpx.updateView("VWAP = (TotalSize != 0) ? (TotalDollars/TotalSize) : NULL_DOUBLE")

sumIfThen = vwapIfThen.view("VWAP").sumBy()

checkNull = vwapIfThen.updateView("NullOrNot=isNull(VWAP)")


# Custom Functionality

trades = db.t("LearnIris", "StockTrades").where("Date=`2017-08-25`")\
    .view("Sym", "Size")\
    .headBy(100, "Sym")

symGroups = trades.by("Sym")

def rollingSum(rows, values):
    calculations = jpy.array('int', values.size()) # create an array of integers
    sum = 0 #our running sum
    for i in range(values.size()):
        sum += values.get(i)
        if (i >= rows): sum -= values.get(i - rows) #subtract when needed
        calculations[i] = sum # store a rolling sum for every value

    return calculations # return an array of rolling sum calculations

sums = symGroups.updateView("RollingSum=(int[]) rollingSum.call(20, Size)")
ungroup = sums.ungroup()