Hacker News Reader

Hacker News

Comment by lettergram | Hacker News Reader

Full thread

lettergram

Hope this helps someone:

https://github.com/capitalone/DataProfiler

We’re working to automate much of that as well in a Python library.

The end goal is to:

1. point at any dataset and load it with one command

2. Calculate statistics and identify entities with one command

3. Generate robust reports with one command.

Regarding the data wrangling... Believe it or not, even automatically detecting a delimited file with a header is hard work. Imagine a header can be on the 3rd row and has a title and author ship one rows 1 and 2 respectively. Further, the delimiter might be the “@“ symbol!

The linked library wrote handles that scenario. But that’s just CSVs, there’s also Json, parquet, Avro, etc etc...

This is an extraordinarily deep and complex field.

View on HN

Do you handle the cases where people nest delimited values inside cells? Like if a cell is itself a delimited set of codes? What about when two cells in a row do that together and are assumed to be related? Like if a cell contains a delimited set of codes and another cell in the row contains a delimited set of descriptions for those codes? What about when people transpose or unmelt their data? Like if all the categories are down the side instead of across the top and identifiers are across the top and then they have a bunch of cells in the middle that are all pluses and minuses and blanks? Or if some arbitrary column itself contains novel header values and their existence implies a yes/positive value? (btw this is not finger wagging. please work on these!)

People always think that the hard part of automated data wrangling is things like delimiters and headers, but the real hard part of automated data wrangling is when people who aren't data wranglers themselves are making the data or when the rules or apparatus for filling cells are even the slightest bit flexible because people will always find a way to mess with you.

We have been through these issues with Easy Data Transform. We try to infer the format when you drag a data file onto the software based on the file extension and content. For example if it has extension .tsv and have more tabs than commas it is probably a tab delimited file. But there are so many variables, even for just a CSV file:

-delimiters

-quoting

-text encoding

-line endings

-headers

When you add in XML, JSON, fixed width etc is gets complicated fast.

So you can never guarantee to infer the data structure automatically. We just make the best guess we can and then allow the user to change those guesses and see the result in real time.

You can use a 'summary' transform to quickly summarize the data (e.g. number of blank values, min, max, numbers, dates etc).

This looks very useful at the start&end of our data processing pipelines, to quickly generate some stats on data that goes in/comes out

Thanks a lot for sharing!

This may be exactly what I need. I want to point it to a csv file and generate a schema of what type of data is in each column.

I do want some flexibiliy though, for example, if a certain column is filled with 9 digit numbers, starting with 0 then treat it as string.

Will have a look at it, thanks for the link!

Not the solution you are looking for, but…

SSIS will generate a schema for a CSV file and then let you modify any column you choose (including what you described). All it does, really, is generate the schema that the “SQL Server bulk copy” tool interprets.

Can I do this programmatically? I need to automate some manual processes.

SSIS? Yes, of course.

It’s standard Microsoft. You can do a million things with it and it can be integrated into the entire Windows ecosystem, but go too far and you are locked in. PS, the version control experience is bad. Really bad.

For casual use, by all means use it. It’s powerful and high performance. For real production use, perhaps try to find something else.

This looks really useful. Thank you for sharing!

Reply on news.ycombinator.com