I have a files with some precessed data for different locations and the data is organized by year, month hour - the hhour is either 00 or 12 UTC and I would like to split those data by year,month and time (00 or 12). I have tried some past posts on the subject but it has not worked. I have attached a xlxs file.


  • $\begingroup$ Does data=SemanticImport["PBLHs.xlsx"] and data[GroupBy[#["Day Hour(12 UTC)"]["Year"] &]] work for you? $\endgroup$ – swish Jun 4 at 17:11
  • $\begingroup$ It seems to do the trick, but how to plot those ? In as mentioned to #flinty this file has 3 separate sheets, corresponding to different locations. So applying this "sorting" on each sheets is easier to plot later.What was really bugging is the hour reference some subsets have, namely 00 and 1 which your code and his split apart. $\endgroup$ – user10737 Jun 5 at 0:36
data = Import["PBLHs.xlsx"];
sheet1 = Rest@data[[1]];(*Rest removes the header row *)
GroupBy[sheet1, DateValue[ #[[1]], {"Year", "Month", "Hour24"}] &]
(* result: an association grouping rows with the same Year, Month, and 24 hour time. *)
| improve this answer | |
  • $\begingroup$ Thank you very much. Since the excel file has 3 different sheets, how to handle that ? Apparently , data = Import["PBLHs.xlsx",{"Data,2"]; seems not doing the job. $\endgroup$ – user10737 Jun 4 at 18:20
  • $\begingroup$ Change sheet1=Rest@data[[1]] to Flatten[data[[All, 2 ;;]], 1] to combine the sheets. The [[All, 2;;]] part skips the header row on each sheet. $\endgroup$ – flinty Jun 4 at 19:49
  • $\begingroup$ It worked finely. Thx $\endgroup$ – user10737 Jun 4 at 23:13

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.