CODEWITHSUNDEEP
rloopsdplyrmerge
SpHan
SpHan

Reputation: 27

Merging two DataFrames matching rows/columns

Given two dataframes of two different dimensions, how could I go about merging one on top of the other keeping the dimensions of the larger df and inserting the data from the smaller where needed.

x = matrix(data = 1:4, nrow = 20, ncol = 22)

y = matrix(data = NA, nrow = 26, ncol = 26)

something such as this where Y is a full blank matrix and X is a matrix of actual data points. How could I go about merging them such that the data from X is overlayed onto the matrix of Y

I've tried something like

Z<- merge(Y, X, by = "Country", all.x= TRUE)

but what ends up happening is I get a DF doubled in size of columns with the data appended onto the end of the Y DF.

I have also attempted this

    library(dplyr)
    Z<- merge(Y, X, by = "Country", all.x = T)
      Z%>% 
        mutate(Afghanistan = ifelse(is.na(Afghanistan.x), Afghanistan.x, Afghanistan.y)) %>% 
           select(-Afghanistan.y, -Afghanistan.x)

For reference, my table's rows/column names are countries. This method works however it only replaces 1 column at a time, is a loop possible to properly input all data for all columns?

Upvotes: 0

Views: 890

Answers (2)

user3116101
user3116101

Reputation: 78

Along the lines of the answer given by @ronak-shah, if your y dataframe has all the row and column names of x (plus some more which are not part of x), then you can simply subset by the row and column names to fit x within y.

Creating the example matrices:

x <- as.data.frame(matrix(data = 1:3, nrow = 5, ncol = 6))
y <- as.data.frame(matrix(data = NA, nrow = 10, ncol = 10))

Renaming some of the rows and columns to create our example:

row.names(x)=c("a", "b", "c", "d", "e")
colnames(x)=c("V1", "V2", "V3", "V5", "V6", "V8")
row.names(y)=c("1",  "2",  "a",  "b",  "5",  "c",  "7",  "d",  "9",  "e")

Now, fitting x within y:

y[row.names(x), colnames(x)] <- x

This will fill in the elements in y that match the row and column names in x, but leave the others untouched.

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 389265

You can subset y with dimensions of x and assign -

y[1:nrow(x), 1:ncol(x)] <- x
y

Upvotes: 2

Related Questions