# How to group_by data.frame by both groups and order?

Imagine a person uses a phone randomly, and the use time is recorded.

``````# Let's make a data.frame first.
library(dplyr)
set.seed(1234)
my_data <- data.frame(
name = c(rep("Lily", 6), rep("Sam", 4)),
log_order = c(1:6, 1:4),
place = c(
rep("home", 2), rep("school", 2), rep("home", 2),
rep("school", 2), rep("home", 2)
),
use_time = sample(1:10, 10)
)
my_data
# name log_order  place use_time
# 1  Lily         1   home       10
# 2  Lily         2   home        6
# 3  Lily         3 school        5
# 4  Lily         4 school        4
# 5  Lily         5   home        1
# 6  Lily         6   home        8
# 7   Sam         1 school        2
# 8   Sam         2 school        7
# 9   Sam         3   home        9
# 10  Sam         4   home        3
# In this case, for the first time, Lily stayed at home and used the phone for 10 minutes; for the second time, Lily used for 6 minutes; for the third time, Lily used the phone for 5 minutes at the school.
``````

What I want to calculate, is the total use time of each person in each period by the log order. In this case, Lily has three "period"s, first and third one is at school and the second one is at home; while Sam has two periods, the first is at the school and the second is at home. The desired result is as follows.

``````my_data_period <- my_data %>%
mutate(period = c(
1, 1, 2, 2, 3, 3,
1, 1, 2, 2
))
my_data_period
# name log_order  place use_time period
# 1  Lily         1   home       10      1
# 2  Lily         2   home        6      1
# 3  Lily         3 school        5      2
# 4  Lily         4 school        4      2
# 5  Lily         5   home        1      3
# 6  Lily         6   home        8      3
# 7   Sam         1 school        2      1
# 8   Sam         2 school        7      1
# 9   Sam         3   home        9      2
# 10  Sam         4   home        3      2

my_data_period %>%
group_by(name, period, place) %>%
summarise(tot_use_time = sum(use_time)) %>%
ungroup() %>%
select(-period)

# name  place  tot_use_time
# <chr> <chr>         <int>
# 1 Lily  home             16
# 2 Lily  school            9
# 3 Lily  home              9
# 4 Sam   school            9
# 5 Sam   home             12
``````

How can I do that based on the group column ("place") and log_order column?

I'm uncertain about the meaning of that. Are you looking for

``````my_data_period %>%
group_by(place, log_order) %>%
summarise(tot_use_time = sum(use_time)) %>%
ungroup()
``````

Thank you for the reply. But no, that is not what I want to do. My raw data is the "my_data" above, the expected result is at the end of the second code trunk above. "my_data_period" is just a middle result and I made it manually. My question is how to get the expected result from "my_data" directly.

I found an alternative solution.

``````library(data.table)
my_data %>%
group_by(name) %>%
mutate(period = rleid(place)) %>%
ungroup() %>%
group_by(name, period, place) %>%
summarise(tot_use_time = sum(use_time)) %>%
ungroup() %>%
select(-period)
``````

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.