Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
re-arranging data into a matrix

Subject: re-arranging data into a matrix

From: Tiina

Date: 20 Feb, 2010 00:42:02

Message: 1 of 8


I need to organise a large file of data that appears like
Date obs
20090102 900
20090102 600
20090102 800
20090102 800
20090102 700

20090103 650
20090103 900
20090103 840

20090104 100
20090104 1000
20090104 600
20090104 450

How can I transform the data to look like the following matrix: ( I got 1000s of dates like that and there are different observations for each day with a max of 100 per day but can be less at times so i got to fill the empty observations with zeros)

2009-01-02 900 600 800 800 700
2009-01-03 650 900 840 0 0
2009-01-04 100 1000 600 450 0
I appreciate any help 

tiina

Subject: re-arranging data into a matrix

From: Sadik

Date: 20 Feb, 2010 01:04:04

Message: 2 of 8

Actually you can do it very easily. Please realize that your dates are now unique numbers. 20 million, 90 thousand 102 and so on.

The best way would be reading your data into a 2-column matrix, very similar to what you have written [I will call this matrix A]. Then, you should do:

uniqueDates = unique(A(:,1));

newMatrix = zeros(length(uniqueDates),100);

for dateCounter = 1:length(uniqueDates)
    indicesOfThisDate = find(A(:,1)==uniqueDates(dateCounter));
    dataOfThisDate = A(indicesOfThisDate,2);
    newMatrix(dateCounter,1:length(dataOfThisDate)) = dataOfThisDate';
end

Please watch for typos since I didn't test this in matlab.

Best.




"Tiina " <creates1@gmail.com> wrote in message <hlnb4q$i5c$1@fred.mathworks.com>...
>
> I need to organise a large file of data that appears like
> Date obs
> 20090102 900
> 20090102 600
> 20090102 800
> 20090102 800
> 20090102 700
>
> 20090103 650
> 20090103 900
> 20090103 840
>
> 20090104 100
> 20090104 1000
> 20090104 600
> 20090104 450
>
> How can I transform the data to look like the following matrix: ( I got 1000s of dates like that and there are different observations for each day with a max of 100 per day but can be less at times so i got to fill the empty observations with zeros)
>
> 2009-01-02 900 600 800 800 700
> 2009-01-03 650 900 840 0 0
> 2009-01-04 100 1000 600 450 0
> I appreciate any help 
>
> tiina

Subject: re-arranging data into a matrix

From: Tiina

Date: 20 Feb, 2010 01:38:05

Message: 3 of 8

Hi,

Thanks Sadik, works perfect.

Can i change dates format from 20090102 to 2009-01-01 (yyyy-mm-dd), is there some function to do that?

regards,

tiina

"Sadik " <sadik.hava@gmail.com> wrote in message <hlnce3$8np$1@fred.mathworks.com>...
> Actually you can do it very easily. Please realize that your dates are now unique numbers. 20 million, 90 thousand 102 and so on.
>
> The best way would be reading your data into a 2-column matrix, very similar to what you have written [I will call this matrix A]. Then, you should do:
>
> uniqueDates = unique(A(:,1));
>
> newMatrix = zeros(length(uniqueDates),100);
>
> for dateCounter = 1:length(uniqueDates)
> indicesOfThisDate = find(A(:,1)==uniqueDates(dateCounter));
> dataOfThisDate = A(indicesOfThisDate,2);
> newMatrix(dateCounter,1:length(dataOfThisDate)) = dataOfThisDate';
> end
>
> Please watch for typos since I didn't test this in matlab.
>
> Best.
>
>
>
>
> "Tiina " <creates1@gmail.com> wrote in message <hlnb4q$i5c$1@fred.mathworks.com>...
> >
> > I need to organise a large file of data that appears like
> > Date obs
> > 20090102 900
> > 20090102 600
> > 20090102 800
> > 20090102 800
> > 20090102 700
> >
> > 20090103 650
> > 20090103 900
> > 20090103 840
> >
> > 20090104 100
> > 20090104 1000
> > 20090104 600
> > 20090104 450
> >
> > How can I transform the data to look like the following matrix: ( I got 1000s of dates like that and there are different observations for each day with a max of 100 per day but can be less at times so i got to fill the empty observations with zeros)
> >
> > 2009-01-02 900 600 800 800 700
> > 2009-01-03 650 900 840 0 0
> > 2009-01-04 100 1000 600 450 0
> > I appreciate any help 
> >
> > tiina

Subject: re-arranging data into a matrix

From: Sadik

Date: 20 Feb, 2010 01:47:02

Message: 4 of 8

You can use the following function:

function dashedDate = convertToDashedDate(myDate)

% Example input: myDate = 20090102; which is a number.

dashedDate = [num2str(myDate(1:4)) '-' num2str(myDate(5:6)) '-' num2str(myDate(7:8))];









"Tiina " <creates1@gmail.com> wrote in message <hlnedt$8g0$1@fred.mathworks.com>...
> Hi,
>
> Thanks Sadik, works perfect.
>
> Can i change dates format from 20090102 to 2009-01-01 (yyyy-mm-dd), is there some function to do that?
>
> regards,
>
> tiina
>
> "Sadik " <sadik.hava@gmail.com> wrote in message <hlnce3$8np$1@fred.mathworks.com>...
> > Actually you can do it very easily. Please realize that your dates are now unique numbers. 20 million, 90 thousand 102 and so on.
> >
> > The best way would be reading your data into a 2-column matrix, very similar to what you have written [I will call this matrix A]. Then, you should do:
> >
> > uniqueDates = unique(A(:,1));
> >
> > newMatrix = zeros(length(uniqueDates),100);
> >
> > for dateCounter = 1:length(uniqueDates)
> > indicesOfThisDate = find(A(:,1)==uniqueDates(dateCounter));
> > dataOfThisDate = A(indicesOfThisDate,2);
> > newMatrix(dateCounter,1:length(dataOfThisDate)) = dataOfThisDate';
> > end
> >
> > Please watch for typos since I didn't test this in matlab.
> >
> > Best.
> >
> >
> >
> >
> > "Tiina " <creates1@gmail.com> wrote in message <hlnb4q$i5c$1@fred.mathworks.com>...
> > >
> > > I need to organise a large file of data that appears like
> > > Date obs
> > > 20090102 900
> > > 20090102 600
> > > 20090102 800
> > > 20090102 800
> > > 20090102 700
> > >
> > > 20090103 650
> > > 20090103 900
> > > 20090103 840
> > >
> > > 20090104 100
> > > 20090104 1000
> > > 20090104 600
> > > 20090104 450
> > >
> > > How can I transform the data to look like the following matrix: ( I got 1000s of dates like that and there are different observations for each day with a max of 100 per day but can be less at times so i got to fill the empty observations with zeros)
> > >
> > > 2009-01-02 900 600 800 800 700
> > > 2009-01-03 650 900 840 0 0
> > > 2009-01-04 100 1000 600 450 0
> > > I appreciate any help 
> > >
> > > tiina

Subject: re-arranging data into a matrix

From: Sadik

Date: 20 Feb, 2010 01:56:02

Message: 5 of 8

By the way, you had better use a cell array whose dimensions are

length(uniqueDates) x 2.

The first column will keep the dashedDate strings, the second column will keep the data pertaining to that date.

If you want, you can write your cell array into an excel file using xlswrite and you can then see the form what you had written at the very beginning.

Best.




"Sadik " <sadik.hava@gmail.com> wrote in message <hlneum$a7r$1@fred.mathworks.com>...
> You can use the following function:
>
> function dashedDate = convertToDashedDate(myDate)
>
> % Example input: myDate = 20090102; which is a number.
>
> dashedDate = [num2str(myDate(1:4)) '-' num2str(myDate(5:6)) '-' num2str(myDate(7:8))];
>
>
>
>
>
>
>
>
>
> "Tiina " <creates1@gmail.com> wrote in message <hlnedt$8g0$1@fred.mathworks.com>...
> > Hi,
> >
> > Thanks Sadik, works perfect.
> >
> > Can i change dates format from 20090102 to 2009-01-01 (yyyy-mm-dd), is there some function to do that?
> >
> > regards,
> >
> > tiina
> >
> > "Sadik " <sadik.hava@gmail.com> wrote in message <hlnce3$8np$1@fred.mathworks.com>...
> > > Actually you can do it very easily. Please realize that your dates are now unique numbers. 20 million, 90 thousand 102 and so on.
> > >
> > > The best way would be reading your data into a 2-column matrix, very similar to what you have written [I will call this matrix A]. Then, you should do:
> > >
> > > uniqueDates = unique(A(:,1));
> > >
> > > newMatrix = zeros(length(uniqueDates),100);
> > >
> > > for dateCounter = 1:length(uniqueDates)
> > > indicesOfThisDate = find(A(:,1)==uniqueDates(dateCounter));
> > > dataOfThisDate = A(indicesOfThisDate,2);
> > > newMatrix(dateCounter,1:length(dataOfThisDate)) = dataOfThisDate';
> > > end
> > >
> > > Please watch for typos since I didn't test this in matlab.
> > >
> > > Best.
> > >
> > >
> > >
> > >
> > > "Tiina " <creates1@gmail.com> wrote in message <hlnb4q$i5c$1@fred.mathworks.com>...
> > > >
> > > > I need to organise a large file of data that appears like
> > > > Date obs
> > > > 20090102 900
> > > > 20090102 600
> > > > 20090102 800
> > > > 20090102 800
> > > > 20090102 700
> > > >
> > > > 20090103 650
> > > > 20090103 900
> > > > 20090103 840
> > > >
> > > > 20090104 100
> > > > 20090104 1000
> > > > 20090104 600
> > > > 20090104 450
> > > >
> > > > How can I transform the data to look like the following matrix: ( I got 1000s of dates like that and there are different observations for each day with a max of 100 per day but can be less at times so i got to fill the empty observations with zeros)
> > > >
> > > > 2009-01-02 900 600 800 800 700
> > > > 2009-01-03 650 900 840 0 0
> > > > 2009-01-04 100 1000 600 450 0
> > > > I appreciate any help 
> > > >
> > > > tiina

Subject: re-arranging data into a matrix

From: Tiina

Date: 20 Feb, 2010 02:20:23

Message: 6 of 8

Hi

I just tested dashedDate = [num2str(myDate(1:4)) '-' num2str(myDate(5:6)) '-' num2str(myDate(7:8))]; is producing an error as ??? Error using ==> horzcat
CAT arguments dimensions are not consistent.
Error in ==> convertToDashedDate at 5
dashedDate = [num2str(myDate(1:4)) '-' num2str(myDate(5:6)) '-' num2str(myDate(7:8))];

when evaluating [num2str(myDate(1:4))] alone to find what is going on I get a colums as such

20090102
20090103
20090104
20090105

maybe its not converting to string and just reading the first 4 rows in mydate vector.

Thanks for keeping up with me

Best

"Sadik " <sadik.hava@gmail.com> wrote in message <hlnffi$bui$1@fred.mathworks.com>...
> By the way, you had better use a cell array whose dimensions are
>
> length(uniqueDates) x 2.
>
> The first column will keep the dashedDate strings, the second column will keep the data pertaining to that date.
>
> If you want, you can write your cell array into an excel file using xlswrite and you can then see the form what you had written at the very beginning.
>
> Best.
>
>
>
>
> "Sadik " <sadik.hava@gmail.com> wrote in message <hlneum$a7r$1@fred.mathworks.com>...
> > You can use the following function:
> >
> > function dashedDate = convertToDashedDate(myDate)
> >
> > % Example input: myDate = 20090102; which is a number.
> >
> > dashedDate = [num2str(myDate(1:4)) '-' num2str(myDate(5:6)) '-' num2str(myDate(7:8))];
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "Tiina " <creates1@gmail.com> wrote in message <hlnedt$8g0$1@fred.mathworks.com>...
> > > Hi,
> > >
> > > Thanks Sadik, works perfect.
> > >
> > > Can i change dates format from 20090102 to 2009-01-01 (yyyy-mm-dd), is there some function to do that?
> > >
> > > regards,
> > >
> > > tiina
> > >
> > > "Sadik " <sadik.hava@gmail.com> wrote in message <hlnce3$8np$1@fred.mathworks.com>...
> > > > Actually you can do it very easily. Please realize that your dates are now unique numbers. 20 million, 90 thousand 102 and so on.
> > > >
> > > > The best way would be reading your data into a 2-column matrix, very similar to what you have written [I will call this matrix A]. Then, you should do:
> > > >
> > > > uniqueDates = unique(A(:,1));
> > > >
> > > > newMatrix = zeros(length(uniqueDates),100);
> > > >
> > > > for dateCounter = 1:length(uniqueDates)
> > > > indicesOfThisDate = find(A(:,1)==uniqueDates(dateCounter));
> > > > dataOfThisDate = A(indicesOfThisDate,2);
> > > > newMatrix(dateCounter,1:length(dataOfThisDate)) = dataOfThisDate';
> > > > end
> > > >
> > > > Please watch for typos since I didn't test this in matlab.
> > > >
> > > > Best.
> > > >
> > > >
> > > >
> > > >
> > > > "Tiina " <creates1@gmail.com> wrote in message <hlnb4q$i5c$1@fred.mathworks.com>...
> > > > >
> > > > > I need to organise a large file of data that appears like
> > > > > Date obs
> > > > > 20090102 900
> > > > > 20090102 600
> > > > > 20090102 800
> > > > > 20090102 800
> > > > > 20090102 700
> > > > >
> > > > > 20090103 650
> > > > > 20090103 900
> > > > > 20090103 840
> > > > >
> > > > > 20090104 100
> > > > > 20090104 1000
> > > > > 20090104 600
> > > > > 20090104 450
> > > > >
> > > > > How can I transform the data to look like the following matrix: ( I got 1000s of dates like that and there are different observations for each day with a max of 100 per day but can be less at times so i got to fill the empty observations with zeros)
> > > > >
> > > > > 2009-01-02 900 600 800 800 700
> > > > > 2009-01-03 650 900 840 0 0
> > > > > 2009-01-04 100 1000 600 450 0
> > > > > I appreciate any help 
> > > > >
> > > > > tiina

Subject: re-arranging data into a matrix

From: Sadik

Date: 20 Feb, 2010 02:34:03

Message: 7 of 8

I guess you are inputting the whole uniqueDates vector. Instead, you should pass them one by one:

cellArray = cell(length(uniqueDates),2);

for k = 1:length(uniqueDates)
    cellArray{k,1} = convertToDashedDate(uniqueDates(k));
    cellArray{k,2} = newMatrix(k,:);
end




"Tiina " <creates1@gmail.com> wrote in message <hlngt7$97f$1@fred.mathworks.com>...
> Hi
>
> I just tested dashedDate = [num2str(myDate(1:4)) '-' num2str(myDate(5:6)) '-' num2str(myDate(7:8))]; is producing an error as ??? Error using ==> horzcat
> CAT arguments dimensions are not consistent.
> Error in ==> convertToDashedDate at 5
> dashedDate = [num2str(myDate(1:4)) '-' num2str(myDate(5:6)) '-' num2str(myDate(7:8))];
>
> when evaluating [num2str(myDate(1:4))] alone to find what is going on I get a colums as such
>
> 20090102
> 20090103
> 20090104
> 20090105
>
> maybe its not converting to string and just reading the first 4 rows in mydate vector.
>
> Thanks for keeping up with me
>
> Best
>
> "Sadik " <sadik.hava@gmail.com> wrote in message <hlnffi$bui$1@fred.mathworks.com>...
> > By the way, you had better use a cell array whose dimensions are
> >
> > length(uniqueDates) x 2.
> >
> > The first column will keep the dashedDate strings, the second column will keep the data pertaining to that date.
> >
> > If you want, you can write your cell array into an excel file using xlswrite and you can then see the form what you had written at the very beginning.
> >
> > Best.
> >
> >
> >
> >
> > "Sadik " <sadik.hava@gmail.com> wrote in message <hlneum$a7r$1@fred.mathworks.com>...
> > > You can use the following function:
> > >
> > > function dashedDate = convertToDashedDate(myDate)
> > >
> > > % Example input: myDate = 20090102; which is a number.
> > >
> > > dashedDate = [num2str(myDate(1:4)) '-' num2str(myDate(5:6)) '-' num2str(myDate(7:8))];
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Tiina " <creates1@gmail.com> wrote in message <hlnedt$8g0$1@fred.mathworks.com>...
> > > > Hi,
> > > >
> > > > Thanks Sadik, works perfect.
> > > >
> > > > Can i change dates format from 20090102 to 2009-01-01 (yyyy-mm-dd), is there some function to do that?
> > > >
> > > > regards,
> > > >
> > > > tiina
> > > >
> > > > "Sadik " <sadik.hava@gmail.com> wrote in message <hlnce3$8np$1@fred.mathworks.com>...
> > > > > Actually you can do it very easily. Please realize that your dates are now unique numbers. 20 million, 90 thousand 102 and so on.
> > > > >
> > > > > The best way would be reading your data into a 2-column matrix, very similar to what you have written [I will call this matrix A]. Then, you should do:
> > > > >
> > > > > uniqueDates = unique(A(:,1));
> > > > >
> > > > > newMatrix = zeros(length(uniqueDates),100);
> > > > >
> > > > > for dateCounter = 1:length(uniqueDates)
> > > > > indicesOfThisDate = find(A(:,1)==uniqueDates(dateCounter));
> > > > > dataOfThisDate = A(indicesOfThisDate,2);
> > > > > newMatrix(dateCounter,1:length(dataOfThisDate)) = dataOfThisDate';
> > > > > end
> > > > >
> > > > > Please watch for typos since I didn't test this in matlab.
> > > > >
> > > > > Best.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Tiina " <creates1@gmail.com> wrote in message <hlnb4q$i5c$1@fred.mathworks.com>...
> > > > > >
> > > > > > I need to organise a large file of data that appears like
> > > > > > Date obs
> > > > > > 20090102 900
> > > > > > 20090102 600
> > > > > > 20090102 800
> > > > > > 20090102 800
> > > > > > 20090102 700
> > > > > >
> > > > > > 20090103 650
> > > > > > 20090103 900
> > > > > > 20090103 840
> > > > > >
> > > > > > 20090104 100
> > > > > > 20090104 1000
> > > > > > 20090104 600
> > > > > > 20090104 450
> > > > > >
> > > > > > How can I transform the data to look like the following matrix: ( I got 1000s of dates like that and there are different observations for each day with a max of 100 per day but can be less at times so i got to fill the empty observations with zeros)
> > > > > >
> > > > > > 2009-01-02 900 600 800 800 700
> > > > > > 2009-01-03 650 900 840 0 0
> > > > > > 2009-01-04 100 1000 600 450 0
> > > > > > I appreciate any help 
> > > > > >
> > > > > > tiina

Subject: re-arranging data into a matrix

From: Oleg Komarov

Date: 20 Feb, 2010 10:12:03

Message: 8 of 8

DDates = [...
20090102
20090103
20090104];

datestr(datenum(num2str(DDates), 'yyyymmdd'), 'yyyy-mm-dd')
ans =
2009-01-02
2009-01-03
2009-01-04

If you have stored DDates in cellformat convert them with cell2mat.

Oleg

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us