Find missing numbers in a sorted column in Pandas Dataframe

user_11740459 0
Viewed 328

I have this dataframe below and I want to detect missing Visits for each subject, how can I sort the visits by Subject and extract only the records that has missing values ? Please check the two type of output needed.

Part 1 : So depending on the highest number in the Column "Visit" for all Subjects missing records for all subjects needs to show up :

    Subject  Visit      X1      X2
       A       1    1647143  1672244
       A       2    1672244  1689707
       A       4    1689707  1713090
       B       1    1735352  1760283
       B       2    1760283  1788062
       B       7    1788062  1789885
       B       9    1789885  1790728

The output would be :

    Subject  Visit      X1      X2
       A       3    1647143  1672244
       A       5    1672244  1689707
       A       6    1689707  1713090
       A       7    1647143  1672244
       A       8    1672244  1689707
       A       9    1689707  1713090
       B       3    1735352  1760283
       B       4    1760283  1788062
       B       5    1788062  1789885
       B       6    1789885  1790728
       B       8    1789885  1790728

Part 2 : So depending on the highest number in the Column "Visit" for each specific Subject missing records in the Sequence of visits needs to show up : Example Output :

    Subject  Visit      X1      X2
       A       3    1647143  1672244
       B       3    1735352  1760283
       B       4    1760283  1788062
       B       5    1788062  1789885
       B       6    1789885  1790728
       B       8    1789885  1790728
pandas tidyverse dataframe r python
3 Answers

You find the missing visits for each subject, where the max visit per subject is the max of the Visit column, you can create the set of all possible (subject, visit) pairs, and then difference the observed pairs.

from itertools import product

all_pairs = set(product(sorted(set(df.Subject)), range(1, df.Visit.max()+1)))
observed_pairs = set(tuple(x) for x in df[['Subject', 'Visit']].to_numpy())

# create a data frame from the missing pairs
pd.DataFrame(sorted(all_pairs.difference(observed_pairs)), columns=['Subject', 'Visit'])
# returns:
   Subject  Visit
0        A      3
1        A      5
2        A      6
3        A      7
4        A      8
5        A      9
6        B      3
7        B      4
8        B      5
9        B      6
10       B      8

To find the missing visit within the max visit range per subject. You can do the following:

def missing_visits(s):
    all_v = set(range(1, s.max()+1))
    obs_v = set(s)
    return sorted(all_v.difference(obs_v))


df.groupby('Subject')['Visit'].apply(missing_visits).explode()
# returns:
Subject
A    3
B    3
B    4
B    5
B    6
B    8
user_50037560 
 #Use the min, max in the visit column for each group to reindex df and fillna
    g=df.groupby('Subject',group_keys=False).apply(lambda x:x.reindex(np.arange(x['Visit'].min(),x['Visit'].max())).ffill().bfill())

#Update the visit column
g['Visit']=g.index

print(g)
# First outcome



Subject  Visit         X1         X2
1       A      1  1672244.0  1689707.0
2       A      2  1689707.0  1713090.0
3       A      3  1689707.0  1713090.0
1       B      1  1735352.0  1760283.0
2       B      2  1735352.0  1760283.0
3       B      3  1735352.0  1760283.0
4       B      4  1760283.0  1788062.0
5       B      5  1788062.0  1789885.0
6       B      6  1789885.0  1790728.0
7       B      7  1789885.0  1790728.0
8       B      8  1789885.0  1790728.0


#Filtered outcome

    #Create and compare tuples of ['Subject','Visit'] of the original and new dataframes
g[~g[['Subject','Visit']].agg(tuple,1).isin(df[['Subject','Visit']].agg(tuple,1))]



    Subject  Visit    X1         X2
3       A      3  1689707.0  1713090.0
3       B      3  1735352.0  1760283.0
4       B      4  1760283.0  1788062.0
5       B      5  1788062.0  1789885.0
6       B      6  1789885.0  1790728.0
8       B      8  1789885.0  1790728.0
user_89869750

Here are data.table options in R

  • Part1
> setDT(df)[, .(Visit = setdiff(seq(max(df[, "Visit"])), Visit)), Subject]
    Subject Visit
 1:       A     3
 2:       A     5
 3:       A     6
 4:       A     7
 5:       A     8
 6:       A     9
 7:       B     3
 8:       B     4
 9:       B     5
10:       B     6
11:       B     8
  • Part2
> setDT(df)[, .(Visit = setdiff(seq(max(Visit)), Visit)), Subject]
   Subject Visit
1:       A     3
2:       B     3
3:       B     4
4:       B     5
5:       B     6
6:       B     8
user_121587570
Related