CODEWITHSUNDEEP
pivotkdb+
ClaudeTi
ClaudeTi

Reputation: 15

Pivot Table in (kdb+/ q)

I have a table with columns state, callable, couponrate, matureyear, rating, yield.

State column has string values as states CA, TX, NY, etc. Callable has two string values Y or N. Couponrate has values 1, 2, 3, 4, 5, 6 as float. Matureyear has values 1, 2, 3,…,30 as integer. Rating has string values AA+, AA-, etc. and yield has float values as the yield.

Data:

State | Callable | Couponrate | Matureyear | Rating | yield

MA | Y | 1 | 2 | AA+ | 4.34 CA | N | 5 | 5 | AA- | 4.64 TX | Y | 3 | 10 | AAA | 4.24 PA | N | 4 | 5 | AA | 4.34 NY | N | 5 | 5 | AA- | 4.64 TX | N | 5 | 10 | AAA | 4.24

Now I want to pivot the data based on rating columns.

When pivoted, index should be state, callable, couponrate and matureyear. columns = rating and values = yield values.

PivotedData should look like below

State | Callable | Couponrate | Matureyear | AAA | AA+ | AA | AA-

MA | Y | 1 | 2 | | 4.34 | | CA | N | 5 | 5 | | | |4.64 TX | Y | 3 | 10 | 4.24 | | |
PA | N | 4 | 5 | | | 4.34 | NY | N | 5 | 5 | | | | 4.64 TX | N | 5 | 10 | 4.24 | | |

I have used the query below but it seems like not working and I am getting error for each [each rating] part

//Pivot the table

pivotedTable: exec yield by state, callable, couponrate, matureyear, rating from table; pivotedTable: exec each[rating]!value each[each rating] by state, callable, couponrate, matureyear from pivotedTable;

Upvotes: 0

Views: 93

Answers (1)

terrylynch
terrylynch

Reputation: 13657

If your string columns are actually strings, they should probably be symbols (makes pivoting easier). So assuming

q)show t:([]State:`MA`CA`TX`PA`NY`TX;Callable:`Y`N`Y`N`N`N;Couponrate:1 5 3 4 5 5f;Matureyear:2 5 10 5 5 10i;Rating:`$("AA+";"AA-";"AAA";"AA";"AA-";"AAA");yield:4.34 4.64 4.24 4.34 4.64 4.24)
State Callable Couponrate Matureyear Rating yield
-------------------------------------------------
MA    Y        1          2          AA+    4.34
CA    N        5          5          AA-    4.64
TX    Y        3          10         AAA    4.24
PA    N        4          5          AA     4.34
NY    N        5          5          AA-    4.64
TX    N        5          10         AAA    4.24

then

q)P:exec distinct Rating from t;
q)exec P#Rating!yield by State,Callable,Couponrate,Matureyear from t
State Callable Couponrate Matureyear| AA+  AA-  AAA  AA
------------------------------------| -------------------
CA    N        5          5         |      4.64
MA    Y        1          2         | 4.34
NY    N        5          5         |      4.64
PA    N        4          5         |                4.34
TX    N        5          10        |           4.24
TX    Y        3          10        |           4.24

as per https://code.kx.com/q/kb/pivoting-tables/

Upvotes: 2

Related Questions