If you\u2019re using a computer that already has OpenRefine installed on it, you can skip Step One.<\/p>\n\n\n\n
Before you get started<\/strong>, download this file<\/a> somewhere onto your computer. It\u2019s a sample data file called NJShipwrecks.csv.<\/p>\n\n\n\n Head to www.openrefine.org\/download<\/a> and download OpenRefine 2.7 as you would any software. It\u2019s available for both Windows and Mac.<\/p>\n\n\n\n NOTE: If you\u2019re on a Mac and, when you try to open OpenRefine, you get a message saying that you can\u2019t open software from an unidentified developer, do the following: Go to System Preferences<\/strong>, then Security and Privacy<\/strong>. On the General<\/strong> tab, click the lock to make changes, and then click on Open Anyway. <\/strong>You should now be able open the software.<\/p>\n\n\n\n Double-click on the OpenRefine icon. It should open in your web browser. Occasionally, for whatever reason, OpenRefine doesn\u2019t launch when you double-click it. If this happens to you, enter localhost:3333<\/strong> in your browser\u2019s address bar and press return.<\/p>\n\n\n\n Click on Create Project<\/strong> and then Choose Files<\/strong>. Navigate to the NJShipwrecks.csv file and then click Next<\/strong>.<\/p>\n\n\n\n This is just a preview of the way your data will look when you\u2019re working with it in OpenRefine. You shouldn\u2019t have to make any changes; just click on Create Project<\/strong>.<\/p>\n\n\n\n This is the main interface you\u2019ll use to work with your data. It sort of looks like Excel, but notice it shows you only 10 records at a time. That\u2019s because you\u2019re not supposed to be working with your data record by record; you\u2019ll find ways to group it into batches and then work with it. We\u2019ll try that next.<\/p>\n\n\n\n In OpenRefine, a facet <\/strong>is a way to isolate certain records that share features. It\u2019s easier to see what I mean when you try it yourself. Click on the down-arrow right next to the VESSEL TYPE<\/strong> column heading. Then select Facet<\/strong>, and then Text Facet<\/strong>.<\/p>\n\n\n\n Look at the VESSEL TYPE list that appears on the lefthand side of the OpenRefine window. Can you tell what\u2019s going on there? OpenRefine\u2019s facet function has grouped together every term that appears in the VESSEL TYPE column, along with how many times it appears.<\/p>\n\n\n\n You can sort the list of terms alphabetically by name, or by count, according to how many times those terms appear on the list. If you click on one of the terms, only those rows that contain that term will be selected. This allows you to work on your data one chunk at a time.<\/p>\n\n\n\n Look closely at that list of terms. You\u2019ll see that it includes two terms that are probably meant to be the same: Bark steamer<\/strong> and Bark Steamer<\/strong>. Even though a human can tell they\u2019re meant to refer to the same thing, a computer doesn\u2019t know that. So it\u2019s important to clean up this data to create accurate visualizations and analyses.<\/p>\n\n\n\n Hover over the Bark Steamer<\/strong> term in the facet list, so that you can see the Edit <\/strong>option. Press Edit<\/strong>and, in the box that appears, change Bark Steamer<\/strong> to Bark steamer<\/strong> and press Apply<\/strong>. Now the two terms will merge into one.<\/p>\n\n\n\n Look again at the Facet<\/strong> box. You\u2019ll see a button marked Cluster. <\/strong>Click it.<\/p>\n\n\n\n The resulting box shows you terms that OpenRefine thinks should be merged together. Check the boxes of the terms you think should be merged and then click Merge Selected and Re-Cluster<\/strong>.<\/p>\n\n\n\n Now experiment with some of the other items on the Method<\/strong> dropdown menu. What happens when you try different methods? Each uses a different algorithm to try to match terms.<\/p>\n\n\n\n When you\u2019re finished experimenting, click Close<\/strong>. You\u2019ll notice you have fewer terms in your facet list.<\/p>\n\n\n\n A lot of the problems with the data in the VESSEL TYPE<\/strong> were the result of variant cases (e.g., Pilot schooner<\/strong> versus Pilot Schooner<\/strong>). One way to eliminate these problems would be to make all of the terms lowercase. Let\u2019s do that now.<\/p>\n\n\n\n Click on the down arrow next to VESSEL TYPE<\/strong>. From the dropdown menu, click Edit cells<\/strong>, and then Common transforms<\/strong>. Finally, select To lowercase<\/strong>. Voila! All the vessel types are now lowercase.<\/p>\n\n\n\n One common problem with data is extra spaces before and after the values. Those are easy to get rid of with OpenRefine. On the Year Built<\/strong> column, click the down arrow, then click Edit cells<\/strong>, then Common transforms<\/strong>. Finally, click Trim leading and trailing whitespace<\/strong>. Much better!<\/p>\n\n\n\n Several of our columns contain location, formatted as City, State. But let\u2019s say we want states to appear in their own column. That\u2019s easy to do with OpenRefine.<\/p>\n\n\n\n Scroll to the\u00a0Departure Point<\/strong>\u00a0column. Click the down arrow, then\u00a0Edit columns<\/strong>, and finally\u00a0Split into\u00a0multiple\u00a0columns<\/strong>. The popup window asks which separator currently separates the values. Enter a comma and a space, since those are the two characters that lie between city and state. Then click\u00a0OK<\/strong>.<\/p>\n\n\n\n You now have two columns! You can rename them by clicking on the down arrow, then Edit column<\/strong>and then Rename<\/strong>.<\/p>\n\n\n\n If you make a mistake in OpenRefine, no worries! It\u2019s easy to undo. Just click on the Undo\/Redo<\/strong> link on the lefthand side of the screen. Then click on the next-to-last step in the list. Your last action will be reversed. If you change your mind about redoing it, you can just click the last step.<\/p>\n\n\n\n Let\u2019s say we want to add the prefix S.S.<\/strong> to the name of any boat that has the vessel type schooner<\/strong>. We\u2019ll do that by first using our vessel type facet to select all the rows with the term schooner<\/strong> in the VESSEL TYPE<\/strong> column.<\/p>\n\n\n\n Once you have all of the schooners selected, head to the SHIP\u2019S NAME<\/strong> column. Click on the down arrow, then select Edit cells<\/strong>, and then Transform\u2026<\/strong><\/p>\n\n\n\n The popup box that follows wants you to use a language called the Google Refine Expression Language (GREL) to transform your data. You don\u2019t have to actually know GREL; you just have to be able to look up the pattern for the expression you want to write.<\/p>\n\n\n\n When you want to add a prefix to some data in OpenRefine, the pattern looks like this:<\/p>\n\n\n\n So in the blank text box, type<\/p>\n\n\n\n You\u2019ll see a preview of how your data will look in the lower right-hand column. When you\u2019re satisfied, press OK<\/strong>.<\/p>\n\n\n\n Now the title of every schooner is prefaced with \u201cS.S.\u201d!<\/p>\n\n\n\n Once you\u2019ve cleaned up your data, you\u2019ll want to get it out of OpenRefine. To do that, click on the Export<\/strong> button in the upper right-hand corner. Then click on Comma-separated value<\/strong>. Your cleaned-up spreadsheet should begin downloading. You can download your data as many times as you want, at any stage of the project.<\/p>\n\n\n\n To close OpenRefine, just close the window or tab in your browser.<\/p>\n\n\n\n Did you finish early? Nice job! Now’s a good time to look around the room. Do any of your classmates want a hand?<\/strong> Remember, we don’t succeed until everyone succeeds!<\/p>\n\n\n\n Now that you’re sure everyone’s in good shape, let’s try some more advanced OpenRefine functions.<\/p>\n\n\n\n Open the njshipwrecks.csv file in OpenRefine again. Can you figure out how to:<\/p>\n\n\n\n These are some of the most common tasks you\u2019ll want to perform in OpenRefine, but OpenRefine can also handle tasks of much greater complexity. To get a sense of some of these tasks, check out these resources:<\/p>\n\n\n\n \u201cIntroduction to OpenRefine,\u201d<\/a> developed by Owen Stephens on behalf of the British Library<\/p>\n\n\n\n \u201cCleaning Data with OpenRefine,\u201d<\/a> by Seth van Hooland, Ruben Verborgh and Max De Wilde<\/p>\n\n\n\n Verborgh, Ruben, and Max De Wilde. Using OpenRefine: The Essential OpenRefine Guide That Takes You From Data Analysis and Error Fixing to Linking Your Dataset to the Web<\/em>. <\/a>Birmingham, UK: Packt Publishing, 2013. (To get to the book, click on \u201cEBSCO eBooks.\u201d)<\/strong><\/p>\n\n\n\n I like OpenRefine, but if you prefer, you can use Excel to do some data-cleaning. Lynda offers several video tutorials on using Excel to clean data. Search for \u201cCleaning Up Your Excel 2013 Data.\u201d<\/p>\n\n\n\n Got the data bug? Want to try some programming? I recommend Automate the Boring Stuff with Python<\/a> <\/em>and \u201cCleaning OCR\u2019d Text with Regular Expressions.\u201d<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" DHers spend a TON of time cleaning and manipulating data. Luckily, there\u2019s a tool that makes all of this easier. It\u2019s called OpenRefine, and it\u2019s free! This tutorial will walk … <\/p>\n1. Install OpenRefine<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh201w19\/wp-content\/uploads\/sites\/12\/2019\/01\/Screen-Shot-2019-01-15-at-11.45.08-AM-1024x617.png\")
<\/figure>\n\n\n\n2. Open OpenRefine<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/open-openrefine.png\")
<\/a><\/figure><\/div>\n\n\n\n3. Open your data file<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/open-your-data-file.png\")
<\/a><\/figure><\/div>\n\n\n\n4. What the heck is this?<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/what-the-heck-is-this-.png\")
<\/a><\/figure><\/div>\n\n\n\n5. What the heck is this (part 2)?<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/what-the-heck-is-this-part-2-.png\")
<\/a><\/figure><\/div>\n\n\n\n6. Create a facet<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/create-a-facet.png\")
<\/a><\/figure><\/div>\n\n\n\n7. Understanding facets<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/understanding-facets.png\")
<\/a><\/figure><\/div>\n\n\n\n8. Clean up some data<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/clean-up-some-data-1.png\")
<\/a><\/figure><\/div>\n\n\n\n9. Another way to clean up some data<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/another-way-to-clean-up-some-data.png\")
<\/a><\/figure><\/div>\n\n\n\n10. Change the case of an entire column<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/change-the-case-of-an-entire-column.png\")
<\/a><\/figure><\/div>\n\n\n\n11. Get rid of extra whitespace<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/get-rid-of-extra-whitespace.png\")
<\/a><\/figure><\/div>\n\n\n\n12. Split multi-valued columns<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/split-multi-valued-columns.png\")
<\/a><\/figure><\/div>\n\n\n\n13. Undo an action<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/undo-an-action.png\")
<\/a><\/figure><\/div>\n\n\n\n14. Add characters to selected data<\/h2>\n\n\n\n
\u201cprefix\u201d+value<\/pre>\n\n\n\n
\u201cS.S. \u201c+value<\/pre>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/add-characters-to-selected-data.png\")
<\/a><\/figure><\/div>\n\n\n\n15. Export your data<\/h2>\n\n\n\n
![\"\"](\"http:\/\/miriamposner.com\/classes\/dh101f17\/wp-content\/uploads\/sites\/7\/2017\/10\/export-your-data.png\")
<\/a><\/figure><\/div>\n\n\n\n16. Look at you!<\/h2>\n\n\n\n
17. That\u2019s just the beginning!<\/h2>\n\n\n\n