14 ms·
Transform Data by Example [video]
- teddyh 9y agoYou know what this reminds me of? Those trained neural-net things which, however many training examples you give it, always seem to find some way to “cheat” and not do what you want while still obeying all your training data correctly. Something like this: Suppose we have a table of strings of digits, some including spaces, and we’d like to remove the spaces. From 123 456 234567 345 678 to 123456 234567 345678 Now, what happens if it encounters, say 4567890 Would the result be unchanged (as we would probably want), or would it “cheat” and remove the middle “7” character, giving “456890”?
- function_seven 9y agoThis is why I want any ML device to be able to explain itself. It could train on your before-and-after examples and come up with a list of what it thinks you want it to do. For your example, it could list: “Remove interior spaces from each item” or it could say: “Remove the middle character from any 7-character strings to make them 6 characters in length” You would be able to do something with that.
- ChristianBundy 9y agoIIRC you _can_ get this, but it's a huge algorithm that doesn't do things in a way that would probably make sense to a human. It would be amazing to be able to transform code into human language.
- function_seven 9y agoI was sloppy in my own examples. I’d be perfectly satisfied with an AST, or regex, or other non-English explanation. But something that I can audit is what I’m after. Otherwise this tool would never be trustworthy enough to let lose on billions of rows of data, with silent errors occurring throughout. (Well, I guess it depends on the nature and importance of the data. Cat photos, meh. Drug prescriptions? Ahh!)
- jononor 9y agoMaybe also synthesize and suggest property based tests, by one specify also some invalid examples. Then these checks could be ran for each transformation. For instance: - 123 456 = 123456 valid - 1234567 = 123567 not valid (dropped 4) Properties: - output may not contain whitespace - no number characters may be dropped - characters may not be reordered
- paulddraper 9y agoNeural nets are infamous for not doing this. Learning algorithms that produce decision trees are usually used in this situation.
- allenz 9y agoIt's theoretically possible for a neural net to do this; the network just needs to have the explanation as an output. I agree that decision trees would be more reliable and easier to train, but I'm not sure if hardcoding every feature is scalable.
- function_seven 9y agoHow do you know that the explanation jives with the other outputs, though? It seems like a turtles-all-the-way-down situation, because now I want to see how it was properly introspective of its own decision making. Also seems like it’s another magnitude of complexity in the neural net to have it not only train and learn on your inputs, but also train and learn on its own training and learning.
- snaky 9y ago>How do you know that the explanation jives with the other outputs, though? The third neural net would do the checking, obviously.
- allenz 9y agoNeural networks don't do anything as sophisticated as self-referential introspection. They just fit the outputs you train them with. The training data you provide would have to include the desired explanations. Consistency is enforced by the dataset, and also by the model. Both outputs would read from the same hidden layer--the one that encodes the desired transformation.
- randomsearch 9y ago> the network just needs to have the explanation as an output And how would you evaluate whether the explanation was correct or not?
- zitterbewegung 9y agoSome ML systems (like decision trees) can give you a comprehenable way that they made the decision (would give you a list of if conditionals). Unfortunately many can't do this (random forests) . Having an AI that can explain itself in every situation why it does it also has to do with the underlying techniques. For instance random forests generate a random sample of the features and creates decision trees. So the explanation wouldn't be much useful to you.
- nerdponx 9y agoNot true. Look up "partial dependence plots".
- jnpatel 9y agoDataWrangler [0] (now productionized as Trifacta Wrangler [1]) does pretty much that. It gives you suggested lists of transformations such as "Cut from position 18-25 as the Year column", that you can chain together as your data cleaning pipeline. [0]: http://vis.stanford.edu/wrangler/ http://vis.stanford.edu/wrangler/ [1]: https://www.trifacta.com/products/wrangler/ https://www.trifacta.com/products/wrangler/
- pishpash 9y agoThis add-on already does that. (Did nobody try it??) It shows in the pane a list of candidate transforms, seemingly ranked in some descending plausibility order. They have semi-readable names. You get to choose one to apply.
- randomsearch 9y ago> This is why I want any ML device to be able to explain itself. This is the problem of lacking explanatory mechanisms in ML. Note that some techniques that are very out of vogue at the moment, such as Genetic Programming, are much better than neural nets in this regard.
- OscarTheGrinch 9y agoYes, this system should do have an intermediate step of spiting out a checklist of clear rules and the user can select the best fit, saving the human the time it would take to search from a bloated dropdown of all possible rules.
- sundarurfriend 9y agoThis is the stated goal of the Explainable AI initiative, (spearheaded afaik by DARPA, though Google tells me corporates have also began work on it). I hope it works out well because there's going to be a lot of AI code in the near future, and the thought of them all being inscrutable black boxes is pretty scary.
- teddyh 9y agoBut, you know, if you saw something like, all your visible examples were like the strings 123 456 234567 345 678 and the program replies with something like what you wrote: “Remove the middle character from any 7-character strings to make them 6 characters in length”, it would actually take a programmer’s mind to be able to envision why this might in some cases be wrong. Most people who are not programmers would, I think, see this as equivalent to “Remove interior spaces from each item”. I suspect that the skill required to choose an algorithm correctly is the exact same skill required to actually being a programmer. All this then buys you is that you don’t have to remember the function names.
- Giroflex 9y agoThis isn't the machine's fault, though; for a small number of linearly independent examples there exists an enormous number of possible functions that match the training data. It has no way of guessing, really.
- thomasahle 9y agoIf the machine had a large background knowledge of what humans would typically like to do, it would help.
- tonmoy 9y agoMS had been experimenting with this for a while[1]. They even included this in Excel 2013 as "FlashFill". It does not use any NN/ML at all. It uses "program synthesis", which by definition can tell you exactly what "program" it has synthesized to convert you data. In fact in you example it would not cheat, rather leave the string unchanged as explained in the paper. [1] https://www.microsoft.com/en-us/research/publication/automating-string-processing-spreadsheets-using-input-output-examples/ https://www.microsoft.com/en-us/research/publication/automat...
- randomsearch 9y agoMore generally, anything by Sumit Gulwani's group at MSR should be of interest.
- teddyh 9y agoBut maybe I did want it to remove the middle character! Using my training data, there’s no way for the system to actually know for sure what I meant. There is also no way (in general) for it to detect “outliers” and ask me about them, because there is no good way to know what is an outlier and what is not.
- vanderZwan 9y agoYou can modify the output of the program synthesis to fit your needs. See this CurryOn! talk from 2015 by Sumit Gulwani https://www.youtube.com/watch?v=uqV9BlxEG5s https://www.youtube.com/watch?v=uqV9BlxEG5s
- TuringTest 9y agoThis is why this kind of software should have an interactive 'feedback' function that allows the user to select among several, equally likely rules. The experimental Lapis editor[1] did exactly this, by the way. [1] https://en.wikipedia.org/wiki/Lapis_(text_editor) https://en.wikipedia.org/wiki/Lapis_(text_editor)
- randomsearch 9y agoSounds like you're talking about over-fitting the data. Or perhaps just not providing an evaluation function that is sufficiently general. All ML can fall into this trap.
- dimaggiosghost 9y agoI agree, thank you for peovoking this thought. It is raw and if so I apologize. This is where hinting is important. Metadata. That sequence if I know it's a phone number, or a sequence of increasing digits, depends a lot on metadata. Given some reasonable sample size, i believe machine learning could provide hints as to some of the common types of formats. Semi automated data hinting or structuring? There is a bidirectional connection between interpreting your data and how your data is structured Is it possible to use your data column to statistically hint at metadata characteristics by some sort of clustering, then use that to automatically clean input data?
- thewarrior 9y agoYou don't realise how big this is. This is the beginning of the automation of coding.
- coldtea 9y agoWe had such things for decades. Besides, it depends on the slope of "coding". If it gets really difficult really quick (exponentially say), this could just be forever stuck in the "low hanging fruit" stage.
- teddyh 9y agoNo, assembly language was the beginning of automation of coding. Almost nobody codes using raw machine code anymore. Everything since is just more added abstractions.
- ke1vin 9y agoWhen you only provide the system with a few examples, there are many possible transformations which satisfy the examples. One way to eliminate this ambiguity is to also provide a natural language description of what you want, e.g. "remove the spaces". In the natural language processing community, we call this semantic parsing. But sometimes the semantic parser can misinterpret the language too and generate a program which still "cheats" in the same manner as you described. We call these "spurious programs". Shameless plug-- my group has been working on how to deal with these spurious programs: From Language to Programs: Bridging Reinforcement Learning and Maximum Marginal Likelihood https://arxiv.org/abs/1704.07926 https://arxiv.org/abs/1704.07926
- netvarun 9y agoIs this related/a commercial application of the 'Deep Learning for Program Synthesis' post[0][1] from Microsoft Research on HN a month ago? [0]https://www.microsoft.com/en-us/research/blog/deep-learning-program-synthesis/ https://www.microsoft.com/en-us/research/blog/deep-learning-... [1]HN Discussion: https://news.ycombinator.com/item?id=14168027 https://news.ycombinator.com/item?id=14168027
- matt4711 9y agoThere is a paper describing such a method (not sure if that is what was implemented): "Zhongjun Jin, Michael R. Anderson, Michael J. Cafarella, H. V. Jagadish: Foofah: Transforming Data By Example. SIGMOD Conference 2017: 683-698"
- tdbeteam 9y agoNo, the paper co-incidentally shares a similar name, but the capabilities are different, and there is no relation in terms of people or underlying technology. https://www.microsoft.com/en-us/research/project/transform-data-by-example/ https://www.microsoft.com/en-us/research/project/transform-d...
- tejtm 9y agoalas it is too late, it transformed our genes to dates, no sequence for Bill
- logicallee 9y agoIt would be nice if it indicated where it was making stuff up (in the zip code example, for the rows that were missing some data, it just makes it up - these rows are not distinguished visually from the rows where it did not add data not in the input.) What I mean is if every row had a date like "12 May 2002" and you wanted it turned into 2002.05.12 then it would be nice if it indicated when it added data. For example if one of the rows just read "15 May" then, since there is no year, it would not be completely absurd if it transformed into 2017.05.15 - or if all of the other data is 2002, then adding that. But I really think silently adding data that was not in the input is going too far. A transform shouldn't ever silently inject plausible data with no indication that this is interpolated. Bad things can result. Otherwise great demo!
- unfamiliar 9y agoHumans are really good at taking a vague description of a task and using a small number of examples to disambiguate it. For example, "sort all of the folders, so that it Alan goes before Amy, etc". The rule ("sort") is pretty ambiguous, but one simple example in the context gives enough information to realise you probably mean alphabetically by first name. Is there something like this example that could be combined with NLP to make things like these "intelligent assistants" we have now much more useful for data processing tasks? It would be great to describe data manipulation to a machine the way that I would describe it to a colleague: give an overview of an algorithm, watch how they interpret it, and correct with a couple of examples in a feedback loop. Currently describing such things for a machine requires writing the algorithm manually in a programming language.
- paulddraper 9y ago> Humans are really good at taking a vague description of a task and using a small number of examples to disambiguate it. IIRC this is tested heavily in IQ tests.
- jdironman 9y agoAbstract thought really.
- Spiritus 9y agoIsn't just because we've already been trained on that since learning the alphabet? Imagine giving a human the same question, but sort "aa" before "bb" but after "cc".
- TuringTest 9y agoMaybe it's "just" because of previous training, but it's still a very useful ability, which programs do not have. Being able to solve quickly the most common cases (which rely in such "common knowledge") would automate a lot of work that now requires writing a complex program in advance, and would allow the user to concentrate on the outliers that require more thought.
- ktamura 9y agoThis is a great product idea. If you ask any Excel power users, by far the most time-consuming and hard-to-automate task is text and date manipulation. The beauty of this product is that its adoption strategy is baked into the product itself: I'd share this with all Excel user friends of mine because I want the algorithm to get smarter, and I might even learn a bit of C# myself so that I can contribute and scratch my own itch. This in turn makes the product better (because of the larger training data), lending itself to more word of mouth. One concern I have is security: I'd love to hear from folks who built this/more familiar with this about how to ensure the security of suggested transformations.
- haswell 9y agoI, too, wonder about security. Just as important: performance/scaleability. What happens when this runs on 100K rows against a service some guy stood up as a weekend project? Now what happens with 100 people hitting that service? Either way, this looks very useful. Having spent more than my fair share of time massaging data prior to import, this looks pretty great.
- Analemma_ 9y ago> What happens when this runs on 100K rows against a service some guy stood up as a weekend project? Now what happens with 100 people hitting that service? Then they complain to Microsoft, who helpfully suggests the product they should upgrade to. This has always been a strong spot of Microsoft's. "I see you've scaled beyond the capacity of [Product A]. Well, fortunately for you we have [Product B] which can handle it, with a nice import wizard to get you started painlessly." It typically goes Excel > Access > On-prem SQL Server > Azure. This sounds very negative and I swear I don't mean it that way. It's a great sales tactic if you offer products at every level of scale.
- paulddraper 9y agoSQL Server lite < SQL Server
- dandare 9y ago
- wayneprice 9y agoI'm playing around with a client-side js implementation of this at https://www.robosheets.com/ https://www.robosheets.com/ It's not production ready / launched yet, but it's getting there. I'd be interested to finds (or really doesn't find) this useful :)
- eob 9y agoI wasn't able to figure out how to use the app, but I did want to drop in to say that I like how you've placed the "Upgrade to enable" notices in cells past a limited range.
- fiatjaf 9y agoSee also http://www.transformy.io/#/app http://www.transformy.io/#/app Ok, just realized somehow the site has vanished. Not working archived version: http://web.archive.org/web/20161028231256/https://www.transformy.io/#/ http://web.archive.org/web/20161028231256/https://www.transf...
- awake 9y agohttps://apps.synesty.com/transformy/en-us https://apps.synesty.com/transformy/en-us this might be it
- copperx 9y agoThat's great, I always loved Auto Fill in Excel, and this brings it to the Mac.
- copperx 9y ago* Correction. I meant Flash Fill. And this is isn't an exact replacement, but it's pretty close.
- sjg007 9y agoThere's a huge opportunity in making excel better..
- bcherny 9y agoWait, Excel had this built in since 2013! https://support.office.com/en-us/article/Use-AutoFill-and-Flash-Fill-2e79a709-c814-4b27-8bc2-c4dc84d49464 https://support.office.com/en-us/article/Use-AutoFill-and-Fl...
- taeric 9y agoI recall a joke from someone... Can't remember who, that went like, "does your startup compete with Excel? Did you just rediscover pivot tables?"
- gshulegaard 9y agoExcel is a really powerful tool. If you are fine with needing Windows or Mac (e.g. not Linux) and you are ok with their licensing constraints it's pretty hard to beat.
- cinch 9y agothe addon supports "excel online"
- Cieplak 9y agoI wonder if it uses Z3 under the hood for solving constraints. Very nice of MSFT to MIT license Z3. It's super useful for problems that result in circular dependencies when modeled in Excel, and require iterative solvers (e.g., goal seek). I use the python bindings, but unfortunately it's not as simple as `pip install` and requires a lengthy build/compilation. Well worth the effort, though. https://github.com/Z3Prover/z3 https://github.com/Z3Prover/z3 https://github.com/Z3Prover/z3/issues/288 https://github.com/Z3Prover/z3/issues/288
- tonmoy 9y agoLove Z3. It is easy to use and very decent performance! I don't think MS is using Z3 on this product though, looks more like the smart enumeration based program synthesis
- c-cube 9y agoI think most of the enumeration-based synthesis tools rely on a SMT solver (Z3 or CVC4, say) to learn from bad solutions.
- gergoerdi 9y agoCheck out MagicHaskeller which figures out list processing functions from examples: http://nautilus.cs.miyazaki-u.ac.jp/~skata/MagicHaskeller.html http://nautilus.cs.miyazaki-u.ac.jp/~skata/MagicHaskeller.ht... For example, given the rule `f "abcde" 2 == "aabbccddee"`, it even figures out the role of the parameter `2`, so `f "zq" 3` gives `"zzzqqq"`.
- Kiro 9y agoI would love something similar for Google Spreadsheet.
- mballantyne 9y agoI believe this is the implementation described in this paper published at POPL 2016: https://www.microsoft.com/en-us/research/publication/transforming-spreadsheet-data-types-using-examples/ https://www.microsoft.com/en-us/research/publication/transfo... Though it probably also uses more recent work from the same group: https://www.microsoft.com/en-us/research/people/sumitg/ https://www.microsoft.com/en-us/research/people/sumitg/
- gerhardi 9y agoThis was also included in the query editor of Microsoft's Power BI in the release a month or two ago. First you select the columns to be used as a source then start writing example values to the new column to be generated. It also shows the generated M/PowerQuery expression. It can't do miracles, but this is time saving in many cases like when you want to concatenate values from different columns in a new format into a single column and so on.
- JoelJacobson 9y agoI hacked together something similar that learns row/column offsets for different fields in a text file, and converts it into a normal CSV, i.e. a normal table. https://github.com/trustly/fixed2csv https://github.com/trustly/fixed2csv
- captnswing 9y agoSeems similar to http://openrefine.org/ http://openrefine.org/
- deleted 9y ago[deleted]
- deleted 9y ago[deleted]
- martinthenext 9y agoOh man, we did it before Microsoft! http://comnsense.io/ http://comnsense.io/ https://youtu.be/ALF9GY2K-wc https://youtu.be/ALF9GY2K-wc
- amelius 9y agoI want this in Vim :) This would be great for refactoring code.
- cblte 9y agonot usable for companies and secured networks. :-( too bad
- tdbeteam 9y agoRelationship to FlashFill feature in Excel: FlashFill is a popular feature in Excel that also uses the example-driven paradigm to automatically produce transformations. While FlashFill supports string-based transformations, Transform Data by Example can leverage sophisticated domain-specific functions to perform semantic transformations beyond string manipulations. For examples, see: https://www.microsoft.com/en-us/research/wp-content/uploads/2017/02/Sample_Reduced-1.xlsx https://www.microsoft.com/en-us/research/wp-content/uploads/...