Calculate correlation between two countries using matching subclasses in R

Question

I’m currently facing this question:

How do I make sure that the correlation between two countries is computed on matching subclasses?

I’m concerned that the correlation between two countries might be calculated on not matching subclasses(named IPC in my dataset e.g. A01B, A01C). See the picture for a wrong example.

Instead, I would like to make sure that the correlation is calculated between two countries on matching dimensions like this:

Ideally, I would like to have a correlation matrix of all countries as output that avoids the above outlined issue. See also this related post for a possible solution.

Many thanks in advance!

My dataset looks like this:

structure(list(LOCATION = 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", "DEU", 
"DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
"DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
"DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
"DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
"DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
"DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
"DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
"DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
"DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", "DEU", 
"DEU", "DEU", "DEU"), Country = 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", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany"), IPC = c("A01B", "A01C", "A01D", 
"A01F", "A01G", "A01H", "A01J", "A01K", "A01L", "A01M", "A01N", 
"A21B", "A21C", "A21D", "A22B", "A22C", "A23B", "A23C", "A23D", 
"A23F", "A23G", "A23J", "A23K", "A23L", "A23N", "A23P", "A24B", 
"A24C", "A24D", "A24F", "A41B", "A41C", "A41D", "A41F", "A41G", 
"A41H", "A42B", "A42C", "A43B", "A43C", "A43D", "A44B", "A44C", 
"A45B", "A45C", "A45D", "A45F", "A46B", "A46D", "A47B", "A47C", 
"A47D", "A47F", "A47G", "A47H", "A47J", "A47K", "A47L", "A61B", 
"A61C", "A61D", "A61F", "A61G", "A61H", "A61J", "A61K", "A61L", 
"A61M", "A61N", "A61P", "A62B", "A62C", "A62D", "A63B", "A63C", 
"A63D", "A63F", "A63G", "A63H", "A63J", "A63K", "F41A", "A01P", 
"A61Q", "A99Z", "A01B", "A01C", "A01D", "A01F", "A01G", "A01H", 
"A01J", "A01K", "A01L", "A01M", "A01N", "A21B", "A21C", "A21D", 
"A22B", "A22C", "A23B", "A23C", "A23D", "A23F", "A23G", "A23J", 
"A23K", "A23L", "A23N", "A23P", "A24B", "A24C", "A24D", "A24F", 
"A41B", "A41C", "A41D", "A41F", "A41G", "A41H", "A42B", "A42C", 
"A43B", "A43C", "A43D", "A44B", "A44C", "A45B", "A45C", "A45D", 
"A45F", "A46B", "A46D", "A47B", "A47C", "A47D", "A47F", "A47G", 
"A47H", "A47J", "A47K", "A47L", "A61B", "A61C", "A61D", "A61F", 
"A61G", "A61H", "A61J", "A61K", "A61L", "A61M", "A61N", "A61P", 
"A62B", "A62C", "A62D", "A63B", "A63C", "A63D", "A63F", "A63G", 
"A63H", "A63J", "A63K", "F41A", "A01P", "A61Q", "A99Z"), 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, 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, 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, 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), Value = c(3.3333, 
4.9167, 5, 0, 8.7639, 3.2835, 0.9236, 10.1159, 1, 5.8833, 15.9971, 
0, 0.0625, 0.6667, 1.8333, 0.8125, 2, 0.7, 0.5611, 0, 4.525, 
0.425, 2.0952, 9.0939, 1, 0.1625, 0, 0, 0, 1, 0, 0, 1.8333, 0.5, 
0, 0, 2, 0, 2.6455, 1.4, 0, 0.9762, 0, 0, 0.6667, 1.4167, 1.8333, 
1.5, 0, 1.8333, 10.9254, 0.2, 2.9167, 10.3389, 2.5556, 6.075, 
1.5, 1.1, 40.7407, 6.2, 1.7357, 17.0141, 3.6046, 2.8333, 3.2108, 
78.5226, 7.8589, 27.4342, 7.7542, 35.3732, 3.9167, 2.1667, 0.4615, 
13.3333, 5.5167, 0, 13.9167, 0.3333, 4.2333, 0, 0, 1.9762, 0.25, 
2.7989, 0, 3.29, 2.6667, 5.3333, 5.1667, 9.7125, 4.6444, 9.3667, 
28.4773, 2.5, 6.25, 104.0737, 1.3667, 2.9833, 3.3, 2.1579, 12.575, 
3.7256, 1.4904, 1.0278, 1.2167, 4.031, 2.2814, 11.3184, 27.9462, 
1.5, 2.0896, 3, 3.1, 5.5333, 0.5667, 2.4777, 0, 10.8472, 0.3333, 
0, 1.7, 3, 0, 8.2727, 2.6667, 1.6167, 9.9167, 1.5, 1, 6.5333, 
12.0556, 1.75, 10.6357, 2.1429, 15.5, 25.8248, 0.8333, 3.5667, 
9.575, 2.5833, 15.3905, 15.3224, 35.5274, 134.6058, 35.998, 1.5167, 
83.7869, 13.9548, 9.5417, 12.5108, 502.65, 54.457, 55.5753, 20.5367, 
250.0678, 8.35, 17.6833, 3.8556, 14.4708, 11.45, 0, 6.0885, 2.25, 
5.4167, 1.5, 0, 12.1333, 0.8095, 65.8262, 0)), row.names = c(NA, 
-170L), groups = structure(list(Country = 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", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany", "Germany", 
"Germany", "Germany", "Germany", "Germany", "Germany"), IPC = c("A01B", 
"A01C", "A01D", "A01F", "A01G", "A01H", "A01J", "A01K", "A01L", 
"A01M", "A01N", "A01P", "A21B", "A21C", "A21D", "A22B", "A22C", 
"A23B", "A23C", "A23D", "A23F", "A23G", "A23J", "A23K", "A23L", 
"A23N", "A23P", "A24B", "A24C", "A24D", "A24F", "A41B", "A41C", 
"A41D", "A41F", "A41G", "A41H", "A42B", "A42C", "A43B", "A43C", 
"A43D", "A44B", "A44C", "A45B", "A45C", "A45D", "A45F", "A46B", 
"A46D", "A47B", "A47C", "A47D", "A47F", "A47G", "A47H", "A47J", 
"A47K", "A47L", "A61B", "A61C", "A61D", "A61F", "A61G", "A61H", 
"A61J", "A61K", "A61L", "A61M", "A61N", "A61P", "A61Q", "A62B", 
"A62C", "A62D", "A63B", "A63C", "A63D", "A63F", "A63G", "A63H", 
"A63J", "A63K", "A99Z", "F41A", "A01B", "A01C", "A01D", "A01F", 
"A01G", "A01H", "A01J", "A01K", "A01L", "A01M", "A01N", "A01P", 
"A21B", "A21C", "A21D", "A22B", "A22C", "A23B", "A23C", "A23D", 
"A23F", "A23G", "A23J", "A23K", "A23L", "A23N", "A23P", "A24B", 
"A24C", "A24D", "A24F", "A41B", "A41C", "A41D", "A41F", "A41G", 
"A41H", "A42B", "A42C", "A43B", "A43C", "A43D", "A44B", "A44C", 
"A45B", "A45C", "A45D", "A45F", "A46B", "A46D", "A47B", "A47C", 
"A47D", "A47F", "A47G", "A47H", "A47J", "A47K", "A47L", "A61B", 
"A61C", "A61D", "A61F", "A61G", "A61H", "A61J", "A61K", "A61L", 
"A61M", "A61N", "A61P", "A61Q", "A62B", "A62C", "A62D", "A63B", 
"A63C", "A63D", "A63F", "A63G", "A63H", "A63J", "A63K", "A99Z", 
"F41A"), .rows = structure(list(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 
    9L, 10L, 11L, 83L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 
    20L, 21L, 22L, 23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L, 
    32L, 33L, 34L, 35L, 36L, 37L, 38L, 39L, 40L, 41L, 42L, 43L, 
    44L, 45L, 46L, 47L, 48L, 49L, 50L, 51L, 52L, 53L, 54L, 55L, 
    56L, 57L, 58L, 59L, 60L, 61L, 62L, 63L, 64L, 65L, 66L, 67L, 
    68L, 69L, 70L, 84L, 71L, 72L, 73L, 74L, 75L, 76L, 77L, 78L, 
    79L, 80L, 81L, 85L, 82L, 86L, 87L, 88L, 89L, 90L, 91L, 92L, 
    93L, 94L, 95L, 96L, 168L, 97L, 98L, 99L, 100L, 101L, 102L, 
    103L, 104L, 105L, 106L, 107L, 108L, 109L, 110L, 111L, 112L, 
    113L, 114L, 115L, 116L, 117L, 118L, 119L, 120L, 121L, 122L, 
    123L, 124L, 125L, 126L, 127L, 128L, 129L, 130L, 131L, 132L, 
    133L, 134L, 135L, 136L, 137L, 138L, 139L, 140L, 141L, 142L, 
    143L, 144L, 145L, 146L, 147L, 148L, 149L, 150L, 151L, 152L, 
    153L, 154L, 155L, 169L, 156L, 157L, 158L, 159L, 160L, 161L, 
    162L, 163L, 164L, 165L, 166L, 170L, 167L), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), row.names = c(NA, -170L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))

user_12505251 · Answer

One way to do this: seperate the data per country and join those data.frames together by IPC

library(dplyr)

data %>% 
  filter(LOCATION == "AUS") %>% 
  full_join(data %>% filter(LOCATION == "DEU"), by="IPC")

returns

# A tibble: 85 x 9
# Groups:   IPC [85]
   LOCATION.x Country.x IPC   Time.x Value.x LOCATION.y Country.y Time.y Value.y
                                   
 1 AUS        Australia A01B    2000   3.33  DEU        Germany     2000    3.29
 2 AUS        Australia A01C    2000   4.92  DEU        Germany     2000    2.67
 3 AUS        Australia A01D    2000   5     DEU        Germany     2000    5.33
 4 AUS        Australia A01F    2000   0     DEU        Germany     2000    5.17
 5 AUS        Australia A01G    2000   8.76  DEU        Germany     2000    9.71
 6 AUS        Australia A01H    2000   3.28  DEU        Germany     2000    4.64
 7 AUS        Australia A01J    2000   0.924 DEU        Germany     2000    9.37
 8 AUS        Australia A01K    2000  10.1   DEU        Germany     2000   28.5 
 9 AUS        Australia A01L    2000   1     DEU        Germany     2000    2.5 
10 AUS        Australia A01M    2000   5.88  DEU        Germany     2000    6.25
# ... with 75 more rows

So Value.x are Australia's values, Value.y Germany's. Since we joined by IPC the values belong to matching subclasses. Make sure to take care of missing values and perhaps have a look at matching Time's, if necessary.

Calculate correlation between two countries using matching subclasses in R

1 Answers