10 ms·

OpenRefine – free, open source, powerful tool for working with messy data

235 points by joubert 7y ago

easygenes 7y ago
I have used and loved this since it was a project from MIT CSAIL SIMILE (circa 2006).
- easygenes 7y ago
  Follow up: Looking over the old SIMILE site I couldn't find the original project. Also David Huynh didn't mention it in his own website, but some searching yielded the original project, "Parallax" https://books.google.com/books?id=Y_FZPtpgntwC&pg=PA36 https://books.google.com/books?id=Y_FZPtpgntwC&pg=PA36 More from the era: https://blog.jonudell.net/2008/08/25/motivating-people-to-write-the-semantic-web-a-conversation-with-david-huynh-about-parallax/ https://blog.jonudell.net/2008/08/25/motivating-people-to-wr...
  - thadguidry 7y ago
    SIMILE library is used in OpenRefine for certain Faceting like timeline, clustering, etc. Parallax was originated by David to show how time series data visualizations could be enhanced. David was one of our original designers of OpenRefine and I worked closely with him and Stefano in testing it.
cstuder 7y ago
Formerely known as Google Refine. The history of the rename can be found in the blog: https://openrefine.org/blog/2013/10/12/openrefine-history.html https://openrefine.org/blog/2013/10/12/openrefine-history.ht...
- riedel 7y ago
  Formerly known as Freebase Gridworks as also mentioned in the article you linked
vcdimension 7y ago
You can do similar stuff using the visidata command line tool: https://www.visidata.org/ https://www.visidata.org/ You can use python code for more advanced data manipulations and creating plugins.
- rasmusei 7y ago
  Wow, that is a nice little tool. Just installed it and tested on some random files in my current data analysis project. By the way, I installed it using pipx https://github.com/pipxproject/pipx https://github.com/pipxproject/pipx by running `pipx install visidata`. To also read HDF and Excel files, I added the necessary packages by running `pipx inject visidata h5py openpyxl`.
  - thadguidry 7y ago
    Thanks @rasmusei! If you are a data scientist you might also be interested in how to work along with Jupyter. Our community has some documentation on our Wiki about that here: https://github.com/OpenRefine/OpenRefine/wiki/Jupyter https://github.com/OpenRefine/OpenRefine/wiki/Jupyter
Chris2048 7y ago
Anyone used this before? My own experience: I had a lot of data to process, which I thought was the use case for a tool like this: but it took a long time, and seemed to have to process the data in order to ingest it properly. What underlying storage/tech is used? Is it all just web-stack?
- larrydag 7y ago
  I've used it sparingly but recognize that I should be using it more. It is really powerful for cleaning up messy data. I'm not sure of the stack but I did reach out to one of the developers about being on HackerNews. Hopefully they can respond.
  - thadguidry 7y ago
    Thanks Larry for the ping. Happy to answer questions from the community here!
- febeling 7y ago
  yes, the size of the data you can process is limited. I had reasonable results with something like 50MiB, after tweaking jvm parameters. One limitation I find annoying is that you can't easily strip your transformation from the data and apply it to similar but different data. It's all together "a project." So incrementally transforming data works well, applying that transformation elsewhere not really. That bothered me more than the size limitation, which I think is not limiting most use cases with real “messy” data anyway. Maybe processing large volume event / log data would still need something else. I used it to look at a json export from a SaaS tool, and to convert it to table structure. Cleaning field contents, which where following certain conventions, but which evolved over time, things like that. For such use-cases it's powerful.
  - _frkl 7y ago
    > So incrementally transforming data works well, applying that transformation elsewhere not really. That bothered me more than the size limitation, which I think is not limiting most use cases with real “messy” data anyway. Maybe processing large volume event / log data would still need something else. Agreed, this would be the one feature I would really need. Would be nice to be able to setup (and refine over time) pipelines to automatically clean up new data.
chrisweekly 7y ago
See also https://lnav.org https://lnav.org
- aembleton 7y ago
  That is really interesting but I don't see what it has to do with OpenRefine. Thanks for the link though.
  - chrisweekly 7y ago
    Was "really interesting" sarcastic? If you found lnav to be interesting, I don't understand how you'd fail to see how it's relevant. lnav is a mini ETL tool, which, like OpenRefine, aids in transforming data from various formats to make it more useful. They're in the same space.
deleted 7y ago
[deleted]
canada_dry 7y ago
Reminded me a bit of the 'data wrangler' tool from Stanford. It was* a fantastic tool for dealing with messy data. http://vis.stanford.edu/wrangler/ http://vis.stanford.edu/wrangler/ *it's now a commercial product maintained by Trifacta (https://www.trifacta.com/start-wrangling/ https://www.trifacta.com/start-wrangling/)

235 points by joubert 7y ago

easygenes 7y ago

I have used and loved this since it was a project from MIT CSAIL SIMILE (circa 2006).

easygenes 7y ago
Follow up: Looking over the old SIMILE site I couldn't find the original project. Also David Huynh didn't mention it in his own website, but some searching yielded the original project, "Parallax" https://books.google.com/books?id=Y_FZPtpgntwC&pg=PA36 https://books.google.com/books?id=Y_FZPtpgntwC&pg=PA36 More from the era: https://blog.jonudell.net/2008/08/25/motivating-people-to-write-the-semantic-web-a-conversation-with-david-huynh-about-parallax/ https://blog.jonudell.net/2008/08/25/motivating-people-to-wr...
- thadguidry 7y ago
  SIMILE library is used in OpenRefine for certain Faceting like timeline, clustering, etc. Parallax was originated by David to show how time series data visualizations could be enhanced. David was one of our original designers of OpenRefine and I worked closely with him and Stefano in testing it.

cstuder 7y ago

Formerely known as Google Refine. The history of the rename can be found in the blog: https://openrefine.org/blog/2013/10/12/openrefine-history.html https://openrefine.org/blog/2013/10/12/openrefine-history.ht...

riedel 7y ago
Formerly known as Freebase Gridworks as also mentioned in the article you linked

vcdimension 7y ago

You can do similar stuff using the visidata command line tool: https://www.visidata.org/ https://www.visidata.org/ You can use python code for more advanced data manipulations and creating plugins.

rasmusei 7y ago
Wow, that is a nice little tool. Just installed it and tested on some random files in my current data analysis project. By the way, I installed it using pipx https://github.com/pipxproject/pipx https://github.com/pipxproject/pipx by running `pipx install visidata`. To also read HDF and Excel files, I added the necessary packages by running `pipx inject visidata h5py openpyxl`.
- thadguidry 7y ago
  Thanks @rasmusei! If you are a data scientist you might also be interested in how to work along with Jupyter. Our community has some documentation on our Wiki about that here: https://github.com/OpenRefine/OpenRefine/wiki/Jupyter https://github.com/OpenRefine/OpenRefine/wiki/Jupyter

Chris2048 7y ago

Anyone used this before? My own experience: I had a lot of data to process, which I thought was the use case for a tool like this: but it took a long time, and seemed to have to process the data in order to ingest it properly. What underlying storage/tech is used? Is it all just web-stack?

larrydag 7y ago
I've used it sparingly but recognize that I should be using it more. It is really powerful for cleaning up messy data. I'm not sure of the stack but I did reach out to one of the developers about being on HackerNews. Hopefully they can respond.
- thadguidry 7y ago
  Thanks Larry for the ping. Happy to answer questions from the community here!
febeling 7y ago
yes, the size of the data you can process is limited. I had reasonable results with something like 50MiB, after tweaking jvm parameters. One limitation I find annoying is that you can't easily strip your transformation from the data and apply it to similar but different data. It's all together "a project." So incrementally transforming data works well, applying that transformation elsewhere not really. That bothered me more than the size limitation, which I think is not limiting most use cases with real “messy” data anyway. Maybe processing large volume event / log data would still need something else. I used it to look at a json export from a SaaS tool, and to convert it to table structure. Cleaning field contents, which where following certain conventions, but which evolved over time, things like that. For such use-cases it's powerful.
- _frkl 7y ago
  > So incrementally transforming data works well, applying that transformation elsewhere not really. That bothered me more than the size limitation, which I think is not limiting most use cases with real “messy” data anyway. Maybe processing large volume event / log data would still need something else. Agreed, this would be the one feature I would really need. Would be nice to be able to setup (and refine over time) pipelines to automatically clean up new data.

chrisweekly 7y ago

See also https://lnav.org https://lnav.org

aembleton 7y ago
That is really interesting but I don't see what it has to do with OpenRefine. Thanks for the link though.
- chrisweekly 7y ago
  Was "really interesting" sarcastic? If you found lnav to be interesting, I don't understand how you'd fail to see how it's relevant. lnav is a mini ETL tool, which, like OpenRefine, aids in transforming data from various formats to make it more useful. They're in the same space.

deleted 7y ago

[deleted]

canada_dry 7y ago

Reminded me a bit of the 'data wrangler' tool from Stanford. It was* a fantastic tool for dealing with messy data. http://vis.stanford.edu/wrangler/ http://vis.stanford.edu/wrangler/ *it's now a commercial product maintained by Trifacta (https://www.trifacta.com/start-wrangling/ https://www.trifacta.com/start-wrangling/)