Numerating one variable based on the sorted-order of another variable

[Cem Mert Dalli]

Hi,

I am working on a dataset almost includes 5000 observation, and I would like to create a new variable based on the sorted-order of another variable.

To put it simply, it is a list of political leaders in each year, thus it is listed as the following:

var1 var2 var3
USA Donald Trump 2017
USA Donald Trump 2018

Since there are some people who left the office and elected again, after sorting the data based on year (var3 in the example), var2 is listed as following:
A - A - A - B - B - A - A - C -C.

Now I have to assign them spell number, and it becomes tricky for person A, who left the office to B and retook it. What I would like to see as result in my new variable is 1 - 1 - 1 - 1 - 1- 2 - 2 - 1 - 1 , as person A serves for second time. If he would stay in the office for 5 years continuously, all would be 1 but we have to assign a new number for the new spell.

In this case, what would you suggest me in assigning spell numbers?

Though I couldn't explain well what I would to do, I hope it will be enough for you.

Thanks,
Cem

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 leader float year str1 country
"a" 2000 "X"
"a" 2001 "X"
"a" 2002 "X"
"b" 2003 "X"
"b" 2004 "X"
"a" 2005 "X"
"a" 2006 "X"
"c" 2007 "X"
"c" 2008 "X"
"a" 2000 "Y"
"a" 2001 "Y"
"c" 2002 "Y"
"b" 2003 "Y"
"b" 2004 "Y"
"b" 2005 "Y"
"a" 2006 "Y"
"c" 2007 "Y"
"c" 2008 "Y"
end

by country leader (year), sort: gen spell = sum(year != year[_n-1] + 1)
sort country year leader

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.



When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Cem Mert Dalli]

Dear Clyde,

Thank you for your help and suggestion for sharing data example. Since I did not mention that there are more than one row per year in some cases (I can't drop them as we should keep them to notice their start date), your suggestion with "sum(year != year[_n-1] + 1" did not work properly, and I could not adapt to our case neither. As you suggested, I am sharing the data example by using dataex in below.


* Example generated by -dataex-. To install: ssc install dataex
clear
input int country_id str10 historical_date str11 country_name str52 v3exnamhog
36 "1788-12-31" "Afghanistan" "Payinda Khan Mohammadzai"
36 "1789-12-31" "Afghanistan" "Payinda Khan Mohammadzai"
36 "1790-12-31" "Afghanistan" "Payinda Khan Mohammadzai"
36 "1791-12-31" "Afghanistan" "Payinda Khan Mohammadzai"
36 "1792-12-31" "Afghanistan" "Payinda Khan Mohammadzai"
36 "1793-01-01" "Afghanistan" "Wafadar Khan"
36 "1793-12-31" "Afghanistan" "Wafadar Khan"
36 "1794-12-31" "Afghanistan" "Wafadar Khan"
36 "1795-12-31" "Afghanistan" "Wafadar Khan"
36 "1796-12-31" "Afghanistan" "Wafadar Khan"
36 "1797-12-31" "Afghanistan" "Wafadar Khan"
36 "1798-12-31" "Afghanistan" "Wafadar Khan"
36 "1799-12-31" "Afghanistan" "Wafadar Khan"
36 "1800-12-31" "Afghanistan" "Wafadar Khan"
36 "1803-01-01" "Afghanistan" "Shir Mohammad Khan"
36 "1803-12-31" "Afghanistan" "Shir Mohammad Khan"
36 "1804-12-31" "Afghanistan" "Shir Mohammad Khan"
36 "1805-12-31" "Afghanistan" "Shir Mohammad Khan"
36 "1806-12-31" "Afghanistan" "Shir Mohammad Khan"
36 "1807-12-31" "Afghanistan" "Shir Mohammad Khan"
36 "1808-01-01" "Afghanistan" "Nawab Mohammad Usman Khan"
36 "1808-12-31" "Afghanistan" "Nawab Mohammad Usman Khan"
36 "1809-01-01" "Afghanistan" "Fateh Khan"
36 "1809-12-31" "Afghanistan" "Fateh Khan"
36 "1810-12-31" "Afghanistan" "Fateh Khan"
36 "1811-12-31" "Afghanistan" "Fateh Khan"
36 "1812-12-31" "Afghanistan" "Fateh Khan"
36 "1813-12-31" "Afghanistan" "Fateh Khan"
36 "1814-12-31" "Afghanistan" "Fateh Khan"
36 "1815-12-31" "Afghanistan" "Fateh Khan"
36 "1816-12-31" "Afghanistan" "Fateh Khan"
36 "1817-12-31" "Afghanistan" "Fateh Khan"
36 "1818-01-01" "Afghanistan" "Mohammad Azim Khan"
36 "1818-12-31" "Afghanistan" "Mohammad Azim Khan"
36 "1819-12-31" "Afghanistan" "Mohammad Azim Khan"
36 "1820-12-31" "Afghanistan" "Mohammad Azim Khan"
36 "1821-12-31" "Afghanistan" "Mohammad Azim Khan"


Thank you all in advance,
Cem

[Nick Cox]

Your example does not include anyone who returned to office later, but this code should cope with that too:

bysort country_id  v3exnamhog (historical) : gen first = historical[1] 
bysort country_id  (first historical) : gen order = sum(first != first[_n-1]) 

list country_name v3exnamhog historical_date first order, sepby(v3exnamhog)

     +---------------------------------------------------------------------------+
     | country_n~e                  v3exnamhog   historic~e        first   order |
     |---------------------------------------------------------------------------|
  1. | Afghanistan    Payinda Khan Mohammadzai   1788-12-31   1788-12-31       1 |
  2. | Afghanistan    Payinda Khan Mohammadzai   1789-12-31   1788-12-31       1 |
  3. | Afghanistan    Payinda Khan Mohammadzai   1790-12-31   1788-12-31       1 |
  4. | Afghanistan    Payinda Khan Mohammadzai   1791-12-31   1788-12-31       1 |
  5. | Afghanistan    Payinda Khan Mohammadzai   1792-12-31   1788-12-31       1 |
     |---------------------------------------------------------------------------|
  6. | Afghanistan                Wafadar Khan   1793-01-01   1793-01-01       2 |
  7. | Afghanistan                Wafadar Khan   1793-12-31   1793-01-01       2 |
  8. | Afghanistan                Wafadar Khan   1794-12-31   1793-01-01       2 |
  9. | Afghanistan                Wafadar Khan   1795-12-31   1793-01-01       2 |
 10. | Afghanistan                Wafadar Khan   1796-12-31   1793-01-01       2 |
 11. | Afghanistan                Wafadar Khan   1797-12-31   1793-01-01       2 |
 12. | Afghanistan                Wafadar Khan   1798-12-31   1793-01-01       2 |
 13. | Afghanistan                Wafadar Khan   1799-12-31   1793-01-01       2 |
 14. | Afghanistan                Wafadar Khan   1800-12-31   1793-01-01       2 |
     |---------------------------------------------------------------------------|
 15. | Afghanistan          Shir Mohammad Khan   1803-01-01   1803-01-01       3 |
 16. | Afghanistan          Shir Mohammad Khan   1803-12-31   1803-01-01       3 |
 17. | Afghanistan          Shir Mohammad Khan   1804-12-31   1803-01-01       3 |
 18. | Afghanistan          Shir Mohammad Khan   1805-12-31   1803-01-01       3 |
 19. | Afghanistan          Shir Mohammad Khan   1806-12-31   1803-01-01       3 |
 20. | Afghanistan          Shir Mohammad Khan   1807-12-31   1803-01-01       3 |
     |---------------------------------------------------------------------------|
 21. | Afghanistan   Nawab Mohammad Usman Khan   1808-01-01   1808-01-01       4 |
 22. | Afghanistan   Nawab Mohammad Usman Khan   1808-12-31   1808-01-01       4 |
     |---------------------------------------------------------------------------|
 23. | Afghanistan                  Fateh Khan   1809-01-01   1809-01-01       5 |
 24. | Afghanistan                  Fateh Khan   1809-12-31   1809-01-01       5 |
 25. | Afghanistan                  Fateh Khan   1810-12-31   1809-01-01       5 |
 26. | Afghanistan                  Fateh Khan   1811-12-31   1809-01-01       5 |
 27. | Afghanistan                  Fateh Khan   1812-12-31   1809-01-01       5 |
 28. | Afghanistan                  Fateh Khan   1813-12-31   1809-01-01       5 |
 29. | Afghanistan                  Fateh Khan   1814-12-31   1809-01-01       5 |
 30. | Afghanistan                  Fateh Khan   1815-12-31   1809-01-01       5 |
 31. | Afghanistan                  Fateh Khan   1816-12-31   1809-01-01       5 |
 32. | Afghanistan                  Fateh Khan   1817-12-31   1809-01-01       5 |
     |---------------------------------------------------------------------------|
 33. | Afghanistan          Mohammad Azim Khan   1818-01-01   1818-01-01       6 |
 34. | Afghanistan          Mohammad Azim Khan   1818-12-31   1818-01-01       6 |
 35. | Afghanistan          Mohammad Azim Khan   1819-12-31   1818-01-01       6 |
 36. | Afghanistan          Mohammad Azim Khan   1820-12-31   1818-01-01       6 |
 37. | Afghanistan          Mohammad Azim Khan   1821-12-31   1818-01-01       6 |
     +---------------------------------------------------------------------------+

.

I don't know what you do if there is more than one person in the role at the same time.

[Cem Mert Dalli]

Thank you, Nick. What you did help me to create id number for each leader, but does not reflect the spell number per person. In the given example, all spell numbers should be numerated as 1 since no person go back and forth. However, if any of the five leader comes back to the office after Mohammad Azim Khan, then spell number will be 2 for the leader who will be listed on line 38.

Btw, there is not more than one person in the role at the same time, thus we don't need to consider that.

[Nick Cox]

I think I see what you're after. Sorry about that.

bysort country_id (historical) : gen spell = sum(v3 != v3[_n-1])
bysort country_id v3 (historical) : gen order = sum(spell != spell[_n-1])
sort country_id historical

[Cem Mert Dalli]

I tried the code on my data, and the result was the order of leaders in each country. Sorry for misleading you, it would be better if we try to discuss on the following piece:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte country_id str10 historical_date str6 country_name str39 v3exnamhog
99 "1788-12-31" "Turkey" "Koca Yusuf Pasha"                       
99 "1789-05-28" "Turkey" "Kethüda Çerkes Hasan Pasha"           
99 "1789-12-03" "Turkey" "Cezayirli Gazi Hasan Pasha"             
99 "1790-03-30" "Turkey" "Rusçuklu Çelebizade Serif Hasan Pasha"
99 "1791-02-15" "Turkey" "Koca Yusuf Pasha"                       
99 "1792-05-04" "Turkey" "Damad Melek Mehmed Pasha"               
99 "1793-12-31" "Turkey" "Damad Melek Mehmed Pasha"               
99 "1794-10-19" "Turkey" "Izzet Mehmed Pasha"                     
99 "1795-12-31" "Turkey" "Izzet Mehmed Pasha"                     
99 "1796-12-31" "Turkey" "Izzet Mehmed Pasha"                     
99 "1797-12-31" "Turkey" "Izzet Mehmed Pasha"                     
99 "1798-08-30" "Turkey" "Yusuf Ziyaüddin Pasha"                 
99 "1799-12-31" "Turkey" "Yusuf Ziyaüddin Pasha"                 
99 "1800-12-31" "Turkey" "Yusuf Ziyaüddin Pasha"                 
99 "1801-12-31" "Turkey" "Yusuf Ziyaüddin Pasha"                 
99 "1802-12-31" "Turkey" "Yusuf Ziyaüddin Pasha"                 
99 "1803-12-31" "Turkey" "Yusuf Ziyaüddin Pasha"                 
99 "1804-12-31" "Turkey" "Yusuf Ziyaüddin Pasha"                 
99 "1805-04-24" "Turkey" "Hafiz Ismail Pasha"                     
99 "1806-11-14" "Turkey" "Ibrahim Hilmi Pasha"                    
99 "1807-06-18" "Turkey" "Çelebi Mustafa Pasha"                  
99 "1808-07-28" "Turkey" `"Alemdar Mustafa Pasha "Bayrakdar""'    
99 "1809-03-01" "Turkey" "Yusuf Ziyaüddin Pasha"                 
99 "1810-12-31" "Turkey" "Yusuf Ziyaüddin Pasha"                 
99 "1811-04-10" "Turkey" "Laz Aziz Ahmed Pasha"                   
99 "1812-09-05" "Turkey" "Hursid Ahmed Pasha"                     
99 "1813-12-31" "Turkey" "Hursid Ahmed Pasha"                     
99 "1814-12-31" "Turkey" "Hursid Ahmed Pasha"                     
99 "1815-04-01" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1816-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1817-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1818-01-05" "Turkey" "Dervis Mehmed Pasha"                    
99 "1819-12-31" "Turkey" "Dervis Mehmed Pasha"                    
99 "1820-01-05" "Turkey" "Seyyid Ali Pasha"                       
99 "1821-04-30" "Turkey" "Haci Salih Pasha"                       
99 "1822-11-10" "Turkey" "Bostancibasi Deli Abdullah Pasha"       
99 "1823-03-10" "Turkey" "Silahdar Ali Pasha"                     
99 "1823-12-13" "Turkey" "Mehmed Said Galip Pasha"                
99 "1824-09-14" "Turkey" "Benderli Selim Sirri Pasha"             
99 "1825-12-31" "Turkey" "Benderli Selim Sirri Pasha"             
99 "1826-12-31" "Turkey" "Benderli Selim Sirri Pasha"             
99 "1827-12-31" "Turkey" "Benderli Selim Sirri Pasha"             
99 "1828-12-31" "Turkey" "Benderli Selim Sirri Pasha"             
99 "1829-01-28" "Turkey" "Resid Mehmed Pasha"                     
99 "1830-12-31" "Turkey" "Resid Mehmed Pasha"                     
99 "1831-12-31" "Turkey" "Resid Mehmed Pasha"                     
99 "1832-12-31" "Turkey" "Resid Mehmed Pasha"                     
99 "1833-02-18" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1834-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1835-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1836-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1837-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1838-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1839-07-02" "Turkey" "Koca Mehmed Hüsrev Pasha"              
99 "1840-06-08" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1841-12-04" "Turkey" "Izzet Mehmed Pasha"                     
99 "1842-08-30" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1843-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1844-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1845-12-31" "Turkey" "Mehmed Emin Rauf Pasha"                 
99 "1846-09-28" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1847-12-31" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1848-04-29" "Turkey" "Ibrahim Sarim Pasha"                    
99 "1848-08-12" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1849-12-31" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1850-12-31" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1851-12-31" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1852-03-05" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1852-10-03" "Turkey" "Damad Mehmed Ali Pasha"                 
99 "1853-05-14" "Turkey" "Mustafa Naili Pasha"                    
99 "1854-05-29" "Turkey" "Kibrisli Mehmed Pasha"                  
99 "1854-11-23" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1855-05-02" "Turkey" "Mehmed Emin Ali Pasha"                  
99 "1856-11-01" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1857-12-31" "Turkey" "Koca Mustafa Resid Pasha"               
99 "1858-01-07" "Turkey" "Mehmed Emin Ali Pasha"                  
99 "1859-12-24" "Turkey" "Mütercim Mehmed Rüstü Pasha"         
99 "1860-05-28" "Turkey" "Kibrisli Mehmed Pasha"                  
99 "1861-08-06" "Turkey" "Mehmed Emin Ali Pasha"                  
99 "1861-11-22" "Turkey" "Keçecizade Mehmed Emin Fuad Pasha"     
99 "1862-12-31" "Turkey" "Keçecizade Mehmed Emin Fuad Pasha"     
99 "1863-01-05" "Turkey" "Yusuf Kamil Pasha"                      
99 "1863-06-01" "Turkey" "Keçecizade Mehmed Emin Fuad Pasha"     
99 "1864-12-31" "Turkey" "Keçecizade Mehmed Emin Fuad Pasha"     
99 "1865-12-31" "Turkey" "Keçecizade Mehmed Emin Fuad Pasha"     
99 "1866-06-05" "Turkey" "Mütercim Mehmed Rüstü Pasha"         
99 "1867-02-11" "Turkey" "Mehmed Emin Ali Pasha"                  
99 "1868-12-31" "Turkey" "Mehmed Emin Ali Pasha"                  
99 "1869-12-31" "Turkey" "Mehmed Emin Ali Pasha"                  
99 "1870-12-31" "Turkey" "Mehmed Emin Ali Pasha"                  
99 "1871-09-07" "Turkey" "Mahmud Nedim Pasha"                     
99 "1872-10-19" "Turkey" "Mütercim Mehmed Rüstü Pasha"         
99 "1873-04-15" "Turkey" "Sirvanlizade Mehmed Rüstü Pasha"      
99 "1874-02-15" "Turkey" "Hüseyin Avni Pasha"                    
99 "1875-04-26" "Turkey" "Ahmed Esad Pasha"                       
99 "1875-08-26" "Turkey" "Mahmud Nedim Pasha"                     
99 "1876-05-12" "Turkey" "Mütercim Mehmed Rüstü Pasha"         
99 "1877-02-05" "Turkey" "Ibrahim Edhem Pasha"                    
99 "1878-06-04" "Turkey" "Mehmed Esad Saffet Pasha"               
99 "1878-12-04" "Turkey" "Tunuslu Hayreddin Pasha"                
end

As you see, Koca Yusuf Pasha and Yusuf Ziyaüddin Pasha left the office and come back again. For the lines 1:4, I want to get result 1, while on line 5 (the second-term of Koca Yusuf Pasha) it should be 2. Similarly for Yusuf Ziyaüddin Pasha, line 12:18 should be 1 while line 23:24 should be 2.

[Nick Cox]

I edited #6 after first posting when I realised what you were saying. You should please try the revised version.

[Cem Mert Dalli]

I edited #6 after first posting when I realised what you were saying. You should please try the revised version.

I have seen it after posting #7, thank you so much! It properly works.