Order data table of users navigation paths based on previous page (like parent child relationship)

user_3778744 0
Viewed 63

What will be the most efficient solution to order a data table based on a parent -> child relationship?

Given this nibble:

df <- tribble(
~user,   ~page,    ~from,
"user1", "home",  "-",
"user1", "product page",  "home",
"user1", "checkout",  "promotion",
"user1", "promotion",  "product page",
"user2", "home",  "-",
"user2", "promotion",  "home",
"user2", "checkout",  "promotion" )

I want to produce this output:

df_results <- tribble(
~user,   ~page,    ~from,   ~order,
"user1", "home",  "-",     1,
"user1", "product page",  "home", 2,
"user1", "promotion",  "product page", 3,
"user1", "checkout",  "promotion", 4,
"user2", "home",  "-",     1,
"user2", "promotion",  "home", 2,
"user2", "checkout",  "promotion",  3 )

It's quite simple to identify the root element using something like:

df_results <- df %>% 
  mutate(order = if_else( from=="-",
                          1,
                          0)) %>% 
  group_by(user)

But I can't figure out any solution that not involve a “for” loop to go further with the sorting. The row in tibble for each users can be longer as the navigation step user performed on a website and pages has not a predefined order.

tidyverse r
3 Answers

Here is a solution using data.table + igraph

library(data.table)
library(igraph)
setDT(df)[
  ,
  order := distances((g <- graph_from_data_frame(.SD[, .(ifelse(from == "-", page, from), page)])),
    v = "home",
    to = V(g)[match(.SD[, page], V(g)$name)]
  ) + 1,
  user
]

which gives

> df
    user         page         from order
1: user1         home            -     1
2: user1 product page         home     2
3: user1     checkout    promotion     4
4: user1    promotion product page     3
5: user2         home            -     1
6: user2    promotion         home     2
7: user2     checkout    promotion     3
user_121587570

You can create a grouping variable and use seq_along:

library(dplyr)
library(stringr)

df %>% 
  dplyr::arrange(as.integer(stringr::str_extract(user, "\\d+")), 
                 as.integer(stringr::str_extract(replace(from, from == "-", 0),"\\d+"))) %>% 
  dplyr::group_by(user) %>% 
  dplyr::mutate(order = seq_along(user)) %>% 
  dplyr::ungroup()

Output

 user  page  from  order
  <chr> <chr> <chr> <int>
1 user1 page1 -         1
2 user1 page2 page1     2
3 user1 page3 page2     3
4 user1 page4 page3     4
5 user2 page1 -         1
6 user2 page2 page1     2
7 user2 page3 page2     3
user_63824340

Here is a somewhat simpler solution, also using dplyr and seq_along(), but does the ordering you requested:

# Create sample data
df <- tribble(
  ~user,   ~page,    ~from,
  "user1", "page1",  "-",
  "user1", "page2",  "page1",
  "user1", "page4",  "page3",
  "user1", "page3",  "page2",
  "user2", "page1",  "-",
  "user2", "page2",  "page1",
  "user2", "page3",  "page2" )

# Assign order column
df2 <- df %>% 
  arrange(user, page) %>% 
  group_by(user) %>% 
  mutate(order = seq_along(user)) 

# Examine results
df2
# # A tibble: 7 x 4
# # Groups:   user [2]
#   user  page  from  order
#   <chr> <chr> <chr> <int>
# 1 user1 page1 -         1
# 2 user1 page2 page1     2
# 3 user1 page3 page2     3
# 4 user1 page4 page3     4
# 5 user2 page1 -         1
# 6 user2 page2 page1     2
# 7 user2 page3 page2     3
user_107361630
Related