Subset base function ignoring repeated entries when outputting

user_10198914 0
Viewed 37

I recently asked how to use dictionary files to recode values in a dataset (Working with data values (dictionary). dataframes and recoding in R)

I am with a simpler problem, but that fix isn't working. Suppose I have the following dataset, each row is a geographical unit, and column V1 lists the "first neighbor" that a geographical package found, but lists it using the row number:

V1 <- c(1, 2, 1)
id <- c(110001, 110002, 110003)
dataset <- as.data.frame(matrix(c(id, V1), ncol=2))
colnames(dataset) <- c("id", "V1")

So on this dataset, region 110001 is a neighbor of itself (V1 = 1), and region 110003 is a neighbor of 110001 (V1 = 1). Now, instead of displaying V1 (first neighbor) as "1, 2, 1", I'd like it to be the id of the geographic region, "110001, 110002, 110001".

So, I create a "dictionary" file that contains the rownumber and id of the geographic region:

dictionary <- as.data.frame(matrix(c(dataset$id, 1:nrow(dataset)),ncol=2))
colnames(dictionary) <- c("id","row")

Then, I try to map these using a mutate. Note that I have many neighborhood variables (V1-V30), and I'm only using one in the example, so I will use syntax that translates to all:

new_dataset <- dataset %>% mutate(across(starts_with("V"), ~subset(dictionary, row == cur_column(), select= id)))

What this should do is: run across columns, compare values to the values at the rows of the dictionary, and then return the appropriate id. It seems that the problem is the repeated entries in dataset$V1 (in this example, rows 1 and 3 are equal to "1"). If I proceeded row-by-row, this would work:

first_row <- dataset[1,] %>% mutate(V1 = subset(dictionary, row == V1, select= id))    
second_row <- dataset[2,] %>% mutate(V1 = subset(dictionary, row == V1, select= id))  
third_row <- dataset[3,] %>% mutate(V1 = subset(dictionary, row == V1, select= id))

My impression is that "subset" is ignoring repeated entries. E.g., if I run this:

 subset(dictionary, row == dataset$V1, select= id)

It should return "110001, 110002, 110001", but only returns "110001, 110002".

Any ideas on how to make subset return everything or another approach to this?

subset matching dataframe r
3 Answers

We can use rowwise

library(dplyr)
dataset %>%
     rowwise %>% 
     mutate(V1 = subset(dictionary, row == V1, select= id)$id) %>%
     ungroup

-ouptut

# A tibble: 3 x 2
      id     V1
   <dbl>  <dbl>
1 110001 110001
2 110002 110002
3 110003 110001

Or with data.table

library(data.table)
 setDT(dataset)[dictionary, V1 := i.id, on = .(V1 = row)]
> dataset
       id     V1
1: 110001 110001
2: 110002 110002
3: 110003 110001

If there are multiple columns, e.g. 'V1', 'V2', etc.

dataset$V2 <- V1[c(1, 3, 2)]
nm1 <- paste0("V", 1:2)
setDT(dataset)
for(nm in nm1) 
   dataset[dictionary, (nm) := i.id, on = setNames("row", nm)][]

-output

> dataset
       id     V1     V2
1: 110001 110001 110001
2: 110002 110002 110001
3: 110003 110001 110002
user_37322710

You could use a self left join with a modified dataset:

library(dplyr)

dataset %>% 
  left_join(
    dataset %>% 
      group_by(V1) %>% 
      slice(1),
    by = "V1") %>% 
  select(-V1)

This returns

    id.x   id.y
1 110001 110001
2 110002 110002
3 110003 110001
user_125052510

Maybe a base R option like below?

transform(
  dataset,
  V1 = ave(id, V1, FUN = function(x) head(x, 1))
)

which gives

      id     V1
1 110001 110001
2 110002 110002
3 110003 110001
user_121587570
Related