Patent Inventor Data - Counting and Averaging Collaborators

Tom Menard

Join Date: Jul 2018
Posts: 29

Patent Inventor Data - Counting and Averaging Collaborators

03 Dec 2021, 07:55

Dear Statalist Users,

I have a dataset containing the id of a patent, the inventors of this patent as well as the current owner of the patent (i.e. the firm). My data is panel-data, i.e. showing the owner of the patent in a respective year. The inventors therefore also appear each year.

What I'm trying to compute is the average number of collaborators (i.e. other inventors that one inventor works together with) for each inventor.

As an example let's assume I have two patents, 1 and 2. On patent 1 we have inventors A and B; on patent 2 we have inventors B and C. This would mean that inventor A has one collaborator, inventor B has two collaborators and inventor C has one collaborator. This computed over multiple patents and then averaged is what I am lookig for.

Unfortunately I have no idea how I would compute such a measure with my data. I have tried looking at network analysis, but also not really sure if this is the way to go or if this can be computed in a much simpler way.

I would appreciate any hints or ideas.

Thanks for your help!

Here is an example of my data with 400 observations:

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str21 firm float year str15 patent_id str74 inventor_id
"Merck & Co"           2015 "10000454" "fl:h_ln:josien-1"          
"Merck & Co"           2019 "10000454" "fl:c_ln:plummer-4"        
"Merck & Co"           2015 "10000454" "fl:a_ln:nair-6"            
"Merck & Co"           2018 "10000454" "fl:h_ln:josien-1"          
"Merck & Co"           2019 "10000454" "fl:c_ln:zhu-4"            
"Merck & Co"           2018 "10000454" "fl:a_ln:nair-6"            
"Merck & Co"           2017 "10000454" "fl:b_ln:hu-27"            
"Merck & Co"           2018 "10000454" "fl:j_ln:frie-1"            
"Merck & Co"           2015 "10000454" "fl:b_ln:hu-27"            
"Merck & Co"           2016 "10000454" "fl:z_ln:chen-645"          
"Merck & Co"           2018 "10000454" "fl:p_ln:biju-1"            
"Merck & Co"           2015 "10000454" "fl:t_ln:blizzard-1"        
"Merck & Co"           2018 "10000454" "fl:w_ln:hagmann-2"        
"Merck & Co"           2017 "10000454" "fl:h_ln:josien-1"          
"Merck & Co"           2016 "10000454" "fl:h_ln:josien-1"          
"Merck & Co"           2015 "10000454" "fl:m_ln:clements-3"        
"Merck & Co"           2019 "10000454" "fl:t_ln:biftu-1"          
"Merck & Co"           2017 "10000454" "fl:t_ln:blizzard-1"        
"Merck & Co"           2018 "10000454" "fl:z_ln:chen-645"          
"Merck & Co"           2017 "10000454" "fl:j_ln:frie-1"            
"Merck & Co"           2019 "10000454" "fl:m_ln:cui-1"            
"Merck & Co"           2019 "10000454" "fl:p_ln:biju-1"            
"Merck & Co"           2018 "10000454" "fl:m_ln:cui-1"            
"Merck & Co"           2016 "10000454" "fl:c_ln:plummer-4"        
"Merck & Co"           2017 "10000454" "fl:p_ln:biju-1"            
"Merck & Co"           2017 "10000454" "fl:a_ln:nair-6"            
"Merck & Co"           2019 "10000454" "fl:b_ln:hu-27"            
"Merck & Co"           2018 "10000454" "fl:c_ln:zhu-4"            
"Merck & Co"           2015 "10000454" "fl:w_ln:hagmann-2"        
"Merck & Co"           2017 "10000454" "fl:t_ln:biftu-1"          
"Merck & Co"           2016 "10000454" "fl:a_ln:nair-6"            
"Merck & Co"           2018 "10000454" "fl:b_ln:hu-27"            
"Merck & Co"           2016 "10000454" "fl:t_ln:biftu-1"          
"Merck & Co"           2015 "10000454" "fl:p_ln:biju-1"            
"Merck & Co"           2019 "10000454" "fl:t_ln:blizzard-1"        
"Merck & Co"           2015 "10000454" "fl:z_ln:chen-645"          
"Merck & Co"           2018 "10000454" "fl:c_ln:plummer-4"        
"Merck & Co"           2019 "10000454" "fl:z_ln:chen-645"          
"Merck & Co"           2019 "10000454" "fl:m_ln:clements-3"        
"Merck & Co"           2017 "10000454" "fl:w_ln:hagmann-2"        
"Merck & Co"           2016 "10000454" "fl:b_ln:hu-27"            
"Merck & Co"           2018 "10000454" "fl:t_ln:biftu-1"          
"Merck & Co"           2015 "10000454" "fl:j_ln:frie-1"            
"Merck & Co"           2016 "10000454" "fl:j_ln:frie-1"            
"Merck & Co"           2017 "10000454" "fl:z_ln:chen-645"          
"Merck & Co"           2016 "10000454" "fl:m_ln:cui-1"            
"Merck & Co"           2019 "10000454" "fl:h_ln:josien-1"          
"Merck & Co"           2017 "10000454" "fl:m_ln:clements-3"        
"Merck & Co"           2016 "10000454" "fl:c_ln:zhu-4"            
"Merck & Co"           2015 "10000454" "fl:c_ln:zhu-4"            
"Merck & Co"           2019 "10000454" "fl:w_ln:hagmann-2"        
"Merck & Co"           2015 "10000454" "fl:t_ln:biftu-1"          
"Merck & Co"           2017 "10000454" "fl:c_ln:plummer-4"        
"Merck & Co"           2016 "10000454" "fl:t_ln:blizzard-1"        
"Merck & Co"           2015 "10000454" "fl:m_ln:cui-1"            
"Merck & Co"           2015 "10000454" "fl:c_ln:plummer-4"        
"Merck & Co"           2018 "10000454" "fl:t_ln:blizzard-1"        
"Merck & Co"           2016 "10000454" "fl:m_ln:clements-3"        
"Merck & Co"           2016 "10000454" "fl:p_ln:biju-1"            
"Merck & Co"           2018 "10000454" "fl:m_ln:clements-3"        
"Merck & Co"           2019 "10000454" "fl:a_ln:nair-6"            
"Merck & Co"           2016 "10000454" "fl:w_ln:hagmann-2"        
"Merck & Co"           2017 "10000454" "fl:m_ln:cui-1"            
"Merck & Co"           2017 "10000454" "fl:c_ln:zhu-4"            
"Merck & Co"           2019 "10000454" "fl:j_ln:frie-1"            
"Bristol Myers Squibb" 2018 "10000466" "fl:j_ln:corte-1"          
"Bristol Myers Squibb" 2017 "10000466" "fl:s_ln:srivastava-40"    
"Bristol Myers Squibb" 2018 "10000466" "fl:s_ln:srivastava-40"    
"Bristol Myers Squibb" 2019 "10000466" "fl:j_ln:corte-1"          
"Bristol Myers Squibb" 2018 "10000466" "fl:m_ln:orwat-1"          
"Bristol Myers Squibb" 2017 "10000466" "fl:m_ln:orwat-1"          
"Bristol Myers Squibb" 2017 "10000466" "fl:j_ln:corte-1"          
"Bristol Myers Squibb" 2018 "10000466" "fl:l_ln:smith-29"          
"Bristol Myers Squibb" 2019 "10000466" "fl:d_ln:pinto-7"          
"Bristol Myers Squibb" 2019 "10000466" "fl:s_ln:srivastava-40"    
"Bristol Myers Squibb" 2017 "10000466" "fl:d_ln:pinto-7"          
"Bristol Myers Squibb" 2017 "10000466" "fl:l_ln:smith-29"          
"Bristol Myers Squibb" 2018 "10000466" "fl:d_ln:pinto-7"          
"Bristol Myers Squibb" 2019 "10000466" "fl:m_ln:orwat-1"          
"Bristol Myers Squibb" 2019 "10000466" "fl:l_ln:smith-29"          
"Johnson & Johnson"    2019 "10000473" "fl:r_ln:zhang-98"          
"Johnson & Johnson"    2018 "10000473" "fl:g_ln:xu-18"            
"Johnson & Johnson"    2018 "10000473" "fl:r_ln:zhang-98"          
"Johnson & Johnson"    2019 "10000473" "fl:t_ln:lu-76"            
"Johnson & Johnson"    2017 "10000473" "fl:g_ln:xu-18"            
"Johnson & Johnson"    2017 "10000473" "fl:f_ln:song-2"            
"Johnson & Johnson"    2017 "10000473" "fl:r_ln:zhang-98"          
"Johnson & Johnson"    2018 "10000473" "fl:m_ln:gaul-1"            
"Johnson & Johnson"    2019 "10000473" "fl:f_ln:song-2"            
"Johnson & Johnson"    2019 "10000473" "fl:g_ln:xu-18"            
"Johnson & Johnson"    2018 "10000473" "fl:f_ln:song-2"            
"Johnson & Johnson"    2017 "10000473" "fl:t_ln:lu-76"            
"Johnson & Johnson"    2017 "10000473" "fl:m_ln:gaul-1"            
"Johnson & Johnson"    2019 "10000473" "fl:m_ln:gaul-1"            
"Johnson & Johnson"    2018 "10000473" "fl:s_ln:yang-342"          
"Johnson & Johnson"    2019 "10000473" "fl:s_ln:yang-342"          
"Johnson & Johnson"    2017 "10000473" "fl:s_ln:yang-342"          
"Johnson & Johnson"    2018 "10000473" "fl:t_ln:lu-76"            
"Bristol Myers Squibb" 2019 "10000480" "fl:s_ln:spergel-1"        
"Bristol Myers Squibb" 2017 "10000480" "fl:d_ln:weinstein-8"      
"Bristol Myers Squibb" 2018 "10000480" "fl:d_ln:weinstein-8"      
"Bristol Myers Squibb" 2019 "10000480" "fl:s_ln:lin-712"          
"Bristol Myers Squibb" 2016 "10000480" "fl:s_ln:wrobleski-1"      
"Bristol Myers Squibb" 2019 "10000480" "fl:s_ln:wrobleski-1"      
"Bristol Myers Squibb" 2016 "10000480" "fl:d_ln:weinstein-8"      
"Bristol Myers Squibb" 2017 "10000480" "fl:y_ln:zhang-15"          
"Bristol Myers Squibb" 2018 "10000480" "fl:s_ln:wrobleski-1"      
"Bristol Myers Squibb" 2019 "10000480" "fl:d_ln:batt-3"            
"Bristol Myers Squibb" 2019 "10000480" "fl:q_ln:liu-6"            
"Bristol Myers Squibb" 2017 "10000480" "fl:s_ln:wrobleski-1"      
"Bristol Myers Squibb" 2018 "10000480" "fl:j_ln:tokarski-2"        
"Bristol Myers Squibb" 2017 "10000480" "fl:s_ln:lin-712"          
"Bristol Myers Squibb" 2019 "10000480" "fl:j_ln:tokarski-2"        
"Bristol Myers Squibb" 2017 "10000480" "fl:d_ln:batt-3"            
"Bristol Myers Squibb" 2018 "10000480" "fl:c_ln:liu-601"          
"Bristol Myers Squibb" 2019 "10000480" "fl:y_ln:zhang-15"          
"Bristol Myers Squibb" 2018 "10000480" "fl:r_ln:moslin-1"          
"Bristol Myers Squibb" 2019 "10000480" "fl:d_ln:weinstein-8"      
"Bristol Myers Squibb" 2016 "10000480" "fl:s_ln:lin-712"          
"Bristol Myers Squibb" 2016 "10000480" "fl:j_ln:tokarski-2"        
"Bristol Myers Squibb" 2018 "10000480" "fl:y_ln:zhang-15"          
"Bristol Myers Squibb" 2017 "10000480" "fl:j_ln:tokarski-2"        
"Bristol Myers Squibb" 2018 "10000480" "fl:d_ln:batt-3"            
"Bristol Myers Squibb" 2016 "10000480" "fl:r_ln:moslin-1"          
"Bristol Myers Squibb" 2016 "10000480" "fl:a_ln:kumar-164"        
"Bristol Myers Squibb" 2018 "10000480" "fl:s_ln:lin-712"          
"Bristol Myers Squibb" 2016 "10000480" "fl:s_ln:spergel-1"        
"Bristol Myers Squibb" 2017 "10000480" "fl:q_ln:liu-6"            
"Bristol Myers Squibb" 2017 "10000480" "fl:a_ln:kumar-164"        
"Bristol Myers Squibb" 2017 "10000480" "fl:s_ln:spergel-1"        
"Bristol Myers Squibb" 2016 "10000480" "fl:y_ln:zhang-15"          
"Bristol Myers Squibb" 2016 "10000480" "fl:d_ln:batt-3"            
"Bristol Myers Squibb" 2019 "10000480" "fl:a_ln:kumar-164"        
"Bristol Myers Squibb" 2018 "10000480" "fl:s_ln:spergel-1"        
"Bristol Myers Squibb" 2016 "10000480" "fl:c_ln:liu-601"          
"Bristol Myers Squibb" 2016 "10000480" "fl:q_ln:liu-6"            
"Bristol Myers Squibb" 2017 "10000480" "fl:c_ln:liu-601"          
"Bristol Myers Squibb" 2019 "10000480" "fl:c_ln:liu-601"          
"Bristol Myers Squibb" 2019 "10000480" "fl:r_ln:moslin-1"          
"Bristol Myers Squibb" 2018 "10000480" "fl:q_ln:liu-6"            
"Bristol Myers Squibb" 2017 "10000480" "fl:r_ln:moslin-1"          
"Bristol Myers Squibb" 2018 "10000480" "fl:a_ln:kumar-164"        
"Merck & Co"           2015 "10000484" "fl:s_ln:dong-19"          
"Merck & Co"           2016 "10000484" "fl:f_ln:ding-1"            
"Merck & Co"           2014 "10000484" "fl:j_ln:jiang-26"          
"Merck & Co"           2016 "10000484" "fl:j_ln:jiang-26"          
"Merck & Co"           2018 "10000484" "fl:j_ln:jiang-26"          
"Merck & Co"           2014 "10000484" "fl:y_ln:guo-249"          
"Merck & Co"           2019 "10000484" "fl:j_ln:jiang-26"          
"Merck & Co"           2015 "10000484" "fl:y_ln:guo-249"          
"Merck & Co"           2019 "10000484" "fl:f_ln:ding-1"            
"Merck & Co"           2016 "10000484" "fl:b_ln:pio-2"            
"Merck & Co"           2017 "10000484" "fl:s_ln:walsh-12"          
"Merck & Co"           2015 "10000484" "fl:s_ln:walsh-12"          
"Merck & Co"           2016 "10000484" "fl:s_ln:walsh-12"          
"Merck & Co"           2014 "10000484" "fl:s_ln:dong-19"          
"Merck & Co"           2017 "10000484" "fl:f_ln:ding-1"            
"Merck & Co"           2018 "10000484" "fl:s_ln:walsh-12"          
"Merck & Co"           2014 "10000484" "fl:s_ln:walsh-12"          
"Merck & Co"           2015 "10000484" "fl:h_ln:chobanian-1"      
"Merck & Co"           2014 "10000484" "fl:f_ln:ding-1"            
"Merck & Co"           2019 "10000484" "fl:s_ln:dong-19"          
"Merck & Co"           2015 "10000484" "fl:f_ln:ding-1"            
"Merck & Co"           2017 "10000484" "fl:j_ln:jiang-26"          
"Merck & Co"           2019 "10000484" "fl:s_ln:walsh-12"          
"Merck & Co"           2014 "10000484" "fl:h_ln:chobanian-1"      
"Merck & Co"           2019 "10000484" "fl:y_ln:guo-249"          
"Merck & Co"           2017 "10000484" "fl:b_ln:pio-2"            
"Merck & Co"           2018 "10000484" "fl:y_ln:guo-249"          
"Merck & Co"           2019 "10000484" "fl:b_ln:pio-2"            
"Merck & Co"           2018 "10000484" "fl:d_ln:kim-624"          
"Merck & Co"           2018 "10000484" "fl:s_ln:dong-19"          
"Merck & Co"           2016 "10000484" "fl:y_ln:guo-249"          
"Merck & Co"           2015 "10000484" "fl:j_ln:jiang-26"          
"Merck & Co"           2016 "10000484" "fl:s_ln:dong-19"          
"Merck & Co"           2017 "10000484" "fl:d_ln:kim-624"          
"Merck & Co"           2016 "10000484" "fl:h_ln:chobanian-1"      
"Merck & Co"           2018 "10000484" "fl:f_ln:ding-1"            
"Merck & Co"           2019 "10000484" "fl:h_ln:chobanian-1"      
"Merck & Co"           2016 "10000484" "fl:d_ln:kim-624"          
"Merck & Co"           2015 "10000484" "fl:d_ln:kim-624"          
"Merck & Co"           2015 "10000484" "fl:b_ln:pio-2"            
"Merck & Co"           2017 "10000484" "fl:s_ln:dong-19"          
"Merck & Co"           2014 "10000484" "fl:b_ln:pio-2"            
"Merck & Co"           2018 "10000484" "fl:b_ln:pio-2"            
"Merck & Co"           2018 "10000484" "fl:h_ln:chobanian-1"      
"Merck & Co"           2019 "10000484" "fl:d_ln:kim-624"          
"Merck & Co"           2017 "10000484" "fl:y_ln:guo-249"          
"Merck & Co"           2014 "10000484" "fl:d_ln:kim-624"          
"Merck & Co"           2017 "10000484" "fl:h_ln:chobanian-1"      
"Merck & Co"           2017 "10000499" "fl:j_ln:skudlarek-1"      
"Merck & Co"           2018 "10000499" "fl:s_ln:kuduk-2"          
"Merck & Co"           2015 "10000499" "fl:s_ln:kuduk-2"          
"Merck & Co"           2019 "10000499" "fl:t_ln:reger-2"          
"Merck & Co"           2016 "10000499" "fl:t_ln:reger-2"          
"Merck & Co"           2015 "10000499" "fl:j_ln:skudlarek-1"      
"Merck & Co"           2019 "10000499" "fl:j_ln:skudlarek-1"      
"Merck & Co"           2018 "10000499" "fl:t_ln:reger-2"          
"Merck & Co"           2015 "10000499" "fl:t_ln:reger-2"          
"Merck & Co"           2018 "10000499" "fl:j_ln:skudlarek-1"      
"Merck & Co"           2016 "10000499" "fl:j_ln:skudlarek-1"      
"Merck & Co"           2017 "10000499" "fl:t_ln:reger-2"          
"Merck & Co"           2019 "10000499" "fl:s_ln:kuduk-2"          
"Merck & Co"           2017 "10000499" "fl:s_ln:kuduk-2"          
"Merck & Co"           2016 "10000499" "fl:s_ln:kuduk-2"          
"Merck & Co"           2017 "10000501" "fl:r_ln:liu-59"            
"Merck & Co"           2018 "10000501" "fl:w_ln:quan-1"            
"Merck & Co"           2018 "10000501" "fl:j_ln:debenham-2"        
"Merck & Co"           2019 "10000501" "fl:l_ln:wang-603"          
"Merck & Co"           2018 "10000501" "fl:a_ln:crespo-7"          
"Merck & Co"           2017 "10000501" "fl:d_ln:guiadeen-2"        
"Merck & Co"           2017 "10000501" "fl:w_ln:quan-1"            
"Merck & Co"           2019 "10000501" "fl:p_ln:liu-140"          
"Merck & Co"           2015 "10000501" "fl:v_ln:colandrea-4"      
"Merck & Co"           2019 "10000501" "fl:r_ln:liu-59"            
"Merck & Co"           2015 "10000501" "fl:x_ln:du-106"            
"Merck & Co"           2017 "10000501" "fl:v_ln:colandrea-4"      
"Merck & Co"           2015 "10000501" "fl:j_ln:debenham-2"        
"Merck & Co"           2017 "10000501" "fl:p_ln:liu-140"          
"Merck & Co"           2016 "10000501" "fl:p_ln:liu-140"          
"Merck & Co"           2018 "10000501" "fl:l_ln:wang-603"          
"Merck & Co"           2018 "10000501" "fl:j_ln:cai-7"            
"Merck & Co"           2017 "10000501" "fl:l_ln:wang-603"          
"Merck & Co"           2018 "10000501" "fl:c_ln:madsen-duggan-1"  
"Merck & Co"           2018 "10000501" "fl:r_ln:liu-59"            
"Merck & Co"           2018 "10000501" "fl:d_ln:guiadeen-2"        
"Merck & Co"           2015 "10000501" "fl:r_ln:liu-59"            
"Merck & Co"           2016 "10000501" "fl:x_ln:du-106"            
"Merck & Co"           2017 "10000501" "fl:c_ln:sinz-1"            
"Merck & Co"           2019 "10000501" "fl:a_ln:crespo-7"          
"Merck & Co"           2016 "10000501" "fl:r_ln:liu-59"            
"Merck & Co"           2017 "10000501" "fl:x_ln:du-106"            
"Merck & Co"           2016 "10000501" "fl:l_ln:wang-603"          
"Merck & Co"           2019 "10000501" "fl:c_ln:sinz-1"            
"Merck & Co"           2016 "10000501" "fl:c_ln:madsen-duggan-1"  
"Merck & Co"           2016 "10000501" "fl:c_ln:sinz-1"            
"Merck & Co"           2019 "10000501" "fl:d_ln:guiadeen-2"        
"Merck & Co"           2018 "10000501" "fl:c_ln:sinz-1"            
"Merck & Co"           2019 "10000501" "fl:j_ln:mccoy-48"          
"Merck & Co"           2017 "10000501" "fl:j_ln:cai-7"            
"Merck & Co"           2015 "10000501" "fl:a_ln:crespo-7"          
"Merck & Co"           2016 "10000501" "fl:j_ln:cai-7"            
"Merck & Co"           2015 "10000501" "fl:p_ln:liu-140"          
"Merck & Co"           2019 "10000501" "fl:c_ln:madsen-duggan-1"  
"Merck & Co"           2015 "10000501" "fl:c_ln:sinz-1"            
"Merck & Co"           2016 "10000501" "fl:w_ln:quan-1"            
"Merck & Co"           2019 "10000501" "fl:j_ln:debenham-2"        
"Merck & Co"           2015 "10000501" "fl:j_ln:cai-7"            
"Merck & Co"           2019 "10000501" "fl:v_ln:colandrea-4"      
"Merck & Co"           2015 "10000501" "fl:j_ln:mccoy-48"          
"Merck & Co"           2015 "10000501" "fl:c_ln:madsen-duggan-1"  
"Merck & Co"           2015 "10000501" "fl:l_ln:wang-603"          
"Merck & Co"           2016 "10000501" "fl:j_ln:mccoy-48"          
"Merck & Co"           2017 "10000501" "fl:c_ln:madsen-duggan-1"  
"Merck & Co"           2018 "10000501" "fl:j_ln:mccoy-48"          
"Merck & Co"           2018 "10000501" "fl:p_ln:liu-140"          
"Merck & Co"           2016 "10000501" "fl:j_ln:debenham-2"        
"Merck & Co"           2018 "10000501" "fl:v_ln:colandrea-4"      
"Merck & Co"           2018 "10000501" "fl:x_ln:du-106"            
"Merck & Co"           2016 "10000501" "fl:v_ln:colandrea-4"      
"Merck & Co"           2015 "10000501" "fl:d_ln:guiadeen-2"        
"Merck & Co"           2016 "10000501" "fl:a_ln:crespo-7"          
"Merck & Co"           2017 "10000501" "fl:j_ln:debenham-2"        
"Merck & Co"           2019 "10000501" "fl:w_ln:quan-1"            
"Merck & Co"           2016 "10000501" "fl:d_ln:guiadeen-2"        
"Merck & Co"           2015 "10000501" "fl:w_ln:quan-1"            
"Merck & Co"           2019 "10000501" "fl:j_ln:cai-7"            
"Merck & Co"           2017 "10000501" "fl:j_ln:mccoy-48"          
"Merck & Co"           2017 "10000501" "fl:a_ln:crespo-7"          
"Merck & Co"           2019 "10000501" "fl:x_ln:du-106"            
"Johnson & Johnson"    2019 "10000502" "fl:p_ln:connolly-4"        
"Johnson & Johnson"    2018 "10000502" "fl:z_ln:zhang-40"          
"Johnson & Johnson"    2017 "10000502" "fl:j_ln:branch-9"          
"Johnson & Johnson"    2019 "10000502" "fl:v_ln:pande-2"          
"Johnson & Johnson"    2019 "10000502" "fl:j_ln:branch-9"          
"Johnson & Johnson"    2018 "10000502" "fl:p_ln:connolly-4"        
"Johnson & Johnson"    2019 "10000502" "fl:c_ln:rocaboy-1"        
"Johnson & Johnson"    2017 "10000502" "fl:l_ln:meerpoel-1"        
"Johnson & Johnson"    2017 "10000502" "fl:c_ln:rocaboy-1"        
"Johnson & Johnson"    2019 "10000502" "fl:z_ln:zhang-40"          
"Johnson & Johnson"    2018 "10000502" "fl:l_ln:trabalon escolar-1"
"Johnson & Johnson"    2017 "10000502" "fl:z_ln:zhang-40"          
"Johnson & Johnson"    2018 "10000502" "fl:c_ln:rocaboy-1"        
"Johnson & Johnson"    2017 "10000502" "fl:v_ln:pande-2"          
"Johnson & Johnson"    2018 "10000502" "fl:l_ln:meerpoel-1"        
"Johnson & Johnson"    2019 "10000502" "fl:l_ln:trabalon escolar-1"
"Johnson & Johnson"    2019 "10000502" "fl:i_ln:hickson-1"        
"Johnson & Johnson"    2017 "10000502" "fl:p_ln:connolly-4"        
"Johnson & Johnson"    2019 "10000502" "fl:g_ln:bignan-1"          
"Johnson & Johnson"    2017 "10000502" "fl:l_ln:trabalon escolar-1"
"Johnson & Johnson"    2018 "10000502" "fl:v_ln:pande-2"          
"Johnson & Johnson"    2018 "10000502" "fl:j_ln:branch-9"          
"Johnson & Johnson"    2018 "10000502" "fl:i_ln:hickson-1"        
"Johnson & Johnson"    2017 "10000502" "fl:i_ln:hickson-1"        
"Johnson & Johnson"    2019 "10000502" "fl:l_ln:meerpoel-1"        
"Johnson & Johnson"    2017 "10000502" "fl:g_ln:bignan-1"          
"Johnson & Johnson"    2018 "10000502" "fl:g_ln:bignan-1"          
"Incyte"               2018 "10000507" "fl:d_ln:burns-16"          
"Incyte"               2016 "10000507" "fl:m_ln:rafalski-1"        
"Incyte"               2016 "10000507" "fl:h_ln:ye-4"              
"Incyte"               2016 "10000507" "fl:y_ln:li-55"            
"Incyte"               2016 "10000507" "fl:s_ln:mei-24"            
"Incyte"               2017 "10000507" "fl:a_ln:wang-189"          
"Incyte"               2018 "10000507" "fl:h_ln:ye-4"              
"Incyte"               2019 "10000507" "fl:y_ln:li-55"            
"Incyte"               2016 "10000507" "fl:t_ln:huang-350"        
"Incyte"               2018 "10000507" "fl:s_ln:mei-24"            
"Incyte"               2016 "10000507" "fl:h_ln:feng-28"          
"Incyte"               2016 "10000507" "fl:w_ln:zhu-132"          
"Incyte"               2017 "10000507" "fl:s_ln:mei-24"            
"Incyte"               2019 "10000507" "fl:d_ln:burns-16"          
"Incyte"               2018 "10000507" "fl:y_ln:li-55"            
"Incyte"               2016 "10000507" "fl:j_ln:pan-77"            
"Incyte"               2017 "10000507" "fl:y_ln:li-55"            
"Incyte"               2019 "10000507" "fl:t_ln:huang-350"        
"Incyte"               2017 "10000507" "fl:c_ln:xue-1"            
"Incyte"               2018 "10000507" "fl:m_ln:rafalski-1"        
"Incyte"               2019 "10000507" "fl:c_ln:xue-1"            
"Incyte"               2019 "10000507" "fl:s_ln:mei-24"            
"Incyte"               2017 "10000507" "fl:d_ln:burns-16"          
"Incyte"               2017 "10000507" "fl:h_ln:ye-4"              
"Incyte"               2017 "10000507" "fl:m_ln:rafalski-1"        
"Incyte"               2017 "10000507" "fl:j_ln:pan-77"            
"Incyte"               2017 "10000507" "fl:h_ln:feng-28"          
"Incyte"               2018 "10000507" "fl:j_ln:pan-77"            
"Incyte"               2018 "10000507" "fl:c_ln:xue-1"            
"Incyte"               2017 "10000507" "fl:t_ln:huang-350"        
"Incyte"               2018 "10000507" "fl:h_ln:feng-28"          
"Incyte"               2019 "10000507" "fl:h_ln:ye-4"              
"Incyte"               2016 "10000507" "fl:d_ln:burns-16"          
"Incyte"               2017 "10000507" "fl:w_ln:zhu-132"          
"Incyte"               2019 "10000507" "fl:m_ln:rafalski-1"        
"Incyte"               2019 "10000507" "fl:w_ln:zhu-132"          
"Incyte"               2018 "10000507" "fl:t_ln:huang-350"        
"Incyte"               2016 "10000507" "fl:a_ln:wang-189"          
"Incyte"               2019 "10000507" "fl:j_ln:pan-77"            
"Incyte"               2016 "10000507" "fl:c_ln:xue-1"            
"Incyte"               2018 "10000507" "fl:a_ln:wang-189"          
"Incyte"               2019 "10000507" "fl:a_ln:wang-189"          
"Incyte"               2019 "10000507" "fl:h_ln:feng-28"          
"Incyte"               2018 "10000507" "fl:w_ln:zhu-132"          
"Merck & Co"           2019 "10000510" "fl:y_ln:hwang-2"          
"Merck & Co"           2017 "10000510" "fl:c_ln:lopez-9"          
"Merck & Co"           2016 "10000510" "fl:c_ln:lopez-9"          
"Merck & Co"           2016 "10000510" "fl:j_ln:gu-76"            
"Merck & Co"           2018 "10000510" "fl:c_ln:lopez-9"          
"Merck & Co"           2019 "10000510" "fl:s_ln:garad-1"          
"Merck & Co"           2015 "10000510" "fl:j_ln:gu-76"            
"Merck & Co"           2017 "10000510" "fl:j_ln:gu-76"            
"Merck & Co"           2015 "10000510" "fl:y_ln:hwang-2"          
"Merck & Co"           2018 "10000510" "fl:p_ln:pathare-1"        
"Merck & Co"           2015 "10000510" "fl:s_ln:garad-1"          
"Merck & Co"           2017 "10000510" "fl:k_ln:moshos-1"          
"Merck & Co"           2018 "10000510" "fl:s_ln:garad-1"          
"Merck & Co"           2019 "10000510" "fl:j_ln:gu-76"            
"Merck & Co"           2017 "10000510" "fl:s_ln:garad-1"          
"Merck & Co"           2016 "10000510" "fl:y_ln:hwang-2"          
"Merck & Co"           2018 "10000510" "fl:v_ln:jurkauskas-2"      
"Merck & Co"           2018 "10000510" "fl:y_ln:hwang-2"          
"Merck & Co"           2016 "10000510" "fl:v_ln:jurkauskas-2"      
"Merck & Co"           2017 "10000510" "fl:p_ln:pathare-1"        
"Merck & Co"           2018 "10000510" "fl:j_ln:gu-76"            
"Merck & Co"           2015 "10000510" "fl:v_ln:jurkauskas-2"      
"Merck & Co"           2017 "10000510" "fl:v_ln:jurkauskas-2"      
"Merck & Co"           2016 "10000510" "fl:p_ln:pathare-1"        
"Merck & Co"           2015 "10000510" "fl:c_ln:lopez-9"          
"Merck & Co"           2015 "10000510" "fl:p_ln:pathare-1"        
"Merck & Co"           2016 "10000510" "fl:s_ln:garad-1"          
"Merck & Co"           2019 "10000510" "fl:c_ln:lopez-9"          
"Merck & Co"           2019 "10000510" "fl:p_ln:pathare-1"        
"Merck & Co"           2019 "10000510" "fl:v_ln:jurkauskas-2"      
"Merck & Co"           2018 "10000510" "fl:k_ln:moshos-1"          
"Merck & Co"           2016 "10000510" "fl:k_ln:moshos-1"          
"Merck & Co"           2015 "10000510" "fl:k_ln:moshos-1"          
"Merck & Co"           2019 "10000510" "fl:k_ln:moshos-1"          
"Merck & Co"           2017 "10000510" "fl:y_ln:hwang-2"          
"Johnson & Johnson"    2017 "10000559" "fl:m_ln:murdock-2"        
"Johnson & Johnson"    2018 "10000559" "fl:x_ln:liu-42"            
"Johnson & Johnson"    2019 "10000559" "fl:s_ln:wu-6"              
"Johnson & Johnson"    2017 "10000559" "fl:c_ln:alderfer-1"        
"Johnson & Johnson"    2018 "10000559" "fl:t_ln:malia-2"          
"Johnson & Johnson"    2019 "10000559" "fl:m_ln:vandermeeren-1"    
"Johnson & Johnson"    2017 "10000559" "fl:m_ln:vandermeeren-1"    
"Johnson & Johnson"    2017 "10000559" "fl:x_ln:liu-42"            
"Johnson & Johnson"    2018 "10000559" "fl:s_ln:wu-6"              
"Johnson & Johnson"    2019 "10000559" "fl:d_ln:janecki-1"        
"Johnson & Johnson"    2018 "10000559" "fl:m_ln:vandermeeren-1"    
"Johnson & Johnson"    2018 "10000559" "fl:c_ln:alderfer-1"        
"Johnson & Johnson"    2019 "10000559" "fl:t_ln:malia-2"          
"Johnson & Johnson"    2017 "10000559" "fl:s_ln:wu-6"              
"Johnson & Johnson"    2018 "10000559" "fl:m_ln:murdock-2"        
"Johnson & Johnson"    2019 "10000559" "fl:c_ln:alderfer-1"        
"Johnson & Johnson"    2019 "10000559" "fl:x_ln:liu-42"            
"Johnson & Johnson"    2018 "10000559" "fl:m_ln:mercken-2"        
"Johnson & Johnson"    2018 "10000559" "fl:d_ln:janecki-1"        
"Johnson & Johnson"    2019 "10000559" "fl:m_ln:murdock-2"        
"Johnson & Johnson"    2019 "10000559" "fl:m_ln:mercken-2"        
"Johnson & Johnson"    2017 "10000559" "fl:d_ln:janecki-1"        
"Johnson & Johnson"    2017 "10000559" "fl:m_ln:mercken-2"        
"Johnson & Johnson"    2017 "10000559" "fl:t_ln:malia-2"          
end

Tags: None

Andrew Musau

Join Date: Oct 2014
Posts: 10483

03 Dec 2021, 09:43

Here is a brute-force method:

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input float patentid str2 inventor
1 "A"
1 "B"
1 "C"
2 "B"
2 "C"
2 "D"
2 "E"
2 "F"
3 "E"
3 "G"
3 "B"
3 "A"
end

preserve
contract patentid inventor
tempfile list
save `list'
levelsof inventor, local(inventors) clean
foreach inventor in `inventors'{
    use `list', clear
    bys patentid: egen ingroup= max(inventor=="`inventor'")
    keep if ingroup
    contract inventor, freq(freq)
    gen N=_N-1
    keep if inventor=="`inventor'"
    mkmat N, rowname(inventor)
    mat wanted= nullmat(wanted)\N
}
restore
l, sepby(patent)
mat l wanted

Res.:

Code:

. l, sepby(patent)

     +---------------------+
     | patentid   inventor |
     |---------------------|
  1. |        1          A |
  2. |        1          B |
  3. |        1          C |
     |---------------------|
  4. |        2          B |
  5. |        2          C |
  6. |        2          D |
  7. |        2          E |
  8. |        2          F |
     |---------------------|
  9. |        3          E |
 10. |        3          G |
 11. |        3          B |
 12. |        3          A |
     +---------------------+

. 
. mat l wanted

wanted[7,1]
   N
A  4
B  6
C  5
D  4
E  6
F  4
G  3

.

Last edited by Andrew Musau; 03 Dec 2021, 09:46.

Announcement

Patent Inventor Data - Counting and Averaging Collaborators

Comment