Reading and separating data
2 views (last 30 days)
Show older comments
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
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!
Accepted Answer
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);
More Answers (1)
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
See Also
Categories
Find more on Characters and Strings 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!