The collection of example flight data in json format available in part 2, described the libraries and the structure of the POST request necessary to collect data in a json object. Despite the process generated and transferred locally a proper response, the data collected were neither in a suitable structure for data analysis nor immediately readable. They appears as just a long string of information nested and separated according to the JavaScript object notation syntax.

Thus, to visualize the deeply nested json object and make it human readable and understandable for further processing, the json content could be copied and pasted in a common online parser. The tool allows to select each node of the tree and observe the data structure up to the variables and data of interest for the statistical analysis. The bulk of the relevant information for the purpose of the analysis on flight prices are hidden in the tripOption node as shown in the following figure (only 50 flight solutions were requested).

However, looking deeply into the object, several other elements are provided as the distance in mile, the segment, the duration, the carrier, etc. The R parser to transform the json structure in a usable dataframe requires the dplyr library for using the pipe operator (%>%) to streamline the code and make the parser more readable. Nevertheless, the library actually wrangling through the lines is tidyjson and its powerful functions:

• enter_object: enters and dives into a data object;
• gather_array: stacks a JSON array;
• spread_values: creates new columns from values assigning specific type (e.g. jstring, jnumber).

library(dplyr)      # for pipe operator %>% and other dplyr functions
library(tidyjson)   # https://cran.r-project.org/web/packages/tidyjson/vignettes/introduction-to-tidyjson.html

data_items <- datajson %>%
  spread_values(kind = jstring("kind")) %>%
  spread_values(trips.kind = jstring("trips","kind")) %>%
  spread_values(trips.rid = jstring("trips","requestId")) %>%
  enter_object("trips","tripOption") %>%
  gather_array %>%
  spread_values(
    id = jstring("id"),
    saleTotal = jstring("saleTotal")) %>%
  enter_object("slice") %>% 
  gather_array %>%
  spread_values(slice.kind = jstring("kind")) %>%
  spread_values(slice.duration = jstring("duration")) %>%
  enter_object("segment") %>% 
  gather_array %>%
  spread_values(
    segment.kind = jstring("kind"),
    segment.duration = jnumber("duration"),
    segment.id = jstring("id"),
    segment.cabin = jstring("cabin")) %>%
  enter_object("leg") %>% 
  gather_array %>%
  spread_values(
    segment.leg.aircraft = jstring("aircraft"),
    segment.leg.origin = jstring("origin"),
    segment.leg.destination = jstring("destination"),
    segment.leg.mileage = jnumber("mileage")) %>%
  select(kind, trips.kind, trips.rid,
         saleTotal,id, slice.kind, slice.duration,
         segment.kind, segment.duration, segment.id,
         segment.cabin, segment.leg.aircraft, segment.leg.origin,
         segment.leg.destination, segment.leg.mileage)

head(data_items)
                    kind             trips.kind              trips.rid saleTotal
1 qpxExpress#tripsSearch qpxexpress#tripOptions UnxCOx4nKIcIOpRiG0QBOe EUR178.38
2 qpxExpress#tripsSearch qpxexpress#tripOptions UnxCOx4nKIcIOpRiG0QBOe EUR178.38
3 qpxExpress#tripsSearch qpxexpress#tripOptions UnxCOx4nKIcIOpRiG0QBOe EUR235.20
4 qpxExpress#tripsSearch qpxexpress#tripOptions UnxCOx4nKIcIOpRiG0QBOe EUR235.20
5 qpxExpress#tripsSearch qpxexpress#tripOptions UnxCOx4nKIcIOpRiG0QBOe EUR248.60
6 qpxExpress#tripsSearch qpxexpress#tripOptions UnxCOx4nKIcIOpRiG0QBOe EUR248.60
                         id           slice.kind slice.duration
1 ftm7QA6APQTQ4YVjeHrxLI006 qpxexpress#sliceInfo            510
2 ftm7QA6APQTQ4YVjeHrxLI006 qpxexpress#sliceInfo            510
3 ftm7QA6APQTQ4YVjeHrxLI009 qpxexpress#sliceInfo            490
4 ftm7QA6APQTQ4YVjeHrxLI009 qpxexpress#sliceInfo            490
5 ftm7QA6APQTQ4YVjeHrxLI007 qpxexpress#sliceInfo            355
6 ftm7QA6APQTQ4YVjeHrxLI007 qpxexpress#sliceInfo            355
            segment.kind segment.duration       segment.id segment.cabin
1 qpxexpress#segmentInfo              160 GixYrGFgbbe34NsI         COACH
2 qpxexpress#segmentInfo              235 Gj1XVe-oYbTCLT5V         COACH
3 qpxexpress#segmentInfo              190 Grt369Z0shJhZOUX         COACH
4 qpxexpress#segmentInfo              155 GRvrptyoeTfrSqg8         COACH
5 qpxexpress#segmentInfo              100 GXzd3e5z7g-5CCjJ         COACH
6 qpxexpress#segmentInfo              105 G8axcks1R8zJWKrN         COACH
  segment.leg.aircraft segment.leg.origin segment.leg.destination segment.leg.mileage
1                  320                FCO                     IST                 859
2                  77W                IST                     LHR                1561
3                  73H                FCO                     ARN                1256
4                  73G                ARN                     LHR                 908
5                  319                FCO                     STR                 497
6                  319                STR                     LHR                 469

Data are now in an R-friendly structure despite not yet ready for analysis. As can be observed from the first rows, each record has information on a single segment of the flight selected. A further step of aggregation using some SQL is needed in order to end up with a dataframe of flights data suitable for statistical analysis.

Next up, the aggregation, some data analysis and data visualization to complete the journey through the web data acquisition using R.

#R #rstats #maRche #json #curl #tidyjson #Rbloggers

This post is also shared in www.r-bloggers.com and LinkedIn