JSON Import Wizard with Pandas Values and Headers

robot — Wed, 04 Sep 2024 17:55:11 GMT

Hi,

I am trying to import a JSON file as a Data Table, and having difficulty finding the correct settings in the JSON Wizard. Does anyone know the correct settings to use? I am using JMP 18.0.1.

Example JSON

{
    "data": {
        "index": [
            0,
            1,
            2,
            3,
            4
        ],
        "columns": [
            "device_name",
            "num",
            "voltage_measure_V",
            "current_source_applied_A",
            "in_compliance"
        ],
        "data": [
            [
                "DEVICE_A",
                1,
                0.35,
                0.03,
                false
            ],
            [
                "DEVICE_B",
                2,
                0.40,
                0.52,
                false
            ],
            [
                "DEVICE_C",
                3,
                0.45,
                0.21,
                false
            ],
            [
                "DEVICE_D",
                4,
                0.50,
                0.53,
                false
            ],
            [
                "DEVICE_E",
                5,
                0.55,
                0.05,
                false
            ]
        ]
    }
}

Desired Data Table

New Table( "ExampleData",
	Add Rows( 5 ),
	New Column( "index", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1, 2, 3, 4] ) ),
	New Column( "device_name", Character, "Nominal", Set Values( {"DEVICE_A", "DEVICE_B", "DEVICE_C", "DEVICE_D", "DEVICE_E"} ) ),
	New Column( "num", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3, 4, 5] ) ),
	New Column( "voltage_measure_V",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0.35, 0.4, 0.45, 0.5, 0.55] )
	),
	New Column( "current_source_applied_A",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0.03, 0.52, 0.21, 0.53, 0.05] )
	),
	New Column( "in_compliance", Numeric, "Ordinal", Format( "Best", 12 ), Set Values( [0, 0, 0, 0, 0] ) )
);

JSON Wizard Best Guess (but not right...)

Open(
	"$DOWNLOADS/ExampleData.json",
	JSON Settings(
		Stack( 0 ),
		Row( "/root/data/index" ),
		Row( "/root/data/data" ),
		Col(
			"/root/data/index",
			Column Name( "data.index" ),
			Fill( "Use Once" ),
			Type( "Numeric" ),
			Format( {"Best"} ),
			Modeling Type( "Continuous" )
		),
		Col(
			"/root/data/columns",
			Column Name( "data.columns" ),
			Fill( "Use Once" ),
			Type( "JSON Lines with Headers" ),
			Format( {"Best"} ),
			Modeling Type( "Continuous" )
		),
		Col(
			"/root/data/data",
			Column Name( "data.data" ),
			Fill( "Use Once" ),
			Type( "Pandas Values" ),
			Format( {"Best"} ),
			Modeling Type( "Continuous" )
		)
	),
	JSON Wizard( 1 )
);

Re: JSON Import Wizard with Pandas Values and Headers

Craige_Hales — Thu, 05 Sep 2024 00:15:23 GMT

Right-click and try the pandas split option.

json = "\[
{
    "data": {
        "index": [
            0,
            1,
            2,
            3,
            4
        ],
        "columns": [
            "device_name",
            "num",
            "voltage_measure_V",
            "current_source_applied_A",
            "in_compliance"
        ],
        "data": [
            [
                "DEVICE_A",
                1,
                0.35,
                0.03,
                false
            ],
            [
                "DEVICE_B",
                2,
                0.40,
                0.52,
                false
            ],
            [
                "DEVICE_C",
                3,
                0.45,
                0.21,
                false
            ],
            [
                "DEVICE_D",
                4,
                0.50,
                0.53,
                false
            ],
            [
                "DEVICE_E",
                5,
                0.55,
                0.05,
                false
            ]
        ]
    }
}
]\";
open(chartoblob(json),JSON Settings(
	Stack( 0 ),
	Col(
		"/root/data",
		Column Name( "data" ),
		Fill( "Use Once" ),
		Type( "Pandas Split" ),
		Format( {"Best"} ),
		Modeling Type( "Continuous" )
	)
))

Re: JSON Import Wizard with Pandas Values and Headers

robot — Thu, 05 Sep 2024 00:24:19 GMT

Very nice. Thanks!

topic Re: JSON Import Wizard with Pandas Values and Headers in Discussions

JSON Import Wizard with Pandas Values and Headers

Re: JSON Import Wizard with Pandas Values and Headers

Re: JSON Import Wizard with Pandas Values and Headers