Saving table as CSV file

davekrog · March 23, 2011, 8:51pm

I am new to KDB+/Q and trying to read futures price file (partial
sample below), calculate a spread, and save the spread table to a CSV
file. I have been able to save the intermediate table (i.e., g, j,
and z) to a CSV file but not the spread table (i.e., s). I have tried
using both the save command as well as `:s.csv 0: “,” 0: s but neither
works. I get a type error. Looking for some guidance. Thanks.

My Q script:
t:(“SISSDDIIIIFFFFFF”;enlist “,”) 0: :c:/q/LHData110317.txt; g:select gp:avg Close2 by Contracttype,Tradeyear,Tradedate from t where Monthsymbol=G
j:select jp:avg Close2 by Contracttype,Tradeyear,Tradedate from t
where Monthsymbol=J z:g,'j s:select Gminusj:gp-jp by Contracttype,Tradeyear,Tradedate from z where gp\>0,jp\>0,Tradeyear\>2009 :s.csv 0: “,” 0: s
/save `s.csv

Partial of input file:
Contracttype Contractyear Monthsymbol Contractname Tradedate
Tradeday Tradeyear Trademonth Tradeweek Tradeweekday Volume OI
Open2 High2 Low2 Close2

LH 1970 G LHG1970 1969.06.25
2011.06.25 1969 6 26 4 3 3
29.72973 29.72973 29.72973 29.72973
LH 1970 G LHG1970 1969.06.26
2011.06.26 1969 6 26 5 6 9
31.08108 31.08108 29.59459 29.59459
LH 1970 G LHG1970 1969.06.27
2011.06.27 1969 6 26 6 7 16
29.45946 29.45946 29.32432 29.45946
LH 1970 G LHG1970 1969.06.30
2011.06.30 1969 6 27 2 4 20
29.39189 29.39189 29.39189 29.39189
LH 1970 G LHG1970 1969.07.01
2011.07.01 1969 7 27 3 13 30
29.59459 29.59459 29.05405 29.05405
LH 1970 G LHG1970 1969.07.02
2011.07.02 1969 7 27 4 0 30
29.32432 29.32432 29.32432 29.32432

trieder · March 23, 2011, 9:32pm

charset=us-ascii
Message-Id:
Cc: Kdb+ Personal Developers
X-Mailer: iPhone Mail (8F190)
From: Timothy Rieder
Subject: Re: [personal kdb+] Saving table as CSV file
Date: Wed, 23 Mar 2011 17:32:49 -0400
To: “personal-kdbplus@googlegroups.com”

Something compact to try:

/ assuming no errors
SaveCsv:{hclose neg neg[hopen f:hsym sv x,csv] .h.cd x;f}

/ save it
SaveCsvs Didn't type this in a term so I hope it works :-) On Mar 23, 2011, at 4:51 PM, "davekrog@yahoo.com" <davekrog> wrote= : >=20 >=20 >=20 >=20 >=20 >=20 > I am new to KDB+/Q and trying to read futures price file (partial > sample below), calculate a spread, and save the spread table to a CSV > file. I have been able to save the intermediate table (i.e., g, j, > and z) to a CSV file but not the spread table (i.e., s). I have tried > using both the save command as well as :s.csv 0: “,” 0: s but neither
> works. I get a type error. Looking for some guidance. Thanks.
>=20
> My Q script:
> t:(“SISSDDIIIIFFFFFF”;enlist “,”) 0: :c:/q/LHData110317.txt; > g:select gp:avg Close2 by Contracttype,Tradeyear,Tradedate from t > where Monthsymbol=3DG
> j:select jp:avg Close2 by Contracttype,Tradeyear,Tradedate from t
> where Monthsymbol=3DJ > z:g,'j > s:select Gminusj:gp-jp by Contracttype,Tradeyear,Tradedate from z > where gp>0,jp>0,Tradeyear>2009 > :s.csv 0: “,” 0: s
> /save `s.csv
>=20
> Partial of input file:
> Contracttype Contractyear Monthsymbol Contractname Tradedate
> Tradeday Tradeyear Trademonth Tradeweek Tradeweekday Volume OI
> Open2 High2 Low2 Close2
> --------------------------------------------------------------------------=
----------------------------------------------------------------------------=
-------------
> LH 1970 G LHG1970 1969.06.25
> 2011.06.25 1969 6 26 4 3 3
> 29.72973 29.72973 29.72973 29.72973
> LH 1970 G LHG1970 1969.06.26
> 2011.06.26 1969 6 26 5 6 9
> 31.08108 31.08108 29.59459 29.59459
> LH 1970 G LHG1970 1969.06.27
> 2011.06.27 1969 6 26 6 7 16
> 29.45946 29.45946 29.32432 29.45946
> LH 1970 G LHG1970 1969.06.30
> 2011.06.30 1969 6 27 2 4 20
> 29.39189 29.39189 29.39189 29.39189
> LH 1970 G LHG1970 1969.07.01
> 2011.07.01 1969 7 27 3 13 30
> 29.59459 29.59459 29.05405 29.05405
> LH 1970 G LHG1970 1969.07.02
> 2011.07.02 1969 7 27 4 0 30
> 29.32432 29.32432 29.32432 29.32432
>=20
> --=20
>

Submitted via Google Groups

Aaron_Davies · March 24, 2011, 1:20am

On Mar 23, 2011, at 4:51 PM, davekrog@yahoo.com wrote:

> My Q script:
> t:(“SISSDDIIIIFFFFFF”;enlist “,”) 0: :c:/q/LHData110317.txt; \> g:select gp:avg Close2 by Contracttype,Tradeyear,Tradedate from t where Monthsymbol=G
> j:select jp:avg Close2 by Contracttype,Tradeyear,Tradedate from t
where Monthsymbol=J \> z:g,'j \> s:select Gminusj:gp-jp by Contracttype,Tradeyear,Tradedate from z where gp\>0,jp\>0,Tradeyear\>2009 \> :s.csv 0: “,” 0: s

the grouping in s: is redundant (and makes Gminusj an F (not f), causing
the 'type error)

try

s:update Gminusj:gp-jp from select from z where gp>0,jp>0,Tradeyear>2009

davekrog · March 24, 2011, 1:45am

Yes, that solved the problem. I need to look into this closer so Iunderstand what I was doing wrong.Thanks for the help, Aaron.On Mar 23, 8:20?pm, Aaron Davies <aaron.dav…> wrote:> On Mar 23, 2011, at 4:51 PM, davek...@yahoo.com wrote:>> > My Q script:> > t:(“SISSDDIIIIFFFFFF”;enlist “,”) 0: :c:/q/LHData110317.txt;> > g:select gp:avg Close2 by Contracttype,Tradeyear,Tradedate from t where Monthsymbol=G> > j:select jp:avg Close2 by Contracttype,Tradeyear,Tradedate from t where Monthsymbol=J> > z:g,'j> > s:select Gminusj:gp-jp by Contracttype,Tradeyear,Tradedate from z where gp>0,jp>0,Tradeyear>2009> > :s.csv 0: “,” 0: s>> the grouping in s: is redundant (and makes Gminusj an F (not f), causing the 'type error)>> try>> s:update Gminusj:gp-jp from select from z where gp>0,jp>0,Tradeyear>2009</aaron.dav…>

Aaron_Davies · March 24, 2011, 2:00am

To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1082)

>>> My Q script:
>>> t:(“SISSDDIIIIFFFFFF”;enlist “,”) 0: :c:/q/LHData110317.txt; \>\>\> g:select gp:avg Close2 by Contracttype,Tradeyear,Tradedate from t = where Monthsymbol=G
>>> j:select jp:avg Close2 by Contracttype,Tradeyear,Tradedate from t =
where Monthsymbol=J \>\>\> z:g,'j \>\>\> s:select Gminusj:gp-jp by Contracttype,Tradeyear,Tradedate from z = where gp\>0,jp\>0,Tradeyear\>2009 \>\>\> :s.csv 0: “,” 0: s
>>
>> the grouping in s: is redundant (and makes Gminusj an F (not f), =
causing the 'type error)
>>
>> try
>>
>> s:update Gminusj:gp-jp from select from z where =
gp>0,jp>0,Tradeyear>2009
>
> Yes, that solved the problem. I need to look into this closer so I
> understand what I was doing wrong.

z is already keyed on Contracttype,Tradeyear,Tradedate, since it’s =
derived from queries that were grouped that way, so grouping it by the =
same column set is guaranteed to give you groups of length one–this =
makes it redundant

any query except a specific small list of aggregates (anything in .Q.a0) =
which is executed with a group clause will return a nested list result, =
even if that group clause results only in groups of length one–this =
makes a subtraction query return a float list instead of a float

note that avg is one of the aggregates in .Q.a0, so it returns a vector =
from a grouped query, which is why gp and jp are f, not F

0: doesn’t support nested data=

davekrog · March 24, 2011, 2:03am

That’s very helpful. Thanks, again!On Mar 23, 9:00?pm, Aaron Davies <aaron.dav…> wrote:> >>> My Q script:> >>> t:(“SISSDDIIIIFFFFFF”;enlist “,”) 0: :c:/q/LHData110317.txt;> >>> g:select gp:avg Close2 by Contracttype,Tradeyear,Tradedate from t where Monthsymbol=G> >>> j:select jp:avg Close2 by Contracttype,Tradeyear,Tradedate from t where Monthsymbol=J> >>> z:g,'j> >>> s:select Gminusj:gp-jp by Contracttype,Tradeyear,Tradedate from z where gp>0,jp>0,Tradeyear>2009> >>> :s.csv 0: “,” 0: s>> >> the grouping in s: is redundant (and makes Gminusj an F (not f), causing the 'type error)>> >> try>> >> s:update Gminusj:gp-jp from select from z where gp>0,jp>0,Tradeyear>2009>> > Yes, that solved the problem. ?I need to look into this closer so I> > understand what I was doing wrong.>> z is already keyed on Contracttype,Tradeyear,Tradedate, since it’s derived from queries that were grouped that way, so grouping it by the same column set is guaranteed to give you groups of length one–this makes it redundant>> any query except a specific small list of aggregates (anything in .Q.a0) which is executed with a group clause will return a nested list result, even if that group clause results only in groups of length one–this makes a subtraction query return a float list instead of a float>> note that avg is one of the aggregates in .Q.a0, so it returns a vector from a grouped query, which is why gp and jp are f, not F>> 0: doesn’t support nested data- Hide quoted text ->> - Show quoted text -</aaron.dav…>

Topic		Replies	Views
Saving a table containing list of strings as .csv. Community Support kdb-and-q	4	0	September 21, 2016
Exporting CSV file Community Support kdb-and-q	5	4	July 24, 2023
How to save the data to disk ? Community Support kdb-and-q	2	0	May 23, 2012
Exporting CSV file Community Support imported , kdb-and-q	4	6	July 24, 2023
a question on data import and export from kdb Community Support kdb-and-q	2	2	October 30, 2011

Saving table as CSV file

Partial of input file: Contracttype Contractyear Monthsymbol Contractname Tradedate Tradeday Tradeyear Trademonth Tradeweek Tradeweekday Volume OI Open2 High2 Low2 Close2

Related topics

Partial of input file:
Contracttype Contractyear Monthsymbol Contractname Tradedate
Tradeday Tradeyear Trademonth Tradeweek Tradeweekday Volume OI
Open2 High2 Low2 Close2