Tricks to prevent incorrect input date formats from resulting in missing values?When importing a data set with a date column so the column is numeric and continuous, if the input date format is incorrect those values that cannot be parsed are set to missing, thus you don't get a second chance at setting the input format. How do you typically handle this in an exploratory/graphical user interface analysis?
I sometimes
- duplicate the column before converting to numeric
- Create a new formula column to parse the date
To demonstrate the issue, run this code to create a table with some dates in m/d/y format, and then set that column to use d/m/y format, as shown in the screenshot below.
names default to here(1);
//Make a table with dates in m/d/y format
vals = index(today()-in days(100), today(), incr=in hours(1));
dt = New Table( "Example Data for Input Date Format",
Add Rows( n items(vals) ),
New Column( "Dates mdy",
Numeric,
"Nominal",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m" ),
Set Values( vals )
)
);
dt << run formulas;
wait(1);
dt:Dates mdy << data type("Character");
Now set all values at once in column info and note how some data is set to missing, even after changing the column back to character.
Data TableRe: Tricks to prevent incorrect input date formats from resulting in missing values?The resulting missing values from Char to Num is due to the Input Format.
Missing results start around row 113 with this "02/13/2021 12:23:50 AM". "d/m/y h:m" Input Format interprets it as the 13th month, which is invalid.
When there is a mistake in conversion, changing format back won't cut it, because the underlying values have been changed. The missing values no longer hold a valid time value. Use "Undo" under Edit instead.
Re: Tricks to prevent incorrect input date formats from resulting in missing values?Thank you @peng_liu,
Yes this perfectly describes the issue. Sometimes either the top few rows appear to parse correctly and the user doesn't think to scroll down to see that lower rows in the table are set to missing, or most values parsed correctly but a few did not due to a data entry issue. I am hoping to find a different way to help users parse dates so they are less likely to find out hours or days later that they have missing values.
Re: Tricks to prevent incorrect input date formats from resulting in missing values?I usually just create custom formulas to parse the dates.
One option that came into my mind, what if you would first change it to Numeric, Nominal with Best as format. I think this would leave it for JMP to determine the correct format and make it a numerical if it can. After making it numerical you could go back to Format settings and choose the desired formatting.
