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
- :
- Re: V12 import treatment of missing data

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

Highlighted

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

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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 );

);

9 REPLIES 9

Highlighted
##

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

Re: V12 import treatment of missing data

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

Highlighted
##

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

Re: V12 import treatment of missing data

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.

Highlighted
##

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.

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

Re: V12 import treatment of missing data

Highlighted
##

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

Re: V12 import treatment of missing data

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

);

Highlighted
##

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?

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

Re: V12 import treatment of missing data

Highlighted
##

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

Re: V12 import treatment of missing data

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

);

Highlighted
##

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

Re: V12 import treatment of missing data

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

);

Highlighted
##

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

Re: V12 import treatment of missing data

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