<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/web-data-acquisition-from-database-to-dataframe-for-data-analysis-and-visualization-part-4/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://r-posts.com/web-data-acquisition-from-database-to-dataframe-for-data-analysis-and-visualization-part-4/"> R-posts.com</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>The previous <a rel="nofollow" href="https://www.linkedin.com/pulse/web-data-acquisition-parsing-json-objects-tidyjson-part-palloni" target="_blank">post</a> described how the deeply nested JSON data on fligths were parsed and stored in an R-friendly database structure. However, looking into the data, the information is not yet ready for statistical analysis and visualization and some further processing is necessary before extracting insights and producing nice plots.</p>
<p>In the parsed batch, it is clearly visible the redundant structure of the data with the flight <em>id</em> repeted for each segment of each flight. This is also confirmed with the following simple check as the rows of the dataframe are more than the unique counts of the elements in the <em>id</em> column.</p>
<pre>dim(data_items)
[1] 397  15

length(unique(data_items$id))
201

# real time changes of data could produce different results
</pre>
<p>This implies that the information of each segment of each flight has to be aggregated and merged in a dataset as single observations of a statistical analysis between, for example, price and distance. First, a unique primary key for each observation has to be used as reference variable to uniquely identify each element of the dataset.</p>
<pre>
library(plyr) # sql like functions
library(readr) # parse numbers from strings
 
data_items &lt;- data.frame(data_items)
 
# id (primary key)
data &lt;- data.frame(unique(data_items<span>$id))
colnames(data) &lt;-c('id')
# n° of segment
n_segment &lt;-aggregate(data_items['segment.id'], by=data_items['id'], length)
data &lt;-join(data, n_segment, by='id', type='left', match='first') # sql left join
# mileage
mileage &lt;-aggregate(data_items['segment.leg.mileage'], by=data_items['id'], sum)
data &lt;-join(data, mileage, by='id', type='left', match='first') # sql left join
# price
price &lt;-data.frame('id'=data_items<span>$id, 'price'=parse_number(data_items<span>$saleTotal))
data &lt;-join(data, price, by='id', type='left', match='first') # sql left join
# dataframe
colnames(data) &lt;-c('id','segment', 'mileage', 'price')
head(data)

<p>The aggregation of mileage and price using the unique primary key allows to set up a dataframe ready for statistical analysis and data visualization. Current data tells us that there is a maximum of three segments in the connection between FCO and LHR with a minimum price of around EUR 122 and a median around EUR 600.</p>
<pre>

# descriptive statistics
summary(data)
 
 
# histogram price &amp; distance
g1 &lt;- ggplot(data, aes(x=price)) + 
  geom_histogram(bins = 50) +  
  ylab(&quot;Distribution of the Price (EUR)&quot;) +
  xlab(&quot;Price (EUR)&quot;) 
 
g2 &lt;- ggplot(data, aes(x=mileage)) + 
  geom_histogram(bins = 50) +  
  ylab(&quot;Distribution of the Distance&quot;) +
  xlab(&quot;Distance (miles)&quot;)
 
grid.arrange(g1, g2)
# price - distance relationship
s0 &lt;- ggplot(data = data, aes(x = mileage, y = price)) +
    geom_smooth(method = &quot;lm&quot;, se=FALSE, color=&quot;black&quot;) +
    geom_point() + labs(x = &quot;Distance in miles&quot;, y = &quot;Price (EUR)&quot;)
s0 &lt;- ggMarginal(s0, type = &quot;histogram&quot;, binwidth = 30)
s0

</pre>
<p>Of course, plenty of other analysis and graphical representations using flights features are possible given the large set of variables available in QPX Express API and the availability of data in real time.<br>
<img data-orig-file="https://i2.wp.com/r-posts.com/wp-content/uploads/2017/06/scatter_persons_two-lines.png?fit=1469%2C836" data-orig-size="1469,836" data-medium-file="https://i2.wp.com/r-posts.com/wp-content/uploads/2017/06/scatter_persons_two-lines.png?fit=450%2C256" data-attachment-id="183" data-image-meta="{" data-image-description="" src="https://i2.wp.com/r-posts.com/wp-content/uploads/2017/06/scatter_persons_two-lines.png?resize=450%2C256" data-permalink="http://r-posts.com/web-data-acquisition-from-database-to-dataframe-for-data-analysis-and-visualization-part-4/scatter_persons_two-lines/" data-large-file="https://i2.wp.com/r-posts.com/wp-content/uploads/2017/06/scatter_persons_two-lines.png?fit=450%2C256" alt="" data-image-title="scatter_persons_two lines" sizes="(max-width: 450px) 100vw, 450px" data-comments-opened="1" class="wp-image-183" data-recalc-dims="1"></p>
<p>To conclude the 4-step (flight) trip from data acquisition to data analysis, let's recap the most important concepts described in each of the post:</p>
<p>1) <a rel="nofollow" href="https://www.linkedin.com/pulse/web-data-acquisition-understanding-rcurl-from-command-roberto-palloni" target="_blank"><strong>Client-Server connection</strong></a></p>
<p>2) <a rel="nofollow" href="https://www.linkedin.com/pulse/web-data-acquisition-structure-rcurl-request-part-2-roberto-palloni" target="_blank"><strong>POST request in R</strong></a></p>
<p>3) <a rel="nofollow" href="https://www.linkedin.com/pulse/web-data-acquisition-parsing-json-objects-tidyjson-part-palloni" target="_blank"><strong>Data parsing and structuring</strong></a></p>
<p>4) <a rel="nofollow" href="https://www.linkedin.com/pulse/web-data-acquisition-from-database-dataframe-analysis-roberto-palloni" target="_blank"><strong>Data analysis and visualization</strong></a></p>
<div><img src="https://i0.wp.com/media.licdn.com/mpr/mpr/AAEAAQAAAAAAAAz4AAAAJGQyMWYzMDUyLTgxMDgtNGFhNi1iYzU1LTQwNjkxMTI2MTg2Yg.png?w=660&ssl=1" data-recalc-dims="1"></div>
<p>That's all folks!</p>
<p>#R #rstats #maRche #json #curl #qpxexpress #Rbloggers</p>
<p><em>This post is also shared in </em><a rel="nofollow" href="https://www.r-bloggers.com/web-data-acquisition-understanding-rcurl-from-the-command-line-part-1/" target="_blank"><em>www.r-bloggers.com</em></a></p>
<pre></pre>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://r-posts.com/web-data-acquisition-from-database-to-dataframe-for-data-analysis-and-visualization-part-4/"> R-posts.com</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...

	<img alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/2ONY96UrvSo" height="1"></a></div></span></span></span></pre>