- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
How to open this JSON file as a data table?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
3 REPLIES 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to open this JSON file as a data table?
nice!
Craige
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to open this JSON file as a data table?
Thanks a lot @jthi !