Sum non-conformable matrices by rowname in r

Question

I have two unequally sized matrices, here is a small sample (my real matrices are much larger):

s <- matrix(1:36, nrow = 6, ncol = 6, byrow=TRUE, dimnames = list(c("Afghanistan","Albania","Algeria","Argentina","Australia","Austria"), c("Afghanistan","Albania","Algeria","Argentina","Australia","Austria")))
s2 <- matrix(1:18, nrow = 3, ncol = 3, byrow=TRUE, dimnames = list(c("Albania","Algeria","Australia"), c("Albania","Algeria","Australia")))

I would like to sum country values by rowname. Since only some rownames exist in both matrices, I need to match the rownames to each other and then sum the values where there is a match. How can I do this?

This is the output that I want, with values from "s2" added to the first matrix "s":

Here are dput's of my full data:

> dput(head(s))
structure(c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 93, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 9937, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 21, 0, 281786, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 1.33333333333333, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
45.3333333333333, 0, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 12.3333333333333, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 51.6666666666667, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 48, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 6, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 13, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2174287.66666667, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 239.5, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 5, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 33000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 498766.333333333, 0, 0, 0, 
0, 0, 103470.333333333, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0), .Dim = c(6L, 179L), .Dimnames = list(c("Afghanistan", 
"Albania", "Algeria", "Antigua and Barbuda", "Argentina", "Armenia"
), c("Afghanistan", "Albania", "Algeria", "Antigua and Barbuda", 
"Argentina", "Armenia", "Aruba", "Australia", "Austria", "Azerbaijan", 
"Bahamas", "Bahrain", "Bangladesh", "Barbados", "Belarus", "Belgium", 
"Belize", "Bermuda", "Bhutan", "Bolivia (Plurinational State of)", 
"Bosnia and Herzegovina", "Botswana", "Brazil", "Brunei Darussalam", 
"Bulgaria", "Burkina Faso", "Burundi", "CÃ´te d'Ivoire", "Cabo Verde", 
"Cambodia", "Cameroon", "Canada", "Central African Republic", 
"Chile", "China, Hong Kong SAR", "China, Macao SAR", "China, mainland", 
"China, Taiwan Province of", "Colombia", "Comoros", "Congo", 
"Cook Islands", "Costa Rica", "Croatia", "Cuba", "Cyprus", "Czechia", 
"Democratic Republic of the Congo", "Denmark", "Djibouti", "Dominica", 
"Ecuador", "Egypt", "El Salvador", "Estonia", "Eswatini", "Ethiopia", 
"Faroe Islands", "Fiji", "Finland", "France", "French Polynesia", 
"Gabon", "Gambia", "Georgia", "Germany", "Ghana", "Greece", "Greenland", 
"Guatemala", "Guinea", "Guyana", "Honduras", "Hungary", "Iceland", 
"India", "Indonesia", "Iran (Islamic Republic of)", "Ireland", 
"Israel", "Italy", "Jamaica", "Japan", "Jordan", "Kazakhstan", 
"Kenya", "Kiribati", "Kuwait", "Kyrgyzstan", "Latvia", "Lebanon", 
"Libya", "Lithuania", "Luxembourg", "Madagascar", "Malawi", "Malaysia", 
"Maldives", "Mali", "Malta", "Mauritania", "Mauritius", "Mexico", 
"Mongolia", "Montenegro", "Morocco", "Namibia", "Nepal", "Netherlands", 
"New Caledonia", "New Zealand", "Nicaragua", "Niger", "Nigeria", 
"North Macedonia", "Norway", "Oman", "Pakistan", "Panama", "Papua New Guinea", 
"Paraguay", "Peru", "Philippines", "Poland", "Portugal", "Qatar", 
"Republic of Korea", "Republic of Moldova", "Romania", "Russian Federation", 
"Rwanda", "Saint Kitts and Nevis", "Saint Lucia", "Saint Vincent and the Grenadines", 
"Sao Tome and Principe", "Saudi Arabia", "Senegal", "Serbia", 
"Seychelles", "Sierra Leone", "Singapore", "Slovakia", "Slovenia", 
"Solomon Islands", "South Africa", "Spain", "Sri Lanka", "Suriname", 
"Sweden", "Switzerland", "Thailand", "Togo", "Tonga", "Tunisia", 
"Turkey", "Uganda", "Ukraine", "United Arab Emirates", "United Kingdom of Great Britain and Northern Ireland", 
"United Republic of Tanzania", "United States of America", "Uruguay", 
"Vanuatu", "Venezuela (Bolivarian Republic of)", "Yemen", "Zambia", 
"Zimbabwe", "Unspecified Area", "Viet Nam", "Syrian Arab Republic", 
"Myanmar", "Benin", "Mozambique", "Trinidad and Tobago", "Lao People's Democratic Republic", 
"Tajikistan", "Angola", "Iraq", "Dominican Republic")))
> 

> dput(head(s2))
structure(c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 23664.36, 9.6, 162.18, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2.46, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 19022.82, 56.4, 
1.02, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2127.96, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 2.88, 0, 0, 0, 0, 0, 2.88, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1137.12, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 1518.75, 0, 1.98, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 129.48, 0, 0, 3615.06, 0, 0.18, 
0, 0, 0, 0, 0, 0, 0, 0, 18.72, 720, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.36, 7458, 0, 0, 0.06, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1170, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
6725.28, 0, 0.6, 0, 0.09, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4045.38, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 629.28, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 2160, 0, 0, 0, 0, 0, 34889.88, 0, 0, 35.7, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 84.66, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 18163.68, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 5001.21, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 1080, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 3212.64, 22.62, 0.09, 0.09, 67.92, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 9.72, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), .Dim = c(6L, 181L), .Dimnames = list(
    c("Albania", "Algeria", "Antigua and Barbuda", "Argentina", 
    "Armenia", "Aruba"), c("Albania", "Algeria", "Antigua and Barbuda", 
    "Argentina", "Armenia", "Aruba", "Australia", "Austria", 
    "Azerbaijan", "Bahamas", "Bahrain", "Bangladesh", "Barbados", 
    "Belarus", "Belgium", "Belize", "Benin", "Bermuda", "Bhutan", 
    "Bolivia (Plurinational State of)", "Bosnia and Herzegovina", 
    "Botswana", "Brazil", "Brunei Darussalam", "Bulgaria", "Burkina Faso", 
    "Burundi", "CÃ´te d'Ivoire", "Cabo Verde", "Cambodia", "Cameroon", 
    "Canada", "Central African Republic", "Chile", "China, Hong Kong SAR", 
    "China, Macao SAR", "China, mainland", "China, Taiwan Province of", 
    "Colombia", "Comoros", "Congo", "Cook Islands", "Costa Rica", 
    "Croatia", "Cuba", "Cyprus", "Czechia", "Democratic Republic of the Congo", 
    "Denmark", "Djibouti", "Dominica", "Ecuador", "Egypt", "El Salvador", 
    "Estonia", "Eswatini", "Ethiopia", "Faroe Islands", "Fiji", 
    "Finland", "France", "French Polynesia", "Gabon", "Gambia", 
    "Georgia", "Germany", "Ghana", "Greece", "Greenland", "Grenada", 
    "Guatemala", "Guinea", "Guyana", "Honduras", "Hungary", "Iceland", 
    "India", "Indonesia", "Iran (Islamic Republic of)", "Ireland", 
    "Israel", "Italy", "Jamaica", "Japan", "Jordan", "Kazakhstan", 
    "Kenya", "Kiribati", "Kuwait", "Kyrgyzstan", "Latvia", "Lebanon", 
    "Libya", "Lithuania", "Luxembourg", "Madagascar", "Malawi", 
    "Malaysia", "Maldives", "Mali", "Malta", "Mauritania", "Mauritius", 
    "Mexico", "Mongolia", "Montenegro", "Montserrat", "Morocco", 
    "Namibia", "Nepal", "Netherlands", "New Caledonia", "New Zealand", 
    "Nicaragua", "Niger", "Nigeria", "North Macedonia", "Norway", 
    "Oman", "Pakistan", "Panama", "Papua New Guinea", "Paraguay", 
    "Peru", "Philippines", "Poland", "Portugal", "Qatar", "Republic of Korea", 
    "Republic of Moldova", "Romania", "Russian Federation", "Rwanda", 
    "Saint Kitts and Nevis", "Saint Lucia", "Saint Vincent and the Grenadines", 
    "Sao Tome and Principe", "Saudi Arabia", "Senegal", "Serbia", 
    "Seychelles", "Sierra Leone", "Singapore", "Slovakia", "Slovenia", 
    "Solomon Islands", "South Africa", "Spain", "Sri Lanka", 
    "Suriname", "Sweden", "Switzerland", "Thailand", "Togo", 
    "Tonga", "Trinidad and Tobago", "Tunisia", "Turkey", "Tuvalu", 
    "Uganda", "Ukraine", "United Arab Emirates", "United Kingdom of Great Britain and Northern Ireland", 
    "United Republic of Tanzania", "United States of America", 
    "Uruguay", "Vanuatu", "Venezuela (Bolivarian Republic of)", 
    "Yemen", "Zambia", "Zimbabwe", "South Sudan", "Unspecified Area", 
    "Viet Nam", "Myanmar", "Equatorial Guinea", "Dominican Republic", 
    "Syrian Arab Republic", "Mozambique", "Angola", "Democratic People's Republic of Korea"
    )))

Afghanistan for example is included in "s" but not in "s2".

GKi · Answer 1 · 2020-10-20T06:48:54.377

0

You can rbind the to a data.frame converted matrix and use xtabs to sum it up:

tt <- rbind(as.data.frame(as.table(s)), as.data.frame(as.table(s2)))
xtabs(Freq ~ Var1 + Var2, tt)
#             Var2
#Var1          Afghanistan Albania Algeria Argentina Australia Austria
#  Afghanistan           1       2       3         4         5       6
#  Albania               7       9      11        10        14      12
#  Algeria              13      18      20        16        23      18
#  Argentina            19      20      21        22        23      24
#  Australia            25      33      35        28        38      30
#  Austria              31      32      33        34        35      36

edited Oct 20 '20 at 06:48

answered Oct 19 '20 at 16:02

GKi

37,245
2
26
48

I would like to have a summed matrix, but your code only seems to work for the small example. Using it for my large dataset produces a list with x (values), y (countries) and z (countries). – MoonS Oct 19 '20 at 17:41
@JohannaH Can you provide a dataset which produces your observed but unwanted output? – GKi Oct 20 '20 at 06:50
df <- read.table(header=TRUE, text="Var1 Var2 Freq Afghanistan Afghanistan 0 Albania Afghanistan 0 Algeria Afghanistan 0 "). Instead, I would like to have a matrix where the country values in s2 are added to country values in s, by any matching country rownames of s2 in s. – MoonS Oct 20 '20 at 08:00
What are you doing the get this `df`? Can you provide, e.g. by using `dput`, the datasets you are really using. – GKi Oct 20 '20 at 08:24
I have now included dput's of my full data. – MoonS Oct 20 '20 at 09:56
When using your `dput` data I get a ` [1:7, 1:185]` `matrix` and not a `list` nor a `data.frame`. Are you using the lines of code of my answer? – GKi Oct 20 '20 at 10:05
Yes, I have two matrices that I want to join by summing into an aggregated matrix that keeps rowvalues from the first matrix. When using your code, I get a list as output, not a matrix? – MoonS Oct 20 '20 at 11:23
Now I used this: j <- acast(rbind(melt(s), melt(s2converted)), Var1~Var2, fun.aggregate=sum) and it seems to have worked? Are there any problems doing it this way? – MoonS Oct 20 '20 at 11:24
Maybe the other output in your case comes form additional libraries you are using. If your way is producing what you want that it might be OK. – GKi Oct 20 '20 at 11:29
Oh ok! So for you, the code creates a matrix? I am using libraries igraph, dplyr and reshape2. They can mess things up? – MoonS Oct 20 '20 at 11:48

score 0 · Answer 2 · answered Oct 19 '20 at 16:38

I hope I understood correctly. If not, please provide the matrix that you want to reach as a final output.

s <- matrix(1:36, nrow = 6, ncol = 6, byrow=TRUE, dimnames = list(c("Afghanistan","Albania","Algeria","Argentina","Australia","Austria"), c("Afghanistan","Albania","Algeria","Argentina","Australia","Austria")))
s2 <- matrix(1:18, nrow = 3, ncol = 3, byrow=TRUE, dimnames = list(c("Albania","Algeria","Australia"), c("Albania","Algeria","Australia")))

s_new<-s[rownames(s) %in% rownames(s2),]
s_new<-s_new[, colnames(s_new) %in% colnames(s2)]

s_new<-s_new[,order(colnames(s_new))]
s2<-s2[,order(colnames(s2))]

Final<-s_new+s2

Sorry for not being clear, I have updated the question with a final output. — MoonS, Oct 19 '20 at 17:43

Arual · Answer 3 · 2020-10-19T23:36:12.537

I think this is what you are looking for. I do not know if it is the most optimal way but it works.

s <- matrix(1:36, nrow = 6, ncol = 6, byrow=TRUE, dimnames = list(c("Afghanistan","Albania","Algeria","Argentina","Australia","Austria"), c("Afghanistan","Albania","Algeria","Argentina","Australia","Austria")))
s2 <- matrix(1:18, nrow = 3, ncol = 3, byrow=TRUE, dimnames = list(c("Albania","Algeria","Australia"), c("Albania","Algeria","Australia")))

s_new<-s[rownames(s) %in% rownames(s2),]
s_new<-s_new[, colnames(s_new) %in% colnames(s2)]

s_new<-s_new[,order(colnames(s_new))]
s2<-s2[,order(colnames(s2))]

Final<-s_new+s2

s1<-s
for( i in 1:dim(s1)[2])
{
  for( j in 1:dim(s1)[1])
  {
  s1[i,j]<-ifelse(rownames(s1)[i] %in% rownames(Final) & colnames(s)[j] %in% colnames(Final), 
                  Final[which(rownames(Final)==rownames(s)[i]),which(colnames(Final)==colnames(s)[j])],s1[i,j])
  }
}

Somehow the code works on my small example but not on my large dataset. Should I adjust the code in any way when in reality, I have two matrices by 179 and 181 cells respectively? — MoonS, Oct 20 '20 at 06:20

score 0 · Answer 4 · answered Oct 20 '20 at 13:16

This should do.

s1<-s
for( i in 1:dim(s1)[2])
{
  for( j in 1:dim(s1)[1])
  {
    s1[i,j]<-ifelse(rownames(s1)[i] %in% rownames(s2) & colnames(s)[j] %in% colnames(s2), 
                    s1[i,j]+s2[which(rownames(s2)==rownames(s)[i]),which(colnames(s2)==colnames(s)[j])],s1[i,j])
  }
}

score 0 · Answer 5 · answered Oct 20 '20 at 13:52

You can try the code below

s2names <- dimnames(s2)
s[s2names[[1]],s2names[[2]]] <- s[s2names[[1]],s2names[[2]]] + s2

such that

            Afghanistan Albania Algeria Argentina Australia Austria
Afghanistan           1       2       3         4         5       6
Albania               7       9      11        10        14      12
Algeria              13      18      20        16        23      18
Argentina            19      20      21        22        23      24
Australia            25      33      35        28        38      30
Austria              31      32      33        34        35      36

Sum non-conformable matrices by rowname in r

5 Answers5