r-powered-excel.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To interface with Excel\n",
    "library(openxlsx)\n",
    "\n",
    "# For data manipulation & visualization\n",
    "library(tidyverse)\n",
    "\n",
    "# For \"tidying\" regression output\n",
    "library(broom)\n",
    "\n",
    "# Borrow a tidyverse dataset\n",
    "head(diamonds)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The `data` worksheet\n",
    "\n",
    "First we'll set up objects referring to our workbook and worksheet, respectively. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add workbook\n",
    "wb <- createWorkbook()\n",
    "\n",
    "# Add worksheet of raw data\n",
    "data_sheet <- addWorksheet(wb, sheetName = 'data')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we'll use the `writeDataTable()` function to write `diamonds` to this worksheet as an Excel table. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exclude row names\n",
    "writeDataTable(wb, sheet = data_sheet, x = diamonds, rowNames = FALSE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The `analysis` worksheet\n",
    "\n",
    "I would also like to include an `analysis` worksheet including regression results and a scatterplot of the relationship between the log of `price` and the log of `carat`. \n",
    "\n",
    "We can set up a new worksheet, add the results of the regression with `writeData()` and add the scatterplot with `writePlot()` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set up a new worksheet \n",
    "analysis_sheet <- addWorksheet(wb, sheetName = 'analysis')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Untransformed relationship\n",
    "ggplot(data = diamonds, aes(x = carat, y = price)) +\n",
    "    geom_point() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Log transformations\n",
    "diamonds <- diamonds %>% \n",
    "  mutate(carat_log = log(carat)) %>% \n",
    "  mutate(price_log = log(price))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transformed relationship\n",
    "ggplot(data = diamonds, aes(x = carat_log, y = price_log)) +\n",
    "    geom_point() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Linear regression -- price \"by\" carat\n",
    "diamonds_lm <- lm(price ~ carat, data = diamonds)\n",
    "\n",
    "# Clean up the regression output\n",
    "diamonds_lm_tidy <- tidy(diamonds_lm)\n",
    "diamonds_lm_tidy\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add the table to this worksheet\n",
    "writeDataTable(wb, sheet = analysis_sheet, x = diamonds_lm_tidy, rowNames = FALSE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Visualize the results with regression trendline\n",
    "ggplot(data = diamonds, aes(x = carat_log, y = price_log)) +\n",
    "    geom_point() +\n",
    "    geom_smooth(method = 'lm')\n",
    "\n",
    "\n",
    "# Add the image to the worksheet, set the placement\n",
    "insertPlot(wb, sheet = analysis_sheet, startRow = 5, startCol = \"A\", width = 4, height = 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Admire our work, part 1\n",
    "\n",
    "OK, let's save and view our work. By default, `saveWorkbook()` will put the file in our working directory.\n",
    "\n",
    "Let's take a look at the finished output in Excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "diamonds_report = 'output/diamonds-report.xlsx'\n",
    "\n",
    "# save workbook to working directory\n",
    "saveWorkbook(wb = wb, file = diamonds_report, overwrite = TRUE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's pretty good! But as a matter of fact I would like to do a few more things to the workbook:\n",
    "\n",
    "- Freeze panes\n",
    "- Format the `price` as currency in the `data` worksheet. \n",
    "\n",
    "Let's give it a go:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Freeze panes\n",
    "\n",
    "for (sheet in getSheetNames(diamonds_report)){\n",
    "    freezePane(wb, sheet = sheet, firstRow = TRUE)\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set currency style to price column\n",
    "\n",
    "currency <- createStyle(numFmt = \"$#,##0\")\n",
    "addStyle(wb, sheet = data_sheet, style = currency, cols = which(colnames(diamonds) == 'price'), 2:(nrow(mpg) + 1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's try this again!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saveWorkbook(wb = wb, file = diamonds_report, overwrite = TRUE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# See you back at the slides for the conclusion"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.0.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}