MATLAB date conversion and analysis

Community, I have a data of the following type. There are three columns. The first column is date array. Second column is IP address array and 3rd column is Serial Number array.

Date:['2014/09/14 06:15', '2014/09/14 06:16', '2014/09/14 06:17' .......

IP address : 192.168.1.1 .....'

Unique Number: '2252534' .....

I want to create something like a pivotable on excel. I would like the group the Date into hours. Followed by this grouping I would like to calculate how many times the IP address and Unique Number have appeared during this Hour. I would like to do that for 24 hours. Can you help me on how I can get started to group the hours and count the number of IP address and Unique number within each hour. I would expect the output to be something like following:

Time: 2014-09-14 6:00 to 7:00 Total number of P addresses within this interval: 6 Total number of unique number within this interval : 4

Thx in advance.

Mike

Answer 1

[Iain]

Matlab datenum follows the convention of

 "day = 1"
 "hour = 1/24"
 "minute = 1/(24*60)"

If today has the value of 523566123 at midnight (this morning), then 6am to 7am would be 523566123 + 6 * hour to 523566123 + 7 * hour

You can index "logically" i.e.:

 accepted_list = times > early_time & times < late_time; % eg, 6am to 7am.
 noted_ips =  ips(accepted_list);
 noted_nums = nums(accepted_list);

That gives you a list of all ip addresses & numbers that were logged, you simply need to count the number that were a specific value: eg.

 sum(noted_nums == 5)
 sum(strcmpi(noted_ips,'101.101.101.10'))

Answer 2

[Guillaume]

There's a function on the file exchange that may do what you want. If not, I would:

• convert the dates to date numbers with datenum
• bin the date numbers with histc and use its 2nd output to find the index of the bin in which each date falls
• use these indices to group the IP and SNs into a cell array, using arrayfun
• use cellfun to operate on each cell array

Depending on the operation, these last two could be replaced by accumarray

e.g:

dndates = datenum(dates);
[~, binindices] = histc(dndates, min(dndates):1/24:max(dndate)+1/24); %to split by hour
groupedip = arrayfun(@(ip, bin) ip(bin), ips, binindices, 'UniformOutput', false);
groupedipcount = cellfun(@(ips) numel(unique(ips)), groupedip);