MATLAB: Read CSV with row in quotes (string and numbers)

csvloadreadtable

I have no idea to read the .CSV file correctly.

Here are the titles incl. the first three rows as example (in the File, there are over 22'000):

id,date,price,bedrooms,bathrooms,sqft_living,sqft_lot,floors,waterfront,view,condition,grade,sqft_above,sqft_basement,yr_built,yr_renovated,zipcode,lat,long,sqft_living15,sqft_lot15
"7129300520,""20141013T000000"",221900,3,1,1180,5650,""1"",0,0,3,7,1180,0,1955,0,""98178"",47.5112,-122.257,1340,5650"
"6414100192,""20141209T000000"",538000,3,2.25,2570,7242,""2"",0,0,3,7,2170,400,1951,1991,""98125"",47.721,-122.319,1690,7639"
"5631500400,""20150225T000000"",180000,2,1,770,10000,""1"",0,0,3,6,770,0,1933,0,""98028"",47.7379,-122.233,2720,8062"

Every row is in quotes, and the data are strings (double quotes) and numbers. The demiliter is the comma ",".

I tried this:

opts = detectImportOptions('house_data.csv')
T = readtable('house_data.csv', opts);

But what I get is a nx1-table but it should be nx21-table (21 is the amount of the features [id, date, price, bedroms, …])

Best Answer

‘I have no idea to read the .CSV file correctly.’

One approach:

C = {"7129300520,""20141013T000000"",221900,3,1,1180,5650,""1"",0,0,3,7,1180,0,1955,0,""98178"",47.5112,-122.257,1340,5650"
"6414100192,""20141209T000000"",538000,3,2.25,2570,7242,""2"",0,0,3,7,2170,400,1951,1991,""98125"",47.721,-122.319,1690,7639"
"5631500400,""20150225T000000"",180000,2,1,770,10000,""1"",0,0,3,6,770,0,1933,0,""98028"",47.7379,-122.233,2720,8062"};
Vn = {'id','date','price','bedrooms','bathrooms','sqft_living','sqft_lot','floors','waterfront','view','condition','grade','sqft_above','sqft_basement','yr_built','yr_renovated','zipcode','lat','long','sqft_living15','sqft_lot15'};
T1 = array2table(squeeze(split([C{:}], ',')),'VariableNames',Vn);

producing:

T1 =
  3×21 table
         id                date             price      bedrooms    bathrooms    sqft_living    sqft_lot    floors    waterfront    view    condition    grade    sqft_above    sqft_basement    yr_built    yr_renovated     zipcode        lat          long       sqft_living15    sqft_lot15
    ____________    ___________________    ________    ________    _________    ___________    ________    ______    __________    ____    _________    _____    __________    _____________    ________    ____________    _________    _________    __________    _____________    __________
    "7129300520"    ""20141013T000000""    "221900"      "3"        "1"           "1180"       "5650"      ""1""        "0"        "0"        "3"        "7"       "1180"          "0"           "1955"        "0"          ""98178""    "47.5112"    "-122.257"       "1340"          "5650"  
    "6414100192"    ""20141209T000000""    "538000"      "3"        "2.25"        "2570"       "7242"      ""2""        "0"        "0"        "3"        "7"       "2170"          "400"         "1951"        "1991"       ""98125""    "47.721"     "-122.319"       "1690"          "7639"  
    "5631500400"    ""20150225T000000""    "180000"      "2"        "1"           "770"        "10000"     ""1""        "0"        "0"        "3"        "6"       "770"           "0"           "1933"        "0"          ""98028""    "47.7379"    "-122.233"       "2720"          "8062"

I will let you take it from there.

EDIT — (10 Mar 2020 at 22:03)

For example, extracting the numeric values is straightforward:

    Price = str2double(T1.price)

produces:

Related Solutions

MATLAB: Clean up imported table

% 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

MATLAB: How to combine cells in cell array

Try

table = table';
strjoin(table(~cellfun(@(s) isa(s,'double'), table)))

to deal with those {0x0 double} cells.

EDIT

Ah ok. There might be a better way, but see if this works:

res = cell(size(table,1),1);
for i = 1:size(table,1)
    row = table(i,:);
    res{i} = strjoin(row(~cellfun(@(s) isa(s,'double'), table(i,:))));
end

Great song by the way!

Best Answer

Related Solutions

MATLAB: Clean up imported table

MATLAB: How to combine cells in cell array

Related Question