From mboxrd@z Thu Jan 1 00:00:00 1970 From: John Kitchin Subject: Re: Import CSV file, remove columns, print table Date: Mon, 16 Sep 2019 09:31:46 -0400 Message-ID: References: <87r24knwwi.fsf@hornfels.zedat.fu-berlin.de> <87sgow1kah.fsf@hornfels.zedat.fu-berlin.de> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="000000000000f97aa50592aba009" Return-path: Received: from eggs.gnu.org ([2001:470:142:3::10]:42188) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1i9r6s-0001Y4-JV for emacs-orgmode@gnu.org; Mon, 16 Sep 2019 09:32:08 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1i9r6q-00045v-GM for emacs-orgmode@gnu.org; Mon, 16 Sep 2019 09:32:06 -0400 Received: from mail-wr1-x434.google.com ([2a00:1450:4864:20::434]:36472) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1i9r6p-00042q-Vd for emacs-orgmode@gnu.org; Mon, 16 Sep 2019 09:32:04 -0400 Received: by mail-wr1-x434.google.com with SMTP id y19so38915547wrd.3 for ; Mon, 16 Sep 2019 06:32:03 -0700 (PDT) In-Reply-To: <87sgow1kah.fsf@hornfels.zedat.fu-berlin.de> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: "Emacs-orgmode" To: Loris Bennett Cc: org-mode-email --000000000000f97aa50592aba009 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Here is another 'source block' solution, this time in python. You could so something similar in elisp. Here I use the library of babel approach so you can call it wherever you want. #+name: csv | ID | Name | Titel / Title | Vorname / First Name | Nachname / Surname | Institution | | 3 | Carol Carrot | Prof. | Carol | Carrot | University of Veg | | 1 | Alice Apple | Fr./Ms. | Alice | Apple | Universit=C3=A4t zum Apfel | | 2 | Bob Birne | Hr./Mr. | Bob | Birne | Pear University | See https://orgmode.org/worg/org-contrib/babel/library-of-babel.html #+name: signature-table #+BEGIN_SRC python :var data=3Dcsv :results value raw results =3D [[surname, firstname, ""] for _, _, _, firstname, surname, _ in data[1:]] sorted_results =3D sorted(results,key=3Dlambda row: row[1]) sorted_results =3D [[i + 1] + result for i, result in enumerate(sorted_results)] import tabulate return tabulate.tabulate(sorted_results, ['#', 'Surname', 'First name', 'Signature'], tablefmt=3D'orgtbl') #+END_SRC #+call: signature-table(data=3Dcsv) #+RESULTS: | # | Surname | First name | Signature | |---+---------+------------+-----------| | 1 | Apple | Alice | | | 2 | Birne | Bob | | | 3 | Carrot | Carol | | ----------------------------------- Professor John Kitchin Doherty Hall A207F Department of Chemical Engineering Carnegie Mellon University Pittsburgh, PA 15213 412-268-7803 @johnkitchin http://kitchingroup.cheme.cmu.edu On Mon, Sep 16, 2019 at 8:48 AM Loris Bennett wrote: > Hi John, > > Thanks - that's a nicely compact solution, albeit in the category > 'source block' and in a language I'm not very skilled at :-) > > I realise that I have slightly misstated the problem. The ID in the > imported CSV is just a key from the database - I don't need it on the > list of participants. However, it would be nice to number the > participants, who are sorted by surname. > > How would I insert a column which just numbers the row? > > Cheers, > > Loris > > John Kitchin writes: > > > You can do something like this: > > > > #+name: csv > > | ID | Name | Titel / Title | Vorname / First Name | Nachname / > Surname | Institution | > > | 1 | Alice Apple | Fr./Ms. | Alice | Apple > | Universit=C3=A4t zum Apfel | > > | 2 | Bob Birne | Hr./Mr. | Bob | Birne > | Pear University | > > | 3 | Carol Carrot | Prof. | Carol | Carrot > | University of Veg | > > > > #+BEGIN_SRC emacs-lisp :var data=3Dcsv > > (let ((table (mapcar (lambda (row) (list (nth 0 row) (nth 4 row) (nth 3 > row))) data))) > > (setf (car table) (append (car table) '("Signature"))) > > table) > > #+END_SRC > > > > #+RESULTS: > > | ID | Nachname / Surname | Vorname / First Name | Signature | > > | 1 | Apple | Alice | | > > | 2 | Birne | Bob | | > > | 3 | Carrot | Carol | | > > > > John > > > > ----------------------------------- > > Professor John Kitchin > > Doherty Hall A207F > > Department of Chemical Engineering > > Carnegie Mellon University > > Pittsburgh, PA 15213 > > 412-268-7803 > > @johnkitchin > > http://kitchingroup.cheme.cmu.edu > > > > On Fri, Sep 13, 2019 at 9:36 AM Loris Bennett < > loris.bennett@fu-berlin.de> wrote: > > > > Hi, > > > > I want to create a list of participants of an event which people can > > sign, so that I can record who actually turned up. > > > > From the registration website I can download a CSV file and import it > > into and org file: > > > > | ID | Name | Titel / Title | Vorname / First Name | Nachname = / > Surname | Institution | > > | 1 | Alice Apple | Fr./Ms. | Alice | Apple > | Universit=C3=A4t zum Apfel | > > | 2 | Bob Birne | Hr./Mr. | Bob | Birne > | Pear University | > > | 3 | Carol Carrot | Prof. | Carol | Carrot > | University of Veg | > > > > I would like to reduce this to > > > > | ID | Nachname / Surname | Vorname / First Name | Signature | > > | 1 | Apple | Alice | | > > | 2 | Birne | Bob | | > > | 3 | Carrot | Carol | | > > > > and then print it out as a LaTeX table. > > > > I can obviously write a source block of Python or R to do this, but ca= n > > I manipulate the table more directly in Org? > > > > Cheers, > > > > Loris > > > > -- > > This signature is currently under construction. > > > -- > Dr. Loris Bennett (Mr.) > ZEDAT, Freie Universit=C3=A4t Berlin Email loris.bennett@fu-berli= n.de > > > --000000000000f97aa50592aba009 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Here is another 'source block' solution, this= time in python. You could so something=C2=A0 similar in elisp. Here I use = the library of babel approach so you can call it wherever you want.



#+name: csv
| ID | Name =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | Titel / Title | Vorname / First Name | Nachname / Surname | Instit= ution =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
| =C2=A03 | Carol Carrot | Pr= of. =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Carol =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| Carrot =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Un= iversity of Veg =C2=A0 =C2=A0 |
| =C2=A01 | Alice Apple =C2=A0| Fr./Ms. = =C2=A0 =C2=A0 =C2=A0 | Alice =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| Apple =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Univers= it=C3=A4t zum Apfel |
| =C2=A02 | Bob Birne =C2=A0 =C2=A0| Hr./Mr. =C2= =A0 =C2=A0 =C2=A0 | Bob =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| Birne =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Pear= University =C2=A0 =C2=A0 =C2=A0 |


See https://orgmode.org/wor= g/org-contrib/babel/library-of-babel.html

#+name: signature-tabl= e
#+BEGIN_SRC python =C2=A0:var data=3Dcsv :results value raw
results= =3D [[surname, firstname, ""] for _, _, _, firstname, surname, _= in data[1:]]

sorted_results =3D sorted(results,key=3Dlambda row: ro= w[1])

sorted_results =3D [[i + 1] + result for i, result in enumerat= e(sorted_results)]

import tabulate
return tabulate.tabulate(sorte= d_results, ['#', 'Surname', 'First name', 'Sign= ature'], tablefmt=3D'orgtbl')
#+END_SRC



#+cal= l: signature-table(data=3Dcsv)

#+RESULTS:
| # | Surname | First n= ame | Signature |
|---+---------+------------+-----------|
| 1 | Appl= e =C2=A0 | Alice =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=
| 2 | Birne =C2=A0 | Bob =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |
| 3 | Carrot =C2=A0| Carol =C2=A0 =C2=A0 =C2=A0| =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |


------------------------------= -----
Professor John Kitchin=C2=A0
Doherty Hall A207F
Department o= f Chemical Engineering
Carnegie Mellon University
Pittsburgh, PA 1521= 3
412-268-7803


On Mon, Sep 16, 2019 at 8:= 48 AM Loris Bennett <loris= .bennett@fu-berlin.de> wrote:
Hi John,

Thanks - that's a nicely compact solution, albeit in the category
'source block' and in a language I'm not very skilled at :-)
I realise that I have slightly misstated the problem.=C2=A0 The ID in the imported CSV is just a key from the database - I don't need it on the list of participants.=C2=A0 However, it would be nice to number the
participants, who are sorted by surname.

How would I insert a column which just numbers the row?

Cheers,

Loris

John Kitchin <jkitchin@andrew.cmu.edu> writes:

> You can do something like this:
>
> #+name: csv
> | ID | Name=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Titel / Title | Vorname= / First Name | Nachname / Surname | Institution=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0|
> |=C2=A0 1 | Alice Apple=C2=A0 | Fr./Ms.=C2=A0 =C2=A0 =C2=A0 =C2=A0| Al= ice=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Apple=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Universit=C3=A4t zum Apfel | > |=C2=A0 2 | Bob Birne=C2=A0 =C2=A0 | Hr./Mr.=C2=A0 =C2=A0 =C2=A0 =C2= =A0| Bob=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Bi= rne=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Pear University=C2=A0= =C2=A0 =C2=A0 =C2=A0|
> |=C2=A0 3 | Carol Carrot | Prof.=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Ca= rol=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Carrot=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| University of Veg=C2=A0 =C2=A0 = =C2=A0|
>
> #+BEGIN_SRC emacs-lisp :var data=3Dcsv
> (let ((table (mapcar (lambda (row) (list (nth 0 row) (nth 4 row) (nth = 3 row))) data)))
>=C2=A0 =C2=A0(setf (car table) (append (car table) '("Signatur= e")))
>=C2=A0 =C2=A0table)
> #+END_SRC
>
> #+RESULTS:
> | ID | Nachname / Surname | Vorname / First Name | Signature |
> |=C2=A0 1 | Apple=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Al= ice=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
> |=C2=A0 2 | Birne=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Bo= b=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
> |=C2=A0 3 | Carrot=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Ca= rol=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
>
> John
>
> -----------------------------------
> Professor John Kitchin
> Doherty Hall A207F
> Department of Chemical Engineering
> Carnegie Mellon University
> Pittsburgh, PA 15213
> 412-268-7803
> @johnkitchin
> http://kitchingroup.cheme.cmu.edu
>
> On Fri, Sep 13, 2019 at 9:36 AM Loris Bennett <loris.bennett@fu-berlin.de&= gt; wrote:
>
>=C2=A0 Hi,
>
>=C2=A0 I want to create a list of participants of an event which people= can
>=C2=A0 sign, so that I can record who actually turned up.
>
>=C2=A0 From the registration website I can download a CSV file and impo= rt it
>=C2=A0 into and org file:
>
>=C2=A0 | ID | Name=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Titel / Title | V= orname / First Name | Nachname / Surname | Institution=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|
>=C2=A0 |=C2=A0 1 | Alice Apple=C2=A0 | Fr./Ms.=C2=A0 =C2=A0 =C2=A0 =C2= =A0| Alice=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Apple= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Universit=C3=A4t zum Apf= el |
>=C2=A0 |=C2=A0 2 | Bob Birne=C2=A0 =C2=A0 | Hr./Mr.=C2=A0 =C2=A0 =C2=A0= =C2=A0| Bob=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = | Birne=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Pear University= =C2=A0 =C2=A0 =C2=A0 =C2=A0|
>=C2=A0 |=C2=A0 3 | Carol Carrot | Prof.=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| Carol=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Carrot= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| University of Veg=C2=A0 = =C2=A0 =C2=A0|
>
>=C2=A0 I would like to reduce this to
>
>=C2=A0 | ID | Nachname / Surname | Vorname / First Name | Signature | >=C2=A0 |=C2=A0 1 | Apple=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | Alice=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
>=C2=A0 |=C2=A0 2 | Birne=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | Bob=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
>=C2=A0 |=C2=A0 3 | Carrot=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| Carol=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
>
>=C2=A0 and then print it out as a LaTeX table.
>
>=C2=A0 I can obviously write a source block of Python or R to do this, = but can
>=C2=A0 I manipulate the table more directly in Org?
>
>=C2=A0 Cheers,
>
>=C2=A0 Loris
>
>=C2=A0 --
>=C2=A0 This signature is currently under construction.
>
--
Dr. Loris Bennett (Mr.)
ZEDAT, Freie Universit=C3=A4t Berlin=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Email= loris.benn= ett@fu-berlin.de


--000000000000f97aa50592aba009--