In a given dataset, a column of numbers can represent a categorical or numerical variable. While one can imagine some simple heuristics for guessing which (e.g., it's categorical if the number of unique values is less than some fixed threshold), I think it would be a fun exercise to train a classifier instead. Features to pass to the classifier might include the number of unique values and the total number of values.

The only difficulty is that I need a labeled data set of such features. It's probably too much to expect that such a meta-dataset already exists, so my question is: are there open data repositories that are sufficiently uniformly structured that I could pull the datasets and assemble the features in an automated way?

  • Can you provide examples of when numbers represent a category versus a variable? I guess I see a flaw with calculating percentages of distinct values as they could be categories or variables depending on the context of the column. It seems only a data dictionary could definitively explain what the column data is representing.
    – Sun
    Sep 5 '14 at 6:18
  • You're exactly right, which is why I find the problem interesting. My idea is to use ML to find a heuristic smarter than a hard percentage threshold. Sep 9 '14 at 3:27

Two things:

  1. Existing functionality: The two main open data portal software platforms I have worked with, Socrata and CKAN, have this functionality built in. Socrata is closed source while CKAN is open source.

  2. Check out data.gov, datahub.io, and others for a lot of open data to "train" from. Notably:

    • For Socrata API endpoints, such as https://open.whitehouse.gov/Government/2010-Report-to-Congress-on-White-House-Staff/rcp4-3y7g you can get column metadata at URLs such as https://open.whitehouse.gov/views/rcp4-3y7g/columns.json. Be careful though, this is no longer documented on dev.socrata.com from what I can tell and might be deprecated/unsupported
    • For CKAN, you need to find the resource ID of a datastore-backed resource, and you can create a URL such as and it will show you the metadata of the columns as well

Hope this helps!

  • Thanks for the detailed response. I haven't looked into (2) very carefully yet, but regarding (1): the column type inference in messytables is for determining the appropriate storage format (e.g., string, integer, float). My problem is a bit less trivial: is a given list of integers semantically categorial or numerical? In general, it is impossible to know, but there are probably some very good heuristics. Aug 30 '14 at 20:23
  • Ah - I see. That is interesting and is probably more of a statistics question, IMHO, than an open data one. I would assume most numbers are numerical unless otherwise noted. Aug 30 '14 at 20:25

I have found a useful resource for this project, the readMLData R package. It provides a uniform R interface to the UCI Machine Learning Repository. Crucially, it includes copious metadata for each dataset.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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