How to transform data into wider/pivot format in R?-CodePudding

I have a trade between cities. For example, London sells Cars to Liverpool at the price of 1000, while Liverpool purchases Cars from London at the price of 1100. Also, we know the price of Bike that is sold from Manchester to Bristol (1700), but don't know the its price from Bristol to Manchester:

df <- data.frame (Product = c("Cars", "Cars", "Bike"),
                  Flow  = c("Sell", "Purchase", "Sell"),
                  city = c("London", "Liverpool","manchester"),
                  Destination = c("Liverpool","London","Bristol"),
                  Price = c(1000, 1100, 1700))

  Product     Flow          city      Destination    Price
1    Cars     Sell        London        Liverpool    1000
2    Cars Purchase     Liverpool           London    1100
3    Bike     Sell    Manchester          Bristol    1700

Now I want to reshape data and have trade time on the same line. Expected outcome:

  Product      city   Destination    Sell_P    Purch_P
1    Cars    London     Liverpool      1000       1100
2    Bike    Manchester   Bristol      1700        NA

Sell_P represents selling price from London to Liverpool, while Purch_P represents purchasing price by Liverpool from London. Price difference on the same product is caused by insurance and transportation costs

CodePudding user response：

We could do it this way:

After pivoting with glueing the names, we could use the alternating behaviour of city and Destination. With grouping and filling !up and slice the first of each group we get this:

library(dplyr)
library(tidyr)

df %>% 
  pivot_wider(
    names_from = Flow,
    values_from = Price, 
    names_glue = "{Flow}_P"
    ) %>% 
  mutate(id = row_number()) %>% 
  group_by(Product) %>% 
  fill(Purchase_P, .direction="up") %>% 
  slice(1) %>% 
  ungroup() %>% 
  arrange(id) %>% 
  select(-id)

  Product city       Destination Sell_P Purchase_P
  <chr>   <chr>      <chr>        <dbl>      <dbl>
1 Cars    London     Liverpool     1000       1100
2 Bike    Manchester Bristol       1700         NA