CODEWITHSUNDEEP
kdb+
wibotwi
wibotwi

Reputation: 118

KDB+/Q: How to categorize data

My column has categorical data. E.g. cat and dog are animals, ant,bee and wasp are insects.

t:([] creature:`cat`dog`ant`bee`wasp`crocodile; cnt:til 5)

And I need to add a column 'category' to show creature type. I know how to do dict mapping, but this looks ugly:

update category:creature ^ ((`cat`dog`ant`bee`wasp)!(`animal`animal`insect`insect`insect)) creature from t

I could use it if I knew how to create such mapping dictionary from simple lists like:

mapping: (???) ((`cat`dog;`animal);(`ant`bee`wasp;`insect)

Upvotes: 1

Views: 125

Answers (2)

SJT
SJT

Reputation: 1097

Or you could make a table and run whatever kind of join suits the case.

q)ungroup flip`creature`category!flip L
creature category
-----------------
cat      animal
dog      animal
ant      insect
bee      insect
wasp     insect

Upvotes: 1

Thomas Smyth
Thomas Smyth

Reputation: 5644

In the format provided you can use flip each to create all the pairs for the dictionary:

q)flip each ((`cat`dog;`animal);(`ant`bee`wasp;`insect))
(`cat`animal;`dog`animal)
(`ant`insect;`bee`insect;`wasp`insect)

Which can then be turned into a dictionary by razing that into a single list first:

(!). flip raze flip each ((`cat`dog;`animal);(`ant`bee`wasp;`insect))
cat | animal
dog | animal
ant | insect
bee | insect
wasp| insect

Upvotes: 3

Related Questions