2025-01-23

datascience / org-mode / emacs / source-code / sql / R

Usually I prepare some personal reports and some shorts analysis as well. The Jupyter-Notebook is an amazing tool, however sometimes I just wanna a simple plain text. I use EMACS org-mode to organize my agenda and to manage my projects. So, Why not use org-mode tables in EMACS?

At the begin I thought it was complicate, but actually not. With source code blocks in EMACS is possible to run several languages. In this case, the languages are SQL and R. Let’s go.

Ensure you have the proper babel support setup

In in .emacs or init.el:

(org-babel-do-load-languages
 'org-babel-load-languages
 '((sqlite . t)
   (R . t)
   (python . t)
   (emacs-lisp . t)))

Create a table

This example is to summarize some investments:

#+name: invest
| TICKET   | AMOUNT | PRICE_AGV | VALUE_INVEST | SECTOR    |  PERC |
|----------|--------|-----------|--------------|-----------|-------|
| STOCK_01 |    170 |    100.06 |     17009.56 | FINANC    | 17.93 |
| STOCK_02 |     85 |    102.35 |      8699.90 | TECH      |  9.17 |
| STOCK_03 |     30 |    114.84 |      3445.33 | TECH      |  3.63 |
| STOCK_04 |    600 |      8.89 |      5335.69 | FINANC    |  5.62 |
| STOCK_05 |    650 |      9.56 |      6215.96 | REALSTATE |  6.55 |
| STOCK_06 |    155 |    106.14 |     16451.32 | REALSTATE | 17.34 |
| STOCK_07 |    420 |     89.83 |     37730.18 | REALSTATE | 39.76 |

Run some code

Now it necessary to know how much money and the percentage by each sector. All you need is a source-code block and a little bit of sql:

#+begin_src sqlite :db /tmp/rip.db :colnames no :var orgtable=invest :header on :exports both value
drop table if exists invest;
.import $orgtable invest
select SECTOR, SUM(VALUE_INVEST) AS TOT_VALUE,
ROUND(100*SUM(VALUE_INVEST) / (SELECT SUM(VALUE_INVEST) FROM invest),2) AS PERC
from invest GROUP BY SECTOR
#+end_src

#+RESULTS:
| SECTOR    | TOT_VALUE |  PERC |
| FINANC    |  22345.25 | 23.55 |
| REALSTATE |  60397.46 | 63.65 |
| TECH      |  12145.23 |  12.8 |

You can run R as well to achieve the same result:

#+begin_src R :file agetable.tsv :var orgtable=invest :colnames yes :exports both value :output-to-buffer:t
agreg <- as.data.frame(aggregate(VALUE_INVEST ~ SECTOR, FUN=sum, data=orgtable))
colnames(agreg) = c("SECTOR", "TOT_VALUE")
agreg$PERC <- round(100 * agreg$TOT_VALUE / sum(agreg$TOT_VALUE), 2)
print(agreg, type="")
#+end_src

#+RESULTS:
| SECTOR    | TOT_VALUE |  PERC |
|-----------|-----------|-------|
| FINANC    |  22345.25 | 23.55 |
| REALSTATE |  60397.46 | 63.65 |
| TECH      |  12145.23 |  12.8 |

It is just an appetizer. You can do much more!

Regards!

Computing data in org-mode table by means sql and R

a quick and pratical option

Create a table

Run some code

References

Share!