is there a way to automatically reassess data type?If I have a data table that I split into different columns, all columns come out with a "Character" data type as expected.
Now, is there a way we can write a JSL script to automatically reassess whether the data type should be numeric or character? If it finds that the data entry is numbers, change the data type to numeric?
Re: is there a way to automatically reassess data type?One approach would be to count the number of missing values, grab a list of the values, force the column to numeric, then count the number of missing values again. If the number of missing is the same before and after, you have a numeric column. If not, change it back and replace the values with the copy.
Where this would fail is when a non-numeric value, like NA, appears in a numeric column.
Names Default to Here(1);
tblRef = Current Data Table();
For(i=1,i<=N Col(tblRef),i++,
nMissing = Col N Missing(Column(tblRef,i));
nextVals = Column(tblRef,i) << Get Values;
Column(tblRef,i) << Set Data Type(Numeric);
If(Col N Missing(Column(tblRef,i)) != nMissing,
Column(tblRef,i) << Set Data Type(Character);
Column(tblRef,i) << Set Values(nextVals)
)
)
Re: is there a way to automatically reassess data type?Don beat me to a solution, but another option would be to use a regular expression to determine if each column contains only number and period (decimal point) characters. The example below takes each column, concatenates all of its values into one big string, and then checks if that string contains any characters that aren't a number or a decimal point ( [^0-9\.] ). If it finds only numbers and decimal points in the entire column (i.e., Contains Item evaluates to zero), it sets the data type to numeric.
Names Default to Here( 1 );
cols = Current Data Table() << Get Column Names();
for( i = 1, i <= Length( cols ), i++,
vals = As Column( cols[i] ) << Get Values;
valsConcat = Concat Items( vals, "" );
If( Contains Item( valsConcat, Pat Regex( "[^0-9\.]") ),
Continue(),
As Column( cols[i] ) << Set Data Type( "Numeric" );
)
);
Re: is there a way to automatically reassess data type?If you don't need an automated solution, there is a red triangle Parse as Numbers option in Recode. It changes any non-numeric entry (like NA) to a missing value.
Re: is there a way to automatically reassess data type?A potential problem with this approach is that it does not support scientific notation. Modifying the Regex to accept scientific notation and negatives, while rejecting things like phones and social security numbers, makes it significantly (hah! pun!) more complex.
Re: is there a way to automatically reassess data type?This is the approach I recommend. Depending on the data likely to be encountered, I also have done the following:
1) Use only the first k rows from each column to make the decision, to boost efficiency.
2) Allow some percentage (tune as needed) of missings in a given column (or its subset). Below this threshold, convert to numeric anyway.
Re: is there a way to automatically reassess data type?Along with the complications already mentioned, having the solution also consider commas as decimal place holder or as numerical separators for 1000's. That way it could be a more international solution as well.
Re: is there a way to automatically reassess data type?Good point (and pun), Brady. This regex solution works for the well-behaved example data but wouldn't generalize to all possible data. I imagine there's one master regex that could get the job done but could be a chore to come up with it. Alternatively, one could loop through each individual row in a column with one or more simpler Contains Item checks; for example, one could allow an initial dash (for negative numbers) but reject anything containing non-initial dashes, then another could check if the row conforms to scientific notation. If all checks pass for all rows in a column, then change the data type. As you say, "significantly" more complex.
Re: is there a way to automatically reassess data type?I'm a bit late to the party, but a "cheaty" way is to export the file as a csv, then re-import, where JMP will automatically determine column types upon opening. This assumes you don't need to save any table scripts or column properties, though. Here's a script.
//assume file is in the downloads folder
dt = open ("$DOWNLOADS/Data to Split.jmp");
dtsplit = Data Table( "Data to split" ) << Split(
Split By( :Label ),
Split( :Data ),
Output Table( "Split Data.jmp" ),
Sort by Column Property
);
close (dt, no save);
dtsplit << Save as ("$DESKTOP/Split Data.csv");
close (dtsplit, no save);
ndt = open ("$DESKTOP/Split Data.csv");
