Extending Excel with Python and R: Unlock the potential of analytics languages for advanced data manipulation and visualization

Extending Excel with Python and R
Contributors
About the authors
About the reviewers
Preface
   Who this book is for
   What this book covers
   To get the most out of this book
   Download the example code files
   Conventions used
   Get in touch
   Share Your Thoughts
   Download a free PDF copy of this book
Part 1:The Basics – Reading and Writing Excel Files from R and Python
1
Reading Excel Spreadsheets
   Technical requirements
   Working with R packages for Excel manipulation
   Reading Excel files to R
      Installing and loading libraries
   Reading multiple sheets with readxl and a custom function
   Python packages for Excel manipulation
      Python packages for Excel manipulation
      Considerations
   Opening an Excel sheet from Python and reading the data
      Using pandas
      Using openpyxl
   Reading in multiple sheets with Python (openpyxl and custom functions)
      The importance of reading multiple sheets
      Using openpyxl to access sheets
      Reading data from each sheet
      Retrieving sheet data with openpyxl
      Combining data from multiple sheets
      Custom function for reading multiple sheets
      Customizing the code
   Summary
2
Writing Excel Spreadsheets
   Technical requirements
   Packages to write into Excel files
      writexl
      openxlsx
      xlsx
      A comprehensive recap and insights
   Creating and manipulating Excel sheets using Python
      Why export data to Excel?
   Keeping it simple – exporting data to Excel with pandas
   Advanced mode – openpyxl for Excel manipulation
      Creating a new workbook
      Adding sheets to the workbook
      Deleting a sheet
      Manipulating an existing workbook
   Choosing between openpyxl and pandas
   Other alternatives
   Summary
3
Executing VBA Code from R and Python
   Technical requirements
   Installing and explaining the RDCOMClient R library
      Installing RDCOMClient
   Executing sample VBA with RDCOMClient
   Integrating VBA with Python using pywin32
      Why execute VBA code from Python?
      Setting up the environment
      Error handling with the environment setup
      Writing and executing VBA code
   Automating Excel tasks
      Pros and cons of executing VBA from Python
   Summary
4
Automating Further – Task Scheduling and Email
   Technical requirements
   Installing and understanding the tasksheduleR library
      Creating sample scripts
   RDCOMClient for Outlook
   Using the Microsoft365R and blastula packages
      Microsoft365R
      The blastula package
   Scheduling Python scripts
      Introduction to Python script scheduling
      Built-in scheduling options
      Third-party scheduling libraries
      Best practices and considerations for robust automation
   Email notifications and automation with Python
      Introduction to email notifications in Python
      Setting up email services
      Sending basic emails
      Sending email notifications for script status
   Summary
Part 2: Making It Pretty – Formatting, Graphs, and More
5
Formatting Your Excel Sheet
   Technical requirements
   Installing and using styledTables in R
      Installing and using basictabler in R
   Advanced options for formatting with Python
      Cell formatting
      Conditional formatting
      Pivot tables
   Summary
6
Inserting ggplot2/matplotlib Graphs
   Technical requirements
   Some basics
      Visualizing data with ggplot2
      Visualizing data with the cowplot package
      Bar charts and dumbbell plots
   Enhancing your Excel reports with plotnine2, matplotlib, and plotly graphs
   Enhancing Excel reports with visualizations
   An introduction to data visualization libraries
      Plotnine – elegant grammar of graphics
      Plotly – interactive visualizations
      Matplotlib – classic and customizable plots
   Creating graphs with plotnine (Python’s ggplot2)
      Understanding the grammar of graphics
      Generating various plot types
      Customizing the visual elements of a plotnine plot
      Incorporating additional layers
      Generating graphs with matplotlib
      Customizing visual elements of a matplotlib plot
   Other visualization libraries
      plotly
      seaborn
   Embedding visualizations into Excel
      A basic embedding process
   Summary
7
Pivot Tables and Summary Tables
   Technical requirements
   Making a table with the Base R xtabs function
   Making a table with the gt package
   Creating pivot tables with tidyquant
   Creating and managing pivot tables in Python with win32com and pypiwin32
   Creating pivot tables with Python: the basics
      Setting up the Python environment
      Creating pivot tables
      Manipulating pivot tables
      Groupings in pivot tables
   Summary
Part 3: EDA, Statistical Analysis, and Time Series Analysis
8
Exploratory Data Analysis with R and Python
   Technical requirements
   Understanding data with skimr
   Using the GGally package in R
   Using the DataExplorer package
   Getting started with EDA for Python
   Data cleaning in Python for Excel data
      Handling missing data
      Dealing with duplicates
      Handling data type conversion
      Excel-specific data issues
   Performing EDA in Python
      Summary statistics
      Data distribution
      Associations between variables
      Scatter plots
      Visualizing key attributes
   Summary
9
Statistical Analysis: Linear and Logistic Regression
   Technical requirements
   Linear regression
   Logistic regression
      Frameworks
   Performing linear regression in R
      Linear regression in base R
      Linear regression with tidymodels and purrr
   Performing logistic regression in R
      Logistic regression with base R
      Performing logistic regression using tidymodels
   Performing linear regression in Python using Excel data
   Logistic regression in Python using Excel data
   Summary
10
Time Series Analysis: Statistics, Plots, and Forecasting
   Technical requirements
   Generating random time series objects in R
      Manipulating the time series parameters
   Time series plotting
      Creating ACF and PACF plots in R
   Auto ARIMA modeling with healthyR.ts
   Creating a Brownian motion with healthyR.ts
   Time series analysis in Python – statistics, plots, and forecasting
   Time series plotting – basic plots and ACF/PACF plots
      Autocorrelation function (ACF) plot
      Partial autocorrelation function (PACF) plot
   Time series statistics and statistical forecasting
      Statistical analysis for time series data
   Understanding predictive modeling approaches
      Forecasting with statsmodels
      Time series forecasting with Facebook’s prophet
   Time series forecasting with deep learning – LSTM
   Summary
Part 4: The Other Way Around – Calling R and Python from Excel
11
Calling R/Python Locally from Excel Directly or via an API
   Technical requirements
   Calling R and Python from Excel locally
   Why you would want to call R/Python from Excel locally
   Setting up an environment
      Steps to set up BERT for R
      Steps to set up xlwings for Python
   Calling R/Python directly from Excel
      Executing R with VBA and BERT
      Interacting with Excel via BERT
   Calling Python from Excel using xlwings
      The Run button
      Macros
      UDFs
   Calling R and Python from Excel via an API
   An introduction to APIs
   Open source solutions for exposing R as API endpoints
   Open-source solutions for exposing Python as an API endpoint
   Calling APIs from Excel VBA
      Commercial API solutions for R and Python
   Summary
Part 5: Data Analysis and Visualization with R and Python for Excel Data – A Case Study
12
Data Analysis and Visualization with R and Python in Excel – A Case Study
   Technical requirements
   Getting visualizations with R
      Getting the data
      Visualizing the data
   Performing a simple ML model with R
      Data preprocessing
   Getting visualizations with Python
      Getting the data
      Visualizing the data
   Performing a simple ML model with Python
      Data preprocessing
Index
   Why subscribe?
Other Books You May Enjoy
   Packt is searching for authors like you
   Share Your Thoughts
   Download a free PDF copy of this book