nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Accepted answers

txnelson

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

All comments

txnelson

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

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?

txnelson

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.

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.