Re: [O] wishful thinking: using SQL to process tables. in the meantime, use R

2016-07-21 Thread Thierry Banel

  
  
Le 21/07/2016 03:13, dmg a écrit :


  
Hi
  everybody,


I
  was wondering if there was a way to join two tables given
a
  common column. I searched but found nothing.


it
  would be awesome to be able to process tables in SQLITE.
something
  like this:


#+BEGIN_SRC
  sqlite :var a=table1 b=table2  :colnames yes
 
  select * from $a join $b using column;


  #+END_SRC
  
  
  I think it is not that hard. The infrastructure is there
already.
  It is just a matter of creating temp tables (this is the
major part missing
  which implies making a create statement from the table,
but given
  that sqlite is very type agnostic, it might not be
hard), 
  load them from the CSV files
  the execute the block. A db parameter might be needed
  for a scratch database file, but it could be a temporary
one if
  none is provided.
  
  
  But in the meantime, it occurred to me, it is simple in R
to do the join
  and might be useful to others:




  #+BEGIN_SRC
R :var a=table1 b=table2 :results value :colnames yes
  merge(a,b,by.x="column")
  #+END_SRC
  
  
  merge
can do left joins, right joins, full joins, joins, 
  
  
  https://stat.ethz.ch/R-manual/R-devel/library/base/html/merge.html
  
  

  but
  there is nothing like the power of SQL to process tables,
  though.



-- 
--dmg
  
  ---
  Daniel M. German
  http://turingmachine.org

  


You may want to try orgtbl-join
  https://github.com/tbanel/orgtbljoin
Available on Melpa (M-x package-install orgtbl-join)
It is pure Emacs (no external dependencies)



  




[O] wishful thinking: using SQL to process tables. in the meantime, use R

2016-07-20 Thread dmg
Hi everybody,

I was wondering if there was a way to join two tables given
a common column. I searched but found nothing.

it would be awesome to be able to process tables in SQLITE.
something like this:

#+BEGIN_SRC sqlite :var a=table1 b=table2  :colnames yes
  select * from $a join $b using column;
#+END_SRC

I think it is not that hard. The infrastructure is there already.
It is just a matter of creating temp tables (this is the major part missing
which implies making a create statement from the table, but given
that sqlite is very type agnostic, it might not be hard),
load them from the CSV files
the execute the block. A db parameter might be needed
for a scratch database file, but it could be a temporary one if
none is provided.

But in the meantime, it occurred to me, it is simple in R to do the join
and might be useful to others:

#+BEGIN_SRC R :var a=table1 b=table2 :results value :colnames yes
merge(a,b,by.x="column")
#+END_SRC

merge can do left joins, right joins, full joins, joins,

https://stat.ethz.ch/R-manual/R-devel/library/base/html/merge.html

but there is nothing like the power of SQL to process tables, though.

-- 
--dmg

---
Daniel M. German
http://turingmachine.org