Guest User

Untitled

a guest
May 23rd, 2018
74
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.05 KB | None | 0 0
  1. df <- merge(df,df
  2. ,by.x=c("from_id","to_id"),by.y=c("to_id","from_id")
  3. ,all.x=TRUE,all.y=TRUE)
  4. df <- mutate(df,Messages_sent.x=coalesce(Messages_sent.x,0),
  5. Messages_sent.y=coalesce(Messages_sent.y,0))
  6. df$row <- 1:nrow(df)
  7. rbind(select(df,-Messages_sents.y) %>%
  8. rename(Messages_sent=Messages_sent.x),
  9. select(df,-Messages_sent.x) %>%
  10. rename(Messages_sent=Messages_sent.y,from_id=to_id,to_id=from_id)
  11. ) %>% arrange(row) %>% select(-row)
  12.  
  13. df <- data.frame(from_id=c(624227,624227,624227,624227,624227,624227,667255,667255,667255,7134655,713465),
  14. to_id = c(352731,693915,184455,771100,503940,91558,626814,857601,862512,156874,419242),
  15. message_sent=c(1,6,2,1,1,1,2,7,3,1,1))
  16.  
  17. #create another dataset where from and to columns are switched
  18. df.swap <- data.frame(from_id = df$to_id, to_id=df$from_id)
  19.  
  20. # merge 2 together
  21. df.full <- merge(df,df.swap, by=c("from_id","to_id"), all=TRUE)
  22.  
  23. # fill missing values with 0
  24. # those records will correspond to all the pairs where
  25. # someone did not send any messages back
  26. df.full$message_sent[is.na(df.full$message_sent)] <- 0
  27.  
  28.  
  29. #create a unique ID for each pair. This can be down various ways. One easy way is to
  30. df.full$pair_id = sprintf("%06d%6d",pmin(df.full$from_id,df.full$to_id ),
  31. pmax(df.full$from_id,df.full$to_id ))
  32.  
  33. # calculate total time for each pair:
  34. total.time <- aggregate(df.full$message_sent, list(df.full$pair_id), sum)
  35. names(total.time) <- c("pair_id", "total_sum")
  36.  
  37. # if this total time needs to be a part of the data.frame you can merge it back
  38. df.full <- merge(df.full, total.time, by = c("pair_id"), all=TRUE)
  39. head(df.full)
  40. # pair_id from_id to_id message_sent total_sum
  41. # 1 091558624227 91558 624227 0 1
  42. # 2 091558624227 624227 91558 1 1
  43. # 3 1568747134655 156874 7134655 0 1
  44. # 4 1568747134655 7134655 156874 1 1
  45. # 5 184455624227 624227 184455 2 2
  46. # 6 184455624227 184455 624227 0 2
Add Comment
Please, Sign In to add comment