Exercises for Chapter 6

For the exercises, we need again the datasets used in Chapter 6 of the book. You can import and prepare these datasets using the code presented in the chapter.

The WID:

wid <- read.csv(file.path("ch06", "us-inequality.csv"))
wid <- subset(wid, select = c(year, value))
colnames(wid) <- c("year", "p90p100")

The US presidents data:

presidents <- read.csv(
  file.path("ch06", "us-presidents.csv"),
  sep = ";"
)
colnames(presidents) <- tolower(colnames(presidents))
presidents <- subset(presidents, select = c(inoffice, president))
presidents$startyear <- as.numeric(substr(presidents$inoffice, 1, 4))
presidents$endyear <- as.numeric(substr(presidents$inoffice, 6, 9)) - 1
presidents$inoffice <- NULL

For the US GDP data, we retain the original data (quarterly estimates)

gdp <- read.csv(file.path("ch06", "us-gdp-pc.csv"))
colnames(gdp) <- c("date", "gdppc")
gdp$date <- as.Date(gdp$date)

and add a separate column that contains the year. For this, we use again the format() function, but extract the year (%Y) from the date:

gdp$year <- as.numeric(format(gdp$date, "%Y"))

Exercise 1: Alternative Merge Conditions

As we have seen in the chapter, joining different datasets with merge() is quite limited due to the fact that the function can only match on the same values of the merge attributes. In the chapter, we have presented a way to get around this by first creating the Cartesian Product of the two tables, and then filtering out the combination of entries that we need. Can you use the same procedure to find out that the level of inequality was in the year before each presidential term? Use the wid and presidents datasets introduced in the chapter, and prepare them using the code from the chapter (see below).

Solution

We first create the Cartesian Product of the two tables with

data_annual <- merge(wid, presidents, all = T)

and then retain those combinations of records where the year of the inequality estimates equals startyear of the presidents table minus one.

data_annual <- subset(data_annual, year == startyear - 1)

Exercise 2: Different Aggregation Functions

In the chapter, we used the us-presidents.csv dataset. You may have noticed that for some presidents, there are two entries (one for each term), while for other presidents with two terms (such as, for example, Barack Obama), there is only one. The reason is that the dataset also lists the vice presidents. If there was a new vice president in a president’s second term in office, the dataset introduces another row. Let us know standardize this dataset such that there is exactly one row per president, which gives the name, the start year (of the first term) and the end year (of the last term) of the respective president. This can be done with a simple aggregation! Think about what the groups are that you aggregate over, and what suitable aggregation functions can give you the start and end years.

Solution

We first aggregate by president, over the variables startyear and endyear. We apply the min() and max() functions to these two variables, such that we get the minimum/maximum across all the entries for the respective president:

library(doBy)
presidencies <- summaryBy(startyear + endyear ~ president, FUN = c(min, max), data = presidents)

Next, we remove the variables we do not need: the maximum of the start year and the minimum of the end year are irrelevant.

presidencies$startyear.max <- NULL
presidencies$endyear.min <- NULL

And we finally fix the naming of the columns:

colnames(presidencies) <- c("president", "startyear", "endyear")

Exercise 3: Create a Lagged Variable with Merging

For many types of statistical analysis in the social sciences, we need so-called lagged dependent variables. These are variables that for a given case and point in time, contain the same variable for the same case, but for the previous time point. For example, lagging the (annual) inequality estimate for the US in 1992 would be the value for the previous year, 1991. It is actually possible to create a lagged dependent variable using merging. Can you do this for the US inequality estimates in the wid data frame? Hint: you need to merge the table with a copy of itself, where you adjust the year such that it refers to the year for which a given value should be used.

Solution

We first create a copy and adjust the column names, to better distinguish original and lagged variables:

wid2 <- wid
colnames(wid2) <- c("year", "p90p100_lag")

Now we need to adjust the year of the new dataset, such that it refers to year it should be matched with, which is the next year (from the perspective of the wid2 table).

wid2$year <- wid2$year + 1

We merge both tables to create the final dataset. Note that if we don’t specify the columns that should be matched on, the function uses those with the same name (which in our case, is the year column).

wid_new <- merge(wid, wid2)

Exercise 4: Determining When the Maximum Occurs

In this exercise, we look at the quarterly US GDP per capita data in the gdp data frame. Recall that this dataset contains quarterly GDP estimates, for January 1, April 1, July 1 and October 1 of each year. Economic performance typically varies by season, and we would like to find out in which quarter of each year the US GDP is the highest in a given year. We do this with a series of aggregation and merge operations. Hint: First, create an aggregated dataset with the annual maximum values of GDP, and then merge this table with the original one to determine which quarter has the highest GDP in the respective year. For this merge operation, it is convenient to merge on two attributes instead of one, and to retain (some of the) unmatched rows. For the latter, take a close look at the all.x or all.y parameters of the merge() function.

Solution

We first create a dataset that contains the annual maximum GDP values by year:

library(doBy)
max_gdp <- summaryBy(gdppc ~ year, FUN = c(max), data = gdp)

We add a new column to this table, with a constant value of 1. You will see later why we do this!

max_gdp$is_max <- 1

If we now merge this table with the original one, using both the year and the GDP value to match on. This way, the GDP value in the original table that is the maximum in a given year will be matched. Let’s make a first attempt - note that we match on two attributes, the names of which we specify explicitly:

gdp_new <- merge(gdp, max_gdp, by.x = c("gdppc", "year"), by.y = c("gdppc.max", "year"))

This is pretty close to what we want - we can already get an overview of the quarters in which the maximum GDP values occur:

table(format(gdp_new$date, "%m"))

## 
## 01 04 07 10 
##  6  9 12 46

Still, the new dataset only contains the matched rows from the gdp table, and we lose the unmatched ones. If we want to retain them, we can use the all.x parameter, which means that all records from the first table will be kept:

gdp_new <- merge(gdp, max_gdp, by.x = c("gdppc", "year"), by.y = c("gdppc.max", "year"), all.x = T)

The variable is_max takes the value NA for the unmatched rows, which is something we can fix easily to get a complete time series without missing values:

gdp_new[is.na(gdp_new$is_max), "is_max"] <- 0