Book Image

Using OpenRefine

Book Image

Using OpenRefine

Overview of this book

Data today is like gold - but how can you manage your most valuable assets? Managing large datasets used to be a task for specialists, but the game has changed - data analysis is an open playing field. Messy data is now in your hands! With OpenRefine the task is a little easier, as it provides you with the necessary tools for cleaning and presenting even the most complex data. Once it's clean, that's when you can start finding value. Using OpenRefine takes you on a practical and actionable through this popular data transformation tool. Packed with cookbook style recipes that will help you properly get to grips with data, this book is an accessible tutorial for anyone that wants to maximize the value of their data. This book will teach you all the necessary skills to handle any large dataset and to turn it into high-quality data for the Web. After you learn how to analyze data and spot issues, we'll see how we can solve them to obtain a clean dataset. Messy and inconsistent data is recovered through advanced techniques such as automated clustering. We'll then show extract links from keyword and full-text fields using reconciliation and named-entity extraction. Using OpenRefine is more than a manual: it's a guide stuffed with tips and tricks to get the best out of your data.
Table of Contents (13 chapters)
Using OpenRefine
About the Authors
About the Reviewers

Recipe 6 – exporting a project

In this recipe, you will explore the various ways to save your modified data in order to reuse them in other contexts, including templating that allows for any custom export format to be used.

Although you may already have moved, renamed, or even removed columns, none of these modifications have been saved to your original dataset (that is, the chapter1.tsv file from Recipe 1 – installing OpenRefine has been left untouched). In fact, unlike most spreadsheet softwares that directly record changes into the files opened with them, OpenRefine always works in memory on an internal copy of the data. While this is an extra safety catch, it also means that any modified data needs to be exported before they are shared with others or injected in another application. The Export menu in the top-right of the screen allows you to do just that:

Most options propose to convert the data back into the file formats that were used during importation, such as CSV and TSV, Excel and Open Document, and different flavors of RDF. Let's have a closer look at other choices though:

  • Export project: This option allows you to export a zipped OpenRefine project in its internal format that can be shared with other people and imported on other machines or simply used for backup purposes.

  • HTML table: This option comes in handy if you want to publish your cleaned data online.

  • Triple loader and MQLWrite: This option has advanced options that require you to align the data to pre-existent schemas through the Freebase extension (there is more about that in Appendix, Regular Expressions and GREL).

  • Custom tabular exporter and templating: Maybe most interesting to you, OpenRefine lets you have a tight control on how your data are effectively exported by selecting and ordering columns, omitting blank rows and choosing the precise format of dates and reconciliation results (see Appendix, Regular Expressions and GREL again), and so on, as you can see in the next screenshot:

  • Templating...: For even more control, you can use your own personal template by typing the desired format once, which will then be applied to all cells. In the following code, cells["Record ID"].value, for instance, corresponds to the actual value of each cell in the Record ID column which is then transformed into JSON, but could just as easily be formatted otherwise as shown in the following code snippet:

      "Record ID" : {{jsonize(cells["Record ID"].value)}},
      "Object Title" : {{jsonize(cells["Object Title"].value)}},
      "Registration Number" : {{jsonize(cells["Registration Number"].value)}},
      "Description. " : {{jsonize(cells["Description. "].value)}},
      "Marks" : {{jsonize(cells["Marks"].value)}},
      "Production Date" : {{jsonize(cells["Production Date"].value)}},