1
$\begingroup$

I have 200k pieces of data like this (only two elements shown), imported from a CSV file

{{2015, 11, 12, "17:04", 1.0811}, {2015, 11, 12, "17:05", 1.0811}}

and want to turn it into a list suitable for DateListPlot (I will only take small chunks once I have a plottable list], i.e. like

{{{2015, 11, 12, 17,04}, 1.0811}, {{2015, 11, 12, 17:05}, 1.0811}}

EDIT The first two lines of the CSV file are, verbatim,

Date_Time,Day,Month,Year,Date,Time,Open,Close,High,Low
12/11/2015 17:04,12,11,2015,12/11/2015,17:04,1.08110,1.08109,1.08110,1.08109

END EDIT

Having looked at Replace, and general List manipulation instructions I realise I don't know where to begin as I am still new to Mathematica. I have looked at related questions but none has seemed appropriate since I wish to replace and restructure at the same time.

I suppose I could simply build a new table and use DateValue to get the Hour and Minute from Part 4 of each sub list, but this seems heavy handed and I would also like to take the opportunity to learn more elegant/efficient methods of performing such list manipulations.

What would you recommend - and why?

[And - is there a way of forcing a list structure during the import so that the CVS fields for dates/time values are automatically combined into such a sublist?]

Improve this question
$\endgroup$
8
  • $\begingroup$ "is there a way of forcing a list structure" ... not sure I understand, but see second argument of SemanticImport (not Import). Is that what you want? $\endgroup$
    – Szabolcs
    Commented Oct 19, 2016 at 15:50
  • $\begingroup$ Indeed, that might deliver the end result I am looking for the overall import operation, but having come across the problem of changing a list as described I would like to know that answer too. Was going AFK when your comment came in so will have to check later. Thx. $\endgroup$
    – Julian Moore
    Commented Oct 19, 2016 at 15:54
  • $\begingroup$ You can get your desired list if you do: {#[[1 ;; 4]], Last[#]} & /@ list $\endgroup$
    – mgamer
    Commented Oct 19, 2016 at 16:34
  • $\begingroup$ If you can upload a small sample of your CSV file, I can look into how to semantically import it properly. $\endgroup$
    – dan7geo
    Commented Oct 19, 2016 at 17:43
  • $\begingroup$ @dan7geo I have added header and 1st line; now that my attention has been drawn to SemanticImport I could do the simple part of making one list per CSV row, it's structuring it as required for DateListPlot that would be most illuminating $\endgroup$
    – Julian Moore
    Commented Oct 19, 2016 at 19:41

1 Answer 1

Reset to default
3
$\begingroup$

To create a list of DateObjects and values you can first do this:

list = {DateObject@Join[#[[;; 3]], 
 ToExpression /@ StringSplit[#[[4]], ":"]], #[[5]]} & /@ data

Replace data with your data. ( /@ stands for Map and # & notation creates a pure function)

Then you can run the following to plot your data:

DateListPlot@list

EDIT:

To create a function that processes the list:

makePlottable := {DateObject@Join[#[[;; 3]], 
 ToExpression /@ StringSplit[#[[4]], ":"]], #[[5]]} & /@ # &

(This feature might be useful for further processing: https://reference.wolfram.com/language/ref/TimeSeries.html )

Improve this answer
$\endgroup$
7
  • $\begingroup$ I expect that works as promised, but having said I am still new to MMA in the question I'm afraid it's too terse for me, but my attempt to grok it is: DateObject (ah! {y, m, d..} or dateobj for DateListPlot) takes ymdhm(s) #[[;; 3]] picks Y M D, StringSplit must be getting h &m; all datetime stuff is then joined and becomes the DateObject and item 5 (the value) appended. # must be a placeholder for the current list item but how the #, & and /@ work together escapes me for now :( $\endgroup$
    – Julian Moore
    Commented Oct 20, 2016 at 8:12
  • $\begingroup$ Could you expand that expression as function calls so it looks like e.g. makePlottable[aList_List] := Map[... I tried and failed and this would help me understand & learn. $\endgroup$
    – Julian Moore
    Commented Oct 20, 2016 at 10:01
  • $\begingroup$ OK, I've got as far as makePlottable[aList_List] := {DateObject[Join[#[[;; 3]]], ToExpression [StringSplit[#[[4]], ":"]]], #[[5]]} & /@ aList but how do I replace the & /@ at the end? $\endgroup$
    – Julian Moore
    Commented Oct 20, 2016 at 10:29
  • $\begingroup$ See update to the answer with links explaining those things. $\endgroup$
    – dan7geo
    Commented Oct 21, 2016 at 4:07
  • $\begingroup$ :) Love the extra "# &" after the "& /@" I was already unclear about :) Where might I find such non-trivial examples explained step by step (or operator by operator) so I can decode for my still rather procedural brain? I've upvoted your answer but, since I did ask for the "why?" (meaning: how it works) I haven't accepted it (yet) $\endgroup$
    – Julian Moore
    Commented Oct 21, 2016 at 13:47

Your Answer

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

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