Display image with 3 variable from abundance of excel data.

Dear community,

My data as attached is in every minute in every day of January 2014 from 31 different of observer (PRN). My variables I want to tackle are Day, Time and VTEC.

First, I need to find rate of change of the VTEC (ROV) within intervals of 1 minute for two cases (i) all PRN, (ii) individual PRN.

Then, I need to find the index for ROV (ROVI) by the standard deviation of the ROV in a five-minute interval also for both cases (i) all PRN, (ii) individual PRN.

Finally, I want to display the image of Time (x), Day (y) and ROVI and yes for both cases (i) all PRN, (ii) individual PRN.

If you asked me whether I already tried or not. The answer is yes (may refer my questions' asked) but I made in seperate code means calculate ROV one .m, ROVI another .m and I find it inconvenience for me. Furthermore, I encounter problem to do the image because of the ROVI value some is NaN and even in imaginary number. Hence, I also worry that what I've done from the start is completely wrong to what should be calculated.

I would be greatful if anyone could help settling this lingering problem.

[Cris LaPierre]

Could you share your code anyway? I ask because I don't want to have to start testing from the very beginning. Additionally, it runs the risk of causing the same problem you've already experienced - we provide a solution that meets the requirements of the question, but doesn't integrate into your larger code.

[Image Analyst]

"I encounter problem to do the image" <=== What image? Did you forget to attach an image that we'd need?

[Ann]

Good day, Cris and sir Image Analyst. Thank you for your response and the seperated codes [for case (i) all PRN] are as below.

CODE A:

Step - Average all PRN for 1 minute and 5 minutes then calculate the ROV. For calculating ROV, I choose for all PRN data that had been averaged in 5 minute (PRNTT5min) because later in calculating ROVI I just can do square root of ROV_5min.

%___Read data___%
data = readtable("Jan_para.xlsx");

%___Convert DAY and TIME into durations___%
data.DAY = days(data.DAY);
data.TIME = days(data.TIME);

%___Create a datetime___%
data.TimeStamp = datetime(2014,01,01) + data.DAY-1 + data.TIME;
data.TimeStamp.Format = "MM-dd-yy HH:mm";

%___Convert the table to a timetable___%
dataTT = table2timetable(data,"RowTimes","TimeStamp");

%___Use retime to average the PRN data into 1 or 5 minute increments___%
%PRNTT1min = retime(dataTT(:,"VTEC"),"minutely","mean");
PRNTT5min = retime(dataTT(:,"VTEC"),"regular","mean","TimeStep",minutes(5));

%___Calculate ROV 5 minute in second or minute___%
%data_ROV = table2array(PRNTT5min);
%ROV_sec = diff(all_data)/(5*60);
ROV_5min = diff(table2array(PRNTT1min))/5;

CODE B:

Step - ROV_5min I got from CODE A, I put in excel file name Jan_5mins.xlsx consists of Day, Time, VTEC (5mins average all PRN) and ROV_5min. Because I tried to call the ROV_5min.mat but keep giving error (it turns out in 8927x1 double) so all related variable I want to use I put in new excel files. Then, I calculated the ROVI.

%___Read data___%
DAY=xlsread('Jan_5mins.xlsx','A:A');
TIME=xlsread('Jan_5mins.xlsx','B:B');
ROV_5min=xlsread('Jan_5mins.xlsx','C:C');

%___Define variable and find ROVI___%
x=TIME;
y=DAY;
ROVI = sqrt( ROV_5min );
round(ROVI, 4);

CODE C:

Step - ROVI also I put in the previous excel used which is Jan_5mins.xlsx but I did not use it here. However, I tried to make the ROVI variable in .mat manually because I could not figure how to make ROVI in form of 31x288 (31 days x 5 minutes in 24 hours). Then it's time to plot the ROVI.

load ROVI_Jan.mat;

ROVI_all(isnan(ROVI_Jan))=0;
%ROVI_all=isnan(find(ROTI_all == 0,288));
row_has_all_zeros=~any(ROVI_Jan);
indices = find(row_has_all_zeros);
ROVI_all(any(ROVI_Jan,2)==0,:)=NaN;
clims=[0 1.0];

%___Display image___%
h=imagesc(ROVI_all,clims);
set(h,'alphadata',~isnan(ROVI_all));
caxis([0.0 1.0]);
cb=colorbar;
set(cb,'fontsize',10);
set(cb, 'yticklabel', {'0.0';'0.1';'0.2';'0.3';'0.4';'0.5';'0.6';'0.7';'0.8';'0.9';'1.0';},'FontSize',10);
colormap(jet)

%___Axes properities___%
set(gca,'FontSize',10);
xlabel('Coordinated Universal Time, UTC (hr)');
set(gca,'xTick',0:24:288);
set(gca,'xTickLabel',{ '0', '2', '4', '6', '8', '10', '12', '14', '16', '18', '20', '22','24'});
ylabel('Day of Month (DOM)');
set(gca,'yDir','normal')
set(gca,'yTick',0:5:31);
title({'Rate of change of VTEC index', '1 to 31 January 2014'},'FontSize',15);

*** But the image only come out in blue (means value ROVI_all=0) because the ROVI are in the form of complex double (e.g: 0.834100000000000 + 0.00000000000000i) and make ROV_all become 0. This is where I think something is wrong with the values that had been calculated before.

My expected image is something like below (ref: Google) but a little difference (x:UTC 00-24 hrs, y:day and colorbar is ROVI). I do have to plot something like this for a year data but I believe I can make it once I can do for a month first.

That is all for case (i) all PRN. While for case (ii) individual PRN, I still didn't start yet as I am stuck with the case (i) but the expected image is nearly the same as above just the data are not all PRN, it's just 31 classified PRN where let say for January I'm gonna have 31 images because of their individual PRN.

Thank you in advanced for your time in considering.

[Cris LaPierre]

You are getting complex numbers because you are taking the square root of negative numbers (some of your differences are negative). You can use either real or abs to extract the real part or compute the magnitude of your complex numbers (respectively).

[Ann]

Thank you for the useful comment!

Answer 1

[Cris LaPierre]

I'm not familiar with the processing steps for this type of data, but here's a modified version of your code that creates the image. Remember, an image is a matrix. Your code is creating a vector.

There were some other mistakes in your code as well as unnecessary code. I've made changes (including removing the diff so sqrt is not complex) to create something that works. No claims to it being correct.

%___Read data___%
data = readtable("Book_A.xlsx");

%___Convert DAY and TIME into durations___%
data.DAY = days(data.DAY);
data.TIME = days(data.TIME);

%___Create a datetime___%
data.TimeStamp = datetime(2014,01,01) + data.DAY-1 + data.TIME;
data.TimeStamp.Format = "MM-dd-yy HH:mm";

%___Convert the table to a timetable___%
dataTT = table2timetable(data,"RowTimes","TimeStamp");

%___Use retime to average the PRN data into 1 or 5 minute increments___%
PRNTT1min = retime(dataTT(:,"VTEC"),"minutely","mean");
PRNTT5min = retime(dataTT(:,"VTEC"),"regular","mean","TimeStep",minutes(5));

%___Calculate ROV 5 minute in second or minute___%
ROV_5min = PRNTT5min.VTEC/5;

ROVI_Jan = sqrt( ROV_5min );

ROVI_Jan(isnan(ROVI_Jan))=0;
ROVI_all = reshape(ROVI_Jan,24*60/5,[])';


%___Display image___%
imagesc(ROVI_all);
caxis([0.0 1.0]);
colorbar;
colormap(jet)

%___Axes properities___%
set(gca,'FontSize',10);
xlabel('Coordinated Universal Time, UTC (hr)');
set(gca,'xTick',0:24:288);
set(gca,'xTickLabel',{ '0', '2', '4', '6', '8', '10', '12', '14', '16', '18', '20', '22','24'});
ylabel('Day of Month (DOM)');
set(gca,'yDir','normal')
set(gca,'yTick',0:5:31);
title({'Rate of change of VTEC index', '1 to 31 January 2014'},'FontSize',15);

[Ann]

OH GOSH!!! Yours is perfectly perfect! I literaly crying after run the code.

I just noticed by removing the diff then divide with 5 minutes already answering the rate of change. I think I am completely clueless that time yea why should I do some diff to get the rate of change.

I am forever indebted to you, thank you so much, Cris!