replace all cells containing only a dash with null value
584 views
Skip to first unread message
Jonathon Paarlberg
Sep 2, 2015, 5:58:07 PM9/2/15
to OpenRefine
I have a situation where many cells contain only dashes when they should contain null values and display as empty instead.
I know I could facet on them and then convert all values to null, but I don't think I can automate a facet-based transformation; as I need to automate the process, I want to convert by using an expression-based transformation on the whole column. A simple value.replace("-","") will work in many cases but not in those rare cases where a dash is part of the string contained in a cell.
How do I write a regex that will work only on cells that contain the dash character in isolation? Or, should I use an IF statement instead?
Something like: if(value="-","",value)
But that doesn't seem to work. 'Can't remember how to properly phrase an if statement.
I know I could facet on them and then convert all values to null, but I don't think I can automate a facet-based transformation; as I need to automate the process, I want to convert by using an expression-based transformation on the whole column. A simple value.replace("-","") will work in many cases but not in those rare cases where a dash is part of the string contained in a cell.
How do I write a regex that will work only on cells that contain the dash character in isolation? Or, should I use an IF statement instead?
Something like: if(value="-","",value)
But that doesn't seem to work. 'Can't remember how to properly phrase an if statement.
Jonathon Paarlberg
Sep 2, 2015, 6:09:58 PM9/2/15
to OpenRefine
O.k., this seems to work:
if(value=="-","",value)
Can I be sure that the empty string is interpreted as null? I'll run a facet and see.
if(value=="-","",value)
Can I be sure that the empty string is interpreted as null? I'll run a facet and see.
Jonathon Paarlberg
Sep 2, 2015, 6:11:52 PM9/2/15
to OpenRefine
Ahh. The empty string is not interpreted as a null (empty) but rather as a blank.
On Wednesday, September 2, 2015 at 5:58:07 PM UTC-4, Jonathon Paarlberg wrote:
Martin Magdinier
Sep 2, 2015, 9:00:07 PM9/2/15
to openrefine
I personally use the facet for when replacing the full content of a cell. Just click edit in the facet and enter your new value. Refine will do a search and replace in the column.
Martin
--
You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Jonathon Paarlberg
Sep 4, 2015, 10:57:14 AM9/4/15
to OpenRefine
Yeah, and it works great. However, I'm not sure I can automate that.
On Wednesday, September 2, 2015 at 5:58:07 PM UTC-4, Jonathon Paarlberg wrote:
Martin Magdinier
Sep 4, 2015, 12:38:40 PM9/4/15
to openr...@googlegroups.com
what do you mean by automate?
Any changes done via the facet are recorded in the history and can be exported and reapply on an other project
Martin
Any changes done via the facet are recorded in the history and can be exported and reapply on an other project
Martin
--
Jonathon Paarlberg
Sep 4, 2015, 4:29:15 PM9/4/15
to OpenRefine
Oh, that was news to me. Good news! Thank you.
On Wednesday, September 2, 2015 at 5:58:07 PM UTC-4, Jonathon Paarlberg wrote:
Reply all
Reply to author
Forward
0 new messages