Finding unique number of IDs in multiple groups

user_12388856 0
Viewed 102

I have a dataset that has doctors and the various practices they work in. Each doctor in my dataset works in at least 1 practice but as many as 17 different practices. I would like to know the unique number of doctors each one works with. The current dataset is in SAS, but I am familiar with Python, Pandas, and SQL. I am fine converting the data into whichever format needed so the answer does not need to be in SAS code.

Example dataset below. This sample shows that Doctor A is in practices, P1, P3, and P5. Doctor E is in practices P1, P2, and P5,etc.

enter image description here

From this chart I would want a new column with the total number of unique doctors each one works with. In this case Doctor A works with 2 other doctors (E & D.) However, if I simply grouped by doctor and summed, I find that Doctor A works with 6 Doctors. However this is wrong because it would count Doctor A 3 times (once for each practice he is listed in) AND it would count Doctor E twice (he is in two group practices with Doctor A, P1 & P5)

I have ~ 800,000 doctors with ~400,000 group practices making manual methods unfeasible. Does any one have any suggestions on how to get this started?

The final output would look:

enter image description here

sample data set code (for SAS) 

input doctor $ tot_in_group group_practices $;
datalines;
A 2 P1
E 2 P1
C 3 P2
B 3 P2
E 3 P2
A 2 P3
D 2 P3

E 2 P5
A 2 P5
;
run;
pandas sas join sql python
4 Answers

A self join within group excluding self-pairings will generate a table of all pairings for each group. Use that concept as the basis for counting the distinct 'partner's for each doctor over all groups.

For true uniqueness, be sure you are using a doctorId distinct to each individual. Attempting to prevent a 'self-pair' based on a name is asking for trouble. (Consider a fictitious group with doctors Dewey, Dewey, Dewey, Dewey and Dewey -- yeah trouble)

data have;
input doctor $ group $;
datalines;
A P1
E P1
C P2
B P2
E P2
A P3
D P3
E P3
E P5
A P5
;
run;

proc sql;
  * demonstrate the combinatoric effect of who (P) paired with whom (Q) within group;
  * do not submit against the big data;

  create table works_with_each as
  select 
    P.doctor as P
  , Q.doctor as Q
  , P.group 
  from have as P
  join have as Q
    on P.group = Q.group
     & P.doctor ^= Q.doctor
  order by 
   P.doctor, Q.doctor, P.group
  ; 

  * count the distinct pairing, regardless of group;

  create table works_with_counts as
  select 
    P.doctor as P
  , count(distinct Q.doctor) as unique_work_with_count
  from have as P
  join have as Q
    on P.group = Q.group
     & P.doctor ^= Q.doctor
  group by 
   P.doctor
  order by 
   P.doctor
  ;

Each
Each works with pair

Unique other in Pair (Works with) Counts
Works with Counts

user_12499620

You might need to do it in more queries in your language (especially the COUNT(DISTINCT var))

SELECT docA , COUNT(DISTINCT docB) FROM 
    (SELECT A.doctor as docA, B.doctor as docB FROM mytable A JOIN mytable B
    ON A.group_practices = B.group_practices WHERE A.doctor > B.doctor)
GROUP BY docA

Then you can join this table to the one you showed previously on doctor = docA

the docA>docB prevent having :

  • A in relation with A,
  • or to have duplicates like A in relation in B and B in relation with A
user_133608440

You could just self-join and aggregate:

select t.doctor, count(distinct t1.doctor) no_coworkers
from mytable t
inner join mytable t1 on t1.doctor <> t.doctor and t1.group_practices = t.group_practices
group by t.doctor
user_106767160

In a database, you would use a window function:

select t.*, count(*) over (partition by practice) as cnt
from t;

Proc SQL doesn't support that. Instead, you can use a weird feature in SAS called "remerging":

select t.*, count(*) as num_in_practice
from t
group by practice;

If you are using a native format to a database, then use the window function!

user_11440350
Related