Reformat data

I have the following data: each set is as follows

code    jobnum  date                    time         type  status
0  1831  3.15000000000000  17:59:0          3  submitted 
1  1831  3.15000000000000  17:59:2          1  exectuing
5  1831  3.15000000000000  18:38:49  0  terminated
0     1832
1     1832
5     1832

how do I get it as

jobnum  date  submitted  executing terminated s_type exec_type
1831    3.15   17:59:0   17:59:2   18:38:49   3       1
1832

Answer 1

[Kye Taylor]

Assuming that your data lives inside someTextFile.txt, that the values in the data set are delimited/separated by tabs (\t) or spaces and that "exectuing" will actually be "executing", the following script formats the data like you showed, then writes this data to newTextFile.txt.

 fid = fopen('someTextFile.txt');
 inMatlab = textscan(fid,'%*f%f%f%s%f%s','headerlines',1);
 fclose(fid);

 % get unique jobnums
 uJobs = unique(inMatlab{1});
 newRow = cell(length(uJobs)+1,7);

 % create header for new text file
 newRow(1,:) = ...
{'jobnum','date','submitted','executing','terminated','s_type','exec_type'};

 % create new rows for new text file
 for j = 1:length(uJobs) 
    isJob = inMatlab{1} == uJobs(j); % which rows belong to this job
    isSub = strcmp('submitted',inMatlab{5});% which rows related to submission
    isExe = strcmp('executing',inMatlab{5});% which rows related to execution
    isTer = strcmp('terminated',inMatlab{5});%which rows related to termination
    jobDate = unique(inMatlab{2}(isJob));
    % make sure jobDate is indeed unique
    assert(isscalar(jobDate),'Same job on two different days?');

    % create new row
    newRow(j+1,:) = {uJobs(j),...
                    jobDate,...
                    char(inMatlab{3}(isSub & isJob)),...
                    char(inMatlab{3}(isExe & isJob)),...
                    char(inMatlab{3}(isTer & isJob)),...
                    inMatlab{4}(isSub & isJob),...
                    inMatlab{4}(isExe & isJob)};
 end

 % open new file
 fid = fopen('newTextFile.txt','wt');
 fprintf(fid,'%s\t%s\t%s\t%s\t%s\t%s\t%s\n',newRow{1,:});
 for j = 2:size(newRow,1)
   fprintf(fid,'%.0f\t%.2f\t%s\t%s\t%s\t%.0f\t%.0f\n',newRow{j,:});
 end
 fclose(fid);