2
$\begingroup$

I have Excel file consisting of two columns such as:

1.2.2014 23:35  |-0.007378

1.2.2014 23:40  |-0.004121

1.2.2014 23:45  |-0.000122

1.2.2014 23:50  |0.000117

1.2.2014 23:55  |-0.000542

When I import that in Mathematica, it creates list like:

List={{1.2.2014 23:35, -0.007378}, {1.2.2014 23:40, -0.004121}, ..., {2.2.2014 00:00, -0.004897}, {2.2.2014 00:05, -0.005179}, ...}

And I need to create a list where only the values from the second column will be and these values will create sublists based on the day of observations. So it should look like (based on "List" above):

List2={{-0.007378, -0.004121, ...}, {-0.004897, -0.005179, ...}, ...}.

The question may be very similar to this one: Split dataset based on the first column ...but I cannot figure out how the mentioned method should be applied in my case, where I have dates in the first column.

Thanks for any help. And sorry if it is an easy problem, but I am still begginer with Mathematica.

Improve this question
$\endgroup$
2
  • $\begingroup$ Johny, please include the exact expression returned by Import, or at least a truncated version of it. Some or all of the data is probably in String form, though this is not shown in your current excerpt. This will change how the data is handled. $\endgroup$ – Mr.Wizard Mar 19 '15 at 13:12
  • $\begingroup$ try list=Import[(*your file*)]; Partition[list[[All,2]],2] $\endgroup$ – k_v Mar 19 '15 at 13:29
0
$\begingroup$

Starting with:

data = Import["btc.xlsx"] // First;

your time stamps are being imported in the DateObject format:

data[[1, 1]] // InputForm

DateObject[{2011, 12, 31}, TimeObject[{0, 5, 0.}]]

The first argument is a Y/M/D triplet that we can Extract for use in GatherBy or GroupBy:

GatherBy[data, Extract[{1, 1}]][[All, All, 2]]

Or:

GroupBy[data, Extract[{1, 1}] -> Last] // Values

The operator form of Extract is used in each example.

Improve this answer
$\endgroup$
0
1
$\begingroup$

If I understood rightly, a starting point could be:

dateString[date_] := DateString[date, {"Year", "Month", "Day"}];
dString[arg_] := dateString[First[arg]];
dataOut = Gather[dataIn, (dString[#1] == dString[#2]) &];

If you wish, you can provide us a plain text file version of your data (es. Dropbox ...) and I will try to help further.

Improve this answer
$\endgroup$
1
  • $\begingroup$ Please forgive, for security reasons, I can't open a xlsx file on this machine. Can you save data as plain txt (xml, csv, html ...) and zip it ? I would be pleased to help, this way. $\endgroup$ – mitochondrial Mar 21 '15 at 13:40
1
$\begingroup$ 
list= {{"1.2.2014 23:35  ", -0.007378`}, {"1.3.2014 23:40  ", -0.004121`},
       {"1.2.2014 23:45  ", -0.000122`}, {"1.3.2014 23:50  ",  0.000117`}, 
       {"1.2.2014 23:55  ", -0.000542`}};

list2 = {DateList[{StringTrim@#, 
         {"Month", "Day", "Year", "Hour", "Minute"}}][[;; 3]], #2} & @@@ list;

Values@GroupBy[ First -> Last][list2]
(* or Values@GroupBy[list2, First -> Last] *)
(* {{-0.007378,-0.000122,-0.000542},{-0.004121,0.000117}} *)
Improve this answer
$\endgroup$
1
  • $\begingroup$ @Johny, with the data you posted, you don't need to process the first column to get datelists. With list=Import[...], you can try Last/@#&/@(Values@GroupBy[ #[[1,;;3]] &][list]). $\endgroup$ – kglr Mar 19 '15 at 23:52

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.