* how to group together fields
@ 2014-01-30 15:45 Stefan Huchler
2014-01-31 11:44 ` Alexander Baier
0 siblings, 1 reply; 12+ messages in thread
From: Stefan Huchler @ 2014-01-30 15:45 UTC (permalink / raw)
To: emacs-orgmode
I want to use org-mode as a (tea) database.
What have I done:
1. I have wrote down a tree of shops/orders/brands
2. I defined COLUMNS for Column View that give me a score per shop list
What do I want as result:
1. give me a list of scores for the shops [DONE]
2. give me a list of scores for each brand [TODO]
is there a way to group together Tea brands to give each Brand a Score
(how I liked it)?
below are the data I used (the scores are just random test-data):
(sorry the field names are german mostly)
* Tea Database
:PROPERTIES:
:COLUMNS: %Shop(Shop) %Bewertung(Bewertung){mean}
:END:
** Tee Kampagne
:PROPERTIES:
:Shop: Tee Kampagne
:END:
*** Bestellung 1
:PROPERTIES:
:END:
**** Tee 1
:PROPERTIES:
:Sorte: Pussimbing
:Bewertung: 1
:END:
*** Bestellung 2
:PROPERTIES:
:END:
**** Tee 1
:PROPERTIES:
:Sorte: Pussimbing
:Bewertung: 5
:END:
**** Tee 2
:PROPERTIES:
:Sorte: Steinthal
:Bewertung: 7
:END:
** Teeblätter Versand
:PROPERTIES:
:Shop: Teeblätter Versand
:END:
*** Tee 1
:PROPERTIES:
:Sorte: Pussimbing
:Bewertung: 6
:END:
*** Tee 2
:PROPERTIES:
:Sorte: Steinthal
:Bewertung: 8
:END:
*** Tee 3
:PROPERTIES:
:Sorte: Risheehat
:Bewertung: 9
:END:
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
2014-01-30 15:45 how to group together fields Stefan Huchler
@ 2014-01-31 11:44 ` Alexander Baier
[not found] ` <87r47nshuh.fsf@mars.lan>
2014-02-22 15:19 ` Stefan Huchler
0 siblings, 2 replies; 12+ messages in thread
From: Alexander Baier @ 2014-01-31 11:44 UTC (permalink / raw)
To: Stefan Huchler; +Cc: emacs-orgmode
On 2014-01-30 16:45 Stefan Huchler wrote:
> What do I want as result:
>
> 2. give me a list of scores for each brand [TODO]
>
> is there a way to group together Tea brands to give each Brand a Score
> (how I liked it)?
I do not think that this is possible with column view. You could maybe
get sum of the scores for each brand (btw, for the "non-german" people:
brand ~ Sorte and score ~ Bewertung) by using the org mapping API:
http://orgmode.org/manual/Using-the-mapping-API.html. But I do not know
if it is possible to integrate this into column view.
Thinking about this a bit more I come to the conclusion that what you
want (2.) does not fit into the structure of your database at
all. Looking at how a CV of your structure would be displayed, there
simply is no right place to display this information. If I missed
something, please tell me which column(s) you want to see displaying
this information.
If you just want the result to be thrown into a buffer, this would be
totally possible.
HTH,
--
Alexander Baier
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
[not found] ` <87r47nshuh.fsf@mars.lan>
@ 2014-02-01 11:03 ` Alexander Baier
[not found] ` <87mwibrs5u.fsf@mars.lan>
[not found] ` <87ioszrr6l.fsf@mars.lan>
0 siblings, 2 replies; 12+ messages in thread
From: Alexander Baier @ 2014-02-01 11:03 UTC (permalink / raw)
To: Stefan Huchler; +Cc: emacs-orgmode
Hello Stefan,
please keep the mailing list cc'd so everyone can read your response.
On 2014-02-01 03:10 Stefan Huchler wrote:
> thx for looking over it.
>
> My idea was that I have 2 :COLUMNS: rows, comment out one or the other.
> I dont need one "table" that shows me both, thats impossible yes, but
> one table for the one information and another for the other.
You would have to maintain two seperate and differently structured trees
of your data to achieve this. I do not know if this is the way you want
to go, as it would require you to keep both trees in sync by hand.
Regards,
--
Alexander Baier
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
[not found] ` <87mwibrs5u.fsf@mars.lan>
@ 2014-02-01 11:46 ` Alexander Baier
0 siblings, 0 replies; 12+ messages in thread
From: Alexander Baier @ 2014-02-01 11:46 UTC (permalink / raw)
To: Stefan Huchler; +Cc: emacs-orgmode
Hello Stefan,
again, please sent your mail also to the org mailing list. Just do a
follow-up in your mail client, instead of a reply, that should do the
right thing I guess.
On 2014-02-01 12:25 Stefan Huchler wrote:
> Hello Alex,
>
> thats exactly what don´t want ^^. I tried something similar a while ago
> with TBLFM function but it did not work.
>
> I dont care to much about how I can do it, how exactly I manage the data
> (in lists in attributes in tables) I just don´t want redundancy.
>
> It should be no rocket science, so I can reproduce it, if nothing else
> maybe you could generate org-trees from this or so.
This can surely be done by using the mapping api I referenced in the
first post. You need to know some elisp for this thought. But I would be
willing to post something along those lines, if you wanted to go down
that way.
Putting the collected data into an org-tree or presenting it in another
way should be doable, too.
> Its sad all data is there but because org-mode dont know something like
> group by, its not working.
>
> it would be very powerful, I thought thats what org-mode/emacs is all
> about.
Well, the question is, if you want to implement a whole database upon
the org-format.
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
[not found] ` <87ioszrr6l.fsf@mars.lan>
@ 2014-02-01 12:33 ` Alexander Baier
2014-02-01 13:02 ` Stefan Huchler
0 siblings, 1 reply; 12+ messages in thread
From: Alexander Baier @ 2014-02-01 12:33 UTC (permalink / raw)
To: Stefan Huchler; +Cc: emacs-orgmode
Hello Stefan,
I say it again: _Please_ Cc the org-mode mailing list, when replying, so
everyone can read our correspondence.
On 2014-02-01 12:46 Stefan Huchler wrote:
> sry I maybe miss readed you. I have no problem in a different
> structure. If you could "join" 2 different org trees by linking in the
> data or something, like a tee table and a vendor table but have no
> redundant data, I would be ok. clearly my way would be even better but
> I would be ok with a other structure.
Linking to another org-header is possible, but you cannot use this to
feed information to the aggregate functions of Column View. Regarding
normal tables, I have no idea.
> This tree is not fixed, its only a demonstration what I want to do. I
> want to manage data somehow that is not redundant where I can type in
> data once and get this 2 tables when I need them generated.
>
> And if possible it should not be that I every time I want to use
> something similar I have to write a big special function for it.
One could try write it general enough to be applicable to similar
situations I think.
> I am not shure If I do always the most obious only good way (as a
> pythoneer would say ^^) to reach my goal.
>
> But in general in my oppinion emacs is not only a editor not only a operation system
> (package manager...) but also a office suite ( authoring, spreadsheet )
> so why not have a somewhat full personal database system.
>
> The alternative would be for me to use for such small things sqlite and
> write some code for representation and sql statements.
>
> so the effort to do this and the big problems with backups with real
> databases would be enorm.
>
> Sorry hope that sounds not like whinig, but I saw 2 days ago a
> presentation (video) from one of or the author from org-mode, and he
> said that some people use it as database. So a database should be able
> to do such basic operation like group by in my oppinion. And you can
> express the logik like I did, its only a question if the org-columns
> function can use it.
I think a lot of people either use it as a more or less flat listing
(with the structure beeing strongly influenced by the aggregate
functions they want to run over it) or they write a bunch of elisp to
help them extract information.
> maybe I have to hack it myself even if I wrote 10 lines of lisp in my
> live ;)
Maybe Bastien (author of Column View I think) can say something to this,
with regard to supplying custom elisp as an aggregate function.
Regards,
--
Alexander Baier
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
2014-02-01 12:33 ` Alexander Baier
@ 2014-02-01 13:02 ` Stefan Huchler
2014-02-03 8:51 ` Bastien
0 siblings, 1 reply; 12+ messages in thread
From: Stefan Huchler @ 2014-02-01 13:02 UTC (permalink / raw)
To: emacs-orgmode
Hi Alex,
sorry in gnus its (f) for follow up thought reply in the mailing-list
would be the same ;)
I use emacs since a few months and gnus since a week so
I am not that familier with it as you see ;)
If Bastien could look over it, it would be nice.
Its not my only emacs problem to solve and have to manage other stuff
like relocating (or find a apartment first) and stuff, so if I had not
to do that my self or with help from somebody more into it, I would be happy.
Not that this "database" is so important but it reapears in my brain as
a todo item again and again ;)
greetings
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
2014-02-01 13:02 ` Stefan Huchler
@ 2014-02-03 8:51 ` Bastien
2014-02-03 15:25 ` Stefan Huchler
0 siblings, 1 reply; 12+ messages in thread
From: Bastien @ 2014-02-03 8:51 UTC (permalink / raw)
To: Stefan Huchler; +Cc: emacs-orgmode
Hi Stefan,
(Hint: use `S W' from Gnus to reply to this email.)
Stefan Huchler <stefan.huchler@mail.de> writes:
> If Bastien could look over it, it would be nice.
The thread is a bit difficult to read -- can you make a summary
of what is the bug report or the feature request?
Thanks!
--
Bastien
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
2014-02-03 8:51 ` Bastien
@ 2014-02-03 15:25 ` Stefan Huchler
2014-02-22 20:20 ` Michael Brand
0 siblings, 1 reply; 12+ messages in thread
From: Stefan Huchler @ 2014-02-03 15:25 UTC (permalink / raw)
To: Bastien; +Cc: emacs-orgmode
Hi Bastien,
I want to extract Information with ColumnView from a org-tree, that
works like group-by in a select statement.
so if I have a tree like that:
* Tea Database
:PROPERTIES:
:COLUMNS: %Shop(Shop) %rating(Rating){mean}
:END:
** Tea Shop 1
:PROPERTIES:
:Shop: Tea Shop 1
:END:
*** Order 1
:PROPERTIES:
:END:
**** Tee 1
:PROPERTIES:
:blend: Pussimbing
:rating: 1
:END:
*** Order 2
:PROPERTIES:
:END:
**** Tea 1
:PROPERTIES:
:blend: Pussimbing
:rating: 5
:END:
**** Tea 2
:PROPERTIES:
:blend: Steinthal
:rating: 7
:END:
** Tea Shop 2
:PROPERTIES:
:Shop: Tea Shop 2
:END:
*** Tea 1
:PROPERTIES:
:blend: Pussimbing
:rating: 6
:END:
*** Tea 2
:PROPERTIES:
:blend: Steinthal
:rating: 8
:END:
that works so far, I can see a list of shops and there mean ratings.
I want now as another "table" a second columns line what I would
comment out with the current :COLUMNS: line that would look like that:
:COLUMNS: %blend(Blend){groupby blend} %rating(Rating){mean}
I get with the 1. COLUMNS line something like that:
Tea Shop 1 | 5.0 | ...
Tea Shop 2 | 7.0 | ...
I want another output that looks like that:
Blend 1 | 6.0 | ...
Blend 2 | 3.0 | ...
btw blend is the wrong word, in this case it would be the origin tea
garden, but thats only a context detail ;)
The idea is that I give in data and a score for the tea, and I can look
than which tea blends I liked most and which shop gave me best quality.
hope its clear now ;)
Bastien <bzg@gnu.org> writes:
> Hi Stefan,
>
> (Hint: use `S W' from Gnus to reply to this email.)
>
> Stefan Huchler <stefan.huchler@mail.de> writes:
>
>> If Bastien could look over it, it would be nice.
>
> The thread is a bit difficult to read -- can you make a summary
> of what is the bug report or the feature request?
>
> Thanks!
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
2014-01-31 11:44 ` Alexander Baier
[not found] ` <87r47nshuh.fsf@mars.lan>
@ 2014-02-22 15:19 ` Stefan Huchler
1 sibling, 0 replies; 12+ messages in thread
From: Stefan Huchler @ 2014-02-22 15:19 UTC (permalink / raw)
To: emacs-orgmode
hi (Alexander and everybody else who is interested in this topic),
sorry the thread is old and I did not update it, sadly Bastian did not
react to my last post.
I just thought I try it myself, but its tricky and org-colview.el has
1000 lines of code. And I wrote only small functions in (e)lisp so far.
But you did say something that it would be "totaly doable" to output
what I want in a new buffer.
I dont really care to much how it exactly work. I tried this also with
org-tables and failed with it too.
I just want a way to write down data (table or org-structure is both k)
of this orders ONE time and get both data, the mean score of tea from a
shop and the mean score from a tea brand (tea garden) so I can speculate
if I just dont like tea from this plantage/region or if just that shop
has low quality of tea.
Do you have idea how I could get there a quick hack, of course it would
be nice if it would be not a hardcoded tea-shop-function ^^.
But maybe it would be easier to write it first static to exact this
data.
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
2014-02-03 15:25 ` Stefan Huchler
@ 2014-02-22 20:20 ` Michael Brand
2014-02-23 1:12 ` Stefan Huchler
0 siblings, 1 reply; 12+ messages in thread
From: Michael Brand @ 2014-02-22 20:20 UTC (permalink / raw)
To: Stefan Huchler; +Cc: Org Mode
Hi Stefan
On Mon, Feb 3, 2014 at 4:25 PM, Stefan Huchler <stefan.huchler@mail.de> wrote:
> I get with the 1. COLUMNS line something like that:
>
> Tea Shop 1 | 5.0 | ...
> Tea Shop 2 | 7.0 | ...
>
> I want another output that looks like that:
>
> Blend 1 | 6.0 | ...
> Blend 2 | 3.0 | ...
My suggestion for these several GROUP BY as in SQL on the same data is
to use Org babel with R or TBLFM.
I am not a user of the R programming language and therefore can not
provide a working example but it would be one of the simpler
solutions. Put all data into one Org table just like "#+NAME: data"
below and for each result table use an Org babel source block with R
that reads from this table.
Some R example of GROUP BY as in SQL:
http://stats.stackexchange.com/questions/8225/how-to-summarize-data-by-group-in-r
With TBLFM the only thing that comes to my mind is org-lookup-all. The
disadvantage of this solution is that you have to enumerate all groups
manually in the result tables:
#+NAME: data
| Purchase | Product | Shop | Rating |
|----------+---------+------+--------|
| a | p1 | s1 | 1 |
| b | p1 | s2 | 4 |
| c | p2 | s1 | 2 |
| d | p3 | s2 | 8 |
Product rating and ranking by average purchase:
| Product | Rating | Ranking |
|---------+--------+---------|
| p1 | 2.5 | 2 |
| p2 | 2.0 | 3 |
| p3 | 8.0 | 1 |
#+TBLFM: $2 = '(let ((all (org-lookup-all '$1 '(remote(data,
@I$2..@>$2)) '(remote(data, @I$4..@>$4))))) (/ (apply '+ all) (length
all) 1.0)); L :: $3 = '(+ 1 (length (org-lookup-all $2 '(@I$2..@>$2)
nil '<))); N
Shop rating and ranking by average purchase:
| Shop | Rating | Ranking |
|------+--------+---------|
| s1 | 1.5 | 2 |
| s2 | 6.0 | 1 |
#+TBLFM: $2 = '(let ((all (org-lookup-all '$1 '(remote(data,
@I$3..@>$3)) '(remote(data, @I$4..@>$4))))) (/ (apply '+ all) (length
all) 1.0)); L :: $3 = '(+ 1 (length (org-lookup-all $2 '(@I$2..@>$2)
nil '<))); N
I added the above TBLFM solution as an ERT that can also be found via
Worg: On the page with the Worg tutorials in the spreadsheet section
follow the link to the "Emacs Regression Test (ERT) for Org tables"
with example use cases for TBLFM:
http://orgmode.org/worg/org-tutorials/index.html#Spreadsheet
In the ERT it is test-org-table/org-lookup-all.
Michael
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
2014-02-22 20:20 ` Michael Brand
@ 2014-02-23 1:12 ` Stefan Huchler
2014-02-23 7:32 ` Michael Brand
0 siblings, 1 reply; 12+ messages in thread
From: Stefan Huchler @ 2014-02-23 1:12 UTC (permalink / raw)
To: emacs-orgmode
thank you Michael,
that works great, sadly I have to type in the name of the shop
redundantly but I can live with that, with a good org-capture template I
could tab-complete them so it would not be that problem.
I dont want to bother you endlessly but just if you happen to know that
randomly:
can you somehow define a template that autocompletes to all other values
like that:
%^{Shop|shop 1|shop 2|**all-knows-old-choices**}
that would that make perfekt but I think thats maybe a wet dream ;)
A second question for me in trying to understand your code:
@I$4..@>$4
I get @> stands for something like first line or is it a * wildcard? I
should find that somewhere in doku but what does @I stand for?
Maybe I should not try to understand it ;)
Thx again ;)
^ permalink raw reply [flat|nested] 12+ messages in thread
* Re: how to group together fields
2014-02-23 1:12 ` Stefan Huchler
@ 2014-02-23 7:32 ` Michael Brand
0 siblings, 0 replies; 12+ messages in thread
From: Michael Brand @ 2014-02-23 7:32 UTC (permalink / raw)
To: Stefan Huchler; +Cc: Org Mode
Hi Stefan
On Sun, Feb 23, 2014 at 2:12 AM, Stefan Huchler <stefan.huchler@mail.de> wrote:
> that works great, sadly I have to type in the name of the shop
> redundantly but I can live with that, with a good org-capture template I
> could tab-complete them so it would not be that problem.
>
> I dont want to bother you endlessly but just if you happen to know that
> randomly:
>
> can you somehow define a template that autocompletes to all other values
> like that:
>
> %^{Shop|shop 1|shop 2|**all-knows-old-choices**}
Although there are many completion tools I doubt that there is one
which knows in which Org table column you are.
http://www.emacswiki.org/emacs/CategoryCompletion
But [...]_ALL to predefine Org property values to be selected easily
in the column view as described here
http://orgmode.org/manual/Property-syntax.html
knows in which column you are. To make benefit of this you could use a
flat tree with an entry for each purchase and a property for product,
shop and rating and capture the column view with
http://orgmode.org/manual/Capturing-column-view.html
into the known table "#+NAME: data" to be processed further.
> A second question for me in trying to understand your code:
>
> @I$4..@>$4
>
> I get @> stands for something like first line or is it a * wildcard? I
> should find that somewhere in doku but what does @I stand for?
@> is the last row, @I is the first hline to exclude the table header
that could have more than one line. For both and more see
http://orgmode.org/manual/References.html
Michael
^ permalink raw reply [flat|nested] 12+ messages in thread
end of thread, other threads:[~2014-02-23 7:32 UTC | newest]
Thread overview: 12+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2014-01-30 15:45 how to group together fields Stefan Huchler
2014-01-31 11:44 ` Alexander Baier
[not found] ` <87r47nshuh.fsf@mars.lan>
2014-02-01 11:03 ` Alexander Baier
[not found] ` <87mwibrs5u.fsf@mars.lan>
2014-02-01 11:46 ` Alexander Baier
[not found] ` <87ioszrr6l.fsf@mars.lan>
2014-02-01 12:33 ` Alexander Baier
2014-02-01 13:02 ` Stefan Huchler
2014-02-03 8:51 ` Bastien
2014-02-03 15:25 ` Stefan Huchler
2014-02-22 20:20 ` Michael Brand
2014-02-23 1:12 ` Stefan Huchler
2014-02-23 7:32 ` Michael Brand
2014-02-22 15:19 ` Stefan Huchler
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).