CODEWITHSUNDEEP
rdplyr
Jdv
Jdv

Reputation: 329

Populate value from a different row when conditions are matched

I am trying to solve the following in dplyr. In a table, find the same combination of people and dates, but in reverse order.

For example:

Person A meets Person B on 11-27-2019 - then there should be another row where Person B meets Person A on the same date.

When this happens, I'd like the "Value Local" to populate on the "Value Traveling". I'd like to find a dplyr solution for this. Below is an example:

library(tidyverse)

Person.Local <- c("A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "C", "C", "C")

Person.Travel <- c("C", "J", "K", "B", "I", "C", "K", "V", "A", "P", "A", "Z", "H")

Date <- c("2019-10-31", "2019-10-14", "2019-10-13", "2019-10-12", "2019-10-12", "2019-10-18", "2019-10-21", "2019-10-22", "2019-10-12", "2019-10-29", "2019-10-31", "2019-04-04", "2019-10-31")

Value.Local <- c(1,4,5,5,7,9,7,5,3,8, 7,4,5)

db <- tibble(Person.Local, Date, Person.Travel, Value.Local)

db

Edit: After some back and forth, ended up solving it like this:

a <- db %>%
  mutate(conc = paste(Person.Local,Date,Person.Travel)) %>%
  select(a.Person.Local = Person.Local, a.Date = Date, a.Person.Travel = Person.Travel, a.Value.Local = Value.Local, conc)

b <- db %>%
  mutate(conc = paste(Person.Travel,Date,Person.Local)) %>%
  select(b.Person.Local = Person.Local, b.Date = Date, b.Person.Travel = Person.Travel, b.Value.Local = Value.Local, conc)

full_join(a,b) %>%
  select(Local = a.Person.Local, Date = a.Date, Travel = a.Person.Travel, Value = a.Value.Local, Value.Travel = b.Value.Local)

Upvotes: 0

Views: 43

Answers (1)

Edward
Edward

Reputation: 18683

A couple of joins should work:

db %>% left_join(
  db %>% inner_join(db,
                   by=c("Person.Local"="Person.Travel", 
                        "Person.Travel"="Person.Local",
                        "Date"="Date"), suffix=c("",".y")) %>%
    rename(Value.Travel=Value.Local.y), 
  by=c("Person.Local"="Person.Local", 
       "Person.Travel"="Person.Travel",
       "Date"="Date", "Value.Local"))

# A tibble: 13 x 5
   Person.Local Date       Person.Travel Value.Local Value.Travel
   <chr>        <chr>      <chr>               <dbl>        <dbl>
 1 A            2019-10-31 C                       1            7
 2 A            2019-10-14 J                       4           NA
 3 A            2019-10-13 K                       5           NA
 4 A            2019-10-12 B                       5            3
 5 A            2019-10-12 I                       7           NA
 6 B            2019-10-18 C                       9           NA
 7 B            2019-10-21 K                       7           NA
 8 B            2019-10-22 V                       5           NA
 9 B            2019-10-12 A                       3            5
10 B            2019-10-29 P                       8           NA
11 C            2019-10-31 A                       7            1
12 C            2019-04-04 Z                       4           NA
13 C            2019-10-31 H                       5           NA

By the way, you should create data like this to prevent warnings about factor levels:

db <- tibble(Person.Local, Date, Person.Travel, Value.Local)

Edit: Thanks to Josedv for reminding me about the Date they met. ^_^

Upvotes: 1

Related Questions