Welcome to the world of Extending Excel with Python and R! In this book, we delve into the convergence of Excel with the powerful capabilities of Python and R, providing a comprehensive guide to leveraging these languages for data manipulation, analysis, visualization, and more.
Join us on this journey as we explore the intersection of Excel, R, and Python, empowering you to excel in today’s data-driven landscape.
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.
Before diving into this book, it’s helpful to have an intermediate understanding of either R or Python (or both), including intermediate-level proficiency in data manipulation and analysis using libraries such as pandas, NumPy, and the tidyverse. Familiarity with Excel basics, such as navigating spreadsheets and performing simple data manipulations, is also assumed. Additionally, a basic understanding of statistical concepts and data visualization techniques will be beneficial for following along with the examples and exercises presented throughout the book.
|
Software/hardware covered in the book |
Operating system requirements |
|
R |
Windows (for the VBA parts), macOS, or Linux (for all content excluding VBA) |
|
Python 3.11 |
|
|
Excel (including VBA) |
An installation guide for the relevant packages and tools will be provided in each chapter.
If you are using the digital version of this book, we advise you to type the code yourself or access the code from the book’s GitHub repository (a link is available in the next section). Doing so will help you avoid any potential errors related to the copying and pasting of code.
Disclaimer
The authors acknowledge the use of cutting-edge AI, such as ChatGPT, with the sole aim of enhancing the language and clarity within the book, thereby ensuring a smooth reading experience for readers. It's important to note that the content itself has been crafted by the authors and edited by a professional publishing team.
You can download the example code files for this book from GitHub at https://github.com/PacktPublishing/Extending-Excel-with-Python-and-R. If there’s an update to the code, it will be updated in the GitHub repository.
There are a number of text conventions used throughout this book.
Code in text: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: “The styledtables package can only be installed from GitHub via the devtools package.”
A block of code is set as follows:
install.packages("devtools")
# Install development version from GitHub
devtools::install_github(
'R-package/styledTables',
build_vignettes = TRUE
) Any command-line input or output is written as follows:
python –m pip install pywin32==306
Bold: Indicates a new term, an important word, or words that you see onscreen. For instance, words in menus or dialog boxes appear in bold. Here is an example: “Before running this code, you can ensure that iris_data.xlsm has the macro by going to Developer | Macros (or Visual Basic) to see whether the macro exists.”
Tips or important notes
Appear like this.
Feedback from our readers is always welcome.
General feedback: If you have questions about any aspect of this book, email us at customercare@packtpub.com and mention the book title in the subject of your message.
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata and fill in the form.
Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at copyright@packt.com with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Thanks for purchasing this book!
Do you like to read on the go but are unable to carry your print books everywhere?
Is your eBook purchase not compatible with the device of your choice?
Don’t worry, now with every Packt book you get a DRM-free PDF version of that book at no cost.
Read anywhere, any place, on any device. Search, copy, and paste code from your favorite technical books directly into your application.
The perks don’t stop there, you can get exclusive access to discounts, newsletters, and great free content in your inbox daily
Follow these simple steps to get the benefits:
https://packt.link/free-ebook/9781804610695
© 2024 Packt Publishing Limited All Rights Reserved
