leer datos desde GitHub en R

Hola, esta es una entrada muy breve, donde describo cómo leer datos desde un fichero alojado en un repositorio GitHub en la Web. El problema tiene dos partes. 

Primero, nos aseguramos de acceder al formato original de los datos. Por ejemplo, para leer el fichero https://github.com/eead-csic-compbio/bioinformatics/blob/main/test_data/Ensembl_repeats.tsv , en formato TSV (texto separado por tabuladores), podemos obtener la ruta o path al fichero crudo haciendo click en el botón "Raw" a la derecha en la figura, que en este caso es https://raw.githubusercontent.com/eead-csic-compbio/bioinformatics/main/test_data/Ensembl_repeats.tsv :

 

Segundo, leemos el fichero directamente pasando su URL a la función read.csv de R indicando que las columnas están separadas por tabuladores y que la primera línea del fichero son los nombres de las columnas:

datos <- read.csv(
  url("https://raw.githubusercontent.com/eead-csic-compbio/bioinformatics/main/test_data/Ensembl_repeats.tsv"),
  sep="\t", 
  header=TRUE) 

 

Hasta pronto,

Bruno

Consultas eficientes sobre ficheros TSV con SQL

Hola,
estos dias está de visita mi colega Valerie y me preguntaba si había alguna manera de seleccionar de manera eficiente filas de un fichero con millones de líneas, en su caso con columnas separadas por tabuladores (TSV). Yo le sugerí una base de datos relacional y como ella escribe sobre todo en python lo hicimos así, con sqlite:

import sqlite3

# local file with data to populate SQL db
data_file = 'data4sql.tab'

# persistent database file to be created, returns a Connection
conn = sqlite3.connect('example.sqlite.db')

# Cursor object to perform SQL commands on that connection
c = conn.cursor()

# create a table as a list of columns and their types (NULL, INTEGER, REAL, TEXT, BLOB)
# NOTE: the columns should match those in data_file
tb_name = 'mytable'
c.execute('CREATE TABLE {tn}\
             (IMG text, scaffold_id text, GC real, length integer, locus_tag text,\
             IMG_gene_id text, gene_type text, gene_start integer, gene_stop integer,\
             gene_length integer, depth real)'.format(tn=tb_name))

with open(data_file) as fin:
    for line in fin:
        cols = line.rstrip().split("\t")

        # make sure empty values are changed to NULL
        cols=[None if x=='' else x for x in cols]
        
        # Insert a row of data
        c.execute("INSERT INTO {tn} VALUES (?,?,?,?,?,?,?,?,?,?,?)".format(tn=tb_name),cols[0:11])

# save (commit) the changes
conn.commit()

# example queries, any valid SELECT SQL can be used
c.execute('SELECT * FROM {tn} WHERE depth >10'.\
        format(tn=tb_name))
for line in c.fetchall():
   print(line)

# close and exit
conn.close()

En realidad esto se debería separar en dos programas, uno para crear la base de datos y otro para hacer consultas sobe ella. El código es sencillo, pero deberás conocer un mínimo de sintaxis SQL para entender cómo crear tablas, con sus tipos, y cómo hacer consultas. Fíjate que los valores perdidos los sustituimos por un objeto None,
saludos,
Bruno y Valerie

Unión de varios ficheros ordenados (sort merge)

Hola,
en entradas pasadas, como ésta de 2010, ya hemos hablado de cómo ordenar resultados tabulares de BLAST con GNU sort. Entre tanto sort ha ido evolucionando y las versiones actuales, por ejemplo la que viene instalada por ejemplo en Ubuntu 18, ahora permite distribuir el trabajo en varias hebras:

--parallel=N    change the number of sorts run concurrently to N

Sin embargo, cuando queremos ordenar de manera global tablas numéricas distribuidas en varios ficheros internamente ordenados seguiremos usando una opción que ya teníamos en 2010:

-m, --merge     merge already sorted files; do not sort

A pesar de ahorrarse ordenar los ficheros internamente, cuando el número de ficheros es grande esta tarea se vuelve muy lenta. En el ejemplo ordenamos 100 ficheros separados por tabuladores (TSV) por las columnas numéricas 1 y 11:

time sort -S500M -s -k1g -k11g -m ficheros*.sorted > all.sorted

real 20m15.793s
user 19m46.045s
sys 0m18.724s

La clave para mejorar es eliminar el uso de ficheros temporales, leyendo de los 100 ficheros a la vez:

time sort --batch-size=100 -S500M -s -k1g -k11g -m ficheros*.sorted > all.sorted

real 13m49.672s
user 13m29.370s
sys 0m9.972s

De esta manera cuesta aproximadamente un tercio menos, y como se ve en el último ejemplo no sirve de nada aumentar la memoria del proceso de 500MB a 5 GB. De la misma manera, en mis pruebas no merece la pena aumentar el números de hebras concurrentes:

time sort --batch-size=100 S5G -s -k1g -k11g -m ficheros*.sorted > all.sorted

real 14m9.339s
user 13m49.315s
sys 0m10.705s

Hasta luego,
Bruno

extraer TSV de todas las hojas de un libro excel

Hola,
cuando intercambiamos datos en al laboratorio a menudo usamos libros Excel, en formato XLSX, con varias hojas. Ahora bien, si luego queremos convertirlos a ficheros con valores separados por tabuladores (TSV) o comas (CSV), MS Excel sólo te permite hacerlo de hoja en hoja, lo cual es un poco latoso para libros gordos. Aquí os pongo un script en Perl para hacer esta tarea desde el terminal, que requiere instalar el módulo Spreadsheet::ParseXLSX, por ejemplo con:

$ sudo cpan -i Spreadsheet::ParseXLSX

El código es:

#!/usr/bin/perl -w
# get separate tab-separated (TSV) files from sheets in Excel .xlsx file 
use strict;
use Spreadsheet::ParseXLSX;

die "# usage: $0 <infile.xlsx> \n" if(!$ARGV[0]);

my $parser = Spreadsheet::ParseXLSX->new();
my $book = $parser->parse($ARGV[0]);
foreach my $sheet (@{$book->{Worksheet}})
{
  open(TSV,'>',"$sheet->{'Name'}.tsv") || 
    die "# cannot create $sheet->{'Name'}.tsv: $!\n";

  foreach my $row ($sheet->{'MinRow'} .. $sheet->{'MaxRow'})
  {
    foreach my $col ($sheet->{'MinCol'} ..  $sheet->{'MaxCol'})
    {
      print TSV "$sheet->{'Cells'}->[$row]->[$col]->{'Val'}\t";
    } print TSV "\n";
  }

  close(TSV);
}

Si guarmos el código en un fichero de nombre xlsx2multitab.pl, podemos invocarlo de la siguiente manera:

$ perl xlsx2multitab.pl libro.xlsx

En el directorio actual se guardará un fichero .tsv por cada hoja del libro,
hasta luego,
Bruno

merge TSV files as Excel sheets (multitab2xls.pl)

Buenas,
hoy me he vuelto a encontrar con una tarea reincidente, la de juntar varios ficheros con datos separados por tabuladores (formato TSV) en un sólo fichero Excel para compartirlo más fácilmente con otros colegas.  Como decía esto me pasa con cierta frecuencia, y por eso hoy dediqué 5 minutos a escribir unas líneas de Perl que permiten automatizar esta tarea, previa instalación del módulo Spreadsheet::WriteExcel .

Para instalar dicho módulo es suficiente con:

$ sudo cpan -i  Spreadsheet::WriteExcel

El código del script multitab2xls.pl es el siguiente:

 #!/usr/bin/perl -w  
 use strict;  
 use Spreadsheet::WriteExcel;  
 use File::Basename;  
 die "# usage: $0 <target.xls> <*.tab> <*.tsv>\n" if(!$ARGV[1]);  
 my ($xlsname,$files) = (shift(@ARGV),0);  
 my $xlsbook = Spreadsheet::WriteExcel->new($xlsname);  
 my $title_sheet = $xlsbook->add_worksheet('original files');  
 foreach my $tabfile (@ARGV) {  
     $title_sheet->write($files++,0,$tabfile);  
   my $sheet = $xlsbook->add_worksheet(substr(basename($tabfile,qw(.tsv .tab)),-30));  
     open(TAB,$tabfile) || die "# cannot read $tabfile, exit\n";  
     while(<TAB>) {  
         $sheet->write_row($.-1, 0, [split(/\t/)]);  
     }  
     close(TAB);  
 }  

Y se ejecutaría de esta manera:

$ perl multitab2xls.pl outfile.xls folder/*.tab 

Un saludo,
Bruno