Use R to write multiple tables to a single Excel file

The possibility of saving several tables in a single file is a nice feature of Excel. When sharing results with colleagues, it might be useful to compact everything in a single file.
As a bioinformatician, I am too lazy to do that manually, and I searched the web for tools that allow doing that.
I found out that there are at least two R packages that work well: xlsx and openxlsx.
First of all, let’s install the packages.


Now, let’s try to write some random stuff using openxlsx.

start_time <- Sys.time()
OUT <- createWorkbook()
for(aaa in 1:20)
addWorksheet(OUT, sname)
writeData(OUT, sheet = sname, x = mdf)
end_time <- Sys.time()

Running time on my machine is around 3 seconds.
Now let's try to save some very similar random stuff using xlsx


start_time <- Sys.time()
for(aaa in 1:20)
end_time <- Sys.time()


Running time is approximately 25s.

It looks like openxlsx is much faster than xlsx, and so I currently prefer it.
If you try to use openxlsx to save with the name of an existing file, you will get an error message.

Error in saveWorkbook(OUT, outfile) : File already exists!

This might be useful if you want to be sure you never overwrite files by mistake, but it can also be annoying. To avoid this error message and allow overwriting of files, you just have to use the following command:



Thanks to comments from Roman and code from Mihal, it was very easy to also test a third option, writexl.

Code below:

start_time <- Sys.time()
for(aaa in 1:20)
end_time <- Sys.time()

Running time is less than 2 seconds.

Wow, how many option for writing excel files. And I still have to try XLConnect, as suggested by Ellie!

Note: I had an experience (which I am no longer able to reproduce, and maybe I am not remembering well!) of openxlsx being unable to save files that xlsx could save without any problem. With this in mind, I kept note of both approaches, hoping that at least one would work in any situation!


Stouffer’s meta-analysis with weight and direction effect in R

I often have to perform meta-analysis of past experiments for which the only info I have is the fold change and the p-value (of any measure you may imagine: species richness, gene expression, depth of coverage, plates of carbonara eaten in 5 minutes, everything).
The hardest thing to find out for me was how to take the direction of the changes into account. E.g. if monday I eat 10 carbonara more than my brother (p=0.01), on tuesday 10 more (p=0.01), on wednesday 5 more (p=0.1), on thursday 10 less (p=0.01), on friday ten less (p=0.01) and on saturday 5 less (p=0.1), a standard Stouffer meta-analysis would return a highly significant p-value, completely disregarding the fact that the significant changes were half in my favor, and half in favor of my brother.
How can I take into account the information on the direction?
I had no idea on how to proceed, and I wasn’t able to find any reference (I am not an expert of meta-analysis), but then I was so lucky to stumble upon the manual page of the software package METAINTER for performing meta-analysis.

The authors described there a method to perform Stouffer meta-analysis accounting for the direction of the effects. Their explanation was so clear that it was easy for me to write a simple function in R: I paste it below.

signed.Stouffer.meta <- function(p, w, sign) { # p is a vector of p-values
  if (missing(w)) {
    w <- rep(1, length(p))/length(p)
  } else {
    if (length(w) != length(p))
      stop("Length of p and w must equal!")
  if(length(p)==1) Zi1)
  Z  <- sum(w*Zi)/sqrt(sum(w^2))
  p.val <- 2*(1-pnorm(abs(Z)))
  return(c(Z = Z, p.value = p.val))

How to cut an mp3 audio with Audacity

Here I will explain how to cut an mp3 audio file using Audacity. Audacity is a fairly simple sound recording/editing software available on Ubuntu and Windows.
For non experts (like me) Audacity is a friendly tool to cut mp3 to the desired sound regions.
Here are the steps.

1) Open Audacity. Select File -> Open, and then browse the file you wish to cut (test.mp3 in this case)
2) With the mouse, go over the region showing the song length. Imagine you want to cut from minute 2:25 to minute 3:00 (approximately!). Position the cursor on minute 2:25, left click, then drag the mouse to minute 3:00. The region will be grey (see Figure).
Figure 1
3) Now just press CTRL-X and the region will be cut from the file!
Figure 2
4) At this point you can save the project or export the file as an mp3 as you will normally do for any sound record (File -> Save Project or File -> Export Audio…) and you will have the cut file on your PC.

Come ridurre le dimensioni di un video con VLC

Mi è accaduto più di una volta di dover ridurre le dimensioni di  video molto pesanti (per consentire poi una facile condivisione o rimaneggiamento).

Nonostante io non sia un esperto dell’argomento, me la sono sempre cavata egregiamente con l’uso di VLC. Immagino che tutti conosciate questo software gratuito per la visualizzazione di video (e molto altro). Altrimenti, vi consiglio di provarlo. Potete scaricarlo da qui.

Riportoqui la semplice procedura, un po’ come promemoria, un po’ con la speranza che possa essere utile a qualcuno.

Vi mostrerò la procedura su sistema operativo Ubuntu, ma i passi sono gli stessi anche su sistemi operativi Windows. L’esempio è riferito al solo compito di ridurre la dimensione di un video mp4 (senza conversione di formato). Comunque, il salvataggio in altro formato è molto semplice.

1) Aprite VLC
2) Dal menu “Media”, selezionate “Convert/Save”
3) Comparirà il pop-up “Open Media” (Figura 1). Dal Tab “File”, cliccate su “Add” per scegliere il file che volete ridurre.

4) Una volta aggiunto il file, il bottone “Convert/Save” diventerà attivo. Cliccateci sopra  (Figura 2)

5) A questo punto, vi sarà possibile settare alcuni parametri relativi alla conversione. Per farlo dovete cliccare sull’icona che ho evidenziato con un contorno rosso in Figura 3.

6) Si aprirà una nuova finestra. Selezionate il tab “Video Codec” e assicuratevi che i valori che compaiono nel tab “Encoding parameters” (parametri di encoding) siano settati sugli stessi valori mostrati in Figura 4. Giocare un po’ con Bitrate e Frame Rate potrebbe essere utile, ma fatelo a vostro rischio: molto spesso il risultato è che ci si trova con un video di qualità molto bassa!

7) A questo punto, cliccate sul tab “Resolution”. Per utenti non esperti (come me) questo è il metodo più facile e sicuro di ridurre la dimensione del video a livelli accettabili senza causare orrende diminuzioni di qualità. Nella mia esperienza, settare il parametro “Scale” a 0.75 dà in  genere buoni risultati, consentendo di ridurre decisamente la dimensione del file senza causare eccessive perdite nella qualità. In genere utilizzo questo valore. Settare “Scale”  più bassi consente di ridurre ulteriormente la dimensione del file, a spese della qualità. La scelta finale dipende dal livello di qualità e riduzione di dimensione che si desidera. In generale, non c’è bisogno di agire su altri settaggi. Potete cliccare sul bottone “Save” in basso a destra (Figura 5).

8) Adesso, selezionate il percorso e il nome del file che volete creare, premete “Start” e attendete; in alcuni minuti (a seconda della dimensione del file), il vostro file ridotto sarà pronto. .

Nota: Nel caso queste istruzioni non siano state utili, non demordete. Online ci sono decine di tutorial che offrono soluzioni lievemente diverse. Per utenti linux, ad esempio, ci sono varie soluzioni sul sempre valido AskUbuntu

How to downsample a video with VLC

I often find in a situation in which I receive very large videos that need to be compressed for being transferred or somehow managed.

I am not expert in the issue, so I found it very useful the use of VLC.

Here is the procedure, which is more a note to self, but will hopefully help people facing the same problem.

Let’s face the easy steps. These are valid both for Windows and Ubuntu, and are focused on the task of reducing the size of an mp4 video (no format conversion). Hoewever, extension to other tasks is simple and I might treat it in future posts.

1) Open VLC
2) From the Media menu, select “Convert/Save”
3) From the the “Convert” popup window click on Add, to select the file you want to downsample (Figure 1)

4) Once you have added the file, the Convert/Save button will become active. Click on it (Figure 2)

5) At this point, you will have the opportunity to check/edit the conversion settings, by clicking on the icon highlighted in red in Figure 3.

6) Click on the Video Codec tab and make sure that the Encoding parameters are as shown in Figure 4. Playing with Bitrate and Frame Rate parameters might be an option, but if you are not expert you might end up with a very low quality video.

7) Click on the Resolution tab. For non-experst (like me) this is the safest way to downsample the size of video. Setting a scale of 0.75 will generally end up in a significant reduction in file size without causing great loss of quality. I use this value often. Lower values will result in smaller files and lower quality. The final choice is up to you. You don’t need to act on other settings. You can click save on the bottom right corner of the window (Figure 5)

8) Now, just select the output file, click start and wait a little bit.


Note: Further details on how to downsample a video in Ubuntu can be found on the always helpful AskUbuntu

How to remove GRUB from Ubuntu-Vista dual boot

Preface: when it comes to installing operating systems I am a script kiddie. Every time I find a solution to some problem I am so happy I want everyone to know. This is why I am publishing this post, although, as you will see, all of the merit goes to a “mysterious” user of askubuntu, anwar.
I was in a situation in which I had an old laptop with a small partition reserved for Linux Ubuntu and a large one for windows.
I decided to give the PC to my mum, who will never use Ubuntu, and she likely would have troubles in managing dual boot.
Thus, I set out to remove grub.

Note: I do not have any install disk, nor recovery disk. Maybe I had them, once, not sure. In case you have a recovery disk, then things are easier.

Luckily enough, I found the answers here on askubuntu (answer by Anwar):

I repost it (with minor modification), in case somenone else may need it.

1) First download bootsect.exe from here into the Download directory (Warning the download will bring you through some redirection, but at the end you will get the program).
2) Rename the file to bootsect.exe (the name should be _Getintopc.com_bootsect.exe)
3) press the windows key, type cmd.exe and on the result (The command prompt) start it as an “Administrator” by right clicking on the cmd icon, and select Run as Administrator.
4) In the new cmd.exe window, cd to your download directory. for example cd C:\Users\Lalu Patel\Downloads. _assuming your user name is “Lalu Patel”.
5) Then enter this command. bootsect.exe /nt60 ALL /force /mbr.
6) Restart your system to see that, Windows is automatically loading without grub.

At this point, if you need to resize your partition, just go to askubuntu and follow the instructions

Visualize KEGG pathway and fold enrichment

As a useful note to self, I paste here an easy example on the use of the pathview package by Weijun Luo to plot the log fold change of gene expression across a given KEGG pathway. This example is on Vitis vinifera (as the prefix vvi can suggest), but the approach is general.
Basically, you just need to feed pathview the pathway argument and a argument. In this case, is a named vector, with names being the (entrez) gene names, and the value is the log Fold Change.
I selected a pathway for which KEGG has a nice representation, you might not be so lucky!


The result should be something like this: