Most of my work recently has involved downloading large datasets of species occurrences from online databases and attempting to smoodge1 them together to create distribution maps for parts of Australia. Online databases typically have a ridiculous number of columns with obscure names which can make the smoodging process quite difficult.
For example, I was trying to combine data from two different regions into one file, where one region had 72 columns of data and another region had 75 columns. If you try and do this using rbind, you get an error but going through and identifying non-matching columns manually would be quite tedious and error-prone.
Here's an example of the function in use with some imaginary data. You'll note that Database One and Two have unequal number of columns (5 versus 6), a number of shared columns (species, latitude, longitude, database) and some unshared columns (method, data.source).
species latitude longitude method database
1 p -33.87 150.5 camera trap database.one
2 a -33.71 151.3 live trapping database.one
3 n -33.79 151.8 camera trap database.one
4 w -34.35 151.3 live trapping database.one
5 h -31.78 151.8 camera trap database.one
6 q -33.17 151.2 live trapping database.one
database species latitude longitude data.source accuracy
1 database.two d -33.95 152.7 herbarium 3.934
2 database.two f -32.60 150.2 museum 8.500
3 database.two z -32.47 150.7 herbarium 3.259
4 database.two f -30.67 150.6 museum 2.756
5 database.two e -32.73 149.4 herbarium 4.072
6 database.two x -33.49 153.3 museum 8.169
rbind(database.one, database.two)
Error: numbers of columns of arguments do not match
So I created a function that can be used to combine the data from two dataframes, keeping only the columns that have the same names (I don't care about the other ones). I'm sure there are other fancier ways of doing this but here's how my function works.
The basics steps
1. Specify the input dataframes
2. Calculate which dataframe has the greatest number of columns
3. Identify which columns in the smaller dataframe match the columns in the larger dataframe
4. Create a vector of the column names that occur in both dataframes
5. Combine the data from both dataframes matching the listed column names using rbind
6. Return the combined data
rbind.match.columns <- function(input1, input2) {
n.input1 <- ncol(input1)
n.input2 <- ncol(input2)
if (n.input2 < n.input1) {
TF.names <- which(names(input2) %in% names(input1))
column.names <- names(input2[, TF.names])
} else {
TF.names <- which(names(input1) %in% names(input2))
column.names <- names(input1[, TF.names])
}
return(rbind(input1[, column.names], input2[, column.names]))
}
rbind.match.columns(database.one, database.two)
species latitude longitude database
1 p -33.87 150.5 database.one
2 a -33.71 151.3 database.one
3 n -33.79 151.8 database.one
4 w -34.35 151.3 database.one
5 h -31.78 151.8 database.one
6 q -33.17 151.2 database.one
7 d -33.95 152.7 database.two
8 f -32.60 150.2 database.two
9 z -32.47 150.7 database.two
10 f -30.67 150.6 database.two
11 e -32.73 149.4 database.two
12 x -33.49 153.3 database.two
Running the function gives us a new dataframe with the four shared columns and twelve records, reflecting the combined data. Awesome!
Edited to add:
Viri asked a good question in the comments – what if you want to keep all of the columns in both data frames? The easiest solution to this problem is to add dummy columns to each dataframe that represent the columns missing from the other data frame and then use rbind to join them together. Of course, you won't actually have any data for these additional columns, so we simply set the values to NA. I've wrapped this up into a function as well.
rbind.all.columns <- function(x, y) {
x.diff <- setdiff(colnames(x), colnames(y))
y.diff <- setdiff(colnames(y), colnames(x))
x[, c(as.character(y.diff))] <- NA
y[, c(as.character(x.diff))] <- NA
return(rbind(x, y))
}
rbind.all.columns(database.one, database.two)
And here you can see that we now have one dataframe containing all seven columns from our two sources, with NA values present where we are missing data from one of the dataframes. Nice!
species latitude longitude method database data.source accuracy
1 p -33.87 150.5 camera trap database.one <NA> NA
2 a -33.71 151.3 live trapping database.one <NA> NA
3 n -33.79 151.8 camera trap database.one <NA> NA
4 w -34.35 151.3 live trapping database.one <NA> NA
5 h -31.78 151.8 camera trap database.one <NA> NA
6 q -33.17 151.2 live trapping database.one <NA> NA
7 d -33.95 152.7 <NA> database.two herbarium 3.934
8 f -32.60 150.2 <NA> database.two museum 8.500
9 z -32.47 150.7 <NA> database.two herbarium 3.259
10 f -30.67 150.6 <NA> database.two museum 2.756
11 e -32.73 149.4 <NA> database.two herbarium 4.072
12 x -33.49 153.3 <NA> database.two museum 8.169
Happy merging everyone!
1 A high technical and scientific term!
Bought to you by the powers of knitr & RWordpress
0.000000
0.000000
Like this:
Like Loading...
