1

I am facing a problem with transforming my data frame. I would like to count how often (once in how many days) does each client buy. I thout that it would be easiest to transform my data about transactions formated as:

Transatcion_ID  Client_ID    Date
1               1            2017-01-01
2               1            2017-01-04
3               2            2017-02-21
4               1            2017-05-01
5               3            2017-02-04
6               3            2017-03-01
...             ...          ...

to :

Client_ID    Date_1_purchase     Date_2_purchase     Date_3_purchase         ...
1            2017-01-01          2017-01-04          2017-05-01              ...
2            2017-02-21          NA                  NA                      ...
3            2017-02-04          2017-03-01          NA                      ...

Or:

Client_ID    Date_First_purchase     Date_Last_purchase     Numberof_orders
1            2017-01-01              2017-05-01              3
2            2017-02-21              2017-02-21              1   
3            2017-02-04              2017-03-01              2

I have tried using dcast but I couldnt achive what I wanted. I bet there is a way to do that or eaven calculating what I want without transforming dataset, but i did not find it.

AAAA
  • 461
  • 6
  • 22

1 Answers1

0

We can create a sequence id with rowid to dcast from 'long' to 'wide' format

library(data.table)
dcast(setDT(df1), Client_ID ~ paste0("Date_", rowid(Client_ID), 
                                  "_purchase"), value.var = "Date")
#   Client_ID Date_1_purchase Date_2_purchase Date_3_purchase
#1:         1      2017-01-01      2017-01-04      2017-05-01 
#2:         2      2017-02-21              NA              NA
#3:         3      2017-02-04      2017-03-01              NA
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank you very much! Do you know also how to do the second table? – AAAA Jun 11 '17 at 16:37
  • @MariuszSiatka You can do `setDT(df1)[, .(Date_First_purchase = Date[1L], Date_Last_purchase= Date[.N], Numberof_orders = .N) , Client_ID]` – akrun Jun 11 '17 at 16:40