What this book covers
Chapter 1, Reading Excel Spreadsheets, delves into importing data from Excel into R/Python. You will begin by importing your first Excel sheet into R, navigating Excel file intricacies, and then conclude with a Python counterpart.
Chapter 2, Writing Excel Spreadsheets, explains how, after analyzing data in R/Python, it’s essential to communicate findings effectively with Excel users. This chapter provides insights into creating Excel sheets from R/Python and exporting analysis results.
Chapter 3, Executing VBA Code from R and Python, explores how, next to writing the results out to Excel, you might want to add VBA macros and functions to the resulting Excel sheet to further empower the end users of the analysis results. We can do this in this chapter.
Chapter 4, Automating Further – Task Scheduling and Email, covers how we have R packages such as RDCOMClient, which will work with Outlook, and Blastula, which can help in automating the analysis and emailing of reports in R. In Python, the smtplib package serves the same purpose.
Chapter 5, Formatting Your Excel Sheet, discusses how packages can help with creating sheets and tables along with formatted data in Excel, and how to use them to create beautiful Excel reports.
Chapter 6, Inserting ggplot2/matplotlib Graphs, shows how to create graphics from ggplot2 and matplotlib. There are ggplot2 themes a user can use as well, along with others to create beautiful graphics in R/Python and place them in Excel.
Chapter 7, Pivot Tables and Summary Tables, explores the world of pivot tables in Excel using R and Python. Learn how to create and manipulate pivot tables directly from R/Python for seamless interaction with Excel.
Chapter 8, Exploratory Data Analysis with R and Python, explains how to pull data in from Excel and perform Exploratory Data Analysis (EDA) with various packages, such as {skimr} for R and pandas and ppscore for Python.
Chapter 9, Statistical Analysis: Linear and Logistic Regression, teaches you how to perform simple statistical analysis with linear and logistic regression in R and Python on Excel data.
Chapter 10, Time Series Analysis: Statistics, Plots, and Forecasting, explains how to perform simple time series analysis using the forecast package in R, and kats and long short-term memory (LSTM) in Python.
Chapter 11, Calling R/Python Locally from Excel Directly or via an API, calls R and Python from Excel locally and via an API. This chapter also covers the open source tools for calling a local R/Python installation from Excel using BERT and xlwings, as well as open source and commercial API solutions.
Chapter 12, Data Analysis and Visualization with R and Python in Excel – A Case Study, presents a case study of performing data visualization and machine learning in Excel by calling R or Python.
