Splitting one column into multiple columns

Hello,

I am writing a script to split a singe cell to 9 columns. Here is the sample data. I want to be able to do some operations on these columns. I have tried reshape but it doesn't seem to work. Also the actual dataset consists of unknown number of rows.

'0.17 1.7285 0.001763792 1.000977 56651.41 0.000017652 DHT11, OK, 35.0,'

'0.34 1.6992 0.001733896 1.000977 57629.88 0.000017352 DHT11, OK, 34.0,'

'0.51 1.6602 0.001694037 0.996094 58700.00 0.000017036 DHT11, OK, 34.0,'

'0.68 1.6650 0.001699019 1.000977 58814.96 0.000017002 DHT11, OK, 34.0,'

'0.85 1.7187 0.001753827 0.996094 56695.46 0.000017638 DHT11, OK, 34.0,'

'1.02 1.6943 0.001728914 0.996094 57513.83 0.000017387 DHT11, OK, 34.0,'

'1.19 1.6016 0.001634247 0.996094 60851.21 0.000016434 DHT11, OK, 34.0,'

Any help would be very useful.

Thanks

Gopika

[Walter Roberson]

Is it correct that the input is a cell array of character vectors?

Is there a reason that you are not using readtable() on the data?

Answer 1

[Walter Roberson]

input = {'0.17        1.7285         0.001763792       1.000977       56651.41         0.000017652          DHT11, 	OK,	35.0,';
'0.34        1.6992         0.001733896       1.000977       57629.88         0.000017352          DHT11, 	OK,	34.0,';
'0.51        1.6602         0.001694037       0.996094       58700.00         0.000017036          DHT11, 	OK,	34.0,';
'0.68        1.6650         0.001699019       1.000977       58814.96         0.000017002          DHT11, 	OK,	34.0,';
'0.85        1.7187         0.001753827       0.996094       56695.46         0.000017638          DHT11, 	OK,	34.0,';
'1.02        1.6943         0.001728914       0.996094       57513.83         0.000017387          DHT11, 	OK,	34.0,';
'1.19        1.6016         0.001634247       0.996094       60851.21         0.000016434          DHT11, 	OK,	34.0,'};

t = textscan(strjoin(input, '\n'), '%f%f%f%f%f%f%s%s%f,');   %this might be what you are looking for directly
as_table = cell2table([num2cell(cell2mat(t(1:6))),t{7},t{8}, num2cell(t{9})]);  %if you want a table

Answer 2

[Andrei Bobrov]

input = {'0.17        1.7285         0.001763792       1.000977       56651.41         0.000017652          DHT11, 	OK,	35.0,';
'0.34        1.6992         0.001733896       1.000977       57629.88         0.000017352          DHT11, 	OK,	34.0,';
'0.51        1.6602         0.001694037       0.996094       58700.00         0.000017036          DHT11, 	OK,	34.0,';
'0.68        1.6650         0.001699019       1.000977       58814.96         0.000017002          DHT11, 	OK,	34.0,';
'0.85        1.7187         0.001753827       0.996094       56695.46         0.000017638          DHT11, 	OK,	34.0,';
'1.02        1.6943         0.001728914       0.996094       57513.83         0.000017387          DHT11, 	OK,	34.0,';
'1.19        1.6016         0.001634247       0.996094       60851.21         0.000016434          DHT11, 	OK,	34.0,'};

v = regexp(input,'\d*(\.\d*)?|\w*\d*','match');
out = cat(1,v{:});
lo=~cellfun(@(x)isletter(x(1)),out);
out(lo) = cellfun(@str2double,out(lo),'un',0);
T = cell2table(out);

[Gopika Rajan]

Thanks. I am getting the following error. I am quite new to MATLAB so I am not sure what this means.

Error using regexp

All cells must be char row vectors.

Thanks

[Banister]

If you could post an example of the input you are using;

The above solutions expect the input to be in rows and in a cell format.

[Andrei Bobrov]

Please attach your 'input' as mat-file.

On my laptop:

>> input = {'0.17        1.7285         0.001763792       1.000977       56651.41         0.000017652          DHT11, 	OK,	35.0,';
'0.34        1.6992         0.001733896       1.000977       57629.88         0.000017352          DHT11, 	OK,	34.0,';
'0.51        1.6602         0.001694037       0.996094       58700.00         0.000017036          DHT11, 	OK,	34.0,';
'0.68        1.6650         0.001699019       1.000977       58814.96         0.000017002          DHT11, 	OK,	34.0,';
'0.85        1.7187         0.001753827       0.996094       56695.46         0.000017638          DHT11, 	OK,	34.0,';
'1.02        1.6943         0.001728914       0.996094       57513.83         0.000017387          DHT11, 	OK,	34.0,';
'1.19        1.6016         0.001634247       0.996094       60851.21         0.000016434          DHT11, 	OK,	34.0,'};
v = regexp(input,'\d*(\.\d*)?|\w*\d*','match');
out = cat(1,v{:});
lo=~cellfun(@(x)isletter(x(1)),out);
out(lo) = cellfun(@str2double,out(lo),'un',0);
T = cell2table(out)
T =
  7×9 table
    out1     out2       out3        out4      out5        out6        out7      out8    out9
    ____    ______    _________    _______    _____    __________    _______    ____    ____
    0.17    1.7285    0.0017638      1.001    56651    1.7652e-05    'DHT11'    'OK'     35 
    0.34    1.6992    0.0017339      1.001    57630    1.7352e-05    'DHT11'    'OK'     34 
    0.51    1.6602     0.001694    0.99609    58700    1.7036e-05    'DHT11'    'OK'     34 
    0.68     1.665     0.001699      1.001    58815    1.7002e-05    'DHT11'    'OK'     34 
    0.85    1.7187    0.0017538    0.99609    56695    1.7638e-05    'DHT11'    'OK'     34 
    1.02    1.6943    0.0017289    0.99609    57514    1.7387e-05    'DHT11'    'OK'     34 
    1.19    1.6016    0.0016342    0.99609    60851    1.6434e-05    'DHT11'    'OK'     34 
>> 

Answer 3

[Banister]

Not the most efficient, but,

input = {'0.17        1.7285         0.001763792       1.000977       56651.41         0.000017652          DHT11, 	OK,	35.0,';
'0.34        1.6992         0.001733896       1.000977       57629.88         0.000017352          DHT11, 	OK,	34.0,';
'0.51        1.6602         0.001694037       0.996094       58700.00         0.000017036          DHT11, 	OK,	34.0,';
'0.68        1.6650         0.001699019       1.000977       58814.96         0.000017002          DHT11, 	OK,	34.0,';
'0.85        1.7187         0.001753827       0.996094       56695.46         0.000017638          DHT11, 	OK,	34.0,';
'1.02        1.6943         0.001728914       0.996094       57513.83         0.000017387          DHT11, 	OK,	34.0,';
'1.19        1.6016         0.001634247       0.996094       60851.21         0.000016434          DHT11, 	OK,	34.0,'}

output = {}
for i = 1:size(input,1)
[b c] = regexp(input(i,1),'\s*|,\s*|,*','match','split')
output = [output; c{:,1}]
end

[Banister]

Is the original as posted a txt file or .csv? What command have you used to get the data into the workspace?

Answer 4

[Gopika Rajan]

Here is the script i have been working on. The first five rows have een removed.

 fid = fopen('LE.txt', 'r') ;              % Open source file.
 fgetl(fid) ;                                  % Read/discard line.
 fgetl(fid) ;                                  % Read/discard line.
 fgetl(fid) ;                                  % Read/discard line.
 fgetl(fid) ;                                  % Read/discard line.
 fgetl(fid) ;                                  % Read/discard line.
 buffer = fread(fid, Inf) ;                    % Read rest of the file.
 fclose(fid);
 fid = fopen('LE_truncated.txt', 'w');  ;   % Open destination file.
 fwrite(fid, buffer) ;                         % Save to file.
 fclose(fid) ;
 
 File = importdata('LE_truncated.txt');
 file1 = File(:,1);
 Data = (file1.textdata);

input = {Data};                           % This is where it shows the error ( Error using regexp,
                                                                                                   All cells must be char row vectors)
v = regexp(input,'\d*(\.\d*)?|\w*\d*','match');
out = cat(1,v{:});
lo=~cellfun(@(x)isletter(x(1)),out);
out(lo) = cellfun(@str2double,out(lo),'un',0);
T = cell2table(out);

[Walter Roberson]

Don't do that. Use readtable() with 'HeaderLines' option, or textscan() with 'HeaderLines' option.