1

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

2 Answers 2

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 |
0

You may shrink those annoying columns to zero width:

#+NAME: hpush
| date             | progression | sets-reps | comment                               |…|…|…|
|                  |             |           |                                       |…|…|…|
|------------------+-------------+-----------+---------------------------------------+…+…+…+
| <2021-11-26 Fri> | push up     | 5 5 5 5 5 | Hard on wrists                        |…|…|…|
| <2021-11-30 Tue> | push up     | 8 8 8 8 8 | Parallettes                           |…|…|…|
| <2021-12-03 Fri> | push up     | 8 8 8 8 8 | parallettes inclined                  |…|…|…|
| <2021-12-06 Mon> | push up     | 3 3 3 3 4 | parallettes parallel                  |…|…|…|
| <2022-01-03 Mon> | push up     | 5 5 5     | parallettes parallel right difficulty |…|…|…|
#+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)

To do so, add width cookies, for example in the second row: <0>

... | year | week-number | total-volume |
... |  <0> |         <0> |          <0> |
... +------+-------------+--------------|
... | 2021 |          47 |           25 |
... | 2021 |          48 |           40 |
... | 2021 |          48 |           40 |
... | 2021 |          49 |           16 |
... | 2022 |           1 |           15 |

And then type C-u C-c Tab in the table to shrink columns. Type C-u C-u C-c Tab to get back full width.

2
  • sorry I don't understand how your answer answers my question. I did not ask about shrinking columns Jan 29, 2022 at 19:09
  • Sure. You have a working solution, but with those 3 additional columns you don't want to see. My suggestion was to stick with your solution, but get those 3 columns out of sight.
    – tbanel
    Feb 1, 2022 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.