Clean up imported table

How do i clean up the data set such that Matlab knows this is numberical values. I want to remove the qotation marks, and change from comme to dot, such that this can be treated as a "math matrix".

head(data);

The data set looks as follows:

ans =

  8×7 table

          ISIN            mdate           RET           RET11          ME          beta          ivol   
    ________________    __________    ___________    ___________    ________    __________    __________

    {'DE0001262012'}    2.0020e+05    {'0,3088' }    {'-0,55'  }    {'10,8'}    {'0,4582'}    {'0,087' }
    {'DE0001262012'}    2.0021e+05    {'-0,3333'}    {'-0,7135'}    {'7,2' }    {'0,62'  }    {'0,0558'}
    {'DE0001262012'}    2.0021e+05    {'-0,375' }    {'-0,7895'}    {'4,5' }    {'0,6142'}    {'0,0405'}
    {'DE0001262012'}    2.0021e+05    {'-0,0837'}    {'-0,8036'}    {'4,12'}    {'0,4678'}    {'0,0439'}
    {'DE0001262012'}    2.0021e+05    {'-0,2183'}    {'-0,8657'}    {'3,22'}    {'0,9192'}    {'0,0517'}
    {'DE0001262012'}    2.0021e+05    {'0,2339' }    {'-0,864' }    {'3,97'}    {'1,5634'}    {'0,0642'}
    {'DE0001262012'}    2.0021e+05    {'-0,3593'}    {'-0,9029'}    {'2,55'}    {'1,3786'}    {'0,0484'}
    {'DE0001262012'}    2.0021e+05    {'0,1766' }    {'-0,892' }    {'3'   }    {'1,2236'}    {'0,0965'}

[Cris LaPierre]

The easiest way is usually to set up the correct options before you import the data using something like detectImportOptions. Can you share your data file? You can attach it to your post using the paperclip icon.

[Andreas Bull]

Yes, I´ve uploaded the file now, thank you.

Answer 1

[Cris LaPierre]

% Set up import options
opts = detectImportOptions("DE_data_long.csv",'DecimalSeparator',",");
opts = setvartype(opts,3:7,"double");
opts = setvartype(opts,"ISIN","string");
opts = setvartype(opts,"mdate","datetime");
opts = setvaropts(opts,"mdate","InputFormat","yyyyMM");
% Import csv file
data = readtable("DE_data_long.csv",opts);
% Set date display format
data.mdate.Format = "MMM yyyy"

data = 67333x7 table
         ISIN          mdate        RET       RET11      ME      beta      ivol 
    ______________    ________    _______    _______    ____    ______    ______

    "DE0001262012"    May 2002     0.3088      -0.55    10.8    0.4582     0.087
    "DE0001262012"    Jun 2002    -0.3333    -0.7135     7.2      0.62    0.0558
    "DE0001262012"    Jul 2002     -0.375    -0.7895     4.5    0.6142    0.0405
    "DE0001262012"    Aug 2002    -0.0837    -0.8036    4.12    0.4678    0.0439
    "DE0001262012"    Sep 2002    -0.2183    -0.8657    3.22    0.9192    0.0517
    "DE0001262012"    Oct 2002     0.2339     -0.864    3.97    1.5634    0.0642
    "DE0001262012"    Nov 2002    -0.3593    -0.9029    2.55    1.3786    0.0484
    "DE0001262012"    Dec 2002     0.1766     -0.892       3    1.2236    0.0965
    "DE0001262012"    Jan 2003    -0.2251    -0.8761    2.32     1.097    0.0428
    "DE0001262012"    Feb 2003          0    -0.8489    2.32    1.0981    0.1241
    "DE0001262012"    Mar 2003          0    -0.7185    2.32    1.2001    0.0512
    "DE0001262012"    Apr 2003     0.2276    -0.7359    2.85    1.1065    0.0465
    "DE0001262012"    May 2003     0.1045    -0.5625    3.15    1.0234    0.0498
    "DE0001262012"    Jun 2003     0.0482    -0.2662     3.3     1.037    0.0556
    "DE0001262012"    Jul 2003    -0.3646    -0.4911     2.1    0.9895    0.0687
    "DE0001262012"    Aug 2003     0.1448    -0.2548     2.4    1.0516    0.0639