Reading and separating data

5 views (last 30 days)
Geant Bepi
Geant Bepi on 28 Mar 2015
Commented: Stephen23 on 30 Mar 2015
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 Comments
Stephen23
Stephen23 on 30 Mar 2015
Excel file formats are either proprietary binary files or compressed XML files, and are certainly not text files. Do not confuse these different formats!

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 30 Mar 2015
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 Comment
Geant Bepi
Geant Bepi on 30 Mar 2015
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.

Sign in to comment.

More Answers (1)

Jos (10584)
Jos (10584) on 30 Mar 2015
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 Comments
Geant Bepi
Geant Bepi on 30 Mar 2015
I also removed the delimiter because there are no commas in the xls file. but wasn't helpful.

Sign in to comment.

Categories

Find more on Large Files and Big Data in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!