tbanel / orgaggregate Goto Github PK

View Code? Open in Web Editor NEW

161.0 4.0 7.0 323 KB

Aggregates tables in Org mode

License: GNU General Public License v3.0

Emacs Lisp 100.00%

orgaggregate's Issues

Filtering empty rows (empty line in aggregated result)

I am trying to filter empty rows and not sure if i stumbled up on a possible issue.

When i have a table and aggregate like below

#+TBLNAME: timetabletest
| Tags    | Headline                              | Time |      |      |     T |
|---------+---------------------------------------+------+------+------+-------|
|         | *Total time*                            | *2:30* |      |      |       |
|---------+---------------------------------------+------+------+------+-------|
|         | Report with filtered tags and sum...  | 2:30 |      |      |       |
|         | \_  Input data                        |      | 2:30 |      |       |
| client1 | \_    Update document for client      |      |      | 0:45 | 00:45 |
| work    | \_    Create my awesome note for work |      |      | 1:29 | 01:29 |
| work    | \_    Fill in timesheet               |      |      | 0:16 | 00:16 |
#+END:

Results are not filtered on empty Tags

#+BEGIN: aggregate :table "timetabletest" :cols "Tags T" :cond (not (equal Tags ""))
| Tags    |     T |
|---------+-------|
|         |       |
| client1 | 00:45 |
| work    | 01:29 |
| work    | 00:16 |
#+END:

However when i use column names it all works as expected.. (See A and B in the data table)

#+TBLNAME: timetabletest
| Tags    | Headline                              | Time |    A |    B |     T |
|---------+---------------------------------------+------+------+------+-------|
|         | *Total time*                            | *2:30* |      |      |       |
|---------+---------------------------------------+------+------+------+-------|
|         | Report with filtered tags and sum...  | 2:30 |      |      |       |
|         | \_  Input data                        |      | 2:30 |      |       |
| client1 | \_    Update document for client      |      |      | 0:45 | 00:45 |
| work    | \_    Create my awesome note for work |      |      | 1:29 | 01:29 |
| work    | \_    Fill in timesheet               |      |      | 0:16 | 00:16 |
#+END:

#+BEGIN: aggregate :table "timetabletest" :cols "Tags T" :cond (not (equal Tags ""))
| Tags    |     T |
|---------+-------|
| client1 | 00:45 |
| work    | 01:29 |
| work    | 00:16 |
#+END:

The some goes when using a function for the group.

Does not work..

#+BEGIN: clocktable :scope file :maxlevel 3 :tags t :match "work|client1" :header "#+TBLNAME: timetable\n"
#+TBLNAME: timetable
| Tags    | Headline                              | Time |      |      |     T |
|---------+---------------------------------------+------+------+------+-------|
|         | *Total time*                            | *2:30* |      |      |       |
|---------+---------------------------------------+------+------+------+-------|
|         | Report with filtered tags and sum...  | 2:30 |      |      |       |
|         | \_  Input data                        |      | 2:30 |      |       |
| client1 | \_    Update document for client      |      |      | 0:45 | 00:45 |
| work    | \_    Create my awesome note for work |      |      | 1:29 | 01:29 |
| work    | \_    Fill in timesheet               |      |      | 0:16 | 00:16 |
#+TBLFM: $6='(convert-org-clocktable-time-to-hhmm $5)::@1$6='(format "%s" "T")
#+END:

#+BEGIN: aggregate :table "timetable" :cols "Tags sum(T);U" :cond (not (equal Tags ""))
#+TBLNAME: timetable
| Tags    | sum(T);U |
|---------+----------|
|         |    00:00 |
| client1 |    00:45 |
| work    |    01:45 |
#+END:

This works...

#+BEGIN: clocktable :scope file :maxlevel 3 :tags t :match "work|client1" :header "#+TBLNAME: timetable\n"
#+TBLNAME: timetable
| Tags    | Headline                              | Time | A    | B    |     T |
|---------+---------------------------------------+------+------+------+-------|
|         | *Total time*                            | *2:30* |      |      |       |
|---------+---------------------------------------+------+------+------+-------|
|         | Report with filtered tags and sum...  | 2:30 |      |      |       |
|         | \_  Input data                        |      | 2:30 |      |       |
| client1 | \_    Update document for client      |      |      | 0:45 | 00:45 |
| work    | \_    Create my awesome note for work |      |      | 1:29 | 01:29 |
| work    | \_    Fill in timesheet               |      |      | 0:16 | 00:16 |
#+TBLFM: $6='(convert-org-clocktable-time-to-hhmm $5)::@1$6='(format "%s" "T")
#+END:

#+BEGIN: aggregate :table "timetable" :cols "Tags sum(T);U" :cond (not (equal Tags ""))
#+TBLNAME: timetable
| Tags    | sum(T);U |
|---------+----------|
| client1 |    00:45 |
| work    |    01:45 |
#+END:

So not sure if it is an issue or if i am missing something :)

Time formatters

The formatting directive ';T' does not seem to work.

Suggestion: separate group by parameters from the projection of the grouping operations

This module is great. I have a suggestion that might make it better.

Aggregation is an operation with two parameters: the columns to aggregate by, and the operations on each of the subgroups. I think the syntax of the blocks would be cleaner if those two parameters are separated:

one parameter for the aggregation column (or columns, perhaps a future option)
one parameter for the actual projected values for each of the subgroups (which might include the aggregation column, or not, or perhaps not in the first location.

Just a thought. This is how SQL implements the operation: subgroup by group by attributes, and then project based on the SELECT statement the results for each subgroup)

Underscore in Header Line breaks vsum

Hi,

Nice package - thanks for sharing it!

I've found that whilst the following fails:
#+BEGIN: aggregate :table "covid" :cols "Country vsum(Cumulative_cases)"

If I rename the column, removing the underscore it works:
#+BEGIN: aggregate :table "covid" :cols "Country vsum(Cumulative)"

Can this be fixed in the code?

Invalid Function Error [Emacs 26.1]

When I tried out the first example (the one counting colors), I got an error:

orgtbl-to-aggregated-table-do-sums: Invalid function: ((symbol-function (quote math-read-preprocess-string)) (function identity))

Then I noticed orgtbl-to-aggregated-table-do-sums begins with an letf, which doesn't exist in Emacs 26.1 (which I am using). It worked after I changed letf to cl-letf.

How calculate values of column#x in whole rows contain some text?

Emacs 26.1

Suppose I has org table:

| Task | State | Duration|
|----------------+------------+------------|
| RP-135 | Taken | 5.50 |
| RP-77 | Taken | 1.50 |
| RP-135 | Taken | 4.50 |
| RP-135 | Taken | 1.50 |
| RP-77 | Taken | 1.50 |
| RP-162 | Taken | 6.00 |

I need to sum all values in column Duration in whole rows that in column "Task" contain RP-77 ?

The result must be 3.00

Preserve horizontal lines in the aggregated tables when using the special column "hline"

When using the hline special column, instead of creating an actual column hline with numbers, it would be great if the horizontal lines where conserved in the aggregated tables.

compilation warnings

Hi,

Using orgtbl-aggregate from Melpa using Emacs 30.0.50 triggers several warnings:

⛔ Warning (comp): orgtbl-aggregate.el:142:14: Warning: ‘inhibit-changing-match-data’ is an obsolete variable (as of 29.1); use ‘save-match-data’ instead.
⛔ Warning (comp): orgtbl-aggregate.el:326:2: Warning: docstring has wrong usage of unescaped single quotes (use \=' or different quoting such as `...')
⛔ Warning (comp): orgtbl-aggregate.el:473:4: Warning: More than one doc string
⛔ Warning (comp): orgtbl-aggregate.el:1246:2: Warning: docstring wider than 80 characters
⛔ Warning (comp): orgtbl-aggregate.el:1246:2: Warning: docstring has wrong usage of unescaped single quotes (use \=' or different quoting such as `...')
⛔ Warning (comp): orgtbl-aggregate.el:1343:2: Warning: docstring wider than 80 characters
⛔ Warning (comp): orgtbl-aggregate.el:1343:2: Warning: docstring has wrong usage of unescaped single quotes (use \=' or different quoting such as `...')
⛔ Warning (comp): orgtbl-aggregate.el:1551:2: Warning: docstring has wrong usage of unescaped single quotes (use \=' or different quoting such as `...')
⛔ Warning (comp): orgtbl-aggregate.el:1624:2: Warning: docstring has wrong usage of unescaped single quotes (use \=' or different quoting such as `...')

Add support for new added Org Mode dynamic block inserting API

Here is my new submitted API code: https://code.orgmode.org/bzg/org-mode/commit/34b71a0ca97559d9195c0f938c19ec292e4e77ec

I'm inspired by your package API code.

And here is the discusssion in mailing list:

There might be missing in the list. Because they are not all in same searched thread result.

Name the column generated by a formula

Is it possible to specify names for the columns generated by :formula/#+TBLFM?

#+NAME: device-data
| Device | Previous | New |
|--------+----------+-----|
| Foo    |       14 |  33 |
| Bar    |       15 |  22 |

#+BEGIN: aggregate :table "device-data" :cols ("Device" "Previous" "New") :formula "$4=$3-$2::$5=100*$3/$2;%2.2f"                                                                                                                                                                                                                                                                                                   
| Device | Previous | New |    |        |
|--------+----------+-----+----+--------|
| Foo    |       14 |  33 | 19 | 235.71 |
| Bar    |       15 |  22 |  7 | 146.67 |
#+TBLFM: $4=$3-$2::$5=100*$3/$2;%2.2f
#+END:

I can set the names manually; however, subsequent C-c C-c's remove the names.

org-insert-dblock.el: typo in (provide ....) line

org-insert-dblock should

(provide 'org-insert-dblock)

instead of (provide 'org-inset-dblock)

Referene name of aggrgated table is missing

I want to use an aggregated table as data source for gnuplot.
For this I have to know the (not yet documented) table name or assign a new table name.

I tried to add #+TBLNAME: myTable but after recalculation it is removed.
I suggest to retain this annotation in a similar way as e.g. #+TBLFM: …

Add rows after aggregation?

Would it be possible to add a parameter ":post" that would add rows, columns, and hlines to the table after aggregation? Here is how I envision it working: Given the table "original" in README.org, I would like to do something like the following:

#+BEGIN: aggregate :table "original" :cols "Day sum(Quantity)" :post "hline rows(1)" :formula "@>$2=vsum(@I..II)"
| Day       | sum(Quantity) |
|-----------+---------------|
| Monday    |            14 |
| Tuesday   |            45 |
| Wednesday |            54 |
| Thursday  |            83 |
| Friday    |            22 |
|-----------+---------------|
|           |           218 |
#+TBLFM: @>$2=vsum(@I..II)
#+END:

For this to work, the rows and columns must be added after aggregation but before the table formulas are executed. In principle, I could manually add the rows, columns myself after aggregation, but they would be removed whenever the table is reaggregated.

How to sort rows after aggregation?

Is it possible to sort the rows based on values after aggregation? If I group by one variable and sum values based on that grouping, it is often desired to get back the results in descending order of the calculated sum values. This would be very handy.

By the way: what a cool package, thanks a lot! I used to do all these things via Python source code blocks using pandas, but this is much quicker for simple aggregations 🤓

unknown rx from literal

I installed orgtbl-aggregate and attempted to just run the very first example given in the documentation. However, it returns the Error: "unknown rx from literal"" and I cannot find anything that explains this error. I am running Emacs 26.3

Skip cookie rows

Hey, thanks for making orgtbl-aggregate – it's a great addition to org-mode that will mostly just DWIM :-) I just have one minor request:

#+NAME: original
|    Day    | Color | Level | Quantity |
|-----------+-------+-------+----------|
|    <c>    |       |       |          |
|  Monday   | Red   |    30 |       11 |
|  Monday   | Blue  |    25 |        3 |
|  Tuesday  | Red   |    51 |       12 |
|  Tuesday  | Red   |    45 |       15 |
|  Tuesday  | Blue  |    33 |       18 |
| Wednesday | Red   |    27 |       23 |
| Wednesday | Blue  |    12 |       16 |
| Wednesday | Blue  |    15 |       15 |
| Thursday  | Red   |    39 |       24 |
| Thursday  | Red   |    41 |       29 |
| Thursday  | Red   |    49 |       30 |
|  Friday   | Blue  |     7 |        5 |
|  Friday   | Blue  |     6 |        8 |
|  Friday   | Blue  |    11 |        9 |

#+BEGIN: aggregate :table "original" :cols "Color count()"
| Color | count() |
|-------+---------|
|       |       1 |
| Red   |       7 |
| Blue  |       7 |
#+END:

that empty 1 in the aggregate shouldn't be there

Retain all columns when filtering via `:cond`

It's awesome that you allow for filtering: that's useful all the time and critically missing from org-tables.

However, one thing that's a bit cumbersome about the current implementation is that one has to always specify all the columns that should be retained after filtering. For me, the default case should be that all columns are retained and only the rows are filtered. Yet, one gets an empty table when not using the :cols specification altogether.

If it requires too much work to change this, one more convenient solution might be to allow for an argument like :cols all?

tbanel / orgaggregate Goto Github PK

orgaggregate's Issues

Recommend Projects

Recommend Topics

Recommend Org