# How can I dynamically distinguish between categorical data and numerical data?

I know someone who is working on a project that involves ingesting files of data without regard to the columns or data types. The task is to take a file with any number of columns and various data types and output summary statistics on the numerical data.

However, he is unsure of how to go about dynamically assigning data types for certain number-based data. For example:

    CITY
Albuquerque
Boston
Chicago


This is obviously not numerical data and will be stored as text. However,

    ZIP
80221
60653
25525


are not clearly marked as categorical. His software would assign the ZIP code as numerical and output summary statistics for it, which does not make sense for that sort of data.

A couple ideas we had were:

1. If a column is all integers, label it as categorical. This clearly wouldn't work, but it was an idea.
2. If a column has fewer than n unique values and is numeric, label it categorical. This might be closer, but there could still be issues with numerical data falling through.
3. Maintain a list of common numeric data that should actually be categorical and compare the column headers to this list for matches. For example, anything with "ZIP" in it would be categorical.

My gut tells me that there is no way to accurately assign numeric data as categorical or numerical, but was hoping for a suggestion. Any insight you have is greatly appreciated.

• Not full proof but if it start with a 0 it is typically not numeric – paparazzo Jan 21 '16 at 21:34
• You should probably also ask yourself what is the consequence of getting it wrong? That should determine how much effort you need to put into the process. Do you want 90% accurate, or 99% accurate, or 99.9999% accurate? – Spacedman Jan 22 '16 at 12:13
• I would take all the heuristics suggested in the answers as features and train a binary classifier. – Emre Jan 22 '16 at 20:22
• See stats.stackexchange.com/a/106400/17230. There isn't really a ground truth here. – Scortchi - Reinstate Monica Mar 22 '18 at 15:40
• – Scortchi - Reinstate Monica May 6 '18 at 10:28

I'm not aware of a foolproof way to do this. Here's one idea off the top of my head:

1. Treat values as categorical by default.
2. Check for various attributes of the data that would imply it is actually continuous. Weight these attributes based on how likely they are to correlate with continuous data. Here are some possible examples:
• Values are integers: +.7
• Values are floats: +.8
• Values are normally distributed: +.3
• Values contain a relatively small number of unique values: +.3
• Values aren't all the same number of characters: +.1
• Values don't contain leading zeros: +.1
3. Treat any columns that sum to greater than 1 as being numerical. Adjust the factors and weights based on testing against different data sets to suit your needs. You could even build and train a separate machine learning algorithm just to do this.
• This answer is interesting. Instead of giving another answer, I'd like to suggest a modification to this one. What about treating this problem as a regular classification problem? You could define a set of features for each column, based on the attributes in this answer (integer, float, normally distributed, relative number of unique values, etc.) After that, it is just a matter of using a classification algorithm. – Pablo Suau Jan 22 '16 at 10:12
• @PabloSuau, I like that idea. Classification algorithms might be a great method here. Thanks to jncraton for some characteristics to look at. – Poisson Fish Jan 22 '16 at 15:12

If you have, for example, number of children of a family (which could range, for example, between 0 and 5), is it a categorical or numerical variable? Actually it depends on your problem and how you intend to solve it. In this sense, you can do the following:

• Compute the number of unique values of that column
• Divide this number by the total number of rows
• If this ratio is bellow some threshold (for example, 20%), you consider it categorical.

In case of discrete values, one additional test could be: use a regression model to estimate some of the parameters and check if the estimated values are contained in the original set of values. If this is not true, you are probably dealing with categorical data (as it is the case of ZIP).

It worked relatively well for me in the past...

Since this question has been cross-posted, the initial comments by @nickcox on Cross Validated are highly relevant and true. My views are slightly different. For instance, I would rephrase the question, decomposing it into two parts: first, there is the issue of how one would go about classifying a stream of unknown information by data type and, second, what kind misclassification rate can be expected. Both questions are immediately answerable, if someone wants to take the time to do it.

The first issue amounts to developing a rules-based expert system along the lines of what has been preliminarily proposed in the comments and answers on these threads using samples of real-world data that are readily available.

Second, given that this real-world data contains known data types, benchmarking the classification error.

A third step would involve iteratively updating the rules to more accurately reflect the information that is being passed through -- to the extent that this is possible.

In my mind, while tedious, such a project wouldn't involve a prohibitive amount of work.