Help with Adverbs!

Community Support
1

Hi,

I have a table like the following: 

r:flip (symselectionIdex)!(enlist 1.166515958;enlist 9622749;enlist (atbatltrd)!(flip (pricesize)!(1.35 1.01;120.69 7066.74);flip (pricesize)!(1.38 1.39;252.93 513.43);flip (price`size)!(1.14 1.15;1575.04 2856.78)));

The method with the adverbs below works fine to pull out the trade prices into a table. 

extractPricesSizes:{[x;y] @[@/[;x];;`float$()] each y};

ungroup select sym, selectionId, price:extractPricesSizes[trdprice;ex], size:extractPricesSizes[trdsize;ex] from r

But when the data has the following structure (i.e. with just lists of data instead of tables/dictionaries), I’m struggling to adapt the adverbs to pull out the trade data: 

r:flip (symselectionIdex)!(1.1664879041.166487904;7283310 9635244;((trdltptvid)!((2.09 183.12;2.1 1.12;2.08 18.12);2.08;50966.51;7283310f);(atbtrdltptvid)!(enlist 1.94 46.37;enlist 1.94 551.62;1.94;455408.8;9635244f)));

The relevant snipping of the underlying json for the first structure looks like this: “trd”:[{“price”:1.14,“size”:1575.04},{“price”:1.15,“size”:2856.78},{“price”:1.16,“size”:3944.74}]

The second is like this: “trd”:[[2.08,183.12], [2.08,183.12], [2.08,183.12]]

I’d appreciate any help with this. I can get somewhere if I do this, but it’s not much closer to the answer. 

extractPricesSizes:{[x;y] @[@/[;x];;`float$()] each y};

ungroup select sym, selectionId, price:extractPricesSizes[trd;ex], size:extractPricesSizes[trd;ex] from r

Thanks. 

2

Hi,

Try

q)ungroup exec pricesize!flip first ex`trd  by sym,selectionId from r

sym         selectionId price size

1.166487904 7283310     2.09  183.12

1.166487904 7283310     2.1   1.12

1.166487904 7283310     2.08  18.12

1.166487904 9635244     1.94  551.62

Jason

On 19 Dec 2019, at 1:25 p.m., Tom <tobriain@gmail.com> wrote:

?
Hi,

I have a table like the following: 

r:flip (symselectionIdex)!(enlist 1.166515958;enlist 9622749;enlist (atbatltrd)!(flip (pricesize)!(1.35 1.01;120.69 7066.74);flip (pricesize)!(1.38 1.39;252.93 513.43);flip (price`size)!(1.14 1.15;1575.04 2856.78)));

The method with the adverbs below works fine to pull out the trade prices into a table. 

extractPricesSizes:{[x;y] @[@/[;x];;`float$()] each y};

ungroup select sym, selectionId, price:extractPricesSizes[trdprice;ex], size:extractPricesSizes[trdsize;ex] from r

But when the data has the following structure (i.e. with just lists of data instead of tables/dictionaries), I’m struggling to adapt the adverbs to pull out the trade data: 

r:flip (symselectionIdex)!(1.1664879041.166487904;7283310 9635244;((trdltptvid)!((2.09 183.12;2.1 1.12;2.08 18.12);2.08;50966.51;7283310f);(atbtrdltptvid)!(enlist 1.94 46.37;enlist 1.94 551.62;1.94;455408.8;9635244f)));

The relevant snipping of the underlying json for the first structure looks like this: “trd”:[{“price”:1.14,“size”:1575.04},{“price”:1.15,“size”:2856.78},{“price”:1.16,“size”:3944.74}]

The second is like this: “trd”:[[2.08,183.12], [2.08,183.12], [2.08,183.12]]

I’d appreciate any help with this. I can get somewhere if I do this, but it’s not much closer to the answer. 

extractPricesSizes:{[x;y] @[@/[;x];;`float$()] each y};

ungroup select sym, selectionId, price:extractPricesSizes[trd;ex],&nbsp;size:extractPricesSizes[trd;ex] from r

Thanks. 

–
Submitted via Google Groups

3

Hi Jason,

That worked perfectly. Thanks again!

Tom.

4

Hi Jason,

That’s working perfectly for the table of trades, so thanks for that. I’m stuck on the next line (from https://github.com/picoDoc/betfair-data-capture) that produces a table with lists of quotes:

r:flip (symselectionIdex)!(enlist 1.166515958;enlist 9622749;enlist (atbatltrd)!(flip (pricesize)!(1.35 1.01;120.69 7066.74);flip (pricesize)!(1.38 1.39;252.93 513.43);flip (price`size)!(1.14 1.15;1575.04 2856.78)));

trades: ungroup select sym, selectionId, price:extractPricesSizes[tradedVolumeprice;ex], size:extractPricesSizes[tradedVolumesize;ex] from r;

quotes: select sym, selectionId, backs: extractPricesSizes[atbprice;ex], lays: s#'extractPricesSizes[atlprice;ex], bsizes: extractPricesSizes[atbsize;ex],lsizes: extractPricesSizes[atl`size;ex] from r;

quotes

With the structure of the data in the new version of the Betfair Api, I can’t reproduce that quotes table when the data has this structure instead:

r:flip (symselectionIdex)!(1.1665159581.166515958;7283310 9635244;((atbatltrdltptvid)!((1.1 100.12;1.1 200;1.3 300);(2.2 500.12;2.3 600;2.4 700);(2.09 183.12;2.1 1.12;2.08 18.12);2.08;50966.51;7283310f);(atbtrdltptvid)!((1.94 46.37;1.96 41;1.98 46);enlist 1.94 551.62;1.94;455408.8;9635244f)));

trades:ungroup exec pricesize!flip first ex`trd  by sym,selectionId from r; //i.e. your suggestion from above

quotes:ungroup exec backsbsizes!flip first exatb,layslsizes!flip first exatl by sym,selectionId from r; //If I remove the second clause of this it works, but I’m only getting two columns out of the four needed

exec backs, bsizes by sym, selectionId from a

I’d appreciate any help at all with this. 

Thanks,

Tom.

5

Hi Tom,

If I’m correct you want unpack the backs/lays data to get a table that looks something like this:

sym         selectionId backs bsizes lays lsizes

1.166515958 7283310     1.1   100.12 2.2  500.12

1.166515958 7283310     1.1   200    2.2  500.12

1.166515958 7283310     1.3   300    2.2  500.12

1.166515958 9635244     1.94  46.37

1.166515958 9635244     1.96  41

1.166515958 9635244     1.98  46

If so the issue is being caused by the absence of lays data for the second selectionId. A workaround would be to unpack the backs and lays dataseperately then left join together by sym,selectionId as such:

0!(2!ungroup exec backsbsizes!flip first exatb by sym,selectionId from r) lj 2!ungroup exec layslsizes!flip first exatl by sym,selectionId from r

This will leave nulls in the missing data as shown in the table above. Is this the kind of output you were looking for?

Daniel