Convert the date string into date vector and expending existing matrix

Hi,

I want to convert the date string into date vector and store it back into the matrix by expending the existing matrix

this the data source:

 21/10/2012 16:00:00 0 81.34          
 21/10/2012 17:00:00 0 87.52

here is the new matrix that I failed to work it out:

yy mm dd hh mm ss      
2012 10 21 16 0 0 0 81.34  
2012 10 21 17 0 0 0 87.52

here is my code

d1a = rain_txt(i); % date string formation like 21/10/2012 16:00:00

formatIn = 'dd/mm/yyyy HH:MM:SS';

mat2= vec2mat(datevec(d1a,formatIn),6);

thank you

[Oleg Komarov]

What does 0 81.34 stand for? You need to exclude the last two columns.

[Noor Mohd Safar]

the raw data is from excel, 1st column : date & time 2nd and 3rd column : data with double data type.

for i=1:5
d1a = rain_txt(i);
formatIn = 'dd/mm/yyyy HH:MM:SS';
DateVec=datevec(d1a,formatIn);
mat2{i}=vec2mat(DateVec,6);
i=i+1;
end;

still not working

thank you

[Geoff Hayes]

@Zuraidin - you mentioned in your question that d1a is a date string formation like 21/10/2012 16:00:00'. So does that mean it does not include columns 2 and 3 from the Excel spreadsheet containing data like 0 and 81.34? If I run the following

 d1a = '21/10/2012 16:00:00';
 formatIn = 'dd/mm/yyyy HH:MM:SS';
 datevec(d1a,formatIn)

then the answer is

 ans =
        2012          10          21          16           0           0

which seems to be almost what you want. The above is a 1x6 vector, so it is not clear to me why you call the vec2mat function with this input and the 6. And if you are expecting the 0 and 81.34 to appear on the end, then you have to extract this data (from wherever it is) and append it to the end of the above result.

When you say that the code is still not working, what do you mean by that?

[Noor Mohd Safar]

thank you Geoff, I mean the script is not complete yet. The conversion to date vector is work but I dont know how to put it back into the matrix.

the data that I get is matrix within the matrix , hence mat2{i}. I'm using vec2mat to convert from date vector into matrix:

like this:

yyyy  mm dd hh mm ss data1 data2
2012  10 21 16 0  0  1.234 2.345

*data is in thousands of row

thank you

Answer 1

[Joseph Cheng]

A quick easy way to do this is

text=['21/10/2012 16:00:00 0 81.34';
'21/10/2012 17:00:00 0 87.52']
temp(find(temp=='/'))=' ';
temp(find(temp==':'))=' ';
for i =1:size(text,1)
A(i,:) = strread(temp(i,:))
end

which will give you

A =

             21           10         2012           16            0            0            0        81.34
             21           10         2012           17            0            0            0        87.52

[Noor Mohd Safar]

Thank you for your answer, I need to import the data from excel and put it into matrix, but i have a problem with the date and time. I need to split the year, month, day, hour,minute and s then put it into the existing or new matrix. Thank you

Answer 2

[Noor Mohd Safar]

*This is the script that works.* 

% ---------------------------------------------------------------------------------
% 14 JUL 2015
% Script for averaging climate data for every month if the available data
% year, month, day,hour and rain rate in its columns 1, 2, 3, 4 and 5, respectively.
% The output would be mean rain for each month
% can be used to plot monthly pattern of rainfall rate

% clear all;
from_file = 'F:\zuraidin\DATA\data_met\DATA_PROJECT\Chuping.xls';
filep = from_file;
[ANN_num_MEAN, ANN_txt_MEAN, ANN_data_MEAN] = xlsread(filep, 'RAIN_RATE_MEAN','');
[ANN_num_A_MEAN, ANN_txt_A_MEAN, ANN_data_A_MEAN] = xlsread(filep, 'RAIN_AMOUNT_MEAN','');
% [ANN_num_TEST, ANN_txt_TEST, ANN_data_TEST] = xlsread(filep, 'CLEAN_DATA_SMALL','');
% [temp, timestamps] = xlsread(filep, 'CLEAN_DATA4','');

% Suppose you have the climate data in column five of an array, A, 
% with the year, month, day, and hour in its columns 1, 2, 3, and 4, respectively.
% It should produce a row for every year-month combination, not just for 12 months.

% C = unique(A) returns the same data as in A, but with no repetitions.
A=ANN_num_MEAN;
[u,~,ix] = unique(A(:,1:2),'rows');
MEAN_RAIN_RATE = [u,accumarray(ix,A(:,5))./accumarray(ix,1)];

%month and year to number 
MEAN_RAIN_RATE_NUM = [datenum(MEAN_RAIN_RATE(:, 1), MEAN_RAIN_RATE(:, 2), 1) MEAN_RAIN_RATE(:, 3)];

% Plot rain rate vs month --------------------------------------------------------------
figure();
% plot(MEAN_RAIN_RATE_NUM(:,1),MEAN_RAIN_RATE_NUM(:,2),'r-o'); % r red , b  blue
plot(MEAN_RAIN_RATE_NUM(:,1),MEAN_RAIN_RATE_NUM(:,2),'b-o','LineWidth',1.5,'MarkerSize',5);
title('Monthly Average Rainfall Rate: Chuping')
xlabel('Month and Year');
ylabel('Mean mm/h');
legend('Monthly average');
set(gca,'XTick',MEAN_RAIN_RATE_NUM(:,1));%X axes ticks
datetick('x','mmm yyyy','keeplimits', 'keepticks');
xticklabel_rotate([],45); %rotate x label by calling fn xticklabel_rotate ()
hold off;