Created
March 24, 2022 06:16
Pivot an origin/destination/value tibble to calculate net values #rstatstips
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment