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

# 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 " wrote in message ... > > 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 " wrote in message ... > 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 " wrote in message ... > > > > 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 " wrote in message ... > 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 " wrote in message ... > > 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 " wrote in message ... > > > > > > 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 " wrote in message ... > 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 " wrote in message ... > > 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 " wrote in message ... > > > 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 " wrote in message ... > > > > > > > > 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 " wrote in message ... > 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 " wrote in message ... > > 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 " wrote in message ... > > > 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 " wrote in message ... > > > > 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 " wrote in message ... > > > > > > > > > > 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 " wrote in message ... > 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 " wrote in message ... > > 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 " wrote in message ... > > > 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 " wrote in message ... > > > > 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 " wrote in message ... > > > > > 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 " wrote in message ... > > > > > > > > > > > > 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