Adding data from vector to matrix

Question

I have a vector p of countries and a matrix Z of countries. Some countries in p are not included in Z. I would like an argument that adds countries from p to Z if they are not yet included in Z, but only if they have values that are >0.

Here is an example of the data. In the expected output, Australia should be added to Z, with 0 in all matrix columns since it does not have any matrix data to report. The matrix dimensions should then be 5x5.

p <- c(Afghanistan = 2769, Albania = 93893300, Algeria = 0, Argentina=4550430, Australia=50, Ban = 2)
p <- stack(p)
names(p) <- c("Value", "Area")
#vector

Z <- matrix(c(0,138201.333333333,0,0,0,0,1162.33333333333,0,38.3333333333333,0,0,0,0,0,0,0,300238,0,9675,0), nrow = 4, ncol = 4, byrow=TRUE)
dimnames(Z) = list(c("Afghanistan","Albania","Algeria", "Argentina"),c("Afghanistan","Albania","Algeria", "Argentina")) #matrix
#matrix

What should be the dimension of the resulting matrix? 4*5 (add a column Australia) or 5*5 (add a column and a row)? — ekoam, Nov 30 '20 at 10:05
Updated. I would like the dimensions to be 5x5 - sorry about the unclarity. — MoonS, Nov 30 '20 at 10:13

Edo · Accepted Answer · 2020-11-30T12:29:07.463

1

A combination of cbind and rbind will do the trick. I've added an additional element in p just to show that it works even with more names.

# your data EDITED BASED ON THE DPUT you posted!!
p <- c(Afghanistan = 2769, Albania = 93893300, Algeria = 0, Argentina=4550430, Australia=50, Ban = 2)
p <- stack(p)
names(p) <- c("Value", "Area")

Z <- matrix(c(0,138201.333333333,0,0,0,0,1162.33333333333,0,38.3333333333333,0,0,0,0,0,0,0,300238,0,9675,0), nrow = 4, ncol = 4, byrow=TRUE)
dimnames(Z) = list(c("Afghanistan","Albania","Algeria", "Argentina"),c("Afghanistan","Albania","Algeria", "Argentina")) #matrix

# solution
to_add <- setdiff(p[p$Value>0, "Area"], unlist(dimnames(Z)))

Z <- cbind(Z, matrix(0, nrow(Z), length(to_add), dimnames = list(NULL, to_add)))
Z <- rbind(Z, matrix(0, length(to_add), ncol(Z), dimnames = list(to_add, NULL)))

Z
#>             Afghanistan  Albania  Algeria Argentina Australia Ban
#> Afghanistan     0.00000 138201.3    0.000         0         0   0
#> Albania         0.00000      0.0 1162.333         0         0   0
#> Algeria        38.33333      0.0    0.000         0         0   0
#> Argentina       0.00000      0.0    0.000         0         0   0
#> Australia       0.00000      0.0    0.000         0         0   0
#> Ban             0.00000      0.0    0.000         0         0   0

edited Nov 30 '20 at 12:29

answered Nov 30 '20 at 10:15

Edo

7,567
2
9
19

Thanks, although I am not sure how I use this code with my real data, where Z is a large matrix created from a csv. I tried your code but it gives me "In Ops.factor(left, right) : ‘>’ not meaningful for factors." – MoonS Nov 30 '20 at 10:35
Your error looks like something that happens in correspondence of the creation of `to_add`. Please be sure that `class(p$p)` is numeric. Otherwise, let me know. – Edo Nov 30 '20 at 10:39
Hm, ```class(p$p)``` gives me ```"NULL"``` – MoonS Nov 30 '20 at 10:57
please share `dput(p)`. Looks like the column of the p dataframe is not called p. That's what generates your problem. – Edo Nov 30 '20 at 11:53
dput(p) was too long for a comment, I have added it below as an answer. – MoonS Nov 30 '20 at 12:02
okay. based on your dput, p in your example is not a good representation of your original p. You need to be more careful. Check out my edit. Now it should work. – Edo Nov 30 '20 at 12:29
also, if this is what you are looking for, edit your question with the example data I've posted and remove from the answers the one with the dput, because it's not an answer. – Edo Nov 30 '20 at 12:31
Thank you, now it works.! I've edited the question - sorry about the confusion. – MoonS Nov 30 '20 at 16:14

TarJae · Answer 2 · 2020-11-30T12:48:03.710

Edited version after new information in your comment: Check this:

# your given matrix
p <- as.matrix(c(Afghanistan = 2769, 
                 Albania = 93893300, 
                 Algeria = 0, 
                 Argentina=4550430, 
                 Australia=50)) # a matrix of 5 values

p_transpose <- t(p)   # transpose matrix p

for(j in seq(ncol(p)))
  if(! all(p[, j] == p_transpose[j, ])) stop("wrong transpose")  # https://stat.ethz.ch/R-manual/R-devel/library/base/html/t.html


p_transpose_dataframe <- as.data.frame(p_transpose)  # p_transpose to dataframe


# your given main matrix
Z <- matrix(c(0,138201.333333333,0,0,
                  0,0,1162.33333333333,0,
                  38.3333333333333,0,0,0,
                  0,0,0,0,
                  300238,0,9675,0), nrow = 5, ncol = 4, byrow=TRUE) # matrix Z
colnames(Z) <- c("Afghanistan","Albania","Algeria", "Argentina")    # name columnes

Z <- as.data.frame(Z)   # make a dataframe Z

# helper function: code from here: # reference: https://stackoverflow.com/questions/45857787/adding-column-if-it-does-not-exist
fncols <- function(data, cname) {
  add <-cname[!cname%in%names(data)]
  
  if(length(add)!=0) data[add] <- 0
  data
} 

newZ <- fncols(Z, colnames(p_transpose_dataframe))   # helper functions to add not existing columns to Z and assign to new dataframe


result_Z <- rbind(newZ, p_transpose_dataframe)     # result dataframe
View(result_Z)                                     # View result dataframe

Please give feedback.

This may work, but my real data contains a lot of countries so adding them manually as in ```Z$Australia <- 0``` is not really an option. Can the code be automatized somehow to only add the unique countries in p? — MoonS, Nov 30 '20 at 11:15

Adding data from vector to matrix

2 Answers2