Organising Dates based on User ID and compute weekly average

Hi all, I would like to organise the dates for each user based on their ID. I would like to start the beginning date for "Day1" for each user and then count the dates accordingly. Once the dates are assigned to the single user then for the next user the starting date should be counted as "Day1 and so on.

For example if user ID: 1024 has the first date as '04-02-2020' it should be counted as day1 for him and if the next date is 07-02-2020', 4th day and so on. Once the count for the particular user is finished then the count should reset for the next user as Day1, Day5 etc.

In addition I also would like to compute weekly average based on days (for example 3 days in a week 3/7, 6 days in a week 6/7 etc).

Any help in this regard is highly appreciated. The sample data is available below:

https://www.dropbox.com/s/ruflcsyiv0hthgo/Date_ID.csv?dl=0

[Peter Perkins]

Ganesh, very likey this is straight-forward using timetables and retime, but your descriptiopn is not clear. Please describe exactly what you expect to end up with, with a short example. Both things. Are you just looking for the number of days for each ID?

Answer 1

[Peter Perkins]

If it's just counting number of days for each ID, try this:

tt =
  1686×1 timetable
       Date         ID  
    ___________    _____
    02-Jul-2020    87609
    03-Jul-2020    87609
    08-Jul-2020    87609
    09-Jul-2020    87609
    10-Jul-2020    87609
[snip]

>> counts = varfun(@length,tt,"GroupingVariables","ID","OutputFormat","table")
counts =
  12×2 table
     ID      GroupCount
    _____    __________
    87595       148    
    87596       238    
    87598       228    
    87600        50    
    87601       254    
    87602       237    
    87603       201    
    87604        44    
    87605        89    
    87606       115    
    87607        32    
    87609        50        

[Ganesh Naik]

Hi Peter thanks for your answer. I have already computed total day counts for indvidual users. I would like to achieve the following:

1) Day count for each individual user as "day1', Day2 etc based on the actual date and it should reset for the next user. To be precise, for example for the following dates with ID the last coulmn should be displayed as: 1, 2 and 7 and it should reset for the next ID and should display as 1, 2, 4 etc based on the actual date.

02-Jul-2020 87609 1

03-Jul-2020 87609 2

08-Jul-2020 87609 7

For the next ID

2020-07-10 87607 1

2020-07-11 87607 2

2020-07-13 87607 4

2) Next, I would like to achieve weekly average based on the actual dates. For example, if the user has only 2 dates in the particular week then the average should be 2/7 = 0.286, for 4 days 4/7 = 0.57 etc.

In fact I have extracted the day name and other details as:

%Extract the day number from the Start-time data
a=table2array(Date);
date_list=datetime(a);  % Which gives me day list in terms of datetime
day_numbers=days(date_list-min(date_list))+1;

I have done the weekly count as the following but unfortunately the weekcount takes into account the system date but I wanted the count as the week 1 for each user based on their starting date.

%Count the Week data 
previousSundays = dateshift(date_list,'dayofweek','Sunday','previous');
weekCount = findgroups(previousSundays);  
tt.Week = weekCount;

Any direction in this regard would be highly useful for me.

[Peter Perkins]

1) Use two grouping variables in the call to varfun: date and user.

2) You should be able to use the week function to add a new grouping variable to your data and then use varfun with week and user as grouping variables. But I don't know what "wanted the count as the week 1 for each user based on their starting date" means. If you are saying that the weeks for each user are different, then you will need to loop over users to define the new "week" grouping variable.

[Ganesh Naik]

Hi Peter thanks,

1) I am not able to pass two grouping variables in the call to varfun

2) My main goal is to compute the weekly average for each user where I want to exclude any days less than 4 days in a week.

[Peter Perkins]

Ganesh, that's not much to go on. Post what you have done so far.