Subscribe Bookmark RSS Feed

V12 import treatment of missing data

Martin

Community Trekker

Joined:

Aug 13, 2013

In V12, when I have some missing data within a text file column, it is treated as Character Data.  In V11 it was treated as Numeric Data.  How do I get V12 to treat it as Numeric?

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

You are correct.

This code corrects the type and seems to work.

Thanks for everybodys help.

--------------------------------------------------------------------------------

Clear Globals();

dt1 = New Table( "Original", Add Rows( 4 ),

    New Column( "Column ID", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [1, 2, 3, 4] )

     ),

    New Column( "Result", Set Values( {11, 12, 13, 14} ) )

);

dt2 = New Table( "NewData", Add Rows( 4 ),

    New Column( "Column ID", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [5, 6, 7, 8, 9, 10] )

     ),

    New Column( "Result", Character, Nominal,

           Set Values( {"e", "f", "g", "h", "i", "j"} )

     )

);

colnames1 = dt1 << Get Column Names();

colnames2 = dt2 << Get Column Names();

//Replace column Data Type and Modeling Type

For(i = 1, i<= N ITems(colnames1), i++,

  dt = column(dt2, colnames2) << Get Data Type;

  mt = column(dt2, colnames2) << Get Modeling Type;

  column(dt1, i) << Data Type( dt ) << Modeling Type( mt );

);

9 REPLIES
Craige_Hales

Staff

Joined:

Mar 21, 2013

Can you post a small bit of the file or describe what the missing value codes look like?  How far down in the file is the first missing data?  Are you using a script to import the file?  Or the text import wizard?  Or just open with preferences?

JMP may be looking further through the file to discover non-numeric data than before.

In the table "source" script you can change the type of the column to numeric; you can rerun the script and get a numeric column with missing values.

8775_textimport.PNG

Craige
Martin

Community Trekker

Joined:

Aug 13, 2013

So, I need to keep playing with this.  It is a very large data table (thousands of records and a couple of thousand fields) and the missing data are only a few and they are buried in the table.  JMP 11 treated them as missing (solid circle) and the field as numeric and JMP 12 puts a "-" in the missing "cells".  I see these using the Summary -> Group function.

When I make a very small test .csv file, it imports correctly.

melaniedrake

Staff

Joined:

May 30, 2014

See if the discussion in this thread covers your question: Re: Is JMP 12 unable to import xls/xlsx as JMP 11?  I'm not sure if you're asking the same thing or not.

Martin

Community Trekker

Joined:

Aug 13, 2013

This is exactly my situation.  Columns with 16,000 rows and 5 empty cells now get imported as Character data.  JMP should make these arbitraty changes without giving the users the ability to define the "stricktness"-level, kinda like the smoother has the ability to adjust the amount of smoothing.

Now I am trying, unsuccessfully, to replace the Data Type and Modeling Type with those from the other data table.  Any help here would be appreciated.

//Replace column Data Type and Modeling Type

For(i = 1, i<= N ITems(colnames1), i++,

  dt = column(dt2, colnames2) << Get Data Type;

  mt = column(dt2, colnames2) << Get Modeling Type;

  column(dt1, i) << Data Type( dt ) << Modeling Type( mt );

);

melaniedrake

Staff

Joined:

May 30, 2014

As long as the column names match between your two tables (and the second tablehas the modeling and data types that you want applied in your first table), that should work. Be aware that any character data will be changed to missing. What in your script is not working? Are there any error messages in the log?

Martin

Community Trekker

Joined:

Aug 13, 2013

Here is an example code that "should" change the data types in dt1 to match dt2, but it does not work, and, the log file is clean.

----------------------------------------------------------------------------------------------------------------------------------

Clear Globals();

dt1 = New Table( "Original", Add Rows( 4 ),

    New Column( "Column ID", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [1, 2, 3, 4] )

     ),

    New Column( "Result", Set Values( {11, 12, 13, 14} ) )

);

dt2 = New Table( "NewData", Add Rows( 4 ),

    New Column( "Column ID", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [5, 6, 7, 8, 9, 10] )

     ),

    New Column( "Result", Character, Nominal,

           Set Values( {"e", "f", "g", "h", "i", "j"} )

     )

);

//Replace column Data Type and Modeling Type

For(i = 1, i<= N ITems(colnames1), i++,

  dt = column(dt2, colnames2) << Get Data Type;

  mt = column(dt2, colnames2) << Get Modeling Type;

  column(dt1, i) << Data Type( dt ) << Modelint Type( mt );

);

melaniedrake

Staff

Joined:

May 30, 2014

You're copying from the new data table (dt2) and applying to the original data table (dt1). Also, your last statement has a typo. Change "Modelint Type" to "Modeling Type" If you reverse that, does it work for you?

Clear Globals();

dt1 = New Table( "Original", Add Rows( 4 ),

New Column( "Column ID", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [1, 2, 3, 4] )

),

New Column( "Result", Set Values( {11, 12, 13, 14} ) )

);

dt2 = New Table( "NewData", Add Rows( 4 ),

New Column( "Column ID", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [5, 6, 7, 8, 9, 10] )

),

New Column( "Result", Character, Nominal,

           Set Values( {"e", "f", "g", "h", "i", "j"} )

)

);

// get column names from each data table

colnames1 = dt1 << get column names;

colnames2 = dt2 << get column names;

//Replace column Data Type and Modeling Type

// dt1 to dt2 instead of dt2 to dt1

For(i = 1, i<= N ITems(colnames1), i++,

dt = column(dt1, colnames1[i]) << Get Data Type;

mt = column(dt1, colnames1[i]) << Get Modeling Type;

column(dt2, i) << Data Type( dt ) << Modeling Type( mt ); // fix typo

);

Solution

You are correct.

This code corrects the type and seems to work.

Thanks for everybodys help.

--------------------------------------------------------------------------------

Clear Globals();

dt1 = New Table( "Original", Add Rows( 4 ),

    New Column( "Column ID", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [1, 2, 3, 4] )

     ),

    New Column( "Result", Set Values( {11, 12, 13, 14} ) )

);

dt2 = New Table( "NewData", Add Rows( 4 ),

    New Column( "Column ID", Numeric, Continuous, Format( "Best", 12 ),

           Set Values( [5, 6, 7, 8, 9, 10] )

     ),

    New Column( "Result", Character, Nominal,

           Set Values( {"e", "f", "g", "h", "i", "j"} )

     )

);

colnames1 = dt1 << Get Column Names();

colnames2 = dt2 << Get Column Names();

//Replace column Data Type and Modeling Type

For(i = 1, i<= N ITems(colnames1), i++,

  dt = column(dt2, colnames2) << Get Data Type;

  mt = column(dt2, colnames2) << Get Modeling Type;

  column(dt1, i) << Data Type( dt ) << Modeling Type( mt );

);

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Dear melaniedrake how to chage column data type and modeling type if the columns do not present a logical sequence. lets day I have 14 columns and need to chenge the data and modeling type with width, decimel, currency type, thousand seperator for 2nd 4th 11th 12th 14th columns each with different data/column type and properties of dec, width, thousand sep... etc  

Many thanks