Shift Cells Up

[Mauricio Salazar]

Hi there everybody I wonder if you can help with these. I got two columns and I want to Shift the Cells Up one row for one column. For instance:

AAA null
BBB 1111
CCC 2222
....

and I want

AAA 1111
BBB 2222
CCC

I wonder if is with the Cells command.

Best Regards

Mauricio

[Tom Morris]

Hi Mauricio.  We probably need to back up a bit in the process to give you the best answer, but the simple answer is that there's no way to do that.  There is a "Fill Down" command, so the most obvious solution would be to try and bring that into play somehow, but I suspect that if we back up further in the pipeline, there might be a better answer.  In other words, what is the source of this row skew in that column?

Tom

Mauricio

--
You received this message because you are subscribed to the Google Groups "Open Refine" 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/groups/opt_out.

[Herwig Van Marck]

The easiest way to do this is to create a dummy column (based on whatever column you like) with the expression 

if(row.index==0,1,null)

I call this column 'tmp'. Then move this column to the beginning so it becomes the index column. Then transform the column you want to shift with the expression

if(isBlank(value),"<empty>",value)

to ensure you don't loose any blank values further in the column. Then use the 'Join multi-valued cells...' command to combine all your data in the first row using a delimiter that is not present in your data (for example I will use the pipe symbol '|'). Then transform the column again with the expression

value.split("|").slice(1).join("|")

Then split the column using the 'Split multi-valued cells...' command (using thepipe symbol again). And finally remove the temporary column again. If your columns are called 'Col 1' and 'Col 2', this leads to the following JSON code (you can apply using the 'Apply...'in the 'Undo/redo' tab:

[

  {

    "op": "core/column-addition",

    "description": "Create column tmp at index 1 based on column Col 1 using expression grel:if(row.index==0,1,null)",

    "engineConfig": {

      "facets": [],

      "mode": "row-based"

    },

    "newColumnName": "tmp",

    "columnInsertIndex": 1,

    "baseColumnName": "Col 1",

    "expression": "grel:if(row.index==0,1,null)",

    "onError": "set-to-blank"

  },

  {

    "op": "core/column-move",

    "description": "Move column tmp to position 0",

    "columnName": "tmp",

    "index": 0

  },

  {

    "op": "core/mass-edit",

    "description": "Mass edit cells in column Col 2",

    "engineConfig": {

      "facets": [],

      "mode": "row-based"

    },

    "columnName": "Col 2",

    "expression": "value",

    "edits": [

      {

        "fromBlank": false,

        "fromError": false,

        "from": [

          "2222"

        ],

        "to": ""

      }

    ]

  },

  {

    "op": "core/text-transform",

    "description": "Text transform on cells in column Col 2 using expression grel:if(isBlank(value),\"<empty>\",value)",

    "engineConfig": {

      "facets": [],

      "mode": "row-based"

    },

    "columnName": "Col 2",

    "expression": "grel:if(isBlank(value),\"<empty>\",value)",

    "onError": "keep-original",

    "repeat": false,

    "repeatCount": 10

  },

  {

    "op": "core/multivalued-cell-join",

    "description": "Join multi-valued cells in column Col 2",

    "columnName": "Col 2",

    "keyColumnName": "tmp",

    "separator": "|"

  },

  {

    "op": "core/text-transform",

    "description": "Text transform on cells in column Col 2 using expression grel:value.split(\"|\").slice(1).join(\"|\")",

    "engineConfig": {

      "facets": [],

      "mode": "row-based"

    },

    "columnName": "Col 2",

    "expression": "grel:value.split(\"|\").slice(1).join(\"|\")",

    "onError": "keep-original",

    "repeat": false,

    "repeatCount": 10

  },

  {

    "op": "core/multivalued-cell-split",

    "description": "Split multi-valued cells in column Col 2",

    "columnName": "Col 2",

    "keyColumnName": "tmp",

    "separator": "|",

    "mode": "plain"

  },

  {

    "op": "core/column-removal",

    "description": "Remove column tmp",

    "columnName": "tmp"

  }

]

Cheers,

Herwig

[Herwig Van Marck]

I forgot one more step: transform the column again with the expression

if(value=="<empty>",null,value)

Note that all blanks will be made 'null'. If you want to distinguish between 'null' and the empty string you need to change the expressions slightly.