turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- V12 import treatment of missing data

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 12, 2015 7:42 AM
(1985 views)

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 12, 2015 11:18 AM
(1640 views)

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.

Craige

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 13, 2015 7:54 AM
(1640 views)

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 13, 2015 9:43 AM
(1640 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 14, 2015 6:29 AM
(1640 views)

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 );

);

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 14, 2015 6:49 AM
(1640 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 14, 2015 8:27 AM
(1640 views)

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 );

);

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 14, 2015 9:58 AM
(1640 views)

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

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 );

);

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 15, 2015 3:51 AM
(1640 views)

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