CODEWITHSUNDEEP
kdb+k
ScarletPumpernickel
ScarletPumpernickel

Reputation: 678

Transform table by creating columns out of row groups

I have a table with time series data grouped by name (with some dates missing), and would like to transform the table by creating a column for each group containing that group's data. In other words, how do I go from this

x:([] name:`a`a`a`b`b`c`c;date:2000.01.01 2000.01.02 2000.01.03 2000.01.01 2000.01.02 2000.01.02 2000.01.03;val:1 2 3 1 2 3 1)

to this?

([] date:2000.01.01 2000.01.02 2000.01.03;a:1 2 3;b:1 2 0N;c:0N 3 1)

Upvotes: 2

Views: 2837

Answers (1)

Ryan Hamilton
Ryan Hamilton

Reputation: 2615

You want to pivot the data:

q)t:([] name:`a`a`a`b`b`c`c;date:2000.01.01 2000.01.02 2000.01.03 2000.01.01 2000.01.02 2000.01.02 2000.01.03;val:1 2 3 1
2 3 1)
q)t
name date       val
-------------------
a    2000.01.01 1
a    2000.01.02 2
a    2000.01.03 3
b    2000.01.01 1
b    2000.01.02 2
c    2000.01.02 3
c    2000.01.03 1

q)P:asc exec distinct name from t // get distinct pivot items
q)P
`s#`a`b`c

q)pvt:exec P!(name!val)P by date:date from t
q)pvt
date      | a b c
----------| -----
2000.01.01| 1 1
2000.01.02| 2 2 3
2000.01.03| 3   1

q)u:([date:2000.01.01 2000.01.02 2000.01.03] a:1 2 3;b:1 2 0N;c:0N 3 1)
q)pvt~u
1b

The kx website steps through a simple example and a more general solution: http://code.kx.com/q/cookbook/pivoting-tables/

Upvotes: 2

Related Questions