1
$\begingroup$

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

Improve this question
$\endgroup$
4
  • 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, 2019 at 17:46
  • 1
    $\begingroup$ Please post a sampe of your dataset. $\endgroup$
    – Alexey Popkov
    Dec 11, 2019 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, 2019 at 7:54
  • $\begingroup$ Thanks, I'll check that out. $\endgroup$
    – BruceR
    Dec 14, 2019 at 15:03

2 Answers 2

Reset to default
1
$\begingroup$

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.

Improve this answer
$\endgroup$
4
  • $\begingroup$ Let me try that. $\endgroup$
    – BruceR
    Dec 16, 2019 at 14:26
  • $\begingroup$ Tell me,,, what does "&/@ Normal[q] do in the above expression? $\endgroup$
    – BruceR
    Dec 19, 2019 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, 2019 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, 2019 at 17:58
1
$\begingroup$

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}}];

z=q["Path']

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.

Improve this answer
$\endgroup$
2
  • $\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, 2019 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, 2019 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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