Here’s the good news: this is exactly where generative AI shines. If you’re somewhat technical and already familiar with the business context and the problem you’re tackling, generative AI can give you that essential jump-start with VBA.

However, as of now, Copilot in Excel doesn’t quite offer the same effortless automation here that you might get with calculated columns or PivotTables. It occasionally falls short of fully automating VBA tasks.

That’s why, in this post, I’ll walk you through some straightforward workflows to effectively use Excel Copilot when working with VBA. You can follow along using the provided exercise file. Make sure you’ve uploaded it to your OneDrive so Copilot can access it.

We’ve got a workbook containing a small dataset on our sales staff, and we’d like to test-drive Excel Copilot to help automate some tasks and build handy VBA macros.

The worksheet has some mysterious blank rows that keep popping up…. unfortunately, a recurring headache. How about we create a quick macro to tidy this up instantly? Let’s fire up Copilot and see how it can help.

One quick reminder: although Copilot might seem limited to your current dataset, that’s not actually true. As a large language model, Copilot is trained on a massive amount of information and can tackle broader tasks too. Check out my earlier blog post on using Excel Copilot for tasks beyond spreadsheets:

I’ll ask Copilot if it can generate a macro for me to quickly remove all blank rows from the worksheet, and it’ll return something along these lines:

Unlike some of Excel’s other features, Copilot can’t execute and automate this macro on its own. We’ll have to manually add and run the macro ourselves.

It’s not all bad news, though. One nice thing is that Copilot can easily build upon and refine this macro based on further prompts. For instance, if we ask it to add comments explaining the code, it’ll do exactly that:

On the bright side, Copilot actually can “see” your worksheet data and directly reference it in macros, which is pretty cool! Let’s give it a shot. I’ll ask Copilot to create a macro that directly interacts with our data:

“Create a macro that generates a separate workbook for each distinct value in the region column, naming each workbook according to its corresponding region.”

Copilot will provide us with some code that we can then copy and paste into our VBA editor, and then see how well it works… or doesn’t.

When I paste this into the editor, I immediately notice some code highlighted in red. If you’ve spent any time working with code editors, you know seeing red usually isn’t good news. Between the potential formatting issues when copying and pasting and Excel Copilot’s limitations as a prompting environment, we’re bound to hit some bumps. So what’s our next move? How do we work around this?

This is exactly the kind of situation where using Copilot 365 is typically the better approach. Here, you’ll get a dedicated Gen AI prompt environment. It’s similar to ChatGPT, but with the added advantage of being able to securely upload, reference, and sandbox your files within your organization’s walls, eliminating the risk of data leaks.

If you’re new to Copilot 365, check out my post on how to use this tool as your personal Excel tutor:

To get started, head over to the Work mode in 365 Copilot and paste your prompt there. Sometimes you’ll get a solid answer even without referencing your dataset directly. But for greater precision and confidence, it’s best to refer explicitly to your actual data. You can easily do this by clicking the + icon to add content to your prompt:

After you’ve found and selected your file from your OneDrive folder, your prompt will look something like this:

Since 365 Copilot offers a more robust and general-purpose Gen AI interface, you’ll typically receive responses that are clearer, better structured, and easier to use. For instance, your VBA macro will be neatly presented in a highlighted code snippet, making it straightforward to copy directly to your clipboard and paste into the VBA editor. You’ll still need to manually add and run the macro yourself, though. (Don’t trust Copilot if it claims it automatically added it to your workbook! In my experience, that rarely ends well.)

So now, taking the code that 365 Copilot generated, let’s return to the VBA editor. This time, everything looks clean—no red errors—and the macro runs perfectly.

Even if you still encounter some errors, you’re in a much better place to troubleshoot here compared to Excel’s somewhat fragile Copilot interface.

If you think you’ll want to refer back to this workbook and continue building on your macros down the road, I’d recommend setting up a Copilot 365 Notebook. You can learn more about how to do that here:

If this seems like a one-off task and you’d prefer not to deal with the hassle of uploading the workbook into Copilot 365, you can always copy the macro generated in Excel Copilot and paste it directly into 365 Copilot. Doing this gives you the benefits of a more powerful, reliable environment and helps you skip some of the earlier steps.

Now, full disclosure: if you’re a seasoned VBA developer, you might roll your eyes a bit at the code quality here. Generative AI tends to be pretty verbose and redundant, and this code is no exception. If you’re aiming for something highly scalable and performant, you’ll likely need deeper VBA expertise.

While generative AI can assist with some of this, the examples here are mainly intended for quick, ad-hoc tasks rather than major development projects. But hey (sorry not sorry, purists) not everything needs to be production-grade to help people get their work done.

In this post, we’ve explored a few straightforward approaches to getting help with VBA macros using Copilot for Excel. While the process isn’t always seamless or intuitive, it’s still an invaluable resource, particularly if you’re stepping into VBA occasionally or for quick tasks. Plus, your data stays secure, since nothing you share with Copilot leaves your organization’s boundaries.

What questions do you have about using Copilot for VBA macros or Excel Copilot in general? Let me know in the comments.

The post Copilot for Excel: How to work with VBA macros first appeared on Stringfest Analytics.

One such technique is using console.log(), a simple yet powerful tool that allows you to track what’s happening inside your scripts in real-time. In this blog post, we’ll explore how to use console.log() for debugging, with a particular focus on its application within loops.

Basic usage of console.log()

Here’s a straightforward example of how console.log() can be utilized in an Office Script. Go ahead and insert this code into a new Script and run it to see what happens:

In this script, console.log() is used to log the start of the script and the name of the active worksheet. This helps confirm that the script is running and interacting with the correct sheet. You will notice that the Output log at the bottom of your Office Scripts code editor will print the results of console.log():

Using console.log() to debug

While console.log() can indeed be used, as shown in the previous example, to print outputs and provide a basic understanding of what’s happening in the script, it can also be a powerful tool for debugging. This function is particularly useful within loops, where it can be invaluable for monitoring iterations and inspecting changes in data or the flow of logic.

To get started, let’s say you want to log numbers from 1 to 5. Here’s how you might do it:

This script will log each number in the loop, helping you verify that the loop iterates the correct number of times and that the correct numbers are being produced.

Now let’s move onto a similar example using actual data from your Excel workbook. In this case, we want the script to sum the values in cells A1 to A5. I have created the script below, and I run it with console.log() to display the results. However, the results are clearly incorrect: It appears that the numbers are being concatenated rather than added.

Let’s add a console.log() inside the loop to figure out what’s happening. I will insert the statement from within the loop to print out exactly what is being added and what the intermediate results are.

From this, we can see that the loop is indeed just appending all the items together and not actually adding them. It appears there’s some kind of indexing issue here.

After some digging (here’s looking at you, gen AI!), I figured out the necessary change in the code (we’ll discuss the specifics shortly). I kept the console.log() statement to ensure the intermediate results make sense. This time, it seems to be performing a cumulative sum and giving me the correct result—amazing!

What caused the error?

At this point in your Office Scripts journey, it might be challenging to understand what exactly caused the differences in results. If that’s the case, don’t worry—it’s a perfect opportunity to deepen your understanding of how Office Scripts handles data, particularly the use of arrays.

If you’re ready to dive deeper, here’s a good example. But if you’re not up for it right now, that’s OK too, as long as you recognize the importance of using console.log() for debugging and displaying results!

The key difference between the two scripts is how they access cell values from the values array, which impacts how the numbers are added together.

In the first script:

let value = values[i]; // Correctly access the value
sum += value; // Add the value to the sum

The line let value = values[i]; is not correctly accessing the cell value. The getValues() function returns a two-dimensional array, even if the range is a single column or row. Therefore, values[i] is still an array, not a single number. This means when you attempt to add value to sum using sum += value;, JavaScript treats value as an array object, leading to unexpected behavior such as concatenation.

In the second script:

let value = values[i][0]; // Correctly access the value
sum += value; // Add the value to the sum

Here, let value = values[i][0]; correctly accesses the first element of the array values[i], which is the actual numeric value of the cell. This allows the script to correctly add up the numbers using sum += value;.

In summary, the first script fails to properly handle the two-dimensional nature of the array returned by getValues(), while the second script correctly accesses the individual cell values and successfully computes the sum.

Removing unnecessary console.log() statements

While console.log() can be extremely helpful for debugging, it’s a good idea to remove any instances used for testing to streamline the output and make it clearer for others to use. This is particularly important when console.log() statements are used in loops, as printing at each iteration can significantly bog down performance.

Conclusion

Debugging with console.log() in Office Scripts can illuminate the inner workings of your scripts, especially within loops where tracking data flow and iteration is crucial. By mastering these techniques, you can ensure your scripts are efficient and error-free, ultimately saving time and reducing frustration in your Excel tasks. Do you have any specific questions about debugging Office Scripts, or about Office Scripts in general? Please let me know in the comments.

The post How to understand console.log() in Office Scripts for Excel first appeared on Stringfest Analytics.

The Basics: JavaScript

JavaScript is a programming language originally designed to make web pages more interactive. Over the years, it has become one of the most widely used programming languages in the world. If you’ve ever interacted with a website that updates dynamically without needing to reload the page, chances are that JavaScript was at work behind the scenes.

In essence, JavaScript instructs the computer on what actions to perform. For instance, it can be used to validate form inputs on a web page, create animations, or fetch data from servers without reloading the page. For an Excel user, think of JavaScript as a way to automate actions that would otherwise be manual and time-consuming.

Enter TypeScript

While JavaScript is incredibly powerful, it can sometimes be difficult to manage, especially as your scripts become more complex. This is where TypeScript comes in. TypeScript is a language built on top of JavaScript, which means it includes everything JavaScript does but adds some useful features. The most important of these features is static typing.

Static typing allows you to define the types of variables and functions in your code, which helps catch errors early on. For example, if you declare that a function should only accept numbers as input, TypeScript will alert you if you accidentally try to pass a text string instead. This makes your code more reliable and easier to debug.

For someone new to coding, think of TypeScript as a version of JavaScript that helps you avoid common mistakes and makes your scripts easier to read and understand. It’s like having an extra set of eyes looking over your shoulder, ensuring that you’re doing things correctly.

Bridging the Gap with Office.js

Now, let’s talk about Office.js. Office.js is a library, or a set of tools, that allows you to use JavaScript (or TypeScript) to interact with Microsoft Office applications like Excel, Word, Outlook, and PowerPoint. It serves as a bridge between your code and the Office applications, providing a set of APIs (Application Programming Interfaces) that you can use to manipulate Office documents programmatically.

With Office.js, you can create Office add-ins, which are essentially web apps that run within Office applications. These add-ins can extend the functionality of Office applications, automate repetitive tasks, and integrate with other services and data sources. For instance, you can build an add-in that fetches data from an external database and populates your Excel spreadsheet, or an add-in that sends automated emails through Outlook based on specific triggers.

Simplifying Automation with Office Scripts

While Office.js is incredibly powerful, it can also be complex and requires a good understanding of JavaScript or TypeScript. This is where Office Scripts come in. Office Scripts provide a simplified scripting environment that makes it easy for you to automate tasks in Excel without needing to become a full-fledged programmer.

Office Scripts serve as an accessible layer on top of Office.js, designed to simplify the process of writing and executing scripts. They are written in a streamlined version of TypeScript, which retains the core advantages of TypeScript—such as static typing and enhanced error checking—while stripping away much of the complexity that can be daunting for beginners. This makes it possible for users who are not professional developers to still create powerful scripts that can automate repetitive tasks, manipulate data, and customize Excel functionality to better meet their needs.

The scripting environment in Office Scripts is intentionally user-friendly, providing an intuitive interface that guides users through the process of writing and running scripts. It offers immediate feedback on errors and results, facilitating a smoother learning curve. This real-time feedback loop is particularly valuable for those new to programming, as it allows them to experiment and see the outcomes of their code instantly, thereby accelerating the learning process.

By lowering the barrier to entry, Office Scripts enable a broader range of users to leverage the full power of Excel automation. Whether you are a business analyst looking to automate data processing tasks, a financial professional seeking to streamline reporting processes, or an administrative assistant aiming to reduce the manual effort in managing spreadsheets, Office Scripts provide the tools you need to enhance productivity without requiring extensive programming knowledge. This democratization of automation tools empowers users across various domains to become more efficient and effective in their roles, transforming how tasks are approached and completed in Excel.

Understanding the Lineage

By taking the time to understand how JavaScript, TypeScript, and Office.js contribute to the functionality of Office Scripts, you gain a deeper appreciation for the technology at your fingertips. This knowledge not only helps you use Office Scripts more effectively but also opens the door to exploring more advanced coding techniques and possibilities in the future.

Office Scripts are your gateway to harnessing the power of coding in Excel. They simplify the complex world of JavaScript and TypeScript, providing a user-friendly way to automate tasks and improve productivity. By understanding their roots and how they fit into the broader ecosystem of programming languages and tools, you can confidently start your journey into coding, enhancing your Excel skills and expanding what you can achieve with this powerful tool.

What questions do you have about the lineage from JavaScript to Office Scripts? Let me know in the comments.

The post How to understand the relationships between JavaScript, TypeScript, Office.js and Office Scripts first appeared on Stringfest Analytics.

Except… not every program indexes the same way. In particular, there is a difference in how to count the element in the first position. Let me explaining using Excel, then an everyday computing example:

How Excel does it

Indexing can be done in Excel with — go figure — the INDEX() function:

INDEX(array, row_num, [column_num])

Let’s take a look at how this plays out in both one and two dimensions.

One-dimensional

By one-dimensional I mean either a row or column of data. I will operate on a named range in the following example:

To get the third item, we pass 3 into the function.

Two-dimensional

By two-dimensional I mean an object with rows and columns. This will work similarly, just an extra argument. Note that I have stored the data in a named table — a good practice for any two-dimensional data.

Another way to count

So far, pretty intuitive. When you want to access the element you want, you start counting from one and that’s the index position. This is an example of one-based indexing. One-based indexing makes a lot of sense because as humans, we tend to start counting at one.

But computers don’t always start counting at one. Instead, they often start counting at zero. This is called (you guessed it) zero-based indexing. This may sound pretty foreign, but I’d like to show you an example of this you’ve probably seen before.

Imagine being so excited to get your hands on a dataset like this that you click “download” several times. You download folder will look something like this:

Did you notice that the second dataset is actually called dataset (1)? The first dataset is just dataset… well, zero. This is zero-based indexing, and it happens all over computing, including Python.

How Python does it

To learn more about how to index in one and two dimensions in Python, check out the below Jupyter Notebook.

Computer programmers can have strong opinions about zero- versus one-based indexing, but you should be comfortable working with both: as you’ve seen, Excel is one-based, as is R, but Python and JavaScript, among others, are zero-based.

Want to keep counting?

If you’d like to learn more about Python, including indexing and pandas, with the specific needs of an Excel user in mind, check out my book Advancing into Analytics: From Excel to Python and R

The post Zero-based indexing: What it is and when you’ve seen it before first appeared on Stringfest Analytics.

This post lays out the basics of Jupyter, how to navigate the interface, and how to execute Python code with it.

What are Jupyter notebooks?

Jupyter is a web-based application used to create interactive files containing code, text and other assets. It is developed and maintained by the open source Project Jupyter. Initially conceived as a more interactive way to work with Python code, the project has since been expanded to scientific computing more generally.

How do you run then?

Python and Jupyter are two different things: the code and an interface for executing the code, respectively. You can download them both, along with some other goodies, with Anaconda Individual, a free install.

To learn more about installing and navigating Jupyter from your computer, check out my book Advancing into Analytics.

Alternatively, you can run a session of Jupyter on the cloud for free here. It will take a moment or two to load; eventually you should see a “Home Page” listing the contents of the repository. Create a new Jupyter notebook here by selecting New > Python 3:

You will now see a blank Jupyter notebook; now let’s review its four elements.

The elements of Jupyter

1. Notebook name

This area displays the notebook name. The file extension for any Jupyter notebook is ipynb, or IPython Notebook. To rename the notebook, you can click and type right over this area.

2. Menu bar

Like most applications, Jupyter contains a menu bar containing some helpful features. A couple particularly to be aware of:

File

Here you can duplicate and create new notebooks. You don’t have to worry so much about saving, as by default Jupyter notebooks are autosaved every two minutes. You can create checkpoints if there are points in your work that you’d like to save a copy of. You can also download a copy of your work in several common file formats such as Markdown (md) or Python scripts (py).

Kernel

The kernel is what Jupyter uses “under the hood” to execute and return the Python code. To be more precise, Jupyter offers a kernel in many programming languages, including JavaScript, SAS and R. (Fun fact: Jupyter is a portmanteau of the Julia, Python and R languages).

If you’re having problems with your notebook, restarting the kernel is similar to restarting your computer — hackneyed advice, but it often works. Be careful, though: once you restart the kernel you’ll lose everything in memory during the session, such as any datasets you’ve imported.

3. Toolbar

This is equivalent to Excel’s Quick Access Toolbar and contains icons for common operations; most of these have to do with cells which are the predominant area of the notebook.

4. Cells

The content of Jupyter notebooks are arranged in modular cells which can be added, deleted and rearranged. Four cell types are offered; we’ll focus on the two most common: Markdown and Code.

Markdown

To make your first cell Markdown, click the dropdown in the menu bar and select Markdown. This is a plain-text markup language; to learn more about it head to the menu and Help > Markdown.

Now, type or paste the following text into your Markdown block:

# Big Header 1
## Smaller Header 2
### Even smaller headers
#### Still more
*Using one asterisk renders italics*
**Using two asterisks renders bold**
- Use dashes to...
- Make bullet lists
Refer to code without running it as `fixed-width text`

Looks pretty weird, right? Just wait a second — you can render this Markdown by selecting Run from the menu. It should now look like this:

The rendered text wouldn’t look out of place in a word processing doc. With Markdown you can even include photos, hyperlinks and more. Markdown blocks also render HTML for even more text customization. This blend of text and other assets with code is one of Jupyter’s most celebrated features. But you’re probably not here to get a lesson in Markdown but instead to learn the basics of working with Python in a notebook.

Code

When you ran the Markdown cell, a new cell was inserted immediately underneath. Change the menu dropdown to Code; let’s look into the basics of running Python code in Jupyter (from, well, Jupyter):

Jupyter notebooks provide a powerful and appealing medium for working with code, which is often (but not always) Python. This post gave you just enough know-how to work with both.

For more, I encourage you to kick the tires on your Jupyter notebook (You’re not going to break anything… you can even ransack the cloud-based link I gave you, no consequences!). In particular, check out the Help menu for links to various Python tutorials and an interactive tour of the Jupyter interface. Jupyter also contains an abundance of keyboard shortcuts — you can view them all from a window off the Help menu.

One could easily write a book about working with Python and Jupyter… wait for it…

Keep learning about Python & Jupyter notebooks… get your copy of my book Advancing into Analytics: From Excel to Python and R.

The post A tour of Python + Jupyter Notebooks first appeared on Stringfest Analytics.

How it works

There are no rubrics or packages needed to teach coding with pair programming. This is a practice that comes from software development where two individuals work together on coding. They trade off on roles, which are:

The Driver

The driver is the one “behind the wheel,” or the keyboard as it were. The driver pushes ahead toward the destination, writing, running and inspecting the code.

The Navigator

We’re mostly used to the “driver” role because that’s what we do when we work by ourselves, right? We push ahead toward our goals.

What makes pair programming different is the presence of a navigator. This person is in co-pilot position, making sure the driver stays on a good course. They help the driver make adjustments to the route mid-journey. This of this as a real-time code review.

Each owns the project

The driver and navigator are equally in charge of the output! Neither role is more important. The driver and navigator also changes places, perhaps trading off on days or activities while learning.

The advantages

Pair programming forces students to think out loud about their work. It also helps students give and receive feedback, which is a critical skill for data analytics, much like most other fields.

The disadvantages

Bringing up the phrase “pair programming” may elicit eye rolls from seasoned programmers, as this Dilbert strip illustrates.

Many programmers find the practice stifling and exhausting. And then there are the personality clashes: if you’ve ever got lost on a road trip with a friend and bickered over what to do… well, imagine that but with code.

These weaknesses can transfer into education as well: students who prefer to work independently may resent pair programming, and some students may struggle to adopt the collegial environment needed for successful pairing.

For these reasons, pair programming may be best used as an occassional aide. That said, these pain points are often the rule, not the exception, in real-life data projects, so the early exposure and practice dealing is not a bad idea.

Read more about teaching coding and data education

To find more presentations on teaching coding, check out my data analytics education resource library. Subscribe below for exclusive reader access.

The post Teaching coding: what is pair programming? first appeared on Stringfest Analytics.

Scaffolding helps you reach the top of your builds with ease. Just keep stacking them as you go! It’s also handy for avoiding fall damage.

Minecraft Wiki

Scaffolding can be used to similar effect when teaching coding, in an exercise called the “faded example.”

Fill-in-the-code

Imagine a faded example as a sort of fill-in-the-blank challenge, but with code. You can start students with a nearly completed block of code, but ask them to fill in some blanks.

Over time, you can ask for more and more blanks until the student is essentially coding the whole problem. This arrangement is often known as “scaffolding.”

The benefits of faded examples

This approach helps students work from example to example, learning progressively more about the code as they advance. It introduces an element of interactivity and debugging into the exercise in a way that starting off a blank file does not.

Faded example demo

The below is a greatly simplified example of a faded example done in the R programming language. Here, you are asked to call some basic functions on the iris data frame.

Notice how you go from some “scaffolding” in the first question, to none in the next.

This pattern of practicing code will be immediately familiar to you if you've ever taken a course at DataCamp. (Disclosure: I am the developer of the DataCamp course, Survey and Measurement Development in R.)

Learning how to teach code

From this example you should see that it takes more to teaching code than giving students a blank text file and asking them to start typing. There's lots of cognitive science and technology at work.

For more presentations and ideas of how to teach coding and data analytics, subscribe to my resource library.

The post Teaching coding: What is a faded example? first appeared on Stringfest Analytics.

When I was an undergrad, a professor suggested I learn this statistical programming language called R.

I took one look at the interface, panicked, and left.

A lot has changed in the R world since then, not the least of which was the release of the RStudio integrated development environment. While the universe of R packages continues to grow, and the work can now be done from the comfort of RStudio, the fact remains: learning R means learning to code R.

Many of my students have never coded before, although this is a half-truth: they’ve probably used Excel, which requires a decent amount of functions and references. What Excel doesn’t require, though, is naming and manipulating variables.

R is an ideal choice for first-time data coders: the familiar tabular data frame is a core structure. Operations are designed with data analysis in mind: after all, R is a statistical programming language. (In my opinion, this makes it preferred to Python, which was designed as a general-purpose scripting language — again, as far as learning to code as a data analyst goes.)

I assume no prior coding language for this workshop. My goals are to equip students to work comfortably from the RStudio environment, ingest and explore data, and make simple graphical representations of data. In particular, students will perform the most common tabular data cleaning and exploration tasks using the dplyr library.

Above all these objectives, however, is my goal to help students not panic over learning R, like I did when I started.

You are welcome to use this learning guide as you see fit.

1: Welcome to the R Project

Objective: Student can install and load an R package

Description:

• What is R and when would I use it?
• R plus RStudio
• Installing and loading packages

Exercise: Install a CRAN task view

Assets needed: None

Time: 35 minutes

Lesson 2: Introduction to RStudio

Objective: Student can navigate the RStudio integrated development environment

Description:

• Basic arithmetic and comparison operations
• Saving, closing and loading scripts
• Opening help documentation
• Plotting graphs
• Assigning objects

Exercises: Practice assigning and removing objects

Assets needed: None

Time: 40 minutes

Lesson 3: Working with vectors

Objective: Student can create, inspect and modify vectors

Description:

• Creating vectors
• Vector operations
• Indexing elements of a vector

Exercises: Drills

Assets needed: None

Time: 35 minutes

Lesson 4: Working with data frames

Objective: Student can create, inspect and modify data frames

Description:

• Creating a data frame
• Data frame operations
• Indexing data frames
• Column calculations
• Filtering and subsetting a data frame
• Conducting exploratory data analysis on a data frame

Exercises: Drills

Assets needed: Iris dataset

Time: 70 minutes

Lesson 5: Reading, writing and exploring data frames

Objective: Student can read, write and analyze tabular external fines

Description:

• Reading and writing csv and txt files
• Reading and writing Excel files
• Exploring a dataset
• Descriptive statistics

Exercises: Drills

Assets needed: Iris dataset

Time:  40 minutes

Lesson 6: Data manipulation with dplyr

Objective: Student can perform common data manipulation tasks with dplyr

Description:

• Manipulating rows
• Manipulating columns
• Summarizing data

Exercises: Drills

Assets needed: Airport flight records

Time: 50 minutes

Lesson 7: Data manipulation with dplyr, continued

Objective: Student can perform more advanced data manipulation with dplyr

Description:

• Building a data pipeline
• Joining two datasets
• Reshaping a dataset

Exercises: Drills

Assets needed: Airport flight records

Time: 50 minutes

Lesson 8: R for data visualization

Objective: Student can create graphs in R using visualization best practices

Description:

• Graphics in base R
• Visualizing a variable’s distribution
• Visualizing values across categories
• Visualizing trends over time
• Graphics in ggplot2

Exercises: Drills

Assets needed: Airport flight records

Time: 70 minutes

Lesson 9: Capstone

Objective: Student can complete end-to-end data exploration project in R

Assets needed: Baseball records

Time: 40 minutes

This download is part of my resource library. For exclusive free access, subscribe below.

The post Learning guide: Introduction to R, one-day workshop first appeared on Stringfest Analytics.

Turns out we can also build “word games” when it comes to code, and these can also be great mental exercises.

What are Parsons problems?

Parsons problems, named after one of the originators of the exercise, are coding challenges where learners must re-arrange a series of lines of code in an order to achieve some purpose.

What are the benefits?

Parsons problems have lots going for them:

• They are interactive. Students are inclined to move around the blocks and try different answers.
• It avoids the “blank screen of terror.” With something already on the screen to engage with, students are less likely to give up or skip ahead.
• It isolates concepts. With a pre-defined problem, students can focus on specific concepts like control flow and code efficiency, rather than naming and assignment variables from scratch
• It builds good habits. Because the code is pre-defined, the end result when correct will be a well-constructed piece of code. Students play an active role in helping to build good code, rather than haphazardly stumbling through suboptimal code on their own.

Demo: Parsons problem

I have a small demo of how Parsons problems work below. Feel free to download the PowerPoint slide to try it yourself! This problem is in the Python programming language.

A couple of things to notice about the example:

• Distractors, or incorrect options, are possible.
• Some programming languages, like Python, are indented. The Parsons problem should accomodate for this.

Building Parsons problems

Parsons problems can be done somewhat effectively with a plain old PowerPoint slide, or even by printed lines of code. There are also ways to auto-grade them: for example, check out the js-parsons JavaScript library.

References

The post Teaching coding: What is a Parsons Problem? first appeared on Stringfest Analytics.

From web scraping to regular expressions to social media automation, I’ve learned a lot collecting a list of over 30,000 quotes related to innovation.

Lately I’ve been turning my attention to finding quotes about computer programming, as digital-savvy is crucial to innovation today. These exercises prove great blog post material and quite “meta,” too… writing code to read quotes about writing code. I will cover one of what I hope to make a series below. For this example…

Scraping DevTopics.com’s “101 Great Computer Programming Quotes”

This is a nice set of quotes but we can’t quite copy-and-paste them into a .csv file as in doing so each quote is split across multiple rows and begins with its numeric position. I also want to eliminate the quotation marks and parentheses from these quotations as stylistically I tend to avoid them for Twitter.

While we might despair about the orderliness of this page based on this first attempt, make no mistake that there is well-reasoned logic running under the code with its HTML, and we will need to go there instead.

Part I: Scrape

To do this I will load up the rvest package for R and SelectorGadget extension for Chrome.

I want to identify the HTML nodes which hold the quotes we want, then collect that text. To do that, I will initialize the SelectorGadget, then hover and click on the first quote.

In the bottom toolbar we see the value is set as li, a common HTML tag for items of a list.

Knowing this, we will use the html_nodes function in R to parse those nodes, then html_text to extract the text they hold.

Doing this will return a character vector, but I will convert it to a dataframe for ease of manipulation.

Our code thus far is below.

#initialize packages and URL
library(rvest)
library(tidyverse)
library(stringr)

link <- c("http://www.devtopics.com/101-great-computer-programming-quotes/")

#read in our url
quotes <- read_html(link)

#gather text held in the "li" html nodes
quote <- quotes %>% 
  html_nodes("li") %>% 
  html_text()

is.vector(quote)

#convert to data frame
quote <- as.data.frame(quote)

Part II: Clean

Gathering our quotes via rvest versus copying-and-pasting, we get one quote per line, making it more legible to store in our final workbook. We’ve also left the numerical position of each quote. But some issues with the text remain.

First off, looking through the gathered selection of text, I will see that not all text held in the li node is a quote. This takes some manual intervention to spot, but here I will use dplyr’s slice function to keep only rows 26 through 126 (corresponding to 100 quotes).

We still want to eliminate the parentheses and quotation markers, and to do this I will use regular expression functions from stringr to replace them.

a. Replace “(“, “)”, and ““” with “”

This is not meant as a comprehensive guide to the notorious regular expression, and if you are not familiar I suggest Chapter 14 of R for Data Science. So I assume some familiarity here as otherwise it becomes quite tedious.

Because “(” and “)” are both metacharacters we will need to escape them. Placing these three characters together with the “or” pipe (|) we then use the str_replace_all function to replace strings matching any of the three with nothing “”.

b. Replace “”” with ” “

The end of a quotation is handled differently as we need a space between the quotation and the author; thus this expression is moved to its own function and we use str_replace to replace matches with ” “.

Bonus: Set it up for social media

Because I intend to send these quotes to Twitter so I will put a couple finishing touches on here.

First, using the paste function from base R, I will concatenate our quotes with a couple select hashtags.

Next, I use dplyr’s filter function to exclude lines that are longer than 240 characters, using another stringr function, str_length.

The quote for Part II is displayed below.

#get the rows I want
quote <- slice(quote, 26:126)

#delete the characters I don't want

charsd <- c("\\(|\\)|“")

quote$quote <- str_replace_all(quote$quote,charsd,"")

quote$quote <- str_replace(quote$quote,"”"," ")

#filter lines >240 characters
quote$quote <- paste(quote$quote, "#quote #coding")
quote <- filter(quote, str_length(quote)< 240)

#write csv
write.csv(quote,"C:/RFiles/tech2quotes.csv")

Finally, find the complete code below.

From web scraping to dataframe manipulation to regular expression, this exercise packs a punch in dealing with real-world unstructured text data — and it comes with some enjoyable reading, too.

I hope this post inspires you to tackle the world of text, and I plan to walk through a couple more of these.

The post Writing Code to Read Quotes About Writing Code first appeared on Stringfest Analytics.