Performing in group operations in R

Question

I have a data in which I have 2 fields in a table sf -> Customer id and Buy_date. Buy_date is unique but for each customer, but there can be more than 3 different values of Buy_dates for each customer. I want to calculate difference in consecutive Buy_date for each Customer and its mean value. How can I do this.

Example

Customer   Buy_date
1          2018/03/01
1          2018/03/19
1          2018/04/3
1          2018/05/10
2          2018/01/02
2          2018/02/10
2          2018/04/13

I want the results for each customer in the format

Customer  mean

Please post some sample code to show us what you have done. And please put some data — Adam Warner, Jun 27 '18 at 14:17
Hi. Welcome to SO. Please read https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example and add a sample of your dataframe using `dput()`. — Lennyy, Jun 27 '18 at 14:18
Please include a small data sample in code rather than describing in prose, e.g. using `dput` or `dput(head(data))` for a minimal dataset, as in [this guide](https://stackoverflow.com/questions/5963269) (just ctrl-F for 'dput'). — dad, Jun 27 '18 at 14:18
`library(dplyr); df %>% group_by(Customer) %>% mutate(mean = mean(Buy_date))` ? — symbolrush, Jun 27 '18 at 14:29
@symbolrush, that should probably be `mean=mean(diff(Buy_date))` since the OP stated *"difference in consecutive `Buy_date`"* (though I'm not certain how "consecutive" fits...). — r2evans, Jun 27 '18 at 14:33

symbolrush · Answer 1 · 2018-06-27T14:46:54.630

Here's a dplyr solution.

Your data:

df <- data.frame(Customer = c(1,1,1,1,2,2,2), Buy_date = c("2018/03/01", "2018/03/19", "2018/04/3", "2018/05/10", "2018/01/02", "2018/02/10", "2018/04/13"))

Grouping, mean Buy_date calculation and summarising:

library(dplyr)
df %>% group_by(Customer) %>% mutate(mean = mean(as.POSIXct(Buy_date))) %>% group_by(Customer, mean) %>% summarise()

Output:

# A tibble: 2 x 2
# Groups:   Customer [?]
  Customer mean               
     <dbl> <dttm>             
1        1 2018-03-31 06:30:00
2        2 2018-02-17 15:40:00

Or as @r2evans points out in his comment for the consecutive days between Buy_dates:

df %>% group_by(Customer) %>% mutate(mean = mean(diff(as.POSIXct(Buy_date)))) %>% group_by(Customer, mean) %>% summarise()

Output:

# A tibble: 2 x 2
# Groups:   Customer [?]
  Customer mean            
     <dbl> <time>          
1        1 23.3194444444444
2        2 50.4791666666667

i tried using this, but it's giving an error : Error in quickdf(.data[names(cols)]) : length(rows) == 1 is not TRUE — AKSHMKP, Jun 27 '18 at 18:22

Adam Warner · Answer 2 · 2018-06-28T12:35:17.537

I am not exactly sure of the desired output but this what I think you want.

library(dplyr)
library(zoo)
dat <- read.table(text = 
"Customer   Buy_date
1          2018/03/01
1          2018/03/19
1          2018/04/3
1          2018/05/10
2          2018/01/02
2          2018/02/10
2          2018/04/13", header = T, stringsAsFactors = F)


dat$Buy_date <- as.Date(dat$Buy_date)

dat %>% group_by(Customer) %>% mutate(diff_between = as.vector(diff(zoo(Buy_date), na.pad=TRUE)), 
                                      mean_days = mean(diff_between, na.rm = TRUE))

This produces:

    Customer Buy_date   diff_between mean_days
     <int> <date>            <dbl>     <dbl>
1        1 2018-03-01           NA      23.3
2        1 2018-03-19           18      23.3
3        1 2018-04-03           15      23.3
4        1 2018-05-10           37      23.3
5        2 2018-01-02           NA      50.5
6        2 2018-02-10           39      50.5
7        2 2018-04-13           62      50.5

EDITED BASED ON USER COMMENTS:

Because you said that you have factors and not characters just convert them by doing the following:

dat$Buy_date <- as.Date(as.character(dat$Buy_date))
dat$Customer <- as.character(dat$Customer)

I tried using the solution u suggested, but it's showing an error : Error in as.character.factor(x) : malformed factor. In my data frame, both the fields are factors. — AKSHMKP, Jun 27 '18 at 18:19
@AKSHMKP so change the field to a character by doing as.Date(as.character(x)) and just as.character(x) — Adam Warner, Jun 28 '18 at 12:34

Performing in group operations in R

2 Answers2

Linked