SQL : PostgreSQL

  Aggregate Functions Like most other relational database products,  PostgreSQL  supports  aggregate functions . An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the  count ,  sum ,  avg  (average),  max  (maximum) and  min  (minimum) over a set of rows. As an example, we can find the highest low-temperature reading anywhere with: SELECT max(temp_lo) FROM weather; max ----- 46 (1 row) If we wanted to know what city (or cities) that reading occurred in, we might try: SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG but this will not work since the aggregate  max  cannot be used in the  WHERE  clause. (This restriction exists because the  WHERE  clause determines which rows will be included in the aggregate calculation; so obviously it has to be evaluated before aggregate functions are computed.) However, as is o...

documentation / report in R

 


R Markdown resources

R Markdown is a useful tool that allows you to save and execute code, and generate shareable reports for stakeholders. As you learn more about how to use it, it can be helpful to bookmark some resources to refer to later.

This reading explores some great online resources that will help you learn more about R Markdown and how to use it to document your analysis. 

Image of a person sitting down holding a large reference book

R Markdown documentation

RStudio's R Markdown documentation includes a series of tutorials that will help you learn about the main features of R Markdown, including code chunks, output formats, notebooks, interactive documents, and more. The tutorials include online lessons that you can complete directly in your RStudio Cloud workspace. 

R Markdown reference materials

RStudio has developed a reference guide and a cheat sheet that you can bookmark and use whenever you practice writing R Markdown files.  

  • T​he R Markdown Reference Guide contains three sections: Markdown syntax, knitr chunk options, and Pandoc options. The guide is super detailed and includes tons of examples and explanations so that you can easily find the exact information you need to customize your R Markdown documents. 

  • T​he R Markdown Cheat Sheet is a convenient summary of the different steps and workflow processes for R. It also includes sections with abbreviated explanations of knitr and pandoc chunk options, and other useful information to review or look up while you work.

R for Data Science book

For a well-organized introduction to the basics of R Markdown, check out the Communicate section of the R for Data Science book. It covers the main features and functions of R Markdown, the various output formats, and the workflow for combining text and code to create an analysis notebook.

R Markdown: The Definitive Guide 

If you want to really explore the capabilities of R Markdown in a systematic way, R Markdown: The Definitive Guide provides a comprehensive guide to the R Markdown ecosystem. This book contains four main parts:

  1. Part I explains how to install the relevant packages and offers an overview of R Markdown, including the syntax for Markdown and code chunks.

  2. Part II provides detailed documentation of the built-in output formats included in R Markdown, like document formats and presentation formats. 

  3. Part III shares several R Markdown extension packages that allow you to build different applications or generate output documents with different styles. 

  4. Part IV covers advanced topics in R Markdown. 

Optional: Jupyter notebooks

Image of the planet jupiter. Jupiter's moons are different icons of code and computer tools

Jupyter notebooks are documents that contain computer code and rich text elements – such as comments, links, or descriptions of your analysis and results. You will find them used in a variety of online tools, including Project Jupyter, Kaggle, and Google Colaboratory ("Colab" for short). These notebooks can be executable documents that you can run to perform an analysis. 

Jupyter notebooks can come in handy with everything from data cleaning and transformation, to statistical modeling and visualizations. They are compatible with R, so you can consider them as an alternative to R Markdown. And just like R Markdown documents, you can easily share Jupyter notebooks with team members and stakeholders. 

Jupyter notebooks in Kaggle

If you are working in Kaggle, there are two types of notebooks available: Jupyter notebooks and scripts (including R Markdown scripts). For more information, refer to the How to Use Kaggle Notebooks page.

Jupyter notebooks in Google Colab

Google Colab is a product from Google Research. Colab is a hosted Jupyter notebook service that requires no setup to use. For more information, refer to the Welcome to Colaboratory page.

Additional resources

To learn more about Jupyter notebooks, check out these resources: 

  • Project Jupyter: This is the home of Jupyter notebooks, as well as JupyterLab – the web-based interactive development environment for Jupyter notebooks, code, and data. 

  • Jupyter Notebook: An Introduction: This detailed introduction of Jupyter notebooks comes from the people at Real Python, a tutorial-based site devoted to all things Python. You can take a video course or follow the written tutorial to get started with Jupyter notebooks and learn about its features and capabilities.

A​nd, just like R Markdown, Jupyter notebooks include basic formatting tools and rules that will help you keep your work organized and user-friendly. In fact, Jupyter uses R Markdown as its language for writing and formatting text in a notebook. 

To learn about basic formatting in Jupyter notebooks, check out these resources: 

  • The Jupyter Notebook: This resource provides an overview of Jupyter notebooks, including information about the structure of the user interface and notebook document. You’ll also learn about the basic workflow for using a notebook document, along with information about keyboard shortcuts and other features that will help you format your work.

  • Using Jupyter Notebook for Writing: This resource focuses on how to use Markdown to format your writing in a Jupyter notebook. Use this as a guide to manage the syntax of your writing, including making titles and subtitles and adding links.

  • The Jupyter Notebook Formatting Guide: This resource includes a wide variety of formatting options for Jupyter notebooks. You’ll learn about the basics as well as some more advanced options, like embedding PDF documents and videos. 

After you know how to apply basic formatting to your notebooks, you can start exploring more advanced options. 

Output formats in R Markdown

You can save this reading for future reference. Feel free to download a PDF version of this reading below:

Output formats available in RMarkdown.pdfPDF File
Open file

This reading will explore the different types of output formats you can produce with R Markdown. 

Setting the output of an R Markdown document

When working in RStudio, you can set the output of a document in R Markdown by changing the YAML header. 

For example, the following code creates an HTML document:

---

title: "Demo"

output: html_document

---

And the following code creates a PDF document:

---

title: "Demo"

output: pdf_document

---

The Knit button in the RStudio source editor renders a file to the first format listed in its output field (HTML is the default). You can render a file to additional formats by clicking the dropdown menu next to the knit button.

Available document outputs

In addition to the default HTML output (html_document), you can create other types of documents in R Markdown using the following output settings: 

  • pdf_document – This creates a PDF file with LaTeX (an open source document layout system). If you don’t already have LaTeX, RStudio will automatically prompt you to install it.

  • w​ord_document – This creates a Microsoft Word document (.docx).

  • o​dt_document – This creates an OpenDocument Text document (.odt).

  • r​tf_document – This creates a Rich Text Format document (.rtf).

  • m​d_document – This creates a Markdown document (which strictly conforms to the original Markdown specification)

  • g​ithub_document – This creates a GitHub document which is a customized version of a Markdown document designed for sharing on GitHub.

For a detailed guide to creating different types of R Markdown documents, check out the Documents chapter in R Markdown: The Definitive Guide

Notebooks

A notebook (html_notebook) is a variation on an HTML document (html_document). Overall, the output formats are similar; the main difference between them is that the rendered output of a notebook always includes an embedded copy of the source code.

Notebooks and HTML documents also have different purposes. HTML documents are good for communicating with stakeholders. Notebooks are better for collaborating with other data analysts or data scientists. 

To learn more, check out the section on Notebooks in the R Markdown documentation. 

Presentations

You can also use R Markdown to produce presentations. Automatically inserting the results of your R code into a presentation can save you lots of time. 

R Markdown renders files to specific presentation formats when you use the following output settings:

  • beamer_presentation – for PDF presentations with beamer

  • ioslides_presentation – for HTML presentations with ioslides

  • slidy_presentation – for HTML presentations with Slidy

  • powerpoint_presentation – for PowerPoint presentations

  • r​evealjs : : revealjs_presentation – for HTML presentations with reveal.js (a framework for creating HTML presentations that requires the reveal.js package)

 To learn more, check out the section on Slide Presentations in the R Markdown documentation. 

Dashboards

Dashboards are a useful way to quickly communicate a lot of information. The flexdashboard package lets you publish a group of related data visualizations as a dashboard. Flexdashboard also provides tools for creating sidebars, tabsets, value boxes, and gauges. 

To learn more, visit the flexdashboard for R page and the Dashboards section in the R Markdown documentation. 

Shiny

Shiny is an R package that lets you build interactive web apps using R code. You can embed your apps in R Markdown documents or host them on a webpage. 

To call Shiny code from an R Markdown document, add  runtime: shiny to the YAML header:

---

title: "Shiny Web App"

output: html_document

runtime: shiny

---

To learn more about Shiny and how to use R code to add interactive components to an R Markdown document, check out the Shiny tutorial from RStudio.  

Other formats

Other packages provide even more output formats:

  • The bookdown package is helpful for writing books and long-form articles.

  • The prettydoc package provides a range of attractive themes for R Markdown documents.

  • The rticles package provides templates for various journals and publishers.

 Visit the RStudio Formats page in the R Markdown documentation for a more comprehensive list of output formats and packages. 

Additional resources

F​or more information, check out these additional resources:

  • The R Markdown gallery from RStudio has tons of examples of the outputs you can create with R Markdown. 

  • The R Markdown Formats chapter in the R for Data Science book provides more details about the output formats introduced in this reading. This reading was compiled from information in this book.


Comments

Popular posts from this blog

Using BigQuery / MySQL / other SQL

SQL : PostgreSQL

About spreadsheet basics