I have been looking at wind speed data from various locations. And I'd like to export this to excel. That part works. But the data are difficult to work with in excel. This is what the raw export looks like.

Here's what the initial query looks like in Mathematica. enter image description here

I'm thinking there must be some modifier within Mathematica that allows me to export the text and numbers in a row and column format that is more typical (Column A Date, Column B Time, Column C wind speed). I tried the DATASET, and that folds the large file together, but still with only a dozen rows and thousands of columns of information mashed together.

enter image description here All the data are on a single row. I have to copy, paste, transpose to get the information to look something like the image below. Also, if its a large data set, XLS/CSV format only accepts a limited number of columns. There must be a switch somewhere in Mathematica that lets me export a more workable format.

Ultimately, this is what I want to see, and work with:

enter image description here

  • 2
    $\begingroup$ Is there any chance you could edit your post to include the FullForm[...yourdata...] for a handful of rows in Mathematica and the line of code you are using to Export[...yourdata...] ? That would let readers see what kind of data you actually have and try possible fixes. It is so easy to assume that the readers here have been sitting next to you and have seen everything that you have seen, but that isn't the case. $\endgroup$ – Bill Oct 30 '19 at 17:46
  • 1
    $\begingroup$ Please post a sampe of your dataset. $\endgroup$ – Alexey Popkov Dec 11 '19 at 5:10
  • $\begingroup$ Have you represented your data as a Dataset? I've had good luck exporting straightforward datasets to csv and successfully opened them in Excel. If you are unfamiliar with Dataset, look it up in the Wolfram documentation - the documentation provides far better examples than I could show. $\endgroup$ – Mark R Dec 13 '19 at 7:54
  • $\begingroup$ Thanks, I'll check that out. $\endgroup$ – BruceR Dec 14 '19 at 15:03

This worked for me:

q = WindSpeedData[Entity["City", {"DesMoines", "Iowa", "UnitedStates"}],
{{2018, 1, 1}, {2018, 12, 31}}];
q2 = Dataset[Association[{"date" -> DateString[#[[1]]], 
  "wind speed (mph)" -> QuantityMagnitude[#[[2]]]}] & /@ Normal[q]];
Export["~/Documents/WindspeedTest.csv", q2]

I couldn't see what units you wanted and this defaulted to mph, which is why I used that for the units. Change how you'd like.

  • $\begingroup$ Let me try that. $\endgroup$ – BruceR Dec 16 '19 at 14:26
  • $\begingroup$ Tell me,,, what does "&/@ Normal[q] do in the above expression? $\endgroup$ – BruceR Dec 19 '19 at 15:19
  • $\begingroup$ I tried Mark's solution. It did not work for me. I wonder, is it possible that Dataset doesn't function that way on Mathematica 10.3? Perhaps I need a higher/newer version. $\endgroup$ – BruceR Dec 19 '19 at 15:24
  • $\begingroup$ On my version, "q" is TemporalData (a TimeSeries) and I needed it as a pair of entries. On your system, evaluate q and look at it and then do something like "Take[Normal[q], 3]" and you'll see the difference. Regarding versions, I am using v12 and don't have the older version installed. At the bottom of the documentation, it says it was introduced in 2014 in v10.0. If you remove the "Dataset" call, does the Association work for you? $\endgroup$ – Mark R Dec 19 '19 at 17:58

Well, it seems like knowing all the language details of Mathematica would help. If I take the windspeed data and do something like this. a=WindSpeedData[Entity["City", {"DesMoines", "Iowa", "UnitedStates"}], {{2018, 1, 1}, {2018, 12, 31}}];


Then export z, I get fairly close to what I was looking for. I at least get columns of dates (in seconds, absolute) and data to work with. That terminology, "Path", seems like a very odd duck.

  • $\begingroup$ Agreed. The following might get you closer to what you want: z = q["Path"]; z[[All, 2]] = QuantityMagnitude[z[[All, 2]]]; z[[All, 1]] = FromAbsoluteTime[#] & /@ z[[All, 1]]; Export["windspeed.xlsx", "Windspeed" -> Join[{{"DateTime", "Windspeed"}}, z]]. $\endgroup$ – JimB Dec 16 '19 at 16:30
  • $\begingroup$ The use of "xlsx" rather than "xls" might get around any of the number of rows and columns limitations. (65,536 rows and 256 columns for xls and 1,048,576 rows and 16,384 columns for xlsx.) $\endgroup$ – JimB Dec 16 '19 at 17:01

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.