How to count date occurrence independently of year?

Hi,

I have an excel file with about 6000 dates from year 1900 and until now. I want to plot the occurrence of dates irrespective of year to see if the occurrence is dependent of time of year. How can I do this? The excel file is attached.

Answer 1

[Adam Danz]

This solution creates a table (T) that lists all month-day combinations in your data in 1 column and the count in the 2nd column. It also produces a plot showing the frequency of month-day combinations (ignore the year label). See comments for details.

% Read in the dates as datetime
m = readmatrix('Dates.xlsx','OutputType','datetime'); 

% Remove missing vals and sort dates 
% (sort isn't really needed but makes it easier to look at the vector)
m = sort(m(~isnat(m))); 

% find day-of-year number
doy = day(m,'dayofyear'); 

% count number of doy's
binEdges = 0:1:max(doy); 
mdCount = histcounts(doy,binEdges); 

% list all possible [month,day] values (we'll use them as labels)
allPossibleDates = (min(m):max(m)).'; 
mdAllPossible = unique([month(allPossibleDates), day(allPossibleDates)], 'rows'); 
allPossible = datetime(1904,mdAllPossible(:,1),mdAllPossible(:,2),'Format', 'MM/dd'); % Year must be any leap year

% Put results in summary table
T = table(allPossible(:), mdCount(:),'VariableNames', {'MonthDay', 'count'}); 

figure(); 
axh = axes(); 
plot(axh, T.MonthDay,T.count)
axh.XAxis.TickLabelFormat = 'MM-dd'; %reformat x ticks if you want mm/dd
xlabel('Day of year (mm-dd)')
ylabel('Count')
title(sprintf('Data from %s to %s',datestr(min(m),'mm/dd/yyyy'),datestr(max(m),'mm/dd/yyyy')))

Result:

head(T)   % show the first few rows of table
ans = 
  8×2 table
    MonthDay    count
    ________    _____
     01/01        0  
     01/02       34  
     01/03       34  
     01/04       24  
     01/05       24  
     01/06       27  
     01/07       13  
     01/08       32  

[KNL]

Thank you, that worked perfectly!

[Adam Danz]

Glad I could help!

Answer 2

[Jacob Ward]

This is a cool way of visualizing it as well:

clear;
dates = readtable('C:\Users\jacob\Downloads\Dates.xlsx','ReadVariableNames',false);

for n = 1:5902
    datesSplitIntoParts(n,:) = double(split(string(dates{n,1}),'/'));
end

figure('Units','Normalized','Position',[0.1 0.1 0.8 0.8])
histogram2(datesSplitIntoParts(:,1),datesSplitIntoParts(:,2))
xlabel('Month')
set(gca,'XTick',[1:12],...
    'XTickLabel',{'January','February','March','April','May','June',...
    'July','August','September','October','November','December'})
ylabel('Day')

[KNL]

Thank you for your help! I was more looking for what Adam provided, but it looks cool :)