<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/data-table-by-example-part-1/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://mathewanalytics.com/2017/09/26/data-table-by-example-part-1/"> R – Mathew Analytics</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>For many years, I actively avoided the data.table package and preferred to utilize the tools available in either base R or dplyr for data aggregation and exploration. However, over the past year, I have come to realize that this was a mistake. Data tables are incredible and provide R users with a syntatically<br>
concise and efficient data structure for working with small, medium, or large datasets. While the package is well documented, I wanted to put together a series of posts that could be useful for those who want to get introduced to the data.table package in a more task oriented format.</p>
<p>For this series of posts, I will be working with data that comes from the Chicago Police Department’s Citizen Law Enforcement Analysis and Reporting system. This dataset contains information on reported incidents of crime that occured in the city of Chicago from 2001 to present. You can use the wget command in the terminal to export it as a csv file.</p>
<pre>
$ wget –no-check-certificate –progress=dot https://data.cityofchicago.org/api/views/ijzp-q8t2/rows.csv?accessType=DOWNLOAD > rows.csv
</pre>
<p>This file can be found in your working directory and was saved as “rows.csv”. We will import the data into R with the fread function and look at the first few rows and structure of the data.</p>
<pre>
dat = fread(&quot;rows.csv&quot;)

dat[1:3]
str(dat)
</pre>
<p>Notice that the each of the string variables in the data set was imported as a character and not a factor. With base R functions like read.csv, we have to set the stringsAsFactors argument to TRUE if we want this result.</p>
<pre>
names(dat) &lt;- gsub(&quot; &quot;, &quot;_&quot;, names(dat))
</pre>
<p>Let’s say that we want to see the frequency distribution of several of these variables. This can be done by using .N in conjunction with the by operator.</p>
<pre>
dat[, .N, by=.(Arrest)]
</pre>
<p>In the code below, you can also see how to chain operations togther. We started by finding the count of each response in the variable, ordered the count in descending order, and then selected only those which occured more than 200,000 times.</p>
<pre>
dat[, .N, by=.(Primary_Type)][order(-N)][N>=200000]

dat[, .N, by=.(Location_Description)][order(-N)][N>=200000]
</pre>
<p>Let’s say that we want to get a count of prostitution incidents by month. To get the desired results, we will need to modify the date values, filter instances in which the primary type was “prostitution”, and then get a count by each date.</p>
<pre>
dat[, date2 := paste(substr(as.Date(dat$Date, format=&quot;%m/%d/%Y&quot;),1,7), &quot;01&quot;, sep=&quot;-&quot;)][
   Primary_Type==&quot;PROSTITUTION&quot;, .N, by=.(date2)][, date2 := as.Date(date2)][order(date2)][]
</pre>
<p>If you want to plot the results as a line graph, just add another chain which executes the visualization.</p>
<pre>
dat[, date2 := paste(substr(as.Date(dat$Date, format=&quot;%m/%d/%Y&quot;),1,7), &quot;01&quot;, sep=&quot;-&quot;)][
   Primary_Type==&quot;PROSTITUTION&quot;, .N, by=.(date2)][, date2 := as.Date(date2)][order(date2)] %>%
      ggplot(., aes(date2, N)) + geom_line(group=1)
</pre>
<p><a rel="nofollow" href="https://mathewanalytics.files.wordpress.com/2017/09/blog_plot.png" target="_blank"><img data-orig-file="https://mathewanalytics.files.wordpress.com/2017/09/blog_plot.png?w=456" data-orig-size="503,319" data-medium-file="https://mathewanalytics.files.wordpress.com/2017/09/blog_plot.png?w=456?w=300" data-attachment-id="827" data-image-meta="{" data-image-description="" src="https://mathewanalytics.files.wordpress.com/2017/09/blog_plot.png?w=456" data-permalink="https://mathewanalytics.com/2017/09/26/data-table-by-example-part-1/blog_plot/#main" data-large-file="https://mathewanalytics.files.wordpress.com/2017/09/blog_plot.png?w=456?w=503" alt="" data-image-title="blog_plot" sizes="(max-width: 503px) 100vw, 503px" data-comments-opened="0" class="wp-image-827" data-recalc-dims="1"></a></p>
<p>I’ve obviously skipped over a lot and some of the code presented here is more verbose than needed. Even so, beginners to R will hopefully find this useful and it will pique your interest in the beauty of the data table package. Future posts will cover more of the goodies available in the data.table package such as get(), set(), {}, and so forth.</p>
<p><em><strong>PS: As of this past week, I’ve decided to re-enter the job market. So if you are looking for a statistical analyst or data scientist with strong experience in programming in R and time series econometrics, please contact me at mathewanalytics@gmail.com or reach me through <a rel="nofollow" href="https://www.linkedin.com/in/abraham-mathew-21221b29/" target="_blank">LinkedIn</a></strong></em></p>
<p> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/mathewanalytics.wordpress.com/823/" target="_blank"><img border="0" alt="" src="http://feeds.wordpress.com/1.0/comments/mathewanalytics.wordpress.com/823/"></a> <img border="0" alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" data-recalc-dims="1"></p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://mathewanalytics.com/2017/09/26/data-table-by-example-part-1/"> R – Mathew Analytics</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/rOuDxlrDpnU" height="1"></a></div>