Calculating a score for each firm within the same industry

[Monowar Maroon]

Dear All,

I hope you are staying well and keeping safe. I am new to variable creating codes and would be very grateful if you could advise on the following issue that I have been struggling with for the last few days.
I have a panel dataset and need to calculate an innovation score for each firm within the same industry using the following formula:

Innovation score = (number of firms with a worse value + (number of firms with the same value as the current firm's value/2))/ total number of firms. For example, let say there 10 firms in one industry and the focal firm's value is 80. There is another firm that has the same value. All other firms have scores lower than 80. So the score will be = (8 + (2/2))/10 = 0.90.

I need to calculate for all firms within other industries.

Many thanks in advance and best wishes.

[Jared Greathouse]

Monowar Maroon We can trade. You provide me an example dataset with the dataex command, I'll try and help you.

Your query seems simple enough, all I need is a dataset to work with.

[Monowar Maroon]

Dear Jared Greathouse,

Thanks a lot for your reply and willingness to help. I have tried to insert a sample using a dataex for the first time. I hope it worked and you will be able to see it.

ID is a firm code and INNOV is an innovation score.

Kind regards


Monowar

* Example generated by -dataex-. For more info, type help dataex
clear
input byte ID int Year str46 Industry byte INNOV
1 2009 "Food & Staples Retailing"                   12
1 2010 "Food & Staples Retailing"                   12
1 2011 "Food & Staples Retailing"                   12
1 2012 "Food & Staples Retailing"                   12
1 2013 "Food & Staples Retailing"                   12
1 2014 "Food & Staples Retailing"                   11
1 2015 "Food & Staples Retailing"                   11
1 2016 "Food & Staples Retailing"                   11
1 2017 "Food & Staples Retailing"                   11
1 2018 "Food & Staples Retailing"                   11
1 2019 "Food & Staples Retailing"                   11
1 2020 "Food & Staples Retailing"                   11
2 2009 "Oil, Gas & Consumable Fuels"                 4
2 2010 "Oil, Gas & Consumable Fuels"                 3
2 2011 "Oil, Gas & Consumable Fuels"                 3
2 2012 "Oil, Gas & Consumable Fuels"                 8
2 2013 "Oil, Gas & Consumable Fuels"                 8
2 2014 "Oil, Gas & Consumable Fuels"                 8
2 2015 "Oil, Gas & Consumable Fuels"                 7
2 2016 "Oil, Gas & Consumable Fuels"                 3
2 2017 "Oil, Gas & Consumable Fuels"                 3
2 2018 "Oil, Gas & Consumable Fuels"                 3
2 2019 "Oil, Gas & Consumable Fuels"                13
3 2009 "Oil, Gas & Consumable Fuels"                 5
3 2010 "Oil, Gas & Consumable Fuels"                 5
3 2011 "Oil, Gas & Consumable Fuels"                 5
3 2012 "Oil, Gas & Consumable Fuels"                 3
3 2013 "Oil, Gas & Consumable Fuels"                 5
3 2014 "Oil, Gas & Consumable Fuels"                 8
3 2015 "Oil, Gas & Consumable Fuels"                10
3 2016 "Oil, Gas & Consumable Fuels"                 8
3 2017 "Oil, Gas & Consumable Fuels"                 8
3 2018 "Oil, Gas & Consumable Fuels"                10
3 2019 "Oil, Gas & Consumable Fuels"                10
3 2020 "Oil, Gas & Consumable Fuels"                10
4 2009 "Oil, Gas & Consumable Fuels"                 9
4 2010 "Oil, Gas & Consumable Fuels"                 8
4 2011 "Oil, Gas & Consumable Fuels"                 9
4 2012 "Oil, Gas & Consumable Fuels"                 9
4 2013 "Oil, Gas & Consumable Fuels"                 8
4 2014 "Oil, Gas & Consumable Fuels"                 8
4 2015 "Oil, Gas & Consumable Fuels"                 8
4 2016 "Oil, Gas & Consumable Fuels"                 9
4 2017 "Oil, Gas & Consumable Fuels"                 9
4 2018 "Oil, Gas & Consumable Fuels"                 9
4 2019 "Oil, Gas & Consumable Fuels"                 9
4 2020 "Oil, Gas & Consumable Fuels"                10
5 2009 "Automobiles"                                14
5 2010 "Automobiles"                                15
5 2011 "Automobiles"                                 0
5 2012 "Automobiles"                                15
5 2013 "Automobiles"                                15
5 2014 "Automobiles"                                15
5 2015 "Automobiles"                                15
5 2016 "Automobiles"                                15
5 2017 "Automobiles"                                15
5 2018 "Automobiles"                                15
5 2019 "Automobiles"                                15
5 2020 "Automobiles"                                15
6 2009 "Internet & Direct Marketing Retail"          0
6 2010 "Internet & Direct Marketing Retail"          0
6 2011 "Internet & Direct Marketing Retail"          4
6 2012 "Internet & Direct Marketing Retail"          6
6 2013 "Internet & Direct Marketing Retail"          6
6 2014 "Internet & Direct Marketing Retail"          6
6 2015 "Internet & Direct Marketing Retail"          6
6 2016 "Internet & Direct Marketing Retail"          5
6 2017 "Internet & Direct Marketing Retail"          6
6 2018 "Internet & Direct Marketing Retail"          8
6 2019 "Internet & Direct Marketing Retail"         10
6 2020 "Internet & Direct Marketing Retail"         10
7 2009 "Oil, Gas & Consumable Fuels"                 8
7 2010 "Oil, Gas & Consumable Fuels"                 8
7 2011 "Oil, Gas & Consumable Fuels"                 8
7 2012 "Oil, Gas & Consumable Fuels"                 8
7 2013 "Oil, Gas & Consumable Fuels"                 8
7 2014 "Oil, Gas & Consumable Fuels"                 8
7 2015 "Oil, Gas & Consumable Fuels"                 8
7 2016 "Oil, Gas & Consumable Fuels"                 8
7 2017 "Oil, Gas & Consumable Fuels"                 9
7 2018 "Oil, Gas & Consumable Fuels"                 9
7 2019 "Oil, Gas & Consumable Fuels"                 9
7 2020 "Oil, Gas & Consumable Fuels"                 9
8 2009 "Automobiles"                                15
8 2010 "Automobiles"                                15
8 2011 "Automobiles"                                15
8 2012 "Automobiles"                                14
8 2013 "Automobiles"                                11
8 2014 "Automobiles"                                11
8 2015 "Automobiles"                                11
8 2016 "Automobiles"                                13
8 2017 "Automobiles"                                13
8 2018 "Automobiles"                                13
8 2019 "Automobiles"                                13
8 2020 "Automobiles"                                14
9 2009 "Technology Hardware, Storage & Peripherals" 11
9 2010 "Technology Hardware, Storage & Peripherals" 11
9 2011 "Technology Hardware, Storage & Peripherals" 13
9 2012 "Technology Hardware, Storage & Peripherals" 11
9 2013 "Technology Hardware, Storage & Peripherals" 11
end

[Monowar Maroon]

Dear Jared Greathouse,

I forgot to mention that this is a panel data. So new scores are to be calculated for each year and within each industry.

Kind regards,

Monowar

[Jared Greathouse]

Monowar Maroon Show me the code you've attempted to solve the problem so far.

[Monowar Maroon]

Dear Jared Greathouse,

I was trying to calculate in Excel initially and could not get it. Since I am a new to Stata, I was wondering is there any command to execute this.

Many thanks,

Monowar

[Jared Greathouse]

Monowar Maroon Read the help files for the generate and egen command. There may be a way to do this, but there's definitely not a command to do this.

I'll get to this in a few, but seriously, read the help files. I know they can be like reading Russian sometimes, but they can be helpful. I'll look at this myself in a few.

[Ken Chui]

Dear Jared Greathouse,

I forgot to mention that this is a panel data. So new scores are to be calculated for each year and within each industry.

Kind regards,

Monowar

How? I don't understand this request. Every year-industry combination in your data example is unique (aka just one case). If you want an innovation by year by industry then whatever you have in the variable is the answer.

[Monowar Maroon]

Dear Ken Chui,

Thank you for your comment. The main point here is to calculate a unique score for each firm in comparison with peers in the same industry. This is how things are calculated by the Refinitiv database. So, just simply taking a firm score, it would be preferred to take a more comprehensive approach based on scores of all firms within an industry. So the calculation is as follows:

Innovation score = (number of firms with a worse value + (number of firms with the same value as the current firm's value/2))/ total number of firms. For example, let say there 10 firms in one industry and the focal firm's value is 80. There is another firm that has the same value. All other firms have scores lower than 80. So the score will be = (8 + (2/2))/10 = 0.90.

Definitely, the above apporach would give different results when running regressions.

Kind regards

[Ken Chui]

Dear Ken Chui,

Thank you for your comment. The main point here is to calculate a unique score for each firm in comparison with peers in the same industry. This is how things are calculated by the Refinitiv database. So, just simply taking a firm score, it would be preferred to take a more comprehensive approach based on scores of all firms within an industry. So the calculation is as follows:

Innovation score = (number of firms with a worse value + (number of firms with the same value as the current firm's value/2))/ total number of firms. For example, let say there 10 firms in one industry and the focal firm's value is 80. There is another firm that has the same value. All other firms have scores lower than 80. So the score will be = (8 + (2/2))/10 = 0.90.

Definitely, the above apporach would give different results when running regressions.

Kind regards

I think I didn't get my point across, or we considered the phrase "for each year and within each industry" differently. What I was referring to is that given the example, there is no score to be computed (everyone will be 0.5 / 1) because in each year, there is only one case in each industry. So there, no multiple cases for you to compute this innovation index.

But enough of my own confusion, there is an approach that may be useful. In this one, I did not consider year (because it would make sense to do so), and only computed the score within industry only:

rename *, lower

preserve
gen samescore = 1
collapse (sum) samescore, by(industry innov)

bysort industry: egen totalcase = total(samescore)
bysort industry: gen lowerthan = 0 if _n == 1
bysort industry: replace lowerthan = samescore[_n-1] + lowerthan[_n-1] if lowerthan == .

gen wanted = (lowerthan + samescore/2) / totalcase

keep industry innov wanted
save tomerge, replace
restore

merge m:1 industry innov using tomerge
drop _merge

dataex

Results:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id int year str46 industry byte innov float wanted
5 2011 "Automobiles"                                 0 .020833334
8 2014 "Automobiles"                                11  .10416666
8 2015 "Automobiles"                                11  .10416666
8 2013 "Automobiles"                                11  .10416666
8 2016 "Automobiles"                                13        .25
8 2018 "Automobiles"                                13        .25
8 2017 "Automobiles"                                13        .25
8 2019 "Automobiles"                                13        .25
8 2012 "Automobiles"                                14   .3958333
5 2009 "Automobiles"                                14   .3958333
8 2020 "Automobiles"                                14   .3958333
5 2010 "Automobiles"                                15   .7291667
5 2012 "Automobiles"                                15   .7291667
5 2015 "Automobiles"                                15   .7291667
8 2011 "Automobiles"                                15   .7291667
5 2017 "Automobiles"                                15   .7291667
8 2010 "Automobiles"                                15   .7291667
5 2018 "Automobiles"                                15   .7291667
5 2019 "Automobiles"                                15   .7291667
5 2016 "Automobiles"                                15   .7291667
5 2013 "Automobiles"                                15   .7291667
5 2020 "Automobiles"                                15   .7291667
8 2009 "Automobiles"                                15   .7291667
5 2014 "Automobiles"                                15   .7291667
1 2019 "Food & Staples Retailing"                   11  .29166666
1 2014 "Food & Staples Retailing"                   11  .29166666
1 2015 "Food & Staples Retailing"                   11  .29166666
1 2020 "Food & Staples Retailing"                   11  .29166666
1 2016 "Food & Staples Retailing"                   11  .29166666
1 2017 "Food & Staples Retailing"                   11  .29166666
1 2018 "Food & Staples Retailing"                   11  .29166666
1 2009 "Food & Staples Retailing"                   12   .7916667
1 2010 "Food & Staples Retailing"                   12   .7916667
1 2012 "Food & Staples Retailing"                   12   .7916667
1 2011 "Food & Staples Retailing"                   12   .7916667
1 2013 "Food & Staples Retailing"                   12   .7916667
6 2010 "Internet & Direct Marketing Retail"          0  .08333334
6 2009 "Internet & Direct Marketing Retail"          0  .08333334
6 2011 "Internet & Direct Marketing Retail"          4  .20833333
6 2016 "Internet & Direct Marketing Retail"          5  .29166666
6 2015 "Internet & Direct Marketing Retail"          6   .5416667
6 2012 "Internet & Direct Marketing Retail"          6   .5416667
6 2017 "Internet & Direct Marketing Retail"          6   .5416667
6 2014 "Internet & Direct Marketing Retail"          6   .5416667
6 2013 "Internet & Direct Marketing Retail"          6   .5416667
6 2018 "Internet & Direct Marketing Retail"          8   .7916667
6 2019 "Internet & Direct Marketing Retail"         10   .9166667
6 2020 "Internet & Direct Marketing Retail"         10   .9166667
2 2011 "Oil, Gas & Consumable Fuels"                 3  .06382979
2 2016 "Oil, Gas & Consumable Fuels"                 3  .06382979
3 2012 "Oil, Gas & Consumable Fuels"                 3  .06382979
2 2010 "Oil, Gas & Consumable Fuels"                 3  .06382979
2 2018 "Oil, Gas & Consumable Fuels"                 3  .06382979
2 2017 "Oil, Gas & Consumable Fuels"                 3  .06382979
2 2009 "Oil, Gas & Consumable Fuels"                 4  .13829787
3 2010 "Oil, Gas & Consumable Fuels"                 5  .19148937
3 2009 "Oil, Gas & Consumable Fuels"                 5  .19148937
3 2013 "Oil, Gas & Consumable Fuels"                 5  .19148937
3 2011 "Oil, Gas & Consumable Fuels"                 5  .19148937
2 2015 "Oil, Gas & Consumable Fuels"                 7  .24468085
7 2009 "Oil, Gas & Consumable Fuels"                 8   .4468085
7 2016 "Oil, Gas & Consumable Fuels"                 8   .4468085
4 2010 "Oil, Gas & Consumable Fuels"                 8   .4468085
2 2012 "Oil, Gas & Consumable Fuels"                 8   .4468085
7 2012 "Oil, Gas & Consumable Fuels"                 8   .4468085
3 2016 "Oil, Gas & Consumable Fuels"                 8   .4468085
3 2014 "Oil, Gas & Consumable Fuels"                 8   .4468085
3 2017 "Oil, Gas & Consumable Fuels"                 8   .4468085
2 2013 "Oil, Gas & Consumable Fuels"                 8   .4468085
7 2013 "Oil, Gas & Consumable Fuels"                 8   .4468085
4 2014 "Oil, Gas & Consumable Fuels"                 8   .4468085
4 2015 "Oil, Gas & Consumable Fuels"                 8   .4468085
4 2013 "Oil, Gas & Consumable Fuels"                 8   .4468085
7 2014 "Oil, Gas & Consumable Fuels"                 8   .4468085
7 2015 "Oil, Gas & Consumable Fuels"                 8   .4468085
2 2014 "Oil, Gas & Consumable Fuels"                 8   .4468085
7 2010 "Oil, Gas & Consumable Fuels"                 8   .4468085
7 2011 "Oil, Gas & Consumable Fuels"                 8   .4468085
7 2019 "Oil, Gas & Consumable Fuels"                 9   .7553192
4 2009 "Oil, Gas & Consumable Fuels"                 9   .7553192
4 2011 "Oil, Gas & Consumable Fuels"                 9   .7553192
4 2012 "Oil, Gas & Consumable Fuels"                 9   .7553192
4 2016 "Oil, Gas & Consumable Fuels"                 9   .7553192
4 2018 "Oil, Gas & Consumable Fuels"                 9   .7553192
4 2019 "Oil, Gas & Consumable Fuels"                 9   .7553192
7 2017 "Oil, Gas & Consumable Fuels"                 9   .7553192
4 2017 "Oil, Gas & Consumable Fuels"                 9   .7553192
7 2018 "Oil, Gas & Consumable Fuels"                 9   .7553192
7 2020 "Oil, Gas & Consumable Fuels"                 9   .7553192
3 2019 "Oil, Gas & Consumable Fuels"                10   .9255319
3 2015 "Oil, Gas & Consumable Fuels"                10   .9255319
4 2020 "Oil, Gas & Consumable Fuels"                10   .9255319
3 2020 "Oil, Gas & Consumable Fuels"                10   .9255319
3 2018 "Oil, Gas & Consumable Fuels"                10   .9255319
2 2019 "Oil, Gas & Consumable Fuels"                13   .9893617
9 2013 "Technology Hardware, Storage & Peripherals" 11         .4
9 2009 "Technology Hardware, Storage & Peripherals" 11         .4
9 2012 "Technology Hardware, Storage & Peripherals" 11         .4
9 2010 "Technology Hardware, Storage & Peripherals" 11         .4
9 2011 "Technology Hardware, Storage & Peripherals" 13         .9
end

[Monowar Maroon]

Dear Ken Chui,

Many thanks for your time and help. I greatly appreciate. Your help motivated me to learn more.

Kind regards and all the best