R - fuzzy join on nearest integer only

Question

Suppose I've got this data set to start with, in this silly layout:

originalDF <- data.frame(
  Index = 1:14,
  Field = c("Name",     "Weight",   "Age",  "Name",     "Weight",   "Age",  "Height",   "Name",     "Weight",   "Age",  "Height",   "Name",     "Age",  "Height"),
  Value = c("Sara",     "115",  "17",   "Bob",  "158",  "22",   "72",   "Irv",  "210",  "42",   "68",   "Fred",     "155",  "65")
  )

I want it to look like this:

Basically, I want to match the Weight, Age, and Height rows to the Name row above it. Splitting the data up is easy using dplyr:

namesDF <- originalDF %>%
  filter(Field == "Name")

detailsDF <- originalDF %>%
  filter(!Field == "Name")

From here, using the Index (row number) seems the best way, i.e. match each row in detailsDF with the entry in namesDF that has the closest Index without going over. I used the fuzzyjoin package and joined them with

fuzzy_left_join(detailsDF, namesDF, by = "Index", match_fun = list(`>`))

This sort of works, but it also joins each row in detailsDF with EVERY row in namesDF with a smaller Index number:

I came up with a solution using the distance to the next Index and filtering out the extra rows that way, but I want to avoid doing this; the actual source file will be over 200k rows, and the temporary resulting dataframe with the extra rows would be too big to fit into memory. Is there anything I can do here? Thanks!

score 3 · Accepted Answer · answered Jan 16 '19 at 19:31

I recommend approaching it in a different way, by keeping track of the most recent "Name" value at each point. fill() from the tidyr package is useful for this.

library(dplyr)
library(tidyr)

originalDF %>%
  mutate(Name = ifelse(Field == "Name", as.character(Value), NA)) %>%
  fill(Name) %>%
  filter(Field != "Name")

Output:

   Index  Field Value Name
1      2 Weight   115 Sara
2      3    Age    17 Sara
3      5 Weight   158  Bob
4      6    Age    22  Bob
5      7 Height    72  Bob
6      9 Weight   210  Irv
7     10    Age    42  Irv
8     11 Height    68  Irv
9     13    Age   155 Fred
10    14 Height    65 Fred

However, if you do want to use the fuzzyjoin approach, you could achieve this with group_by() and slice() on your outcome, where you grab the last row for each value of Index.x.

fuzzy_left_join(detailsDF, namesDF, by = "Index", match_fun = list(`>`)) %>%
  group_by(Index.x) %>%
  slice(n()) %>%
  ungroup()

Output:

# A tibble: 10 x 6
   Index.x Field.x Value.x Index.y Field.y Value.y
     <int> <fct>   <fct>     <int> <fct>   <fct>  
 1       2 Weight  115           1 Name    Sara   
 2       3 Age     17            1 Name    Sara   
 3       5 Weight  158           4 Name    Bob    
 4       6 Age     22            4 Name    Bob    
 5       7 Height  72            4 Name    Bob    
 6       9 Weight  210           8 Name    Irv    
 7      10 Age     42            8 Name    Irv    
 8      11 Height  68            8 Name    Irv    
 9      13 Age     155          12 Name    Fred   
10      14 Height  65           12 Name    Fred

Thanks! I knew there was something much simpler that I was overlooking. And I've pretty much memorized parts of Hadley's R For Data Science book too... — ThingyBlahBlah3, Jan 16 '19 at 21:51

dww · Answer 2 · 2019-01-16T19:21:38.203

You can use

x = which(originalDF$Field == "Name")
originalDF$Name = rep(originalDF$Value[x], times = diff(c(x, NROW(originalDF)+1)))
NewDF = originalDF[originalDF$Field != 'Name', c(4,2,3)]
#    Name  Field Value
# 2  Sara Weight   115
# 3  Sara    Age    17
# 5   Bob Weight   158
# 6   Bob    Age    22
# 7   Bob Height    72
# 9   Irv Weight   210
# 10  Irv    Age    42
# 11  Irv Height    68
# 13 Fred    Age   155
# 14 Fred Height    65

score 1 · Answer 3 · answered Jan 16 '19 at 19:43

You could group by cumsum(Field == "Name"). With dplyr...

library(dplyr) 
originalDF %>% 
  group_by(Name = Value[Field == "Name"][cumsum(Field == "Name")]) %>%
  slice(-1) %>% select(c("Name", "Field", "Value"))

# A tibble: 10 x 3
# Groups:   Name [4]
   Name  Field  Value
   <fct> <fct>  <fct>
 1 Bob   Weight 158  
 2 Bob   Age    22   
 3 Bob   Height 72   
 4 Fred  Age    155  
 5 Fred  Height 65   
 6 Irv   Weight 210  
 7 Irv   Age    42   
 8 Irv   Height 68   
 9 Sara  Weight 115  
10 Sara  Age    17

With data.table...

library(data.table)
data.table(originalDF)[, 
  .SD[-1], 
by=.(Name = Value[Field == "Name"][cumsum(Field == "Name")]), .SDcols=c("Field", "Value")]

R - fuzzy join on nearest integer only

3 Answers3