Please help! Timestamp matrix problem, creating new matrices

Hi all,

I'm with a problem doing a part of a program, I hope you can help me.

I have this three columns in excel;

it goes until 31-01-2012 23:45

And now I want to read them into matlab and convert them into 3 new matrix with this format;

Timestamp

01-01-2012 00:00

01-01-2012 01:00

(...)

Period

A

C

Price

11,800000

10

So basicly I want a program that read's timestamp and keep's day month year and hour (ignoring the minutes), for that period of time it needs to show the most repetitive value in periods of one hour (for example: it will give A to 00 and C to 01) and finally I want it to sum the prices for that hour (so it will sum the price at 00:15 + price at 00:30 etc.)

It will be a huge help, if you can solve me this problem.

[Walter Roberson]

What if there was a 1 hour slot that had two 'D' and two 'E' period markers? Then there is no "most repetitive" period marker.

In the example you show, the Period marker is the same for everything in the first hour, and is different for the second hour but that second value is the same for the entire second hour. In the third hour, can the period again be 'A' or 'C', or is each period marker unique for an hour? Or is it cyclic, repeating at 24 hour intervals?

[João]

Thanks for responding, in the 1 hour periods I will always have one most repetitive marker (3 of A and 1 C, 3 D and 1 A etc.) I could find in this month 4 markers, A B C D. This markers aren't cyclic but I will find it all across the month, so it will reappear.

Answer 1

[Walter Roberson]

To convert S = '01-01-2012 00:15' to '01-01-2012 00:00' then

S(end-1:end) = '00';

Answer 2

[João]

The problem is that I have more than one item with minutes, if I use your method I will obtain

 S= 01-01-2012 00:00 , 01-01-2012 00:15, 01-01-2012 00:30, 01-01-2012 00:00.

And I only want to keep the first item ( ex: 01-01-2012 00:00) for each our.

[Walter Roberson]

If S is a cell array of strings, then cellfun() it.

newS = cellfun( @(in) [in(1:end-2), '00'], S, 'Uniform', 0);

Now you can group together all of the entries that have the same newS entry.

On the other hand, for this purpose it does matter whether you want to group together (:00, :15, :30, :45),(:00, :15, :30, :45), or if instead you want to group together (:15, :30, :45, :00), (:15, :30, :45, :00) Either grouping could be reasonable for different purposes, but the grouping that starts with :00 instead of starting with :15 is a little easier to compute with.

When you have your data sets, does the data in the file always start with what should be the first entry of a group, or does it sometimes start part way through a group? Are there ever any missing entries, or any extra entries? Is it every possible, for example, that the :00 routine might run a fraction of a second early and so end up with a :59 timestamp, thus giving 5 entries with the same leading hour?

[João]

I tried that but I get now a new parameter, seconds, e.g 1-01-2012 23:45:00' and for 00:00 I'm getting '1-01-2000'

I don't want to keep 00:15 00:30 or 00:45 I only want to get a new cell that has only the hour (00 in this case).

These data in file is basically the time since 01-01-2012 00:00 to 31-01-2012 23:45 with intervals of 15 minutes, so I will not have :59 and I don't have missing entries.

I show you here a bit more from the file I have at excel, maybe it will clarify my question.

For reading this files I'm using

[temp, timestamps] = xlsread('2012_15min.xls', 'JAN', 'A25:A2999');

[~,txt] = xlsread('2012_15min.xls','JAN','B25:B2999');

Power = xlsread('2012_15min.xls','JAN','C25:C2999');

[Walter Roberson]

Well a good way to be sure on the time extraction is to do a date conversion.

timevec = datevec(timestamps);
time_hour = timevec(:,4);

Or you might want

date_hour = timevec(:,1:4);

[João]

Thx once more for answering, that code isn't doing what I want to get,

for the last example I gave you I should get;

01 01 2012 0

01 01 2012 1

01 01 2012 2

01 01 2012 3

So I want to get the day,month, year and hour separated, and I don't want to get repetitive hours

[Walter Roberson]

date_myway = timevec(:,[3 2 1 4]);

if your order is D Mon Y H. datevec returns in the order Y Mon D H Min S Frac

[João]

Now it's almost doing what I want but after 1-12-2012 23 I'm getting wrong data I'm getting for example 5 7 18 0, hour is correct but the rest is wrong. And I only need one more thing I'm getting each hour repetitive 4 times (ex: 1 1 2012 0, 1 1 2012 0, 1 1 2012 0, 1 1 2012 0, 1 1 2012 1 etc) what do you recommend for not having repetitive dates?