cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
View Original Published Thread

How to open this JSON file as a data table?

shasheminassab1
Level I

I need help with opening this JSON file in JMP as a data table: https://www.purpleair.com/data.json

 

I used JMP's JSON Import Wizard, but have not been able to open the file in a way that I want to.

I'd like the 'fields' to be the column headers and 'data' to be the rows in the data table. I appreciate your help in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User


Re: How to open this JSON file as a data table?

Most likely there are better ways to handle this, but here is one possible solution (requires JMP16 due to For Each):

Names Default To Here(1);
s = New HTTP Request(
	url("https://www.purpleair.com/data.json"),
	Method("GET"),
	Headers({"Accept: application/json"})
) << Send;
aa_json = Associative Array(Parse JSON(s));
//aa_json << get keys;
//aa_json["fields"];

//Create collection table and columns
dt = New Table("json");
For Each({new_col}, aa_json["fields"],
	dt << New Column(new_col);
);
Column(dt, "Label") << Data Type("Character"); //character columns must be set "manually"

//Fill rows
row_count = N Items(aa_json["data"]);
dt << Add Rows(row_count);
dt[0,0] = aa_json["data"];
Write(); //empty Write() to avoid printing all values to log

 

-Jarmo

View solution in original post

3 REPLIES 3
jthi
Super User


Re: How to open this JSON file as a data table?

Most likely there are better ways to handle this, but here is one possible solution (requires JMP16 due to For Each):

Names Default To Here(1);
s = New HTTP Request(
	url("https://www.purpleair.com/data.json"),
	Method("GET"),
	Headers({"Accept: application/json"})
) << Send;
aa_json = Associative Array(Parse JSON(s));
//aa_json << get keys;
//aa_json["fields"];

//Create collection table and columns
dt = New Table("json");
For Each({new_col}, aa_json["fields"],
	dt << New Column(new_col);
);
Column(dt, "Label") << Data Type("Character"); //character columns must be set "manually"

//Fill rows
row_count = N Items(aa_json["data"]);
dt << Add Rows(row_count);
dt[0,0] = aa_json["data"];
Write(); //empty Write() to avoid printing all values to log

 

-Jarmo
Craige_Hales
Super User


Re: How to open this JSON file as a data table?

nice!

Craige


Re: How to open this JSON file as a data table?

Thanks a lot @jthi !