Vfrdtyky

I have a dataframe of this form

distance city obs

9        0    1

5        1    2

7        0    3

6        0    4

5        0    5

10       1    6

11       0    7

15       0    8

I would like to create a new column "difference" which computes the difference of the values in the column "distance" between each observation and its closest (in terms of values in the column distance) city.

In other words I want to have something like this

    distance city obs difference

    9        0    1   1

    5        1    2   0

    7        0    3   2

    6        0    4   1

    5        0    5   0

    10       1    6   0

    11       0    7   1

    15       0    8   5

where the first obs in the new column has 1 because this is the difference between distances 9 and 10, which are the values in the column distance associated with observation 1 and its closest city (obs 6 in this case) respectively. The same reasoning applies for the other obs. For instance obs 3 presents a difference of 2 since this represents the difference between the values in the column distance between obs 3 itself and its closest city, which in this is case is observation 2. Cities themselves present 0.

Can anyone help me with this??

Many many thanks.

Here's a dplyr solution where you find the minimum distance to any of the cities:

library(dplyr)

df %>% rowwise %>% mutate(difference = min(abs(df$distance[df$city == 1] - distance)))

#Source: local data frame [8 x 4]

#Groups: <by row>

#

# A tibble: 8 x 4

#  distance  city   obs difference

#     <int> <int> <int>      <int>

#1        9     0     1          1

#2        5     1     2          0

#3        7     0     3          2

#4        6     0     4          1

#5        5     0     5          0

#6       10     1     6          0

#7       11     0     7          1

#8       15     0     8          5

And here is a base-r approach:

df$difference <- sapply(df$distance,function(x)  min(abs(df$distance[df$city == 1] - x)))

df

#  distance city obs difference

#1        9    0   1          1

#2        5    1   2          0

#3        7    0   3          2

#4        6    0   4          1

#5        5    0   5          0

#6       10    1   6          0

#7       11    0   7          1

#8       15    0   8          5

This is the same as @jasbner's except using a rolling join, which I suspect might be more efficient in some cases:

library(data.table)

setDT(DF)



DF[, v := DF[city == 1][.SD, on=.(distance), roll="nearest", abs(x.distance-i.distance)]]



   distance city obs v

1:        9    0   1 1

2:        5    1   2 0

3:        7    0   3 2

4:        6    0   4 1

5:        5    0   5 0

6:       10    1   6 0

7:       11    0   7 1

8:       15    0   8 5