У меня есть индекс для условий правопорядка во многих странах за несколько лет. Для каждой страны я хочу узнать, сколько стран впереди и сколько стран позади за данный год. На прикрепленном ниже изображении я попытался показать, что я хочу сделать. Я вручную выполнил расчет для Албании (ALB). Из всех четырех стран в выборке, 3 страны были впереди Албании в 1995 году, и ни одна не была ниже ее. Аналогично в 1996 году 2 страны были выше Албании, а 1 страна была ниже ее. То же самое касается 1997 и 1998 годов.
Проблема в том, что у меня 134 страны и 16 лет. Мне понадобятся недели, чтобы сделать это вручную. Я попробовал следующую формулу в MS Excel, но безуспешно:
=IF($B:$B=B2,COUNTIF($C$2:$C$17,"<"&C2))
Есть идеи, как это сделать? Я открыт для предложений. Я одновременно использую MS Excel и Stata, поэтому мне подойдет любая платформа.
решение1
Один из способов в 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)
Конечно, некоторые предположения делаются.
Редактировать
Стратегия очень проста. Сортируем данные по year law. Результат:
. 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 |
+-----------------------+
Теперь нам нужно только понять, что в каждой yearгруппе первое наблюдение (т. е. наблюдения 1, 4 и 7) опережается двумя другими наблюдениями (т. е. наблюдение 1 опережается наблюдением 2, 3; наблюдение 4 опережается наблюдением 5, 6; а наблюдение 7 опережается наблюдением 8, 9). Это означает, что естьдванаблюдения, которые относятся aheadк наблюдениям 1, 4, 7.
Далее, в каждой yearгруппе второе наблюдение (т.е. наблюдения 2, 5 и 8) опережается одним другим наблюдением. Это означает, что естьодиннаблюдение aheadнаблюдений 2, 5, 8.
Наконец, в каждой yearгруппе третье наблюдение (т.е. наблюдения 3, 6 и 9) опережается нулевыми другими наблюдениями. Это означает, что естьнульнаблюдения aheadнаблюдений 3, 6, 9.
Итак, теперь мы видим, что после соответствующего sortраспределения данных по yearгруппам нам просто нужно создать последовательностьдва, один, ноль, для aheadпеременной. Это можно сделать несколькими способами. Я решил сделать это с помощью индексации (см. help subscripting) и системных переменных _nи _N(см. help _variables). _N— общее количество наблюдений в группе (3), а _n— текущее наблюдение в группе. Пример: для наблюдения 1 вычисление равно 3-1 = 2. Для наблюдения 2 — 3-2 = 1и так далее.
Переменная behindвычисляется аналогично.
Результат:
+----------------------------------------+
| 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 |
+----------------------------------------+
В моем коде после вычисления новых переменных я sortснова просто представляю данные в исходном порядке сортировки ( country year).
решение2
В Excel COUNTIFS(с буквой «S» на конце) можно использовать для подсчета с несколькими условиями, поэтому попробуйте эту формулу в D2, скопированную вниз
=COUNTIFS(B:B,B2,C:C,">"&C2)
Для E2 просто поменяйте < на >
решение3
Просто отсортируйте по году, а затем примените формулу 16 раз - формула может выглядеть так: =COUNTIF($C$2:$C$13,">"&C2)где 'C2-C13 is range of values for each year andC2` - это значение страны (за год), с которым нужно сравнить. Я думаю, это не займет много времени, может быть, 1 минуту для страны.
решение4
Я использую $i=1,...,n=164$ для подсчета стран, $t=1,...,16$ для подсчета лет.
Обозначим через $s_{it}$ оценку страны $i$ в году $t$. Обозначим также через $\alpha_{it}$ количество стран, которые опережают страну $i$ в году $t$, а через $\beta_{it}$ количество стран, которые отстают от страны $i$ в году $t$.
Очевидно (при условии отсутствия связей, в этом случае вам придется принять решение),
$$ \alpha_{it}+\beta_{it}= N-1,\;\; \forall t$$ Наивный способ:
1)Разделите (в Excel) $16$ наблюдений, которые у вас есть, получив $16$ ряды поперечных сечений по $164$ сечений каждое. Сохраните идентификатор для каждого значения в каждом из $16$ векторов, в любом виде, в котором они у вас есть (трехбуквенный идентификатор страны, год)
2)Отсортируйте каждый из 16 векторов по величине, отсамый низкийоценканаибольшийсчет 3)Справа или слева от каждого из $16$ отсортированных векторов создадим ряд ${1,2,3,...,164}$
Таким образом, самая низкая оценка $s_{it}$ связана с числом $1$, а самая высокая — с числом $164$.
Вы только что оценили каждую страну на основе ее баллов за каждый год., обозначают рейтинг $r_{it}$.
Затем
$$\alpha_{it} = 164-r_{it},\;\;\; \beta_{it} = r_{it}-1 $$
Как вы думаете, сколько времени это займет?