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.
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.
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.
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).
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.
There is a grey area between cleaning data and feature engineering. At some point, the more sophisticated methods for data cleaning becomes imputation and inference of features. I wonder what are the ramifications of this.
I hate SPSS, SAS and other GUI enhanced statistical software because it makes it too easy for the blind with no sound training to analyze data and come up with laughable conclusions. This is a nightmare.
I have been working on this problem for a little while. I don't think the issue is that Excel is inherently worse for data wrangling -- it just isn't scalable or easily traceable. My solution is a spreadsheet extension for Jupyter Notebooks, that lets you transform your data visually and outputs the equivalent Pandas.
Automated data prep/transformation is definitely a useful solution, and obviously is going to be a part of most data wrangling tools in the future. I think the danger is a lack of visibility and tracing, something a combo between spreadsheets and pandas provides well -- I think :)
Like to suggest adding to the page title on the homepage - maybe even just 'data python spreadsheets jupyter' -
when went to bookmark I noticed it only had 'mito' in the name.. so if I go back like 3 weeks from now to my bookmarks or whatever and type spreadsheets jup - maybe your thing would not show up (?)
Maybe I'm wrong - I have yet to do a deep dive on what data firefox indexes when I bookmark things.
Well I actually think people with no appropriate training shouldn't do open brain surgery. I think the main problem here is underestimating the complexity of a problem and these tools don't improve the situation.
I think a large part of the problem is that the Tolstoy "All clean data is clean in the same way, but all dirty data is dirty in its own way" riff is itself an over-simplification. In practice, "clean" just means "fit for my purpose" and "dirty" just means "unfit for my purpose".
If counting the instances of a particular string is all you need to do (and it quite often is) then de facto all data is "clean", as you can get the job done with grep. If, on the other hand, you require a relational database with strict primary and foreign key definitions and NULL limitations, then you're gonna have to put a lot more work into scrubbing.
Added to that, the more stringent the defition of "clean" you're working with, the more you're going to have to deal with trade-offs. E.g. what do you do with missing required data field values? Loosen the requirement? Add defaults? Dump those rows? And what do you do with data that makes no sense, e.g. where end dates come before start dates? What if you need to anonymize the data to meet data privacy requirements? Is rounding dates to the nearest month or year fine, or will it render the data useless?
There are recurrent problems, and recurrent solutions, and the author is absolutely right to say that more open source work should be done in that area. But due to complexity and inconsistency of the definitions of "clean" and "dirty", we will unfortunately never get to the point where we just feed some library the path to an arbitrary dirty data directory, press enter, go off to grab a coffee and come back to find our freshly cleaned data waiting for us.
I guess it is conceivable that you could take 1000 dirty input files and their cleaned output and use this as a training set for a ML-power data cleaning system. But:
Sure. You could and should do that if you can. But, as you say, it'll still only work for one narrow definition of "dirty" and another, equally narrow, definition of "clean".
I spend a good portion of my time creating / cleaning / structuring / feature engineering / running ml on data and I am extremely skeptical of automated ways of doing that.
Everything I have seen so far was so laughably besides the point. This whole process involves an extreme amount of judgment and tradeoffs and most importantly a lot of knowledge of the respective domain. I have seen so many people with no domain knowledge trying to run an "automated approache" and horribly fail.
My job also involves a lot of time cleaning and structuring data, and I agree. I've been able to automate some parts of a cleaning/transforming process within the same domain but it would be very rare for that same process to be applicable to another domain without modification (and extensive consultation with the subject experts).
26 comments
[ 3.0 ms ] story [ 35.0 ms ] threadIt is my favourite university spinout story here on this side of the pond.
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.
Thanks a lot for sharing!
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!
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.
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.
-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).
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.
2. The data is dirty. We need to clean it up.
3. Lets clean it up using machine learning!
4. Goto 1.
It's machine learning all the way down...
And wouldn't they just do in it Excel instead, probably with even worse results?
Website, for any interested: http://trymito.io/
Automated data prep/transformation is definitely a useful solution, and obviously is going to be a part of most data wrangling tools in the future. I think the danger is a lack of visibility and tracing, something a combo between spreadsheets and pandas provides well -- I think :)
Maybe I'm wrong - I have yet to do a deep dive on what data firefox indexes when I bookmark things.
Oh Excel ... I don't even know where to start.
If counting the instances of a particular string is all you need to do (and it quite often is) then de facto all data is "clean", as you can get the job done with grep. If, on the other hand, you require a relational database with strict primary and foreign key definitions and NULL limitations, then you're gonna have to put a lot more work into scrubbing.
Added to that, the more stringent the defition of "clean" you're working with, the more you're going to have to deal with trade-offs. E.g. what do you do with missing required data field values? Loosen the requirement? Add defaults? Dump those rows? And what do you do with data that makes no sense, e.g. where end dates come before start dates? What if you need to anonymize the data to meet data privacy requirements? Is rounding dates to the nearest month or year fine, or will it render the data useless?
There are recurrent problems, and recurrent solutions, and the author is absolutely right to say that more open source work should be done in that area. But due to complexity and inconsistency of the definitions of "clean" and "dirty", we will unfortunately never get to the point where we just feed some library the path to an arbitrary dirty data directory, press enter, go off to grab a coffee and come back to find our freshly cleaned data waiting for us.
-It would only work in a narrow domain.
-How do you know if you can trust the results?