Examples

Each example below is a complete program from the clojask-examples repository, shown without its ns form. The input and output files are included, so you can follow along without running anything.

Run them yourself. Clone the repository, set :main in project.clj to the example you want (for instance clojask-examples.basic-tutorial/-main), and run lein run. The results land in outputs/. lein test runs every example and checks its outputs.

git clone https://github.com/clojure-finance/clojask-examples
cd clojask-examples
lein run

All examples use ck for clojask.dataframe. The other aliases they use are gb-agg (clojask.api.gb-aggregate), agg (clojask.api.aggregate), input (clojask-io.input) and ds (tech.ml.dataset).

Basic tutorial

Set a column type, filter rows and update a column, then write the result. The same steps also run on a TSV file, written with the -> macro, and through clojask-io's read-file.

Source: basic_tutorial.clj · run with :main clojask-examples.basic-tutorial/-main

(defn -main
  []
  ;; The simple way
  (def df (ck/dataframe "resources/employees.csv"))
  (ck/print-df df)
  (ck/set-type df "Salary" "double")
  (ck/filter df "Salary" (fn [salary] (<= salary 800)))
  (ck/operate df (fn [salary] (* salary 1.2)) "Salary")
  ;; the output keeps the input's row order
  (ck/compute df 8 "outputs/basic.csv")

  ;; Using the `->` macro:
  ;; tsv dataset
  (-> (ck/dataframe "resources/cats.tsv")
      (ck/set-type "Weight(kg)" "double")
      (ck/filter "Weight(kg)" (fn [weight] (<= weight 5)))
      (ck/compute 8 "outputs/basic3.csv"))

  ;; or use the clojask-io plugin
  (-> (ck/dataframe (fn [] (read-file "resources/cats.tsv" :sep "\t" :stat true :output true)))
      (ck/set-type "Weight(kg)" "double")
      (ck/filter "Weight(kg)" (fn [weight] (<= weight 5)))
      (ck/compute 8 "outputs/basic4.tsv")))

Employee,EmployeeName,Department,Salary,UpdateDate
1,Alice,11,300,2020/12/12
2,Bob,11,600,2020/12/01
3,Carla,12,900,2020/12/03
4,Daniel,12,1000,2020/12/05
5,Evelyn,13,800,2020/12/03
6,Ferdinand,21,700,2020/12/05
7,Amy,11,50000,2020/11/26

Cat's name	Weight(kg)
Lily	2.3
George	7
Meow	3.2

Employee,EmployeeName,Department,Salary,UpdateDate
1,Alice,11,360.0,2020/12/12
2,Bob,11,720.0,2020/12/01
5,Evelyn,13,960.0,2020/12/03
6,Ferdinand,21,840.0,2020/12/05

Cat's name,Weight(kg)
Lily,2.3
Meow,3.2

Cat's name	Weight(kg)
Lily	2.3
Meow	3.2

Group-by and aggregate

Aggregate within each department after group-by, then aggregate the whole dataframe without grouping. Note the different function namespaces: gb-agg for groups, agg for the whole dataframe.

Source: groupby_aggregate.clj · run with :main clojask-examples.groupby-aggregate/-main

(defn -main
  []
  ;; groupby aggregate
  (def df (ck/dataframe "resources/employees.csv"))
  (ck/set-type df "UpdateDate" "date:yyyy/MM/dd")
  (ck/set-type df "Salary" "int")
  (ck/group-by df "Department")
  (ck/aggregate df gb-agg/max "UpdateDate")
  ;; can aggregate on multiple columns in a group; the last argument names the
  ;; output column (the default name comes from the function, which for an
  ;; anonymous function is something like _main$fn__2093(Salary))
  (ck/aggregate df #(reduce + %) "Salary" "sum(Salary)")
  (ck/compute df 8 "outputs/gb-agg.csv")

  ;; direct aggregate
  (def df (ck/dataframe "resources/employees.csv"))
  (ck/set-type df "UpdateDate" "date:yyyy/MM/dd")
  (ck/set-type df "Salary" "int")
  (ck/aggregate df agg/max "UpdateDate")
  (ck/aggregate df agg/min "UpdateDate") ;; can aggregate on multiple columns
  (ck/compute df 8 "outputs/agg.csv"))

Employee,EmployeeName,Department,Salary,UpdateDate
1,Alice,11,300,2020/12/12
2,Bob,11,600,2020/12/01
3,Carla,12,900,2020/12/03
4,Daniel,12,1000,2020/12/05
5,Evelyn,13,800,2020/12/03
6,Ferdinand,21,700,2020/12/05
7,Amy,11,50000,2020/11/26

Department,max(UpdateDate),sum(Salary)
12,2020/12/05,1900
21,2020/12/05,700
11,2020/12/12,50900
13,2020/12/03,800

max(UpdateDate),min(UpdateDate)
Sat Dec 12 00:00:00 HKT 2020,Thu Nov 26 00:00:00 HKT 2020

Inner, left and right join

Join the employee table with a table of leave days on the Employee column. Columns from the left dataframe get the prefix 1_, those from the right 2_.

Source: ordinary_join.clj · run with :main clojask-examples.ordinary-join/-main

(defn -main
  []
  (def x (ck/dataframe "resources/employees.csv"))
  (def y (ck/dataframe "resources/employees-workleave.csv"))
  (ck/set-type x "Employee" "int")
  (ck/set-type y "Employee" "int")
  (def z (ck/inner-join x y ["Employee"] ["Employee"]))
  (ck/print-df z)
  (ck/compute z 8 "outputs/inner-join.csv" :select ["1_Employee" "1_Salary" "2_WorkLeave"])
  (ck/compute (ck/left-join x y ["Employee"] ["Employee"]) 8 "outputs/left-join.csv" :exception false)
  (ck/compute (ck/right-join x y ["Employee"] ["Employee"]) 8 "outputs/right-join.csv" :exception false))

Employee,EmployeeName,Department,Salary,UpdateDate
1,Alice,11,300,2020/12/12
2,Bob,11,600,2020/12/01
3,Carla,12,900,2020/12/03
4,Daniel,12,1000,2020/12/05
5,Evelyn,13,800,2020/12/03
6,Ferdinand,21,700,2020/12/05
7,Amy,11,50000,2020/11/26

Employee,EmployeeName,WorkLeave,UpdateDate
1,Alice,20,2020/12/10
2,Bob,15,2020/12/05
3,Carla,5,2020/12/03
7,Angel,30,2020/12/11
8,Leo,100,2020/03/23

1_Employee,1_Salary,2_WorkLeave
1,300,20
2,600,15
3,900,5
7,50000,30

1_Employee,1_EmployeeName,1_Department,1_Salary,1_UpdateDate,2_Employee,2_EmployeeName,2_WorkLeave,2_UpdateDate
1,Alice,11,300,2020/12/12,1,Alice,20,2020/12/10
2,Bob,11,600,2020/12/01,2,Bob,15,2020/12/05
3,Carla,12,900,2020/12/03,3,Carla,5,2020/12/03
4,Daniel,12,1000,2020/12/05,,,,
5,Evelyn,13,800,2020/12/03,,,,
6,Ferdinand,21,700,2020/12/05,,,,
7,Amy,11,50000,2020/11/26,7,Angel,30,2020/12/11

2_Employee,2_EmployeeName,2_WorkLeave,2_UpdateDate,1_Employee,1_EmployeeName,1_Department,1_Salary,1_UpdateDate
1,Alice,20,2020/12/10,1,Alice,11,300,2020/12/12
2,Bob,15,2020/12/05,2,Bob,11,600,2020/12/01
3,Carla,5,2020/12/03,3,Carla,12,900,2020/12/03
7,Angel,30,2020/12/11,7,Amy,11,50000,2020/11/26
8,Leo,100,2020/03/23,,,,,

Outer join

Keep the rows of both tables, matched or not. A row found in only one table has empty columns for the other.

Source: outer_join.clj · run with :main clojask-examples.outer-join/-main

(defn -main
  []
  (def x (ck/dataframe "resources/employees.csv"))
  (def y (ck/dataframe "resources/employees-workleave.csv"))
  (ck/set-type x "Employee" "int")
  (ck/set-type y "Employee" "int")
  (def z (ck/outer-join x y ["Employee"] ["Employee"]))
  (ck/print-df z)
  ;; rows found only in y have empty 1_ columns, so keep the 2_ key columns:
  ;; for those rows 2_Employee is the only place their Employee appears
  (ck/compute z 8 "outputs/outer-join.csv"))

Employee,EmployeeName,Department,Salary,UpdateDate
1,Alice,11,300,2020/12/12
2,Bob,11,600,2020/12/01
3,Carla,12,900,2020/12/03
4,Daniel,12,1000,2020/12/05
5,Evelyn,13,800,2020/12/03
6,Ferdinand,21,700,2020/12/05
7,Amy,11,50000,2020/11/26

Employee,EmployeeName,WorkLeave,UpdateDate
1,Alice,20,2020/12/10
2,Bob,15,2020/12/05
3,Carla,5,2020/12/03
7,Angel,30,2020/12/11
8,Leo,100,2020/03/23

1_Employee,1_EmployeeName,1_Department,1_Salary,1_UpdateDate,2_Employee,2_EmployeeName,2_WorkLeave,2_UpdateDate
2,Bob,11,600,2020/12/01,2,Bob,15,2020/12/05
4,Daniel,12,1000,2020/12/05,,,,
7,Amy,11,50000,2020/11/26,7,Angel,30,2020/12/11
1,Alice,11,300,2020/12/12,1,Alice,20,2020/12/10
3,Carla,12,900,2020/12/03,3,Carla,5,2020/12/03
6,Ferdinand,21,700,2020/12/05,,,,
5,Evelyn,13,800,2020/12/03,,,,
,,,,,8,Leo,100,2020/03/23

Rolling join

Match each employee record with the leave record of the nearest date: the latest one not after it (forward) or the earliest one not before it (backward), optionally only within 7 days (:limit).

Source: rolling_join.clj · run with :main clojask-examples.rolling-join/-main

(defn within-days
  "A :limit for rolling joins: join two rows only when their roll values
   (here dates) are at most n days apart."
  [n]
  (fn [a b]
    (<= (Math/abs (- (.getTime a) (.getTime b)))
        (* n 24 60 60 1000))))

(defn -main
  []
  (def x (ck/dataframe "resources/employees.csv"))
  (def y (ck/dataframe "resources/employees-workleave.csv"))

  (ck/set-type x "UpdateDate" "date:yyyy/MM/dd")
  (ck/set-type y "UpdateDate" "date:yyyy/MM/dd")

  ;; forward: each row of x gets the row of y with the same Employee and
  ;; the latest UpdateDate that is not after x's
  (ck/compute (ck/rolling-join-forward x y ["Employee"] ["Employee"] "UpdateDate" "UpdateDate") 8 "outputs/rolling-forward.csv")

  ;; backward: the earliest UpdateDate that is not before x's
  (ck/compute (ck/rolling-join-backward x y ["Employee"] ["Employee"] "UpdateDate" "UpdateDate") 8 "outputs/rolling-backward.csv")

  ;; threshold: :limit is called on the two roll values and skips the join
  ;; when it returns false; here rows more than 7 days apart stay unjoined
  (ck/compute (ck/rolling-join-backward x y ["Employee"] ["Employee"] "UpdateDate" "UpdateDate" :limit (within-days 7)) 8 "outputs/rolling-backward-7days.csv"))

Employee,EmployeeName,Department,Salary,UpdateDate
1,Alice,11,300,2020/12/12
2,Bob,11,600,2020/12/01
3,Carla,12,900,2020/12/03
4,Daniel,12,1000,2020/12/05
5,Evelyn,13,800,2020/12/03
6,Ferdinand,21,700,2020/12/05
7,Amy,11,50000,2020/11/26

Employee,EmployeeName,WorkLeave,UpdateDate
1,Alice,20,2020/12/10
2,Bob,15,2020/12/05
3,Carla,5,2020/12/03
7,Angel,30,2020/12/11
8,Leo,100,2020/03/23

1_Employee,1_EmployeeName,1_Department,1_Salary,1_UpdateDate,2_Employee,2_EmployeeName,2_WorkLeave,2_UpdateDate
1,Alice,11,300,2020/12/12,1,Alice,20,2020/12/10
2,Bob,11,600,2020/12/01,,,,
3,Carla,12,900,2020/12/03,3,Carla,5,2020/12/03
4,Daniel,12,1000,2020/12/05,,,,
5,Evelyn,13,800,2020/12/03,,,,
6,Ferdinand,21,700,2020/12/05,,,,
7,Amy,11,50000,2020/11/26,,,,

1_Employee,1_EmployeeName,1_Department,1_Salary,1_UpdateDate,2_Employee,2_EmployeeName,2_WorkLeave,2_UpdateDate
1,Alice,11,300,2020/12/12,,,,
2,Bob,11,600,2020/12/01,2,Bob,15,2020/12/05
3,Carla,12,900,2020/12/03,3,Carla,5,2020/12/03
4,Daniel,12,1000,2020/12/05,,,,
5,Evelyn,13,800,2020/12/03,,,,
6,Ferdinand,21,700,2020/12/05,,,,
7,Amy,11,50000,2020/11/26,7,Angel,30,2020/12/11

1_Employee,1_EmployeeName,1_Department,1_Salary,1_UpdateDate,2_Employee,2_EmployeeName,2_WorkLeave,2_UpdateDate
1,Alice,11,300,2020/12/12,,,,
2,Bob,11,600,2020/12/01,2,Bob,15,2020/12/05
3,Carla,12,900,2020/12/03,3,Carla,5,2020/12/03
4,Daniel,12,1000,2020/12/05,,,,
5,Evelyn,13,800,2020/12/03,,,,
6,Ferdinand,21,700,2020/12/05,,,,
7,Amy,11,50000,2020/11/26,,,,

Results in memory, Excel in and out

Read an Excel sheet with clojask-io's read-excel, keep an intermediate result in memory (compute with output path nil), feed it into a join, and write the final result to an .xlsx file.

Source: in_memory.clj · run with :main clojask-examples.in-memory/-main

(defn time-diff
  [start end]
  (/ (- (.getTime end) (.getTime start)) 60000))

;; With output path nil, compute keeps the result in memory and returns the
;; rows (header first) as formatted strings, which can feed another
;; dataframe. The last compute writes an Excel file: compute picks the
;; output format from the file's extension.

(defn -main
  []
  (def df (ck/dataframe (fn [] (input/read-excel "resources/course timetable.xlsx" "Sheet1" :stat true))))
  (ck/filter df "THU" #(not= % nil))
  (ck/set-type df "START TIME" "datetime:HH:mm")
  (ck/set-type df "END TIME" "datetime:HH:mm")
  (ck/operate df time-diff ["START TIME" "END TIME"] "diff")
  (let [groupby-key ["COURSE CODE" "CLASS SECTION" "START DATE"]]
    (ck/group-by df groupby-key)
    (ck/aggregate df #(reduce + %) "diff" "MINS/WEEK")
    (def res (ck/compute df 8 nil))
    (def res-df (ck/dataframe (fn [] res)))
    ;; reinitialize df
    (def df (ck/dataframe (fn [] (input/read-excel "resources/course timetable.xlsx" "Sheet1" :stat true))))
    (def join-df (ck/right-join df res-df groupby-key groupby-key))
    (ck/compute join-df 8 "outputs/in-mem.xlsx" :select ["1_COURSE CODE" "1_TERM" "1_START DATE" "1_END DATE" "1_START TIME" "1_END TIME" "2_MINS/WEEK"])))

Converting to and from tech.ml.dataset

Turn a tech.ml.dataset dataset into a Clojask dataframe, and a Clojask result back into a dataset.

Source: techmldataset.clj · run with :main clojask-examples.techmldataset/-main

(defn get-from
  [list keys]
  (map #(get list %) keys))

(defn local-date-formatter
  [date]
  (let [formatter (DateTimeFormatter/ofPattern "yyyy/MM/dd")]
    (.format date formatter)))

(defn -main
  []
  ;; define a tech.ml.dataset
  (def df (ds/->dataset "resources/employees.csv"))
  
  ;; transform it into a Clojask dataset
  (def clojask-df (ck/dataframe (fn []
                                  (let [headers (keys (first (ds/mapseq-reader (ds/head df 1))))]
                                    (conj (map #(get-from % headers) (ds/mapseq-reader df)) headers)))))
  (ck/print-df clojask-df)
  (ck/set-formatter clojask-df "UpdateDate" local-date-formatter)
  ;; You may find that UpdateDate has type java.time.LocalDate.
  ;; Before grouping or joining or clojask-df, transform it to java.util.Date
  
  ;; transform the results of Clojask to tech.ml.dataset
  (def result (ck/compute clojask-df 8 nil))
  (println result)
  (def df2 (ds/->dataset (map zipmap (repeat (first result)) (rest result))))
  ;; df2's columns are all strings, you may convert them if needed
  
  ;; More details about tech.ml.dataset can be found at
  ;; https://github.com/techascent/tech.ml.dataset
  )

Employee,EmployeeName,Department,Salary,UpdateDate
1,Alice,11,300,2020/12/12
2,Bob,11,600,2020/12/01
3,Carla,12,900,2020/12/03
4,Daniel,12,1000,2020/12/05
5,Evelyn,13,800,2020/12/03
6,Ferdinand,21,700,2020/12/05
7,Amy,11,50000,2020/11/26

Reshaping: melt, dcast, rbind

Reshape a wide table to long with melt and back with dcast, and stack files with rbind-csv (see Extensions).

Source: enhanced_reshape.clj · run with :main clojask-examples.enhanced-reshape/-main

;; find more explicit explanation here
;; https://cran.r-project.org/web/packages/data.table/vignettes/datatable-reshape.html#enhanced-new-functionality
(defn -main
  []
  ;; enhanced melt
  (def x (ck/dataframe "resources/melt.csv"))
  ;; some operations to x
  (ck/rename-col x "dob_child1" "child1")
  (ck/rename-col x "dob_child2" "child2")
  (ck/rename-col x "dob_child3" "child3")
  (melt x "outputs/1.csv" ["family_id" "age_mother"] ["child1" "child2" "child3"] :measure-name "child" :value-name "dob")
  ;; x and y are from the same source
  (def y (ck/dataframe "resources/melt.csv"))
  (ck/rename-col y "gender_child1" "child1")
  (ck/rename-col y "gender_child2" "child2")
  (ck/rename-col y "gender_child3" "child3")
  (melt y "outputs/2.csv" ["family_id" "age_mother"] ["child1" "child2" "child3"] :measure-name "child" :value-name "gender")
  (def z (cbind-csv "outputs/1.csv" "outputs/2.csv"))
  ;; you can rename the column names of z here
  ;; skipped
  (ck/compute z 8 "outputs/melt_result.csv" :select ["family_id1" "age_mother1" "child1" "dob" "gender"])

  ;; rbind: stack csv files that have the same columns; the header comes
  ;; from the first file. Split the long table by child, then stack it back.
  (def first-child (ck/dataframe "resources/dcast.csv"))
  (ck/filter first-child "child" #(= % "1"))
  (ck/compute first-child 8 "outputs/first_child.csv")
  (def other-children (ck/dataframe "resources/dcast.csv"))
  (ck/filter other-children "child" #(not= % "1"))
  (ck/compute other-children 8 "outputs/other_children.csv")
  (ck/compute (rbind-csv "outputs/first_child.csv" "outputs/other_children.csv") 8 "outputs/rbind_result.csv")

  ;; enhanced dcast
  (def a (ck/dataframe "resources/dcast.csv"))
  (def dob (dcast a "outputs/1.csv" ["family_id" "age_mother"] "child" "dob" ["1" "2" "3"] :vals-name ["child1" "child2" "child3"]))
  (def b (ck/dataframe "resources/dcast.csv"))
  (def gender (dcast b "outputs/2.csv" ["family_id" "age_mother"] "child" "gender" ["1" "2" "3"] :vals-name ["child1" "child2" "child3"]))
  (def res (ck/inner-join dob gender ["family_id" "age_mother"] ["family_id" "age_mother"] :col-prefix ["dob" "gender"]))
  ;; you can rename the column names of res here
  ;; skipped
  (ck/compute res 8 "outputs/dcast_result.csv" :exclude ["dob_family_id" "dob_age_mother"]))

family_id,age_mother,dob_child1,dob_child2,dob_child3,gender_child1,gender_child2,gender_child3
1,30,1998-11-26,2000-01-29,,1,2,
2,27,1996-06-22,,,2,,
3,26,2002-07-11,2004-04-05,2007-09-02,2,2,1
4,32,2004-10-10,2009-08-27,2012-07-21,1,1,1
5,29,2000-12-05,2005-02-28,,2,1,

family_id,age_mother,child,dob,gender
1,30,1,1998-11-26,1
2,27,1,1996-06-22,2
3,26,1,2002-07-11,2
4,32,1,2004-10-10,1
5,29,1,2000-12-05,2
1,30,2,2000-01-29,2
2,27,2,,
3,26,2,2004-04-05,2
4,32,2,2009-08-27,1
5,29,2,2005-02-28,1
1,30,3,,
2,27,3,,
3,26,3,2007-09-02,1
4,32,3,2012-07-21,1
5,29,3,,

family_id1,age_mother1,child1,dob,gender
1,30,child1,1998-11-26,1
1,30,child2,2000-01-29,2
1,30,child3,,
2,27,child1,1996-06-22,2
2,27,child2,,
2,27,child3,,
3,26,child1,2002-07-11,2
3,26,child2,2004-04-05,2
3,26,child3,2007-09-02,1
4,32,child1,2004-10-10,1
4,32,child2,2009-08-27,1
4,32,child3,2012-07-21,1
5,29,child1,2000-12-05,2
5,29,child2,2005-02-28,1
5,29,child3,,

family_id,age_mother,child,dob,gender
1,30,1,1998-11-26,1
2,27,1,1996-06-22,2
3,26,1,2002-07-11,2
4,32,1,2004-10-10,1
5,29,1,2000-12-05,2
1,30,2,2000-01-29,2
2,27,2,,
3,26,2,2004-04-05,2
4,32,2,2009-08-27,1
5,29,2,2005-02-28,1
1,30,3,,
2,27,3,,
3,26,3,2007-09-02,1
4,32,3,2012-07-21,1
5,29,3,,

dob_child1,dob_child2,dob_child3,gender_family_id,gender_age_mother,gender_child1,gender_child2,gender_child3
1996-06-22,,,2,27,2,,
1998-11-26,2000-01-29,,1,30,1,2,
2000-12-05,2005-02-28,,5,29,2,1,
2002-07-11,2004-04-05,2007-09-02,3,26,2,2,1
2004-10-10,2009-08-27,2012-07-21,4,32,1,1,1

Time zones

Parse timestamps that end in a time zone code, convert them to UTC with operate, write them with a custom formatter, and sum sales per UTC timestamp.

Source: timezone.clj · run with :main clojask-examples.timezone/-main

(def string-zone
  {"UTC" "UTC"
   "HKG" "Asia/Hong_Kong"
   "UK" "Europe/London"
   "DUB" "Asia/Dubai"
   "LA" "America/Los_Angeles"})

(defn get-utc-time
  "the input is a datetime string with timezone identifier as suffix"
  ;; suppose the input format is like this 2010-01-19 23:34:23 UTC
  [time-string]
  (let [time (subs time-string 0 (str/last-index-of time-string " "))
        timezone (subs time-string (inc (str/last-index-of time-string " ")))
        timezone (get string-zone timezone)
        iso-format (SimpleDateFormat. "yyyy-MM-dd HH:mm:ss")
        tmp (.setTimeZone iso-format (TimeZone/getTimeZone timezone))
        time (.parse iso-format time)]
    time))

(defn get-timezone
  [time-string]
  (let [timezone (subs time-string (inc (str/last-index-of time-string " ")))]
    timezone))
;; formatters run on several worker threads at once, so this one must be
;; thread-safe: DateTimeFormatter is, a shared SimpleDateFormat is not
(def utc-format (.withZone (DateTimeFormatter/ofPattern "yyyy-MM-dd HH:mm:ss") ZoneOffset/UTC))
(defn utctime-formatter
  "the input is a java.util.Date, return is a format string"
  [utc-time]
  (.format utc-format (.toInstant utc-time)))

(defn -main
  []
  (def df (ck/dataframe "resources/sales.csv"))
  (ck/operate df get-utc-time "datetime" "utc-time")
  (ck/operate df get-timezone "datetime" "timezone")
  (ck/set-formatter df "utc-time" utctime-formatter)
  (ck/group-by df "utc-time")
  (ck/compute df 8 "outputs/timezone1.csv")
  (ck/set-type df "sold" "int")
  (ck/aggregate df #(reduce + %) "sold" "sum(sold)")
  (ck/compute df 8 "outputs/timezone2.csv"))

datetime,cust,item,sold
2010-01-19 12:12:12 UTC,101,2,11
2010-01-22 23:34:23 HKG,102,1,7
2010-01-22 15:34:23 UK,102,1,7
2010-01-25 23:34:23 DUB,101,2,9
2010-01-26 23:34:23 LA,101,1,10

utc-time
2010-01-27 07:34:23
2010-01-25 19:34:23
2010-01-19 12:12:12
2010-01-22 15:34:23

utc-time,sum(sold)
2010-01-27 07:34:23,10
2010-01-25 19:34:23,9
2010-01-19 12:12:12,11
2010-01-22 15:34:23,14