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:
- If a column is all integers, label it as categorical. This clearly wouldn't work, but it was an idea.
- 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.
- 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.