Styjun

For the project at hand, I am forced to join several data frames by a character column. This is sometimes problematic due to - for example - trailing whitespace but can also be remedied easily. However, in this case the joining does not work and I am unable to figure out what distinguishes the character values in the column used to do the joining.

Since in its original format the problem was not reproducible, here is a link where data in question can be downloaded. It looks like this:

readRDS("path/sourceA") -> sourceA
sourceA
# A tibble: 1 x 2
 Name category
 <chr> <dbl>
1 Grundschule Kronsberg 1

readRDS("path/sourceB") -> sourceB
sourceB
# A tibble: 1 x 2
 Name value
 <chr> <dbl>
1 Grundschule Kronsberg 2

I want to join these data frames together using the common id variable Name. As you can see, it appears the value is exactly the same in both frames. However, when I apply any joining procedure, this happens:

library(tidyverse)
joined.df <- full_join(sourceA, sourceB, by = "Name")

joined.df
# A tibble: 2 x 3
 Name category value
 <chr> <dbl> <dbl>
1 Grundschule Kronsberg 1 NA
2 Grundschule Kronsberg NA 2

In trying to figure this out, I tried to remove the whitespace from the Name column but, using standard procedure, was only able to so for sourceA. For sourceB it looks as if the procedure does not cut out the whitespace in between "Grundschule" and "Kronsberg".

joined.df %>%
 mutate(Name_test = stringr::str_replace_all(Name, fixed(" "), ""))

# A tibble: 2 x 4
 Name category value Name_test 
 <chr> <dbl> <dbl> <chr> 
1 Grundschule Kronsberg 1 NA GrundschuleKronsberg 
2 Grundschule Kronsberg NA 2 Grundschule Kronsberg

Weirdly, when using stringr::str_replace_all(Name, "\pWHITE_SPACE", ""), it works:

joined.df %>%
 mutate(Name_test = stringr::str_replace_all(Name, "\pWHITE_SPACE", ""))

# A tibble: 2 x 4
 Name category value Name_test 
 <chr> <dbl> <dbl> <chr> 
1 Grundschule Kronsberg 1 NA GrundschuleKronsberg
2 Grundschule Kronsberg NA 2 GrundschuleKronsberg

I don't know anything about how the lookup in "\pWHITE_SPACE"differs from fixed(" ") under the hood, but I thought it might be good clue for someone who does.

After quite a discussion in the comments, I was able to solve the problem. While the Name variables looked identical (and where parsed identically by dput()), there was a subtle difference when converting the characters into their ASCII codes:

library(gtools)

asc(sourceA$Name)
 Grundschule Kronsberg
 [1,] 71
 [2,] 114
 [3,] 117
 [4,] 110
 [5,] 100
 [6,] 115
 [7,] 99
 [8,] 104
 [9,] 117
[10,] 108
[11,] 101
[12,] 32
[13,] 75
[14,] 114
[15,] 111
[16,] 110
[17,] 115
[18,] 98
[19,] 101
[20,] 114
[21,] 103

asc(sourceB$Name)
 Grundschule Kronsberg
 [1,] 71
 [2,] 114
 [3,] 117
 [4,] 110
 [5,] 100
 [6,] 115
 [7,] 99
 [8,] 104
 [9,] 117
[10,] 108
[11,] 101
[12,] 194
[13,] 160
[14,] 75
[15,] 114
[16,] 111
[17,] 110
[18,] 115
[19,] 98
[20,] 101
[21,] 114
[22,] 103

sourceB has an extra code compared to sourceA and different values in position 12 and 13. Using chr() (also from gtools), I was able to re-convert the ASCII codes into characters:

 chr(asc(sourceA$Name))
 [1] "G" "r" "u" "n" "d" "s" "c" "h" "u" "l" "e" " " "K" "r" "o" "n" "s" "b" "e" "r" "g"

chr(asc(sourceB$Name))
 [1] "G" "r" "u" "n" "d" "s" "c" "h" "u" "l" "e" "Â" " " "K" "r" "o" "n" "s" "b" "e" "r" "g"

In sourceB, there is an extra  (ASCII decimal code 194) in the string, and the white space is coded with the decimal 160 instead of 32. I still don't know why in conjunction these two ASCII codes were displayed as a regular white space, but was able to solve the issue by simply replacing all white spaces with " "

sourceB <- sourceB %>%
 mutate(Name = stringr::str_replace_all(Name, "\pWHITE_SPACE", " "))

full_join(sourceA, sourceB, by = "Name")
# A tibble: 1 x 3
 Name category value
 <chr> <dbl> <dbl>
1 Grundschule Kronsberg 1 2

This (somehow) changed the ASCII codes so that they now align with one another:

chr(asc(sourceB$Name))
 [1] "G" "r" "u" "n" "d" "s" "c" "h" "u" "l" "e" " " "K" "r" "o" "n" "s" "b" "e" "r" "g"