Taking a date/time out of Excel or text file
in [Matlab]
|
From: Peter on 8 Aug 2010 02:42 I have a number of text files with: date-time p11 p12 .. p1n etc where pij are numeric values date-time p21 p22 .. p2n and date-time format is, e.g. "2003-01-01T00:00" I converted the file to .xls (from within Excel) just to try and use the "read from excel" function: [X,Y]=xlsread('DAR_radiation_2003-01.xls'); The pij values all end up in array X. The date-time values end up in array Y. I realized eventually that the form of date-time in Y was not a string, but a "cell". It's probably very simply but I can't work out how to easily do what I want, which is convert the "cell" type into a useful value. Really what I want to do is plot pi3 (for example) vs datetime. The steps seem to be: 1. Import the data (I can do this ok) 2. Convert the date-time value from "cell" into "string"? 3. Convert the "string" into Matlab date time? 4. Move this new value into matrix X so i can plot against the values recorded? Anyone help with how to do these steps (2-4)? And is it just as good to import the data directly from text file instead of Excel (saves one manual step)? Thanks, appreciate any help.
From: Peter on 8 Aug 2010 03:41 Answering 1% of my own question.. I created one value by: =====matlab========= EDU>> d=Y(2,1) d = '2003-01-01T00:00' ======end matlab ========= then trying something: =====matlab========= EDU>> dd=cell2mat(d) dd = 2003-01-01T00:00 EDU>> whos dd Name Size Bytes Class Attributes dd 1x16 32 char EDU>> dd(1:10) ans = 2003-01-01 ======end matlab ========= So I'm making tiny progress. I have one cell converted into text and then I can capture the data section.. So I thought ok, this will be easy: =====matlab========= EDU>> d=Y(:,1); EDU>> dd=cell2mat(d); ??? Error using ==> cat CAT arguments dimensions are not consistent. Error in ==> cell2mat at 89 m{n} = cat(1,c{:,n}); ======end matlab ========= Ok, not so easy, but for the meantime until someone tells me the super solution, let's try this: =====matlab========= EDU>> for i=1:numel(d) dd(i)=cell2mat(d(i)); end ??? In an assignment A(:) = B, the number of elements in A and B must be the same. ======end matlab ========= No. Do not pass Go, do not collect $200.. Well, I'm sure it's easy for those who know. Appreciate any advice. Thanks.
From: Steven_Lord on 8 Aug 2010 21:12 "Peter " <pgillies3(a)gmail.com> wrote in message news:i3ln2h$79g$1(a)fred.mathworks.com... > Answering 1% of my own question.. > > I created one value by: > =====matlab========= > EDU>> d=Y(2,1) > > d = '2003-01-01T00:00' > ======end matlab ========= > > then trying something: > > =====matlab========= > EDU>> dd=cell2mat(d) > > dd = > > 2003-01-01T00:00 > > EDU>> whos dd > Name Size Bytes Class Attributes > > dd 1x16 32 char > EDU>> dd(1:10) > > ans = > > 2003-01-01 > ======end matlab ========= > > So I'm making tiny progress. I have one cell converted into text and then > I can capture the data section.. > > So I thought ok, this will be easy: > > =====matlab========= > EDU>> d=Y(:,1); > EDU>> dd=cell2mat(d); > ??? Error using ==> cat > CAT arguments dimensions are not consistent. That suggests that the strings stored in your cells are not all the same size; you can't have a matrix with rows that have different number of columns. > Error in ==> cell2mat at 89 > m{n} = cat(1,c{:,n}); > ======end matlab ========= > > Ok, not so easy, but for the meantime until someone tells me the super > solution, let's try this: What are you trying to do? If you're trying to store those date strings in an array, a cell array is as good a choice as any. If you need to store the _dates_ in an array that's not a cell array, convert the date strings into date numbers using DATENUM and store those date numbers in an array. > =====matlab========= > EDU>> for i=1:numel(d) > dd(i)=cell2mat(d(i)); > end > ??? In an assignment A(:) = B, the number of elements in A and B > must be the same. You can't store a multi-character string in one element of the array dd, which is what this command tried to do. -- Steve Lord slord(a)mathworks.com comp.soft-sys.matlab (CSSM) FAQ: http://matlabwiki.mathworks.com/MATLAB_FAQ To contact Technical Support use the Contact Us link on http://www.mathworks.com
From: Peter on 9 Aug 2010 05:25 You asked "what am I trying to do?". Great question. My original data has date/time (in a text format of "yyyy-mm-ddThh-mm") and a corresponding numeric value. Using xlsread brings the date/time into one array (as a cell) and the numeric value into a different array. I wish I could use excel (but excel is limited to a small amount of data).. and anyway I'm sure matlab has a cool way of doing this. I have 12 of these files, 1 for each month. And 44,635 rows for each month. What I really want to do is: 1) bring all of these 12 files into 1 data set (535620 rows) 2) plot a graph of any subset of data of value vs date/time (easy to do once I have the data in one array) 3) do some simple statistics on this data I don't know how to bring this text format from a text file or excel into a matlab data value so that Matlab will understand it is a data/time and plot it accordingly.
From: Ashish Uthama on 9 Aug 2010 08:03
Have a quick look through this might be very useful for your project: http://www.mathworks.com/access/helpdesk/help/techdoc/matlab_prog/bspgcx2-1.html#bspgc4m-1 Or please paste this in the MATLAB command window: web([docroot,'/techdoc/matlab_prog/bspgcx2-1.html#bspgc4m-1']) An example of what Steve suggested: (the above material would help understand this) %c is the cell array of strings as returned by XLSREAD. >> c{1}='2003-01-01T00:00'; >> c{2}='2010-12-02T17:23'; >> v = datevec(c,'yyyy-mm-ddTHH:MM') v = 2003 1 1 0 0 0 2010 12 2 17 23 0 or >> v = datenum(c,'yyyy-mm-ddTHH:MM') v = 1.0e+05 * 7.3158 7.3447 You could use the single datenum value to plot against (i.e as the x-axis). Peter wrote: > You asked "what am I trying to do?". Great question. > > My original data has date/time (in a text format of "yyyy-mm-ddThh-mm") > and a corresponding numeric value. Using xlsread brings the date/time > into one array (as a cell) and the numeric value into a different array. > > I wish I could use excel (but excel is limited to a small amount of > data).. and anyway I'm sure matlab has a cool way of doing this. > > I have 12 of these files, 1 for each month. And 44,635 rows for each month. > > What I really want to do is: > 1) bring all of these 12 files into 1 data set (535620 rows) > > 2) plot a graph of any subset of data of value vs date/time (easy to do > once I have the data in one array) > > 3) do some simple statistics on this data > > I don't know how to bring this text format from a text file or excel > into a matlab data value so that Matlab will understand it is a > data/time and plot it accordingly. |