{"id":3274,"date":"2018-05-23T17:11:49","date_gmt":"2018-05-23T17:11:49","guid":{"rendered":"https:\/\/max-drake.cc\/?p=3274"},"modified":"2018-05-23T17:17:21","modified_gmt":"2018-05-23T17:17:21","slug":"open-refine-a-free-open-source-powerful-tool-for-working-with-messy-data","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=3274","title":{"rendered":"Open Refine. A free, open source, powerful tool for working with messy data"},"content":{"rendered":"

OpenRefine (formerly Google Refine) is a powerful tool for working with messy data: cleaning it; transforming it from one format into another; and extending it with web services and external data. You can download it from here<\/strong><\/a>.<\/p>\n

In the Cognitive Class tutorial on OpenRefine it talks about 60-85% of time of data scientists spending on cleaning the data. So this tool could be very useful.<\/p>\n

There are 3 videos worth watching on the Home page<\/a>. It shows it to be a pretty powerful tool. I was pretty impressed by its capabilities.<\/p>\n

On my first brief tinker I did not find it intuitive and will need to do some tutorials to see how you need to programme it. There seem to be a number of tutorials HERE<\/a>.<\/p>\n

In some ways it is a bit like the Power BI setup with it remembering the steps you used to filter your data, so you can go backwards through the steps if you have made errors.<\/p>\n

\"\"<\/p>\n

In one of the videos it shows a demonstration of how it setup filters for an “Actresses” Table, then used the process to replicate all the steps on an “Actors” table that was in the same format.<\/p>\n

 <\/p>\n

Process<\/h3>\n

I am importing a 3D PDF export to CSV file. I want to filter out Room Data that I can put in a table to show Room information. So this is a data cleaning exercise.<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

The imput from CSV gets a bit garbled, there are 2 lines of data on one line. So I tried saving the file as an XLS file and importing it. See the results below. It comes in correctly<\/p>\n

\"\"<\/p>\n

 <\/p>\n

\"\"<\/p>\n

Note also , in image above I took away the header by changing Line(s) of Column header from 1 to 0.<\/p>\n

I have since found, that I can bring this file in correctly in CSV format by using custom import (red arrow on left at bottom).<\/p>\n

This is the preview window, you next need to create the project.<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

Once the project created, it is then ready to “Refine”.<\/p>\n

\"\"<\/p>\n

Filter<\/h3>\n

I want to filter “Column 1”  by 3D Room Tags , so use the text filter on pull-down.<\/p>\n

\"\"<\/p>\n

Using just 3d, we get the some data not actualy related to Rooms, this is the 3D_RoomTag(56), which we don’t want, so we want to filter that out too.<\/p>\n

\"\"<\/p>\n

So use 3DRm in filter and only get room data.<\/p>\n

\"\"<\/p>\n

Other resources<\/h3>\n

The General Refine Expression Language( GREP) information can be found here<\/strong><\/a>.  <\/p>\n