Ask HN: What is the best tool to infer data type of tabular data?

#1

Post by mahalel » Tue, Jun 08, 2021, 8:51 PM UTC

Hi HN, I am looking for the most accurate tool which I can use to infer Data Types from Tabular data (csv,tsv,excel)

I need to be able to perform some small customization, if possible, to the detection algorithm. For example if I have a 9 digit number, starting with 0, then treat it as a String.

So far - I have found Frictionless Framework [0] which seems good, but I can't see any way of specifying customizations to the profiling algorithm, and Data Profiler [1] which uses ML for type detection, and it seems I should be able to train some new rules but I need a CUDA capable machine, which at the moment I do not have.

Hoping the collective HN brain can point me to something better if it exists.

[0] - https://framework.frictionlessdata.io/ [1] - https://github.com/capitalone/DataProfiler

Re: Ask HN: What is the best tool to infer data type of tabular data?

#2

Post by quickthrower2 » Tue, Jun 08, 2021, 9:52 PM UTC

I’d probably knock one up in nodejs ad follows:

Import a csv reader library that can stream.

Read each line and apply a series of regex, each one classifying on match.

Eg

    ^0\d{8}$

Means string

Then have a reduction rule e.g.

If so far we think it’s a numeric column and we get a string then treat as string.

If so far we think it’s a numeric column and we get a number it is still a numeric column.

Then doing the regex and reduce in a loop will give you the final answers.

Happy to knock up some example code if you wish.

Re: Ask HN: What is the best tool to infer data type of tabular data?

#3

Post by switch007 » Tue, Jun 08, 2021, 10:12 PM UTC

What kind of types?

https://pandas.pydata.org/pandas-docs/stable/reference/api/p... is pretty powerful (see also "parse_dates" and "converters" parameters). See also parse_excel()

You can also use procedural code to look at the column data and change the type:

    # if all values in col c2 when converted to string begin with "0" and and values are of length 9, convert to int64
    if df["col"].str.match("^0").all() and set(df["col"].str.len()) == {9}:
        df["col"] = df["col"].astype("int64")

Re: Ask HN: What is the best tool to infer data type of tabular data?

#4

Post by mahalel » Wed, Jun 09, 2021, 3:08 AM UTC

quickthrower2 wrote: post #2

I’d probably knock one up in nodejs ad follows: Import a csv reader library that can stream. Read each line and apply a series of regex, each one classifying on match. Eg ^0\d{8}$ Means string Then have a reduction rule e.g. If so far we think it’s a numeric column and we get a string then treat as string. If so far we think it’s a numeric column and we get a number it is still a numeric column. Then doing the regex…

Hey that's pretty awesome, I'm ok at the regex stuff, but not that familiar with NodeJS. If you don't mind throwing me a snippet to develop from, that would be much appreciated.

Re: Ask HN: What is the best tool to infer data type of tabular data?

#5

Post by mahalel » Wed, Jun 09, 2021, 3:10 AM UTC

switch007 wrote: post #3

What kind of types? https://pandas.pydata.org/pandas-docs/stable/reference/api/p... is pretty powerful (see also "parse_dates" and "converters" parameters). See also parse_excel() You can also use procedural code to look at the column data and change the type: # if all values in col c2 when converted to string begin with "0" and and values are of length 9, convert to int64 if df["col"].str.match("^0").all() and set(d…

Thank you! I will have a play with it :D

Re: Ask HN: What is the best tool to infer data type of tabular data?

#6

Post by quickthrower2 » Wed, Jun 09, 2021, 9:52 PM UTC

mahalel wrote: post #4

quickthrower2 wrote: post #2

I’d probably knock one up in nodejs ad follows: Import a csv reader library that can stream. Read each line and apply a series of regex, each one classifying on match. Eg ^0\d{8}$ Means string Then have a reduction rule e.g. If so far we think it’s a numeric column and we get a string then treat as string. If so far we think it’s a numeric column and we get a number it is still a numeric column. Then doing the regex…

Hey that's pretty awesome, I'm ok at the regex stuff, but not that familiar with NodeJS. If you don't mind throwing me a snippet to develop from, that would be much appreciated.

It doesn’t have to be nodejs I think this would be just as easy in python or Java etc.

I’ll put something together though when I get some time

Re: Ask HN: What is the best tool to infer data type of tabular data?

#7

Post by mahalel » Thu, Jun 10, 2021, 4:21 AM UTC

quickthrower2 wrote: post #6

mahalel wrote: post #4

Earlier quoted context omitted.

Hey that's pretty awesome, I'm ok at the regex stuff, but not that familiar with NodeJS. If you don't mind throwing me a snippet to develop from, that would be much appreciated.

It doesn’t have to be nodejs I think this would be just as easy in python or Java etc. I’ll put something together though when I get some time

Hey, I got it working in Python with pandas, thanks so much for the suggestions

Re: Ask HN: What is the best tool to infer data type of tabular data?

#8

Post by mahalel » Thu, Jun 10, 2021, 4:23 AM UTC

switch007 wrote: post #3

What kind of types? https://pandas.pydata.org/pandas-docs/stable/reference/api/p... is pretty powerful (see also "parse_dates" and "converters" parameters). See also parse_excel() You can also use procedural code to look at the column data and change the type: # if all values in col c2 when converted to string begin with "0" and and values are of length 9, convert to int64 if df["col"].str.match("^0").all() and set(d…

Got it working, thank you ;)