I need help trying to figure out how to do this in R.

I have a df that looks like:

I am trying to add three columns that look like this:

The columns that need to be created need to first match which location does the customer 'Like', calculate their distance from it, and then put the distance in the 'Distance from Like' column and so on.

I am using this function to calculate the distance:

library(zipcodeR)
zip_distance(zipcode_a, zipcode_b, lonlat = TRUE, units = "meters")

(Credit: First answer on Conditional column value calculation, zip code to lat/long)

CodePudding user response：

If you change your column names so that they are consistently formatted with location_variable then you can use tidyr's pivot_long to get everything into long format, and then you can calculate all the distances as the zip codes for all locations are now in a column. You can then either leave it as a long data format or wrangle it back to the wide format you requested

library(tidyverse)
test<- read.table( text = "ID,UserZIPcode,loc1_zipcode,loc2_zipcode,loc3_zipcode,loc1_Opinion,loc2_Opinion,loc3_Opinion
1,1,90804,91723,90814,91604,Neutral,Like,Dislike", sep = "," )

test_distances <- test %>% 
        pivot_longer(cols = c(-ID, -UserZIPcode), names_pattern  = "(.*)_(.*)", names_to = c("location",".value")) %>% 
        mutate(Distance = zip_distance(UserZIPcode, zipcode, lonlat = TRUE, units = "meters"))
  


test_distances %>% 
  pivot_wider(id_cols = ID, names_from = Opinion, names_prefix = "Distance_from", values_from = "Distance") %>% 
  right_join(test)