Use external APIs and data wrangling?

Questions
, , , ,
1

Hi, I am considering using the Wolfram Cloud to host a project that makes use of API calls to a third party (never done production deployments in the WC before). The problem is that API calls (in the default way provided by the third party) contain metadata and even the key-value pairs included in the response need to be cleaned up (the only thing that is relevant to me is the last value, in the example below that would be the number 1.9082*^7). Is there any way to make the call so that just that value is extracted (or at least the list put in usable key-value dataset format?)? If not, what would be the most efficient way to clean up the output, to simply assign that value to a variable? The code will be making many simultaneous API calls, and I’ll really prefer to avoid performance issues -not to waste too much computing power wrangling data. Thanks!

{meta->{request->{granularity->Daily,start_date->2018-01-27,end_date->2018-01-31,limit->Null },status->Success,last_updated->2018-07-31},value->{{date->2018-01-27,value->1.48229*^7},{date->2018-01-28,value->1.42697*^7},{date->2018-01-29,value->1.67565*^7},{date->2018-01-30,value->1.91857*^7},{**date->2018-01-31,value->1.9082*^7**}}}
2

Is the data of JSON format? If so, Mathematica has JSON importers which may make this easy for you:

3

(1) Would something like this help. The idea would be to run your API as is but then get your data in a format that’s easier to work with and then (2) run an efficient Query on it.

 response = {meta -> {request -> {granularity -> Daily, 
       startdate -> 2018 - 01 - 27, enddate -> 2018 - 01 - 31, 
       limit -> Null}, status -> Success, 
     last_updated -> 2018 - 07 - 31}, 
   value -> {{date -> 2018 - 01 - 27, 
      value -> 1.48229^7}, {date -> 2018 - 01 - 28, 
      value -> 1.42697^7}, {date -> 2018 - 01 - 29, 
      value -> 1.67565^7}, {date -> 2018 - 01 - 30, 
      value -> 1.91857^7}, {date -> 2018 - 01 - 31, 
      value -> 1.9082^7}}}

Now turn your data into hierarchical Associations. Note the right composition of operator forms.

 response2 = (Association /* MapAt[Association, Key@meta] /* 
     MapAt[Association, {Key@value, All}])[response]

And now run the appropriate Query.

 Query[Key@value, All, Key@value][response2]

The result is the list {15.7229, 12.0477, 37.0922, 95.6849, 92.1228}, which is I believe what you want.

The advantage of this method is that once the data is in a nice form, you can use the panoply of Wolfram Language functionality. The only disadvantage I see is bandwidth. If all you really need is that last number and that’s all you are ever going to need, then fiddling with the API query might be a better way to go.

4

Thanks. I’ve tried importing as json, and also simply executing the API URL, but I get errors:

response = URLExecute[''URL"]

response2 = (Association /* MapAt[Association, Key@meta] /* 
    MapAt[Association, {Key@value, All}])[response]

MapAt::partw: Part {Key[meta]} of <|meta->{request->{granularity->Daily,start_date->2018-01-27,end_date->2018-01-31,limit->Null},status->Success,last_updated->2018-07-31},value->{<<1>>}|> does not exist.

MapAt::partw: Part {Key[value],All} of MapAt[Association,Key[meta]][<|meta->{request->{granularity->Daily,start_date->2018-01-27,end_date->2018-01-31,limit->Null},status->Success,last_updated->2018-07-31},value->{<<1>>}|>] does not exist.

BTW: the list in your example changes the actual values, not sure why (the last element returned should be 1.9082^7):

{15.7229, 12.0477, 37.0922, 95.6849, 92.1228}

5

(1) Would something like this help. The idea would be to run your API as is but then get your data in a format that’s easier to work with and then (2) run an efficient Query on it.

 response = {meta -> {request -> {granularity -> Daily, 
       startdate -> 2018 - 01 - 27, enddate -> 2018 - 01 - 31, 
       limit -> Null}, status -> Success, 
     last_updated -> 2018 - 07 - 31}, 
   value -> {{date -> 2018 - 01 - 27, 
      value -> 1.48229^7}, {date -> 2018 - 01 - 28, 
      value -> 1.42697^7}, {date -> 2018 - 01 - 29, 
      value -> 1.67565^7}, {date -> 2018 - 01 - 30, 
      value -> 1.91857^7}, {date -> 2018 - 01 - 31, 
      value -> 1.9082^7}}}

Now turn your data into hierarchical Associations. Note the right composition of operator forms.

 response2 = (Association /* MapAt[Association, Key@meta] /* 
     MapAt[Association, {Key@value, All}])[response]

And now run the appropriate Query.

 Query[Key@value, All, Key@value][response2]

The result is the list {15.7229, 12.0477, 37.0922, 95.6849, 92.1228}, which is I believe what you want.

The advantage of this method is that once the data is in a nice form, you can use the panoply of Wolfram Language functionality. The only disadvantage I see is bandwidth. If all you really need is that last number and that’s all you are ever going to need, then fiddling with the API query might be a better way to go.

I wrongly assumed based on your question that things like meta were symbols rather than the string “meta”. Can you provide the InputForm of the response you are getting. The reason we are getting different numbers is that Mathematica is parsing the ^ as an power operator rather than meaning 10 to the 7th power. I am confident your goal can be met pretty easily once we have the data in InputForm.

6

My bad. Here’s the InputForm:

{"meta" -> {"request" -> {"granularity" -> "Daily", "start_date" -> "2018-01-01", "end_date" -> "2018-01-31", "limit" -> Null}, "status" -> "Success", "last_updated" -> "2018-07-31"}, 
 "value" -> { {"date" -> "2018-01-27", "value" -> 1.4822867137494706*^7}, 
   {"date" -> "2018-01-28", "value" -> 1.4269713530779257*^7}, 
   {"date" -> "2018-01-29", "value" -> 1.6756473992098019*^7}, 
   {"date" -> "2018-01-30", "value" -> 1.9185713970146105*^7}, 
   {"date" -> "2018-01-31", "value" -> 1.9081979391072355*^7}}}
7

There are many ways to solve this problem, but I would start by turning the data into hierarchical Associations.

 response2 = (Association /* MapAt[Association, "meta"] /* 
     MapAt[Association, {"value", All}])[response]

Then it should be a simple Query

 Query["value", All, "value"][response2]

I get this:

 {1.48229*10^7, 1.42697*10^7, 1.67565*10^7, 1.91857*10^7, 1.9082*10^7}

Obviously, this could be made into a one liner. Also, I have not dealt with more complex responses from the API if those occur. And you might also benefit from turning the date string into a DateObject.

8

Dear @George W, welcome to Wolfram Community! Please make sure you know the rules: https://wolfr.am/READ-1ST

The rules explain how to format your code properly. If you do not format code, it may become corrupted and useless to other members. Please EDIT your post and make sure code blocks start on a new paragraph and look framed and colored like this.

int = Integrate[1/(x^3 - 1), x];
Map[Framed, int, Infinity]

ScreenShot2017-04-28at9.43.33AM.png
ScreenShot2017-04-28at9.43.33AM.png500×135 18.6 KB

9

Thank you all for the valuable input and recommendations. The error has been corrected and everything works now!

10

A solution that does not require conversion to Association

response // Lookup["value"] /* Map[Last] // Values

{1.48229*10^7, 1.42697*10^7, 1.67565*10^7, 1.91857*10^7, 1.9082*10^7}