Closed
Description
When loading my CSV or Excel file with a Date field Orange does not recognize or provide the ability to change the field type to DateTime. All date fields come in as Categorical data. I'm trying to do a time series prediction but without a the dates being recognized I'm unable to build out my prediction model. I've tried date in several different formats to no avail.
Please note that the format per Orange's documentation matches what is in my data set. YYYY-MM-DD is the required format and is one of the format's I've tried to get Orange to recognize. Please see documentation & screenshots below.
Metadata
Assignees
Labels
No labels
Activity
kernc commentedon Mar 26, 2018
The following minimal example file works for me: date-test.csv.zip (.xls or .xlsx work the same).
Can you attach a small example of your data?
tbuttle commentedon Mar 26, 2018
File Attached -
Orange isn't recognizing any of the date formats attached. I'm able to change the "Numeric" values to date/time but unable to do so when the dates register as "Categorical" values.
Date Sample.xlsx
kernc commentedon Mar 26, 2018
Right. The columns are heuristically marked as categorical due to only some 60 unique values for some 2.5k rows. The real issue here imho is that the widget doesn't allow switching from categorical to datetime.
You can force Orange to interpret a column as datetime by prefixing the name with "T#", e.g.
See also: http://orange-visual-programming.readthedocs.io/loading-your-data/index.html#header-with-attribute-type-information
ajdapretnar commentedon Mar 28, 2018
+1 on this. An issue was opened a while ago with similar concerns: #1520.
nemontemi commentedon Feb 21, 2019
It seems that the column containing the datetime data defaults to "categorical" (without possibility to change) if the column is non-unique, i.e., if there are duplicate datetimes within that column.
drgooo commentedon Mar 11, 2019
I had the same issue. The values didn't follow the required datetime format for Orange. Adding a small python script solved the issue. Might not be the cleanest but did the job for me.
It takes the input file, creates a new column and sets the value to the correct string format.
Looking at your table, you'll need lines of code to set your column 1, 3 and 7 into new columns. I included your first column in the domain definition as an example, but you'd need to add the rest if you wanted them in the final output
bhavin83012 commentedon Mar 14, 2019
Hello,
Software: Orange V 3.20
I loaded excel file, which include date column. I matched the date format to ISO. Orange file widget could not recognize the date and loaded as Categorical. On the data table, there are strange numbers loaded on Date column. I tried with different files and same is repeating. (Please see below screenshot.)

Then, I tried with T# to enforce the date format to the column. Orange did recognize that as date, but, this time the date range was completely different. The dates started in 1970-01-01 hh:mm:ss. Below screenshot.

It will be very helpful if someone can help me to get this issue fixed. I am not a programmer, so I can not see the solution in this matter.

Below is the screenshot of the file.
The file is attached here with.
date-test - Copy.xlsx
ajdapretnar commentedon Mar 15, 2019
@bhavin83012 This is not an issue of Orange, but an issue of Excel. Excel tries to be smart and once it recognizes this is a datetime variable, it reformats is behind the scenes. You need to set the number format to Text to force Excel not to mess with your data.
ajdapretnar commentedon Mar 19, 2019
This is not solved. File widget's domain editor should enable changing categorical to datetime if possible.
bhavin83012 commentedon Mar 19, 2019
Hi Ajda,
Your advise is useful - I worked out by below way.
The original excel files opened with Open Office software - (Say Libero Office). Then change the date format to ISO - Then you can use the file as it is or convert it to CSV and that should work in Orange.
Thank you for your support.
19 remaining items