Hi,
I’m new to KDB and trying to get myself familiar with q’s syntax. I noticed that it is fairly easy to get a specific column or a number of columns from a table.
for example, the following table in the sp.q file.
s p qty
---------
s1 p1 300
s1 p2 200
s1 p3 400
s1 p4 200
s4 p5 100
s1 p6 100
s2 p1 300
s2 p2 400
s3 p2 200
s4 p2 200
s4 p4 300
s1 p5 400
====== my question =========
The table below selects two of the three columns in the table above. I get the result by simply typing in: delete p from sp
s qty
------
s1 300
s1 200
s1 400
s1 200
s4 100
s1 100
s2 300
s2 400
s3 200
s4 200
s4 300
s1 400
Is there any other way to get specific columns from a table? eg. A table contains lots of columns and I just want to get the 4th column (or 4-8th columns), is it possible to do so by using column index just like what we could do with row index?
Any suggestions and help are sincerely appreciated!
Best,
Xinyu
Hi Xinyu,
Normally in q, columns are referenced using their name, the same as you have done in your delete statement:
select s,qty from sp
will return the same as what you’re getting with delete.
Do you still need to access the columns via a numerical index? This would be slightly more involved.
Hope this helps,
Liam,
AquaQ Analytics
Hi,
Expanding on what Liam said, here is a basic run through of using a functional select statement to select column names by index:
The basic structure of a functional select is:
?[table;conditions;grouping;columns]
For example select from table t converts to:
?[t;();0b;()]
Which can be seen using:
q)parse"select from t"
?
`t
()
0b
()
For example selecting the columns a and b gives a dictionary consisting of column names:
q)parse"select a,b from t"
?
`t
()
0b
ab!ab
In order to select columns by index we must use the cols keyword:
q)cols t
ab
From here we can select from the column names by indexing:
q)ind:(cols tab)[0 1]
ab
Substituting this into the functional select statement gives:
?[`t;();0b;ind!ind]
Regards,
Thomas Smyth
AquaQ Analytics
Hi Liam,
Thanks for your reply. I think I understand the issue now. Normally we could do it with column names.
In terms of the numerical index, I am thinking about first get the column names via: col_name:cols sp
then get the position of a specific column name col_name[1], which is `p
however, select col_name[1] from sp doesn’t work. Is it possible to get rid of the " ` " before p to make it work? Probably most of the time refer to a column by its name is enough , I am just curious to know more about this.
Thanks!
Best,
Xinyu
Hi Thomas,
Thank you so much for your detailed reply. I am just about to learn functional select and your post is a great start! Thank you!
Best,
Xinyu
To: Xinyu Gai , Kdb+ Personal Developers
Another option for unkeyed tables is the take (#) operator:
col_name:cols sp;
col_name?[0 2]#sp
(note that left of take must be a list so col_name[1] by itself won’t work)
From: Xinyu Gai
Sent: Thursday, March 3, 2016 09:36
To: Kdb+ Personal Developers
Reply To: personal-kdbplus@googlegroups.com
Subject: [personal kdb+] Re: select columns from a table
|
Hi Liam,
Thanks for your reply. I think I understand the issue now. Normally we could do it with column names.
In terms of the numerical index, I am thinking about first get the column names via: col_name:cols sp
then get the position of a specific column name col_name[1], which is `p
however, select col_name[1] from sp doesn't work. Is it possible to get rid of the " ` " before p to make it work? Probably most of the time refer to a column by its name is enough , I am just curious to know more about this.
Thanks!
Best,
Xinyu
On Thursday, 3 March 2016 14:58:36 UTC, Liam Baron wrote:
Hi Xinyu,
Normally in q, columns are referenced using their name, the same as you have done in your delete statement:
select s,qty from sp
will return the same as what you're getting with delete.
Do you still need to access the columns via a numerical index? This would be slightly more involved.
Hope this helps,
Liam,
AquaQ Analytics
On Thursday, March 3, 2016 at 2:35:01 PM UTC, Xinyu Gai wrote:
Hi,
I'm new to KDB and trying to get myself familiar with q's syntax. I noticed that it is fairly easy to get a specific column or a number of columns from a table.
for example, the following table in the sp.q file.
s p qty
---------
s1 p1 300
s1 p2 200
s1 p3 400
s1 p4 200
s4 p5 100
s1 p6 100
s2 p1 300
s2 p2 400
s3 p2 200
s4 p2 200
s4 p4 300
s1 p5 400
====== my question =========
The table below selects two of the three columns in the table above. I get the result by simply typing in: delete p from sp
s qty
------
s1 300
s1 200
s1 400
s1 200
s4 100
s1 100
s2 300
s2 400
s3 200
s4 200
s4 300
s1 400
Is there any other way to get specific columns from a table? eg. A table contains lots of columns and I just want to get the 4th column (or 4-8th columns), is it possible to do so by using column index just like what we could do with row index?
Any suggestions and help are sincerely appreciated!
Best,
Xinyu
--
Submitted via Google Groups
Very simple way of achieving the same result!
This can be expanded slightly, like David said the following command will fail because the left hand side is not a list:
col_name[0]#sp
But by enlist can be used to create a list out of the single element on the left:
(enlist col_name[0])#sp
Which will now return a result.
Regards
Thomas Smyth
AquaQ Analytics
Hey,
For the second part; to select columns by index you could get a list of columns and index into this list. This would return a list of your required columns. Once you have your column names, you need a way to incorporate these into a select statement.
You could do this a few ways. The preferred would be to write the select statement functionally. There is a function called parse which can help us construct the functional form of a query. Basically, it takes a string as a parameter (our select statement) and returns the parse tree of this. We then use this parse tree to construct the functional version. This is easier to explain with an example;
q)parse"select s,p from sp"
?
`sp
()
0b
sp!sp
q)
//we can use this output to build the functional version of the query. This would be;
q)?[sp;();0b;sp!sp]
//so if we run the above, it will return the same as running q)select s,p from sp
// the difference which is useful to us, is that we can specify the columns by passing in symbols. So we need to figure out how to dynamically generate a list of columns as symbols using column indexes. To do this, we get a list of columns and index into them;
q)cols sp
sp`qty
q)(cols sp)0 2
sqty
//for simplicity we can assign this to a variable called ‘kols’, we can then incorporate this variable into our functional query
q)kols:(cols sp)0 2
q)?[sp;();0b;kols!kols]
//thats it. We can make a function out of this code which will take two parameters. The table and the indexes of the columns we want. This function would be;
q)f:{[tab;idx] kols:(cols tab)idx; ?[tab;();0b;kols!kols]}
//here is is in action;
q)f[sp;0 2]
s qty
s1 300
s1 200
s1 400
s1 200
s4 100
s1 100
s2 300
s2 400
s3 200
s4 200
s4 300
s1 400
q)f[sp;0 1]
s p
s1 p1
s1 p2
s1 p3
s1 p4
s4 p5
s1 p6
s2 p1
s2 p2
s3 p2
s4 p2
s4 p4
s1 p5
q)
//Let me know if this helps!
Thanks,
Michael
Thanks David! This is the terse answer that I was looking for at the very beginning. Since I start to learn q, I am always surprised by how flexible the language could be.
Best,
Xinyu
Hi Michael,
Thanks a lot for the detailed reply. I like the last function in your post very much!
Best,
Xinyu
Thank you all for enlightening me over this issue. Here is my final question.
I started to learn q two weeks ago, and found it very different from other Ianguages I learned before. It is too flexible for me to pick up, sometimes a problem that confuses me a lot can be solved by very simple code, which somewhat makes me feel a bit depressed.
As you seem to know this language very well, could you please share some experience or suggestions on how to learn it? What I am doing now is simply looking at some examples, as well as doing some practice on the way. I’m curious to know how you become expert on this.
Thanks,
Xinyu
Hi Xinyu,
it might help us to answer your question if we know your background in programming? e.g. Have you met any of the array, functional or sql programming paradigms before? Or are you coming from a structured/OO background? Which languages have you learnt previously?
thanks,
Charlie
Hi Charlie,
Thanks a lot for your reply.
I completed my bachelor’s in mechanical engineering, after which I did a master’s in finance. I don’t really have much experience about programming, most of the time I just use MATLAB/Python/R to process data. Years ago I learned VB and C, but I didn’t go deep into them. Actually I didn’t work with database before, so basically q is the first database language that I learned.
I’m currently working as a data analyst, however I find my knowledge extremely limited and I’m eager to develop professional expertise in this area. I am willing to learn anything related with data processing and programming.
Probably it’s difficult to answer my question as my knowledge in this area is quite limited, but any suggestions are greatly appreciated!
Thanks,
Xinyu
To: Xinyu Gai , Kdb+ Personal Developers
Do you have the two books on q? Q for Mortals 2 by Jeffry Borror, and the recent Q-Tips by Nick Psaris? Both are very good (in different ways).
One good way to learn q is with practical problems that have already been solved; don’t look at the solution but try to solve the problem yourself and then compare your answer to other people’s.
Another place I like to look to start ‘thinking like q’ is the Q idioms page - ?
http://code.kx.com/wiki/Qidioms. The first 15 or so are fairly obtuse but many of the rest are quite practical.
I wouldn’t be too frustrated after only a couple weeks - not very long ?and well worth spending the time to learn it properly.
From: Xinyu Gai
Sent: Friday, March 4, 2016 05:42
To: Kdb+ Personal Developers
Reply To: personal-kdbplus@googlegroups.com
Subject: Re: [personal kdb+] Re: select columns from a table
|
Hi Charlie,
Thanks a lot for your reply.
I completed my bachelor's in mechanical engineering, after which I did a master's in finance. I don't really have much experience about programming, most of the time I just use MATLAB/Python/R to process data. Years ago I learned vb and C, but I didn't go deep into them. Actually I didn't work with database before, so basically q is the first database language that I learned.
I'm currently working as a data analyst, however i find my knowledge extremely limited and I'm eager to develop professional expertise in this area. I am willing to learn anything related with data processing and programming.
Probably it's difficult to answer my question as my knowledge in this area is quite limited, but any suggestions are greatly appreciated!
Thanks,
Xinyu
On Friday, 4 March 2016 10:49:33 UTC, Charles Skelton wrote:
Hi Xinyu,
it might help us to answer your question if we know your background in programming? e.g. Have you met any of the array, functional or sql programming paradigms before? Or are you coming from a structured/OO background? Which languages have you learnt previously?
thanks,
Charlie
To: “thomas.smyth@aquaq.co.uk” , Kdb+ Personal Developers
Or any of:
col_name[enlist 0]#sp
?col_name[1#0]#sp
col_name[(),0]#sp
The last (), is a useful construct? - it converts an atom to a list but leaves a list unchanged. Quite useful inside functions where you want to support both atom and list inputs consistently
Sent: Thursday, March 3, 2016 10:58
To: Kdb+ Personal Developers
Reply To: personal-kdbplus@googlegroups.com
Subject: Re: [personal kdb+] Re: select columns from a table
|
Very simple way of achieving the same result!
This can be expanded slightly, like David said the following command will fail because the left hand side is not a list:
col_name[0]#sp
But by enlist can be used to create a list out of the single element on the left:
(enlist col_name[0])#sp
Which will now return a result.
Regards
Thomas Smyth
AquaQ Analytics