Measuring partners' dispersion by using a Herfindahl index

[Maysam Ayoub]

Hello,
I would like to calculate the geographic dispersion of partners for a specific company in a specific country and in a specific year.

A small subset of the data is:

Year	Country	Audit firm	City	Partner Name
2012	Belgium	Unilever	Brussels	Maysam
2012	Belgium	Unilever	Brussels	Lea
2012	Belgium	Unilever	Antwerp	Sara
2012	Belgium	Unilever	Liege	Anna
2012	Belgium	Unilever	Liege	Sam
2013	France	Nestle	Paris	Farah
2013	France	Nestle	Paris	Angelo
2013	France	Nestle	Bordeaux	Adam
2013	France	Nestle	Toulouse	Anthony
2013	France	Nestle	Strasbourg	George

Intended calculation is the following:

Year	Country	Audit firm	City	Partner Name	Number of partners in each office	Total Number of partners in all cities	Herfindhal Index	More details
2012	Belgium	Unilever	Brussels	Maysam	2	5	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 partners in Brussels, 1 partner in Antwerp and 2 partners in Liege
2012	Belgium	Unilever	Brussels	Lea	2	5	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 partners in Brussels, 1 partner in Antwerp and 2 partners in Liege
2012	Belgium	Unilever	Antwerp	Sara	1	5	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 partners in Brussels, 1 partner in Antwerp and 2 partners in Liege
2012	Belgium	Unilever	Liege	Anna	2	5	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 partners in Brussels, 1 partner in Antwerp and 2 partners in Liege
2012	Belgium	Unilever	Liege	Sam	2	5	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 partners in Brussels, 1 partner in Antwerp and 2 partners in Liege
2013	France	Nestle	Paris	Farah	2	5	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 partners in Brussels, 1 partner in Birdeaux, 1 partners in Toulouse and 1 partner in Strasbourg
2013	France	Nestle	Paris	Angelo	2	5	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 partners in Brussels, 1 partner in Birdeaux, 1 partners in Toulouse and 1 partner in Strasbourg
2013	France	Nestle	Bordeaux	Adam	1	5	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 partners in Brussels, 1 partner in Birdeaux, 1 partners in Toulouse and 1 partner in Strasbourg
2013	France	Nestle	Toulouse	Anthony	1	5	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 partners in Brussels, 1 partner in Birdeaux, 1 partners in Toulouse and 1 partner in Strasbourg
2013	France	Nestle	Strasbourg	George	1	5	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 partners in Brussels, 1 partner in Birdeaux, 1 partners in Toulouse and 1 partner in Strasbourg

Thanking you in advance

[Nick Cox]

Please read https://www.statalist.org/forums/help#stata and then use dataex to give an example of real(istic) data.

[Maysam Ayoub]

Hello again

Please find the below:
* Example generated by -dataex-. For more info, type help dataex
clear
input int Year str7 Country str8 CompanyName str10 City str7 Partnername
2012 "Belgium" "Unilever" "Brussels" "Maysam"
2012 "Belgium" "Unilever" "Brussels" "Lea"
2012 "Belgium" "Unilever" "Antwerp" "Sara"
2012 "Belgium" "Unilever" "Liege" "Anna"
2012 "Belgium" "Unilever" "Liege" "Sam"
2012 "Belgium" "Unilever" "Liege" "Anna"
2013 "France" "Nestle" "Paris" "Angelo"
2013 "France" "Nestle" "Bordeaux" "Adam"
2013 "France" "Nestle" "Toulouse" "Anthony"
2013 "France" "Nestle" "Strasbourg" "George"
2013 "France" "Nestle" "Paris" "Farah"
end
[/CODE]

intended solution:

Year	Country	CompanyName	City	Partnername	Herfindhal Index	More details
2012	Belgium	Unilever	Brussels	Maysam	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 unique partners in Brussels, 1 unique partner in Antwerp and 2 unique partners in Liege
2012	Belgium	Unilever	Brussels	Lea	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 unique partners in Brussels, 1 unique partner in Antwerp and 2 unique partners in Liege
2012	Belgium	Unilever	Antwerp	Sara	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 unique partners in Brussels, 1 unique partner in Antwerp and 2 unique partners in Liege
2012	Belgium	Unilever	Liege	Anna	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 unique partners in Brussels, 1 unique partner in Antwerp and 2 unique partners in Liege
2012	Belgium	Unilever	Liege	Sam	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 unique partners in Brussels, 1 unique partner in Antwerp and 2 unique partners in Liege
2012	Belgium	Unilever	Liege	Anna	36%	(2/5)^2+(1/5)^2+(2/5)^2	2 unique partners in Brussels, 1 unique partner in Antwerp and 2 unique partners in Liege
2013	France	Nestle	Paris	Angelo	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 unique partners in Paris, 1 unique partner in B0rdeaux, 1 unique partners in Toulouse and 1 unique partner in Strasbourg
2013	France	Nestle	Bordeaux	Adam	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 unique partners in Paris, 1 unique partner in B0rdeaux, 1 unique partners in Toulouse and 1 unique partner in Strasbourg
2013	France	Nestle	Toulouse	Anthony	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 unique partners in Paris, 1 unique partner in B0rdeaux, 1 unique partners in Toulouse and 1 unique partner in Strasbourg
2013	France	Nestle	Strasbourg	George	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 unique partners in Paris, 1 unique partner in B0rdeaux, 1 unique partners in Toulouse and 1 unique partner in Strasbourg
2013	France	Nestle	Paris	Farah	28%	(2/5)^2+(1/5)^2+(1/5)^2+(1/5)^2	2 unique partners in Paris, 1 unique partner in B0rdeaux, 1 unique partners in Toulouse and 1 unique partner in Strasbourg

Thank you!

[Nick Cox]

Thanks for the data example.

* Example generated by -dataex-. For more info, type help dataex
clear
input int Year str7 Country str8 CompanyName str10 City str7 Partnername
2012 "Belgium" "Unilever" "Brussels" "Maysam"
2012 "Belgium" "Unilever" "Brussels" "Lea"
2012 "Belgium" "Unilever" "Antwerp" "Sara"
2012 "Belgium" "Unilever" "Liege" "Anna"
2012 "Belgium" "Unilever" "Liege" "Sam"
2012 "Belgium" "Unilever" "Liege" "Anna"
2013 "France" "Nestle" "Paris" "Angelo"
2013 "France" "Nestle" "Bordeaux" "Adam"
2013 "France" "Nestle" "Toulouse" "Anthony"
2013 "France" "Nestle" "Strasbourg" "George"
2013 "France" "Nestle" "Paris" "Farah"
end

bysort Year Country Company City Partner : gen tag = _n == 1 
by Year Country Company City: egen numer = total(tag)
by Year Country Company : egen denom = total(tag)
by Year Country Company City : gen TAG = _n == 1 
by Year Country Company : egen wanted = total(TAG * (numer/denom)^2)

egen which = group(Year Country Company), label 
tabdisp which, c(wanted)

----------------------------------
group(Year Country    |
CompanyName)          |     wanted
----------------------+-----------
2012 Belgium Unilever |        .36
   2013 France Nestle |        .28
----------------------------------

[Maysam Ayoub]

Dear Nick, thank you so much!
That was extremely useful

[Nick Cox]

Here's another solution using entropyetc from SSC as announced at https://www.statalist.org/forums/for...lable-from-ssc

* Example generated by -dataex-. For more info, type help dataex
clear
input int Year str7 Country str8 CompanyName str10 City str7 Partnername
2012 "Belgium" "Unilever" "Brussels" "Maysam"
2012 "Belgium" "Unilever" "Brussels" "Lea"
2012 "Belgium" "Unilever" "Antwerp" "Sara"
2012 "Belgium" "Unilever" "Liege" "Anna"
2012 "Belgium" "Unilever" "Liege" "Sam"
2012 "Belgium" "Unilever" "Liege" "Anna"
2013 "France" "Nestle" "Paris" "Angelo"
2013 "France" "Nestle" "Bordeaux" "Adam"
2013 "France" "Nestle" "Toulouse" "Anthony"
2013 "France" "Nestle" "Strasbourg" "George"
2013 "France" "Nestle" "Paris" "Farah"
end


save Partneretc, replace

contract *
entropyetc City , by(Year Country Company) gen(1=H 3=R)
collapse H R ,by(Year Country Company)
merge 1:m Year Country Company  using Partneretc

sort Year Country Company City

list , sepby(Year)


     +----------------------------------------------------------------------------------+
     | Year   Country   Compan~e          H     R         City   Partne~e        _merge |
     |----------------------------------------------------------------------------------|
  1. | 2012   Belgium   Unilever    1.05492   .36      Antwerp       Sara   Matched (3) |
  2. | 2012   Belgium   Unilever    1.05492   .36     Brussels     Maysam   Matched (3) |
  3. | 2012   Belgium   Unilever    1.05492   .36     Brussels        Lea   Matched (3) |
  4. | 2012   Belgium   Unilever    1.05492   .36        Liege       Anna   Matched (3) |
  5. | 2012   Belgium   Unilever    1.05492   .36        Liege        Sam   Matched (3) |
  6. | 2012   Belgium   Unilever    1.05492   .36        Liege       Anna   Matched (3) |
     |----------------------------------------------------------------------------------|
  7. | 2013    France     Nestle   1.332179   .28     Bordeaux       Adam   Matched (3) |
  8. | 2013    France     Nestle   1.332179   .28        Paris     Angelo   Matched (3) |
  9. | 2013    France     Nestle   1.332179   .28        Paris      Farah   Matched (3) |
 10. | 2013    France     Nestle   1.332179   .28   Strasbourg     George   Matched (3) |
 11. | 2013    France     Nestle   1.332179   .28     Toulouse    Anthony   Matched (3) |
     +----------------------------------------------------------------------------------+

[Maysam Ayoub]

Oh nice! Thank you again