Read CSV with row in quotes (string and numbers)

Question

Benjamin Brodwolf 2020-3-10

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/510186-read-csv-with-row-in-quotes-string-and-numbers

评论： Benjamin Brodwolf 2020-3-11

采纳的回答： Star Strider

在 MATLAB Online 中打开

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, ...])

3 个评论
显示 1更早的评论隐藏 1更早的评论

Josh Zagorski 2020-3-10

编辑：Josh Zagorski 2020-3-10

在 MATLAB Online 中打开

I think your "quotes" are screwing up the data, I put your data into a csv with all the "quotes" removed and I was able to read in your data with importdata(*.csv,',') or if you just delete the first and last "quotes" on every line, it works as well

First way:

ans =
  4×21 cell array
  Columns 1 through 2
    {'id'        }    {'date'           }
    {'7129300520'}    {'20141013T000000'}
    {'6414100192'}    {'20141209T000000'}
    {'5631500400'}    {'20150225T000000'}
  Columns 3 through 5
    {'price' }    {'bedrooms'}    {'bathrooms'}
    {0×0 char}    {0×0 char  }    {0×0 char   }
    {0×0 char}    {0×0 char  }    {0×0 char   }
    {0×0 char}    {0×0 char  }    {0×0 char   }

Second way:

 {'id'        }    {'date'             }
    {'7129300520'}    {'20141013T000000""'}
    {'6414100192'}    {'20141209T000000""'}
    {'5631500400'}    {'20150225T000000""'}
  Columns 3 through 5
    {'price' }    {'bedrooms'}    {'bathrooms'}
    {'221900'}    {'3'       }    {'1'        }
    {'538000'}    {'3'       }    {'2.25'     }
    {'180000'}    {'2'       }    {'1'        }

Benjamin Brodwolf 2020-3-10

编辑：Benjamin Brodwolf 2020-3-10

Is it possible to remove this quotes via Matlab?

Josh Zagorski 2020-3-10

I don't think so, I can't read it as a .csv into excel either because of the quotes. At least not easier than removing the quotes at the beginning and end of each line...

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Star Strider 2020-3-10

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/510186-read-csv-with-row-in-quotes-string-and-numbers#answer_419526

编辑：Star Strider 2020-3-10

在 MATLAB Online 中打开

‘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:

4 个评论
显示 2更早的评论隐藏 2更早的评论

Benjamin Brodwolf 2020-3-11

Thank you Star Strider and Josh Zagorski for your help.

I found a way to load the value as string, remove the qoutes, split it in string arrays and convert it to a table. :-)

Star Strider 2020-3-11

As always, my pleasure!

请先登录，再进行评论。

Answer 2

Cris LaPierre 2020-3-11

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/510186-read-csv-with-row-in-quotes-string-and-numbers#answer_419714

编辑：Cris LaPierre 2020-3-11

在 MATLAB Online 中打开

I'd take advantage of some of the built in capabilities of readtable. By setting the appropriate options, MATLAB can handle the mix of quotes and commas for you automatically, bringing the data into a table with the desired data type already set.

% detect and set import options
opts = delimitedTextImportOptions("Delimiter",{'"',','},...
    "ConsecutiveDelimitersRule","join",...
    "LeadingDelimitersRule","ignore",...
    "VariableNamesLine",1,...
    "NumVariables",21,...
    "ExtraColumnsRule","ignore",...
    "DataLines",2);
% Set data types
opts = setvartype(opts,[1,3:8,13:21],'double');
opts = setvartype(opts,2,"datetime");
opts = setvartype(opts,[9,10],"logical");
opts = setvartype(opts,[11,12,15,16,17],"categorical");
% Define datetime format for input and display
opts = setvaropts(opts,2,"InputFormat",'yyyyMMdd''T''HHmmss',"DatetimeFormat",'d MMM yyyy');
% Import data
data = readtable("house_data.csv",opts)

To explain, I set quotes and commas as the delimiter. If there are more than one of these together, they are treated as a single delimeter. I had to tell it what line contained the variable names, as well as how many there were. I also had to tell it what row the data started on.

Hopefully the code is fairly straightforward, but let me know if you have any questions.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Benjamin Brodwolf 2020-3-11

Wow! That's awesome. I never thougth it would be possible to create a table this way. Really nice to know. I learned a lot.

请先登录，再进行评论。

Read CSV with row in quotes (string and numbers)

3 个评论
显示 1更早的评论隐藏 1更早的评论

采纳的回答

4 个评论
显示 2更早的评论隐藏 2更早的评论

更多回答（1 个）

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

Community Treasure Hunt

Read CSV with row in quotes (string and numbers)

3 个评论 显示 1更早的评论隐藏 1更早的评论

采纳的回答

4 个评论 显示 2更早的评论隐藏 2更早的评论

更多回答（1 个）

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

Community Treasure Hunt

3 个评论
显示 1更早的评论隐藏 1更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论

1 个评论
显示 -1更早的评论隐藏 -1更早的评论