tbanel / orgaggregate Goto Github PK
View Code? Open in Web Editor NEWAggregates tables in Org mode
License: GNU General Public License v3.0
Aggregates tables in Org mode
License: GNU General Public License v3.0
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
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:
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)
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
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
?
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.
org-insert-dblock should
(provide 'org-insert-dblock)
instead of (provide 'org-inset-dblock)
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
.
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.
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: โฆ
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.
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 ๐ค
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.
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?
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
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 :)
The formatting directive ';T' does not seem to work.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.