Pivot an origin/destination/value tibble to calculate net values #rstatstips

netflows.r

# in hindsight i probably should've used a network analysis package to do this

library(tidyverse)
set.seed(1)

# create a sample data frame of origins and destinations
testdf <-
  tibble(
    origin = sample(letters, 10),
    destination = sample(letters, 10),
    value = runif(10, 1, 100)) %>%
  bind_rows(tibble(
    origin = .$destination,
    destination = .$origin,
    value = runif(10, 1, 100))) %>%
  print()
#> # A tibble: 20 × 3
#>    origin destination value
#>    <chr>  <chr>       <dbl>
#>  1 y      v           87.1 
#>  2 d      n           34.7 
#>  3 g      j           48.7 
#>  4 a      g           60.4 
#>  5 b      i           49.9 
#>  6 k      o           19.4 
#>  7 n      e           82.9 
#>  8 r      z           67.2 
#>  9 w      y           79.6 
#> 10 j      t           11.7 
#> 11 v      y           72.6 
#> 12 n      d           41.7 
#> 13 j      g           82.3 
#> 14 g      a           65.1 
#> 15 i      b           78.5 
#> 16 o      k           55.8 
#> 17 e      n           53.4 
#> 18 z      r           79.1 
#> 19 y      w            3.31
#> 20 t      j           48.2

testdf %>%
  # now create the two country key
  mutate(
    direction = if_else(origin < destination, "toA", "toB"),
    pairkey = paste(pmin(origin, destination), pmax(origin, destination),
      sep = "_")) %>%
  # do the pivot!
  pivot_wider(
    id_cols = pairkey, names_from = "direction",
    values_from = c(origin, destination, value),
    values_fn = function(x) x[[1]]) %>%
  # ditch the extra country columns
  select(countryA = destination_toA, countryB = destination_toB, value_toA,
    value_toB) %>%
  # calculate net flow
  mutate(
    netflow = abs(value_toB - value_toA),
    net_destination = if_else(value_toB > value_toA, countryB, countryA))
#> # A tibble: 10 × 6
#>    countryA countryB value_toA value_toB netflow net_destination
#>    <chr>    <chr>        <dbl>     <dbl>   <dbl> <chr>          
#>  1 y        v             72.6     87.1    14.5  v              
#>  2 n        d             34.7     41.7     7.02 d              
#>  3 j        g             48.7     82.3    33.5  g              
#>  4 g        a             60.4     65.1     4.70 a              
#>  5 i        b             49.9     78.5    28.6  b              
#>  6 o        k             19.4     55.8    36.3  k              
#>  7 n        e             53.4     82.9    29.5  e              
#>  8 z        r             67.2     79.1    12.0  r              
#>  9 y        w             79.6      3.31   76.3  y              
#> 10 t        j             11.7     48.2    36.6  j