Importing datetime column from pandas dataframe

matth1 — Thu, 28 Nov 2024 16:29:33 GMT

I have a pandas data frame which has been imported from a database. One column is in the datetime64[ns] type. I want to copy the dataframe into a JMP data table, however the datatime data is lost (missing values) when using the method in this post: https://community.jmp.com/t5/JMPer-Cable/New-in-JMP-18-Python-jmp-DataTable-and-pandas-DataFrame/ba-p/744680

An example is below:

import jmp
import pandas as pd
from pandas.api.types import is_object_dtype, is_numeric_dtype, is_bool_dtype, is_string_dtype, is_datetime64_dtype

df = pd.DataFrame({'txt': ['A', 'B'],
	'num': [ 123, 543 ], 
	'time': [pd.to_datetime('2024-11-24 12:34:56'), pd.to_datetime('2024-11-25 12:34:56')]})

# ------- WORKAROUNDS BELOW -------
# Creating two new columns to get the datetime column to JMP data table
#  Convert datetime to epoch and add Num( 01Jan1970 ) to match JMP's method:
df['datetime_epoch'] = df['time'].astype('int64')//1e9 + 2082844800 
#  or... create new column with datetime as a string
df['time_str'] = df[ 'time' ].astype('str')
# -------

dt0 = jmp.DataTable( 'tb', df.shape[0] )
# get the column names from the data frame
names = list(df.columns)
for j in range( df.shape[1] ):
	# check if the coulumn data type is string or numeric
	if is_string_dtype(df[ names[j] ] ):
		dt0.new_column(names[j], jmp.DataType.Character )
	else:
		dt0.new_column(names[j], jmp.DataType.Numeric )
	# populate the JMP column with data
	dt0[j] = list(df.iloc[:,j])

A couple of workarounds are to either change the datetime to a string then use Parse Date() in a new column, or concert the datetime to an integer, add the necessary offset and then reformat it in the JMP table.

// New column to parse the string format date
Data Table( "tb" ):Column 6 << Input Format( "d/m/y h:m:s", 0 ) <<
Format( "d/m/y h:m:s", 22, 0 ) << Set Formula( Parse Date( :time_str ) );

// Convert the epoch date format to human readable
Data Table( "tb" ):datetime_epoch << Input Format( "d/m/y h:m:s", 0 ) <<
Format( "d/m/y h:m:s", 22, 0 );

Both are OK, but require some work and leave some redundant columns. Is there a proper JMP way of doing this?

I'm using JMP 18.1.1.

Re: Importing datetime column from pandas dataframe

jthi — Thu, 28 Nov 2024 17:58:02 GMT

I think both of those methods are ok.JMP doesn't recognize the timestamp which I think you did notice)

You can also do direct conversion (I think this might use temporary .csv)

import jmp
import pandas as pd
from pandas.api.types import is_object_dtype, is_numeric_dtype, is_bool_dtype, is_string_dtype, is_datetime64_dtype

df = pd.DataFrame({'txt': ['A', 'B'],
	'num': [ 123, 543 ], 
	'time': [pd.to_datetime('2024-11-24 12:34:56'), pd.to_datetime('2024-11-25 12:34:56')]})

jmp.run_jsl("""
	dt = Python Get(df);
	dt << new data view;
	Python Send(dt);
"""
)

Re: Importing datetime column from pandas dataframe

matth1 — Fri, 29 Nov 2024 09:25:34 GMT

Thanks @jthi. The Python Get() method does seem to save a temporary .csv file. I'll use the workarounds for now, but it would be nice to have a more efficient built-in way of copying pandas dataframes to JMP tables.

topic Importing datetime column from pandas dataframe in Discussions

Importing datetime column from pandas dataframe

Re: Importing datetime column from pandas dataframe

Re: Importing datetime column from pandas dataframe