Stacked bar graph with repeating datetime

I am trying to create a stacked bar graph from data extracted from an excel file. The X-values are in datetime ('yyyy-mmm-dd') and the y values are just different frequency written as integers.

I keep getting an error because the XData are not unique. However, I don't know how else to extract this data.

An example of what the table output is:

2020-Jan-03 1

2020-Jan-03 3

2020-Feb-04 5

2020-Feb-04 1

2020-Feb-04 4

How can I get a stacked bar graph out of this data?

clear all;
T = readtable(['Project1.xlsx']);

date = T.Date;
date1 = date(~isnat(date));
FeltN = T.Felt;
Felt = FeltN(~isnan(FeltN));
t=table(date1,Felt)
bar(date1,Felt,'stacked')

[AndresVar]

There are a couple of answers to your question but I wonder a couple of things:

(1) You should check for nan on both variables at the same time (in case date is missing but there is a value)

(2) If you had time data you could plot distributions and a line on top to show the total for each day. Sort of how the iphone's screentime charts look.

Answer 1

[Arif Hoq]

A=readtable('Project1.xlsx', 'PreserveVariableNames', 0);
AA=table2array(A);
B = regexp(table2array(A), '\s+', 'split');  % split each cell
C = vertcat(B{:});  
date1=C(:,1); % date column
dd=datenum(date1);  % converting to datenum
Felt=str2double(C(:,2));   % data
table=[dd Felt];
[x y z]=unique(table(:,1));  % to find the equal number
x1=datetime(x,'ConvertFrom', 'datenum', 'TimeZone','local','Format','dd-MMM-yyyy');
array1=table(1:2,:);
array2=table(3:end,:);
trans1=[array1(:,2)',0]; % transpose and adding 0 to make vector equal with other vector
trans2=array2(:,2)';     % transpose
Matrix=[trans1;trans2];
bar(x1,Matrix,'stacked')

Answer 2

[AndresVar]

You need to reorder your data into a matrix with shape "Number of Unique Dates" by "Maximum Measurements on Any Date"

Something like this could work.

clear;
dates = {'2020-Jan-03';
    '2020-Jan-03';    
    '2020-Feb-04';
    '2020-Feb-04';
    '2020-Feb-04'};
Freqs = [1 2 5 1 4]';
% assuming you removed the nans...

% table with dates and frequencies
dates = datetime(dates);
T = table(dates,Freqs);
T = sortrows(T,"dates") % sorted table by date

T = 5×2 table
       dates       Freqs
    ___________    _____

    03-Jan-2020      1  
    03-Jan-2020      2  
    04-Feb-2020      5  
    04-Feb-2020      1  
    04-Feb-2020      4  

% date categories
[date_categories,~,ic] = unique(T.dates);

% categorize frequencies and store in Freqs_byDate
[~,maxValsPerDate] = mode(ic);
nUniqueDates = numel(date_categories);
Freqs_byDate = zeros(nUniqueDates,maxValsPerDate);
for ii = 1:nUniqueDates    
    Freqs_onDate = T.Freqs(T.dates==date_categories(ii));
    Freqs_byDate(ii,1:numel(Freqs_onDate))=Freqs_onDate;    
end

bar(date_categories,Freqs_byDate,'stacked')

[Arif Hoq]

@AndresVar i appreciate your answer.can you please explin the loop ? it's just a curiosity.

1. Freqs_onDate return [5;1;4]. please explain ?
2. if Freqs_onDate return [5;1;4], then Freqs_byDate return [1 2 0;5 1 4]. How?

thank you very much.

[Briana Bellomo]

Thank you very much! This worked for me as I already removed the NaN and NaT from the dataset. Thanks again!

[AndresVar]

@Arif Hoq to answer your question

1) Yes the loop runs for each unique date, the first time Freqs_onDate = [1;2] the second time it's [5;1;4]. Each time the loop runs it populates a single row in the Freqs_byDate matrix.

2) Since Freqs_onDate is 1D and a row in Freqs_byDate is also 1D, the assignment happens element-by-element. Even though they are different shape they are 1D vectors with same lenght.