I have a dataset with multiple rows that describes one user. I am trying to change my dataset to be one row represents one user.
Reproducible Example:
old_way <- data.frame("Day" = 1:10, "Purchase" = 20:29, "Name" = c("John", "John", "John", "Dora", "Dora", "Dora", "Dora", "Gerald", "Gerald", "Gerald"), stringsAsFactors = FALSE)
Day Purchase Name
1 1 20 John
2 2 21 John
3 3 22 John
4 4 23 Dora
5 5 24 Dora
6 6 25 Dora
7 7 26 Dora
8 8 27 Gerald
9 9 28 Gerald
10 10 29 Gerald
The big difference being each row is now one person. So when I am doing records, I can very easily see which days they were here and what purchase they did.
desired_way <- data.frame("Name" = c("John","Dora","Gerald"), "Day" = c("1, 2, 3", "4, 5, 6, 7", "8, 9 ,10"), "Purchase" = c("20, 21, 22", "23, 24, 25, 26", "27, 28, 29"), "Last_Day" = c("3", "7", "10"), "Avg_Purchase" = c("21","25","28"))
Name Day Purchase Last_Day Avg_Purchase
1 John 1, 2, 3 20, 21, 22 3 21
2 Dora 4, 5, 6, 7 23, 24, 25, 26 7 25
3 Gerald 8, 9 ,10 27, 28, 29 10 28
How could I create that cell that encapsulates the other rows information? And does R support operations done on that cell, or would I need to calculate the most recent and average when I am creating that cell?
Thank you all in advance!
It may be better to keep it in a list instead of a single string after doing the grouping by 'Name'
library(dplyr)
old_way %>%
group_by(Name) %>%
summarise(Last_Day = last(Day),
Avg_Purchase = mean(Purchase),
Day = list(Day), Purchase = list(Purchase), .groups = 'drop')
-output
# A tibble: 3 x 5
# Name Last_Day Avg_Purchase Day Purchase
# <chr> <int> <dbl> <list> <list>
#1 Dora 7 24.5 <int [4]> <int [4]>
#2 Gerald 10 28 <int [3]> <int [3]>
#3 John 3 21 <int [3]> <int [3]>