Add a variable to refer to other rows

[allanaaa]

I was wondering if there was a way to compare two rows' values to each other. For example, I wanted to create a column using diff() on a column of dates, outputting the distance between row1's date and row2's date (and subsequently between row2's date and row3's date, etc.).

Dates	Diff between value and value in the next row (days)
2020-10-31	2
2020-10-29	5
2020-10-24	3
2020-10-21

There doesn't seem to be a way to call "the value of this cell in [this row's index plus one]". You can call other cells in the same row (by pointing to a specific column, with syntax like cells["Dates"].value), but not other cells in the same column (by pointing to a specific row with rowIndex).

Another use case: you could transform your data by transferring all the values in a column up by one or more cells.

Proposed solution

What I would like is a way to pull from any row's cell values by its index number. Both absolutely and relatively - as in, "row 6" as well as "this row minus two". I am terrible at syntax so I'll leave that bit to someone else.

It may also be useful to have a variable for the total number of rows/records in the project. (Which we offer at a smaller scale with row.record.rowCount.)

[wetneb]

Similar to #1340

[iomicifikko]

@allanaaa Hi there, I'm using the following workaround to compare values between a row and its following:

1. create a column row with the code row.index() (ex: 0,1,2...)
2. create a column nextRow with the code row.index() + 1 (ex: 1,2,3...)
3. use the cross function within the same project to create a new column (ex: startDate_nextRow) from the column row and import values from row2 into row1 (ex: cell.cross("sameProject", "nextRow")[0].cells["StartDate"].value
4. compare the imported values (ex: endDate VS startDate_nextRow)

[wetneb]

I think there is now a standard syntax using the cross function, because when the project name and column name are omitted, they are understood as being the current project and the row index. So if you want to access the cell at row 123 in column Address, you can simply do cell.cross(123).cells["Address"].value[0] (I haven't checket it though).

I suggest we change this issue into a documentation issue (on the openrefine.org repo), where the task would be to document this workaround in https://openrefine.org/docs/manual/expressions

[antoine2711]

So if you want to access the cell at row 123 in column Address, you can simply do cell.cross(123).cells["Address"].value[0] (I haven't checket it though).

I confirm this works with a slite change:
cross(123).cells["Address"][0].value (no cell. at the start and the [0] can go after the cell column, or after the value).

It's the same as: "123".cross().cells.Address[0].value.

Regards, Antoine

[wetneb]

ah yes, simply cross(123).cells["Address"].value[0] (no cell. in front).

[ostephens]

Hah - this is excellent - I had not realised this and have been abusing the record mode to achieve similar things

[thadguidry]

@wetneb @ostephens So I think this issue can be closed now and a new doc improvement issue can be made? It would be "swell" if @ostephens took that up for us, since I feel he might explain it very well for others.

[wetneb]

Agreed, I have opened an issue accordingly: OpenRefine/openrefine.org#214