Tengo un índice de las condiciones de orden público en muchos países durante varios años. Para cada país, quiero saber cuántos países están por delante y cuántos están detrás en un año determinado. En la imagen adjunta a continuación, he intentado mostrar lo que quiero hacer. Hice manualmente el cálculo para Albania (ALB). Del total de cuatro países de la muestra, tres estaban por delante de Albania en 1995 y ninguno estaba por debajo. De manera similar, en 1996, dos países estaban por encima de Albania y un país estaba por debajo. Lo mismo ocurre con los años 1997 y 1998.
El problema aquí es que tengo 134 países y 16 años. Me llevará semanas hacerlo manualmente. Probé la siguiente fórmula en MS Excel sin éxito:
=IF($B:$B=B2,COUNTIF($C$2:$C$17,"<"&C2))
¿Alguna idea de cómo puedo hacer esto? Estoy abierto a sugerencias. Utilizo simultáneamente MS Excel y Stata, por lo que cualquier plataforma funcionará para mí.
Respuesta1
Unidireccional en Stata:
clear all
set more off
*----- example data -----
input ///
country year law
1 1996 235
1 1997 25
1 1998 6445
2 1996 436
2 1997 2356
2 1998 224
3 1996 3129
3 1997 735
3 1998 836
end
list, sepby(country)
*----- what you want -----
sort year law
by year: gen ahead = _N - _n
by year: gen behind = _n - 1
sort country year
list, sepby(country)
Por supuesto, se hacen algunas suposiciones.
Editar
La estrategia es realmente sencilla. Ordena los datos por year law. El resultado es:
. list, sepby(year)
+-----------------------+
| country year law |
|-----------------------|
1. | 1 1996 235 |
2. | 2 1996 436 |
3. | 3 1996 3129 |
|-----------------------|
4. | 1 1997 25 |
5. | 3 1997 735 |
6. | 2 1997 2356 |
|-----------------------|
7. | 2 1998 224 |
8. | 3 1998 836 |
9. | 1 1998 6445 |
+-----------------------+
Ahora sólo necesitamos darnos cuenta de que por cada yeargrupo, la primera observación (es decir, las observaciones 1, 4 y 7) es superada por otras dos observaciones (es decir, la obs 1 es superada por 2, 3; la obs 4 es superada por 5, 6; y obs 7 es superado por 8, 9). Esto significa que haydosobservaciones que son aheadde las observaciones 1, 4, 7.
A continuación, en cada yeargrupo, la segunda observación (es decir, las observaciones 2, 5 y 8) es superada por otra observación. Esto significa que hayunoobservación aheadde las observaciones 2, 5, 8.
Finalmente, por cada yeargrupo, la tercera observación (es decir, las observaciones 3, 6 y 9) es superada por ninguna otra observación. Esto significa que hayceroobservaciones aheadde las observaciones 3, 6, 9.
Entonces ahora vemos que después de apropiadamente sortlos datos, por yeargrupo, solo necesitamos crear la secuencia.dos, uno, cero, para la aheadvariable. Esto se puede hacer de varias maneras. Elegí hacerlo usando subíndices (ver help subscripting) y variables del sistema _ny _N(ver help _variables). _Nes el número total de observaciones por grupo (3) y _nes la observación actual por grupo. Un ejemplo: para la observación 1, el cálculo es 3-1 = 2. Para la observación 2, es 3-2 = 1, y así sucesivamente.
La behindvariable se calcula de manera análoga.
El resultado es:
+----------------------------------------+
| country year law ahead behind |
|----------------------------------------|
1. | 1 1996 235 2 0 |
2. | 2 1996 436 1 1 |
3. | 3 1996 3129 0 2 |
|----------------------------------------|
4. | 1 1997 25 2 0 |
5. | 3 1997 735 1 1 |
6. | 2 1997 2356 0 2 |
|----------------------------------------|
7. | 2 1998 224 2 0 |
8. | 3 1998 836 1 1 |
9. | 1 1998 6445 0 2 |
+----------------------------------------+
En mi código, después de calcular las nuevas variables, sortnuevamente solo presento los datos en su orden de clasificación original ( country year).
Respuesta2
En Excel COUNTIFS(con una "S" al final) se puede usar para contar con múltiples condiciones, así que pruebe esta fórmula en D2 copiada
=COUNTIFS(B:B,B2,C:C,">"&C2)
Para E2 simplemente invierta el < a >
Respuesta3
Simplemente ordene por año y luego aplique la fórmula 16 veces; la fórmula puede verse así: =COUNTIF($C$2:$C$13,">"&C2)donde 'C2-C13 is range of values for each year andC2` es el valor del país (para el año) con el que se debe comparar. Creo que no tomaría mucho tiempo, tal vez 1 minuto para el país.
Respuesta4
Utilizo $i=1,...,n=164$ para contar los países, $t=1,...,16$ para contar los años.
Denota $s_{it}$ la puntuación del país $i$ en el año $t$. Denota también $\alpha_{it}$ el número de países que están por delante del país $i$ en el año $t$ y por $\beta_{it}$ el número de países que están por detrás del país $i$ en el año $t $.
Evidentemente (suponiendo que no haya vínculos, por lo que tendrás que tomar una decisión),
$$ \alpha_{it}+\beta_{it}= N-1,\;\; \forall t$$ La forma ingenua:
1)Separe (en Excel) las $16$ observaciones que tiene, obteniendo $16$ series transversales de $164$ secciones transversales cada una. Mantenga el ID de cada valor en cada uno de los vectores $16$, de cualquier forma que los tenga (ID del país de tres letras, año)
2)Ordene cada uno de los $16$ vectores por magnitud, demás bajopuntuación paramás altopuntaje 3)A la derecha o izquierda de cada uno de los $16$ vectores ordenados, cree la serie ${1,2,3,...,164}$
Entonces, la puntuación más baja $s_{it}$ está asociada con el número $1$ y la más alta con el número $164$.
Acaba de clasificar cada país según su puntuación de cada año., denota la clasificación $r_{it}$.
Entonces
$$\alpha_{it} = 164-r_{it},\;\;\; \beta_{it} = r_{it}-1 $$
¿Cuánto tiempo crees que te llevará?