I'm running into an annoying issue when importing a table of tab-separated-data. Several columns are numeric, while several are strings. Using Import[url,"TSV"] basically works perfectly - however, some of the strings are "5d2", "4e1" or things of that nature, which Import then interprets as scientific notation. For instance, ImportString["4d2", "TSV"] yields {{400.}}, which I do not want. However there are some columns which are properly in scientific notation (i.e. 2.3e+02) which I do want intepreted as numbers - is there a clean way to selectively import certain table columns as numbers, leaving others as strings?

  • $\begingroup$ You can use ReadList instead and specify columns' types: ReadList["path to file here",{Word, Number}] for a file with "4d2" in first column and 2.3e+02 in the second. $\endgroup$ – Alx Oct 1 '19 at 2:04

Without knowing more, I would first say to look at using the "Numeric" -> False option in

Import["data.tsv", "TSV", "Numeric" -> False]

This seems (I've never worked with this functionality until now and I got the idea from here.) to leave everything as strings.

This also takes care of the misinterpretation of scientific notation problem as

ImportString[#, "TSV", "Numeric" -> False] & /@ {"4e1", "5d2"}


{{{"4e1"}}, {{"5d2"}}}

Then, once everything is imported as strings, you can change the columns of scientific notation strings to numbers. For example,

data[[;;, column]] = Internal`StringToDouble /@ data[[;;, column]]

(Also stole the Internal`StringToDouble from here.)

All together

data = Import["data.tsv", "TSV", "Numeric" -> False]
data[[-1]] = Internal`StringToDouble /@ data[[-1]];

{{"1"}, {"2"}, {"3"}, {"4"}, {"5"}, {"6"}, {"7"}, {"8"}, {"9"}, {"10"}, {"4e1"}, {"5d2"}, {"2.3e+02"}}

{{"1"}, {"2"}, {"3"}, {"4"}, {"5"}, {"6"}, {"7"}, {"8"}, {"9"}, {"10"}, {"4e1"}, {"5d2"}, {230.}}

| improve this answer | |

Suppose your file is like this (two different types of columns for simplicity):

"4d2"    2.3e+02
"5e1"    -1.3e-05

I use here StringToStream to simulate file, but you just place your file (path + name) instead (ReadList["path to file",{Word, Number}]) with appropriate number and types of columns you have:

ReadList[StringToStream["\"4d2\"\t2.3e+02\n\"5e1\"\t-1.3e-05"], {Word, Number}]

which gives

{{"4d2", 230.}, {"5e1", -0.000013}}
| improve this answer | |

If your file.tsv has data such as:

"4d2" 2.3e+02 105.5

"5e1" -1.3e05 235

Then SemanticImport may help:

data = SemanticImport["file.tsv",{"String","Number","Number"}, "HeaderLines"-> 0]

Set HeaderLines appropriately to reflect the presence of header row(s) in your file.

| improve this answer | |

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.