Calculate correlation for all country pairs over multiple years in R

Question

I’m currently facing the following issue: Based on my previous question, I need to calculate the correlation between nations (Value.x and Value.y) for multiple countries for multiple years. What is the best way to achieve that?

An expected output should be something like this (for all country pairs and all years). Thus, all correlation results are stored in a table/dataframe.


correlation country_x     country_y       Time

0.787812    Germany     United States   2000

The first 100 rows of my dataset look like this:



structure(list(LOCATION.x = c("AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", "AUS", 
"AUS", "AUS", "AUS", "AUS", "AUS"), Country.x = c("Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia"), IPC = c("A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", "A01B", 
"A01B", "A01B", "A01B"), Time = c(2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 
2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 
2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 
2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 
2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 2001L, 
2001L, 2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 2002L, 
2002L, 2002L, 2002L, 2002L, 2002L, 2002L), Value.x = c(3.3333, 
3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 
3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 
3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 
3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 
3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 3.3333, 
3.3333, 3.3333, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 
4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 
4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 
4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 
4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 
4.2143, 4.2143, 4.2143, 4.2143, 4.2143, 1.1944, 1.1944, 1.1944, 
1.1944, 1.1944, 1.1944, 1.1944, 1.1944, 1.1944, 1.1944, 1.1944, 
1.1944, 1.1944, 1.1944), LOCATION.y = c("AUS", "AUT", "BEL", 
"CAN", "CZE", "DNK", "FIN", "FRA", "DEU", "GRC", "HUN", "ISL", 
"IRL", "ITA", "JPN", "KOR", "LUX", "MEX", "NLD", "NZL", "NOR", 
"POL", "PRT", "SVK", "ESP", "SWE", "CHE", "TUR", "GBR", "USA", 
"BRA", "CHL", "CHN", "COL", "CRI", "EST", "IND", "IDN", "ISR", 
"LVA", "LTU", "SVN", "ZAF", "AUS", "AUT", "BEL", "CAN", "CZE", 
"DNK", "FIN", "FRA", "DEU", "GRC", "HUN", "ISL", "IRL", "ITA", 
"JPN", "KOR", "LUX", "MEX", "NLD", "NZL", "NOR", "POL", "PRT", 
"SVK", "ESP", "SWE", "CHE", "TUR", "GBR", "USA", "BRA", "CHL", 
"CHN", "COL", "CRI", "EST", "IND", "IDN", "ISR", "LVA", "LTU", 
"SVN", "ZAF", "AUS", "AUT", "BEL", "CAN", "CZE", "DNK", "FIN", 
"FRA", "DEU", "GRC", "HUN", "ISL", "IRL", "ITA"), Country.y = c("Australia", 
"Austria", "Belgium", "Canada", "Czech Republic", "Denmark", 
"Finland", "France", "Germany", "Greece", "Hungary", "Iceland", 
"Ireland", "Italy", "Japan", "Korea", "Luxembourg", "Mexico", 
"Netherlands", "New Zealand", "Norway", "Poland", "Portugal", 
"Slovak Republic", "Spain", "Sweden", "Switzerland", "Turkey", 
"United Kingdom", "United States", "Brazil", "Chile", "China", 
"Colombia", "Costa Rica", "Estonia", "India", "Indonesia", "Israel", 
"Latvia", "Lithuania", "Slovenia", "South Africa", "Australia", 
"Austria", "Belgium", "Canada", "Czech Republic", "Denmark", 
"Finland", "France", "Germany", "Greece", "Hungary", "Iceland", 
"Ireland", "Italy", "Japan", "Korea", "Luxembourg", "Mexico", 
"Netherlands", "New Zealand", "Norway", "Poland", "Portugal", 
"Slovak Republic", "Spain", "Sweden", "Switzerland", "Turkey", 
"United Kingdom", "United States", "Brazil", "Chile", "China", 
"Colombia", "Costa Rica", "Estonia", "India", "Indonesia", "Israel", 
"Latvia", "Lithuania", "Slovenia", "South Africa", "Australia", 
"Austria", "Belgium", "Canada", "Czech Republic", "Denmark", 
"Finland", "France", "Germany", "Greece", "Hungary", "Iceland", 
"Ireland", "Italy"), Value.y = c(3.3333, 0, 0, 0.6667, 0, 1.1429, 
0.7333, 7.0667, 3.29, 0, 0, 0, 0, 1, 0.2, 1, 0, 0, 0.4762, 0, 
3, 0, 0, 0, 0, 3, 0, 0, 2.5, 13.2, 0, 0, 0, 0, 0, 0, 0.5, 0, 
0, 0, 0, 0, 1, 4.2143, 0.3333, 0, 0, 0, 3, 1, 2.5, 5.0833, 0, 
0, 0, 0, 1.4167, 0.5667, 0, 0, 0.5, 1.6278, 0.5, 1, 0, 0, 0, 
0, 0, 0, 0, 2.45, 9.1742, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0.1, 1.1944, 0.5, 0.5, 2.3409, 0.0909, 0, 0.6, 8, 6.0286, 0, 
0, 0, 0, 1.5)), row.names = c(NA, -100L), groups = structure(list(
    IPC = "A01B", .rows = structure(list(1:100), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), row.names = c(NA, -1L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))

In response to the comments, I added this example dataset for didactic purposes. It should work with the code provided by @r2evans. (It assumes that the listed countries have only two sub dimensions A01B and A01C for the years 2000 and 2001.)


structure(list(LOCATION.x = c("AUS", "AUS", "USA", "USA", "AUS", 
"AUS", "USA", "USA"), Country.x = c("Australia", "Australia", 
"United States", "United States", "Australia", "Australia", "United States", 
"United States"), IPC = c("A01B", "A01C", "A01B", "A01C", "A01B", 
"A01C", "A01B", "A01C"), Time = c(2000, 2000, 2000, 2000, 2001, 
2001, 2001, 2001), Value.x = c(7, 42, 92, 1, 70, 87, 88, 3), 
    LOCATION.y = c("DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
    "DEU", "DEU"), Country.y = c("Germany", "Germany", "Germany", 
    "Germany", "Germany", "Germany", "Germany", "Germany"), Value.y = c(97, 
    85, 25, 14, 15, 97, 97, 52)), row.names = c(NA, -8L), class = c("tbl_df", 
"tbl", "data.frame"))

Many thanks in advance!

user_3358272 · Answer

I think you can do a simple summarize for that,

dat %>%
  group_by(Country.x, Country.y) %>%
  summarize(corr = cor(Value.x, Value.y)) %>%
  filter(complete.cases(.))
# Warning in cor(Value.x, Value.y) : the standard deviation is zero
##### repeated 21 times
# # A tibble: 21 x 3
# # Groups:   Country.x [1]
#    Country.x Country.y        corr
#                    
#  1 Australia Australia       1    
#  2 Australia Austria        -0.539
#  3 Australia Belgium        -0.959
#  4 Australia Canada         -1.00 
#  5 Australia Czech Republic -0.959
#  6 Australia Denmark         0.931
#  7 Australia Finland         0.911
#  8 Australia France         -0.825
#  9 Australia Germany        -0.550
# 10 Australia India          -1    
# # ... with 11 more rows

With more data and wanting to add Time to your grouping, then use

dat %>%
  group_by(Time, Country.x, Country.y) %>%
  summarize(corr = cor(Value.x, Value.y))

This doesn't work here due to the small sample but should work on your larger data.

Calculate correlation for all country pairs over multiple years in R

1 Answers