Match every observation with eachother

Question

I have the following dataframe with 1051 observations.

customer_id  long       lat
11111        111.320    110.574 
11112        111.243    110.311

I need to manipulate the dataframe so that every observation is matched up with every observation. This will allow me to get the distance between each observation.

customer_id_a  long_a   lat_b    customer_id_b  long_a    lat_b
11111          111.320  110.574  11112          111.243   110.311

In R, how do I do this?

Possible duplicate of [How to do cross join in R?](https://stackoverflow.com/questions/10600060/how-to-do-cross-join-in-r) — Mike, Nov 16 '18 at 17:47
Don't reinvent the wheel. Check e.g. `geosphere::distm` No need to reshape. — Henrik, Nov 16 '18 at 18:20

score 1 · Accepted Answer · answered Nov 16 '18 at 18:34

A solution in base R. First I create some toy data:

n <- 50
df <- data.frame(customer_id = sprintf("1%0.5d", 1:50),
                 long = rnorm(n)+105, lat = rnorm(n)+110)
head(df)
#  customer_id     long      lat
#1      100001 105.7532 109.4935
#2      100002 102.0772 110.9918
#3      100003 102.8655 110.7422
#4      100004 103.3984 111.1385
#5      100005 102.8614 111.8068
#6      100006 105.1860 110.3117

With this data, we get all combinations, replicate df appropriately, and combine the two:

cs <- combn(nrow(df), 2)   
new_df <- cbind(a = df[cs[1,], ], b = df[cs[2,], ])    
rownames(new_df) <- NULL  # Remove default rownames

head(new_df)
#  a.customer_id   a.long    a.lat b.customer_id   b.long    b.lat
#1        100001 105.7532 109.4935        100002 102.0772 110.9918
#2        100001 105.7532 109.4935        100003 102.8655 110.7422
#3        100001 105.7532 109.4935        100004 103.3984 111.1385
#4        100001 105.7532 109.4935        100005 102.8614 111.8068
#5        100001 105.7532 109.4935        100006 105.1860 110.3117
#6        100001 105.7532 109.4935        100007 103.8722 111.2530

akrun · Answer 2 · 2018-11-16T18:04:02.007

We can use dcast from data.table

library(data.table)
dcast(setDT(df1)[, newid := 1], newid ~ letters[rowid(newid)], 
     value.var = c('customer_id', 'long', 'lat'))[, newid := NULL][]
#    customer_id_a customer_id_b long_a  long_b   lat_a   lat_b
#1:         11111         11112 111.32 111.243 110.574 110.311

Or use reshape from base R

df2 <- transform(df1, newid = 1)
df2$Seq <- with(df2, letters[ave(newid, newid, FUN = seq_along)])
reshape(df2, idvar = 'newid', timevar= 'Seq', direction = 'wide')[-1]
#  customer_id.a long.a   lat.a customer_id.b  long.b   lat.b
#1         11111 111.32 110.574         11112 111.243 110.311

data

df1 <- structure(list(customer_id = 11111:11112, long = c(111.32, 111.243
), lat = c(110.574, 110.311)), class = "data.frame", row.names = c(NA, 
 -2L))

Match every observation with eachother

2 Answers2

data