Search code examples
rlistdummy-variable

Match observations between two datasets by ID

I am working with the following data: http://people.stern.nyu.edu/wgreene/Econometrics/healthcare.csv

What I want to do is train my algorithm to predict correctly if a person will drop out in the subsequent period. 

data1 <- subset(data, YEAR==1984)

data2 <- subset(data, YEAR==1985)

didtheydrop <- as.integer(data1$id)

didtheydrop <- lapply(didtheydrop, function(x) as.integer(ifelse(x==data2$id, 0, 1)))

This created a large list with the values that I think I wanted, but I'm not sure. In the end, I would like to append this variable to the 1984 data and then use that to create my model.

What can I do to ensure that the appropriate values are compared? The list lengths aren't the same, and it's also not the case that they appear in the correct order (i.e. respondents 3 - 7 do not respond in 1984 but they appear in 1985)

Solution

  • Assumming data1 and data2 are two dataframes (unclear, because it appears that you extracted them from an original larger single dataframe called data), I think it is better to merge them and work with a single dataframe. That is, if there is a single larger dataframe, do not subset it, just delete the columns you do not need; if data1 and data2 are two dataframes merge them and work with only one dataframe.

    There are multiple ways to do this in R.

    You should review the merge function calling ?merge in your console and reading the function description.

    Essentially, to merge two dataframes, you should do something like:

    merge(data1, data2, by= columnID) #Where columnID is the name of the variable that identifies the ID. If it is different in data1 and data2 you can use by.x and by.y

    Then you have to define if you want to merge all rows from both tables with the parameters all.x, all.y, and all: all values from data1 even if no matching is found in data2, or all values from data2 even if no matching is found in data1 or all values regardless of whether there is a matching ID in the other database.

    Merge is in the base package with any installation of R.

    You can also use dplyr package, which makes the type of join even more explicit:

    inner_join(data1, data2, by = "ID") left_join(data1, data2, by = "ID") right_join(data1, data2, by = "ID") full_join(data1, data2, by = "ID")

    This is a good link for dplyr join https://rpubs.com/williamsurles/293454

    Hope it helps