Separate one column of in a multi-column, (which is itself a cell array) into two columns in the existing cell array

I have an application that reads data (from someone else's code), in a cell array with 8 columns. Here is a snipped of the first 9 rows:

9×8 cell array

Columns 1 through 6

{'E100M000.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.04]}    {1×2 cell}    {[4]}
{'E100M001.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.05]}    {1×2 cell}    {[4]}
{'E100M002.txt'}    {'4781'}    {'23-Sep-2020 15:…'}    {[5.08]}    {1×2 cell}    {[0]}
{'E100M003.txt'}    {'4717'}    {'23-Sep-2020 15:…'}    {[3.36]}    {1×2 cell}    {[0]}
{'E100M004.txt'}    {'4781'}    {'23-Sep-2020 15:…'}    {[5.04]}    {1×2 cell}    {[0]}
{'E100M005.txt'}    {'6051'}    {'23-Sep-2020 16:…'}    {[5.08]}    {1×2 cell}    {[0]}
{'E100M006.txt'}    {'4167'}    {'23-Sep-2020 15:…'}    {[ 3.6]}    {1×2 cell}    {[0]}
{'E100M007.txt'}    {'6022'}    {'23-Sep-2020 15:…'}    {[0.26]}    {1×2 cell}    {[0]}
{'E100M008.txt'}    {'6002'}    {'23-Sep-2020 16:…'}    {[2.92]}    {1×2 cell}    {[0]}

Columns 7 through 8

{[2971.6]}    {'Pleasant Valley…'}
{[3523.4]}    {'Pleasant Valley…'}
{[     0]}    {'Peckham Propert…'}
{[     0]}    {'251 Creek Road'   }
{[     0]}    {'Peckham Propert…'}
{[     0]}    {'23 Plateau Road'  }
{[     0]}    {'Peckham Propert…'}
{[     0]}    {'Peckham Propert…'}

Column 5 is itself a cell array, and I would like to replace this with two columns in a new cell array of everything. Column 5 has a letter in the first column of the array, and a number in the second. Simple to split this? reshape? split? ???

Wish I could get it in the right format in the first place, no such luck.

Thanks!

Doug Anderson

[Arif Hoq]

as you did not attach your data, you can try this

% A is your cell array
col5=A{:,5}
out=horzcat(A(:,1),A(:,2),A(:,3),A(:,4),col5,A(:,6),A(:,7),A(:,8),A(:,9))

Answer 1

[Voss]

C = { ...
    'E100M000.txt' '6109' '23-Sep-2020 14:…' 0.04 {'A' 1} 4 2971.6 'Pleasant Valley…'; ...
    'E100M001.txt' '6109' '23-Sep-2020 14:…' 0.05 {'B' 2} 4 3523.4 'Pleasant Valley…'; ...
    'E100M002.txt' '4781' '23-Sep-2020 15:…' 5.08 {'C' 3} 0    0   'Peckham Propert…'}

C = 3×8 cell array
    {'E100M000.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0400]}    {1×2 cell}    {[4]}    {[2.9716e+03]}    {'Pleasant Valley…'}
    {'E100M001.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0500]}    {1×2 cell}    {[4]}    {[3.5234e+03]}    {'Pleasant Valley…'}
    {'E100M002.txt'}    {'4781'}    {'23-Sep-2020 15:…'}    {[5.0800]}    {1×2 cell}    {[0]}    {[         0]}    {'Peckham Propert…'}

C_new = [C(:,1:4) vertcat(C{:,5}) C(:,6:end)]

C_new = 3×9 cell array
    {'E100M000.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0400]}    {'A'}    {[1]}    {[4]}    {[2.9716e+03]}    {'Pleasant Valley…'}
    {'E100M001.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0500]}    {'B'}    {[2]}    {[4]}    {[3.5234e+03]}    {'Pleasant Valley…'}
    {'E100M002.txt'}    {'4781'}    {'23-Sep-2020 15:…'}    {[5.0800]}    {'C'}    {[3]}    {[0]}    {[         0]}    {'Peckham Propert…'}

[Douglas Anderson]

Thank you very much. That certainly worked, but I'm not sure why vertcat works in this situation.

[Voss]

You're welcome!

C = { ...
    'E100M000.txt' '6109' '23-Sep-2020 14:…' 0.04 {'A' 1} 4 2971.6 'Pleasant Valley…'; ...
    'E100M001.txt' '6109' '23-Sep-2020 14:…' 0.05 {'B' 2} 4 3523.4 'Pleasant Valley…'; ...
    'E100M002.txt' '4781' '23-Sep-2020 15:…' 5.08 {'C' 3} 0    0   'Peckham Propert…'};
vertcat(C{:,5})

ans = 3×2 cell array
    {'A'}    {[1]}
    {'B'}    {[2]}
    {'C'}    {[3]}

vertcat(C{:,5}) is the same as vercat(C{1,5}, C{2,5}, C{3,5}, ...)

It is an example of using a comma-separated list (in this case the elements of the fifth column of C) as input arguments (in this case inputs to vertcat).

See also: https://www.mathworks.com/matlabcentral/answers/1656435-tutorial-comma-separated-lists-and-how-to-use-them

[Douglas Anderson]

Ah, so Arif Hoq's comment is actually the same thing, but transposed so he could use horzcat. I'm guessing that the whole thing would need to be transposed again after the horzcat usage.

This way is much cleaner. Thank you once again, useful AND educational!

[Voss]

Actually, this is not correct:

col5=A{:,5}

That assigns only A{1,5} to col5 (because col5 is only one variable). Observe:

A = { ...
    'E100M000.txt' '6109' '23-Sep-2020 14:…' 0.04 {'A' 1} 4 2971.6 'Pleasant Valley…'; ...
    'E100M001.txt' '6109' '23-Sep-2020 14:…' 0.05 {'B' 2} 4 3523.4 'Pleasant Valley…'; ...
    'E100M002.txt' '4781' '23-Sep-2020 15:…' 5.08 {'C' 3} 0    0   'Peckham Propert…'};
col5 = A{:,5}

col5 = 1×2 cell array
    {'A'}    {[1]}

% try assigning to 2 output variables:
[col51,col52] = A{:,5}

col51 = 1×2 cell array
    {'A'}    {[1]}

col52 = 1×2 cell array
    {'B'}    {[2]}

So you would need to call vertcat() at that point as well:

col5 = vertcat(A{:,5})

col5 = 3×2 cell array
    {'A'}    {[1]}
    {'B'}    {[2]}
    {'C'}    {[3]}

Then, the explicit call to horzcat():

out = horzcat(A(:,1),A(:,2),A(:,3),A(:,4),col5,A(:,6),A(:,7),A(:,8))

out = 3×9 cell array
    {'E100M000.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0400]}    {'A'}    {[1]}    {[4]}    {[2.9716e+03]}    {'Pleasant Valley…'}
    {'E100M001.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0500]}    {'B'}    {[2]}    {[4]}    {[3.5234e+03]}    {'Pleasant Valley…'}
    {'E100M002.txt'}    {'4781'}    {'23-Sep-2020 15:…'}    {[5.0800]}    {'C'}    {[3]}    {[0]}    {[         0]}    {'Peckham Propert…'}

is the same as using [ ] to do the horizontal concatenation:

out = [A(:,1),A(:,2),A(:,3),A(:,4),col5,A(:,6),A(:,7),A(:,8)] % same

out = 3×9 cell array
    {'E100M000.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0400]}    {'A'}    {[1]}    {[4]}    {[2.9716e+03]}    {'Pleasant Valley…'}
    {'E100M001.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0500]}    {'B'}    {[2]}    {[4]}    {[3.5234e+03]}    {'Pleasant Valley…'}
    {'E100M002.txt'}    {'4781'}    {'23-Sep-2020 15:…'}    {[5.0800]}    {'C'}    {[3]}    {[0]}    {[         0]}    {'Peckham Propert…'}

And you don't need to take each individual column separately; you can take the first 4 together and the last 3 together:

out = [A(:,1:4),col5,A(:,6:end)] % same

out = 3×9 cell array
    {'E100M000.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0400]}    {'A'}    {[1]}    {[4]}    {[2.9716e+03]}    {'Pleasant Valley…'}
    {'E100M001.txt'}    {'6109'}    {'23-Sep-2020 14:…'}    {[0.0500]}    {'B'}    {[2]}    {[4]}    {[3.5234e+03]}    {'Pleasant Valley…'}
    {'E100M002.txt'}    {'4781'}    {'23-Sep-2020 15:…'}    {[5.0800]}    {'C'}    {[3]}    {[0]}    {[         0]}    {'Peckham Propert…'}

So the idea is essentially the same, yes; I just did it without the temporary variable col5. (And I don't think anything is transposed or needs to be transposed - you do vertcat on the elements of column 5, then horzcat (maybe using [ ]) that result with all the other columns (in the right order of course).)

[Douglas Anderson]

Thank you for the Tutorial. I hope others find it useful too!