Reading and separating data

Hey! Happy Easter folks!

I've been searching two days for an answer to this problem but couldn't figure out yet; there's tons of advise but I have to figure out the best approach for my particular case.

My data set looks like this:

Time 2012/02/13 00:02:00.000; <<- ALL this is in one cell........
place, 1, 2,  9;
item,     11,     14,     18,     21,     22,     24,     27,     31,     32;
value,   724,  1454,   344,   2449,  1683,   197,   463,   2750,   175;
increase,   -182,    175,    704, 408,    199,    208,    626,  154,   363;

Time 2012/02/18 00:00:00.000;
place, 1, 2,  5;
item,     11,     18,     22,     27,     32;
value,   2913,   1061,   3365,   2703,   3337;
increase, -624, -862, -820, -596, -505;

(just on here, columns are separated by comma and rows are separated by semicolon)

maximum number of items 32 (i.e., there could be 32 columns of data)

I need to plot Time (X axis) against value (Y axis) for each item.

Problem I am having with "load"/"fopen"/"strmatch"/"xlsread" is I can't specify a column for an item because it varies through the data (for e.g., item 18 above).

For each time the data is collected I need to plot the value of each item. How do I approach getting Matlab to separate this data for me?

Also I was having trouble assigning the single cell containing both characters and numbers as the X value (Time ...).

How should I approach this problem?

appreciate your time!

[Stephen23]

It would be easier for us if you simply uploaded your .MAT file, or whatever source file you are referring to, and tell us what you want to do with the data. You can use the paperclip button to upload data files: please do this by either editing your original question or adding a comment.

[Jos (10584)]

Is it in a plain ascii text file? If so, can you post a view of the lines?

From you description I gathered that there are 5 rows per set, always starting with the words "Time","place","item","value", and "increase", before the next set of data starts (" ... rows are separated by semicolon) "). Is that correct?

[Geant Bepi]

Hey, thanks Stephen and Jos,

yeah the data is in plain text files..no headings or anything weird like that. It begins like copied here below and repeats for about 300 times and in some files more (5 rows of data but varying number of columns).

Time 2012/02/13 00:02:00.000; <<- ALL this is in one cell........
place, 1, 2, 9;
item, 11, 14, 18, 21, 22, 24, 27, 31, 32; 
value, 724, 1454, 344, 2449, 1683, 197, 463, 2750, 175; 
increase, -182, 175, 704, 408, 199, 208, 626, 154, 363;
Time 2012/02/18 00:00:00.000; 
place, 1, 2, 5; 
item, 11, 18, 22, 27, 32; 
value, 2913, 1061, 3365, 2703, 3337; 
increase, -624, -862, -820, -596, -505;

(just on here, columns are separated by comma and rows are separated by semicolan)

I can get Matlab to read my text files, row by row, column by column. So the files I'm working with are fine.

What I need

X axis - row Time (row 1, row 5, row 10 so on..) Y axis - row Value

I need to plot Time against Value for each item

I don't know how to tell Matlab to do this. Because for e.g., item 18 is not always on column 3. (If an "item" is in column 3 then its corresponding "value" will always be in the same column-following row.)

The way I'm thinking I am approaching this like I would do with small set of data;

I know my "item" is a (1,32) matrix (maximum number of items is 32 (mostly data is generated for fewer items but that's the maximum it could go)).

Same for "value".

so plot at this particular "time" the "value" for item 1, and item 2 and so on..

When an item doesn't occupy the same column throughout how do I tell Matlab what I want?

Thanks heaps!

[dpb]

"I can get Matlab to read my text files..."

So, show us how you have the data stored; don't make us guess.

Basically, though, it'll be an application of logical addressing to find the location in the (presumably) cell array of the desired item for each case and then retrieve those. Or, alternatively, you convert the variable-sized cell data into a regular array by inserting missing values from which then the indices would be fixed. But, specific code would rely on knowing precisely how you have the data in the first place.

[Geant Bepi]

data is stored in an excel file(.xls). and Matlab2014b Import Data reads it as I described above. Even the xls file display the data as described above.

I added the commas and semicolons so it's easy to view here.

so do you mean, to add strmatch function? (Matlab keeps warning against using strmatch anymore).

>>
fileID = fopen('value.xls');

X = strmatch('Time',fileID);
>>

It just returns an empty matrix for X.

I'm still a rookie at Matlab. :-(

[dpb]

I don't have your file (you've resisted the previous requests to attach a file) so can't duplicate whatever it is that importdata does (and I don't have a release later than R2012b, either, so not sure could duplicate what you've got, anyway).

If you won't do the former, at least show us what

whos _your_loaded_variables_

gives.

[Geant Bepi]

Oh cool! I've added the file here.

When I tried the following the output was gibberish; probably because I don't know how to specify my data.

fileID = fopen('value.xls');
formatSpec = '%s''%{dd.MM.yyyy HH:mm:ss}D''%s''%f''%s''%f''%s''%f''%s''%f'; 
data = textscan(fileID,formatSpec);
fclose(fileID);
celldisp(data)

Any advice you have to put me on the right track is appreciated.

Cheers

[Image Analyst]

Where did you add the file? You have to BOTH click the "Choose file" button and then the "Attach file" button. You probably forgot to click "Attach file". Also read this http://www.mathworks.com/matlabcentral/answers/13205-tutorial-how-to-format-your-question-with-markup so we don't have to correct the formatting for you and you can get it right the first time.

[Geant Bepi]

I know, I did. here's the direct link if you can't see the tag;

[Guillaume]

An Excel file is not a text file. You cannot use textscan and similar to read it. You have to use xlsread or readtable.

[Geant Bepi]

Aha! I didn't know textscan cannot read Excel files.. thanks! but I tried xlsread before (1st post) it didn't help me.

Now, it returns the following error;

Cell contents reference from a non-cell array object.

Error in value_item_time (line 13) DATA(k0).time = datevec(strread(C{k},'Time %s')) ;

>> code I ran;

C = xlsread('value.xls');

k0 = 1 ;

for k=1:5:numel(C) ;

DATA(k0).time = datevec(strread(C{k},'Time %s')) ;

DATA(k0).place = strread(C{k+1}(7:end),'%d') ;

DATA(k0).item = strread(C{k+1}(6:end),'%d') ;

DATA(k0).value = strread(C{k+1}(6:end),'%d') ;

DATA(k0).increase = strread(C{k+1}(9:end),'%d');

k0 = k0 + 1 ;

end

[Stephen23]

Excel file formats are either proprietary binary files or compressed XML files, and are certainly not text files. Do not confuse these different formats!

Answer 1

[Guillaume]

Basically, you have two issues here: 1) importing the data into matlab, 2) rearranging it so that it's indexed by item number instead of time.

For 1), it would be much easier to work from the original text file, but since you've not posted that, I'll work with the excel file. Note that an excel file is not a text file and cannot be manipulated as such. The code for reading a text file or an excel file would be completely different. So for now, I'll simply do:

[numbers, text] = xlsread('value[1].xls')
itemtimes = datenum(regexp(text(1:5:end), '(?<=Time ).*', 'match', 'once'), 'yyyy/mm/dd HH:MM:SS.FFF');

Now for 2), the simplest thing is to iterate over the itemtimes and extract item numbers, values, and increase into a new container indexed by item number. The best containers would be either structures or maps. I'll use a map:

itemmap = containers.Map('KeyType', 'double', 'ValueType', 'any');
for row = 1:numel(itemtimes)
    itemtime = itemtimes(row);
    itemids = numbers((row-1)*5+2, :);
    itemvalues = numbers((row-1)*5+3, :);
    itemincreases = numbers((row-1)*5+4, :);
    for col = 1:sum(~isnan(itemids))
       newrow = [itemtimes(row) itemvalues(col) itemincreases(col)];
       if isKey(itemmap, itemids(col))
          itemmap(itemids(col)) = [itemmap(itemids(col)); newrow];
       else
          itemmap(itemids(col)) = newrow;
       end
    end
end

Each value in the map is an Nx3 matrix, where the first column is the time, the second column is the value and the third is the increase. So for example, for plotting Value vs Time for item 11:

m = itemmap(11);
plot(m(:, 1), m(:, 2));
datetick(gca);

[Geant Bepi]

wow man! this right here ((row-1)*5+2 was the thinking I was missing. I couldn't imaging the data set as an Nx3 matrix. thanks for illuminating my path.

yeah, probably you could do more with a txt file. I only have the xls file for now.

Answer 2

[Jos (10584)]

My approach would be:

1) read in all the lines of the text file as strings using ';' as a delimiter

C = textread('myfile.txt','%s','delimiter',';') ;

2) parse through all the strings using a loop

k0 = 1 ;
for k=1:5:numel(C) ;
  DATA(k0).time = datevec(strread(C{k},'Time %s','delimiter','')) ;
  DATA(k0).place = strread(C{k+1}(7:end),'%d','delimiter',',') ; % ignore the first 6 characters 
  DATA(k0).item = strread(C{k+1}(6:end),'%d','delimiter',',') ; 
  % etc.
  k0 = k0 + 1 ;
end

[Geant Bepi]

Hi Jos, thank you!

I tried, but it says;

Error using dataread Trouble reading literal string from file (row 1, field 1) ==> ÐÏࡱá

Error in strread (line 48) [varargout{1:nlhs}]=dataread('string',varargin{:}); %#ok<REMFF1>

Error in value_item_time (line 13) DATA(k0).time= datevec(strread(C{k},'Time %s')) ;

Also, "The variable DATA appears to change size on every loop iteration. consider preallocating for speed"; that's one.

Then I tried the following, with not much luck.

C = importdata('value.xls','%s');

k0 = zeros(1,1000000) ;

for k=1:5:numel(C) ;

DATA(k0).time = datevec(strread(C{k},'Time %s')) ;

DATA(k0).place = strread(C{k+1}(7:end),'%d','delimiter',',') ;

DATA(k0).item = strread(C{k+1}(6:end),'%d','delimiter',',') ;

DATA(k0).value = strread(C{k+1}(6:end),'%d','delimiter',',') ;

DATA(k0).increase = strread(C{k+1}(9:end),'%d','delimiter',',');

k0 = k0 + 1 ;

end

plot(time,value)

>>>>>>>

[Geant Bepi]

I also removed the delimiter because there are no commas in the xls file. but wasn't helpful.