Reading and separating data

Question

0 个投票

value[1].xls

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!

12 个评论
显示 10更早的评论隐藏 10更早的评论

Geant Bepi 2015-3-29

编辑：dpb 2015-3-29

在 MATLAB Online 中打开

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!

Geant Bepi 2015-3-30

编辑：Geant Bepi 2015-3-30

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 2015-3-30

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

请先登录，再进行评论。

请先登录，再回答此问题。

Follow Question

Answer 1

Guillaume 2015-3-30

在 MATLAB Online 中打开

0 个投票

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);

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Geant Bepi 2015-3-30

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) 2015-3-30

在 MATLAB Online 中打开

0 个投票

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

2 个评论
显示无隐藏无

Geant Bepi 2015-3-30

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 2015-3-30

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

请先登录，再进行评论。

Reading and separating data

12 个评论
显示 10更早的评论隐藏 10更早的评论

采纳的回答

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

更多回答（1 个）

2 个评论
显示无隐藏无

类别

标签

Community Treasure Hunt

Reading and separating data

12 个评论 显示 10更早的评论 隐藏 10更早的评论

采纳的回答

1 个评论 显示 -1更早的评论 隐藏 -1更早的评论

更多回答（1 个）

2 个评论 显示 无 隐藏 无

类别

标签

另请参阅

Community Treasure Hunt

12 个评论
显示 10更早的评论隐藏 10更早的评论

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

2 个评论
显示无隐藏无