ZeroColon is awesome! but could still use some pointers on

Community Support
1

I unfortunately discovered ZeroColon late! it would have saved me a lot of time


url:“http://download.finance.yahoo.com/d/quotes.csv?s=FTR+CTL+LVLT+T+VZ&f=sr”

a:.Q.hg`$url

a1:“s,pe\n”,a

t:(“SS”;enlist",")0:a1

q)t

s pe

----------

FTR N/A

CTL 14.43

LVLT 5.58

T 19.70

VZ 15.26



Here is my code for converting a yahoo csv into a q table.

Let’s assume that ZeroColon doesn’t exist, I still feel that my conversion from g to h isn’t optimal.

any pointers?


// here was my original attempt


p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures}

q)url:“http://download.finance.yahoo.com/d/quotes.csv?s=FTR+CTL+LVLT+T+VZ&f=sr”

q)a:.Q.hq`:url

q)a

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures}

“"FTR",N/A\n"CTL",14.43\n"LVLT",5.58\n"T",19.70\n"VZ",15.26\n”

q)b:“\n” vs a / return list of strings (delimited by \n)

q)b

“"FTR",N/A”

“"CTL",14.43”

“"LVLT",5.58”

“"T",19.70”

“"VZ",15.26”

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures}

“”

q)c:-1_b / drop the last empty string

q)c

“"FTR",N/A”

“"CTL",14.43”

“"LVLT",5.58”

“"T",19.70”

“"VZ",15.26”

q)d:{ssr[x;“"”;“”]} each c / remove "

q)d

“FTR,N/A”

“CTL,14.43”

“LVLT,5.58”

“T,19.70”

“VZ,15.26”

q)e:{ssr[x;“N/A”;“0”]} each d / remove N/A

q)e

“FTR,0”

“CTL,14.43”

“LVLT,5.58”

“T,19.70”

"VZ,15.26

q)f:{“,” vs x} each e

q)f

“FTR” ,“0”

“CTL” “14.43”

“LVLT” “5.58”

,“T” “19.70”

“VZ” “15.26”

q)g:{“S”$x} each f

q)g

FTR 0

CTL 14.43

LVLT 5.58

T 19.70

VZ 15.26


p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures}

q)h:flip g

q)h

FTR CTL LVLT T VZ

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures}

0 14.43 5.58 19.70 15.26


p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures}

q)t:flip spe!(h 0 ; h 1)

q)t

s pe

----------

FTR 0

CTL 14.43

LVLT 5.58

T 19.70

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures}

VZ 15.26

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures} p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures} p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures} p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures} p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures} p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures} p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Menlo; color: #000000; background-color: #ffffff}span.s1 {font-variant-ligatures: no-common-ligatures}
2

I would avoid using symbol types for the prices. It would be particularly problematic for large datasets when splaying or partitioning to disk, as you would end up with a bloated symbol file. See here for more details:

http://www.timestored.com/kdb-guides/strings-symbols-enumeration

Here’s a one-liner, with prices as floats:

q)update pe:0^pe from flip spe!(“SF”; “,”) 0: .Q.hg`$url

s pe

----------

FTR 0

CTL 14.43

LVLT 5.58

T 19.7

VZ 15.26


3

There’s a few things you could do without 0:. For starters I’d replace ssr with except because you’re just removing “"” each time:

a:.Q.hg hsym `$url
b:a except “"”

“FTR,N/A\nCTL,21.11\nLVLT,30.88\nT,19.70\nVZ,15.26\n”

From here we can use vs to get closer to the final table:

c:“,” vs/: “\n” vs -1_b

“FTR” “N/A”

“CTL” “21.11”

“LVLT” “30.88”

,“T” “19.70”

“VZ” “15.26”

We can then apply the column names:

d:flip spe!flip c

s pe

--------------

“FTR” “N/A”

“CTL” “21.11”

“LVLT” “30.88”

,“T” “19.70”

“VZ” “15.26”

And finally convert types:

update `$s, 0^“F”$pe from d

s pe

----------

FTR 0

CTL 21.11

LVLT 30.88

T 19.7

VZ 15.26

Of course, using 0: will still be a better solution.

Regards,

Thomas Smyth

AquaQ Analytics