Archivo de la categoría: Trucos

Truco Excel. Repetir un campo a partir de otro que define el número de repeticiones

Excel, Formación, Trucos

En realidad necesitamos repetir filas, pero el número de repeticiones está parametrizado por otro campo. Partimos de un rango donde la primera columna es un valor que necesita ser repetido el número de filas que nos indica la segunda columna. Es una duda que planteaba un lector del blog:

Hola, a ver si me podeis ayudar,
quiero repetir cada fila el número de veces que hay en cada celda que le corresponde.
Es decir tengo una variable Nombre (Columna A) y otra Frecuencia (Columna B)
Nombre Frecuencia
A 5
B 8
C 25
D 12
… ….

La idea es repetir la la Fila A, 5 veces, la fila B, 8 veces y así sucesivamente.
En realidad es lo inverso a crear una tabla de frecuencias desde una matriz de datos.

Tenía en la nevera un truco de Excel que puede hacer esta tarea y que también subiré al blog en los próximos días, en realidad esta entrada y la siguiente son modificaciones de un anterior truco Excel que subí al blog. En esta imagen podéis ver lo que hace:

excel_repite_filas

La macro que lo realiza es esta:

[source languaje=”vb”]
Sub rango_columnas2()

Dim rango, celda As Variant
Dim i As Long, j As Long, k As Long
Dim col As Long

rango = Application.InputBox(Prompt:="Seleccione el rango a repetir sin cabeceras", Type:=8)
Set celda = Application.InputBox(Prompt:="Seleccione donde quiere poner los datos", Type:=8)

‘Esta es la parte que permite ubicar la salida
celda.Select
k = 0

‘Esto recorre el rango
For i = 1 To UBound(rango, 1)
For j = 1 To rango(i, 2)
ActiveCell.Offset(k, 0).Value = rango(i, 1)
k = k + 1
Next
Next

End Sub
[/source]

Se ejecutan 2 cuadros con el método Application.InputBox, en el primero seleccionamos el rango de datos sin cabeceras y en el segundo indicamos donde queremos que salgan los datos. Se trata de recorrer el rango y pararse a repetir las primera columna del rango tantas veces como lo indica la segunda columna. Un proceso sencillo con Visual Basic pero que sería interesante replicar con funciones de Excel, en este caso creo que INDIRECTO sería la más adecuada. En el siguiente enlace podéis descargar el ejemplo:

repite_filas

En siguientes fechas daré más vueltas a este bucle para realizar transposiciones de datos. Saludos.

 

Truco Excel. Pasar un rango de varias columnas a una

Excel, Formación, Trucos

Macro de Excel que nos permite pasar de varias columnas a una sola. De momento no es una función, es un código que sorprende por su sencillez:

Sub rango_columnas()

Dim rango As Variant
Dim i As Long, j As Long, k As Long
Dim col As Long

rango = Selection.Value

'Esta es la parte que permite ubicar la salida
col = Selection.Column
k = Selection.Row

'Esto recorre el rango y realiza la trasposición
For i = 1 To UBound(rango, 1)
    For j = 1 To UBound(rango, 2)
        Cells(k, col + UBound(rango, 2)).Value = rango(i, j)
        k = k + 1
    Next
Next

End Sub

Este código lo ponéis tal cual en vuestro Excel y os ilustro a continuación sobre su funcionamiento. Lo primero es seleccionar el rango de columnas que deseamos transponer:

varias_columnas_a_una_excel1

Ahora sólo ejecutamos la macro rango_columnas:

varias_columnas_a_una_excel2

Y aparece justo al lado del rango que deseamos transponer a una sola columna:

varias_columnas_a_una_excel3

En este caso el resultado lo obtenemos en la columna F. Jugando con los índices i y j de nuestra macro podremos modificar la forma de la transposición. Me parece una macro útil y por eso lo comparto con vosotros. Saludos.

El paquete de R weatherData para la obtención de datos meteorológicos en España

Formación, R, Trucos

Tenía pendiente un proyecto con modelos de Lee Carter y el paquete weatherData de R (¡toma!) pero como no lo voy a llevar a cabo nunca os traigo a estas líneas un paquete más que interesante de R que nos permite obtener datos de las estaciones meteorológicas de los aeropuertos del mundo (https://www.wunderground.com/history/airport/) y encima te lo pone como un objeto de R, qué más podemos pedir. En github tenéis una completa batería de ejemplos de uso. En el caso de que necesitemos descargar información meteorológica de España tenemos que irnos a http://weather.rap.ucar.edu/surface/stations.txt donde están listados todos los aeropuertos que recoge este sistema de información, buscamos SPAIN y nos interesa el “ICAO” que es el International Civil Aviation Organization, el código del aeropuerto vamos. Con estas premisas si quiero recoger las temperaturas de 2015 del aeropuerto de Albacete:

install.packages("weatherData")
library(weatherData)

anio = getWeatherForYear("LEAB",2015)

La información de los aeropuertos es bastante completa y están distribuidos por toda la geografía nacional además tenemos muchos datos a nuestra disposición y bien tabulados. Yo conozco más de uno que se está acordando de mi por no haber escrito sobre este paquete unos meses antes. Saludos.

Truco SAS. Como leer PC Axis con SAS

Formación, R, SAS, Trucos

Estoy leyendo información del INE que tiene que terminar cargándose en SAS y estos datos están en formato PC Axis. Existen macros en SAS para generar datasets a partir de PC Axis pero la verdad es que no he llegado a entender muy bien como funcionan y tras varios errores la mejor opción que he encontrado es emplear R y el paquete pxR que han creado algunos miembros de la Comunidad de R-Hispano. Como realizo esta tarea es más que sencillo:

En R realizamos la importación del archivo *.px:

nacionalidad = read.px("ubicacion\\seccion_censal_nacionalidad.px")
nacionalidad = data.frame(nacionalidad)
write.csv( nacionalidad, file = "ubicacion\\nacionalidad.csv" )

Hemos generado un csv que importamos desde SAS:

proc import datafile="ubicacion\nacionalidad.csv"
     out=nacionalidades
     dbms=csv
     replace;
     getnames=yes;
run;

También quería aprovechar esta entrada para comentaros que es preferible usar los viejos csv para mover archivos entre R  y SAS que usar librerías como SASxport que generan ficheros “portables” de SAS, aunque los ficheros “portables” garantizan que se puedan leer con distintas versiones de SAS este paquete tarda mucho (demasiado) tiempo en crear los archivos. Y si alguien tiene una versión más sencilla de la macro de SAS que mande el link. Saludos.

Truco Excel. Abrir múltiples libros de Excel en distintas hojas de un nuevo libro

Excel, Formación, Monográficos, Trucos

Hace tiempo escribí sobre el método de Excel GetOpenFilename para abrir archivos desde Excel a través del explorador de archivos ahora le damos una nueva vuelta de tuerca a aquella entrada y de forma simple podemos abrir múltiples libros de Excel que además se añadirán de forma sucesiva en un nuevo libro. En este link podéis descargaros el archivo y como veréis no tiene nada. Un botón Abrir Excel realiza el proceso, se abre el explorador de Windows y podéis seleccionar múltiples archivos Excel que se almacenan en un array. La macro a ejecutar es la siguiente:

Sub abre_libros()
Dim Hoja As Object, rango As String
Dim libros As Variant

‘Ventana con archivos
libros = Application.GetOpenFilename _
(“Archivos Excel (*.xls*), *.xls*”, 2, “Abrir archivos”, , True)
‘Es necesario seleccionar archivos
If IsArray(libros) Then
‘Creamos un libro nuevo
Workbooks.Add
libro_actual = ActiveWorkbook.Name
‘Ahora pegamos las hojas
For i = LBound(libros) To UBound(libros)
Workbooks.Open libros(i)
libro_nuevo = ActiveWorkbook.Name
For Each Hoja In ActiveWorkbook.Sheets
Hoja.Copy after:=Workbooks(libro_actual).Sheets(Workbooks(libro_actual).Sheets.Count)
Next
Workbooks(libro_nuevo).Close False
Next
End If
End Sub

No se me ocurrió como hacer el código más sencillo y al final lo que hace es recorrer el array de libros que abre y añadir las hojas sobre un libro nuevo. Tiene algunas limitaciones en la forma en la que pega las nuevas hojas pero nada que pueda ser muy complicado de solucionar. Ahora tenéis que mejorar la entrada más visitada de este blog para que pueda unir en uno los Excel que se abren.

DESREF para trasponer en Excel varias columnas

Excel, Trucos

Hoy han planteado una duda en el blog que me ha parecido interesante porque es un buen ejemplo de uso de la función DESREF de Excel para trasponer filas en columnas con cierto criterio. La idea que planteaba es realizar una trasposición de 3 en 3 elementos como indica en la figura:

ejemplo_trasponer_desref

Para entender como funciona la función DESREF lo mejor es pensar en lo siguiente: fijado un punto nos movemos x-filas ; x-columnas. En el ejemplo concreto el punto inicial está en la celda $A$1;0;0 si nos desplazamos a la derecha haremos $A$1;1;0 es decir, nos movemos a la derecha +1 y siempre mantenemos la columna porque estamos trasponiendo filas en columnas. Las coordenadas de la fila para la trasposición en nuestro ejemplo quedarían:

+0 +1 +2
+3 +4 +5
+6 +7 +8
+9 +10 +11

 

En este caso se ha hecho:

ejemplo_trasponer_desref_2

La fórmula lee un dato que va de 3 en 3 a la izquierda y suma +1 o +2 si es el primer o el segundo elemento que deseamos en columna. Evidentemente esta fórmula es mejorable pero es un buen ejemplo de  DESREF en Excel. Saludos.

Macro SAS. Número de variables de un dataset en una macro

Formación, SAS, Trucos, WPS

Una macro que nos permite saber el número de variables que tiene un conjunto de datos SAS. Es una petición de una lectora y la macro es análoga a otra que ya pusimos en el blog allá por 2010. Veamos cómo funciona:

%macro numvars(datos);
 %global numvars;
 /*ABRIMOS EL CONJUNTO DE DATOS PARA VER SUS CARACTERISTICAS*/
 %let datosid = %sysfunc(open(&datos));
 /*SI ESTA ABIERTO ENTONCES LA FUNCION ATTRN NOS DA EL NUMERO DE VARIABLES*/
 %if &datosid %then %do;
 %let numvars =%sysfunc(attrn(&datosid,nvars));
 /*CERRAMOS EL CONJUNTO DE DATOS*/
 %let rc = %sysfunc(close(&datosid));%end;
 %mend;
data ejemplo;
 a=1;
 b=2;
 c=3;
 d=4;
 f=5;
 g=6;
 run;
%numvars(ejemplo);
 %put _user_;

Utilizamos las funciones I/O de SAS, en concreto ATTRN que unido a NVARS nos permite saber el número de variables que tiene un dataset, el número de variables lo ponemos en la macrovariable global &numvars..

Saludos.

Macro SAS. Crear variables dummy desde una variable categórica

Formación, SAS, Trucos, WPS

En alguna ocasión ya he conjugado el verbo dumificar y preparando una segmentación he creado una macro SAS que genera variables dummy a partir de variables categóricas, es decir, si la variable A toma valores 1, 2 y 3 tendría que generar A_1 con valor 1 si A toma 1 y con valor 0 en caso contrario, A_2 tiene valor 1 si A es igual a 2 y A_3 tiene valor 1 si A es igual a 3, no es complicado de comprender, pasamos de una variable con 3 niveles a 3 variables con valores 0 o 1. Para esto podemos emplear arrays o la siguiente macro:

%macro dumificar(varib, grupos, mv);
%global &mv.;
data instruccion;
do i=1 to &grupos.;

instruccion=”&varib._”||compress(put(i,3.))||
“=0; IF &varib.=”||put(i,3.)||” THEN &varib._”||compress(put(i,3.))||”=1″;

output;
end;
run;

proc sql noprint ;
select instruccion into:&mv. separated by “;”
from instruccion;
quit;
proc delete data=instruccion;quit;
%mend;

La intención es crear de forma automática un código del tipo VARIABLE_1=0; IF VARIABLE = 2 THEN VARIABLE_1=1; La macro tiene 3 parámetros, VARIB que es la variable que deseamos dumificar, GRUPOS que es el número de grupos de la variable que vamos a transformar en dummies y MV que es el nombre de la macrovariable que tiene el código SAS que generamos de forma automática. A modo de ejemplo de uso:

data aleatorios;
do i=1 to 1000 ;
datoA = min(ranpoi(2,4),9);
if datoA <= 3 then datoB = ranpoi(89,2);
else if datoA <= 5 then datoB = min(ranpoi(89,6),6);
else datoB = min(ranpoi(89,2),3);
output;
end;
drop i;
run;

%dumificar(datoA, 9, dumifica_datoA);
%dumificar(datoB, 8, dumifica_datoB);

data aleatorios;
set aleatorios;
&dumifica_datoA.;
&dumifica_datoB.;
run;

Espero que os sea de utilidad esta macro. Saludos.

Truco Excel. Función para identificar el color de una celda

Excel, Formación, Trucos

En alguna entrada anterior ya vimos como identificar el color de una celda con Excel. Recientemente me trasladaron una duda, se trataba de realizar una acción determinada si el color de la celda era distinto. Algo muy habitual cuando realizas alguna validación visual y marcas celdas con otro color. La solución es sencilla, se trata de crear nuestra propia función que identifique el color de la celda:

Function color_celda(celda As Range)
color_celda = celda.Interior.Color
End Function

En este caso podríamos realizar funciones del tipo =SI(color_celda(A1)<> 16777215; ACCION1; ACCION2) además podemos darle otra vuelta de tuerca y si deseamos ordenar por colores podemos hacer:

Function color_orden(celda As Range)
color_orden = celda.Interior.ColorIndex
End Function

Establecemos un orden de colores y podemos realizar más acciones. Un truco sencillo que puede ayudar a las intervenciones manuales sobre nuestros libros de Excel. Saludos.

Truco para EMB Emblem. Cambiar el nivel base de un factor

Formación, Modelos, Trucos

Un buen truco que me han descubierto hoy para los usuarios de EMB Emblem, como cambiar el nivel base de un factor de datos sin necesidad de pasar por los datos (habitualmente SAS) o sin hacerlo a posteriori (habitualmente Excel y lo que hacía el ahora escribiente). Cuando se generan los datos se genera el fichero binario *.BID y el fichero que se emplea para leer ese fichero *.FAC; para alterar el nivel base debemos abrir este archivo *.FAC con un block de notas o cualquier editor de texto plano. Al abrirlo tendremos lo siguiente:

XXXXXXXXXX        –> ES EL NOMBRE DEL ARCHIVO
99                                 –>ES EL NÚMERO DE FACTORES
XXXXXXXXXX.Bid –> ES EL NOMBRE DEL ARCHIVO BINARIO CON LOS DATOS
0
99999999                   –> ES EL NÚMERO DE REGISTROS
No. Levels Base         –> ES LA DESCRIPCION DE LO QUE VIENE A CONTINUACIÓN
XXXXXXXXXXXX  –> NOMBRE DE UN FACTOR
999                 99         –> EL PRIMERO ES EL NÚMERO DE NIVELES Y EL SEGUNDO ES EL NIVEL BASE

ASÍ PARA TODOS LOS FACTORES. Si deseamos cambiar el nivel base de un factor sólo tenemos que cambiar ese segundo número que hay tras cada definición de los factores. Un truco muy sencillo y que será de utilidad para aquellos que usáis EMB Emblem. Saludos.