combine two cell into one string

Hello

I have attached SummaryResult.xlsx

eg.

here, I need data as

for A3 B3 it should be aaa_20220623

same for A4 B4, aaa_20220413

I want them to be collected in variable called eg, Newlyaddednames[] = ['aaa_20220623', 'aaa_20220413', .....]

for this

for A21 B21 it should be aaa_20220623

same for A22 B22, aaa_20220413

I want them to be collected in variable called eg, Missingdatanames[] = ['aaa_20220623', 'aaa_20220413', .....]

Thank you

Please let me know for more brief

Answer 1

[Chunru]

t = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1085370/SummaryResult%20-%20Copy.xlsx');

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

t = t(1:6,1:4)

t = 6×4 table
    PLDStatus    BLFFileName    Time_sec_    EgoSpeed_kmh_
    _________    ___________    _________    _____________

     {'aaa'}     2.0221e+07       1504            12      
     {'aaa'}      2.022e+07       1234            11      
     {'aaa'}     2.0221e+07       1122            33      
     {'aaa'}     2.0221e+07       5673            21      
     {'aaa'}      2.022e+07       2345            22      
     {'aaa'}     2.0221e+07       3421            16      

 t.Missingdatanames = string(t.PLDStatus)+"_"+string(t.BLFFileName)

t = 6×5 table
    PLDStatus    BLFFileName    Time_sec_    EgoSpeed_kmh_    Missingdatanames
    _________    ___________    _________    _____________    ________________

     {'aaa'}     2.0221e+07       1504            12           "aaa_20220623" 
     {'aaa'}      2.022e+07       1234            11           "aaa_20220413" 
     {'aaa'}     2.0221e+07       1122            33           "aaa_20220903" 
     {'aaa'}     2.0221e+07       5673            21           "aaa_20221322" 
     {'aaa'}      2.022e+07       2345            22           "aaa_20220123" 
     {'aaa'}     2.0221e+07       3421            16           "aaa_20220620" 

[Santosh Biradar]

Thank you so much @Chunru

It worked.

Answer 2

[dpb]

opt=detectImportOptions('SummaryResult - Copy.xlsx');
opt.VariableTypes(1:2)={'char'};
tG=readtable('SummaryResult - Copy.xlsx',opt);
tG.New=join(tG{:,1:2},'_');

yields

>> tG.New
tG.New =
  23×1 cell array
    {'aaa_20220623'            }
    {'aaa_20220413'            }
    {'aaa_20220903'            }
    {'aaa_20221322'            }
    {'aaa_20220123'            }
    {'aaa_20220620'            }
    {'_'                       }
    {'_'                       }
    {'_'                       }
    {'_'                       }
    {'_'                       }
    {'_'                       }
    {'_'                       }
    {'_'                       }
    {'_'                       }
    {'_'                       }
    {'_'                       }
    {'MissingData_'            }
    {'PLD Status_BLF File Name'}
    {'aaa_20220623'            }
    {'aaa_20220413'            }
    {'aaa_20220903'            }
    {'aaa_20221322'            }
>> 

Select as desire with logical addressing those rows of interest; not clear what the deal is about all the missing stuff and other data like headers in the column, you'll know what to do with that.

Default input scanning returned the second column as numeric, not text, hence the use of DetectImportOptions to set the data type.

[Santosh Biradar]

Thank you for your responce @dpb

Answer 3

[Cris LaPierre]

I would do this in 2 steps.

file = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/1085370/SummaryResult%20-%20Copy.xlsx';
newlyAdded = readtable(file, 'Range','A2:P8','TextType','string')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

newlyAdded = 6×16 table
    PLDStatus    BLFFileName    Time_sec_    EgoSpeed_kmh_    AnalysisTargetOrNot    Weather    Branch_Merge_TunnelEtc_    RoadCondition    SurrondingVehicleStatus    ExistanceOfStaticObject_Pylon_carEtc__    LineCategory    LineColor    LineVisibility    LaneOfEgoCar    StabilityOfEgoCar    DetailReasonOfSystemLimit
    _________    ___________    _________    _____________    ___________________    _______    _______________________    _____________    _______________________    ______________________________________    ____________    _________    ______________    ____________    _________________    _________________________

      "aaa"      2.0221e+07       1504            12                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           
      "aaa"       2.022e+07       1234            11                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           
      "aaa"      2.0221e+07       1122            33                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           
      "aaa"      2.0221e+07       5673            21                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           
      "aaa"       2.022e+07       2345            22                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           
      "aaa"      2.0221e+07       3421            16                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           

Newlyaddednames = newlyAdded.PLDStatus + "_" + num2str(newlyAdded.BLFFileName)

Newlyaddednames = 6×1 string array
    "aaa_20220623"
    "aaa_20220413"
    "aaa_20220903"
    "aaa_20221322"
    "aaa_20220123"
    "aaa_20220620"

missingData = readtable(file,'Range','A21:P25','TextType','string')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

missingData = 4×16 table
    PLDStatus    BLFFileName    Time_sec_    EgoSpeed_kmh_    AnalysisTargetOrNot    Weather    Branch_Merge_TunnelEtc_    RoadCondition    SurrondingVehicleStatus    ExistanceOfStaticObject_Pylon_carEtc__    LineCategory    LineColor    LineVisibility    LaneOfEgoCar    StabilityOfEgoCar    DetailReasonOfSystemLimit
    _________    ___________    _________    _____________    ___________________    _______    _______________________    _____________    _______________________    ______________________________________    ____________    _________    ______________    ____________    _________________    _________________________

      "aaa"      2.0221e+07       1501            12                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           
      "aaa"       2.022e+07       1232            11                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           
      "aaa"      2.0221e+07       1128            33                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           
      "aaa"      2.0221e+07       5677            21                  NaN              NaN                NaN                   NaN                   NaN                               NaN                          NaN            NaN            NaN              NaN                NaN                      NaN           

Missingdatanames = missingData.PLDStatus + "_" + num2str(missingData.BLFFileName)

Missingdatanames = 4×1 string array
    "aaa_20220623"
    "aaa_20220413"
    "aaa_20220903"
    "aaa_20221322"

[Santosh Biradar]

Thank you for your responce @Cris LaPierre

Answer 4

[David Hill]

c=readtable('SummaryResult - Copy.xlsx');
m=[cell2mat(c.PLDStatus([1:6,20:23])),repmat('_',10,1),num2str(c.BLFFileName([1:6,20:23]))];

[Santosh Biradar]

Thank you for your responce @David Hill

Answer 5

[Santosh Biradar]

Hello @dpb @Chunru @Cris LaPierre @David Hill

I am very thankful for your response but I tried to do with everyone's soution, facing below error.

As Excel is having Column name as "BLF File Name". As it is having space between the words. Unble to read the space.

NewData = num2str(t.BLFFileName)+"_"+num2str(t.Time_sec_);

Unable to resolve the name c.BLFFileName.

Error: Invalid expression. Check for missing multiplication operator, missing or unbalanced delimiters, or other syntax

error. To construct matrices, use brackets instead of parentheses.

I tried with different expression as

num2str(t.('BLF File Name'))+"_"+num2str(t.Time_sec_);

num2str(t.('BLFFileName'))+"_"+num2str(t.Time_sec_);

num2str(t.('BLF_File_Name'))+"_"+num2str(t.Time_sec_);

num2str(t.'BLF_File_Name')+"_"+num2str(t.Time_sec_);

num2str(t.(BLF_File_Name))+"_"+num2str(t.Time_sec_);

for all, showing same error msg.

Thank you

Please let me know for brief.

[Cris LaPierre]

It all depends on how you read in the table. Preview your table, or at least the variable names, to see. By default, MATLAB will convert the column names to valid variable names unless you preserve variable names using the "VariableNamingRule","preserve" name-value pair.

file = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/1085370/SummaryResult%20-%20Copy.xlsx';
dflt = readtable(file);

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

head(dflt)

ans = 8×16 table
    PLDStatus     BLFFileName    Time_sec_    EgoSpeed_kmh_    AnalysisTargetOrNot     Weather      Branch_Merge_TunnelEtc_    RoadCondition    SurrondingVehicleStatus    ExistanceOfStaticObject_Pylon_carEtc__    LineCategory    LineColor     LineVisibility    LaneOfEgoCar    StabilityOfEgoCar    DetailReasonOfSystemLimit
    __________    ___________    _________    _____________    ___________________    __________    _______________________    _____________    _______________________    ______________________________________    ____________    __________    ______________    ____________    _________________    _________________________

    {'aaa'   }    2.0221e+07       1504             12             {0×0 char}         {0×0 char}          {0×0 char}            {0×0 char}            {0×0 char}                         {0×0 char}                   {0×0 char}     {0×0 char}      {0×0 char}       {0×0 char}        {0×0 char}               {0×0 char}        
    {'aaa'   }     2.022e+07       1234             11             {0×0 char}         {0×0 char}          {0×0 char}            {0×0 char}            {0×0 char}                         {0×0 char}                   {0×0 char}     {0×0 char}      {0×0 char}       {0×0 char}        {0×0 char}               {0×0 char}        
    {'aaa'   }    2.0221e+07       1122             33             {0×0 char}         {0×0 char}          {0×0 char}            {0×0 char}            {0×0 char}                         {0×0 char}                   {0×0 char}     {0×0 char}      {0×0 char}       {0×0 char}        {0×0 char}               {0×0 char}        
    {'aaa'   }    2.0221e+07       5673             21             {0×0 char}         {0×0 char}          {0×0 char}            {0×0 char}            {0×0 char}                         {0×0 char}                   {0×0 char}     {0×0 char}      {0×0 char}       {0×0 char}        {0×0 char}               {0×0 char}        
    {'aaa'   }     2.022e+07       2345             22             {0×0 char}         {0×0 char}          {0×0 char}            {0×0 char}            {0×0 char}                         {0×0 char}                   {0×0 char}     {0×0 char}      {0×0 char}       {0×0 char}        {0×0 char}               {0×0 char}        
    {'aaa'   }    2.0221e+07       3421             16             {0×0 char}         {0×0 char}          {0×0 char}            {0×0 char}            {0×0 char}                         {0×0 char}                   {0×0 char}     {0×0 char}      {0×0 char}       {0×0 char}        {0×0 char}               {0×0 char}        
    {0×0 char}           NaN        NaN            NaN             {0×0 char}         {0×0 char}          {0×0 char}            {0×0 char}            {0×0 char}                         {0×0 char}                   {0×0 char}     {0×0 char}      {0×0 char}       {0×0 char}        {0×0 char}               {0×0 char}        
    {0×0 char}           NaN        NaN            NaN             {0×0 char}         {0×0 char}          {0×0 char}            {0×0 char}            {0×0 char}                         {0×0 char}                   {0×0 char}     {0×0 char}      {0×0 char}       {0×0 char}        {0×0 char}               {0×0 char}        

dflt.BLFFileName(1)

ans = 20220623

% Now do the same but with the 'preserve' option
preserve = readtable(file,"VariableNamingRule","preserve")

preserve = 23×16 table
    PLD Status    BLF File Name    Time(sec)    EgoSpeed(kmh)    Analysis Target or not     Weather      Branch/Merge/Tunnel etc.    Road condition    Surronding vehicle status    Existance of static object(Pylon,car etc.)    Line category    Line color    Line visibility    Lane of ego car    Stability of ego car    Detail reason of system limit
    __________    _____________    _________    _____________    ______________________    __________    ________________________    ______________    _________________________    __________________________________________    _____________    __________    _______________    _______________    ____________________    _____________________________

    {'aaa'   }     2.0221e+07        1504             12               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }      2.022e+07        1234             11               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }     2.0221e+07        1122             33               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }     2.0221e+07        5673             21               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }      2.022e+07        2345             22               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }     2.0221e+07        3421             16               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          

head(preserve)

ans = 8×16 table
    PLD Status    BLF File Name    Time(sec)    EgoSpeed(kmh)    Analysis Target or not     Weather      Branch/Merge/Tunnel etc.    Road condition    Surronding vehicle status    Existance of static object(Pylon,car etc.)    Line category    Line color    Line visibility    Lane of ego car    Stability of ego car    Detail reason of system limit
    __________    _____________    _________    _____________    ______________________    __________    ________________________    ______________    _________________________    __________________________________________    _____________    __________    _______________    _______________    ____________________    _____________________________

    {'aaa'   }     2.0221e+07        1504             12               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }      2.022e+07        1234             11               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }     2.0221e+07        1122             33               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }     2.0221e+07        5673             21               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }      2.022e+07        2345             22               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {'aaa'   }     2.0221e+07        3421             16               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          
    {0×0 char}            NaN         NaN            NaN               {0×0 char}          {0×0 char}           {0×0 char}             {0×0 char}             {0×0 char}                            {0×0 char}                     {0×0 char}      {0×0 char}      {0×0 char}         {0×0 char}            {0×0 char}                  {0×0 char}          

preserve.('BLF File Name')(1)

ans = 20220623