Choose Language Hide Translation Bar
Highlighted
FN
FN
Level V

Removing datetime columns from column list

I a have list containing columns the user has selected.

 

These are already numeric, but I want to find those that are with time or date format as they need special treatment.

 

How can I filter these using JSL?

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Removing datetime columns from column list

Here is an example of one way to do this.  The script opens a data table, finds all of the numeric, continuous column names, and then deletes the ones where it finds that it has a format that is one of the listed Date and DateTime formats.  

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Aircraft Incidents.jmp" );

colList = dt << get column names( string, numeric, continuous );
// Create a list of the date and datetime formats to check for
dateList = {"m/d/y", "mmddyyyy", "m/d/y h:m", "m/d/y h:m"};

show("All Numeric List", colList );

For( i = N Items( colList ), i >= 1, i--,
	If(
		Contains(
			dateList,
			Word( 2, Char( Column( dt, colList[i] ) << get format ), "(,\!"" )
		),
		show(Word( 2, Char( Column( dt, colList ) << get format ), "(,\!"" ));
		Remove From( colList, i, 1 )
	)
);

show("\!nList with Date columns removed", colList );
Jim

View solution in original post

5 REPLIES 5
Highlighted
txnelson
Super User

Re: Removing datetime columns from column list

Here is an example of one way to do this.  The script opens a data table, finds all of the numeric, continuous column names, and then deletes the ones where it finds that it has a format that is one of the listed Date and DateTime formats.  

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Aircraft Incidents.jmp" );

colList = dt << get column names( string, numeric, continuous );
// Create a list of the date and datetime formats to check for
dateList = {"m/d/y", "mmddyyyy", "m/d/y h:m", "m/d/y h:m"};

show("All Numeric List", colList );

For( i = N Items( colList ), i >= 1, i--,
	If(
		Contains(
			dateList,
			Word( 2, Char( Column( dt, colList[i] ) << get format ), "(,\!"" )
		),
		show(Word( 2, Char( Column( dt, colList ) << get format ), "(,\!"" ));
		Remove From( colList, i, 1 )
	)
);

show("\!nList with Date columns removed", colList );
Jim

View solution in original post

Highlighted
FN
FN
Level V

Re: Removing datetime columns from column list

I can also change the date to integer (sum of seconds since starting date).

 

Would it be better to change the format of all the columns in a data table that has already numeric values considering that I can have 100.000 rows and hundreds of columns?

 

 

 

 

Highlighted
txnelson
Super User

Re: Removing datetime columns from column list

I don't understand what changing the values to integers would do to help you identify datetime values. Datetime columns already are the number of seconds since Jan, 1, 1904. Turning them into integers just means that you would just be eliminating the fractions of a second from the datetime value.
Jim
Highlighted
FN
FN
Level V

Re: Removing datetime columns from column list

Indeed, without context it does not make much sense.

 

PythonSend() works by saving a temporary csv and loading it into pandas. When dealing with dates, current JMP implementation does not have the date_parser active and the object is load as text instead of a number.

Python Send() does not recognize datetime format (sent as string) 

 

As my date column is within other X's which are continuous, I need a way to either filter that column out or transform it to integers.

 

 

 

Highlighted
txnelson
Super User

Re: Removing datetime columns from column list

To determine if a character column is actually a datetime value, is pretty straight forward, particularly if the structure is known. Then it is a simple mater to set an informat to the datetime format of the incoming columns, and then change the data type to numeric and the modeling type to coninuous.....oh yes, and the format to the date or datetime format of your choice.
Jim
Article Labels