CODEWITHSUNDEEP
rdplyrtransformreshape2
AAAA
AAAA

Reputation: 473

Transforming data

I have a transaction data about people buying specific product (lets say it is soap) and I want to find how the intensity of buying soaps changed in time. The intensity I would define as average number of soaps used in a day, with assumption that if a person bought again he/she finished his/her previous supply of soap. It would be grate also to delate the outliers for each consumer (average individual for consumer +- 2 * standard deviation individual for consumer) and to delate information after last purchase

Currently the data frame looks like that:

transacrions <- data.frame(Client_ID = c(1, 2, 1, 3, 4, 1, 3, 2, 1), 
                           date = c("2017-01-01", "2017-01-01", "2017-01-02", "2017-01-03", "2017-01-04", "2017-01-05", "2017-01-06", "2017-01-09", "2017-01-10"),
                           soaps_bought = c(1, 12, 2, 19, 20, 10, 32, 12, 11))

I have thought that there would be needed specific steps to get to my desired table. First step would "fill" the missing dates for each consumer where he/she did not buy with NA:

partial_results <- data.frame(Client_ID = rep(1:4, each = 10), 
                              date = rep(seq(as.Date("2017-01-01"), as.Date("2017-01-10"), by = "day"), 4),
                              soaps_bought = c(1,  2,  NA, NA, 10, NA, NA, NA, NA, 11,
                                               12, NA, NA, NA, NA, NA, NA, NA, 12, NA,
                                               NA, NA, 19, NA, NA, 32, NA, NA, NA, NA,
                                               NA, NA, NA, 20, NA, NA, NA, NA, NA, NA ))

Second step would calculate how many days passed between purchases and calculated the mean usage. It would be best to also delate last purchase:

partial_results_II <- data.frame(Client_ID = rep(1:4, each = 10), 
                              date = rep(seq(as.Date("2017-01-01"), as.Date("2017-01-10"), by = "day"), 4),
                              avg_soaps_bought = c(1/1,  2/3,  2/3,  2/3,  10/5, 10/5, 10/5, 10/5, 10/5, 11/1,
                                                   12/8, 12/8, 12/8, 12/8, 12/8, 12/8, 12/8, 12/8, 12/2, 12/2,
                                                   NA,   NA,   19/3, 19/3, 19/3, 32/5, 32/5, 32/5, 32/5, 32/5,
                                                   NA,   NA,   NA,   20/7, 20/7, 20/7, 20/7, 20/7, 20/7, 20/7 ))

Third step I know how to do - it will transform from long to wide table:

desired_results <- dcast(setDT(partial_results_II), Client_ID ~ date, value.var = "avg_soaps_bought")

Fourth - delaying the outliers would delate from consumer 1 the last date (11 soaps used): I calculated the average and standard deviation for each person, and even checked which are outliers, but I do not now how to delate observations based on that 

desired_results_DF <- data.frame(desired_results)
avg <-  rowMeans(desired_results_DF[, -1], na.rm = TRUE)
library(matrixStats)
desired_results_MX <- data.matrix(desired_results_DF[, -1])
sd <-   rowSds(desired_results_MX, na.rm = TRUE)

is_ok <- desired_results_DF[, -1] < avg + 2 * sd |  desired_results_DF[, -1] > avg - 2 * sd

Upvotes: 1

Views: 74

Answers (1)

moodymudskipper
moodymudskipper

Reputation: 47320

You can do it this way:

library(dplyr)
library(tidyr)
transacrions %>%
  group_by(Client_ID) %>%
  mutate(avg_soaps_bought = soaps_bought/as.numeric(c(diff(as.Date(date)),1))) %>%
  complete(date) %>%
  fill(avg_soaps_bought) %>% # partial_results_II
  select(-soaps_bought) %>%
  spread(date,avg_soaps_bought) # final result

# # A tibble: 4 x 9
# # Groups:   Client_ID [4]
#   Client_ID `2017-01-01` `2017-01-02` `2017-01-03` `2017-01-04` `2017-01-05` `2017-01-06` `2017-01-09` `2017-01-10`
# *     <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>
# 1         1          1.0    0.6666667    0.6666667    0.6666667     2.000000          2.0            2           11
# 2         2          1.5    1.5000000    1.5000000    1.5000000     1.500000          1.5           12           12
# 3         3           NA           NA    6.3333333    6.3333333     6.333333         32.0           32           32
# 4         4           NA           NA           NA   20.0000000    20.000000         20.0           20           20

I changed a bit the order of your operations, but the function you wanted for your first step was tidyr::complete

Upvotes: 3

Related Questions