Transform the JSON into a Table for easier manipulation
You need to do quite a bit of filtering and transformation to get the desired result. As you have learned, a good strategy for tackling larger problems is to solve the problem in small steps.
You decide to scope the first step so that the result is the raw JSON data transformed into a Table (a data structure provided by the RPA.Tables
library) that is then easy to filter, sort, group, etc.:
producer.robot
:
*** Settings ***
Documentation Inhuman Insurance, Inc. Artificial Intelligence System robot.
... Produces traffic data work items.
Library RPA.HTTP
Library RPA.JSON
Library RPA.Tables
*** Tasks ***
Produce traffic data work items
Download traffic data
${traffic_data}= Load traffic data as table
*** Keywords ***
Download traffic data
Download
... https://github.com/robocorp/inhuman-insurance-inc/raw/main/RS_198.json
... ${OUTPUT_DIR}${/}traffic.json
... overwrite=True
Load traffic data as table
${json}= Load JSON from file ${OUTPUT_DIR}${/}traffic.json
${table}= Create Table ${json}[value]
RETURN ${table}
You add a new Load traffic data as table
keyword that returns the JSON data in a Table format.
- The
Load JSON from file
keyword provided by theRPA.JSON
library returns the file contents (string) in JSON format. - The
Create Table
keyword from theRPA.Tables
library converts the JSON format into a Table.
Tables can be created from lists, for example. Looking at the raw JSON content, you see that the
value
property points to a list of all the things you will need.
You run the robot and view the log. Your robot has transformed the raw JSON data into a Table with lots of rows:
Created table: Table(columns=['Id', 'IndicatorCode', 'SpatialDimType', 'SpatialDim', 'TimeDimType', 'TimeDim', 'Dim1Type', 'Dim1', 'Dim2Type', 'Dim2', 'Dim3Type', 'Dim3', 'DataSourceDimType', 'DataSourceDim', 'Value', 'NumericValue', 'Low', 'High', 'Comments', 'Date', 'TimeDimensionValue', 'TimeDimensionBegin', 'TimeDimensionEnd'], rows=11640)
Viewing the whole table using a VS Code extensions
The table entry in the log is nice and concise but lacks the actual data.
To view the entire table, you decide to export the Table into a CSV file and view that as a human-readable representation using the Excel Viewer VS Code extension.
You call the super-handy Write table to CSV keyword:
Write Table To Csv ${traffic_data} test.csv
After rerunning the robot, you see a test.csv
file at the root. Clicking on it opens the raw CSV file. You click on the Open Preview
icon at the top-right. A preview of the data is displayed as a table. What a nice way to manually inspect the intermediate results!
DRY - Don't repeat yourself
Always in search for tidy code, you see that you have broken the Don't repeat yourself principle by duplicating the path to the traffic.json
file.
It's not a massive deal at the moment since you have so little code, but it still requires future maintainers to find and change the path in two places if the path needs to be changed.
Little repetition might eventually grow into lots of small repetition and make it slower to maintain your robot or even cause subtle bugs if someone forgets to replace all the instances of the duplicated things.
You decide to store the path of the traffic.json
file into a variable and then refer to that variable instead. This way, you remove the duplication:
producer.robot
:
*** Settings ***
Documentation Inhuman Insurance, Inc. Artificial Intelligence System robot.
... Produces traffic data work items.
Library RPA.HTTP
Library RPA.JSON
Library RPA.Tables
*** Variables ***
${TRAFFIC_JSON_FILE_PATH}= ${OUTPUT_DIR}${/}traffic.json
*** Tasks ***
Produce traffic data work items
Download traffic data
${traffic_data}= Load traffic data as table
*** Keywords ***
Download traffic data
Download
... https://github.com/robocorp/inhuman-insurance-inc/raw/main/RS_198.json
... ${TRAFFIC_JSON_FILE_PATH}
... overwrite=True
Load traffic data as table
${json}= Load JSON from file ${TRAFFIC_JSON_FILE_PATH}
${table}= Create Table ${json}[value]
RETURN ${table}