* 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
[parent not found: <87r47nshuh.fsf@mars.lan>]
* 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
[parent not found: <87mwibrs5u.fsf@mars.lan>]
* 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
[parent not found: <87ioszrr6l.fsf@mars.lan>]
* 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-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
* 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
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).