R

By Lindsay Lamb

Recently, Andrea wrote a post encouraging folks to break away from Excel and recode variables in R. I used this post (along with a lot of tutoring and hand-holding from Andrea) to begin to learn R. At the beginning of the year, I wrote about finally achieving my goal of learning R. To be sure, I still have a LONG way to go, but I am comfortable enough to stumble along, look up code, and find and fix problems on my own without bugging Andrea all the time. As one of my former colleagues told me, “You can’t break R, R is already broken. Our job is to fix it.”

In my previous role as an evaluation coordinator for the Austin Independent School District, we often talked about how much of what we did was figure out problems with data. Why did a student have multiple records? What did a code of F mean? Why did some students have missing data? How should we recode the data in order to get it into the right format for our analyses? We reveled in figuring out these logic puzzles.

The work Andrea and I engage in with our clients at Agile Analytics is pretty much the same… except it is a little more complicated. We are often pulling together disparate files from various data sources and often without a data dictionary. We have to do a lot of sleuthing to figure out if we received the data necessary for our analyses, what our data represent, where the data came from, and which students were included (or not included).

One thing I always do to help me answer these questions is to compute the mean, range of values, and standard deviation of values. When I trained new hires and graduate students in data analysis, I always emphasized the importance of computing measures of central tendency before running any analyses. It is even more important to conduct this first step in data analysis when you do not know the range of your data, how variables were coded, if values are character or numeric, if there is systematic missing data, how missing data are coded (or not), and if any variables are out of range.

Recently, I needed to review the data from one of our client’s previous evaluations that was not conducted by Agile Analytics. The program manager did not know much about the data, but knew it was messy and couldn’t tell me anything more about it. The data were coded in two ways that I typically don’t use: scores ranged from -1 to +1 or -2 to +2 on a 5 point Likert-scale. Our purpose in examining the data was to compute the mean and standard deviation for each variable and compute a factor analysis to help our client think through which items to trim on their survey. Because of this, I thought it was appropriate to recode the data to a standard 1 to 5 scale. This would also make it easier for our clients to understand the measures of central tendency I presented to them as we talked through which items to keep on their survey.

As I was recoding the data, I discovered that several variables included errors in their coding. These errors were super easy for me to identify because in my process of inspecting the data using R. First, I looked at the minimum and maximum of each variable in a table. I could easily see the codes assigned to each variable, and used this to help recode to a 1 to 5 scale. As a reminder, variables were either coded on a -2 to +2 scale or a -1 to +1. As you can see in my code below, I added a .5 even though this variable ranged from -2 to +2. That is because I noticed an error after I computed the table (see below):

Had I been recoding in Excel, I would have completely missed these errors. Sure, I would have found them eventually when I brought the data back in to SPSS, SAS or whatever stats package I used, but then I would have to go back to Excel and recode the data… with no record of what I had done. In R, you save your script, so it is easy to show your clients where the errors were and how you fixed them. In Excel, you have to save different iterations of your recoding because there is no script to save. You can also save projects and some code in SAS and SPSS, but R has everything all in one place, or printout. You can see the tables and code you used to recode the data in a single glance rather than having to walk someone through your code first and the output second. Someone else can also open your project (if data are in a shared location) and run it or tweak it to see if you get the same result. Notes can easily be added to the script to ensure everyone is on the same page.

Like I said, I am still an R neophyte, but I’m getting better at fixing it and sharing what I have learned along the way with the rest of you all 😉

By Lindsay Lamb

About a year ago, Andrea and I met to plan out the best year ever! We went through our goals as a business, our goals working with clients, our professional goals and our personal goals. It was a really fun process to talk about where we were, both personally and professionally, and where we wanted to go both in our business and in our personal lives. We were ambitious, but we had each other for support and accountability.

And then the pandemic happened.

Although it wasn’t one of our goals, Andrea and I adapted. We spent nearly a year working apart. Checking in over zoom, a quick phone call, and sometimes when the stars aligned even a walk. We used Trello to keep all of our projects and activities on track, we were making it work (all while juggling watching kids, zoom kindergarten, and our spouse’s work schedule). Had someone come along during our year-in-review planning meeting and told us about the pandemic and how we would have to shift our work and personal lives, I probably would have said, “You are crazy. There is no way I can do all of that.” And yet, here we are, nearly one year later.

Andrea and I embraced our company name – Agile Analytics – and truly became agile. We met with clients virtually, used innovative tools like Miro to make planning meetings more intimate and engaging, sought out new clients and opportunities that didn’t seem possible a year ago, we thought of new resources our clients and colleagues in the field might benefit from having and started creating those. I started writing a blog!

While all of these activities are great, they were not my personal or professional goals for 2020.

My big goal? Learning R.

Learning R has been a personal and professional goal for a while now and I always end up getting out of it. I spent about 10 years using SAS and nearly 20 years using SPSS. In many ways, learning R was more like learning a new language. I didn’t need to learn R since I could use SAS, SPSS, and Excel for many things, but I wanted to learn R. I decided 2020 would be different. Andrea, an R guru, was teaching an R course to one of our clients and asked me to join. I jumped at the opportunity. We had our first class the week before everything shut down las Spring. I figured that was it for me and R… but we persisted and continued having classes throughout the Spring and Summer, virtually of course.

During this time, Andrea sent me bite-sized projects and I challenged myself to recreate projects Andrea worked on to make sure I understood what she did and to see if I could make them work on my end.

Without even recognizing it happening, I spent the past week uploading, cleaning, and merging data for one of our clients. I ran simple statistics and created tables, saved out subsets of data for Andrea to review. I was doing it! Sure, I have a LONG way to go, but I am no longer scared of using R. I can read the code and understand what is going on behind the scenes. I actually enjoy writing code and using online resources to figure out how to solve problems.

So here I am about a year later, and I feel like I am a totally different person (in more ways than one) than I was a year ago. I have adapted in ways I never thought possible, and I still found opportunities to push myself personally and professionally. Learning R was just one little piece of that, but it was something from my pre-pandemic life that I wanted to accomplish, and honestly helped make me feel a little normal. It gave me something outside of all this madness on which to focus. I made it past that first psychological hurdle in learning R – in the midst of a pandemic, no less – and I feel sure that you can learn R too!

by Andrea Hutson

Note: This post may be for you even if you don’t regularly use R! You’ll learn a technique that is FASTER than Find & Replace in EXCEL and is more accurate to boot.

Raise your hand high if are in the Find & Replace club – you use it in EXCEL to clean up your data before starting analyses, no matter what software you eventually use to analyze data.

Here’s one way we often use EXCEL – to recode text data into numeric data. Let’s imagine we have a dataset like the one below:

We need to recode these variables so that they’re numeric and then we can analyze them.

The typical recode method in EXCEL looks something like this:

It takes a while, but it works, right? Well, mostly. Have you ever done something like this?

In the example above, I coded “Agree” first, which replaced all of the text for “Strongly Agree”, “Somewhat Agree”, etc. with “4”. Yes, there are ways to avoid this happening,but I often forget about them until I’ve made the mistake.

Have you ever confused yourself so much you’ve had to start COMPLETELY OVER?

I’m here to tell you something exciting – your copy and paste days can be TOTALLY OVER, even if you don’t normally use R.

STEP 1: Load Your EXCEL Data

In step 1, you need to load your EXCEL data into R. This is harder than it should be, and requires you to use the “openxlsx” library or convert your file to a .csv format as there’s no native EXCEL support in R. I’ll plan to do a post all about this in the future, as this was my first major roadblock to using R – actually loading the dang data.

Here’s the first step. For simplicity in the next steps, please name your dataset “myData”.

 
setwd("~/Directory Of Your Files")   # set working directory
 
     # You can get to this in R studio by going to Session -> Set Working Directory 
 
library(openxlsx) # if this is your first time using openxlsx, you'll need to install first
 
    # Use this code first if so ->   install.packages("openxlsx") 
 
myData <- read.xlsx("Your EXCEL FILE.xlsx", sheet=1)

STEP 2: Recode

I’m going to skip explaining the ‘lapply’ function right now – just know that you don’t need to change anything here but the values for your scales. That is, you can change “Strongly Agree” to “Very Often” if that’s the scale you’re using, or change the value for Strongly Agree to a different number. You can add more values, too, just make sure that:

• each value is separated by a semicolon (;)
• your text values are in SINGLE quotes,
• the final bit of code has an end double quote and a closing parenthesis.

# --> Load the library that has the recode function (car) - this one generally comes with R so you shouldn't need to install
 
   library (car)
 
# --> Now do a batch recode!  The code below works for your typical 1-5 "Strongly Disagree -> Strongly Agree" scale but you can use 
 
myData <- lapply(myData, FUN = function(x) recode(x, "'Strongly Agree' = 5; 'Agree' = 4; 
                                              'Neither Agree nor Disagree' = 3; 'Disagree' = 2;
                                              'Strongly Disagree'=1; 'not applicable' = NA")

STEP 3: Convert back to a table

Almost done. The last step to find/replace is to convert this new object you’ve created back into a data frame, which only uses one line of code.

 
# --> Change object back into data frame
 
   myData <- as.data.frame(myData)

STEP 4: Send back to EXCEL

Many of us that use R for data analysis will skip this step, and just start analyzing here. But you can absolutely just send this right back to EXCEL if you’d like.

# --> Write to EXCEL
 
   write.xlsx(myData, 'Data recoded.xlsx')

Boom – you’re done! Now, I know, some of you are thinking, ‘But Andrea, in EXCEL find/replace is just one step, and you’ve just given us FOUR steps to follow. I’m going to ignore this post and keep doing things as I’ve always done.’

But here’s why this way is better:

• It is NOT just one step in EXCEL! You’ve got to Find/Replace each individual value…and it’s more time consuming than you think.
• Once you have the base code written down you can reuse it over and over!
• Similarly, if you save the R file, you can recode this particular data set over and over again. If you get a survey update with 10 more students, you don’t have to spend an hour finding/replacing – you literally just use the same exact code you’ve already written.
• You’re not going to make silly mistakes.

Try it out! I’ve got the entire code below for your copy/pasting needs. The items that you will need to change should appear in RED. You should be able to leave all of the rest of the code as is.

 
# --> Set the directory where your files are : you can get to this in R studio by going to Session -> Set Working Directory 
 
setwd("~/Directory Of Your Files")   # set working directory
 
 
library(openxlsx) # if this is your first time using openxlsx, you'll need to install first
 
    # Use this code first if so ->   install.packages("openxlsx") 
 
myData <- read.xlsx("Your EXCEL FILE.xlsx", sheet=1)
 
# --> Load the library that has the recode function (car) - this one generally comes with R so you shouldn't need to install
 
   library (car)
 
# --> Now do a batch recode!  The code below works for your typical 1-5 "Strongly Disagree -> Strongly Agree" scale but you can use 
 
myData <- lapply(myData, FUN = function(x) recode(x, "'Strongly Agree' = 5; 'Agree' = 4; 
                                              'Neither Agree nor Disagree' = 3; 'Disagree' = 2;
                                              'Strongly Disagree'=1; 'not applicable' = NA")
 
# --> Change object back into data frame
 
   myData <- as.data.frame(myData) 
 
 
# --> Write to EXCEL
 
   write.xlsx(myData, 'Data recoded.xlsx')

Try it out and let me know how it goes! I promise, after you’ve saved yourself an hour of tedious finding and replacing, you’ll be a convert!

I’ve talked to many, many people who analyze data as part of their jobs who have expressed an interest in R.  But people are sometimes scared to take the plunge. Here are four reasons you might want to consider it.

1.    You’re tired of being locked into $$$ contracts with SPSS, SAS, or STATA

When I went out on my own, I went from having a free version of SPSS (well, free to me!) to having to pay for it.  I shelled out a not significant amount of money (for one year), for a basic version of SPSS then got to work.  

On my very first project, I realized I needed to do a logistic regression – a pretty basic procedure. I’d done it hundreds of times on my work computer. But the menu option was not available on my screen.  What the heck?

Contacted support. My issue?  Logistic regresssions were not supported in the basic version.

Seriously? The module I would have needed to download was another several hundred dollars, for ONE year of access, and for literally ONE type of analysis.  Craziness. And not doable when you are on a shoestring budget.

In fact, as of press date, to get the professional version of SPSS 26, with (I assume), all packages installed, the cost is $5730 per year.  Per person!

That’s insane. Guess how much R costs:  NOTHING. It’s free!

2.    You realize there’s so much that your stats program can’t do.

Propensity Score Matching has become a big area of interest in quantitative evaluation work today.  Can you do it in SPSS?

Nope.  That’s right, you’ve paid potentially thousands of dollars for an annual contract, but the program can’t do what you need it to do.

 [OK, you can go to this page and download several python extensions that maybe will work. Check out this long list of instructions you need to make that happen. I’m sure you have to pay extra for the python integration!]

In R, getting the software you need on your computer is literally as easy as typing “install.packages (‘MatchIt’) .

And when a new trendy statistical procedure comes around? You can bet it will be available on R before any of the other packages.

What’s available now? Pretty much anything you can think of.

There’s a package that helps recode data easily.  There’s a package that will help you automatically write your results to EXCEL. There’s a package that automatically scores survey data, even reverse coding items on the fly.  There are tons of customizable graphics packages, if you want to go down that rabbit hole.

3.    You are tired of always starting at square one with your data.

When I worked for a local school district, we had a climate survey that was given to every student, staff member, and parent.  Tens of thousands of surveys needed to be processed, the results tabulated, and distributed online in the form of reports.

Each summer I dutifully spent one to two months preparing all of the data so it could be placed back into our survey report software, which cost over $20,000 up front and came with a $2000ish yearly maintenance fee.  I spent a lot of time writing down procedures in a handbook so I would remember what to do the next time around. 

One year, we had a small subset of parent survey data come in a month or so after the official close date.  Now we had to make a choice – redo all of that work again to integrate the new data (and delay the reports by another few weeks), or ignore it.

It didn’t have to be that way!

With R, you write a script one time and then you’re done.  You can reuse – and modify – that same script over and over, saving jillions of hours of time, and reducing the possibility that you’re going to make a mistake and not notice it.  

So that late data that came in would have been loaded in with all of the other data, and the results would have been ready immediately. All in all, it might have added an hour or two to the process at most instead of WEEKS.

4.    You know there are things you’re doing in EXCEL that you shouldn’t be doing.

Do you go through your data set and use “Find and Replace” to change “Strongly Agree” to 5,  or “Yes” to “1” before importing into your statistics software?  That is SO easy to do in R.  

A list of EXCEL things you can and should be doing in R instead:

• Find and replace
• Merging data sets (VLOOKUP, and manual ways)
• Sorting data
• Filtering data 
• Creating a clean ‘final data set’

Are you ready to give R a go?  Go here to download RStudio Desktop (yes, there is a paid version, but the free version will do all you need and more).

If you work with a large amount of data, you’ll often be faced with loading many files (EXCEL, SAS, etc.) into R as the very first step of your project. And often, if your clients send you a large amount of data, they would like to know right away if everything loaded correctly — or, it is in your best interest to make sure everything’s perfect before the person who pulled the data forgets what they pulled or moves on to other work.  Unfortunately, loading each file in a data set can be quite tedious:

attendance_2014_C15 <- read.csv(“attendance_2014_C15.csv”, stringsAsFactors=FALSE)
attendance_2014_C14 <- read.csv(“attendance_2014_C14.csv”, stringsAsFactors=FALSE)
attendance_2015_C15 <- read.csv(“attendance_2015_C15.csv”, stringsAsFactors=FALSE)
attendance_2015_C14 <- read.csv(“attendance_2014_C14.csv”, stringsAsFactors=FALSE)
grades_2014_s1_C14 <- read.csv(“grades_2014_s1_C14.csv”, stringsAsFactors=FALSE)
grades_2014_s2_C14 <- read.csv(“grades_2014_s2_C14.csv”, stringsAsFactors=FALSE)

….etc.  Typically we import these data using copy/paste or just typing in the code. This can be tedious if there are more than a few files…. and it’s a way to inadvertently introduce errors (there’s an error in the code above, see if you spot it, that would not be caught by R).

Well, I’m here to introduce you to a new and easy method for getting all the data from a directory and saving it to your R global environment.

And it just takes a few line of code.

# get file names from directory

   files <- list.files()

# split to save names; name for data frame will be first element

   names <- strsplit(files, "\\.")

# now get the files

for (i in 1:length(files)) { # for each file in the list
   fileName <- files[[i]] # save filename of element i
   dataName <- names[[i]][[1]] # save data name of element i
   tempData <- read.csv (fileName, stringsAsFactors=FALSE) # read csv file
   assign (dataName, tempData, envir=.GlobalEnv)  # assign the results of file to the data named

}