Organising Dates based on User ID and compute weekly average

Question

Ganesh Naik 2021-11-23

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1593449-organising-dates-based-on-user-id-and-compute-weekly-average

评论： Peter Perkins 2021-11-29

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

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Peter Perkins 2021-11-23

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 2021-11-23

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1593449-organising-dates-based-on-user-id-and-compute-weekly-average#answer_838754

在 MATLAB Online 中打开

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        

4 个评论
显示 2更早的评论隐藏 2更早的评论

Ganesh Naik 2021-11-24

在 MATLAB Online 中打开

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.

Ganesh Naik 2021-11-29

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 2021-11-29

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

请先登录，再进行评论。

Organising Dates based on User ID and compute weekly average

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

回答（1 个）

4 个评论
显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Organising Dates based on User ID and compute weekly average

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

回答（1 个）

4 个评论 显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论