0

I have the following org table where I actively record columns 1,2,3,4 and the remaining columns are calculated from the first four. Using this I calculate the aggregate from the last three columns using orgaggregate link.

MWE

#+TITLE: Workout

#+NAME: helpers
#+begin_src emacs-lisp
(defun k/get-week (org-date-time)
  (set 'year (nth 5 (org-parse-time-string org-date-time)))
  (set 'month (nth 4 (org-parse-time-string org-date-time)))
  (set 'day (nth 3 (org-parse-time-string org-date-time)))
  (car
   (calendar-iso-from-absolute
    (calendar-absolute-from-gregorian
     (list month day year)))))
#+end_src

* Horizontal Push
#+NAME: hpush
| date             | progression | sets-reps | comment                               | year | week-number | total-volume |
|------------------+-------------+-----------+---------------------------------------+------+-------------+--------------|
| <2021-11-26 Fri> | push up     | 5 5 5 5 5 | Hard on wrists                        | 2021 |          47 |           25 |
| <2021-11-30 Tue> | push up     | 8 8 8 8 8 | Parallettes                           | 2021 |          48 |           40 |
| <2021-12-03 Fri> | push up     | 8 8 8 8 8 | parallettes inclined                  | 2021 |          48 |           40 |
| <2021-12-06 Mon> | push up     | 3 3 3 3 4 | parallettes parallel                  | 2021 |          49 |           16 |
| <2022-01-03 Mon> | push up     | 5 5 5     | parallettes parallel right difficulty | 2022 |           1 |           15 |
#+TBLFM: $7='(reduce '+ (mapcar 'string-to-number (split-string $3 " ")))
#+TBLFM: $5='(nth 5 (org-parse-time-string $1))
#+TBLFM: $6='(k/get-week $1)

#+BEGIN: aggregate :table "hpush" :cols "'year' 'week-number' sum('total-volume')"
| 'year' | 'week-number' | sum('total-volume') |
|--------+---------------+---------------------|
|   2021 |            47 |                  25 |
|   2021 |            48 |                  80 |
|   2021 |            49 |                  16 |
|   2022 |             1 |                  15 |
#+END:

# Local Variables:
# org-confirm-babel-evaluate: nil
# eval: (progn (org-babel-goto-named-src-block "helpers") (org-babel-execute-src-block) (outline-hide-sublevels 1))
# End:

I would like to record only columns date, progression, sets-reps, comment in the first table and not calculate the remaining columns in the first table but create a second table (using the first table's 4 columns) which will have year, week-number, sum(total-volume).

With SQL, I'd roughly be doing this

select to_year(date), get_week_number(date), sum(sets-reps)
from table
group by to_year(date), get_week_number(date);

I'm trying to get weekly volume of my reps.

3
0

I was able to do this with sqlite and python. I still can't figure out a elisp method.

Sqlite

#+begin_src sqlite :db /tmp/workout.db :var workout=hpush :colnames yes
drop table if exists workout;
create table workout(date text, progression text, set_rep text, comment text, year text, week text, vol text);
.mode csv
.import $workout workout
with recursive split(date, set_rep, str) AS (
select date, '', set_rep||' ' from workout
union all select
date,
substr(str, 0, instr(str, ' ')),
substr(str, instr(str, ' ')+1)
from split where str != ''
)
select
strftime('%Y', substr(date, 2, 10)) year,
strftime('%W', substr(date, 2, 10)) week,
sum(set_rep) as total_volume
from split
where set_rep != ''
group by year, week
;
#+end_src

#+RESULTS:
| year | week | total_volume |
|------+------+--------------|
| 2021 |   47 |           25 |
| 2021 |   48 |           80 |
| 2021 |   49 |           16 |
| 2022 |    1 |           15 |

Python

#+NAME: pyhelper
#+begin_src python :var tbl=None
from datetime import datetime
from collections import defaultdict
parse_date = lambda x: datetime.strptime(x, "<%Y-%m-%d %a>")
get_week = lambda x: parse_date(x).strftime("%W")
get_year = lambda x: parse_date(x).strftime("%Y")
sum_reps = lambda x: sum(map(int, str(x).split()))

final = defaultdict(int)
for item in tbl:
    year = get_year(item[0])
    reps = sum_reps(item[2])
    week = get_week(item[0])
    final[f'{year}_{week}'] += reps
res = [[key, val] for key, val in final.items()]
return res
#+end_src

#+CALL: pyhelper(tbl=hpush)

#+RESULTS:
| 2021_47 | 25 |
| 2021_48 | 80 |
| 2021_49 | 16 |
| 2022_01 | 15 |

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.