CODEWITHSUNDEEP
kdb+
user9471643
user9471643

Reputation:

Pivot table with columns as year/date in KDB+

I am trying to create a pivot table with columns as year out of a simple table 

q)growth:([] stock:asc 9#`goog`apple`nokia; year: 9#2015 2016 2017; returns:9?20 )
q)growth
stock year returns
------------------
apple 2015 9
apple 2016 18
apple 2017 17
goog  2015 8
goog  2016 13
goog  2017 17
nokia 2015 12
nokia 2016 12
nokia 2017 2

but I am not able to get the correct structure, it is still returning me a dictionary rather than multiple year columns.

q)exec (distinct growth`year)#year!returns by stock:stock from growth
stock|
-----| ----------------------
apple| 2015 2016 2017!9 18 17
goog | 2015 2016 2017!8 13 17
nokia| 2015 2016 2017!12 12 2

am I doing anything wrong?

Upvotes: 3

Views: 1062

Answers (2)

Thomas Smyth
Thomas Smyth

Reputation: 5644

You need to convert the years to symbols in order to use them as column headers. In this case I have updated the growth table first then performed the pivot:

q)exec distinct[year]#year!returns by stock:stock from update `$string year from growth
stock| 2015 2016 2017
-----| --------------
apple| 12   8    10
goog | 1    9    11
nokia| 5    6    1

Additionally you may see that I have changed to distinct[year] from (distinct growth`year) as this yields the same result with year being pulled from the updated table.

Upvotes: 4

nyi
nyi

Reputation: 3229

The column names of a table in KDB should be symbols rather than any other data type.

In your pivot table , the datatype of 'year' column is int\long this is the reason a proper table is not turning up.

If you type cast it as symbol, then it will work.

q)growth:([] stock:asc 9#`goog`apple`nokia; year: 9#2015 2016 2017; returns:9?20 )
q)growth:update `$string year from growth
q)exec (distinct growth`year)#year!returns by stock:stock from growth
stock| 2015 2016 2017
-----| --------------
apple| 9    18   17
goog | 8    13   17
nokia| 12   12   2

Alternatively, you can switch the pivot columns to 'stock' rather than 'year' and get a pivot table with the same original table.

q)growth:([] stock:asc 9#`goog`apple`nokia; year: 9#2015 2016 2017; returns:9?20 )
q)show exec (distinct growth`stock)#stock!returns by year:year from growth
year| apple goog nokia
----| ----------------
2015| 4     2    4
2016| 5     13   12
2017| 12    6    1

Upvotes: 3

Related Questions