Filtering and Sorting with OpenRefine
OverviewTeaching: 10 min
Exercises: 10 minQuestions
How can we select only a subset of our data to work with?
How can we sort our data?Objectives
Employ text filter or include/exclude to filter to a subset of rows.
Sort tables by a column.
Sort tables by multiple columns.
There are many entries in our data table. We can filter it to work on a subset of the data in the list for the next set of operations. Please ensure you perform this step to save time during the class.
- Click the down arrow next to
Text filter. A
scientificNamefacet will appear on the left margin.
- Type in
baiand press return. There are 48 matching rows of the original 35549 rows (and these rows are selected for the subsequent steps). Only a subset of the 48 rows appear.
- At the top, click on
Showsection. This way you will see all the matching rows.
- What scientific names (genus and species) are selected by this procedure?
- How would you restrict this to one of the species selected?
Text faceton the
scientificNamecolumn after filtering. This will show that two names match your filter criteria. They are
- To restrict to only one of these two species, you could:
- Check the
case sensitivebox within the
scientificNamefacet. Once you do this, you will see that using the upper-case
Baiwill only > > return
Baiomys taylori, while using lower-case
baiwill only return
- You could include more letters in your filter (i.e. typing
baiowill exclusively return
Baiomys taylori, while
bailwill only return
In addition to the solutions included above, another way to narrow our filter is to
exclude entries in a facet. If you still have your facet for
scientificName, you can use it, or use drop-down menu >
Text facet to create a new facet. Only the entries with names that agree with your
Text filter will be included in this facet.
Faceting and filtering look very similar. A good distinction is that faceting gives you an overview description of all the data that is currently selected, while filtering allows you to select a subset of your data for analysis.
include / excludeto select only entries from one of these two species.
- In the facet (left margin), click on one of the names, such as
Baiomys taylori. Notice that when you click on the name, or hover over it, there are entries to the right for
include. This will explicitly include this species, and exclude others that are not expicitly included. Notice that the option now changes to
excludeon the other species (
Chaetodipus baileyi) and notice how the two entries appear and disappear from the table.
Important: Select both species for your filtered dataset before continuing with the rest of the exercises.
You can sort the data in a column by using the drop-down menu available in that column.
There you can sort by
FALSE values). You can also specify what order to put
Errors in the sorted results.
If this is your first time sorting this table, then the drop-down menu for the selected column shows
Sort.... Select what you would like to sort by (such as
numbers). Additional options will then appear for you to fine-tune your sorting.
Sort by month. How can you ensure that months are in order?
smallest first. The months are listed from 1 (for January) through 12 (December).
If you try to re-sort a column that you have already used, the drop-down menu changes slightly, to >
Sort without the
..., to remind you that you have already used this column. It will give you additional options:
Sort...- This option enables you to modify your original sort.
Reverse- This option allows you to reverse the order of the sort.
Remove sort- This option allows you to undo your sort.
Sort the data by
plot. What year(s) were observations recorded for plot 1 in this filtered dataset?
smallest first. The years represented are between 1989 and 1995.
Sorting by multiple columns
You can sort by multiple columns by performing sort on additional columns. The sort will depend on the order in which you select columns to sort. To restart the sorting process with a particular column, check the
sort by this column alone box in the
Sort pop-up menu.
You might like to look for trends in your data by month of collection across years.
- How do you sort your data by month?
- How would you do this differently if you were instead trying to see all of your entries in chronological order?
- For the
mocolumn, click on
numbers. This will group all entries made in, for example, January, together, regardless of the year that entry was collected.
- For the
yrcolumn, click on
sort by this column alone. This will undo the sorting by month step. Once you’ve sorted by
yryou can then apply another sorting step to sort by month within year. To do this for the
mocolumn, click on
numbersbut do not select
sort by this column alone. To ensure that all entries are shown chronologically, you will need to also sort by days within each month. Click on the
numbers. Your data should now be in chronological order.
If you go back to one of the already sorted columns and select >
Remove sort, that column is removed from your multiple sort. If it is the only column sorted, then data reverts to its original order.
dayin some order. Be creative: try sorting as
text, and in reverse order (
largest to smallestor
z to a).
Remove sortto remove the sort on the second of three columns. Notice how that changes the order.
OpenRefine provides a way to sort and filter data without affecting the raw data.