0
$\begingroup$

I need to prepare a dataset, which will be much easier using e.g. Excel. Then I want to import and use it in Mathematica. One row in the dataset would look like this in Mathematica:

{"A2",{"a2","b3"},"K6",{"a6","b2"}}

When doing this in Excel, it would look like this.

enter image description here

However, importing this into Mathematica produces:

{"A2","{"a2","b3"}","K6","{"a6","b2"}"}

The problem is the curly brackets, which get some extra quotation mark. I need these curly brackets in several columns. Using a combination of ToExpression and then back to ToString becomes very clumsy, in particular as during such a procedure a2 gets a space in between etc. Ideally, I'd like to import it as typed in Excel. I tried various cell formats in Excel without success. I also could not find a way in Mathematica for a plain import. Even exporting and then importing it as .csv doesn't get rid of this.

Improve this question
$\endgroup$
2
  • $\begingroup$ I wonder if it wouldn't actually be much more expedient to generate the dataset in Mathematica directly. Is there an algorithmic rule that generates each row? $\endgroup$
    – MarcoB
    May 14 '18 at 20:48
  • $\begingroup$ What is the final output you're expecting? Something like this? {"A2",{"a2","b3"},"K6",{"a6","b2"}} $\endgroup$
    – GenericAccountName
    May 15 '18 at 7:14
2
$\begingroup$

As indicated in the comments, when importing the xlsx file you get rather something like :

xsl={{{"A2", "{a2,a3}", "K6", "{a6,b2}"}}};

and basically what you need is only to transform strings like "{a2,a3}" to a list of strings {"a2","a3"}.

String manipulation functions can help. Here is a solution:

xsl /. x_String?(StringMatchQ["{*}"]) :> StringSplit[StringTake[x, {2, -2}], ","]

{{{"A2", {"a2", "a3"}, "K6", {"a6", "b2"}}}}

Improve this answer
$\endgroup$
3
  • $\begingroup$ Great! However ... I was wondering if this is also possible within a dataset. Say, there are many xs1, then xs2, then xs3, ... Then two columns contain "{a2,a3}" in each row. Now I'd like to apply your function to the entire column, so that in the dataset the column contains the "{a2,a3}" correctly. I tried a few things from the 'dataset' help menu, but was not successful. $\endgroup$
    – Mockup Dungeon
    May 16 '18 at 8:00
  • $\begingroup$ @MockupDungeon Minimal example: ds = Dataset[{ <|"a" -> 1, "b" -> "x", "c" -> "{1}"|>, <|"a" -> 2, "b" -> "y", "c" -> "{2,3}"|>}]; ds[All, {"c" -> (# /. x_String?(StringMatchQ["{*}"]) :> StringSplit[StringTake[x, {2, -2}], ","] &)}]. The "c" column has been transformed. Read the Dataset docs. $\endgroup$
    – SquareOne
    May 16 '18 at 8:50
  • $\begingroup$ Thanks! Tried something very similar, but made some minor mistake. $\endgroup$
    – Mockup Dungeon
    May 16 '18 at 9:48
0
$\begingroup$

Using a .xlsx file with the data pictured below:

data = Import["thedata.xlsx"];
Flatten[data,2]

Should produce:

{A2, {a2,b3}, K6, {a6,b2}}

Are you not getting a similar result?

Excel data

Improve this answer
$\endgroup$
5
  • 1
    $\begingroup$ You are being tricked by the way strings are shown in the output. Try Flatten[data, 2] // InputForm to see what is really being imported, which is equivalent to the problem in the OP. $\endgroup$
    – MarcoB
    May 14 '18 at 20:44
  • $\begingroup$ It is tricky, but I get something slightly different from the OP: {"A2", "{a2,b3}", "K6", "{a6,b2}"}, I don't see quotes around the elements of the inner lists. $\endgroup$
    – Lee
    May 14 '18 at 21:03
  • $\begingroup$ I agree with you on the last point. I suspect that the OP has misplaced their quotes when copy/pasting, since the pattern they indicated is unlikely. Nevertheless, what you get is still not what they want, unfortunately. $\endgroup$
    – MarcoB
    May 14 '18 at 21:48
  • $\begingroup$ @Lee it just looks right but unfortunately isn't, as will be seen when executing the output. I probably have the quotes because of a different input format in Excel. The main problem is the quotation marks around the entire cell content (e.g. "{a2,b3}") which I cannot get rid of. $\endgroup$
    – Mockup Dungeon
    May 15 '18 at 5:25
  • $\begingroup$ @MockupDungeon Yes, I learned that after following the suggestion from MarcoB. I tried many, many different methods with Import and SemanticImport, but nothing imports the lists properly. $\endgroup$
    – Lee
    May 15 '18 at 10:43

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.