This is a question that comes up regularly, and so it maybe helpful to take a look at this discussion thread from the old OpenRefine Google Group https://groups.google.com/g/openrefine/c/-loChQe4CNg/m/eroRAq9_BwAJ
If your question isn’t answered by that thread, please feel free to ask for more information by replying to this topic!
Newbie question here: I'm applying GREL functions I've copied from members of this group and I keep getting the message about too many choices to display. What are my options for changing all the rows in a given column, say, from all caps to title case?
Thanks.
Hi @Nancy_Sack
The "too many choices" warning is just a warning - you can increase the number (the warning should prompt you to increase it I think - if you click "ok", let me know if not). However if you make this very large you could see some slow performance (for reference, I currently have my maximum facet display set to 23,000, I wouldn't necessarily recommend going that high, but a few thousand should not be a problem)
However, to change all the rows in a given column from all caps to title case is relatively straightforward and doesn't require a facet. To apply a change to a column, you access the dropdown menu at the top of the column and choose Edit cells. In the sub-menu that displays you'll see an option that says Common transformations and then under that To titlecase. You can simply select that option to apply the change
I think you might find a tutorial like the Library Carpentry OpenRefine lesson a useful way to get started - you can work through this by yourself Library Carpentry: OpenRefine: Summary and Setup
The lesson on making "transformations", including the example of changing text to title case is in the 7th episode of the lesson Library Carpentry: OpenRefine: Introduction to Transformations
Hope this is helpful
Owen
PS I'm still writing an answer to your other question about subject headings but have been travelling and preparing for a presentation - I'm hoping I might be able to send you an answer this evening
Just checking that warning you mentioned - if you get a warning like this:
Click the "Set choice count limit" option and you'll see something like:
If the number in the box is large enough (it will always give a number bigger than the list you are currently trying to display by hopping up to the next round thousand), you can click "OK" and the limit will be increased and your facet list should display automatically
Many thanks, Owen, for your informative message. I did go through the excellent Library Carpentry tutorial. The reason I asked about changing all caps titles to title case is because I want to preserve the titles in "cataloger case" (with first word capitalized along with all proper nouns and adjectives).
Thanks in advance for your advice on splitting subject headings. I'm hoping to reconcile the resulting terms to FAST, if that's possible.
Best,
Nancy
Owen, I used your expression:
forEach(value.split(' '),v,if(isNonBlank(v.match(/([^a-z])/)[0]),toTitlecase(v.match(/([^a-z])/)[0]),v)).join(" ")strong text
And then clicked OK but nothing changed.
Hi @Nancy_Sack
I can't remember where I first posted this and what it was intended to do, but as it is written here it looks like this expression will look for any words that are written in ALL uppercase letters, and convert only those words to title case (i.e. so they just start with a capital letter). Words written in any other way will be left alone
forEach(value.split(' '),v,if(isNonBlank(v.match(/([^a-z]*)/)[0]),toTitlecase(v.match(/([^a-z]*)/)[0]),v)).join(" ")
So it would convert:
A CASE OF FAHR'S SYNDROME -> A Case Of Fahr's Syndrome
but convert
A case of Fahr's syndrome -> A case of Fahr's syndrome (i.e. do nothing)
I'm not sure if that's exactly what you are looking for from your description? The challenge of starting with a string like:
A CASE OF FAHR'S SYNDROME
Is that there is no way of knowing in advance there is a proper noun in this string (so we can tell looking at it that Fahr is a proper noun and so should start with a capital, but this is a harder task for a machine).
I think to more accurately covert titles to "cataloguer case" is a hard task because you can't rely on simple rules to know whether to capitalise certain words - in the above example there's no easy way to know that Fahr should be capitalised while case and syndrome should be lower case (and in the case of syndrome I had to look up to check if it would be Fahr's Syndrome or Fahr's syndrome - so not an easy task for a human either!
I suspect there are tools that can do this work and it's probably possible to integrate these with OpenRefine - but off the top of my head there's no easy way to do this. To give an example @michael_markert posted this use of the OpenAI API in another thread Using the OpenAI API to apply natural language queries to cells/data - that's not the specific answer in this case but I suspect that it could be adapted to do the work for you here - but it will require an OpenAI API account and possibly some payment for the service
I want to preserve the titles in "cataloger case" (with first word capitalized along with all proper nouns and adjectives).
Unfortunately, OpenRefine's toTitlecase operation doesn't do this. There's an enhancement request to improve it:
https://github.com/OpenRefine/OpenRefine/issues/2483
Tom
HI Owen, The thread dates back to 2017 and I'm wondering if it's still applicable or If I'm not searching correctly with the right question. I have a dowloaded a csv file (1.77 GB) that I know is more rows the excel limit of 1,048,576. I am new to OpenRefine & just starting but before I try using it with my new project I need to know the maximum or the limit of data sets it can handle since I know it will be more than 1,048,576 rows (about 24 columns). Please advise if possible. Thank you, Lisa
Hi Lisa. Welcome to the OpenRefine community. The internal architecture of the tool hasn't changed, but computers have gotten faster and come with more RAM, which is the key limiting factor. There is no hard limit for a maximum number of rows the way there is with Excel, so the biggest factor is how much memory you have available to allocate to OpenRefine (be sure to read about how to increase the default amount of memory). If 1.8 GB is the uncompressed size of the file, I'd make sure you have at least 4 GB and probably more like 8 GB to allocate to OpenRefine. There's a little meter shown when you're creating the project which will show you if you are getting close to running out of memory.
Good luck and let us know how things go.
Tom
Thanks so much, Tom! Will do.
The practical limit is usually more about available RAM than the raw number of rows. If a project is getting heavy, splitting the data into smaller batches and doing the expensive transformations separately can make OpenRefine much more responsive.
After Lisa's query, I did a little research into the benchmark, but never posted my notes, so here are a few interesting tidbits (mostly to help remind me in the future):
the benchmark dataset is 4 columns of synthetically generated strings containing names, email addresses, street addresses and cities
the total size is a little less than 80MB per million rows (ie not very big)
the timeout waiting for the operations to complete is 60 seconds (Ruby's default), so the operations aren't actually failing, but rather taking longer, in aggregate, than 60 seconds to complete
timing is only done for the full set of operations, not individual operations.
the operations create 3 new columns from the Name column using regex, then remove the old column.
increasing memory available to OpenRefine to 8GB and just looking at project creation without any additional operations allows it to load 5 million rows totalling 393 MB in 60 seconds (I was able to load 5.5M rows interactively in 1m45s using 3.5GB of heap, 5.5 GB committed, 7.5 GB max)
the last version of OpenRefine the benchmark works with is OpenRefine 3.2 (due to the CSRF protection that was added to the protocol)
the last version of Ruby it works with is Ruby 3.1 (due to the Ruby client, not the benchmarking code)
Also, I should point out that OpenRefine doesn't make effective use of multiple cores, so processing time for operations is typically linear in proportion to the number of rows in the project and will be (mostly) the same whether you have a single core processor or 40 core processor.
The reason that I recommend 2x-4x RAM vs data size is because of the large amount of internal overhead (~40 bytes per cell). This isn't terrible if you're dealing with strings that are hundreds of characters long, but if you're dealing with short strings, or worse, numbers, the overhead adds up quickly.
Some examples for various cell types:
There's also some per-row overhead, but it's much less significant typically.
I've done some investigation into column-oriented storage/processing using Apache Arrow which shows promise with 3x memory efficiency by basically eliminating all this overhead. This works by packing all the data values together and keeping the metadata separately. Early days, but shows promise...
