One of the frustrating things about doing empirical research is that the data you want lives in many different places. Sometimes you can download a beautifully formatted database that contains exactly the data you need. But more often, the data you want must be pulled from many corners of the internet.
Once you have the data, you need to put it together so you can work with it. If you’re working in R, you can use the
merge function. As the name suggests, the function merges two datasets.
What’s funny is that I was several years into using R before I discovered this function. I had been banging my head writing custom code to combine various datasets, only to learn that there was a base R function that did exactly what I want. The lesson here is that before you write custom code, you’re better off doing a quick search to see if there’s a pre-written function that will do what you want. There usually is.
Back to the
merge function. To understand how it works, imagine that you have a database called
sales that contains sales data for various companies:
sales: year company sales profit 1: 1997 Apple 105 5 2: 1999 Ford 505 105 3: 1999 Ford 505 51 4: 2000 Microsoft 2450 181 5: 2001 Microsoft 2509 203
Imagine that we want to calculate the ‘markup’ of each company. To do that we need profit data. The trouble is that this data lives in a seperate database,
profit, that looks like this:
profit: year company profit 1: 1999 Ford 105 2: 2001 Microsoft 203 3: 1997 Apple 5 4: 1999 Ford 51 5: 2000 Microsoft 181
To work with our sales and profit data, we need to combine it into one dataset. That’s whate the
merge function is designed for. It takes as an input two sets of data,
merge(x, y, by)
by value tells
merge the variables you would like to use as indexes. In our example, we want to merge the data by
company. The function will then look for all the entries with the same year and company and merge them. Applying that to our sale and profit data, we’d enter:
all = merge(sales, profit, by = c("year", "company"))
Some comments. Yes, the quotes around
"company" are required. I don’t know why … that’s just the way the function works. The
c() argument is how R combines elements. Note that by default,
merge will keep only the by elements that are common to both sets of data.
Here’s what the function will output:
all: year company sales profit 1: 1997 Apple 105 5 2: 1999 Ford 505 105 3: 2000 Microsoft 2450 181 4: 2001 Microsoft 2509 203
Now we can calculate the markup for each company by dividing profits by sales:
all$markup = all$profit / all$sales * 100 all: year company sales profit markup 1: 1997 Apple 105 5 4.761905 2: 1999 Ford 505 105 20.792079 3: 2000 Microsoft 2450 181 7.387755 4: 2001 Microsoft 2509 203 8.090873
So far, I haven’t talked about the type of data we’ve been working with. The nice thing about
merge is that it works both on data.frames — R’s basic data holder — and also on data.tables. If you’re working with large data, I recommend using data.tables as they are much much faster.