DateTransform_TransformFechas.txt

----------------------------------------------------------------------------
---Do not copy /No Copiar

--EN

SiB Colombia scripts for Biodiversity data validation and cleaning in OPEN REFINE

Script Name: Date format transformation ISO 8601
Language: GREL 'General Refine Expression Language'
Created:2018-03-21
Last Update:2021-02-16
Contributors: Camila Plata, Ricardo Ortiz 

This script:
1-Calls canadensys date Parsing API
2-Extracts ISO 8601 Date as text
3-Creates DwC date elements: year, month, day, and a verbatimEventDate

Conditions:
Dataset with DwC element 'eventDate'

New data will be stored in columns at the beginning of the dataset
Review the output for nulls, canadensys may not recognize all date formats

--ES

Rutinas del SiB Colombia para la validación y limpieza de datos primarios 
de Biodiversidad en OPEN REFINE

Nombre rutina: Transformación del formato de la fecha ISO 8601
Languaje: GREL 'General Refine Expression Language'
Creado:2018-03-21
Última Actualización:2019-06-07
Autores:Camila Plata, Ricardo Ortiz 

Esta rutina:
1-Llama al API de fechas de Canadensys 
2-Extrae la fecha ISO 8601 como texto
3-Crea los elementos DwC asociados a la fecha: year, month, day y la fecha original (verbatimEventDate).

Requerimientos:
Conjunto de datos con el elemento DwC 'eventDate'


Los nuevos datos seran guardados en columnas el inicio del conjunto de datos
No todas las fechas son convertidas exitosamente por el API, revise las celdas donde el resultado haya sido nulo

---Do not copy /No Copiar
----------------------------------------------------------------------------



[
  {
    "op": "core/column-addition-by-fetching-urls",
    "description": "Create column dateParsingCanadensysAPI at index 2 by fetching URLs based on column eventDate using expression grel:\"http://data.canadensys.net/tools/dates.json?data=\"+escape(cells[\"eventDate\"].value,\"url\")",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "dateParsingCanadensysAPI",
    "columnInsertIndex": 2,
    "baseColumnName": "eventDate",
    "urlExpression": "grel:\"http://data.canadensys.net/tools/dates.json?data=\"+escape(cells[\"eventDate\"].value,\"url\")",
    "onError": "set-to-blank",
    "delay": 250,
    "cacheResponses": true,
    "httpHeadersJson": [
      {
        "name": "authorization",
        "value": ""
      },
      {
        "name": "user-agent",
        "value": "OpenRefine 3.0 [TRUNK]"
      },
      {
        "name": "accept",
        "value": "*/*"
      }
    ]
  },
 
   {
    "op": "core/column-addition",
    "description": "Create column dayISO8601 at index 2 based on column dateParsingCanadensysAPI using expression grel:forEach(value.parseJson().get(\"data\").get(\"results\"),v,v.get(\"day\"))[0])",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "dayISO8601",
    "columnInsertIndex": 2,
    "baseColumnName": "dateParsingCanadensysAPI",
    "expression": "grel:forEach(value.parseJson().get(\"data\").get(\"results\"),v,v.get(\"day\"))[0])",
    "onError": "set-to-blank"
  },
  {
    "op": "core/text-transform",
    "description": "Text transform on cells in column dayISO8601 using expression value.toString()",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "columnName": "dayISO8601",
    "expression": "value.toString()",
    "onError": "keep-original",
    "repeat": false,
    "repeatCount": 10
  },
  {
    "op": "core/column-addition",
    "description": "Create column monthISO8601 at index 4 based on column dateParsingCanadensysAPI using expression grel:forEach(value.parseJson().get(\"data\").get(\"results\"),v,v.get(\"month\"))[0])",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "monthISO8601",
    "columnInsertIndex": 2,
    "baseColumnName": "dateParsingCanadensysAPI",
    "expression": "grel:forEach(value.parseJson().get(\"data\").get(\"results\"),v,v.get(\"month\"))[0])",
    "onError": "set-to-blank"
  },
  {
    "op": "core/text-transform",
    "description": "Text transform on cells in column monthISO8601 using expression value.toString()",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "columnName": "monthISO8601",
    "expression": "value.toString()",
    "onError": "keep-original",
    "repeat": false,
    "repeatCount": 10
  },
   {
    "op": "core/column-addition",
    "description": "Create column yearISO8601 at index 4 based on column dateParsingCanadensysAPI using expression grel:forEach(value.parseJson().get(\"data\").get(\"results\"),v,v.get(\"year\"))[0])",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "yearISO8601",
    "columnInsertIndex": 2,
    "baseColumnName": "dateParsingCanadensysAPI",
    "expression": "grel:forEach(value.parseJson().get(\"data\").get(\"results\"),v,v.get(\"year\"))[0])",
    "onError": "set-to-blank"
  },
  {
    "op": "core/text-transform",
    "description": "Text transform on cells in column yearISO8601 using expression value.toString()",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "columnName": "yearISO8601",
    "expression": "value.toString()",
    "onError": "keep-original",
    "repeat": false,
    "repeatCount": 10
  },
  {
    "op": "core/column-addition",
    "description": "Create column eventDateISO8601 at index 4 based on column dateParsingCanadensysAPI using expression grel:forEach(value.parseJson().get(\"data\").get(\"results\"),v,v.get(\"iso8601\"))[0])",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "eventDateISO8601",
    "columnInsertIndex": 2,
    "baseColumnName": "dateParsingCanadensysAPI",
    "expression": "grel:forEach(value.parseJson().get(\"data\").get(\"results\"),v,v.get(\"iso8601\"))[0])",
    "onError": "set-to-blank"
  },
  {
    "op": "core/column-rename",
    "description": "Rename column eventDate to verbatimEventDate",
    "oldColumnName": "eventDate",
    "newColumnName": "verbatimEventDate"
  },
  {
    "op": "core/column-move",
    "description": "Move column verbatimEventDate to position 2",
    "columnName": "verbatimEventDate",
    "index": 2
  }
  {
    "op": "core/text-transform",
    "description": "Text transform on cells in column monthISO8601 using expression grel:if(value.length() == 1, '0'+value, value)",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "columnName": "monthISO8601",
    "expression": "grel:if(value.length() == 1, '0'+value, value)",
    "onError": "keep-original",
    "repeat": false,
    "repeatCount": 10
  },
  {
    "op": "core/text-transform",
    "description": "Text transform on cells in column dayISO8601 using expression grel:if(value.length() == 1, '0'+value, value)",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "columnName": "dayISO8601",
    "expression": "grel:if(value.length() == 1, '0'+value, value)",
    "onError": "keep-original",
    "repeat": false,
    "repeatCount": 10
  },
  {
    "op": "core/column-removal",
    "description": "Remove column dateParsingCanadensysAPI",
    "columnName": "dateParsingCanadensysAPI"
  }    
]