Only Year Please

Support Data cleaning and transformations
1

Hi,

Kindly requesting how to convert numerical value (Example: '1850') into Date value with only a four digit Year? No months or days required.

Everytime I convert to date, OpenRefine automatically adds '1850-01-01', but I only require year for Wikidata.

2

We have some great documentation on this starting here, take a look! :
Exploring data - Dates | OpenRefine
and
GREL functions - Dates | OpenRefine

In particular, we have .datePart("years")
If you have standardized ISO date formats already in your source data and don't need fancy parsing within the toDate() function, then you can try just this in GREL:

value.toDate().datePart("years")

if you already transformed your column and the values are already Date data type, then just do:

value.datePart("years")

3

Thank you for the swift reply and the resources provided.

value.datePart("years") is working perfectly for already changed date values.

Is there a way to convert a column with numerical values (Example: '1850') to Date Value?

None of the options in the resources or your examples seem to work? Would I need to manually convert each cell?

4

Did you have problems if you tried the GREL function:
value.toDate()

or generally using the Column menu dropdown to try the Common transform to change the data type to Date? Cell editing | OpenRefine

5

Hi,

It only seems to do this for the first value and not the rest. Do I need to select the others in some way?

6

  1. Did you use the Column menu operation? Edit Cells -> Common Transform -> to Date on your column?

  2. Or did you instead use the Edit button on a Cell?

(see my issue with some screenshots which is not for your case, but can be used as a guide to understand what I am talking about
Using Cell Edit button to change String to Date data type gives Uncaught RangeError · Issue #6534 · OpenRefine/OpenRefine (github.com)
)

A screenshot from you would certainly help us to understand better.
You can copy/paste an image in your reply, or use the Upload button.

7

I used Edit Cells -> Common Transform -> to Date on your column, but only the first cell is changing?

Only changed the first cell;

image

I have tried refreshing, closing and opening again. Any advise?

8

image

9

Sorry, I had assumed based on your original issue that you had a column that already had contained dates in a standard String format with yyyy-MM-dd. But now with your screenshot I see you actually have 4 digit Numbers (Integers)! Screenshots are so helpful!

To parse Strings into Dates, you can use the GREL .toDate() function as explained before at this link GREL functions | OpenRefine <-- What parts in it did not make sense to you? We can improve the docs with your feedback.

OpenRefine Date functions work on Dates or Strings. They do not work on Numbers.
That's probably something we should say directly in the first paragraph in our Docs. So I'll fix our Docs on that later.

So, in your case, you first need to undo the conversion of your String to Numbers and make them Strings again:
(you can use the Undo/Redo to select and click on an earlier step that converted them, or)

  1. use the Cell Edit -> Common Transforms -> to Text
  2. then use the Cell Edit -> Common Transforms -> to Date
10

It worked perfectly!

In the GREL functions | OpenRefine I did not realise that it does that for strings.

Was worried that I would have to manually change each row.

Thanks for the patience and step-by-step instructions.

1 Like
11

OpenRefine Date functions work on Dates or Strings. They do not work on Numbers.

The toDate() function is intended to treat integers as years, so if that's not working, it's a bug.

Tom