From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp1 ([2001:41d0:2:bcc0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms11 with LMTPS id +LtKAalnM2BSawAA0tVLHw (envelope-from ) for ; Mon, 22 Feb 2021 08:13:29 +0000 Received: from aspmx1.migadu.com ([2001:41d0:2:bcc0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp1 with LMTPS id KLKhOKhnM2BtcgAAbx9fmQ (envelope-from ) for ; Mon, 22 Feb 2021 08:13:28 +0000 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 440E81FBC0 for ; Mon, 22 Feb 2021 09:13:28 +0100 (CET) Received: from localhost ([::1]:35088 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1lE6LP-0003OC-59 for larch@yhetil.org; Mon, 22 Feb 2021 03:13:27 -0500 Received: from eggs.gnu.org ([2001:470:142:3::10]:47472) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1lE6Ke-0003Nx-Si for emacs-orgmode@gnu.org; Mon, 22 Feb 2021 03:12:40 -0500 Received: from hiwela.pair.com ([209.68.5.201]:31525) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1lE6Kd-0008VV-36 for emacs-orgmode@gnu.org; Mon, 22 Feb 2021 03:12:40 -0500 Received: from hiwela.pair.com (localhost [127.0.0.1]) by hiwela.pair.com (Postfix) with ESMTP id A02169805B1; Mon, 22 Feb 2021 03:12:37 -0500 (EST) Received: from minshall-entroware-apollo.cliq.com (unknown [95.8.212.136]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by hiwela.pair.com (Postfix) with ESMTPSA id 70B4F8F0957; Mon, 22 Feb 2021 03:12:37 -0500 (EST) Received: from apollo2.minshall.org (localhost [IPv6:::1]) by minshall-entroware-apollo.cliq.com (Postfix) with ESMTP id EAB3B630C3; Mon, 22 Feb 2021 11:12:34 +0300 (+03) From: Greg Minshall To: "Cook, Malcolm" Subject: Re: state of the art in org-mode tables e.g. join, etc In-reply-to: Your message of "Mon, 22 Feb 2021 06:52:30 +0000." X-Mailer: MH-E 8.6+git; nmh 1.7.1; GNU Emacs 27.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <706394.1613981554.1@apollo2.minshall.org> Date: Mon, 22 Feb 2021 11:12:34 +0300 Message-ID: <706395.1613981554@apollo2.minshall.org> Received-SPF: softfail client-ip=209.68.5.201; envelope-from=minshall@umich.edu; helo=hiwela.pair.com X-Spam_score_int: -11 X-Spam_score: -1.2 X-Spam_bar: - X-Spam_report: (-1.2 / 5.0 requ) BAYES_00=-1.9, SPF_HELO_NONE=0.001, SPF_SOFTFAIL=0.665 autolearn=no autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: Tim Cross , org-mode-email , John Kitchin Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org Sender: "Emacs-orgmode" X-Migadu-Flow: FLOW_IN X-Migadu-Spam-Score: -2.27 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=fail reason="SPF not aligned (relaxed), No valid DKIM" header.from=umich.edu (policy=none); spf=pass (aspmx1.migadu.com: domain of emacs-orgmode-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=emacs-orgmode-bounces@gnu.org X-Migadu-Queue-Id: 440E81FBC0 X-Spam-Score: -2.27 X-Migadu-Scanner: scn0.migadu.com X-TUID: tOxuW6CT/pih Malcolm, > Checkout what R sqldf package makes easy: very nice! Greg ps -- (feeling a challenge... :) for base R, dplyr::inner_join, the following seem to work (i apologize that i don't know how people embed org-frags in e-mail, or how important that format might be?) ---- #+NAME: original | Day | Color | Level | Quantity | |-----------+-------+-------+----------| | 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 | #+PROPERTY: header-args:R :session *R* #+begin_src R :results none library(dplyr) #+end_src #+begin_src R :var original=original :colnames yes as.data.frame(table(Color=original$Color)) #+end_src #+RESULTS: | Color | Freq | |-------+------| | Blue | 7 | | Red | 7 | *** join example Example from https://github.com/tbanel/orgtbljoin #+name: nutrition | type | Fiber | Sugar | Protein | Carb | |----------+-------+-------+---------+------| | eggplant | 2.5 | 3.2 | 0.8 | 8.6 | | tomatoe | 0.6 | 2.1 | 0.8 | 3.4 | | onion | 1.3 | 4.4 | 1.3 | 9.0 | | egg | 0 | 18.3 | 31.9 | 18.3 | | rice | 0.2 | 0 | 1.5 | 16.0 | | bread | 0.7 | 0.7 | 3.3 | 16.0 | | orange | 3.1 | 11.9 | 1.3 | 17.6 | | banana | 2.1 | 9.9 | 0.9 | 18.5 | | tofu | 0.7 | 0.5 | 6.6 | 1.4 | | nut | 2.6 | 1.3 | 4.9 | 7.2 | | corn | 4.7 | 1.8 | 2.8 | 21.3 | #+name: recipe | type | quty | |----------+------| | onion | 70 | | tomatoe | 120 | | eggplant | 300 | | tofu | 100 | #+begin_src R :var recipe=recipe :var nutrition=nutrition :colnames yes dplyr::inner_join(nutrition, recipe) #+end_src #+RESULTS: | type | Fiber | Sugar | Protein | Carb | quty | |----------+-------+-------+---------+------+------| | eggplant | 2.5 | 3.2 | 0.8 | 8.6 | 300 | | tomatoe | 0.6 | 2.1 | 0.8 | 3.4 | 120 | | onion | 1.3 | 4.4 | 1.3 | 9 | 70 | | tofu | 0.7 | 0.5 | 6.6 | 1.4 | 100 |