require(mosaic)
require(readr)
require(tidyr)For many of you, data management will be an important consideration. There are two functions in R that can make this task considerably easier: join and reshape. Today we will learn how to use these functions.
First, let’s load a data file from a csv into RStudio. In past years, I have used a google sheet here, but this year I’m trying something new by using csv data from your data appendices! To get the data, go to http://www.science.smith.edu/~amcnamara/sds291/labs/sample_data.
doctorsDot = read.csv("sample_data/Physicians.csv")
str(doctorsDot)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)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)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)So, let’s rename that variable.
doctors = doctors %>%
rename(Physicians = `Physicians per 100,000 Population`)
names(doctors)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)Now 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)Much better!
mean(~Physicians, data=doctors, na.rm=TRUE)That 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)It doesn’t work because it’s a factor
mean(~as.numeric(Physicians.per.100.000.Population), data=doctorsDot, na.rm=TRUE)Now 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).
mean(~gdp, data=gdpLong) gdpLong <- gdpLong %>% mutate(gdp = as.numeric(gdp)) mean(~gdp, data=gdpLong, na.rm=TRUE)
Now that we have our data in long format, we can easily pick out subsets
gdpLong %>%
filter(Country == "United States")Or visualize the data
# Use type "b" for "b"oth lines and points
xyplot(gdp ~ Year, groups=Country, data=gdpLong, type="b")OK, now let’s do the same thing to another data frame.
pop.url <- "https://docs.google.com/spreadsheets/d/1IbDM8z5XicMIXgr93FPwjgwoTTKMuyLfzU6cQrGZzH8/pub?gid=1"
pop <- pop.url %>% gs_url(lookup = FALSE)
pop <- pop %>% gs_read(range = cell_limits(c(1, 1), c(276, 34)))
head(pop)
dim(pop)
n2 <- nrow(pop)
k2 <- ncol(pop)
pop <- pop %>%
rename(Country = Total.population)
popLong <- gather(pop, key = Year, value = population, -Country)
dim(popLong)
n2 * (k2-1)
popLong <- popLong %>%
mutate(Year = as.numeric(gsub("X", "", Year)))
summary(popLong)Note that if you have a lot of these to do, you could write a function that will process them quickly. In the long term you will probably save time by writing the function, but in the short term you may save time by doing them manually.
Here we have encountered a new problem. The population column contains commas that separate the thousands places in what are supposed to be numeric values. By default, R will interpret these as character strings, not numbers. One way to get around this is to go back to your data file and remove all of the commas. Another way is to use the gsub() command to remove them all at once.
popLong <- popLong %>%
mutate(population = as.numeric(gsub(",", "", population)))
summary(popLong)Again, we can look at subsets and visualize this data.
popLong %>%
filter(Country == "China")
xyplot(population ~ Year, groups=Country, data=popLong, type="b")Now that we have our data in long format, we want to combine it. After all, if we want to understand the relationship between GDP and population, we have to have both variables in the same data frame. The command that allows you to combine data frame is called inner_join, and it 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.
This is a nice visual explanation of the various types of JOIN operations.
First, let’s consider the dimensions of our two data frames.
dim(gdpLong)
dim(popLong)Note that it is possible, though not necessarily guaranteed, that the rows in the population data frame are a superset of the rows in the GDP data frame. In fact, that is the case here, but for pedagogical purposes I don’t want that, so I’m going to throw out countries from each table. [In general, you should not do this!]
gdpLong <- gdpLong %>%
filter(Country != "Djibouti")
popLong <- popLong %>%
filter(!Country %in% c("Cameroon", "Andorra"))
dim(gdpLong)
dim(popLong)In this case we want to have a key across the two columns Country and Year. That is, each unique combination of Country and Year in the GDP data frame will get matched to the corresponding row of the population data frame. The resulting data frame will contain both columns, which is what we want.
The default JOIN type is INNER JOIN. In this case only the rows that are present in both data frames get returned.
ds <- inner_join(x = gdpLong, y = popLong, by = c("Country" = "Country", "Year" = "Year"))
dim(ds)
head(ds)Note that there are fewer rows in the merged data frame than in either of the two original data frames. (Why?) Also, note that rows get reordered according the key columns. This is important because if you try to add new columns you need to make sure that they match up!
A LEFT JOIN retains all of the records from the first data frame, regardless of whether there is a match in the second data frame.
ds <- left_join(x = gdpLong, y = popLong, by = c("Country" = "Country", "Year" = "Year"))
dim(ds)A RIGHT JOIN retains all of the records from the second data frame, regardless of whether there is a match in the first data frame.
ds <- right_join(x = gdpLong, y = popLong, by = c("Country" = "Country", "Year" = "Year"))
dim(ds)Finally, a FULL JOIN contains 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.
ds <- full_join(x = gdpLong, y = popLong, by = c("Country" = "Country", "Year" = "Year"))
dim(ds)Finally we have both variables associated with a specific country for a specific year. This is panel data!
us <- ds %>%
filter(Country=="United States")
xyplot(gdp ~ population, groups=Country, data=us, type=c("b", "r"))
fm <- lm(gdp ~ population, data=us)
summary(fm)
fm.ge <- lm(gdp ~ population + Country, data=ds)
summary(fm.ge)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(ds, "gdp-pop.csv")The file should then show up in your “Files” window, where you can “Export” it to your local computer if you like.