Today, we’re going to continue talking about data wrangling, motivated by issues you identified in your data appendices.
Now is a good time to clean up your Environment in R. I recommend clicking the broom icon on your Environment tab and removing all the objects from your environment. If you’re worried about a particular dataset, you can skip down to the bottom of the lab and learn how to use write_csv() to save data out of R. But in general, there is no danger to cleaning up your Environment.
Now, quit R and when it asks if you want to save your workspace image, select Yes. This is the last time I want you to select that! In general, you want to select No because you don’t want to preserve your environment.
We’re using a few additional packages today.
require(mosaic)
require(readr)
require(tidyr)To get the data files, go to http://www.science.smith.edu/~amcnamara/sds291/labs/sample_data.
doctorsDot = read.csv("sample_data/Physicians.csv")
str(doctorsDot)## 'data.frame': 53 obs. of 2 variables:
## $ State : Factor w/ 53 levels "Alabama","Alaska",..: 2 1 5 4 6 7 8 9 10 11 ...
## $ Physicians.per.100.000.Population: Factor w/ 49 levels "1612","269","286",..: 45 9 6 20 20 21 37 1 46 18 ...This example illustrates one issue with using read.csv()– it wants to make everything into factors, which we don’t want! I’ll keep that data around to show something later on.
doctors = read_csv("sample_data/Physicians.csv")
str(doctors)## Classes 'tbl_df', 'tbl' and 'data.frame': 53 obs. of 2 variables:
## $ State : chr "Alaska" "Alabama" "Arkansas" "Arizona" ...
## $ Physicians per 100,000 Population: chr "514" "331" "321" "370" ...See how this is better? It’s making the variables character vectors, which makes more sense.
The variable names in this dataset are going to be hard to use,
names(doctors)## [1] "State" "Physicians per 100,000 Population"In order to run something like favstats(), we’d have to use backtics to write out the variable name.
favstats(~`Physicians per 100,000 Population`, data=doctors)## Warning in FUN(eval(formula[[2]], data, .envir), ...): Auto-converting
## character to numeric.## Warning in FUN(eval(formula[[2]], data, .envir), ...): NAs introduced by
## coercion## min Q1 median Q3 max mean sd n missing
## 269 346 400 464.25 1612 429.0577 184.9278 52 1So, let’s rename that variable.
doctors = doctors %>%
rename(Physicians = `Physicians per 100,000 Population`)
names(doctors)## [1] "State" "Physicians"That’s better!
But, we’ve still got a variable type issue. When we run summary statistics, R complains because there is something weird in the data. Let’s try tally()ing up the values in the data.
tally(~Physicians, data=doctors)## Physicians
## 1612 269 286 287 310 321 322 323 331 332 335 336 338 340 348
## 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
## 352 355 357 361 370 371 376 388 389 400 401 405 412 417 423
## 1 1 1 2 2 1 1 1 1 2 1 1 1 1 1
## 428 433 434 448 457 461 464 465 466 478 485 504 506 510 514
## 1 1 1 1 1 1 1 1 1 2 1 1 1 1 1
## 563 575 644 N/A
## 1 1 1 1Now we see it! There’s a strange NA value that R doesn’t know how to deal with. We can actually add this into our data-reading-in code.
doctors = read_csv("sample_data/Physicians.csv", na = c("", NA, "N/A"))Of course, we have to redo our renaming.
doctors = doctors %>%
rename(Physicians = `Physicians per 100,000 Population`)favstats(~Physicians, data=doctors)## min Q1 median Q3 max mean sd n missing
## 269 346 400 464.25 1612 429.0577 184.9278 52 1Much better!
mean(~Physicians, data=doctors, na.rm=TRUE)## [1] 429.0577That makes sense, on average states have about 430 doctors. But what if we had used our data from read.csv()?
mean(~Physicians.per.100.000.Population, data=doctorsDot, na.rm=TRUE)## Warning in mean.default(eval(formula[[2]], data, .envir), ...): argument is
## not numeric or logical: returning NA## [1] NAIt doesn’t work because it’s a factor, so we might want to convert it to a numeric vector. The most intuitive thing (that doesn’t do what you want!!) would be
mean(~as.numeric(Physicians.per.100.000.Population), data=doctorsDot, na.rm=TRUE)## [1] 25.07547Now that number doesn’t make sense, but it’s also not so weird it would really catch your attention. It turns out what R does when you run as.numeric() on a factor variable is it turns each “label” (like 514 for the number of doctors in Alaska) into a number, based on where it is in the order of the factor levels (so, 514 turns into 45).
It turns out you can get the appropriate results by nesting as.numeric() and as.character(),
mean(~as.numeric(as.character(Physicians.per.100.000.Population)), data=doctorsDot, na.rm=TRUE)## Warning: NAs introduced by coercion## [1] 429.0577but it’s much easier to just start with data from read_csv().
We might not want to keep all the data in our dataset. By looking at
tally(~State, data=doctors)## State
## Alabama Alaska All US Arizona Arkansas
## 1 1 1 1 1
## California Colorado Connecticut D.C. Delaware
## 1 1 1 1 1
## Florida Georgia Hawaii Idaho Illinois
## 1 1 1 1 1
## Indiana Iowa Kansas Kentucky Louisiana
## 1 1 1 1 1
## Maine Maryland Massachusetts Michigan Minnesota
## 1 1 1 1 1
## Mississippi Missouri Montana Nebraska Nevada
## 1 1 1 1 1
## New Hampshire New Jersey New Mexico New York North Carolina
## 1 1 1 1 1
## North Dakota Ohio Oklahoma Oregon Pennsylvania
## 1 1 1 1 1
## Puerto Rico Rhode Island South Carolina South Dakota Tennessee
## 1 1 1 1 1
## Texas Utah Vermont Virginia Washington
## 1 1 1 1 1
## West Virginia Wisconsin Wyoming
## 1 1 1I can see that All US is included, as is Puerto Rico. Let’s remove those.
doctors = doctors %>%
filter(State != "All US", State != "Puerto Rico")
tally(~State, data=doctors)## State
## Alabama Alaska Arizona Arkansas California
## 1 1 1 1 1
## Colorado Connecticut D.C. Delaware Florida
## 1 1 1 1 1
## Georgia Hawaii Idaho Illinois Indiana
## 1 1 1 1 1
## Iowa Kansas Kentucky Louisiana Maine
## 1 1 1 1 1
## Maryland Massachusetts Michigan Minnesota Mississippi
## 1 1 1 1 1
## Missouri Montana Nebraska Nevada New Hampshire
## 1 1 1 1 1
## New Jersey New Mexico New York North Carolina North Dakota
## 1 1 1 1 1
## Ohio Oklahoma Oregon Pennsylvania Rhode Island
## 1 1 1 1 1
## South Carolina South Dakota Tennessee Texas Utah
## 1 1 1 1 1
## Vermont Virginia Washington West Virginia Wisconsin
## 1 1 1 1 1
## Wyoming
## 1That did it.
Let’s load in some more data. This data is about police killings in different states
policekillings = read_csv("sample_data/policekillings.csv")
str(policekillings)## Classes 'tbl_df', 'tbl' and 'data.frame': 47 obs. of 2 variables:
## $ state : chr "AK" "AL" "AR" "AZ" ...
## $ deaths: int 2 8 4 25 74 12 1 1 2 29 ...It doesn’t have many data issues. But, say we wanted to merge it with our physicians data. We need a variable to “join on.” That is, something that R can use to match up which row in doctors goes with which row in policekillings. Unfortunately, doctors has state names written out and policekillings just has the state abbreviations. We need to change one to match the other.
policekillings = policekillings %>%
mutate(state = state.name[match(state, state.abb)])
str(policekillings)## Classes 'tbl_df', 'tbl' and 'data.frame': 47 obs. of 2 variables:
## $ state : chr "Alaska" "Alabama" "Arkansas" "Arizona" ...
## $ deaths: int 2 8 4 25 74 12 1 1 2 29 ...This works because state.abb is a list of all the state abbreviations and state.name is a list of all the full state names. Both come built in to R.
Now, we can do some joining. After all, if we want to understand the relationship between physicians and police killings, we have to have both variables in the same data frame. The command that allows you to combine data frames is a “join”, which corresponds to the database operation called a JOIN.
When two database tables (or data frames) are joined, the rows of one table get matched to the rows of the other. Computers are exceptionally better suited for this task than humans, but the computer needs to be told what the criteria is for matching up the rows. The variable(s) upon which the matching is based are called a key. Both tables must contain the key columns, but there are variety of ways in which the matching can be done.
Joins can be complicated, so you may want to look at a visual explanation:
Before we start, let’s consider the dimensions of our two data frames.
dim(policekillings)## [1] 47 2dim(doctors)## [1] 51 2They don’t have the same number of rows, so we should think about how large we want our data to be at the end of our join. Do we want to keep all 51 rows in doctors and match elements from policekillings, filling with NA values where there aren’t matches? Or, are we more interested in policekillings and we want to just match the 47 states with elements from doctors?
In databases, the default JOIN type is INNER JOIN. In this case only the rows that are present in both data frames get returned. In R, I find I most often use a left_join, which retains all of the records from the first data frame, regardless of whether there is a match in the second data frame.
There are also A right joins, which retain all of the records from the second data frame, regardless of whether there is a match in the first data frame, and full joins, which contain all of the records from both data frames, regardless of whether there was a match. [Note that the number of rows returned can exceed the number of rows in either of the two original data frames.]
For this application, let’s try a left_join.
joinedData = left_join(policekillings, doctors)## Error: `by` required, because the data sources have no common variablesThis throws an error, because it is expecting there to be a variable with the same name in each data set. To get it to work, we need to specify which variables to join on.
joinedData = left_join(policekillings, doctors, by = c("state"= "State"))
str(joinedData)## Classes 'tbl_df', 'tbl' and 'data.frame': 47 obs. of 3 variables:
## $ state : chr "Alaska" "Alabama" "Arkansas" "Arizona" ...
## $ deaths : int 2 8 4 25 74 12 1 1 2 29 ...
## $ Physicians: int 514 331 321 370 370 371 464 NA 563 357 ...Notice that this dataset only has 47 observations, because there were only 47 in policekillings (the “left” in our left_join). We could have reversed the order of the datasets to get something slightly different.
joinedData = left_join(doctors, policekillings, by = c("State"= "state"))
str(joinedData)## Classes 'tbl_df', 'tbl' and 'data.frame': 51 obs. of 3 variables:
## $ State : chr "Alaska" "Alabama" "Arkansas" "Arizona" ...
## $ Physicians: int 514 331 321 370 370 371 464 1612 563 357 ...
## $ deaths : int 2 8 4 25 74 12 1 NA 2 29 ...Now, the data has 51 observations! (We could have gotten the same result by running right_join with the data specified the same way as our first join.)
For another example, consider this data about political opinions.
politics = read_csv("sample_data/politics.csv")
str(politics)## Classes 'tbl_df', 'tbl' and 'data.frame': 3564 obs. of 12 variables:
## $ : int 4178 4761 4299 4857 4892 3979 4454 4946 4479 4356 ...
## $ Left_Right_Wing: int 3 3 5 7 5 4 0 6 3 0 ...
## $ BirthYear : int 1976 1972 1977 1970 1981 1964 1975 1995 1965 1975 ...
## $ Income : int 4 4 5 2 5 5 3 3 3 2 ...
## $ Religiousity : int 3 2 1 2 3 2 3 3 1 3 ...
## $ Education : int 4 8 8 3 4 4 7 4 8 4 ...
## $ Country : chr "Austria" "Austria" "Austria" "Austria" ...
## $ Location : int 1 4 1 2 3 2 2 1 2 1 ...
## $ Gender : int 1 2 1 1 1 1 2 1 1 2 ...
## $ Occupation : int 722 233 216 723 522 121 226 441 216 412 ...
## $ Marital_Status : int 4 1 1 4 4 1 1 4 4 3 ...
## $ Age : int 40 44 39 46 35 52 41 21 51 41 ...We might want to filter the data to only include the observations from Bulgaria. So, we could use a filter() to filter out the data.
politics = politics %>%
filter(Country == "Bulgaria")## Error in filter_impl(.data, quo): Evaluation error: attempt to use zero-length variable name.This doesn’t work, and the error message is a little obscure. I had to google it! Looking at those results, I saw one that mentioned filter(), so I checked it out. It seems like the problem is empty variable names, which we have in this dataset.
names(politics)## [1] "" "Left_Right_Wing" "BirthYear"
## [4] "Income" "Religiousity" "Education"
## [7] "Country" "Location" "Gender"
## [10] "Occupation" "Marital_Status" "Age"To fix this, we can do a rather strange renaming. Now, our filter works!
politics = politics %>%
rename(obs = )
str(politics)
politics = politics %>%
filter(Country == "Bulgaria")We also might want to recode Left_Right_Wing to be a binary variable.
politics = politics %>%
mutate(LRbinary = if_else(Left_Right_Wing>5, "right", "left"))
str(politics)I didn’t have a great example from your data appendices here, but string operations are often important for data cleaning. What will help you here is the stringr package and the gsub() function. The parse_numeric() function from readr can also be very helpful.
This code removes Xs from the front of the Year variable.
library(stringr)
popLong = popLong %>%
mutate(Year = as.numeric(gsub("X", "", Year)))This code fixes an issue in the population column, which contains commas that separate the thousands places in what are supposed to be numeric values. We can fix this by using the gsub() command to remove them all at once.
popLong = popLong %>%
mutate(population = as.numeric(gsub(",", "", population)))Now that we’ve spent all this time getting our data into the right format, we want to write it to a file so that we never have to do this again! The opposite of read_csv is write_csv and it does exactly that.
write_csv(policekillings, "policekillings.csv")The file should then show up in your “Files” window, where you can “Export” it to your local computer if you like.
Now, close your RStudio session and say Don’t Save to the environment question!