making matrix with text file information

I have following data:

2022-04-1113:38:14.002 0

G4 20

G9 39

G17 41

G20 42

G27 17

G28 41

G32 35

G4 20

G9 39

G17 41

G20 42

G27 17

G28 41

G32 35

2022-04-1113:38:15.000 0

G4 18

G9 42

G17 42

G20 38

G28 42

G30 43

G4 18

G9 42

G17 42

G20 38

G28 42

G30 43

I want to convert it to following:

G4 G9 G17 G20 G27 G28 G32 G30

2022-04-1113:38:14.002 20 39 41 42 17 41 35

2022-04-1113:38:15.000 18 42 42 38 42 43

1) Taking timestamp from rows and makes it row "a new" of matrix

2) in that new row, it looks for next lines until another timestamp found

2a) If no timestamp found, it makes a new colum for each unique values of G* and populates it with the value of signal strength

3) When new timestamp found it appends the matrix

4) The number of G* are not same for each time stamp

5) The value of G* are also vary with timestamp

Do you have any suggestion please?

[Geoff Hayes]

@Anjali Mishra - is the above data found in a text file? If so, can you attach it to this question?

[Anjali Mishra]

Thank you, the file is attached

[DGM]

Each data line appears to be duplicated. Is it certain that all duplicate Gxx entries in a given data block also contain duplicate data?

[Anjali Mishra]

It is true that each data is duplicated. They need to be removed.

[DGM]

Is the output simply to be a cell array, or is it text printed to a file or something?

Answer 1

[DGM]

EDIT: updated to handle new file encoding and empty blocks

I'm just going to throw this out there. I'm sure it's entirely overcomplicated, but I guess it does a thing. I'm sure there are tools that exist to make this kind of sorting easier, but I'm just going to opt to be a caveman today.

% open the file and read the whole thing
fid = fopen('TCM_Sat_SS.txt', 'r');
alllines = fread(fid,inf,'uint16=>char')';
fclose(fid);
alllines = split(alllines,newline);

% strip empty lines
alllines = alllines(~cellfun(@isempty,alllines));

% find timestamps
tslines = regexp(alllines,'^[0-9]{4}-[0-9]{2}-[0-9]{2,}.*$','match');
tslines = ~cellfun(@isempty,tslines);
tsidx = find(tslines);

% break the data into blocks
nblocks = numel(tsidx);
timestamps = cell(nblocks,1);
datablocks = cell(nblocks,1);
blockisempty = false(nblocks);
for k = 1:nblocks
    timestamps(k) = alllines(tsidx(k));
    if k ~= nblocks
        blockend = tsidx(k+1)-1;
    else
        blockend = size(alllines,1);
    end
    thisblock = unique(alllines(tsidx(k)+1:blockend));
    bdata = regexp(thisblock,'^G([0-9]+)\s+([0-9]+).*$','tokens');
    bdata = cellfun(@(x) str2double(x{:}),bdata,'uniform',false);
    if ~isempty(bdata)
        datablocks{k} = sortrows(cell2mat(bdata),1,'ascend');
    else
        blockisempty(k) = true;
    end
end

% get unique row header list
uqheaders = vertcat(datablocks{:});
uqheaders = unique(uqheaders(:,1));

% build the "table" column header
colheader = split(sprintf('G%d\n',uqheaders));
colheader = [{''} colheader(1:end-1)'];

% build output array
outputarray = cell(nblocks,numel(colheader));
outputarray = [colheader; outputarray];
outputarray(2:end,1) = timestamps;
for b = 1:nblocks
    if ~blockisempty(b)
        thisblock = datablocks{b};
        idx = find(ismember(uqheaders,thisblock(:,1)))+1;
        thisblock = num2cell(thisblock(:,2));
        [outputarray{b+1,idx}] = thisblock{:};
    end
end
outputarray

outputarray = 3705×12 cell array
    {0×0 char                  }    {'G1'      }    {'G2'      }    {'G4'      }    {'G9'}    {'G10'     }    {'G13'     }    {'G17'}    {'G20'}    {'G27'     }    {'G28'}    {'G32'}
    {'2022-04-1113:38:15.000→←'}    {0×0 double}    {0×0 double}    {[      18]}    {[42]}    {0×0 double}    {0×0 double}    {[ 42]}    {[ 38]}    {0×0 double}    {[ 42]}    {[ 43]}
    {'2022-04-1113:38:16.000→←'}    {0×0 double}    {0×0 double}    {[      18]}    {[39]}    {0×0 double}    {0×0 double}    {[ 42]}    {[ 43]}    {0×0 double}    {[ 42]}    {[ 46]}
    {'2022-04-1113:38:17.000→←'}    {0×0 double}    {0×0 double}    {[      17]}    {[39]}    {0×0 double}    {[      19]}    {[ 42]}    {[ 39]}    {0×0 double}    {[ 42]}    {[ 33]}
    {'2022-04-1113:38:18.000→←'}    {0×0 double}    {0×0 double}    {[      15]}    {[40]}    {0×0 double}    {[      16]}    {[ 42]}    {[ 40]}    {0×0 double}    {[ 42]}    {[ 38]}
    {'2022-04-1113:38:19.000→←'}    {0×0 double}    {0×0 double}    {0×0 double}    {[42]}    {0×0 double}    {[      14]}    {[ 42]}    {[ 35]}    {0×0 double}    {[ 42]}    {[ 38]}
    {'2022-04-1113:38:20.000→←'}    {0×0 double}    {0×0 double}    {0×0 double}    {[42]}    {0×0 double}    {[      11]}    {[ 41]}    {[ 45]}    {0×0 double}    {[ 41]}    {[ 42]}
    {'2022-04-1113:38:21.000→←'}    {0×0 double}    {0×0 double}    {[      39]}    {[40]}    {0×0 double}    {0×0 double}    {[ 41]}    {[ 39]}    {0×0 double}    {[ 41]}    {[ 36]}
    {'2022-04-1113:38:22.005→←'}    {[      41]}    {0×0 double}    {[      38]}    {[40]}    {0×0 double}    {0×0 double}    {[ 41]}    {[ 35]}    {[      41]}    {[ 41]}    {[ 42]}
    {'2022-04-1113:38:23.000→←'}    {0×0 double}    {0×0 double}    {[      29]}    {[40]}    {0×0 double}    {0×0 double}    {[ 41]}    {[ 44]}    {[      40]}    {[ 41]}    {[ 41]}
    {'2022-04-1113:38:24.006→←'}    {[      42]}    {0×0 double}    {[      45]}    {[43]}    {0×0 double}    {0×0 double}    {[ 41]}    {[ 42]}    {[      42]}    {[ 41]}    {[ 43]}
    {'2022-04-1112:10:55.720→←'}    {0×0 double}    {0×0 double}    {[      37]}    {[40]}    {0×0 double}    {0×0 double}    {[ 41]}    {[ 35]}    {[      41]}    {[ 41]}    {[ 42]}
    {'2022-04-1112:10:56.720→←'}    {[      39]}    {0×0 double}    {[      44]}    {[45]}    {0×0 double}    {0×0 double}    {[ 41]}    {[ 37]}    {[      34]}    {[ 41]}    {[ 40]}
    {'2022-04-1112:10:58.000→←'}    {[      39]}    {0×0 double}    {[      32]}    {[46]}    {0×0 double}    {0×0 double}    {[ 41]}    {[ 39]}    {[      37]}    {[ 41]}    {[ 43]}
    {'2022-04-1112:10:59.000→←'}    {[      45]}    {0×0 double}    {[      38]}    {[37]}    {0×0 double}    {0×0 double}    {[ 41]}    {[ 43]}    {[      38]}    {[ 41]}    {[ 38]}
    {'2022-04-1112:11:00.000→←'}    {[      38]}    {0×0 double}    {[      36]}    {[33]}    {0×0 double}    {0×0 double}    {[ 41]}    {[ 37]}    {[      42]}    {[ 41]}    {[ 30]}

[Stephen23]

Anjali Mishra's incorrectly posted "Answers" moved here and code formatted correctly:

Cell array output is most useful.

Code errors out at this point:

bdata = cellfun(@(x) str2double(x{:}),bdata,'uniform',false);

It says:

Error using cellfun
Not enough input arguments.
Error in untitled5 (line 29)
bdata = cellfun(@(x) str2double(x{:},bdata,'uniform',false));

When corrected as below:

 bdata = cellfun(@(x) str2double(x{:}),bdata,'uniform',false);

The error is now:

Error using matlab.internal.math.sortrowsParseInputs
Column sorting vector must contain integers with absolute value between 1 and the number of columns in the first argument.
Error in sortrows (line 64)
    [col, nanflag, compareflag] = matlab.internal.math.sortrowsParseInputs(ismatrix(A),size(A,2),A,varargin{:});
Error in untitled5 (line 30)
    datablocks{k} = sortrows(cell2mat(bdata),1,'ascend');

bdata and thisblock are both empty cells when code reaches this point.

It was some junk at the end of file which was causing teh two errors mentioned. The logic works well. Thank you very much.