14
$\begingroup$

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.

Improve this question
$\endgroup$
5
  • $\begingroup$ Not full proof but if it start with a 0 it is typically not numeric $\endgroup$
    – paparazzo
    Commented Jan 21, 2016 at 21:34
  • $\begingroup$ 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? $\endgroup$
    – Spacedman
    Commented Jan 22, 2016 at 12:13
  • $\begingroup$ I would take all the heuristics suggested in the answers as features and train a binary classifier. $\endgroup$
    – Emre
    Commented Jan 22, 2016 at 20:22
  • $\begingroup$ See stats.stackexchange.com/a/106400/17230. There isn't really a ground truth here. $\endgroup$
    – Scortchi
    Commented Mar 22, 2018 at 15:40
  • $\begingroup$ See also stats.stackexchange.com/q/336261/17230. $\endgroup$
    – Scortchi
    Commented May 6, 2018 at 10:28

4 Answers 4

Reset to default
10
$\begingroup$

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.
Improve this answer
$\endgroup$
2
  • $\begingroup$ 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. $\endgroup$
    – Pablo Suau
    Commented Jan 22, 2016 at 10:12
  • $\begingroup$ @PabloSuau, I like that idea. Classification algorithms might be a great method here. Thanks to jncraton for some characteristics to look at. $\endgroup$
    – Poisson Fish
    Commented Jan 22, 2016 at 15:12
4
$\begingroup$

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...

Improve this answer
$\endgroup$
2
$\begingroup$

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.

---- Additional Comment ----

A good discussion of feature extraction as it relates to the data generating process popped up on CV here:

https://stats.stackexchange.com/questions/191935/what-does-the-process-that-generates-the-data-mean-and-how-does-feature-selec

Feature extraction, selection and the DGP is an excellent point of departure for developing an expert system.

Improve this answer
$\endgroup$
2
  • $\begingroup$ I have deleted the question on Cross Validated. For reference, @NickCox stated: "It's worse than you think, even if you think it's worse than you think. Decimal points could lurk within categorical variables, as part of coded classifications, e.g. of industries or diseases. Small integers could mean counts rather than categories: 3, meaning 3 cars or cats, is not the same as 3, meaning "person owns a car" or "person is owned by a cat". Measurements could just be integers by convention, e.g. heights of people may just be reported as integer cm or inches, blood pressures as integer mm Hg." $\endgroup$
    – Poisson Fish
    Commented Jan 22, 2016 at 15:32
  • $\begingroup$ @NickCox second comment: "The number of distinct (a better term than "unique", which still has the primary meaning of occurring just once) values is not a good guide either. The number of different heights of people possible in moderate samples is probably much less than the number of different religious affiliations or ethnic origins." $\endgroup$
    – Poisson Fish
    Commented Jan 22, 2016 at 15:32
0
$\begingroup$

What if we use LLM to classify the column as Categorical or Numerical .. it will be able to understand based on the column name to which category it will fall

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.