# Instead of one-hot encoding, can I store the same information in one column using a single value?

Rather than creating 15 additional columns full of sparse binary data, could I:

1) use the first 15 prime numbers as indexes for the 15 categories

2) store data by multiplying the prime numbers of the categories that otherwise would have a value of 1 in one-hot encoding

3) retrieve data by factorizing the value generated by multiplying unique prime numbers

Ex: 1914 would yield the list [2, 3, 11, 29] which would let you know that the user with the 1914 value has property 2, 3, 11, and 29 but nothing else.

I understand this is limited because BIGINTs can only hold the product of the first 15 prime numbers, but would it not still be useful in some situations and save time when searching the database? The entire table would be 14 columns smaller. I guess this is less about machine learning algorithms and more about storing and retrieving data.

• It depends on what you want to do with it. Is it going into a neural net as the label to learn? – horaceT Jan 5 '17 at 0:05
• if you can have multiple categories per entry it is not really one-hot-encoding, is it?! – oW_ Jan 5 '17 at 0:22
• You mention in a comment that you might want millions of "bits" in the future. If your data is really sparse, say 100 bits set out of a million, storing each value separately will be smaller (100 x 32 bits = 3 200 bits, plus overhead) than storing 999 900 0s as well (1 000 000 bits plus overhead). – CJ Dennis Jan 5 '17 at 5:47

I suppose you could do this, but if your goal is simply to store 15 boolean values in a single column you are complicating things unnecessarily. Instead of going to all the trouble to compute the prime factors of the stored value, why don't you just store the flags as a bit string? Your example of 15 different possible values could be stored in a single SMALLINT (2-byte) SQL column. After retrieving the value, you would just need to extract the bits of interest for your record with some basic bitwise arithmetic.

• I see. It seems like I just invented a more complicated version of bit fields. I just never heard of that before. You get the answer credit, but can you also tell me if there's a (hacky) way to store lists of indefinite length in a SQL table column? – Austin Capobianco Jan 5 '17 at 3:25
• One way would be to store your bits as a BLOB. – timleathart Jan 5 '17 at 3:49

"Premature optimization is the root of all evil". --Knuth

You could do this, but why? You definitely don't want to do this, if you're going to feed the result into a classifier: most classifiers will perform worse after this transformation. There's no point to do this, if you're trying to save space in a database: hard disks can store hundreds of gigabytes, so a measly 15 columns probably won't make any noticeable difference.

15 additional columns is tiny. Just save the additional 15 columns and spare yourself headaches. The cost of your time to program this up and troubleshoot all of the problems it causes downstream will almost surely exceed the miniscule cost of computation or storage to store the data in the natural format.

And if you seriously have big data where you truly need to do some optimization, the very first step is to measure: measure how much space/time you're actually consuming, and what the dominant contributors to that total cost is, and then focus on optimizing those dominant factors. Optimizations should be guided by data; otherwise you risk implementing complicated optimizations that make little difference overall but add needless complexity and epicycles to your data processing workflow.

• In hindsight the data science stack exchange site may not have been the appropriate place to ask this question. I just randomly thought of this while coding the move list database for a game and I totally understand why one-hot encoding is much more useful for actually doing data science. Still, when it comes to reducing the amount of data to work with, why isn't this a useful method of data compression? If you have a couple thousand rows of 14 columns of 1's & 0's it wont help much, but change that to a couple dozen million and I think itd make a difference. I havent implemented it yet though – Austin Capobianco Jan 5 '17 at 3:20

What is wrong with simply enumerating them?

i.e. 0= apple, 1 = banana, 2 = orange?

By definition of one hot encoding, only one of them may be set at a time. No need to jugle with bit encoding (i.e. power set) or prime encoding (bag-of-x).

But thrmain question is how do you (plan to) use the data, not so much how you encode it. Sparse data can be stored compactly by compression and sparse formats (which pretty much reduces to above approach). But the storage must fit you access pattern or performance will be bad.