select columns from a table

[Xinyu Gai]

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  

[Liam Baron]

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

[thomas...@aquaq.co.uk]

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

`a`b!`a`b

In order to select columns by index we must use the cols keyword:

q)cols t

`a`b

From here we can select from the column names by indexing:

q)ind:(cols tab)[0 1]

`a`b

Substituting this into the functional select statement gives:

?[`t;();0b;ind!ind]

Regards,

Thomas Smyth

AquaQ Analytics

[Xinyu Gai]

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

[Xinyu Gai]

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

[David Demner]

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)

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com.
To post to this group, send email to personal...@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

[thomas...@aquaq.co.uk]

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

[Michael Keenan]

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

`s`p!`s`p

q)

//we can use this output to build the functional version of the query. This would be;

q)?[sp;();0b;`s`p!`s`p]

//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

`s`p`qty

q)(cols sp)0 2

`s`qty

//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

--

[Xinyu Gai]

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

On Thursday, 3 March 2016 16:44:55 UTC, David D wrote:

[Xinyu Gai]

Hi Michael,

Thanks a lot for the detailed reply. I like the last function in your post very much!

Best,

Xinyu

[Xinyu Gai]

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  

On Thursday, 3 March 2016 14:35:01 UTC, Xinyu Gai wrote:

[Charles Skelton]

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

--

[Xinyu Gai]

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

[David Demner]

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...@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:

[David Demner]

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