Torre de Babel

Intercambio de datos entre Excel y R

by Francisco Charte.

Microsoft Excel es una de las herramientas más usadas por parte de los denominados power users (usuarios con conocimientos por encima del usuario medio) a la hora de resumir, analizar y representar gráficamente todo tipo de datos, generalmente con el objetivo de extraer información útil a partir de ellos. Personalmente es una aplicación que comencé a usar en la versión 95, después de haber trabajado durante años en DOS con Paradox, las hojas de cálculo de Open Access y Symphony y el mítico Lotus 1,2,3. Desde la versión 2000 hasta la 2013 he escrito una docena de libros sobre Excel, siendo una herramienta que uso prácticamente a diario para distintas tareas.

A pesar de la potencia de Excel, que incluye la posibilidad de definir nuevas funciones escritas en lenguaje VBA y el desarrollo de aplicaciones sobre esta herramienta desde Visual Studio, hay ocasiones en las ciertas tareas sencillamente resultan demasiado arduas. Aquí es donde entra en escena el lenguaje R, empleado por miles de usuarios, especialmente estudiantes de ciencias, investigadores y científicos de múltiples ramas. R es un lenguaje funcional e interpretado que, de manera interactiva, permite operar sobre listas, vectores y matrices de datos de manera extremadamente sencilla. Gran parte de su versatilidad estriba en la inmediatez para aplicar cualquier operación sobre todos los elementos de una de esas estructuras (o partes de ellas) en un solo paso, así como en la extensa biblioteca de paquetes (CRAN) disponibles para resolver prácticamente cualquier problema que se nos ocurra (obviamente siempre circunscrito al campo del análisis de datos).

El portapapeles

Son muchos los casos en que tengo información en Excel que me sería más fácil procesar con R y, en ocasiones, ocurre lo contrario. Imagino que no seré la única persona que tenga la necesidad de exportar datos desde Excel a R, o viceversa, y por ello voy a compartir aquí los métodos que he venido utilizando hasta ahora, comenzando por el portapapeles que fue el primero que usé aunque ahora ya no lo haga.

Si ejecutamos R y Excel en la misma máquina uno de los recursos más inmediatos para compartir información es el portapapeles. Copiar un rango de celdillas desde Excel al portapapeles, o pegarlo, es una acción simple: basta con seleccionarlo y usar los habituales atajos de teclado Control+C y Control+V, respectivamente. En R podemos pegar en la consola los datos copiados desde Excel, pero esto no los almacenará en ninguna variable. Aunque existe una función readClipboard para la lectura genérica de datos del portapapeles (introducir ?readClipboard en la consola para obtener ayuda sobre la misma), en este caso no nos resultaría de utilidad porque Excel por defecto copia los datos al portapapeles como líneas de texto, separando el contenido de cada celdilla con un tabulador. Lo que necesitamos, por tanto, es una función que tenga en cuenta que los datos están delimitados por un separador, en este caso el tabulador, y que además diferencie entre la primera fila, que suele contener títulos de las columnas, y el resto, que contendrán los datos propiamente dichos. Esa función es read.delim y el único parámetro que necesita es la cadena "clipboard".

El procedimiento a seguir para llevar datos desde Excel a R mediante el portapapeles consta de dos pasos:

  1. Seleccionamos en Excel los datos, incluyendo títulos, y los copiamos al portapapeles.
  2. Introducimos en la consola de R el comando rango <- read.delim("clipboard"), asumiendo que deseemos guardar los datos en la variable rango. Esta será un data.frame, basta con escribir su nombre para obtener el contenido.

Las dos imágenes siguientes muestran el procedimiento descrito.

En este momento podríamos trabajar con los datos desde R, realizando el procesamiento que necesitemos.

La transferencia de datos en sentido inverso, desde un data.frame de R a una hoja de Excel, se completa con los dos pasos siguientes:

  1. Copiamos el contenido del data.frame de R al portapapeles, usando para ello la función write.table(variable, "clipboard", sep="\t", row.names=F). A diferencia de read.delim, que asume por defecto que el separador de datos usado es el tabulador, la función write.table no aplica separador alguno, por ello es necesario indicarlo explícitamente con el parámetro sep. En cuanto al parámetro row.names, al que asignamos el valor FALSE, comunica a la función si debe o no incluir los nombres de fila del data.frame (en la imagen anterior pueden verse en la primera columna, como una secuencia de números generada automáticamente).
  2. Desde Excel obtenemos los datos sencillamente colocándonos en la celdilla de destino y pegando desde el portapapeles.

La ventaja de usar el portapapeles para intercambiar información entre Excel y R es que no es necesario usar archivos intermedios ni tampoco la instalación de paquetes adicionales en R. La principal desventaja, aparte del hecho de que requiere que las dos aplicaciones se ejecuten simultáneamente en la misma máquina, estriba en que no es un medio cómodo cuando el volumen de los datos es considerable. Imaginemos las veces que tendríamos que repetir los pasos anteriores para copiar el contenido de una decena de hojas de un libro Excel a una lista de data.frames (además en la práctica podría no ser posible, ya que la cantidad de información que puede copiarse en el portapapeles es limitada). En las secciones siguientes explico otros métodos más eficientes en este sentido.

Formatos CSV y DIF

Cuando R y Excel no se ejecutan en la misma máquina, o el volumen de datos es demasiado grande como para usar el portapapeles con comodidad, una vía alternativa para intercambiar datos consiste en recurrir a formatos de archivo cuya finalidad es precisamente esa: facilitar el intercambio de información entre aplicaciones. Entre dichos formatos, dos de los más conocidos son CSV (Comma-Separated Values) y DIF (Data Interchange Format).

El formato CSV, como su propio nombre indica, utiliza comas para separar los valores y se estructura como una serie de filas (registros) con columnas de longitud variable. Los valores no numéricos pueden ir o no entrecomillados y en los numéricos el separador decimal es el punto. En algunos países europeos se usa la coma como separador decimal y el punto y como para separar unos valores de otros. Es importante destacar que Excel utiliza el punto y coma como separador incluso cuando su configuración establece que el separador decimal es el punto y, por tanto, podría usarse la coma según dicta el formato estándar CSV. Asumiendo que trabajamos con los mismos datos mostrados en la entrada anterior a esta, el archivo CSV generado por Excel tendría el siguiente contenido:

;Mean;Desv;Mean;Desv;Mean;Desv;Mean;Desv;Mean;Desv
Method 1;0.0168;0.0002;0.9764;0.0059;347.1082;2.3381;0.7448;0.006;0.0245;0.0033
Method 2;0.0167;0.0002;0.977;0.0059;347.192;1.5445;0.7451;0.0065;0.0245;0.0045
Method 3;0.0167;0.0003;0.9748;0.0075;346.793;2.4845;0.7434;0.0109;0.0264;0.0057
Method 4;0.0167;0.0002;0.9764;0.0069;347.2316;2.337;0.746;0.008;0.0236;0.0038
Method 5;0.0167;0.0002;0.973;0.0068;347.1902;2.1718;0.7452;0.009;0.0263;0.0041
Method 6;0.0167;0.0003;0.9726;0.0084;347.0992;2.6537;0.7436;0.01;0.0268;0.0056
Method 7;0.0168;0.0003;0.9736;0.0084;347.3674;1.4827;0.7451;0.0056;0.0253;0.0036
Method 8;0.0167;0.0001;0.9752;0.0096;347.6234;2.6344;0.7457;0.012;0.025;0.0067
Method 9;0.0167;0.0002;0.974;0.0078;347.8062;1.8226;0.7443;0.006;0.0254;0.0039
Method 10;0.0167;0.0002;0.9754;0.0049;347.6574;2.1976;0.7459;0.0083;0.0242;0.0032
Method 11;0.0167;0.0002;0.9732;0.0109;348.0798;1.3106;0.7461;0.0053;0.0243;0.004
Method 12;0.0166;0.0002;0.9696;0.0085;347.2472;1.6961;0.7441;0.0085;0.0271;0.0039
Method 13;0.0166;0.0002;0.97;0.0071;347.1204;1.9556;0.7441;0.0066;0.0271;0.0043
Method 14;0.0166;0.0002;0.971;0.0068;347.6144;1.7622;0.7462;0.0065;0.0254;0.0036
Method 15;0.0166;0.0002;0.9686;0.0077;347.8932;1.779;0.7444;0.0072;0.0271;0.0037

El formato DIF fue diseñado a principios de la década de los 80 del siglo pasado expresamente para facilitar el intercambio de datos entre hojas de cálculo, usándose por entonces en Visicalc y Lotus 1, 2, 3 y habiendo llegado hasta Excel y FreeOffice Calc. Se trata de un formato más descriptivo y, en consecuencia, menos compacto que CSV. A continuación se muestra parte del mismo contenido anterior pero en formato DIF: 

TABLE
0,1
"EXCEL"
VECTORS
0,16
""
TUPLES
0,11
""
DATA
0,0
""
-1,0
BOT
1,0
""
1,0
"Mean"
1,0
"Desv"
1,0
"Mean"
1,0
"Desv"
1,0
"Mean"
1,0
"Desv"
1,0
"Mean"
1,0
"Desv"
1,0
"Mean"
1,0
"Desv"
-1,0
BOT
1,0
"Method 1"
0,0.0168
V
0,0.0002
V
0,0.9764
V
0,0.0059
V
0,347.1082
V
0,2.3381
...

Lo interesante es que ambos formatos pueden ser utilizados a la hora de intercambiar datos entre Excel y R, que es el objeto de esta entrada. Partamos del supuesto en el que los datos están en Excel, posiblemente en el ordenador del usuario que los ha generado, y queremos transferirlos a otra máquina en la que trabajamos en R. El primer usuario debe guardar los datos en formato CSV o DIF y enviarnos el archivo resultante, para ello debe dar los pasos siguientes:

  1. Crear un nuevo libro Excel, en blanco, o en su defecto una nueva hoja en el libro actual.
  2. Copiar en el nuevo libro/hoja los datos que desea enviar.
  3. Utilizar la opción Guardar (o Guardar como si hemos creado una nueva hoja en lugar de un nuevo libro) para abrir el cuadro de diálogo del mismo nombre.
  4. Desplegar la lista Tipo y elegir el formato deseado: CSV o DIF, tal y como se muestra en la imagen siguiente.
  5. Pulsar los botones Aceptar y Sí de los dos cuadros de diálogo que aparecerán a continuación, confirmando que deseamos guardar únicamente el contenido de la hoja actual y que deseamos mantener el formato elegido a pesar de que se pierdan características del libro.

Al guardar en cualquiera de estos dos formato Excel escribirá en el archivo de destino todo el contenido de la hoja actual, no la selección que tuviésemos hecha. Por ello es preciso el paso previo en que se crea una hoja auxiliar (o un nuevo libro) y se copia en ella la información a exportar. Esa hoja, lógicamente, puede ser eliminada al final. Si esta operación vamos a realizarla a menudo lo más cómodo es registrar una macro de Excel con todos los pasos, partiendo desde la copia al portapapeles de la selección actual e incluyendo la inserción de la nueva hoja, pegado de los datos, selección de la opción Guardar como y establecimiento del formato de exportación. De esta forma en adelante solo habrá que ejecutar la macro, que puede ser asignada a una cierta combinación de teclas, tras haber marcado el rango de celdillas a exportar.

Una vez que tenemos la información en el archivo CSV/DIF llega el momento de importarla a R. Para leer un CSV tenemos a nuestra disposición múltiples funciones: read.csv, read.csv2, read.delim y la más genérica read.table. Vamos a concentrarnos en las dos primeras que son las que más se ajustan a lo que necesitamos en este momento. Ambas precisan como parámetro indispensable el nombre del archivo a leer y por defecto asumen que habrá una primera línea con los encabezados: los títulos de las columnas. La diferencia fundamental es que la primera asume que el formato sigue el estándar CSV: se usa el punto para decimales y la coma como separador de valores, mientras que la segunda está preconfigurada para el formato que suele usarse en algunos países europeos: coma para decimales y punto y coma como separador de valores. Si el formato que tenemos que leer no es ninguno de estos dos, como ocurre en nuestro caso que utiliza el punto para los decimales pero separa los valores con punto y coma, podemos usar cualquiera de las dos incluyendo los parámetros dec y/o sep para indicarlo. En la imagen siguiente puede verse cómo se ha usado read.csv para obtener el contenido del archivo en un data.frame.

Para importar en R el contenido de un archivo en formato DIF recurriemos a la función read.DIF. Si la estructura del archivo se ajusta estrictamente al estándar, solamente habrá que facilitar el nombre del archivo y se obtendrá como resultado un data.frame con los datos. En el caso de Excel, no obstante, hay una diferencia importante: el orden en que se entregan filas y columnas está invertido, siendo necesario agregar el parámetro transpose dándole el valor TRUE (por defecto es FALSE) tal y como se aprecia en la siguiente imagen.

Supongamos ahora que el sentido de transferencia de la información se invierte: tenemos un data.frame en R con datos que necesitamos enviar a alguien que los tratará con Excel. Para ello lo primero que haremos será generar el archivo CSV, sencillamente usando la función write.csv con dos parámetros: el data.frame que contiene los datos y el argumento file indicando el nombre del archivo de destino. También podemos usar write.csv2 para cambiar los separadores de decimales y valores, o bien incluir los parámetros dec y sep indicados anteriormente. El resultado será un archivo CSV que enviaremos al destinatario.

Excel puede abrir directamente archivos CSV, de hecho suele ser la aplicación establecida por defecto para abrir ese tipo de archivos en sistemas Windows. El contenido del archivo se llevará a una nueva hoja en un nuevo libro, a partir de la celdilla A1. Sin embargo, a menos que los separadores sean los que espera Excel, puede ocurrir que lo que obtengamos sean unas líneas de texto en lugar de los datos debidamente almacenados en sus celdillas con el formato que corresponda. Además, es probable que no queramos los datos en un nuevo libro sino que nos interese agregarlos en una hoja ya existente. En estos casos reproduciremos los pasos siguientes:

  1. Colocamos el cursor en la posición de la hoja a partir de la cual deseemos colocar los datos que van a importarse (la esquina superior izquierda del rango resultante).
  2. Abrimos la pestaña Datos de la Cinta de opciones y hacemos clic en la opción Obtener datos externos -> Desde texto.
  3. En el cuadro de diálogo que se abre seleccionamos el archivo CSV que contiene los datos a exportar.
  4. Se pone en marcha el Asistente para importar texto, cuyo primer paso nos permite elegir entre datos delimitados (con un separdor) o de ancho fijo. La opción marcada por defecto será la primera, por lo que hacemos clic en Siguiente.
  5. En el segundo paso es donde tendremos que configurar el separador de valores, desmarcando el que aparece por defecto (Tabulación normalmente) y marcando Coma o Punto y coma según el caso. En la parte inferior de la ventana podremos apreciar cuál es el resultado que se obtendría según la configuración establecida actualmente.
  6. En el tercer paso del asistente podemos configurar el formato aplicado a cada una de las columnas de valores. En caso de que el separador de decimales no sea el adecuado, según la configuración que se haya establecido en Excel, usaremos el botón Avanzadas para abrir un pequeño cuadro de diálogo en el que podremos indicar explícitamente cuál es el separador decimal.

Una vez llegado al última paso del asistente podremos elegir el destino de los datos, por defecto será la posición actual en la hoja, y completar el proceso de importación. Ya tenemos el contenido del data.frame de R en la hoja de cálculo.

Un último apunte en relación a la importación/exportación usando archivos CSV/DIF, especialmente si se utilizan ordenadores distintos y sistemas distintos: en caso de que haya columnas con textos es importante que la codificación de caracteres empleada sea la misma al importar que al exportar. Todas las funciones R mencionadas aceptan el parámetro fileEncoding, con el que podemos indicar la codificación al leer o escribir. En Excel al importar datos, con el procedimiento descrito antes, el primer paso del asistente permite elegir la codificación en la lista Origen del archivo.

El paquete xlsx

En las dos secciones anteriores se han descrito procedimientos dirigidos a intercambiar información entre Excel y R, en el primer caso usando el portapapeles y en el segundo mediante archivos CSV/DIF como intermediarios. El primer enfoque es adecuado cuando el usuario de R y Excel es la misma persona y usa ambas aplicaciones en el mismo equipo, mientras que el segundo permite compartir información entre distintos equipos y usuarios, por ejemplo enviando los archivos a un servidor compartido o por correo electrónico.

A pesar de la sencillez del proceso, descrito en la segunda sección, no todos los usuarios de Excel están preparados (o dispuestos) a exportar sus datos a CSV para enviárselos a un tercero o a importar datos de un archivo cuyo formato no conoce y que, a priori, podría darle problemas, por ejemplo si la configuración regional de Excel altera los separadores de decimales y miles. Afortunadamente R, como herramienta de código abierto que es, dispone de infinidad de paquetes para todo tipo de tareas, entre ellas la lectura y escritura directa de hojas de cálculo en formato XLSX (el usado por Excel desde las versión 2007).

En CRAN (el repositorio de paquetes R) podemos encontrar múltiples paquetes para trabajar con hojas de cálculo Excel desde R. Uno de ellos es el paquete xlsx, con el que también puede trabajar con hojas de versiones previas de Excel. Para instalar este paquete no tenemos más que facilitar su nombre a install.packages desde la consola de R. Si la configuración del repositorio es correcta, se descargará el paquete y también sus dependencias, quedando instalados y listos para usar. Las funciones indicadas a continuación dependen de que se haya cargado el paquete en la actual sesión de R, para lo cual ejecutaremos library(xlsx) al inicio.

Si la tarea a realizar consiste en enviar a una hoja de cálculo Excel la información que tenemos almacenada en un data.frame de R (u otra estructura de datos), la función a usar es write.xlsx. Los parámetros imprescindibles son dos: el conjunto de datos a escribir y el nombre del archivo en que reside la hoja de cálculo (argumento filename. Además también son de uso habitual los parámetros siguientes:

  • sheetName: Nombre de la hoja del libro en que se introducirán los datos. Ha de ser un nombre único, no debe haber otra hoja con ese nombre en el libro Excel en caso de que estén añadiéndose datos a un libro existente. Por defecto toma el valor Sheet1.
  • append: Por defecto toma el valor FALSE, provocando que la función cree un libro con el nombre indicado por filename eliminándolo si ya existiese. Si es TRUE el libro debe existir y al mismo se añadirá una hoja con los nuevos datos.
  • col.names y row.names: Determinan si se escribirán en la hoja los nombres asociados a las columnas y las filas del data.frame. Por defecto ambos parámetros toman el valor TRUE.

En la imagen siguiente puede verse un ejemplo de uso de esta función. En él se agregan a un libro existente tres nuevas hojas, con los nombres de tres meses, y datos hipotéticamente correspondientes a las ventas diarias. En la imagen de más abajo puede verse el libro tras abrirlo en Excel.

La función complementaria a la anterior, mediante la que podemos leer desde R información almacenada en un libro Excel sin necesidad de tener instalada esta aplicación, es read.xlsx. El primer argumento será en este caso el nombre del archivo donde está almacenada la información, siendo obligatorio indicar un número de hoja con el parámetro sheetIndex o bien su nombre con el parámetro sheetName. Sin más parámetros la función leerá todo el contenido de la hoja indicada, generará un data.frame incluyendo títulos de columnas y lo devolverá. En caso de que nos interese leer un conjunto concreto de celdillas, no la hoja completa, recurriremos a los parámetros colIndex y rowIndex para especificar qué columnas y qué filas han de recuperarse. Estas no tienen necesariamente que ser contiguas. En el ejemplo de la imagen siguiente se recupera la primera fila y las filas 23 a 32.

Por defecto la función read.xlsx evaluará las fórmulas que pudieran existir en la hoja de cálculo, almacenando en el data.frame el resultado que produzcan. Dando el valor TRUE al parámetro keepFormulas se anulará la evaluación, obteniendo en su lugar el texto de la fórmula.