This will open up a window into which you can type a GREL expression. GREL stands for General Refine Expression Language.
Remove all of the left square brackets (
[). In the Expression box type
value.replace("[", "") and click
OK. (What the expression means is this: Take the
value in each cell in the selected column and replace all of the “[” with “” (i.e. nothing - delete).
OK. You should see in the
items_ownedcolumn that there are no longer any left square brackets.
'), the right square brackets (
]), and spaces from the
Create a text facet to see which items were commonly owned or rarely owned by the interview respondents.
Custom text facet...
months_lack_foodcolumn. Which month(s) were farmers more likely to lack food?
Hint: To reuse a GREL command, click the
History tab and then click
Reuse next to the command you would like to apply to that column.
Optional: If you are on a roll, clean up
no_food_mitigation columns. Post your solution in the chat, we will see who figures out first which months is dryest, which livestock is most commonly owned and what most people do if they run out of food.
Full documentation for GREL is available here.
Undo / Redoon the left side of the screen. All the changes you have made so far are listed here.
Redothe actions. Before moving on to the next lesson, redo all the steps in your analysis so that all of the column you modified are lacking in square brackets, spaces, and single quotes.
villageon the first row to introduce a space at the end, set to
villagecolumn . You should now see two different entries for
God, one of those has a trailing whitespace.
Trim leading and trailing whitespace.
Text filter. A
respondent_roof_typefacet will appear on the left margin.
mabatand press return. There are 58 matching rows of the original 131 rows (and these rows are selected for the subsequent steps).
rows. This way you will see most of the matching rows.
FALSEvalues). You can also specify what order to put
Errorsin the sorted results.
gps_Altitude. Do you think the first few entries may have incorrect altitudes?
villageentries was given as 49. This is clearly wrong. By looking at the GPS coordinates for the entries of the other villages can we decide what village the data in that column was collected from?
gps_Latitudeas a number with the smallest first.
gps_Longitudeas a number with the smallest first.
Move column to end. This will allow you to compare village names with GPS coordinates.
49. Can you tell from it’s GPS coordinates which village it belong to?
interview_dateas date. Move the
villagecolumn to the start of the table. Does the row where village is
49group with one particular village? Is it the same village as when comparing GPS coordinates?
villagecolumn and change
49to the correct village name.
Transform cells to numbers:
Text filterfacets you have enabled. You can remove an existing facet by clicking the
xin the upper left of that facet window
years_farmcolumn to numbers by clicking the down arrow for that column, then
To number. You will notice the
years_farmvalues change from left-justified to right-justified, and black to green in color.
buildings_in_compound, from text to numbers. Can all columns be transformed to numbers? - Try it with
abc) or blank (no number or text). You will need to change the
textusing the drop-down menu.
Error. Below these are counts of the number of cells in each category. You should see checks for
Blankif you changed some values.
Undo / Redosection, click
Extract..., and select the steps that you want to apply to other datasets by clicking the check boxes.
Make plain textand save the file as a
Undo / Redotab >
Applyand paste in the contents of
.txtfile with the JSON code.
Perform operations. The dataset should now be the same as your other cleaned dataset.
Exportbutton in the top right and select
OpenRefine project archive to file.
tar.gzfile will download to your default
Downloaddirectory. Depending on your browser you may have to confirm that you want to save the file. The
tar.gzextension tells you that this is a compressed file. The downloaded
tar.gzfile is actually a folder of files that have been compressed. Linux and Mac machines will have the software installed to automatically expand this type of file when you double-click on it. Windows-based machines might need a utility like ‘7-zip’ in order to expand the file and see the files in the folder ( but it is not necessary to do this, OpenRefine will be able to handle this data regardless).
Exportin the top right and select the file type you want to export the data in.
csv) would be good choices.
Downloaddirectory. That file can then be opened in a spreadsheet program or imported into programs like R or Python.
These exercises are based on Data Carpentry lessons.