MATLAB: Reading and separating data

fopenloadstrmatchxlsread

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!

Best Answer

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

Related Solutions

MATLAB: Merging specific Excel files according to a string in the filename

folder = '\\fscbeng\Project\Lab_Measurements\Results\20190424-1';
filelist = dir(fullfile(folder, '*.csv'));
filedata = cell(numel(filelist), 2);
for fileidx = 1:numel(filelist)
    filecontent = fileread(fullfile(folder, filelist(fileidx).name));  %read whole file at once
    [header, data] = regexp(filecontent, '^.*=+ DATA[^\n\r]+[\n\r]+[^\n\r+]+[\n\r]+', 'match', 'split', 'once');  %identify line with '==== DATA'. Keep everything up to that and the next line as header. After that it's data
    filedata{fileidx, 1} = header; 
    filedata{fileidx, 2} = data{2};  %data{1} is text before the regexp match which will always be empty since we match from the start
end
filenames = char({filelist.name});
filenames = filenames(:, 1:25);
filedata = cell2table([mat2cell(filenames, ones(1, numel(filelist)), [7, 16, 2]), filedata], ...
                   'VariableNames', {'Sensor_type', 'UID', 'Sensor_position', 'Header', 'Data'});
merged = rowfun(@(header, data) {[header{1}, data{:}]}, filedata, 'InputVariables', {'Header', 'Data'}, 'GroupingVariables', 'Sensor_position', 'OutputVariableNames', 'Content');

The above simply concatenates horizontally the header of the first file with the data of each file with the same Sensor_position. It doesn't add any newline between each, relying on the fact that your data already ends with a newline (at least in the sample file you shared). At the end you get a new table with the unique Sensor_position and the merged data. To actually write it to files you can use a loop (or another rowfun):

destfolder = '\\somewhere\somefolder'
for row = 1:height(merged)
    fid = fopen(fullfile(destfolder, sprintf('%s.csv', merged.Sensor_position{row})), 'w');  %no idea what the output file name should be. Just using sensor_position here
    fwrite(fid, merged.Content{row});
    fclose(fid);
end

Code is completely untested. There may be typos, bugs.

edit: fixed many typos

MATLAB: Add the value of column from text file to the value to matrix

data = load('u.data');
[uni_user, ~, uni_user_idx] = unique(data(:,1));
[uni_item, ~, uni_item_idx] = unique(data(:,2));
rating = data(:,3);
rating_matrix = sparse(uni_user_idx, uni_item_idx, rating);

This will have about 0.6% occupancy and so is well suited for a sparse matrix, but if you have particular reason, you can full() to turn it into a 12 megabyte rectangular array.

Best Answer

Related Solutions

MATLAB: Merging specific Excel files according to a string in the filename

MATLAB: Add the value of column from text file to the value to matrix

Related Question