How to convert some string columns in a csv

Hi I have csv file. It includes 151 rows and each row has 5 columns. The 4 first columns are numbers but the last one is string. How should I convert the last one to a specific number like 1 ? Thanks

Answer 1

[Star Strider]

You can read your .csv file with the textscan function without having to convert anything.

Example code:

fidi = fopen(file_name, 'r');
Data = textscan(fidi, '%f%f%f%f%s', 'Delimiter',',', 'CollectOutput',1);
fclose(fidi);

The ‘Data’ variable will be a (1x2) cell, with the first cell containing a (151x4) double array and the second a (151x1) string array.

EDIT — Added fclose call.

[Mohammad Amin Javadi]

That worked!! Some more questions: 1- What does 'r' mean in the first line? 2- The last column was string. Is that now number or we just separated that? 3- The last column from row 1-50 is one string which should be replaced by number 1, from row 51-100 is another string which should be replaced by number 2, and from row 101-150 (I mistakenly said 151 rows) is another string which should be replaced by number 3. Now, what should I do? 4- Finally I`d like to use plotmatrix to plot these cells. Thanks

[Star Strider]

My pleasure!

1. The 'r' is known as a ‘permission’, in this instance meaning ‘read’. See the documentation for fopen for all the interesting details.

2. The last column, ‘Data{2}’, remains a string (or here a cell array of strings).

3. If you want to create a numeric vector to have those properties, one easy way is:

v = [ones(50,1); ones(50,1)*2; ones(50,1)*3];

If you want to concatenate it to the numeric array:

Data{1} = cat(2,Data{1},v);

4. You can use plotmatrix with either the original or concatenated ‘Data{1}’ as:

figure(1)
plotmatrix(Data{1})

Note the curly braces ‘{}’ for cell addressing.

As always, my pleasure.

[Mohammad Amin Javadi]

Thank you so much. You`re a huge helper. Without no excess explanation, you solved everything! In this way, I have "Data" which includes 1*5 and 1*1 column. Is that possible that I omit the 5th one from first one and have the last column which was changed to string separately? I mean now I see 1*2 cell for Data which itself includes two cells of 1*2 and 1*1.

[Star Strider]

My pleasure.

I am also happy you like the format of my Answer.

I am a bit concerned that the first cell in ‘Data’ is a (1x5) double array. It should have 150 rows. Please run your code again, changing the textscan call to:

Data = textscan(fidi, '%f%f%f%f%s', 'Delimiter',',', 'CollectOutput',1, 'EndOfLine','\r\n');

to see if that gets all the rows.

The ‘Data’ variable as you read it in should have one cell as a (150x4) double and a (150x1) string. (If you concatenated the ‘v’ vector to the (150x4) cell, it will be (150x5).) You may have to use specific cell addressing to see that clearly.

You can use the cell2mat function on the double array to convert it to a double matrix, so you do not have to use cell addressing with it. You cannot use cell2mat on the string vector.

[Mohammad Amin Javadi]

Thank you so much. The ‘Data’ variable now has two cells as a (150x5) double and a (150x1) string. In fact, I need a (150x4) double which excludes the last (converted to num) column. I`d like to have the last converted column as a separate (150x1) double. At the end, I`ll draw the plot. using two variables (one 150*4 and the other 150*1).

[Star Strider]

Again, my pleasure.

Use the cell2mat function to convert the double cell to a double matrix:

DataMtx = cell2mat(Data{1});
DataMtx = DataMtx(:,1:4);

The ‘DataMtx’ variable is the double matrix. You can then use the cat function to concatenate the ‘v’ vector to it. (I described that earlier, so I won’t repeat it here.)

[Mohammad Amin Javadi]

Thank you very much for your great help

[Star Strider]

As always, my pleasure.

[Mohammad Amin Javadi]

By the way, the last command gives me the following error: Cell contents reference from a non-cell array object. Error in cell2mat (line 42) cellclass = class(c{1});

[Star Strider]

I don’t have your file so I’m guessing as to what the textscan call returns.

In that instance, forget the cell2mat call and just go with:

DataMtx = Data{1};

That should work.

[Mohammad Amin Javadi]

It worked. Thanks a lot

[Star Strider]

Great! My pleasure.

[Mohammad Amin Javadi]

I want to use the replace command to simultaneously change 3 different strings to 3 corresponding numbers. I used the following but it only replaces one at a time. str = Label; expression = 'Iris-setosa'; replace = '1'; newStr = regexprep(str,expression,replace) I`d like to do these for two other things, too: str = Label; expression = 'Iris-versicolor'; replace = '2'; newStr = regexprep(str,expression,replace) str = Label; expression = 'Iris-virginica'; replace = '3'; newStr = regexprep(str,expression,replace)

[Star Strider]

It can only replace one at a time.

You can’t vectorise it to replace multiple strings with multiple numbers at one go. You’re doing it correctly.

[Mohammad Amin Javadi]

Thanks but when I replace one of them and then replace another one, the first one goes back to its first status of being string.

[Star Strider]

That’s likely because you’re replacing them with strings:

replace = '1';

See if:

replace = 1;

works.

If not, replace them with strings and then use str2num on the last column to convert them to numbers, then concatenate them to the other matrix.

[Mohammad Amin Javadi]

Is that easier to use "if" command?

[Star Strider]

Experiment with different approaches to see what works best for you.

[Mohammad Amin Javadi]

I understand. You`re right. I think here we see how experience works!

[Star Strider]

That’s the only way to find out!

[Agnes Palit]

hi, any idea how to solve this?

I have one cell contains the data above on picture. Any idea how to convert that into one table? or one csv file? The type of the data inside the cell is "string"

[Star Strider]

The first line is going to be a problem regardless. Normally, I would begin with the readtable function. However, with your file, using the fileread (link) function first, then editing it and converting the rest to a table object would likely be best.

You will have to experiment.