Amongst other things this can be used for cleaning tables/lists from special characters, changing date formats and creating xml or json.
Feedback and suggestions are very much welcome! We plan on adding a few more features soon as right now it is fairly basic but would like to hear some opinions and see if there's people out there that have a use for this.
This is really neat! Any chance this will be a cli tool or module/library?
It doesn't seem to play will with something like this as an input:
3, Roberto/Carlos, soccer, Brazil
35, Roberto/Carlos Michael Jordan, baseball, USA
6, Roberto/Carlos James Lebron, basketball, USA
10, Roberto/Carlos Shinji Kagawa, soccer, Japan
Format:
3, ROBERTO/CARLOS, soccer, Brazil
Gives me:
3, ROBERTO/CARLOS, soccer, Brazil
35, ROBERTO/CARLOS, Michael, Jordan
6, ROBERTO/CARLOS, James, Lebron
10, ROBERTO/CARLOS, Shinji, Kagawa
I can't seem to find a way to get it to parse that out properly (playing with the ROBERTO/CARLOS part.
I even tried this as an input:
3, Roberto Carlos, soccer, Brazil
35, Roberto Carlos Michael Jordan, baseball, USA
6, Roberto Carlos James Lebron, basketball, USA
10, Roberto Carlos Shinji Kagawa, soccer, Japan
Format:
3, ROBERTO CARLOS, soccer, Brazil
Gives me:
3, ROBERTO CARLOS, soccer, Brazil
35, ROBERTO CARLOS, Michael, Jordan
6, ROBERTO CARLOS, James, Lebron
10, ROBERTO CARLOS, Shinji, Kagawa
I formatted their examples to appear like some real data I have that appears like this, obviously not names but descriptions of some projects. I was curious how this would handle it.
In any case, get rid of the "/" and its closer to real. Some people have more than two names in their full name. And on a set a little larger you could very well have something close to my second example.
Currently it matches word by word, so for example if someone has a family name in two parts like "Van Buyten", it wont work. I think it's the same problem in your example: that the first "column" contains multiple words in some cases? We'll be fixing this in a future release!
I thought that a cli version might be useful too. The closest thing I have right now is sed/awk. Sed can do this kind of stuff but you have to specify a Regular Expression instead of a simple example. Because you have to be more specific about what you want, Sed will definitely handle those examples, with the caveat that you have to tell Sed what it is that you want to substitute and where for each line.
It took me about a year of use before I could figure out how to munge lines in it, so it's definitely not for the faint of heart. I use it for things like transforming excel spreadsheets into C struct arrays.
I was curious and sketched up something similar to this website in about a 100 lines of Python code. It has a CLI interface, have a look if you're interested:
Not sarcastic... I didn't know about FlashFill. I looked at the videos, and it seems that it's not as powerful as this tool. In excel the results would be a custom cell format combined with custom text functions.
Not sure about the implementation in Excel. At least the paper (POPL 2011) describes more powerful functions, containing e.g. case distinctions and simple loops. There are some examples in there. I'm not an Excel expert, but would be surprised if cell format could do that.
I could have used this hundreds of time in Visual Studio.
We often process messages with hundreds of fields. So I will have a class from specs (usually excel) with a number of properties, then I need a method to populate each of those from some other object, then I need a method that does the reverse - populates some other object from the properties.
This happens over and over for us. Typically I just use a Notepad++ macro. But I could probably use this as it stands, just having it in visual studio would be really incredible.
You know, for people that don't know Awk or aren't comfortable with a scripting language, this is a really nice idea. Thinking back to grad school, which was in a non-computer science quantitative field, there are lots of people that would have appreciated having something like this easily available.
Afaik, "LeBron" is his first name. The way you use it (and misspell it) is confusing, I'd suggest you replace him with a simpler alternative (Kobe Bryant? Tim Duncan? whatever).
Same for Kagawa -- the Japanese use surnames in a different way, might be simpler to replace him.
The last example with JSON replace is really nice, I do this regularly with regular expression find-replace with groups, on larger datasets. I guess i can forget about regular expressions now. Nice work!
We don't interpret the data, we only change the format. Since "November" doesn't literally match anything on the first line, it is repeated for each one of them.
Feature requests: allow for more than one example.
Input:
{"class": "101", "students": 101}
{"class": "201", "students": 80}
{"class": "202", "students": 50}
{"class": "301", "students": 120}
Example:
Class 101 has 101 students
Output:
Class 101 has 101 students
Class 201 has 201 students
Class 202 has 202 students
Class 301 has 301 students
Right now the first line cannot have any ambiguity. This is fixable by reordering, but with large enough data sets I may have some ambiguity in all lines, at different places. Multiple examples would fix that.
Again, loved the tool. I can see this going very far, specially with non-technical people.
For that use case you can use the Lapis[1][2] desktop app (the secret weapon I use for data munging), which allows you to choose several examples and edit a file with direct manipulation - or define patterns using a DSL.
Right, but if you have a thousand items, with a dozen fields each, you can't be sure the one example you've picked will resolve all ambiguities. But if you could supply four or five example lines, the chances of ambiguity drop off.
You could just add a "dummy" input as the first line with all unique entries. Then just remove it from the output. Am I missing something? It's a tiny bit of additional manual processing, but doesn't seem unreasonable.
You could just add a "dummy" input as the first line with all unique entries. Then just remove it from the output. Am I missing something? It's a tiny bit of additional manual processing, but doesn't seem unreasonable.
This is expected behavior. I'm assuming you gave "a 1" as an example? Since your first line didn't contain the number 1, it is repeated over every line.
One can achieve the same in sublime text using the multiple cursors and edit feature. This is great for non tech people.
For those of you who are wondering what sublime text can do, do give a visit to a sublime text video series on tutsplus, its awesome and teaches you the power of sublime text
I just attended a talk but Sumit Gulwani where he demo'd FlashFill, FlashExtract and walked through their underlying architecture. His talk had much cooler demos than any I could find online but from what I can tell it seems like they provide a superset of transformy's functionality (not to detract from it, this is very cool, and I'd be curious to see how related the underlying theories are). Apparently current work by that team at Microsoft is focused on abstracting out functionality into a system called FlashMeta so that it can be applied to a bunch of domain specific problems. Overall very exciting work, from both parties.
Nice, Excel really is one of the most undervalued pieces of software, it has so many details like this and it handles almost anything you throw at it. Pivot table is another of the lesser known features that is hard to live without once you've found out that it exists.
Given that Excel runs many, if not most financial organizations at a practical level, I'm not sure that it's undervalued in practice.
Excel is to finance people as terminals are to developers. When getting bug reports from end users, we've even had them delivered to us as screenshots embedded in Excel files.
On this same subject, I would just make the boxes on the very first page editable so you can just play with it right away. After reading the description at the bottom I spent a few seconds trying to manipulate the text boxes on the front page before I realized I had to click a button. It would be really cool if they were live editable examples right on the front page.
A few feature requests: allow downloading the output as a text file; show a pseudo-code formula of how transformy interpreted the transformation, like "s/.+:\/\/(.+?)\/(.*)/\1, \2/"; and add support for common arbitrary transformations like "November"↔"NOV"↔"11", or "2"↔"2nd".
I think it's trying to be too magical. At this point it either seems to work, or something triggers it's pattern matching wrong and it's really hard to figure out what or why. I think giving back a little of the simpleness in favor of more control is worthwhile. For example, if the example portions that were formatting were differentiated from the data matching, it's not too complicated but intent is much clearer.
For example, if the rules were: example content must be contained within braces, and any braces within the example content need to be escaped, it's clear. At that point, your example becomes:
{example1.org}, {path/index.html}
It would still probably just return "wwwexample.4, g/hijklmnop" for the last example though, because it's ambiguous as to whether you want just the end of the url, or the whole thing. Allowing regex markup for more explicit matching would make it clearer still, but your example still causes problems until you go all the way to positive lookbehind assertions. At that point I need to learn all that, I might as well just use perl:
Sketching means to provide a partial specification, of which details are filled in by the system. But in this case, the user is providing a full description of a single concrete example. These are different concepts.
Thanks for the clarification, they are similar but not the same. It looks like _sketching_ is more analogous to using Hindley-Milner for filling in gaps in an executable spec, where as Programming by Example infers code from data (examples). There is a wealth of interesting material referenced in http://web.media.mit.edu/~lieber/Your-Wish-Intro.html
Sketching is a superset of programming by example. In examples, you only get input/output pairs, which as this thread shows, infers frustratingly close but wrong programs.
In sketching, the input and output can also includes partial programs (sketches), so you can mark what you like, tweak, etc, and it fills in the rest.
Increasing usability, the fragments can be in different languages. For example, input can be simple C and some test data, and output can be CUDA GPU code or pthread mutex locking schemes.
For Graphistry.com, we believe in these techniques for ETL and sketching visualizations.
(Also, sketching normally uses machine learning or SAT/SMT solvers: types are more typically used for input hints.)
Cool. I have been mulling what it would be like to have a semigraphical tool to generate SQL queries (Spark,Hive,VoltDB,etc) where the user would drive a keyboard applying operators over columns, rows and relations for mapping, filtering, joining. Like VIM plus ZPL and the goal is a statistical result over some range. Why can't prolog programs generate our programs? One of my query visualizations is an origami like structure where data is joined across a relation, something like an Explain Plan on Hollywoolsd.
By analogous, I meant more generally, using some partial knowledge about a program or spec to fill in missing pieces, not types specifically.
Nice to know folks are using these techniques to do real world tasks, I always thought something like this would be first used for cleaning data. Types, properties, sketches and examples.
149 comments
[ 2.7 ms ] story [ 202 ms ] threadFeedback and suggestions are very much welcome! We plan on adding a few more features soon as right now it is fairly basic but would like to hear some opinions and see if there's people out there that have a use for this.
It doesn't seem to play will with something like this as an input:
Format: Gives me: I can't seem to find a way to get it to parse that out properly (playing with the ROBERTO/CARLOS part.I even tried this as an input:
Format: Gives me: Edit: formathttp://en.wikipedia.org/wiki/AWK
In any case, get rid of the "/" and its closer to real. Some people have more than two names in their full name. And on a set a little larger you could very well have something close to my second example.
See other response as it works on word by word. Here is (hopefully) a better example:
Input:
Transform-at: You'll get: But I was expecting:http://linux.die.net/abs-guide/x19673.html
It took me about a year of use before I could figure out how to munge lines in it, so it's definitely not for the faint of heart. I use it for things like transforming excel spreadsheets into C struct arrays.
On the first line, you have "Roberto Carlos" followed immediately by a comma. On subsequent lines you have Roberto Carlos followed by two other names.
Also your example works fine if you use a different delimiter for your format vs for your input, e.g.
https://gist.github.com/martinthenext/fc989ffa6ec84ee09962
That difference would change the kind of examples I would try it with, knowing in advance when they're too complex to work.
We often process messages with hundreds of fields. So I will have a class from specs (usually excel) with a number of properties, then I need a method to populate each of those from some other object, then I need a method that does the reverse - populates some other object from the properties.
This happens over and over for us. Typically I just use a Notepad++ macro. But I could probably use this as it stands, just having it in visual studio would be really incredible.
USA, Barack
Germany, Angela
to
USA, Obama
Germany, Merkel
based on a single example. Do this please. :)
Start by improving your proofreading :)
Same for Kagawa -- the Japanese use surnames in a different way, might be simpler to replace him.
11/1/2008, 12/1/2008, 6/1/2009
transformed to this:
November 1, 2008, November 1, 2008, November 1, 2009
Feature requests: allow for more than one example.
Right now the first line cannot have any ambiguity. This is fixable by reordering, but with large enough data sets I may have some ambiguity in all lines, at different places. Multiple examples would fix that.Again, loved the tool. I can see this going very far, specially with non-technical people.
[1]https://en.wikipedia.org/wiki/Lapis_(text_editor)
[2]http://groups.csail.mit.edu/uid/lapis/
But thanks for the suggestion, it might be useful.
I found that it doesn't like "=" in the transformation result.
Thanks!!!
For those of you who are wondering what sublime text can do, do give a visit to a sublime text video series on tutsplus, its awesome and teaches you the power of sublime text
But I wouldn't necessarily call emacs "great for non tech people".
(I suppose it's tiring for people to keep pointing out "yeah, emacs can do this too". Sorry.)
https://www.youtube.com/watch?v=UccfqwwOCoY
It's one of many missing features.
Excel is to finance people as terminals are to developers. When getting bug reports from end users, we've even had them delivered to us as screenshots embedded in Excel files.
But you are right that we should make it more clear that it's free. Thanks!
For example, if the rules were: example content must be contained within braces, and any braces within the example content need to be escaped, it's clear. At that point, your example becomes:
It would still probably just return "wwwexample.4, g/hijklmnop" for the last example though, because it's ambiguous as to whether you want just the end of the url, or the whole thing. Allowing regex markup for more explicit matching would make it clearer still, but your example still causes problems until you go all the way to positive lookbehind assertions. At that point I need to learn all that, I might as well just use perl:http://www.eecs.berkeley.edu/Pubs/TechRpts/2008/EECS-2008-17...
edit: see below
http://en.wikipedia.org/wiki/Programming_by_example
Sketching means to provide a partial specification, of which details are filled in by the system. But in this case, the user is providing a full description of a single concrete example. These are different concepts.
In sketching, the input and output can also includes partial programs (sketches), so you can mark what you like, tweak, etc, and it fills in the rest.
Increasing usability, the fragments can be in different languages. For example, input can be simple C and some test data, and output can be CUDA GPU code or pthread mutex locking schemes.
For Graphistry.com, we believe in these techniques for ETL and sketching visualizations.
(Also, sketching normally uses machine learning or SAT/SMT solvers: types are more typically used for input hints.)
By analogous, I meant more generally, using some partial knowledge about a program or spec to fill in missing pieces, not types specifically.
Nice to know folks are using these techniques to do real world tasks, I always thought something like this would be first used for cleaning data. Types, properties, sketches and examples.
Three levels of relatedness, https://vimeo.com/22606387