Convertire TSV in XLSX

Pipeline di sequenziamento, tabelle di espressione differenziale, file di annotazione, log di strumenti, download da search console e report di piattaforme pubblicitarie escono tutti separati da tabulazioni, e la maggior parte non offre alternativa. La tabulazione è stata scelta perché descrizioni di geni, nomi di campagne e titoli di pagina sono pieni di virgole.

Non è un formato di lavoro. Il file non porta tipi di colonna, larghezze, riquadro bloccato o difesa contro ciò che succede dopo, cioè che qualcuno lo apre in Excel perché è lì che avverrà davvero l’analisi. L’apertura è il passo pericoloso, ed è quello che sostituisce questa conversione.

Excel che importa testo applica la propria interpretazione a ogni valore, e parte di quell’interpretazione è un parser di date con una rete molto ampia. 1-2 diventa il due gennaio. MARCH1 e SEPT1 diventano date. 2/3 diventa una data. Lunghe stringhe di cifre perdono gli zeri iniziali e quelle molto lunghe vengono rese in notazione scientifica.

Non è una voce o un vecchio bug. È stato misurato nella letteratura genomica come fenomeno che colpisce una frazione sostanziale delle tabelle supplementari pubblicate, ed è stato infine risolto rinominando un insieme di geni umani piuttosto che sistemando il foglio di calcolo — il che dice quanto sia difficile annullarlo una volta accaduto.

La conversione legge il file separato da tabulazioni e scrive un foglio di calcolo, e non esegue mai il parser di date di Excel. Un valore 1-2 resta i quattro caratteri 1-2, SEPT1 resta SEPT1, e 2024-03-11 resta quella stringa. Ognuno viene scritto nella cella come testo, con la cella che porta il proprio tipo.

Quest’ultima parte è ciò che rende la protezione duratura. Una cella XLSX registra che tipo di valore contiene, quindi quando Excel apre il foglio di calcolo mostra ciò che c’è piuttosto che rianalizzarlo. La coercizione avviene solo all’importazione di testo non tipizzato, e quando il foglio di calcolo raggiunge Excel non resta testo non tipizzato.

Non è un rifiuto generale di interpretare qualsiasi cosa. Un campo che legge come numero semplice diventa una cella numerica, ciò che volete per conteggi, valori p, variazioni di espressione, impressioni e costi — arrivano pronti per ordinare e calcolare.

Il costo ricade sui codici con zeri iniziali. 00123 diventa 123, e una posizione su piastra o un riferimento interno che si affida al padding lo perde. Vale la pena controllare una colonna di codici nel foglio di calcolo prima di lavorarci.

Lo stesso rifiuto che salva 1-2 si applica a una vera colonna data: 2024-03-11 viene scritta come testo, non come valore data, quindi non ordinerà cronologicamente e l’aritmetica delle date non funzionerà finché non viene convertita dentro Excel.

È il lato deliberato dello scambio, ed è il verso giusto. Trasformare una colonna testo in date in Excel è un’operazione che eseguite consapevolmente su una colonna che avete guardato — selezionate la colonna, Dati, Testo in colonne, e impostate esplicitamente il formato data.

Un file separato da tabulazioni contiene una tabella, quindi il foglio di calcolo ha un foglio di lavoro. Prende il nome dal file con l’estensione rimossa, troncato ai trentuno caratteri consentiti a un nome di foglio.

La riga di intestazione diventa la prima riga del foglio e fissa le colonne: ogni riga successiva viene indicizzata contro di essa, quindi un campo extra che appare a metà non ottiene una colonna propria. I suoi valori in eccesso vengono raccolti sotto una colonna chiamata __parsed_extra.0, che è il foglio che vi dice che l’esportazione è irregolare.

Un foglio di lavoro contiene 1.048.576 righe inclusa l’intestazione, e 16.384 colonne. È un limite del formato file piuttosto che di questa conversione, e nessuno strumento può scrivere un foglio più grande.

Matrici di espressione, log di eventi ed esportazioni complete di parole chiave superano regolarmente quella linea. Quando succede, la risposta onesta è che un foglio di calcolo è lo strumento sbagliato: filtrate o aggregate la tabella prima di convertire, oppure portate lo stesso file in Parquet e interrogatelo in DuckDB o pandas.

Una colonna descrizione o annotazione contenente un ritorno a capo è genuinamente pericolosa in un file separato da tabulazioni: la riga finisce dove il valore doveva continuare, e ogni conteggio a valle è sbagliato. Una tabulazione incollata in un valore fa la stessa cosa più silenziosamente.

Un foglio di calcolo non ha questa fragilità. Un ritorno a capo dentro una cella è un ritorno a capo dentro una cella, e una tabulazione è un carattere ordinario. Se un’esportazione separata da tabulazioni ha dato conteggi di righe incoerenti fra strumenti, convertirla è una diagnosi ragionevole oltre che una correzione.

Entrambe le metà girano su questa pagina: il parser separato da tabulazioni è JavaScript ordinario e lo scrittore del foglio di calcolo è una libreria caricata al bisogno. Nessuna richiesta porta il file, quindi un insieme di risultati non pubblicati, i dati di campagna di un cliente o una tabella collegata a pazienti non vengono copiati a terzi.

Il limite è la memoria piuttosto che un piano. L’intera tabella viene tenuta contemporaneamente mentre il foglio di calcolo viene assemblato, quindi decine di megabyte convertono senza cerimonie e centinaia è dove una scheda del browser inizia a soffrire.

Tenetelo se il passo successivo è uno script. R lo legge con read.delim, pandas con sep="\t", ed entrambi permettono di dichiarare esplicitamente i tipi di colonna, una garanzia più forte di qualsiasi conversione. Un foglio di calcolo non aggiunge nulla a quella pipeline e toglie la possibilità di fare un diff.

Tenetelo comunque come copia d’archivio. La conversione legge i valori simili a numeri come numeri, quindi il foglio di calcolo non è un record fedele byte per byte dell’esportazione, e il file originale è l’unica cosa che può risolvere una discussione su cosa conteneva una colonna.