combine two cell into one string

5 views (last 30 days)
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

Accepted Answer

Chunru
Chunru on 2 Aug 2022
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"

More Answers (4)

dpb
dpb on 2 Aug 2022
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.

Cris LaPierre
Cris LaPierre on 2 Aug 2022
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"

David Hill
David Hill on 2 Aug 2022
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
Santosh Biradar on 2 Aug 2022
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.
  1 Comment
Cris LaPierre
Cris LaPierre on 2 Aug 2022
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

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!