Editing a cell value

[Marco Antonino]

Hi everyone,

I'm trying to edit a cell value with this expression

if(cells["Type"].value!="s",value="New String blah blah", value)

So I'm checking the value of a the cell in a different column (Type) and if it's different from "s" I want to change the value of the current cell in "New String blah blah".

The if control works just fine, but I'm not able to change the value inside the cell. I can only append some strings. So value+"New String blah blah" works.

Another thing that I'd like to do is to flaw the current row. I've tried to write set row.flagged = true, but whit no results.

Any help will be great,

ty

Marco

p.s sorry for my english

[John Little]

Hi Marco.

Is this what you want?  

if(cells["Type"].value != "s", "my special words", value)

--
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.

[Marco Antonino]

YES! 

Thank you!

Just another question: how I can add a flag on the current row? So the new expression should be something similar to

if(cells["Type"].value != "s", row.flagged = true, value)

Again,

thank you

Marco

[Ettore Rizza]

Ciao Marco, 

No need to use GREL formulas for this kind of thing. Open Refine has everything you need in its menus to perform this kind of action in a semi-manual way. 

In your case, go to the "Type" column and use "Text filter" to sort the cells that do NOT contain the term "s". You can use a regular expression for that (do not forget to check the corresponding box in text filter):

[^ The word or the expression you want to avoid]

Which means: anything but that.

Same thing for the starred of flagged rows : just facet by star or by flag and do what you want to do. Here is a visual demo where I play around with text filter or facet by star to isolate rows in one column and tranform everything into another one.

[John Little]

I don't know the GREL for that.  My approach would be to do that as a secondary action.  First facet on the "Type" column, make your selection, invert the selection, and then choose All > Edit rows > Star rows

For example:

a. Type > Facet > Text facet  (select "s" in the sidebar) > click "invert" in the header of the facet box 

b. All > Edit rows > Star rows 

c. Close the facet in the sidebar 

(Facet and select where the value 

[Ettore Rizza]

You can do a lot of things in Open Refine with GREL, or even with Python/Jython. But when it comes to filtering cells according to certain criteria, nothing like facets and filters. In this screenshot, I have isolated the flagged cells that a certain column contains the word "Anderlecht", another "Bizet", another  has as value "1000", "1004" or "1005" and one last is less than a certain number of characters. I don't even want to know what GREL formula should have been used to reproduce that.

[Marco Antonino]

Thank you so much Ettore and John!

This is exactly what I was looking for.

Thank you, again

Marco

[Marco Antonino]

Here I am again.

I'm trying to use the filter on "Type" column...but it doesn't work. I feel really stupid writing this kind of posts.

But I'm trying to filter by text, just to see if it works, and it doesn't.

Here's a picture

As you can see, even if I wrote the character "s" in the filter field, the "p" and the "v" rows still are there. I also clicked on refresh but with no result.

As usual, any help will be very appreciated.

Marco

[Owen Stephens]

Hi Marco,

In this picture OpenRefine is operating in Records mode - you can tell because although there are several rows, only the first one in numbered in the 'All' column. See http://kb.refinepro.com/2012/03/difference-between-record-and-row.html for a further explanation of Rows vs Records.

When in Records mode, the filter works on the Record (and all the rows in that record) rather than on the individual rows. So in this case Record '2' is retrieved because 's' appears in one of the rows within the larger record.

To just get the rows you want, force OpenRefine back into Rows mode by clicking 'rows' towards the top left of the data table where it says "Show as: rowsrecords"

Owen

[Ettore Rizza]

The "row versus record" logic is very powerful, but it is not immediately understood when one comes from Excel. 

[Ettore Rizza]

If you have a background in databases, you can also say that the record mode is a smart way to reproduce, in a single table, a one to many relationship.

[Marco Antonino]

Nice!

Thank you all for all the replies.

And thank you for answering so quickly.

[Thad Guidry]

Ettore,

That's exactly the reason we created Records mode.  (for one-to-many relationships)  I just noticed we don't even mention that on our Records section on the Wiki.  Can you add that verbiage ? Thanks !

-Thad

+ThadGuidry  

[Ettore Rizza]

@Thad : I'll do that. I'm wondering if we will ever have to go through all the documentation, putting it together in a single PDF, to more easily identify weaknesses or gaps. The other day I spent an hour writing a Jython formula that I thought was very clever, based on a Levensthein distance, to help me sort candidates from a reconciliation with Wikidata ... before I realized that there is already a facet "best candidate name similarity". She was before my eyes, and I could not see her, for I had never read anything about her.

[Thad Guidry]

I will slit both my wrists if we put it all in a single PDF.

Having said that... yeah, you can do that and generate once a week and host it and provide a link to that PDF version of our official Wiki docs on the Wiki home page.

If you cannot keep it up to date....then slit your wrists as well. :)

GENERATE a PDF with code.

https://softwarerecs.stackexchange.com/questions/36861/free-for-commercial-use-java-library-to-convert-html-to-pdf

-Thad

+ThadGuidry  

[Thad Guidry]

Ettore,

It probably would nice for someone to go through our code of commands and operations ... and get additional keywords like "levensthein" etc...and add them to those areas of our Wiki docs.

We've never fully finished documenting all the commands and operations... I.E.  what's available from menus, etc...and what they actually do.  +1 for adding that issue/task and getting the community to finish documenting those.

-Thad

+ThadGuidry