Finding the order and sequence of events in R

user_3407225 0
Viewed 451

I have some data with dates and names and a task that I'd like to order and work out the order of tasks that people do and the flow of tasks from one another. So, very simply here is some sample data. 

    Name    Date        Food
    Fred    01/01/2018  Peanuts
    Jim     03/02/2018  Banana
    Barney  02/02/2018  Rice
    Fred    06/03/2018  Rice
    Barry   12/02/2018  Peanuts
    John    04/04/2018  Rice
    Jim     03/03/2018  Rice
    Fred    20/04/2018  Rice
    Den     12/02/2018  Banana
    Barney  04/05/2018  Banana
    Jim     05/06/2018  Rice
    John    06/07/2018  Peanuts
    Jim     30/06/2018  Banana
    Fred    05/05/2018  Rice

This gives me the dates at which each of the named persons ate the specified food. What I would like to know is for each person the full list of foods they've ate plus the order they did them in.

I've used the order function in R and used created a seq from 1 to nrow to get the ordering but I don't know how to get this for each individual.

My second step is that I'd like to create a table of flows and a count of the number of times each flow is recorded so, the end result would be a table like this.

  Flow                 count
  Peanuts to rice      1
  Peanuts to banana    0
  Peanuts to peanuts   0
  Rice to peanuts      1
  Rice to banana       2
  Rice to rice         3
  Banana to rice       1
  Banana to peanuts    0
  Banana to banana     0

Thanks

UPDATE:

As ever with these things, the more I get into something the more alterations I'd like to do with the data!

So, the answer provided below has given me the flow table I want - thanks for that. now what I'd like to do is be able to edit my original data frame to remove instances of flows that I'm not interested in, or don't want to analyse.

So, for example, how would I remove from the data frame all flows (regardless of person) that say move from Rice to Peanuts or from Bananas to Rice?

r
2 Answers

Let your data frame be dat, and assume that:

  1. it has been sorted in ascending order by Date column (or Date is sorted within Name, as you currently have);
  2. Name and Food are factor columns.

## split by person; not to be messed up by "between person" flow
x <- split(levels(dat$Food)[dat$Food], dat$Name)

#$Barney
#[1] "Rice"   "Banana"
#
#$Barry
#[1] "Peanuts"
#
#$Den
#[1] "Banana"
#
#$Fred
#[1] "Peanuts" "Rice"    "Rice"    "Rice"   
#
#$Jim
#[1] "Banana" "Rice"   "Rice"   "Banana"
#
#$John
#[1] "Rice"    "Peanuts"

method 1

getFlow1 <- function (u) {
  if (length(u) == 1L) NULL
  else paste(u[-length(u)], u[-1], sep = " to ")
  }

Flow1 <- unlist(lapply(x, getFlow1), use.names = FALSE)
#[1] "Rice to Banana"  "Peanuts to Rice" "Rice to Rice"    "Rice to Rice"   
#[5] "Banana to Rice"  "Rice to Rice"    "Rice to Banana"  "Rice to Peanuts"

## maybe you can control the order of factor levels here
All_Flow <- outer(levels(dat$Food), levels(dat$Food), paste, sep = " to ")
Flow1 <- table("Flow" = factor(Flow1, levels = All_Flow))
#Flow
#  Banana to Banana  Peanuts to Banana     Rice to Banana  Banana to Peanuts 
#                 0                  0                  2                  0 
#Peanuts to Peanuts    Rice to Peanuts     Banana to Rice    Peanuts to Rice 
#                 0                  1                  1                  1 
#      Rice to Rice 
#                 3 

as.data.frame(Flow1)

#                Flow Freq
#1   Banana to Banana    0
#2  Peanuts to Banana    0
#3     Rice to Banana    2
#4  Banana to Peanuts    0
#5 Peanuts to Peanuts    0
#6    Rice to Peanuts    1
#7     Banana to Rice    1
#8    Peanuts to Rice    1
#9       Rice to Rice    3

method 2 (I prefer to this one)

getFlow2 <- function (u) {
  if (length(u) == 1L) NULL
  else cbind(u[-length(u)], u[-1])
  }

Flow2 <- do.call("rbind", lapply(x, getFlow2))
#     [, 1]     [, 2]     
#[1,] "Rice"    "Banana" 
#[2,] "Peanuts" "Rice"   
#[3,] "Rice"    "Rice"   
#[4,] "Rice"    "Rice"   
#[5,] "Banana"  "Rice"   
#[6,] "Rice"    "Rice"   
#[7,] "Rice"    "Banana" 
#[8,] "Rice"    "Peanuts"

Flow2 <- table("From" = Flow2[, 1], "To" = Flow2[, 2])
#         To
#From      Banana Peanuts Rice
#  Banana       0       0    1
#  Peanuts      0       0    1
#  Rice         2       1    3

as.data.frame(Flow2)
#     From      To Freq
#1  Banana  Banana    0
#2 Peanuts  Banana    0
#3    Rice  Banana    2
#4  Banana Peanuts    0
#5 Peanuts Peanuts    0
#6    Rice Peanuts    1
#7  Banana    Rice    1
#8 Peanuts    Rice    1
#9    Rice    Rice    3
user_48917380

Here's a full tidyverse solution.

library(tidyverse)

data <- 
  tribble(~Name,    ~Date,        ~Food,
          "Fred",    "01/01/2018",  "Peanuts",
          "Jim",     "03/02/2018",  "Banana",
          "Barney",  "02/02/2018",  "Rice",
          "Fred",    "06/03/2018",  "Rice",
          "Barry",   "12/02/2018",  "Peanuts",
          "John",    "04/04/2018",  "Rice",
          "Jim",     "03/03/2018",  "Rice",
          "Fred",    "20/04/2018",  "Rice",
          "Den",     "12/02/2018",  "Banana",
          "Barney",  "04/05/2018",  "Banana",
          "Jim",     "05/06/2018",  "Rice",
          "John",    "06/07/2018",  "Peanuts",
          "Jim",     "30/06/2018",  "Banana",
          "Fred",    "05/05/2018",  "Rice")

First we convert dates to the proper format.

data_clean <-
  data %>% 
  mutate(Date = as.Date(Date, "%d/%m/%Y"))

data_clean

Then we get the list of food each person ate with arrange, summarise, and str_c(..., collapse = ", ").

list_of_food_by_person <- 
  data_clean %>%
  group_by(Name) %>%
  distinct(Name, Food) %>% 
  arrange(Food) %>% 
  summarise(List = str_c(Food, collapse = ", "))


list_of_food_by_person

# A tibble: 6 x 2
  Name   List         
  <chr>  <chr>        
1 Barney Banana, Rice 
2 Barry  Peanuts      
3 Den    Banana       
4 Fred   Peanuts, Rice
5 Jim    Banana, Rice 
6 John   Peanuts, Rice

Similarly, we get the flow of food items per person, again with str_c().

flow_of_food_per_person <- 
  data_clean %>% 
  arrange(Date) %>% 
  group_by(Name) %>% 
  summarise(Flow = str_c(Food, collapse = " to "))

flow_of_food_per_person

# A tibble: 6 x 2
  Name   Flow                            
  <chr>  <chr>                           
1 Barney Rice to Banana                  
2 Barry  Peanuts                         
3 Den    Banana                          
4 Fred   Peanuts to Rice to Rice to Rice 
5 Jim    Banana to Rice to Rice to Banana
6 John   Rice to Peanuts

Finally, we get the order of each item per person with group_by() and sequence(n()). I didn't actually make use of this ordering, but you had asked for a way to create it. What I did was simply use lag() to grab the previous food item, followed by str_glue() to drop it into a string value. 

flow_count <- 
  data_clean %>%
  arrange(Date) %>% 
  group_by(Name) %>% 
  mutate(Order = sequence(n())) %>% 
  mutate(Previous = lag(Food),
         Flow = str_glue("{Previous} to {Food}")) %>% 
  ungroup() %>% 
  filter(!is.na(Previous)) %>% 
  count(Flow)

flow_count

# A tibble: 5 x 2
  Flow                n
  <chr>           <int>
1 Banana to Rice      1
2 Peanuts to Rice     1
3 Rice to Banana      2
4 Rice to Peanuts     1
5 Rice to Rice        3
user_37909730
Related