# Creating sublists based on the first column

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.

• 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. – Mr.Wizard Mar 19 '15 at 13:12
• try list=Import[(*your file*)]; Partition[list[[All,2]],2] – k_v Mar 19 '15 at 13:29

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.

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.

• 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. – mitochondrial Mar 21 '15 at 13:40
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}} *)

• @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])`. – kglr Mar 19 '15 at 23:52