Plot magnitude on y-axis and time on x-axis read from an excel file

I have the data as follows :

 Stage    Magnitude     Date             Time
1             -1        06/10/2012       17:56:50
1             -3        06/10/2012       17:59:33
2             -2        06/11/2012       8:52:45
2             -5        06/11/2012       8:52:46
3             -3        06/12/2012       9:11:37
3             -4        06/12/2012       9:55:56
3             -1        06/12/2013       9:57:46
4             -5        06/12/2013       10:47:49
4             -2        06/12/2013       10:48:08
4             -4        06/12/2013       10:50:35
5             -3        06/12/2013       7:47:43
5             -1        06/12/2013       8:15:30
5             -2        06/12/2013       8:16:04

I want to plot the magnitude column on y-axis and date & time on x-axis. And I want to create such plots for each Stage (as per above data I should get 5 different plots)

Can I any one tell me the format of the code?

P.S : As you can see the date and time are in serial order, I mean after 11th comes 12th june(date)and after 17:59:33 after 10th june, 8:52:45 is on 11th june

[Andy L]

Have you read the data in fine? See xlsread if you are struggling!

[num, txt, raw] = xlsread('file_name');

Answer 1

[Ashish Gudla]

If you can import the excel file as column vectors, you can just loop through unique stage values and plot the respective Magnitude and Date/Time Values.

You may need to set the "XTick" and "XTickLabel" properties to make the plot look better.

Assuming you need to plot in 5 different figures (you can modify it to plot on different axes in same figure or whatever your requirement is) you could do something like this.

s = unique(Stage);
for i = s'
    XData = Time(Stage == i);
    YData = Magnitude(Stage == i);
    DData = Date(Stage == i);
    figure;
    plot(XData , YData); 
    set(gca , 'XTick', XData,'XTickLabel', [datestr(DData) datestr(XData,'HH:MM:SS')]);
end

[KRUNAL]

This is the code I have written so far In this I have data in columns C(stage), E(Date), F(Time)and S(its magnitude)

 clc;
 clear all;

 orginf = '<filename>';

 sheet = 'Sheet1';

 for n =1:151
 xlrange = ['C' num2str(n+1)];
 a = xlsread (orginf,sheet,xlrange);
if a==1
  range = ['E' num2str(n+1)];
  range1 = ['F' num2str(n+1)];
  range2 = ['S' num2str(n+1)];
  [~,~,b] = xlsread(orginf,sheet,range);
  c = xlsread (orginf,sheet,range1);
  d = {datestr(c,'HH:MM:SS')};
  dt_time = {b,d};
  mag = xlsread (orginf,sheet,range2);

xData = linspace(dt_time,5);
figure
plot(dt_time,mag)
set(gca,'XTick',xData)
datetick('x','mm/dd/yyHH:MM:SS','keepticks')

The error is coming as :

Error using linspace Inputs must be floats, namely single or double.

[Andy L]

dt_time is a cell array. change this to a matrix ( cell2mat )

[KRUNAL]

I did it by entering the statement :

 dt_time_new=cell2mat(dt_time);
  plot(dt_time_new,mag,'--rs','LineWidth',2,...
      'MarkerEdgeColor','k',...
      'MarkerFaceColor','g',...
      'MarkerSize',10)

These two lines I have added after

 mag  = xlsread........

line It shows me now error as : Cannot support cell arrays containing cell arrays or objects for the cell2mat line

[KRUNAL]

@Ashish... This is what I wrote to access the column data. In my file stage is in column C, Date(in file it is called as AcquisitionDate)in column E , Time(in file it is called as AcquisitionTime)in column F and magnitude in column S

 clc;
clear all;

 orginf = '<filelocation>;

 sheet = 'Sheet1';
Stage = xlsread (orginf,sheet,'C1:S151');

 s = unique(Stage); %line 9%
for i = s'
    XData = AcquisitionDate(Stage == i); %line 11%
    YData = Magnitude(Stage == i);
    DData = AcquisitionTime(Stage == i);
    figure;
    plot(DData,YData); 
    set(gca ,'XTick', XData,'XTickLabel',[datestr(DData) datestr(XData,'HH:MM:SS')]);
end

It gives me error as

Undefined function 'AcquisitionDate' for input arguments of type 'logical'.

Error in event_plot_tr (line 11) XData = AcquisitionDate(Stage == i);

I also tried to change the line of Stage = xlsread.... to

 [~, ~, Stage] = xlsread(orginf,sheet,'C1:S151');

for which I received the error as

Input A must be a cell array of strings.

Error in event_plot_tr (line 9) s = unique(Stage);

Tell me where I am wrong?

Answer 2

[Nir Rattner]

You can use the Import Data tool in the Home tab to import your data into MATLAB. At that point you can use the "datenum" function to combine the dates and times and then use the "datetick" function to properly display the dates and times on your plots.

for i = Stage(1):Stage(end)
    figure;
    stageIndex = Stage == i;
    DateTime = datenum(Date(stageIndex)) + datenum(Time(stageIndex)) - datenum('00:00');
    plot(DateTime, Magnitude(stageIndex), '-o');
    datetick('x', 0);
end

[KRUNAL]

I am trying to import data but it is not importing correct data. I wrote your above data and using the import function I tried importing data column wise.

The error says

Undefined function 'Stage' for input arguments of type 'double'.

Also after importing the data I am not able to see them in the workspace soon after I hit the run coomand

Answer 3

[Michael Haderlein]

Please try this:

[num,txt]=xlsread('test.xlsx');
figure
dates=datestr(datenum(txt(2:end,end-1),'mm.dd.yyyy')+num(:,end),'dd.mm.yyyy HH:MM:SS');
for cnt=1:max(num(:,1))
  subplot(max(num(:,1)),1,cnt)
  plot(num(num(:,1)==cnt,2))
  set(gca,'xtick',1:sum(num(:,1)==cnt),'xticklabel',dates(num(:,1)==cnt,:))
end

On my computer, Excel changed the date format from mm/dd/yyyy automatically to mm.dd.yyyy. So maybe you need to change this in the dates line.

[KRUNAL]

So do you mean I need to 1st change my date information in the excel file to mm.dd.yyyy ?

[KRUNAL]

This is my code after what you mentioned me to edit as :

 clc;
 clear all;

 orginf = '<filename>';

 sheet = 'Sheet1';

 for n =1:151
 xlrange = ['C' num2str(n+1)];
a = xlsread (orginf,sheet,xlrange);
if a==1
  range = ['E' num2str(n+1)];
  range1 = ['F' num2str(n+1)];
  range2 = ['S' num2str(n+1)];
  [num,txt,b] = xlsread(orginf,sheet,range);
  c = xlsread(orginf,sheet,range1);
  d = {datestr(c,'HH:MM:SS')};
  dt_time = [b d];
 [num1,txt1,mag] = xlsread(orginf,sheet,range2);
  dt_time_new=cell2mat(dt_time)
  figure
  dates=datestr(datenum(txt(2:end,end-1),'mm.dd.yyyy')+num(:,end),'dd.mm.yyyy HH:MM:SS'); %line 22
  for cnt=1:max(num(:,1))
      subplot(max(num(:,1)),1,cnt)
      plot(num(num(:,1)==cnt,2))
      set(gca,'xtick',1:sum(num(:,1)==cnt),'xticklabel',dates(num(:,1)==cnt,:))
  end

It shows me the following error :

Subscript indices must either be real positive integers or logicals(line 22)

[KRUNAL]

Can you explain me what exactly line 22 is doing?

[Michael Haderlein]

This line is converting the date and the time to one date&time information by adding them. In case your Excel installation shows the date in format mm/dd/yyyy, please change the first format definition in this line. Also, for me the mm/dd/yyyy format is very inconvenient, so I'd rather use dd.mm.yyyy as xticklabel. If you want to keep the mm/dd/yyyy format, also change the second format definition in this line.

[KRUNAL]

OK.

Now this is what I have created till now and I am able to plot one figure, when stage column value is equal to 1.

 clc;
 clear all;

 orginf = '<filename>';

 sheet = 'Sheet1';

 for n =1:5
xlrange = ['C' num2str(n+1)];
a = xlsread (orginf,sheet,xlrange);
if a==1
  range = ['E' num2str(n+1)];
  range1 = ['F' num2str(n+1)];
  range2 = ['S' num2str(n+1)];
  [~,~,b] = xlsread(orginf,sheet,range);
  c = xlsread(orginf,sheet,range1);
  d = {datestr(c,'HH:MM:SS')};
  dt_time = [b,' ',d];
 [~,~,mag] = xlsread(orginf,sheet,range2);
 mag_new(n) = cell2mat(mag);
  dt_time_new(n)={cell2mat(dt_time)}
  formatIn = 'mm/dd/yyyy HH:MM:SS';
  final_time(n)=datenum(dt_time_new(n),formatIn);
end
end
  time_min = min(final_time);
  time_max = max(final_time);
  mag_min = min(mag_new)
  mag_max = max(mag_new)
  xData = linspace(time_min,time_max,5)
  yData =linspace(mag_min,mag_max,5)
  y = datestr(xData);
  plot(final_time,mag_new,'--rs','LineWidth',2,...
      'MarkerEdgeColor','k',...
      'MarkerFaceColor','g',...
      'MarkerSize',10)
  set (gca, 'Xtick',xData,'xticklabel',datestr(xData))
  set (gca, 'Ytick',yData,'Yticklabel',(yData))
  set(gca,'Ydir','reverse')

Now if I want to do the same for all the stages can anyone tell me what changes/additional script do I need to add and where..to plot one figure for each stage eg : stage 1 -figure 1,stage 2 -figure 2 and so on ?

[Michael Haderlein]

I don't have much time right now, but if you can use my answer from above, you just need to replace the line with the subplot by "figure". Then you'll plot each stage in an individual figure.

[KRUNAL]

I tried using your answer in my code as I mentioned above and I also told you what was the error. I would be glad if you can tell me where I am going wrong in that

[Michael Haderlein]

I don't understand you code in every detail. If you use my code, change both "dd.mm.yyyy" to "mm/dd/yyyy", what will be the error? If you want, you can also change "subplot(max(num(:,1)),1,cnt)" to simply "figure".