Ich habe einen Index für die öffentlichen Ordnungsbedingungen in vielen Ländern über mehrere Jahre. Für jedes Land möchte ich herausfinden, wie viele Länder in einem bestimmten Jahr vorn und wie viele Länder hinterherhinken. Im unten angehängten Bild habe ich versucht zu zeigen, was ich tun möchte. Ich habe die Berechnung für Albanien (ALB) manuell durchgeführt. Von den insgesamt vier Ländern in der Stichprobe lagen 1995 drei Länder vor Albanien und keins war darunter. Ähnlich verhält es sich im Jahr 1996: Zwei Länder lagen vor Albanien und ein Land war darunter. Dasselbe gilt für 1997 und 1998.
Das Problem dabei ist, dass ich 134 Länder und 16 Jahre habe. Es würde Wochen dauern, das manuell zu machen. Ich habe die folgende Formel in MS Excel ohne Erfolg ausprobiert:
=IF($B:$B=B2,COUNTIF($C$2:$C$17,"<"&C2))
Irgendwelche Ideen, wie ich das machen kann? Ich bin offen für Vorschläge. Ich verwende MS Excel und Stata gleichzeitig, daher ist jede Plattform für mich geeignet.
Antwort1
Eine Möglichkeit in 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)
Natürlich werden einige Annahmen getroffen.
Bearbeiten
Die Strategie ist wirklich einfach. Sortieren Sie die Daten nach year law. Das Ergebnis ist:
. 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 |
+-----------------------+
Jetzt müssen wir nur noch feststellen, dass pro yearGruppe die erste Beobachtung (also die Beobachtungen 1, 4 und 7) von zwei anderen Beobachtungen überholt wird (also Beobachtung 1 wird von 2, 3 überholt; Beobachtung 4 wird von 5, 6 überholt; und Beobachtung 7 wird von 8, 9 überholt). Das bedeutet, dass eszweiBeobachtungen, die aheadzu den Beobachtungen 1, 4, 7 gehören.
Als nächstes wird pro yearGruppe die zweite Beobachtung (also die Beobachtungen 2, 5 und 8) von einer anderen Beobachtung überholt. Das bedeutet, dass eseinsBeobachtung aheadder Beobachtungen 2, 5, 8.
Schließlich wird pro yearGruppe die dritte Beobachtung (also die Beobachtungen 3, 6 und 9) von null anderen Beobachtungen überholt. Das bedeutet, dass esnullBeobachtungen aheadder Beobachtungen 3, 6, 9.
Wir sehen also, dass wir nach der entsprechenden sortAufteilung der Daten pro yearGruppe nur noch die Sequenz erstellen müssenZwei, Eins, Null, für die aheadVariable. Dies kann auf verschiedene Weise erfolgen. Ich habe mich für die Verwendung von Indizes (siehe help subscripting) und Systemvariablen _nund _N(siehe help _variables) entschieden. _Nist die Gesamtzahl der Beobachtungen pro Gruppe (3) und _nist die aktuelle Beobachtung pro Gruppe. Ein Beispiel: Für Beobachtung 1 ist die Berechnung 3-1 = 2. Für Beobachtung 2 ist sie 3-2 = 1, und so weiter.
Die Berechnung der behindVariable erfolgt analog.
Das Ergebnis ist:
+----------------------------------------+
| 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 |
+----------------------------------------+
In meinem Code versuche ich nach der Berechnung der neuen Variablen, sortdie Daten erneut in ihrer ursprünglichen Sortierreihenfolge darzustellen ( country year).
Antwort2
In Excel COUNTIFS(mit einem "S" am Ende) kann verwendet werden, um mit mehreren Bedingungen zu zählen, also versuchen Sie diese Formel in D2 nach unten kopiert
=COUNTIFS(B:B,B2,C:C,">"&C2)
Für E2 kehren Sie einfach das < in > um
Antwort3
Sortieren Sie einfach nach Jahr und wenden Sie die Formel dann 16 Mal an. Die Formel kann folgendermaßen aussehen: =COUNTIF($C$2:$C$13,">"&C2)wobei „C2-C13 is range of values for each year andC2“ der Länderwert (für das Jahr) ist, mit dem verglichen werden muss. Ich denke, das würde nicht lange dauern, vielleicht 1 Minute für das Land.
Antwort4
Ich verwende $i=1,...,n=164$ zum Zählen der Länder und $t=1,...,16$ zum Zählen der Jahre.
Bezeichnen Sie mit $s_{it}$ das Ergebnis von Land $i$ im Jahr $t$. Bezeichnen Sie mit $\alpha_{it}$ auch die Anzahl der Länder, die im Jahr $t$ vor Land $i$ liegen, und mit $\beta_{it}$ die Anzahl der Länder, die im Jahr $t$ hinter Land $i$ liegen.
Offensichtlich (unter der Annahme, dass es keine Gleichstände gibt, für die Sie eine Entscheidung treffen müssen),
$$ \alpha_{it}+\beta_{it}= N-1,\;\; \forall t$$ Der naive Weg:
1)Trennen Sie (in Excel) die 16 Beobachtungen, die Sie haben, und erhalten Sie 16 Querschnittsreihen mit jeweils 164 Querschnitten. Behalten Sie die ID für jeden Wert in jedem der 16 Vektoren, egal wie Sie sie haben (dreistellige Länder-ID, Jahr).
2)Sortieren Sie jeden der 16 Vektoren nach Größe, vonniedrigstepunktenhöchstePunktzahl 3)Rechts oder links von jedem der 16 sortierten Vektoren entsteht die Reihe {1,2,3,...,164}
Der niedrigste Punktestand $s_{it}$ ist also mit der Zahl $1$ verknüpft und der höchste mit der Zahl $164$.
Sie haben gerade jedes Land nach seiner Punktzahl für jedes Jahr eingestuft, bezeichnen die Rangfolge $r_{it}$.
Dann
$$\alpha_{it} = 164-r_{it},\;\;\; \beta_{it} = r_{it}-1 $$
Wie lange werden Sie Ihrer Meinung nach dafür brauchen?