6
$\begingroup$

(MMA v11.1.1)

I have many empty fields in my CSV files and I couldn't understand why sometimes SemanticImport just failed to recognize the headers. I just find out that the problem is when the last column is ALL empty

Here is a minimal example:

(for the purpose i use SemanticImportString with a string, instead of SemanticImport with a file, but the results are the same)

SemanticImportString["a,b,c\n1,2,\n11,22,\n111,222,"]

enter image description here

whereas if just one element exists in the last column, all works fine:

SemanticImportString["a,b,c\n1,2,\n11,22,33\n111,222,"]

enter image description here

Trying to trick the process by specifying explicit delimiters like this produce an error:

SemanticImportString["a,b,c\n1,2,\n11,22,\n111,222,", Delimiters -> {",", ",\n"}]

enter image description here

The only workaround i found is adding an additionnal comma at the end of the lines:

SemanticImportString["a,b,c\n1,2,\n11,22,\n111,222," // 
  StringReplace[#, ",\n" -> ",,\n"] &]

enter image description here


In comparison Import (or ImportString) works fine:

ImportString["a,b,c\n1,2,\n11,22,\n111,222,"]

{{"a", "b", "c"}, {1, 2, ""}, {11, 22, ""}, {111, 222, ""}}

and Export (ExportString) of a table with an empty last column show that rows are just terminated with a comma, and that this format should be handled by mathematica:

ExportString[{{a, b, c}, {1, 2,}, {11, 22,}}, "CSV"] // InputForm

"a,b,c\n1,2,\n11,22,"


So, I would say it is a bug ...

Any other workaround than adding (programmatically or manually) an additional comma to all the problematic files ?

$\endgroup$
2
$\begingroup$
file = StringToStream["a,b,c\n1,2,\n11,22,\n111,222,"];
First@ImportString[#, "CSV"] & /@ ReadList[file, "String"]  // TableForm
Close[file];

enter image description here

edit: using SemanticImport on each line and making a dataset:

file = StringToStream["a,b,c\n1,2,\n11,22,\n111,222,"];
data = Normal@*SemanticImportString /@ ReadList[file, "String"];
{head, data} = Through@{First, Rest}@PadRight@data;
Close[file];
Dataset[Association[Rule @@@ Transpose[{head, #}]] & /@ data]

enter image description here

if you don't like zero fillers do this:

{head, data} = 
  Through@{First, Rest}@( 
    PadRight[#, Length@data[[1]], "empty"] & /@ data);
$\endgroup$
  • $\begingroup$ Thanks but at the end of my post I already gave a working example with ImportString, I know it works. Actually I do need the "Semantic" feature even if my minimal example do not show that, sorry if ithat was not clear. $\endgroup$ – SquareOne Jun 22 '17 at 17:04
  • $\begingroup$ you might take this and run SemanticImportString on each line as well. $\endgroup$ – george2079 Jun 22 '17 at 17:37
  • $\begingroup$ The problem is that If I run SemanticImportString on each line separately, it does not produce a Dataset of the whole data. $\endgroup$ – SquareOne Jun 22 '17 at 18:00
  • $\begingroup$ oh i see, see edit $\endgroup$ – george2079 Jun 22 '17 at 18:49
  • 1
    $\begingroup$ I just noticed this with some datasets we have here - the CSV files are written using some mythical python CSV library and each 'row' ends with a ,\n and not just \n. So I have a 'ghost column' without a label even. These Import OK but don't SemanticImport $\endgroup$ – flip Jul 6 '17 at 23:08
1
$\begingroup$

Here are two workarounds:

The first one is the one i gave already in the post and consists in adding an additional comma at the end of each line. This works but it probably should not and won't work when the problem is fixed:

file = StringToStream["a,b,c\n1,9:00,\n11,10:00,\n111,11:00,"];
Import[file, "Text"] // StringReplace[#, ",\n" -> ",,\n"] & // SemanticImportString
Close[file];

The second should be definitely more robust and consists in adding a given arbitrary text tag in the last column which will be explicitly recognized by SemanticImport as an empty field:

file = StringToStream["a,b,c\n1,9:00,\n11,10:00,\n111,11:00,"]; 
Import[file, "Text"] // 
StringReplace[#, ("," ~~ EndOfString | "\n") -> ",missing\n"] & // 
SemanticImportString[#,  MissingDataRules -> {"missing" -> Missing["Empty"]}] &
Close[file];

Both return:

enter image description here

where the b column data type was correctly recognized as a time object.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.