Data formatting challenge from json to JMP

Peter_Hersh

I have been pulling data from Strava and bringing it into JMP.  The data is in a JSON format like the one attached It is a bit of a painful process at this point for me involving multi-series stacking deleting columns and a lot of data maneuvering.  I would like to end up with the two tables I attached.  I am hoping it is just my lack of knowledge with JSON parsing that is causing the trouble.  If anyone has a chance to take a look I would appreciate any ideas on making the process less painful.  

Heart Rate.jmp

Strava Run formatted.jmp

Morning_Run_.json

brady_brady

**** EDIT: be sure to change your path in line 5 to wherever your file resides.

 

 

Pete, try this. After running the import wizard, it seems that what comes back, while useless at first blush, is enough to go on, at least for the dataset at hand. Let me know how it goes for you on this (and ideally, another) dataset. I've made assumptions about how the data comes back, which may be invalid for other data.

 

Names Default To Here( 1 );

jsonFromWizardExpr = Expr(
	dt = Open(
		"/Users/brady.brady@jmp.com/Downloads/Morning_Run_.json",
		JSON Settings(
			Stack( 0 ),
			Row( "/root/data/values" ),
			Col(
				"/root/data/values",
				Column Name( "values" ),
				Fill( "Use Once" ),
				Type( "Numeric" ),
				Format( {"Best"} ),
				Modeling Type( "Continuous" )
			),
			Col(
				"/root/data/fields",
				Column Name( "fields" ),
				Fill( "Use Once" ),
				Type( "Character" ),
				Format( {"Best"} ),
				Modeling Type( "Continuous" )
			)
		),
		JSON Wizard( 0 )
	)
);

jsonFromWizardExpr;

//get nonempty rows in column 2. These follow the data having the fields indicated
{rowA, rowB} = As List( dt << get rows where( dt:fields != "" ) );

//main logic is here; note latlng needs to be split to lat, lng
colList = Words( Substitute( dt:fields[rowA], "latlng", "lat, lng" ), " ," );
As Table( Shape( dt[1 :: (rowA - 1), 1], (rowA - 1) / N Items( colList ) ), <<column names( colList ) );

colList = Words( dt:fields[rowB], ", " );
As Table( Shape( dt[((rowA) :: (rowB - 1)), 1], (rowB - rowA) / N Items( colList ) ), <<column names( colList ) );