You are now following this question
- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.
How to convert some string columns in a csv
7 views (last 30 days)
Show older comments
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
Accepted Answer
Star Strider
on 1 Feb 2016
Edited: Star Strider
on 1 Feb 2016
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.
22 Comments
Mohammad Amin Javadi
on 1 Feb 2016
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
on 1 Feb 2016
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
on 1 Feb 2016
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
on 1 Feb 2016
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
on 1 Feb 2016
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
on 2 Feb 2016
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
on 2 Feb 2016
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
on 2 Feb 2016
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
on 2 Feb 2016
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
on 2 Feb 2016
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
on 3 Feb 2016
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
on 3 Feb 2016
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
on 3 Feb 2016
I understand. You`re right. I think here we see how experience works!
Agnes Palit
on 22 Jul 2018
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
on 22 Jul 2018
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.
More Answers (0)
See Also
Categories
Find more on Characters and Strings in Help Center and File Exchange
Tags
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)