How to have Row Names extend to empty rows.

Hello,

I am importing a data in txt file. Have a couple Rows where the Channel name or row name is in the first column under Var 1.When i import there are sections within the data (Data_in) where the Under Var1 you would find White Spaces but the corresponding Columns would have data. As the original txt file where this is being importated from it was a little matrix with in a table.

EXAMPLE: Here all the rows and Column data belongs to Var 1 ABC but I try to extract only First row of data agnist ABC shows up and not the ROWS below.

VAR1 VAR2 Var3 Var 4

ABC 5 15 23

10 20 25

55 85 65

Below is Screenshot of my results.

Also Attaching my Script and File to the question.

[Voss]

filename = 'C3_NGHD_E369.e001.p000_Freightliner_DD13_2021_ModeAB_11Spd_C3_1.txt';
Data_in = readtable(filename, 'Delimiter',{',',';',' '});

Is it intentional that Var4 and Var8 contain the (non-NaN) data in these rows,

disp(Data_in(1:10,:))

                  Var1                       Var2         Var3    Var4    Var5    Var6    Var7    Var8
    _________________________________    _____________    ____    ____    ____    ____    ____    ____

    {'C_ADS_ModeSupportKeyOnTimeout'}    {'[FLOAT]'  }    NaN       1     NaN     NaN     NaN       1 
    {'C_APPRRate_EngTrqMaxRate'     }    {'[FLOAT]'  }    NaN     250     NaN     NaN     NaN     250 
    {'C_APPRRate_EngTrqMaxThd'      }    {'[FLOAT]'  }    NaN     700     NaN     NaN     NaN     700 
    {'C_APPRRate_EngTrqMinRate'     }    {'[FLOAT]'  }    NaN     400     NaN     NaN     NaN     400 
    {'C_APPRRate_EngTrqMinThd'      }    {'[FLOAT]'  }    NaN     500     NaN     NaN     NaN     500 
    {'C_Acn_ADASConfig'             }    {'[UINT(8)]'}    NaN       0     NaN     NaN     NaN       0 
    {'C_Acn_AdasAdsc3Config'        }    {'[UINT(8)]'}    NaN       1     NaN     NaN     NaN       1 
    {'C_Acn_AdasTc1Config'          }    {'[UINT(8)]'}    NaN       0     NaN     NaN     NaN       0 
    {'C_Acn_AutoNeutralEnable'      }    {'[UINT(8)]'}    NaN       1     NaN     NaN     NaN       1 
    {'C_Acn_AutoNeutralToneEnable'  }    {'[UINT(8)]'}    NaN       0     NaN     NaN     NaN       0 

but Var3 and Var7 contain the (non-NaN) data in these rows?

disp(Data_in(105:124,:))

              Var1                  Var2       Var3    Var4    Var5    Var6    Var7    Var8
    _________________________    __________    ____    ____    ____    ____    ____    ____

    {'C_CluActForceMapDownY'}    {'[FLOAT'}     NaN    NaN     NaN     NaN      NaN    NaN 
    {':'                    }    {0×0 char}       0    NaN     NaN     NaN        0    NaN 
    {':'                    }    {0×0 char}     628    NaN     NaN     NaN      628    NaN 
    {':'                    }    {0×0 char}    1329    NaN     NaN     NaN     1329    NaN 
    {':'                    }    {0×0 char}    1987    NaN     NaN     NaN     1987    NaN 
    {':'                    }    {0×0 char}    2603    NaN     NaN     NaN     2603    NaN 
    {':'                    }    {0×0 char}    3111    NaN     NaN     NaN     3111    NaN 
    {':'                    }    {0×0 char}    3501    NaN     NaN     NaN     3501    NaN 
    {':'                    }    {0×0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                    }    {0×0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                    }    {0×0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                    }    {0×0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                    }    {0×0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                    }    {0×0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                    }    {0×0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                    }    {0×0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {'C_CluActForceMapUpY'  }    {'[FLOAT'}     NaN    NaN     NaN     NaN      NaN    NaN 
    {':'                    }    {0×0 char}       0    NaN     NaN     NaN        0    NaN 
    {':'                    }    {0×0 char}     785    NaN     NaN     NaN      785    NaN 
    {':'                    }    {0×0 char}    1473    NaN     NaN     NaN     1473    NaN 

In other words, is that how you want the table to be (except with Var1 filled in as described in your question)? Or would it be more convenient to have, say, a table with 4 columns (Name, Type, Value1, Value2) and not so many NaNs (maybe no NaNs)?

I notice in your script you don't use Var7, only ever Var1 and Var8. Are you expecting Var8 to contain the non-NaN data in those sections where it is actually in Var7, or do you really want to use the NaNs in those sections?

[Sai Gudlur]

Voss,

This is data that comes from various controller and its functions inputs. For instance in the 2nd screenshot that you shared the data below {'C_CluActForceMapDownY'} until {'C_CluActForceMapUpY' } is associated with {'C_CluActForceMapDownY'}. I actually want to perform various operations with multiple columns trying to take Var8 was just an example to see if I can extract the elements associated with one Variable/Parameter.

[Voss]

"the data below {'C_CluActForceMapDownY'} until {'C_CluActForceMapUpY' } is associated with {'C_CluActForceMapDownY'}"

I understand.

My question was more about Var3 vs Var4 and Var7 vs Var8. Like in the case of 'C_ADS_ModeSupportKeyOnTimeout' non-NaN data shows up in Var4 and Var8, but for 'C_CluActForceMapDownY' it's in Var3 and Var7. This is due to how readtable interprets the file, and it seems likely to me that this is not a desirable way to store the data. In particular, there's never more than 2 non-NaN values in Var3 through Var8 in any row.

filename = 'C3_NGHD_E369.e001.p000_Freightliner_DD13_2021_ModeAB_11Spd_C3_1.txt';
Data_in = readtable(filename, 'Delimiter',{',',';',' '});

% number of non-NaN values in each row, for Var3 through Var8:
n = sum(~isnan(Data_in{:,3:end}),2);

nnz(n == 0) % 1993 rows have all NaNs in Var3 through Var8

ans = 1993

nnz(n == 1) % 43 rows have one non-NaN value in Var3 through Var8

ans = 43

nnz(n == 2) % 50557 rows have two non-NaN values in Var3 through Var8

ans = 50557

nnz(n > 2) % no row has more than 2 non-NaN values in Var3 through Var8

ans = 0

So Var3 through Var8 could conceivably be collapsed into two columns of mostly non-NaN data, by making appropriate changes to the readtable call and/or by appropriate post-processing of the table returned from readtable.

Would it be better for your purposes to have just two columns of numeric data? Or do you need all those NaNs in there (which are merely an artifact of what readtable does with this crazy file format)?

In any case, it's something to think about, I think. Regardless, I've gone ahead and answered the question you asked.

Answer 1

[Voss]

filename = 'C3_NGHD_E369.e001.p000_Freightliner_DD13_2021_ModeAB_11Spd_C3_1.txt';
T = readtable(filename, 'Delimiter',{',',';',' '});

disp(T(114:130,:)) % example region with ':' Var1

             Var1                 Var2       Var3    Var4    Var5    Var6    Var7    Var8
    _______________________    __________    ____    ____    ____    ____    ____    ____

    {':'                  }    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                  }    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                  }    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                  }    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                  }    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                  }    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {':'                  }    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {'C_CluActForceMapUpY'}    {'[FLOAT'}     NaN    NaN     NaN     NaN      NaN    NaN 
    {':'                  }    {0x0 char}       0    NaN     NaN     NaN        0    NaN 
    {':'                  }    {0x0 char}     785    NaN     NaN     NaN      785    NaN 
    {':'                  }    {0x0 char}    1473    NaN     NaN     NaN     1473    NaN 
    {':'                  }    {0x0 char}    2170    NaN     NaN     NaN     2170    NaN 
    {':'                  }    {0x0 char}    2851    NaN     NaN     NaN     2851    NaN 
    {':'                  }    {0x0 char}    3398    NaN     NaN     NaN     3398    NaN 
    {':'                  }    {0x0 char}    3764    NaN     NaN     NaN     3764    NaN 
    {':'                  }    {0x0 char}    3915    NaN     NaN     NaN     3915    NaN 
    {':'                  }    {0x0 char}    4200    NaN     NaN     NaN     4200    NaN 

is_empty = strcmp(T.Var1,':').';
s_idx = strfind([false is_empty],[false true]);
e_idx = strfind([is_empty false],[true false]);
for ii = 1:numel(s_idx)
    T{s_idx(ii):e_idx(ii),1} = T{s_idx(ii)-1,1};
end

disp(T(114:130,:)) % now filled in

              Var1                  Var2       Var3    Var4    Var5    Var6    Var7    Var8
    _________________________    __________    ____    ____    ____    ____    ____    ____

    {'C_CluActForceMapDownY'}    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {'C_CluActForceMapDownY'}    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {'C_CluActForceMapDownY'}    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {'C_CluActForceMapDownY'}    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {'C_CluActForceMapDownY'}    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {'C_CluActForceMapDownY'}    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {'C_CluActForceMapDownY'}    {0x0 char}    3725    NaN     NaN     NaN     3725    NaN 
    {'C_CluActForceMapUpY'  }    {'[FLOAT'}     NaN    NaN     NaN     NaN      NaN    NaN 
    {'C_CluActForceMapUpY'  }    {0x0 char}       0    NaN     NaN     NaN        0    NaN 
    {'C_CluActForceMapUpY'  }    {0x0 char}     785    NaN     NaN     NaN      785    NaN 
    {'C_CluActForceMapUpY'  }    {0x0 char}    1473    NaN     NaN     NaN     1473    NaN 
    {'C_CluActForceMapUpY'  }    {0x0 char}    2170    NaN     NaN     NaN     2170    NaN 
    {'C_CluActForceMapUpY'  }    {0x0 char}    2851    NaN     NaN     NaN     2851    NaN 
    {'C_CluActForceMapUpY'  }    {0x0 char}    3398    NaN     NaN     NaN     3398    NaN 
    {'C_CluActForceMapUpY'  }    {0x0 char}    3764    NaN     NaN     NaN     3764    NaN 
    {'C_CluActForceMapUpY'  }    {0x0 char}    3915    NaN     NaN     NaN     3915    NaN 
    {'C_CluActForceMapUpY'  }    {0x0 char}    4200    NaN     NaN     NaN     4200    NaN 

[Sai Gudlur]

Thanks a lot Voss. Also attaching my code.

[filelist,pathn] = uigetfile('*.txt','Please Select a Par File');
if isnumeric(filelist); error('no file selected');
end
filename = fullfile(pathn, filelist);
tic
Data_in = readtable(filename, 'Delimiter',{',',';',' '});
is_empty = strcmp(Data_in.Var1,':').';
s_idx = strfind([false is_empty],[false true]);
e_idx = strfind([is_empty false],[true false]);
for ii = 1:numel(s_idx)
    Data_in{s_idx(ii):e_idx(ii),1} = Data_in{s_idx(ii)-1,1};
end
Par_File_Parameters = Data_in.Var1;
Parameter_Values = Data_in.Var8;
Eco_PG_00 = find(endsWith(Par_File_Parameters,"_00"));
Common_00 = Data_in(Eco_PG_00,:);
Eco_PG_Table = table(Common_00.Var1,Common_00.Var3,Common_00.Var7,'VariableNames',["Economy_PG","Economy_PG_Value","Economy_PG_Value_2"]);
Standard_PG_01 = find(endsWith(Par_File_Parameters,"_01"));
Common_01 = Data_in(Standard_PG_01,:);
Standard_PG_Table = table(Common_01.Var1,Common_01.Var3,Common_01.Var7,'VariableNames',["Standard_PG","Standard_PG_Value","Standard_PG_Value_2"]);
Performance_PG_02 = find(endsWith(Par_File_Parameters,"_00"));
Common_02 = Data_in(Performance_PG_02,:);
Performance_PG_02_Table = table(Common_02.Var1,Common_02.Var3,Common_02.Var7,'VariableNames',["Performace_PG","Performance_PG_Value","Performance_PG_Value_2"]);
Tanker_PG_03 = find(endsWith(Par_File_Parameters,"_03"));
Common_03 = Data_in(Tanker_PG_03,:);
Tanker_PG_Table = table(Common_03.Var1,Common_03.Var3,Common_03.Var7,'VariableNames',["Tanker_PG","Tanker_PG_Value","Tanker_PG_Value_2"]);
Eco_STandard_ismatch = Common_00.Var3== Common_01.Var3;
Full_Table = [Eco_PG_Table,Standard_PG_Table,Performance_PG_02_Table,Tanker_PG_Table];
[C,ia,ib] = unique(Full_Table(:,["Economy_PG_Value","Economy_PG_Value_2","Standard_PG_Value","Standard_PG_Value_2","Performance_PG_Value","Performance_PG_Value_2","Tanker_PG_Value","Tanker_PG_Value_2"]));
PG_Names = extractBefore(Eco_PG_Table.Economy_PG(ia),"_00");
Final_Table = [PG_Names,C];
Final_Table = renamevars(Final_Table,"Var1","PG_Names");
toc

[Voss]

You're welcome!

[Sai Gudlur]

[filelist,pathn] = uigetfile('*.txt','Please Select a Par Files', 'multiselect', 'on');
if isnumeric(filelist); error('no file selected');
end
filelist = cellstr(filelist);
filelist = fullfile(pathn, filelist);
N = numel(filelist);
Input_File_Names = cell(1,N);
tic
for K = 1:N
filename = filelist{K};
file_Details = importdata(filename);
Data_in = readtable(filename, 'Delimiter',{',',';',' '});
is_empty = strcmp(Data_in.Var1,':').';
s_idx = strfind([false is_empty],[false true]);
e_idx = strfind([is_empty false],[true false]);
for ii = 1:numel(s_idx)
    Data_in{s_idx(ii):e_idx(ii),1} = Data_in{s_idx(ii)-1,1};
end
Par_File_Parameters = Data_in.Var1;
Parameter_Values = Data_in.Var8;
Eco_PG_00 = find(endsWith(Par_File_Parameters,"_00"));
Common_00 = Data_in(Eco_PG_00,:);
Eco_PG_Table = table(Common_00.Var1,Common_00.Var3,Common_00.Var7,'VariableNames',["Economy_PG","Economy_PG_Value","Economy_PG_Value_2"]);
Standard_PG_01 = find(endsWith(Par_File_Parameters,"_01"));
Common_01 = Data_in(Standard_PG_01,:);
Standard_PG_Table = table(Common_01.Var1,Common_01.Var3,Common_01.Var7,'VariableNames',["Standard_PG","Standard_PG_Value","Standard_PG_Value_2"]);
Performance_PG_02 = find(endsWith(Par_File_Parameters,"_00"));
Common_02 = Data_in(Performance_PG_02,:);
Performance_PG_02_Table = table(Common_02.Var1,Common_02.Var3,Common_02.Var7,'VariableNames',["Performace_PG","Performance_PG_Value","Performance_PG_Value_2"]);
Tanker_PG_03 = find(endsWith(Par_File_Parameters,"_03"));
Common_03 = Data_in(Tanker_PG_03,:);
Tanker_PG_Table = table(Common_03.Var1,Common_03.Var3,Common_03.Var7,'VariableNames',["Tanker_PG","Tanker_PG_Value","Tanker_PG_Value_2"]);
% Eco_STandard_ismatch = Common_00.Var3== Common_01.Var3;
OffRoad_PG_04 = find(endsWith(Par_File_Parameters,"_04"));
Common_04 = Data_in(OffRoad_PG_04,:);
OffRoad_PG_Table = table(Common_04.Var1,Common_04.Var3,Common_04.Var7,'VariableNames',["OffRoad_PG","OffRoad_PG_Value","Off_Road_PG_Value_2"]);
DownSpeed_Eco_PG_05 = find(endsWith(Par_File_Parameters,"_05"));
Common_05 = Data_in(DownSpeed_Eco_PG_05,:);
DownSpeed_Eco_PG_Table = table(Common_05.Var1,Common_05.Var3,Common_05.Var7,'VariableNames',["Downspeed_Eco_PG","Downspeed_Eco_PG_Value","Downspeed_Eco_PG_Value_2"]);
Heavy_Haul_PG_06 = find(endsWith(Par_File_Parameters,"_06"));
Common_06 = Data_in(Heavy_Haul_PG_06,:);
Heavy_Haul_PG_Table = table(Common_06.Var1,Common_06.Var3,Common_06.Var7,'VariableNames',["Heavy_Haul_PG","Heavy_Haul_Value","Heavy_Haul_Value_2"]);
Full_Table {K} = [Eco_PG_Table,Standard_PG_Table,Performance_PG_02_Table,Tanker_PG_Table,OffRoad_PG_Table,Heavy_Haul_PG_Table];
All_Tables_Together = Full_Table{K}(:,["Economy_PG_Value","Economy_PG_Value_2","Standard_PG_Value","Standard_PG_Value_2","Performance_PG_Value","Performance_PG_Value_2","Tanker_PG_Value","Tanker_PG_Value_2","OffRoad_PG_Value","Off_Road_PG_Value_2","Heavy_Haul_Value","Heavy_Haul_Value_2"]);
PG_Names = extractBefore(Eco_PG_Table.Economy_PG,"_00");
Final_Table = [PG_Names,All_Tables_Together];
Final_Table  = renamevars(Final_Table,"Var1","PG_Names");
Output_Directory = 'C:\Users\C3064378\Documents\Odera_Results';
Output_Filename = cellstr(extractBetween(filename,"NGHD_",".txt"));
Output_File_Names = cellstr(append(Output_Directory,Output_Filename));
writetable(Final_Table,O);
end
toc
% Starting the Variable Output Directory is when I am trying to write each
% processed file into excel file with its respective Input file name and
% then move to next file. that's why ending my loop after write table. 

[Voss]

What extension do you want the output files to have? If .txt, you can use extractAfter; if something else, you can use extractBetween(_,_,".txt")+".xlsx" or whatever. I'm showing both ways below:

[filelist,pathn] = uigetfile('*.txt','Please Select a Par Files', 'multiselect', 'on');
if isnumeric(filelist)
    error('no file selected')
end
filelist = cellstr(filelist);

% output directory doesn't change inside the loop, so define it beforehand:
Output_Directory = 'C:\Users\C3064378\Documents\Odera_Results';
% and you can go ahead and construct your output file names before the loop too:
% if you want output files with .txt extension (same as input):
Output_Filename = extractAfter(filelist,"NGHD_");
% or if you want output files with some other extension:
Output_Filename = extractBetween(filelist,"NGHD_",".txt") + ".xlsx"; % <- include the output file extension (I've used .xlsx)
% full-path output file names:
Output_File_Names = fullfile(Output_Directory,Output_Filename);

filelist = fullfile(pathn, filelist);
N = numel(filelist);
tic
for K = 1:N
    filename = filelist{K};
    % ... read file
    % ... etc., etc.
    % ... construct Final_Table
    writetable(Final_Table,Output_File_Names{K});
end
toc

[Sai Gudlur]

Thank you!!!!! Once Again

You Also suggested I run it prior to the Loop as Directory would be defined ahead of time and doesn't change niether does the extension I was running it in the loop as it was the end was like if it works I could look into Computing time later. But yes it makes complete sense to do it with the headers as we already have the names in filelist.

The line below that you corrected is where is was messing up.

Output_Filename = extractBetween(filelist,"NGHD_",".txt") + ".xlsx"; % <- include the output file extension (I've used .xlsx)

[Voss]

You're welcome!